有这样一种需求,虽然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(*) SQL> / COUNT(*) PL/SQL procedure successfully completed. SQL> select count(*) from v$archived_log; COUNT(*)
PROCEDURE resetCfileSection(record_type IN binary_integer ); — This procedure attempts to reset the circular controlfile section.
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 ROWNUM-1 TYPE 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 control file sequential readReading 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. 相关文章 | Related posts:
|
|