- Introduction: In Oracle, a switchover is a role transition operation between the primary and standby databases in a Data Guard environment. A switchover allows you to switch roles between the primary and standby databases in a planned manner, without data loss. This is typically done for maintenance, testing, or load balancing. Here we will do switchover activity using DG broker step-by-step.
- Prerequisites: o Downtime required at Database end for 30 mins.
- Environment:
- Now start Switchover activity step by step:
o Application and Database connectivity for Standby site.
o Primary and Standby databases should be in sync.
o DG Broker should be configured between Primary and Standby.
| Server | Primary | Standby |
|---|---|---|
| Hostname | Source | Target |
| IP | 192.168.80.51 | 192.168.80.111 |
| OS | OEL 9 | OEL 9 |
| SID | ORCLDC | ORCLDR |
| Service Name | ORCLDC | ORCLDR |
At Primary:
SQL> select NAME,DB_UNIQUE_NAME,DATABASE_ROLE,OPEN_MODE,SWITCHOVER_STATUS from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE SWITCHOVER_STATUS
--------- ------------------------------ ---------------- ------------------ --------------------
ORCL ORCLDC PRIMARY READ WRITE TO STANDBY
SQL> set lines 200 pages 300
SQL> alter session set nls_date_format= 'DD-MON-YYYY HH24:MI:SS';
Session altered.
SQL> select d.db_unique_name, d.database_role, a.thread#, b.last_seq, a.applied_seq, a.last_app_timestamp, b.last_seq - a.applied_seq ARC_DIFF
FROM
(select thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp from gv$archived_log where applied='YES' group by thread#) a,
(select thread#, MAX(sequence#) last_seq from gv$archived_log group by thread#) b,
(select db_unique_name, database_role from v$database) d
where a.thread#=b.thread#;
DB_UNIQUE_NAME DATABASE_ROLE THREAD# LAST_SEQ APPLIED_SEQ LAST_APP_TIMESTAMP ARC_DIFF
---------------------- ------------------ ------- -------- ----------- ---------------------- --------
ORCLDC PRIMARY 1 62 62 24-JAN-2025 10:27:01 0
SQL> select NAME,DB_UNIQUE_NAME,DATABASE_ROLE,OPEN_MODE,SWITCHOVER_STATUS from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE SWITCHOVER_STATUS
--------- ------------------------------ ------------------- ---------------------- --------------------
ORCL ORCLDR PHYSICAL STANDBY READ ONLY WITH APPLY NOT ALLOWED
SQL> set lines 200 pages 300
SQL> alter session set nls_date_format= 'DD-MON-YYYY HH24:MI:SS';
Session altered.
SQL> select d.db_unique_name, d.database_role, a.thread#, b.last_seq, a.applied_seq, b.last_seq - a.applied_seq ARC_DIFF, a.last_app_timestamp,
round((sysdate - a.last_app_timestamp) * 24 * 60, 2) Gap_in_Mins, round((sysdate - a.last_app_timestamp) * 24 * 60 * 60, 2) Gap_in_Seconds
FROM
(select thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp from gv$archived_log where REGISTRAR='RFS' and applied='YES' group by thread#) a,
(select thread#, MAX(sequence#) last_seq from gv$archived_log group by thread#) b,
(select db_unique_name, database_role from v$database) d
where a.thread#=b.thread#;
DB_UNIQUE_NAME DATABASE_ROLE THREAD# LAST_SEQ APPLIED_SEQ ARC_DIFF LAST_APP_TIMESTAMP GAP_IN_MINS GAP_IN_SECONDS
---------------------- ------------------ ------- -------- ----------- -------- ---------------------- ----------- --------------
ORCLDR PHYSICAL STANDBY 1 62 62 0 24-JAN-2025 10:27:01 12.85 771
SQL> alter database switchover to orcldr verify;
Database altered.
If you receive any warning message like below then check DB alert log and take action accordingly.
SQL> alter database switchover to orcldr verify;
alter database switchover to orcldr verify
*
ERROR at line 1:
ORA-16475: succeeded with warnings, check alert log for more details
[oracle@source ~]$ dgmgrl sys@orcldc
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Fri Jan 24 10:40:44 2025
Version 19.24.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Password:
Connected to "ORCLDC"
Connected as SYSDBA.
DGMGRL>
DGMGRL> switchover to orcldr
Performing switchover NOW, please wait...
Operation requires a connection to database "orcldr"
Connecting ...
Connected to "ORCLDR"
Connected as SYSDBA.
New primary database "orcldr" is opening...
Operation requires start up of instance "orcldc" on database "orcldc"
Starting instance "orcldc"...
Unable to connect to database using (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=source)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCLDC_DGMGRL)(INSTANCE_NAME=orcldc)(SERVER=DEDICATED)))
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Failed.
[W000 2025-01-24T10:42:16.151+05:30] Failed to attach to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=source)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCLDC_DGMGRL)(INSTANCE_NAME=orcldc)(SERVER=DEDICATED))).
Please complete the following steps to finish switchover:
start up instance "orcldc" of database "orcldc"
DGMGRL> exit
At new Primary:
[oracle@target ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jan 24 10:43:15 2025
Version 19.24.0.0.0
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.24.0.0.0
SQL>
SQL> set lines 200 pages 1000
col open_mode for a15
col HOST_NAME for a15
select NAME,DATABASE_ROLE,OPEN_MODE,INSTANCE_NUMBER,INSTANCE_NAME,HOST_NAME,VERSION,STARTUP_TIME,STATUS from gv$database,gv$instance;
SQL>
NAME DATABASE_ROLE OPEN_MODE INSTANCE_NUMBER INSTANCE_NAME HOST_NAME VERSION STARTUP_T STATUS
--------- ---------------- --------------- --------------- ---------------- --------------- ----------------- --------- ------------
ORCL PRIMARY READ WRITE 1 orcldr target 19.0.0.0.0 24-JAN-25 OPEN
SQL> show parameter dest_2
NAME TYPE VALUE
------------------------------------ ----------- ---------------------------------------------------------
db_create_online_log_dest_2 string
log_archive_dest_2 string service="orcldc", ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 reopen=300 db_unique_name="orcldc" net_timeout=30, valid_for=(online_logfile,all_roles)
log_archive_dest_20 string
log_archive_dest_21 string
log_archive_dest_22 string
log_archive_dest_23 string
log_archive_dest_24 string
log_archive_dest_25 string
log_archive_dest_26 string
log_archive_dest_27 string
log_archive_dest_28 string
log_archive_dest_29 string
SQL>
[oracle@source ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jan 24 10:43:00 2025
Version 19.24.0.0.0
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to an idle instance.
SQL>
SQL> startup mount
ORACLE instance started.
Total System Global Area 1459616616 bytes
Fixed Size 9177960 bytes
Variable Size 1174405120 bytes
Database Buffers 268435456 bytes
Redo Buffers 7598080 bytes
Database mounted.
SQL> set lines 200 pages 1000
SQL> col open_mode for a15
SQL> col HOST_NAME for a15
SQL> select NAME,DATABASE_ROLE,OPEN_MODE,INSTANCE_NUMBER,INSTANCE_NAME,HOST_NAME,VERSION,STARTUP_TIME,STATUS from gv$database,gv$instance;
SQL>
NAME DATABASE_ROLE OPEN_MODE INSTANCE_NUMBER INSTANCE_NAME HOST_NAME VERSION STARTUP_T STATUS
--------- ---------------- --------------- --------------- ---------------- --------------- ----------------- --------- ------------
ORCL PHYSICAL STANDBY MOUNTED 1 orcldc source 19.0.0.0.0 24-JAN-25 MOUNTED
SQL> show parameter dest_2
NAME TYPE VALUE
------------------------------------ ----------- ---------------------------------------------------------
db_create_online_log_dest_2 string
log_archive_dest_2 string
log_archive_dest_20 string
log_archive_dest_21 string
log_archive_dest_22 string
log_archive_dest_23 string
log_archive_dest_24 string
log_archive_dest_25 string
log_archive_dest_26 string
log_archive_dest_27 string
log_archive_dest_28 string
log_archive_dest_29 string
SQL>
5. Now do some log switches on new Primary and check the sync.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
Thanks for visiting!!
0 comments:
Post a Comment