How Data Guard Benefited with RMAN 12c New Features

We know there are 500+ features introduced with Oracle 12c and Multitenant, Indeed there are few new features with RMAN and in this article we are going to see how the 12c RMAN new features are going to benefit to the Data Guard and how really they are helpful in terms of loss of datafiles and quick recovery on standby databases.

Loss of Data files in Data Guard Configuration

Before starting with 12c RMAN new features and how they are going simplify restore and recovery in Data Guard configuration, we will discuss how the restore and recovery actually was in prior to 12c. For example if any datafile loss on standby database then

1. Take backup of datafile or image copy on primary
2. Copy the backup remotely to the standby database
3. Stop MRP on standby
4. Restore datafile on standby
5. Do recover

Indeed there are lots of efforts involved in such scenarios and of course this procedure is same if we lose the datafile either in primary or standby.

 

Now the picture has changed, we can directly issue restore data file from the problematic database by using the Oracle Net service. We will see demo how actually it works.

Loss of Standby CDB datafile

We have primary with PDB and standby with PDB open and read only status; Of course there is no backup available of data file on standby.

SQL> select name,database_role from v$database;

NAME     DATABASE_ROLE

——— —————-

MCDB     PHYSICAL STANDBY

SQL> select file#,name from v$datafile where con_id=1;

     FILE# NAME

———- ————————————————–

        1 /u02/app/oracle/oradata/mcdb/system01.dbf

         3 /u02/app/oracle/oradata/mcdb/sysaux01.dbf

         4 /u02/app/oracle/oradata/mcdb/undotbs01.dbf

         6 /u02/app/oracle/oradata/mcdb/users01.dbf

       11 /u02/app/oracle/oradata/mcdb/rdgcdb01.dbf

RMAN> list backup of datafile 11;

using target database control file instead of recovery catalog

specification does not match any backup in the repository

RMAN>

Now we will remove the datafile manually from Linux and we will fetch the active failures of database.

-bash-3.2$ ls -ltr /u02/app/oracle/oradata/mcdb/rdgcdb01.dbf

-rw-r—– 1 oracle oinstall 104865792 Jul 9 14:54 /u02/app/oracle/oradata/mcdb/rdgcdb01.dbf

-bash-3.2$ rm /u02/app/oracle/oradata/mcdb/rdgcdb01.dbf

-bash-3.2$ ls -ltr /u02/app/oracle/oradata/mcdb/rdgcdb01.dbf

ls: /u02/app/oracle/oradata/mcdb/rdgcdb01.dbf: No such file or directory

-bash-3.2$
Without notice of standby database we have deleted the datafile, Now when we run the “List failure” then physical standby database reported with critical priority and stated one or more non-system datafiles are unavailable.

RMAN> list failure;

using target database control file instead of recovery catalog

Database Role: PHYSICAL STANDBY

List of Database Failures

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

Failure ID Priority Status   Time Detected Summary

———- ——– ——— ————- ——-

283       CRITICAL OPEN     09-JUL-15     One or more non-system datafiles are missing

262       HIGH     OPEN     09-JUL-15     Datafiles are mutually inconsistent

RMAN>

We can also check the status of data file from SQL level and it clears that database unable to read or open file.

SQL> select file#,status,error from v$datafile_header where file#=11;

     FILE# STATUS ERROR

———- ——- ————————-

       11 ONLINE CANNOT OPEN FILE

SQL>

If you are using Data Guard broker, then we can get the status report of physical standby database and broker of course it can measure the issue.

DGMGRL> show database india statusreport

STATUS REPORT

       INSTANCE_NAME   SEVERITY ERROR_TEXT

                   *     ERROR ORA-16839: one or more user data files are missing

DGMGRL>

In order to resolve the ongoing issue, we have to ensure there is no recovery (MRP) is in progress or else we will encounter into “exclusive enqueue” or "Flashback or Recovery enabled". Usually the MRP will be terminated with error opening the datafile as missing with status " MRP0: Background Media Recovery terminated with error 1110", If in case the MRP is running without impact then consider to stop MRP manually.

DGMGRL> edit database india set state='APPLY-OFF';

Succeeded.

DGMGRL>

As I said above, to restore the data file of standby we use the primary Oracle net service to perform backup through network. In this article the primary database service as “CANADA” and standby service name acts as “INDIA”, the commands are very simple and no more lengthy procedures. When we issue command “restore datafile xx from service Canada”, internally RMAN performs backup and network copy and itself performs the restore of datafile. We have mentioned the data file 11 which is missing/removed above.

RMAN> restore datafile 11 from service canada;

