在使用RMAN命令删除归档后，查询v$archived_log视图会发现name列为空了，但其他列的信息还保留，时间长了会留下很多过期的信息，影响维护工作，需要将过期的信息删除。首先模拟下问题的出现过程：
- --删除归档日志之前查看v$archived_log视图，情况正常
- SQL > select dest_id , sequence # , name , blocks from v$archived_log ;
- DEST_ID SEQUENCE # NAME BLOCKS
- ---------- ---------- --------------------------------------------- ----------
- 1 101 / oradata / archive / orcl_1_101_851966182 . arc 2730
- 1 102 / oradata / archive / orcl_1_102_851966182 . arc 95711
- 1 103 / oradata / archive / orcl_1_103_851966182 . arc 94813
- 1 104 / oradata / archive / orcl_1_104_851966182 . arc 95048
- 1 105 / oradata / archive / orcl_1_105_851966182 . arc 94677
- 1 106 / oradata / archive / orcl_1_106_851966182 . arc 97494
- 1 107 / oradata / archive / orcl_1_107_851966182 . arc 94300
- 1 108 / oradata / archive / orcl_1_108_851966182 . arc 97494
-
- --使用RAMN命令删除归档
- RMAN > delete archivelog all ;
- --再次查询v$archived_log视图，name列为空
- SQL > select dest_id , sequence # , name , blocks from v$archived_log ;
- DEST_ID SEQUENCE # NAME BLOCKS
- ---------- ---------- --------------------------------------------- ----------
- 1 101 2730
- 1 102 95711
- 1 103 94813
- 1 104 95048
- 1 105 94677
- 1 106 97494
- 1 107 94300
- 1 108 97494
出现这样的现象是因为使用RMAN命令在删除归档日志的时候不能够清楚控制文件中的内容，导致v$archived_log留下的过期的不完整信息。下面将归档信息进行清除：
- --清除控制文件中关于v$archived_log的信息
- SQL > execute sys . dbms_backup_restore . resetCfileSection ( 11 ) ;
- PL / SQL procedure successfully completed .
- --再次查询v$archived_log，信息已经被清除
- SQL > select dest_id , sequence # , name , blocks from v$archived_log ;
- no rows selected
但是这样是把所有的v$archive_log信息都清除了，包括未过期的也会不清除。下面再将未过期的归档文件信息注册进来。
- --我测试环境上归档日志都在/oradata/archive/中
- RMAN > catalog start with '/oradata/archive/' ;
- --再次查询v$archived_log，未被删除的归档信息可以查询到了
- SQL > select dest_id , sequence # , name , blocks from v$archived_log ;
- DEST_ID SEQUENCE # NAME BLOCKS
- ---------- ---------- --------------------------------------------- ----------
- 1 110 / oradata / archive / orcl_1_110_851966182 . arc 1
- 1 111 / oradata / archive / orcl_1_111_851966182 . arc 2
- 1 109 / oradata / archive / orcl_1_109_851966182 . arc 31079
需要注意的是，对于命令 “ SYS.DBMS_BACKUP_RESTORE.RESETCFILESECTION(11) ”中的 11 代表的是归档文件（其实就是控制文件中的“ record type ”），那么其它数字分别代表什么含义呢？其实，可以通过视图 V$CONTROLFILE_RECORD_SECTION 来查看：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 | SYS@lhrdb> SELECT ROWNUM-1 "NUMBER" , TYPE FROM V$CONTROLFILE_RECORD_SECTION;
number TYPE
---------- --------------------------------------------------------
0 DATABASE
1 CKPT PROGRESS
2 REDO THREAD
3 REDO LOG
4 DATAFILE
5 FILENAME
6 TABLESPACE
7 TEMPORARY FILENAME
8 RMAN CONFIGURATION
9 LOG HISTORY
10 OFFLINE RANGE
11 ARCHIVED LOG
12 BACKUP SET
13 BACKUP PIECE
14 BACKUP DATAFILE
15 BACKUP REDOLOG
16 DATAFILE COPY
17 BACKUP CORRUPTION
18 COPY CORRUPTION
19 DELETED OBJECT
20 PROXY COPY
21 BACKUP SPFILE
22 DATABASE INCARNATION
23 FLASHBACK LOG
24 RECOVERY DESTINATION
25 INSTANCE SPACE RESERVATION
26 REMOVABLE RECOVERY FILES
27 RMAN STATUS
28 THREAD INSTANCE NAME MAPPING
29 MTTR
30 DATAFILE HISTORY
31 STANDBY DATABASE MATRIX
32 GUARANTEED RESTORE POINT
33 RESTORE POINT
34 DATABASE BLOCK CORRUPTION
35 ACM OPERATION
36 FOREIGN ARCHIVED LOG
37 PDB RECORD
38 AUXILIARY DATAFILE COPY
39 MULTI INSTANCE REDO APPLY
40 PDBINC RECORD
41 TABLESPACE KEY HISTORY
|
因此，输入不同的数字，会对不同的视图数据进行清理，例如：
1 2 3 4 5 6 7 | EXECUTE DBMS_BACKUP_RESTORE.RESETCFILESECTION(11); /** CLEAR V$ARCHIVED_LOG */
EXECUTE DBMS_BACKUP_RESTORE.RESETCFILESECTION(12) ; /** CLEAR V$BACKUP_SET */
EXECUTE DBMS_BACKUP_RESTORE.RESETCFILESECTION(13) ; /** CLEAR V$BACKUP_PIECE */
EXECUTE DBMS_BACKUP_RESTORE.RESETCFILESECTION(17); /** CLEAR V$BACKUP_CORRUPTION */
EXECUTE DBMS_BACKUP_RESTORE.RESETCFILESECTION(18); /** CLEAR V$COPY_CORRUPTION */
EXECUTE DBMS_BACKUP_RESTORE.RESETCFILESECTION(19); /** CLEAR V$DELETED_OBJECT */
EXECUTE DBMS_BACKUP_RESTORE.RESETCFILESECTION(28); /** CLEAR V$RMAN_STATUS */
|
RMAN>delete archivelog all;
之后再查看 v$archived_log发现还有记录。 http://www.linuxidc.com/Linux/2013-06/86718.htm
1.重建控制文件，例如 control_file_record_keep_time=0 然后等待记录被重用，这样很不好。
3. 使用包来清理，注意不要在生产库上这么做
PROCEDURE resetCfileSection(record_typeINbinary_integer);
– Input parameters:
– The circular record type whose controlfile sectionisto be reset.
execute sys.dbms_backup_restore.resetCfileSection(11); ==> 清理 v$rman_status 对应的记录
Removing entries in v$archived_log referencing a particluar DEST_ID [ID 845361.1]
Applies to:
Oracle Server – Enterprise Edition – Version: 10.2.0.3 and later [Release: 10.2 and later ]
Information in this document applies to any platform.
Goal
This note provides instructions on how to clear the section in the controlfile which contains data referencing v$archived_log.
For example v$archived_log may contain data from dest_id = 1 & dest_id=2.
This note will guide you through the process of only keeping entries from one distinct location
Solution
It is possible to clear different section of the controlfile.
Section 11 refers to the v$archived_log entries.
SQL>execute sys.dbms_backup_restore.resetCfileSection( 11);
This will clear all files in v$archived_log;
Then using RMAN we can catalog the DEST=1 file back in.
Assume that all archivelogs reside in /recovery_area/archives
RMAN> catalog start with '/recovery_area/archives';
This will update the controlfile with these entries only.
NOTE:
If you clear a controlfile section using undocumented event, then you also need to update high_al_recid in the node table for that database to 0 in
recovery catalog.
For 11g recovery catalog schema and above:
update node set high_al_recid = 0 where db_unique_name = '
For 10gR2 recovery catalog schema and below:
update dbinc set high_al_recid = 0 where db_name = '';
