OracleDBWorld

How to Take Incremental Backup and Roll Forward Standby Database in Oracle

Posted on by N Namrata

How often you see archive missing from production which is not shipped to Standby database ? Rare right? But you will come across this situation once for sure when due to re-org you were forced to delete archives / due to network disconnection archive didn’t got shipped properly and due to automation job archive got deleted from production. Hence we are here to explain how to Rolling Forward a Physical Standby Database using RMAN Incremental Backup. Lets know how to take Incremental Backup and Roll Forward Standby Database in easy break down steps.

Content
Basic Pre-requisite
How to take incremental Backup and Recover your Standby database

Here is test case environment ->

ORACLE VERSION -> 11.2.0.4
SINGLE INSTANCE PRODUCTION and SINGLE INSTANCE STANDBY
OS -> RHEL 6

Basic Pre-requisite

It’s always better to check the alert logs and identify what caused the issue. Before starting and taking the Incremental backup. This will help you to narrow down the problem and make a proper action plan for it.

Pre-requisite for the activity

Let me list all the pre-requisites –

  1. Always take control file backup at Standby before restoring the control file from Primary on standby database.
  2. Space is available to take incremental backup on source and destination server
  3. Space available on database mount point to apply the incremental at Standby side.
  4. Output of following sqls from Primary and Standby database spooled. 
select name from v$datafile;
select group#,type,member from v$logfile order by group#;
select name from v$controlfile;

Lets get started with How to Take Incremental Backup and Roll Forward Standby Database in Oracle

How to Take Incremental Backup and Roll Forward Standby Database in Oracle

Step 1 Always keep details of location of datafiles, redo logs, control files before doing any changes in standby control file. For below instance, you might have noticed that datafile locations and redolog locations and control file locations are exact the same hence there is no additional step involved post restore of the control file.

Production --> 

SQL> select name from v$datafile;

NAME                                      
--------------------------------------------
/sansui/datafiles/SYSTEM.dbf              
/sansui/datafiles/SYSAUX.dbf             
/sansui/datafiles/UNDOTBS1.dbf        
/sansui/datafiles/USERS.dbf


SQL> select group#,type,member from v$logfile order by group#;

    GROUP# TYPE    MEMBER                                      
---------- ------- --------------------------------------------
         1 ONLINE  /sansui/redo/redo1.log                      
         2 ONLINE  /sansui/redo/redo2.log                      
         3 ONLINE  /sansui/redo/redo3.log                      
         4 STANDBY /sansui/redo/standby_redo1.log              
         5 STANDBY /sansui/redo/standby_redo2.log              
         6 STANDBY /sansui/redo/standby_redo3.log              
         7 STANDBY /sansui/redo/standby_redo4.log              

SQL> select name from v$controlfile;

NAME                                      
--------------------------------------------
/sansui/control/control.ctl


Standby Database --> 


SQL> select name from v$datafile;

NAME                                      
--------------------------------------------
/sansui/datafiles/SYSTEM.dbf              
/sansui/datafiles/SYSAUX.dbf             
/sansui/datafiles/UNDOTBS1.dbf        
/sansui/datafiles/USERS.dbf


SQL> select group#,type,member from v$logfile order by group#;

    GROUP# TYPE    MEMBER                                      
---------- ------- --------------------------------------------
         1 ONLINE  /sansui/redo/redo1.log                      
         2 ONLINE  /sansui/redo/redo2.log                      
         3 ONLINE  /sansui/redo/redo3.log                      
         4 STANDBY /sansui/redo/standby_redo1.log              
         5 STANDBY /sansui/redo/standby_redo2.log              
         6 STANDBY /sansui/redo/standby_redo3.log              
         7 STANDBY /sansui/redo/standby_redo4.log              

SQL> select name from v$controlfile;

NAME                                      
--------------------------------------------
/sansui/control/control.ctl

Step 2 Here, we have to make sure there is no datafile count mis-match. In case you find the difference then click here

Production -->

SQL> select count(1) from v$datafile;

COUNT(1)
---------
        4

Standby -->

SQL> select count(1) from v$datafile;

COUNT(1)
---------
        4

Since it was a test machine, where I deleted 10 archives from production server which were not shipped to standby.

Roll Forward Standby Database – Actual Activity Start here

Step 3 Cancel the Recovery on Standby database –

Before Applying Incremental Always remember to Cancel the Recovery on Standby database. This can be done just before applying the increment. It will be your choice. There is no lapses if you cancel the recovery before as well.


SQL> select name, open_mode, database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
sansui    MOUNTED              PHYSICAL STANDBY


SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Step 4 We have to check till what SCN our standby database is in sync.

We always take the lower value from output of below SQLs. For instance, lower value is 10253.

SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN
--------------
         10284

