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;