Oracle 如何规范清理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> 

感谢阅读，希望能帮助到大家，谢谢大家对本站的支持！