Oracle: How to roll forward standby database using archivelogs

At times oracle standby database may get out of sync with primary database. After fixing the issue causing standby out of sync, we need to roll forward standby database so that it can sync up with primary again.

To roll forward standby database, we can use archivelogs.

1. If the archivelogs are deleted from primary already.

We can restore the archivelogs from backups. Suppose our catdb stores RMAN catalog.

we can use this code to restore the archivelogs.

connect target /

connect catalog catuser/catpass@catdb

RUN {
allocate channel c1 type 'SBT_TAPE' 
parms 'ENV=(NB_ORA_CLIENT=prod1)' maxopenfiles 10;

allocate channel c2 type 'SBT_TAPE'
parms 'ENV=(NB_ORA_CLIENT=prod2)' maxopenfiles 10;
restore archivelog from time 'sysdate - 1' until time 'sysdate';

release channel c1;
release channel c2;

}

Depending on how long the standby has been out of sync, we can change the 'from time' accordingly. 

2. If the archivelogs are still on primary database.

We can simply copy them to the standby database,
after copying archivelogs to the standby, we need to catalog the files copied over.

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

Otherwise oracle cannot recognize these files.

Once we have the archivelogs, either through method 1 or method 2,
we can roll forward the standby database.

SQL> recover managed standby database disconnect;

Oracle: How to setup physical standby database

In production environment, It's often required to have a physical standby database. In case the primary environment is gone, we can continue serve customer requests using standby database.

There are a few ways to set up oracle standby database, In any ways, please make sure you have the following ready:

• remote login is enabled, both primary and standby SYS password are the same, this can be set using orapwd.
• log shipping is enabled on primary, define the proper TNS entry, set log_archive_dest_x, and set log_archive_dest_state_x to enable.
• The pfile or spfile is ready on standby database.

1. Using storage level replication
On primary database, put the database in backup mode. This is to make sure the copy replicated to standby environment is consistent.

SQL> alter database begin backup;

In storage, split the replication. Stop backup mode on primary database:

SQL> alter database end backup;

Create standby controlfiile:

RMAN> backup current controlfile for standby format '/tmp/stdby_ctrol_%U';

Copy the controlfile backup to standby servers, restore the controlfile.

SQL> startup nomoun;

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

RMAN> restore controlfile from '/tmp/stdby_ctrol_blahblah';

Mount the standby database, and enable redo apply.

SQL> alter database mount standby database;

SQL> recover managed standby database disconnect;

Note: Please make sure do NOT sync back the standby environment on storage level again, otherwise it will destroy the standby database.

2. Using RMAN duplicate.

connect to primary database and standby databse.

$ rman target / AUXILIARY SYS/sys_pwd@sbdb

RMAN> DUPLICATE TARGET DATABASE FOR STANDBY NOFILENAMECHECK;