十年网站开发经验 + 多家企业客户 + 靠谱的建站团队
量身定制 + 运营维护+专业推广+无忧售后,网站问题一站解决
Oracle 如何规范清理v$archived_log记录实例详解
创新互联主营麻城网站建设的网络公司,主营网站建设方案,成都App定制开发,麻城h5微信小程序搭建,麻城网站营销推广欢迎麻城等地区企业咨询
单机实例上面,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>
感谢阅读,希望能帮助到大家,谢谢大家对本站的支持!