Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
Before 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 the primary.
- Check the current of the SCN of the standby database from where recovery needs to be done.
- We need to take the incremental backup of the primary database from that SCN obtained from the standby database.
- Copy the backup from primary to the standby and catalog the backups
- Cancel the MRP of the 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.
In 12c, the method remains the same, but it has been made easier. We can use the RECOVER DATABASE USING SERVICE to sync the standby database with the primary.
The command does the same as described above in the background creates incremental backups containing the changes in the primary database. All changes to datafile on the primary database starting with the SCN in the standby data file header all are included in the incremental backup then the backup will be transferred over the network to the standby location and the incremental backup will be applied to the standby database.
Below are the details which we will use:
Primary Database
1 2 3 4 5 6 7 8 9 10 11 | DB_UNIQUE_NAME=AMIT(NET SERVICE NAME AMIT) Service name can be selected by giving the below query SQL> Show parameter service NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ service_names string AMIT |
Standby Database:
1 2 3 4 5 6 7 8 9 | DB_UNIQUE_NAME=VASHISHTH(Net service Name VASHISHTH) SQL> Show parameter service NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ service_names string VASHISHTH |
There are few prerequisites before we start the activity for 12c
- 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 location make sure we have enough space on the server.
Place the standby database in mount Mode:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | 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. |
Checking if Managed Recovery is running on the standby
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | SQL>set feedback off set pages 0 set lines 180 select THREAD#,SEQUENCE#,STATUS ,BLOCK# from gv$managed_standby ;SQL> SQL> SQL> SQL> 1 150829 CLOSING 1757184 0 0 CONNECTED 0 1 150860 CLOSING 1593344 1 150830 CLOSING 1785856 1 150859 CLOSING 1662976 0 0 IDLE 0 0 0 IDLE 0 0 0 IDLE 0 1 150861 IDLE 2255 1 150861 APPLYING_LOG 2256 SQL> currently applying the log from thread 1 Seq# 150861 |
CHECK WHETHER REDO APPLY ACTIVE
1 2 3 4 5 6 7 8 9 | SQL> set feedback off set pages 0 set lines 180 select trim(BLOCK#) from gv$managed_standby where PROCESS='MRP0';SQL> SQL> SQL> 2256 |
By executing the above command if the BLOCK# is keep on changing the redo apply is active and database will be in sync with the primary, if the BLOCK# remain the same we need to do the recovery.
We can check the sync by using the command archive log list on both primary and standby and comparing the current log sequence no. If there is no difference, we are good else we need to sync the standby database with the primary by executing the below steps on the standby database.
1 2 3 | SQL> recover managed standby database cancel; Media recovery complete. |
Check SCN of the Standby database
1 2 3 | SQL> SELECT TO_CHAR(dbms_flashback.get_system_change_number) FROM dual; 12835661337649 |
This will be used in later steps.
Connect to the RMAN to refreshes the standby database using the service name
1 | RMAN> recover database from service AMIT noredo using compressed backupset; |
Below is the sample output:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 | Starting recover at 22-DEC-19 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=342 device type=DISK channel ORA_DISK_1: starting incremental datafile backup set restore channel ORA_DISK_1: using compressed network backup set from service AMIT destination for restore of datafile 00001: /archive/AMIT/AMIT/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 AMIT destination for restore of datafile 00002: /archive/AMIT/AMIT/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 AMIT destination for restore of datafile 00003: /archive/AMIT/AMIT/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 AMIT destination for restore of datafile 00004: /archive/AMIT/AMIT/users01.dbf channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 Finished recover at 22-DEC-19 |
At this point the standby will be in sync with the primary but the control file of the standby database contains the old values of SCN so to complete the synchronization. We need to refresh the control file from the primary database.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 | 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 26587413659 bytes Fixed Size 60025612 bytes Variable Size 46305684240 bytes Database Buffers 9132569914368 bytes Redo Buffers 104458963088 bytes RMAN> restore standby controlfile from service AMIT; Starting restore at 22-DEC-19 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=678 device type=DISK channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: using network backup set from service AMIT channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 output file name=/archive/AMIT/AMIT/control01.ctl output file name=/archive/AMIT/AMIT/control02.ctl Finished restore at 22-DEC-19 RMAN> alter database mount; RMAN> report schema; |
Report schema will do an implicit crosscheck and catalogued the files in the recovery area.
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 VASHISHTH
To rectify perform the below steps:
1 2 3 | RMAN> Catalog start with '/archive/AMITDB/AMIT/VASHISHTH/';çlocation of the files RMAN> SWITCH DATABASE TO COPY; |
We need recover the standby database before opening in read only mode :
1 | RMAN> RECOVER DATABASE; |
If we open the database now and start the managed process, we will get the below alert in the alertlog
RFS[4]: No standby redo logfiles available for thread 1
So, we need to drop the standby redo logs and recreate them
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | SQL> alter database drop logfile group 5; Database altered. SQL> alter database drop logfile group 4; 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> alter database add standby logfile '/archive/AMIT/AMIT/stdby_redo04.log' size 64532100; Database altered. SQL> alter database add standby logfile '/archive/AMIT/AMIT/stdby_redo05.log' size 64532100; Database altered. SQL> alter database add standby logfile '/archive/AMIT/AMIT/stdby_redo06.log' size 64532100; Database altered. SQL> alter database add standby logfile '/archive/AMIT/AMIT/stdby_redo07.log' size 64532100; Database altered. |
Open the standby database in read only mode and start the recovery managed process
1 2 3 | SQL> ALTER DATABASE OPEN READ ONLY; SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; |
The standby database will be in sync with primary database now. Which can be checked using archive log list or using
1 | select max(sequence#) from v$log_history; |
