Problem statement :- standby database goes out of sync as the archive logs are missing from primary site.
Environment:
Primary:
Primary database node : primary.soumya.com
Database name : orclcdb
Primary DB UNIQUE NAME : orclcdb
Primary database instances : orclcdb
DB Version: 19.0.0
Standby:
Standby database node : standby.soumya.com
Database name : orclcdb
Standby DB UNIQUE NAME : orclstd
Standby database instances : orclcdb
DB Version: 19.0.0
Primary:
SQL>select open_mode from v$database;
OPEN_MODE
——————–
READ WRITE
Standby:
SQL>select open_mode from v$database;
OPEN_MODE
——————–
MOUNTED
DataGuard Broker Status:
[oracle@standby 2020_04_15]$ dgmgrl sys/admin123#@orclcdb
DGMGRL for Linux: Release 19.0.0.0.0 – Production on Wed Apr 15 19:47:10 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type “help” for information.
Connected to “orclcdb”
Connected as SYSDBA.
DGMGRL> show configuration;
Configuration – my_dg_config
Protection Mode: MaxPerformance
Members:
orclcdb – Primary database
orclstd – Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 53 seconds ago)
Now we will try to simulate a “out of sync” situation between the primary and standby database.
To achive this , on Primary database we disabled the redo log transport towards the standby database.
DGMGRL> edit database ‘orclcdb’ SET STATE=”LOG-TRANSPORT-OFF”;
Succeeded.
Now we performed a few log switch on primary database to forward the log sequence number.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
Now check the sequence number on Primary:-
SQL> select thread#,max(sequence#) from v$archived_log group by thread#;
THREAD# MAX(SEQUENCE#)
———- ————–
1 58
On Standby :-
SQL> select thread#,max(sequence#) from v$archived_log where applied=’YES’ group by thread#;
THREAD# MAX(SEQUENCE#)
———- ————–
1 50
Now we will move the archives generated on primary database.
[oracle@primary 2020_04_15]$ mv *.arc /u01/
Now we enable the log apply on primary database to put the standby database out of sync.
on Primary:-
DGMGRL> edit database ‘orclcdb’ SET STATE=TRANSPORT-ON;
Succeeded.
DGMGRL> exit
On Standby:-
Check the mrp process status. it shows waiting for WAIT_FOR_GAP
SQL> select process,status,sequence#,thread# from v$managed_standby;
PROCESS STATUS SEQUENCE# THREAD#
——— ———— ———- ———-
DGRD ALLOCATED 0 0|}
/…..
ARCH CLOSING 49 1
DGRD ALLOCATED 0 0
ARCH CLOSING 50 1
ARCH CONNECTED 0 0
ARCH CLOSING 61 1
ARCH CLOSING 62 1
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
PROCESS STATUS SEQUENCE# THREAD#
——— ———— ———- ———-
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
PROCESS STATUS SEQUENCE# THREAD#
——— ———— ———- ———-
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
RFS IDLE 0 1
PROCESS STATUS SEQUENCE# THREAD#
——— ———— ———- ———-
RFS IDLE 0 0
MRP0 WAIT_FOR_GAP 51 1
RFS IDLE 0 0
RFS IDLE 63 1
37 rows selected.
Now, Let us identify the datafiles on standby database which are out of sync with respect to primary.
On primary:-
SQL>select HXFIL File_num,substr(HXFNM,1,40),fhscn from x$kcvfh
SQL> col substr(HXFNM,1,40) for a40
SQL> /
FILE_NUM SUBSTR(HXFNM,1,40) FHSCN
———- —————————————- ——————–
1 /u01/app/oracle/oradata/ORCLCDB/system01 3195846
3 /u01/app/oracle/oradata/ORCLCDB/sysaux01 3195846
4 /u01/app/oracle/oradata/ORCLCDB/undotbs0 3195846
5 /u01/app/oracle/oradata/ORCLCDB/pdbseed/ 2136332
6 /u01/app/oracle/oradata/ORCLCDB/pdbseed/ 2136332
7 /u01/app/oracle/oradata/ORCLCDB/users01. 3195846
8 /u01/app/oracle/oradata/ORCLCDB/pdbseed/ 2136332
9 /u01/app/oracle/oradata/ORCLCDB/orclpdb1 3195846
10 /u01/app/oracle/oradata/ORCLCDB/orclpdb1 3195846
11 /u01/app/oracle/oradata/ORCLCDB/orclpdb1 3195846
12 /u01/app/oracle/oradata/ORCLCDB/orclpdb1 3195846
11 rows selected.
on Standby :-
SQL> set lines 200
SQL> col SUBSTR(HXFNM,1,40) for a40
SQL> /
FILE_NUM SUBSTR(HXFNM,1,40) FHSCN
———- —————————————- ——————–
1 /u01/app/oracle/oradata/orclstd/system01 3188343
3 /u01/app/oracle/oradata/orclstd/sysaux01 3188343
4 /u01/app/oracle/oradata/orclstd/undotbs0 3188343
5 /u01/app/oracle/oradata/orclstd/pdbseed/ 2136332
6 /u01/app/oracle/oradata/orclstd/pdbseed/ 2136332
7 /u01/app/oracle/oradata/orclstd/users01. 3188343
8 /u01/app/oracle/oradata/orclstd/pdbseed/ 2136332
9 /u01/app/oracle/oradata/orclstd/orclpdb1 3188343
10 /u01/app/oracle/oradata/orclstd/orclpdb1 3188343
11 /u01/app/oracle/oradata/orclstd/orclpdb1 3188343
12 /u01/app/oracle/oradata/orclstd/orclpdb1 3188343
11 rows selected.
From the above example, when comparing the SCN of the datafiles’ header on the primary (orclcdb) and standby (orclstd),
we see that whereas the SCN of datafiles 5,6 and 8 match that of primary, the rest of the datafiles (1,2,3,4,7,9,10,11,12) of
the standby are lagging behind the primary database.
Note the current SCN of the physical standby database. This is required to determine, in a later step, if new data files were
added to the primary database.
SQL> SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
———–
3200264
The RECOVER DATABASE FROM SERVICE command refreshes the standby data files and rolls them forward to the same
point-in-time as the primary.
First cancel the recovery on standby database.
On Standby:-
SQL>alter database recover managed standby database cancel;
Database altered.
Now,Connect the standby database through RMAN as “target” and issue the “recover database from service”command.
on Standby :-
[oracle@standby ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 – Production on Wed Apr 15 21:20:28 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCLCDB (DBID=2807809769, not open)
RMAN> recover database from service orclcdb noredo using compressed backupset;
Starting recover at 15-APR-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=75 device type=DISK
skipping datafile 5; already restored to SCN 2136332
skipping datafile 6; already restored to SCN 2136332
skipping datafile 8; already restored to SCN 2136332
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service orclcdb
destination for restore of datafile 00001: /u01/app/oracle/oradata/orclstd/system01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:16
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service orclcdb
destination for restore of datafile 00003: /u01/app/oracle/oradata/orclstd/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 orclcdb
destination for restore of datafile 00004: /u01/app/oracle/oradata/orclstd/undotbs01.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 orclcdb
destination for restore of datafile 00007: /u01/app/oracle/oradata/orclstd/users01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service orclcdb
destination for restore of datafile 00009: /u01/app/oracle/oradata/orclstd/orclpdb1/system01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service orclcdb
destination for restore of datafile 00010: /u01/app/oracle/oradata/orclstd/orclpdb1/sysaux01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service orclcdb
destination for restore of datafile 00011: /u01/app/oracle/oradata/orclstd/orclpdb1/undotbs01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service orclcdb
destination for restore of datafile 00012: /u01/app/oracle/oradata/orclstd/orclpdb1/users01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished recover at 15-APR-20
RMAN>
Now, Lets check the SCNs of the datafiles at primary and standby now.
On primary:-
SQL> select HXFIL File_num,substr(HXFNM,1,40),fhscn from x$kcvfh;
FILE_NUM SUBSTR(HXFNM,1,40) FHSCN
———- —————————————- ——————–
1 /u01/app/oracle/oradata/ORCLCDB/system01 3201351
3 /u01/app/oracle/oradata/ORCLCDB/sysaux01 3201390
4 /u01/app/oracle/oradata/ORCLCDB/undotbs0 3201418
5 /u01/app/oracle/oradata/ORCLCDB/pdbseed/ 2136332
6 /u01/app/oracle/oradata/ORCLCDB/pdbseed/ 2136332
7 /u01/app/oracle/oradata/ORCLCDB/users01. 3201448
8 /u01/app/oracle/oradata/ORCLCDB/pdbseed/ 2136332
9 /u01/app/oracle/oradata/ORCLCDB/orclpdb1 3201453
10 /u01/app/oracle/oradata/ORCLCDB/orclpdb1 3201471
11 /u01/app/oracle/oradata/ORCLCDB/orclpdb1 3201486
12 /u01/app/oracle/oradata/ORCLCDB/orclpdb1 3201493
On Standby:-
SQL> select HXFIL File_num,substr(HXFNM,1,40),fhscn from x$kcvfh;
FILE_NUM SUBSTR(HXFNM,1,40) FHSCN
———- —————————————- ——————–
1 /u01/app/oracle/oradata/orclstd/system01 3201351
3 /u01/app/oracle/oradata/orclstd/sysaux01 3201390
4 /u01/app/oracle/oradata/orclstd/undotbs0 3201418
5 /u01/app/oracle/oradata/orclstd/pdbseed/ 2136332
6 /u01/app/oracle/oradata/orclstd/pdbseed/ 2136332
7 /u01/app/oracle/oradata/orclstd/users01. 3201448
8 /u01/app/oracle/oradata/orclstd/pdbseed/ 2136332
9 /u01/app/oracle/oradata/orclstd/orclpdb1 3201453
10 /u01/app/oracle/oradata/orclstd/orclpdb1 3201471
11 /u01/app/oracle/oradata/orclstd/orclpdb1 3201486
12 /u01/app/oracle/oradata/orclstd/orclpdb1 3201493
11 rows selected.
Now, from the above output we can see all the scns of primary and standby database’s datafiles are matching.
However, the standby control file still contains old SCN values which are lower than the SCN values in the standby datafiles.
Therefore, to complete the synchronization of the physical standby database, we must refresh the standby controlfile
from the primary.
On standby :-
Shutdown the database and restore the control file
SQL> shut immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1644164416 bytes
Fixed Size 9135424 bytes
Variable Size 1392508928 bytes
Database Buffers 234881024 bytes
Redo Buffers 7639040 bytes
SQL>
[oracle@standby ~]$ rman target /
RMAN> restore standby controlfile from service orclcdb;
Starting restore at 15-APR-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=39 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service orclcdb
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output file name=/u01/app/oracle/oradata/orclstd/control01.ctl
Finished restore at 15-APR-20
Now,If the datafiles locations on the primary and standby database are different, then we will have to follow this step.
Since that we have restored the controlfile from the primary database, the datafiles locations will still be pointing to the
location of the datafiles that we have on the primary site.Depending on the configuration, if the path and names of the standby
datafiles after the standby controlfile refresh are correct then we can stop here .
RMAN> alter database mount;
released channel: ORA_DISK_1
Statement processed
RMAN> report schema;
Starting implicit crosscheck backup at 15-APR-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=81 device type=DISK
Crosschecked 3 objects
Finished implicit crosscheck backup at 15-APR-20
Starting implicit crosscheck copy at 15-APR-20
using channel ORA_DISK_1
Finished implicit crosscheck copy at 15-APR-20
searching for all files in the recovery area
cataloging files…
cataloging done
List of Cataloged Files
=======================
File Name: /u01/archive_log/ORCLSTD/archivelog/2020_04_12/o1_mf_1_22_h96ptj4c_.arc
File Name: /u01/archive_log/ORCLSTD/archivelog/2020_04_12/o1_mf_1_23_h96q00fh_.arc
File Name: /u01/archive_log/ORCLSTD/archivelog/2020_04_12/o1_mf_1_24_h96q01gx_.arc
File Name: /u01/archive_log/ORCLSTD/archivelog/2020_04_12/o1_mf_1_25_h96q04s3_.arc
File Name: /u01/archive_log/ORCLSTD/archivelog/2020_04_13/o1_mf_1_26_h98sxhhf_.arc
File Name: /u01/archive_log/ORCLSTD/archivelog/2020_04_13/o1_mf_1_27_h98sxmbo_.arc
File Name: /u01/archive_log/ORCLSTD/archivelog/2020_04_13/o1_mf_1_28_h98t1mlj_.arc
File Name: /u01/archive_log/ORCLSTD/archivelog/2020_04_13/o1_mf_1_29_h98t1y7n_.arc
File Name: /u01/archive_log/ORCLSTD/archivelog/2020_04_13/o1_mf_1_30_h98t3shx_.arc
File Name: /u01/archive_log/ORCLSTD/archivelog/2020_04_13/o1_mf_1_31_h98tmob9_.arc
File Name: /u01/archive_log/ORCLSTD/archivelog/2020_04_13/o1_mf_1_33_h98tmsjn_.arc
File Name: /u01/archive_log/ORCLSTD/archivelog/2020_04_13/o1_mf_1_32_h98tmsnt_.arc
File Name: /u01/archive_log/ORCLSTD/archivelog/2020_04_15/o1_mf_1_35_h9g4wz0m_.arc
File Name: /u01/archive_log/ORCLSTD/archivelog/2020_04_15/o1_mf_1_36_h9g4wz0w_.arc
File Name: /u01/archive_log/ORCLSTD/archivelog/2020_04_15/o1_mf_1_38_h9g4x09m_.arc
File Name: /u01/archive_log/ORCLSTD/archivelog/2020_04_15/o1_mf_1_37_h9g4x0cj_.arc
File Name: /u01/archive_log/ORCLSTD/archivelog/2020_04_15/o1_mf_1_34_h9g4x6vw_.arc
File Name: /u01/archive_log/ORCLSTD/archivelog/2020_04_15/o1_mf_1_40_h9g51842_.arc
File Name: /u01/archive_log/ORCLSTD/archivelog/2020_04_15/o1_mf_1_39_h9g518ns_.arc
File Name: /u01/archive_log/ORCLSTD/archivelog/2020_04_15/o1_mf_1_41_h9g518wp_.arc
File Name: /u01/archive_log/ORCLSTD/archivelog/2020_04_15/o1_mf_1_42_h9g5j00x_.arc
File Name: /u01/archive_log/ORCLSTD/archivelog/2020_04_15/o1_mf_1_43_h9g5otsq_.arc
File Name: /u01/archive_log/ORCLSTD/archivelog/2020_04_15/o1_mf_1_44_h9g5ozdk_.arc
File Name: /u01/archive_log/ORCLSTD/archivelog/2020_04_15/o1_mf_1_45_h9g5q9lo_.arc
File Name: /u01/archive_log/ORCLSTD/archivelog/2020_04_15/o1_mf_1_46_h9g5qmqr_.arc
File Name: /u01/archive_log/ORCLSTD/archivelog/2020_04_15/o1_mf_1_47_h9g5qp1j_.arc
File Name: /u01/archive_log/ORCLSTD/archivelog/2020_04_15/o1_mf_1_48_h9g5qrvj_.arc
File Name: /u01/archive_log/ORCLSTD/archivelog/2020_04_15/o1_mf_1_49_h9g6kot2_.arc
File Name: /u01/archive_log/ORCLSTD/archivelog/2020_04_15/o1_mf_1_50_h9g6wfv8_.arc
File Name: /u01/archive_log/ORCLSTD/archivelog/2020_04_15/o1_mf_1_60_h9g93nnk_.arc
File Name: /u01/archive_log/ORCLSTD/archivelog/2020_04_15/o1_mf_1_61_h9g93nv8_.arc
File Name: /u01/archive_log/ORCLSTD/archivelog/2020_04_15/o1_mf_1_62_h9g94yk8_.arc
File Name: /u01/archive_log/ORCLSTD/autobackup/2020_04_15/o1_mf_s_1037821819_h9g62f0r_.bkp
RMAN-06139: warning: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name ORCLSTD
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
—- ——– ——————– ——- ————————
1 910 SYSTEM *** /u01/app/oracle/oradata/orclstd/system01.dbf
3 590 SYSAUX *** /u01/app/oracle/oradata/orclstd/sysaux01.dbf
4 295 UNDOTBS1 *** /u01/app/oracle/oradata/orclstd/undotbs01.dbf
5 270 PDB$SEED:SYSTEM *** /u01/app/oracle/oradata/orclstd/pdbseed/system01.dbf
6 330 PDB$SEED:SYSAUX *** /u01/app/oracle/oradata/orclstd/pdbseed/sysaux01.dbf
7 5 USERS *** /u01/app/oracle/oradata/orclstd/users01.dbf
8 100 PDB$SEED:UNDOTBS1 *** /u01/app/oracle/oradata/orclstd/pdbseed/undotbs01.dbf
9 280 ORCLPDB1:SYSTEM *** /u01/app/oracle/oradata/orclstd/orclpdb1/system01.dbf
10 360 ORCLPDB1:SYSAUX *** /u01/app/oracle/oradata/orclstd/orclpdb1/sysaux01.dbf
11 100 ORCLPDB1:UNDOTBS1 *** /u01/app/oracle/oradata/orclstd/orclpdb1/undotbs01.dbf
12 5 ORCLPDB1:USERS *** /u01/app/oracle/oradata/orclstd/orclpdb1/users01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
—- ——– ——————– ———– ——————–
1 133 TEMP 32767 /u01/app/oracle/oradata/orclstd/temp01.dbf
2 36 PDB$SEED:TEMP 32767 /u01/app/oracle/oradata/orclstd/pdbseed/temp012020-04-03_09-48-16-376-AM.dbf
3 36 ORCLPDB1:TEMP 32767 /u01/app/oracle/oradata/orclstd/orclpdb1/temp01.dbf
Here , from the above report schema command, we can see the datafiles are pointing to path of standby site. Hence we will stop here
and check the mrp status in standby database.
on standby:-
SQL> select process,status,sequence#,thread# from gv$managed_standby;
PROCESS STATUS SEQUENCE# THREAD#
——— ———— ———- ———-
ARCH CLOSING 63 1
DGRD ALLOCATED 0 0
DGRD ALLOCATED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
PROCESS STATUS SEQUENCE# THREAD#
——— ———— ———- ———-
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
PROCESS STATUS SEQUENCE# THREAD#
——— ———— ———- ———-
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
RFS IDLE 0 1
PROCESS STATUS SEQUENCE# THREAD#
——— ———— ———- ———-
RFS IDLE 64 1
MRP0 APPLYING_LOG 64 1
35 rows selected.
So we see the mrp process is now applying log .
Let’s check the sync status of the standby database with the primary database.
On the primary:
SQL> select thread#,max(sequence#) from v$archived_log group by thread#;
THREAD# MAX(SEQUENCE#)
———- ————–
1 63
on standby:-
SQL> select thread#,max(sequence#) from v$archived_log where applied=’YES’ group by thread#;
THREAD# MAX(SEQUENCE#)
———- ————–
1 63
Now we see that the standby is sync with the primary.
P.S. If we see that from above report schema output , the path of datafiles in standby site are pointing towards primary site
then we will update the datafile and tempfile location as per standby site location.
RMAN> catalog start with ‘<path where the actual standby datafile existed>’;
RMAN> SWITCH DATABASE TO COPY;
SQL>select open_mode from v$database;
OPEN_MODE
——————–
READ WRITE
Standby:
SQL>select open_mode from v$database;
OPEN_MODE
——————–
MOUNTED
DataGuard Broker Status:
[oracle@standby 2020_04_15]$ dgmgrl sys/admin123#@orclcdb
DGMGRL for Linux: Release 19.0.0.0.0 – Production on Wed Apr 15 19:47:10 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type “help” for information.
Connected to “orclcdb”
Connected as SYSDBA.
DGMGRL> show configuration;
Configuration – my_dg_config
Protection Mode: MaxPerformance
Members:
orclcdb – Primary database
orclstd – Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 53 seconds ago)
Now we will try to simulate a “out of sync” situation between the primary and standby database.
To achive this , on Primary database we disabled the redo log transport towards the standby database.
DGMGRL> edit database ‘orclcdb’ SET STATE=”LOG-TRANSPORT-OFF”;
Succeeded.
Now we performed a few log switch on primary database to forward the log sequence number.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
Now check the sequence number on Primary:-
SQL> select thread#,max(sequence#) from v$archived_log group by thread#;
THREAD# MAX(SEQUENCE#)
———- ————–
1 58
On Standby :-
SQL> select thread#,max(sequence#) from v$archived_log where applied=’YES’ group by thread#;
THREAD# MAX(SEQUENCE#)
———- ————–
1 50
Now we will move the archives generated on primary database.
[oracle@primary 2020_04_15]$ mv *.arc /u01/
Now we enable the log apply on primary database to put the standby database out of sync.
on Primary:-
DGMGRL> edit database ‘orclcdb’ SET STATE=TRANSPORT-ON;
Succeeded.
DGMGRL> exit
On Standby:-
Check the mrp process status. it shows waiting for WAIT_FOR_GAP
SQL> select process,status,sequence#,thread# from v$managed_standby;
PROCESS STATUS SEQUENCE# THREAD#
——— ———— ———- ———-
DGRD ALLOCATED 0 0|}
/…..
ARCH CLOSING 49 1
DGRD ALLOCATED 0 0
ARCH CLOSING 50 1
ARCH CONNECTED 0 0
ARCH CLOSING 61 1
ARCH CLOSING 62 1
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
PROCESS STATUS SEQUENCE# THREAD#
——— ———— ———- ———-
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
PROCESS STATUS SEQUENCE# THREAD#
——— ———— ———- ———-
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
RFS IDLE 0 1
PROCESS STATUS SEQUENCE# THREAD#
——— ———— ———- ———-
RFS IDLE 0 0
MRP0 WAIT_FOR_GAP 51 1
RFS IDLE 0 0
RFS IDLE 63 1
37 rows selected.
Now, Let us identify the datafiles on standby database which are out of sync with respect to primary.
On primary:-
SQL>select HXFIL File_num,substr(HXFNM,1,40),fhscn from x$kcvfh
SQL> col substr(HXFNM,1,40) for a40
SQL> /
FILE_NUM SUBSTR(HXFNM,1,40) FHSCN
———- —————————————- ——————–
1 /u01/app/oracle/oradata/ORCLCDB/system01 3195846
3 /u01/app/oracle/oradata/ORCLCDB/sysaux01 3195846
4 /u01/app/oracle/oradata/ORCLCDB/undotbs0 3195846
5 /u01/app/oracle/oradata/ORCLCDB/pdbseed/ 2136332
6 /u01/app/oracle/oradata/ORCLCDB/pdbseed/ 2136332
7 /u01/app/oracle/oradata/ORCLCDB/users01. 3195846
8 /u01/app/oracle/oradata/ORCLCDB/pdbseed/ 2136332
9 /u01/app/oracle/oradata/ORCLCDB/orclpdb1 3195846
10 /u01/app/oracle/oradata/ORCLCDB/orclpdb1 3195846
11 /u01/app/oracle/oradata/ORCLCDB/orclpdb1 3195846
12 /u01/app/oracle/oradata/ORCLCDB/orclpdb1 3195846
11 rows selected.
on Standby :-
SQL> set lines 200
SQL> col SUBSTR(HXFNM,1,40) for a40
SQL> /
FILE_NUM SUBSTR(HXFNM,1,40) FHSCN
———- —————————————- ——————–
1 /u01/app/oracle/oradata/orclstd/system01 3188343
3 /u01/app/oracle/oradata/orclstd/sysaux01 3188343
4 /u01/app/oracle/oradata/orclstd/undotbs0 3188343
5 /u01/app/oracle/oradata/orclstd/pdbseed/ 2136332
6 /u01/app/oracle/oradata/orclstd/pdbseed/ 2136332
7 /u01/app/oracle/oradata/orclstd/users01. 3188343
8 /u01/app/oracle/oradata/orclstd/pdbseed/ 2136332
9 /u01/app/oracle/oradata/orclstd/orclpdb1 3188343
10 /u01/app/oracle/oradata/orclstd/orclpdb1 3188343
11 /u01/app/oracle/oradata/orclstd/orclpdb1 3188343
12 /u01/app/oracle/oradata/orclstd/orclpdb1 3188343
11 rows selected.
From the above example, when comparing the SCN of the datafiles’ header on the primary (orclcdb) and standby (orclstd),
we see that whereas the SCN of datafiles 5,6 and 8 match that of primary, the rest of the datafiles (1,2,3,4,7,9,10,11,12) of
the standby are lagging behind the primary database.
Note the current SCN of the physical standby database. This is required to determine, in a later step, if new data files were
added to the primary database.
SQL> SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
———–
3200264
The RECOVER DATABASE FROM SERVICE command refreshes the standby data files and rolls them forward to the same
point-in-time as the primary.
First cancel the recovery on standby database.
On Standby:-
SQL>alter database recover managed standby database cancel;
Database altered.
Now,Connect the standby database through RMAN as “target” and issue the “recover database from service”command.
on Standby :-
[oracle@standby ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 – Production on Wed Apr 15 21:20:28 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCLCDB (DBID=2807809769, not open)
RMAN> recover database from service orclcdb noredo using compressed backupset;
Starting recover at 15-APR-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=75 device type=DISK
skipping datafile 5; already restored to SCN 2136332
skipping datafile 6; already restored to SCN 2136332
skipping datafile 8; already restored to SCN 2136332
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service orclcdb
destination for restore of datafile 00001: /u01/app/oracle/oradata/orclstd/system01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:16
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service orclcdb
destination for restore of datafile 00003: /u01/app/oracle/oradata/orclstd/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 orclcdb
destination for restore of datafile 00004: /u01/app/oracle/oradata/orclstd/undotbs01.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 orclcdb
destination for restore of datafile 00007: /u01/app/oracle/oradata/orclstd/users01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service orclcdb
destination for restore of datafile 00009: /u01/app/oracle/oradata/orclstd/orclpdb1/system01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service orclcdb
destination for restore of datafile 00010: /u01/app/oracle/oradata/orclstd/orclpdb1/sysaux01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service orclcdb
destination for restore of datafile 00011: /u01/app/oracle/oradata/orclstd/orclpdb1/undotbs01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service orclcdb
destination for restore of datafile 00012: /u01/app/oracle/oradata/orclstd/orclpdb1/users01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished recover at 15-APR-20
RMAN>
Now, Lets check the SCNs of the datafiles at primary and standby now.
On primary:-
SQL> select HXFIL File_num,substr(HXFNM,1,40),fhscn from x$kcvfh;
FILE_NUM SUBSTR(HXFNM,1,40) FHSCN
———- —————————————- ——————–
1 /u01/app/oracle/oradata/ORCLCDB/system01 3201351
3 /u01/app/oracle/oradata/ORCLCDB/sysaux01 3201390
4 /u01/app/oracle/oradata/ORCLCDB/undotbs0 3201418
5 /u01/app/oracle/oradata/ORCLCDB/pdbseed/ 2136332
6 /u01/app/oracle/oradata/ORCLCDB/pdbseed/ 2136332
7 /u01/app/oracle/oradata/ORCLCDB/users01. 3201448
8 /u01/app/oracle/oradata/ORCLCDB/pdbseed/ 2136332
9 /u01/app/oracle/oradata/ORCLCDB/orclpdb1 3201453
10 /u01/app/oracle/oradata/ORCLCDB/orclpdb1 3201471
11 /u01/app/oracle/oradata/ORCLCDB/orclpdb1 3201486
12 /u01/app/oracle/oradata/ORCLCDB/orclpdb1 3201493
On Standby:-
SQL> select HXFIL File_num,substr(HXFNM,1,40),fhscn from x$kcvfh;
FILE_NUM SUBSTR(HXFNM,1,40) FHSCN
———- —————————————- ——————–
1 /u01/app/oracle/oradata/orclstd/system01 3201351
3 /u01/app/oracle/oradata/orclstd/sysaux01 3201390
4 /u01/app/oracle/oradata/orclstd/undotbs0 3201418
5 /u01/app/oracle/oradata/orclstd/pdbseed/ 2136332
6 /u01/app/oracle/oradata/orclstd/pdbseed/ 2136332
7 /u01/app/oracle/oradata/orclstd/users01. 3201448
8 /u01/app/oracle/oradata/orclstd/pdbseed/ 2136332
9 /u01/app/oracle/oradata/orclstd/orclpdb1 3201453
10 /u01/app/oracle/oradata/orclstd/orclpdb1 3201471
11 /u01/app/oracle/oradata/orclstd/orclpdb1 3201486
12 /u01/app/oracle/oradata/orclstd/orclpdb1 3201493
11 rows selected.
Now, from the above output we can see all the scns of primary and standby database’s datafiles are matching.
However, the standby control file still contains old SCN values which are lower than the SCN values in the standby datafiles.
Therefore, to complete the synchronization of the physical standby database, we must refresh the standby controlfile
from the primary.
On standby :-
Shutdown the database and restore the control file
SQL> shut immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1644164416 bytes
Fixed Size 9135424 bytes
Variable Size 1392508928 bytes
Database Buffers 234881024 bytes
Redo Buffers 7639040 bytes
SQL>
[oracle@standby ~]$ rman target /
RMAN> restore standby controlfile from service orclcdb;
Starting restore at 15-APR-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=39 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service orclcdb
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output file name=/u01/app/oracle/oradata/orclstd/control01.ctl
Finished restore at 15-APR-20
Now,If the datafiles locations on the primary and standby database are different, then we will have to follow this step.
Since that we have restored the controlfile from the primary database, the datafiles locations will still be pointing to the
location of the datafiles that we have on the primary site.Depending on the configuration, if the path and names of the standby
datafiles after the standby controlfile refresh are correct then we can stop here .
RMAN> alter database mount;
released channel: ORA_DISK_1
Statement processed
RMAN> report schema;
Starting implicit crosscheck backup at 15-APR-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=81 device type=DISK
Crosschecked 3 objects
Finished implicit crosscheck backup at 15-APR-20
Starting implicit crosscheck copy at 15-APR-20
using channel ORA_DISK_1
Finished implicit crosscheck copy at 15-APR-20
searching for all files in the recovery area
cataloging files…
cataloging done
List of Cataloged Files
=======================
File Name: /u01/archive_log/ORCLSTD/archivelog/2020_04_12/o1_mf_1_22_h96ptj4c_.arc
File Name: /u01/archive_log/ORCLSTD/archivelog/2020_04_12/o1_mf_1_23_h96q00fh_.arc
File Name: /u01/archive_log/ORCLSTD/archivelog/2020_04_12/o1_mf_1_24_h96q01gx_.arc
File Name: /u01/archive_log/ORCLSTD/archivelog/2020_04_12/o1_mf_1_25_h96q04s3_.arc
File Name: /u01/archive_log/ORCLSTD/archivelog/2020_04_13/o1_mf_1_26_h98sxhhf_.arc
File Name: /u01/archive_log/ORCLSTD/archivelog/2020_04_13/o1_mf_1_27_h98sxmbo_.arc
File Name: /u01/archive_log/ORCLSTD/archivelog/2020_04_13/o1_mf_1_28_h98t1mlj_.arc
File Name: /u01/archive_log/ORCLSTD/archivelog/2020_04_13/o1_mf_1_29_h98t1y7n_.arc
File Name: /u01/archive_log/ORCLSTD/archivelog/2020_04_13/o1_mf_1_30_h98t3shx_.arc
File Name: /u01/archive_log/ORCLSTD/archivelog/2020_04_13/o1_mf_1_31_h98tmob9_.arc
File Name: /u01/archive_log/ORCLSTD/archivelog/2020_04_13/o1_mf_1_33_h98tmsjn_.arc
File Name: /u01/archive_log/ORCLSTD/archivelog/2020_04_13/o1_mf_1_32_h98tmsnt_.arc
File Name: /u01/archive_log/ORCLSTD/archivelog/2020_04_15/o1_mf_1_35_h9g4wz0m_.arc
File Name: /u01/archive_log/ORCLSTD/archivelog/2020_04_15/o1_mf_1_36_h9g4wz0w_.arc
File Name: /u01/archive_log/ORCLSTD/archivelog/2020_04_15/o1_mf_1_38_h9g4x09m_.arc
File Name: /u01/archive_log/ORCLSTD/archivelog/2020_04_15/o1_mf_1_37_h9g4x0cj_.arc
File Name: /u01/archive_log/ORCLSTD/archivelog/2020_04_15/o1_mf_1_34_h9g4x6vw_.arc
File Name: /u01/archive_log/ORCLSTD/archivelog/2020_04_15/o1_mf_1_40_h9g51842_.arc
File Name: /u01/archive_log/ORCLSTD/archivelog/2020_04_15/o1_mf_1_39_h9g518ns_.arc
File Name: /u01/archive_log/ORCLSTD/archivelog/2020_04_15/o1_mf_1_41_h9g518wp_.arc
File Name: /u01/archive_log/ORCLSTD/archivelog/2020_04_15/o1_mf_1_42_h9g5j00x_.arc
File Name: /u01/archive_log/ORCLSTD/archivelog/2020_04_15/o1_mf_1_43_h9g5otsq_.arc
File Name: /u01/archive_log/ORCLSTD/archivelog/2020_04_15/o1_mf_1_44_h9g5ozdk_.arc
File Name: /u01/archive_log/ORCLSTD/archivelog/2020_04_15/o1_mf_1_45_h9g5q9lo_.arc
File Name: /u01/archive_log/ORCLSTD/archivelog/2020_04_15/o1_mf_1_46_h9g5qmqr_.arc
File Name: /u01/archive_log/ORCLSTD/archivelog/2020_04_15/o1_mf_1_47_h9g5qp1j_.arc
File Name: /u01/archive_log/ORCLSTD/archivelog/2020_04_15/o1_mf_1_48_h9g5qrvj_.arc
File Name: /u01/archive_log/ORCLSTD/archivelog/2020_04_15/o1_mf_1_49_h9g6kot2_.arc
File Name: /u01/archive_log/ORCLSTD/archivelog/2020_04_15/o1_mf_1_50_h9g6wfv8_.arc
File Name: /u01/archive_log/ORCLSTD/archivelog/2020_04_15/o1_mf_1_60_h9g93nnk_.arc
File Name: /u01/archive_log/ORCLSTD/archivelog/2020_04_15/o1_mf_1_61_h9g93nv8_.arc
File Name: /u01/archive_log/ORCLSTD/archivelog/2020_04_15/o1_mf_1_62_h9g94yk8_.arc
File Name: /u01/archive_log/ORCLSTD/autobackup/2020_04_15/o1_mf_s_1037821819_h9g62f0r_.bkp
RMAN-06139: warning: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name ORCLSTD
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
—- ——– ——————– ——- ————————
1 910 SYSTEM *** /u01/app/oracle/oradata/orclstd/system01.dbf
3 590 SYSAUX *** /u01/app/oracle/oradata/orclstd/sysaux01.dbf
4 295 UNDOTBS1 *** /u01/app/oracle/oradata/orclstd/undotbs01.dbf
5 270 PDB$SEED:SYSTEM *** /u01/app/oracle/oradata/orclstd/pdbseed/system01.dbf
6 330 PDB$SEED:SYSAUX *** /u01/app/oracle/oradata/orclstd/pdbseed/sysaux01.dbf
7 5 USERS *** /u01/app/oracle/oradata/orclstd/users01.dbf
8 100 PDB$SEED:UNDOTBS1 *** /u01/app/oracle/oradata/orclstd/pdbseed/undotbs01.dbf
9 280 ORCLPDB1:SYSTEM *** /u01/app/oracle/oradata/orclstd/orclpdb1/system01.dbf
10 360 ORCLPDB1:SYSAUX *** /u01/app/oracle/oradata/orclstd/orclpdb1/sysaux01.dbf
11 100 ORCLPDB1:UNDOTBS1 *** /u01/app/oracle/oradata/orclstd/orclpdb1/undotbs01.dbf
12 5 ORCLPDB1:USERS *** /u01/app/oracle/oradata/orclstd/orclpdb1/users01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
—- ——– ——————– ———– ——————–
1 133 TEMP 32767 /u01/app/oracle/oradata/orclstd/temp01.dbf
2 36 PDB$SEED:TEMP 32767 /u01/app/oracle/oradata/orclstd/pdbseed/temp012020-04-03_09-48-16-376-AM.dbf
3 36 ORCLPDB1:TEMP 32767 /u01/app/oracle/oradata/orclstd/orclpdb1/temp01.dbf
Here , from the above report schema command, we can see the datafiles are pointing to path of standby site. Hence we will stop here
and check the mrp status in standby database.
on standby:-
SQL> select process,status,sequence#,thread# from gv$managed_standby;
PROCESS STATUS SEQUENCE# THREAD#
——— ———— ———- ———-
ARCH CLOSING 63 1
DGRD ALLOCATED 0 0
DGRD ALLOCATED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
PROCESS STATUS SEQUENCE# THREAD#
——— ———— ———- ———-
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
PROCESS STATUS SEQUENCE# THREAD#
——— ———— ———- ———-
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
RFS IDLE 0 1
PROCESS STATUS SEQUENCE# THREAD#
——— ———— ———- ———-
RFS IDLE 64 1
MRP0 APPLYING_LOG 64 1
35 rows selected.
So we see the mrp process is now applying log .
Let’s check the sync status of the standby database with the primary database.
On the primary:
SQL> select thread#,max(sequence#) from v$archived_log group by thread#;
THREAD# MAX(SEQUENCE#)
———- ————–
1 63
on standby:-
SQL> select thread#,max(sequence#) from v$archived_log where applied=’YES’ group by thread#;
THREAD# MAX(SEQUENCE#)
———- ————–
1 63
Now we see that the standby is sync with the primary.
P.S. If we see that from above report schema output , the path of datafiles in standby site are pointing towards primary site
then we will update the datafile and tempfile location as per standby site location.
RMAN> catalog start with ‘<path where the actual standby datafile existed>’;
RMAN> SWITCH DATABASE TO COPY;
Leave a Reply