select min(checkpoint_change#) from v$datafile_header where file# not in (select file# from v$datafile where enabled = 'READ ONLY');

MIN(CHEC
---------
    10253

Step 5 Now it’s time to take incremental backup from Production.

Oracle Recommends to take the lower value of SCN . Though I usually take the scn number as (whatever value I got from above SQL – 50 )

In my case Minimum Value for SCN was 10253, I will take 10203. 

Compressed backup --> 
RMAN> BACKUP AS COMPRESSED BACKUPSET INCREMENTAL FROM SCN 10203 DATABASE FORMAT '/RMANDISK/Incre_6122020_Standby_%U';
RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/RMANDISK/Incre_Control';

OR

Non Compressed backup --> 
RMAN> BACKUP INCREMENTAL FROM SCN 10203 DATABASE FORMAT '/RMANDISK/Incre_6122020_Standby_%U';
RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/RMANDISK/Incre_Control';

Step 6 Transfer the backup to Standby server

Each client have their own way to do file transfers arcoss the server. Many has a separate team which handles it for you. Since it is my Test Machine, I had all ports open

scp /RMANDISK/Incre_* oracledbworld@192.168.1.7:/RMANDISK

Step 7 Catalog the backup Pieces

Since we all know control file will have the information of backup pieces at primary side. Though it will not be there on your standby. So Now you have to catalog the backup pieces so that standby database knows about the backup.

Since I have copied the files in /RMANDISK hence I am cataloging that location. It’s always better to make a separate folder for backup and separate folder to move the backup at Standby side. So it will not take time to catalog. 

RMAN> CATALOG START WITH '/RMANDISK';

using target database control file instead of recovery catalog
searching for all files that match the pattern /RMANDISK

List of Files Unknown to the Database
=====================================
File Name: /RMANDISK/Incre_6122020_Standby_07slss4_2_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: /RMANDISK/Incre_6122020_Standby_07slss4_2_1

Step 8 Recover the standby database using Incremental backup from production.

RMAN> RECOVER DATABASE NOREDO;

starting recover at 06-DEC-20
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: /sansui/datafiles/SYSTEM.dbf  
destination for restore of datafile 00002: /sansui/datafiles/SYSAUX.dbf  
destination for restore of datafile 00003: /sansui/datafiles/UNDOTBS1.dbf
destination for restore of datafile 00004: /sansui/datafiles/USERS.dbf
channel ORA_DISK_1: reading from backup piece /RMANDISK/Incre_6122020_Standby_07slss4_2_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/RMANDISK/Incre_6122020_Standby_07slss4_2_1 tag=FORSTANDBY
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
Finished recover at 06-DEC-20

Step 9 Prechecks information

It’s always better to capture the details and compare initially so you will have a clear idea of what neeeds to be done. In my case standby and primary had exactly same naming convention. In case you are using ASM /OMF please click here ->

Production and Standby check for following --> And make a spool of it. 
select name from v$datafile;
select group#,type,member from v$logfile order by group#;
select name from v$controlfile;
Backup control file on standby

Step 10 Backup Control file on DR

RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/RMANDISK/Before_Incre_Control_Standby_06122020';

Step 11 Restore the control file whose backup you took from Primary database.

Never forget to check the status of the database and to make sure you are connected to desired database. You will have multiple databases on same server in many cases. In that case this habit will come handy

Standby –>

SQL> select name, open_mode, database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
sansui    MOUNTED              PHYSICAL STANDBY

Shutdown the database

SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP NOMOUNT 

RMAN> RESTORE STANDBY CONTROLFILE FROM '/RMANDISK/Incre_Control';

Starting restore at 06-DEC-20
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=/sansui/control/control.ctl
Finished restore at 06-DEC-20

Step 12 Mount the database – 

SQL> alter database mount

Step 13 Recreate redo logs to new location ( if you have different location ) [ Not mandatory ]

Refer -> https://www.oracledbworld.com/oracle-dba/how-to-recreate-online-redo-logs-in-oracle/

or

You can rename to the new location. During switchover/Activation it will automatically clear them.

SQL>alter database rename file '<currentlocation>' to '<newlocation>';

Step 14 Recreate standby redo logs to new location ( if you have different location ) [ Not mandatory ]

Refer -> https://www.oracledbworld.com/oracle-dba/how-to-recreate-standby-redo-logs-on-standby-database/

If location is same and everything is proper then, 

Clear all standby redo logs
SQL> select GROUP# from v$logfile where TYPE='STANDBY' group by GROUP#;
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 4;
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 5;
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 6;
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 7;

Step 15 Start the recovery –>

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Once database is in sync, cancel the recovery, open the database in Read Only ( If your database was in read only previously ) And start the recovery again.

Reference –> Steps to perform for Rolling Forward a Physical Standby Database using RMAN Incremental Backup. (Doc ID 836986.1)

Hints and Tips

  1. If you are using 12c or above then you have option to do incremental over the network with compression option. click here –>
  2. You can also use network in 11g database to apply the incremental.

Leave a Reply

Your email address will not be published. Required fields are marked *