Oracle 如何规范清理v$archived_log记录实例详解
程序员文章站
2022-07-06 17:01:57
oracle 如何规范清理v$archived_log记录实例详解
单机实例上面,v$archived_log 很多,有上万条记录了,所以得清理一下,不然每次查询都直接滚...
oracle 如何规范清理v$archived_log记录实例详解
单机实例上面,v$archived_log 很多,有上万条记录了,所以得清理一下,不然每次查询都直接滚屏幕了
sql> select sequence#,applied from v$archived_log order by sequence# ; sequence# applied .................... sequence# applied ---------- --------- 9376 no 9377 no 9377 no 9378 no 9378 no 9379 no 9379 no 9380 no 9380 no 9381 no 9381 no sequence# applied ---------- --------- 9382 no 9382 no 11200 rows selected. sql>
然后查看下当前的归档记录
sql> archive log list; database log mode archive mode automatic archival enabled archive destination use_db_recovery_file_dest oldest online log sequence 164 next log sequence to archive 166 current log sequence 166 sql>
看到归档记录才是164,和v$archived_log里面上w的记录数不匹配,这是因为这是rman备份恢复遗留下来的记录,所以需要清理一下。
清理记录,采用sys.dbms_backup_restore.resetcfilesection(11);清理:
sql> execute sys.dbms_backup_restore.resetcfilesection(11); pl/sql procedure successfully completed. sql> select sequence#,applied from v$archived_log order by sequence# ; no rows selected sql>
再次测试,可以查看到日志记录变化了,v$archived_log已经是最新的,只有一条记录数存在了:
sql> alter system switch logfile; system altered. sql> select sequence#,applied from v$archived_log order by sequence# ; sequence# applied ---------- --------- 166 no sql> execute sys.dbms_backup_restore.resetcfilesection(11); pl/sql procedure successfully completed. sql> select sequence#,applied from v$archived_log order by sequence# ; no rows selected sql>
扩展话题,单机实例可以用上,述办法操作,那么oracle集群比如dg呢,分析master库、standby库
#master库上v$archived_log表记录数: sql> select count(1) from v$archived_log; count(1) ---------- 623616 sql> #standby库上v$archived_log表记录数: sql> select count(1) from v$archived_log; count(1) ---------- 2226823 sql>
感谢阅读,希望能帮助到大家,谢谢大家对本站的支持!