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 表空間增加檔案Oracle
- Tablespace表空間刪除
- oracle級聯刪除使用者，刪除表空間Oracle
- 新建的表空間(或資料檔案)丟失以及控制檔案丟失，有新建表空間(或資料檔案)前的控制文
- Oracle 刪除使用者、表空間、資料檔案、使用者下的所有表Oracle
- Oracle表空間Oracle
- oracle 表空間Oracle
- 刪除臨時表空間組
- oracle表空間的整理Oracle
- Oracle臨時表空間檢視、新增臨時表空間資料檔案、修改預設臨時表空間 方法！Oracle
- [待整理]oracle10g刪除(釋放)資料檔案/表空間流程Oracle
- 增加oracle表空間Oracle
- oracle temp 表空間Oracle
- 刪除表空間出現ORA-22868錯誤（一）
- 刪除表空間時，遇到了ORA-14404錯誤
- oracle 臨時表空間的增刪改查Oracle
- 當使用者無限制使用表空間配額且表空間有足夠空間時出現超出表空間的空間限額
- oracle 表移動表空間Oracle
- Oracle表移動表空間Oracle
- oracle 普通表空間資料檔案壞塊Oracle
- Oracle 10g大檔案表空間(轉)Oracle 10g
- 16、表空間 建立表空間
- Oracle的表空間quota詳解Oracle
- Oracle OCP(47)：表空間的建立Oracle
- Oracle 批量建表空間Oracle
- Oracle清理SYSAUX表空間OracleUX
- 表空間和資料檔案的管理
- Linux檔案刪除空間未釋放Linux
- （轉載）刪除檔案後硬碟空間不釋放的問題硬碟
- Linux檔案刪除但空間不釋放問題篇Linux
- Oracle案例11——Oracle表空間資料庫檔案收縮Oracle資料庫
- 表空間利用率及表空間的補充
- KingbaseES的表空間
- Oracle中表空間、表、索引的遷移Oracle索引
- Oracle新建使用者、表空間、表Oracle
- Oracle Temp 表空間切換Oracle
- Oracle OCP(49)：表空間管理Oracle
- Oracle表空間收縮方案Oracle