[20121105]清除控制檔案的資訊.txt
[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
--看來一些資訊還是不能清除的.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-748366/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20121105]重建控制檔案少一個資料檔案的情況.txt
- 清除Oracle控制檔案中的歸檔資訊v$archived_logOracleHive
- [重慶思莊每日技術分享]-清除控制檔案的資訊
- Java輸出流在txt檔案中的換行控制Java
- [20160830]清除日誌與跟蹤檔案.txt
- 清除垃圾檔案DOSBATBAT
- 關於控制檔案與資料檔案頭資訊的說明（zt）
- [20210622]logrotate清除oracle aud檔案的問題.txtlogrotateOracle
- 【控制檔案】映象控制檔案
- Java——接收使用者在控制檯的資訊，將取到的資訊寫入lianxi.txt檔案中，並且將lianxi.txt內容複製到home.txt中Java
- 刪除控制檔案中的磁帶備份資訊
- 如何快速清除.svn檔案
- 重建控制檔案後將備份資訊註冊
- 重建控制檔案時，與資料檔案相關的Checkpoint資訊來自何處
- [20210224]控制檔案序列號滿的分析.txt
- php如何上傳txt檔案，並且讀取txt檔案PHP
- 清空檔案下的SVN控制檔案
- 控制檔案的跟蹤檔案全文
- PCL——txt檔案轉到PCD檔案
- Jpeg的檔案資訊
- 一鍵清除Delphi中無用的檔案
- 控制檔案
- 使用舊的控制檔案備份來恢復控制檔案
- git clean清除未跟蹤檔案Git
- [20210225]控制檔案序列號滿的恢復.txt
- ORA-01207: 檔案比控制檔案更新 - 舊的控制檔案
- Oracle 控制檔案的重建Oracle
- 增加oracle的控制檔案Oracle
- 控制檔案的結構
- 控制檔案的誤解
- 使用11G 的ADRCI 清除trace檔案
- 【備份恢復】利用 備份控制檔案到指定目錄下的控制檔案 恢復控制檔案
- Oracle 匯出txt檔案Oracle
- alter session set events語句dump 控制檔案頭資訊示例Session
- 列出檔案的inode資訊
- mysql 主從日誌檔案mysql-bin檔案清除方法MySql
- chm檔案怎麼轉換成TXT格式？chm檔案快速轉化成TXT格式的方法
- SQLSERVER匯出TXT文字檔案，ORACLE SQL LOADER匯入TXT文字檔案SQLServerOracle