Documentation Links

These are some useful documentation links I’ve come across.

 

It’s fair to say that most of the information I need, I get straight from searching google. These sites come up again and again, but mostly google is the best way to get directly to where you need to be, when you need to be there.

 

 

it-ebooks.info	This site allows you to download PDF copies of all manner of books including Oracle. Not sure at all about this one. I can’t believe it’s legal so use it entirely at your own discretion. It often comes up when you’re searching google for legal sources of manuals (that’s how I know about it).
http://www.oracle.com/technetwork/indexes/documentation/index.html	Oracle’s official documentation site. All versions are available from here.
https://community.oracle.com	Oracle’s official forum
http://ss64.com/ora/	A very simple SQL command reference. List of commands, click to see the manual page.
http://www.dbasupport.com	Another good forum site
http://www.dba-oracle.com	Another good site from Don Burleson. Some experts seem to have doubts about the quality of his advice but I like it.

 

A few other links

 

http://www.oracle.com/us/downloads/index.html	Oracle’s main software download area. Just as easy to find it from www.oracle.com though.
https://edelivery.oracle.com/	Oracle’s other software site. You’ll need a verified email account to get into this.
https://support.oracle.com	My Oracle Support or Metalink as was. A full account is only available with your CSI.

 

 

 

Happyjohn

 

RMAN Reporting commands

RMAN – Reporting commands

LIST
lists details of backups, archived logs, and database incarnations.

LIST BACKUP;                                     List all backup sets, image copies and proxy copies.

LIST BACKUP SUMMARY;             As above but significantly more concise and usable;
LIST BACKUPSET;                              Lists only backup sets and proxy copies (can also use the summary option).
LIST COPY;                                           Lists only Image copies (cannot use the summary option).

LIST EXPIRED BACKUP;                   Backups did not found after crosscheck.

That is backup is manually moved or deleted from OS.

Obsolete can only be viewed from the REPORT command

LIST BACKUP BY FILE;                      List backup by Datafile, archivelogs, controlfile, spfile.

Easier to read than LIST BACKUP but obviously not ordered by set no.

LIST BACKUP OF DATABASE;       List backups but only data files.

LIST BACKUP OF DATAFILE 1;      As above only for a single data file.

 

LIST BACKUP LIKE '/tmp/%';        copy only

 

Other example variations

 

LIST ARCHIVELOG ALL BACKED UP 2 TIMES TO DEVICE TYPE DISK;

LIST COPY OF DATAFILE 2 COMPLETED BETWEEN '01-JAN-2014' AND '07-JAN-2014';
LIST INCARNATION;

LIST INCARNATION OF DATABASE;

                               
REPORT

analyses the available backups and return results about while files need backup which files are obsolete etc.

If backups have been deleted from disk or tape outside of RMAN, reports generated by RMAN will not automatically reflect these changes. Always run CROSSCHECK of all backup before reporting to update repository.

REPORT SCHEMA;                            Displays information about the database files. The isn’t an easier way of displaying this information.

REPORT OBSOLETE;                         Displays the backups that are obsolete according to the current retention policy.

REPORT NEED BACKUP;                 Determine which database files need backup for the retention policy.

REPORT UNRECOVERABLE;          Report which database files require backup because they have been affected by some NOLOGGING operation.

Other example variations

REPORT NEED BACKUP RECOVERY WINDOW OF 2 DAYS DATABASE SKIP TABLESPACE data01;

If you use Recovery Catalogue then past data can be shown.
REPORT SCHEMA AT TIME 'SYSDATE-10';


DELETE
used to delete any backup or backupsets.

delete backupset all;                                               Delete all backup sets

delete copy all;                                                            Delete all image copies.
DELETE OBSOLETE;                                                           Delete obsolete Backups.

DELETE EXPIRED BACKUP;                                            Delete expired RMAN backup following CROSSCHECK

DELETE CONTROLFILECOPY '/tmp/ctl01.ctl';          Delete backups by filename on disk
DELETE NOPROMPT ARCHIVELOG UNTIL SEQUENCE = 5;                Delete archive logs from disk based on sequence
DELETE BACKUP TAG='a_tag';                                     Delete backups based on tags

Other example variations

DELETE OBSOLETE REDUNDANCY = 2;
DELETE OBSOLETE RECOVERY WINDOW OF 4 DAYS;

Happyjohn

Nice and Simple : Archivelog mode

In Oracle 11g switching on archive log mode is simplicity itself.

 

Remember, you’ll need archivelog mode if you want  to use RMAN to backup your database without closing it first.

 

I’m going to put my archive redo logs into /u01/FRA with default names. You should probably check the current settings of db_recovery_file_dest and archive log list before doing this.

 

sqlplus / as sysdba

 

                show parameter recover

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_dest                string      /u01/FRA

