重庆思庄Oracle、Redhat认证学习论坛

标题: 清除控制文件的信息 [打印本页]

作者: 刘泽宇 时间: 2022-3-20 18:30
标题: 清除控制文件的信息
通过查询v$controlfile_record_section视图获得许多信息.
SQL> select rownum -1 a, crs.* from v$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

欢迎光临重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)