ITPub博客

首页 > 数据库 > Oracle > undo表空间文件丢失恢复(1)--有备份

undo表空间文件丢失恢复(1)--有备份

原创 Oracle 作者:lhrbest 时间:2015-03-13 16:29:33 0 删除 编辑

 

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_TAG20150312T180231_bj2s1vh3_.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_TAG20150312T180231_bj2s49dm_.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_TAG20150312T180231_bj2s1vh3_.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_TAG20150312T180231_bj2s1vh3_.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>

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26736162/viewspace-1458654/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
QQ:646634621| 网名:小麦苗| 微信公众号:xiaomaimiaolhr| 11g OCM| QQ群:618766405 微信群:私聊| 《数据库笔试面试宝典》作者| OCP、OCM、高可用(RAC+DG+OGG)网络班开讲啦,有需要的小伙伴可以私聊我。

注册时间:2012-09-23

  • 博文量
    1367
  • 访问量
    8348678