Sometimes standby database lags far behind from the primary database leading to Archive Gap.  There are some reasons could be for that such as

  1. Network issue between the primary and the standby database leading to the archive gaps.
  2. Standby archive log dest may be full or Standby db disk becomes full so the primary and the standby database leading to the archive gaps.

In those cases(or some other cases) where the standby lags far behind from the primary database, incremental backup is very useful to roll forward the physical standby database to have it in sync with the primary database.

Step 1:
Check the current SCN on primary database

SQL> select status,instance_name,database_role from v$database,v$instance;
 
STATUS        INSTANCE_NAME    DATABASE_ROLE
------------ ---------------- ----------------
OPEN           ORCL            PRIMARY
 
column current_scn format 99999999999999999999999999
SQL> select current_scn from v$database;
CURRENT_SCN
------------
2289543148

Step 2:
Check the current SCN on a standby database

SQL> select status,instance_name,database_role from v$database,v$instance;
 
STATUS        INSTANCE_NAME   DATABASE_ROLE
------------ ---------------- ----------------
MOUNTED       ORCLDR           PHYSICAL STANDBY
 
column current_scn format 99999999999999999999999999
SQL> select current_scn from v$database;

CURRENT_SCN
------------
2289542180

As you can see there is a difference between two SCN

Step 3:
Cancel the Managed Recovery Process on the Standby database.

SQL>Alter database recover managed standby database cancel;

Step 4:
On the Primary database, take the incremental SCN backup from the SCN that is currently recorded on the standby database. In our SCN number is 2289542180

oracle@myhost$ rman target /

RMAN> RUN
{
SET command id TO 'destotp_dr_inc';
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE DEVICE TYPE disk PARALLELISM 4;
BACKUP as compressed backupset INCREMENTAL from scn 2289542180 database format '/tmp/inc_bckp/ORCL_inc_bckp_%s';
}

Step 5:
When backup has been done, copy backup to standby server by using scp command

scp /tmp/inc_bckp/ORCL_inc* oracle@192.XX.XX:/tmp/inc_bckp

Step 6:
Create standby control on Primary and also copy this file to standby server

oracle@myhost$ sqlplus "/as sysdba"
SQL> Alter database create standby controlfile as '/tmp/inc_bckp/standby.ctl';

scp /tmp/inc_bckp/standby* oracle@192.XX.XX:/tmp/inc_bckp

Step 7:
On standby check current contfolfile location

oracle@myhost_dr$ sqlplus "/as sysdba"

SQL> SELECT NAME FROM V$CONTROLFILE;
NAME
-------------------------------------
/u01/oracle/ORCLDR/control01.ctl

Step 8:
Close to standby db

SQL> shutdown immediate;

Step 9:
On standby server rename or delete existing control files by using new control file

oracle@myhost_dr$ mv /u01/oracle/ORCLDR/control01.ctl /u01/oracle/ORCLDR/control01.ctl_old
oracle@myhost_dr$ cp /tmp/inc_bckp/standby.ctl /u01/oracle/ORCLDR/control01.ctl

Step 9:
Open standby db in mount mode

SQL> startup mount

Step 10:
Catalog backups on standby server.Than recover database until ORA-00334 error. This error can be ignored safelty

oracle@myhost_dr$ rman target /

RMAN> catalog start with '/tmp/inc_bckp/';

RMAN> recover database;

Possible errors:

Error 1:
RMAN> recover database;

Starting recover at 23-MAR-18
using channel ORA_DISK_1
using channel ORA_DISK_2

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/23/2018 09:34:13
RMAN-06094: datafile 4 must be restored

Check what is datafile 4 by using below query

col name format a60
set linesize 1000
set pagesize 0
select file#, name from v$datafile order by file# ;

On primary db:

RMAN > BACKUP DATAFILE 4 FORMAT '/tmp/inc_bckp/Datafile4.bck';

copy /tmp/inc_bckp/Datafile4.bck to standby

catalog it

RMAN> catalog start with '/tmp/inc_bckp/';

than

RMAN> recover database;

Error 2:
If some archivelog has been backuped, than you may restore it by using below syntax

RUN
{ 
ALLOCATE CHANNEL ch1 DEVICE TYPE DISK;
restore archivelog from sequence 89809 high sequence 89810 thread 1;
}

or

RUN
{ 
ALLOCATE CHANNEL ch1 DEVICE TYPE DISK;
restore archivelog from logseq=89809 until logseq=89809 thread 1;
}
Advertisements