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;
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
