11g helpful guide

8/12/2019 11g Helpful Guide

1/42


2/42

Media Recovery Log /u02/oradata/apexdg/arch/APEXDG/archivelog/2008_12_05/o1_mf_1_15694_4mk48s9y_.arcMedia Recovery Waiting for thread 1 sequence 15695 (in transit)Recovery of Online Redo Log: Thread 1 Group 5 Seq 15695 Reading mem 0Mem# 0: /u02/oradata/apexdg/standby02.log

Conclusion

The physical standby database is a physical replica of the primary database, kept in sync by the application of redolog entries from the primary database.

With Oracle Active Data Guard and real time apply, the log entries are applied in real time but the primary databasedoes not wait for the redo application to complete, thereby protecting itself from failures or slowdowns in the shipmentof log information from the primary to the standby database.Using the Oracle Active Data Guard option, you can openthe physical standby database for read-only operations while the managed recovery process is going on. You canoffload all reporting applications and the Oracle RMAN backup to the standby database, easing the load on theprimary database considerably.

And because the standby database is being recovered continuously with real time apply, the standby database can beactivated and used immediately in case the primary database fails. This combination of features makes the investmentin Oracle Active Data Guard very worthwhile.

11g Snapshot Standby Database

Oracle 11g introduces the Snapshot Standby database which essentially is an updateable standby database whichhas been created from a physical standby database.

We can convert a physical standby database to a snapshot standby database, do some kind of testing on a databasewhich is a read write copy of the current primary or production database and then finally revert it t o its earlier state asa physical standby database.

While the snapshot standby database is open in read-write mode, redo is being received from the primary database,but is not applied.

After converting it back to a physical standby database, it is resynchronized with the primary by applying theaccumalated redo data which was earlier shipped from the primary database but not applied.

Using a snapshot standby, we are able to do real time application testing using near real time production data. Veryoften we are required to do production clones for the purpose of testing. But using snapshot standby databases wecan meet the same requirement sparing the effort,time,resources and disk space.

Initially we have a Data Guard broker configuration in place where APEX is the Primary databaseand APEXDG is the Standby database.

DGMGRL> show configuration

ConfigurationName: gavinEnabled: YESProtection Mode: MaxAvailabilityDatabases:

apex - Primary databaseapexdg - Physical standby database

Fast-Start Failover: DISABLED

Current status for "gavin":SUCCESS

We now convert the physical standby database to a snapshot standby

DGMGRL> convert database 'apexdg' to snapshot standby;Converting database "apexdg" to a Snapshot Standby database, please wait...Database "apexdg" converted successfully

DGMGRL> show configuration


3/42

ConfigurationName: gavinEnabled: YESProtection Mode: MaxAvailabilityDatabases:

apex - Primary databaseapexdg - Snapshot standby database


Current status for "gavin":SUCCESS

We now connect to the snapshot standby database which is open in read-write mode and we create a table

apex:/u01/oracle/scripts> export ORACLE_SID=apexdgapexdg:/u01/oracle/scripts> sqlplus sh/SH

SQL*Plus: Release 11.1.0.6.0 - Production on Fri Sep 19 09:33:29 2008

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to:Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table test_snapshot2 as select * from all_objects;

Table created.

SQL> select count(*) from test_snapshot;

COUNT(*)----------

56467

At this point in time, we revert back to the original state

DGMGRL> convert database 'apexdg' to physical standby;

Converting database "apexdg" to a Physical Standby database, please wait...Operation requires shutdown of instance "apexdg" on database "apexdg"Shutting down instance "apexdg"...Database closed.

Database dismounted.ORACLE instance shut down.Operation requires startup of instance "apexdg" on database "apexdg"Starting instance "apexdg"...ORACLE instance started.Database mounted.Continuing to convert database "apexdg" ...Operation requires shutdown of instance "apexdg" on database "apexdg"Shutting down instance "apexdg"...ORA-01109: database not open

Database dismounted.ORACLE instance shut down.Operation requires startup of instance "apexdg" on database "apexdg"Starting instance "apexdg"...ORACLE instance started.Database mounted.Database "apexdg" converted successfully


4/42

DGMGRL>

We now connect to the physical standby database and see that all the changes we made whilethe standby database was functioning as a snapshot standby have been rolled back. The tablewhich we created in the snapshot standby database is not physically present in the physicalstandby database.

apexdg:/u01/oracle/scripts> sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Fri Sep 19 09:48:38 2008Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to:Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter database open read only;

Database altered.

SQL> conn sh/SH@apexdg

Connected.

SQL> select count(*) from test_snapshot;select count(*) from test_snapshot

*ERROR at line 1:ORA-00942: table or view does not exist

11g Passwords can be case sensitive

In 11g Passwords are case sensitive but this can be controlled by an init.ora parameter

SQL> show parameter SEC_CASE_SENSITIVE_LOGON ;

NAME TYPE VALUE sec_case_sensitive_logon boolean TRUE

SQL> create user arjun identified by ARJUN; Password in Upper Case.

User created.

SQL> grant create session to arjun;

Grant succeeded.

SQL> conn arjun/arjun; Tried to connect with lower case password FAILS.ERROR:ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.SQL> exitapex:/u01/oracle> sqlplus sys as sysdba

Connected to:Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> conn arjun/ARJUN CONNECTS Connected.SQL>exit

Sqlplus sys as sysdba


5/42

? drop user arjun cascade;

Now alter the parameter to FALSE so case sensitive passwords are not required.

SQL> alter system set sec_case_sensitive_logon=false;

System altered.

SQL> create user arjun identified by ARJUN;

User created.

SQL> grant create session to arjun;

Grant succeeded.

SQL> conn arjun/arjun; Connects with lower case password although password at time of creation was in UPPERcase.

11g Standby Database creation without a backup

11g New Feature Standby Database creation from active database without having to take a backup .

Note the command output APEXDG is created by copying the datafiles directly from an open database APEXwithout having to restore the backup from tape

RMAN> run {

allocate channel c1 type disk;

allocate auxiliary channel c2 type disk;

duplicate target database for standby from active database

dorecovernofilenamecheck;

}

11g RMAN REPAIR ADVISOR

USING RMAN REPAIR ADVISOR:

Export ORACLE_SID=test1

sqlplus sys as sysdba

Shutdown immediate.

Go to c:\app\bb16872\test1 and rename SYSTEM01.DBF to SYSTEM01.old

Next try to startup databasewill fail with error that SYSTEM01.DBF is missing.

Y:\>rman target /

Recovery Manager: Release 11.1.0.6.0 Production on Wed Apr 30 10:51:03 2008


connected to target database: TEST1 (DBID=1030433487, not open)

RMAN> list failure;using target database control file instead of recovery catalog


6/42

List of Database Failures=========================

Failure ID Priority Status Time Detected Summary - - -122 CRITICAL OPEN 30-APR- 08 System datafile 1: C: \APP\BB16872\TEST1\ SYSTEM01.DBF is missing

RMAN> list failure detail;


Failure ID Priority Status Time Detected Summary - - -122 CRITICAL OPEN 30-APR-08System datafile 1: C: \APP\BB16872\TEST1\ SYSTEM01.DBF is missing Impact: Database cannot be opened

RMAN>

RMAN> advise failure;List of Database Failures=========================