db_recovery_file_dest_size           big integer 3852M

db_unrecoverable_scn_tracking        boolean     TRUE

recovery_parallelism                 integer     0

                archive log list

                                                Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     620

Current log sequence           622

                                quit

 

My db_recovery_file_dest is already set but we’ll do it again just to demonstrate.

 

mkdir /u01/FRA

sqlplus / as sysdba

 

shutdown immediate

startup mount

alter system set db_recovery_file_dest = ‘/u01/FRA’ scope=both;

alter database archivelog;

alter database open;

alter system switch logfile;

quit

 

ls /u01/FRA/ORCL/archivelog/2014_01_03

   where ORCL is the instance name and 2014_01_03 is the current date.

 

 

Happyjohn

 

Enterprise Linux - Keyboard 'deadkeys' problem

Problem:

 

When using the ‘Oracle Developer Days’ virtualbox virtual server, I found that the keyboard was set to US (English).

 

As very Englishman knows only too well, US English is NOT English.

 

So I switched the keyboard to the appropriate ‘United Kingdom International’ (although I’ve no idea what is international about it, since England is the mother country and centre of the universe). However the entry provided includes ‘with dead keys’. This bring about the very strange situation of DEADKEYS where you must click twice on the ‘ and “ to get anything, but what is input is still not a linux quote or double-quote. It appears that this option is for international characters with accents etc. Again, none of these are appropriate for true English so why this exists at all is anyone’s guess.

 

Solution:

 

There is much to be said of the web about this but it’s mostly un-applicable (or nonsense as we say).

 

My solution was to use the following command and insert it into the /etc/profile or other profile (bash, csh etc). For the oracle user I had to edit the .bashrc file. But it works.

 

setxkbmap gb

 

 

 

Happyjohn.

 

Oracle 11g - Silent Software Installation and Deinstallation

Silent Installation

Firstly you need to get your software down from Oracle.com and into a suitable location on your server. That’s up to you. When I was doing this I was using Oracle’s Virtualbox and a presetup virtual server which didn’t have the vsftpd daemon. So I had to download the 2 zip files to my host PC, and create a shared location in virtualbox. Fro mthere I was able to copy the zip files into /u01/orainst/11.2.0.1 and there unzip them.

From there I was able to copy file database/response/db_install.rsp to a safe location (/u01/orainst/11.2.0.1) and edit it to include only the items I was interested in. I did however keep the other entries but commented them out as you can see here. This includes all of the startdb items since I was only installing the software.

[oracle@localhost 11.2.0.1]$ cat db_install.rsp

oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v11_2_0

oracle.install.option=INSTALL_DB_SWONLY

ORACLE_HOSTNAME=Linux

UNIX_GROUP_NAME=dba

INVENTORY_LOCATION=/u01/orainst/11.2.0.1/database/stage/products.xml

SELECTED_LANGUAGES=en

ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1

ORACLE_BASE=/u01/app/oracle

oracle.install.db.InstallEdition=EE

oracle.install.db.isCustomInstall=false

#oracle.install.db.customComponents=oracle.server:11.2.0.1.0,oracle.sysman.ccr:10.2.7.0.0,oracle.xdk:11.2.0.1.0,oracle.rdbms.oci:11.2.0.1.0,oracle.network:11.2.0.1.0,oracle.network.listener:11.2.0.1.0,oracle.rdbms:11.2.0.1.0,oracle.options:11.2.0.1.0,oracle.rdbms.partitioning:11.2.0.1.0,oracle.oraolap:11.2.0.1.0,oracle.rdbms.dm:11.2.0.1.0,oracle.rdbms.dv:11.2.0.1.0,orcle.rdbms.lbac:11.2.0.1.0,oracle.rdbms.rat:11.2.0.1.0

oracle.install.db.DBA_GROUP=oracle

oracle.install.db.OPER_GROUP=oracle

#oracle.install.db.CLUSTER_NODES=

#oracle.install.db.config.starterdb.type=

#oracle.install.db.config.starterdb.globalDBName=

#oracle.install.db.config.starterdb.SID=

#oracle.install.db.config.starterdb.characterSet=AL32UTF8

#oracle.install.db.config.starterdb.memoryOption=true

#oracle.install.db.config.starterdb.memoryLimit=

#oracle.install.db.config.starterdb.installExampleSchemas=false

#oracle.install.db.config.starterdb.enableSecuritySettings=true

#oracle.install.db.config.starterdb.password.ALL=

#oracle.install.db.config.starterdb.password.SYS=

#oracle.install.db.config.starterdb.password.SYSTEM=

#oracle.install.db.config.starterdb.password.SYSMAN=

#oracle.install.db.config.starterdb.password.DBSNMP=

