|
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 operations 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 between 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 225. On NR, applied log sequence is 225. On DR, applied log sequence is 225. 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 230. ---> Your Primary DB is far ahead from both NR and DR databases. On NR, applied log sequence is 225. On DR, applied log sequence is 225. 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 again from NR site and continue the transactions 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 operations 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 FINISH command start the MRP process that will apply all the available archived logs from sequence 226 to 230 in NR database ? Will these logs from log sequence 226 to 230 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; In our scenario, we must prefer "Test Case 1" since we don't want to apply failed EOD logs. So let's start this scenario i.e. Test Case 1 without FINISH. For Test Case 2, refer the link "What if you execute ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH before activating standby database in Oracle" Test Case 1: 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#) -------------- 225 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 226 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#) -------------- 225 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#) -------------- 225 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 225. On NR, applied log sequence is 225. On DR, applied log sequence is 225. Step 3: Now suppose, you started an application load testing or EOD operations considering DML operations 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. Perform few log switches. GROUP# A.BYTES/1024/1024 MEMBER STATUS SEQUENCE# ------ ----------------- ------------------------------------------ ------- --------- 1 200 D:\RUPESH\SETUPS\PRIMARY\DB\PR\REDO_01.LOG CURRENT 226 2 200 D:\RUPESH\SETUPS\PRIMARY\DB\PR\REDO_02.LOG ACTIVE 224 3 200 D:\RUPESH\SETUPS\PRIMARY\DB\PR\REDO_03.LOG ACTIVE 225 SQL> create table without_finish(id number(10)); Table created. SQL> insert into without_finish values(1); 1 row created. SQL> insert into without_finish values(2); 1 row created. SQL> insert into without_finish values(3); 1 row created. SQL> insert into without_finish values(4); 1 row created. SQL> insert into without_finish values(5); 1 row created. SQL> commit; Commit complete. SQL> select * from without_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; MAX(SEQUENCE#) -------------- 230 GROUP# A.BYTES/1024/1024 MEMBER STATUS SEQUENCE# ------ ----------------- ------------------------------------------ -------- --------- 1 200 D:\RUPESH\SETUPS\PRIMARY\DB\PR\REDO_01.LOG INACTIVE 229 2 200 D:\RUPESH\SETUPS\PRIMARY\DB\PR\REDO_02.LOG INACTIVE 230 3 200 D:\RUPESH\SETUPS\PRIMARY\DB\PR\REDO_03.LOG CURRENT 231 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 is 230. (The current log sequence number at PR is 226 before the table creation and the logs from seq 227 to 230 are of Load testing/EOD). Last applied sequence on NR is 225. Last applied sequence on DR is 225. In this case, the current log sequence is 226 at PR site just before the table creation and hence consider the logs starting from sequence 227 to 230 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. So let's start the Test Case1 i.e. WITHOUT FINISH command. You can see all the logs starting from sequence 226 to 230 are shipped to NR and DR since log shipping destination is enabled, but have not yet been applied since MRP is down. DR database Alert Log: 2023-11-18T16:20:10.017938+05:30 rfs (PID:12328): Selected LNO:4 for T-1.S-226 dbid 2829010735 branch 1150397905 2023-11-18T16:20:10.126532+05:30 ARC1 (PID:11116): Archived Log entry 18 added for T-1.S-226 ID 0xa991dc35 LAD:1 2023-11-18T16:20:11.772636+05:30 rfs (PID:15600): Selected LNO:4 for T-1.S-228 dbid 2829010735 branch 1150397905 2023-11-18T16:20:11.850315+05:30 ARC2 (PID:18840): Archived Log entry 19 added for T-1.S-228 ID 0xa991dc35 LAD:1 2023-11-18T16:20:11.956466+05:30 rfs (PID:12328): Opened log for T-1.S-227 dbid 2829010735 branch 1150397905 2023-11-18T16:20:11.968045+05:30 rfs (PID:12328): Archived Log entry 20 added for B-1150397905.T-1.S-227 ID 0xa991dc35 LAD:2 2023-11-18T16:20:15.798434+05:30 rfs (PID:15600): Selected LNO:4 for T-1.S-229 dbid 2829010735 branch 1150397905 2023-11-18T16:20:15.888815+05:30 ARC3 (PID:1936): Archived Log entry 21 added for T-1.S-229 ID 0xa991dc35 LAD:1 2023-11-18T16:20:17.726719+05:30 rfs (PID:6712): Selected LNO:4 for T-1.S-230 dbid 2829010735 branch 1150397905 2023-11-18T16:20:17.789192+05:30 ARC0 (PID:18776): Archived Log entry 22 added for T-1.S-230 ID 0xa991dc35 LAD:1 In NR standby database, you can see the log sequence 226 which is ACTIVE in NR as the last applied log is 225 and 226 has been added by ARCH process in NR standby database by reading the entry from Standby Redo Log file "D:\RUPESH\SETUPS\NR\SRLS\MINIMUM01.LOG". SQL> select a.GROUP#,a.status,a.sequence# from v$standby_log a; GROUP# STATUS SEQUENCE# ------ ---------- ---------- 4 ACTIVE 226 5 UNASSIGNED 0 6 UNASSIGNED 0 7 UNASSIGNED 0 NR database alert log: PR00 (PID:3376): Media Recovery Waiting for T-1.S-226 (in transit) 2023-11-18T16:13:16.677652+05:30 Recovery of Online Redo Log: Thread 1 Group 4 Seq 226 Reading mem 0 Mem# 0: D:\RUPESH\SETUPS\NR\SRLS\MINIMUM01.LOG 2023-11-18T16:17:01.388207+05:30 ALTER DATABASE RECOVER managed standby database cancel 2023-11-18T16:17:01.396301+05:30 PR00 (PID:3376): MRP0: Background Media Recovery cancelled with status 16037 2023-11-18T16:17:01.405338+05:30 Errors in file D:\RUPESH\APP\RGHUBADE\diag\rdbms\nr\nr\trace\nr_pr00_3376.trc: ORA-16037: user requested cancel of managed recovery operation PR00 (PID:3376): Managed Standby Recovery not using Real Time Apply Recovery interrupted! Recovered data files to a consistent state at change 13193898 Stopping change tracking 2023-11-18T16:17:01.787303+05:30 Errors in file D:\RUPESH\APP\RGHUBADE\diag\rdbms\nr\nr\trace\nr_pr00_3376.trc: ORA-16037: user requested cancel of managed recovery operation 2023-11-18T16:17:02.408595+05:30 Completed: ALTER DATABASE RECOVER managed standby database cancel 2023-11-18T16:20:09.905648+05:30 rfs (PID:14952): Selected LNO:5 for T-1.S-228 dbid 2829010735 branch 1150397905 2023-11-18T16:20:09.928331+05:30 ARC1 (PID:9892): Archived Log entry 33 added for T-1.S-226 ID 0xa991dc35 LAD:1 2023-11-18T16:20:10.920628+05:30 rfs (PID:15012): Opened log for T-1.S-227 dbid 2829010735 branch 1150397905 2023-11-18T16:20:10.933731+05:30 rfs (PID:15012): Archived Log entry 35 added for B-1150397905.T-1.S-227 ID 0xa991dc35 LAD:2 2023-11-18T16:20:11.606723+05:30 rfs (PID:14952): Selected LNO:4 for T-1.S-229 dbid 2829010735 branch 1150397905 2023-11-18T16:20:11.676722+05:30 ARC3 (PID:19336): Archived Log entry 36 added for T-1.S-228 ID 0xa991dc35 LAD:1 2023-11-18T16:20:14.684164+05:30 rfs (PID:14952): Selected LNO:5 for T-1.S-230 dbid 2829010735 branch 1150397905 2023-11-18T16:20:15.694267+05:30 ARC0 (PID:4196): Archived Log entry 39 added for T-1.S-229 ID 0xa991dc35 LAD:1 2023-11-18T16:20:17.633620+05:30 rfs (PID:14952): Selected LNO:4 for T-1.S-231 dbid 2829010735 branch 1150397905 2023-11-18T16:20:17.640882+05:30 ARC2 (PID:13220): Archived Log entry 41 added for T-1.S-230 ID 0xa991dc35 LAD:1 |
|
In this scenario to avoid this issue, you have to execute direct activate command without FINISH command. On NR: SQL> ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE; Database altered. SQL> select name,db_unique_name,open_mode,database_role from v$database; NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE ----- --------------- ---------- ------------- PR NR MOUNTED PRIMARY 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 13193899 1153239998 12155220 CURRENT NR Database Alert Log File: 2023-11-18T16:26:36.228667+05:30 ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE 2023-11-18T16:26:36.244808+05:30 .... (PID:4152): Killing 5 processes (PIDS:9596,724,5860,14952,15012) (all RFS) in order to disallow current and future RFS connections. Requested by OS process 4152 2023-11-18T16:26:38.358289+05:30 .... (PID:4152): Begin: SRL archival .... (PID:4152): End: SRL archival RESETLOGS after incomplete recovery UNTIL CHANGE 13193898 time 11/18/2023 16:17:00 2023-11-18T16:26:38.677402+05:30 NET (PID:4152): Archived Log entry 43 added for T-1.S-231 ID 0xa991dc35 LAD:1 Resetting resetlogs activation ID 2844908597 (0xa991dc35) Online log D:\RUPESH\SETUPS\NR\DB\NR\ONLINELOG\O1_MF_1_LOK4PB6P_.LOG: Thread 1 Group 1 was previously cleared Online log D:\RUPESH\SETUPS\NR\DB\NR\ONLINELOG\O1_MF_2_LOK4PCJ6_.LOG: Thread 1 Group 2 was previously cleared Online log D:\RUPESH\SETUPS\NR\DB\NR\ONLINELOG\O1_MF_3_LOK4PCT9_.LOG: Thread 1 Group 3 was previously cleared Standby became primary SCN: 13193896 2023-11-18T16:26:38.862341+05:30 Setting recovery target incarnation to 14 2023-11-18T16:26:38.886183+05:30 NET (PID:4152): RT: Role transition work is not done NET (PID:4152): The Time Management Interface (TMI) is being enabled for role transition NET (PID:4152): information. This will result in messages beingoutput to the alert log NET (PID:4152): file with the prefix 'TMI: '. This is being enabled to make the timing of NET (PID:4152): the various stages of the role transition available for diagnostic purposes. NET (PID:4152): This output will end when the role transition is complete. NET (PID:4152): Redo network throttle feature is disabled at mount time 2023-11-18T16:26:38.981270+05:30 NET (PID:4152): Database role cleared from PHYSICAL STANDBY [kcvs.c:1114] ACTIVATE STANDBY: Complete - Database mounted as primary Completed: ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE At this point, you won't see any resetlog related messages in the DR database alert log file. SQL> ALTER DATABASE OPEN; Database altered. NR Alert Log File: 2023-11-18T16:31:26.063973+05:30 ALTER DATABASE OPEN 2023-11-18T16:31:26.067974+05:30 TMI: adbdrv open database BEGIN 2023-11-18 16:31:26.064974 Smart fusion block transfer is disabled: instance mounted in exclusive mode. Endian type of dictionary set to little 2023-11-18T16:31:26.470286+05:30 Assigning activation ID 2847787408 (0xa9bdc990) 2023-11-18T16:31:26.520036+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 Current log# 2 seq# 2 mem# 0: D:\RUPESH\SETUPS\NR\DB\NR\ONLINELOG\O1_MF_2_LOK4PCJ6_.LOG 2023-11-18T16:31:26.601203+05:30 Successful open of redo thread 1 2023-11-18T16:31:26.612804+05:30 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set Stopping change tracking 2023-11-18T16:31:26.652406+05:30 Starting background process SMCO 2023-11-18T16:31:26.699420+05:30 .... Database Characterset is AL32UTF8 No Resource Manager plan active 2023-11-18T16:31:27.606872+05:30 ARC1 (PID:9892): LGWR is scheduled to archive to LAD:2 after log switch 2023-11-18T16:31:27.829079+05:30 ARC1 (PID:9892): Archived Log entry 45 added for T-1.S-1 ID 0xa9bdc990 LAD:1 2023-11-18T16:31:28.000808+05:30 NET (PID:4152): 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 19412 cid 0 2023-11-18T16:31:28.230726+05:30 replication_dependency_tracking turned off (no async multimaster replication found) LOGSTDBY: Validating controlfile with logical metadata LOGSTDBY: Validation complete Starting background process AQPC 2023-11-18T16:31:28.697251+05:30 AQPC started with pid=42, OS id=5092 TMI: adbdrv open database END 2023-11-18 16:31:28.992929 Starting background process CJQ0 Completed: ALTER DATABASE OPEN 2023-11-18T16:31:29.513222+05:30 rfs (PID:2788): krsr_rfs_atc: Identified database type as 'PRIMARY': Client is Foreground (PID:4848) rfs (PID:2788): Database mount ID mismatch [0xa9bdb458:0xa9bdc990] (2847781976:2847787408) rfs (PID:2788): Not using real application clusters rfs (PID:2788): Possible network disconnect with primary database rfs (PID:2788): while processing B-1150397905.T-1.S-0 BNUM:0 BCNT:0 rfs (PID:2788): Current process action IDLE, elapsed idle time 0 2023-11-18T16:31:29.561314+05:30 rfs (PID:11000): krsr_rfs_atc: Identified database type as 'PRIMARY': Client is ASYNC (PID:2224) 2023-11-18T16:31:29.579309+05:30 rfs (PID:11000): Database mount ID mismatch [0xa9bdb458:0xa9bdc990] (2847781976:2847787408) rfs (PID:11000): Not using real application clusters rfs (PID:11000): Possible network disconnect with primary database rfs (PID:11000): while processing B-1150397905.T-1.S-231 BNUM:0 BCNT:0 rfs (PID:11000): Current process action IDLE, elapsed idle time 0 rfs (PID:11000): RFS client ASYNC ORL SINGLE (PID:2224) 2023-11-18T16:31:30.110144+05:30 CJQ0 started with pid=44, OS id=4564 2023-11-18T16:31:30.575686+05:30 rfs (PID:2788): RFS client GAP MANAGER (PID:4848) 2023-11-18T16:31:30.833789+05:30 QPI: opatch file present, opatch.bat QPI: qopiprep.bat file present 2023-11-18T16:31:33.387949+05:30 Setting Resource Manager plan SCHEDULER[0x4D1E]:DEFAULT_MAINTENANCE_PLAN via scheduler window Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter 2023-11-18T16:32:47.327126+05:30 Shared IO Pool defaulting to 96MB. Trying to get it from Buffer Cache for process 9576. =========================================================== Dumping current patch information =========================================================== Patch Id: 34110685 Patch Description: Windows Database Bundle Patch : 19.16.0.0.220719 (34110685) Patch Apply Time: 2022-10-11T20:43:57+05:30 Bugs Fixed: 7391838,8460502,8476681,13742922,14570574,14735102,15931756, 15959416,16662822,16664572,16750494,17275499,17395507,17428816,17468475, 17562236,17777718,17787434,18534283,18605946,18697534,19080742,19138896, 19697993,19884953,19958239,20007421,20083476,20313356,20319830,20479545, 20661314,20867658,20922160,21119541,21232786,21245711,21374587,21528318, 21629064,21639146,21888352,21965541,22066547,22162072,22252368,22325312, 22387320,22553815,22580355,22725871,22729345,22748979,23020668,23066463, 2023-11-18T16:32:48.328546+05:30 ....... 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 Perform few log switches in new primary database(NR). 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> select max(sequence#) from v$archived_log where RESETLOGS_CHANGE#=13193899; MAX(SEQUENCE#) -------------- 6 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 7 NO ASYNCHRONOUS NR Database Alert Log File: 2023-11-18T16:40:25.391277+05:30 Thread 1 advanced to log sequence 3 (LGWR switch), current SCN: 13196097 Current log# 3 seq# 3 mem# 0: D:\RUPESH\SETUPS\NR\DB\NR\ONLINELOG\O1_MF_3_LOK4PCT9_.LOG 2023-11-18T16:40:25.477427+05:30 Thread 1 advanced to log sequence 4 (LGWR switch), current SCN: 13196124 2023-11-18T16:40:25.488864+05:30 ARC3 (PID:19336): Archived Log entry 48 added for T-1.S-2 ID 0xa9bdc990 LAD:1 2023-11-18T16:40:25.516063+05:30 ARC0 (PID:4196): Archived Log entry 49 added for T-1.S-3 ID 0xa9bdc990 LAD:1 2023-11-18T16:40:26.504499+05:30 Current log# 1 seq# 4 mem# 0: D:\RUPESH\SETUPS\NR\DB\NR\ONLINELOG\O1_MF_1_LOK4PB6P_.LOG 2023-11-18T16:40:26.533415+05:30 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_LOK4PB6P_.LOG 2023-11-18T16:40:27.181398+05:30 Thread 1 advanced to log sequence 5 (LGWR switch), current SCN: 13196132 Current log# 2 seq# 5 mem# 0: D:\RUPESH\SETUPS\NR\DB\NR\ONLINELOG\O1_MF_2_LOK4PCJ6_.LOG Thread 1 cannot allocate new log, sequence 6 2023-11-18T16:40:27.219138+05:30 ARC2 (PID:13220): Archived Log entry 52 added for T-1.S-4 ID 0xa9bdc990 LAD:1 2023-11-18T16:40:28.226448+05:30 Checkpoint not complete 2023-11-18T16:40:28.237421+05:30 Current log# 2 seq# 5 mem# 0: D:\RUPESH\SETUPS\NR\DB\NR\ONLINELOG\O1_MF_2_LOK4PCJ6_.LOG 2023-11-18T16:40:29.030266+05:30 Thread 1 advanced to log sequence 6 (LGWR switch), current SCN: 13196140 Current log# 3 seq# 6 mem# 0: D:\RUPESH\SETUPS\NR\DB\NR\ONLINELOG\O1_MF_3_LOK4PCT9_.LOG 2023-11-18T16:40:29.069346+05:30 ARC3 (PID:19336): Archived Log entry 54 added for T-1.S-5 ID 0xa9bdc990 LAD:1 2023-11-18T16:40:29.084571+05:30 Thread 1 cannot allocate new log, sequence 7 Checkpoint not complete Current log# 3 seq# 6 mem# 0: D:\RUPESH\SETUPS\NR\DB\NR\ONLINELOG\O1_MF_3_LOK4PCT9_.LOG 2023-11-18T16:40:32.072288+05:30 Thread 1 advanced to log sequence 7 (LGWR switch), current SCN: 13196150 Current log# 1 seq# 7 mem# 0: D:\RUPESH\SETUPS\NR\DB\NR\ONLINELOG\O1_MF_1_LOK4PB6P_.LOG 2023-11-18T16:40:32.112393+05:30 ARC0 (PID:4196): Archived Log entry 56 added for T-1.S-6 ID 0xa9bdc990 LAD:1 2023-11-18T16:40:48.934157+05:30 As soon as you execute "alter database open" command on NR database, below log messages will be appeared in DR database alert log file. The message in target DR database "Setting recovery target incarnation to 14" is very important message and which should be reached and written to DR database 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-18T16:31:26.754922+05:30 rfs (PID:15716): krsr_rfs_atc: Identified database type as 'PHYSICAL STANDBY': Client is ASYNC (PID:15480) rfs (PID:15716): New archival redo branch: 1153239998 current: 1150397905 rfs (PID:15716): Primary database is in MAXIMUM PERFORMANCE mode 2023-11-18T16:31:26.796018+05:30 rfs (PID:15716): Selected LNO:5 for T-1.S-2 dbid 2829010735 branch 1153239998 2023-11-18T16:31:27.674875+05:30 rfs (PID:15840): krsr_rfs_atc: Identified database type as 'PHYSICAL STANDBY': Client is ARCH (PID:9892) rfs (PID:15840): New archival redo branch: 1153239998 current: 1150397905 2023-11-18T16:31:27.713644+05:30 rfs (PID:15840): Selected LNO:6 for T-1.S-1 dbid 2829010735 branch 1153239998 rfs (PID:15840): A new recovery destination branch has been registered rfs (PID:15840): New Archival REDO Branch(resetlogs_id): 1153239998 Prior: 1150397905 rfs (PID:15840): Archival Activation ID: 0xa9bdc990 Current: 0xa991dc35 rfs (PID:15840): Effect of primary database OPEN RESETLOGS 2023-11-18T16:31:27.793565+05:30 Setting recovery target incarnation to 14 2023-11-18T16:31:27.896097+05:30 ARC1 (PID:11116): Archived Log entry 24 added for T-1.S-1 ID 0xa9bdc990 LAD:1 2023-11-18T16:31:28.674254+05:30 rfs (PID:14492): krsr_rfs_atc: Identified database type as 'PHYSICAL STANDBY': Client is Foreground (PID:5940) 2023-11-18T16:31:28.804217+05:30 rfs (PID:15840): Opened log for T-1.S-226 dbid 2829010735 branch 1150397905 2023-11-18T16:31:28.824739+05:30 rfs (PID:15840): Archived Log entry 25 added for B-1150397905.T-1.S-226 ID 0xa991dc35 LAD:2 2023-11-18T16:40:25.616405+05:30 rfs (PID:15840): Opened log for T-1.S-3 dbid 2829010735 branch 1153239998 2023-11-18T16:40:25.624410+05:30 ARC2 (PID:18840): Archived Log entry 26 added for T-1.S-2 ID 0xa9bdc990 LAD:1 2023-11-18T16:40:25.634916+05:30 rfs (PID:15840): Archived Log entry 27 added for B-1153239998.T-1.S-3 ID 0xa9bdc990 LAD:2 2023-11-18T16:40:25.679220+05:30 rfs (PID:15324): krsr_rfs_atc: Identified database type as 'PHYSICAL STANDBY': Client is ASYNC (PID:15044) rfs (PID:15324): Primary database is in MAXIMUM PERFORMANCE mode 2023-11-18T16:40:25.708937+05:30 rfs (PID:15324): Selected LNO:5 for T-1.S-4 dbid 2829010735 branch 1153239998 2023-11-18T16:40:27.406170+05:30 rfs (PID:15324): Selected LNO:6 for T-1.S-5 dbid 2829010735 branch 1153239998 2023-11-18T16:40:28.414357+05:30 ARC3 (PID:1936): Archived Log entry 28 added for T-1.S-4 ID 0xa9bdc990 LAD:1 2023-11-18T16:40:29.218643+05:30 rfs (PID:15324): Selected LNO:5 for T-1.S-6 dbid 2829010735 branch 1153239998 2023-11-18T16:40:29.221652+05:30 ARC0 (PID:18776): Archived Log entry 29 added for T-1.S-5 ID 0xa9bdc990 LAD:1 2023-11-18T16:40:32.245426+05:30 rfs (PID:15324): Selected LNO:6 for T-1.S-7 dbid 2829010735 branch 1153239998 2023-11-18T16:40:32.251519+05:30 ARC1 (PID:11116): Archived Log entry 30 added for T-1.S-6 ID 0xa9bdc990 LAD:1 |
|
Step 5: Please note that MRP process has not yet been started on DR server, but log shipping is enabled. Now its time to start the MRP process in DR standby database. Once you start the MRP process in DR database, then below messages will be appeared in alert log. The change 13193898 is the last resetlog change that was recorded in NR database when we executed activate command. You can see that MRP process has applied archived log sequence 226 which was in ACTIVE state at NR site and before executing ACTIVATE STANDBY command, but has not applied remaining archived logs which were physically available in NR i.e. from seq 227 to seq 230 because we have not executed "FINISH" command before ACTIVATE command. After applying 226 sequence, it started the sequence from 1. DR Alert Log File: 2023-11-18T16:43:07.618770+05:30 ALTER DATABASE RECOVER managed standby database disconnect from session Starting background process MRP0 2023-11-18T16:43:07.682882+05:30 MRP0 started with pid=34, OS id=5820 2023-11-18T16:43:12.740440+05:30 Started logmerger process 2023-11-18T16:43:12.773827+05:30 PR00 (PID:15240): Managed Standby Recovery starting Real Time Apply 2023-11-18T16:43:13.099229+05:30 Parallel Media Recovery started with 8 slaves 2023-11-18T16:43:13.222615+05:30 Media Recovery start incarnation depth : 1, target inc# : 14, irscn : 13193898 Stopping change tracking 2023-11-18T16:43:13.340852+05:30 PR00 (PID:15240): Media Recovery Log D:\RUPESH\SETUPS\STANDBY\ARCH\1_226_1150397905.ARC 2023-11-18T16:43:13.538450+05:30 PR00 (PID:15240): Media Recovery Log D:\RUPESH\SETUPS\STANDBY\ARCH\1_1_1153239998.ARC 2023-11-18T16:43:13.714830+05:30 PR00 (PID:15240): Media Recovery Log D:\RUPESH\SETUPS\STANDBY\ARCH\1_2_1153239998.ARC 2023-11-18T16:43:13.744839+05:30 Completed: ALTER DATABASE RECOVER managed standby database disconnect from session 2023-11-18T16:43:14.134178+05:30 PR00 (PID:15240): Media Recovery Log D:\RUPESH\SETUPS\STANDBY\ARCH\1_3_1153239998.ARC 2023-11-18T16:43:14.289825+05:30 PR00 (PID:15240): Media Recovery Log D:\RUPESH\SETUPS\STANDBY\ARCH\1_4_1153239998.ARC 2023-11-18T16:43:14.483406+05:30 PR00 (PID:15240): Media Recovery Log D:\RUPESH\SETUPS\STANDBY\ARCH\1_5_1153239998.ARC 2023-11-18T16:43:14.673481+05:30 PR00 (PID:15240): Media Recovery Log D:\RUPESH\SETUPS\STANDBY\ARCH\1_6_1153239998.ARC PR00 (PID:15240): Media Recovery Waiting for T-1.S-7 (in transit) 2023-11-18T16:43:14.914140+05:30 Recovery of Online Redo Log: Thread 1 Group 6 Seq 7 Reading mem 0 Mem# 0: D:\RUPESH\SETUPS\STANDBY\SRLS\STANDBY03.LOG |
|
You can check the dummy table which was created in PR database and part of failed EOD operations. The created table does not exist in the target database since we did not execute FINISH command. On NR: SQL> select * from without_finish; select * from without_finish * ERROR at line 1: ORA-00942: table or view does not exist OR On DR: SQL> recover managed standby database cancel; Media recovery complete. SQL> alter database open read only; Database altered. SQL> select * from without_finish; select * from without_finish * ERROR at line 1: ORA-00942: table or view does not exist |
So the final conclusion is that MRP will apply the archived log sequence(Seq 226) which was the current log sequence from the previous incarnation after the last applied archived log sequence(Seq 225) at NR and DR sites. i.e. Here, the log sequence number 226, but it will not apply remaining available archived logs (Seq 227- Seq 230). It will then start applying log sequences starting from log sequence 1.
Thanks for reading this post ! Please comment if you like this post ! Click on FOLLOW to get next blog updates !
Nicely explained Rupesh.....!
ReplyDeleteI had this query today and it helped me , thanks
ReplyDelete