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 hh34: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 hh34: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）.
