Rolling forward standby database when archives missing in primary in 12c DBACLASS

There can be situation , in dataguard setup, where archive logs are missing from primary , before it was being shipped to standby . Till 11g , we can make standby db sync with primary by taking an incremental backup from primary and apply the same in standby . But with 12c , a new feature has been introduced ( RECOVER DATBASE USING SERVICE) , which simplifies this method a lot .

If you are in 11g or 18c, then follow below articles:

For 11g Refer : How to recover standby database when archive logs are missing in primary

For 18c Refer : How to recover standby database from primary using service

STEP 1 : CHECK PRIMARY / STANDBY DATABASE STATUS

-- PRIMARY 
SQL> select db_unique_name,OPEN_MODE,DATABASE_ROLE from v$database;

DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE
------------------------------ -------------------- ----------------
PRIMDB READ WRITE PRIMARY


-- STANDBY 

SQL> SQL> select db_unique_name,OPEN_MODE,DATABASE_ROLE from v$database;
DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE
------------------------------ -------------------- ----------------
STYDB READ ONLY WITH APPLY PHYSICAL STANDBY

2. CHECK ARCHIVE SEQUENCES

-- PRIMARY
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /archive/PRIMDB/ofaroot/arch
Oldest online log sequence 88
Next log sequence to archive 90
Current log sequence 90

-- STANDBY

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /archive/PRIMDB/ofaroot/arch
Oldest online log sequence 72
Next log sequence to archive 0
Current log sequence 74

Archive sequences from 74 are missing from primary , before being shipped to standby .
Follow the below steps to make standby in sync with primary in 12c .

NOTE – All the below steps need to be done on standby database

STEP 3 : CANCEL RECOVERY ON STANDBY :

SQL> select db_unique_name,OPEN_MODE,DATABASE_ROLE from v$database;

DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE
------------------------------ -------------------- ----------------
STYDB READ ONLY PHYSICAL STANDBY

SQL> recover managed standby database cancel;
Media recovery complete.

STEP 4 : START STANDBY DB IN MOUNT STATE( IF it is ACTIVE DATAGUARD)

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1.3935E+10 bytes
Fixed Size 6002112 bytes
Variable Size 4630514240 bytes
Database Buffers 9193914368 bytes
Redo Buffers 104153088 bytes
Database mounted.

STEP 5 : RECOVER STANDBY USING SERVICE

Connect to rman target in standby database and run the recover standby database command using the service of PRIMARY .

SYNTAX – RECOVER DATABASE FROM SERVICE < PRIMARY DB SERVICE NAME > NOREDO USING COMPRESSED BACKUPSET;

$ rman target /

Recovery Manager: Release 12.1.0.1.0 - Production on Tue Oct 18 18:32:15 2016

Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.

connected to target database: STYDB (DBID=599956155, not open)

RMAN> recover database from service PRIMDB noredo using compressed backupset;

Starting recover at 18-OCT-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=689 device type=DISK
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service PRIMDB
destination for restore of datafile 00001: /archive/PRIMDB/PRIMDB/system01.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 compressed network backup set from service PRIMDB
destination for restore of datafile 00002: /archive/PRIMDB/PRIMDB/sysaux01.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 compressed network backup set from service PRIMDB
destination for restore of datafile 00003: /archive/PRIMDB/PRIMDB/undotbs01.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 compressed network backup set from service PRIMDB
destination for restore of datafile 00004: /archive/PRIMDB/PRIMDB/users01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

Finished recover at 18-OCT-16

STEP 6 : RESTORE STANDBY CONTROLFILE IN NOMOUNT STAGE:

SYNTAX – RESTORE STANDBY CONTROLFILE FROM SERVICE < PRIMARY DB SERVICE NAME > ;

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 13934583808 bytes

Fixed Size 6002112 bytes
Variable Size 4630514240 bytes
Database Buffers 9193914368 bytes
Redo Buffers 104153088 bytes

RMAN> restore standby controlfile from service PRIMDB;

Starting restore at 18-OCT-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=593 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service PRIMDB
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/archive/PRIMDB/PRIMDB/control01.ctl
output file name=/archive/PRIMDB/PRIMDB/control02.ctl
Finished restore at 18-OCT-16

STEP 7 : CHECK DATAFILES

RMAN> report schema;

Starting implicit crosscheck backup at 18-OCT-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=593 device type=DISK
Finished implicit crosscheck backup at 18-OCT-16

Starting implicit crosscheck copy at 18-OCT-16
using channel ORA_DISK_1
Crosschecked 2 objects
Finished implicit crosscheck copy at 18-OCT-16

searching for all files in the recovery area
cataloging files...
no files cataloged

RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name PRIMDB

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 700 SYSTEM *** /archive/PRIMDB/PRIMDB/system01.dbf
2 550 SYSAUX *** /archive/PRIMDB/PRIMDB/sysaux01.dbf
3 335 UNDOTBS1 *** /archive/PRIMDB/PRIMDB/undotbs01.dbf
4 5 USERS *** /archive/PRIMDB/PRIMDB/users01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 /archive/PRIMDB/PRIMDB/temp01.dbf



RMAN> catalog start with 'c';

searching for all files that match the pattern c
no files found to be unknown to the database

STEP 8 : RUN RECOVER DATABASE( IN STANDBY )

RMAN> RECOVER DATABASE;

Starting recover at 18-OCT-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=673 device type=DISK

starting media recovery