#oracle.install.db.config.starterdb.control=DB_CONTROL

#oracle.install.db.config.starterdb.gridcontrol.gridControlServiceURL=

#oracle.install.db.config.starterdb.dbcontrol.enableEmailNotification=false

#oracle.install.db.config.starterdb.dbcontrol.emailAddress=

#oracle.install.db.config.starterdb.dbcontrol.SMTPServer=

#oracle.install.db.config.starterdb.automatedBackup.enable=false

#oracle.install.db.config.starterdb.automatedBackup.osuid=

#oracle.install.db.config.starterdb.automatedBackup.ospwd=

#oracle.install.db.config.starterdb.storageType=

#oracle.install.db.config.starterdb.fileSystemStorage.dataLocation=

#oracle.install.db.config.starterdb.fileSystemStorage.recoveryLocation=

#oracle.install.db.config.asm.diskGroup=

#oracle.install.db.config.asm.ASMSNMPPassword=

#MYORACLESUPPORT_USERNAME=

#MYORACLESUPPORT_PASSWORD=

#SECURITY_UPDATES_VIA_MYORACLESUPPORT=

DECLINE_SECURITY_UPDATES=true

#PROXY_HOST=

#PROXY_PORT=

#PROXY_USER=

#PROXY_PWD=

I specified a totally new inventory location. Since I already had a full installation elsewhere on the server, I didn’t want to impact it at all with this installation.

Finally I ran the installation using the following commands.

cd database

./runInstaller -ignoreSysPrereqs -force -silent -responseFile /u01/orainst/11.2.0.1/db_install.rsp

I did have some issues with the GROUP settings. Originally /I had them down as dba but this didn’t serve. I found that dba hadn’t been allocated to the oracle user, however even after having done the necessary edit to /etc/group file it still didn’t work. Perhaps I needed to log out. Anyway, I just set them to oracle group and it all worked swimmingly.

While its running it is worth starting another session and tailing the log file to see what’s happening. The log file name is given in the output fro mteh runinstaller command (below).

                tail -f /home/oracle/app/oraInventory/logs/installActions2014-01-03_03-45-50AM.log

Output from the runInstaller command

[oracle@localhost database]$ ./runInstaller -ignoreSysPrereqs -force -silent -responseFile /u01/orainst/11.2.0.1/db_install.rsp

Starting Oracle Universal Installer...

Checking Temp space: must be greater than 80 MB.   Actual 7395 MB    Passed

Checking swap space: must be greater than 150 MB.   Actual 1520 MB    Passed

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2014-01-03_03-45-50AM. Please wait …

[WARNING] [INS-13014] Target environment do not meet some optional requirements.

   CAUSE: Some of the optional prerequisites are not met. See logs for details. /home/oracle/app/oraInventory/logs/installActions2014-01-03_03-45-50AM.log

   ACTION: Identify the list of failed prerequisite checks from the log: /home/oracle/app/oraInventory/logs/installActions2014-01-03_03-45-50AM.log. Then either from the log file or from installation manual find the appropriate configuration to meet the prerequisites and fix it manually.

[WARNING] [INS-13014] Target environment do not meet some optional requirements.

   CAUSE: Some of the optional prerequisites are not met. See logs for details. /home/oracle/app/oraInventory/logs/installActions2014-01-03_03-45-50AM.log

   ACTION: Identify the list of failed prerequisite checks from the log: /home/oracle/app/oraInventory/logs/installActions2014-01-03_03-45-50AM.log. Then either from the log file or from installation manual find the appropriate configuration to meet the prerequisites and fix it manually.

You can find the log of this install session at:

/home/oracle/app/oraInventory/logs/installActions2014-01-03_03-45-50AM.log

[WARNING] [INS-13014] Target environment do not meet some optional requirements.

   CAUSE: Some of the optional prerequisites are not met. See logs for details. /home/oracle/app/oraInventory/logs/installActions2014-01-03_03-45-50AM.log

   ACTION: Identify the list of failed prerequisite checks from the log: /home/oracle/app/oraInventory/logs/installActions2014-01-03_03-45-50AM.log. Then either from the log file or from installation manual find the appropriate configuration to meet the prerequisites and fix it manually.

The following configuration scripts need to be executed as the "root" user.

 #!/bin/sh

 #Root scripts to run

/u01/app/oracle/product/11.2.0/db_1/root.sh

To execute the configuration scripts:

         1. Open a terminal window

         2. Log in as "root"

         3. Run the scripts

         4. Return to this window and hit "Enter" key to continue

Successfully Setup Software.

Just follow the instructions at the end and everything is fine.

Removing the installation

Deleting it is really straight forward. Just cd to the new oracle home, then cd into deinstall and run deinstall. This gets rid of everything including the inventory items.

Happyjohn