Starting restore at 09-JUL-15

Starting implicit crosscheck backup at 09-JUL-15

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=39 device type=DISK

Crosschecked 8 objects

Finished implicit crosscheck backup at 09-JUL-15

Starting implicit crosscheck copy at 09-JUL-15

using channel ORA_DISK_1

Finished implicit crosscheck copy at 09-JUL-15

searching for all files in the recovery area

cataloging files…

cataloging done

List of Cataloged Files

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

File Name: /u02/app/oracle/fast_recovery_area/INDIA/archivelog/2015_07_09/o1_mf_1_49_bswk0n9z_.arc

File Name: /u02/app/oracle/fast_recovery_area/INDIA/archivelog/2015_07_09/o1_mf_1_50_bswl3g41_.arc

File Name: /u02/app/oracle/fast_recovery_area/INDIA/archivelog/2015_07_09/o1_mf_1_48_bsw7rzjo_.arc

File Name: /u02/app/oracle/fast_recovery_area/INDIA/archivelog/2015_07_09/o1_mf_1_47_bsw7lnyr_.arc

File Name: /u02/app/oracle/fast_recovery_area/INDIA/archivelog/2015_07_09/o1_mf_1_52_bswl3kco_.arc

File Name: /u02/app/oracle/fast_recovery_area/INDIA/archivelog/2015_07_09/o1_mf_1_51_bswl3gw2_.arc

File Name: /u02/app/oracle/fast_recovery_area/INDIA/archivelog/2015_07_09/o1_mf_1_55_bswlj16y_.arc

File Name: /u02/app/oracle/fast_recovery_area/INDIA/archivelog/2015_07_09/o1_mf_1_54_bswlhv3f_.arc

File Name: /u02/app/oracle/fast_recovery_area/INDIA/archivelog/2015_07_09/o1_mf_1_53_bswlhsj3_.arc

File Name: /u02/app/oracle/fast_recovery_area/INDIA/archivelog/2015_07_07/o1_mf_1_41_bsqzponv_.arc

File Name: /u02/app/oracle/fast_recovery_area/INDIA/archivelog/2015_07_07/o1_mf_1_42_bsqzwnxx_.arc

File Name: /u02/app/oracle/fast_recovery_area/INDIA/archivelog/2015_07_07/o1_mf_1_43_bsr00swv_.arc

File Name: /u02/app/oracle/fast_recovery_area/INDIA/archivelog/2015_07_07/o1_mf_1_39_bsq6dzhg_.arc

File Name: /u02/app/oracle/fast_recovery_area/INDIA/archivelog/2015_07_07/o1_mf_1_40_bsq6vcqg_.arc

File Name: /u02/app/oracle/fast_recovery_area/INDIA/archivelog/2015_04_18/o1_mf_1_28_bm4fy09p_.arc

File Name: /u02/app/oracle/fast_recovery_area/INDIA/archivelog/2015_04_18/o1_mf_1_25_bm4fymrk_.arc

File Name: /u02/app/oracle/fast_recovery_area/INDIA/archivelog/2015_07_08/o1_mf_1_46_bsspb044_.arc

File Name: /u02/app/oracle/fast_recovery_area/INDIA/archivelog/2015_07_08/o1_mf_1_45_bssp9xgx_.arc

File Name: /u02/app/oracle/fast_recovery_area/INDIA/archivelog/2015_07_08/o1_mf_1_44_bssp9s4l_.arc

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: using network backup set from service canada

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

channel ORA_DISK_1: restoring datafile 00011 to /u02/app/oracle/oradata/mcdb/rdgcdb01.dbf

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

Finished restore at 09-JUL-15

RMAN>

From the RMAN output it is clear that data file 11 is restored successfully, now we are safe to start recovery further.

DGMGRL> edit database india set state='APPLY-ON';

Succeeded.

DGMGRL>

Now we can crosscheck what is the status of the datafile and from output there are no errors reported and status is “ONLINE”

SQL> select file#,status,error from v$datafile_header where file#=11

     FILE# STATUS ERROR

———- ——- ————

       11 ONLINE

SQL>

Furthermore we can enable debug/trace of the RMAN to see what packages used in order to perform the RMAN restore through network. (just for reference)

DBGSQL:           TARGET> begin sys.dbms_backup_restore.networkReadFileHeader( service => :service , dfnumber => :fno, blksize => :blksize, blocks   => :blocks, crescn   => :crescn, rlgscn   => :rlgscn, ckpscn   => :ckpscn, afzscn   => :afzscn, rfzscn   => :rfzscn, fhdbi   => :fhdbi, fhfdbi   => :fhfdbi, fhplus   => :fhplus); end;

