作者:dbtan |【转载时请以超链接形式标明文章出处作者信息】


Oracle 19c RAC + ADG 手动 switchover 角色转换步骤

Revision V2.0

No.DateAuthor/ModifierComments
1.02020-03-06谈权初稿:搭建 Oracle MAA: Oracle 19c RAC + Active Data Gurad
2.02020-03-10谈权增加:16. 手动 switchover 角色转换步骤

[TOC]

接上篇文章(搭建 Oracle MAA: Oracle 19c RAC + ADG), 本文继续完成 「16. 手动 switchover 角色转换步骤」。

16. 手动 switchover 角色转换步骤

You can use the same steps to switchover for single data guard or multiple data guard configuration.

Switchover operation will convert primary side to data guard and data guard to primary side.

Oracle 19c Data Guard Switchover Structure-1

You have to control listeners whether it is running before switchover steps.

Switchover Steps

-- 1. Primary Side
-- 此命令将提供有关数据保护当前状态的适当消息。
SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO STANDBY

SQL> alter system archive log current;

SQL> alter database commit to switchover to standby with session shutdown;

SQL> shutdown immediate;

SQL> startup mount;
-- 此命令将提供有关数据保护当前状态的适当消息。
sys@TQDB(tqdb21)> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO STANDBY

The switchover_status column of v$database can have the following values:

Not Allowed:-Either this is a standby database and the primary database has not been switched first, or this is a primary database and there are no standby databases
Session Active:- Indicates that there are active SQL sessions attached to the primary or standby database that need to be disconnected before the switchover operation is permitted
Switchover Pending:- This is a standby database and the primary database switchover request has been received but not processed.
Switchover Latent:- The switchover was in pending mode, but did not complete and went back to the primary database
To Primary:- This is a standby database, with no active sessions, that is allowed to switch over to a primary database
To Standby:- This is a primary database, with no active sessions, that is allowed to switch over to a standby database
Recovery Needed:- This is a standby database that has not received the switchover request

-- 2. Data Guard Side
-- 此命令将提供有关数据保护当前状态的适当消息。
SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO PRIMARY

SQL> alter database recover managed standby database cancel;

SQL> alter database commit to switchover to primary with session shutdown;

SQL> shutdown immediate;

SQL> startup;
-- 3. Primary Side
SQL> alter database recover managed standby database disconnect;

If you create standby log files you can use real time apply with below command.

SQL> alter database open read only;

SQL> alter database recover managed standby database using current logfile disconnect;
-- 在备库启动 recover 过程,应用主库传过来的日志(默认已经是real-time apply模式,因此省略using current logfile);
SQL> ALTER DATABASE RECOVER managed standby database disconnect from session;

操作记录:「RAC主库」与「备库」switchover, 切换后:「RAC」为 standby,「原备库」为 primary

说明: switchover 切换后:「备库」为 primary ,由于「备库」是单实例,所以切换日志alter system archive log current; 时,只会切换 THREAD#1 的日志组。(即:对应「RAC主库」节点1 的日志组(THREAD#1 ))。

-- 1. Primary Side
-- 此命令将提供有关数据保护当前状态的适当消息。
23:19:24 sys@TQDB(tqdb21)> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO STANDBY

23:19:25 sys@TQDB(tqdb21)> 

The switchover_status column of v$database can have the following values:

Not Allowed:-Either this is a standby database and the primary database has not been switched first, or this is a primary database and there are no standby databases
Session Active:- Indicates that there are active SQL sessions attached to the primary or standby database that need to be disconnected before the switchover operation is permitted
Switchover Pending:- This is a standby database and the primary database switchover request has been received but not processed.
Switchover Latent:- The switchover was in pending mode, but did not complete and went back to the primary database
To Primary:- This is a standby database, with no active sessions, that is allowed to switch over to a primary database
To Standby:- This is a primary database, with no active sessions, that is allowed to switch over to a standby database
Recovery Needed:- This is a standby database that has not received the switchover request

