DBA_LOGSTDBY_EVENTS视图记录了逻辑STANDBY的操作日志,该视图默认的只记录100条信息
SQL> select event_time,status from dba_logstdby_events;
EVENT_TIM STATUS
--------- ------------------------------------------------------------
08-MAR-14 ORA-16111: log mining and apply setting up
08-MAR-14 ORA-16128: User initiated stop apply successfully completed
08-MAR-14 ORA-16111: log mining and apply setting up
08-MAR-14 ORA-16128: User initiated stop apply successfully completed
08-MAR-14 ORA-16111: log mining and apply setting up
08-MAR-14 ORA-16128: User initiated stop apply successfully completed
08-MAR-14 ORA-16111: log mining and apply setting up
08-MAR-14 ORA-16128: User initiated stop apply successfully completed
08-MAR-14 ORA-16111: log mining and apply setting up
08-MAR-14 ORA-16128: User initiated stop apply successfully completed
08-MAR-14 ORA-16111: log mining and apply setting up
修改该视图保留记录的最大条数
修改前需关闭SQL应用
STANDBY > alter database stop logical standby apply;
Database altered.
STANDBY > EXEC DBMS_LOGSTDBY.APPLY_SET('MAX_EVENTS_RECORDED','200');
PL/SQL procedure successfully completed.
启动SQL应用并查看保留条数
STANDBY > alter database start logical standby apply immediate;
Database altered.
STANDBY > select * from v$logstdby_stats where name like '%recorded%' ;
NAME VALUE
---------------------------------------- ------------------------------
maximum events recorded 200
DBA_LOGSTDBY_LOG视图显示归档日志的SQL应用情况
SQL> select SEQUENCE#,FIRST_CHANGE#,NEXT_CHANGE#,APPLIED from dba_logstdby_log;
SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# APPLIED
---------- ------------- ------------ --------
14 494521 494573 CURRENT
15 494573 545758 NO
16 545758 572067 NO
上面的结果中出现应用结果为NO的情况,是因为在数据库重启期间SQL应用未开启,开启SQL应用即可
SQL> alter database start logical standby apply immediate;
Database altered.
稍等片刻之后
SQL> select SEQUENCE#,FIRST_CHANGE#,NEXT_CHANGE#,APPLIED from dba_logstdby_log;
SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# APPLIED
---------- ------------- ------------ --------
14 494521 494573 CURRENT
15 494573 545758 CURRENT
16 545758 572067 NO
SQL> select SEQUENCE#,FIRST_CHANGE#,NEXT_CHANGE#,APPLIED from dba_logstdby_log;
SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# APPLIED
---------- ------------- ------------ --------
16 545758 572067 CURRENT
最后完成SQL应用完全完成后发现少了几组重做日志信息,是因为默认情况下stanbyd数据库SQL应用之后自动将废弃的归档文件删除,当然,也可以通过设置相关参数使之保留
取消自动删除归档文件设置:
SQL> exec DBMS_LOGSTDBY.APPLY_SET('LOG_AUTO_DELETE', 'FALSE');
PL/SQL procedure successfully completed.
查看一下修改后的standby参数
SQL> select * from dba_logstdby_parameters;
NAME VALUE
-------------------- ------------------------------
PREP_DICT_RECEIVED
LMNR_SID 1
GUARD_STANDBY READY
FIRST_SCN 491619
PRIMARY 1369377775
APPLY_SCN 492166
LOG_AUTO_DELETE FALSE
此时在primary端多切换几次日志,再在standby查看日志的SQL应用情况,会发现被应用过的归档日志APPLIED的值设置为YES,而非删除
PRIMARY > alter system switch logfile;
System altered.
PRIMARY > alter system switch logfile;
System altered.
SQL> set sqlprompt 'STANDBY > '
STANDBY > select SEQUENCE#,FIRST_CHANGE#,NEXT_CHANGE#,APPLIED from dba_logstdby_log;
SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# APPLIED
---------- ------------- ------------ --------
18 573112 573168 YES
19 573168 574263 YES
20 574263 574486 CURRENT
手动删除过期的归档日志
首先需要对归档日志进行检查
STANDBY > exec DBMS_LOGSTDBY.PURGE_SESSION;
PL/SQL procedure successfully completed.
该过程会将不需要的日志标记在DBA_LOGMNR_PURGED_LOG中
STANDBY > select * from DBA_LOGMNR_PURGED_LOG;
FILE_NAME
--------------------------------------------------------------------------------
/u01/log/arch_pri_log/1_18_841597045.dbf
/u01/log/arch_pri_log/1_19_841597045.dbf
在操作系统中将不需要的日志删除即可(具体是否丢弃要根据自己的需求确定)
STANDBY > !rm -rf /u01/log/arch_pri_log/1_18_841597045.dbf
STANDBY > select * from DBA_LOGMNR_PURGED_LOG;
FILE_NAME
--------------------------------------------------------------------------------
/u01/log/arch_pri_log/1_19_841597045.dbf
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29320885/viewspace-1104402/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29320885/viewspace-1104402/