Oracle 如何规范清理v$archived_log记录实例详解
Oracle如何规范清理v$archived_log记录实例详解
单机实例上面,v$archived_log很多,有上万条记录了,所以得清理一下,不然每次查询都直接滚屏幕了
SQL>selectsequence#,appliedfromv$archived_logorderbysequence#; SEQUENCE#APPLIED .................... SEQUENCE#APPLIED ------------------- 9376NO 9377NO 9377NO 9378NO 9378NO 9379NO 9379NO 9380NO 9380NO 9381NO 9381NO SEQUENCE#APPLIED ------------------- 9382NO 9382NO 11200rowsselected. SQL>
然后查看下当前的归档记录
SQL>archiveloglist; DatabaselogmodeArchiveMode AutomaticarchivalEnabled ArchivedestinationUSE_DB_RECOVERY_FILE_DEST Oldestonlinelogsequence164 Nextlogsequencetoarchive166 Currentlogsequence166 SQL>
看到归档记录才是164,和v$archived_log里面上W的记录数不匹配,这是因为这是rman备份恢复遗留下来的记录,所以需要清理一下。
清理记录,采用sys.dbms_backup_restore.resetCfileSection(11);清理:
SQL>executesys.dbms_backup_restore.resetCfileSection(11); PL/SQLproceduresuccessfullycompleted. SQL>selectsequence#,appliedfromv$archived_logorderbysequence#; norowsselected SQL>
再次测试,可以查看到日志记录变化了,v$archived_log已经是最新的,只有一条记录数存在了:
SQL>altersystemswitchlogfile; Systemaltered. SQL>selectsequence#,appliedfromv$archived_logorderbysequence#; SEQUENCE#APPLIED ------------------- 166NO SQL>executesys.dbms_backup_restore.resetCfileSection(11); PL/SQLproceduresuccessfullycompleted. SQL>selectsequence#,appliedfromv$archived_logorderbysequence#; norowsselected SQL>
扩展话题,单机实例可以用上,述办法操作,那么Oracle集群比如dg呢,分析master库、standby库
#master库上v$archived_log表记录数: SQL>selectcount(1)fromv$archived_log; COUNT(1) ---------- 623616 SQL> #standby库上v$archived_log表记录数: SQL>selectcount(1)fromv$archived_log; COUNT(1) ---------- 2226823 SQL>
感谢阅读,希望能帮助到大家,谢谢大家对本站的支持!