undo表空間檔案丟失恢復（2）--無備份有redo的情況下恢復
undo表空間的資料檔案丟失，如果沒有備份的情況下，但是redo完好，這個時候可以這樣恢復，下邊給出一個例子。
undo表空間檔案丟失恢復（1）--有備份的情況下恢復：http://blog.itpub.net/26736162/viewspace-1458654/
[oracle@rhel6_lhr ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Mar 12 18:13:13 2015
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u03/app/oracle/oradata/ora1024g/system01.dbf
/u03/app/oracle/oradata/ora1024g/undotbs01.dbf
/u03/app/oracle/oradata/ora1024g/sysaux01.dbf
/u03/app/oracle/oradata/ora1024g/users01.dbf
/u03/app/oracle/oradata/ora1024g/example01.dbf
/u03/app/oracle/oradata/ora1024g/undotbs02.dbf
6 rows selected.
SQL> drop table bb;
Table dropped.
SQL> create table bb as select * from user_tables;
Table created.
SQL> insert into bb select * from user_tables;
707 rows created.
SQL> select count(1) from bb;
COUNT(1)
----------
1413
SQL>
SQL>
SQL> ho rm /u03/app/oracle/oradata/ora1024g/undotbs01.dbf
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 448790528 bytes
Fixed Size 2084616 bytes
Variable Size 130023672 bytes
Database Buffers 310378496 bytes
Redo Buffers 6303744 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/u03/app/oracle/oradata/ora1024g/undotbs01.dbf'
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL>
SQL> set line 9999
SQL> col name format a100
SQL> select name,status,enabled from v$datafile;
NAME STATUS ENABLED
---------------------------------------------------------------------------------------------------- ------- ----------
/u03/app/oracle/oradata/ora1024g/system01.dbf SYSTEM READ WRITE
/u03/app/oracle/oradata/ora1024g/undotbs01.dbf ONLINE READ WRITE
/u03/app/oracle/oradata/ora1024g/sysaux01.dbf ONLINE READ WRITE
/u03/app/oracle/oradata/ora1024g/users01.dbf ONLINE READ WRITE
/u03/app/oracle/oradata/ora1024g/example01.dbf ONLINE READ WRITE
/u03/app/oracle/oradata/ora1024g/undotbs02.dbf ONLINE READ WRITE
6 rows selected.
SQL> select * from v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- ---------
3 OFFLINE OFFLINE FILE NOT FOUND 0
SQL> alter database create datafile 2 as '/u03/app/oracle/oradata/ora1024g/undotbs01.dbf' size 50m;
Database altered.
SQL> select * from v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- ---------
2 ONLINE ONLINE 1278091 12-MAR-15
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 2 needs media recovery
ORA-01110: data file 2: '/u03/app/oracle/oradata/ora1024g/undotbs01.dbf'
SQL> select * from v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- ---------
2 ONLINE ONLINE 1278091 12-MAR-15
SQL> recover datafile 2;
ORA-00279: change 1278091 generated at 03/12/2015 20:14:19 needed for thread 1
ORA-00289: suggestion : /u03/app/oracle/flash_recovery_area/ORA1024G/archivelog/2015_03_13/o1_mf_1_31_%u_.arc
ORA-00280: change 1278091 for thread 1 is in sequence #31
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 1299920 generated at 03/12/2015 20:32:01 needed for thread 1
ORA-00289: suggestion : /u03/app/oracle/flash_recovery_area/ORA1024G/archivelog/2015_03_13/o1_mf_1_32_%u_.arc
ORA-00280: change 1299920 for thread 1 is in sequence #32
ORA-00278: log file '/u03/app/oracle/flash_recovery_area/ORA1024G/archivelog/2015_03_12/o1_mf_1_31_bj31wg6x_.arc' no longer needed for this recovery
ORA-00279: change 1319924 generated at 03/12/2015 20:33:18 needed for thread 1
ORA-00289: suggestion : /u03/app/oracle/flash_recovery_area/ORA1024G/archivelog/2015_03_13/o1_mf_1_33_%u_.arc
ORA-00280: change 1319924 for thread 1 is in sequence #33
ORA-00278: log file '/u03/app/oracle/flash_recovery_area/ORA1024G/archivelog/2015_03_12/o1_mf_1_32_bj31wg77_.arc' no longer needed for this recovery
ORA-00279: change 1339928 generated at 03/12/2015 20:33:59 needed for thread 1
ORA-00289: suggestion : /u03/app/oracle/flash_recovery_area/ORA1024G/archivelog/2015_03_13/o1_mf_1_34_%u_.arc
ORA-00280: change 1339928 for thread 1 is in sequence #34
ORA-00278: log file '/u03/app/oracle/flash_recovery_area/ORA1024G/archivelog/2015_03_12/o1_mf_1_33_bj31xqc4_.arc' no longer needed for this recovery
ORA-00279: change 1359932 generated at 03/12/2015 20:35:20 needed for thread 1
ORA-00289: suggestion : /u03/app/oracle/flash_recovery_area/ORA1024G/archivelog/2015_03_13/o1_mf_1_35_%u_.arc
ORA-00280: change 1359932 for thread 1 is in sequence #35
ORA-00278: log file '/u03/app/oracle/flash_recovery_area/ORA1024G/archivelog/2015_03_12/o1_mf_1_34_bj3208q4_.arc' no longer needed for this recovery
ORA-00279: change 1379936 generated at 03/12/2015 20:36:20 needed for thread 1
ORA-00289: suggestion : /u03/app/oracle/flash_recovery_area/ORA1024G/archivelog/2015_03_13/o1_mf_1_36_%u_.arc
ORA-00280: change 1379936 for thread 1 is in sequence #36
ORA-00278: log file '/u03/app/oracle/flash_recovery_area/ORA1024G/archivelog/2015_03_12/o1_mf_1_35_bj3224vc_.arc' no longer needed for this recovery
ORA-00279: change 1399940 generated at 03/12/2015 20:37:20 needed for thread 1
ORA-00289: suggestion : /u03/app/oracle/flash_recovery_area/ORA1024G/archivelog/2015_03_13/o1_mf_1_37_%u_.arc
ORA-00280: change 1399940 for thread 1 is in sequence #37
ORA-00278: log file '/u03/app/oracle/flash_recovery_area/ORA1024G/archivelog/2015_03_12/o1_mf_1_36_bj32409g_.arc' no longer needed for this recovery
ORA-00279: change 1419945 generated at 03/12/2015 20:40:48 needed for thread 1
ORA-00289: suggestion : /u03/app/oracle/flash_recovery_area/ORA1024G/archivelog/2015_03_13/o1_mf_1_38_%u_.arc
ORA-00280: change 1419945 for thread 1 is in sequence #38
ORA-00278: log file '/u03/app/oracle/flash_recovery_area/ORA1024G/archivelog/2015_03_12/o1_mf_1_37_bj32bj52_.arc' no longer needed for this recovery
ORA-00279: change 1439949 generated at 03/12/2015 20:43:49 needed for thread 1
ORA-00289: suggestion : /u03/app/oracle/flash_recovery_area/ORA1024G/archivelog/2015_03_13/o1_mf_1_39_%u_.arc
ORA-00280: change 1439949 for thread 1 is in sequence #39
ORA-00278: log file '/u03/app/oracle/flash_recovery_area/ORA1024G/archivelog/2015_03_12/o1_mf_1_38_bj32j54p_.arc' no longer needed for this recovery
ORA-00279: change 1459953 generated at 03/12/2015 20:45:50 needed for thread 1
ORA-00289: suggestion : /u03/app/oracle/flash_recovery_area/ORA1024G/archivelog/2015_03_13/o1_mf_1_40_%u_.arc
ORA-00280: change 1459953 for thread 1 is in sequence #40
ORA-00278: log file '/u03/app/oracle/flash_recovery_area/ORA1024G/archivelog/2015_03_12/o1_mf_1_39_bj32mygp_.arc' no longer needed for this recovery
ORA-00279: change 1479957 generated at 03/12/2015 20:48:27 needed for thread 1
ORA-00289: suggestion : /u03/app/oracle/flash_recovery_area/ORA1024G/archivelog/2015_03_13/o1_mf_1_41_%u_.arc
ORA-00280: change 1479957 for thread 1 is in sequence #41
ORA-00278: log file '/u03/app/oracle/flash_recovery_area/ORA1024G/archivelog/2015_03_12/o1_mf_1_40_bj32rv2k_.arc' no longer needed for this recovery
Log applied.
Media recovery complete.
SQL> select name,status,enabled from v$datafile;
NAME STATUS ENABLED
---------------------------------------------------------------------------------------------------- ------- ----------
/u03/app/oracle/oradata/ora1024g/system01.dbf SYSTEM READ WRITE
/u03/app/oracle/oradata/ora1024g/undotbs01.dbf ONLINE READ WRITE
/u03/app/oracle/oradata/ora1024g/sysaux01.dbf ONLINE READ WRITE
/u03/app/oracle/oradata/ora1024g/users01.dbf ONLINE READ WRITE
/u03/app/oracle/oradata/ora1024g/example01.dbf ONLINE READ WRITE
/u03/app/oracle/oradata/ora1024g/undotbs02.dbf ONLINE READ WRITE
6 rows selected.
SQL> select * from v$recover_file;
no rows selected
SQL> alter database open;
Database altered.
SQL> select count(1) from bb;
COUNT(1)
----------
706
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28628435/viewspace-1984477/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- undo表空間檔案丟失恢復（3）--無備份無redo的情況下恢復
- undo表空間檔案丟失恢復（4）--無備份無recover的情況下恢復
- undo表空間檔案丟失恢復（1）--有備份
- REDO檔案丟失的恢復__沒有任何備份的情況
- 恢復之利用備份在所有控制檔案丟失情況下恢復（四）
- 恢復之利用備份在所有控制檔案丟失情況下恢復（三）
- 恢復之利用備份在所有控制檔案丟失情況下恢復（二）
- 恢復之利用備份在所有控制檔案丟失情況下恢復（一）
- 沒有自動備份的情況下控制檔案全部丟失的恢復
- 備份與恢復系列 九 丟失表空間資料檔案的還原與恢復
- 【恢復】Redo日誌檔案丟失的恢復
- 非歸檔無備份下控制檔案丟失的恢復
- rman恢復--歸檔模式無備份，丟失資料檔案的恢復模式
- rman恢復--歸檔模式有備份，丟失資料檔案的恢復模式
- Oracle備份與恢復【丟失資料檔案的恢復】Oracle
- 撤消表空間資料檔案丟失的恢復.
- 恢復案例：歸檔模式下丟失非系統表空間資料檔案的恢復模式
- 【備份恢復】恢復 丟失已歸檔重做日誌檔案
- 系統表空間檔案丟失無備份用控制程式碼的辦法恢復
- 【備份恢復】 丟失一個控制檔案 之恢復操作
- RMAN資料庫恢復 之歸檔模式有(無)備份-丟失資料檔案的恢復資料庫模式
- UNDO 表空間檔案損壞的恢復
- 【備份恢復】非歸檔模式下丟失任意資料檔案 恢復操作模式
- INDEX表空間檔案丟失或者損壞的恢復Index
- 【物理熱備】（下）備份恢復系統表空間 手工備份恢復
- 備份恢復之資料檔案丟失
- Oracle-無備份情況下，如何手動恢復控制檔案Oracle
- 歸檔模式無備份丟失資料檔案後恢復模式
- REDO檔案丟失或者損壞的恢復
- 【備份恢復】歸檔模式下丟失系統關鍵資料檔案 利用RMAN備份恢復模式
- 歸檔模式有備份丟失資料檔案後恢復模式
- mysql無備份恢復-獨立表空間MySql
- 【備份恢復】所有控制檔案丟失後 利用trace中的控制檔案備份執行恢復
- Oracle備份與恢復【丟失控制檔案的恢復】Oracle
- 系統表空間資料檔案丟失，無備份，無重啟，通過控制程式碼恢復
- 全備份情況下，刪除控制檔案及恢復
- TEMP表空間的檔案丟失或損壞後的恢復
- 只有rman備份集，控制檔案丟失的恢復