Recover Standby Database Using Service

Start Here

Get in touch with a
TriCore Solutions specialist

Blog | Nov 24, 2016

Recover Standby Database Using Service


Prior to 12c, we had to make the standby DB sync with the primary DB by taking an incremental backup from primary DB and applying the same in standby DB. This entire process was time consuming. In 12c, we now have a new feature Recover Standby Database Using Service, which simplifies the process greatly.

Introduction:

There can be a situation in the DataGuard setup, where archive logs are missing from the primary database, before being sent to standby. Prior to 12c, we had to make the standby DB sync with the primary DB by taking an incremental backup from primary DB and applying the same in standby DB. This entire process was time consuming.

However, in 12c, we now have a new feature Recover Database Using Service, which simplifies the process greatly. In this blog, I will discuss this new feature explain how to sync standby DB with Primary DB when archives are missing from the primary in 12c.

                                                             Typical DataGuard Configuration 

oracle database backup

Source: Oracle Data Guard 12c Administration Beginners Guide

Simulation and Assumptions

  1. A properly configured primary and standby site.
  2. Steps are performed on DB Version: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production.
  3. To simulate the feature, on primary site the second archive location has been deferred as given below to increase the lag between primary and the standby site: 

SQL> alter system set log_archive_dest_state_2='DEFER';

System altered. 

Now I will demonstrate the steps for rolling forward the standby database using the “RECOVER…FROM SERVICE” command. Based on the above assumption, I have created a demo setup in my testing environment. Here are the steps:

 STEP 1: Check Primary/ Standby Database Status & Check Archive Sequences

 ##Primary

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

DB_UNIQUE_NA OPEN_MODE            DATABASE_ROLE

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

orcl         READ WRITE           PRIMARY

 ##Standby

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

DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE

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

orclst                         MOUNTED              PHYSICAL STANDBY

##Primary:

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /u01/app/orapi/arch_pri

Oldest online log sequence     46

Next log sequence to archive   48

Current log sequence           48

SQL>

##Standby:

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /u01/app/orast/arch_st

Oldest online log sequence     21

Next log sequence to archive   0

Current log sequence           23

We have made out of sync by DEFERring the log_archive_dest_state_2 parameter from archive number 23. Follow the below steps to make the standby DB sync with primary DB in 12c.

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

STEP 2: Cancel Recovery on Standby:

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

DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE

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

orclst                         MOUNTED              PHYSICAL STANDBY

SQL> recover managed standby database cancel;

Media recovery complete.

NOTE: Start the standby database in mount state if it is in active DataGaurd

STEP 3: Recover Standby Using Service of Primary

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;

[orast@localhost dbs]$ rman target /

Recovery Manager: Release 12.1.0.1.0 - Production on Sun Nov 13 02:30:11 2016

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

connected to target database: ORCL (DBID=1454901951, not open)

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

Starting recover at 13-NOV-16

Starting implicit crosscheck backup at 13-NOV-16

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=78 device type=DISK

Crosschecked 1 objects

Finished implicit crosscheck backup at 13-NOV-16 

Starting implicit crosscheck copy at 13-NOV-16

using channel ORA_DISK_1

Finished implicit crosscheck copy at 13-NOV-16 

searching for all files in the recovery area

cataloging files...

no files cataloged 

using channel ORA_DISK_1

skipping datafile 5; already restored to SCN 1738816

skipping datafile 7; already restored to SCN 1738816

skipping datafile 8; already restored to SCN 1745916

skipping datafile 9; already restored to SCN 1745916

skipping datafile 10; already restored to SCN 1745916

channel ORA_DISK_1: starting incremental datafile backup set restore

channel ORA_DISK_1: using compressed network backup set from service orcl

destination for restore of datafile 00001: /u01/app/orast/oradata/orclst/system01.dbf

channel ORA_DISK_1: restore complete, elapsed time: 00:00:25

channel ORA_DISK_1: starting incremental datafile backup set restore

channel ORA_DISK_1: using compressed network backup set from service orcl

destination for restore of datafile 00003: /u01/app/orast/oradata/orclst/sysaux01.dbf

channel ORA_DISK_1: restore complete, elapsed time: 00:00:15

channel ORA_DISK_1: starting incremental datafile backup set restore

channel ORA_DISK_1: using compressed network backup set from service orcl

destination for restore of datafile 00004: /u01/app/orast/oradata/orclst/undotbs01.dbf

channel ORA_DISK_1: restore complete, elapsed time: 00:00:03

channel ORA_DISK_1: starting incremental datafile backup set restore

channel ORA_DISK_1: using compressed network backup set from service orcl

destination for restore of datafile 00006: /u01/app/orast/oradata/orclst/users01.dbf

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 

