Never stop learning ...!!!
Greetings! Welcome to my Oracle DBA blog.
Disclaimer
▼
▼
Wednesday, 18 August 2021
Switch over and switch back database using DATAGUARD broker
Switch over and switch back database using DATAGUARD broker ========================================== Check the Database Is Ready for a Role Change It will checks whether it is ready for switchover.
DGMGRL> VALIDATE DATABASE 'ORCL';
DGMGRL> VALIDATE DATABASE 'ORCL';
Database Role: Primary database Ready for Switchover: Yes Flashback Database Status: orcl: Off Managed by Clusterware: orcl: NO Validating static connect identifier for database orcl... Unable to connect to database using (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac8.samik.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL_DGMGRL)(INSTANCE_NAME=ORCL)(SERVER=DEDICATED)(STATIC_SERVICE=TRUE))) ORA-12514: TNS:listener does not currently know of service requested in connect descriptor Failed. Warning: Ensure primary database's StaticConnectIdentifier property is configured properly so that the primary database can be restarted by DGMGRL after switchover --------- Oracle Data Guard Broker and Static Service Registration (Doc ID 1387859.1) Note: Static "_DGMGRL" entries are no longer needed as of Oracle Database 12.1.0.2 in Oracle Data Guard Broker configurations that are managed by Oracle Restart, RAC On Node or RAC as the Broker will use the clusterware to restart an instance. Solution:- edit database ORCL set property StaticConnectIdentifier='ORCL';
=================== Connect to the Primary Instance : using dgmgrl utility
Before Switchover status (Primary):
SQL> select status,instance_name,database_role from v$database,v$instance; STATUS INSTANCE_NAME DATABASE_ROLE ------------ ---------------- ---------------- OPEN ORCL PRIMARY
Before Switchover status (Standby):
SQL> select status,instance_name,database_role from v$database,v$instance; STATUS INSTANCE_NAME DATABASE_ROLE ------------ ---------------- ---------------- MOUNTED ORCLDG PHYSICAL STANDBY
[oracle@rac8 ~]$ dgmgrl sys/sys#123@ORCL DGMGRL for Linux: Release 18.0.0.0.0 - Production on Wed Aug 18 01:26:44 2021 Version 18.3.0.0.0 Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved. Welcome to DGMGRL, type "help" for information. Connected to "ORCL" Connected as SYSDBA. DGMGRL> DGMGRL> DGMGRL> switchover to ORCLDG; Performing switchover NOW, please wait... Operation requires a connection to database "orcldg" Connecting ... Connected to "ORCLDG" Connected as SYSDBA. New primary database "orcldg" is opening... Operation requires start up of instance "ORCL" on database "orcl" Starting instance "ORCL"... Unable to connect to database using ORCL ORA-12505: TNS:listener does not currently know of SID given in connect descriptor Failed. Warning: You are no longer connected to ORACLE. Please complete the following steps to finish switchover: start up and mount instance "ORCL" of database "orcl" DGMGRL> DGMGRL> DGMGRL> DGMGRL> exit
-------Check the database status at Standby with become primary now-------
STATUS INSTANCE_NAME DATABASE_ROLE ------------ ---------------- ---------------- OPEN ORCLDG PRIMARY [oracle@rac8 ~]$ sqlplus '/as sysdba' SQL*Plus: Release 18.0.0.0.0 - Production on Wed Aug 18 02:36:03 2021 Version 18.3.0.0.0 Copyright (c) 1982, 2018, Oracle. All rights reserved. Connected to an idle instance.
SQL> startup nomount; ORACLE instance started. Total System Global Area 1174403152 bytes Fixed Size 8656976 bytes Variable Size 436207616 bytes Database Buffers 721420288 bytes Redo Buffers 8118272 bytes SQL> SQL> SQL> alter database mount standby database; Database altered. ---Primary 07 -- SQL> alter system switch logfile; SQL> alter system switch logfile; SQL> alter system switch logfile; SQL> alter system switch logfile; SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination +DATA/orcldg Oldest online log sequence 353 Next log sequence to archive 355 Current log sequence 355 ---Standby 08 --Automatically enable MRP by broker-
SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;
Thread Last Sequence Received Last Sequence Applied Difference ---------- ---------------------- --------------------- ---------- 1 354 354 0 [oracle@rac7 ~]$ dgmgrl DGMGRL for Linux: Release 18.0.0.0.0 - Production on Wed Aug 18 02:41:13 2021 Version 18.3.0.0.0 Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved. Welcome to DGMGRL, type "help" for information. DGMGRL> DGMGRL> DGMGRL> connect sys/sys#123@ORCLDG Connected to "ORCLDG" Connected as SYSDBA. DGMGRL> DGMGRL> DGMGRL> DGMGRL> show configuration; Configuration - my_dg_config Protection Mode: MaxPerformance Members: orcldg - Primary database orcl - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS (status updated 13 seconds ago) DGMGRL>
--Run the command newly become Primary---Switchback from rac7 to rac8
DGMGRL> DGMGRL> switchover to ORCL; Performing switchover NOW, please wait... Operation requires a connection to database "orcl" Connecting ... Connected to "ORCL" Connected as SYSDBA. New primary database "orcl" is opening... Oracle Clusterware is restarting database "orcldg" ... Connected to "ORCLDG" Connected to "ORCLDG" Switchover succeeded, new primary is "orcl" DGMGRL>
Note:- We could see that DB was in mount state but when we switchover from rac8 to rac7, DB got down.
[oracle@rac7 ~]$ ps -ef | grep smon grid 24624 1 0 Aug17 ? 00:00:00 asm_smon_+ASM oracle 82039 1 0 02:44 ? 00:00:00 ora_smon_ORCLDG oracle 82462 28479 0 02:46 pts/1 00:00:00 grep --color=auto smon [oracle@rac7 ~]$ [oracle@rac7 ~]$ [oracle@rac7 ~]$ sqlplus '/as sysdba' SQL*Plus: Release 18.0.0.0.0 - Production on Wed Aug 18 02:46:16 2021 Version 18.3.0.0.0 Copyright (c) 1982, 2018, Oracle. All rights reserved. Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.3.0.0.0
SQL> select status from v$instance; STATUS ------------ MOUNTED [oracle@rac8 ~]$ sqlplus '/as sysdba' SQL*Plus: Release 18.0.0.0.0 - Production on Wed Aug 18 02:48:58 2021 Version 18.3.0.0.0 Copyright (c) 1982, 2018, Oracle. All rights reserved. Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.3.0.0.0
SQL> select status,instance_name,database_role from v$database,v$instance; STATUS INSTANCE_NAME DATABASE_ROLE ------------ ---------------- ---------------- OPEN ORCL PRIMARY
No comments:
Post a Comment