Advertisements
Skip to content
December 18, 2015 / Shivananda Rao P

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, 2013, 2014, 2015. 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.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: