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;