undo表空间的数据文件丢失，如果没有备份的情况下，而且redo也不可用，这个时候就要采用隐藏参数来恢复，下边给出一个例子。
undo表空间文件丢失恢复（1）--有备份的情况下恢复：http://blog.itpub.net/26736162/viewspace-1458654/
undo表空间文件丢失恢复（2）--无备份有redo的情况下恢复：http://blog.itpub.net/26736162/viewspace-1458663/
[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> create undo tablespace undotbs2 datafile '/u03/app/oracle/oradata/ora1024g/undotbs02.dbf' size 5m autoextend on;
Tablespace created.
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL> create table bb as select * from user_tables;
Table created.
SQL> insert into bb select * from user_tables;
707 rows created.
SQL> shutdown abort;
ORACLE instance shut down.
SQL>
SQL> ho rm /u03/app/oracle/oradata/ora1024g/undotbs01.dbf
SQL> startup force;
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> alter system set undo_tablespace=undotbs2 scope=spfile;
System altered.
SQL> alter system set undo_management=manual scope=spfile;
System altered.
SQL> startup force mount;
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.
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.
---注意这里undo的状态为recover
SQL> select file#,name,status,enabled from v$datafile;
FILE# NAME STATUS ENABLED
---------- ---------------------------------------------------------------------------------------------------- ------- ----------
1 /u03/app/oracle/oradata/ora1024g/system01.dbf SYSTEM READ WRITE
2 /u03/app/oracle/oradata/ora1024g/undotbs01.dbf RECOVER READ WRITE
3 /u03/app/oracle/oradata/ora1024g/sysaux01.dbf ONLINE READ WRITE
4 /u03/app/oracle/oradata/ora1024g/users01.dbf ONLINE READ WRITE
5 /u03/app/oracle/oradata/ora1024g/example01.dbf ONLINE READ WRITE
6 /u03/app/oracle/oradata/ora1024g/undotbs02.dbf ONLINE READ WRITE
6 rows selected.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
SQL>
此时查看altert日志：
Thu Mar 12 18:16:17 2015
alter database open
Thu Mar 12 18:16:17 2015
Beginning crash recovery of 1 threads
parallel recovery started with 2 processes
Thu Mar 12 18:16:17 2015
Started redo scan
Thu Mar 12 18:16:17 2015
Completed redo scan
123 redo blocks read, 23 data blocks need recovery
Thu Mar 12 18:16:17 2015
Started redo application at
Thread 1: logseq 3, block 99
Thu Mar 12 18:16:17 2015
Recovery of Online Redo Log: Thread 1 Group 3 Seq 3 Reading mem 0
Mem# 0: /u03/app/oracle/oradata/ora1024g/redo03.log
Thu Mar 12 18:16:17 2015
Completed redo application
Thu Mar 12 18:16:18 2015
Completed crash recovery at
Thread 1: logseq 3, block 222, scn 734292
23 data blocks read, 23 data blocks written, 123 redo blocks read
Thu Mar 12 18:16:18 2015
LGWR: STARTING ARCH PROCESSES
ARC0 started with pid=18, OS id=33684
Thu Mar 12 18:16:18 2015
ARC0: Archival started
ARC1: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC1 started with pid=19, OS id=33686
Thu Mar 12 18:16:18 2015
Thread 1 advanced to log sequence 4 (thread open)
Thread 1 opened at log sequence 4
Current log# 1 seq# 4 mem# 0: /u03/app/oracle/oradata/ora1024g/redo01.log
Successful open of redo thread 1
Thu Mar 12 18:16:18 2015
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Thu Mar 12 18:16:18 2015
ARC0: Becoming the 'no FAL' ARCH
ARC0: Becoming the 'no SRL' ARCH
Thu Mar 12 18:16:18 2015
SMON: enabling cache recovery
Thu Mar 12 18:16:18 2015
ARC1: Becoming the heartbeat ARCH
Thu Mar 12 18:16:18 2015
db_recovery_file_dest_size of 2048 MB is 29.58% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Thu Mar 12 18:16:18 2015
Errors in file /u03/app/oracle/admin/ora1024g/udump/ora1024g_ora_33662.trc:
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/u03/app/oracle/oradata/ora1024g/undotbs01.dbf'
Thu Mar 12 18:16:18 2015
Error 376 happened during db open, shutting down database
USER: terminating instance due to error 376
Instance terminated by USER, pid = 33662
ORA-1092 signalled during: alter database open...
查看文件： /u03/app/oracle/admin/ora1024g/udump/ora1024g_ora_33662.trc
[root@rhel6_lhr ~]# more /u03/app/oracle/admin/ora1024g/udump/ora1024g_ora_33662.trc
/u03/app/oracle/admin/ora1024g/udump/ora1024g_ora_33662.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u03/app/oracle/product/10.2.0/db_1
System name: Linux
Node name: rhel6_lhr
Release: 2.6.32-431.el6.x86_64
Version: #1 SMP Sun Nov 10 22:19:54 EST 2013
Machine: x86_64
Instance name: ora1024g
Redo thread mounted by this instance: 1
Oracle process number: 15
Unix process pid: 33662, image: oracle@rhel6_lhr (TNS V1-V3)
*** 2015-03-12 18:16:17.849
*** ACTION NAME:() 2015-03-12 18:16:17.849
*** MODULE NAME:(sqlplus@rhel6_lhr (TNS V1-V3)) 2015-03-12 18:16:17.849
*** SERVICE NAME:() 2015-03-12 18:16:17.849
*** SESSION ID:(159.3) 2015-03-12 18:16:17.849
Successfully allocated 2 recovery slaves
Using 550 overflow buffers per recovery slave
Thread 1 checkpoint: logseq 3, block 2, scn 713814
cache-low rba: logseq 3, block 99
on-disk rba: logseq 3, block 222, scn 714292
start recovery at logseq 3, block 99, scn 0
----- Redo read statistics for thread 1 -----
Read rate (ASYNC): 61Kb in 0.12s => 0.50 Mb/sec
Total physical reads: 4096Kb
Longest record: 1Kb, moves: 0/284 (0%)
Change moves: 4/47 (8%), moved: 0Mb
Longest LWN: 38Kb, moves: 0/21 (0%), moved: 0Mb
Last redo scn: 0x0000.000ae633 (714291)
----------------------------------------------
----- Recovery Hash Table Statistics ---------
Hash table buckets = 32768
Longest hash chain = 1
Average hash chain = 23/23 = 1.0
Max compares per lookup = 1
Avg compares per lookup = 176/225 = 0.8
----------------------------------------------
*** 2015-03-12 18:16:17.970
KCRA: start recovery claims for 23 data blocks
*** 2015-03-12 18:16:17.999
KCRA: blocks processed = 23/23, claimed = 23, eliminated = 0
*** 2015-03-12 18:16:17.999
Recovery of Online Redo Log: Thread 1 Group 3 Seq 3 Reading mem 0
----- Recovery Hash Table Statistics ---------
Hash table buckets = 32768
Longest hash chain = 1
Average hash chain = 23/23 = 1.0
Max compares per lookup = 1
Avg compares per lookup = 191/199 = 1.0
----------------------------------------------
tkcrrsarc: (WARN) Failed to find ARCH for message (message:0x1)
tkcrrpa: (WARN) Failed initial attempt to send ARCH message (message:0x1)
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/u03/app/oracle/oradata/ora1024g/undotbs01.dbf'
[root@rhel6_lhr ~]#
必须读取2号文件才能保证一致性，此时使用隐含参数：
[oracle@rhel6_lhr ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Mar 12 18:22:58 2015
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup mount;
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.
SQL> alter system set "_offline_rollback_segments"=true scope=spfile;
System altered.
SQL> startup force mount;
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.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@rhel6_lhr dbs]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Mar 12 20:36:56 2015
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup mount;
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.
SQL> alter system set "_corrupted_rollback_segments"='_SYSSMU1$','_SYSSMU2$','_SYSSMU3$','_SYSSMU4$','_SYSSMU5$','_SYSSMU6$','_SYSSMU7$','_SYSSMU8$','_SYSSMU9$','_SYSSMU10$' scope=spfile;
System altered.
SQL> alter database open;
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 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> alter system reset "_offline_rollback_segments" scope=spfile sid='*';
System altered.
SQL> alter system reset "_corrupted_rollback_segments" scope=spfile sid='*';
System altered.
SQL>
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>