Reinstate Previous Primary DB as standby after Failover completed
Fast_start failover initiated by Oracle:
Oracle will try to reinstate old primary db as new standby. In case of failure (corrupted datafiles, ….) it needs to be done manually.
Manual failover:
==> Oracle will disable all databases except failover target. They need to be reinstated/recreated manually as standby.
Check current status:
DGMGRL> show configuration;
Reinstate:
DGMGRL> reinstate database <old_primary>
You might have to check broker log ($ORACLE_BASE/diag/rdbms/<db>/<ORACLE_SID>/trace/drc<ORACLE_SID>.log) for additional information if reinstate fails.
Recreate standby DB from new primary DB
Manual Failover to DR database (DBR) because Oracle crashed on primary (DB) and standby (DB2) due to dis-/remounted Oracle disks.
Previous Primary (DB) needs to be recreated. Standby (DB2) was reinstated, but log apply failed (ORA-16856: transport lag could not be determined) – re-create standby, too.
Initial state:
DGMGRL> show configuration lag
Configuration - my_dg_config
Protection Mode: MaxAvailability
Members:
DBR - Primary database
Error: ORA-16810: multiple errors or warnings detected for the member
DB - Physical standby database (disabled)
ORA-16795: the standby database needs to be re-created
Transport Lag: (unknown)
Apply Lag: (unknown)
DB2 - Physical standby database
Warning: ORA-16809: multiple warnings detected for the member
Transport Lag: (unknown)
Apply Lag: (unknown)
Fast-Start Failover: Disabled
DGMGRL> show database DB2
Database - DB2
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: (unknown)
Apply Lag: (unknown)
Average Apply Rate: (unknown)
Real Time Query: OFF
Instance(s):
DB2
Database Error(s):
ORA-16700: The standby database has diverged from the primary database.
Database Warning(s):
ORA-16854: apply lag could not be determined
ORA-16856: transport lag could not be determined
ORA-16857: member disconnected from redo source for longer than specified threshold
Database Status: ERROR
DGMGRL> show database DB;
Database - DB
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: (unknown)
Apply Lag: (unknown)
Average Apply Rate: (unknown)
Real Time Query: OFF
Instance(s):
DB
Database Status: DISABLED - ORA-16795: the standby database needs to be re-created
DGMGRL> show database DBR;
Database - DBR
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
DBR
Database Warning(s):
ORA-16629: database reports a different protection level from the protection mode
Database Status: WARNING
Oracle Error Doc:
- ORA-16795: the standby database needs to be re-created
Cause: A switchover or failover operation caused this database to require re-creation. The database was marked for re-creation because it was not a viable standby database for the new primary database. Until this error status is resolved for this database, information about this database and the broker configuration to which it belongs is unavailable to a broker client that is connected to this database. Therefore, all commands directed by that client to this database cannot be completed.
Action: Re-create (or flash back) the standby database. Connect to the primary database in the broker configuration and reenable broker management of that database. Once enabled, it is possible to connect to that standby database and manage it with the broker. Alternatively, many client commands that cannot be completed at the standby database when in this error state can be completed successfully when issued to the primary database. In this case, simply reconnect to the primary database and retry the command.
- ORA-16700: The standby database has diverged from the primary database.
Cause: The primary database may have been flashed back or restored from a backup set and then reopened with the RESETLOGS option. Or, a role change to a logical standby database was performed.
Action: Re-create the standby database from the primary database or flash back the standby database to the same point the primary database had been flashed back to. If the roles are switched back to the original primary database, then no action is necessary.
Re-creation steps (example DB2 showing multiple errors):
1) Start standby db in mount state, stop redo apply:
SQL> shutdown immediate
SQL> startup nomount
Stop/disable redo apply:
DGMGRL> edit database DB2 set STATE='APPLY-OFF' ;
2) Identify the datafiles which are out of sync:
Primary (DBR):
SQL> select thread#,max(sequence#) from v$archived_log group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 14694
SQL> select process,status,sequence# from v$managed_standby;
PROCESS STATUS SEQUENCE#
--------- ------------ ----------
ARCH CLOSING 61
DGRD ALLOCATED 0
DGRD ALLOCATED 0
ARCH CLOSING 63
ARCH CLOSING 60
ARCH CLOSING 51
DGRD ALLOCATED 0
DGRD ALLOCATED 0
Note: sequence reset during failover!
SQL> select max(sequence#),resetlogs_change#,resetlogs_time from v$log_history group by resetlogs_change#, resetlogs_time ;
MAX(SEQUENCE#) RESETLOGS_CHANGE# RESETLOGS_TIME
-------------- ----------------- -------------------
14693 179254067 2020-12-01 07:40:06
xx 471817568 2022-06-06 11:57:21
SQL> select HXFIL File_num,substr(HXFNM,1,40),fhscn from x$kcvfh;
"FILE_NUM","SUBSTR(HXFNM,1,40)","FHSCN"
1,"+DGDATA/DBR/DATAFILE/system.286.10827","472402862"
2,"+DGDATA/DBR/DATAFILE/zonos_oss_10121_","472402862"
3,"+DGDATA/DBR/DATAFILE/sysaux.288.10827","472402862"
4,"+DGDATA/DBR/DATAFILE/undotbs1.289.108","472402862"
5,"+DGDATA/DBR/DATAFILE/zonos_oss_10121.","472402862"
6,"+DGDATA/DBR/DATAFILE/users.259.108279","472402862"
7,"+DGDATA/DBR/DATAFILE/customer_10121.2","472402862"
8,"+DGDATA/DBR/DATAFILE/customer_10121_i","472402862"
Standby (DB2):
SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 14693
SQL> select process,status,sequence# from v$managed_standby;
PROCESS STATUS SEQUENCE#
--------- ------------ ----------
ARCH CONNECTED 0
DGRD ALLOCATED 0
DGRD ALLOCATED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
MRP0 WAIT_FOR_LOG 14694
SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Diference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP B THREAD#)) ARCH, (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP B THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 14693 14693 0
SQL> select sequence#,process,pid,status,client_process,block#, active_agents, known_agents,BLOCKS FROM gv$managed_standby ORDER BY thread#, pid;
SEQUENCE# PROCESS PID STATUS CLIENT_P BLOCK# ACTIVE_AGENTS KNOWN_AGENTS BLOCKS
---------- --------- ------------------------ ------------ -------- ---------- ------------- ------------ ----------
0 ARCH 126420 CONNECTED ARCH 0 0 0 0
0 DGRD 126423 ALLOCATED N/A 0 0 0 0
0 DGRD 126427 ALLOCATED N/A 0 0 0 0
0 ARCH 126431 CONNECTED ARCH 0 0 0 0
0 ARCH 126434 CONNECTED ARCH 0 0 0 0
0 ARCH 126436 CONNECTED ARCH 0 0 0 0
14694 MRP0 147978 WAIT_FOR_LOG N/A 0 17 17 0
SQL> set markup csv on;
SQL> select HXFIL File_num,substr(HXFNM,1,40),fhscn from x$kcvfh;
"FILE_NUM","SUBSTR(HXFNM,1,40)","FHSCN"
1,"+DGDATA/DB2/DATAFILE/system.259.10827","471816272"
2,"+DGDATA/DB2/DATAFILE/zonos_oss_10121_","471816272"
3,"+DGDATA/DB2/DATAFILE/sysaux.257.10827","471816272"
4,"+DGDATA/DB2/DATAFILE/undotbs1.291.108","471816272"
5,"+DGDATA/DB2/DATAFILE/zonos_oss_10121.","471816272"
6,"+DGDATA/DB2/DATAFILE/users.289.108279","471816272"
7,"+DGDATA/DB2/DATAFILE/customer_10121.2","471816272"
8,"+DGDATA/DB2/DATAFILE/customer_10121_i","471816272"
SQL> SELECT CURRENT_SCN FROM V$DATABASE;
"CURRENT_SCN"
471816271
3) Recover DB2 from primary
$ rlwrap rman target sys/xxx_@DB2_DGB
RMAN> report schema;
using target database control file instead of recovery catalog
RMAN-06139: warning: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name DB2
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 1130 SYSTEM *** +DGFRA/DB2/DATAFILE/system.3399.1082800975
2 461 zonos_oss_10121_idx *** +DGFRA/DB2/DATAFILE/zonos_oss_10121_idx.3400.1082801001
3 2230 SYSAUX *** +DGFRA/DB2/DATAFILE/sysaux.3395.1082800935
4 815 UNDOTBS1 *** +DGFRA/DB2/DATAFILE/undotbs1.3403.1082800991
5 746 zonos_oss_10121 *** +DGFRA/DB2/DATAFILE/zonos_oss_10121.3402.1082800999
6 5 USERS *** +DGFRA/DB2/DATAFILE/users.266.1082801005
7 3515 customer_10121 *** +DGFRA/DB2/DATAFILE/customer_10121.3396.1082800961
8 100 customer_10121_idx *** +DGFRA/DB2/DATAFILE/customer_10121_idx.267.1082801005
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 197 TEMP 32767 +DGDATA/DBR/TEMPFILE/temp.263.1083328959
RMAN> recover standby database from service DBR_DGB;
Starting recover at 07-JUN-22
Oracle instance started
Total System Global Area 9932109768 bytes
Fixed Size 12445640 bytes
Variable Size 1577058304 bytes
Database Buffers 8321499136 bytes
Redo Buffers 21106688 bytes
contents of Memory Script:
{
restore standby controlfile from service 'DBR_DGB';
alter database mount standby database;
}
executing Memory Script
Starting restore at 07-JUN-22
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=690 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service DBR_DGB
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
output file name=+DGDATA/DB2/CONTROLFILE/current.281.1082799879
output file name=+DGFRA/DB2/CONTROLFILE/current.3409.1082799879
Finished restore at 07-JUN-22
released channel: ORA_DISK_1
Statement processed
Executing: alter system set standby_file_management=manual
contents of Memory Script:
{
set newname for tempfile 1 to
"+DGDATA/DB2/TEMPFILE/temp.300.1082885495";
switch tempfile all;
set newname for datafile 1 to
"+DGDATA/DB2/DATAFILE/system.259.1082799885";
set newname for datafile 2 to
"+DGDATA/DB2/DATAFILE/zonos_oss_10121_idx.258.1082799893";
set newname for datafile 3 to
"+DGDATA/DB2/DATAFILE/sysaux.257.1082799897";
set newname for datafile 4 to
"+DGDATA/DB2/DATAFILE/undotbs1.291.1082799921";
set newname for datafile 5 to
"+DGDATA/DB2/DATAFILE/zonos_oss_10121.290.1082799929";
set newname for datafile 6 to
"+DGDATA/DB2/DATAFILE/users.289.1082799935";
set newname for datafile 7 to
"+DGDATA/DB2/DATAFILE/customer_10121.285.1082799937";
set newname for datafile 8 to
"+DGDATA/DB2/DATAFILE/customer_10121_idx.287.1082799953";
catalog datafilecopy "+DGDATA/DB2/DATAFILE/system.259.1082799885",
"+DGDATA/DB2/DATAFILE/zonos_oss_10121_idx.258.1082799893",
"+DGDATA/DB2/DATAFILE/sysaux.257.1082799897",
"+DGDATA/DB2/DATAFILE/undotbs1.291.1082799921",
"+DGDATA/DB2/DATAFILE/zonos_oss_10121.290.1082799929",
"+DGDATA/DB2/DATAFILE/users.289.1082799935",
"+DGDATA/DB2/DATAFILE/customer_10121.285.1082799937",
"+DGDATA/DB2/DATAFILE/customer_10121_idx.287.1082799953";
switch datafile all;
}
executing Memory Script
executing command: SET NEWNAME
Starting implicit crosscheck backup at 07-JUN-22
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=346 device type=DISK
Crosschecked 10 objects
Finished implicit crosscheck backup at 07-JUN-22
Starting implicit crosscheck copy at 07-JUN-22
using channel ORA_DISK_1
Finished implicit crosscheck copy at 07-JUN-22
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: +DGFRA/DB2/BACKUPSET/2022_06_07/nnndn1_dailyimage_0.2901.1106709035
File Name: +DGFRA/DB2/BACKUPSET/2022_06_07/annnf0_tag20220607t031125_0.5212.1106709085
File Name: +DGFRA/DB2/ARCHIVELOG/2022_06_07/thread_1_seq_66.2881.1106763693
File Name: +DGFRA/DB2/ARCHIVELOG/2022_06_07/thread_1_seq_65.5216.1106763701
File Name: +DGFRA/DB2/ARCHIVELOG/2022_06_07/thread_1_seq_68.1929.1106765025
File Name: +DGFRA/DB2/ARCHIVELOG/2022_06_07/thread_1_seq_69.5214.1106765041
File Name: +DGFRA/DB2/ARCHIVELOG/2022_06_07/thread_1_seq_70.5192.1106765325
File Name: +DGFRA/DB2/ARCHIVELOG/2022_06_07/thread_1_seq_71.2873.1106769111
File Name: +DGFRA/DB2/ARCHIVELOG/2022_06_07/thread_1_seq_72.4208.1106770993
File Name: +DGFRA/DB2/ARCHIVELOG/2022_06_07/thread_1_seq_73.1645.1106771661
File Name: +DGFRA/DB2/ARCHIVELOG/2022_06_07/thread_1_seq_74.5937.1106771669
File Name: +DGFRA/DB2/ARCHIVELOG/2022_06_07/thread_1_seq_75.2907.1106771687
File Name: +DGFRA/DB2/ARCHIVELOG/2022_06_07/thread_1_seq_76.3624.1106771979
File Name: +DGFRA/DB2/ARCHIVELOG/2022_06_07/thread_1_seq_77.1004.1106772033
File Name: +DGFRA/DB2/ARCHIVELOG/2022_06_07/thread_1_seq_78.4733.1106772053
File Name: +DGFRA/DB2/AUTOBACKUP/2022_06_07/s_1106395671.6142.1106709095
File Name: +DGFRA/DB2/DATAFILE/sysaux.3395.1082800935
File Name: +DGFRA/DB2/DATAFILE/customer_10121.3396.1082800961
File Name: +DGFRA/DB2/DATAFILE/system.3399.1082800975
File Name: +DGFRA/DB2/DATAFILE/undotbs1.3403.1082800991
File Name: +DGFRA/DB2/DATAFILE/zonos_oss_10121.3402.1082800999
File Name: +DGFRA/DB2/DATAFILE/zonos_oss_10121_idx.3400.1082801001
File Name: +DGFRA/DB2/DATAFILE/customer_10121_idx.267.1082801005
File Name: +DGFRA/DB2/DATAFILE/users.266.1082801005
renamed tempfile 1 to +DGDATA/DB2/TEMPFILE/temp.300.1082885495 in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
cataloged datafile copy
datafile copy file name=+DGDATA/DB2/DATAFILE/system.259.1082799885 RECID=5687 STAMP=1106772267
cataloged datafile copy
datafile copy file name=+DGDATA/DB2/DATAFILE/zonos_oss_10121_idx.258.1082799893 RECID=5688 STAMP=1106772267
cataloged datafile copy
datafile copy file name=+DGDATA/DB2/DATAFILE/sysaux.257.1082799897 RECID=5689 STAMP=1106772267
cataloged datafile copy
datafile copy file name=+DGDATA/DB2/DATAFILE/undotbs1.291.1082799921 RECID=5690 STAMP=1106772267
cataloged datafile copy
datafile copy file name=+DGDATA/DB2/DATAFILE/zonos_oss_10121.290.1082799929 RECID=5691 STAMP=1106772267
cataloged datafile copy
datafile copy file name=+DGDATA/DB2/DATAFILE/users.289.1082799935 RECID=5692 STAMP=1106772267
cataloged datafile copy
datafile copy file name=+DGDATA/DB2/DATAFILE/customer_10121.285.1082799937 RECID=5693 STAMP=1106772267
cataloged datafile copy
datafile copy file name=+DGDATA/DB2/DATAFILE/customer_10121_idx.287.1082799953 RECID=5694 STAMP=1106772267
datafile 1 switched to datafile copy
input datafile copy RECID=5687 STAMP=1106772267 file name=+DGDATA/DB2/DATAFILE/system.259.1082799885
datafile 2 switched to datafile copy
input datafile copy RECID=5688 STAMP=1106772267 file name=+DGDATA/DB2/DATAFILE/zonos_oss_10121_idx.258.1082799893
datafile 3 switched to datafile copy
input datafile copy RECID=5689 STAMP=1106772267 file name=+DGDATA/DB2/DATAFILE/sysaux.257.1082799897
datafile 4 switched to datafile copy
input datafile copy RECID=5690 STAMP=1106772267 file name=+DGDATA/DB2/DATAFILE/undotbs1.291.1082799921
datafile 5 switched to datafile copy
input datafile copy RECID=5691 STAMP=1106772267 file name=+DGDATA/DB2/DATAFILE/zonos_oss_10121.290.1082799929
datafile 6 switched to datafile copy
input datafile copy RECID=5692 STAMP=1106772267 file name=+DGDATA/DB2/DATAFILE/users.289.1082799935
datafile 7 switched to datafile copy
input datafile copy RECID=5693 STAMP=1106772267 file name=+DGDATA/DB2/DATAFILE/customer_10121.285.1082799937
datafile 8 switched to datafile copy
input datafile copy RECID=5694 STAMP=1106772267 file name=+DGDATA/DB2/DATAFILE/customer_10121_idx.287.1082799953
Executing: alter database rename file '+DGDATA/DBR/ONLINELOG/group_1.271.1082798943' to '+DGDATA/DB2/ONLINELOG/group_1.286.1082799963'
Executing: alter database rename file '+DGFRA/DBR/ONLINELOG/group_1.5364.1082798943' to '+DGFRA/DB2/ONLINELOG/group_1.256.1082799963'
Executing: alter database rename file '+DGDATA/DBR/ONLINELOG/group_2.270.1082798943' to '+DGDATA/DB2/ONLINELOG/group_2.288.1082799965'
Executing: alter database rename file '+DGFRA/DBR/ONLINELOG/group_2.4250.1082798943' to '+DGFRA/DB2/ONLINELOG/group_2.257.1082799965'
Executing: alter database rename file '+DGDATA/DBR/ONLINELOG/group_3.269.1082798943' to '+DGDATA/DB2/ONLINELOG/group_3.284.1082799965'
Executing: alter database rename file '+DGFRA/DBR/ONLINELOG/group_3.3829.1082798943' to '+DGFRA/DB2/ONLINELOG/group_3.5202.1082799967'
contents of Memory Script:
{
recover database from service 'DBR_DGB';
}
executing Memory Script
Starting recover at 07-JUN-22
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service DBR_DGB
destination for restore of datafile 00001: +DGDATA/DB2/DATAFILE/system.259.1082799885
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 network backup set from service DBR_DGB
destination for restore of datafile 00002: +DGDATA/DB2/DATAFILE/zonos_oss_10121_idx.258.1082799893
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 network backup set from service DBR_DGB
destination for restore of datafile 00003: +DGDATA/DB2/DATAFILE/sysaux.257.1082799897
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 network backup set from service DBR_DGB
destination for restore of datafile 00004: +DGDATA/DB2/DATAFILE/undotbs1.291.1082799921
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 network backup set from service DBR_DGB
destination for restore of datafile 00005: +DGDATA/DB2/DATAFILE/zonos_oss_10121.290.1082799929
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 network backup set from service DBR_DGB
destination for restore of datafile 00006: +DGDATA/DB2/DATAFILE/users.289.1082799935
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 network backup set from service DBR_DGB
destination for restore of datafile 00007: +DGDATA/DB2/DATAFILE/customer_10121.285.1082799937
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 network backup set from service DBR_DGB
destination for restore of datafile 00008: +DGDATA/DB2/DATAFILE/customer_10121_idx.287.1082799953
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
starting media recovery
archived log for thread 1 with sequence 80 is already on disk as file +DGFRA/DB2/ARCHIVELOG/2022_06_07/thread_1_seq_80.1514.1106772277
archived log for thread 1 with sequence 81 is already on disk as file +DGFRA/DB2/ARCHIVELOG/2022_06_07/thread_1_seq_81.5209.1106772281
archived log file name=+DGFRA/DB2/ARCHIVELOG/2022_06_07/thread_1_seq_80.1514.1106772277 thread=1 sequence=80
archived log file name=+DGFRA/DB2/ARCHIVELOG/2022_06_07/thread_1_seq_81.5209.1106772281 thread=1 sequence=81
media recovery complete, elapsed time: 00:00:01
Finished recover at 07-JUN-22
Executing: alter system set standby_file_management=auto
Finished recover at 07-JUN-22
RMAN> report schema;
RMAN-06139: warning: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name DB2
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 1130 SYSTEM *** +DGDATA/DB2/DATAFILE/system.259.1082799885
2 461 zonos_oss_10121_idx *** +DGDATA/DB2/DATAFILE/zonos_oss_10121_idx.258.1082799893
3 2230 SYSAUX *** +DGDATA/DB2/DATAFILE/sysaux.257.1082799897
4 815 UNDOTBS1 *** +DGDATA/DB2/DATAFILE/undotbs1.291.1082799921
5 746 zonos_oss_10121 *** +DGDATA/DB2/DATAFILE/zonos_oss_10121.290.1082799929
6 5 USERS *** +DGDATA/DB2/DATAFILE/users.289.1082799935
7 3658 customer_10121 *** +DGDATA/DB2/DATAFILE/customer_10121.285.1082799937
8 100 customer_10121_idx *** +DGDATA/DB2/DATAFILE/customer_10121_idx.287.1082799953
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 197 TEMP 32767 +DGDATA/DB2/TEMPFILE/temp.300.1082885495
RMAN> SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
-----------
472474379
4) Check state
Primary (DBR):
SQL> select thread#,max(sequence#) from v$archived_log group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 14694
SQL> select process,status,sequence# from v$managed_standby;
PROCESS STATUS SEQUENCE#
--------- ------------ ----------
ARCH CLOSING 67
DGRD ALLOCATED 0
DGRD ALLOCATED 0
ARCH CLOSING 80
ARCH CLOSING 74
ARCH CLOSING 81
DGRD ALLOCATED 0
DGRD ALLOCATED 0
LGWR WRITING 82
SQL> select HXFIL File_num,substr(HXFNM,1,40),fhscn from x$kcvfh;
"FILE_NUM","SUBSTR(HXFNM,1,40)","FHSCN"
1,"+DGDATA/DBR/DATAFILE/system.286.10827","472474380"
2,"+DGDATA/DBR/DATAFILE/zonos_oss_10121_","472474380"
3,"+DGDATA/DBR/DATAFILE/sysaux.288.10827","472474380"
4,"+DGDATA/DBR/DATAFILE/undotbs1.289.108","472474390"
5,"+DGDATA/DBR/DATAFILE/zonos_oss_10121.","472474407"
6,"+DGDATA/DBR/DATAFILE/users.259.108279","472474414"
7,"+DGDATA/DBR/DATAFILE/customer_10121.2","472474417"
8,"+DGDATA/DBR/DATAFILE/customer_10121_i","472474471"
Standby (DB2):
SQL> select HXFIL File_num,substr(HXFNM,1,40),fhscn from x$kcvfh;
"FILE_NUM","SUBSTR(HXFNM,1,40)","FHSCN"
1,"+DGDATA/DB2/DATAFILE/system.259.10827","472474380"
2,"+DGDATA/DB2/DATAFILE/zonos_oss_10121_","472474380"
3,"+DGDATA/DB2/DATAFILE/sysaux.257.10827","472474380"
4,"+DGDATA/DB2/DATAFILE/undotbs1.291.108","472474390"
5,"+DGDATA/DB2/DATAFILE/zonos_oss_10121.","472474407"
6,"+DGDATA/DB2/DATAFILE/users.289.108279","472474414"
7,"+DGDATA/DB2/DATAFILE/customer_10121.2","472474417"
8,"+DGDATA/DB2/DATAFILE/customer_10121_i","472474471"
8 rows selected.
SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 83 81 2
SQL> set lines 300
SQL> select sequence#,process,pid,status,client_process,block#, active_agents, known_agents,BLOCKS FROM gv$managed_standby ORDER BY thread#, pid;
SEQUENCE# PROCESS PID STATUS CLIENT_P BLOCK# ACTIVE_AGENTS KNOWN_AGENTS BLOCKS
---------- --------- ------------------------ ------------ -------- ---------- ------------- ------------ ----------
0 DGRD 49937 ALLOCATED N/A 0 0 0 0
0 DGRD 49939 ALLOCATED N/A 0 0 0 0
0 RFS 49989 IDLE UNKNOWN 0 0 0 0
83 ARCH 49935 CLOSING ARCH 1 0 0 3
80 ARCH 49941 CLOSING ARCH 1 0 0 35
81 ARCH 49943 CLOSING ARCH 1 0 0 17
82 ARCH 49945 CLOSING ARCH 2048 0 0 900
0 RFS 49970 IDLE Archival 0 0 0 0
84 RFS 49983 IDLE LGWR 3788 0 0 1
5) Start log apply
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
SQL> select process,status,sequence# from v$managed_standby;
PROCESS STATUS SEQUENCE#
--------- ------------ ----------
ARCH CLOSING 83
DGRD ALLOCATED 0
DGRD ALLOCATED 0
ARCH CLOSING 80
ARCH CLOSING 81
ARCH CLOSING 82
RFS IDLE 0
RFS IDLE 84
RFS IDLE 0
MRP0 APPLYING_LOG 84
DGMGRL> show database DB2
Database - DB2
Role: PHYSICAL STANDBY
Intended State: APPLY-OFF
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Average Apply Rate: (unknown)
Real Time Query: OFF
Instance(s):
DB2
Error: ORA-16765: Redo Apply is running
Database Status: ERROR
SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 83 83 0
DGMGRL> show database DB2
Database - DB2
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Average Apply Rate: 17.00 KByte/s
Real Time Query: OFF
Instance(s):
DB2
Database Status:
SUCCESS
6) Open standby db
SQL> select name, open_mode, database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
DB MOUNTED PHYSICAL STANDBY
SQL> ALTER DATABASE OPEN READ ONLY;
Database altered.
SQL> SELECT INSTANCE_NAME, STATUS, DATABASE_STATUS FROM V$INSTANCE;
INSTANCE_NAME STATUS DATABASE_STATUS
---------------- ------------ -----------------
DB2 OPEN ACTIVE
SQL> SELECT open_mode FROM V$DATABASE;
OPEN_MODE
--------------------
READ ONLY WITH APPLY
SQL> select RECOVERY_MODE from V$ARCHIVE_DEST_STATUS ;
RECOVERY_MODE
----------------------------------
MANAGED REAL TIME APPLY
IDLE
...
7) Check redo apply
Primary (DBR):
SQL> select max(sequence#) from v$log;
MAX(SEQUENCE#)
--------------
87
Standby (DB2):
SQL> select process,status,sequence# from v$managed_standby;
PROCESS STATUS SEQUENCE#
--------- ------------ ----------
ARCH CLOSING 83
DGRD ALLOCATED 0
DGRD ALLOCATED 0
ARCH CLOSING 84
ARCH CLOSING 86
ARCH CLOSING 85
RFS IDLE 0
RFS IDLE 87
RFS IDLE 0
MRP0 APPLYING_LOG 87
10 rows selected.
DGMGRL> show database DB2
Database - DB2
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Average Apply Rate: 4.00 KByte/s
Real Time Query: ON
Instance(s):
DB2
Database Status:
SUCCESS
Recover broken standby DB from primary DB after incomplete failover (ORA-16143: RFS connections not allowed…)
Oracle left 1 of 2 standby db after incomplete/aborted failover in a state which could not be fixed. Standby DB can’t be opened.
Primary:
ORA-16143: RFS connections not allowed during or after terminal recovery
Standby (failover target):
rfs (PID:34840): No connections allowed during/after terminal recovery
Data Guard: Redo Apply Services will be started after instance open completes
Standby crash recovery failed to bring standby database to a consistent point because needed redo hasn’t arrived yet.
Recover standby db according to instructions in Oracle KB doc:
Rolling Forward a Physical Standby Using Recover From Service Command in 12c (Doc ID 1987763.1)
1) Preparations
On broken standby:
SQL> shutdown immediate
SQL> startup mount
Stop/disable redo apply:
DGMGRL> edit database DB2 set STATE='APPLY-OFF' ;
Identify the datafiles on standby database which are out of sync:
On primary:
SQL> select HXFIL File_num,substr(HXFNM,1,40),fhscn from x$kcvfh;
"FILE_NUM","SUBSTR(HXFNM,1,40)","FHSCN"
1,"+DGDATA/DB/DATAFILE/system.260.104481","405028572"
2,"+DGDATA/DB/DATAFILE/zonos_oss_10121_i","405028572"
3,"+DGDATA/DB/DATAFILE/sysaux.262.104481","405028572"
4,"+DGDATA/DB/DATAFILE/undotbs1.263.1044","405028572"
5,"+DGDATA/DB/DATAFILE/zonos_oss_10121.2","405028572"
6,"+DGDATA/DB/DATAFILE/users.265.1044819","405028572"
7,"+DGDATA/DB/DATAFILE/customer_10121.26","405028572"
8,"+DGDATA/DB/DATAFILE/customer_10121_id","405028572"
8 rows selected.
On standby:
SQL> select HXFIL File_num,substr(HXFNM,1,40),fhscn from x$kcvfh;
"FILE_NUM","SUBSTR(HXFNM,1,40)","FHSCN"
1,"+DGDATA/DB2/DATAFILE/system.259.10827","399876111"
2,"+DGDATA/DB2/DATAFILE/zonos_oss_10121_","399876128"
3,"+DGDATA/DB2/DATAFILE/sysaux.257.10827","399876136"
4,"+DGDATA/DB2/DATAFILE/undotbs1.291.108","399876273"
5,"+DGDATA/DB2/DATAFILE/zonos_oss_10121.","399876346"
6,"+DGDATA/DB2/DATAFILE/users.289.108279","399876361"
7,"+DGDATA/DB2/DATAFILE/customer_10121.2","399876366"
8,"+DGDATA/DB2/DATAFILE/customer_10121_i","399876767"
8 rows selected.
SQL> SELECT CURRENT_SCN FROM V$DATABASE;
"CURRENT_SCN"
399440107
Comparing the FHSCN of primary and standby datafiles, no SCN of standby matches the SCN of primary, so all datafiles of standby are lagging behind primary.
The currect SCN of standby db will be needed later in step 6).
2) Rollforward data files on standby to the same point as on primary
On standby:
$ rlwrap rman target sys/passwd@DB2_DGB
...
connected to target database: DB (DBID=552521712, not open)
RMAN> recover database from service DB_DGB noredo using compressed backupset;
Starting recover at 23-FEB-22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=641 device type=DISK
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service DB_DGB
destination for restore of datafile 00001: +DGDATA/DB2/DATAFILE/system.259.1082799885
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 DB_DGB
destination for restore of datafile 00002: +DGDATA/DB2/DATAFILE/zonos_oss_10121_idx.258.1082799893
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 DB_DGB
destination for restore of datafile 00003: +DGDATA/DB2/DATAFILE/sysaux.257.1082799897
channel ORA_DISK_1: restore complete, elapsed time: 00:00:36
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service DB_DGB
destination for restore of datafile 00004: +DGDATA/DB2/DATAFILE/undotbs1.291.1082799921
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 DB_DGB
destination for restore of datafile 00005: +DGDATA/DB2/DATAFILE/zonos_oss_10121.290.1082799929
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 DB_DGB
destination for restore of datafile 00006: +DGDATA/DB2/DATAFILE/users.289.1082799935
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 DB_DGB
destination for restore of datafile 00007: +DGDATA/DB2/DATAFILE/customer_10121.285.1082799937
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 DB_DGB
destination for restore of datafile 00008: +DGDATA/DB2/DATAFILE/customer_10121_idx.287.1082799953
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished recover at 23-FEB-22
3) Check SCN
On standby:
$ rlwrap sqlplus sys/passwd@DB2_DGB as sysdba
SQL> set markup csv on;
SQL> select HXFIL File_num,substr(HXFNM,1,40),fhscn from x$kcvfh;
"FILE_NUM","SUBSTR(HXFNM,1,40)","FHSCN"
1,"+DGDATA/DB2/DATAFILE/system.259.10827","405039416"
2,"+DGDATA/DB2/DATAFILE/zonos_oss_10121_","405039444"
3,"+DGDATA/DB2/DATAFILE/sysaux.257.10827","405039474"
4,"+DGDATA/DB2/DATAFILE/undotbs1.291.108","405039544"
5,"+DGDATA/DB2/DATAFILE/zonos_oss_10121.","405039574"
6,"+DGDATA/DB2/DATAFILE/users.289.108279","405039613"
7,"+DGDATA/DB2/DATAFILE/customer_10121.2","405039640"
8,"+DGDATA/DB2/DATAFILE/customer_10121_i","405039682"
8 rows selected.
SCN should now match SCN on primary, or at least gap should be reduced.
4) Restore standby controlfile from primary
On standby:
SQL> shutdown immediate
SQL> startup nomount
$ rlwrap rman target sys/passwd@DB2_DGB
...
connected to target database: DB (not mounted)
RMAN> RESTORE STANDBY CONTROLFILE FROM SERVICE DB_DGB ;
Starting restore at 23-FEB-22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=690 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service DB_DGB
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output file name=+DGDATA/DB2/CONTROLFILE/current.281.1082799879
output file name=+DGFRA/DB2/CONTROLFILE/current.3409.1082799879
Finished restore at 23-FEB-22
RMAN> alter database mount;
released channel: ORA_DISK_1
Statement processed
RMAN> report schema;
Starting implicit crosscheck backup at 23-FEB-22
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=396 device type=DISK
Crosschecked 5 objects
Finished implicit crosscheck backup at 23-FEB-22
Starting implicit crosscheck copy at 23-FEB-22
using channel ORA_DISK_1
Crosschecked 8 objects
Finished implicit crosscheck copy at 23-FEB-22
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: +DGFRA/DB2/AUTOBACKUP/2022_02_17/s_1096563761.5209.1096830645
File Name: +DGFRA/DB2/DATAFILE/sysaux.3395.1082800935
File Name: +DGFRA/DB2/DATAFILE/customer_10121.3396.1082800961
File Name: +DGFRA/DB2/DATAFILE/system.3399.1082800975
File Name: +DGFRA/DB2/DATAFILE/undotbs1.3403.1082800991
File Name: +DGFRA/DB2/DATAFILE/zonos_oss_10121.3402.1082800999
File Name: +DGFRA/DB2/DATAFILE/zonos_oss_10121_idx.3400.1082801001
File Name: +DGFRA/DB2/DATAFILE/customer_10121_idx.267.1082801005
File Name: +DGFRA/DB2/DATAFILE/users.266.1082801005
RMAN-06139: warning: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name DB2
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 0 SYSTEM *** +DGDATA/DB/DATAFILE/system.260.1044819581
2 0 zonos_oss_10121_idx *** +DGDATA/DB/DATAFILE/zonos_oss_10121_idx.261.1044819595
3 0 SYSAUX *** +DGDATA/DB/DATAFILE/sysaux.262.1044819597
4 0 UNDOTBS1 *** +DGDATA/DB/DATAFILE/undotbs1.263.1044819613
5 0 zonos_oss_10121 *** +DGDATA/DB/DATAFILE/zonos_oss_10121.264.1044819619
6 0 USERS *** +DGDATA/DB/DATAFILE/users.265.1044819621
7 0 customer_10121 *** +DGDATA/DB/DATAFILE/customer_10121.266.1044819623
8 0 customer_10121_idx *** +DGDATA/DB/DATAFILE/customer_10121_idx.267.1044819625
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 197 TEMP 32767 +DGDATA/DB/TEMPFILE/temp.275.1055605339
5) Update location of data+tmp files in standby controlfile from primary to standby locations
RMAN> catalog start with '+DGDATA/DB2/DATAFILE/';
searching for all files that match the pattern +DGDATA/DB2/DATAFILE/
List of Files Unknown to the Database
=====================================
File Name: +DGDATA/DB2/DATAFILE/system.259.1082799885
File Name: +DGDATA/DB2/DATAFILE/zonos_oss_10121_idx.258.1082799893
File Name: +DGDATA/DB2/DATAFILE/sysaux.257.1082799897
File Name: +DGDATA/DB2/DATAFILE/undotbs1.291.1082799921
File Name: +DGDATA/DB2/DATAFILE/zonos_oss_10121.290.1082799929
File Name: +DGDATA/DB2/DATAFILE/users.289.1082799935
File Name: +DGDATA/DB2/DATAFILE/customer_10121.285.1082799937
File Name: +DGDATA/DB2/DATAFILE/customer_10121_idx.287.1082799953
Do you really want to catalog the above files (enter YES or NO)? y
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: +DGDATA/DB2/DATAFILE/system.259.1082799885
File Name: +DGDATA/DB2/DATAFILE/zonos_oss_10121_idx.258.1082799893
File Name: +DGDATA/DB2/DATAFILE/sysaux.257.1082799897
File Name: +DGDATA/DB2/DATAFILE/undotbs1.291.1082799921
File Name: +DGDATA/DB2/DATAFILE/zonos_oss_10121.290.1082799929
File Name: +DGDATA/DB2/DATAFILE/users.289.1082799935
File Name: +DGDATA/DB2/DATAFILE/customer_10121.285.1082799937
File Name: +DGDATA/DB2/DATAFILE/customer_10121_idx.287.1082799953
RMAN> SWITCH DATABASE TO COPY;
datafile 1 switched to datafile copy "+DGDATA/DB2/DATAFILE/system.259.1082799885"
datafile 2 switched to datafile copy "+DGDATA/DB2/DATAFILE/zonos_oss_10121_idx.258.1082799893"
datafile 3 switched to datafile copy "+DGDATA/DB2/DATAFILE/sysaux.257.1082799897"
datafile 4 switched to datafile copy "+DGDATA/DB2/DATAFILE/undotbs1.291.1082799921"
datafile 5 switched to datafile copy "+DGDATA/DB2/DATAFILE/zonos_oss_10121.290.1082799929"
datafile 6 switched to datafile copy "+DGDATA/DB2/DATAFILE/users.289.1082799935"
datafile 7 switched to datafile copy "+DGDATA/DB2/DATAFILE/customer_10121.285.1082799937"
datafile 8 switched to datafile copy "+DGDATA/DB2/DATAFILE/customer_10121_idx.287.1082799953"
6) Check for new datafiles
Use the current SCN returned in step 4 to determine if new data files were added to the primary database since the standby database was last refreshed.
If yes, these datafiles need to be restored on the standby from the primary database.
SCN standby returned in step 1):
“CURRENT_SCN”
399440107
Now:
RMAN> SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
-----------
405041079
RMAN> SELECT file# FROM V$DATAFILE WHERE creation_change# >= 399440107;
no rows selected
7) Update names of the online redo logs and standby redo logs in the standby control file
SQL> select GROUP# from v$logfile where TYPE='STANDBY' group by GROUP#;
GROUP#
----------
4
5
6
7
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 4;
Database altered.
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 5;
Database altered.
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 6;
Database altered.
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 7;
Database altered.
8) Enable Redo Apply
On standby:
DGMGRL> edit database DB2 set state=APPLY-ON;
Succeeded.
DGMGRL> show database DB2
Database - DB2
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Average Apply Rate: 1.88 MByte/s
Real Time Query: OFF
Instance(s):
DB2
Database Status:
SUCCESS
DGMGRL> show configuration
Configuration - my_dg_config
Protection Mode: MaxAvailability
Members:
DB - Primary database
DBR - Physical standby database
DB2 - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 54 seconds ago)
9) Open standby db
On primary:
Switch the archived redo log files:
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
System altered.
On standby:
SQL> RECOVER DATABASE;
SQL> ALTER DATABASE OPEN READ ONLY;
Database altered.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
10) Check status and redo apply
On primary:
SQL> select thread#,max(sequence#) from v$archived_log group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 11503
SQL> select process,status,sequence# from v$managed_standby;
PROCESS STATUS SEQUENCE#
--------- ------------ ----------
DGRD ALLOCATED 0
ARCH CLOSING 11502
DGRD ALLOCATED 0
ARCH CLOSING 11496
ARCH CLOSING 11500
ARCH CLOSING 11497
LNS WRITING 11504
DGRD ALLOCATED 0
LGWR WRITING 11504
DGRD ALLOCATED 0
10 rows selected.
On Standby 1:
SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 11503
SQL> select process,status,sequence# from v$managed_standby;
PROCESS STATUS SEQUENCE#
--------- ------------ ----------
ARCH CLOSING 11501
DGRD ALLOCATED 0
DGRD ALLOCATED 0
ARCH CLOSING 11502
ARCH CLOSING 11503
ARCH CLOSING 11499
RFS IDLE 0
RFS IDLE 11504
RFS IDLE 0
RFS IDLE 0
MRP0 APPLYING_LOG 11504
11 rows selected.
On Standby 2:
SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 11503
SQL> select process,status,sequence# from v$managed_standby;
PROCESS STATUS SEQUENCE#
--------- ------------ ----------
ARCH CLOSING 11502
DGRD ALLOCATED 0
DGRD ALLOCATED 0
ARCH CLOSING 11495
ARCH CLOSING 11503
ARCH CLOSING 11494
RFS IDLE 0
RFS IDLE 11504
MRP0 APPLYING_LOG 11504
9 rows selected.
Status in DataGuard:
DGMGRL> show configuration lag
Configuration - my_dg_config
Protection Mode: MaxAvailability
Members:
DB - Primary database
DBR - Physical standby database
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
DB2 - Physical standby database
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 54 seconds ago)
11) Enable FSFO
DGMGRL> enable fast_start failover
DGMGRL> show fast_start failover
Recover datafiles on standby DB from primary DB after failover to out-of-sync standby
FRA+DATA ASM running out of disk space on both PRD servers. Fast_start failover target DB out of synch. Customer performed manual failover to target standby and disabled DataGuard broker on original primary DB.
Multiple issues during restoring original configuration – newer incarnation on standby as on primary since both DB was opened read-write, additional data file on standby not found on primary, some commands mentioned above not supported in Oracle 12c, ….
Recreation of standby db by restoring particular (huge – 15TB) datafiles while applying logs to synch with primary. Recreate standby db from primary service as described above failed (error on additional datafile, open standby read-only failed with “ORA-01152 file xy was not restored from a sufficiently old backup”, …).
Standby db open mounted.
1) Reset standby to previous incarnation that matches current incarnation on primary:
Check incarnations on current primary and standby:
RMAN> list incarnation of database;
Reset standby to current incarantion on primary:
RMAN> reset database to incarnation <number>;
2) Restore datafiles from primary service
After starting redo apply, MRP process dies with “ORA-10567: Redo is inconsistent with data block (file xy …”.
Check MRP process:
SQL> select process,status,sequence# from v$managed_standby;
Check if datafile is actually corrupted:
RMAN> VALIDATE DATAFILE ...
Restore datafile on standby from primary service:
# Stop the Manged recovery on Standby
RMAN> alter database recover managed standby database cancel ;
RMAN> restore datafile <fileno> from service <Primary service name> ;
Example:
DataGuard output – redo apply on MYDB stopped:
... MYDBPRD8003 - Primary database
MYDBPRD8050 - Physical standby database
MYDB - Physical standby database
Error: ORA-16766: Redo Apply is stopped ...
Oracle alert log:
Wed Jun 14 15:52:03 2023
Recovery interrupted!
Wed Jun 14 15:55:43 2023
Recovered data files to a consistent state at change 946969506341
Wed Jun 14 15:55:43 2023
Errors in file /u01/diag/rdbms/MYDB/MYDBPRD/trace/MYDBPRD_pr00_66801.trc:
ORA-00448: normal completion of background process
Wed Jun 14 15:55:43 2023
Errors in file /u01/diag/rdbms/MYDB/MYDBPRD/trace/MYDBPRD_mrp0_66760.trc:
ORA-00756: recovery detected a lost write of a data block
ORA-10567: Redo is inconsistent with data block (file# 18, block# 1709574423, file offset is 3240288256 bytes)
ORA-10564: tablespace customer_10121_idx_1
ORA-01110: data file 18: '+DGDATA/MYDB/DATAFILE/customer_10121_idx_1.273.1048227701'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 522490
Wed Jun 14 15:55:43 2023
MRP0: Background Media Recovery process shutdown (MYDBPRD)
Check datafile 18 => 15 TB customer idx file:
RMAN> report schema;
using target database control file instead of recovery catalog
RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name MYDB
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 4970 SYSTEM *** +DGDATA/MYDB/DATAFILE/system.287.1039713277
2 1024 customer_10121 *** +DGDATA/MYDB/DATAFILE/customer_10121.281.1069669141
3 32000 SYSAUX *** +DGDATA/MYDB/DATAFILE/sysaux.286.1039713281
4 32767 UNDOTBS1 *** +DGDATA/MYDB/DATAFILE/undotbs1.283.1039713289
5 1243 EXAMPLE *** +DGDATA/MYDB/DATAFILE/example.285.1039713283
6 5 USERS *** +DGDATA/MYDB/DATAFILE/users.284.1039713285
7 3905552 customer_10121_idx *** +DGDATA/MYDB/DATAFILE/customer_10121_idx.277.1039713293
8 856192 customer_10121_idx_2 *** +DGDATA/MYDB/DATAFILE/customer_10121_idx_2.265.1061199147
9 100 logpartapi_10121_idx *** +DGDATA/MYDB/DATAFILE/logpartapi_10121_idx.262.1039713289
10 1353344 customer_10121_idx_3 *** +DGDATA/MYDB/DATAFILE/customer_10121_idx_3.261.1061199163
11 100 logportapi_10121_idx *** +DGDATA/MYDB/DATAFILE/logportapi_10121_idx.260.1039713297
12 2679402 customer_10121_idx_4 *** +DGDATA/MYDB/DATAFILE/customer_10121_idx_4.263.1061199181
13 100 useracc_10121_idx *** +DGDATA/MYDB/DATAFILE/useracc_10121_idx.264.1039713303
14 224868 zonos_oss_10121 *** +DGDATA/MYDB/DATAFILE/zonos_oss_10121.274.1039713323
15 251423 zonos_oss_10121_idx *** +DGDATA/MYDB/DATAFILE/zonos_oss_10121_idx.266.1039713311
16 4444672 customer_10121_idx_5 *** +DGDATA/MYDB/DATAFILE/customer_10121_idx_5.282.1061199197
17 100 voucheracc_10121_idx *** +DGDATA/MYDB/DATAFILE/voucheracc_10121_idx.278.1039713311
18 15801816 customer_10121_idx_1 *** +DGDATA/MYDB/DATAFILE/customer_10121_idx_1.273.1048227701
...
Restore datafile from primary service (creates incremental datafile backup set on primary, transfers file to target, and restores file from backup) :
RMAN> restore datafile 18 from service MYDBPRD8003;
...
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service MYDBPRD8003
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00018 to +DGDATA/MYDB/DATAFILE/customer_10121_idx_1.273.1048227701
Check progress of rman job:
SQL> SELECT opname, round(TIME_REMAINING/3600, 2), round(sofar/totalwork*100) "% Complete" FROM v$session_longops WHERE opname LIKE 'RMAN%' AND totalwork != 0 AND sofar <> totalwork ORDER BY 2;
SQL> select sid,serial,filename,status,bytes/total_bytes*100 "Completed",EFFECTIVE_BYTES_PER_SECOND/1024/1024 "MB/S" from V$BACKUP_ASYNC_IO where type = 'OUTPUT' and bytes<>0 order by status;
SQL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
SQL> select sid, row_type, operation, status, to_char(start_time,'dd-mm-yyyy hh24:mi:ss') start_time, to_char(end_time,'dd-mm-yyyy hh24:mi:ss') end_time from v$rman_status where operation like '%RESTORE%';
Start log apply again, and repeat steps if Oracle complains about next datafile.
