Dataguard - Performing RMAN backup from standby site and test recovery from standby backup

I wanted to share a small piece of work I had done for one of my earlier employers who had asked me to check the viability of taking RMAN backups on a standby site and test a recovery scenario of a full database wipe-out scenario.

PRIMARY - STANDBY SETUP

==============

1)

Primary Database : orioles

 Server Name: patty

Backup location at Primary :  /mnt/xserve-raid2/u05/bkp (same as in standby)

2)

Standby Database: orioles

Server Name : selma

Backup location at Standby :  /mnt/xserve-raid2/u05/bkp (same as in primary)

2a) Catalog instance is rmancat and the catalog owner is rman_owner

3)

Initorioles.ora (Primary) at patty

I have only pasted the relevant standby parameters

####################################

#Standby Parameters

####################################

log_archive_dest_2='SERVICE=orioles_standby ARCH OPTIONAL'

standby_file_management=auto #Allow rename, add, drop a data file

#remote_archive_enable=TRUE # deprecated

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

#log_parallelism=1 # obsolete in 10g

###########################################

# ARchive logging

###########################################

 log_archive_dest_1='LOCATION=/mnt/xserve-raid/u03/oradata/orioles/arch'

 log_archive_format='%t_%s_%r.arc'

 #log_archive_start=FALSE # deprecated in 10g

 log_archive_max_processes=9

db_recovery_file_dest=''

dg_broker_config_file1=''

dg_broker_config_file2=''

4)

Initorioles.ora (Standby) at Selma (only standby parameters)

Please note FAL_SERVER and FAL_CLIENT corresponds to tnsnames.ora connection string .

FAL_SERVER should be present in the standby machine’s tnsnames.ora so that the standby will know which primary database to communicate when there is a log gap and it sends the FAL_CLIENT value with it’s request. The primary will use the FAL_CLIENT to know to which standby database it should be sending the missing archive log files to.

For simplicity, it is best to have both the tnsnames.ora file of primary and standby to have the same values.

It would be a good idea to also have these settings in the primary database so that if a switchover happens the new standby will also benefit from the FAL processes.In our example the init.ora of the orioles instance(primary) on patty does not have it but it is adviced to have it over there too.

####################################

#Standby Parameters

####################################

FAL_SERVER='orioles_primary'

FAL_CLIENT='orioles_standby'

# standby_archive_dest is not compulsory as the default will be log_archive_dest_1 if it is absent.

standby_archive_dest='LOCATION=/mnt/xserve-raid/u02/oradata/orioles/arch'

#log_archive_dest_2='SERVICE=ltstdby OPTIONAL'

standby_file_management=auto #Allow rename, add, drop a data file

#remote_archive_enable=TRUE # deprecated

LOG_ARCHIVE_DEST_STATE_1=ENABLE

#LOG_ARCHIVE_DEST_STATE_2=ENABLE

#log_parallelism=1 # obsolete in 10g

###########################################

# ARchive logging

###########################################

 log_archive_dest_1='LOCATION=/mnt/xserve-raid/u04/oradata/orioles/arch'

 log_archive_format='%t_%s_%r.arc'

 #log_archive_start=FALSE # deprecated in 10g

 log_archive_max_processes=9

db_recovery_file_dest=''

dg_broker_config_file1=''

dg_broker_config_file2=''

5) tnsnames.ora file on both primary and standby

ORIOLES_PRIMARY =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = patty.wgenhq.net)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = orioles.world)

    )

  )

ORIOLES_STANDBY =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = selma.wgenhq.net)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = orioles.world)

    )

  )

rmancat =

  (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = marge.wgenhq.net)(PORT = 1527))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = rmancat.world)

    )

)

 6) Please do the following at Patty to cleanup all the rman backup data as we want to start afresh

RMAN> list backup;

List of Backup Sets

===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

9453    Full    6.30M      DISK        00:00:00     25-JUN-07     

        BP Key: 9454   Status: AVAILABLE  Compressed: NO  Tag: TAG20070625T141518

        Piece Name: /mnt/xserve-raid2/u05/bkp/PRIMARY_control_ORIOLES_20070625_12il5rnn_1_1

  Control File Included: Ckp SCN: 13558656688   Ckp time: 25-JUN-07

BS Key  Size       Device Type Elapsed Time Completion Time

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

9497    4.86M      DISK        00:00:01     25-JUN-07     

………….

………….

………….

(Heavily edited )

  1    196     13558677195 25-JUN-07 13558678022 25-JUN-07

BS Key  Size       Device Type Elapsed Time Completion Time

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

9767    32.00K     DISK        00:00:01     25-JUN-07     

        BP Key: 9773   Status: AVAILABLE  Compressed: NO  Tag: TAG20070625T145043

        Piece Name: /mnt/xserve-raid2/u05/bkp/PRIMARY_arc_ORIOLES_20070625_1cil5tq4_1_1

  List of Archived Logs in backup set 9767

  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time

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

  1    197     13558678022 25-JUN-07 13558678344 25-JUN-07

  1    198     13558678344 25-JUN-07 13558678367 25-JUN-07

RMAN>

Now we know that all backups are at : /mnt/xserve-raid2/u05/bkp

Now will delete all backups from the OS

