Oracle data guard常用维护操作命令

Data Guard是Oracle提供的一种高可用性解决方案，用于数据保护和容灾，通过日志同步来把数据及时传送到备用节点，现总结一下Data Guard环境下常用的维护命令：

1、在生产库停止Data Guard操作：

SQL> show parameter log_archive_dest
SQL> alter system set log_archive_dest_state_2=defer;

2、在生产库开启Data Guard操作：

SQL> alter system set log_archive_dest_state_2=enable;

3、在备份库查看生产库传过来的归档应用情况

SELECT FILE_NAME, SEQUENCE# AS SEQ#, FIRST_CHANGE# AS FCHANGE#,NEXT_CHANGE# AS NCHANGE#, TIMESTAMP,DICT_BEGIN AS BEG, DICT_END AS END,THREAD# AS THR# , APPLIED FROM DBA_LOGSTDBY_LOG ORDER BY THREAD#,SEQUENCE#;

4、在备份库查看应用事件

SELECT EVENT_TIME,EVENT,XIDUSN, XIDSLT, XIDSQN FROM DBA_LOGSTDBY_EVENTS order by 1 desc;

可跳过某些引起阻塞的DDL或DML应用，然后手工执行这些应用：

alter database stop logical standby apply;
exec dbms_logstdby.skip_transaction(14,21,517969);
ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

5、在备份库跳过特定的DML或DDL操作

alter database stop logical standby apply;
execute dbms_logstdby.skip (stmt => 'DML', schema_name => 'BANPING',object_name => 'TABLENAME', proc_name => null);
alter database start logical standby apply;

由于oracle的bug，10203版本开始应用后会报以下错误：

ORA-00604: error occurred at recursive SQL level 1
ORA-01425: escape character must be character string of length 1

此时执行这个SQL语句可解决：

SQL> update system.logstdby$skip set esc = '\' where esc is NULL;
SQL> commit;

6、重新初始化表

alter database stop logical standby apply;
execute dbms_logstdby.unskip('DML','BANPING','TABLENAME');
exec dbms_logstdby.instantiate_table('BANPING','TABLENAME','dblink_name');
alter database start logical standby apply;

注意这里建立的DBLINK dblink_name必须是public的，否则会报以下错误：

ORA-39001: invalid argument value
ORA-06512: at "SYS.DBMS_LOGSTDBY", line 577
ORA-06512: at line 1

7、在备库查看日志应用状态和进度：

select * from V$LOGSTDBY_STATE
select * from V$LOGSTDBY_PROGRESS

8、在备库手动注册归档日志

SQL> ALTER DATABASE REGISTER LOGICAL LOGFILE '/stb_arch/1_143313_640266118.dbf';
Database altered.