During the Network restore process many internal steps will be involved such as

• Version compatibility
• Setting RMAN Status Row
• Creating RMAN status Row
• Device allocation, parsing restore query, setting maxsize, channel info, parallel, max read bytes,
• Network read file header – Reading remote datafile header
• Backup the datafile
• Restore the backup piece

Loss of Primary CDB datafile

Likewise we restored the data file on standby we can do in same manner if in case of primary data file lost. Below is the brief log how to restore the data file upon lost.

SQL> select database_role from v$database;

DATABASE_ROLE

—————-

PRIMARY

SQL>

SQL> select file#,name from v$datafile;

     FILE# NAME

———- ————————————————————

         1 /u02/app/oracle/oradata/mcdb/system01.dbf

         3 /u02/app/oracle/oradata/mcdb/sysaux01.dbf

         4 /u02/app/oracle/oradata/mcdb/undotbs01.dbf

         5 /u02/app/oracle/oradata/mcdb/pdbseed/system01.dbf

         6 /u02/app/oracle/oradata/mcdb/users01.dbf

         7 /u02/app/oracle/oradata/mcdb/pdbseed/sysaux01.dbf

         8 /u02/app/oracle/oradata/mcdb/mpdb/system01.dbf

         9 /u02/app/oracle/oradata/mcdb/mpdb/sysaux01.dbf

       10 /u02/app/oracle/oradata/mcdb/mpdb/mpdb_users01.dbf

       11 /u02/app/oracle/oradata/mcdb/rdgcdb01.dbf

10 rows selected.

 

SQL> !ls -ltr /u02/app/oracle/oradata/mcdb/users01.dbf

-rw-r—– 1 oracle oinstall 5251072 Jul 11 12:41 /u02/app/oracle/oradata/mcdb/users01.dbf

SQL> !rm /u02/app/oracle/oradata/mcdb/users01.dbf

SQL> !ls -ltr /u02/app/oracle/oradata/mcdb/users01.dbf

ls: /u02/app/oracle/oradata/mcdb/users01.dbf: No such file or directory

SQL>

 

RMAN> sql 'alter database datafile 6 offline';

using target database control file instead of recovery catalog

sql statement: alter database datafile 6 offline

RMAN> restore datafile 6 from service india;

Starting restore at 11-JUL-15

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=75 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: using network backup set from service india

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

channel ORA_DISK_1: restoring datafile 00006 to /u02/app/oracle/oradata/mcdb/users01.dbf

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

Finished restore at 11-JUL-15

RMAN>

 

RMAN> recover datafile 6;

Starting recover at 11-JUL-15

using channel ORA_DISK_1

starting media recovery

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

Finished recover at 11-JUL-15

 

RMAN> sql 'alter database datafile 6 online';

sql statement: alter database datafile 6 online

RMAN>

The New Generation – Incremental Roll Forward of Standby

Incremental roll forward technique is widely used much, It's an great technique to sync the standby database if in case of un-resolvable gaps. In detail if suppose the generated archives on primary were deleted or corrupted on primary database before they transmitted to standby database then standby database will be stalled. In this cases prior to 10g versions we have to rebuild whole standby database in order to function the standby database. In later releases the Incremental roll forward technique makes life easier by taking incremental backup from SCN of standby and then do recover on standby and indeed there are more manual steps involved in taking backup, copy and recover. From 12c Oracle more simplified the process of Roll Forward in case of un-resolvable gaps. The below demonstration will explains how to use new feature of 12c.

 

When there is gap on standby which cannot be resolved, then we can use the view "v$archive_gap" to check what is the last sequence available and what is the high sequence available on standby system, By that we can measure as the archive log sequences 147 and 148 are missing on standby. Please note that this view may not always says you truth, there are several bugs associated with this view in even 11gRx but hopefully not in 12c 😉

SQL> select * from v$archive_gap;

   THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#     CON_ID

———- ————- ————– ———-

         1           146           149         1

SQL

We can also check the GAP status from Broker if available in configuration,

DGMGRL> show configuration

Configuration – ckpt12c

Protection Mode: MaxPerformance

Databases:

canada – Primary database

   Error: ORA-16724: cannot resolve gap for one or more standby databases

   india – Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

ERROR

DGMGRL>

Before jumping into how to fix the issue, its very much necessary to gather details like how much gap we actually have. If in case there are days of gap between primary and standby then depending on the database size sometimes it's better to go with full database restore. Because with huge data transfer over network again it will be burden to Network/bandwidth.