patty:oracle>rm /mnt/xserve-raid2/u05/bkp/*

Connect back to rman and make the status as EXPIRED

RMAN> crosscheck backup;

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=190 devtype=DISK

crosschecked backup piece: found to be 'EXPIRED'

backup piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_control_ORIOLES_20070625_12il5rnn_1_1 recid=33 stamp=626192119

crosschecked backup piece: found to be 'EXPIRED'

backup piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_arc_ORIOLES_20070625_13il5rqn_1_1 recid=34 stamp=626192216

crosschecked backup piece: found to be 'EXPIRED'

backup piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_arc_ORIOLES_20070625_14il5ru2_1_1 recid=35 stamp=626192323

crosschecked backup piece: found to be 'EXPIRED'

backup piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_FULL_ORIOLES_20070625_15il5s2p_1_1 recid=36 stamp=626192473

crosschecked backup piece: found to be 'EXPIRED'

backup piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_FULL_ORIOLES_20070625_16il5s38_1_1 recid=37 stamp=626192489

crosschecked backup piece: found to be 'EXPIRED'

backup piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_arc_ORIOLES_20070625_17il5s3b_1_1 recid=38 stamp=626192492

crosschecked backup piece: found to be 'EXPIRED'

backup piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_FULL_ORIOLES_20070625_18il5sug_1_1 recid=39 stamp=626193360

crosschecked backup piece: found to be 'EXPIRED'

backup piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_FULL_ORIOLES_20070625_19il5sun_1_1 recid=40 stamp=626193367

crosschecked backup piece: found to be 'EXPIRED'

backup piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_control_ORIOLES_20070625_1ail5sup_1_1 recid=41 stamp=626193369

crosschecked backup piece: found to be 'EXPIRED'

backup piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_arc_ORIOLES_20070625_1bil5sur_1_1 recid=42 stamp=626193372

crosschecked backup piece: found to be 'EXPIRED'

backup piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_arc_ORIOLES_20070625_1cil5tq4_1_1 recid=43 stamp=626194245

Crosschecked 11 objects

RMAN> delete expired backup;

using channel ORA_DISK_1

List of Backup Pieces

BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name

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

9454    9453    1   1   EXPIRED     DISK        /mnt/xserve-raid2/u05/bkp/PRIMARY_control_ORIOLES_20070625_12il5rnn_1_1

9499    9497    1   1   EXPIRED     DISK        /mnt/xserve-raid2/u05/bkp/PRIMARY_arc_ORIOLES_20070625_13il5rqn_1_1

9594    9554    1   1   EXPIRED     DISK        /mnt/xserve-raid2/u05/bkp/PRIMARY_arc_ORIOLES_20070625_14il5ru2_1_1

9633    9616    1   1   EXPIRED     DISK        /mnt/xserve-raid2/u05/bkp/PRIMARY_FULL_ORIOLES_20070625_15il5s2p_1_1

9634    9617    1   1   EXPIRED     DISK        /mnt/xserve-raid2/u05/bkp/PRIMARY_FULL_ORIOLES_20070625_16il5s38_1_1

9652    9650    1   1   EXPIRED     DISK        /mnt/xserve-raid2/u05/bkp/PRIMARY_arc_ORIOLES_20070625_17il5s3b_1_1

9708    9705    1   1   EXPIRED     DISK        /mnt/xserve-raid2/u05/bkp/PRIMARY_FULL_ORIOLES_20070625_18il5sug_1_1

9709    9706    1   1   EXPIRED     DISK        /mnt/xserve-raid2/u05/bkp/PRIMARY_FULL_ORIOLES_20070625_19il5sun_1_1

9721    9719    1   1   EXPIRED     DISK        /mnt/xserve-raid2/u05/bkp/PRIMARY_control_ORIOLES_20070625_1ail5sup_1_1

9737    9735    1   1   EXPIRED     DISK        /mnt/xserve-raid2/u05/bkp/PRIMARY_arc_ORIOLES_20070625_1bil5sur_1_1

9773    9767    1   1   EXPIRED     DISK        /mnt/xserve-raid2/u05/bkp/PRIMARY_arc_ORIOLES_20070625_1cil5tq4_1_1

Do you really want to delete the above objects (enter YES or NO)? yes

deleted backup piece

backup piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_control_ORIOLES_20070625_12il5rnn_1_1 recid=33 stamp=626192119

deleted backup piece

backup piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_arc_ORIOLES_20070625_13il5rqn_1_1 recid=34 stamp=626192216

deleted backup piece

backup piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_arc_ORIOLES_20070625_14il5ru2_1_1 recid=35 stamp=626192323

deleted backup piece

backup piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_FULL_ORIOLES_20070625_15il5s2p_1_1 recid=36 stamp=626192473

deleted backup piece

backup piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_FULL_ORIOLES_20070625_16il5s38_1_1 recid=37 stamp=626192489

deleted backup piece

backup piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_arc_ORIOLES_20070625_17il5s3b_1_1 recid=38 stamp=626192492

deleted backup piece

backup piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_FULL_ORIOLES_20070625_18il5sug_1_1 recid=39 stamp=626193360

deleted backup piece

backup piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_FULL_ORIOLES_20070625_19il5sun_1_1 recid=40 stamp=626193367

deleted backup piece

backup piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_control_ORIOLES_20070625_1ail5sup_1_1 recid=41 stamp=626193369

deleted backup piece

backup piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_arc_ORIOLES_20070625_1bil5sur_1_1 recid=42 stamp=626193372

deleted backup piece

backup piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_arc_ORIOLES_20070625_1cil5tq4_1_1 recid=43 stamp=626194245

Deleted 11 EXPIRED objects

RMAN> list backup;

RMAN>

Now the above list backup shows no records.

7) Run the following commands at patty to backup the primary database

RMAN> run {

2>  allocate channel d1 type disk maxpiecesize=10g;

3>  backup database format '/mnt/xserve-raid2/u05/bkp/PRIMARY_FULL_%d_%T_%U';

4>  backup current controlfile for standby format '/mnt/xserve-raid2/u05/bkp/PRIMARY_control_standby_%d_%T_%U';

5>  #backup current controlfile  format '/mnt/xserve-raid2/u05/bkp/PRIMARY_control_%d_%T_%U';

6>  backup archivelog all format '/mnt/xserve-raid2/u05/bkp/PRIMARY_arc_%d_%T_%U' delete input;

7>  release channel d1;

8>  }

released channel: ORA_DISK_1

allocated channel: d1

channel d1: sid=190 devtype=DISK

Starting backup at 28-JUN-07

channel d1: starting full datafile backupset

channel d1: specifying datafile(s) in backupset

input datafile fno=00001 name=/mnt/xserve-raid/u02/oradata/orioles/system01.dbf

input datafile fno=00002 name=/mnt/xserve-raid/u03/oradata/orioles/sysaux01.dbf

input datafile fno=00004 name=/mnt/xserve-raid/u02/oradata/orioles/users01.dbf

input datafile fno=00003 name=/mnt/xserve-raid/u02/oradata/orioles/undo_tbs01.dbf

channel d1: starting piece 1 at 28-JUN-07

channel d1: finished piece 1 at 28-JUN-07

piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_FULL_ORIOLES_20070628_1dilde4i_1_1 tag=TAG20070628T111218 comment=NONE

channel d1: backup set complete, elapsed time: 00:00:07

channel d1: starting full datafile backupset

channel d1: specifying datafile(s) in backupset

including current control file in backupset

channel d1: starting piece 1 at 28-JUN-07

channel d1: finished piece 1 at 28-JUN-07

piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_FULL_ORIOLES_20070628_1eilde4p_1_1 tag=TAG20070628T111218 comment=NONE

channel d1: backup set complete, elapsed time: 00:00:02

Finished backup at 28-JUN-07

Starting backup at 28-JUN-07

channel d1: starting full datafile backupset

channel d1: specifying datafile(s) in backupset

including standby control file in backupset

channel d1: starting piece 1 at 28-JUN-07

channel d1: finished piece 1 at 28-JUN-07

piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_control_standby_ORIOLES_20070628_1filde4s_1_1 tag=TAG20070628T111227 comment=NONE

channel d1: backup set complete, elapsed time: 00:00:01

Finished backup at 28-JUN-07

Starting backup at 28-JUN-07

current log archived

channel d1: starting archive log backupset

channel d1: specifying archive log(s) in backup set

input archive log thread=1 sequence=199 recid=366 stamp=626392805

input archive log thread=1 sequence=200 recid=367 stamp=626439608

input archive log thread=1 sequence=201 recid=368 stamp=626440349

channel d1: starting piece 1 at 28-JUN-07

channel d1: finished piece 1 at 28-JUN-07

piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_arc_ORIOLES_20070628_1gilde4u_1_1 tag=TAG20070628T111230 comment=NONE

channel d1: backup set complete, elapsed time: 00:00:04

channel d1: deleting archive log(s)

archive log filename=/mnt/xserve-raid/u03/oradata/orioles/arch/1_199_624645845.arc recid=366 stamp=626392805

archive log filename=/mnt/xserve-raid/u03/oradata/orioles/arch/1_200_624645845.arc recid=367 stamp=626439608

archive log filename=/mnt/xserve-raid/u03/oradata/orioles/arch/1_201_624645845.arc recid=368 stamp=626440349

Finished backup at 28-JUN-07

released channel: d1

RMAN>

8) scp all files to Selma from Patty to the backup location /mnt/xserve-raid2/u05/bkp

At Selma :

selma:oracle>hostname

selma.wgenhq.net

selma:oracle>pwd

/mnt/xserve-raid2/u05/bkp

selma:oracle>scp oracle@patty:/mnt/xserve-raid2/u05/bkp/* .

PRIMARY_FULL_ORIOLES_20070628_1dilde4i_1_1    100%  335MB  55.9MB/s   00:06   

PRIMARY_FULL_ORIOLES_20070628_1eilde4p_1_1                                                        100% 6464KB   6.3MB/s   00:00   

PRIMARY_arc_ORIOLES_20070628_1gilde4u_1_1                                                         100%  125MB  41.8MB/s   00:03   

PRIMARY_control_standby_ORIOLES_20070628_1filde4s_1_1                                             100% 6464KB   6.3MB/s   00:00   

selma:oracle>

9)Create the standby database on Selma

At Selma:

Very Important: Please make sure the password file is created on the standby and the passwords are the same between primary and standby. Also set the relevant init.ora remote_login_passwordfile  is set to exclusive.

If password file is not available the primary will not be able to contact the standby to ship archive logs.

selma:oracle>export ORACLE_SID=orioles

selma:oracle>sqlplus /nolog

SQL*Plus: Release 10.2.0.3.0 - Production on Thu Jun 28 11:26:57 2007

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

SQL> connect / as sysdba;

Connected to an idle instance.

SQL> startup nomount

ORACLE instance started.

Total System Global Area  759169024 bytes

Fixed Size                  2075784 bytes

Variable Size             331350904 bytes

Database Buffers          419430400 bytes

Redo Buffers                6311936 bytes

SQL>

Run the rman duplicate command

rman target system/oracle@orioles_primary catalog rman_owner/rman_owner@rmancat auxiliary /

Recovery Manager complete.

wner@rmancat auxiliary / system/oracle@orioles_primary catalog rman_owner/rman_o

Recovery Manager: Release 10.2.0.3.0 - Production on Thu Jun 28 11:32:43 2007

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

connected to target database: ORIOLES (DBID=1460895637)

connected to recovery catalog database

connected to auxiliary database: ORIOLES (not mounted)

RMAN>

RMAN>  DUPLICATE TARGET DATABASE FOR STANDBY NOFILENAMECHECK DORECOVER;

Starting Duplicate Db at 28-JUN-07

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: sid=211 devtype=DISK

contents of Memory Script:

{

   set until scn  13558802368;

   restore clone standby controlfile;

   sql clone 'alter database mount standby database';

}

executing Memory Script

executing command: SET until clause

Starting restore at 28-JUN-07

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backupset restore

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: reading from backup piece /mnt/xserve-raid2/u05/bkp/PRIMARY_control_standby_ORIOLES_20070628_1filde4s_1_1

channel ORA_AUX_DISK_1: restored backup piece 1

piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_control_standby_ORIOLES_20070628_1filde4s_1_1 tag=TAG20070628T111227

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03

output filename=/mnt/xserve-raid/u02/oradata/orioles/control01.ctl

output filename=/mnt/xserve-raid/u03/oradata/orioles/control02.ctl

Finished restore at 28-JUN-07

sql statement: alter database mount standby database

released channel: ORA_AUX_DISK_1

contents of Memory Script:

{

   set until scn  13558802368;

   set newname for tempfile  1 to

 "/mnt/xserve-raid/u03/oradata/orioles/temp01.dbf";

   switch clone tempfile all;

   set newname for datafile  1 to

 "/mnt/xserve-raid/u02/oradata/orioles/system01.dbf";

   set newname for datafile  2 to

 "/mnt/xserve-raid/u03/oradata/orioles/sysaux01.dbf";

   set newname for datafile  3 to

 "/mnt/xserve-raid/u02/oradata/orioles/undo_tbs01.dbf";

   set newname for datafile  4 to

 "/mnt/xserve-raid/u02/oradata/orioles/users01.dbf";

   restore

   check readonly

   clone database

   ;

}

executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

renamed temporary file 1 to /mnt/xserve-raid/u03/oradata/orioles/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 28-JUN-07

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: sid=214 devtype=DISK

channel ORA_AUX_DISK_1: starting datafile backupset restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00001 to /mnt/xserve-raid/u02/oradata/orioles/system01.dbf

restoring datafile 00002 to /mnt/xserve-raid/u03/oradata/orioles/sysaux01.dbf

restoring datafile 00003 to /mnt/xserve-raid/u02/oradata/orioles/undo_tbs01.dbf

restoring datafile 00004 to /mnt/xserve-raid/u02/oradata/orioles/users01.dbf

channel ORA_AUX_DISK_1: reading from backup piece /mnt/xserve-raid2/u05/bkp/PRIMARY_FULL_ORIOLES_20070628_1dilde4i_1_1

channel ORA_AUX_DISK_1: restored backup piece 1

piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_FULL_ORIOLES_20070628_1dilde4i_1_1 tag=TAG20070628T111218

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:36

Finished restore at 28-JUN-07

contents of Memory Script:

{

   switch clone datafile all;

}

executing Memory Script

datafile 1 switched to datafile copy

input datafile copy recid=7 stamp=626441695 filename=/mnt/xserve-raid/u02/oradata/orioles/system01.dbf

datafile 2 switched to datafile copy

input datafile copy recid=8 stamp=626441695 filename=/mnt/xserve-raid/u03/oradata/orioles/sysaux01.dbf

datafile 3 switched to datafile copy

input datafile copy recid=9 stamp=626441695 filename=/mnt/xserve-raid/u02/oradata/orioles/undo_tbs01.dbf

datafile 4 switched to datafile copy

input datafile copy recid=10 stamp=626441695 filename=/mnt/xserve-raid/u02/oradata/orioles/users01.dbf

contents of Memory Script:

{

   set until scn  13558802368;

   recover

   standby

   clone database

    delete archivelog

   ;

}

executing Memory Script

executing command: SET until clause

Starting recover at 28-JUN-07

using channel ORA_AUX_DISK_1

starting media recovery

channel ORA_AUX_DISK_1: starting archive log restore to default destination

channel ORA_AUX_DISK_1: restoring archive log

archive log thread=1 sequence=201

channel ORA_AUX_DISK_1: reading from backup piece /mnt/xserve-raid2/u05/bkp/PRIMARY_arc_ORIOLES_20070628_1gilde4u_1_1

channel ORA_AUX_DISK_1: restored backup piece 1

piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_arc_ORIOLES_20070628_1gilde4u_1_1 tag=TAG20070628T111230

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03

archive log filename=/mnt/xserve-raid/u04/oradata/orioles/arch/1_201_624645845.arc thread=1 sequence=201

channel clone_default: deleting archive log(s)

archive log filename=/mnt/xserve-raid/u04/oradata/orioles/arch/1_201_624645845.arc recid=1 stamp=626441698

media recovery complete, elapsed time: 00:00:03

Finished recover at 28-JUN-07

Finished Duplicate Db at 28-JUN-07

RMAN>

10) Put the standby in managed recover mode

At Selma :

selma:oracle>sqlplus /nolog

SQL*Plus: Release 10.2.0.3.0 - Production on Thu Jun 28 11:36:53 2007

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

SQL> connect / as sysdba;

Connected.

SQL> shutdown immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup nomount

ORACLE instance started.

Total System Global Area  759169024 bytes

Fixed Size                  2075784 bytes

Variable Size             331350904 bytes

Database Buffers          419430400 bytes

Redo Buffers                6311936 bytes

SQL> alter database mount standby database;

Database altered.

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL>

Check the alert log of standby database with log switches at the primary database

tail –f alert*

Thu Jun 28 11:45:53 2007

RFS[1]: No standby redo logfiles created

Thu Jun 28 11:45:53 2007

RFS[2]: No standby redo logfiles created

Thu Jun 28 11:45:53 2007

RFS[1]: Archived Log: '/mnt/xserve-raid/u02/oradata/orioles/arch/1_210_624645845.arc'

Thu Jun 28 11:45:53 2007

RFS[2]: Archived Log: '/mnt/xserve-raid/u02/oradata/orioles/arch/1_211_624645845.arc'

Thu Jun 28 11:45:55 2007

Media Recovery Log /mnt/xserve-raid/u02/oradata/orioles/arch/1_210_624645845.arc

Media Recovery Log /mnt/xserve-raid/u02/oradata/orioles/arch/1_211_624645845.arc

Media Recovery Waiting for thread 1 sequence 212

At primary orioles :

After multiple log switches

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /mnt/xserve-raid/u03/oradata/orioles/arch

Oldest online log sequence     210

Next log sequence to archive   212

Current log sequence           212

Now the standby is all done and we are setting ourselves up for bigger things like a full database recovery …..

FULL DATABASE RECOVERY

1.Cleanup all prior backup information

This can be done from both standby or primary. I am doing this from standby.

Physically deleting the files from both patty and Selma

patty:oracle>cd /mnt/xserve-raid2/u05/bkp

patty:oracle>ls

PRIMARY_arc_ORIOLES_20070628_1gilde4u_1_1

PRIMARY_control_standby_ORIOLES_20070628_1filde4s_1_1

PRIMARY_FULL_ORIOLES_20070628_1dilde4i_1_1

PRIMARY_FULL_ORIOLES_20070628_1eilde4p_1_1

patty:oracle>rm *

patty:oracle>

selma:oracle>cd /mnt/xserve-raid2/u05/bkp

selma:oracle>ls

PRIMARY_arc_ORIOLES_20070628_1gilde4u_1_1              PRIMARY_FULL_ORIOLES_20070628_1dilde4i_1_1

PRIMARY_control_standby_ORIOLES_20070628_1filde4s_1_1  PRIMARY_FULL_ORIOLES_20070628_1eilde4p_1_1

selma:oracle>rm *

selma:oracle>

At Selma :

RMAN> crosscheck backup;

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=191 devtype=DISK

crosschecked backup piece: found to be 'EXPIRED'

backup piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_FULL_ORIOLES_20070628_1dilde4i_1_1 recid=44 stamp=626440338

crosschecked backup piece: found to be 'EXPIRED'

backup piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_FULL_ORIOLES_20070628_1eilde4p_1_1 recid=45 stamp=626440346

crosschecked backup piece: found to be 'EXPIRED'

backup piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_control_standby_ORIOLES_20070628_1filde4s_1_1 recid=46 stamp=626440348

crosschecked backup piece: found to be 'EXPIRED'

backup piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_arc_ORIOLES_20070628_1gilde4u_1_1 recid=47 stamp=626440351

Crosschecked 4 objects

RMAN> delete expired backup;

using channel ORA_DISK_1

List of Backup Pieces

BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name

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

9805    9803    1   1   EXPIRED     DISK        /mnt/xserve-raid2/u05/bkp/PRIMARY_FULL_ORIOLES_20070628_1dilde4i_1_1

9806    9804    1   1   EXPIRED     DISK        /mnt/xserve-raid2/u05/bkp/PRIMARY_FULL_ORIOLES_20070628_1eilde4p_1_1

9820    9818    1   1   EXPIRED     DISK        /mnt/xserve-raid2/u05/bkp/PRIMARY_control_standby_ORIOLES_20070628_1filde4s_1_1

9840    9838    1   1   EXPIRED     DISK        /mnt/xserve-raid2/u05/bkp/PRIMARY_arc_ORIOLES_20070628_1gilde4u_1_1

Do you really want to delete the above objects (enter YES or NO)? yes

deleted backup piece

backup piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_FULL_ORIOLES_20070628_1dilde4i_1_1 recid=44 stamp=626440338

deleted backup piece

backup piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_FULL_ORIOLES_20070628_1eilde4p_1_1 recid=45 stamp=626440346

deleted backup piece

backup piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_control_standby_ORIOLES_20070628_1filde4s_1_1 recid=46 stamp=626440348

deleted backup piece

backup piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_arc_ORIOLES_20070628_1gilde4u_1_1 recid=47 stamp=626440351

Deleted 4 EXPIRED objects

RMAN> list backup;

RMAN>

2) Take the controlfile backup from patty (primary) and scp the file to Selma

patty:oracle>rman target / catalog rman_owner/rman_owner@rmancat

Recovery Manager: Release 10.2.0.3.0 - Production on Thu Jun 28 12:01:56 2007

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

connected to target database: ORIOLES (DBID=1460895637)

connected to recovery catalog database

RMAN> run {

2>  allocate channel d1 type disk maxpiecesize=10g;

3>  #backup database format '/mnt/xserve-raid2/u05/bkp/PRIMARY_FULL_%d_%T_%U';

4>  #backup current controlfile for standby format '/mnt/xserve-raid2/u05/bkp/PRIMARY_control_standby_%d_%T_%U';

5>  backup current controlfile  format '/mnt/xserve-raid2/u05/bkp/PRIMARY_control_%d_%T_%U';

6>  #backup archivelog all format '/mnt/xserve-raid2/u05/bkp/PRIMARY_arc_%d_%T_%U' delete input;

7>  release channel d1;

8>  }

starting full resync of recovery catalog

full resync complete

allocated channel: d1

channel d1: sid=214 devtype=DISK

Starting backup at 28-JUN-07

channel d1: starting full datafile backupset

channel d1: specifying datafile(s) in backupset

including current control file in backupset

channel d1: starting piece 1 at 28-JUN-07

channel d1: finished piece 1 at 28-JUN-07

piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_control_ORIOLES_20070628_1hildh3o_1_1 tag=TAG20070628T120304 comment=NONE

channel d1: backup set complete, elapsed time: 00:00:01

Finished backup at 28-JUN-07

released channel: d1

RMAN>

3) scp the controlfile back to Selma ( or might as well keep it there as it is anyways going to be needed at patty during recovery) when we blow out orioles at patty. I prefer to keep it there as this saves me some labour.

4) Perform the full database backup and archivelog backup at Selma

run {

 allocate channel d1 type disk maxpiecesize=10g;

 backup database  format '/mnt/xserve-raid2/u05/bkp/STANDBY_FULL_%d_%T_%U';

 backup archivelog all format '/mnt/xserve-raid2/u05/bkp/STANDBY_arc_%d_%T_%U' delete input;

 release channel d1;

 }

….

….

….

There were some syntax errors and therefore I had to fire the backup database and backup archivelog separately.

Anyways at the end , here is what I had

RMAN> list backup;

List of Backup Sets

===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

9981    Full    6.30M      DISK        00:00:00     28-JUN-07     

        BP Key: 9982   Status: AVAILABLE  Compressed: NO  Tag: TAG20070628T120304

        Piece Name: /mnt/xserve-raid2/u05/bkp/PRIMARY_control_ORIOLES_20070628_1hildh3o_1_1

  Control File Included: Ckp SCN: 13558804034   Ckp time: 28-JUN-07

BS Key  Size       Device Type Elapsed Time Completion Time

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

10031   512.50K    DISK        00:00:01     28-JUN-07     

        BP Key: 10032   Status: AVAILABLE  Compressed: NO  Tag: TAG20070628T120714

        Piece Name: /mnt/xserve-raid2/u05/bkp/STANDBY_arc_ORIOLES_20070628_1hildhbj_1_1

  List of Archived Logs in backup set 10031

  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time

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

  1    202     13558802368 28-JUN-07 13558803405 28-JUN-07

  1    203     13558803405 28-JUN-07 13558803411 28-JUN-07

  1    204     13558803411 28-JUN-07 13558803611 28-JUN-07

  1    205     13558803611 28-JUN-07 13558803617 28-JUN-07

  1    206     13558803617 28-JUN-07 13558803622 28-JUN-07

  1    207     13558803622 28-JUN-07 13558803627 28-JUN-07

  1    208     13558803627 28-JUN-07 13558803630 28-JUN-07

  1    209     13558803630 28-JUN-07 13558803632 28-JUN-07

  1    210     13558803632 28-JUN-07 13558803635 28-JUN-07

  1    211     13558803635 28-JUN-07 13558803637 28-JUN-07

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

10074   Full    336.34M    DISK        00:00:05     28-JUN-07     

        BP Key: 10077   Status: AVAILABLE  Compressed: NO  Tag: TAG20070628T120755

        Piece Name: /mnt/xserve-raid2/u05/bkp/STANDBY_FULL_ORIOLES_20070628_1iildhcr_1_1

  List of Datafiles in backup set 10074

  File LV Type Ckp SCN    Ckp Time  Name

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

  1       Full 13558803637 28-JUN-07 /mnt/xserve-raid/u02/oradata/orioles/system01.dbf

  2       Full 13558803637 28-JUN-07 /mnt/xserve-raid/u03/oradata/orioles/sysaux01.dbf

  3       Full 13558803637 28-JUN-07 /mnt/xserve-raid/u02/oradata/orioles/undo_tbs01.dbf

  4       Full 13558803637 28-JUN-07 /mnt/xserve-raid/u02/oradata/orioles/users01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

10075   Full    6.30M      DISK        00:00:00     28-JUN-07     

        BP Key: 10078   Status: AVAILABLE  Compressed: NO  Tag: TAG20070628T120755

        Piece Name: /mnt/xserve-raid2/u05/bkp/STANDBY_FULL_ORIOLES_20070628_1jildhd2_1_1

  Standby Control File Included: Ckp SCN: 13558803637   Ckp time: 28-JUN-07

RMAN>

selma:oracle>cd /mnt/xserve-raid2/u05/bkp/

selma:oracle>ls

STANDBY_arc_ORIOLES_20070628_1hildhbj_1_1  STANDBY_FULL_ORIOLES_20070628_1iildhcr_1_1  STANDBY_FULL_ORIOLES_20070628_1jildhd2_1_1

selma:oracle>ls -l

total 351768

-rw-r-----  1 oracle oracle    525312 Jun 28 12:07 STANDBY_arc_ORIOLES_20070628_1hildhbj_1_1

-rw-r-----  1 oracle oracle 352681984 Jun 28 12:07 STANDBY_FULL_ORIOLES_20070628_1iildhcr_1_1

-rw-r-----  1 oracle oracle   6619136 Jun 28 12:08 STANDBY_FULL_ORIOLES_20070628_1jildhd2_1_1

selma:oracle>pwd

/mnt/xserve-raid2/u05/bkp

selma:oracle>ls

STANDBY_arc_ORIOLES_20070628_1hildhbj_1_1  STANDBY_FULL_ORIOLES_20070628_1iildhcr_1_1  STANDBY_FULL_ORIOLES_20070628_1jildhd2_1_1

selma:oracle>scp * oracle@patty:/mnt/xserve-raid2/u05/bkp/.

STANDBY_arc_ORIOLES_20070628_1hildhbj_1_1                                                         100%  513KB 513.0KB/s   00:00   

STANDBY_FULL_ORIOLES_20070628_1iildhcr_1_1                                                        100%  336MB  56.1MB/s   00:06   

STANDBY_FULL_ORIOLES_20070628_1jildhd2_1_1                                                        100% 6464KB   6.3MB/s   00:00   

selma:oracle>

5)Blowup orioles on patty (Primary) or rather move it to a backup location

At Patty:

patty:oracle>sqlplus /nolog

SQL*Plus: Release 10.2.0.3.0 - Production on Thu Jun 28 12:19:38 2007

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

SQL> connect / as sysdba;

Connected.

SQL> select file_name from dba_data_files;

FILE_NAME

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

/mnt/xserve-raid/u02/oradata/orioles/system01.dbf

/mnt/xserve-raid/u03/oradata/orioles/sysaux01.dbf

/mnt/xserve-raid/u02/oradata/orioles/undo_tbs01.dbf

/mnt/xserve-raid/u02/oradata/orioles/users01.dbf

SQL> select name from v$controlfile;

NAME

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

/mnt/xserve-raid/u02/oradata/orioles/control01.ctl

/mnt/xserve-raid/u03/oradata/orioles/control02.ctl

SQL> select member from v$logfile;

MEMBER

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

/mnt/xserve-raid/u03/oradata/orioles/redo01a.log

/mnt/xserve-raid/u02/oradata/orioles/redo01b.log

/mnt/xserve-raid/u03/oradata/orioles/redo02a.log

/mnt/xserve-raid/u02/oradata/orioles/redo2b.log

/mnt/xserve-raid/u03/oradata/orioles/redo03a.log

/mnt/xserve-raid/u02/oradata/orioles/redo03b.log

6 rows selected.

patty:oracle>cd /mnt/xserve-raid/u02/oradata/orioles

patty:oracle>ls

control01.ctl  redo01b.log  redo03b.log  redo2b.log  system01.dbf  undo_tbs01.dbf  users01.dbf  users01.dbf.06222007

patty:oracle>mkdir bkp

patty:oracle>cp * ./bkp/.

cp: omitting directory `bkp'

patty:oracle>cd /mnt/xserve-raid/u03/oradata/orioles

patty:oracle>mkdir bkp

patty:oracle>cp * ./bkp/.

cp: omitting directory `arch'

cp: omitting directory `bkp'

patty:oracle>

6) Start the rman recovery at primary

At Patty:

patty:oracle>sqlplus /nolog

SQL*Plus: Release 10.2.0.3.0 - Production on Thu Jun 28 12:25:18 2007

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

SQL> connect / as sysdba;

Connected to an idle instance.

SQL> startup nomount

ORACLE instance started.

Total System Global Area  759169024 bytes

Fixed Size                  2075784 bytes

Variable Size             331350904 bytes

Database Buffers          419430400 bytes

Redo Buffers                6311936 bytes

patty:oracle>rman target / catalog rman_owner/rman_owner@rmancat

Recovery Manager: Release 10.2.0.3.0 - Production on Thu Jun 28 12:26:23 2007

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

connected to target database: orioles (not mounted)

connected to recovery catalog database

RMAN> list backup of controlfile;

List of Backup Sets

===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

9981    Full    6.30M      DISK        00:00:00     28-JUN-07     

        BP Key: 9982   Status: AVAILABLE  Compressed: NO  Tag: TAG20070628T120304

        Piece Name: /mnt/xserve-raid2/u05/bkp/PRIMARY_control_ORIOLES_20070628_1hildh3o_1_1

  Control File Included: Ckp SCN: 13558804034   Ckp time: 28-JUN-07

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

10075   Full    6.30M      DISK        00:00:00     28-JUN-07     

        BP Key: 10078   Status: AVAILABLE  Compressed: NO  Tag: TAG20070628T120755

        Piece Name: /mnt/xserve-raid2/u05/bkp/STANDBY_FULL_ORIOLES_20070628_1jildhd2_1_1

  Standby Control File Included: Ckp SCN: 13558803637   Ckp time: 28-JUN-07

RMAN> run

2> { set until scn 13558804035; #one higher than above

3> restore controlfile;

4> }

executing command: SET until clause

Starting restore at 28-JUN-07

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=211 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: reading from backup piece /mnt/xserve-raid2/u05/bkp/PRIMARY_control_ORIOLES_20070628_1hildh3o_1_1

channel ORA_DISK_1: restored backup piece 1

piece handle=/mnt/xserve-raid2/u05/bkp/PRIMARY_control_ORIOLES_20070628_1hildh3o_1_1 tag=TAG20070628T120304

channel ORA_DISK_1: restore complete, elapsed time: 00:00:02

output filename=/mnt/xserve-raid/u02/oradata/orioles/control01.ctl

output filename=/mnt/xserve-raid/u03/oradata/orioles/control02.ctl

Finished restore at 28-JUN-07

RMAN> run

2> {restore database; #no need to set time or scn as we want the latest

3> }

Starting restore at 28-JUN-07

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00001 to /mnt/xserve-raid/u02/oradata/orioles/system01.dbf

restoring datafile 00002 to /mnt/xserve-raid/u03/oradata/orioles/sysaux01.dbf

restoring datafile 00003 to /mnt/xserve-raid/u02/oradata/orioles/undo_tbs01.dbf

restoring datafile 00004 to /mnt/xserve-raid/u02/oradata/orioles/users01.dbf

channel ORA_DISK_1: reading from backup piece /mnt/xserve-raid2/u05/bkp/STANDBY_FULL_ORIOLES_20070628_1iildhcr_1_1

channel ORA_DISK_1: restored backup piece 1

piece handle=/mnt/xserve-raid2/u05/bkp/STANDBY_FULL_ORIOLES_20070628_1iildhcr_1_1 tag=TAG20070628T120755

channel ORA_DISK_1: restore complete, elapsed time: 00:00:35

Finished restore at 28-JUN-07

patty:oracle>sqlplus /nolog

SQL*Plus: Release 10.2.0.3.0 - Production on Thu Jun 28 12:38:31 2007

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

SQL> connect / as sysdba;

Connected.

SQL> shutdown immediate

ORA-01507: database not mounted

sORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area  759169024 bytes

Fixed Size                  2075784 bytes

Variable Size             331350904 bytes

Database Buffers          419430400 bytes

Redo Buffers                6311936 bytes

Database mounted.

SQL> exit

patty:oracle>rman target / catalog rman_owner/rman_owner@rmancat

Recovery Manager: Release 10.2.0.3.0 - Production on Thu Jun 28 12:39:23 2007

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

connected to target database: ORIOLES (DBID=1460895637, not open)

connected to recovery catalog database

RMAN> list backup;

List of Backup Sets

===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

9981    Full    6.30M      DISK        00:00:00     28-JUN-07     

        BP Key: 9982   Status: AVAILABLE  Compressed: NO  Tag: TAG20070628T120304

        Piece Name: /mnt/xserve-raid2/u05/bkp/PRIMARY_control_ORIOLES_20070628_1hildh3o_1_1

  Control File Included: Ckp SCN: 13558804034   Ckp time: 28-JUN-07

BS Key  Size       Device Type Elapsed Time Completion Time

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

10031   512.50K    DISK        00:00:01     28-JUN-07     

        BP Key: 10032   Status: AVAILABLE  Compressed: NO  Tag: TAG20070628T120714

        Piece Name: /mnt/xserve-raid2/u05/bkp/STANDBY_arc_ORIOLES_20070628_1hildhbj_1_1

  List of Archived Logs in backup set 10031

  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time

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

  1    202     13558802368 28-JUN-07 13558803405 28-JUN-07

  1    203     13558803405 28-JUN-07 13558803411 28-JUN-07

  1    204     13558803411 28-JUN-07 13558803611 28-JUN-07

  1    205     13558803611 28-JUN-07 13558803617 28-JUN-07

  1    206     13558803617 28-JUN-07 13558803622 28-JUN-07

  1    207     13558803622 28-JUN-07 13558803627 28-JUN-07

  1    208     13558803627 28-JUN-07 13558803630 28-JUN-07

  1    209     13558803630 28-JUN-07 13558803632 28-JUN-07

  1    210     13558803632 28-JUN-07 13558803635 28-JUN-07

  1    211     13558803635 28-JUN-07 13558803637 28-JUN-07

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

10074   Full    336.34M    DISK        00:00:05     28-JUN-07     

        BP Key: 10077   Status: AVAILABLE  Compressed: NO  Tag: TAG20070628T120755

        Piece Name: /mnt/xserve-raid2/u05/bkp/STANDBY_FULL_ORIOLES_20070628_1iildhcr_1_1

  List of Datafiles in backup set 10074

  File LV Type Ckp SCN    Ckp Time  Name

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

  1       Full 13558803637 28-JUN-07 /mnt/xserve-raid/u02/oradata/orioles/system01.dbf

  2       Full 13558803637 28-JUN-07 /mnt/xserve-raid/u03/oradata/orioles/sysaux01.dbf

  3       Full 13558803637 28-JUN-07 /mnt/xserve-raid/u02/oradata/orioles/undo_tbs01.dbf

  4       Full 13558803637 28-JUN-07 /mnt/xserve-raid/u02/oradata/orioles/users01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

10075   Full    6.30M      DISK        00:00:00     28-JUN-07     

        BP Key: 10078   Status: AVAILABLE  Compressed: NO  Tag: TAG20070628T120755

        Piece Name: /mnt/xserve-raid2/u05/bkp/STANDBY_FULL_ORIOLES_20070628_1jildhd2_1_1

  Standby Control File Included: Ckp SCN: 13558803637   Ckp time: 28-JUN-07

#if you look above the restored datafile backup scn was 13558803637 and but the archivelog backup was till

#13558803635 only. Luckily we have archive log sequence 212 on disk as we did not delete it and we know that this should cover-up
#recovery till 13558803637 .In case the archivelog availability scn was lower than this , we would have been forced to restore a earlier  #datafile backup. This is the maximum we can recover to as everything else like the online redo log on the primary is wiped out and we #depend entirely on the archive logs which have been backed up. So I use a SCN one higher than

#13558803637 which is 13558803638.This ensures recovery is done till 13558803637.

RMAN> run        

2> {

3> set until scn=13558803638;

4> recover database;

5> }

executing command: SET until clause

Starting recover at 28-JUN-07

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=211 devtype=DISK

starting media recovery

archive log thread 1 sequence 212 is already on disk as file /mnt/xserve-raid/u02/oradata/orioles/redo2b.log

archive log filename=/mnt/xserve-raid/u02/oradata/orioles/redo2b.log thread=1 sequence=212

media recovery complete, elapsed time: 00:00:02

Finished recover at 28-JUN-07

RMAN> run

2> {alter database open resetlogs;

3> }

database opened

new incarnation of database registered in recovery catalog

starting full resync of recovery catalog

full resync complete

RMAN>

Let’s do some verification

patty:oracle>sqlplus /nolog

SQL*Plus: Release 10.2.0.3.0 - Production on Thu Jun 28 12:46:18 2007

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

SQL> connect / as sysdba;

Connected.

SQL> select name from v$database;

NAME

---------

ORIOLES

SQL> select * from recover_file;

select * from recover_file

              *

ERROR at line 1:

ORA-00942: table or view does not exist

SQL> desc v$recover_file

 Name                                      Null?    Type

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

 FILE#                                              NUMBER

 ONLINE                                             VARCHAR2(7)

 ONLINE_STATUS                                      VARCHAR2(7)

 ERROR                                              VARCHAR2(18)

 CHANGE#                                            NUMBER

 TIME                                               DATE

SQL> select * from v$recover_file;

no rows selected

SQL> select username from dba_users;

USERNAME

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

TSMSYS

ROBERT

OUTLN

DIP

DBSNMP

SCOTT

GAUTHAM

SYS

SYSTEM

9 rows selected.

SQL>

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS

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

SYSTEM                         ONLINE

SYSAUX                         ONLINE

TEMP                           ONLINE

UNDO_TBS                       ONLINE

USERS                          ONLINE

The database looks all good