-- 
23:27:45 sys@TQDB(tqdb21)> alter system archive log current;

System altered.

23:27:52 sys@TQDB(tqdb21)> 

-- 「主库RAC」节点1
23:42:46 sys@TQDB(tqdb21)> alter database commit to switchover to standby with session shutdown;
ERROR:
ORA-01034: ORACLE not available
Process ID: 32074
Session ID: 452 Serial number: 21981



Database altered.

23:43:25 sys@TQDB(tqdb21)> conn / as sysdba
Connected to an idle instance.
23:44:36 idle(tqdb21)> 

-- 「主库RAC」节点2
23:45:09 sys@TQDB(tqdb22)> 
23:45:09 sys@TQDB(tqdb22)> conn / as sysdba
ERROR:
ORA-03113: end-of-file on communication channel
Process ID: 0
Session ID: 390 Serial number: 18811


Connected to an idle instance.
23:45:13 idle(tqdb22)> 

[root@tqdb21: ~]# crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr
               ONLINE  ONLINE       tqdb21                   STABLE
               ONLINE  ONLINE       tqdb22                   STABLE
ora.chad
               ONLINE  ONLINE       tqdb21                   STABLE
               ONLINE  ONLINE       tqdb22                   STABLE
ora.net1.network
               ONLINE  ONLINE       tqdb21                   STABLE
               ONLINE  ONLINE       tqdb22                   STABLE
ora.ons
               ONLINE  ONLINE       tqdb21                   STABLE
               ONLINE  ONLINE       tqdb22                   STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup)
      1        ONLINE  ONLINE       tqdb21                   STABLE
      2        ONLINE  ONLINE       tqdb22                   STABLE
      3        ONLINE  OFFLINE                               STABLE
ora.DATA.dg(ora.asmgroup)
      1        ONLINE  ONLINE       tqdb21                   STABLE
      2        ONLINE  ONLINE       tqdb22                   STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       tqdb21                   STABLE
ora.OCR.dg(ora.asmgroup)
      1        ONLINE  ONLINE       tqdb21                   STABLE
      2        ONLINE  ONLINE       tqdb22                   STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.asm(ora.asmgroup)
      1        ONLINE  ONLINE       tqdb21                   Started,STABLE
      2        ONLINE  ONLINE       tqdb22                   Started,STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.asmnet1.asmnetwork(ora.asmgroup)
      1        ONLINE  ONLINE       tqdb21                   STABLE
      2        ONLINE  ONLINE       tqdb22                   STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.cvu
      1        ONLINE  ONLINE       tqdb21                   STABLE
ora.qosmserver
      1        ONLINE  ONLINE       tqdb21                   STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       tqdb21                   STABLE
ora.tqdb.db
      1        OFFLINE OFFLINE                               Instance Shutdown,ST
                                                             ABLE
      2        OFFLINE OFFLINE                               Instance Shutdown,ST
                                                             ABLE
ora.tqdb21.vip
      1        ONLINE  ONLINE       tqdb21                   STABLE
ora.tqdb22.vip
      1        ONLINE  ONLINE       tqdb22                   STABLE
--------------------------------------------------------------------------------
[root@tqdb21: ~]# 

此时,「主库RAC」两个节点数据库实例都已经 `shutdown` 了。

-- 「主库RAC」节点1
23:48:51 idle(tqdb21)> startup mount;
ORACLE instance started.

Total System Global Area  822080768 bytes
Fixed Size                  8901888 bytes
Variable Size             390070272 bytes
Database Buffers          419430400 bytes
Redo Buffers                3678208 bytes
Database mounted.
23:49:06 idle(tqdb21)> 
23:50:06 idle(tqdb21)> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
RECOVERY NEEDED

23:50:08 idle(tqdb21)> 

-- 「主库RAC」节点2
23:54:45 idle(tqdb22)> startup mount;
ORACLE instance started.

