# 事件简介 本以为 DG 是个很简单的工作，结果因为环境的原因，踩了很多坑，所以总结了一下经验，特意整理了一份完整版操作手册 # 环境检查 ### 数据库版本 ```sql opatch lsinventory SQL> SELECT PATCH_ID, ACTION, ACTION_TIME FROM DBA_REGISTRY_SQLPATCH; ``` ### 参数文件 ``` show parameter LOG_ARCHIVE_CONFIG; show parameter LOG_ARCHIVE_DEST; show parameter LOG_ARCHIVE_DEST_STATE; show parameter FAL; show parameter CONVERT; show parameter STANDBY_FILE_MANAGEMENT; ``` # network - TNS ```shell cat $ORACLE_HOME/network/admin/tnsnames.ora ``` - sqlnet ```shell cat $ORACLE_HOME/network/admin/sqlnet.ora ``` ### Log File - Standby Redo ```sql col member for a60 SELECT L.THREAD#, L.GROUP#, L.BYTES / 1024 / 1024 SIZE_M, L.STATUS, LF.MEMBER, LF.STATUS FROM V$STANDBY_LOG L INNER JOIN V$LOGFILE LF ON L.GROUP# = LF.GROUP# ORDER BY L.THREAD#, L.GROUP#; ``` - Redo - 查看文件是否存在 ```sql col member for a60 SELECT L.THREAD#, L.GROUP#, L.BYTES / 1024 / 1024 SIZE_M, L.STATUS, LF.MEMBER, L.MEMBERS, LF.STATUS FROM V$LOG L INNER JOIN V$LOGFILE LF ON L.GROUP# = LF.GROUP# ORDER BY L.THREAD#, L.GROUP#; ``` - redo 文件重命名 避免路径不存在，还可以规范命名 ```sql alter database rename file '/oracle/app/oracle/fast_recovery_area/SIPDS/onlinelog/o1_mf_1_b1p5k81l_.log' to '/oracle/app/oracle/fast_recovery_area/SIPDS/onlinelog/redo01.log'; ``` ### TempFile - 查看文件是否存在 ```sql col "TableSpace Name" for a30 col "File Name" for a55 col "Size (MB)" for a20 col "Used (MB)" for a20 SELECT A.TABLESPACE_NAME "TableSpace Name", A.FILE_NAME "File Name", A.STATUS "Status", A.AUTOEXTENSIBLE "Auto", TO_CHAR(NVL(A.BYTES / 1024 / 1024, 0), '99G999G990D900') "Size (MB)", TO_CHAR(NVL(A.BYTES - NVL(F.BYTES, 0), 0) / 1024 / 1024, '99G999G990D900') "Used (MB)", TO_CHAR(NVL(A.BYTES - NVL(F.BYTES, 0), 0) / 1024 / 1024, '99G999G990D900') "Max (MB)", TO_CHAR(NVL((A.BYTES - NVL(F.BYTES, 0)) / A.BYTES * 100, 0), '990D00') "Used%" FROM DBA_TEMP_FILES A, (SELECT FILE_ID, SUM(BYTES) BYTES FROM DBA_FREE_SPACE GROUP BY FILE_ID) F WHERE A.FILE_ID = F.FILE_ID(+) order by "TableSpace Name", "File Name"; ``` - temp 表空间路径修改 ```sql alter tablespace temp add tempfile '<新路径>' size 2000M autoextend on maxsize 31G; alter tablespace temp drop tempfile '<旧路径>'; ``` ### RAC - 数据库启动状态和服务列表 ```shell srvctl config database -db <db_unique_name> ``` - 查看服务 ```shell srvctl config service -db <db_unique_name> -s <service_name> ``` ### Object - DB Link ```sql COLUMN owner FORMAT a15 HEADING 'Owner' COLUMN db_link FORMAT a25 HEADING 'DB Link Name' COLUMN username format a15 HEADING 'Username' COLUMN host format a130 trunc HEADING 'Host' COLUMN created FORMAT a20 HEADING 'Created' SELECT OWNER OWNER, DB_LINK, USERNAME, TO_CHAR(CREATED, 'mm/dd/yyyy HH24:MI:SS') CREATED, HOST FROM DBA_DB_LINKS ORDER BY OWNER, DB_LINK; ``` - JOB ```sql select * from DBA_JOBS_RUNNING; ``` - SCHEDULE ```sql select * from DBA_SCHEDULER_RUNNING_JOBS; ``` ### 同步状态 - 检查current sequence# ```sql select thread#, sequence#, status from v$log; ``` - 备库检查正在应用的 sequence# ```sql SELECT PROCESS, THREAD#, SEQUENCE#, STATUS FROM V$MANAGED_STANDBY; ``` - 备库检查是否存在 GAP ```sql SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP; ``` - 查看日志同步情况 ```sql select sequence#, applied from dba_logstdby_log; ``` - 延迟 ```sql select * from v$dataguard_stats ``` ### CDB 验证是否可以切换 ```sql ALTER DATABASE SWITCHOVER TO <standbydb_unique_name> VERIFY; ``` # 操作执行 ### 断开应用 - 关闭应用 - 关闭监听或者锁掉所有用户 ```sql select 'ALTER USER ' || USERNAME || 'ACCOUNT LOCK;' from dba_users where ACCOUNT_STATUS ='OPEN' and USERNAME not in ('SYS','SYSTEM'); ``` - 杀掉所有非本地链接 ```shell ps -ef|grep LOCAL=NO |grep <instance_name> |awk '{print $2}' |xargs kill -9 ``` - 杀掉剩余会话 ```sql SELECT 'alter system disconnect session ''' || sid || ',' || serial# || ''' immediate;' FROM v$session WHERE TYPE = 'USER'; ``` ### 切换 ##### 手动切换 - 主库操作 如果是rac，关闭除操作节点外的其它数据库 保证session只有此一个会话链接 - 关闭主库 ```sql alter database commit to switchover to physical standby with session shutdown; ``` - 开启standby模式 ```sql startup nomount; alter database mount standby database; alter database recover managed standby database disconnect from session; ``` - 备库操作 - 切换备库为主库 ```sql alter database commit to switchover to primary WITH SESSION SHUTDOWN; ``` - 开启数据库 如果是第一次切换，需做如下操作 ##### Broker 切换备机工作 ``` DGMGRL> switchover to <standbydb_unique_name>; ``` ##### CDB - 主库操作，进行切换 ```sql ALTER DATABASE SWITCHOVER TO <standbydb_unique_name>; ``` - 备库操作 - 启动至 mount ```sql SQL> shutdown immediate; SQL> STARTUP MOUNT; ``` - 打开数据库 ```sql SQL> ALTER DATABASE OPEN; SQL> alter pluggable database all open instances=all; ``` - 主库应用日志 ```sql SQL> startup; SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION; ``` # 结果确认 1. 验证角色 ```sql select database_role,switchover_status from v$database; ``` 2. 应用模式 ```sql SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID=2; ``` 3. 同步延迟 ```sql select * from v$dataguard_stats ``` 4. 归档确认 ```sql alter system switch logfile; select status,error from v$archive_dest; ``` 5. 服务确认 ```shell lsnrctl status ``` 6. 如果是rac，需要确认启动状态 ```shell srvctl status database -db <standbydb_unique_name> crsctl stat res -t ``` 7. 后台日志