logical standby DG同步錯誤問題總結

--查詢日誌是否在應用
select sequence#,first_change#,next_change#,timestamp,thread#,applied from
 dba_logstdby_log where applied<>'YES'order by 1

--檢視相關事件
 SELECT EVENT_TIME,EVENT,XIDUSN, XIDSLT, XIDSQN,status FROM DBA_LOGSTDBY_EVENTS order by 1 desc;

--檢視錯誤資訊，留意詳細資訊
--如ORA-16240: 等待日誌檔案 (thread# 2, sequence# 62159)
--提示ORA-00001: 違反唯一約束條件，刪除備庫相關表
select * from v$logstdby_process

--查詢本地是否存在日誌檔案
 select * from dba_logstdby_log WHERE SEQUENCE# BETWEEN 62159 AND 62334

--檢視主庫有無對應的歸檔日誌，並且檔案是否還存在，或已備份
select * from v$archived_log where sequence# between 62159 and 62334

select * from V$BACKUP_ARCHIVELOG_DETAILS where sequence# between 62159 and 62334

--檢視當前應用程式
Select ls.serial# "Apply Process",sas.state "State",sas.SID SID,s.sql_address "“SQL Address",s.sql_hash_value "SQL Hash Value",
sa.sql_text "SQL Text" from v$logstdby ls,v$streams_apply_server sas,v$session s,v$sqlarea sa
 where ls.type='APPLIER' AND sas.state!='IDLE' AND sas.serial#=ls.serial# AND s.SID=sas.SID AND sa.address(+)=s.sql_address
AND sa.hash_value(+)=s.sql_hash_value;

 

--跳過特定使用者表的DDL操作
SELECT 'EXEC DBMS_LOGSTDBY.SKIP(''SCHEMA_DDL'',''POS'','''||TABLE_NAME||''');' FROM USER_TABLES;

--增加LCR CACHE加快應用速度
execute dbms_logstdby.apply_set('max_sga',200);

--設定COMMIT是否與主庫順序一致
EXEC DBMS_LOGSTDBY.APPLY_SET('PRESERVE_COMMIT_ORDER',FALSE)

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/429786/viewspace-776199/，如需轉載，請註明出處，否則將追究法律責任。