RECOVER DATABASE USING SERVICE NAME

 

In versions prior to 12c if one of the standby database goes out of  sync with respect to the primary database we have to perform the  below steps in order to make the standby in sync with primary. 

 

• Check the current SCN of the standby database from where recovery needs to be done. 

• Take incremental backup of primary database from the SCN obtained from the standby database. 

• Copy the backup from primary to the standby and catalog the backups. 

• Cancel the MRP of standby database and apply the backup from the primary 

• Create a new control file for the standby database from the primary and mount the standby using the new control file. 

• Start the managed recovery of the standby database 

 

From 12c, the method remains the same, but it has been made easier. We can RECOVER DATABASE USING SERVICE NAME to sync the standby database with the primary.

The command does the same as described above. In the background it creates incremental backups containing the changes in the primary database. All the changes done to the datafile on the primary database are included in the incremental backup starting with the SCN in the standby from where we need recovery and then the backup will be transferred over the network to the standby location and the incremental backup will be applied to the standby database.

Let's say below are the primary and standby database details: 

 

Primary : 

DB_UNIQUE_NAME=ORCL

Service name can be selected by giving the below query.  

NAME               TYPE   VALUE
------------------ ------------ --------------
service_names  string    ORCL

Standby :

DB_UNIQUE_NAME=ORCLSBY  

NAME               TYPE   VALUE
------------------ ------------ --------------
service_names  string   ORCLSBY

There are few prerequisites that needs to be checked before we start the activity. 

• There should be Oracle net connectivity between standby and primary which can be done by adding the TNS entry in the tnsnames.ora file of the  standby database. 

• This only work for oracle 12c or higher version of the database so the COMPATIBLE parameter should be set to 12 or the higher version.  

• Check the space on the standby database filesystem. Make sure we have enough space on the server.

Place the standby database in mount state. 

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

SQL> startup mount;
ORACLE instance started.
Total System Global Area 1.52875E+10 bytes
Fixed Size 8796438 bytes
Variable Size 4630514240 bytes
Database Buffers 9193914368 bytes
Redo Buffers 104153088 bytes
Database mounted.

Check if Managed Recovery Process(MRP0) is running on the standby.

select recovery_mode from v$archive_dest_status where recovery_mode like 'MANAGED%';

Cancel Managed Recovery Process on standby.

recover managed standby database cancel;

Check SCN of the Standby database. This will be used in later steps.

Set numwidth 50
SQL> select current_scn from v$database;
CURRENT_SCN
--------------
10301523591238

Connect to RMAN on standby database.   

 

rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Thu May 13 07:57:54 2021
Version 19.9.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCLSBY(DBID=341160335, not open)

Execute below command to recover standby database.   

 

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

At this point the standby will be in sync with primary but the control file of the standby database contains the old values of SCN. To complete the synchronization, we need to restore the control file from primary database. 

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 34327068776 bytes
Fixed Size 30150760 bytes
Variable Size 9193914368 bytes
Database Buffers 24964497408 bytes
Redo Buffers 138506240 bytes

RMAN> restore standby controlfile from service ORCL; 

RMAN> alter database mount;
released channel: ORA_DISK_1
Statement processed

RMAN> report schema;

 The REPORT SCHEMA command lists and displays information about the database files.

If the directory structure of the primary and standby database is different you will get the below alert while performing the above steps.

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

Report of database schema for database with db_unique_name ORCLSBY

Perform the below steps if you see above error: 

 

RMAN> Catalog start with '<DATAFILE LOCATION ON PRIMARY>';
RMAN> SWITCH DATABASE TO COPY;
RMAN> RECOVER STANDBY DATABASE

Check Redo Logs 

SQL> select thread#,group#,sequence#,status, bytes/1024/1024 mb from v$standby_log;
THREAD# GROUP# SEQUENCE# STATUS MB
---------- ------ ---------- ---------- ------
1 4 0 UNASSIGNED 1024
1 5 734869 ACTIVE 1024
1 6 0 UNASSIGNED 1024

SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
<Location>/redo04.log
<Location>/redo05.log
<Location>/redo06.log
3 rows selected.

Open the standby database in read only mode and start the recovery managed process.

SQL> ALTER DATABASE OPEN READ ONLY;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Check sync status of standby database with primary.

SELECT floor(((sysdate-max(NEXT_TIME))*24*60*60)/3600)|| ' Hour(s) ' ||
floor((((sysdate-max(NEXT_TIME))*24*60*60) -
floor(((sysdate-max(NEXT_TIME))*24*60*60)/3600)*3600)/60) || ' Minute(s) ' ||
round((((sysdate-max(NEXT_TIME))*24*60*60) -
floor(((sysdate-max(NEXT_TIME))*24*60*60)/3600)*3600 -
(floor((((sysdate-max(NEXT_TIME))*24*60*60) -
floor(((sysdate-max(NEXT_TIME))*24*60*60)/3600)*3600)/60)*60) ))|| ' Second(s) '
"Standby ARCHIVELOG GAP/LAG" from gv$archived_log where applied ='YES';