Failure ID Priority Status Time Detected Summary - - -122 CRITICAL OPEN 30-APR-08System dataf ile 1: C: \APP\BB16872\TEST1\ SYSTEM01.DBF is missing Impact: Database cannot be opened

analyzing automatic repair options; this may take some timeallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=153 device type=DISKanalyzing automatic repair options complete

Mandatory Manual Actions========================no manual actions available

Optional Manual Actions=======================1. If file C:\APP\BB16872\TEST1\SYSTEM01.DBF was unintentionally renamed ormoved, restore it

Automated Repair Options========================Option Repair Description 1 Restore and recover datafile 1Strategy: The repair includes complete media recovery with no data lossRepair script: c:\app\bb16872\diag\rdbms\test1\test1\hm\reco_971996631.hm

RMAN> repair failure preview;

Strategy: The repair includes complete media recovery with no data lossRepair script: c:\app\bb16872\diag\rdbms\test1\test1\hm\reco_971996631.hm

contents of repair script:# restore and recover datafilerestore datafile 1;

recover datafile 1;RMAN> repair failure noprompt;Strategy: The repair includes complete media recovery with no data lossRepair script: c:\app\bb16872\diag\rdbms\test1\test1\hm\reco_971996631.hm


7/42

contents of repair script:# restore and recover datafilerestore datafile 1;recover datafile 1;

executing repair script

Starting restore at 30-APR-08using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setchannel ORA_DISK_1: restoring datafile 00001 to C:\APP\BB16872\TEST1\SYSTEM01.DBFchannel ORA_DISK_1: reading from backup piece C:\APP\BB16872\BACKUP\1_TEST1_653395264channel ORA_DISK_1: piece handle=C:\APP\BB16872\BACKUP\1_TEST1_653395264 tag=TAG20080430T104104channel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:01:45Finished restore at 30-APR-08

Starting recover at 30-APR-08using channel ORA_DISK_1

starting media recoverymedia recovery complete, elapsed time: 00:00:01

Finished recover at 30-APR-08repair failure complete

Do you want to open the database (enter YES or NO)? YESdatabase opened.

In case of loss of controlfile. Follow same steps above remove controlfile and then try to startup.

RMAN> repair failure noprompt;

Strategy: The repair includes complete media recovery with no data lossRepair script: c:\app\bb16872\diag\rdbms\test1\test1\hm\reco_3514023340.hm

contents of repair script:# restore control file using multiplexed copyrestore control file from C: \APP\BB16872\TEST1\ CONTROL03.CTL; sql alter database mount; executing repair script

Starting restore at 30-APR-08using channel ORA_DISK_1

channel ORA_DISK_1: copied control file copyoutput file name=C:\APP\BB16872\TEST1\CONTROL01.CTLoutput file name=C:\APP\BB16872\TEST1\CONTROL02.CTLoutput file name=C:\APP\BB16872\TEST1\CONTROL03.CTLFinished restore at 30-APR-08

sql statement: alter database mountreleased channel: ORA_DISK_1repair failure complete

database openedRMAN> exit

Recovery Manager complete.


8/42

Y:\>sqlplus sys as sysdba

SQL*Plus: Release 11.1.0.6.0 Production on Wed Apr 30 11:17:04 2008


Enter password:

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select file_name from dba_Data_Files;FILE_NAME

C:\APP\BB16872\TEST1\SYSTEM01.DBFC:\APP\BB16872\TEST1\SYSAUX01.DBFC:\APP\BB16872\TEST1\UNDOTBS01.DBFC:\APP\BB16872\TEST1\USERS01.DBF

11g RAC Transparent Application Failover (TAF)

This example illustrates Transparent Application Failover (TAF) in a two node AIX 11g RAC configuration.

The database name is racdb and the two instances are racdb1 and racdb2.

Node 1 middba1 which hosts instance racdb1.Node2 middba2 which hosts instance racdb2

We have defined two services racdb1 and racdb2. For service racdb1 the preferred instance is racdb1 andfor service racdb2 the preferred instance is racdb2.

The following srvctl commands illustrates the same.

middba1:/u01/oracle/dump> srvctl status service -d racdb -s racdb1 Service racdb1 is running on instance(s) racdb1

middba1:/u01/oracle/dump> srvctl config service -d racdb -s racdb1 racdb1 PREF: racdb1 AVAIL: racdb2

middba1:/u01/oracle/dump> srvctl status service -d racdb -s racdb2 Service racdb2 is running on instance(s) racdb2

middba1:/u01/oracle/dump> srvctl config service -d racdb -s racdb2 racdb2 PREF: racdb2 AVAIL: racdb1

From a SQL*PLUS client, we establish a session as user SYSTEM using the service racdb1. Note the machine namewhere this service is running from.

testdb:/u01/oracle> sqlplus system/xxx@racdb1

SQL*Plus: Release 11.1.0.6.0 - Production on Fri Aug 14 10:37:03 2009


Connected to:Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit ProductionWith the Partitioning, Real Application Clusters and Real Application Testing options

SQL> select host_name from v$instance;

HOST_NAME----------------------------------------------------------------middba1


9/42

We then execute a long running SELECT statement

SQL> select * from sh.sales;

While this statement is running we reboot the node middba1 where this service is currently connected to.

We will note that the screen will momentarily freeze while the service gets relocated to the other functional noderacdb2 and then the query continues from where it got interrupted. The session will not get disconnected so for theend user it is transparent from which instance the query is being serviced.

If we check the status of the cluster resources, we will find that the VIP from node middba1 has now relocated tomiddba2. Other resources that were originally running from node middba1 are now offline.

middba2:/u01/oracle> crs_stat -tName Type Target State Host------------------------------------------------------------ora....SM1.asm application ONLINE OFFLINEora....A1.lsnr application ONLINE OFFLINEora....ba1.gsd application ONLINE OFFLINEora....ba1.ons application ONLINE OFFLINEora....ba1.vip application ONLINE ONLINE middba2 ora....SM2.asm application ONLINE ONLINE middba2ora....A2.lsnr application ONLINE ONLINE middba2ora....ba2.gsd application ONLINE ONLINE middba2ora....ba2.ons application ONLINE ONLINE middba2ora....ba2.vip application ONLINE ONLINE middba2 ora.racdb.db application ONLINE ONLINE middba2ora....cdb1.cs application ONLINE ONLINE middba2ora....b1.inst application ONLINE OFFLINEora....db1.srv application ONLINE ONLINE middba2ora....cdb2.cs application ONLINE ONLINE middba2ora....b2.inst application ONLINE ONLINE middba2ora....db2.srv application ONLINE ONLINE middba2

If we check the status of the service, we will find that the service racdb1 is now running on node middba2 connectingto the instance racdb2.

middba2:/u01/oracle> srvctl status service -d racdb -s racdb1 Service racdb1 is running on instance(s) racdb2

Once the node middba1 which was rebooted finally does come online, we will find that the VIP will relocateto its original node middba1 once the cluster services are brought online after the reboot.

