[20121105]清除控制文件的信息.txt
参考链接:http://space.itpub.net/22578826/viewspace-722815
上午看了以上链接,学习如何清除控制文件的信息,这个仅仅用在测试环境,不能使用在生产系统.切记切记!
大家都知道控制文件记录了许多信息,archivelog的信息,log history的信息,以及INCARNATION等信息.
通过查询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.
LOG HISTORY在控制文件中的编号为9, ARCHIVED LOG 在控制文件中的编号为11,DATABASE INCARNATION在控制文件中的编号为22等等.
使用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
3.在做一个清除DATABASE INCARNATION的测试.
BEGIN dbms_backup_restore.resetcfilesection(22); END;
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [2162], [22], [], [], [], [], [], [], [], [], [], []
ORA-06512: at "SYS.DBMS_BACKUP_RESTORE", line 6966
ORA-06512: at line 1
--可以发现报错.关机做一个INCARNATION在测试看看.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 2137886720 bytes
Fixed Size 2215064 bytes
Variable Size 1728054120 bytes
Database Buffers 402653184 bytes
Redo Buffers 4964352 bytes
Database mounted.
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 3011113645 generated at 11/05/2012 11:53:52 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle11g/flash_recovery_area/TEST/archivelog/2012_11_05/o1_mf_1_498_%u_.arc
ORA-00280: change 3011113645 for thread 1 is in sequence #498
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log '/u01/app/oracle11g/flash_recovery_area/TEST/archivelog/2012_11_05/o1_mf_1_498_%u_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00308: cannot open archived log '/u01/app/oracle11g/flash_recovery_area/TEST/archivelog/2012_11_05/o1_mf_1_498_%u_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
--估计没有归档!
SQL> select * from v$log ;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------ -------------------
1 1 496 52428800 512 1 YES INACTIVE 3011113398 2012-11-05 11:45:14 3011113402 2012-11-05 11:45:16
3 1 498 52428800 512 1 NO CURRENT 3011113406 2012-11-05 11:45:16 2.8147E+14
2 1 497 52428800 512 1 YES INACTIVE 3011113402 2012-11-05 11:45:16 3011113406 2012-11-05 11:45:16
SQL> select * from v$logfile ;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ------------------------------------------------------------ ---
3 ONLINE /u01/app/oracle11g/oradata/test/redo03.log NO
2 ONLINE /u01/app/oracle11g/oradata/test/redo02.log NO
1 ONLINE /u01/app/oracle11g/oradata/test/redo01.log NO
--可以发现 /u01/app/oracle11g/oradata/test/redo03.log 没有归档.
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 3011113645 generated at 11/05/2012 11:53:52 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle11g/flash_recovery_area/TEST/archivelog/2012_11_05/o1_mf_1_498_%u_.arc
ORA-00280: change 3011113645 for thread 1 is in sequence #498
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle11g/oradata/test/redo03.log
Log applied.
Media recovery complete.
SQL> alter database open ;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs ;
Database altered.
--使用backup controlfile恢复时候(实际上是我并没有替换控制文件),只不过recover使用了backup controlfile.
--要正常打开必须open resetlogs.
SQL> select * from (select rownum -1 a, crs.* from v$controlfile_record_section crs) where a=22;
A TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID
---------- ---------------------------- ----------- ------------- ------------ ----------- ---------- ----------
22 DATABASE INCARNATION 56 292 2 1 2 2
--可以发现现在产生了2个INCARNATION.rman也可以验证.
RMAN> list incarnation ;
using target database control file instead of recovery catalog
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 TEST 2071943378 PARENT 15808173 2012-08-10 11:04:07
2 2 TEST 2071943378 CURRENT 3011113647 2012-11-05 11:58:00
SQL> execute dbms_backup_restore.resetcfilesection(22);
BEGIN dbms_backup_restore.resetcfilesection(22); END;
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [2162], [22], [], [], [], [], [], [], [], [], [], []
ORA-06512: at "SYS.DBMS_BACKUP_RESTORE", line 6966
ORA-06512: at line 1
--依旧不行.到mount状态看看.是否可以.
SQL> startup force mount
ORACLE instance started.
Total System Global Area 2137886720 bytes
Fixed Size 2215064 bytes
Variable Size 1728054120 bytes
Database Buffers 402653184 bytes
Redo Buffers 4964352 bytes
Database mounted.
SQL> execute dbms_backup_restore.resetcfilesection(22);
BEGIN dbms_backup_restore.resetcfilesection(22); END;
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [2162], [22], [], [], [], [], [], [], [], [], [], []
ORA-06512: at "SYS.X$DBMS_BACKUP_RESTORE", line 6969
ORA-06512: at line 1
--看来一些信息还是不能清除的.