Standby Database : DR
Step 1: Ensure below connectivity is fine before performing DR-Drill.
- DC-DR Sync.
- Network Delay
- tnsping connectivity
- sqlplus connectivity
- PORT is working fine
- Any critical ORA-error in the alert logfile.
Primary: SQL>
set lines 300 pages 3000
SQL>
select
name,db_unique_name,database_role,open_mode,controlfile_type,switchover_status
from v$database;
NAME
DB_UNIQUE_NAME DATABASE_ROLE
OPEN_MODE CONTROL SWITCHOVER_STATUS
----
--------------- ------------- ------------ ------- -----------------
DC DC PRIMARY READ WRITE CURRENT SESSIONS ACTIVE
SQL>
select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
26
SQL>
col dest_name for a23
SQL>
SELECT DEST_ID, DEST_NAME, STATUS, ERROR FROM
V$ARCHIVE_DEST;
DEST_ID
DEST_NAME STATUS ERROR
-------
---------------- --------- ------
1 LOG_ARCHIVE_DEST_1 VALID
2 LOG_ARCHIVE_DEST_2 VALID
3 LOG_ARCHIVE_DEST_3 INACTIVE
4 LOG_ARCHIVE_DEST_4 INACTIVE
5 LOG_ARCHIVE_DEST_5 INACTIVE
6 LOG_ARCHIVE_DEST_6 INACTIVE
7 LOG_ARCHIVE_DEST_7 INACTIVE
8 LOG_ARCHIVE_DEST_8 INACTIVE
9 LOG_ARCHIVE_DEST_9 INACTIVE
10 LOG_ARCHIVE_DEST_10 INACTIVE
11 LOG_ARCHIVE_DEST_11 INACTIVE
12 LOG_ARCHIVE_DEST_12 INACTIVE
13 LOG_ARCHIVE_DEST_13 INACTIVE
14 LOG_ARCHIVE_DEST_14 INACTIVE
15 LOG_ARCHIVE_DEST_15 INACTIVE
16 LOG_ARCHIVE_DEST_16 INACTIVE
17 LOG_ARCHIVE_DEST_17 INACTIVE
18 LOG_ARCHIVE_DEST_18 INACTIVE
19 LOG_ARCHIVE_DEST_19 INACTIVE
20 LOG_ARCHIVE_DEST_20 INACTIVE
21 LOG_ARCHIVE_DEST_21 INACTIVE
22 LOG_ARCHIVE_DEST_22 INACTIVE
23 LOG_ARCHIVE_DEST_23 INACTIVE
24 LOG_ARCHIVE_DEST_24 INACTIVE
25 LOG_ARCHIVE_DEST_25 INACTIVE
26 LOG_ARCHIVE_DEST_26 INACTIVE
27 LOG_ARCHIVE_DEST_27 INACTIVE
28 LOG_ARCHIVE_DEST_28 INACTIVE
29 LOG_ARCHIVE_DEST_29 INACTIVE
30 LOG_ARCHIVE_DEST_30 INACTIVE
31 LOG_ARCHIVE_DEST_31 INACTIVE 31
rows selected.
|
Standby : SQL> set lines 300 pages 3000 SQL> select name,db_unique_name,database_role,open_mode,controlfile_type,switchover_status from v$database; NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE CONTROL SWITCHOVER_STATUS ---- -------------- ---------------- --------- ---------- ------------------ DC DR PHYSICAL STANDBY MOUNTED STANDBY NOT ALLOWED SQL> select max(sequence#) from v$archived_log where applied='YES'; MAX(SEQUENCE#) -------------- 26 SQL> col dest_name for a23 SQL> SELECT DEST_ID, DEST_NAME, STATUS, ERROR FROM V$ARCHIVE_DEST; DEST_ID DEST_NAME STATUS ERROR ------- ---------------- --------- ---------- 1 LOG_ARCHIVE_DEST_1 VALID 2 LOG_ARCHIVE_DEST_2 VALID 3 LOG_ARCHIVE_DEST_3 INACTIVE 4 LOG_ARCHIVE_DEST_4 INACTIVE 5 LOG_ARCHIVE_DEST_5 INACTIVE 6 LOG_ARCHIVE_DEST_6 INACTIVE 7 LOG_ARCHIVE_DEST_7 INACTIVE 8 LOG_ARCHIVE_DEST_8 INACTIVE 9 LOG_ARCHIVE_DEST_9 INACTIVE 10 LOG_ARCHIVE_DEST_10 INACTIVE 11 LOG_ARCHIVE_DEST_11 INACTIVE 12 LOG_ARCHIVE_DEST_12 INACTIVE 13 LOG_ARCHIVE_DEST_13 INACTIVE 14 LOG_ARCHIVE_DEST_14 INACTIVE 15 LOG_ARCHIVE_DEST_15 INACTIVE 16 LOG_ARCHIVE_DEST_16 INACTIVE 17 LOG_ARCHIVE_DEST_17 INACTIVE 18 LOG_ARCHIVE_DEST_18 INACTIVE 19 LOG_ARCHIVE_DEST_19 INACTIVE 20 LOG_ARCHIVE_DEST_20 INACTIVE 21 LOG_ARCHIVE_DEST_21 INACTIVE 22 LOG_ARCHIVE_DEST_22 INACTIVE 23 LOG_ARCHIVE_DEST_23 INACTIVE 24 LOG_ARCHIVE_DEST_24 INACTIVE 25 LOG_ARCHIVE_DEST_25 INACTIVE 26 LOG_ARCHIVE_DEST_26 INACTIVE 27 LOG_ARCHIVE_DEST_27 INACTIVE 28 LOG_ARCHIVE_DEST_28 INACTIVE 29 LOG_ARCHIVE_DEST_29 INACTIVE 30 LOG_ARCHIVE_DEST_30 INACTIVE 31 LOG_ARCHIVE_DEST_31 INACTIVE 32 STANDBY_ARCHIVE_DEST VALID 32 rows selected. |
Step 3: Confirm with an Application Developer whether he has stopped all Application Services connecting to the respective database. Also check any remote connection to the database from non-oracle users.
SQL> select username,status,event from v$session where username is not null group by username,status,event order by 1;
Step 4: Now initiate switchover activity.
Switchover Initiation
Primary Database: SQL> alter database commit to switchover to standby with session shutdown nowait; Database altered. SQL> shutdown immediate; ORA-01012: not logged on SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options -bash-4.1$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Fri Jun 23 16:33:24 2017 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. SQL> startup nomount; SQL> alter database mount standby database; Database altered. SQL> alter database recover managed standby database disconnect from session; Database altered. SQL> @rd NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE CONTROL SWITCHOVER_STATUS ---- ---------------- ----------------- ---------- --------- -------------- DC DC PHYSICAL STANDBY MOUNTED STANDBY TO PRIMARY MAX(SEQUENCE#) -------------- 27 Primary Database Alert Logfile: SQL> alter database commit to switchover to standby with session shutdown nowait; Fri Jun 23 16:32:45 2017 alter database commit to switchover to standby with session shutdown nowait ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY [Process Id: 5304] (DC) Switchover End-Of-Redo Log thread 1 sequence 27 has been fixed Switchover: Primary highest seen SCN set to 0x0.0xf5685 ARCH: Noswitch archival of thread 1, sequence 27 ARCH: End-Of-Redo Branch archival of thread 1 sequence 27 ARCH: LGWR is actively archiving destination LOG_ARCHIVE_DEST_2 Archived Log entry 38 added for thread 1 sequence 27 ID 0x6306e1d7 dest 1: ARCH: Archiving is disabled due to current logfile archival Primary will check for some target standby to have received alls redo Final check for a synchronized target standby. Check will be made once. LOG_ARCHIVE_DEST_2 is a potential Physical Standby switchover target Active, synchronized target has been identified Target has also received all redo Backup controlfile written to trace file /home/oracle/u01/diag/rdbms/dc/DC/trace/DC_ora_5304.trc Clearing standby activation ID 1661395415 (0x6306e1d7) 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 52428800; ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 52428800; ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 52428800; ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 52428800; Archivelog for thread 1 sequence 27 required for standby recovery Switchover: Primary controlfile converted to standby controlfile succesfully. Switchover: Complete - Database shutdown required USER (ospid: 5304): terminating the instance Instance terminated by USER, pid = 5304 Completed: alter database commit to switchover to standby with session shutdown nowait Shutting down instance (abort) |
Standby Database: SQL> @rd NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE CONTROL SWITCHOVER_STATUS ---- ---------------- ----------------- ---------- --------- -------------- DC DR PHYSICAL STANDBY MOUNTED STANDBY TO PRIMARY MAX(SEQUENCE#) -------------- 27 SQL> alter database recover managed standby database cancel; Database altered. SQL> alter database commit to switchover to primary with session shutdown nowait; Database altered. SQL> shut immediate; ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL> startup; SQL> @rd NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE CONTROL SWITCHOVER_STATUS ---- ---------------- ----------------- ---------- --------- -------------- DC DR PRIMARY READ WRITE CURRENT RESOLVABLE GAP MAX(SEQUENCE#) -------------- 27 Standby Database Alert Logfile: SQL> alter database commit to switchover to primary with session shutdown nowait; Fri Jun 23 16:38:23 2017 alter database commit to switchover to primary with session shutdown nowait ALTER DATABASE SWITCHOVER TO PRIMARY (DC) No wait specified for role transition. Backup controlfile written to trace file /home/oracle/u01/diag/rdbms/dr/DC/trace/DC_ora_5329.trc SwitchOver after complete recovery through change 1005189 Online logfile pre-clearing operation disabled by switchover Online log /home/oracle/u01/fast_recovery_area/DR/onlinelog/o1_mf_1_dnq8nzon_.log: Thread 1 Group 1 was previously cleared Online log /home/oracle/u01/fast_recovery_area/DR/onlinelog/o1_mf_2_dnq8o01m_.log: Thread 1 Group 2 was previously cleared Online log /home/oracle/u01/fast_recovery_area/DR/onlinelog/o1_mf_3_dnq8o0dc_.log: Thread 1 Group 3 was previously cleared Standby became primary SCN: 1005187 Switchover: Complete - Database mounted as primary Completed: alter database commit to switchover to primary with session shutdown nowait Fri Jun 23 16:38:29 2017 |
At this stage, we can say that switchover activity is completed successfully and now Primary database is new standby and standby database is new Primary.
New Primary: DR
New Standby : DC
Step 5: Verify everything is running fine without any issue. Also switch 3-4 logs from newly created primary database to standby database. Check with an Application Developer whether they are facing any issue while connecting Application and any other issue with newly converted primary database.
Note: Directly do not initiate switchback activity. First check with an Application Developer regarding this. Once he confirms then you can proceed. They can run their application for few days or few weeks.
Switchback Initiation
New Primary Database(DR): SQL> set lines 300 pages 3000 SQL> select name,db_unique_name,database_role,open_mode,controlfile_type,switchover_status from v$database; NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE CONTROL SWITCHOVER_STATUS ---- -------------- ------------- ----------- ------- ----------------- DC DR PRIMARY READ WRITE CURRENT TO STANDBY MAX(SEQUENCE#) -------------- 37 SQL> alter database commit to switchover to standby with session shutdown nowait; Database altered. SQL> shut immediate; ORA-01012: not logged on SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options -bash-4.1$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Fri Jun 23 17:17:18 2017 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. SQL> startup nomount; SQL> alter database mount standby database; Database altered. SQL> alter database recover managed standby database disconnect from session; Database altered. SQL> @rd NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE CONTROL SWITCHOVER_STATUS ---- -------------- ----------------- ------- ------- ----------------- DC DR PHYSICAL STANDBY READ WRITE STANDBY TO PRIMARY MAX(SEQUENCE#) -------------- 38 SQL> alter database commit to switchover to standby with session shutdown nowait; Fri Jun 23 17:16:27 2017 alter database commit to switchover to standby with session shutdown nowait ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY [Process Id: 5953] (DC) Switchover End-Of-Redo Log thread 1 sequence 38 has been fixed Switchover: Primary highest seen SCN set to 0x0.0xfb01c ARCH: Noswitch archival of thread 1, sequence 38 ARCH: End-Of-Redo Branch archival of thread 1 sequence 38 ARCH: LGWR is actively archiving destination LOG_ARCHIVE_DEST_2 Archived Log entry 35 added for thread 1 sequence 38 ID 0x630891dc dest 1: ARCH: Archiving is disabled due to current logfile archival Primary will check for some target standby to have received alls redo Final check for a synchronized target standby. Check will be made once. LOG_ARCHIVE_DEST_2 is a potential Physical Standby switchover target Active, synchronized target has been identified Target has also received all redo Backup controlfile written to trace file /home/oracle/u01/diag/rdbms/dr/DC/trace/DC_ora_5953.trc Clearing standby activation ID 1661506012 (0x630891dc) 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 52428800; ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 52428800; ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 52428800; ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 52428800; Archivelog for thread 1 sequence 38 required for standby recovery Switchover: Primary controlfile converted to standby controlfile succesfully. Switchover: Complete - Database shutdown required USER (ospid: 5953): terminating the instance Instance terminated by USER, pid = 5953 Completed: alter database commit to switchover to standby with session shutdown nowait Shutting down instance (abort) License high water mark = 3 |
New Standby Database(DR): SQL> @rd NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE CONTROL SWITCHOVER_STATUS ---- --------------- ---------------- ---------- -------- ----------------- DC DC PHYSICAL STANDBY MOUNTED STANDBY TO PRIMARY MAX(SEQUENCE#) -------------- 38 SQL> alter database recover managed standby database cancel; Database altered. SQL> alter database commit to switchover to primary with session shutdown nowait; Database altered. SQL> shut immediate; ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL> startup; SQL> @rd NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE CONTROL SWITCHOVER_STATUS ----- --------------- --------------- ----------- -------- ------------------ DC DC PRIMARY READ WRITE CURRENT RESOLVABLE GAP MAX(SEQUENCE#) -------------- 38 SQL> alter database commit to switchover to primary with session shutdown nowait; Fri Jun 23 17:20:16 2017 alter database commit to switchover to primary with session shutdown nowait ALTER DATABASE SWITCHOVER TO PRIMARY (DC) No wait specified for role transition. Backup controlfile written to trace file /home/oracle/u01/diag/rdbms/dc/DC/trace/DC_ora_5807.trc SwitchOver after complete recovery through change 1028124 Online logfile pre-clearing operation disabled by switchover Online log /home/oracle/datafile/DC/redo01.log: Thread 1 Group 1 was previously cleared Online log /home/oracle/datafile/DC/redo02.log: Thread 1 Group 2 was previously cleared Online log /home/oracle/datafile/DC/redo03.log: Thread 1 Group 3 was previously cleared Standby became primary SCN: 1028122 Switchover: Complete - Database mounted as primary Completed: alter database commit to switchover to primary with session shutdown nowait |
DR Drill has been completed without any issue.
Step 6: If everything is running fine then you can handover it to an Application Developer. Also check with him for any DB level issue.
Useful content especially for beginners
ReplyDeleteIt is very useful and excellent explanation with examples.
ReplyDeleteVery useful, Thanks for sharing....
ReplyDelete