undo表空間的資料檔案丟失，如果沒有備份的情況下，而且丟失的undo檔案可以置為offline狀態後（注意是offline不是recover狀態），則可以如下恢復，下邊給出一個例子。

undo表空間檔案丟失恢復（1）--有備份的情況下恢復：http://blog.itpub.net/26736162/viewspace-1458654/

undo表空間檔案丟失恢復（2）--無備份有redo的情況下恢復：http://blog.itpub.net/26736162/viewspace-1458663/

undo表空間檔案丟失恢復（3）--無備份無redo的情況下恢復：http://blog.itpub.net/26736162/viewspace-1458750/

[oracle@rhel6_lhr ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Mar 12 18:13:13 2015

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> show parameter undo

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

undo_management string AUTO

undo_retention integer 900

undo_tablespace string UNDOTBS1

SQL>

---這裡一致性關閉後重啟

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> ho rm /u03/app/oracle/oradata/ora1024g/undotbs01.dbf

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> 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>

SQL> alter database datafile 2 offline;

Database altered.

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 OFFLINE 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>

SQL> alter database open;

Database altered.

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 OFFLINE 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 segment_name,status,tablespace_name from dba_rollback_segs;

SEGMENT_NAME STATUS TABLESPACE_NAME

------------------------------ ---------------- ------------------------------

SYSTEM ONLINE SYSTEM

_SYSSMU10$ OFFLINE UNDOTBS1

_SYSSMU9$ OFFLINE UNDOTBS1

_SYSSMU8$ OFFLINE UNDOTBS1

_SYSSMU7$ OFFLINE UNDOTBS1

_SYSSMU6$ OFFLINE UNDOTBS1

_SYSSMU5$ OFFLINE UNDOTBS1

_SYSSMU4$ OFFLINE UNDOTBS1

_SYSSMU3$ OFFLINE UNDOTBS1

_SYSSMU2$ OFFLINE UNDOTBS1

_SYSSMU1$ OFFLINE UNDOTBS1

_SYSSMU20$ OFFLINE UNDOTBS2

_SYSSMU19$ OFFLINE UNDOTBS2

_SYSSMU18$ OFFLINE UNDOTBS2

_SYSSMU17$ OFFLINE UNDOTBS2

_SYSSMU16$ OFFLINE UNDOTBS2

_SYSSMU15$ OFFLINE UNDOTBS2

_SYSSMU14$ OFFLINE UNDOTBS2

_SYSSMU13$ OFFLINE UNDOTBS2

_SYSSMU12$ OFFLINE UNDOTBS2

_SYSSMU11$ OFFLINE UNDOTBS2

21 rows selected.

SQL> create undo tablespace undotbs2 datafile '/u03/app/oracle/oradata/ora1024g/undotbs02.dbf' size 5m autoextend on;

Tablespace created.

SQL> alter system set undo_tablespace=undotbs2 scope=spfile;

System altered.

SQL> alter system set undo_management=manual scope=spfile;

System altered.

SQL> shutdown immediate;

Database closed.

Database dismounted.

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.

Database opened.

SQL>

SQL> drop tablespace UNDOTBS1;

Tablespace dropped.

SQL>

SQL> create undo tablespace undotbs1 datafile '/u03/app/oracle/oradata/ora1024g/undotbs01.dbf' size 50m autoextend on;

Tablespace created.

SQL> alter system set undo_tablespace=UNDOTBS1 scope=spfile;

System altered.

SQL> alter system set undo_management=auto scope=spfile;

System altered.

SQL> shutdown immediate;

Database closed.

Database dismounted.

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.

Database opened.

SQL> show parameter undo

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

undo_management string AUTO

undo_retention integer 900

undo_tablespace string UNDOTBS1

SQL>

undo表空間檔案丟失恢復（4）--無備份無recover的情況下恢復

相關文章