Oracle DG运维常用SQL

=====运维SQL --查看switch状态 select switchover_status from v$database; --查看数据库状态  set lin 200 pages 100  select current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from v$database; --查看进程状态 set lin 200 pages 200 select process,status,thread#,sequence#,block#,blocks from v$managed_standby; --查看备库是否应用日志  select sequence#,applied,standby_dest from gv$archived_log; select * from ( select sequence#,applied,standby_dest from v$archived_log order by sequence# asc) where rownum<20; --查看归档目录错误 col dest_name for a30 col error for a50 set lin 200 pages 100 select dest_id,error,status,log_sequence,applied_scn,MAX_CONNECTIONS,NET_TIMEOUT,COMPRESSION from v$archive_dest where dest_id<5; --查看数据库参数，11g以后不需要配置fal_client set linesize 500 pages 100 col value for a70  col name for a30  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_dest_state_1', 'log_archive_dest_3',  'log_archive_dest_state_3', 'log_archive_dest_state_2',  'remote_login_passwordfile',  'log_archive_format',  'log_archive_max_processes',  'fal_server','db_file_name_convert',  'log_file_name_convert',  'standby_file_management')  / --查看归档路径状态 col message for a80 select SEVERITY,error_code,to_char(timestamp,'yyyymmdd hh24:mi:ss') timestamps,message from v$dataguard_status; --查看standby日志  select * from V$STANDBY_LOG; --查看是否存在未应用的日志 SELECT * FROM V$ARCHIVE_GAP; --查看primary端归档目录监控 col destination for a60 SELECT DESTINATION, DEST_NAME,STATUS, ARCHIVED_THREAD#, ARCHIVED_SEQ# FROM V$ARCHIVE_DEST_STATUS WHERE STATUS <> 'DEFERRED' AND STATUS <> 'INACTIVE'; --查看日志状态  select group#,bytes/1024/1024 mb, members ,status from v$log; ===启动、停止复制 --启动备库到mount alter database mount standby database; --开始应用归档，12c以后存在standby log即为实时应用 alter database recover managed standby database disconnect from session; --取消应用归档 alter database recover managed standby database cancel; --启动redo apply， 12c以后取消USING CURRENT LOGFILE参数 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION; --取消redo apply alter database recover managed standby database cancel; =====问题处理 --手动注册归档日志 ALTER DATABASE REGISTER LOGFILE '/physical_standby1/thread1_dest/arcr_1_9.arc'; ====遗留问题 主备切换后检查temp表空间对应临时文件是否存在、路径是否正确。 ===常见拼接SQL --rename datafile select 'alter database rename file '''||name||''' to '''||name||''';' from v$datafile order by name; --rename logfile select 'alter database rename file '''||member||''' to '''||member||''';' from v$logfile order by member; ==dg 延迟监控 select to_char(SYSDATE,'yyyymmdd hh24:mi:ss') CTIME,NAME,VALUE,DATUM_TIME  from V$DATAGUARD_STATS SQL> / CTIME             NAME                           VALUE                                    DATUM_TIME ----------------- ------------------------------ ---------------------------------------- ------------------------------ 20180806 14:25:21 transport lag                  +00 00:00:00                             08/06/2018 14:25:19 20180806 14:25:21 apply lag                      +00 00:00:00                             08/06/2018 14:25:19 20180806 14:25:21 apply finish time              +00 00:00:00.000 20180806 14:25:21 estimated startup time         12 主要关注应用延迟即可，延迟时间为value + （sysdate-DATUM_TIME）.