middba2:/u01/oracle> crs_stat -tName Type Target State Host------------------------------------------------------------ora....SM1.asm application ONLINE ONLINE middba1ora....A1.lsnr application ONLINE ONLINE middba1ora....ba1.gsd application ONLINE ONLINE middba1ora....ba1.ons application ONLINE ONLINE middba1ora....ba1.vip application ONLINE ONLINE middba1 ora....SM2.asm application ONLINE ONLINE middba2ora....A2.lsnr application ONLINE ONLINE middba2ora....ba2.gsd application ONLINE ONLINE middba2ora....ba2.ons application ONLINE ONLINE middba2ora....ba2.vip application ONLINE ONLINE middba2 ora.racdb.db application ONLINE ONLINE middba2ora....cdb1.cs application ONLINE ONLINE middba2ora....b1.inst application ONLINE ONLINE middba1ora....db1.srv application ONLINE ONLINE middba2ora....cdb2.cs application ONLINE ONLINE middba2ora....b2.inst application ONLINE ONLINE middba2ora....db2.srv application ONLINE ONLINE middba2


10/42

Note however, that the service racdb1 will continue running on node middba2 (instance racdb2)even though theoriginal node which it was running on middba1 has been brought online.

We will have to relocate the service back to its original node using the srvctl command

middba1:/u01/oracle> srvctl relocate service -d racdb -s racdb1 -i racdb2 -t racdb1

Oracle 11g Advanced Compression

Although table compression was introduced in Oracle 9i and 10g, it was aimed more at bulk load operations for datawarehouse environments. The overhead associated with compression and uncompression made it unsuitable forOLTP type shops.

In Oracle 11g, Advanced Compression (which is an additional licensed feature) includes OLTP compressioncapabilities as well as compression of unstructured data like images and text with Secure Files and also directcompression of the export dump file generated by Data Pump which is also directly read without any uncompresswhile doing an import.

The compression feature has been greatly enhanced so as to remove any overhead associated with dealing withcompressed data while performing any DML activities.The COMPRESS FOR ALL OPERATIONS keyword needs tobe used for enabling OLTP data compression.

While Oracle does claim compression ratios of 3:1, we were able to see clearly 2:1 ratios in terms of storagereductions and no real performance overhead while performing deletes and inserts on compressed data as shown inthe example below.

So this feature could provide us both Storage as well as Performance gains due to the reduced number of blockswhich will need to be read while performing I/O.

UNCOMPRESSED DATA

SQL> create table myobjects2 as select * from all_objects;

Table created.

SQL> select sum(bytes)/1048576 from user_segments where segment_name=MYOBJECTS;

SUM(BYTES)/1048576 21

SQL> insert into myobjects2 select * from all_objects;

56261 rows created.

Elapsed: 00:00: 09.39

SQL> select count(*) from myobjects where object_type=TABLE;

COUNT(*) -444

Elapsed: 00:00: 00.66

SQL> delete myobjects;

168780 rows deleted.

Elapsed: 00:00: 12.22

COMPRESSED DATA


11/42

SQL> create table myobjects_compress2 compress for all operations 3 as select * from all_objects;

SQL> select sum(bytes)/1048576 from user_segments where segment_name=MYOBJECTS_COMPRESS;

SUM(BYTES)/1048576 9

SQL> insert into myobjects_compress2 select * from all_objects;

56261 rows created.

Elapsed: 00:00: 09.08

SQL> select count(*) from myobjects_compress where object_type=TABLE;

COUNT(*) -444

Elapsed: 00:00: 00.21

SQL> delete myobjects_compress;

168783 rows deleted.

Elapsed: 00:00: 10.96

Compression of Data Pump Exports

While doing an export, we can use the parameter COMPRESSION=ALL (other options are DATA_ONLY orMETADATA) and we see that the size of the dumpfile has reduced very significantly while using this compressionfeature.

The uncompressed dumpfile was 113 MB while the compressed dumpfile was just 13 MB. This feature can be veryuseful when we need to take a large export on disk but are limited by disk space. The earlier methods required us touse a pipe while performing an export, but we had to then uncompress the file while performing the import as wellwhich is a very costly operation in terms of time and resources.

$ expdp directory=data_file_dir dumpfile=uncompress.dmp schemas=SH compression=ALL

middba1:/u01/oracle/product/11.1.0/db/demo/schema/sales_history> ls -l *.dmp-rw-rw- 1 oracle dba 113610752 Sep 3 12:25 uncompress.dmp-rw-rw- 1 oracle dba 13426688 Sep 3 12:21 compress.dmp

ASMCMD examples

asmcmd is a command line tool that we can use to manage Oracle ASM instances, ASM disk groups, files anddierctories, templates very much as in the same way we would do while working with say a UNIX file system.

ASMCMD can be launched in interactive or non-interactive modes and we need to first ensure that our environmentpoints to the Grid Infrastructure Home as in 11g R2, ASM is not part of the standard database software installation.

Also chec that the ORACLE_SID points to the ASM instance which should be running.

Let us have a quick look at some of the useful command options which are now available with 11g Release 2.

lsct : Lists information about current Oracle ASM clients from the V$ASM_CLIENT view.

ASMCMD> lsctDB_Name Status Software_Version Compatible_version Instance_Name Disk_Group+ASM CONNECTED 11.2.0.1.0 11.2.0.1.0 +ASM DATAtestdb CONNECTED 11.2.0.1.0 11.2.0.0.0 testdb DATA


12/42

cp : We can copy files from ASM to OS as wel as from OS to ASM disk groups

ASMCMD> cp EXAMPLE.265.697907183 /tmp/example01.bakcopying +DATA/TESTDB/DATAFILE/EXAMPLE.265.697907183 -> /tmp/example01.bak

ASMCMD> cp /tmp/example01.bak +DATA/TESTDB/DATAFILE/example01.bakcopying /tmp/example01.bak -> +DATA/TESTDB/DATAFILE/example01.bak

ASMCMD> ls

EXAMPLE.265.697907183SYSAUX.257.697907095SYSTEM.256.697907095UNDOTBS1.258.697907095USERS.259.697907095example01.bak

du : Total space in MB used by files particular directory can also be specified

ASMCMD> pwd+DATA/TESTDB/DATAFILE

ASMCMD> du

Used_MB Mirror_used_MB1574 1574

ASMCMD> du +DATA/TESTDB/ONLINELOGUsed_MB Mirror_used_MB

153 153

find : we can use the wildcard or can specify a particular file type by using the type clause

ASMCMD> find --type ONLINELOG +DATA *+DATA/TESTDB/ONLINELOG/group_1.261.697907177+DATA/TESTDB/ONLINELOG/group_2.262.697907179+DATA/TESTDB/ONLINELOG/group_3.263.697907179

ASMCMD> find +DATA example*+DATA/ASM/DATAFILE/example01.bak.267.698929915+DATA/TESTDB/DATAFILE/EXAMPLE.265.697907183+DATA/TESTDB/DATAFILE/example01.bak

ls : list the contents of an ASM Disk Group directory as well as attributes of files located in the directory

ASMCMD> ls -sBlock_Size Blocks Bytes Space Name

8192 12801 104865792 106954752 EXAMPLE.265.6979071838192 89601 734011392 736100352 SYSAUX.257.6979070958192 89601 734011392 736100352 SYSTEM.256.6979070958192 7681 62922752 65011712 UNDOTBS1.258.6979070958192 641 5251072 6291456 USERS.259.697907095

