Oracle 11g 表空間的誤刪除, 當前的控制檔案不識別該表空間
|
--做3次日誌切換 --檢視歸檔日誌資訊, 注意resetlogs前後,incarnation的變化 sys@TESTDB11>select sequence#, name from v$archived_log;
SEQUENCE# NAME ---------- ------------------------------ 93 /archive1/1_93_813665348.dbf 93 /archive2/1_93_813665348.dbf 94 /archive1/1_94_813665348.dbf 94 /archive2/1_94_813665348.dbf 95 /archive1/1_95_813665348.dbf 95 /archive2/1_95_813665348.dbf 94 /archive1/1_94_813665348.dbf 94 /archive2/1_94_813665348.dbf 95 /archive1/1_95_813665348.dbf 95 /archive2/1_95_813665348.dbf 96 /archive1/1_96_813665348.dbf 96 /archive2/1_96_813665348.dbf 1 /archive1/1_1_823322444.dbf 1 /archive2/1_1_823322444.dbf
14 rows selected.
--檢視當前表空間的資訊 sys@TESTDB11>select tablespace_name from dba_tablespaces;
TABLESPACE_NAME ------------------------------ SYSTEM SYSAUX UNDOTBS1 TEMP USERS EXAMPLE
6 rows selected.
--建立一個新的表空間並在該表空間上建立一張表
sys@TESTDB11>create tablespace newts datafile '/oradata/newts01.dbf' size 20m;
Tablespace created.
scott@TESTDB11>create table tab_newts tablespace newts as select * from salgrade;
Table created.
--手工備份該表空間 sys@TESTDB11>alter tablespace newts begin backup;
[oracle@S1011:/export/home/oracle]$ cp /oradata/newts01.dbf /pooldisk02/backup01/inconsistent/
sys@TESTDB11>alter tablespace newts end backup;
--把該表空間存在時的控制檔案備份出來 sys@TESTDB11>alter database backup controlfile to '/pooldisk02/backup01/inconsistent/control_newsts.bak';
Database altered.
--做3次日誌切換 --檢視當前SCN，恢復時就恢復到這個SCN sys@TESTDB11>select current_scn from v$database;
CURRENT_SCN ----------- 2529246
1 row selected.
--表空間誤刪除，包括相應的物件和資料檔案 sys@TESTDB11>drop tablespace newts including contents and datafiles;
Tablespace dropped.
--進行不完全恢復，是要還原所有的資料檔案，然後執行不完全恢復，但當前的控制檔案不認識newts表空間 sys@TESTDB11>select name from v$tablespace;
NAME ------------------------------ SYSTEM SYSAUX UNDOTBS1 USERS EXAMPLE TEMP
6 rows selected.
--資料檔案也不包括了 sys@TESTDB11>select name from v$datafile;
NAME ------------------------------ /oradata/system01.dbf /oradata/sysaux01.dbf /oradata/undotbs01.dbf /oradata/users01.dbf /oradata/example01.dbf
5 rows selected.
--因此需要使用一個可以識別該表空間的備份的控制檔案來恢復
sys@TESTDB11>shutdown abort; ORACLE instance shut down.
--還原控制檔案和資料檔案 [oracle@S1011:/export/home/oracle]$ cp /pooldisk02/backup01/inconsistent/control_newsts.bak /u01/app/oracle/oradata/TestDB11/control01.ctl [oracle@S1011:/export/home/oracle]$ cp /pooldisk02/backup01/inconsistent/control_newsts.bak /u01/app/oracle/fast_recovery_area/TestDB11/control02.ctl
[oracle@S1011:/export/home/oracle]$ cp /pooldisk02/backup01/inconsistent/*.dbf /oradata
sys@TESTDB11>startup mount; ORACLE instance started.
Total System Global Area 855982080 bytes Fixed Size 2230792 bytes Variable Size 641730040 bytes Database Buffers 209715200 bytes Redo Buffers 2306048 bytes Database mounted.
--檢視資料檔案中檢查點 idle>col name for a60 idle>select name, checkpoint_change# from v$datafile_header;
NAME CHECKPOINT_CHANGE# ------------------------------------------------------------ ------------------ /oradata/system01.dbf 2526335 /oradata/sysaux01.dbf 2526361 /oradata/undotbs01.dbf 2526381 /oradata/users01.dbf 2526397 /oradata/example01.dbf 2526409 /oradata/newts01.dbf 2529022
6 rows selected.
--恢復 idle>recover database using backup controlfile until change 2529246; ORA-00279: change 2526335 generated at 08/13/2013 04:43:44 needed for thread 1 ORA-00289: suggestion : /archive2/1_1_823322444.dbf ORA-00280: change 2526335 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL} auto ORA-00279: change 2527038 generated at 08/13/2013 04:56:13 needed for thread 1 ORA-00289: suggestion : /archive2/1_2_823322444.dbf ORA-00280: change 2527038 for thread 1 is in sequence #2 ORA-00278: log file '/archive2/1_1_823322444.dbf' no longer needed for this recovery
ORA-00279: change 2527103 generated at 08/13/2013 04:58:35 needed for thread 1 ORA-00289: suggestion : /archive2/1_3_823322444.dbf ORA-00280: change 2527103 for thread 1 is in sequence #3 ORA-00278: log file '/archive2/1_2_823322444.dbf' no longer needed for this recovery
ORA-00279: change 2529206 generated at 08/13/2013 05:30:22 needed for thread 1 ORA-00289: suggestion : /archive2/1_4_823322444.dbf ORA-00280: change 2529206 for thread 1 is in sequence #4 ORA-00278: log file '/archive2/1_3_823322444.dbf' no longer needed for this recovery
ORA-00279: change 2529221 generated at 08/13/2013 05:31:02 needed for thread 1 ORA-00289: suggestion : /archive2/1_5_823322444.dbf ORA-00280: change 2529221 for thread 1 is in sequence #5 ORA-00278: log file '/archive2/1_4_823322444.dbf' no longer needed for this recovery
ORA-00279: change 2529225 generated at 08/13/2013 05:31:04 needed for thread 1 ORA-00289: suggestion : /archive2/1_6_823322444.dbf ORA-00280: change 2529225 for thread 1 is in sequence #6 ORA-00278: log file '/archive2/1_5_823322444.dbf' no longer needed for this recovery
ORA-00308: cannot open archived log '/archive2/1_6_823322444.dbf' ORA-27037: unable to obtain file status Solaris-AMD64 Error: 2: No such file or directory Additional information: 3
--缺少日誌，判斷當前的資料檔案中檢查點，沒有到達指定位置 idle>select name, checkpoint_change# from v$datafile_header;
NAME CHECKPOINT_CHANGE# ------------------------------------------------------------ ------------------ /oradata/system01.dbf 2529225 /oradata/sysaux01.dbf 2529225 /oradata/undotbs01.dbf 2529225 /oradata/users01.dbf 2529225 /oradata/example01.dbf 2529225 /oradata/newts01.dbf 2529225
6 rows selected. --檢視當前的聯機日誌，確定哪一個沒有歸檔，需要的資訊就在其中 idle>select sequence#, group#, status from v$log;
SEQUENCE# GROUP# STATUS ---------- ---------- ---------------- 1 1 INACTIVE 3 3 CURRENT 2 2 INACTIVE
idle>col member for a50 idle>select group#, member from v$logfile;
GROUP# MEMBER ---------- -------------------------------------------------- 2 /u01/app/oracle/oradata/TestDB11/redo02.log 1 /u01/app/oracle/oradata/TestDB11/redo01.log 3 /u01/app/oracle/oradata/TestDB11/redo03.log
idle>recover database using backup controlfile until change 2529246; ORA-00279: change 2529225 generated at 08/13/2013 05:31:04 needed for thread 1 ORA-00289: suggestion : /archive2/1_6_823322444.dbf ORA-00280: change 2529225 for thread 1 is in sequence #6
Specify log: {<RET>=suggested | filename | AUTO | CANCEL} /u01/app/oracle/oradata/TestDB11/redo03.log Log applied. Media recovery complete.
--開庫，確認已經恢復到刪除表空間之前 Alter database open resetlogs;
scott@TESTDB11>select * from tab_newts;
GRADE LOSAL HISAL ---------- ---------- ---------- 1 700 1200 2 1201 1400 3 1401 2000 4 2001 3000 5 3001 9999 --檢視錶空間和相應的資料檔案 sys@TESTDB11>col file_name for a30 sys@TESTDB11>select tablespace_name, file_name from dba_data_files;
TABLESPACE_NAME FILE_NAME ------------------------------ ------------------------------ EXAMPLE /oradata/example01.dbf USERS /oradata/users01.dbf UNDOTBS1 /oradata/undotbs01.dbf SYSAUX /oradata/sysaux01.dbf SYSTEM /oradata/system01.dbf NEWTS /oradata/newts01.dbf
6 rows selected. |
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/17013648/viewspace-1153857/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 刪除表空間和表空間包含的檔案
- oracle誤刪除表空間的資料檔案Oracle
- Oracle 11g 表空間的誤刪除前傳, 有關控制檔案的備份和恢復（預熱）Oracle
- 誤刪oracle資料庫表空間檔案Oracle資料庫
- oracle 失誤刪掉資料檔案後，刪除表空間操作Oracle
- ORACLE表空間的建立修改刪除Oracle
- 解決linux下刪除檔案或oracle表空間後空間不釋放的問題LinuxOracle
- oracle刪除(釋放)資料檔案/表空間流程Oracle
- flashback database 恢復誤刪除的表空間。Database
- oracle建立臨時表空間和資料表空間以及刪除Oracle
- 表空間不完全恢復（全備--備份控制檔案--刪除表空間andy--日誌檔案）
- Oracle 11g RMAN恢復-使用者誤刪除表空間Oracle
- 如何正確的刪除表空間資料檔案
- 2 Day DBA-管理Oracle例項-修改表空間-刪除表空間Oracle
- Oracle 表空間增加檔案Oracle
- oracle 小議如何從表空間 刪除 資料檔案Oracle
- ORACLE 11g TSPITR恢復被刪除的表空間Oracle
- 新建的表空間(或資料檔案)丟失以及控制檔案丟失，有新建表空間(或資料檔案)前的控制文
- oracle 刪除表空間試驗面面觀Oracle
- 臨時表空間的建立、刪除，設定預設臨時表空間
- 刪除表空間，資料檔案也刪除後，但作業系統層面上空閒空間不見增加。作業系統
- oracle級聯刪除使用者，刪除表空間Oracle
- UNDO表空間下的資料檔案被誤刪除後的處理方法
- 錯誤新增表空間的資料檔案
- 刪除資料庫表空間資料庫
- 歸檔模式下的表空間檔案無法用命令刪除模式
- 使用中undo表空間資料檔案被誤刪
- oracle8i誤刪除臨時表空間後的恢復Oracle
- 大檔案表空間
- hpux刪除檔案後空間不釋放UX
- Oracle 11g 表空間加密Oracle加密
- Oracle表空間小知識Oracle
- Oracle 表空間與資料檔案Oracle
- oracle 資料檔案表空間管理Oracle
- Oracle系統表空間剛新增的一個資料檔案誤刪除恢復處理Oracle
- 表空間&資料檔案和控制檔案(zt)
- oracle 回收表空間的資料檔案大小Oracle
- Oracle表空間Oracle