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

 

时间： 2024-09-19 13:13:38

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

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

  undo表空间的数据文件丢失,如果没有备份的情况下,而且丢失的undo文件可以置为offline状态后(注意是offline不是recover状态),则可以如下恢复,下边给出一个例子.   undo表空间文件丢失恢复(1)--有备份的情况下恢复:http://blog.itpub.net/26736162/viewspace-1458654/ undo表空间文件丢失恢复(2)--无备份有redo的情况下恢复:http://blog.itpub.net/26736162/viewspace-1

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

undo表空间的数据文件丢失,如果没有备份的情况下,而且redo也不可用,这个时候就要采用隐藏参数来恢复,下边给出一个例子.   undo表空间文件丢失恢复(1)--有备份的情况下恢复:http://blog.itpub.net/26736162/viewspace-1458654/ undo表空间文件丢失恢复(2)--无备份有redo的情况下恢复:http://blog.itpub.net/26736162/viewspace-1458663/   [oracle@rhel6_lhr ~]$

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

undo表空间的数据文件丢失,如果没有备份的情况下,但是redo完好,这个时候可以这样恢复,下边给出一个例子.   undo表空间文件丢失恢复(1)--有备份的情况下恢复:http://blog.itpub.net/26736162/viewspace-1458654/   [oracle@rhel6_lhr ~]$ sqlplus / as sysdba   SQL*Plus: Release 10.2.0.4.0 - Production on Thu Mar 12 18:13:13 201

关于undo表空间配置错误的ORA-30012

      undo表空间是Oracle体系结构的重要组成部分,为什么我们可以回滚,就是因为有它.数据库任意数据的修改都会在undo表空间里生成前镜像,一是可以回滚,二是可以实现并发,以及一致性查询.因此undo也是Oracle数据库在创建和配置参数时必要的组成部分.本文描述的是错误的配置undo表空间之后故障的解决.       有关undo表空间的基础知识可以参考:             Oracle 回滚(ROLLBACK)和撤销(UNDO)            检查及设置合理的und

收缩undo表空间

      通常情况下,如果undo表空间的处于自动扩展且未指定最大值的情形,对于使用小表空间模式的数据库,undo表空间可能会一再增长,直到达到32GB.或者是在指定了自动扩展及其最大值而月底或年末的批量数据计算导致undo表空间疯狂超范围增长后不再释放.对于这些情形我们需要手动收缩表空间以达到节省空间资源以及数据库管理开销,如rman备份等.本文列出了收缩undo表空间的基本步骤并给出示例.      有关表空间,undo表空间的文章可参考:           Oracle 表空间与数据文

检查及设置合理的undo表空间

      UNDO是用于实现并发控制以及构建一致性读,也就是在数据变更之前产生前镜像,以保证用户能够回滚或撤销对数据库所作的修改.是Oracle数据库完整性的重要组成部分.因此合理的设计及配置undo以及使用undo都将对数据库有较大的影响.通常情况下,对于大规模数据的删除,更新操作,我们建议使用分批删除分次提交以减少对undo的占用和冲击.那么对于undo的大小到底应该设置多大?是启用自动扩展还是关闭自动扩展?这个问题仁者见仁,智者见智,见下文.   1.当前数据库环境及undo配置信息 s

UNDO表空间丢失/损坏测试

1.模拟丢失了undo表空间数据文件,SHUTDOWN ABORT,进行启动报错找不到数据文件 2.设置UNDO datafile offline后启动数据如下 SQL> alter database datafile 2 offline; 接下来试试能不能打开数据库,能打开就查看那些回退段 SQL> startup ORACLE instance started. Total System Global Area  599785472 bytes Fixed Size            

Oracle管理UNDO表空间

创建undo talespace有两种方式: (1)数据库创建时创建undo tablespace; (2)在一个已经存在的数据库创建. 在undo tablespace中不能创建数据库对象,这是因为这个表空间是为数据库recover而准备的. 1.创建数据库时创建undo tablespace 在创建数据库的时候可以通过指定undo子句来创建undo tablespace,但是这个子句不是必须的. 如果在创建数据库时,系统指定是auto模式,但是没有指明undo tablespace的名字,那

探索ORACLE之RMAN_07单个数据文件丢失恢复

探索ORACLE之RMAN_07单个数据文件丢失恢复 作者:吴伟龙   Name:Prodence Woo QQ:286507175  msn:hapy-wuweilong@hotmail.com   备份的终极目的是为了更好的将数据恢复和还原过来,在前面的章节中我们已经重点谈完了RMAN的备份,实际上也穿插的谈了些复杂的完整恢复.当然在这节当中我们将会由浅入深的详细谈谈在几种不同情况下的数据库恢复. 1.     数据文件的丢失恢复 1.1    在wwl表空间上创建5张表,并添加数据. SQ