example01.bak => +DATA/ASM/DATAFILE/example01.bak.267.698929915

ASMCMD> ls -lType Redund Striped Time Sys NameDATAFILE UNPROT COARSE SEP 30 11:00:00 Y EXAMPLE.265.697907183DATAFILE UNPROT COARSE SEP 28 23:00:00 Y SYSAUX.257.697907095DATAFILE UNPROT COARSE SEP 29 22:00:00 Y SYSTEM.256.697907095DATAFILE UNPROT COARSE SEP 18 22:00:00 Y UNDOTBS1.258.697907095DATAFILE UNPROT COARSE SEP 18 22:00:00 Y USERS.259.697907095

N example01.bak => +DATA/ASM/DATAFILE/example01.bak.267.698929915

iostat : Uses the V$ASM_DISK_IOSTAT view to display I/O statistics of disks in mounted ASM disk groups

ASMCMD> iostat -G DATAGroup_Name Dsk_Name Reads WritesDATA DATA_0000 25448671744 19818926592


13/42

ASMCMD> iostat -tGroup_Name Dsk_Name Reads Writes Read_Time Write_TimeDATA DATA_0000 25450195456 19819686912 6491.434444 8042.604156

lsdg : Uses V$ASM_DISKGROUP_STAT view to list information about a particular disk group

ASMCMD> lsdg DATAState Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Name

MOUNTED EXTERN N 512 4096 1048576 51200 49375 0 49375 DATA/

ASMCMD> lsdsk -t -G DATACreate_Date Mount_Date Repair_Timer Path18-SEP-09 18-SEP-09 0 /dev/raw/raw1

lsattr : List attributes of a disk group

ASMCMD> lsattr -l -G DATAName Valueaccess_control.enabled FALSEaccess_control.umask 066au_size 1048576

cell.smart_scan_capable FALSEcompatible.asm 11.2.0.0.0compatible.rdbms 10.1.0.0.0disk_repair_time 3.6hsector_size 512

Use the setattr command to change an attribute

ASMCMD> setattr -G data compatible.rdbms 11.2.0.0.0 ASMCMD> lsattr -l -G DATAName Valueaccess_control.enabled FALSEaccess_control.umask 066au_size 1048576cell.smart_scan_capable FALSEcompatible.asm 11.2.0.0.0compatible.rdbms 11.2.0.0.0disk_repair_time 3.6hsector_size 512

11g Release 2 ACFS (Automatic Storage Management Cluster File System)

The following are some of the new ASM related features introduced in 11g R2:

GridInfrastructure Home.

stry can no longer be installed on raw devices. Theycan now be installed on ASM Disk Groups

-01031 error as shown below[oracle@redhat346 stage]$ sqlplus sys/xxx as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Oct 16 09:10:55 2009


Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit ProductionWith the Automatic Storage Management option

SQL> shutdown immediate;ORA-01031: insufficient privileges


14/42

storage of files like Oracle binaries and other application files related to text,video, audio etc

The example below will show how we can configure and mount a ACFS on Red HatLinux with 11g R2.

Note: ASM operations like creating and adding disk groups etc which were earlier performed via DBCA will now beperformed via the GUI ASM Configuration Assistant which needs to be launched via the asmca command from theGrid Infrastructure Home.

We first create a disk group called ASMCFS and then create a volume called ASMCFS_VOL1 of 25 GB using the diskgroup that we just created.

We will then use a mount point /u03 to mount this file system. A configuration script is created which includes thecommands to be run as root to mount this file system. After the script is run we will see the ACFS file system mountedon /u03.

/bin/mount -t acfs /dev/asm/asmcfs_vol1-44 /u03

chown oracle:dba /u03

chmod 775 /u03

[oracle@redhat346 stage]$ df -k

Filesystem 1K-blocks Used Available Use% Mounted on.........../dev/asm/asmcfs_vol1-44

26214400 89112 26125288 1% /u03


15/42


16/42


17/42


18/42

11g Release 2 Grid Infrastructure Installations

Let us have a look at the installation screenshots of 11g Release 2 on a Red Hat Linux platform. Currently the 11g R2software has only been released for the Linux platform with release dates for other platforms like Solaris and AIX setfor sometime end October to mid November.

There are quite a few new installation options available as can be seen from the screenshots below. What we areinstalling belo w is Grid Infrastructure for a stand alone server.

In 11g R2, ASM is now part of what is called the Grid Infrastructure (nothing to do with Grid Control). It is no longer anoption available in dbca and there is a command line option asmca which is launched from the Grid Infrastructurehome. The ASM instance will be running from the Grid Home and not the database Oracle Home.


19/42

Further, the Grid Infrastructure can be installed for a stand alone server or for a cluster deployment. The ASM andClusterware are instaled in the same home directory and it should be noted that now in 11g R2, the clusterware fileslike the OCR and the Voting Disk can be located on ASM storage and raw devices is no longer supported.

Also, now there is separation between SYSASM and SYSDBA and if you want to connect to the ASM instance youneed to do it as SYSASM. It is recommended to also create a separate group in addition to the DBA group specific for

ASM administration and make SYSASM grantees members of this group. In one of the screenshots below we will seean alert being displayed when we have selected the dba group for the ASM installation.


20/42


21/42


22/42


23/42


24/42


25/42

11g Flashback Data Archive Part One

Businesses are increasingly realizing the immense value that historical data can provide to help them understandmarket trends and customer behaviour as well. Further, many regulatory and compliance policieslike SOX and BASEL-2 mandate retention of historical data.

Until now, this unfortunately involved application rewrites,administration overheads or costly third-party softwaresolutions. Total Recall or the new Oracle 11g Flashback Data Archive feature has greatly enhanced the flashbacktechnology which was introduced in Oracle 9i to make it far more than just a tool to recover from logical corruptions

and human error. It is now an out-of-the box Information Life Cycle Management tool and provides ease ofmanagement of historical information and long term secure data tracking with minimal performance overheads.

11g Flashback Data Archive provides the automated ability to track and store all transactional changes to a table overits lifetime without having to build this intelligence into your application. Prior to 11g, Flashback technology to a largepart was based on the availability of undo data or flashback logs and both the undo data as well as flashback logs aresubject to recycling when out of space pressure exists. The UNDO tablespace in Oracle was primarily meant fortransaction consistency and not data archival.

Flashback Data Archive or Total Recall Features

apply to all tables, one or a group of tables with simple enable archive command complete protection from accidental or malicious updates and deletes

of performance and storage capture process is asynchronous background process and data in historytables is partitioned as well as compressed automatically

ent historical data is automatically purged without any human intervention

Flashback Data Archive uses a background process fbda to capture data asynchronously. It runs every 5 minuteswhich is the default as well as at more frequent intervals depending on system activity. The Primary source forhistorical data is the Undo data, but this undo data is not reused until the historical data is written first.

Getting Started

FLASHBACK ARCHIVE ADMINISTER to create and administer a flashback data archive

FLASHBACK ARCHIVE privilege on the specific flashback data archive to enable historical datatracking

ashback data archive has been created

Creating a Flashback Data Archive (lets call it FBDA)

tablespace needs to be ASSM

tional)

turned off.SQL> CREATE TABLESPACE his_data_1

2 DATAFILE '+data' SIZE 500M;

Tablespace created.

