ORACLE 19C ADG 手动切换

原创乔治和猫 2022-11-09

1502

ORACLE 19C ADG& DG切换

一、环境说明


参数名称	PRIMARY	STANDBYA	STANDBYB
Clusterware	19C R7 Grid Infrastructure ( 19.7.0.0)	19C R7 Grid Infrastructure ( 19.7.0.0)	19C R7 Grid Infrastructure ( 19.7.0.0)
Cluster Nodes	testdb1p, testdb2p(2-node RAC)	testdb3p (single db)	szztdb1p(single db)
SCAN	testdb-scan		
SCAN listener Host/port	SCAN VIPs (port 1521)	 	 
VIPs	testdb1p-vip, testdb2p-vip		
DB_UNIQUE_NAME	testdb	testdbsa	testdbsb
DB_NAME	testdb	testdb	testdb
DB Instances	testdb1, testdb2	testdbsa	testdbsb
DB LISTENER	LISTENER_1525	LISTENER_1525	LISTENER_1525
DB Listener Host/port	testdb1p, testdb2p (port 1525)	testdb3p (port 1525)	szztdb1p (port 1525)
DB STORAGE	+ASM1,+ASM2	+ASM	+ASM
ASM diskgroup for DB files/Filesystem	+DATADGA	+DATADGA	+DATADGA
ASM Diskgroup for Recovery Files	+DATADGA	+DATADGA	+DATADGA
ORACLE_HOME	/u01/app/oracle/product/19.0.0/dbhome_1	/u01/app/oracle/product/19.7/dbhome_1	/u01/app/oracle/product/19.7/dbhome_1
19C R2 RAC DB version	19.7.0.0	19.7.0.0	19.7.0.0
OS	Red Hat release 7.6 (Maipo)	Red Hat release 7.6 (Maipo)	Red Hat release 7.6 (Maipo)


PRIMARY Site:
•2-node 19C R7 Grid Infrastructure ( 19.7.0.0) 已经安装配置。
•数据库软件(19.7.0.0)已经安装，数据库已经存在.
•ASM diskgroup +DATADGA已经建立。
•DB listener_1525已经建立.
•LOCAL_LISTENER、REMOTE_LISTENER参数已经配置.

DR Site:
•1-node 19C R7 Grid Infrastructure ( 19.7.0.0)已经安装配置。
•ASM diskgroup +DATADGA已经建立。
•数据库软件(19.7.0.0)已经安装.
•数据文件及归档存储已经配置。
•Listener_1525已经配置.
复制

二、主备配置

2.1 相关参数配置

主库

alter system set LOG_ARCHIVE_DEST_1='LOCATION=+DATADGA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=testdb' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_2='SERVICE=testdbsa LGWR async VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=testdbsa' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_3='SERVICE=testdbsb LGWR async VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=testdbsb' scope=both sid='*';
alter system set log_archive_format='%t_%s_%r.arc' scope=spfile sid='*';
alter system set log_archive_max_processes=8 scope=both sid='*';
alter system set fal_server='testdbsa' scope=both sid='*';
alter system set standby_file_management=AUTO scope=both sid='*';

alter system set db_file_name_convert='testdbsa','testdbsb','testdb' scope=spfile sid='*';
alter system set log_file_name_convert='testdbsa','testdbsa','testdb' scope=spfile sid='*';
复制

同城ADG testdbsa

alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(testdbsa,testdb,testdbsb)' scope=both;
alter system set LOG_ARCHIVE_DEST_1='LOCATION=+DATADGA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=testdbsa' scope=both;
alter system set LOG_ARCHIVE_DEST_2='SERVICE=testdbpri LGWR async VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=testdb' scope=both;
alter system set LOG_ARCHIVE_DEST_3='SERVICE=testdbsb LGWR async VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=testdbsb' scope=both;
alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;
alter system set log_archive_max_processes=8 scope=both;
alter system set fal_server='testdb' scope=both;
alter system set standby_file_management=AUTO scope=both;


alter system set db_file_name_convert='testdb','testdbsb','testdbsa' scope=both sid='*';
alter system set log_file_name_convert='testdb','testdbsb','testdbsa' scope=spfile;
复制

异地 DG testdbsb

alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(testdbsb,testdb,testdbsa)' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_1='LOCATION=+DATADGA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=testdbsb' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_2='SERVICE=testdbpri LGWR async VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=testdb' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_3='SERVICE=testdbsa LGWR async VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=testdbsa' scope=both sid='*';
alter system set log_archive_format='%t_%s_%r.arc' scope=spfile sid='*';
alter system set log_archive_max_processes=8 scope=both sid='*';
alter system set fal_server='testdb' scope=both sid='*';
alter system set standby_file_management=AUTO scope=both sid='*';


alter system set db_file_name_convert='testdb','testdbsb' scope=spfile sid='*';
alter system set log_file_name_convert='testdb','testdbsb' scope=spfile sid='*';
复制

2.2 TNSNAMES配置

Primary:

testDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = testdb-scan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = testdb)
    )
  )

testDBPRI =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS=(PROTOCOL = TCP)(HOST= 10.0.25.141)(PORT = 1525))
      (ADDRESS=(PROTOCOL = TCP)(HOST= 10.0.25.143)(PORT = 1525))
    ) 
    (CONNECT_DATA =
      (SERVER = DEDICATED) 
      (SERVICE_NAME = testdb)
    )
  )

testDBSA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS=(PROTOCOL = TCP)(HOST= 10.0.25.139)(PORT = 1525))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = testdbsa)
    )
  )

testDBSB =
  (DESCRIPTION = 
    (ADDRESS_LIST =
      (ADDRESS=(PROTOCOL = TCP)(HOST= 10.32.67.135)(PORT = 1525))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = testdbsb)
    )
  )
复制

testdbsa:

testDBPRI =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS=(PROTOCOL = TCP)(HOST= 10.0.25.141)(PORT = 1525))
          (ADDRESS=(PROTOCOL = TCP)(HOST= 10.0.25.143)(PORT = 1525))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = testdb)
    )
  )

testDBSA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS=(PROTOCOL = TCP)(HOST= 10.0.25.139)(PORT = 1525))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = testdbsa)
    )
  )

testDBSB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS=(PROTOCOL = TCP)(HOST= 10.32.67.135)(PORT = 1525))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = testdbsb)
    )
  )

复制

testdbsb:

testDBPRI =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS=(PROTOCOL = TCP)(HOST= 10.0.25.141)(PORT = 1525))
      (ADDRESS=(PROTOCOL = TCP)(HOST= 10.0.25.143)(PORT = 1525))
    ) 
    (CONNECT_DATA =
      (SERVER = DEDICATED) 
      (SERVICE_NAME = testdb)
    )
  )

testDBSA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS=(PROTOCOL = TCP)(HOST= 10.0.25.139)(PORT = 1525))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = testdbsa)
    )
  )

