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