Total System Global Area  822080768 bytes
Fixed Size                  8901888 bytes
Variable Size             360710144 bytes
Database Buffers          448790528 bytes
Redo Buffers                3678208 bytes
Database mounted.
23:56:01 idle(tqdb22)> conn / as sysdba
Connected.
23:56:22 idle(tqdb22)> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
RECOVERY NEEDED

23:56:40 idle(tqdb22)> 
-- 2. Data Guard Side
23:59:18 sys@TQDB(tq1)>  select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO PRIMARY

23:59:19 sys@TQDB(tq1)> 


00:00:10 sys@TQDB(tq1)> alter database recover managed standby database cancel;

Database altered.

00:00:44 sys@TQDB(tq1)>  

00:01:08 sys@TQDB(tq1)> 
00:01:32 sys@TQDB(tq1)> alter database commit to switchover to primary with session shutdown;

Database altered.

00:02:19 sys@TQDB(tq1)> 
00:02:43 sys@TQDB(tq1)> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
00:03:11 sys@TQDB(tq1)> 
00:05:50 sys@TQDB(tq1)> startup 
ORACLE instance started.

Total System Global Area 1191181696 bytes
Fixed Size                  8895872 bytes
Variable Size             318767104 bytes
Database Buffers          855638016 bytes
Redo Buffers                7880704 bytes
Database mounted.
Database opened.
00:06:08 sys@TQDB(tq1)> 

00:06:46 sys@TQDB(tq1)> -- 查询 Oracle ADG 保护模式
00:07:42 sys@TQDB(tq1)> select DATABASE_ROLE, open_mode, PROTECTION_MODE,PROTECTION_LEVEL from v$database;

DATABASE_ROLE    OPEN_MODE            PROTECTION_MODE      PROTECTION_LEVEL
---------------- -------------------- -------------------- --------------------
PRIMARY          READ WRITE           MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

00:07:43 sys@TQDB(tq1)> 
-- 3. Primary Side
-- 「主库RAC」节点1
00:10:41 idle(tqdb21)> alter database open;

Database altered.

00:10:51 idle(tqdb21)> -- 查看DG的基本统计信息 @standby    
00:11:43 idle(tqdb21)> set linesize 200;   
00:11:43 idle(tqdb21)> col name for a25;   
00:11:43 idle(tqdb21)> column value format a20;    
00:11:43 idle(tqdb21)> select * from v$dataguard_stats;    

SOURCE_DBID SOURCE_DB_UNIQUE_NAME            NAME                      VALUE                UNIT                           TIME_COMPUTED                  DATUM_TIME                         CON_ID
----------- -------------------------------- ------------------------- -------------------- ------------------------------ ------------------------------ ------------------------------ ----------
          0                                  transport lag             +00 00:00:00         day(2) to second(0) interval   03/11/2020 00:11:44            03/11/2020 00:11:43                     0
          0                                  apply lag                                      day(2) to second(0) interval   03/11/2020 00:11:44                                                    0
          0                                  apply finish time                              day(2) to second(3) interval   03/11/2020 00:11:44                                                    0
          0                                  estimated startup time    30                   second                         03/11/2020 00:11:44                                                    0

00:11:44 idle(tqdb21)>

-- 「主库RAC」节点2
23:56:40 idle(tqdb22)> alter database open;

Database altered.

00:15:29 idle(tqdb22)> conn / as sysdba
Connected.
00:15:49 sys@TQDB(tqdb22)>

-- 「主库RAC」节点1
00:10:51 idle(tqdb21)> conn / as sysdba
00:10:51 sys@TQDB(tqdb21)> -- 查看DG的基本统计信息 @standby    
00:11:43 sys@TQDB(tqdb21)> set linesize 200;   
00:11:43 sys@TQDB(tqdb21)> col name for a25;   
00:11:43 sys@TQDB(tqdb21)> column value format a20;    
00:11:43 sys@TQDB(tqdb21)> select * from v$dataguard_stats;    

