有时候，你可能需要维护control file中的archivelog记录，比如，archivelog记录过多，会导致备份过程中【control file sequential read】的等待事件。
比较简单的做法是手动清理v$archived_log。
下面在我的环境中演示该过程。
查看下备库当前的状态
SQL> select name,database_role,open_mode from v$database; NAME DATABASE_ROLE OPEN_MODE --------- ---------------- -------------------- ENMY PHYSICAL STANDBY READ ONLY WITH APPLY SQL> SQL> set linesize 400 SQL> col name for a65 SQL> select name,thread#,sequence#,to_char(first_time,'yyyy-mm-dd hh24:mi:ss') "First",to_char(next_time,'yyyy-mm-dd hh24:mi:ss') "Next",applied from v$archived_log; NAME THREAD# SEQUENCE# First Next APPLIED ----------------------------------------------------------------- ---------- ---------- ------------------- ------------------- --------- /u01/app/oracle/dg_archlog/1_137_928630070.archive 1 137 2016-12-07 20:48:33 2016-12-07 20:48:44 YES /u01/app/oracle/dg_stdbylog/1_138_928630070.archive 1 138 2016-12-07 20:48:44 2016-12-07 20:49:32 YES /u01/app/oracle/dg_stdbylog/1_139_928630070.archive 1 139 2016-12-07 20:49:32 2016-12-07 22:00:26 YES /u01/app/oracle/dg_stdbylog/1_140_928630070.archive 1 140 2016-12-07 22:00:26 2016-12-07 22:02:07 YES /u01/app/oracle/dg_stdbylog/1_141_928630070.archive 1 141 2016-12-07 22:02:07 2016-12-07 22:02:37 YES /u01/app/oracle/dg_stdbylog/1_142_928630070.archive 1 142 2016-12-07 22:02:37 2016-12-07 22:02:54 IN-MEMORY 6 rows selected. SQL>
将会用到的存储过程：
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.
SQL> desc v$controlfile_record_section;
Name Null? Type
----------------------------------------- -------- ----------------------------
TYPE VARCHAR2(28)
RECORD_SIZE NUMBER
RECORDS_TOTAL NUMBER
RECORDS_USED NUMBER
FIRST_INDEX NUMBER
LAST_INDEX NUMBER
LAST_RECID NUMBER
SQL>
SQL> set linesize 300
SQL> set pagesize 40
SQL> select rownum-1 "Section ID",type from v$controlfile_record_section order by "Section ID";
Section ID 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 rows selected.
SQL>注意，ARCHIVELOG，编号为：11。
执行存储过程，清空ARCHIVELOG的记录：
SQL> select count(*) from v$archived_log;
COUNT(*)
----------
6
SQL> execute sys.dbms_backup_restore.resetCfileSection(11);
PL/SQL procedure successfully completed.
SQL> select count(*) from v$archived_log;
COUNT(*)
----------
0
SQL>
SQL> select name,thread#,sequence#,to_char(first_time,'yyyy-mm-dd hh24:mi:ss') "First",to_char(next_time,'yyyy-mm-dd hh24:mi:ss') "Next",applied from v$archived_log;
no rows selected
SQL>这样就清空了。
如果后续还有日志追加过来，日志的SEQUENCE#编号不会乱掉，会延续之前的日志编号：
SQL> run 1* select name,thread#,sequence#,to_char(first_time,'yyyy-mm-dd hh24:mi:ss') "First",to_char(next_time,'yyyy-mm-dd hh24:mi:ss') "Next",applied from v$archived_log NAME THREAD# SEQUENCE# First Next APPLIED ----------------------------------------------------------------- ---------- ---------- ------------------- ------------------- --------- /u01/app/oracle/dg_archlog/1_143_928630070.archive 1 143 2016-12-07 22:02:54 2016-12-07 22:21:43 YES /u01/app/oracle/dg_stdbylog/1_144_928630070.archive 1 144 2016-12-07 22:21:43 2016-12-07 22:22:45 IN-MEMORY SQL>
————————————
Done。