ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle data guard常用维护操作命令(转)

Oracle data guard常用维护操作命令(转)

原创 Linux操作系统 作者:wdnmg 时间:2011-03-22 11:38:19 0 删除 编辑

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.


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/70109/viewspace-690142/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-01-31

  • 博文量
    53
  • 访问量
    144554