SOURCE_DBID SOURCE_DB_UNIQUE_NAME            NAME                      VALUE                UNIT                           TIME_COMPUTED                  DATUM_TIME                         CON_ID
----------- -------------------------------- ------------------------- -------------------- ------------------------------ ------------------------------ ------------------------------ ----------
          0                                  transport lag             +00 00:00:00         day(2) to second(0) interval   03/11/2020 00:11:44            03/11/2020 00:11:43                     0
          0                                  apply lag                                      day(2) to second(0) interval   03/11/2020 00:11:44                                                    0
          0                                  apply finish time                              day(2) to second(3) interval   03/11/2020 00:11:44                                                    0
          0                                  estimated startup time    30                   second                         03/11/2020 00:11:44                                                    0

00:11:44 sys@TQDB(tqdb21)> alter database recover managed standby database disconnect from session;

Database altered.

00:16:28 sys@TQDB(tqdb21)> select * from v$dataguard_stats;    

SOURCE_DBID SOURCE_DB_UNIQUE_NAME            NAME                      VALUE                UNIT                           TIME_COMPUTED                  DATUM_TIME                         CON_ID
----------- -------------------------------- ------------------------- -------------------- ------------------------------ ------------------------------ ------------------------------ ----------
          0                                  transport lag             +00 00:00:00         day(2) to second(0) interval   03/11/2020 00:16:43            03/11/2020 00:16:42                     0
          0                                  apply lag                 +00 00:00:00         day(2) to second(0) interval   03/11/2020 00:16:43            03/11/2020 00:16:42                     0
          0                                  apply finish time                              day(2) to second(3) interval   03/11/2020 00:16:43                                                    0
          0                                  estimated startup time    30                   second                         03/11/2020 00:16:43                                                    0

00:16:43 sys@TQDB(tqdb21)> 

00:27:43 sys@TQDB(tqdb21)> -- 查询 Oracle ADG 保护模式
00:28:00 sys@TQDB(tqdb21)> select DATABASE_ROLE, open_mode, PROTECTION_MODE,PROTECTION_LEVEL from v$database;

DATABASE_ROLE    OPEN_MODE            PROTECTION_MODE      PROTECTION_LEVEL
---------------- -------------------- -------------------- --------------------
PHYSICAL STANDBY READ ONLY WITH APPLY MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

00:28:00 sys@TQDB(tqdb21)> 

[root@tqdb21: ~]# crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr
               ONLINE  ONLINE       tqdb21                   STABLE
               ONLINE  ONLINE       tqdb22                   STABLE
ora.chad
               ONLINE  ONLINE       tqdb21                   STABLE
               ONLINE  ONLINE       tqdb22                   STABLE
ora.net1.network
               ONLINE  ONLINE       tqdb21                   STABLE
               ONLINE  ONLINE       tqdb22                   STABLE
ora.ons
               ONLINE  ONLINE       tqdb21                   STABLE
               ONLINE  ONLINE       tqdb22                   STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup)
      1        ONLINE  ONLINE       tqdb21                   STABLE
      2        ONLINE  ONLINE       tqdb22                   STABLE
      3        ONLINE  OFFLINE                               STABLE
ora.DATA.dg(ora.asmgroup)
      1        ONLINE  ONLINE       tqdb21                   STABLE
      2        ONLINE  ONLINE       tqdb22                   STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       tqdb21                   STABLE
ora.OCR.dg(ora.asmgroup)
      1        ONLINE  ONLINE       tqdb21                   STABLE
      2        ONLINE  ONLINE       tqdb22                   STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.asm(ora.asmgroup)
      1        ONLINE  ONLINE       tqdb21                   Started,STABLE
      2        ONLINE  ONLINE       tqdb22                   Started,STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.asmnet1.asmnetwork(ora.asmgroup)
      1        ONLINE  ONLINE       tqdb21                   STABLE
      2        ONLINE  ONLINE       tqdb22                   STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.cvu
      1        ONLINE  ONLINE       tqdb21                   STABLE
