合 Oracle undo表空间文件丢失恢复

 

0    320    1

Tags：

👉 本文共约14146个字，系统预计阅读时间或需54分钟。

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

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
AiDBA后续精彩内容已被站长无情隐藏，请输入验证码解锁本文！
验证码：
获取验证码： 请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“AiDBA”或者“dbaup6”或者微信扫描右侧二维码都可以关注本站微信公众号。

标签：

Avatar photo

小麦苗

学习或考证，均可联系麦老师，请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复