重庆思庄oracle技术分享-清除控制文件的信息

通过查询v$controlfil{e}_{r}ecor{d}_{s}ection视图获得许多信息.SQL>selectrownum-1a,crs.\ast fromv$controlfile_record_section crs;

     A TYPE                         RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID

---

     0 DATABASE                             316             1            1           0          0          0
     1 CKPT PROGRESS                       8180            11            0           0          0          0
     2 REDO THREAD                          256             8            1           0          0          0
     3 REDO LOG                              72            16            3           0          0          0
     4 DATAFILE                             520          1600            8           0          0          2
     5 FILENAME                             524          5298           12           0          0          0
     6 TABLESPACE                            68          1600            9           0          0          2
     7 TEMPORARY FILENAME                    56          1600            1           0          0          1
     8 RMAN CONFIGURATION                  1108            50            0           0          0          0
     9 LOG HISTORY                           56           292            4           1          4          4
    10 OFFLINE RANGE                        200          1063            0           0          0          0
    11 ARCHIVED LOG                         584           308           12           1         12         12
    12 BACKUP SET                            40          1227            0           0          0          0
    13 BACKUP PIECE                         736          1000            0           0          0          0
    14 BACKUP DATAFILE                      200          1063            0           0          0          0
    15 BACKUP REDOLOG                        76           215            0           0          0          0
    16 DATAFILE COPY                        736          1000            2           1          2          2
    17 BACKUP CORRUPTION                     44          1115            0           0          0          0
    18 COPY CORRUPTION                       40          1227            0           0          0          0
    19 DELETED OBJECT                        20           818            5           1          5          5
    20 PROXY COPY                           928          1004            0           0          0          0
    21 BACKUP SPFILE                        124           131            0           0          0          0
    22 DATABASE INCARNATION                  56           292            1           1          1          1
    23 FLASHBACK LOG                         84          2048            0           0          0          0
    24 RECOVERY DESTINATION                 180             1            1           0          0          0
    25 INSTANCE SPACE RESERVATION            28          1055            1           0          0          0
    26 REMOVABLE RECOVERY FILES              32          1000            0           0          0          0
    27 RMAN STATUS                          116           141           29           1         29         29
    28 THREAD INSTANCE NAME MAPPING          80             8            8           0          0          0
    29 MTTR                                 100             8            1           0          0          0
    30 DATAFILE HISTORY                     568            57            0           0          0          0
    31 STANDBY DATABASE MATRIX              400            31           31           0          0          0
    32 GUARANTEED RESTORE POINT             212          2048            0           0          0          0
    33 RESTORE POINT                        212          2083            0           0          0          0
    34 DATABASE BLOCK CORRUPTION             80          8384            0           0          0          0
    35 ACM OPERATION                        104            64            6           0          0          0
    36 FOREIGN ARCHIVED LOG                 604          1002            0           0          0          0

37 rows selected.

使用dbms_backup_restore包resetcfilesection可以清除对应的信息
注意：有的信息不能清除，所以在生存环境中需要使用的时候要非常谨慎
例子如下:

SQL> execute dbms_backup_restore.resetcfilesection(9);

SQL> select * from (select rownum -1 a, crs.* from v$controlfile_record_section crs) where a=9;

     A TYPE                         RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID

---

     9 LOG HISTORY                           56           292            0           0          0          0

–对比发现可以清除.

SQL> execute dbms_backup_restore.resetcfilesection(11);

PL/SQL procedure successfully completed.

SQL> select * from (select rownum -1 a, crs.* from v$controlfile_record_section crs) where a=11;

     A TYPE                         RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID

---

    11 ARCHIVED LOG                         584           308            0           0          0          0