ora.qosmserver
      1        ONLINE  ONLINE       tqdb21                   STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       tqdb21                   STABLE
ora.tqdb.db
      1        ONLINE  ONLINE       tqdb21                   Open,Readonly,HOME=/
                                                             u01/app/oracle/produ
                                                             ct/19c/dbhome,STABLE
      2        ONLINE  ONLINE       tqdb22                   Open,Readonly,HOME=/
                                                             u01/app/oracle/produ
                                                             ct/19c/dbhome,STABLE
ora.tqdb21.vip
      1        ONLINE  ONLINE       tqdb21                   STABLE
ora.tqdb22.vip
      1        ONLINE  ONLINE       tqdb22                   STABLE
--------------------------------------------------------------------------------
[root@tqdb21: ~]# 

-- 「主库RAC」节点2
00:15:29 idle(tqdb22)> conn / as sysdba
Connected.
00:15:49 sys@TQDB(tqdb22)> -- 查看DG的基本统计信息 @standby    
00:16:53 sys@TQDB(tqdb22)> set linesize 200;   
00:16:53 sys@TQDB(tqdb22)> col name for a25;   
00:16:53 sys@TQDB(tqdb22)> column value format a20;    
00:16:53 sys@TQDB(tqdb22)> select * from v$dataguard_stats;    

SOURCE_DBID SOURCE_DB_UNIQUE_NAME            NAME                      VALUE                UNIT                           TIME_COMPUTED                  DATUM_TIME                         CON_ID
----------- -------------------------------- ------------------------- -------------------- ------------------------------ ------------------------------ ------------------------------ ----------
          0                                  transport lag             +00 00:00:00         day(2) to second(0) interval   03/11/2020 00:16:53            03/11/2020 00:16:53                     0
          0                                  apply lag                 +00 00:00:00         day(2) to second(0) interval   03/11/2020 00:16:53            03/11/2020 00:16:53                     0
          0                                  apply finish time                              day(2) to second(3) interval   03/11/2020 00:16:53                                                    0
          0                                  estimated startup time    20                   second                         03/11/2020 00:16:53                                                    0

00:16:53 sys@TQDB(tqdb22)> 
00:28:51 sys@TQDB(tqdb22)> -- 查询 Oracle ADG 保护模式
00:28:51 sys@TQDB(tqdb22)> select DATABASE_ROLE, open_mode, PROTECTION_MODE,PROTECTION_LEVEL from v$database;

DATABASE_ROLE    OPEN_MODE            PROTECTION_MODE      PROTECTION_LEVEL
---------------- -------------------- -------------------- --------------------
PHYSICAL STANDBY READ ONLY WITH APPLY MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

00:28:52 sys@TQDB(tqdb22)> 

此时:「RAC」为 standby,「原备库」为 primary。 再次 switchover 回去,「RAC」为 primary,「备库」为 standby

操作记录:此时:「RAC」为 standby,「原备库」为 primary。 再次 switchover 回去,「RAC」为 primary,「备库」为 standby

-- 1. Primary Side
00:44:34 sys@TQDB(tq1)> alter system archive log current;

System altered.

00:44:37 sys@TQDB(tq1)> select * from v$log;

GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS          FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME               CON_ID
    ---------- ---------- ---------- ---------- ---------- ---------- --- --------------- ------------- ------------------- ------------ ------------------- ----------
     1          1         68  209715200        512          1 NO  CURRENT               4758229 2020-03-11 00:44:37   9.2954E+18                              0
         2          1         67  209715200        512          1 YES ACTIVE                4758149 2020-03-11 00:43:59      4758229 2020-03-11 00:44:37          0
         3          2         57  209715200        512          1 YES INACTIVE              4751656 2020-03-11 00:02:19      4751938 2020-03-11 00:06:07          0
         4          2          0  209715200        512          1 YES UNUSED                      0                                0                              0
    
