Description: Suppose, there is an issue or outage at Primary Database site where Primary Database is not accessible at all. The issue can be anything like hardware/server/network/storage, etc. Your entire application is down. In this case, if you have physical standby database configured or available, then you can activate the physical standby database and application can run from standby site till Primary DB is accessible. Let's do the activity. Refer below steps to activate physical standby database. Activating physical standby database will open the standby database in read-write mode. There is difference between snapshot standby and activate standby. In snapshot standby method also, you can convert the physical standby database into read-write mode, but that is only for testing purpose and for specific duration and it is role reversable which means that snapshot standby database can be converted back into physical standby database, but once you activate the standby database, role reversable is not possible which means that you can not convert the activated database back into physical standby. Suppose, your old Primary DB is available after some time, but after activating standby database, then you can not revert back the activated standby DB to physical standby. In that case, you have to re-configure the old Primary database and perform the switchover operation. |
Activating Physical Standby Database in case of Primary DB is not available or inaccessible is called as failover.
|
Environment Details: - Oracle database 19.0.0.0 EE - Windows Server Windows 11 Pro 64 Bit As a DBA, you noticed that Primary database server is not accessible at all and application is entirely down. Issue can be anything like hardware, network, or storage. Your first task is to highlight the issue to the customer stating Primary DB server is not accessible and as an immediate solution, physical standby database has to be activated so that business will be up and running. Respective vendor team can check the issue at Primary site. To activate the physical standby database, perform the below steps. To achieve this in my environment, I have aborted the Primary database and kept the instance down. Step 1: Check the data loss before activating standby database. After the outage, your Primary and Standby databases can be in sync or can not be. If both were in sync before Primary gets inaccessible, then there can not be a data loss, but if Primary and Standby were not in sync, then there can be a data loss. You need to highlight this to customer before activating standby. For testing purpose, let's abort the Primary instance. On Primary: SQL> select name,db_unique_name,open_mode,database_role from v$database; NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE ----- ---------------- ------------ ---------------- PR PR READ WRITE PRIMARY SQL> select max(sequence#) from v$archived_log; MAX(SEQUENCE#) -------------- 290 SQL> shut abort ORACLE instance shut down. SQL> Now let's consider Primary DB server is not accessible at all. Let's start the scenario. In this case, Primary DB and Standby DB were in sync before Primary DB gets inaccessible. Hence, no data loss here. You can refer Standby DB alert log file for last applied archived log or you can query the DB to get the details. SQL> select name,db_unique_name,open_mode,database_role from v$database; NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE ----- -------------- ---------- ---------------- PR DR MOUNTED PHYSICAL STANDBY SQL> select max(sequence#) from v$archived_log where applied='YES'; MAX(SEQUENCE#) -------------- 290 When your Primary DB is not accessible, then "Possible Network disconnect from primary database" messages will be appeared in the Standby DB alert log file. Standby DB Alert Log File: 2023-12-30T20:54:44.413390+05:30 rfs (PID:15132): Possible network disconnect with primary database rfs (PID:15132): while processing B-1150397905.T-1.S-291 BNUM:9025 BCNT:1 rfs (PID:15132): Current process action IDLE, elapsed idle time 0 rfs (PID:15132): RFS client ASYNC ORL MULTI (PID:18104) 2023-12-30T20:54:44.628685+05:30 rfs (PID:4012): Possible network disconnect with primary database rfs (PID:4012): while processing B-0.T-0.S-0 BNUM:0 BCNT:0 rfs (PID:4012): Current process action IDLE, elapsed idle time 2999 rfs (PID:4012): RFS client ARCH GAP (PID:5344) 2023-12-30T20:54:45.070685+05:30 rfs (PID:1236): Possible network disconnect with primary database rfs (PID:1236): while processing B-1150397905.T-1.S-291 BNUM:0 BCNT:0 rfs (PID:1236): Current process action IDLE, elapsed idle time 62 rfs (PID:1236): RFS client GAP MANAGER (PID:11052) |
- If the protection mode is MAXIMUM PROTECTION mode, it is reset to MAXIMUM PERFORMANCE mode. You can upgrade the protection mode later. - If the protection mode is MAXIMUM AVAILABILITY or MAXIMUM PERFORMANCE, it remains unchanged. Step 2: Execute "FINISH" command before executing "ACTIVATE" command. This will cancel the MRP process and will apply all the available archived redo log files located on Standby DB server. SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH; Database altered. SQL> select name,open_mode,database_role,controlfile_type,log_mode,PROTECTION_MODE from v$database; NAME OPEN_MODE DATABASE_ROLE CONTROL LOG_MODE PROTECTION_MODE ---- --------- ---------------- ------- ---------- ------------------- PR MOUNTED PHYSICAL STANDBY STANDBY ARCHIVELOG MAXIMUM PERFORMANCE When you execute FINISH command, below messages will be appeared in Standby DB alert log file. Also, you can see the last applied archived log sequence number in Standby DB was 290. The next archived log sequence which was in current state at Primary and was in active state in Standby i.e. 291 which was applied by Terminal Recovery through Standby Redo Log file "D:\RUPESH\SETUPS\STANDBY\SRLS\STANDBY02.LOG". Standby DB Alert Log File: 2023-12-30T21:05:25.697101+05:30 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH 2023-12-30T21:05:25.702582+05:30 Terminal Recovery requested in process 15416 .... (PID:15416): The Time Management Interface (TMI) is being enabled for role transition .... (PID:15416): information. This will result in messages beingoutput to the alert log .... (PID:15416): file with the prefix 'TMI: '. This is being enabled to make the timing of .... (PID:15416): the various stages of the role transition available for diagnostic purposes. .... (PID:15416): This output will end when the role transition is complete. TMI: adbdrv termRecovery BEGIN 2023-12-30 21:05:25.716541 .... (PID:15416): Terminal Recovery: Stopping real time apply 2023-12-30T21:05:25.727075+05:30 PR00 (PID:16348): MRP0: Background Media Recovery cancelled with status 16037 2023-12-30T21:05:25.738071+05:30 Errors in file D:\RUPESH\APP\RGHUBADE\diag\rdbms\dr\dr\trace\dr_pr00_16348.trc: ORA-16037: user requested cancel of managed recovery operation PR00 (PID:16348): Managed Standby Recovery not using Real Time Apply Recovery interrupted! Recovered data files to a consistent state at change 14351402 Stopping change tracking 2023-12-30T21:05:26.166716+05:30 Errors in file D:\RUPESH\APP\RGHUBADE\diag\rdbms\dr\dr\trace\dr_pr00_16348.trc: ORA-16037: user requested cancel of managed recovery operation 2023-12-30T21:05:26.743302+05:30 .... (PID:15416): Terminal Recovery: Stopped real time apply TMI: adbdrv termRecovery END 2023-12-30 21:05:26.746696 2023-12-30T21:05:26.766438+05:30 Serial Media Recovery started NET (PID:15416): Managed Standby Recovery not using Real Time Apply Stopping change tracking NET (PID:15416): Begin: SRL archival NET (PID:15416): End: SRL archival NET (PID:15416): Terminal Recovery timestamp is '12/30/2023 21:05:26' NET (PID:15416): Terminal Recovery: applying standby redo logs. NET (PID:15416): Terminal Recovery: thread 1 seq# 291 redo required 2023-12-30T21:05:27.024100+05:30 NET (PID:15416): Terminal Recovery: 2023-12-30T21:05:27.035617+05:30 Recovery of Online Redo Log: Thread 1 Group 5 Seq 291 Reading mem 0 Mem# 0: D:\RUPESH\SETUPS\STANDBY\SRLS\STANDBY02.LOG 2023-12-30T21:05:27.102864+05:30 Incomplete Recovery applied until change 14351403 time 12/30/2023 20:54:34 Terminal Recovery: successful completion NET (PID:15416): Forcing ARSCN to IRSCN for TR SCN:0x0000000000dafc2b NET (PID:15416): Attempt to set limbo arscn SCN:0x0000000000dafc2b irscn SCN:0x0000000000dafc2b NET (PID:15416): Resetting standby activation ID 2850400372 (0xa9e5a874) Stopping change tracking Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH |
Step 3: Now it's time to activate the standby database. SQL> ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE; Database altered. When you execute "ACTIVATE" command, below messages will be appeared in Standby DB alert log file. Standby DB Alert Log: 2023-12-30T21:06:15.085472+05:30 ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE 2023-12-30T21:06:15.099275+05:30 NET (PID:15416): Begin: SRL archival NET (PID:15416): End: SRL archival Standby terminal recovery start SCN: 14351402 RESETLOGS after incomplete recovery UNTIL CHANGE 14351403 time 12/30/2023 20:54:34 Online log D:\RUPESH\SETUPS\STANDBY\DB\DR\ONLINELOG\O1_MF_1_LQLFRV0Y_.LOG: Thread 1 Group 1 was previously cleared Online log D:\RUPESH\SETUPS\STANDBY\DB\DR\ONLINELOG\O1_MF_2_LQLFRV74_.LOG: Thread 1 Group 2 was previously cleared Online log D:\RUPESH\SETUPS\STANDBY\DB\DR\ONLINELOG\O1_MF_3_LQLFRVG0_.LOG: Thread 1 Group 3 was previously cleared Standby became primary SCN: 14351401 2023-12-30T21:06:15.545648+05:30 Setting recovery target incarnation to 14 2023-12-30T21:06:15.561868+05:30 NET (PID:15416): RT: Role transition work is not done NET (PID:15416): The Time Management Interface (TMI) is being enabled for role transition NET (PID:15416): information. This will result in messages beingoutput to the alert log NET (PID:15416): file with the prefix 'TMI: '. This is being enabled to make the timing of NET (PID:15416): the various stages of the role transition available for diagnostic purposes. NET (PID:15416): This output will end when the role transition is complete. NET (PID:15416): Redo network throttle feature is disabled at mount time AUDIT_TRAIL initialization parameter is changed back to its original value as specified in the parameter file. 2023-12-30T21:06:15.679735+05:30 NET (PID:15416): Database role cleared from PHYSICAL STANDBY [kcvs.c:1114] ACTIVATE STANDBY: Complete - Database mounted as primary Completed: ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE ACTIVATE command will not open your database. You have to open it manually. SQL> select name,open_mode,database_role,controlfile_type,log_mode,PROTECTION_MODE from v$database; NAME OPEN_MODE DATABASE_ROLE CONTROL LOG_MODE PROTECTION_MODE ---- --------- ------------- ------- ---------- ------------------- PR MOUNTED PRIMARY CURRENT ARCHIVELOG MAXIMUM PERFORMANCE SQL> ALTER DATABASE OPEN; Database altered. SQL> select name,open_mode,database_role,controlfile_type,log_mode,PROTECTION_MODE from v$database; NAME OPEN_MODE DATABASE_ROLE CONTROL LOG_MODE PROTECTION_MODE ---- ---------- ------------- ------- ---------- ------------------- PR READ WRITE PRIMARY CURRENT ARCHIVELOG MAXIMUM PERFORMANCE Standby DB Alert Log: 2023-12-30T21:06:46.643652+05:30 ALTER DATABASE OPEN 2023-12-30T21:06:46.645722+05:30 TMI: adbdrv open database BEGIN 2023-12-30 21:06:46.643652 Smart fusion block transfer is disabled: instance mounted in exclusive mode. Endian type of dictionary set to little 2023-12-30T21:06:46.826028+05:30 Assigning activation ID 2851425057 (0xa9f54b21) Redo log for group 1, sequence 1 is not located on DAX storage Thread 1 opened at log sequence 1 Current log# 1 seq# 1 mem# 0: D:\RUPESH\SETUPS\STANDBY\DB\DR\ONLINELOG\O1_MF_1_LQLFRV0Y_.LOG Successful open of redo thread 1 2023-12-30T21:06:46.947754+05:30 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set Stopping change tracking 2023-12-30T21:06:46.970635+05:30 ARC2 (PID:3268): Becoming the 'no SRL' ARCH 2023-12-30T21:06:47.189181+05:30 Undo initialization recovery: Parallel FPTR failed: start:8491781 end:8491781 diff:0 ms (0.0 seconds) Undo initialization recovery: err:0 start: 8491781 end: 8491828 diff: 47 ms (0.0 seconds) [15416] Successfully onlined Undo Tablespace 2. Undo initialization online undo segments: err:0 start: 8491828 end: 8491890 diff: 62 ms (0.1 seconds) Undo initialization finished serial:0 start:8491781 end:8491890 diff:109 ms (0.1 seconds) Dictionary check beginning Dictionary check complete 2023-12-30T21:06:47.362051+05:30 Starting background process SMCO 2023-12-30T21:06:47.401745+05:30 SMCO started with pid=27, OS id=18900 2023-12-30T21:06:48.379568+05:30 Database Characterset is AL32UTF8 2023-12-30T21:06:48.537560+05:30 No Resource Manager plan active NET (PID:15416): Enable RFS client [kcrlc.c:622] joxcsys_required_dirobj_exists: directory object exists with required path D:\RUPESH\SETUPS\WINDOWS.X64_193000_DB_HOME\JAVAVM\ADMIN\, pid 15404 cid 0 replication_dependency_tracking turned off (no async multimaster replication found) 2023-12-30T21:06:49.758700+05:30 LOGSTDBY: Validating controlfile with logical metadata LOGSTDBY: Validation complete Starting background process AQPC 2023-12-30T21:06:49.868656+05:30 AQPC started with pid=40, OS id=13440 2023-12-30T21:06:50.245765+05:30 TMI: adbdrv open database END 2023-12-30 21:06:50.236808 Starting background process CJQ0 2023-12-30T21:06:50.341273+05:30 CJQ0 started with pid=42, OS id=19124 2023-12-30T21:06:51.350612+05:30 Completed: ALTER DATABASE OPEN |
Step 4: Perform post checks after activating Physical Standby DB. SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination D:\RUPESH\Setups\Standby\ARCH Oldest online log sequence 1 Next log sequence to archive 1 Current log sequence 1 SQL> col TO_CHAR(RESETLOGS_CHANGE#) for a17 SQL> alter session set nls_date_format='dd-mm-yyyy hh24:mi:ss'; Session altered. SQL> select INCARNATION#,to_char(RESETLOGS_CHANGE#),RESETLOGS_TIME,to_char(PRIOR_RESETLOGS_CHANGE#),PRIOR_RESETLOGS_TIME, status,RESETLOGS_ID,PRIOR_INCARNATION# from V$DATABASE_INCARNATION; INCARNATION# TO_CHAR(RESETLOGS RESETLOGS_TIME TO_CHAR(PRIOR_RESETLOGS_CHANGE#) PRIOR_RESETLOGS_TIM STATUS RESETLOGS_ID PRIOR_INCARNATION# ------------ ----------------- ------------------- -------------------------------- ------------------- ------- ------------ ------------------ 1 1 30-05-2019 03:09:50 0 PARENT 1009595390 0 2 1928875 18-04-2023 17:15:32 1 30-05-2019 03:09:50 PARENT 1134494132 1 3 10902231 14-10-2023 00:20:56 1928875 18-04-2023 17:15:32 PARENT 1150158056 2 4 11012770 14-10-2023 01:17:48 10902231 14-10-2023 00:20:56 PARENT 1150161468 3 5 11131041 14-10-2023 03:11:36 11012770 14-10-2023 01:17:48 PARENT 1150168296 4 6 11280291 14-10-2023 06:28:56 11131041 14-10-2023 03:11:36 PARENT 1150180136 5 7 11395555 14-10-2023 08:03:58 11280291 14-10-2023 06:28:56 PARENT 1150185838 6 8 11536905 15-10-2023 16:36:53 11395555 14-10-2023 08:03:58 PARENT 1150303013 7 9 11678865 15-10-2023 22:02:49 11536905 15-10-2023 16:36:53 PARENT 1150322569 8 10 11787022 15-10-2023 22:50:36 11678865 15-10-2023 22:02:49 PARENT 1150325436 9 11 11929766 16-10-2023 16:24:05 11787022 15-10-2023 22:50:36 PARENT 1150388645 10 12 12046881 16-10-2023 18:14:42 11929766 16-10-2023 16:24:05 PARENT 1150395282 11 13 12155220 16-10-2023 18:58:25 12046881 16-10-2023 18:14:42 PARENT 1150397905 12 14 14351404 30-12-2023 21:06:15 12155220 16-10-2023 18:58:25 CURRENT 1156971975 13 14 rows selected. RMAN> list incarnation; using target database control file instead of recovery catalog List of Database Incarnations DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time ------- ------- -------- ------------ --- ---------- ---------- 1 1 PR 2829010735 PARENT 1 30-MAY-19 2 2 PR 2829010735 PARENT 1928875 18-APR-23 3 3 PR 2829010735 PARENT 10902231 14-OCT-23 4 4 PR 2829010735 PARENT 11012770 14-OCT-23 5 5 PR 2829010735 PARENT 11131041 14-OCT-23 6 6 PR 2829010735 PARENT 11280291 14-OCT-23 7 7 PR 2829010735 PARENT 11395555 14-OCT-23 8 8 PR 2829010735 PARENT 11536905 15-OCT-23 9 9 PR 2829010735 PARENT 11678865 15-OCT-23 10 10 PR 2829010735 PARENT 11787022 15-OCT-23 11 11 PR 2829010735 PARENT 11929766 16-OCT-23 12 12 PR 2829010735 PARENT 12046881 16-OCT-23 13 13 PR 2829010735 PARENT 12155220 16-OCT-23 14 14 PR 2829010735 CURRENT 14351404 30-DEC-23 SQL> select max(sequence#) from v$archived_log where RESETLOGS_CHANGE#=14351404; MAX(SEQUENCE#) -------------- SQL> alter system switch logfile; System altered. SQL> / System altered. SQL> / System altered. You have to add "RESETLOGS_CHANGE# " number in where clause to check the DC-DR sync status in new resetlog. SQL> select max(sequence#) from v$archived_log where RESETLOGS_CHANGE#=14351404; MAX(SEQUENCE#) -------------- 3 |
Thanks for reading this post ! Please comment if you like this post ! Click on FOLLOW to get next blog updates !
Thanks for posting
ReplyDelete