Oracle DG運(yùn)維常用SQL

=====運(yùn)維SQL

--查看switch狀態(tài)

select switchover_status from v$database;

--查看數(shù)據(jù)庫(kù)狀態(tài)

 set lin 200 pages 100

 select current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from v$database;

--查看進(jìn)程狀態(tài)

set lin 200 pages 200

select process,status,thread#,sequence#,block#,blocks from v$managed_standby;

--查看備庫(kù)是否應(yīng)用日志

 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;

--查看歸檔目錄錯(cuò)誤

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;

--查看數(shù)據(jù)庫(kù)參數(shù)，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') 

/

--查看歸檔路徑狀態(tài)

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;

--查看是否存在未應(yīng)用的日志

SELECT * FROM V$ARCHIVE_GAP;

--查看primary端歸檔目錄監(jiān)控

col destination for a60

SELECT DESTINATION, DEST_NAME,STATUS, ARCHIVED_THREAD#, ARCHIVED_SEQ# FROM V$ARCHIVE_DEST_STATUS WHERE STATUS <> 'DEFERRED' AND STATUS <> 'INACTIVE';

--查看日志狀態(tài)

 select group#,bytes/1024/1024 mb, members ,status from v$log;

===啟動(dòng)、停止復(fù)制

--啟動(dòng)備庫(kù)到mount

alter database mount standby database;

--開始應(yīng)用歸檔，12c以后存在standby log即為實(shí)時(shí)應(yīng)用

alter database recover managed standby database disconnect from session;

--取消應(yīng)用歸檔

alter database recover managed standby database cancel;

--啟動(dòng)redo apply， 12c以后取消USING CURRENT LOGFILE參數(shù)

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

--取消redo apply

alter database recover managed standby database cancel;

=====問(wèn)題處理

--手動(dòng)注冊(cè)歸檔日志

ALTER DATABASE REGISTER LOGFILE '/physical_standby1/thread1_dest/arcr_1_9.arc';

====遺留問(wèn)題

主備切換后檢查temp表空間對(duì)應(yīng)臨時(shí)文件是否存在、路徑是否正確。

===常見拼接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 延遲監(jiān)控

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

主要關(guān)注應(yīng)用延遲即可，延遲時(shí)間為value + （sysdate-DATUM_TIME）.