00:44:40 sys@TQDB(tq1)> 

-- 此命令将提供有关数据保护当前状态的适当消息。
00:47:22 sys@TQDB(tq1)> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO STANDBY

00:47:23 sys@TQDB(tq1)> 

00:47:52 sys@TQDB(tq1)> alter database commit to switchover to standby with session shutdown;
ERROR:
ORA-01034: ORACLE not available
Process ID: 6298
Session ID: 1 Serial number: 62412



Database altered.

00:48:31 sys@TQDB(tq1)> conn / as sysdba
Connected to an idle instance.
00:49:23 idle(tq1)> 
00:49:26 idle(tq1)> shutdown immediate;
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
Additional information: 4376
Additional information: -1183956957
Process ID: 0
Session ID: 0 Serial number: 0


00:49:41 idle(tq1)> conn / as sysdba
Connected to an idle instance.
00:49:45 idle(tq1)> startup mount;
ORACLE instance started.

Total System Global Area 1191181696 bytes
Fixed Size                  8895872 bytes
Variable Size             318767104 bytes
Database Buffers          855638016 bytes
Redo Buffers                7880704 bytes
Database mounted.
00:50:05 idle(tq1)> 

-- 2. Data Guard Side
00:54:07 sys@TQDB(tqdb21)> -- 此命令将提供有关数据保护当前状态的适当消息。
00:54:08 sys@TQDB(tqdb21)> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO PRIMARY

00:54:13 sys@TQDB(tqdb21)> 


-- 「RAC主库」节点1
00:57:20 sys@TQDB(tqdb21)> alter database recover managed standby database cancel;

Database altered.

00:57:50 sys@TQDB(tqdb21)> 
00:58:47 sys@TQDB(tqdb21)> alter database commit to switchover to primary with session shutdown;

Database altered.

00:59:35 sys@TQDB(tqdb21)> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
01:01:34 sys@TQDB(tqdb21)> conn / as sysdba
Connected to an idle instance.
01:01:45 idle(tqdb21)> 

-- 「RAC主库」节点2
01:02:23 sys@TQDB(tqdb22)> conn / as sysdba
Connected.
01:02:29 idle(tqdb22)> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
01:03:17 idle(tqdb22)> 

-- 「RAC主库」节点1
[root@tqdb21: ~]# crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr
           ONLINE  ONLINE       tqdb21                   STABLE
               ONLINE  ONLINE       tqdb22                   STABLE
    ora.chad
           ONLINE  ONLINE       tqdb21                   STABLE
               ONLINE  ONLINE       tqdb22                   STABLE
    ora.net1.network
           ONLINE  ONLINE       tqdb21                   STABLE
               ONLINE  ONLINE       tqdb22                   STABLE
    ora.ons
           ONLINE  ONLINE       tqdb21                   STABLE
               ONLINE  ONLINE       tqdb22                   STABLE
    --------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup)
  1        ONLINE  ONLINE       tqdb21                   STABLE
      2        ONLINE  ONLINE       tqdb22                   STABLE
      3        ONLINE  OFFLINE                               STABLE
    ora.DATA.dg(ora.asmgroup)
  1        ONLINE  ONLINE       tqdb21                   STABLE
      2        ONLINE  ONLINE       tqdb22                   STABLE
      3        OFFLINE OFFLINE                               STABLE
    ora.LISTENER_SCAN1.lsnr
  1        ONLINE  ONLINE       tqdb21                   STABLE
    ora.OCR.dg(ora.asmgroup)
  1        ONLINE  ONLINE       tqdb21                   STABLE
      2        ONLINE  ONLINE       tqdb22                   STABLE
      3        OFFLINE OFFLINE                               STABLE
    ora.asm(ora.asmgroup)
  1        ONLINE  ONLINE       tqdb21                   Started,STABLE
      2        ONLINE  ONLINE       tqdb22                   Started,STABLE
      3        OFFLINE OFFLINE                               STABLE
    ora.asmnet1.asmnetwork(ora.asmgroup)
  1        ONLINE  ONLINE       tqdb21                   STABLE
      2        ONLINE  ONLINE       tqdb22                   STABLE
      3        OFFLINE OFFLINE                               STABLE
    ora.cvu
  1        ONLINE  ONLINE       tqdb21                   STABLE
    ora.qosmserver
  1        ONLINE  ONLINE       tqdb21                   STABLE
    ora.scan1.vip
  1        ONLINE  ONLINE       tqdb21                   STABLE
    ora.tqdb.db
  1        OFFLINE OFFLINE                               STABLE
      2        OFFLINE OFFLINE                               STABLE
    ora.tqdb21.vip
  1        ONLINE  ONLINE       tqdb21                   STABLE
    ora.tqdb22.vip
  1        ONLINE  ONLINE       tqdb22                   STABLE
    --------------------------------------------------------------------------------
