如何清除Oracle控制文件中的无用记录，例如v$archived_log中的deleted归档日志记录

有这样一种需求，虽然controlfile中的archivelog和其他一些备份信息会被覆盖重用，收到参数control_file_record_keep_time的影响，但是我们没法手动触发这种重用，这导致有时候controlfile中的archivelog 记录过多，可能导致一些备份、恢复过程中的”control file sequential read”等待事件。

已知的一些解决方案，包括：

 

1. 重建控制文件， 例如 backup controlfile to trace后重建该控制文件，但要求有数据文件均存在

2. 设置control_file_record_keep_time=0 然后等待记录被重用， 太过被动了….

 

这里再介绍一种手动清除v$ARCHIVED_LOG中不管是DELETED还是available 记录的方法，注意不要在你的产品数据库上这样做：

 

SQL> select count(*) from v$archived_log;

COUNT(*)
———-
553

SQL> /

COUNT(*)
———-
553
SQL> execute sys.dbms_backup_restore.resetCfileSection( 11);

PL/SQL procedure successfully completed.

SQL> select count(*) from v$archived_log;

COUNT(*)
———-
0

 

PROCEDURE resetCfileSection(record_type IN binary_integer );

— This procedure attempts to reset the circular controlfile section.
—
— Input parameters:
— record_type
— The circular record type whose controlfile section is to be reset.

 

execute sys.dbms_backup_restore.resetCfileSection( 11);  ==> 清理v$ARCHIVED_LOG对应的记录

execute sys.dbms_backup_restore.resetCfileSection(28);  ==>清理v$rman_status对应的记录

 

以上是2个例子，具体的section_id可以这样获得：

 

1* select rownum-1, type from v$controlfile_record_section
SQL> /

ROWNUM-1 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

LOG History占用的控制文件记录数可以查询v$controlfile_record_section获得：

 

SQL> select * from v$controlfile_record_section where type=’LOG HISTORY’ ;

TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID
—————————- ———– ————- ———— ———– ———- ———-
LOG HISTORY 56 584 563 300 278 570

control file sequential read

Reading from the controlfile. This happens in many cases, e.g. while:

– making a backup of the controlfiles.

– the shared info (between instances) from the controlfile.

– reading other blocks from the controlfiles.

– reading the header block.

Wait time

The wait time is the elapse time of the read.

Parameters

file#

This identifies the controlfile that Oracle7 is reading from and with the following SQL statement one can determine the name of the controlfile:

select *

from x$kcccf

where indx = file#

block#

Blocknumber in the controlfile from where we are starting to read. The blocksize is the as the physical blocksize of the port (normally 512 bytes, some UNIX ports have 1 Kilobytes or 2 Kilobytes).

blocks

The number of blocks that we are trying to read.

Advise

If the wait time is too long (more then average I/O speed), check if the controlfiles are not a disk that is too busy. This could really impact Parallel Server performance as some of the synchronization between instances is done through the controlfiles.