有这样一种需求，虽然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.