To know how much lag on standby and to compare with primary database, the best view is v$database to know the current scn of database, From below example we have current scn output from both primary database and standby database and then need to convert to timestamp to know the difference in time stamp. Based on timestamp we can check in v$archived_log with FIRST_CHANGE# and NEXT_CHANGE# in order to identify which archive log sequence standby is required. But these calculations are not required with 12c anymore, Because Oracle can estimate the required SCN.

 

Primary

SQL> select current_scn from v$database;

CURRENT_SCN

———–

   2176549

SQL> select scn_to_timestamp(2176549) as timestamp from dual;

TIMESTAMP

—————————————————————————

09-JUL-15 08.07.40.000000000 PM

SQL>

 

Standby

SQL> select current_scn from v$database;

CURRENT_SCN

———–

   2176231

SQL> select scn_to_timestamp(2176231) as timestamp from dual;

TIMESTAMP

—————————————————————————

09-JUL-15 08.05.22.000000000 PM

SQL>

To know only the apply lag time then we can directly use the view "v$dataguard_stats" and the results shows as 14 minutes of Lag.

SQL> select name,value from v$dataguard_stats;

NAME                             VALUE

——————————– ——————–

transport lag                   +00 00:14:54

apply lag                       +00 00:14:54

apply finish time

estimated startup time           16

SQL>

Now we will work on actual incremental roll forward over physical standby using new 12c RMAN features, In order to perform RMAN recovery it is mandatory to cancel the MRP if its running.

DGMGRL> edit database india set state='APPLY-OFF';

Succeeded.

DGMGRL>

If the standby database is in Open/Read-Only Mode, then ensure standby database is in Mount status to perform recovery.

RMAN> shutdown immediate;

database closed

database dismounted

Oracle instance shut down

 

RMAN> startup mount

connected to target database (not started)

Oracle instance started

database mounted

Total System Global Area     730714112 bytes

Fixed Size                    2292672 bytes

Variable Size               583009344 bytes

Database Buffers             142606336 bytes

Redo Buffers                   2805760 bytes

Get the list of the datafiles and tempfiles using "Report Schema" , it will be useful to compare the standby after we restore the controlfile from primary database.

List of Permanent Datafiles

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

File Size(MB) Tablespace           RB segs Datafile Name

—- ——– ——————– ——- ————————

1   790     SYSTEM               ***     /u02/app/oracle/oradata/mcdb/system01.dbf

3   800     SYSAUX               ***     /u02/app/oracle/oradata/mcdb/sysaux01.dbf

4   240     UNDOTBS1             ***     /u02/app/oracle/oradata/mcdb/undotbs01.dbf

5   260     PDB$SEED:SYSTEM     ***     /u02/app/oracle/oradata/mcdb/pdbseed/system01.dbf

6   5        USERS               ***     /u02/app/oracle/oradata/mcdb/users01.dbf

7   640     PDB$SEED:SYSAUX     ***     /u02/app/oracle/oradata/mcdb/pdbseed/sysaux01.dbf

8   260     MPDB:SYSTEM         ***     /u02/app/oracle/oradata/mcdb/mpdb/system01.dbf

9   670     MPDB:SYSAUX         ***     /u02/app/oracle/oradata/mcdb/mpdb/sysaux01.dbf

10   5       MPDB:USERS           ***     /u02/app/oracle/oradata/mcdb/mpdb/mpdb_users01.dbf

11   100     RDGCDB               ***     /u02/app/oracle/oradata/mcdb/rdgcdb01.dbf

 

List of Temporary Files

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

File Size(MB) Tablespace           Maxsize(MB) Tempfile Name

—- ——– ——————– ———– ——————–

1   88       TEMP                 32767       /u02/app/oracle/oradata/mcdb/temp01.dbf

2   87       PDB$SEED:TEMP       32767       /u02/app/oracle/oradata/mcdb/pdbseed/pdbseed_temp01.dbf

3   20       MPDB:TEMP           32767       /u02/app/oracle/oradata/mcdb/mpdb/temp01.dbf

Now we are set to start performing recover the physical standby database, then RMAN will perform the incremental network backup set using the service we have mentioned. To perform this command no backup required either on primary or standby database.

Please note that RMAN we have to connect using TNS service: $ rman target sys/oroacle@india.

RMAN> recover database from service canada noredo;

Starting recover at 09-JUL-15

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=21 device type=DISK

skipping datafile 5; already restored to SCN 1913352

skipping datafile 7; already restored to SCN 1913352

channel ORA_DISK_1: starting incremental datafile backup set restore

channel ORA_DISK_1: using network backup set from service canada

destination for restore of datafile 00001: /u02/app/oracle/oradata/mcdb/system01.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 network backup set from service canada

