undo表空間檔案丟失恢復（4）--無備份無recover的情況下恢復
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
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> 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>
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28628435/viewspace-1984479/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle-無備份情況下，如何手動恢復控制檔案Oracle
- 【BBED】丟失歸檔檔案情況下的恢復
- SYSTEM 表空間管理及備份恢復
- dg丟失歸檔，使用rman增量備份恢復
- Oracle 無備份情況下undo檔案損壞處理Oracle
- 【PG備份恢復】pg_basebackup 多表空間備份恢復測試
- 剪下的檔案還能恢復嗎，恢復剪貼丟失的檔案
- MySQL異常恢復之無主鍵情況下innodb資料恢復的方法MySql資料恢復
- 電腦檔案丟失資料恢復資料恢復
- 丟失的隨身碟檔案如何恢復？
- Omni Recover for Mac如何恢復所有丟失的iPhone資料MaciPhone
- XFS檔案系統的備份、恢復、修復
- Oracle RMAN 表空間恢復Oracle
- RAC備份恢復之Voting備份與恢復
- Sql Server資料庫檔案丟失的恢復方法SQLServer資料庫
- 【伺服器資料恢復】XFS檔案系統分割槽分割槽丟失無法訪問的資料恢復案例伺服器資料恢復
- 12 使用RMAN備份和恢復檔案
- Linux中XFS檔案系統的備份，恢復，修復Linux
- 表空間TSPITR恢復-實驗
- 【Oracle 恢復表空間】 實驗Oracle
- innobackupex 部分表備份和恢復
- 備份與恢復：polardb資料庫備份與恢復資料庫
- MySQL備份與恢復——基於Xtrabackup物理備份恢復MySql
- DATA GUARD主庫丟失資料檔案的恢復(3)
- DATA GUARD主庫丟失資料檔案的恢復(1)
- DATA GUARD主庫丟失資料檔案的恢復(2)
- Oracle Redo丟失恢復方案Oracle
- (Les16 執行資料庫恢復)-表空間恢復資料庫
- 【北亞資料恢復】MongoDB資料遷移檔案丟失的MongoDB資料恢復案例資料恢復MongoDB
- 【資料庫資料恢復】mdb_catalog.wt檔案丟失的MongoDB資料恢復案例資料庫資料恢復MongoDB
- 【伺服器資料恢復】xfs檔案系統資料丟失的資料恢復案例伺服器資料恢復
- mydumper備份恢復
- Mysql備份恢復MySql
- 備份和恢復
- oracle drop table purge無備份bbed恢復(3/3)Oracle
- oracle drop table purge無備份bbed恢復(1/3)Oracle
- oracle drop table purge無備份bbed恢復(2/3)Oracle
- 伺服器資料丟失了怎麼恢復/分割槽丟失恢復教程伺服器
- MySQL 非常規恢復與物理備份恢復MySql