SQL> CREATE FLASHBACK ARCHIVE DEFAULT fba12 TABLESPACE his_data_13 RETENTION 2 DAY;

Flashback archive created.

SQL> SELECT FLASHBACK_ARCHIVE_NAME, to_char(CREATE_TIME,'dd-mon-yyyy') Created,2 RETENTION_IN_DAYS,STATUS FROM DBA_FLASHBACK_ARCHIVE;

FLASHBACK_ARCHIVE_NA CREATED RETENTION_IN_DAYS STATUS


26/42

-------------------- ----------- ----------------- -------FBA1 02-nov-2009 2 DEFAULTSQL> GRANT FLASHBACK ARCHIVE ON fba1 TO scott;

Grant succeeded.

SQL> ALTER TABLE mysales FLASHBACK ARCHIVE fba1;

Table altered.

SQL> CREATE TABLE2 EMPSAL_HIS3 (EMPNO number,4 ENAME VARCHAR2(10),5 SAL NUMBER,6 FLASHBACK ARCHIVE;

Table created.

SQL> select TABLE_NAME,FLASHBACK_ARCHIVE_NAME,ARCHIVE_TABLE_NAME,STATUS2 from USER_FLASHBACK_ARCHIVE_tables;

TABLE_NAME FLASHBACK_ARCHIVE_NA ARCHIVE_TABLE_NAME STATUS----------- -------------------- -------------------- ------------MYSALES FBA1 SYS_FBA_HIST_77429 ENABLEDEMPSAL_HIS FBA1 SYS_FBA_HIST_77419 ENABLED

Note: for every table where Flashback Archive is enabled, corresponding internal history tables are created inthe flashback archive tablespace as shown below. These tables are automatically partitioned and compressedas well

SQL> select object_id from dba_objects where object_name=DEPT';

OBJECT_ID----------

73201

SQL> select table_name,tablespace_name from user_tables;

TABLE_NAME TABLESPACE_NAME------------------------------ ------------------------------DEPT USERSSYS_FBA_DDL_COLMAP_73201 HIS_DATA_1SYS_FBA_TCRV_73201 HIS_DATA_1SYS_FBA_HIST_73201

SQL> desc SYS_FBA_HIST_73201Name Null? Type----------------------------------------- -------- ----------------------------RID VARCHAR2(4000)STARTSCN NUMBERENDSCN NUMBERXID RAW(8)OPERATION VARCHAR2(1)

EMPNO NUMBER(4)ENAME VARCHAR2(10)MGR NUMBER(10)

DEPTNO NUMBER(2)JOB VARCHAR2(20)

SQL> SELECT TABLE_NAME,PARTITION_NAME,COMPRESSION from USER_TAB_PARTITIONS;

TABLE_NAME PARTITION_NAME COMPRESS------------------------------ ------------------------------ --------SYS_FBA_HIST_77429 HIGH_PART ENABLED


27/42

SYS_FBA_HIST_77419 HIGH_PART ENABLED

11g RMAN Virtual Private Catalog

In Oracle 11g, we can grant restricted access to the RMAN catalog to some users so that they can only access alimited set of databases that are registered in the RMAN catalog.

This is done by creating a Virtual Private Catalog which in turn will grant a particular user read/write access to onlythat users RMAN metadata. We can in this way create a number of multiple recovery catalog users each seeing only

having access to a limited set of databases while the base recovery catalog owner has access to the entire metadata.

For example, in the RMAN catalog owned by user RMAN11D, there a a number of databases registered, but wewould like to restrict access to the APEX database to a single user RMAN_APEX.

So we need to first create a user in the database which houses the base RMAN catalog, grant that user theRECOVERY_CATALOG_OWNER role and then the catalog for database .. privilege.

That user will then create a virtual catalog and when he connects to that catalog, we will see that he can only accessthe one database which he has been granted access for which is the APEX database.

The original RMAN catalog owner is RMAN11D note the databases which are currently registered:

SQL> select name from rc_database;

NAME--------SID1DSID1SSID1A

APEXSID1T

Create the Virtual Catalog User RMAN_APEX

SQL> create user rman_apex identified by rman_apex2 default tablespace RMAN11D3 temporary tablespace temp4 quota unlimited on rman11d;

User created.

SQL> grant recovery_catalog_owner to rman_apex;

Grant succeeded.

Connect to catalog as catalog owner and grant permissions on the one database APEX

[PROD] emrep:/u01/oracle > rman target / catalog rman11d/d11rman@rcatd

Recovery Manager: Release 11.1.0.6.0 - Production on Tue Dec 1 10:34:33 2009


connected to target database: EMREP (DBID=3892233981)connected to recovery catalog database

RMAN> grant catalog for database apex to rman_apex;

Grant succeeded.

Connect now as the user RMAN_APEX and create the Virtual Private Catalog[PROD] emrep:/u01/oracle > rman target / catalog rman_apex/rman_apex@rcatd

Recovery Manager: Release 11.1.0.6.0 - Production on Tue Dec 1 10:39:56 2009


28/42


connected to target database: EMREP (DBID=3892233981)connected to recovery catalog database

RMAN> create virtual catalog;

found eligible base catalog owned by RMAN11Dcreated virtual catalog against base catalog owned by RMAN11D

If we connect as the original RMAN catalog owner we can see all the registered databases

[PROD] emrep:/u01/oracle > rman target / catalog rman11d/d11rman@rcatd

RMAN> list incarnation;

List of Database IncarnationsDB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time------- ------- -------- ---------------- --- ---------- ----------72209 72226 APEX 1312143933 PARENT 1 07-NOV-0772209 72210 APEX 1312143933 CURRENT 15653492933 04-AUG-0917258 17259 SID1T 2951173163 CURRENT 1 08-OCT-09

761 762 SID1S 3097605603 CURRENT 1 08-SEP-092139 2140 SID1A 3639578917 CURRENT 1 16-SEP-091 2 SID1D 3743031640 CURRENT 1 27-AUG-09

Note that only one database is registered in this catalog when we connect as RMAN_APEX

rman target / catalog rman_apex/rman_apex@rcatd

RMAN> list incarnation;

List of Database IncarnationsDB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time------- ------- -------- ---------------- --- ---------- ----------72209 72226 APEX 1312143933 PARENT 1 07-NOV-0772209 72210 APEX 1312143933 CURRENT 15653492933 04-AUG-09

11g Standby database creation without any RMAN backups

Quick steps to set up a 11g Standby database with Active Data Guard using the Active Duplication feature available in11g where we can create a standby database without having to take a backup on the primary database. Datafiles arecopied over the network.

Primary machine OATU036Standby machine DROU036

Database Name SID1O

TNS alias for Primary sid1o_fcTNS alias for standby sid1o_js

Enable force logging on the Primary database

SQL> alter database force logging;

Database altered.

Create the Standby log files on the Primary database

Copy the password file from the $ORACLE_HOME/dbs directory on primary server to $ORACLE_HOME/dbson the standby server

Update listener.ora on Standby machine


29/42

(SID_DESC=(GLOBAL_DBNAME=sid1o_js)(ORACLE_HOME=/u01/oracle/product/11.1.0/db_1)(SID_NAME=sid1o))

Stop and Restart the listener on the standby site

Update tnsnames.ora on Standby as well as Primary site with the alias sid1o_js and sid1o_fc

sid1o_js =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = drou036)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = sid1o_js )))