[root@tqdb21: ~]# 

01:01:45 idle(tqdb21)> startup 
ORACLE instance started.

Total System Global Area  822080768 bytes
Fixed Size                  8901888 bytes
Variable Size             390070272 bytes
Database Buffers          419430400 bytes
Redo Buffers                3678208 bytes
Database mounted.
Database opened.
01:04:15 idle(tqdb21)> conn / as sysdba
Connected.
01:04:24 sys@TQDB(tqdb21)> 
01:04:24 sys@TQDB(tqdb21)> -- 查询 Oracle ADG 保护模式
01:08:05 sys@TQDB(tqdb21)> select DATABASE_ROLE, open_mode, PROTECTION_MODE,PROTECTION_LEVEL from v$database;

DATABASE_ROLE    OPEN_MODE            PROTECTION_MODE      PROTECTION_LEVEL
---------------- -------------------- -------------------- --------------------
PRIMARY          READ WRITE           MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

01:08:06 sys@TQDB(tqdb21)> 

-- 「RAC主库」节点2
01:05:20 sys@TQDB(tqdb22)> -- 查询 Oracle ADG 保护模式
01:08:10 sys@TQDB(tqdb22)> select DATABASE_ROLE, open_mode, PROTECTION_MODE,PROTECTION_LEVEL from v$database;

DATABASE_ROLE    OPEN_MODE            PROTECTION_MODE      PROTECTION_LEVEL
---------------- -------------------- -------------------- --------------------
PRIMARY          READ WRITE           MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

01:08:11 sys@TQDB(tqdb22)> 

