Oracle undo表空间文件丢失恢复

undo表空间文件丢失恢复（1）--有备份

undo表空间的数据文件丢失，如果有备份的情况下，恢复非常简单，下边给出一个例子：

[oracle@rhel6_lhr ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Mar 12 18:02: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> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 1

Next log sequence to archive 2

Current log sequence 2

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 ~]$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Thu Mar 12 18:02:23 2015

Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: ORA1024G (DBID=2698093861)

RMAN> backup database;

Starting backup at 2015-03-12 18:02:30

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=144 devtype=DISK

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00001 name=/u03/app/oracle/oradata/ora1024g/system01.dbf

input datafile fno=00003 name=/u03/app/oracle/oradata/ora1024g/sysaux01.dbf

input datafile fno=00005 name=/u03/app/oracle/oradata/ora1024g/example01.dbf

input datafile fno=00002 name=/u03/app/oracle/oradata/ora1024g/undotbs01.dbf

input datafile fno=00004 name=/u03/app/oracle/oradata/ora1024g/users01.dbf

channel ORA_DISK_1: starting piece 1 at 2015-03-12 18:02:31

channel ORA_DISK_1: finished piece 1 at 2015-03-12 18:03:49

piece handle=/u03/app/oracle/flash_recovery_area/ORA1024G/backupset/2015_03_12/o1_mf_nnndf_TAG20150312T180231bj2s1vh3.bkp tag=TAG20150312T180231 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:01:19

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

including current control file in backupset

including current SPFILE in backupset

channel ORA_DISK_1: starting piece 1 at 2015-03-12 18:03:53

channel ORA_DISK_1: finished piece 1 at 2015-03-12 18:03:54

piece handle=/u03/app/oracle/flash_recovery_area/ORA1024G/backupset/2015_03_12/o1_mf_ncsnf_TAG20150312T180231bj2s49dm.bkp tag=TAG20150312T180231 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03

Finished backup at 2015-03-12 18:03:54

RMAN> report schema;

Report of database schema

List of Permanent Datafiles

===========================

File Size(MB) Tablespace RB segs Datafile Name

1 480 SYSTEM *** /u03/app/oracle/oradata/ora1024g/system01.dbf

2 30 UNDOTBS1 *** /u03/app/oracle/oradata/ora1024g/undotbs01.dbf

3 250 SYSAUX *** /u03/app/oracle/oradata/ora1024g/sysaux01.dbf

4 5 USERS *** /u03/app/oracle/oradata/ora1024g/users01.dbf

5 100 EXAMPLE *** /u03/app/oracle/oradata/ora1024g/example01.dbf

List of Temporary Files

=======================

File Size(MB) Tablespace Maxsize(MB) Tempfile Name

1 20 TEMP 32767 /u03/app/oracle/oradata/ora1024g/temp01.dbf

RMAN> exit

Recovery Manager complete.

[oracle@rhel6_lhr ~]$ rm /u03/app/oracle/oradata/ora1024g/undotbs01.dbf

[oracle@rhel6_lhr ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Mar 12 18:05:00 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> 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> 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 ~]$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Thu Mar 12 18:05:37 2015

Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: ORA1024G (DBID=2698093861, not open)

RMAN> restore tablespace undotbs1;

Starting restore at 2015-03-12 18:05:56

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00002 to /u03/app/oracle/oradata/ora1024g/undotbs01.dbf

channel ORA_DISK_1: reading from backup piece /u03/app/oracle/flash_recovery_area/ORA1024G/backupset/2015_03_12/o1_mf_nnndf_TAG20150312T180231bj2s1vh3.bkp

channel ORA_DISK_1: restored backup piece 1

piece handle=/u03/app/oracle/flash_recovery_area/ORA1024G/backupset/2015_03_12/o1_mf_nnndf_TAG20150312T180231bj2s1vh3.bkp tag=TAG20150312T180231

channel ORA_DISK_1: restore complete, elapsed time: 00:00:06

Finished restore at 2015-03-12 18:06:03

RMAN> recover tablespace undotbs1;

Starting recover at 2015-03-12 18:06:17

using channel ORA_DISK_1

starting media recovery

media recovery complete, elapsed time: 00:00:02

Finished recover at 2015-03-12 18:06:19

RMAN> alter database open;

database opened

--注意这里一定要让undo在线

RMAN> sql 'alter database datafile 2 online';

using target database control file instead of recovery catalog

sql statement: alter database datafile 2 online

RMAN> exit

RMAN> report schema;

Report of database schema

List of Permanent Datafiles

===========================

File Size(MB) Tablespace RB segs Datafile Name

1 480 SYSTEM *** /u03/app/oracle/oradata/ora1024g/system01.dbf

2 30 UNDOTBS1 *** /u03/app/oracle/oradata/ora1024g/undotbs01.dbf

3 250 SYSAUX *** /u03/app/oracle/oradata/ora1024g/sysaux01.dbf

4 5 USERS *** /u03/app/oracle/oradata/ora1024g/users01.dbf

5 100 EXAMPLE *** /u03/app/oracle/oradata/ora1024g/example01.dbf

List of Temporary Files

=======================

File Size(MB) Tablespace Maxsize(MB) Tempfile Name

1 20 TEMP 32767 /u03/app/oracle/oradata/ora1024g/temp01.dbf

RMAN>

RMAN>

undo表空间文件丢失恢复（2）--无备份有redo的情况下恢复

[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_131%u_.arc

ORA-00280: change 1278091 for thread 1 is in sequence #31

Specify log: {=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_132%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_31bj31wg6x.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_133%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_32bj31wg77.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_134%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_33bj31xqc4.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_135%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_34bj3208q4.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_136%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_35bj3224vc.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_137%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_36bj32409g.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_138%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_37bj32bj52.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_139%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_38bj32j54p.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_140%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_39bj32mygp.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_141%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_40bj32rv2k.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

undo表空间文件丢失恢复（3）--无备份无redo的情况下恢复

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

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

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>

undo表空间文件丢失恢复（4）--无备份无recover的情况下恢复

undo表空间的数据文件丢失，如果没有备份的情况下，而且丢失的undo文件可以置为offline状态后（注意是offline不是recover状态），则可以如下恢复，下边给出一个例子。

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