Finished recover at 13-NOV-16 

STEP 4: Restore Standby Control file In Nomount Stage:

[orast@localhost dbs]$ sqlplus "/as sysdba"

SQL*Plus: Release 12.1.0.1.0 Production on Sun Nov 13 02:32:25 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options 

SQL> shu immediate;

ORA-01109: database not open 

Database dismounted.

ORACLE instance shut down.

SQL> startup nomount;

ORACLE instance started. 

Total System Global Area  421724160 bytes

Fixed Size                  2289112 bytes

Variable Size             343933480 bytes

Database Buffers           71303168 bytes

Redo Buffers                4198400 bytes

SQL> 

[orast@localhost dbs]$ rman target /

Recovery Manager: Release 12.1.0.1.0 - Production on Sun Nov 13 02:34:29 2016

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

connected to target database: ORCL (not mounted) 

RMAN> restore standby controlfile from service orcl; 

Starting restore at 13-NOV-16

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=20 device type=DISK 

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: using network backup set from service orcl

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:07

output file name=/u01/app/orast/oradata/orclst/control01.ctl

Finished restore at 13-NOV-16 

STEP 5: Check Datafiles

RMAN> report schema;

RMAN-06139: WARNING: control file is not current for REPORT SCHEMA

Report of database schema for database with db_unique_name ORCLST 

List of Permanent Datafiles

===========================

File Size(MB) Tablespace           RB segs Datafile Name

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

1    780      SYSTEM               ***     /u01/app/orast/oradata/orclst/system01.dbf

3    710      SYSAUX               ***     /u01/app/orast/oradata/orclst/sysaux01.dbf

4    85       UNDOTBS1             ***     /u01/app/orast/oradata/orclst/undotbs01.dbf

5    250      PDB$SEED:SYSTEM      ***     /u01/app/orast/oradata/orclst/pdbseed/system01.dbf

6    5        USERS                ***     /u01/app/orast/oradata/orclst/users01.dbf

7    590      PDB$SEED:SYSAUX      ***     /u01/app/orast/oradata/orclst/pdbseed/sysaux01.dbf

8    260      PDBORCL:SYSTEM       ***     /u01/app/orast/oradata/orclst/pdborcl/system01.dbf

9    610      PDBORCL:SYSAUX       ***     /u01/app/orast/oradata/orclst/pdborcl/sysaux01.dbf

10   5        PDBORCL:USERS        ***     /u01/app/orast/oradata/orclst/pdborcl/pdborcl_users01.dbf
 

List of Temporary Files

=======================

File Size(MB) Tablespace           Maxsize(MB) Tempfile Name

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

1    60       TEMP                 32767       /u01/app/orast/oradata/orclst/temp01.dbf

2    20       PDB$SEED:TEMP        32767       /u01/app/orast/oradata/orclst/pdbseed/pdbseed_temp01.dbf

3    20       PDBORCL:TEMP         32767       /u01/app/orast/oradata/orclst/pdborcl/temp01.dbf 

STEP 6: Run Recover Database (in Standby)

RMAN> recover database;

Starting recover at 13-NOV-16

using channel ORA_DISK_1

starting media recovery

media recovery complete, elapsed time: 00:00:00

Finished recover at 13-NOV-16 

STEP 7: Mount the Database and Start Media Recovery & Validate:

Status: After RECOVER DATBASE USING SERVICE 

##Primary

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /u01/app/orapi/arch_pri

Oldest online log sequence     46

Next log sequence to archive   48

Current log sequence           48

 ##Standby

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /u01/app/orast/arch_st

Oldest online log sequence     46

Next log sequence to archive   48

Current log sequence           48 

STEP 8: Perform Some Log Switch Manually and Start Recovery at STANDBY DB: SYNC STATUS:

SQL> recover managed standby database using current logfile disconnect from session;

Media recovery complete.

SYNC STATUS:

-----------

##Primary

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /u01/app/orapi/arch_pri

Oldest online log sequence     52

Next log sequence to archive   54

Current log sequence           54 

##Standby

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /u01/app/orast/arch_st

Oldest online log sequence     52

Next log sequence to archive   0

Current log sequence           54

SQL>


Conclusion:

“Rolling forward the standby database when archives are missing from Primary DB in 12c” OR “there is a huge lag between the primary and standby site” is now a very simplified process as compared to 12c prior versions. In the earlier versions a DBA had to perform lots of tasks/steps to sync the primary and the standby databases. With this new feature, it saves a lot of time in rolling forward the standby database and also important to note, we need not disturb the primary site manually while using this method. We merely have to run two RMAN commands and we are all set within no time. For any questions on the topic click below:

Ask Mukesh