Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In today’s article we will learn to Switchover Using DGMGRL.
We can also do Switchover from DGMGRL. Switchover from DGMGRL is easier. It automatically executes all the commands we run using SQL.
Those that need to be closed from instances close themselves. In summary, the processing steps are much less and easier than SQL.
In Oracle 12c, whether Primary and Standby Database is ready for Switchover can be done with validate commands from DGMGRL. But 11g does not have these commands, as in SQL, the controls must be done manually.
If the checks are made and the result is successful, the Switchover process from DGMGRL is as follows.
1. We query the status of the Broker configuration from DGMGRL.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | DGMGRL> show configuration Configuration - Broker_Configuraiton Protection Mode: MaxPerformance Databases: primary - Primary database standby - Physical standby database logical - Logical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS |
2.We check whether Primary and Standby Database are ready for Switchover with SQL commands.
3.We perform the Switchover operation from DGMGRL.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | DGMGRL> switchover to 'standby'; Performing switchover NOW, please wait... Operation requires a connection to instance "primary1" on database "standby" Connecting to instance "primary1"... Connected. New primary database "standby" is opening... Operation requires startup of instance "primary1" on database "primary" Starting instance "primary1"... ORA-01017: invalid username/password; logon denied Warning: You are no longer connected to ORACLE. Please complete the following steps to finish switchover: start up instance "primary1" of database "primary" |
The reason for getting this error is that a static listener must be defined so that the broker can STARTUP the database after SHUTDOWN. Since this definition could not be made exactly correctly, it could not start automatically.
4. We have a New Physical Standby Database startup, which cannot start automatically due to the Static Listener not being set correctly.
1 2 3 4 5 6 7 8 9 10 | [Primary-1] SQL> startup; ORACLE instance started. Total System Global Area 6480490496 bytes Fixed Size 2265384 bytes Variable Size 1241517784 bytes Database Buffers 5217714176 bytes Redo Buffers 18993152 bytes Database mounted. Database opened. |
5.We start the checks.
a. We query the status of instances.
1 2 3 4 5 6 | [Physical-1] SQL> select status from gv$instance; STATUS ------------ OPEN OPEN |
1 2 3 4 5 6 | [Primary-1] SQL> select status from gv$instance; STATUS ------------ OPEN OPEN |
1 2 3 4 5 6 | [Logical-1] SQL> select status from gv$instance; STATUS ------------ OPEN OPEN |
b. After the switchover, we question whether EskiPhysical Standby is Primary.
1 2 3 4 5 6 | [Physical-1] SQL> select open_mode, database_role, protection_mode from gv$database; OPEN_MODE DATABASE_ROLE PROTECTION_MODE -------------------- ---------------- -------------------- READ WRITE PRIMARY MAXIMUM PERFORMANCE READ WRITE PRIMARY MAXIMUM PERFORMANCE |
1 2 3 4 5 6 | [Primary-1] SQL> select open_mode, database_role, protection_mode from gv$database; OPEN_MODE DATABASE_ROLE PROTECTION_MODE -------------------- ---------------- -------------------- READ ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM PERFORMANCE READ ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM PERFORMANCE |
1 2 3 4 5 6 | [Logical-1] SQL> select open_mode, database_role, protection_mode from gv$database; OPEN_MODE DATABASE_ROLE PROTECTION_MODE -------------------- ---------------- -------------------- READ WRITE LOGICAL STANDBY MAXIMUM PERFORMANCE READ WRITE LOGICAL STANDBY MAXIMUM PERFORMANCE |
c. We query whether the Redo Apply processes have started.
1 2 3 4 5 | [Primary-1] SQL> select recovery_mode from v$archive_dest_status where dest_id < 2; RECOVERY_MODE ----------------------- MANAGED REAL TIME APPLY |
1 2 3 4 5 | [Logical-1] SQL> select recovery_mode from v$archive_dest_status where dest_id < 2; RECOVERY_MODE ----------------------- LOGICAL REAL TIME APPLY |
ç. We check if there is Redo Transport and Apply LAG in the Standby Databases.
1 2 3 4 5 6 7 8 9 10 11 12 | [Primary-1] SQL> set linesize 9000 [Primary-1] SQL> column name format a25 [Primary-1] SQL> column value format a20 [Primary-1] SQL> column time_computed format a25 [Primary-1] SQL> SELECT name, value, time_computed FROM v$dataguard_stats; NAME VALUE TIME_COMPUTED ------------------------- -------------------- ------------------------- transport lag +00 00:00:00 01/28/2017 12:37:09 apply lag +00 00:00:00 01/28/2017 12:37:09 apply finish time +00 00:00:00.000 01/28/2017 12:37:09 estimated startup time 23 01/28/2017 12:37:09ö |
1 2 3 4 5 6 7 8 9 10 11 12 | [Logical-1] SQL> set linesize 9000 [Logical-1] SQL> column name format a25 [Logical-1] SQL> column value format a20 [Logical-1] SQL> column time_computed format a25 [Logical-1] SQL> SELECT name, value, time_computed FROM v$dataguard_stats; NAME VALUE TIME_COMPUTED ------------------------- -------------------- ------------------------- transport lag +00 00:00:00 01/28/2017 11:54:45 apply lag +00 00:00:00 01/28/2017 11:54:45 apply finish time +00 00:00:00.000 01/28/2017 11:54:45 estimated startup time 22 01/28/2017 11:54:45 |
d.With this method, we also check whether the Log Switch operation is done properly and whether there are Redo and Transport LAGs afterwards.
1 2 3 4 5 6 | [Physical-1] SQL> select max(sequence#),thread# from v$archived_log where first_time > to_date('27/01/2017 13:41:50', 'DD-MM-YYYY HH24:MI:SS') group by thread#; MAX(SEQUENCE#) THREAD# -------------- ---------- 40 1 41 2 |
The reason for adding the first_time condition to the WHERE condition in the query is because the databases eat RESETLOGS due to previous tests and therefore SEQUENCE#’s are reset.
1 2 3 4 5 6 | [Primary-1] SQL> select max(sequence#),thread# from v$archived_log where first_time > to_date('27/01/2017 13:41:50', 'DD-MM-YYYY HH24:MI:SS') group by thread#; MAX(SEQUENCE#) THREAD# -------------- ---------- 40 1 41 2 |
1 2 3 4 5 6 | [Logical-1] SQL> SELECT max(sequence#),thread#,applied FROM dba_logstdby_log where first_time > to_date('27/01/2017 13:41:50', 'DD-MM-YYYY HH24:MI:SS') group by thread#, applied; MAX(SEQUENCE#) THREAD# APPLIED -------------- ---------- -------- 40 1 YES 41 2 YES |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | [Physical-1] SQL> alter system switch logfile; System altered. [Physical-1] SQL> alter system switch logfile; System altered. [Physical-1] SQL> select max(sequence#),thread# from v$archived_log where first_time > to_date('27/01/2017 13:41:50', 'DD-MM-YYYY HH24:MI:SS') group by thread#; MAX(SEQUENCE#) THREAD# -------------- ---------- 42 1 42 2 |
1 2 3 4 5 6 | [Primary-1] SQL> select max(sequence#),thread# from v$archived_log where first_time > to_date('27/01/2017 13:41:50', 'DD-MM-YYYY HH24:MI:SS') group by thread#; MAX(SEQUENCE#) THREAD# -------------- ---------- 42 1 42 2 |
1 2 3 4 5 6 | [Logical-1] SQL> SELECT max(sequence#),thread#,applied FROM dba_logstdby_log where first_time > to_date('27/01/2017 13:41:50', 'DD-MM-YYYY HH24:MI:SS') group by thread#, applied; MAX(SEQUENCE#) THREAD# APPLIED -------------- ---------- -------- 42 1 YES 42 2 YES |
