在使用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 = '';
About Me
| ........................................................................................................................ ● 本文作者：小麦苗，部分内容整理自网络，若有侵权请联系小麦苗删除 ● 本文在itpub（ http://blog.itpub.net/26736162 ）、博客园（ http://www.cnblogs.com/lhrbest ）和个人weixin公众号（ xiaomaimiaolhr ）上有同步更新 ● 本文itpub地址： http://blog.itpub.net/26736162 ● 本文博客园地址： http://www.cnblogs.com/lhrbest ● 本文pdf版、个人简介及小麦苗云盘地址： http://blog.itpub.net/26736162/viewspace-1624453/ ● 数据库笔试面试题库及解答： http://blog.itpub.net/26736162/viewspace-2134706/ ● DBA宝典今日头条号地址： http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826 ........................................................................................................................ ● QQ群号： 230161599 （满） 、618766405 ● weixin群：可加我weixin，我拉大家进群，非诚勿扰 ● 联系我请加QQ好友 （ 646634621 ） ，注明添加缘由 ● 于 2019-03-01 06:00 ~ 2019-03-31 24:00 在魔都完成 ● 最新修改时间：2019-03-01 06:00 ~ 2019-03-31 24:00 ● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解 ● 版权所有，欢迎分享本文，转载请保留出处 ........................................................................................................................ ● 小麦苗的微店 ： https://weidian.com/s/793741433?wfr=c&ifr=shopdetail ● 小麦苗出版的数据库类丛书 ： http://blog.itpub.net/26736162/viewspace-2142121/ ● 小麦苗OCP、OCM、高可用网络班 ： http://blog.itpub.net/26736162/viewspace-2148098/ ● 小麦苗腾讯课堂主页 ： https://lhr.ke.qq.com/ ........................................................................................................................ 使用 weixin客户端 扫描下面的二维码来关注小麦苗的weixin公众号（ xiaomaimiaolhr ）及QQ群（DBA宝典）、添加小麦苗weixin， 学习最实用的数据库技术。 ........................................................................................................................ |
 |  |
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26736162/viewspace-1799884/，如需转载，请注明出处，否则将追究法律责任。
扫一扫
1552
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