SID1O_FC =(DESCRIPTION =

(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = oatu036)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = sid1o.bankwest.com)))

Create an init.ora on the Standby machine with just a single line which is the db_name parameter

sid1o:/u01/oracle/product/11.1.0/db_1/dbs> cat initsid1o.oradb_name=sid1o

Startup the Standby instance in nomount state

SQL> startup nomount;ORACLE instance started.

Total System Global Area 217157632 bytesFixed Size 2152328 bytesVariable Size 159385720 bytesDatabase Buffers 50331648 bytesRedo Buffers 5287936 bytes

On the Primary launch RMAN and establish an auxiliary connection to the standby instance

sid1o:/u01/oracle> rman target / auxiliary sys/xxx@sid1o_js

Recovery Manager: Release 11.1.0.7.0 Production on Fri Dec 4 10:28:51 2009


connected to target database: SID1O (DBID=2860177231)connected to auxiliary database: SID1O (not mounted)

Run the command to create the Standby Database

D U P L IC AT E TA R G E T D ATA B A S E

F O R S TA N D B YF R O M A C T IV E D ATA B A S E

NOFILENAMECHECK

DORECOVER

SPFILE

SET DB_UNIQUE_NAME=sid1o_js


30/42

SET LOG_ARCHIVE_DEST_2=service=sid1o_fc LGWR S YNC REGISTERVALID_FOR=(online_logfile,primary_role) SET FAL_SERVER=sid1o_fc SET FAL_CLIENT=sid1o_js SET CONTROL_FILES=/u02/oradata/sid1o/control01.ctl,'/u03/oradata/sid1o/control02.ctl;

Change the init.ora parameters related to redo transport and redo apply

On standby and primary

SQL> alter system set standby_file_management=AUTO scope=both;

System altered.

On Primary

SQL> alter system set fal_server=sid1o_js scope=both;

System altered.

SQL> alter system set fal_client=sid1o_fc scope=both;

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_2=SERVICE=sid1o_js LGWR SYNCVALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=sid1o_js scope=both; 2

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_1=LOCATION=USE_DB_RECOVERY_FILE_DESTVALID_ FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=sid1o;

System altered.

Shutdown the Standby and enable managed recovery (active standby mode)

SQL> shutdown immediate;ORA-01109: database not open

Database dismounted.ORACLE instance shut down.SQL> startupORACLE instance started.

Total System Global Area 1043886080 bytesFixed Size 2160352 bytes

Variable Size 775948576 bytesDatabase Buffers 260046848 bytesRedo Buffers 5730304 bytesDatabase mounted.Database opened.

SQL> recover managed standby database using current logfile disconnect;Media recovery complete.SQL>

Check if the MRP process is running

SQL> !ps -ef |grep mrp

oracle 446526 1 0 10:59:01 0:00 ora_mrp0_sid1o

TEST

On Primary


31/42

SQL> conn system/xxxConnected.SQL> create table test_dr2 (mydate date);

Table created.

SQL> insert into test_dr2 values

3 (sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

On Standby

SQL> conn system/xxxConnected.SQL> select to_char(mydate,DD -MON-YY HH24:MI:SS) from test_dr;

TO_CHAR(MYDATE,DD -MON-YYHH 04-DEC-09 11:15:49

Create Standby Database using 11g DUPLICATE FROM ACTIVE DATABASE

Purpose

This note explains the procedure of creating a Physical Standby database using 11g RMAN DUPLICATE FROM ACTIVE DATABASE feature which is now available in 11g Release 1 onwards. This enables us to create a physicalstandby database without having to take a backup of the primary database as a prerequisite step.

Environment

Primary Database DB_UNIQUE_NAME: genoa1_jsStandby Database DB_UNIQUE_NAME: genoa1_fc

ORACLE_SID: genoa1

Primary hostname: oatu037

Standby hostname: drou037Oracle software version: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 64bit

Enable Force Logging on the Primary database

SQL> alter database force logging;

Database altered.

11g Data Guard Broker DGMGRL Configuration quick steps

This note describes the commands used to create a Data Guard broker configuration using the command line dgmgrlinterface. This can also be done via the Enterprise Manager Grid Control GUI as well and the assumption is that thereis a Physical Standby database in place and redo transport and redo apply have already been configured and thatboth the Primary and Standby database are in sync.

Primary Database: genoa1_js


32/42

Standby Database: genoa1_fc

On both Primary as well as Standby database start the Data Guard Broker process

SQL> ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH;

System altered.

Edit the listener.ora on both nodes to add a static entry for DGMGRL

This is to prevent the ORA-12154 error which can be observed on startup of the standby database after performing aswitchover.

Ensure that the GLOBAL_DBNAME is set to db_unique_name_DGMGRL.db_domain

SID_LIST_LISTENER =(SID_LIST =

(SID_DESC =(GLOBAL_DBNAME = genoa1_js_dgmgrl)(ORACLE_HOME = /u01/oracle/product/11.1.0/db_1)(SID_NAME = genoa1))

)

Create the configuration

genoa1:/u01/oracle> dgmgrlDGMGRL for IBM/AIX RISC System/6000: Version 11.1.0.7.0 - 64bit Production


Welcome to DGMGRL, type "help" for information.DGMGRL> connect sys/xxxx Connected.DGMGRL> CREATE CONFIGURATION 'GenesisDR' > AS > PRIMARY DATABASE IS 'genoa1_js' > CONNECT IDENTIFIER IS 'genoa1_js' > ;Configuration "GenesisDR" created with primary database "genoa1_js"

Add the Standby database to the configuration

DGMGRL> ADD DATABASE > 'genoa1_fc' > AS > CONNECT IDENTIFIER IS 'genoa1_fc' > ;Database "genoa1_fc" added

DGMGRL> SHOW CONFIGURATION

ConfigurationName: GenesisDREnabled: NOProtection Mode: MaxAvailabilityDatabases:

genoa1_js - Primary databasegenoa1_fc - Physical standby database


Current status for "GenesisDR":DISABLED

Enable the configuration


33/42

DGMGRL> ENABLE CONFIGURATION Enabled.

DGMGRL> SHOW CONFIGURATION

ConfigurationName: GenesisDREnabled: YESProtection Mode: MaxAvailability

Databases:genoa1_js - Primary databasegenoa1_fc - Physical standby database


Current status for "GenesisDR":SUCCESS

View the Standby and Primary database properties

DGMGRL> show database genoa1_js

DatabaseName: genoa1_jsRole: PRIMARYEnabled: YESIntended State: TRANSPORT-ONInstance(s):

genoa1

Current status for "genoa1_js":SUCCESS

DGMGRL> show database genoa1_fc

DatabaseName: genoa1_fcRole: PHYSICAL STANDBYEnabled: YESIntended State: APPLY-ONInstance(s):

genoa1

Current status for "genoa1_fc":SUCCESS

DGMGRL> show database verbose genoa1_fc

DatabaseName: genoa1_fcRole: PHYSICAL STANDBYEnabled: YESIntended State: APPLY-ONInstance(s):

genoa1

Properties:DGConnectIdentifier = 'genoa1_fc'ObserverConnectIdentifier = ''LogXptMode = 'SYNC'DelayMins = '0'Binding = 'OPTIONAL'MaxFailure = '0'MaxConnections = '1'


34/42

ReopenSecs = '300'NetTimeout = '30'RedoCompression = 'DISABLE'LogShipping = 'ON'PreferredApplyInstance = ''

ApplyInstanceTimeout = '0' ApplyParallel = 'AUTO'StandbyFileManagement = 'AUTO'

ArchiveLagTarget = '0'

LogArchiveMaxProcesses = '4'LogArchiveMinSucceedDest = '1'DbFileNameConvert = ''LogFileNameConvert = ''FastStartFailoverTarget = ''StatusReport = '(monitor)'InconsistentProperties = '(monitor)'InconsistentLogXptProps = '(monitor)'SendQEntries = '(monitor)'LogXptStatus = '(monitor)'RecvQEntries = '(monitor)'HostName = 'drou037'SidName = 'genoa1'

StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=drou037)

(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=genoa1_fc_DGMGRL)(INSTANCE_NAME=genoa1)(SERVER=DEDICATED)))'

StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST'

AlternateLocation = ''

LogArchiveTrace = '0'

LogArchiveFormat = '%t_%s_%r.dbf'

LatestLog = '(monitor)'

TopWaitEvents = '(monitor)'

Current status for "genoa1_fc":

SUCCESS

Change the properties of a configured databaase

DGMGRL> EDIT DATABASE 'genoa1_js' SET PROPERTY 'LogXptMode'='SYNC'; Property "LogXptMode" updated

Performing a switchover

Note: In this case, currently the Primary Database is genoa1_fc and the Standby database is genoa1_js.

DGMGRL> switchover to 'genoa1_js' Performing switchover NOW, please wait...New primary database "genoa1_js" is opening...Operation requires shutdown of instance "genoa1" on database "genoa1_fc"Shutting down instance "genoa1"...ORA-01109: database not open

Database dismounted.ORACLE instance shut down.Operation requires startup of instance "genoa1" on database "genoa1_fc"Starting instance "genoa1"...ORACLE instance started.


35/42

Database mounted.Switchover succeeded, new primary is "genoa1_js"

DGMGRL> SHOW CONFIGURATION;

ConfigurationName: GenesisDREnabled: YESProtection Mode: MaxAvailability

Databases:genoa1_js - Primary databasegenoa1_fc - Physical standby database


Current status for "GenesisDR":SUCCESS

Monitoring the Data Guard Broker Configuration

If we receive any error or warnings we cab obtain more information about the same ny running the commands asshown below. In this case there is no output seen because currently we are not experiencing any errors or warning.

DGMGRL> show database genoa1_js statusreport STATUS REPORT

INSTANCE_NAME SEVERITY ERROR_TEXT

DGMGRL> show database genoa1_js logxptstatus LOG TRANSPORT STATUSPRIMARY_INSTANCE_NAME STANDBY_DATABASE_NAME STATUS

genoa1 genoa1_fc

DGMGRL> show database genoa1_js InconsistentProperties INCONSISTENT PROPERTIES

INSTANCE_NAME PROPERTY_NAME MEMORY_VALUE SPFILE_VALUE BROKER_VALUE

How to clone the Oracle database software

This note describes the process of cloning an Oracle Home - in this case we are cloning a 11g Release 2 OracleHome from source machine devastator to target machine vixen.On source (devastator)ORACLE_HOME location is : /u01/app/oracle/product/11.2.0/dbhome_1

Go to directory on source machine one level higher and tar the db_home1 directory and all sub-directoriesdevastator:$ pwddevastator:$ /u01/app/oracle/product/11.2.0devastator:$ tar -cvf 11gr2.tar dbhome_1

On Target (vixen)Create the directory /u01/app/oracle/product/11.2.0 if it does not existOn Source

scp or ftp the tar file to this location on the target machine

devastator:$ scp -rp 11gr2.tar oracle@vixen:/u01/app/oracle/product/11.2.0

On targetUncompress the tar filevixen:$ cd /u01/app/oracle/product/11.2.0vixen:$ tar -xvf 11gr2.tarClone the Oracle Home using the perl script clone.pl which is located under the $ORACLE_HOME/clone/binvixen:$ cd /u01/app/oracle/product/11.2.0/dbhome_1/clone/bin


36/42


37/42

The following environment variables are set as:

ORACLE_OWNER= oracleORACLE_HOME= /u01/app/oracle/product/11.2.0/dbhome_1

Entries will be added to the /var/opt/oracle/oratab file as needed byDatabase Configuration Assistant when a database is createdFinished running generic part of root.sh script.Now product-specific root actions will be performed.Finished product-specific root actions.

We can now check that the OraInventory has been updated with the 11g Release 2 Oracle Home details.

On this machine, earlier there was only a 10g Oracle Home installed. When we check the contents of theinventory.xml file, we will see that it has information about the 11g Oracle Home 11GR2_HOME

vixen:$ cat /var/opt/oracle/oraInst.loc inventory_loc=/u01/app/oracle/oraInventoryinst_group=dba

vixen:$ cd /u01/app/oracle/oraInventory

vixen:$ lsbackup ContentsXML logs oraInstaller.properties ouiContents install.platform oraInst.loc orainstRoot.sh sessionContext.se

vixen:$ cd ContentsXML

vixen:$ lscomps.xml inventory.xml libs.xml

vixen:$ vi inventory.xml

11.2.0.1.02.1.0.6.0

Diagnosing and Repairing Failures with 11g Data Recovery Advisor

The 11g Data Recovery Advisor is part of the 11g database health checking framework and diagnoses persistent datafailures and not only presents options to repair and fix the problem but also can execute the repair and recoveryprocess at our request.

The Repair Advisor can take away lot of the stress associated with peforming backup and recovery by diagnosingwhat is wrong as well as presenting us with the syntax as well to execute the commands to restore and recover as thecase may be. Under pressure, everyone can make mistakes and it is comforting to know that there is a tool which canreally he;p the DBA.

The Data Recovery Advisor can be used via OEM Database or Grid Control or via the RMAN command line interface.

Let us look at an example of using the RMAN Data Recovery Advisor to recover from a loss of control files situationwith and without the CONTROL AUTOBACKUP option being enabled.


38/42

Note, that when there is no control file autobackup, the RMAN Repair Advisor is not able to do the full automatedrecovery for us and we use a combination of automatic and manual repair to fix the problem.

Scenario is loss of control files AUTOBACKUP is enabled

RMAN> list failure ;

using target database control file instead of recovery catalogList of Database Failures=========================

Failure ID Priority Status Time Detected Summary - - -5652 CRITICAL OPEN 18-JUN-12 Control file /u01/app/oracle/flash_recovery_area/sqlfun/control02.ctl is missing5649 CRITICAL OPEN 18-JUN-12 Control file /u01/app/oracle/oradata/sqlfun/control01.ctl is missing

RMAN> advise failure;


Failure ID Priority Status Time Detected Summary - - -5304 CRITICAL OPEN 18-JUN-12 Control file /u01/app/oracle/flash_recovery_area/sqlfun/control02.ctl is missing



Optional Manual Actions=======================no manual actions available