media recovery complete, elapsed time: 00:00:00
Finished recover at 18-OCT-16

STEP 9 : OPEN THE DATABASE and START MEDIA RECOVERY:

SQL> recover managed standby database cancel;
Media recovery complete.
SQL> alter database open;

Database altered.

SQL> recover managed standby database using current logfile disconnect from session;
Media recovery complete.

Now standby database is in sync with primary . Do few log switch in primary and check whether archives are shipping to standby or not.

But if we check the alert log of stand by , we can see warning like No standby redo .

Archived Log entry 4 added for thread 1 sequence 93 rlc 925475123 ID 0x23c3441f dest 2:
RFS[4]: No standby redo logfiles available for thread 1
RFS[4]: Opened log for thread 1 sequence 94 dbid 599956155 branch 925475123
Tue Oct 18 19:10:59 2016

To fix it, we need to drop and recreate the standby redo logs

STEP 10 : DROP AND RECREATE STANDBY REDO LOGS( ON STANDBY)

SQL> recover managed standby database cancel;
Media recovery complete.
SQL> alter database drop logfile group 4;

Database altered.

SQL> alter database drop logfile group 5;

Database altered.

SQL> alter database drop logfile group 6;

Database altered.

SQL> select thread#, group#, sequence#, status from v$standby_log;
no rows selected

SQL> SQL> alter database add standby logfile '/archive/PRIMDB/PRIMDB/stdby_redo04.log' size 52428800;
Database altered.

SQL> SQL> alter database add standby logfile '/archive/PRIMDB/PRIMDB/stdby_redo05.log' size 52428800;
Database altered.

SQL> SQL> alter database add standby logfile '/archive/PRIMDB/PRIMDB/stdby_redo06.log' size 52428800;
Database altered.

SQL> SQL> alter database add standby logfile '/archive/PRIMDB/PRIMDB/stdby_redo07.log' size 52428800;
Database altered.

STEP 11 : START THE RECOVERY AGAIN:

SQL> recover managed standby database using current logfile disconnect from session;
Media recovery complete.

MORE ARTICLES ON STANDBY:

1. Oracle dataguard Concepts:

2. How to create physical standby database

3 . Convert physical standby database to snapshot database

4. Switchover in physical standby database

5. Applying PSU patch in physical standby database

7 thoughts on “Rolling forward standby database when archives missing in primary in 12c”

Karthick says:

October 20, 2016 at 7:22 pm

Thanks alot admin great stuff .
thanks for Khalid for the use ful link

Syed ikram says:

March 8, 2017 at 11:23 am

very good. thanks

Naresh says:

July 20, 2017 at 12:31 pm

Nice information . But can using roll forward on standby impact primary database in terms of performance . or Cpu Spike ??

1. Admin says:
  
  July 20, 2017 at 1:18 pm
  
  Dear Naresh,
  There will be little load on the primary, during the rollback(i.e during the recovering through network, it will allocate some channels for rman backup). But it is very negligible.
  
  Regards
  ADMIN
  
Prashanth says:

February 9, 2019 at 6:29 am

very much useful thanks a lot

VVS says:

October 6, 2020 at 3:27 pm

Useful information but little confused with step 9 :- “alter database open ;” on standby server ?

1. Admin says:
  
  October 6, 2020 at 3:34 pm
  
  Dear ,
  
  If you run alter database open on standby , it will open the database in read only mode only.
  
  either you can
  
  run alter database open readonly
  
  (or) alter database open;
  
  Both serve the same purpose.
  
  Regards

Rolling forward standby database when archives missing in primary in 12c

STEP 1 : CHECK PRIMARY / STANDBY DATABASE STATUS

2. CHECK ARCHIVE SEQUENCES

STEP 3 : CANCEL RECOVERY ON STANDBY :

STEP 4 : START STANDBY DB IN MOUNT STATE( IF it is ACTIVE DATAGUARD)

STEP 5 : RECOVER STANDBY USING SERVICE

STEP 6 : RESTORE STANDBY CONTROLFILE IN NOMOUNT STAGE:

STEP 7 : CHECK DATAFILES

STEP 8 : RUN RECOVER DATABASE( IN STANDBY )

STEP 9 : OPEN THE DATABASE and START MEDIA RECOVERY:

STEP 10 : DROP AND RECREATE STANDBY REDO LOGS( ON STANDBY)

STEP 11 : START THE RECOVERY AGAIN:

About Admin

7 thoughts on “Rolling forward standby database when archives missing in primary in 12c”

Leave a Reply Cancel reply

STEP 1 : CHECK PRIMARY / STANDBY DATABASE STATUS

2. CHECK ARCHIVE SEQUENCES

STEP 3 : CANCEL RECOVERY ON STANDBY :

STEP 4 : START STANDBY DB IN MOUNT STATE( IF it is ACTIVE DATAGUARD)

STEP 5 : RECOVER STANDBY USING SERVICE

STEP 6 : RESTORE STANDBY CONTROLFILE IN NOMOUNT STAGE:

STEP 7 : CHECK DATAFILES

STEP 8 : RUN RECOVER DATABASE( IN STANDBY )

STEP 9 : OPEN THE DATABASE and START MEDIA RECOVERY:

STEP 10 : DROP AND RECREATE STANDBY REDO LOGS( ON STANDBY)

STEP 11 : START THE RECOVERY AGAIN:

About Admin

Related Articles

7 thoughts on “Rolling forward standby database when archives missing in primary in 12c”

Leave a Reply Cancel reply