合 Oracle undo表空间文件丢失恢复
Tags： Oracle
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;
1 | FILE# NAME STATUS ENABLED |
1 2 3 4 5 6 7 8 9 10 11 | 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
