Scenario Description: How to achieve Zero Data Loss if Primary DB server goes down abruptly or crashed and not available. You will have question in your mind that both Primary and Standby DBs were in sync or not, but what about current redo logs data which was yet to be archived. It can be lost. How can we recover this current redo log data ? |
Environment Details:
Primary DB Name: PR
Standby DB Name: DR
FAR Sync Instance Name: FSYNC
Operating System: Windows 10 Pro 64 Bit
Database Version: 19.16.0.0
Note: Here, PR, DR, and FAR SYNC DB servers are located on same Windows host since this is testing environment. In actual environment, PR,DR, and FAR SYNC will be on different hosts.
Scenario Description: Suppose, your primary and standby databases are in sync without any issue. If primary goes down or terminated abruptly then how to avoid zero data loss. Your standby database can be in near distance or in far distance which is in hundreds or thousands of miles or in any geographical area. There can be data loss due to network latency issues between primary and standby databases. Using SYNC redo transport mode to achieve zero data loss may not be a feasible option due to network latency between primary and standby databases because of the impact on the commit response times at the primary site. Here, the FAR Sync instance concept comes where FAR Sync instance can be created at Primary Site in SYNC mode where Primary is located (Refer below diagram) so that logs will get shipped immediately to FAR sync Instance and then that destination can be further cascaded to actual standby database(DR) from FAR Sync DB server. i.e. Primary---> FAR Sync ---> Standby Refer detail info here: What is FAR Sync Instance or FAR Sync Standby in Oracle ? How the above configuration will work if Primary gets terminated or not available? Let's say If the primary database has failed and assume that the far sync instance was synchronized at the time of the primary failure, then the FAR sync instance and the actual standby instance will coordinate a final redo shipment from the FAR sync instance to the standby to ship any redo not yet available to the standby and then perform a zero-data-loss failover. This enables zero data loss failover for all cases, except for a disaster outage of both the primary and the Far Sync instance at the same time. So let's start the scenario to avoid zero data loss. |
Step 1 : Consider you have already configured Primary and Standby DB servers. Ensure your Primary and Standby databases are in sync and logs are getting applied from Primary to Standby without any issue. On Primary: SQL> set lines 300 pages 3000 SQL> select name,db_unique_name,open_mode,database_role,protection_mode,controlfile_type from v$database; NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE CONTROL ----- -------------- ---------- ------------- -------------------- ------- PR PR READ WRITE PRIMARY MAXIMUM PERFORMANCE CURRENT 16:27:07 SQL> select max(sequence#) from v$archived_log; MAX(SEQUENCE#) -------------- 275 On Standby: SQL> set lines 300 pages 3000 SQL> select name,db_unique_name,open_mode,database_role,protection_mode,controlfile_type from v$database; NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE CONTROL ---- -------------- --------- ---------------- ------------------- ------- PR DR MOUNTED PHYSICAL STANDBY MAXIMUM PERFORMANCE STANDBY 16:27:20 SQL> select max(sequence#) from v$archived_log where applied='YES'; MAX(SEQUENCE#) -------------- 275 |
Step 2: Create and configure 3rd DB server as FAR Sync instance. 1) Refer below pfile for FAR Sync instance located in "D:\RUPESH\Setups\WINDOWS.X64_193000_db_home\database\INITFARSYNC.ORA" *._readable_standby_sync_timeout=60 *.audit_file_dest='D:\RUPESH\Setups\FARSYNC\adump' *.audit_trail='db' *.compatible='19.0.0' *.control_files='D:\RUPESH\Setups\FARSYNC\DB\FSYNC\control01.ctl','D:\RUPESH\Setups\FARSYNC\DB\FSYNC\control02.ctl' *.db_block_size=8192 *.db_keep_cache_size=218103808 *.db_name='PR' *.db_unique_name='FSYNC' *.diagnostic_dest='D:\RUPESH\app\rghubade' *.log_archive_dest_1='LOCATION=D:\RUPESH\Setups\FARSYNC\ARCH' *.log_archive_format='%t_%s_%r.arc' *.nls_language='AMERICAN' *.nls_territory='AMERICA' *.open_cursors=300 *.pga_aggregate_limit=3221225472 *.pga_aggregate_target=1073741824 *.processes=1000 *.remote_login_passwordfile='EXCLUSIVE' *.sga_target=2048m *.undo_tablespace='UNDOTBS1' *.standby_file_management='AUTO' *.log_archive_config='dg_config=(PR,DR,FSYNC)' 2) Copy existing database password file and rename as D:\RUPESH\Setups\WINDOWS.X64_193000_db_home\database\PWDFSYNC.ora 3) Add below entries in listener.ora and tnsnames.ora file. D:\RUPESH\Setups\WINDOWS.X64_193000_db_home\network\admin\listener.ora LISTENER_FSYNC = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC4)) (ADDRESS = (PROTOCOL = TCP)(HOST = 10.172.100.1)(PORT = 1533)) ) ) SID_LIST_LISTENER_FSYNC = (SID_LIST = (SID_DESC = (ORACLE_HOME = D:\RUPESH\Setups\WINDOWS.X64_193000_db_home) (SID_NAME = FSYNC) ) ) D:\RUPESH\Setups\WINDOWS.X64_193000_db_home\network\admin\tnsnames.ora FSYNC = (DESCRIPTION = (SDU = 65535) (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.172.100.1)(PORT = 1533)) ) (CONNECT_DATA = (SERVICE_NAME = FSYNC) ) ) C:\windows\system32> lsnrctl start LISTENER_FSYNC Test the connection by tnsping command. C:\windows\system32>tnsping fsync TNS Ping Utility for 64-bit Windows: Version 19.0.0.0.0 - Production on 22-MAY-2023 16:25:26 Copyright (c) 1997, 2022, Oracle. All rights reserved. Used parameter files: D:\RUPESH\Setups\WINDOWS.X64_193000_db_home\network\admin\sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (SDU = 65535) (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.172.100.1)(PORT = 1533))) (CONNECT_DATA = (SERVICE_NAME = FSYNC))) OK (20 msec) 4) First create service using below command since this is Windows server. C:\windows\system32>oradim -NEW -SID FSYNC -syspwd sys123 -STARTMODE auto -PFILE D:\RUPESH\Setups\WINDOWS.X64_193000_db_home\database\INITFSYNC.ORA OPW-00029: Password complexity failed for SYS user : Password must contain at least 8 characters. Instance created. 5) Execute below command on Primary database to set protection mode to maximum availability. SQL> ALTER DATABASE SET STANDBY TO MAXIMIZE AVAILABILITY; Database altered. SQL> set lines 300 pages 3000 SQL> select name,db_unique_name,open_mode,database_role,protection_mode,controlfile_type from v$database; NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE CONTROL ---- -------------- ---------- ------------- -------------------- ------- PR PR READ WRITE PRIMARY MAXIMUM AVAILABILITY CURRENT 6) Now create FAR Sync control file for FAR Sync instance. Execute below command in Primary database. SQL> ALTER DATABASE CREATE FAR SYNC INSTANCE CONTROLFILE AS 'D:\RUPESH\Setups\FARSYNC\DB\FSYNC\far_sync.ctl'; Database altered. When you execute "CREATE FAR SYNC" command then you will see below messages in Primary DB alert log file. Primary DB Alert Log: 2023-05-22T16:37:27.810845+05:30 ALTER DATABASE CREATE FAR SYNC INSTANCE CONTROLFILE AS 'D:\RUPESH\Setups\FARSYNC\DB\FSYNC\far_sync.ctl' 2023-05-22T16:37:27.907335+05:30 Clearing standby activation ID 2831561964 (0xa8c634ec) The primary database controlfile was created using the 'MAXLOGFILES 16' clause. There is space for up to 13 standby redo logfiles Use the following SQL commands on the standby database to create standby redo logfiles that match the primary database: ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 209715200; ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 209715200; ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 209715200; ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 209715200; Completed: ALTER DATABASE CREATE FAR SYNC INSTANCE CONTROLFILE AS 'D:\RUPESH\Setups\FARSYNC\DB\FSYNC\far_sync.ctl' 7) Now set the environment variables on FAR Sync Server and start the instance in nomount stage. C:\windows\system32>set ORACLE_SID=FSYNC C:\windows\system32>sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Mon May 22 16:46:30 2023 Version 19.16.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.16.0.0.0 SQL> select name,open_mode from v$database; select name,open_mode from v$database * ERROR at line 1: ORA-01507: database not mounted SQL> shut abort ORACLE instance shut down. SQL> SQL> startup nomount ORACLE instance started. Total System Global Area 2147482432 bytes Fixed Size 9030464 bytes Variable Size 452984832 bytes Database Buffers 1677721600 bytes Redo Buffers 7745536 bytes SQL> show parameter db_name NAME TYPE VALUE ----------- ------- ------ db_name string PR SQL> show parameter db_unique_name NAME TYPE VALUE ---------------- ------- ------ db_unique_name string FSYNC 8) Restore FAR Sync controlfile located in "D:\RUPESH\Setups\FARSYNC\DB\FSYNC\" directory. C:\windows\system32>rman target / Recovery Manager: Release 19.0.0.0.0 - Production on Mon May 22 16:50:04 2023 Version 19.16.0.0.0 Copyright (c) 1982, 2022, Oracle and/or its affiliates. All rights reserved. connected to target database: PR (not mounted) RMAN> restore farsync controlfile from 'D:\RUPESH\Setups\FARSYNC\DB\FSYNC\far_sync.ctl'; Starting restore at 22-MAY-23 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=195 device type=DISK channel ORA_DISK_1: copied control file copy output file name=D:\RUPESH\SETUPS\FARSYNC\DB\FSYNC\CONTROL01.CTL output file name=D:\RUPESH\SETUPS\FARSYNC\DB\FSYNC\CONTROL02.CTL Finished restore at 22-MAY-23 RMAN> Now mount the instance. From RMAN prompt also, you can mount the instance. RMAN> alter database mount; released channel: ORA_DISK_1 Statement processed RMAN> 9) You can verify the database role and control file as FAR SYNC. NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE CONTROL ----- -------------- --------- ------------- -------------------- ------- PR FSYNC MOUNTED FAR SYNC MAXIMUM AVAILABILITY FARSYNC 10) Now you have to drop and recreate standby redo log files since the FAR Sync instance has primary SRL's entry which are not physically available. SQL> col member for a56 SQL> set lines 300 pages 3000 SQL> select GROUP#,STATUS,TYPE,MEMBER from v$logfile; GROUP# STATUS TYPE MEMBER ------ ------- ------- ------------------------------------------- 3 DELETED ONLINE D:\RUPESH\SETUPS\PRIMARY\DB\PR\REDO03.LOG 2 DELETED ONLINE D:\RUPESH\SETUPS\PRIMARY\DB\PR\REDO02.LOG 1 DELETED ONLINE D:\RUPESH\SETUPS\PRIMARY\DB\PR\REDO01.LOG 4 STANDBY D:\RUPESH\SETUPS\PRIMARY\SRLS\PRIMARY01.LOG 5 STANDBY D:\RUPESH\SETUPS\PRIMARY\SRLS\PRIMARY02.LOG 6 STANDBY D:\RUPESH\SETUPS\PRIMARY\SRLS\PRIMARY03.LOG 7 STANDBY D:\RUPESH\SETUPS\PRIMARY\SRLS\PRIMARY04.LOG SQL> select GROUP#,THREAD#,SEQUENCE#,BYTES,BLOCKSIZE,USED,ARCHIVED,STATUS from v$standby_log; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE USED ARC STATUS ------ ------- ---------- ---------- ---------- ---- --- ---------- 4 0 0 209715200 512 0 YES UNASSIGNED 5 0 0 209715200 512 0 YES UNASSIGNED 6 1 0 209715200 512 0 YES UNASSIGNED 7 1 0 209715200 512 0 YES UNASSIGNED SQL> alter database drop logfile group 4; Database altered. SQL> alter database drop logfile group 5; Database altered. SQL> alter database drop logfile group 6; Database altered. SQL> alter database drop logfile group 7; Database altered. SQL> alter database add standby logfile group 4 'D:\RUPESH\Setups\FARSYNC\SRLs\fsyncredo01.log' size 200m; Database altered. SQL> alter database add standby logfile group 5 'D:\RUPESH\Setups\FARSYNC\SRLs\fsyncredo02.log' size 200m; Database altered. SQL> alter database add standby logfile group 6 'D:\RUPESH\Setups\FARSYNC\SRLs\fsyncredo03.log' size 200m; Database altered. SQL> alter database add standby logfile group 7 'D:\RUPESH\Setups\FARSYNC\SRLs\fsyncredo04.log' size 200m; Database altered. SQL> select GROUP#,STATUS,TYPE,MEMBER from v$logfile; GROUP# STATUS TYPE MEMBER ------ ------- ------- --------------------------------------------- 4 STANDBY D:\RUPESH\SETUPS\FARSYNC\SRLS\FSYNCREDO01.LOG 5 STANDBY D:\RUPESH\SETUPS\FARSYNC\SRLS\FSYNCREDO02.LOG 6 STANDBY D:\RUPESH\SETUPS\FARSYNC\SRLS\FSYNCREDO03.LOG 7 STANDBY D:\RUPESH\SETUPS\FARSYNC\SRLS\FSYNCREDO04.LOG |
Step 3: Now check the existing destination parameters in Primary and Standby databases. In your existing configuration, on Primary, dest_id 2 is set for standby(DR) database. On Primary: SQL> show parameter log_archive_dest_2 NAME TYPE VALUE ------------------- ----------- ------------------------------ log_archive_dest_2 string service=DR LGWR ASYNC valid_fo r=(all_logfiles,all_roles) db_ unique_name=DR SQL> show parameter log_archive_dest_3 NAME TYPE VALUE ------------------- ----------- ------------------------------ log_archive_dest_3 string service=FSYNC LGWR SYNC AFFIRM v alid_for=(all_logfiles,all_rol es) db_unique_name=FSYNC SQL> show parameter log_archive_dest_state_2 NAME TYPE VALUE ------------------------- ----------- --------- log_archive_dest_state_2 string ENABLE SQL> show parameter log_archive_dest_state_3 NAME TYPE VALUE ------------------------- ----------- ---------- log_archive_dest_state_3 string ENABLE On Standby: SQL> show parameter log_archive_dest_2 NAME TYPE VALUE --------------------- ----------- ------------------- log_archive_dest_2 string SQL> show parameter log_archive_dest_3 NAME TYPE VALUE --------------------- ----------- ------------------- log_archive_dest_3 string SQL> show parameter log_archive_dest_state_2 NAME TYPE VALUE -------------------------- ----------- -------------- log_archive_dest_state_2 string enable SQL> show parameter log_archive_dest_state_3 NAME TYPE VALUE -------------------------- ----------- -------------- log_archive_dest_state_3 string enable |
Step 4: Reset the destination parameters on Primary, Standby, and FAR SYNC instances as below. Existing: PR ----> DR New: PR ----> FAR SYNC ----> DR No need to reset on Primary, you can just defer the destination for standby(DR). On Primary: SQL> alter system set log_archive_config='dg_config=(PR,DR,FSYNC)'; System altered. SQL> alter system set log_archive_dest_3='service=FSYNC LGWR SYNC AFFIRM valid_for=(all_logfiles,all_roles) db_unique_name=FSYNC'; System altered. SQL> alter system set log_archive_dest_state_2=defer; System altered. SQL> alter system set log_archive_dest_state_3=enable; System altered. SQL> show parameter log_archive_dest_2 NAME TYPE VALUE -------------------- ----------- ------------------------------ log_archive_dest_2 string service=DR LGWR ASYNC valid_fo r=(all_logfiles,all_roles) db_ unique_name=DR SQL> show parameter log_archive_dest_3 NAME TYPE VALUE -------------------- ----------- ------------------------------ log_archive_dest_3 string service=MIN LGWR SYNC AFFIRM v alid_for=(all_logfiles,all_rol es) db_unique_name=MIN SQL> show parameter log_archive_dest_state_2 NAME TYPE VALUE -------------------------- ----------- ------ log_archive_dest_state_2 string DEFER SQL> show parameter log_archive_dest_state_3 NAME TYPE VALUE -------------------------- ----------- ------ log_archive_dest_state_3 string ENABLE SQL> select dest_id,error from v$archive_dest where dest_id in(2,3); DEST_ID ERROR ---------- --------------- 2 3 On Standby: SQL> alter system set log_archive_config='dg_config=(PR,DR,FSYNC)'; System altered. SQL> alter system set log_archive_dest_3='service=FSYNC LGWR ASYNC valid_for=(online_logfile,primary_role) db_unique_name=FSYNC'; System altered. SQL> alter system set log_archive_dest_state_3=defer; System altered. SQL> show parameter log_archive_dest_2 NAME TYPE VALUE -------------------- ----------- ------ log_archive_dest_2 string SQL> show parameter log_archive_dest_3 NAME TYPE VALUE ---------------------- ----------- ------------------------------ log_archive_dest_3 string service=FSYNC LGWR ASYNC valid _for=(online_logfile,primary_r ole) db_unique_name=FSYNC SQL> show parameter log_archive_dest_state_2 NAME TYPE VALUE -------------------------- ----------- ------- log_archive_dest_state_2 string enable SQL> show parameter log_archive_dest_state_3 NAME TYPE VALUE -------------------------- ----------- ------- log_archive_dest_state_3 string DEFER SQL> show parameter log_archive_config NAME TYPE VALUE ----------------------- ----------- ------------------------ log_archive_config string dg_config=(PR,DR,FSYNC) On FAR Sync Instance: SQL> alter system set log_archive_config='dg_config=(PR,DR,FSYNC)'; System altered. 1SQL> alter system set log_archive_dest_2='service=DR LGWR ASYNC valid_for=(all_logfiles,all_roles) db_unique_name=DR'; System altered. SQL> show parameter log_archive_dest_2 NAME TYPE VALUE -------------------- ----------- ------------------------------ log_archive_dest_2 string service=DR LGWR ASYNC valid_fo r=(all_logfiles,all_roles) db_ unique_name=DR SQL> show parameter log_archive_dest_3 NAME TYPE VALUE -------------------- ----------- ------ log_archive_dest_3 string SQL> show parameter log_archive_dest_state_2 NAME TYPE VALUE ------------------------- ----------- ------- log_archive_dest_state_2 string ENABLE SQL> show parameter log_archive_dest_state_3 NAME TYPE VALUE --------------------------- ----------- ------- log_archive_dest_state_3 string enable SQL> show parameter log_archive_config NAME TYPE VALUE ----------------------- ----------- ------------------------ log_archive_config string dg_config=(PR,DR,FSYNC) |
Step 5: Ensure your SRLs(Standby Redo Log Files) are getting updated on both FAR Sync and Standby database(DR). Execute below commands to verify the same. On Primary: SQL> alter system switch logfile; SQL> alter system switch logfile; SQL> alter system switch logfile; 17:31:52 SQL> select max(sequence#) from v$archived_log; MAX(SEQUENCE#) -------------- 300 17:37:34 SQL> set lines 300 pages 3000 17:37:34 SQL> col member for a56 17:37:34 SQL> select a.GROUP#, A.BYTES/1024/1024 SIZE_MB, b.MEMBER,a.status,a.sequence# from v$log a,v$logfile b where a.group#=b.group#; GROUP# SIZE_MB MEMBER STATUS SEQUENCE# ------ ------- ----------------------------------------- -------- --------- 3 200 D:\RUPESH\SETUPS\PRIMARY\DB\PR\REDO03.LOG CURRENT 301 2 200 D:\RUPESH\SETUPS\PRIMARY\DB\PR\REDO02.LOG INACTIVE 300 1 200 D:\RUPESH\SETUPS\PRIMARY\DB\PR\REDO01.LOG INACTIVE 299 On Standby: 17:33:04 SQL> select max(sequence#) from v$archived_log where applied='YES'; MAX(SEQUENCE#) -------------- 300 17:35:15 SQL> select a.GROUP#,a.status,a.sequence# from v$standby_log a; GROUP# STATUS SEQUENCE# ---------- ---------- ---------- 4 ACTIVE 301 5 UNASSIGNED 0 6 UNASSIGNED 0 7 UNASSIGNED 0 17:39:43 SQL> set lines 300 pages 3000 17:39:51 SQL> select GROUP#,THREAD#,SEQUENCE#,BYTES,BLOCKSIZE,USED,ARCHIVED,STATUS from v$standby_log; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE USED ARC STATUS ------ ------- --------- ---------- ---------- ------- --- ---------- 4 1 301 209715200 512 3296768 YES ACTIVE 5 1 0 209715200 512 0 NO UNASSIGNED 6 0 0 209715200 512 0 YES UNASSIGNED 7 0 0 209715200 512 0 YES UNASSIGNED On FAR Sync: 17:38:33 SQL> select a.GROUP#,a.status,a.sequence# from v$standby_log a; GROUP# STATUS SEQUENCE# ---------- ---------- ---------- 4 UNASSIGNED 0 5 ACTIVE 301 6 UNASSIGNED 0 7 UNASSIGNED 0 17:39:10 SQL> set lines 300 pages 3000 17:39:14 SQL> select GROUP#,THREAD#,SEQUENCE#,BYTES,BLOCKSIZE,USED,ARCHIVED,STATUS from v$standby_log; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE USED ARC STATUS ------ ------- --------- --------- --------- ------- --- ---------- 4 1 0 209715200 512 0 NO UNASSIGNED 5 1 301 209715200 512 3278336 YES ACTIVE 6 0 0 209715200 512 0 NO UNASSIGNED 7 0 0 209715200 512 0 NO UNASSIGNED Here, you can see the last generated archive log sequence number is 300 which is reached to FAR Sync and applied on Standby. Now the next sequence number is 301 which has to be archived on Primary which has not yet been generated. The same sequence number is active on Standby and FAR Sync. Note: Do not execute "alter system switch logfile" command on Primary since we are going to perform scenario test. |
Step 6: Now start the scenario as below. On Primary: 17:42:47 SQL> create table employee(emp_id number(5),emp_name varchar2(12)); Table created. 17:42:49 SQL> insert into employee values(1,'ABC'); 1 row created. 17:42:49 SQL> insert into employee values(2,'PQR'); 1 row created. 17:42:49 SQL> insert into employee values(3,'XYZ'); 1 row created. 17:42:49 SQL> select * from employee; EMP_ID EMP_NAME ---------- ------------ 1 ABC 2 PQR 3 XYZ 17:42:49 SQL> commit; Commit complete. You can again check the last generated archive log sequence. 17:42:50 SQL> select a.GROUP#, A.BYTES/1024/1024 SIZE_MB, b.MEMBER,a.status,a.sequence# from v$log a,v$logfile b where a.group#=b.group#; GROUP# SIZE_MB MEMBER STATUS SEQUENCE# ------ ------- ----------------------------------------- -------- --------- 3 200 D:\RUPESH\SETUPS\PRIMARY\DB\PR\REDO03.LOG CURRENT 301 2 200 D:\RUPESH\SETUPS\PRIMARY\DB\PR\REDO02.LOG INACTIVE 300 1 200 D:\RUPESH\SETUPS\PRIMARY\DB\PR\REDO01.LOG INACTIVE 299 17:43:17 SQL> select max(sequence#) from v$archived_log; MAX(SEQUENCE#) -------------- 300 Here, I am terminating the instance as testing purpose. In real scenario, your primary instance will not be accessible which is similar to our case i.e. instance termination. 17:43:24 SQL> shut abort ORACLE instance shut down. 17:44:02 SQL> On Standby: 17:44:37 SQL> select a.GROUP#,a.status,a.sequence# from v$standby_log a; GROUP# STATUS SEQUENCE# ---------- ---------- ---------- 4 ACTIVE 301 5 UNASSIGNED 0 6 UNASSIGNED 0 7 UNASSIGNED 0 17:44:37 SQL> select max(sequence#) from v$archived_log where applied='YES'; MAX(SEQUENCE#) -------------- 300 On FAR Sync: 17:45:05 SQL> select a.GROUP#,a.status,a.sequence# from v$standby_log a; GROUP# STATUS SEQUENCE# ---------- ---------- ---------- 4 UNASSIGNED 0 5 ACTIVE 301 6 UNASSIGNED 0 7 UNASSIGNED 0 |
Step 7: Ensure destination is enabled in FAR Sync instance which points to standby database(DR) and also, the destination in standby(DR) database is set for FAR SYNC instance and can be DEFERRED. On FAR Sync: SQL> show parameter log_archive_dest_2 NAME TYPE VALUE -------------------- ----------- ------------------------------ log_archive_dest_2 string service=DR LGWR ASYNC valid_fo r=(all_logfiles,all_roles) db_ unique_name=DR SQL> show parameter log_archive_dest_state_3 NAME TYPE VALUE --------------------------- ----------- ------- log_archive_dest_state_3 string enable On Standby: SQL> show parameter log_archive_dest_3 NAME TYPE VALUE ---------------------- ----------- ------------------------------ log_archive_dest_3 string service=FSYNC LGWR ASYNC valid _for=(online_logfile,primary_r ole) db_unique_name=FSYNC SQL> show parameter log_archive_dest_state_3 NAME TYPE VALUE -------------------------- ----------- ------- log_archive_dest_state_3 string DEFER |
Step 8: Now you are ready to perform failover command in Standby database in just one command. Execute below command in Standby Database(DR). On Standby: 17:53:12 SQL> alter database failover to DR; Database altered. Elapsed: 00:00:04.98 SQL> set lines 300 pages 3000 SQL> col DB_UNIQUE_NAME for a16 SQL> col OPEN_MODE for a14 SQL> col name for a8 SQL> select name,db_unique_name,open_mode,database_role,protection_mode,controlfile_type from v$database; NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE CONTROL ---- -------------- --------- ------------- -------------------- ------- PR DR MOUNTED PRIMARY MAXIMUM PERFORMANCE CURRENT 17:56:41 SQL> alter database open; Database altered. SQL> select name,db_unique_name,open_mode,database_role,protection_mode,controlfile_type from v$database; NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE CONTROL ---- -------------- ---------- ------------- -------------------- ------- PR DR READ WRITE PRIMARY MAXIMUM PERFORMANCE CURRENT SQL> select * from employee; EMP_ID EMP_NAME ---------- ------------ 1 ABC 2 PQR 3 XYZ Now you can see the the table created in Primary database which was in the current log sequence number 301 and which was not generated i.e. it was in current state in Primary database which is now available in standby database(DR) after activating standby database. Standby Database Alert Log: 2023-05-22T17:53:27.617506+05:30 alter database failover to DR 2023-05-22T17:53:27.622808+05:30 .... (PID:18180): The Time Management Interface (TMI) is being enabled for role transition .... (PID:18180): information. This will result in messages beingoutput to the alert log .... (PID:18180): file with the prefix 'TMI: '. This is being enabled to make the timing of .... (PID:18180): the various stages of the role transition available for diagnostic purposes. .... (PID:18180): This output will end when the role transition is complete. TMI: dbsdrv failover to target BEGIN 2023-05-22 17:53:27.622808 Terminal Recovery requested in process 18180 TMI: adbdrv termRecovery BEGIN 2023-05-22 17:53:27.642552 .... (PID:18180): Terminal Recovery: Stopping real time apply 2023-05-22T17:53:27.658386+05:30 PR00 (PID:3532): MRP0: Background Media Recovery cancelled with status 16037 2023-05-22T17:53:27.658386+05:30 Errors in file D:\RUPESH\APP\RGHUBADE\diag\rdbms\dr\dr\trace\dr_pr00_3532.trc: ORA-16037: user requested cancel of managed recovery operation PR00 (PID:3532): Managed Standby Recovery not using Real Time Apply Recovery interrupted! Recovered data files to a consistent state at change 6948582 Stopping change tracking 2023-05-22T17:53:27.851378+05:30 Errors in file D:\RUPESH\APP\RGHUBADE\diag\rdbms\dr\dr\trace\dr_pr00_3532.trc: ORA-16037: user requested cancel of managed recovery operation 2023-05-22T17:53:28.662718+05:30 .... (PID:18180): Terminal Recovery: Stopped real time apply TMI: adbdrv termRecovery END 2023-05-22 17:53:28.662718 2023-05-22T17:53:28.680863+05:30 Serial Media Recovery started NET (PID:18180): Managed Standby Recovery not using Real Time Apply Stopping change tracking NET (PID:18180): Media Recovery Waiting for T-1.S-301 (in transit) 2023-05-22T17:53:30.783791+05:30 NET (PID:18180): Killing 1 processes (PIDS:13920) (all RFS, wait for I/O) in order to disallow current and future RFS connections. Requested by OS process 18180 2023-05-22T17:53:32.018801+05:30 NET (PID:18180): Begin: SRL archival NET (PID:18180): End: SRL archival NET (PID:18180): Terminal Recovery timestamp is '05/22/2023 17:53:32' NET (PID:18180): Terminal Recovery: applying standby redo logs. NET (PID:18180): Terminal Recovery: thread 1 seq# 301 redo required 2023-05-22T17:53:32.064685+05:30 NET (PID:18180): Terminal Recovery: 2023-05-22T17:53:32.064685+05:30 Recovery of Online Redo Log: Thread 1 Group 4 Seq 301 Reading mem 0 Mem# 0: D:\RUPESH\SETUPS\STANDBY\SRLS\STANDBY01.LOG Terminal Recovery finished with No-Data-Loss 2023-05-22T17:53:32.097036+05:30 Incomplete Recovery applied until change 6948583 time 05/22/2023 17:43:44 2023-05-22T17:53:32.192280+05:30 NET (PID:18180): Terminal Recovery enabled LAD:3 Terminal Recovery: successful completion NET (PID:18180): Forcing ARSCN to IRSCN for TR SCN:0x00000000006a06e7 NET (PID:18180): Attempt to set limbo arscn SCN:0x00000000006a06e7 irscn SCN:0x00000000006a06e7 NET (PID:18180): Resetting standby activation ID 2831561964 (0xa8c634ec) Stopping change tracking Maximum wait for role transition is 15 minutes. TMI: kcv_commit_to_so_to_primary wait for MRP to finish BEGIN 2023-05-22 17:53:32.272787 TMI: kcv_commit_to_so_to_primary wait for MRP to finish END 2023-05-22 17:53:32.272787 TMI: kcv_commit_to_so_to_primary Switchover from physical BEGIN 2023-05-22 17:53:32.272787 Backup controlfile written to trace file D:\RUPESH\APP\RGHUBADE\diag\rdbms\dr\dr\trace\dr_ora_18180.trc Standby terminal recovery start SCN: 6948582 RESETLOGS after incomplete recovery UNTIL CHANGE 6948583 time 05/22/2023 17:43:44 NET (PID:18180): ORL pre-clearing operation disabled by switchover Online log D:\RUPESH\SETUPS\STANDBY\DB\DR\REDO01.LOG: Thread 1 Group 1 was previously cleared Online log D:\RUPESH\SETUPS\STANDBY\DB\DR\REDO02.LOG: Thread 1 Group 2 was previously cleared Online log D:\RUPESH\SETUPS\STANDBY\DB\DR\REDO03.LOG: Thread 1 Group 3 was previously cleared Standby became primary SCN: 6948581 2023-05-22T17:53:32.480368+05:30 Setting recovery target incarnation to 3 2023-05-22T17:53:32.496402+05:30 NET (PID:18180): RT: Role transition work is not done NET (PID:18180): The Time Management Interface (TMI) is being enabled for role transition NET (PID:18180): information. This will result in messages beingoutput to the alert log NET (PID:18180): file with the prefix 'TMI: '. This is being enabled to make the timing of NET (PID:18180): the various stages of the role transition available for diagnostic purposes. NET (PID:18180): This output will end when the role transition is complete. NET (PID:18180): Redo network throttle feature is disabled at mount time 2023-05-22T17:53:32.567967+05:30 NET (PID:18180): Database role cleared from PHYSICAL STANDBY [kcvs.c:1114] Switchover: Complete - Database mounted as primary TMI: kcv_commit_to_so_to_primary Switchover from physical END 2023-05-22 17:53:32.567967 TMI: dbsdrv failover to target END 2023-05-22 17:53:32.567967 Failover completed with No-Data-Loss. Completed: alter database failover to DR 2023-05-22T17:54:02.352171+05:30 2023-05-22T17:58:24.015551+05:30 alter database open 2023-05-22T17:58:24.015551+05:30 TMI: adbdrv open database BEGIN 2023-05-22 17:58:24.015551 Smart fusion block transfer is disabled: instance mounted in exclusive mode. Endian type of dictionary set to little 2023-05-22T17:58:24.110772+05:30 Assigning activation ID 2832039740 (0xa8cd7f3c) 2023-05-22T17:58:24.142819+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\STANDBY\DB\DR\REDO02.LOG Successful open of redo thread 1 2023-05-22T17:58:24.178583+05:30 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set Stopping change tracking 2023-05-22T17:58:24.186262+05:30 ARC1 (PID:6100): LGWR is scheduled to archive to LAD:3 after log switch 2023-05-22T17:58:24.340046+05:30 Undo initialization recovery: Parallel FPTR failed: start:72736812 end:72736812 diff:0 ms (0.0 seconds) Undo initialization recovery: err:0 start: 72736812 end: 72736843 diff: 31 ms (0.0 seconds) 2023-05-22T17:58:24.392500+05:30 ALTER SYSTEM SET log_archive_dest_state_3='ENABLE' SCOPE=MEMORY SID='*'; [18180] Successfully onlined Undo Tablespace 2. Undo initialization online undo segments: err:0 start: 72736843 end: 72736890 diff: 47 ms (0.0 seconds) 2023-05-22T17:58:24.405314+05:30 ARC1 (PID:6100): Archived Log entry 52 added for T-1.S-1 ID 0xa8cd7f3c LAD:1 2023-05-22T17:58:24.411083+05:30 Undo initialization finished serial:0 start:72736812 end:72736890 diff:78 ms (0.1 seconds) Dictionary check beginning Dictionary check complete Database Characterset is AL32UTF8 2023-05-22T17:58:24.447158+05:30 Starting background process SMCO 2023-05-22T17:58:24.447401+05:30 SMCO started with pid=39, OS id=6124 No Resource Manager plan active NET (PID:18180): Enable RFS client [kcrlc.c:622] 2023-05-22T17:58:25.061081+05:30 joxcsys_required_dirobj_exists: directory object exists with required path D:\RUPESH\SETUPS\WINDOWS.X64_193000_DB_HOME\JAVAVM\ADMIN\, pid 6880 cid 0 replication_dependency_tracking turned off (no async multimaster replication found) LOGSTDBY: Validating controlfile with logical metadata LOGSTDBY: Validation complete Starting background process AQPC 2023-05-22T17:58:25.359229+05:30 AQPC started with pid=42, OS id=17152 2023-05-22T17:58:25.559203+05:30 TMI: adbdrv open database END 2023-05-22 17:58:25.553533 Starting background process CJQ0 2023-05-22T17:58:25.594442+05:30 CJQ0 started with pid=45, OS id=18396 2023-05-22T17:58:26.595832+05:30 Completed: alter database open 2023-05-22T17:58:26.602169+05:30 You have successfully performed zero data loss recovery. Note: Now your current Primary database will be located on DR server. You will have question in your mind that what about my primary database server which was terminated. Once the issue is resolved at Primary site then you can perform below steps to again go live the Primary DB server. 1) Drop Primary database located at Primary site. 2) Take backup of newly activated Primary database(current PR) to configure the DR. 3) Restore the backup on Primary Site. 4) Configure the DR database. 5) Enable SYNC between new Primary and new DR. 6) Initiate switchover from new Primary to new DR 7) Go live Primary Site 8) Application sanity checks. |
Thanks for reading this post ! Please comment if you like this post ! Click on FOLLOW to get next blog updates !
I want steps to make DC & DR in sync? Is this scenario is already covered in this blog?
ReplyDeleteWill post similar post soon.
Delete