destination for restore of datafile 00003: /u02/app/oracle/oradata/mcdb/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 network backup set from service canada

destination for restore of datafile 00004: /u02/app/oracle/oradata/mcdb/undotbs01.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 network backup set from service canada

destination for restore of datafile 00006: /u02/app/oracle/oradata/mcdb/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 network backup set from service canada

destination for restore of datafile 00008: /u02/app/oracle/oradata/mcdb/mpdb/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 network backup set from service canada

destination for restore of datafile 00009: /u02/app/oracle/oradata/mcdb/mpdb/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 network backup set from service canada

destination for restore of datafile 00010: /u02/app/oracle/oradata/mcdb/mpdb/mpdb_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 network backup set from service canada

destination for restore of datafile 00011: /u02/app/oracle/oradata/mcdb/rdgcdb01.dbf

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

Finished recover at 09-JUL-15

RMAN>

Don't think that your job done 🙂 Still you need to restore the controlfile. Again Oracle made life easy so that we can restore controlfile again using the primary database oracle net service as below. Before restoring the control file ensure the instance is in NoMount status.

RMAN> shutdown immediate;

database dismounted

Oracle instance shut down

 

RMAN> startup nomount

connected to target database (not started)

Oracle instance started

Total System Global Area     730714112 bytes

Fixed Size                     2292672 bytes

Variable Size               574620736 bytes

Database Buffers             150994944 bytes

Redo Buffers                   2805760 bytes

 

RMAN> restore standby controlfile from service canada;

Starting restore at 09-JUL-15

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=21 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: using network backup set from service canada

channel ORA_DISK_1: restoring control file

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

output file name=/u02/app/oracle/oradata/mcdb/control01.ctl

output file name=/u02/app/oracle/fast_recovery_area/mcdb/control02.ctl

Finished restore at 09-JUL-15

 

RMAN> shutdown immediate;

Oracle instance shut down

 

RMAN> startup mount

connected to target database (not started)

Oracle instance started

database mounted

Total System Global Area     730714112 bytes

Fixed Size                     2292672 bytes

Variable Size               578815040 bytes

Database Buffers             146800640 bytes

Redo Buffers                   2805760 bytes

RMAN>

After restoring the control file, do not forget to put database in Mount or Open status as per the configuration and we can start recovery/MRP on standby database.

DGMGRL> edit database india set state='APPLY-ON';

Succeeded.

DGMGRL>

Now standby is able to apply the fresh archive logs of primary and waiting for the new sequence.

Thu Jul 09 20:16:54 2015

Media Recovery Log /u02/app/oracle/fast_recovery_area/INDIA/archivelog/2015_07_09/o1_mf_1_156_bsx2bt76_.arc

Thu Jul 09 20:16:54 2015

Media Recovery Log /u02/app/oracle/fast_recovery_area/INDIA/archivelog/2015_07_09/o1_mf_1_157_bsx2bx85_.arc

Media Recovery Waiting for thread 1 sequence 158 (in transit)

We can consider to review the view to check if there is any gaps still persists. Of course it won't be there but just in case and we can also crosscheck the last applied SCN on standby.

SQL> select * from v$archive_gap;

no rows selected

SQL> select current_scn from v$database;

CURRENT_SCN

———–

   2178111

SQL>

SQL> select scn_to_timestamp(2178111) as timestamp from dual;

TIMESTAMP

—————————————————————————

09-JUL-15 08.16.04.000000000 PM

SQL>

SQL> !date

Thu Jul 9 20:17:25 IST 2015

Finally, After all above steps of Incremental roll forward you may have to adjust the redo log files and standby redo log files

Thu Jul 09 20:18:40 2015

Errors in file /u01/app/oracle/diag/rdbms/india/drmcdb/trace/drmcdb_rsm0_19524.trc:

ORA-00313: open failed for members of log group 6 of thread 0

ORA-00312: online log 6 thread 0: '/u02/app/oracle/oradata/mcdb/sredo03.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

Because if we have performed duplicate then by using LOG_FILE_NAME_CONVERT by default the redo log files used to create in the desired locations, But in this technique we have restored control file of primary and hence you have to drop and recreate the redo logs.

Conclusion

We have seen how RMAN 12c new features made life easier to Data Guard to perform fast data files restore in case of corruption or missing and how the incremental roll forward technique is simplified and lifted out the traditional lengthy and time taking methods in order to fix the un-resolvable gaps with step by step demo and in detail.

References:

http://docs.oracle.com/database/121/BRADV/rcmadvre.htm#CACEGAGJ