Roll Forward Physical standby database in 12c
As you are aware that rolling forward a physical standby via an incremental SCN backup method is the simplest method of getting your standby database with lag into sync with the primary database. The lag can be due to missing archives on the primary which haven’t been shipped or applied on the standby.
With 12c, the roll forward technique is a bit different. In the prior versions, we had to initially take an incremental backup of the primary database from the SCN where the standby was stalled and then on copy these backup pieces to the standby server and recover the standby using them. Here is a detailed steps of how to roll forward your physical standby database in 11g or 10g.
Roll Forward Physical Standby in 10g/11g
But, with 12c, it’s different. The entire operation of restore and recovery can be performed through RMAN over the network using the “net service name”.
The syntax would be as “recover database…from service”. This has reduced the multi steps of manual method of rolling forward a standby database. Let’s move on in rolling forward a physical standby database. The environment is of a 2 node Primary database and a 2 node standby database which uses ASM as a storage media and has the broker configuration enabled (not mandatory).
Environment:
Primary:
Primary database nodes : ora12c-node1, ora12c-node2 Database name : srprim Primary DB UNIQUE NAME : srprim Primary database instances : srprim1, srprim2
Standby:
Standby database nodes : ora12cdr1, ora12cdr2 Database name : srprim Standby DB UNIQUE NAME : srpstb Standby database instances : srpstb1, srpstb2
The details of the primary and standby instances are as follows:
Primary:
[oracle@ora12c-node1 ~]$ srvctl status database -db srprim -v -f Instance srprim1 is running on node ora12c-node1. Instance status: Open. Instance srprim2 is running on node ora12c-node2. Instance status: Open.
Standby:
[oracle@ora12cdr1 ~]$ srvctl status database -db srpstb -v -f Instance srpstb1 is running on node ora12cdr1. Instance status: Mounted (Closed). Instance srpstb2 is running on node ora12cdr2. Instance status: Mounted (Closed).
Broker configuration Details and Status:
[oracle@ora12c-node1 ~]$ dgmgrl /
DGMGRL for Linux: Version 12.1.0.1.0 - 64bit Production
Copyright (c) 2000, 2012, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected as SYSDG.
DGMGRL>
DGMGRL> show configuration;
Configuration - dgsrp
Protection Mode: MaxPerformance
Databases:
srprim - Primary database
srpstb - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
On the primary, the last sequence generated is 217 and 175 for thread 1 and 2 respectively.
SYS@srprim1 > select thread#,max(sequence#) from v$archived_log group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 217
2 175
While on the standby, the last sequence applied is 216 and 175 for thread 1 and 2 respectively.
SYS@srpstb1 > select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 216
2 175
Now, due to missing archives on the primary, there has been a gap detected on the standby and it’s waiting for log sequence 219 of thread 1.
SYS@srpstb1 > select process,status,sequence#,thread# from gv$managed_standby; PROCESS STATUS SEQUENCE# THREAD# --------- ------------ ---------- ---------- ARCH CLOSING 225 1 ARCH CLOSING 217 1 ARCH CONNECTED 0 0 ARCH CLOSING 178 2 MRP0 WAIT_FOR_GAP 219 1 RFS IDLE 0 0 RFS IDLE 0 0 RFS IDLE 179 2 RFS IDLE 0 0 RFS IDLE 0 0 RFS IDLE 0 0 PROCESS STATUS SEQUENCE# THREAD# --------- ------------ ---------- ---------- ARCH CONNECTED 0 0 ARCH CLOSING 226 1 ARCH CONNECTED 0 0 ARCH CONNECTED 0 0 RFS IDLE 227 1 RFS IDLE 0 0 17 rows selected.
A gap exists from log sequence 219 to 221 of thread 1 and this is what is been informed in the alert log of the standby database.
FAL[client]: Failed to request gap sequence GAP - thread 1 sequence 219-221 DBID 307664432 branch 891722994 FAL[client]: All defined FAL servers have been attempted.
Now, let’s compare the checkpoint change# for each of the datafiles on the primary database with the corresponding datafiles on the standby database to understand which all need recovery on the standby database.
On the primary:
SYS@srprim1 > select file#,checkpoint_change# from v$datafile;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 3111435
3 3111435
4 3111435
5 1739965
6 3111435
7 1739965
8 3111435
30 3050385
31 3050385
9 rows selected.
On the standby database:
SYS@srpstb1 > select file#,checkpoint_change# from v$datafile;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 3110480
3 3110480
4 3110480
5 1739965
6 3110480
7 1739965
8 3110480
30 3050385
31 3050385
9 rows selected.
From the above outcomes, it’s clear that datafiles 1,3,4,6 and 8 have a different checkpoint change# value on the standby when compared to the corresponding files on the primary database. This means, that the archive log sequence 219 to 221 contain the changes that need to be applied to these 5 (1,3,4,6,8) datafiles.
To move on, cancel the recovery on the standby database.
SYS@srpstb1 > alter database recover managed standby database cancel; Database altered.
Connect the standby database through RMAN as “target” and issue the “recover database from service <primary net alias>” command.
This command, recovers the target database (standby database) from the service name that you specify (primary database). You can also specify the “using compressed backupset” clause along with the above command so that RMAN takes a compressed backupset from the primary database and restores and recovers the standby database.
RMAN takes a backup of the corresponding datafile, transfers it over the network to the standby server and restores the file.
Secondly, RMAN is clever enough to restore only those datafiles whose checkpoint change# on the standby database is different from that of the primary database. This is a great feature in 12c, where as in prior versions, we had to manually take the incremental SCN backup of the entire primary database, manually ship the backups to the standby site and then recover. But here, RMAN does in one single command.
From the below outcome, it can be noticed that RMAN is skipping datafiles 5,7,30 and 31 as they have the same checkpoint change# across the primary and the standby sites and restoring only datafiles 1,3,4,6 and 8.
[oracle@ora12cdr1 ~]$ rman target / Recovery Manager: Release 12.1.0.1.0 - Production on Wed Dec 2 13:17:00 2015 Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved. connected to target database: SRPRIM (DBID=307664432, not open) RMAN> recover database from service srprim noredo using compressed backupset; Starting recover at 02-DEC-15 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=53 instance=srpstb1 device type=DISK skipping datafile 5; already restored to SCN 1739965 skipping datafile 7; already restored to SCN 1739965 skipping datafile 30; already restored to SCN 3050385 skipping datafile 31; already restored to SCN 3050385 channel ORA_DISK_1: starting incremental datafile backup set restore channel ORA_DISK_1: using compressed network backup set from service srprim destination for restore of datafile 00001: +DATA/SRPSTB/DATAFILE/system.270.893882851 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 srprim destination for restore of datafile 00003: +DATA/SRPSTB/DATAFILE/sysaux.268.893882883 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 srprim destination for restore of datafile 00004: +DATA/SRPSTB/DATAFILE/undotbs1.266.893882909 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 srprim destination for restore of datafile 00006: +DATA/SRPSTB/DATAFILE/users.264.893882923 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 srprim destination for restore of datafile 00008: +DATA/SRPSTB/DATAFILE/undotbs2.262.893882949 channel ORA_DISK_1: restore complete, elapsed time: 00:00:07 Finished recover at 02-DEC-15 RMAN>
So, now the datafiles have been restored and recovered, but remember that the controlfile needs to be updated. Hence we need to restore the controlfile from the primary site.
To achieve this, stop the standby database and start one instance of it in nomount state.
[oracle@ora12cdr1 ~]$ srvctl stop database -db srpstb [oracle@ora12cdr1 ~]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.1.0 Production on Wed Dec 2 13:21:26 2015 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. SYS@srpstb1 > startup nomount ORACLE instance started. Total System Global Area 1286066176 bytes Fixed Size 2287960 bytes Variable Size 536872616 bytes Database Buffers 738197504 bytes Redo Buffers 8708096 bytes SYS@srpstb1 > exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP, Advanced Analytics and Real Application Testing options
Connect the standby database through RMAN as target and restore the controlfile from primary using the ‘service’ clause.
[oracle@ora12cdr1 ~]$ rman target / Recovery Manager: Release 12.1.0.1.0 - Production on Wed Dec 2 13:21:40 2015 Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved. connected to target database: SRPRIM (not mounted) RMAN> restore standby controlfile from service srprim; Starting restore at 02-DEC-15 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=36 instance=srpstb1 device type=DISK channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: using network backup set from service srprim channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:15 output file name=+DATA/SRPSTB/CONTROLFILE/current.267.893882827 output file name=+FRA1/SRPSTB/CONTROLFILE/current.258.893882827 Finished restore at 02-DEC-15
Shutdown the standby instance that was started earlier before the restore of the controlfile and start the complete standby database in MOUNT state.
[oracle@ora12cdr1 ~]$ srvctl start database -db srpstb [oracle@ora12cdr1 ~]$ srvctl status database -db srpstb -v -f Instance srpstb1 is running on node ora12cdr1. Instance status: Mounted (Closed). Instance srpstb2 is running on node ora12cdr2. Instance status: Mounted (Closed).
If the datafiles locations on the primary and standby database are different, then you may have to follow this step. Since that we have restored the controlfile from the primary database, the datafiles locations will still be pointing to the location of the datafiles that we have on the primary site.
To overcome this, you need to catalog the standby datafile locations.
Connect the standby database through RMAN and catalog the location of it’s datafiles. In my environment, the datafiles of the standby are placed in “+DATA/SRPSTB” location.
RMAN> catalog start with '+DATA/SRPSTB/'; searching for all files that match the pattern +DATA/SRPSTB/ List of Files Unknown to the Database ===================================== File Name: +DATA/SRPSTB/spfilesrpstb.ora File Name: +DATA/SRPSTB/orapwsrpstb File Name: +DATA/SRPSTB/243D4D0CCA302D9BE0537200A8C03F95/DATAFILE/SYSTEM.315.895493381 File Name: +DATA/SRPSTB/243D4D0CCA302D9BE0537200A8C03F95/DATAFILE/SYSAUX.316.895493429 File Name: +DATA/SRPSTB/241D296020C9234AE0537200A8C02179/DATAFILE/SYSTEM.309.895350841 File Name: +DATA/SRPSTB/241D296020C9234AE0537200A8C02179/DATAFILE/SYSAUX.310.895350855 File Name: +DATA/SRPSTB/23F4F3269D3E2C5FE0537200A8C07E9C/DATAFILE/SYSTEM.286.895175737 File Name: +DATA/SRPSTB/TEMPFILE/TEMP.283.895009995 File Name: +DATA/SRPSTB/DATAGUARDCONFIG/srpstb.282.894924201 File Name: +DATA/SRPSTB/241D296020C8234AE0537200A8C02179/DATAFILE/SYSTEM.307.895347841 File Name: +DATA/SRPSTB/241D296020C8234AE0537200A8C02179/DATAFILE/SYSAUX.308.895348445 File Name: +DATA/SRPSTB/ONLINELOG/group_1.274.893883017 File Name: +DATA/SRPSTB/ONLINELOG/group_2.273.893883019 File Name: +DATA/SRPSTB/ONLINELOG/group_3.272.893883019 File Name: +DATA/SRPSTB/ONLINELOG/group_4.271.893883021 File Name: +DATA/SRPSTB/ONLINELOG/group_5.257.893886219 File Name: +DATA/SRPSTB/ONLINELOG/group_6.275.893886225 File Name: +DATA/SRPSTB/ONLINELOG/group_7.276.893886227 File Name: +DATA/SRPSTB/ONLINELOG/group_8.277.893886233 File Name: +DATA/SRPSTB/ONLINELOG/group_9.278.893886235 File Name: +DATA/SRPSTB/ONLINELOG/group_10.279.893886237 File Name: +DATA/SRPSTB/ONLINELOG/group_5.287.895259785 File Name: +DATA/SRPSTB/ONLINELOG/group_7.288.895259787 File Name: +DATA/SRPSTB/ONLINELOG/group_6.289.895259795 File Name: +DATA/SRPSTB/ONLINELOG/group_10.290.895259837 File Name: +DATA/SRPSTB/ONLINELOG/group_8.291.895259837 File Name: +DATA/SRPSTB/ONLINELOG/group_9.292.895259841 File Name: +DATA/SRPSTB/ONLINELOG/group_1.293.895260099 File Name: +DATA/SRPSTB/ONLINELOG/group_2.294.895260103 File Name: +DATA/SRPSTB/ONLINELOG/group_3.295.895260103 File Name: +DATA/SRPSTB/ONLINELOG/group_4.296.895260107 File Name: +DATA/SRPSTB/ONLINELOG/group_1.297.895261173 File Name: +DATA/SRPSTB/ONLINELOG/group_2.298.895261175 File Name: +DATA/SRPSTB/ONLINELOG/group_3.299.895261179 File Name: +DATA/SRPSTB/ONLINELOG/group_4.300.895261181 File Name: +DATA/SRPSTB/ONLINELOG/group_7.301.895261323 File Name: +DATA/SRPSTB/ONLINELOG/group_5.302.895261323 File Name: +DATA/SRPSTB/ONLINELOG/group_6.303.895261329 File Name: +DATA/SRPSTB/ONLINELOG/group_8.304.895261343 File Name: +DATA/SRPSTB/ONLINELOG/group_10.305.895261343 File Name: +DATA/SRPSTB/ONLINELOG/group_9.306.895261345 File Name: +DATA/SRPSTB/ONLINELOG/group_7.261.895492899 File Name: +DATA/SRPSTB/ONLINELOG/group_5.260.895492899 File Name: +DATA/SRPSTB/ONLINELOG/group_10.259.895492907 File Name: +DATA/SRPSTB/ONLINELOG/group_6.258.895492907 File Name: +DATA/SRPSTB/ONLINELOG/group_8.313.895492911 File Name: +DATA/SRPSTB/ONLINELOG/group_9.314.895492915 File Name: +DATA/SRPSTB/ONLINELOG/group_1.317.895493507 File Name: +DATA/SRPSTB/ONLINELOG/group_2.318.895493509 File Name: +DATA/SRPSTB/ONLINELOG/group_3.319.895493513 File Name: +DATA/SRPSTB/ONLINELOG/group_4.320.895493515 File Name: +DATA/SRPSTB/ONLINELOG/group_10.312.895515035 File Name: +DATA/SRPSTB/ONLINELOG/group_8.311.895496139 File Name: +DATA/SRPSTB/ONLINELOG/group_9.321.895496147 File Name: +DATA/SRPSTB/ONLINELOG/group_5.322.895496149 File Name: +DATA/SRPSTB/ONLINELOG/group_6.323.895496153 File Name: +DATA/SRPSTB/ONLINELOG/group_7.324.895496157 File Name: +DATA/SRPSTB/ONLINELOG/group_1.325.895496313 File Name: +DATA/SRPSTB/ONLINELOG/group_2.326.895496317 File Name: +DATA/SRPSTB/ONLINELOG/group_3.327.895496321 File Name: +DATA/SRPSTB/ONLINELOG/group_4.328.895496325 File Name: +DATA/SRPSTB/20E576F1A6FE02E0E0537200A8C01581/TEMPFILE/TEMP.285.895010041 File Name: +DATA/SRPSTB/20E55957E61A7D6CE0537200A8C09297/TEMPFILE/TEMP.284.895009997 File Name: +DATA/SRPSTB/20E55957E61A7D6CE0537200A8C09297/DATAFILE/SYSTEM.265.893882915 File Name: +DATA/SRPSTB/20E55957E61A7D6CE0537200A8C09297/DATAFILE/SYSAUX.263.893882923 File Name: +DATA/SRPSTB/DATAFILE/SYSTEM.270.893882851 File Name: +DATA/SRPSTB/DATAFILE/SYSAUX.268.893882883 File Name: +DATA/SRPSTB/DATAFILE/UNDOTBS1.266.893882909 File Name: +DATA/SRPSTB/DATAFILE/USERS.264.893882923 File Name: +DATA/SRPSTB/DATAFILE/UNDOTBS2.262.893882949 File Name: +DATA/SRPSTB/CONTROLFILE/Current.269.893882795 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/SRPSTB/243D4D0CCA302D9BE0537200A8C03F95/DATAFILE/SYSTEM.315.895493381 File Name: +DATA/SRPSTB/243D4D0CCA302D9BE0537200A8C03F95/DATAFILE/SYSAUX.316.895493429 File Name: +DATA/SRPSTB/241D296020C9234AE0537200A8C02179/DATAFILE/SYSTEM.309.895350841 File Name: +DATA/SRPSTB/241D296020C9234AE0537200A8C02179/DATAFILE/SYSAUX.310.895350855 File Name: +DATA/SRPSTB/23F4F3269D3E2C5FE0537200A8C07E9C/DATAFILE/SYSTEM.286.895175737 File Name: +DATA/SRPSTB/TEMPFILE/TEMP.283.895009995 File Name: +DATA/SRPSTB/241D296020C8234AE0537200A8C02179/DATAFILE/SYSTEM.307.895347841 File Name: +DATA/SRPSTB/241D296020C8234AE0537200A8C02179/DATAFILE/SYSAUX.308.895348445 File Name: +DATA/SRPSTB/20E576F1A6FE02E0E0537200A8C01581/TEMPFILE/TEMP.285.895010041 File Name: +DATA/SRPSTB/20E55957E61A7D6CE0537200A8C09297/TEMPFILE/TEMP.284.895009997 File Name: +DATA/SRPSTB/20E55957E61A7D6CE0537200A8C09297/DATAFILE/SYSTEM.265.893882915 File Name: +DATA/SRPSTB/20E55957E61A7D6CE0537200A8C09297/DATAFILE/SYSAUX.263.893882923 File Name: +DATA/SRPSTB/DATAFILE/SYSTEM.270.893882851 File Name: +DATA/SRPSTB/DATAFILE/SYSAUX.268.893882883 File Name: +DATA/SRPSTB/DATAFILE/UNDOTBS1.266.893882909 File Name: +DATA/SRPSTB/DATAFILE/USERS.264.893882923 File Name: +DATA/SRPSTB/DATAFILE/UNDOTBS2.262.893882949
Switch the standby database to the copy.
RMAN> switch database to copy; datafile 1 switched to datafile copy "+DATA/SRPSTB/DATAFILE/system.270.893882851" datafile 3 switched to datafile copy "+DATA/SRPSTB/DATAFILE/sysaux.268.893882883" datafile 4 switched to datafile copy "+DATA/SRPSTB/DATAFILE/undotbs1.266.893882909" datafile 5 switched to datafile copy "+DATA/SRPSTB/20E55957E61A7D6CE0537200A8C09297/DATAFILE/system.265.893882915" datafile 6 switched to datafile copy "+DATA/SRPSTB/DATAFILE/users.264.893882923" datafile 7 switched to datafile copy "+DATA/SRPSTB/20E55957E61A7D6CE0537200A8C09297/DATAFILE/sysaux.263.893882923" datafile 8 switched to datafile copy "+DATA/SRPSTB/DATAFILE/undotbs2.262.893882949" datafile 30 switched to datafile copy "+DATA/SRPSTB/243D4D0CCA302D9BE0537200A8C03F95/DATAFILE/system.315.895493381" datafile 31 switched to datafile copy "+DATA/SRPSTB/243D4D0CCA302D9BE0537200A8C03F95/DATAFILE/sysaux.316.895493429"
Now that we are done, check the progress of the recovery on the standby database.
It could be seen that now MRP is applying log sequence 180 of thread 2.
<pre>SYS@srpstb1 > select process,status,sequence#,thread# from gv$managed_standby; PROCESS STATUS SEQUENCE# THREAD# --------- ------------ ---------- ---------- ARCH CLOSING 229 1 ARCH CONNECTED 0 0 ARCH CLOSING 227 1 ARCH CLOSING 228 1 RFS CLOSING 230 1 RFS IDLE 0 0 RFS IDLE 0 0 RFS IDLE 0 0 MRP0 APPLYING_LOG 180 2 ARCH CONNECTED 0 0 ARCH CONNECTED 0 0 PROCESS STATUS SEQUENCE# THREAD# --------- ------------ ---------- ---------- ARCH CONNECTED 0 0 ARCH CONNECTED 0 0 RFS IDLE 180 2 RFS IDLE 0 0 15 rows selected.
Let’s check the sync status of the standby database with the primary database.
On the primary:
SYS@srprim1 > select thread#,max(sequence#) from v$archived_log group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 231
2 180
On the standby:
SYS@srpstb1 > select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 230
2 180
It’s clear that the standby is sync with the primary and we are happy to go !
COPYRIGHT
© Shivananda Rao P, 2012 to 2018. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Shivananda Rao and http://www.shivanandarao-oracle.com with appropriate and specific direction to the original content.
DISCLAIMER
The views expressed here are my own and do not necessarily reflect the views of any other individual, business entity, or organization. The views expressed by visitors on this blog are theirs solely and may not reflect mine.
Shivananda Rao 
Leave a Reply