[root@tqdb22: ~]# crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr
           ONLINE  ONLINE       tqdb21                   STABLE
               ONLINE  ONLINE       tqdb22                   STABLE
    ora.chad
           ONLINE  ONLINE       tqdb21                   STABLE
               ONLINE  ONLINE       tqdb22                   STABLE
    ora.net1.network
           ONLINE  ONLINE       tqdb21                   STABLE
               ONLINE  ONLINE       tqdb22                   STABLE
    ora.ons
           ONLINE  ONLINE       tqdb21                   STABLE
               ONLINE  ONLINE       tqdb22                   STABLE
    --------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup)
  1        ONLINE  ONLINE       tqdb21                   STABLE
      2        ONLINE  ONLINE       tqdb22                   STABLE
      3        ONLINE  OFFLINE                               STABLE
    ora.DATA.dg(ora.asmgroup)
  1        ONLINE  ONLINE       tqdb21                   STABLE
      2        ONLINE  ONLINE       tqdb22                   STABLE
      3        OFFLINE OFFLINE                               STABLE
    ora.LISTENER_SCAN1.lsnr
  1        ONLINE  ONLINE       tqdb21                   STABLE
    ora.OCR.dg(ora.asmgroup)
  1        ONLINE  ONLINE       tqdb21                   STABLE
      2        ONLINE  ONLINE       tqdb22                   STABLE
      3        OFFLINE OFFLINE                               STABLE
    ora.asm(ora.asmgroup)
  1        ONLINE  ONLINE       tqdb21                   Started,STABLE
      2        ONLINE  ONLINE       tqdb22                   Started,STABLE
      3        OFFLINE OFFLINE                               STABLE
    ora.asmnet1.asmnetwork(ora.asmgroup)
  1        ONLINE  ONLINE       tqdb21                   STABLE
      2        ONLINE  ONLINE       tqdb22                   STABLE
      3        OFFLINE OFFLINE                               STABLE
    ora.cvu
  1        ONLINE  ONLINE       tqdb21                   STABLE
    ora.qosmserver
  1        ONLINE  ONLINE       tqdb21                   STABLE
    ora.scan1.vip
  1        ONLINE  ONLINE       tqdb21                   STABLE
    ora.tqdb.db
  1        ONLINE  ONLINE       tqdb21                   Open,HOME=/u01/app/o
                                                             racle/product/19c/db
                                                             home,STABLE
      2        ONLINE  ONLINE       tqdb22                   Open,HOME=/u01/app/o
                                                             racle/product/19c/db
                                                             home,STABLE
    ora.tqdb21.vip
  1        ONLINE  ONLINE       tqdb21                   STABLE
    ora.tqdb22.vip
  1        ONLINE  ONLINE       tqdb22                   STABLE
    --------------------------------------------------------------------------------
[root@tqdb22: ~]# 

-- 3. Primary Side
01:10:13 idle(tq1)> alter database open;

Database altered.

01:10:20 idle(tq1)> conn / as sysdba
Connected.
01:10:26 sys@TQDB(tq1)> alter database recover managed standby database disconnect from session;

Database altered.

01:11:43 sys@TQDB(tq1)> -- 查看DG的基本统计信息 @standby    
01:12:01 sys@TQDB(tq1)> set linesize 200;   
01:12:02 sys@TQDB(tq1)> col name for a25;   
01:12:02 sys@TQDB(tq1)> column value format a20;    
01:12:02 sys@TQDB(tq1)> select * from v$dataguard_stats;    

SOURCE_DBID SOURCE_DB_UNIQUE_NAME            NAME                      VALUE                UNIT                           TIME_COMPUTED                  DATUM_TIME                         CON_ID
----------- -------------------------------- ------------------------- -------------------- ------------------------------ ------------------------------ ------------------------------ ----------
3966209240 tqdb                             transport lag             +00 00:00:00         day(2) to second(0) interval   03/11/2020 01:12:02            03/11/2020 01:12:01                     0
 3966209240 tqdb                             apply lag                 +00 00:00:00         day(2) to second(0) interval   03/11/2020 01:12:02            03/11/2020 01:12:01                     0
 3966209240 tqdb                             apply finish time                              day(2) to second(3) interval   03/11/2020 01:12:02                                                    0
       0                                  estimated startup time    26                   second                         03/11/2020 01:12:02                                                    0
    
01:12:02 sys@TQDB(tq1)>
01:16:08 sys@TQDB(tq1)> -- 查询 Oracle ADG 保护模式
01:16:08 sys@TQDB(tq1)> select DATABASE_ROLE, open_mode, PROTECTION_MODE,PROTECTION_LEVEL from v$database;

DATABASE_ROLE    OPEN_MODE            PROTECTION_MODE      PROTECTION_LEVEL
---------------- -------------------- -------------------- --------------------
PHYSICAL STANDBY READ ONLY WITH APPLY MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

01:16:09 sys@TQDB(tq1)> 

本文完整介绍了 Oracle 19c RAC + ADG 手动 switchover 角色转换步骤。希望对各位看官有所帮助。

下一篇,我们将介绍「 Oracle 19c RAC + ADG 手动 failover 角色转换步骤」 。

-- The End --