Automated Repair Options========================Option Repair Description 1 Use a multiplexed copy to restore control file /u01/app/oracle/flash_recovery_area/sqlfun/control02.ctlStrategy: The repair includes complete media recovery with no data lossRepair script: /u01/app/oracle/diag/rdbms/sqlfun/sqlfun/hm/reco_632546057.hm


Strategy: The repair includes complete media recovery with no data lossRepair script: /u01/app/oracle/diag/rdbms/sqlfun/sqlfun/hm/reco_632546057.hm

contents of repair script:# restore control file using multiplexed copyrestore controlfile from /u01/app/oracle/oradata/sqlfun/control01.ctl; sql alter database mount;

RMAN> repair failure;


contents of repair script:# restore control file using multiplexed copy


39/42

restore controlfile from /u01/app/oracle/oradata/sqlfun/control01.ctl; sql alter database mount;

Do you really want to execute the above repair (enter YES or NO)? YESexecuting repair script

Starting restore at 18-JUN-12using channel ORA_DISK_1

channel ORA_DISK_1: copied control file copyoutput file name=/u01/app/oracle/oradata/sqlfun/control01.ctloutput file name=/u01/app/oracle/flash_recovery_area/sqlfun/control02.ctlFinished restore at 18-JUN-12

sql statement: alter database mountreleased channel: ORA_DISK_1repair failure complete

Do you want to open the database (enter YES or NO)? YES database opened

RMAN>

RMAN> list failure;

no failures found that match specification

Scenario is loss of control files No AUTOBACKUP

RMAN> list failure ;

using target database control file instead of recovery catalogList of Database Failures=========================


RMAN> advise failure ;




Mandatory Manual Actions========================

1. If file /u01/app/oracle/flash_recovery_area/sqlfun/control02.ctl was unintentionally renamed or moved, restore it2. If file /u01/app/oracle/oradata/sqlfun/control01.ctl was unintentionally renamed or moved, restore it3. If you have a CREATE CONTROLFILE script, use it to create a new control file4. Contact Oracle Support Services if the preceding recommendations cannot be used, or if they do not fix the failuresselected for repair


40/42

Optional Manual Actions=======================1. If a standby database is available, then perform a Data Guard failover initiated from the standby

Automated Repair Options========================no automatic repair options available


RMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of repair command at 06/18/2012 11:00:06RMAN-06953: no automatic repairs were listed by ADVISE FAILURE

Find the last database backup of control file in FRA

RMAN> restore controlfile from/u01/app/oracle/flash_recovery_area/SQLFUN/autobackup/2012_06_18/o1_mf_s_786251074_7xwbl3l4_.bkp;

Starting restore at 18-JUN-12using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=125 device type=DISK

channel ORA_DISK_1: restoring control filechannel ORA_DISK_1: restore complete, elapsed time: 00:00:02output file name=/u01/app/oracle/oradata/sqlfun/control01.ctloutput file name=/u01/app/oracle/flash_recovery_area/sqlfun/control02.ctlFinished restore at 18-JUN-12

RMAN> list failure;

no failures found that match specification

RMAN> alter database mount;

database mountedreleased channel: ORA_DISK_1

RMAN> alter database open resetlogs ;

RMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of alter db command at 06/18/2012 11:36:01ORA-01194: file 1 needs more recovery to be consistentORA-01110: data file 1: /u01/app/oracle/oradata/sqlfun/system01.dbf

RMAN> list failure;


Failure ID Priority Status Time Detected Summary - - -5898 CRITICAL OPEN 18-JUN- 12 System datafile 1: /u01/app/oracle/oradata/sqlfun/system01.dbf needs mediarecovery5895 CRITICAL OPEN 18-JUN-12 Control file needs media recovery

8 HIGH OPEN 18-JUN-12 One or more non-system datafiles need media recoveryRMAN> advise failure ;

Starting implicit crosscheck backup at 18-JUN-12allocated channel: ORA_DISK_1


41/42

channel ORA_DISK_1: SID=125 device type=DISKCrosschecked 3 objectsFinished implicit crosscheck backup at 18-JUN-12

Starting implicit crosscheck copy at 18-JUN-12using channel ORA_DISK_1Crosschecked 5 objectsFinished implicit crosscheck copy at 18-JUN-12

searching for all files in the recovery areacataloging files cataloging done

List of Cataloged Files=======================File Name:/u01/app/oracle/flash_recovery_area/SQLFUN/backupset/2012_06_18/o1_mf_ncsnf_TAG20120618T112825_7xx83b9m_.bkpFile Name:/u01/app/oracle/flash_recovery_area/SQLFUN/autobackup.old/2012_06_18/o1_mf_s_786277031_7xx3x7pw_.bkp.oldFile Name:/u01/app/oracle/flash_recovery_area/SQLFUN/autobackup.old/2012_06_18/o1_mf_s_786251074_7xwbl3l4_.bkp

File Name:/u01/app/oracle/flash_recovery_area/SQLFUN/autobackup.old/2012_06_18/o1_mf_s_786281256_7xx8184v_.bkpFile Name:/u01/app/oracle/flash_recovery_area/SQLFUN/autobackup.old/2012_06_18/o1_mf_s_786279412_7xx67nlv_.bkp


Failure ID Priority Status Time Detected Summary - - -5898 CRITICAL OPEN 18-JUN- 12 System datafile 1: /u01/app/oracle/oradata/sqlfun/system01.dbf needs mediarecovery5895 CRITICAL OPEN 18-JUN-12 Control file needs media recovery8 HIGH OPEN 18-JUN-12 One or more non-system datafiles need media recovery

analyzing automatic repair options; this may take some timeusing channel ORA_DISK_1analyzing automatic repair options complete


Optional Manual Actions=======================1. If you have the correct version of the control file, then shutdown the database and replace the old control file2. If you restored the wrong version of data file /u01/app/oracle/oradata/sqlfun/system01.dbf, then replace it with thecorrect one3. If you restored the wrong version of data file /u01/app/oracle/oradata/sqlfun/sysaux01.dbf, then replace it with thecorrect one4. If you restored the wrong version of data file /u01/app/oracle/oradata/sqlfun/undotbs01.dbf, then replace it with thecorrect one5. If you restored the wrong version of data file /u01/app/oracle/oradata/sqlfun/users01.dbf, then replace it with thecorrect one6. If you restored the wrong version of data file /u01/app/oracle/oradata/sqlfun/threatened_fauna_data.dbf, thenreplace it with the correct one

Automated Repair Options========================Option Repair Description 1 Recover databaseStrategy: The repair includes complete media recovery with no data lossRepair script: /u01/app/oracle/diag/rdbms/sqlfun/sqlfun/hm/reco_2266295139.hm


42/42

RMAN> repair failure preview ;


contents of repair script:# recover databaserecover database;alter database open resetlogs;

RMAN> repair failure noprompt;


contents of repair script:# recover databaserecover database;alter database open resetlogs;executing repair script

Starting recover at 18-JUN-12using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/oradata/sqlfun/redo01.logarchived log file name=/u01/app/oracle/oradata/sqlfun/redo01.log thread=1 sequence=1media recovery complete, elapsed time: 00:00:01Finished recover at 18-JUN-12

database openedrepair failure complete

RMAN>

11g helpful guide

Documents