Activity Description: Scenario Example: In cascaded standby environment, Application team has done major changes in application at Primary site only , not at Near DR and FAR DR sites. They want to run EOD operations on Production environment (PR Site). If it is successful, then they want to proceed with changes and continue business operations from PR Site. If not successful, then they want to abort the EOD and activate the NR standby database where they have not made any changes in application. During the activity time, MRP will be stopped on both Near DR and FAR DR servers so that in case of revert back, they don't want to apply archived logs of failed EOD, but log shipping from PR to NR and NR to DR destinations will be enabled so that in case of successful testing, they don't want to wait for longer time to ship the logs. Activity POA: 1) Capture the archived log sequence numbers at PR, NR, and DR sites. On PR, archived log sequence is 196. On NR, applied log sequence is 196. On DR, applied log sequence is 196. 2) Stop MRP on both NR and DR servers. Do not defer the target archive log destinations i.e. from PR to NR and NR to DR. Destinations will be enabled so that logs will be transferred but will not apply. 3) Perform application load testing or EOD operations containing DML operations at PR site. 4) Now capture archived log sequence numbers at all sites. On PR, archived log sequence is 207. ---> Your Primary DB will be far ahead from both NR and DR databases. On NR, applied log sequence is 196. On DR, applied log sequence is 196. 5) Now suppose, your testing is wrong and not successful at any how, then abort the testing and shutdown the Primary database. 6) Activate the NR standby database and re-run the EOD operations once from NR site and continue the transactions or business from there. Scenario Description: Let's say your Primary database is PR, 1st Standby Database is NR (Near DR) and the 2nd Standby Database is DR(Far DR). Now you want to perform EOD operations or an application load testing considering DML operations on PR. If the testing is successful, then customer can not wait for log shipping time which can be in minutes or hours and hence he may ask you to enable log shipping during this activity time. If the testing or modifications go wrong due to any reason and you are in the middle of the testing, then your plan is to abort the testing and scrap the entire Primary Database and re-run the EOD operations from NR site and continue the business operations from there by activating NR Database(1st Standby). Now here, you have two cases. In case1, if testing is successful, then you can go ahead with continuing business operations at PR site. In case2, if testing is not successful or failed, then stop the Primary database and activate NR standby database as per the above POA and re-run the EOD from NR DB and continue the business operations from there (NR - New Primary). Ideally, we execute "ALTER DATABSE RECOVER MANAGED STANDBY DATABASE FINISH" command before ACTIVATE STANDBY command. What it does? Since my log shipping is enabled between PR to NR and NR to DR, will the the FINISH command start the MRP process and will MRP apply all the available archived logs from sequence 197 to 206 in NR database ? Will these logs from log sequence 197 to 206 get applied in DR standby database as well after starting MRP? Should we go ahead with FINISH or without FINISH? Let's start the test cases. 1) Test Case1: Activate Standby without FINISH On NR: SQL> ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE; SQL> alter database open; OR 2) Test Case2: Activate Standby with FINISH On NR: SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH; SQL> ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE; SQL> alter database open; Please refer the link "What if you execute activate standby command on 1st Standby Database in case of cascaded standby environment" where I have demonstrated entire steps in Test Case1. Now lets start with Test Case2 i.e. WITH FINISH. Test Case2: Operating System: Windows 11 Pro Database: Oracle 19.0.0.0 EE Environment 1: (Primary Server) Database Name: PR db_unique_name: PR Database Role: Primary SRLs Configured(Standby Redo Logs): YES(Not needed) Environment 2: (Standby Server - Near DR) Database Name: PR db_unique_name: NR fal_server=PR Database Role: Physical Standby SRLs Configured(Standby Redo Logs): YES Environment 3: (Standby Server - Far DR) Database Name: PR db_unique_name: DR fal_server=NR Database Role: Physical Standby SRLs Configured(Standby Redo Logs): YES |
Step1: Here, you can see all three DBs are in sync. On Primary: log_archive_dest_2='service=NR valid_for=(all_logfiles,all_roles) db_unique_name=NR' log_archive_dest_state_2=ENABLE protection_mode=MAXIMUM PERFORMANCE 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#) -------------- 196 SQL> select INCARNATION#,RESETLOGS_CHANGE#,RESETLOGS_ID,PRIOR_RESETLOGS_CHANGE#,status from v$database_incarnation; INCARNATION# RESETLOGS_CHANGE# RESETLOGS_ID PRIOR_RESETLOGS_CHANGE# STATUS ------------ ----------------- ------------ ----------------------- ------- 1 1 1009595390 0 PARENT 2 1928875 1134494132 1 PARENT 3 10902231 1150158056 1928875 PARENT 4 11012770 1150161468 10902231 PARENT 5 11131041 1150168296 11012770 PARENT 6 11280291 1150180136 11131041 PARENT 7 11395555 1150185838 11280291 PARENT 8 11536905 1150303013 11395555 PARENT 9 11678865 1150322569 11536905 PARENT 10 11787022 1150325436 11678865 PARENT 11 11929766 1150388645 11787022 PARENT 12 12046881 1150395282 11929766 PARENT 13 12155220 1150397905 12046881 CURRENT SQL> select protection_mode from v$database; PROTECTION_MODE -------------------- MAXIMUM PERFORMANCE SQL> set lines 300 pages 3000 SQL> col DEST_NAME for a23 SQL> select dest_id,dest_name,status,type,process,LOG_SEQUENCE,AFFIRM,TRANSMIT_MODE from v$archive_dest where dest_id=2; DEST_ID DEST_NAME STATUS TYPE PROCESS LOG_SEQUENCE AFF TRANSMIT_MOD ------- ------------------ ------ ------- ------- ------------ --- ------------ 2 LOG_ARCHIVE_DEST_2 VALID PUBLIC LGWR 197 NO ASYNCHRONOUS On Near DR (NR): log_archive_dest_2='service=DR valid_for=(all_logfiles,all_roles) db_unique_name=DR' log_archive_dest_state_2=ENABLE protection_mode=MAXIMUM PERFORMANCE SQL> select name,db_unique_name,open_mode,database_role from v$database; NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE ------ --------------- ---------- ---------------- PR NR MOUNTED PHYSICAL STANDBY SQL> select max(sequence#) from v$archived_log where applied='YES'; MAX(SEQUENCE#) -------------- 196 SQL> select INCARNATION#,RESETLOGS_CHANGE#,RESETLOGS_ID,PRIOR_RESETLOGS_CHANGE#,status from v$database_incarnation; INCARNATION# RESETLOGS_CHANGE# RESETLOGS_ID PRIOR_RESETLOGS_CHANGE# STATUS ------------ ----------------- ------------ ----------------------- ------- 1 1 1009595390 0 PARENT 2 1928875 1134494132 1 PARENT 3 10902231 1150158056 1928875 PARENT 4 11012770 1150161468 10902231 PARENT 5 11131041 1150168296 11012770 PARENT 6 11280291 1150180136 11131041 PARENT 7 11395555 1150185838 11280291 PARENT 8 11536905 1150303013 11395555 PARENT 9 11678865 1150322569 11536905 PARENT 10 11787022 1150325436 11678865 PARENT 11 11929766 1150388645 11787022 PARENT 12 12046881 1150395282 11929766 PARENT 13 12155220 1150397905 12046881 CURRENT SQL> select protection_mode from v$database; PROTECTION_MODE -------------------- MAXIMUM PERFORMANCE SQL> set lines 300 pages 3000 SQL> col DEST_NAME for a23 SQL> select dest_id,dest_name,status,type,process,LOG_SEQUENCE,AFFIRM,TRANSMIT_MODE from v$archive_dest where dest_id=2; DEST_ID DEST_NAME STATUS TYPE PROCESS LOG_SEQUENCE AFF TRANSMIT_MOD ------- ------------------ ------ ------- ------- ------------ --- ------------ 2 LOG_ARCHIVE_DEST_2 VALID PUBLIC ARCH 0 NO SYNCHRONOUS On FAR DR (DR): 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#) -------------- 196 SQL> select INCARNATION#,RESETLOGS_CHANGE#,RESETLOGS_ID,PRIOR_RESETLOGS_CHANGE#,status from v$database_incarnation; INCARNATION# RESETLOGS_CHANGE# RESETLOGS_ID PRIOR_RESETLOGS_CHANGE# STATUS ------------ ----------------- ------------ ----------------------- ------- 1 1 1009595390 0 PARENT 2 1928875 1134494132 1 PARENT 3 10902231 1150158056 1928875 PARENT 4 11012770 1150161468 10902231 PARENT 5 11131041 1150168296 11012770 PARENT 6 11280291 1150180136 11131041 PARENT 7 11395555 1150185838 11280291 PARENT 8 11536905 1150303013 11395555 PARENT 9 11678865 1150322569 11536905 PARENT 10 11787022 1150325436 11678865 PARENT 11 11929766 1150388645 11787022 PARENT 12 12046881 1150395282 11929766 PARENT 13 12155220 1150397905 12046881 CURRENT SQL> select protection_mode from v$database; PROTECTION_MODE -------------------- MAXIMUM PERFORMANCE SQL> set lines 300 pages 3000 SQL> col DEST_NAME for a23 SQL> select dest_id,dest_name,status,type,process,LOG_SEQUENCE,AFFIRM,TRANSMIT_MODE from v$archive_dest where dest_id=2; DEST_ID DEST_NAME STATUS TYPE PROCESS LOG_SEQUENCE AFF TRANSMIT_MOD ------- ------------------ --------- ------- ------- ------------ --- ------------ 2 LOG_ARCHIVE_DEST_2 INACTIVE PUBLIC ARCH 0 NO SYNCHRONOUS No need to set any destination parameters since this is last target standby database server. log_archive_dest_2 ----> Not set db_unique_name=DR' ----> Not set log_archive_dest_state_2 ----> Not set protection_mode=MAXIMUM PERFORMANCE |
Step 2: Stop MRP on both Near DR and FAR DR servers. Do not defer log shipping between PR to NR and NR to DR so that during the EOD operations or application load testing time, logs can transfer but will not apply. On NR: SQL> recover managed standby database cancel; Media recovery complete. On DR: SQL> recover managed standby database cancel; Media recovery complete. At this stage, On PR, archived log sequence is 196. On NR, applied log sequence is 196. On DR, applied log sequence is 196. Step 3: Now suppose, you started an application load testing or EOD operations considering DML operations at PR site and you are in the middle of the activity. Suddenly, due to any reason, you want to abort the testing and go back, then shut down the PR instance as per the POA. On Primary: For testing purpose, create a dummy table in Primary database and insert some values. SQL> create table with_finish(id number(10)); Table created. SQL> insert into with_finish values(1); 1 row created. SQL> insert into with_finish values(2); 1 row created. SQL> insert into with_finish values(3); 1 row created. SQL> insert into with_finish values(4); 1 row created. SQL> insert into with_finish values(5); 1 row created. SQL> commit; Commit complete. SQL> select * from with_finish; ID ---------- 1 2 3 4 5 SQL> alter system switch logfile; System altered. ..... ..... SQL> alter system switch logfile; System altered. SQL> select max(sequence#) from v$archived_log where RESETLOGS_CHANGE#=12046881; MAX(SEQUENCE#) -------------- 206 Suppose at this point, you think that testing is wrong and want to abort the testing, then shut down the primary instance. SQL> shut immediate; Now here, Last archived sequence on Primary(PR) is 206. (The current log sequence number at PR is 197 before the table creation and the logs from seq 198 to 206 are of Load testing/EOD). Last applied sequence on NR is 196. Last applied sequence on DR is 196. In this case, the current log sequence is 197 at PR site just before the table creation and hence consider the logs starting from sequence 198 to 206 are not required since these contain failed EOD operations data and we don't want this data. If we apply this data, then there can be data integrity issue and hence we have to avoid this. But this is achieved in Test Case1. Here, we will perform Test Case2 scenario by "WITH FINISH" command and will see what will happen. You can see all the logs starting from sequence 197 to 206 are shipped to NR and DR since log shipping destination is enabled, but have not yet been applied since MRP is down. DR Alert Log File: 2023-11-12T09:50:20.651125+05:30 rfs (PID:5680): Selected LNO:4 for T-1.S-197 dbid 2829010735 branch 1150397905 2023-11-12T09:50:20.697806+05:30 ARC3 (PID:17392): Archived Log entry 16 added for T-1.S-197 ID 0xa991dc35 LAD:1 2023-11-12T09:50:20.760299+05:30 rfs (PID:21136): Opened log for T-1.S-198 dbid 2829010735 branch 1150397905 2023-11-12T09:50:20.760299+05:30 rfs (PID:21136): Archived Log entry 17 added for B-1150397905.T-1.S-198 ID 0xa991dc35 LAD:2 2023-11-12T09:50:22.386550+05:30 rfs (PID:5680): Selected LNO:4 for T-1.S-199 dbid 2829010735 branch 1150397905 2023-11-12T09:50:22.433498+05:30 ARC0 (PID:16740): Archived Log entry 18 added for T-1.S-199 ID 0xa991dc35 LAD:1 2023-11-12T09:50:25.346508+05:30 rfs (PID:21136): Selected LNO:4 for T-1.S-200 dbid 2829010735 branch 1150397905 2023-11-12T09:50:25.384748+05:30 ARC1 (PID:17272): Archived Log entry 19 added for T-1.S-200 ID 0xa991dc35 LAD:1 2023-11-12T09:50:28.416611+05:30 rfs (PID:5680): Selected LNO:4 for T-1.S-201 dbid 2829010735 branch 1150397905 2023-11-12T09:50:28.463485+05:30 ARC2 (PID:21896): Archived Log entry 20 added for T-1.S-201 ID 0xa991dc35 LAD:1 2023-11-12T09:50:34.441827+05:30 rfs (PID:21136): Selected LNO:4 for T-1.S-202 dbid 2829010735 branch 1150397905 2023-11-12T09:50:34.505628+05:30 ARC3 (PID:17392): Archived Log entry 21 added for T-1.S-202 ID 0xa991dc35 LAD:1 2023-11-12T09:50:37.457260+05:30 rfs (PID:5680): Selected LNO:4 for T-1.S-203 dbid 2829010735 branch 1150397905 2023-11-12T09:50:37.511638+05:30 ARC0 (PID:16740): Archived Log entry 22 added for T-1.S-203 ID 0xa991dc35 LAD:1 2023-11-12T09:50:40.419426+05:30 rfs (PID:21136): Selected LNO:4 for T-1.S-204 dbid 2829010735 branch 1150397905 2023-11-12T09:50:40.465691+05:30 ARC1 (PID:17272): Archived Log entry 23 added for T-1.S-204 ID 0xa991dc35 LAD:1 2023-11-12T09:50:44.499333+05:30 rfs (PID:5680): Selected LNO:4 for T-1.S-205 dbid 2829010735 branch 1150397905 2023-11-12T09:50:44.531028+05:30 ARC2 (PID:21896): Archived Log entry 24 added for T-1.S-205 ID 0xa991dc35 LAD:1 2023-11-12T09:50:46.404722+05:30 rfs (PID:21136): Selected LNO:4 for T-1.S-206 dbid 2829010735 branch 1150397905 2023-11-12T09:50:46.467478+05:30 ARC3 (PID:17392): Archived Log entry 25 added for T-1.S-206 ID 0xa991dc35 LAD:1 |
In this scenario to avoid this issue, you have to execute always direct activate command without FINISH command, but as per the test case2, we will execute "WITH FINISH" command and will see the result. On NR: SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH; Database altered. As soon as you execute the FINISH command, below messages will be appeared in NR database alert log file. Parallel Media Recover will be started and will apply all the available archived log files. Here, it applied logs from sequence 197 to 206 and waiting for 207 log. The current SRL(Standby Redo Log) is archived and applied through SRL "D:\RUPESH\SETUPS\NR\SRLS\MINIMUM02.LOG". At the same time, you won't see any messages in DR alert log file. NR Database Alert Log File: 2023-11-12T09:57:04.033084+05:30 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH 2023-11-12T09:57:04.033084+05:30 Terminal Recovery requested in process 14620 .... (PID:14620): The Time Management Interface (TMI) is being enabled for role transition .... (PID:14620): information. This will result in messages beingoutput to the alert log .... (PID:14620): file with the prefix 'TMI: '. This is being enabled to make the timing of .... (PID:14620): the various stages of the role transition available for diagnostic purposes. .... (PID:14620): This output will end when the role transition is complete. TMI: adbdrv termRecovery BEGIN 2023-11-12 09:57:04.064186 TMI: adbdrv termRecovery END 2023-11-12 09:57:04.064186 Started logmerger process 2023-11-12T09:57:04.127601+05:30 PR00 (PID:23316): Managed Standby Recovery not using Real Time Apply 2023-11-12T09:57:04.285678+05:30 Parallel Media Recovery started with 8 slaves 2023-11-12T09:57:04.316928+05:30 Stopping change tracking 2023-11-12T09:57:04.395835+05:30 PR00 (PID:23316): Media Recovery Log D:\RUPESH\SETUPS\NR\ARCH\1_197_1150397905.ARC 2023-11-12T09:57:04.585249+05:30 PR00 (PID:23316): Media Recovery Log D:\RUPESH\SETUPS\NR\ARCH\1_198_1150397905.ARC 2023-11-12T09:57:04.764842+05:30 PR00 (PID:23316): Media Recovery Log D:\RUPESH\SETUPS\NR\ARCH\1_199_1150397905.ARC 2023-11-12T09:57:04.931734+05:30 PR00 (PID:23316): Media Recovery Log D:\RUPESH\SETUPS\NR\ARCH\1_200_1150397905.ARC 2023-11-12T09:57:05.126139+05:30 PR00 (PID:23316): Media Recovery Log D:\RUPESH\SETUPS\NR\ARCH\1_201_1150397905.ARC 2023-11-12T09:57:05.293642+05:30 PR00 (PID:23316): Media Recovery Log D:\RUPESH\SETUPS\NR\ARCH\1_202_1150397905.ARC 2023-11-12T09:57:05.466671+05:30 PR00 (PID:23316): Media Recovery Log D:\RUPESH\SETUPS\NR\ARCH\1_203_1150397905.ARC 2023-11-12T09:57:05.672047+05:30 PR00 (PID:23316): Media Recovery Log D:\RUPESH\SETUPS\NR\ARCH\1_204_1150397905.ARC 2023-11-12T09:57:05.868007+05:30 PR00 (PID:23316): Media Recovery Log D:\RUPESH\SETUPS\NR\ARCH\1_205_1150397905.ARC 2023-11-12T09:57:06.050515+05:30 PR00 (PID:23316): Media Recovery Log D:\RUPESH\SETUPS\NR\ARCH\1_206_1150397905.ARC 2023-11-12T09:57:06.224089+05:30 PR00 (PID:23316): Media Recovery Waiting for T-1.S-207 (in transit) 2023-11-12T09:57:07.845902+05:30 PR00 (PID:23316): Killing 5 processes (PIDS:23488,7084,18808,14004,18312) (all RFS, wait for I/O) in order to disallow current and future RFS connections. Requested by OS process 23316 2023-11-12T09:57:09.971622+05:30 PR00 (PID:23316): Begin: SRL archival PR00 (PID:23316): End: SRL archival PR00 (PID:23316): Terminal Recovery timestamp is '11/12/2023 09:57:10' PR00 (PID:23316): Terminal Recovery: applying standby redo logs. PR00 (PID:23316): Terminal Recovery: thread 1 seq# 207 redo required 2023-11-12T09:57:10.082306+05:30 PR00 (PID:23316): Terminal Recovery: 2023-11-12T09:57:10.113563+05:30 Recovery of Online Redo Log: Thread 1 Group 5 Seq 207 Reading mem 0 Mem# 0: D:\RUPESH\SETUPS\NR\SRLS\MINIMUM02.LOG 2023-11-12T09:57:10.239670+05:30 Incomplete Recovery applied until change 13024592 time 11/12/2023 09:57:06 Terminal Recovery: successful completion PR00 (PID:23316): Forcing ARSCN to IRSCN for TR SCN:0x0000000000c6bd50 PR00 (PID:23316): Attempt to set limbo arscn SCN:0x0000000000c6bd50 irscn SCN:0x0000000000c6bd50 PR00 (PID:23316): Resetting standby activation ID 2844908597 (0xa991dc35) Stopping change tracking 2023-11-12T09:57:10.556224+05:30 Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH When you execute activate command then below messages will be appeared in the NR database alert log file. At this stage also, you won't see any messages in DR alert log file. SQL> ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE; Database altered. NR Database Alert Log File: 2023-11-12T09:58:16.262429+05:30 ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE 2023-11-12T09:58:16.262429+05:30 NET (PID:14620): Begin: SRL archival NET (PID:14620): End: SRL archival Standby terminal recovery start SCN: 13023713 RESETLOGS after incomplete recovery UNTIL CHANGE 13024592 time 11/12/2023 09:57:06 Online log D:\RUPESH\SETUPS\NR\DB\NR\ONLINELOG\O1_MF_1_LO0LO5JO_.LOG: Thread 1 Group 1 was previously cleared Online log D:\RUPESH\SETUPS\NR\DB\NR\ONLINELOG\O1_MF_2_LO0LO6QG_.LOG: Thread 1 Group 2 was previously cleared Online log D:\RUPESH\SETUPS\NR\DB\NR\ONLINELOG\O1_MF_3_LO0LO6YB_.LOG: Thread 1 Group 3 was previously cleared Standby became primary SCN: 13023712 2023-11-12T09:58:16.528721+05:30 Setting recovery target incarnation to 14 2023-11-12T09:58:16.528721+05:30 NET (PID:14620): RT: Role transition work is not done NET (PID:14620): The Time Management Interface (TMI) is being enabled for role transition NET (PID:14620): information. This will result in messages beingoutput to the alert log NET (PID:14620): file with the prefix 'TMI: '. This is being enabled to make the timing of NET (PID:14620): the various stages of the role transition available for diagnostic purposes. NET (PID:14620): This output will end when the role transition is complete. NET (PID:14620): Redo network throttle feature is disabled at mount time 2023-11-12T09:58:16.607387+05:30 NET (PID:14620): Database role cleared from PHYSICAL STANDBY [kcvs.c:1114] ACTIVATE STANDBY: Complete - Database mounted as primary Completed: ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE 2023-11-12T09:58:33.053919+05:30 ARC0 (PID:4316): Becoming the 'no SRL' ARCH SQL> ALTER DATABASE OPEN; Database altered. SQL> alter system switch logfile; System altered. ...... SQL> alter system switch logfile; System altered. NR Alert Log File: 2023-11-12T09:59:13.420709+05:30 alter database open 2023-11-12T09:59:13.420709+05:30 TMI: adbdrv open database BEGIN 2023-11-12 09:59:13.420709 Smart fusion block transfer is disabled: instance mounted in exclusive mode. Endian type of dictionary set to little 2023-11-12T09:59:13.641361+05:30 Assigning activation ID 2847175275 (0xa9b4726b) 2023-11-12T09:59:13.657398+05:30 Thread 1 advanced to log sequence 2 (thread open) Redo log for group 2, sequence 2 is not located on DAX storage Thread 1 opened at log sequence 2 2023-11-12T09:59:13.705392+05:30 Current log# 2 seq# 2 mem# 0: D:\RUPESH\SETUPS\NR\DB\NR\ONLINELOG\O1_MF_2_LO0LO6QG_.LOG Successful open of redo thread 1 2023-11-12T09:59:13.705392+05:30 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set Stopping change tracking Undo initialization recovery: Parallel FPTR failed: start:1095285875 end:1095285875 diff:0 ms (0.0 seconds) Undo initialization recovery: err:0 start: 1095285875 end: 1095285906 diff: 31 ms (0.0 seconds) [14620] Successfully onlined Undo Tablespace 2. Undo initialization online undo segments: err:0 start: 1095285906 end: 1095285953 diff: 47 ms (0.0 seconds) Undo initialization finished serial:0 start:1095285875 end:1095285953 diff:78 ms (0.1 seconds) Dictionary check beginning 2023-11-12T09:59:13.990103+05:30 Errors in file D:\RUPESH\APP\RGHUBADE\diag\rdbms\nr\nr\trace\nr_dbw0_3288.trc: ORA-01186: file 201 failed verification tests ORA-01157: cannot identify/lock data file 201 - see DBWR trace file ORA-01110: data file 201: 'D:\RUPESH\SETUPS\NR\DB\NR\DATAFILE\O1_MF_TEMP_%U_.TMP' 2023-11-12T09:59:13.990103+05:30 File 201 not verified due to error ORA-01157 2023-11-12T09:59:14.005798+05:30 Dictionary check complete 2023-11-12T09:59:14.020304+05:30 Starting background process SMCO Re-creating tempfile D:\RUPESH\SETUPS\NR\DB\NR\DATAFILE\O1_MF_TEMP_%U_.TMP as D:\RUPESH\SETUPS\NR\DB\NR\DATAFILE\O1_MF_TEMP_LO0O4T05_.TMP 2023-11-12T09:59:14.036010+05:30 SMCO started with pid=33, OS id=9684 Database Characterset is AL32UTF8 No Resource Manager plan active 2023-11-12T09:59:14.492330+05:30 NET (PID:14620): 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 12136 cid 0 replication_dependency_tracking turned off (no async multimaster replication found) 2023-11-12T09:59:14.712290+05:30 ARC2 (PID:11744): LGWR is scheduled to archive to LAD:2 after log switch LOGSTDBY: Validating controlfile with logical metadata LOGSTDBY: Validation complete Starting background process AQPC 2023-11-12T09:59:14.853325+05:30 ARC2 (PID:11744): Archived Log entry 49 added for T-1.S-1 ID 0xa9b4726b LAD:1 2023-11-12T09:59:14.868935+05:30 AQPC started with pid=42, OS id=22872 2023-11-12T09:59:15.027531+05:30 TMI: adbdrv open database END 2023-11-12 09:59:15.027531 Starting background process CJQ0 2023-11-12T09:59:15.075325+05:30 CJQ0 started with pid=44, OS id=4060 2023-11-12T09:59:16.069000+05:30 QPI: opatch file present, opatch.bat QPI: qopiprep.bat file present 2023-11-12T09:59:17.096044+05:30 Completed: alter database open 2023-11-12T09:59:18.275621+05:30 Setting Resource Manager plan SCHEDULER[0x4D1F]:DEFAULT_MAINTENANCE_PLAN via scheduler window Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter 2023-11-12T09:59:18.780982+05:30 TT05 (PID:20516): Enabling archival of deferred physical standby SRLs 2023-11-12T09:59:18.843882+05:30 TT05 (PID:20516): Archived Log entry 51 added for T-1.S-207 ID 0xa991dc35 LAD:1 2023-11-12T09:59:33.237448+05:30 rfs (PID:8572): krsr_rfs_atc: Identified database type as 'PRIMARY': Client is Foreground (PID:8024) rfs (PID:8572): Database mount ID mismatch [0xa9b119d0:0xa9b4726b] (2846955984:2847175275) rfs (PID:8572): Not using real application clusters rfs (PID:8572): Possible network disconnect with primary database rfs (PID:8572): while processing B-1150397905.T-1.S-0 BNUM:0 BCNT:0 2023-11-12T09:59:33.252452+05:30 rfs (PID:18900): krsr_rfs_atc: Identified database type as 'PRIMARY': Client is ASYNC (PID:22568) 2023-11-12T09:59:33.268163+05:30 rfs (PID:18900): Database mount ID mismatch [0xa9b119d0:0xa9b4726b] (2846955984:2847175275) rfs (PID:18900): Not using real application clusters rfs (PID:18900): Possible network disconnect with primary database rfs (PID:18900): while processing B-1150397905.T-1.S-207 BNUM:0 BCNT:0 rfs (PID:18900): Current process action IDLE, elapsed idle time 0 rfs (PID:18900): RFS client ASYNC ORL SINGLE (PID:22568) 2023-11-12T09:59:34.276414+05:30 rfs (PID:8572): Current process action IDLE, elapsed idle time 0 2023-11-12T09:59:34.276414+05:30 rfs (PID:8572): RFS client GAP MANAGER (PID:8024) The below message in target DR database "Setting recovery target incarnation to 14" is very important one and which should reached and written to DR database first before starting MRP process. Never start the MRP process before enabling the target destination. In our case, destination is already enabled, so don't worry. DR Alert Log File: 2023-11-12T09:59:13.784252+05:30 rfs (PID:18580): krsr_rfs_atc: Identified database type as 'PHYSICAL STANDBY': Client is ASYNC (PID:22752) rfs (PID:18580): New archival redo branch: 1152698296 current: 1150397905 rfs (PID:18580): Primary database is in MAXIMUM PERFORMANCE mode 2023-11-12T09:59:13.816062+05:30 rfs (PID:18580): Selected LNO:5 for T-1.S-2 dbid 2829010735 branch 1152698296 2023-11-12T09:59:14.759296+05:30 rfs (PID:12304): krsr_rfs_atc: Identified database type as 'PHYSICAL STANDBY': Client is ARCH (PID:11744) rfs (PID:12304): New archival redo branch: 1152698296 current: 1150397905 2023-11-12T09:59:14.791435+05:30 rfs (PID:12304): Selected LNO:6 for T-1.S-1 dbid 2829010735 branch 1152698296 rfs (PID:12304): A new recovery destination branch has been registered rfs (PID:12304): New Archival REDO Branch(resetlogs_id): 1152698296 Prior: 1150397905 rfs (PID:12304): Archival Activation ID: 0xa9b4726b Current: 0xa991dc35 rfs (PID:12304): Effect of primary database OPEN RESETLOGS 2023-11-12T09:59:14.837779+05:30 Setting recovery target incarnation to 14 2023-11-12T09:59:14.891563+05:30 ARC0 (PID:16740): Archived Log entry 26 added for T-1.S-1 ID 0xa9b4726b LAD:1 2023-11-12T09:59:15.898224+05:30 rfs (PID:18500): krsr_rfs_atc: Identified database type as 'PHYSICAL STANDBY': Client is Foreground (PID:19864) NR Database: SQL> select INCARNATION#,RESETLOGS_CHANGE#,RESETLOGS_ID,PRIOR_RESETLOGS_CHANGE#,status from v$database_incarnation; INCARNATION# RESETLOGS_CHANGE# RESETLOGS_ID PRIOR_RESETLOGS_CHANGE# STATUS ------------ ----------------- ------------ ----------------------- ------- 1 1 1009595390 0 PARENT 2 1928875 1134494132 1 PARENT 3 10902231 1150158056 1928875 PARENT 4 11012770 1150161468 10902231 PARENT 5 11131041 1150168296 11012770 PARENT 6 11280291 1150180136 11131041 PARENT 7 11395555 1150185838 11280291 PARENT 8 11536905 1150303013 11395555 PARENT 9 11678865 1150322569 11536905 PARENT 10 11787022 1150325436 11678865 PARENT 11 11929766 1150388645 11787022 PARENT 12 12046881 1150395282 11929766 PARENT 13 12155220 1150397905 12046881 PARENT 14 13024593 1152698296 12155220 CURRENT SQL> select name,db_unique_name,open_mode,database_role from v$database; NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE ------ ---------------- ------------ ------------- PR NR READ WRITE PRIMARY SQL> select max(sequence#) from v$archived_log where RESETLOGS_CHANGE#=13024593; MAX(SEQUENCE#) -------------- 1 Check whether the table exists that you created in Primary database. You can see the table exists since you executed FINISH command before executing activate standby command which means that failed EOD data is written to the database which we don't expect. But you have seen in earlier post that the table was not created since you executed activate standby command without FINISH command. It means that if you don't want the data from the available archived log files, then you must execute activate standby with "WITHOUT FINISH" option, but if you want to apply all the available archived log files, the you must execute activate standby command with "WITH FINISH" option. Otherwise there can be data mismatch issue and data integrity issue. SQL> select * from with_finish; ID ---------- 1 2 3 4 5 SQL> alter system switch logfile; System altered. SQL> alter system switch logfile; System altered. SQL> alter system switch logfile; System altered. SQL> alter system switch logfile; System altered. SQL> alter system switch logfile; System altered. SQL> alter system switch logfile; System altered. SQL> alter system switch logfile; System altered. SQL> alter system switch logfile; System altered. SQL> alter system switch logfile; System altered. SQL> alter system switch logfile; System altered. Perform few log switches on new Primary database(NR). NR Database Alert: 2023-11-12T10:02:08.640220+05:30 Thread 1 advanced to log sequence 3 (LGWR switch), current SCN: 13025212 Current log# 3 seq# 3 mem# 0: D:\RUPESH\SETUPS\NR\DB\NR\ONLINELOG\O1_MF_3_LO0LO6YB_.LOG 2023-11-12T10:02:08.679612+05:30 ARC0 (PID:4316): Archived Log entry 53 added for T-1.S-2 ID 0xa9b4726b LAD:1 2023-11-12T10:02:08.695240+05:30 Thread 1 advanced to log sequence 4 (LGWR switch), current SCN: 13025239 Current log# 1 seq# 4 mem# 0: D:\RUPESH\SETUPS\NR\DB\NR\ONLINELOG\O1_MF_1_LO0LO5JO_.LOG Thread 1 cannot allocate new log, sequence 5 Checkpoint not complete Current log# 1 seq# 4 mem# 0: D:\RUPESH\SETUPS\NR\DB\NR\ONLINELOG\O1_MF_1_LO0LO5JO_.LOG 2023-11-12T10:02:08.744291+05:30 ARC1 (PID:3728): Archived Log entry 54 added for T-1.S-3 ID 0xa9b4726b LAD:1 2023-11-12T10:02:10.713470+05:30 Thread 1 advanced to log sequence 5 (LGWR switch), current SCN: 13025247 Current log# 2 seq# 5 mem# 0: D:\RUPESH\SETUPS\NR\DB\NR\ONLINELOG\O1_MF_2_LO0LO6QG_.LOG 2023-11-12T10:02:10.744170+05:30 Thread 1 advanced to log sequence 6 (LGWR switch), current SCN: 13025253 2023-11-12T10:02:10.744785+05:30 Current log# 3 seq# 6 mem# 0: D:\RUPESH\SETUPS\NR\DB\NR\ONLINELOG\O1_MF_3_LO0LO6YB_.LOG 2023-11-12T10:02:11.744899+05:30 ARC3 (PID:16560): Archived Log entry 57 added for T-1.S-4 ID 0xa9b4726b LAD:1 2023-11-12T10:02:11.776151+05:30 ARC0 (PID:4316): Archived Log entry 59 added for T-1.S-5 ID 0xa9b4726b LAD:1 2023-11-12T10:02:11.855103+05:30 Thread 1 advanced to log sequence 7 (LGWR switch), current SCN: 13026405 Current log# 1 seq# 7 mem# 0: D:\RUPESH\SETUPS\NR\DB\NR\ONLINELOG\O1_MF_1_LO0LO5JO_.LOG 2023-11-12T10:02:11.885761+05:30 ARC3 (PID:16560): Archived Log entry 60 added for T-1.S-6 ID 0xa9b4726b LAD:1 2023-11-12T10:02:12.784486+05:30 Thread 1 cannot allocate new log, sequence 8 Checkpoint not complete Current log# 1 seq# 7 mem# 0: D:\RUPESH\SETUPS\NR\DB\NR\ONLINELOG\O1_MF_1_LO0LO5JO_.LOG 2023-11-12T10:02:13.655655+05:30 Thread 1 advanced to log sequence 8 (LGWR switch), current SCN: 13026413 Current log# 2 seq# 8 mem# 0: D:\RUPESH\SETUPS\NR\DB\NR\ONLINELOG\O1_MF_2_LO0LO6QG_.LOG Thread 1 cannot allocate new log, sequence 9 Checkpoint not complete 2023-11-12T10:02:13.686912+05:30 Current log# 2 seq# 8 mem# 0: D:\RUPESH\SETUPS\NR\DB\NR\ONLINELOG\O1_MF_2_LO0LO6QG_.LOG 2023-11-12T10:02:14.693811+05:30 ARC0 (PID:4316): Archived Log entry 63 added for T-1.S-7 ID 0xa9b4726b LAD:1 2023-11-12T10:02:16.706402+05:30 Thread 1 advanced to log sequence 9 (LGWR switch), current SCN: 13026423 Current log# 3 seq# 9 mem# 0: D:\RUPESH\SETUPS\NR\DB\NR\ONLINELOG\O1_MF_3_LO0LO6YB_.LOG Thread 1 cannot allocate new log, sequence 10 2023-11-12T10:02:16.737608+05:30 ARC1 (PID:3728): Archived Log entry 65 added for T-1.S-8 ID 0xa9b4726b LAD:1 Checkpoint not complete 2023-11-12T10:02:16.737608+05:30 Current log# 3 seq# 9 mem# 0: D:\RUPESH\SETUPS\NR\DB\NR\ONLINELOG\O1_MF_3_LO0LO6YB_.LOG 2023-11-12T10:02:19.750439+05:30 Thread 1 advanced to log sequence 10 (LGWR switch), current SCN: 13026433 Current log# 1 seq# 10 mem# 0: D:\RUPESH\SETUPS\NR\DB\NR\ONLINELOG\O1_MF_1_LO0LO5JO_.LOG 2023-11-12T10:02:19.767143+05:30 ARC2 (PID:11744): Archived Log entry 67 added for T-1.S-9 ID 0xa9b4726b LAD:1 2023-11-12T10:02:19.782164+05:30 Thread 1 cannot allocate new log, sequence 11 Checkpoint not complete Current log# 1 seq# 10 mem# 0: D:\RUPESH\SETUPS\NR\DB\NR\ONLINELOG\O1_MF_1_LO0LO5JO_.LOG 2023-11-12T10:02:22.659761+05:30 Thread 1 advanced to log sequence 11 (LGWR switch), current SCN: 13026443 Current log# 2 seq# 11 mem# 0: D:\RUPESH\SETUPS\NR\DB\NR\ONLINELOG\O1_MF_2_LO0LO6QG_.LOG Thread 1 cannot allocate new log, sequence 12 Checkpoint not complete 2023-11-12T10:02:22.691855+05:30 Current log# 2 seq# 11 mem# 0: D:\RUPESH\SETUPS\NR\DB\NR\ONLINELOG\O1_MF_2_LO0LO6QG_.LOG 2023-11-12T10:02:23.683763+05:30 ARC3 (PID:16560): Archived Log entry 69 added for T-1.S-10 ID 0xa9b4726b LAD:1 2023-11-12T10:02:25.714690+05:30 Thread 1 advanced to log sequence 12 (LGWR switch), current SCN: 13026453 Current log# 3 seq# 12 mem# 0: D:\RUPESH\SETUPS\NR\DB\NR\ONLINELOG\O1_MF_3_LO0LO6YB_.LOG 2023-11-12T10:02:25.731084+05:30 ARC0 (PID:4316): Archived Log entry 71 added for T-1.S-11 ID 0xa9b4726b LAD:1 DR Alert Log File: 2023-11-12T10:00:16.050895+05:30 rfs (PID:23116): krsr_rfs_atc: Identified database type as 'PHYSICAL STANDBY': Client is FAL (PID:16560) 2023-11-12T10:00:16.066523+05:30 rfs (PID:23116): Opened log for T-1.S-207 dbid 2829010735 branch 1150397905 2023-11-12T10:00:16.082146+05:30 rfs (PID:23116): Archived Log entry 27 added for B-1150397905.T-1.S-207 ID 0xa991dc35 LAD:2 2023-11-12T10:02:08.805795+05:30 rfs (PID:12304): Opened log for T-1.S-3 dbid 2829010735 branch 1152698296 2023-11-12T10:02:08.805795+05:30 rfs (PID:12304): Archived Log entry 28 added for B-1152698296.T-1.S-3 ID 0xa9b4726b LAD:2 2023-11-12T10:02:08.821416+05:30 ARC1 (PID:17272): Archived Log entry 29 added for T-1.S-2 ID 0xa9b4726b LAD:1 2023-11-12T10:02:08.885418+05:30 rfs (PID:18196): krsr_rfs_atc: Identified database type as 'PHYSICAL STANDBY': Client is ASYNC (PID:14888) rfs (PID:18196): Primary database is in MAXIMUM PERFORMANCE mode 2023-11-12T10:02:08.932102+05:30 rfs (PID:18196): Selected LNO:5 for T-1.S-4 dbid 2829010735 branch 1152698296 2023-11-12T10:02:10.887071+05:30 rfs (PID:18196): Selected LNO:6 for T-1.S-6 dbid 2829010735 branch 1152698296 2023-11-12T10:02:11.902870+05:30 ARC2 (PID:21896): Archived Log entry 30 added for T-1.S-4 ID 0xa9b4726b LAD:1 2023-11-12T10:02:11.933021+05:30 rfs (PID:12304): Opened log for T-1.S-5 dbid 2829010735 branch 1152698296 2023-11-12T10:02:11.945530+05:30 rfs (PID:12304): Archived Log entry 31 added for B-1152698296.T-1.S-5 ID 0xa9b4726b LAD:2 2023-11-12T10:02:12.013807+05:30 rfs (PID:18196): Selected LNO:5 for T-1.S-7 dbid 2829010735 branch 1152698296 2023-11-12T10:02:12.013807+05:30 ARC3 (PID:17392): Archived Log entry 32 added for T-1.S-6 ID 0xa9b4726b LAD:1 2023-11-12T10:02:13.828509+05:30 rfs (PID:18196): Selected LNO:6 for T-1.S-8 dbid 2829010735 branch 1152698296 2023-11-12T10:02:13.828509+05:30 ARC0 (PID:16740): Archived Log entry 33 added for T-1.S-7 ID 0xa9b4726b LAD:1 2023-11-12T10:02:16.863161+05:30 rfs (PID:18196): Selected LNO:5 for T-1.S-9 dbid 2829010735 branch 1152698296 2023-11-12T10:02:17.886645+05:30 ARC1 (PID:17272): Archived Log entry 34 added for T-1.S-8 ID 0xa9b4726b LAD:1 2023-11-12T10:02:19.907801+05:30 rfs (PID:18196): Selected LNO:6 for T-1.S-10 dbid 2829010735 branch 1152698296 2023-11-12T10:02:20.929682+05:30 ARC2 (PID:21896): Archived Log entry 35 added for T-1.S-9 ID 0xa9b4726b LAD:1 2023-11-12T10:02:22.817507+05:30 rfs (PID:18196): Selected LNO:5 for T-1.S-11 dbid 2829010735 branch 1152698296 2023-11-12T10:02:22.817507+05:30 ARC3 (PID:17392): Archived Log entry 36 added for T-1.S-10 ID 0xa9b4726b LAD:1 2023-11-12T10:02:25.872294+05:30 rfs (PID:18196): Selected LNO:6 for T-1.S-12 dbid 2829010735 branch 1152698296 2023-11-12T10:02:25.872294+05:30 ARC0 (PID:16740): Archived Log entry 37 added for T-1.S-11 ID 0xa9b4726b LAD:1 |
Step 5: Now start the MRP process in DR standby database. Once you start the MRP process on DR database, then below messages will be appeared in alert log. The change 13024592 is the last resetlog change that was recorded in NR database when we executed activate command. DR Alert Log File: 2023-11-12T10:04:23.383777+05:30 ALTER DATABASE RECOVER managed standby database disconnect from session Starting background process MRP0 2023-11-12T10:04:23.430735+05:30 MRP0 started with pid=33, OS id=11472 2023-11-12T10:04:28.473111+05:30 Started logmerger process 2023-11-12T10:04:28.504437+05:30 PR00 (PID:22704): Managed Standby Recovery starting Real Time Apply 2023-11-12T10:04:28.693156+05:30 Parallel Media Recovery started with 8 slaves 2023-11-12T10:04:28.709691+05:30 Media Recovery start incarnation depth : 1, target inc# : 14, irscn : 13024592 Stopping change tracking 2023-11-12T10:04:28.929473+05:30 PR00 (PID:22704): Media Recovery Log D:\RUPESH\SETUPS\STANDBY\ARCH\1_197_1150397905.ARC 2023-11-12T10:04:29.103971+05:30 PR00 (PID:22704): Media Recovery Log D:\RUPESH\SETUPS\STANDBY\ARCH\1_198_1150397905.ARC 2023-11-12T10:04:29.292965+05:30 PR00 (PID:22704): Media Recovery Log D:\RUPESH\SETUPS\STANDBY\ARCH\1_199_1150397905.ARC 2023-11-12T10:04:29.466503+05:30 PR00 (PID:22704): Media Recovery Log D:\RUPESH\SETUPS\STANDBY\ARCH\1_200_1150397905.ARC 2023-11-12T10:04:29.482727+05:30 Completed: ALTER DATABASE RECOVER managed standby database disconnect from session 2023-11-12T10:04:29.640689+05:30 PR00 (PID:22704): Media Recovery Log D:\RUPESH\SETUPS\STANDBY\ARCH\1_201_1150397905.ARC 2023-11-12T10:04:29.814652+05:30 PR00 (PID:22704): Media Recovery Log D:\RUPESH\SETUPS\STANDBY\ARCH\1_202_1150397905.ARC 2023-11-12T10:04:30.018999+05:30 PR00 (PID:22704): Media Recovery Log D:\RUPESH\SETUPS\STANDBY\ARCH\1_203_1150397905.ARC 2023-11-12T10:04:30.193206+05:30 PR00 (PID:22704): Media Recovery Log D:\RUPESH\SETUPS\STANDBY\ARCH\1_204_1150397905.ARC 2023-11-12T10:04:30.381333+05:30 PR00 (PID:22704): Media Recovery Log D:\RUPESH\SETUPS\STANDBY\ARCH\1_205_1150397905.ARC 2023-11-12T10:04:30.556256+05:30 PR00 (PID:22704): Media Recovery Log D:\RUPESH\SETUPS\STANDBY\ARCH\1_206_1150397905.ARC 2023-11-12T10:04:30.730493+05:30 PR00 (PID:22704): Media Recovery Log D:\RUPESH\SETUPS\STANDBY\ARCH\1_207_1150397905.ARC ----> last current log 2023-11-12T10:04:30.893900+05:30 PR00 (PID:22704): Resetting standby activation ID 2844908597 (0xa991dc35) 2023-11-12T10:04:30.903982+05:30 Media Recovery End-Of-Redo indicator encountered 2023-11-12T10:04:30.919694+05:30 Media Recovery Continuing 2023-11-12T10:04:30.935933+05:30 PR00 (PID:22704): Media Recovery Log D:\RUPESH\SETUPS\STANDBY\ARCH\1_1_1152698296.ARC 2023-11-12T10:04:31.077138+05:30 PR00 (PID:22704): Media Recovery Log D:\RUPESH\SETUPS\STANDBY\ARCH\1_2_1152698296.ARC 2023-11-12T10:04:31.426567+05:30 PR00 (PID:22704): Media Recovery Log D:\RUPESH\SETUPS\STANDBY\ARCH\1_3_1152698296.ARC 2023-11-12T10:04:31.600583+05:30 PR00 (PID:22704): Media Recovery Log D:\RUPESH\SETUPS\STANDBY\ARCH\1_4_1152698296.ARC 2023-11-12T10:04:31.775211+05:30 PR00 (PID:22704): Media Recovery Log D:\RUPESH\SETUPS\STANDBY\ARCH\1_5_1152698296.ARC 2023-11-12T10:04:31.948322+05:30 PR00 (PID:22704): Media Recovery Log D:\RUPESH\SETUPS\STANDBY\ARCH\1_6_1152698296.ARC 2023-11-12T10:04:32.123068+05:30 PR00 (PID:22704): Media Recovery Log D:\RUPESH\SETUPS\STANDBY\ARCH\1_7_1152698296.ARC 2023-11-12T10:04:32.281920+05:30 PR00 (PID:22704): Media Recovery Log D:\RUPESH\SETUPS\STANDBY\ARCH\1_8_1152698296.ARC 2023-11-12T10:04:32.439933+05:30 PR00 (PID:22704): Media Recovery Log D:\RUPESH\SETUPS\STANDBY\ARCH\1_9_1152698296.ARC 2023-11-12T10:04:32.597759+05:30 PR00 (PID:22704): Media Recovery Log D:\RUPESH\SETUPS\STANDBY\ARCH\1_10_1152698296.ARC 2023-11-12T10:04:32.754968+05:30 PR00 (PID:22704): Media Recovery Log D:\RUPESH\SETUPS\STANDBY\ARCH\1_11_1152698296.ARC 2023-11-12T10:04:32.929029+05:30 PR00 (PID:22704): Media Recovery Waiting for T-1.S-12 (in transit) 2023-11-12T10:04:32.960286+05:30 Recovery of Online Redo Log: Thread 1 Group 6 Seq 12 Reading mem 0 Mem# 0: D:\RUPESH\SETUPS\STANDBY\SRLS\STANDBY03.LOG You can see that MRP process have applied all the available archived logs starting from log sequence number 197 to 206 and the sequence number 207 which was the last current log sequence at NR site before activating. This is because we have executed "FINISH" command before ACTIVATE command. After applying 207 sequence, it started the sequence from 1. |
You can check the dummy table which was created in PR database and part of failed EOD operations. The created table exist in the target database since we executed FINISH command. On DR: SQL> select INCARNATION#,RESETLOGS_CHANGE#,RESETLOGS_ID,PRIOR_RESETLOGS_CHANGE#,status from v$database_incarnation; INCARNATION# RESETLOGS_CHANGE# RESETLOGS_ID PRIOR_RESETLOGS_CHANGE# STATUS ------------ ----------------- ------------ ----------------------- ------- 1 1 1009595390 0 PARENT 2 1928875 1134494132 1 PARENT 3 10902231 1150158056 1928875 PARENT 4 11012770 1150161468 10902231 PARENT 5 11131041 1150168296 11012770 PARENT 6 11280291 1150180136 11131041 PARENT 7 11395555 1150185838 11280291 PARENT 8 11536905 1150303013 11395555 PARENT 9 11678865 1150322569 11536905 PARENT 10 11787022 1150325436 11678865 PARENT 11 11929766 1150388645 11787022 PARENT 12 12046881 1150395282 11929766 PARENT 13 12155220 1150397905 12046881 PARENT 14 13024593 1152698296 12155220 CURRENT SQL> select max(sequence#) from v$archived_log where RESETLOGS_CHANGE#=13024593 and applied='YES'; MAX(SEQUENCE#) -------------- 11 SQL> recover managed standby database cancel; Media recovery complete. SQL> alter database open read only; Database altered. SQL> select name,db_unique_name,open_mode,database_role from v$database; NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE ----- --------------- ----------- ---------------- PR DR READ ONLY PHYSICAL STANDBY SQL> select * from with_finish; ID ---------- 1 2 3 4 5 |
So the final conclusion is that MRP will apply all the available archived logs if you execute FINISH command before executing ACTIVATE STANDBY i.e. the log sequence number 197 to 206 and the sequence log which was current before the ACTIVATE command. Then it will start applying logs from log sequence 1.
Thanks for reading this post ! Please comment if you like this post ! Click on FOLLOW to get next blog updates !
ReplyDeleteOracle Fusion HCM Training
course provides students with an overview of the various components of human resource management. It also provides an overview of each Oracle Fusion HCM Online module. The Fusion HCM course will cover topics such as creating users and permissions, managing employee data, and running reports.