Rolling Forward a Physical Standby Database
Using the RECOVER FROM SERVICE
Command
A standby database is a transactionally consistent copy of the production
database. It enables production Oracle databases to survive disasters and
data corruption. If the production database becomes unavailable because
of a planned or an unplanned outage, Data Guard can switch a standby
database to the production role, minimizing the downtime associated with
the outage. Moreover, the performance of production database can be
improved by offloading resource-intensive backup and reporting operations
to standby systems. As you can see, it’s always desirable to have standby
database synchronized with the primary database.
Prior to 12c, in order to roll forward the standby database using incremental
backups you would need to:
Determine the necessary SCN of the standby
Take an incremental backup on the primary starting from that SCN# of
the standby database.
Copy the incremental backup to the standby host
Catalog the backups (copied from the primary) into the standby
controlfile.
Cancel managed recovery of the standby database and apply the
incremental backup on the standby database.
Create a control file for the standby database on the primary
database.
Mount the standby database with newly created standby control file.
Start managed recovery of standby database.
In 12c, this procedure has been dramatically simplified. In 12c, you can use
the RECOVER … FROM SERVICE command to synchronize the physical
standby database with the primary database. This command does the
following:
Creates an incremental backup containing the changes to the primary
database. All changes to data files on the primary database, beginning
with the SCN in the standby datafile header, are included in the
incremental backup.
Transfers the incremental backup over the network to the physical
standby database.
Applies the incremental backup to the physical standby database.
This results in rolling forward the standby datafiles to the same point-in-
time as the primary. However, since the standby controlfile still contains old
SCN values (lower than the SCN values of the standby datafiles) to
complete the synchronization of the physical standby database, the
standby control file needs to be refreshed.
Setup
Primary Database:
DB_UNIQUE_NAME: prim (net service name 'PRIM')
PDB names = PDB1, PDB3
Standby Database:
DB_UNIQUE_NAME: clone (net service name 'CLONE')
Use the following steps to refresh the physical standby database with
changes made to the primary database:
Prerequisites
– Oracle Net connectivity is established between the physical standby
database and the primary database. This is done by adding an entry
corresponding to the primary.
– database in the tnsnames.ora file of the physical standby database.
– The password files on the primary database and the physical standby database are identical.
– The COMPATIBLE parameter in the initialization parameter file of the primary database
and physical standby database is set to 12.0 or higher.
– Start RMAN and connect as target to the physical standby database.
– Check the existing size of the Primary database and compare it with the existing size of the
standby datafiles. You will need at least the difference in free space size. If the datafiles on
primary have autoextended, the standby datafile would not be same in the size comparison.
When the incremental rollforward is executed, it will apply the newly added blocks to the
standby datafiles.
1. Place the physical standby database in MOUNT mode.
RMAN> SHUTDOWN IMMEDIATE;
RMAN> STARTUP MOUNT;
2. Stop the managed recovery processes on the physical standby
database.
RMAN> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
3. Let us identify the datafiles on standby database which are out of sync
with respect to primary.
PRIMARY:
SQL> select HXFIL File_num,substr(HXFNM,1,40),fhscn from x$kcvfh;
FILE_NUM SUBSTR(HXFNM,1,40) FHSCN
---------- ---------------------------------------- ----------------
1 /[path]/prim/system01.dbf 1984501
3 /[path]/prim/sysaux01.dbf 1984501
4 /[path]/prim/undotbs01.dbf 1984501
5 /[path]/prim/pdbseed/system01.dbf 1733076
6 /[path]/prim/users01.dbf 1984501
7 /[path]/prim/pdbseed/sysaux01.dbf 1733076
8 /[path]/prim/pdb1/system01.dbf 1984501
9 /[path]/prim/pdb1/sysaux01.dbf 1984501
10 /[path]/prim/pdb1/pdb1_users01.dbf 1984501
16 /[path]/prim/pdb3/system01.dbf 1984501
17 /[path]/prim/pdb3/sysaux01.dbf 1984501
18 /[path]/prim/pdb3/pdb3_users01.dbf 1984501
19 /[path]/prim/pdb3/test.dbf 1984501
13 rows selected.
STANDBY:
SQL> select HXFIL File_num,substr(HXFNM,1,40),fhscn from x$kcvfh;
FILE_NUM SUBSTR(HXFNM,1,40) FHSCN
---------- ---------------------------------------- ----------------
1 /[path]/clone/system01.dbf 1980995
3 /[path]/clone/sysaux01.dbf 1980998
4 /[path]/clone/undotbs01.dbf 1981008
5 /[path]/clone/pdbseed/system01.dbf 1733076
6 /[path]/clone/users01.dbf 1981012
7 /[path]/clone/pdbseed/sysaux01.dbf 1733076
8 /[path]/clone/pdb1/system01.dbf 1981015
9 /[path]/clone/pdb1/sysaux01.dbf 1981021
10 /[path]/clone/pdb1/pdb1_users01.dbf 1981028
16 /[path]/clone/pdb3/system01.dbf 1981030
17 /[path]/clone/pdb3/sysaux01.dbf 1981036
18 /[path]/clone/pdb3/pdb3_users01.dbf 1981043
19 /[path]/clone/pdb3/test.dbf 1981044
13 rows selected.
From the above example, when comparing the SCN of the datafiles’ header
on the primary (PRIM) and standby (CLONE), we see that whereas the
SCN of datafiles 5 and 7 match that of primary, the rest of the datafiles
(1,3,4,6,8,9,10,16,17) of the standby are lagging behind the primary
database.
4. Note the current SCN of the physical standby database. This is required
to determine, in a later step, if new data files were added to the primary
database. Query the V$DATABASE view to obtain the current SCN using
the following command:
RMAN> SELECT CURRENT_SCN FROM V$DATABASE;
For Example:
RMAN> SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
-----------
1984232
5. The RECOVER … FROM SERVICE command refreshes the standby
data files and rolls them forward to the same point-in-time as the primary.
$ rman target SYS/[password]
Recovery Manager: Release 12.1.0.1.0 - Production on Mon Mar 9 18:22:52
2015
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights
reserved.
connected to target database: PRIM (DBID=4165840403, not open)
RMAN> recover database from service PRIM noredo using compressed
backupset;
Output Sample:
Starting recover at 09-MAR-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=32 device type=DISK
skipping datafile 5; already restored to SCN 1733076
skipping datafile 7; already restored to SCN 1733076
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service
prim
destination for restore of datafile 00001: /[path]/clone/system01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service
prim
destination for restore of datafile 00003: /[path]/clone/sysaux01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service
prim
destination for restore of datafile 00004: /[path]/clone/undotbs01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service
prim
destination for restore of datafile 00006: /[path]/clone/users01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service
prim
destination for restore of datafile 00008:
/[path]/clone/pdb1/system01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service
prim
destination for restore of datafile 00009:
/[path]/clone/pdb1/sysaux01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service
prim
destination for restore of datafile 00010:
/[path]/clone/pdb1/pdb1_users01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service
prim
destination for restore of datafile 00016:
/[path]/clone/pdb3/system01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service
prim
destination for restore of datafile 00017:
/[path]/clone/pdb3/sysaux01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service
prim
destination for restore of datafile 00018:
/[path]/clone/pdb3/pdb3_users01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service
prim
destination for restore of datafile 00019: /[path]/clone/pdb3/test.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
Finished recover at 09-MAR-15
6. Lets check the SCNs of the datafiles at primary and standby now.
PRIMARY:
SQL> select HXFIL File_num,substr(HXFNM,1,40),fhscn from x$kcvfh;
FILE_NUM SUBSTR(HXFNM,1,40) FHSCN
---------- ---------------------------------------- ----------------
1 /[path]/prim/system01.dbf 1985174
3 /[path]/prim/sysaux01.dbf 1985183
4 /[path]/prim/undotbs01.dbf 1985194
5 /[path]/prim/pdbseed/system01.dbf 1733076
6 /[path]/prim/users01.dbf 1985203
7 /[path]/prim/pdbseed/sysaux01.dbf 1733076
8 /[path]/prim/pdb1/system01.dbf 1985206
9 /[path]/prim/pdb1/sysaux01.dbf 1985212
10 /[path]/prim/pdb1/pdb1_users01.dbf 1985218
16 /[path]/prim/pdb3/system01.dbf 1985221
17 /[path]/prim/pdb3/sysaux01.dbf 1985343
18 /[path]/prim/pdb3/pdb3_users01.dbf 1985350
19 /[path]/prim/pdb3/test.dbf 1985354
13 rows selected
STANDBY:
RMAN> select HXFIL File_num,substr(HXFNM,1,40),fhscn from x$kcvfh;
FILE_NUM SUBSTR(HXFNM,1,40) FHSCN
---------- ---------------------------------------- ----------------
1 /[path]/clone/system01.dbf 1985174
3 /[path]/clone/sysaux01.dbf 1985183
4 /[path]/clone/undotbs01.dbf 1985194
5 /[path]/clone/pdbseed/system01.dbf 1733076
6 /[path]/clone/users01.dbf 1985203
7 /[path]/clone/pdbseed/sysaux01.dbf 1733076
8 /[path]/clone/pdb1/system01.dbf 1985206
9 /[path]/clone/pdb1/sysaux01.dbf 1985212
10 /[path]/clone/pdb1/pdb1_users01.dbf 1985218
16 /[path]/clone/pdb3/system01.dbf 1985221
17 /[path]/clone/pdb3/sysaux01/dbf 1985343
18 /[path]/clone/pdb3/pdb3_users01.dbf 1985350
19 /[path]/clone/pdb3/test.dbf 1985354
13 rows selected
From above, you will observe that the primary and standby datafiles’ SCNs
are now matching or gap is reduced. However, the standby control file still
contains old SCN values which are lower than the SCN values in the
standby datafiles. Therefore, to complete the synchronization of the
physical standby database, we must refresh the standby controlfile from the
primary.
7. Use the following commands to shut down the standby database and
then start it in NOMOUNT mode.
RMAN> SHUTDOWN IMMEDIATE;
RMAN> STARTUP NOMOUNT;
Note: To achieve this in RAC, stop the standby database and start one
instance of it in nomount state.
$ srvctl stop database -db [db_name]
$ sqlplus / as sysdba
sql> startup nomount
8. Restore the standby control file by using the control file on the primary
database using service prim. The following command restores the control
file on the physical standby database by using the primary database control
file:
RMAN> RESTORE STANDBY CONTROLFILE FROM SERVICE [primary_tns_service];
For example:
RMAN> restore standby controlfile from service PRIM;
Starting restore at 09-MAR-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service prim
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/[path]/clone/control01.ctl
output file name=/[path]/fast_recovery_area/clone/control02.ctl
Finished restore at 09-MAR-15
After this step, the location and names of the datafiles in the standby
controlfile are those of the primary database. Depending on the
configuration, if the path and names of the standby datafiles after the
standby controlfile refresh are correct, steps #9 and #10 can be skipped.
Mount and check the location and datafile names of the standby datafiles
by executing the following:
RMAN> alter database mount;
Statement processed
released channel: ORA_DISK_1
Note: In RAC, Shutdown the standby instance that was started earlier in
step 7 before the restore of the controlfile and start the complete
standby database in MOUNT state.
RMAN> report schema;
Starting implicit crosscheck backup at 09-MAR-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK
Crosschecked 9 objects
Finished implicit crosscheck backup at 09-MAR-15
Starting implicit crosscheck copy at 09-MAR-15
using channel ORA_DISK_1
Crosschecked 2 objects
Finished implicit crosscheck copy at 09-MAR-15
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name:
/[path]/fast_recovery_area/CLONE/archivelog/2015_03_05/o1_mf_1_17_11q13
dm8_.arc
File Name:
/[path]/fast_recovery_area/CLONE/archivelog/2015_03_05/o1_mf_1_16_10q13
dm8_.arc
File Name:
/[path]/fast_recovery_area/CLONE/archivelog/2015_03_05/o1_mf_1_2_bhk1ct
cz_.arc
File Name:
/[path]/fast_recovery_area/CLONE/archivelog/2015_03_05/o1_mf_1_1_bhk17c
w8_.arc
RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name CLONE
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 780 SYSTEM *** /[path]/prim/system01.dbf
<=== datafiles are showing in PRIM directory rather than CLONE
3 730 SYSAUX *** /[path]/prim/sysaux01.dbf
4 90 UNDOTBS1 *** /[path]/prim/undotbs01.dbf
5 250 PDB$SEED:SYSTEM ***
/[path]/prim/pdbseed/system01.dbf
6 5 USERS *** /[path]/prim/users01.dbf
7 590 PDB$SEED:SYSAUX ***
/[path]/prim/pdbseed/sysaux01.dbf
8 260 PDB1:SYSTEM ***
/[path]/prim/pdb1/system01.dbf
9 620 PDB1:SYSAUX ***
/[path]/prim/pdb1/sysaux01.dbf
10 5 PDB1:USERS ***
/[path]/prim/pdb1/pdb1_users01.dbf
16 260 PDB3:SYSTEM ***
/[path]/prim/pdb3/system01.dbf
17 620 PDB3:SYSAUX ***
/[path]/prim/pdb3/sysaux01.dbf
18 5 PDB3:USERS ***
/[path]/prim/pdb3/pdb3_users01.dbf
19 50 PDB3:TEST *** /[path]/prim/pdb3/test.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 60 TEMP 32767 /[path]/prim/temp01.dbf
2 20 PDB$SEED:TEMP 32767
/[path]/prim/pdbseed/pdbseed_temp01.dbf
3 373 PDB1:TEMP 32767
/[path]/prim/pdb1/temp01.dbf
4 71 PDB3:TEMP 32767
/[path]/prim/pdb3/temp01.dbf
9. Update the names of the data files and the temp files in the standby
control file.
Note: It is recommended to check the incarnation of the primary and
standby prior to continuing.
Since the controlfile is restored from PRIMARY the datafile location names
in this restored STANDBY controlfile will be same as those of the
PRIMARY database. If the directory structure is different between the
standby and primary databases or if you are using Oracle-managed file
names OMF, catalog the STANDBY datafiles with RMAN to execute the
rename operation. If the primary and standby have identical structure and
datafile names, this step can be skipped.
RMAN> catalog start with '[path where the actual standby datafile
existed]';
For Example:
RMAN> Catalog start with '/u01/app/oracle/oradata/clone/';
searching for all files that match the pattern
/u01/app/oracle/oradata/clone
List of Files Unknown to the Database
=====================================
File Name: /[path]/clone/pdb1/pdb1_users01.dbf
File Name: /[path]/clone/pdb1/sysaux01.dbf
File Name: /[path]/clone/pdb1/system01.dbf
File Name: /[path]/clone/pdbseed/sysaux01.dbf
File Name: /[path]/clone/pdbseed/system01.dbf
File Name: /[path]/clone/sysaux01.dbf
File Name: /[path]/clone/system01.dbf
File Name: /[path]/clone/undotbs01.dbf
File Name: /[path]/clone/users01.dbf
File Name: /[path]/clone/pdb3/pdb3_users01.dbf
File Name: /[path]/clone/pdb3/sysaux01.dbf
File Name: /[path]/clone/pdb3/system01.dbf
File Name: /[path]/clone/pdb3/test.dbf
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /[path]/clone/pdb1/pdb1_users01.dbf
File Name: /[path]/clone/pdb1/sysaux01.dbf
File Name: /[path]/clone/pdb1/system01.dbf
File Name: /[path]/clone/pdbseed/sysaux01.dbf
File Name: /[path]/clone/pdbseed/system01.dbf
File Name: /[path]/clone/sysaux01.dbf
File Name: /[path]/clone/system01.dbf
File Name: /[path]/clone/undotbs01.dbf
File Name: /[path]/clone/users01.dbf
File Name: /[path]/clone/pdb3/pdb3_users01.dbf
File Name: /[path]/clone/pdb3/sysaux01.dbf
File Name: /[path]/clone/pdb3/system01.dbf
File Name: /[path]/clone/pdb3/test.dbf
10. Switch to the cataloged datafile copy.
RMAN> SWITCH DATABASE TO COPY;
datafile 1 switched to datafile copy "/[path]/clone/system01.dbf"
datafile 3 switched to datafile copy "/[path]/clone/sysaux01.dbf"
datafile 4 switched to datafile copy "/[path]/clone/undotbs01.dbf"
datafile 5 switched to datafile copy
"/[path]/clone/pdbseed/system01.dbf"
datafile 6 switched to datafile copy "/[path]/clone/users01.dbf"
datafile 7 switched to datafile copy
"/[path]/clone/pdbseed/sysaux01.dbf"
datafile 8 switched to datafile copy "/[path]/clone/pdb1/system01.dbf"
datafile 9 switched to datafile copy "/[path]/clone/pdb1/sysaux01.dbf"
datafile 10 switched to datafile copy
"/[path]/clone/pdb1/pdb1_users01.dbf"
datafile 16 switched to datafile copy "/[path]/clone/pdb3/system01.dbf"
datafile 17 switched to datafile copy "/[path]/clone/pdb3/sysaux01.dbf"
datafile 18 switched to datafile copy
"/[path]/clone/pdb3/pdb3_users01.dbf"
datafile 19 switched to datafile copy "/[path]/clone/pdb3/test.dbf"
Here, /u01/app/oracle/oradata/clone is the location of the data files on the
physical standby database. All data files must be stored in this location. If
the 'switch database to copy' command fails with the "RMAN-06571:
datafile .. does not have recoverable copy" error. This is an indication that
either you have not cataloged all your standby datafiles OR some of your
standby datafile's location/name is the same as the primary.
If the latter, you will have to switch each individual datafile whose
location/name are different than that of the primary. i.e.
RMAN> switch datafile [number] to copy;
For example:
RMAN> switch datafile 5 to copy;
11. Use the current SCN returned in step 4 to determine if new data files
were added to the primary database since the standby database was last
refreshed. If yes, these datafiles need to be restored on the standby from
the primary database.
The following example assumes that the CURRENT_SCN returned in Step
6 is 1984232 and lists the data files that were created on the primary after
the timestamp represented by this SCN:
SQL> SELECT file# FROM V$DATAFILE WHERE creation_change# >= 1984232;
If no files are returned in above query, go to step 13. If one or more files
are returned in Step 11, then restore these datafiles from the primary
database as in step 12.
12. If you are not connected to a recovery catalog, then use the following
commands to restore data files that were added to the primary after the
standby was last refreshed.
For example, assume datafile 21 was returned by the query in step 11.
Execute:
RMAN> RUN
2> {
3> SET NEWNAME FOR DATABASE TO '/';
4> RESTORE DATAFILE 21 FROM SERVICE prim;
5> }
13. Update the names of the online redo logs and standby redo logs in the
standby control file using one of the following methods:
Clear the log files:
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;
....
NOTE: Clearing log files is recommended only if the standby database
does not have access to the online or standby redo log files of the primary
database. For example, the standby and primary are running on the same
server and/or are using same ASM disk group. If the standby database has
access to the redo log files of the primary database and the primary
database redo logs are using OMF names, then the ALTER DATABASE
command will delete log files on the primary database.
If above is the case, the standby has access to the primary redo logs, then
you must use the ALTER DATABASE RENAME FILE command to rename
the redo log files. An individual command will be executed for each logfile
member. To rename log files, the STANDBY_FILE_MANAGEMENT
initialization parameter must be set to MANUAL.
14. (Oracle Active Data Guard only) Perform the following steps to open
the physical standby database:
On the primary database, switch the archived redo log files using the
following command:
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
On the physical standby database, run the following commands:
SQL> RECOVER DATABASE;
SQL> ALTER DATABASE OPEN READ ONLY;
Start the managed recovery processes on the physical standby database
by using the following command:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM
SESSION;
