解决OracleRMAN删除归档⽇志不释放问题的⽅法
前⾔
当Oracle 归档⽇志满了后，将⽆法正常登⼊oracle，需要删除⼀部分归档⽇志才能正常登⼊ORACLE。
最近同事在⼯作中遇到⼀个问题，他那边的⼀套Oracle 11g数据库使⽤RMAN没办法删除旧的归档，导致磁盘使⽤率很⾼。
oracle@ps251n2s:[/opt/oracle/archive/db] ls -ltr |more
total 3059881082truncated命令不记录日志
-rw-r----- 1 oracle oinstall 725320704 May 20 2016 1_1_91233774.dbf
-rw-rw---- 1 oracle oinstall 1756937216 Jun 7 2016 1_2_91233774.dbf
-rw-rw---- 1 oracle oinstall 1756731392 Jun 21 2016 1_3_91233774.dbf
-rw-rw---- 1 oracle oinstall 1758001152 Jul 3 2016 1_4_91233774.dbf
-rw-rw---- 1 oracle oinstall 1756729344 Jul 15 2016 1_5_91233774.dbf
-rw-rw---- 1 oracle oinstall 1765750784 Jul 26 22:00 1_6_91233774.dbf
-rw-rw---- 1 oracle oinstall 1760346112 Aug 6 14:04 1_7_91233774.dbf
......
使⽤RMAN list archivelog all；查看⽇志也没有显⽰旧归档⽇志的记录
RMAN> list archivelog all;
using target database control file instead of recovery catalog
List of Archived Log Copies for database with db_unique_name DBS
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
1206 1 1206 A 14-JAN-17
Name: /opt/oracle/archive/db/1_1206_91233774.dbf
1207 1 1207 A 14-JAN-17
Name: /opt/oracle/archive/db/1_1207_91233774.dbf
1208 1 1208 A 14-JAN-17
Name: /opt/oracle/archive/db/1_1208_91233774.dbf
......
使⽤crosscheck检查也没有查到旧归档⽇志记录
RMAN> crosscheck archivelog all;
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=16 device type=DISK
validation succeeded for archived log
archived log file name=/opt/oracle/archive/db/1_1206_91233774.dbf RECID=1206 STAMP=933244655
validation succeeded for archived log
archived log file name=/opt/oracle/archive/db/1_1207_91233774.dbf RECID=1207 STAMP=933249510
validation succeeded for archived log
archived log file name=/opt/oracle/archive/db/1_1208_91233774.dbf RECID=1208 STAMP=933254500
validation succeeded for archived log
archived log file name=/opt/oracle/archive/db/1_1209_91233774.dbf RECID=1209 STAMP=933259445
validation succeeded for archived log
查看v$archived_log视图也没有旧归档⽇志记录
解决过程
于是在⽹上到⼀篇⽂章，⼤体意思是MAN删除归档⽇志的依据是controlfile中记录的归档⽇志，⽽不是你磁盘上实际存在的归档⽇志，如果你的归档⽇志已经不在controlfile中就⽆法被RMAN识别到，也就⽆法被删除。⽽致于归档信息何时在控制⽂件中被覆盖，这由⼀个参数control_file_record_keep_time来控制。
查看数据库中的参数是默认的7天
SYS@db> show parameter CONTROL_FILE_RECORD_KEEP_TIME
NAME        TYPE      VALUE
------------------------------------ -------------------------------- ------------------------------
control_file_record_keep_time  integer      7
难道是在控制⽂件中归档⽇志的记录条数只能保留7天的么？于是到MOS去相关⽂档到⼀篇Why Ar
e Controlfile Entries For Archived Logs Not Overwritten After CONTROL_FILE_RECORD_KEEP_TIME? (⽂档 ID 1056085.1)，⽂档介绍CONTROL_FILE_RECORD_KEEP_TIME参数并不是强制在控制⽂件中保留多少天的归档⽇志条⽬，⽽是由
V$CONTROLFILE_RECORD_SECTION视图中的ARCHIVED LOG条⽬去控制的，当
RECORDS_TOTAL=RECORDS_USED时旧的归档⽇志就会被覆盖掉。
于是查数据库中的V$CONTROLFILE_RECORD_SECTION视图，看到ARCHIVED LOG⾏的records_total=224意味着控制⽂件中只能保留224条归档⽇志⽂件信息。
SYS@dbs> select * from v$controlfile_record_section where type='ARCHIVED LOG';
TYPE      RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID
---------------------------- ----------- ------------- ------------ ----------- ---------- ----------
ARCHIVED LOG      584  224  224  88  87  1235
查询v$archived_log视图，总数为224条
SYS@dbs> select count(*) from v$archived_log;
COUNT(*)
----------
224
这时应该可以推断出由于控制⽂件中只能保留224条归档⽇志数据，所以以前的归档信息已经被控制⽂件刷出，RMAN⽆法识别到这些归档⽂件，所以就⽆法删除这些归档⽇志。
那么现在如果使⽤RMAN删除这些旧的归档⽇志呢？我想到使⽤catalog start with 命令重新注册这些⽂件到控制⽂件中
RMAN> catalog start with '/opt/oracle/archive/db';
......
File Name: /opt/oracle/archive/db/1_1005_91233774.dbf
File Name: /opt/oracle/archive/db/1_1006_91233774.dbf
File Name: /opt/oracle/archive/db/1_1007_91233774.dbf
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging done
......
RMAN> list archivelog all;
.....
1236 1 301 A 22-OCT-16
Name: /opt/oracle/archive/db/1_301_91233774.dbf
1237 1 302 A 22-OCT-16
Name: /opt/oracle/archive/db/1_302_91233774.dbf
......
旧的归档⽇志⽂件已经注册到控制⽂件中了，再使⽤RMAN进⾏删除就可以了
delete archivelog until logseq 1007;
再次查询v$controlfile_record_section视图，records_total也被“撑”⼤了
SYS@db> select * from v$controlfile_record_section where type='ARCHIVED LOG';
TYPE      RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID
---------------------------- ----------- ------------- ------------ ----------- ---------- ----------
ARCHIVED LOG      584  896  742  807  652  1800
⾄此问题结束。
总结
以上就是这篇⽂章的全部内容了，希望本⽂的内容对⼤家的学习或者⼯作能带来⼀定的帮助，如果有疑问⼤家可以留⾔交流，谢谢⼤家对的⽀持。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。