testDBSB =
  (DESCRIPTION = 
    (ADDRESS_LIST =
      (ADDRESS=(PROTOCOL = TCP)(HOST= 10.32.67.135)(PORT = 1525))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = testdbsb)
    )
  )
复制

2.3 CRS状态检查

testdbpri:

[oracle@testdb1p ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr
               ONLINE  ONLINE       testdb1p                 STABLE
               ONLINE  ONLINE       testdb2p                 STABLE
ora.LISTENER_1525.lsnr
               ONLINE  ONLINE       testdb1p                 STABLE
               ONLINE  ONLINE       testdb2p                 STABLE
ora.chad
               ONLINE  ONLINE       testdb1p                 STABLE
               ONLINE  ONLINE       testdb2p                 STABLE
ora.net1.network+
               ONLINE  ONLINE       testdb1p                 STABLE
               ONLINE  ONLINE       testdb2p                 STABLE
ora.ons
               ONLINE  ONLINE       testdb1p                 STABLE
               ONLINE  ONLINE       testdb2p                 STABLE
ora.proxy_advm
               OFFLINE OFFLINE      testdb1p                 STABLE
               OFFLINE OFFLINE      testdb2p                 STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup)
      1        ONLINE  ONLINE       testdb1p                 STABLE
      2        ONLINE  ONLINE       testdb2p                 STABLE
      3        ONLINE  OFFLINE                               STABLE
ora.ASMNET2LSNR_ASM.lsnr(ora.asmgroup)
      1        ONLINE  ONLINE       testdb1p                 STABLE
      2        ONLINE  ONLINE       testdb2p                 STABLE
      3        ONLINE  OFFLINE                               STABLE
ora.CRSDG.dg(ora.asmgroup)
      1        ONLINE  ONLINE       testdb1p                 STABLE
      2        ONLINE  ONLINE       testdb2p                 STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.DATADGA.dg(ora.asmgroup)
      1        ONLINE  ONLINE       testdb1p                 STABLE
      2        ONLINE  ONLINE       testdb2p                 STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       testdb1p                 STABLE
ora.asm(ora.asmgroup)
      1        ONLINE  ONLINE       testdb1p                 Started,STABLE
      2        ONLINE  ONLINE       testdb2p                 Started,STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.asmnet1.asmnetwork(ora.asmgroup)
      1        ONLINE  ONLINE       testdb1p                 STABLE
      2        ONLINE  ONLINE       testdb2p                 STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.asmnet2.asmnetwork(ora.asmgroup)
      1        ONLINE  ONLINE       testdb1p                 STABLE
      2        ONLINE  ONLINE       testdb2p                 STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.cvu
      1        ONLINE  ONLINE       testdb1p                 STABLE
ora.qosmserver
      1        ONLINE  ONLINE       testdb1p                 STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       testdb1p                 STABLE
ora.testdb.db
      1        ONLINE  ONLINE       testdb1p                 Open,HOME=/u01/app/o
                                                             racle/product/19.0.0
                                                             /dbhome_1,STABLE
      2        ONLINE  ONLINE       testdb2p                 Open,HOME=/u01/app/o
                                                             racle/product/19.0.0
                                                             /dbhome_1,STABLE
ora.testdb1p.vip
      1        ONLINE  ONLINE       testdb1p                 STABLE
ora.testdb2p.vip
      1        ONLINE  ONLINE       testdb2p                 STABLE
--------------------------------------------------------------------------------
复制

testdbsa:

