Oracle DG主备架构常用命令总结1、备库日志应用相关命令备库上执行 1主库切换日志时才应用同步 alter database recover managed standby database disconnect from session; 2实时日志应用方式 alter database recover managed standby database using current logfile disconnect from session; 3取消日志应用 alter database recover managed standby database cancel;2、DG保护模式的设置语法主库上执行 1最大保护模式 Alter database set standby to maximize protection; 2最大可用模式 Alter database set standby to maximize availability; 3最大性能模式 Alter database set standby to maximize performance;3、DG正常切换switchover相关命令1主库切换备库命令 Alter database commit to switchover to standby with session shutdown; 2备库切换主库命令 alter database commit to switchover to primary with session shutdown ;4、DG异常切换failover相关命令1将主库中所有未到备库的归档日志和redo刷新到备库命令主库必须mount状态执行 ALTER SYSTEM FLUSH REDO TO target_db_name; 备注target_db_name:用于指定目标备库的DB_UNIQUE_NAME必须使用单引号括起来 2failover常规切换命令常规切换不行、报错就只能强制切换 alter database recover managed standby database finish force; alter database commit to switchover to primary with session shutdown; 3failover强制切换命令会丢失一部分数据 alter database activate physical standby database; 412c版本failover常规切换命令 ALTER DATABASE FAILOVER TO targe_db_name; ALTER DATABASE FAILOVER TO targe_db_name FORCE; 512c版本failover强制切换命令会丢失一部分数据 alter database activate physical standby database5、DG主备架构中的常用查询语句1DG主备架构中核心参数的查询语句set lines 500 pages 999 col value for a90 col name for a50 select name,value from v$parameter where name in(db_name,db_unique_name, log_archive_config, log_archive_dest_1,log_archive_dest_2, log_archive_max_processes, fal_server,fal_client, db_file_name_convert, log_file_name_convert, standby_file_management);2强制日志force logging功能和归档是否开启的查询语句SELECT NAME, LOG_MODE,FORCE_LOGGING FROM V$DATABASE;3DG主备架构中DB_UNIQUE_NAME查询语句SELECT DB_UNIQUE_NAME FROM V$DATAGUARD_CONFIG;4redo和standby日志文件的查询语句select group#,thread#,bytes from v$standby_log; select group#,thread#,bytes from v$log;5查看数据库基本信息的语句查询数据库角色、打开模式、保护模式和保护级别select open_mode,database_role,protection_level,protection_mode,switchover_status,force_logging from v$database; 备注根据SWITCHOVER_STATUS列判断是否可以切换TO PRIMARY和active session表示可以进行切换6查看备库恢复模式确认备库是否开启了实时应用REAL TIME APPLY方法一主库上查询 SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID2; 方法二备库上查询 select open_mode,database_role,protection_level,protection_mode,switchover_status,force_logging from v$database;7DG主备同步情况检查从归档日志应用情况来检查主备同步情况,主库上查询 set linesize 20o pagesize 999 col standby_tns_name format a30 select inst_id,max(sequence#),max(name) standby_tns_name from gv$archived_log where standby_destYES and appliedYES group by inst_id order by 1; select inst_id,max(sequence#) from gv$archived_log where standby_destNO group by inst_id order by 1;8归档志有没有gap的查询语句备库上执行select * from v$archive_gap; select inst_id,max(sequence#) ,min(sequence#) from gv$archived_log where appliedNO group by inst_id order by 1;9归档目标状态的查询语句主库上执行set linesize 200 pagesize 999 col dest_name format a20 SELECT dest_name,status,type,database_mode,recovery_mode FROM v$archive_dest_status WHERE status INACTIVE;10归档传输错误的查询语句主库上执行col destination format a20 SELECT dest_id, destination, status, error, fail_sequence FROM v$archive_dest WHERE status INACTIVE and error IS NOT NULL;11查看DG状态事件查看DG的告警和状态消息用于排查错误COLUMN MESSAGE FORMAT A100 SELECT MESSAGE_NUM, MESSAGE FROM V$DATAGUARD_STATUS ORDER BY TIMESTAMP;12集群环境中判断MPR日志应用进程在哪个节点的查询语句备库上执行SELECT INST_ID,PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM GV$MANAGED_STANDBY where PROCESS like %MRP%; 备注在12c及更高版本中V$MANAGED_STANDBY视图已被V$DATAGUARD_PROCESS取代。13查看DG进程状态在主库执行查看ARCH归档和LNS日志网络服务进程 SELECT INST_ID,PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM GV$MANAGED_STANDBY ; 在备库执行查看RFS远程文件服务接收日志和MRP0管理恢复进程应用日志进程。 SELECT INST_ID,PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM GV$MANAGED_STANDBY ; 备注在12c及更高版本中V$MANAGED_STANDBY视图已被V$DATAGUARD_PROCESS取代。14手工注册缺失的归档日志命令ALTER DATABASE REGISTER PHYSICAL LOGFILE 归档日志文件绝对路径和名称;15DGMGRL命令行工具常用语句连接 dgmgrl sys/passworddb_unique_name 查看整体配置 SHOW CONFIGURATION; 查看数据库状态 SHOW DATABASE database_name; 验证配置 VALIDATE DATABASE database_name;