oracle logical standby 日常管理常用的一些語句

// 執行跳過，跳過的結果在

execute dbms_logstdby.skip(stmt => 'DML',schema_name => '%', object_name => '%');

stmt的取值可以是：

http://download-west.oracle.com/docs/cd/B14117_01/appdev.101/b10802/d_lsbydb.htm#997290

// 跳過的内容記載在下面

select * from dba_logstdby_skip

// 停止apply

alter database stop logical standby apply;

alter database abort logical standby apply;

// 執行apply

alter database start logical standby apply;

// 實時apply

alter database start logical standby apply immediate;

// 跳過錯誤，在dba_logstdby_skip表中，ERROR列為Y

execute dbms_logstdby.skip_error('NON_SCHEMA_DDL');

// 執行apply，跳過失敗的事務

alter database start logical standby apply skip failed transaction;

// 設定參數，是否記錄跳過錯誤

exec dbms_logstdby.apply_set('RECORD_SKIP_ERRORS','FALSE');

// 設定參數，是否記錄跳過DDL

exec dbms_logstdby.apply_set('RECORD_SKIP_DDL','FALSE');

// 在備庫上關掉dataguard，備庫可寫

alter database guard none;

// 在備庫上啟用dataguard，備庫不可寫

alter database guard all;

// 官方文檔

http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10823/toc.htm

//執行某個表不通過，手工同步表

alter database stop logical standby apply;

// 建立DBLINK指向主庫，然後同步建立表

exec dbms_logstdby.instantiate_table('EYGLE','SALES','dblink_name');

alter database start logical standby apply;

// 手工添加備庫的日志

$ cp /u01/arch/WENDING/1_22751_666200636.arc /u04/arch/WDSTD/log_1_22751_666200636.arc

SQL> alter database register logical logfile '/u04/arch/WDSTD/log_1_22751_666200636.arc';

or

SQL> alter database register or replace logical logfile '/u04/arch/WDSTD/log_1_22751_666200636.arc';

//檢視最後的進度

select LATEST_SCN,MINING_SCN,APPLIED_SCN,LATEST_TIME,MINING_TIME,APPLIED_TIME from V$LOGSTDBY_PROGRESS;

// 監控同步進度的腳本

SELECT * FROM dba_logstdby_log;

select * from dba_logstdby_events order by event_time desc;

select LATEST_SCN,MINING_SCN,APPLIED_SCN,LATEST_TIME,MINING_TIME,APPLIED_TIME from V$LOGSTDBY_PROGRESS;

select LOGSTDBY_ID,type,status process_status from v$logstdby_process;

select * from v$logstdby_state;

select * from v$dataguard_status order by timestamp desc;

// 增加apply的程序數

ALTER DATABASE STOP LOGICAL STANDBY APPLY; --- Stop SQL Apply

EXECUTE DBMS_LOGSTDBY.APPLY_SET('APPLY_SERVERS', 20); --- 調整apply程序數為20，預設為5個

ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE; --- Start real-time Apply

// 停止apply時，如果目前正在應用，會等待執行後才停止

// 下面的指令可以重複執行，如果執行提示stop，則意味着正在apply還沒有結束，等結束後重新執行即可

ALTER DATABASE START LOGICAL STANDBY APPLY;