[oracle@szztdb1p ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATADGA.dg
               ONLINE  ONLINE       szztdb1p                 STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       szztdb1p                 STABLE
ora.LISTENER_1525.lsnr
               ONLINE  ONLINE       szztdb1p                 STABLE
ora.asm
               ONLINE  ONLINE       szztdb1p                 Started,STABLE
ora.ons
               OFFLINE OFFLINE      szztdb1p                 STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        ONLINE  ONLINE       szztdb1p                 STABLE
ora.diskmon
      1        OFFLINE OFFLINE                               STABLE
ora.evmd
      1        ONLINE  ONLINE       szztdb1p                 STABLE
--------------------------------------------------------------------------------
复制

testdbsb:

[oracle@szztdb1p ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATADGA.dg
               ONLINE  ONLINE       szztdb1p                 STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       szztdb1p                 STABLE
ora.LISTENER_1525.lsnr
               ONLINE  ONLINE       szztdb1p                 STABLE
ora.asm
               ONLINE  ONLINE       szztdb1p                 Started,STABLE
ora.ons
               OFFLINE OFFLINE      szztdb1p                 STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        ONLINE  ONLINE       szztdb1p                 STABLE
ora.diskmon
      1        OFFLINE OFFLINE                               STABLE
ora.evmd
      1        ONLINE  ONLINE       szztdb1p                 STABLE
--------------------------------------------------------------------------------
复制

2.4 同步状态检查

testdbpri:

set lines 200 pages 999
col FORCE_LOGGING for a10
SQL> select db_unique_name,status,protection_mode,synchronization_status,synchronized from v$archive_dest_status where dest_id<4;

DB_UNIQUE_NAME                 STATUS    PROTECTION_MODE      SYNCHRONIZATION_STATUS SYN
------------------------------ --------- -------------------- ---------------------- ---
testdb                         VALID     MAXIMUM PERFORMANCE  CHECK CONFIGURATION    NO
testdbsa                       VALID     MAXIMUM PERFORMANCE  CHECK CONFIGURATION    NO
testdbsb                       VALID     MAXIMUM PERFORMANCE  CHECK CONFIGURATION    NO

SQL>  select OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, FORCE_LOGGING, DATAGUARD_BROKER from v$database;

OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS    FORCE_LOGGING   DATAGUAR
-------------------- ---------------- -------------------- --------------- --------
READ WRITE           PRIMARY          TO STANDBY           YES             DISABLED

################当前日志###################

SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

   THREAD# MAX(SEQUENCE#)
---------- --------------
         1            150
         2             86

set linesize 200
col dest_name for a20
col status for a10
col destination for a18
col error for a20
col ALTERNATE for a20
col DESTINATION for a30
select dest_id,dest_name,status,target,destination,error,alternate from v$archive_dest where DESTINATION is not null;
复制

testdbsa:

set lines 200 pages 999
col FORCE_LOGGING for a10

SQL> select db_unique_name,status,protection_mode,synchronization_status,synchronized from v$archive_dest_status where dest_id<4;

DB_UNIQUE_NAME                 STATUS    PROTECTION_MODE      SYNCHRONIZATION_STATUS SYN
------------------------------ --------- -------------------- ---------------------- ---
testdbsa                       VALID     MAXIMUM PERFORMANCE  STATUS NOT AVAILABLE   NO
testdb                         VALID     MAXIMUM PERFORMANCE  STATUS NOT AVAILABLE   NO
testdbsb                       VALID     MAXIMUM PERFORMANCE  STATUS NOT AVAILABLE   NO

SQL> select OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, FORCE_LOGGING, DATAGUARD_BROKER from v$database;

OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS    FORCE_LOGG DATAGUAR
-------------------- ---------------- -------------------- ---------- --------
READ ONLY WITH APPLY PHYSICAL STANDBY NOT ALLOWED          YES        DISABLED

################应用日志与gap检查###################
SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

   THREAD# MAX(SEQUENCE#)
---------- --------------
         1            150
         2             86

SET LINES 200 PAGES 999
COL DEST_NAME FOR A20
COL STATUS FOR A10
COL DESTINATION FOR A18
COL ERROR FOR A20
COL ALTERNATE FOR A20
COL DESTINATION FOR A30
select dest_id,dest_name,status,target,destination,error,alternate from v$archive_dest where DESTINATION is not null;
SQL> select dest_id,dest_name,status,target,destination,error,alternate from v$archive_dest where DESTINATION is not null;

   DEST_ID DEST_NAME            STATUS     TARGET           DESTINATION                    ERROR                ALTERNATE
---------- -------------------- ---------- ---------------- ------------------------------ -------------------- --------------------
         1 LOG_ARCHIVE_DEST_1   VALID      PRIMARY          +DATADGA                                            NONE
         2 LOG_ARCHIVE_DEST_2   VALID      STANDBY          testdbsa                                            NONE
         3 LOG_ARCHIVE_DEST_3   VALID      STANDBY          testdbsb                                            NONE

###检查日志应用状态
select thread#,sequence#,applied,archived,deleted,status from v$archived_log order by 1,2;

SQL>  select thread#,sequence#,applied,archived,deleted,status from v$archived_log order by 1,2;

   THREAD#  SEQUENCE# APPLIED   ARC DEL STATUS
---------- ---------- --------- --- --- ----------
         1         46 NO        YES YES D
         1         46 YES       YES NO  A
         1         47 YES       YES YES D
         1         48 NO        YES YES D
         1         48 YES       YES NO  A
         1         49 YES       YES NO  A
         1         50 YES       YES NO  A
         1         51 YES       YES NO  A
         1         52 YES       YES NO  A
         1         53 YES       YES NO  A
         1         54 YES       YES NO  A
         1         55 YES       YES NO  A
         1         56 YES       YES NO  A
         1         57 YES       YES NO  A
         1         58 YES       YES NO  A
         1         59 YES       YES NO  A
         1         60 YES       YES NO  A
         1         61 YES       YES NO  A
         1         62 YES       YES NO  A
         1         63 YES       YES NO  A
         1         64 YES       YES NO  A
         1         65 YES       YES NO  A
         1         66 YES       YES NO  A
         1         67 YES       YES NO  A
         1         68 YES       YES NO  A
         1         69 YES       YES NO  A
         1         70 YES       YES NO  A
         1         71 YES       YES NO  A
         1         72 YES       YES NO  A
         1         73 YES       YES NO  A
         1         74 YES       YES NO  A
         1         75 YES       YES NO  A
         1         76 YES       YES NO  A
         1         77 YES       YES NO  A
         1         78 YES       YES NO  A
         1         79 YES       YES NO  A
         1         80 YES       YES NO  A
         1         81 YES       YES NO  A
         1         82 YES       YES NO  A
         1         83 YES       YES NO  A
         1         84 YES       YES NO  A
         1         85 YES       YES NO  A
         1         86 YES       YES NO  A
         1         87 YES       YES NO  A
         1         88 YES       YES NO  A
         1         89 YES       YES NO  A
         1         90 YES       YES NO  A
         1         91 YES       YES NO  A
         1         92 YES       YES NO  A
         1         93 YES       YES NO  A
         1         94 YES       YES NO  A
         1         95 YES       YES NO  A
         1         96 YES       YES NO  A
         1         97 YES       YES NO  A
         1         98 YES       YES NO  A
         1         99 YES       YES NO  A
         1        100 YES       YES NO  A
         1        101 YES       YES NO  A
         1        102 YES       YES NO  A
         1        103 YES       YES NO  A
         1        104 YES       YES NO  A
         1        105 YES       YES NO  A
         1        106 YES       YES NO  A
         1        107 YES       YES NO  A
         1        108 YES       YES NO  A
         1        109 YES       YES NO  A
         1        110 YES       YES NO  A
         1        111 YES       YES NO  A
         1        112 YES       YES NO  A
         1        113 YES       YES NO  A
         1        114 YES       YES NO  A
         1        115 YES       YES NO  A
         1        116 YES       YES NO  A
         1        117 YES       YES NO  A
         1        118 YES       YES NO  A
         1        119 YES       YES NO  A
         1        120 YES       YES NO  A
         1        121 YES       YES NO  A
         1        122 YES       YES NO  A
         1        123 YES       YES NO  A
         1        124 YES       YES NO  A
         1        125 YES       YES NO  A
         1        126 YES       YES NO  A
         1        127 YES       YES NO  A
         1        128 YES       YES NO  A
         1        129 YES       YES NO  A
         1        130 YES       YES NO  A
         1        131 YES       YES NO  A
         1        132 YES       YES NO  A
         1        133 YES       YES NO  A
         1        134 YES       YES NO  A
         1        135 YES       YES NO  A
         1        136 YES       YES NO  A
         1        137 YES       YES NO  A
         1        138 YES       YES NO  A
         1        139 YES       YES NO  A
         1        140 YES       YES NO  A
         1        141 YES       YES NO  A
         1        142 YES       YES NO  A
         1        143 YES       YES NO  A
         1        144 YES       YES NO  A
         1        145 YES       YES NO  A
         1        146 YES       YES NO  A
         1        147 YES       YES NO  A
         1        148 YES       YES NO  A
         1        149 YES       YES NO  A
         1        150 YES       YES NO  A
         2         36 YES       YES YES D
         2         37 YES       YES YES D
         2         38 YES       YES NO  A
         2         39 YES       YES NO  A
         2         40 YES       YES NO  A
         2         41 YES       YES NO  A
         2         42 YES       YES NO  A
         2         43 YES       YES NO  A
         2         44 YES       YES NO  A
         2         45 YES       YES NO  A
         2         46 YES       YES NO  A
         2         47 YES       YES NO  A
         2         48 YES       YES NO  A
         2         49 YES       YES NO  A
         2         50 YES       YES NO  A
         2         51 YES       YES NO  A
         2         52 YES       YES NO  A
         2         53 YES       YES NO  A
         2         54 YES       YES NO  A
         2         55 YES       YES NO  A
         2         56 YES       YES NO  A
         2         57 YES       YES NO  A
         2         58 YES       YES NO  A
         2         59 YES       YES NO  A
         2         60 YES       YES NO  A
         2         61 YES       YES NO  A
         2         62 YES       YES NO  A
         2         63 YES       YES NO  A
         2         64 YES       YES NO  A
         2         65 YES       YES NO  A
         2         66 YES       YES NO  A
         2         67 YES       YES NO  A
         2         68 YES       YES NO  A
         2         69 YES       YES NO  A
         2         70 YES       YES NO  A
         2         71 YES       YES NO  A
         2         72 YES       YES NO  A
         2         73 YES       YES NO  A
         2         74 YES       YES NO  A
         2         75 YES       YES NO  A
         2         76 YES       YES NO  A
         2         77 YES       YES NO  A
         2         78 YES       YES NO  A
         2         79 YES       YES NO  A
         2         80 YES       YES NO  A
         2         81 YES       YES NO  A
         2         82 YES       YES NO  A
         2         83 YES       YES NO  A
         2         84 YES       YES NO  A
         2         85 YES       YES NO  A
         2         86 YES       YES NO  A

158 rows selected.

SQL> select thread#,low_sequence#,high_sequence# from v$archive_gap;   

no rows selected
复制

testdbsb:

set lines 200 pages 999
col FORCE_LOGGING for a10

SQL> select db_unique_name,status,protection_mode,synchronization_status,synchronized from v$archive_dest_status where dest_id<4;

DB_UNIQUE_NAME                 STATUS    PROTECTION_MODE      SYNCHRONIZATION_STATUS SYN
------------------------------ --------- -------------------- ---------------------- ---
testdbsb                       VALID     MAXIMUM PERFORMANCE  STATUS NOT AVAILABLE   NO
testdb                         VALID     MAXIMUM PERFORMANCE  STATUS NOT AVAILABLE   NO
testdbsa                       VALID     MAXIMUM PERFORMANCE  STATUS NOT AVAILABLE   NO

SQL> select OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, FORCE_LOGGING, DATAGUARD_BROKER from v$database;

OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS    FORCE_LOGG DATAGUAR
-------------------- ---------------- -------------------- ---------- --------
MOUNTED              PHYSICAL STANDBY NOT ALLOWED          YES        DISABLED

################应用日志与gap检查###################
SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

   THREAD# MAX(SEQUENCE#)
---------- --------------
         1            150
         2             86

SET LINES 200 PAGES 999
COL DEST_NAME FOR A20
COL STATUS FOR A10
COL DESTINATION FOR A18
COL ERROR FOR A20
COL ALTERNATE FOR A20
COL DESTINATION FOR A30
select dest_id,dest_name,status,target,destination,error,alternate from v$archive_dest where DESTINATION is not null;

SQL> select dest_id,dest_name,status,target,destination,error,alternate from v$archive_dest where DESTINATION is not null;

   DEST_ID DEST_NAME            STATUS     TARGET           DESTINATION                    ERROR                ALTERNATE
---------- -------------------- ---------- ---------------- ------------------------------ -------------------- --------------------
         1 LOG_ARCHIVE_DEST_1   VALID      LOCAL            +DATADGA                                            NONE
         2 LOG_ARCHIVE_DEST_2   VALID      REMOTE           testdbpri                                           NONE
         3 LOG_ARCHIVE_DEST_3   VALID      REMOTE           testdbsa                                            NONE
        32 STANDBY_ARCHIVE_DEST VALID      LOCAL            +DATADGA                                            NONE

###检查日志应用状态
select thread#,sequence#,applied,archived,deleted,status from v$archived_log order by 1,2;

SQL>  select thread#,sequence#,applied,archived,deleted,status from v$archived_log order by 1,2;

   THREAD#  SEQUENCE# APPLIED   ARC DEL STATUS
---------- ---------- --------- --- --- ----------
         1        134 NO        YES YES D
         1        134 YES       YES NO  A
         1        135 YES       YES YES D
         1        136 NO        YES YES D
         1        136 YES       YES NO  A
         1        137 YES       YES NO  A
         1        138 YES       YES NO  A
         1        139 YES       YES NO  A
         1        140 YES       YES NO  A
         1        141 YES       YES NO  A
         1        142 YES       YES NO  A
         1        143 YES       YES NO  A
         1        144 YES       YES NO  A
         1        145 YES       YES NO  A
         1        146 YES       YES NO  A
         1        147 YES       YES NO  A
         1        148 YES       YES NO  A
         1        149 YES       YES NO  A
         1        150 YES       YES NO  A
         2         72 YES       YES YES D
         2         73 YES       YES YES D
         2         74 YES       YES NO  A
         2         75 YES       YES NO  A
         2         76 YES       YES NO  A
         2         77 YES       YES NO  A
         2         78 YES       YES NO  A
         2         79 YES       YES NO  A
         2         80 YES       YES NO  A
         2         81 YES       YES NO  A
         2         82 YES       YES NO  A
         2         83 YES       YES NO  A
         2         84 YES       YES NO  A
         2         85 YES       YES NO  A
         2         86 YES       YES NO  A

34 rows selected.


SQL> select thread#,low_sequence#,high_sequence# from v$archive_gap;   

no rows selected
复制

三、切换方案概述

3.1正常切换演练

3.1.1 同城ADG切换为主库

同城ADG切换为主库，RAC及异地DG从同城ADG应用日志


#同城ADG确认同步状态
SET LINES 200 PAGES 999
COL FORCE_LOGGING FOR A10
SELECT OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, FORCE_LOGGING, DATAGUARD_BROKER FROM V$DATABASE;
SELECT THREAD#,MAX(SEQUENCE#) FROM V$ARCHIVED_LOG GROUP BY THREAD#;

SET LINES 200 PAGES 999
COL DEST_NAME FOR A20
COL STATUS FOR A10
COL DESTINATION FOR A18
COL ERROR FOR A20
COL ALTERNATE FOR A20
COL DESTINATION FOR A30
select dest_id,dest_name,status,target,destination,error,alternate from v$archive_dest where DESTINATION is not null;

###检查日志应用状态
select thread#,sequence#,applied,archived,deleted,status from v$archived_log order by 1,2;

###检查GAP
SQL> select thread#,low_sequence#,high_sequence# from v$archive_gap;   


#主库切换状态查询
select OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, FORCE_LOGGING, DATAGUARD_BROKER from gv$database;

SQL> select OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, FORCE_LOGGING, DATAGUARD_BROKER from gv$database;

OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS    FORCE_LOGGING   DATAGUAR
-------------------- ---------------- -------------------- --------------- --------
READ WRITE           PRIMARY          TO STANDBY           YES             DISABLED
READ WRITE           PRIMARY          TO STANDBY           YES             DISABLED
#切换时主库只保留一个节点，需关闭其他节点
Oracle用户：
oracle $ srvctl stop instance -d testdb -i testdb2
#主库切换为备库
select OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, FORCE_LOGGING, DATAGUARD_BROKER from gv$database;

ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY WITH SESSION SHUTDOWN;

testdbsa:
#ADG库切换为主库
set lines 200 pages 999
select database_role,name,SWITCHOVER_STATUS from v$database;   -- switchover_status 应为To Primary

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

#需关闭数据库，重新启动，并确认ADG节点是否存在报错
SQL> SHUTDOWN IMMEDIATE;
SQL> startup 
SQL> select database_role,name,SWITCHOVER_STATUS from v$database;
select dest_id,dest_name,status,target,destination,error,alternate from v$archive_dest where DESTINATION is not null;

SQL> SELECT DEST_ID, STATUS, APPLIED_SCN FROM V$ARCHIVE_DEST WHERE TARGET='STANDBY';

   DEST_ID STATUS    APPLIED_SCN
---------- --------- -----------
	 2 VALID	 1997588
	 3 VALID	 1998739

SQL> alter system archive log current;

复制

testdbpri:

#####RAC启库，启动实时日志应用
oracle:
srvctl start database  -d testdb  –o  mount

#RAC起库建议先开日志应用,待检查正常后开实时日志应用
alter database recover managed standby database disconnect from session;

#检查RAC 日志同步状态

SET LINES 200 PAGES 999
COL FORCE_LOGGING FOR A10
SELECT OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, FORCE_LOGGING, DATAGUARD_BROKER FROM V$DATABASE;
SELECT THREAD#,MAX(SEQUENCE#) FROM V$ARCHIVED_LOG GROUP BY THREAD#;

SET LINES 200 PAGES 999
COL DEST_NAME FOR A20
COL STATUS FOR A10
COL DESTINATION FOR A18
COL ERROR FOR A20
COL ALTERNATE FOR A20
COL DESTINATION FOR A30
select dest_id,dest_name,status,target,destination,error,alternate from v$archive_dest where DESTINATION is not null;

###检查日志应用状态
select thread#,sequence#,applied,archived,deleted,status from v$archived_log order by 1,2;

###检查GAP
SQL> select thread#,low_sequence#,high_sequence# from v$archive_gap;   

#取消日志应用开启实时日志应用
alter database recover managed standby database cancel;
alter database recover managed standby database using current logfile disconnect from session;



testdbsb:
#DG 如果异常应停止恢复，再手动启动恢复,并检查日志应用和gap情况
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;


#检查DG日志同步状态

SET LINES 200 PAGES 999
COL FORCE_LOGGING FOR A10
SELECT OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, FORCE_LOGGING, DATAGUARD_BROKER FROM V$DATABASE;
SELECT THREAD#,MAX(SEQUENCE#) FROM V$ARCHIVED_LOG GROUP BY THREAD#;

SET LINES 200 PAGES 999
COL DEST_NAME FOR A20
COL STATUS FOR A10
COL DESTINATION FOR A18
COL ERROR FOR A20
COL ALTERNATE FOR A20
COL DESTINATION FOR A30
select dest_id,dest_name,status,target,destination,error,alternate from v$archive_dest where DESTINATION is not null;

###检查日志应用状态
select thread#,sequence#,applied,archived,deleted,status from v$archived_log order by 1,2;

###检查GAP
SQL> select thread#,low_sequence#,high_sequence# from v$archive_gap;   
复制

3.1.2 同城ADG回切为备库

同城ADG切换为主库，同城ADG及异地DG从RAC应用日志

#RAC确认同步状态
SET LINES 200 PAGES 999
COL FORCE_LOGGING FOR A10
SELECT OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, FORCE_LOGGING, DATAGUARD_BROKER FROM V$DATABASE;
SELECT THREAD#,MAX(SEQUENCE#) FROM V$ARCHIVED_LOG GROUP BY THREAD#;

SET LINES 200 PAGES 999
COL DEST_NAME FOR A20
COL STATUS FOR A10
COL DESTINATION FOR A18
COL ERROR FOR A20
COL ALTERNATE FOR A20
COL DESTINATION FOR A30
select dest_id,dest_name,status,target,destination,error,alternate from v$archive_dest where DESTINATION is not null;

###检查日志应用状态
select thread#,sequence#,applied,archived,deleted,status from v$archived_log order by 1,2;

###检查GAP
SQL> select thread#,low_sequence#,high_sequence# from v$archive_gap;   


#ADG主库切换状态查询
select OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, FORCE_LOGGING, DATAGUARD_BROKER from v$database;

#主库切换为备库
select OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, FORCE_LOGGING, DATAGUARD_BROKER from gv$database;

SQL> select OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, FORCE_LOGGING, DATAGUARD_BROKER from v$database;

OPEN_MODE	     DATABASE_ROLE    SWITCHOVER_STATUS    FORCE_LOGG DATAGUAR
-------------------- ---------------- -------------------- ---------- --------
READ WRITE	     PRIMARY	      TO STANDBY	   YES	      DISABLED

ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY WITH SESSION SHUTDOWN;
复制

testdbpri:

#RAC库切换为主库
set lines 200 pages 999
select database_role,name,SWITCHOVER_STATUS from v$database;   -- switchover_status 应为To Primary

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

#需关闭数据库，重新启动，并确认ADG节点是否存在报错
SQL> SHUTDOWN IMMEDIATE;
SQL> startup
SQL> select database_role,name,SWITCHOVER_STATUS from v$database;
select dest_id,dest_name,status,target,destination,error,alternate from v$archive_dest where DESTINATION is not null;



SELECT DEST_ID, STATUS, APPLIED_SCN FROM V$ARCHIVE_DEST WHERE TARGET='STANDBY';

#RAC切日志
Alter system archive log current;

复制

testdbsa:

#ADG启库，启动日志应用
Startup mount;

alter database recover managed standby database disconnect from session;

#检查ADG日志同步状态

SET LINES 200 PAGES 999
COL FORCE_LOGGING FOR A10
SELECT OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, FORCE_LOGGING, DATAGUARD_BROKER FROM V$DATABASE;
SELECT THREAD#,MAX(SEQUENCE#) FROM V$ARCHIVED_LOG GROUP BY THREAD#;

SET LINES 200 PAGES 999
COL DEST_NAME FOR A20
COL STATUS FOR A10
COL DESTINATION FOR A18
COL ERROR FOR A20
COL ALTERNATE FOR A20
COL DESTINATION FOR A30
select dest_id,dest_name,status,target,destination,error,alternate from v$archive_dest where DESTINATION is not null;

###检查日志应用状态
select thread#,sequence#,applied,archived,deleted,status from v$archived_log order by 1,2;

###检查GAP
SQL> select thread#,low_sequence#,high_sequence# from v$archive_gap;   

alter database recover managed standby database disconnect from session;

Alter database open read only;
alter database recover managed standby database using current logfile disconnect from session;
复制

testdbsa:

#DG如异常可停止恢复，再手动启动恢复,并检查日志应用和gap情况
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

#检查DG日志同步状态

SET LINES 200 PAGES 999
COL FORCE_LOGGING FOR A10
SELECT OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, FORCE_LOGGING, DATAGUARD_BROKER FROM V$DATABASE;
SELECT THREAD#,MAX(SEQUENCE#) FROM V$ARCHIVED_LOG GROUP BY THREAD#;

SET LINES 200 PAGES 999
COL DEST_NAME FOR A20
COL STATUS FOR A10
COL DESTINATION FOR A18
COL ERROR FOR A20
COL ALTERNATE FOR A20
COL DESTINATION FOR A30
select dest_id,dest_name,status,target,destination,error,alternate from v$archive_dest where DESTINATION is not null;

###检查日志应用状态
select thread#,sequence#,applied,archived,deleted,status from v$archived_log order by 1,2;

###检查GAP
SQL> select thread#,low_sequence#,high_sequence# from v$archive_gap;   

复制

3.1.3 异地DG切换为主库

异地 DG切换为主库，RAC及同城ADG从异地DG应用日志

#异地 DG确认同步状态

SET LINES 200 PAGES 999
COL FORCE_LOGGING FOR A10
SELECT OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, FORCE_LOGGING, DATAGUARD_BROKER FROM V$DATABASE;
SELECT THREAD#,MAX(SEQUENCE#) FROM V$ARCHIVED_LOG GROUP BY THREAD#;

SET LINES 200 PAGES 999
COL DEST_NAME FOR A20
COL STATUS FOR A10
COL DESTINATION FOR A18
COL ERROR FOR A20
COL ALTERNATE FOR A20
COL DESTINATION FOR A30
select dest_id,dest_name,status,target,destination,error,alternate from v$archive_dest where DESTINATION is not null;

###检查日志应用状态
select thread#,sequence#,applied,archived,deleted,status from v$archived_log order by 1,2;

###检查GAP
SQL> select thread#,low_sequence#,high_sequence# from v$archive_gap;   


#主库切换状态查询
select OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, FORCE_LOGGING, DATAGUARD_BROKER from gv$database;

SQL> select OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, FORCE_LOGGING, DATAGUARD_BROKER from gv$database;

OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS    FORCE_LOGGING   DATAGUAR
-------------------- ---------------- -------------------- --------------- --------
READ WRITE           PRIMARY          TO STANDBY           YES             DISABLED
READ WRITE           PRIMARY          TO STANDBY           YES             DISABLED
#切换时主库只保留一个节点，需关闭其他节点

#切换时主库只保留一个节点，需关闭其他节点
Oracle用户：
oracle $ srvctl stop instance -d testdb -i testdb2
#主库切换为备库
select OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, FORCE_LOGGING, DATAGUARD_BROKER from gv$database;


#主库切换为备库

ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY WITH SESSION SHUTDOWN;


#DG库切换为主库
select OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, FORCE_LOGGING, DATAGUARD_BROKER, GUARD_STATUS from v$database;
select switchover_status from v$database;
switchover_status 应为To Primary


ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

alter database open;

SQL>  select OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, FORCE_LOGGING, DATAGUARD_BROKER, GUARD_STATUS from v$database;

OPEN_MODE	     DATABASE_ROLE    SWITCHOVER_STATUS    FORCE_LOGG DATAGUAR GUARD_S
-------------------- ---------------- -------------------- ---------- -------- -------
READ WRITE	     PRIMARY	      TO STANDBY	   YES	      DISABLED NONE

复制

testdbpri:

#RAC启库，启动日志应用
oracle:
srvctl start database  -d testdb  –o  mount

alter database recover managed standby database disconnect from session;


#检查RAC 日志同步状态

SET LINES 200 PAGES 999
COL FORCE_LOGGING FOR A10
SELECT OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, FORCE_LOGGING, DATAGUARD_BROKER FROM V$DATABASE;
SELECT THREAD#,MAX(SEQUENCE#) FROM V$ARCHIVED_LOG GROUP BY THREAD#;

SET LINES 200 PAGES 999
COL DEST_NAME FOR A20
COL STATUS FOR A10
COL DESTINATION FOR A18
COL ERROR FOR A20
COL ALTERNATE FOR A20
COL DESTINATION FOR A30
select dest_id,dest_name,status,target,destination,error,alternate from v$archive_dest where DESTINATION is not null;

###检查日志应用状态
select thread#,sequence#,applied,archived,deleted,status from v$archived_log order by 1,2;

###检查GAP
SQL> select thread#,low_sequence#,high_sequence# from v$archive_gap;   


alter database recover managed standby database using current logfile disconnect from session;

复制

testdbsa:

#ADG如果停止恢复，再手动启动恢复,并检查日志应用和gap情况
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;


#检查ADG日志同步状态

SET LINES 200 PAGES 999
COL FORCE_LOGGING FOR A10
SELECT OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, FORCE_LOGGING, DATAGUARD_BROKER FROM V$DATABASE;
SELECT THREAD#,MAX(SEQUENCE#) FROM V$ARCHIVED_LOG GROUP BY THREAD#;

SET LINES 200 PAGES 999
COL DEST_NAME FOR A20
COL STATUS FOR A10
COL DESTINATION FOR A18
COL ERROR FOR A20
COL ALTERNATE FOR A20
COL DESTINATION FOR A30
select dest_id,dest_name,status,target,destination,error,alternate from v$archive_dest where DESTINATION is not null;

###检查日志应用状态
select thread#,sequence#,applied,archived,deleted,status from v$archived_log order by 1,2;

###检查GAP
SQL> select thread#,low_sequence#,high_sequence# from v$archive_gap;   

############日志应用完成可开启实时日志应用
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
复制

3.1.4 异地DG回切为备库

RAC切换为主库，同城ADG及异地DG从RAC应用日志

#RAC确认同步状态
SET LINES 200 PAGES 999
COL FORCE_LOGGING FOR A10
SELECT OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, FORCE_LOGGING, DATAGUARD_BROKER FROM V$DATABASE;
SELECT THREAD#,MAX(SEQUENCE#) FROM V$ARCHIVED_LOG GROUP BY THREAD#;

SET LINES 200 PAGES 999
COL DEST_NAME FOR A20
COL STATUS FOR A10
COL DESTINATION FOR A18
COL ERROR FOR A20
COL ALTERNATE FOR A20
COL DESTINATION FOR A30
select dest_id,dest_name,status,target,destination,error,alternate from v$archive_dest where DESTINATION is not null;

###检查日志应用状态
select thread#,sequence#,applied,archived,deleted,status from v$archived_log order by 1,2;

###检查GAP
SQL> select thread#,low_sequence#,high_sequence# from v$archive_gap;   


#DG主库切换状态查询
select OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, FORCE_LOGGING, DATAGUARD_BROKER from v$database;

#DG主库切换为备库
select OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, FORCE_LOGGING, DATAGUARD_BROKER from gv$database;

ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY WITH SESSION SHUTDOWN;
复制

testdbpri:

#RAC库切换为主库
set lines 200 pages 999
select database_role,name,SWITCHOVER_STATUS from v$database;   -- switchover_status 应为To Primary

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

#需关闭数据库，重新启动，并确认RAC节点是否存在报错
Oracle:
Srvctl stop database -d testdb

Srvctl start database -d testdb

SQL> select database_role,name,SWITCHOVER_STATUS from v$database;
select dest_id,dest_name,status,target,destination,error,alternate from v$archive_dest where DESTINATION is not null;
复制

testdbsb:

#DG启库，启动实时日志应用
Startup mount;
alter database recover managed standby database disconnect from session;

#检查DG日志同步状态

SET LINES 200 PAGES 999
COL FORCE_LOGGING FOR A10
SELECT OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, FORCE_LOGGING, DATAGUARD_BROKER FROM V$DATABASE;
SELECT THREAD#,MAX(SEQUENCE#) FROM V$ARCHIVED_LOG GROUP BY THREAD#;

SET LINES 200 PAGES 999
COL DEST_NAME FOR A20
COL STATUS FOR A10
COL DESTINATION FOR A18
COL ERROR FOR A20
COL ALTERNATE FOR A20
COL DESTINATION FOR A30
select dest_id,dest_name,status,target,destination,error,alternate from v$archive_dest where DESTINATION is not null;

###检查日志应用状态
select thread#,sequence#,applied,archived,deleted,status from v$archived_log order by 1,2;

###检查GAP
SQL> select thread#,low_sequence#,high_sequence# from v$archive_gap;   

######如需要，开启实时日志应用
alter database recover managed standby database cancel;

alter database recover managed standby database using current logfile disconnect from session;
复制

testdbsa:

#ADG 如异常停止恢复，再手动启动恢复,并检查日志应用和gap情况
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

#检查DG日志同步状态

SET LINES 200 PAGES 999
COL FORCE_LOGGING FOR A10
SELECT OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, FORCE_LOGGING, DATAGUARD_BROKER FROM V$DATABASE;
SELECT THREAD#,MAX(SEQUENCE#) FROM V$ARCHIVED_LOG GROUP BY THREAD#;

SET LINES 200 PAGES 999
COL DEST_NAME FOR A20
COL STATUS FOR A10
COL DESTINATION FOR A18
COL ERROR FOR A20
COL ALTERNATE FOR A20
COL DESTINATION FOR A30
select dest_id,dest_name,status,target,destination,error,alternate from v$archive_dest where DESTINATION is not null;

###检查日志应用状态
select thread#,sequence#,applied,archived,deleted,status from v$archived_log order by 1,2;

###检查GAP
SQL> select thread#,low_sequence#,high_sequence# from v$archive_gap;   
复制

3.2 应急切换方案

3.2.1 同城ADG切换为主库

同城ADG切换为主库，异地DG从同城ADG应用日志
该方案适用于RAC已经宕机，同城ADG需强行升级为主库，对应用提供服务

#确定主库是否可以启到mount状态
#若可以,主库执行如下命令，数据不会丢
select open_mode from v$database; 
alter system flush redo to ‘testdbsb’;
#或可通过拷贝主库备份，再在备库注册
alter database register physical logfile 'filepath';
#若主库不可以启动到mount，会丢失Redolog部分未同步数据
#异地 DG确认同步状态

SET LINES 200 PAGES 999
COL FORCE_LOGGING FOR A10
SELECT OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, FORCE_LOGGING, DATAGUARD_BROKER FROM V$DATABASE;
SELECT THREAD#,MAX(SEQUENCE#) FROM V$ARCHIVED_LOG GROUP BY THREAD#;

SET LINES 200 PAGES 999
COL DEST_NAME FOR A20
COL STATUS FOR A10
COL DESTINATION FOR A18
COL ERROR FOR A20
COL ALTERNATE FOR A20
COL DESTINATION FOR A30
select dest_id,dest_name,status,target,destination,error,alternate from v$archive_dest where DESTINATION is not null;

###检查日志应用状态
select thread#,sequence#,applied,archived,deleted,status from v$archived_log order by 1,2;

###检查GAP
SQL> select thread#,low_sequence#,high_sequence# from v$archive_gap;  

recover managed standby database cancel; 
alter database recover managed standby database finish;
alter database activate standby database;
#查看切换状态
select OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, FORCE_LOGGING, DATAGUARD_BROKER from gv$database;
#重启DG库
shutdown immediate;
startup
#可能需要修改was等配置

复制

四、 ADG运维命令

4.1日常维护操作

4.1.1 检查日志应用


#检查配置状态
select * from v$dataguard_config;

#检查日志应用情况
select group#,thread#,sequence#,status,first_change#,next_change# from v$standby_log;
select thread#,sequence#,applied,archived from v$archived_log;

#检查有无报错信息
set linesize 200
col dest_name for a20
col status for a10
col destination for a18
col error for a50
col ALTERNATE for a20
col DESTINATION for a30
select dest_id,dest_name,status,target,destination,error,alternate from v$archive_dest where DESTINATION is not null;
复制

4.1.2 更改DG安全模式

Primary DB:
SQL> select INST_ID,name,database_role,protection_mode,protection_level from gv$database;

   INST_ID NAME      DATABASE_ROLE    PROTECTION_MODE      PROTECTION_LEVEL
---------- --------- ---------------- -------------------- --------------------
         2 COREDB    PRIMARY          MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE
         1 COREDB    PRIMARY          MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

SQL> show parameters LOG_ARCHIVE_DEST_2;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string      SERVICE=coredbstb LGWR VALID_F
                                                 OR=(ONLINE_LOGFILES,PRIMARY_RO
                                                 LE) DB_UNIQUE_NAME=coredbstb
SQL> select inst_id,dest_name,DELAY_MINS,NET_TIMEOUT,TRANSMIT_MODE,AFFIRM from gv$archive_dest where dest_id=2;

   INST_ID DEST_NAME            DELAY_MINS NET_TIMEOUT TRANSMIT_MOD AFF
---------- -------------------- ---------- ----------- ------------ ---
         2 LOG_ARCHIVE_DEST_2            0          30 PARALLELSYNC YES
         1 LOG_ARCHIVE_DEST_2            0          30 PARALLELSYNC YES

SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;
SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;

-- Maximum Protection.
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION;
复制

4.1.3 开启/取消延时应用

延迟30分钟应用

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DELAY 30 DISCONNECT FROM SESSION;
复制

取消延迟应用

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY DISCONNECT FROM SESSION;
复制

查看日志的应用情况

SQL> select first_time,next_time,applied,sequence# from v$archived_log order by sequence#;
复制

4.1.4 主备切换（switchover）

主端操作：
注：如果是RAC环境，需要保留一个instance是活动的，其它的instance需关闭。
例如，以下为2node节点，保留node1的实例，关闭node2

set linesize 200
col HOST_NAME for a30
connect sys/oracle@coredb2 as sysdba
select instance_name,host_name from gv$instance where inst_id <> (select instance_number from v$instance);
SQL> shutdown immediate;
SQL> exit
复制

只保留一个节点实例为打开的模式，然后按照如下操作：

#检查主备数据库的状态
$ sqlplus / as sysdba
set linesize 200
col dest_name for a20
col status for a10
col destination for a18
col error for a50
col ALTERNATE for a20
col DESTINATION for a20
select dest_id,dest_name,status,target,destination,error,alternate from v$archive_dest where DESTINATION is not null;

select dest_id,dest_name,status,target,destination,error,alternate from v$archive_dest where DESTINATION is not null;

#确认数据库是否适合进行switchover 
SQL> select inst_id,database_role,switchover_status,name from gv$database;
alter database switchover to prdsupstb verify;

#进行数据库的switchover的切换，并开启日志应用
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY;
SQL> STARTUP NOMOUNT;
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
select dest_id,dest_name,status,target,destination,error,alternate from v$archive_dest where DESTINATION is not null;
 
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
复制

备端操作：

#把备端切换为主
set linesize 200
select database_role,name,SWITCHOVER_STATUS from v$database;
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

#需关闭数据，重起启动，并确认ADG节点是否存在报错
SQL> SHUTDOWN IMMEDIATE;
SQL> startup
SQL> select database_role,name,SWITCHOVER_STATUS from v$database;
select dest_id,dest_name,status,target,destination,error,alternate from v$archive_dest where DESTINATION is not null;

4.1.5 Snapshot Standby（适合对数据库进行DML更改操作的测试，测试完成后，测试的数据将不保存）
1.	shutdown immediate;
2.	startup mount
3.	alter database convert to snapshot standby;
4.	shutdown immediate
5.	startup nomount
6.	alter database mount standby database;
7.	recover managed standby database disconnect;
8.	select flashback_on from v$database;

SQL> recover managed standby database cancel;
Media recovery complete.
SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

SQL> alter database convert to snapshot standby;

Database altered.

SQL> show parameters flash

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flash_cache_file                  string
db_flash_cache_size                  big integer 0
db_flashback_retention_target        integer     1440
SQL> show parameters recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/oradata/coredb
db_recovery_file_dest_size           big integer 10G
recovery_parallelism                 integer     0

SQL> select database_role,open_mode from v$database;

DATABASE_ROLE    OPEN_MODE
---------------- --------------------
SNAPSHOT STANDBY MOUNTED

SQL> alter database open;

Database altered.

SQL> select database_role,open_mode from v$database;

DATABASE_ROLE    OPEN_MODE
---------------- --------------------
SNAPSHOT STANDBY READ WRITE

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
RESTORE POINT ONLY

转化成snapshot standby后，所有更改的数据在转换成 physical standby后，将全部丢失。

SQL> connect tom/tom
Connected.
SQL> select * from test;

        ID NAME
---------- ------------
         1 tom
         2 ken
         3 ryan
         4 tony
         5 sammi
         6 ruby
         7 magic

7 rows selected.

SQL> insert into test(8,'aaa');
insert into test(8,'aaa')
                 *
ERROR at line 1:
ORA-00928: missing SELECT keyword


SQL> insert into test values(8,'aaa');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test;

        ID NAME
---------- ------------
         8 aaa
         1 tom
         2 ken
         3 ryan
         4 tony
         5 sammi
         6 ruby
         7 magic

8 rows selected.

SQL> create table test2(num number(3),name varchar2(10),age number(3));

Table created.

SQL> insert into test2 values(1,'tom',33);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test2;

       NUM NAME              AGE
---------- ---------- ----------
         1 tom                33


转换为physical standby:

SQL> connect / as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 3941736448 bytes
Fixed Size                  2259280 bytes
Variable Size            2181039792 bytes
Database Buffers         1744830464 bytes
Redo Buffers               13606912 bytes
Database mounted.
SQL> alter database convert to physical standby;

Database altered.

SQL> shutdown immeidate;
SP2-0717: illegal SHUTDOWN option
SQL> shutdown immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 3941736448 bytes
Fixed Size                  2259280 bytes
Variable Size            2181039792 bytes
Database Buffers         1744830464 bytes
Redo Buffers               13606912 bytes
SQL> alter database mount standby database;

Database altered.

SQL> alter database open;

Database altered.
之前的数据已经不存在：
SQL> select * from tom.test;

        ID NAME
---------- ------------
         1 tom
         2 ken
         3 ryan
         4 tony
         5 sammi
         6 ruby
         7 magic

7 rows selected.

SQL> select * from tom.test2;
select * from tom.test2
                  *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> recover managed standby database using current logfile disconnect from session;
Media recovery complete.
SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO
复制

4.2 异常维护操作

4.2.1 Failover

Primary:

select open_mode from v$database; --如果主库可以启动到mount状态
alter system flush redo to ‘coredbsa’;
复制

Standby:

select thread#,low_sequence#,high_sequence# from v$archive_gap; --如没有gap,数据不会丢

无gap:
1. recover managed standby database cancel; 
2. alter database recover managed standby database finish;
3. alter database activate standby database;
复制

4.2.2 GAP问题处理

1.将对应的归档文件copy到standby，进行注册
Select unique thread# as thread,max(sequence#) over(partition by thread#) as LAST from v$archived_log;
2.注册归档日志
alter database register physical logfile ‘filespec1’;
select thread#,low_sequence#,high_sequence# from v$archive_gap; --如还有gap
3.将主库所有的online redo copy到standby
4.stop redo apply
recover managed standby database cancel; 
5.恢复备库
recover standby database until cancel;
--手动输入online redo
6.激活备库
alter database recover managed standby database finish;
alter database activate standby database;
7.重启数据库
shutdown immediate;
startup;
复制

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

ORACLE 19C ADG 手动切换

ORACLE 19C ADG& DG切换

一、环境说明

二、主备配置

2.1 相关参数配置

2.2 TNSNAMES配置

2.3 CRS状态检查

2.4 同步状态检查

三、切换方案概述

3.1正常切换演练

3.1.1 同城ADG切换为主库

3.1.2 同城ADG回切为备库

3.1.3 异地DG切换为主库

3.1.4 异地DG回切为备库

3.2 应急切换方案

3.2.1 同城ADG切换为主库

四、 ADG运维命令

4.1日常维护操作

4.1.1 检查日志应用

4.1.2 更改DG安全模式

4.1.3 开启/取消延时应用

4.1.4 主备切换（switchover）

4.2 异常维护操作

4.2.1 Failover

4.2.2 GAP问题处理

评论

相关阅读

ORACLE 19C ADG 手动切换

ORACLE 19C ADG& DG切换

一、 环境说明

二、 主备配置

2.1 相关参数配置

2.2 TNSNAMES配置

2.3 CRS状态检查

2.4 同步状态检查

三、 切换方案概述

3.1正常切换演练

3.1.1 同城ADG切换为主库

3.1.2 同城ADG回切为备库

3.1.3 异地DG切换为主库

3.1.4 异地DG回切为备库

3.2 应急切换方案

3.2.1 同城ADG切换为主库

四、 ADG运维命令

4.1日常维护操作

4.1.1 检查日志应用

4.1.2 更改DG安全模式

4.1.3 开启/取消延时应用

4.1.4 主备切换（switchover）

4.2 异常维护操作

4.2.1 Failover

4.2.2 GAP问题处理

评论

相关阅读

一、环境说明

二、主备配置

三、切换方案概述