Restoring the GAP Between Live & Standby Database

Rolling forward a standby database using Incremental Backup



1) On the standby database

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

  2) On the STANDBY DATABASE, find the 'lowest SCN' which will be used for the incremental backup at the primary database:

SQL> SELECT to_char(CURRENT_SCN) FROM V$DATABASE;

CURRENT_SCN
--------------
3164433 

SQL> select min(f.fhscn) from x$kcvfh f, v$datafile d where f.hxfil =d.file# and d.enabled != 'READ ONLY' ;

MIN(F.FHSCN)
----------------
3162298


3) In RMAN, connect to the PRIMARY database and create an incremental backup from the SCN derived in the previous step:

RMAN> BACKUP INCREMENTAL FROM SCN 3162298 DATABASE FORMAT '/tmp/ForStandby_%U' tag 'FORSTANDBY';

4) Transfer all backup sets created on the primary system to the standby system.

scp /tmp/ForStandby_* standby:/tmp

5) On the STANDBY catalog the backuppieces:

RMAN> CATALOG START WITH '/tmp/ForStandby'; 
RMAN> RECOVER DATABASE NOREDO; 

6) In RMAN, connect to the PRIMARY database and create a standby control file backup:

RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT                 '/tmp/ctlForStandby.bck';

7) Copy the standby control file backup to the STANDBY system. 

scp /tmp/ctlForStandby.bck standby:/tmp



8) From RMAN, connect to STANDBY database and restore the standby control file:

RMAN> SHUTDOWN IMMEDIATE ;
RMAN> STARTUP NOMOUNT; 
RMAN> RESTORE STANDBY CONTROLFILE FROM '/tmp/ctlForStandby.bck'; 

9) Shut down the STANDBY database and startup mount:

RMAN> SHUTDOWN; 
RMAN> STARTUP MOUNT;
RMAN> CATALOG START WITH '+DATA/mystd/datafile/'; 
RMAN> SWITCH DATABASE TO COPY; 

10) On standby database

  alter database open;

11) On the STANDBY database, start the recovery process

SQL> alter database recover managed standby database disconnect from session;

switch-over database roles..

On primary

select name,open_mode,database_role,db_unique_name,protection_mode from v$database;

alter database commit to switchover to standby;

startup mount;

alter database recover managed standby database disconnect from session;

select name,open_mode,database_role,db_unique_name,protection_mode from v$database;

Now go to the originally standby database

alter database commit to switchover to primary;
alter database open;
select name,open_mode,database_role,db_unique_name,protection_mode from v$database;


---------------------------------------------------------------

Errors Handled 

SQL> alter database commit to switchover to primary;

SQL> alter database commit to switchover to primary;

alter database commit to switchover to primary

*

ERROR at line 1:

ORA-16139: media recovery required

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS

--------------------

RECOVERY NEEDED

SQL> RECOVER MANAGED STANDBY DATABASE FINISH;

Media recovery complete.

SQL> alter database commit to switchover to primary;

Database altered.

----------------------------------------

If you get an error in the above command like

ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected

Did followng and worked fine for me

select switchover_status from v$database;

select sid, process, program from v$session where type = 'USER';

alter database commit to switchover to primary with session shutdown;

---------------------------------------------

Standby database configuration - 11G/12c/19c

* Archival should be Enabled to start the below configurations

 
In this Document Primary DB Unique Name is 'KANDY' and Standby DB Unique Name is 'COLOMBO'

- Execute below command on the Primary DB

alter database force logging; 

 
select FORCE_LOGGING from v$database; ( Result should be 'YES' )

alter system set log_archive_config='dg_config=(kandy,colombo)';

( kandy and colombo are the DB_UNIQUE_NAME's of the primary and standby databases)


alter system set log_archive_dest_2='service=TNS_COL LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=colombo' scope=both;

( service is tns alies in the standby db db_unique_name is standby db_unique_name )

 
alter system set log_archive_dest_state_2=enable;
alter system set remote_login_passwordfile=exclusive scope=spfile;
alter system set fal_server=TNS_COL; 

( fal_server is the TNS allies of the Standby DB)

 
alter system set db_file_name_convert='colombo','kandy' scope=spfile;

alter system set log_file_name_convert='colombo','kandy' scope=spfile;

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

-- Restart the PRIMARY DB
-- ADD TNS Entry for both DR and LIVE server accordingly


TNS_KANDY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.10.11)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = KANDY)
    )
  )

TNS_COL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.10.12)(PORT = 1521))
    )
    (CONNECT_DATA =
   (SERVICE_NAME = COLOMBO)
    )
  )



-- Take a rman backup of DB and Controlfile

RMAN --> 

backup database format '/vol0/RMAN/%U';

backup archivelog all format '/vol0/RMAN/%U';

SQL  --> 

alter database create standby controlfile as 

'/vol0/RMAN/stdcontrol.ctl';

-- Copy backups to standby db Server

scp /vol0/RMAN/* 172.16.10.12:/vol0/RMAN/

-- Copy the pwd file to standby db Server

scp /vol0/app/oracle/product/11.2.0.4/db_1/dbs/orapwKANDY 172.16.10.12:/vol0/app/oracle/product/11.2.0.4/db_1/dbs/orapwCOLOMBO


-- GO TO Standby DB Server

export ORACLE_SID=COLOMBO
-- Create a pfile and add below Entries.
-----------------------
*.compatible='11.2.0.4.0'
*.db_file_name_convert='KANDY','COLOMBO'  
*.log_file_name_convert='KANDY','COLOMBO'
*.db_name='KANDY'
*.db_unique_name='COLOMBO'
*.fal_server='TNS_COL'
*.log_archive_config='dg_config=(kandy,colombo)' 
*.log_archive_dest_2='service=TNS_KANDY LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=kandy'
*.log_archive_dest_state_2='ENABLE'
*.standby_file_management='AUTO'


-------------------------------

-- create spfile from the pfile
-- Startup nomount

RMAN --> 

Restore controlfile from '/vol0/RMAN/stdcontrol.ctl';
RMAN --> alter database mount;
RMAN --> restore database;

-- Set the size of the standby logfiles as same as the redo logfiles
-- Add standby logfile groups as much as the redo logfile groups

alter database add standby logfile thread 1 group 4 ('/vol0/oradata/COLOMBO/stnadby_redo04.log') size 50m;
alter database add standby logfile thread 1 group 5 ('/vol0/oradata/COLOMBO/stnadby_redo05.log') size 50m;
alter database add standby logfile thread 1 group 6 ('/vol0/oradata/COLOMBO/stnadby_redo06.log') size 50m;

select member from v$logfile where type ='STANDBY';
select member from v$logfile;

-- log into primary DB and create standby redo log files

alter database add standby logfile thread 1 group 4 ('/vol0/oradata/KANDY/stnadby_redo04.log') size 50m;
alter database add standby logfile thread 1 group 5 ('/vol0/oradata/KANDY/stnadby_redo05.log') size 50m;
alter database add standby logfile thread 1 group 6 ('/vol0/oradata/KANDY/stnadby_redo06.log') size 50m;

alter system set log_archive_dest_state_2=enable; 

select member from v$logfile where type ='STANDBY';
select member from v$logfile;

-- Issue the bellow command from standby DB

alter database recover managed standby database disconnect from session;

-- if you need to cancel applying, execute below command from standby DB
-- alter database recover managed standby database cancel ;

-- From the Primary db Execute below

alter system switch logfile;
select max(sequence#) from V$ARCHIVED_LOG group by thread#;
select max(sequence#) from V$ARCHIVED_LOG where applied='YES' group by thread#;