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