1) Stop the managed
recovery process (MRP) on the STANDBY database
SQL> ALTER
DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Note: For the remaining steps, the standby database
must be in a MOUNT state.
2) Determine the SCN of
the STANDBY database.
On the standby database, find the SCN which will be used
for the incremental backup at the primary database:
You need to use the 'lowest SCN' from the queries below:
SQL> SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
--------------
3164433
SQL> select min(checkpoint_change#) from v$datafile_header
where file# not in (select file# from v$datafile where enabled = 'READ ONLY');
MIN(F.FHSCN)
----------------
3162298
You need to use the 'lowest SCN' from the queries, in this
example is SCN: 3162298. Therefore, From the above you need
to backup from SCN 3162298
3) Take an incremental
backup of the PRIMARY database
In RMAN, connect to the primary database and create an
incremental backup from the SCN derived in the previous step:
RMAN> BACKUP
INCREMENTAL FROM SCN 3162298 DATABASE FORMAT '/tmp/ForStandby_%U' tag
'FORSTANDBY';
4) Transfer all backup
sets to STANDBY server
All backups created by step #3 must be transferred from the
primary to the standby system. If the backup is written to NFS device,
this step can be skipped.
scp
/tmp/ForStandby_* standby:/tmp
5) Catalog the backups
in STANDBY controlfile.
In order for the standby database to know about the
backups, catalog the backup pieces on the STANDBY database.
RMAN> CATALOG
START WITH '/tmp/ForStandby';
using target
database control file instead of recovery catalog
searching for all
files that match the pattern /tmp/ForStandby
List of Files
Unknown to the Database
=====================================
File Name:
/tmp/ForStandby_2lkglss4_1_1
File Name:
/tmp/ForStandby_2mkglst8_1_1
Do you really want
to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done
List of Cataloged
Files
=======================
File Name:
/tmp/ForStandby_2lkglss4_1_1
File Name:
/tmp/ForStandby_2mkglst8_1_1
6) Recover the STANDBY database
with the cataloged incremental backup:
RMAN> RECOVER
DATABASE NOREDO;
starting recover at
03-JUN-09
allocated channel:
ORA_DISK_1
channel ORA_DISK_1:
sid=28 devtype=DISK
channel ORA_DISK_1:
starting incremental datafile backupset restore
channel ORA_DISK_1:
specifying datafile(s) to restore from backup set
destination for
restore of datafile 00001: +DATA/mystd/datafile/system.297.688213333
destination for
restore of datafile 00002: +DATA/mystd/datafile/undotbs1.268.688213335
destination for
restore of datafile 00003: +DATA/mystd/datafile/sysaux.267.688213333
channel ORA_DISK_1:
reading from backup piece /tmp/ForStandby_2lkglss4_1_1
channel ORA_DISK_1:
restored backup piece 1
piece
handle=/tmp/ForStandby_2lkglss4_1_1 tag=FORSTANDBY
channel ORA_DISK_1:
restore complete, elapsed time: 00:00:02
Finished recover at
03-JUN-09
7) In RMAN, connect to
the PRIMARY database and create a standby control file backup:
RMAN> BACKUP
CURRENT CONTROLFILE FOR STANDBY FORMAT '/tmp/ForStandbyCTRL.bck';
8) Copy the standby
control file backup to the STANDBY system.
scp /tmp/ForStandbyCTRL.bck
standby:/tmp
9) Capture datafile
information in STANDBY database.
We now need to refresh the standby controlfile from primary
controlfile (for standby) backup. However, since the datafile names are likely
different than primary, let's save the name of datafiles on standby first,
which we can refer after restoring controlfile from primary backup to verify if
there is any discrepancy. Run below query from Standby and save results for
further use.
spool
datafile_names_step8.txt
set lines 200
col name format a60
select file#, name
from v$datafile order by file# ;
spool off
10) From RMAN, connect
to STANDBY database and restore the standby control file:
RMAN> SHUTDOWN
IMMEDIATE ;
RMAN> STARTUP
NOMOUNT;
RMAN> RESTORE
STANDBY CONTROLFILE FROM '/tmp/ForStandbyCTRL.bck';
Starting restore at
03-JUN-09
using target
database control file instead of recovery catalog
allocated channel:
ORA_DISK_1
channel ORA_DISK_1:
sid=36 devtype=DISK
channel ORA_DISK_1:
restoring control file
channel ORA_DISK_1:
restore complete, elapsed time: 00:00:07
output
filename=+DATA/mystd/controlfile/current.257.688583989
Finished restore at
03-JUN-09
11) Shut down the STANDBY database
and startup mount:
RMAN> SHUTDOWN;
RMAN> STARTUP
MOUNT;
12) Catalog datafiles
in STANDBY if location/name of datafiles is different
Note: We recommend checking the incarnation for
primary and standby before completing this step.
example: RMAN> list incarnation;
Since the controlfile is restored from PRIMARY the datafile
locations in STANDBY controlfile will be same as PRIMARY database, if the
directory structure is different between the standby and primary or you are
using Oracle managed file names, catalog the datafiles in STANDBY will do the
necessary rename operations. If the primary and standby have identical
structure and datafile names, this step can be skipped.
Perform the below step in STANDBY for
each diskgroup where the datafile directory structure between primary and
standby are different.
RMAN> CATALOG
START WITH '+DATA/mystd/datafile/';
List of Files
Unknown to the Database
=====================================
File Name:
+data/mystd/DATAFILE/SYSTEM.309.685535773
File Name:
+data/mystd/DATAFILE/SYSAUX.301.685535773
File Name:
+data/mystd/DATAFILE/UNDOTBS1.302.685535775
File Name:
+data/mystd/DATAFILE/SYSTEM.297.688213333
File Name:
+data/mystd/DATAFILE/SYSAUX.267.688213333
File Name:
+data/mystd/DATAFILE/UNDOTBS1.268.688213335
Do you really want
to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done
List of Cataloged
Files
=======================
File Name:
+data/mystd/DATAFILE/SYSTEM.297.688213333
File Name:
+data/mystd/DATAFILE/SYSAUX.267.688213333
File Name: +data/mystd/DATAFILE/UNDOTBS1.268.688213335
If any datafiles have been added to Primary AFTER scn
3162298 they will also have to be restored to the standby host (see Note 1531031.1 Steps to perform for Rolling
forward a standby database using RMAN incremental backup when datafile is added
to primary) and cataloged as shown above before doing the
switch.
To determine if any files have been added to Primary since
the standby current scn:
SQL>SELECT
FILE#, NAME FROM V$DATAFILE WHERE CREATION_CHANGE# > 3162298
If the above query returns with 0 zero rows, you can switch
the datafiles. This will rename the datafiles to its correct name at the
standby site:
RMAN> SWITCH
DATABASE TO COPY;
datafile 1 switched
to datafile copy "+DATA/mystd/datafile/system.297.688213333"
datafile 2 switched
to datafile copy "+DATA/mystd/datafile/undotbs1.268.688213335"
datafile 3 switched
to datafile copy "+DATA/mystd/datafile/sysaux.267.688213333"
Note:
a) At this point, you can compare the query output from
step 9 for any discrepancy (other than newly added datafiles) to ensure we have
all the datafiles added in standby.
b) run the queries from step 2 again to confirm that the
incremental apply has moved the datafiles forward. The SCN should now be bigger
than its initial values.
13) Configure the STANDBY database
to use flashback (optional)
If the STANDBY database needs to be
configured for FLASHBACK run the following commands:
SQL> ALTER
DATABASE FLASHBACK OFF;
SQL> ALTER
DATABASE FLASHBACK ON;
NOTE: This command may return the following:
ORA-38788 More standby database recovery is
needed
Depending on situation, this is expected. Proceed
with the remaining steps.
14) On STANDBY database,
clear all standby redo log groups:
SQL> select
GROUP# from v$logfile where TYPE='STANDBY' group by GROUP#;
SQL> ALTER
DATABASE CLEAR LOGFILE GROUP 1;
SQL> ALTER
DATABASE CLEAR LOGFILE GROUP 2;
SQL> ALTER
DATABASE CLEAR LOGFILE GROUP 3;
....
15) On the STANDBY database,
start the MRP
SQL> ALTER
DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
