RMAN备份与恢复之UNDO表空间丢失

一 UNDO表空间讲解在上一篇文章（RMAN备份与恢复之可脱机数据文件丢失 http://www.linuxidc.com/Linux/2014-01/95334.htm）中，我们讲到可脱机数据文件丢失怎么处理，这篇文章我们讲解UNDO表空间丢失的解决办法。UNDO表空间用于存放UNDO数据，当执行DML操作（INSERT、UPDATE、DELETE）的时候，Oracle会将这些操作的旧数据写入到UNDO段。UNDO数据也称为回滚数据，用于确保数据的一致性。作用包括：回退事、读一致性、事务恢复、闪回查询。9i开始，管理UNDO数据可以使用UNDO表空间，也可以使用回滚段。10g开始，ORACLE已经放弃使用回滚段。提到UNDO表空间，不得不提UNDO段。UNDO Segment分为两个部分，一个是UNDO Segment Head，还有一个是UNDO Segment Block（也称为事务槽）。UNDO Segment Head中包含了这个回滚段的事务信息，而且有一个指针指向Undo Segment Block。UNDO表空间是非常重要的，如果丢失，会出现无法对数据进行更新。平时的数据库管理中应该注意UNDO表空间的空间是否足够，采用自动扩展还是限制大小，undo_retention值的设定等等。二备份与恢复UNDO表空间讲解备份与恢复UNDO表空间，首先要有备份。使用RMAN备份完成后，我们模拟UNDO表空间丢失。此时做更新操作仍然成功，因为shared pool和buffer cache存放了更新的信息。如果我们刷新shared pool和buffer cache，再做连接用户或者更新操作，会提示数据文件找不到。因为UNDO表空间丢失，并且UNDO表空间不可脱机，所以我们不能在数据库运行状态下对UNDO表空间进行恢复。这就要求我们关闭数据库进行恢复操作。如果在真实环境中进行操作，务必在业务低峰期或者测试库进行操作。我们使用一致性关闭数据库会失败，只有强制关闭。此时参数文件、控制文件正常，只是数据文件不正常，所以我们能把数据库启动到MOUNT状态。启动到MOUNT状态后，我们需要使UNDO表空间数据文件离线，注意此时的数据文件编号。然后登录到RMAN中，还原UNDO表空间数据文件，实际上做了一个拷贝的操作，从备份文件中拷贝UNDO表空间数据文件到数据目录，待拷贝完成后，我们需要对UNDO表空间数据文件进行恢复。恢复完成后，再使UNDO表空间数据文件在线，此时的数据库是MOUNT状态，我们需要打开数据库。如果所有的操作都成功，就可以对数据进行更新。三模拟Step 1，RMAN中备份全库RMAN> BACKUP DATABASE;Starting backup at 12-DEC-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile（s） in backup set
input datafile file number=00001 name=/u01/oracle/oradata/justdb/system01.dbf
input datafile file number=00002 name=/u01/oracle/oradata/justdb/sysaux01.dbf
input datafile file number=00003 name=/u01/oracle/oradata/justdb/undotbs01.dbf
input datafile file number=00004 name=/u01/oracle/oradata/justdb/users01.dbf
channel ORA_DISK_1: starting piece 1 at 12-DEC-13
channel ORA_DISK_1: finished piece 1 at 12-DEC-13
piece handle=/u01/oracle/fast_recovery_area/JUSTDB/backupset/2013_12_12/o1_mf_nnndf_TAG20131212T095816_9bl61rrn_.bkp tag=TAG20131212T095816 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile（s） in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 12-DEC-13
channel ORA_DISK_1: finished piece 1 at 12-DEC-13
piece handle=/u01/oracle/fast_recovery_area/JUSTDB/backupset/2013_12_12/o1_mf_ncsnf_TAG20131212T095816_9bl62lw2_.bkp tag=TAG20131212T095816 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 12-DEC-13 Step 2，模拟UNDO表空间丢失SQL> CONN / AS SYSDBA
Connected.
SQL> HO mv /u01/oracle/oradata/justdb/undotbs01.dbf /opt/learn/ Step 3，SQL Plus中连接到sys用户，刷新shared pool和buffer cacheSQL> CONN / AS SYSDBA
Connected.SQL> CONN / AS SYSDBA
Connected.
SQL> ALTER SYSTEM FLUSH shared_pool;System altered.SQL> ALTER SYSTEM FLUSH buffer_cache;System altered. Step 4，SQL Plus连接到scoot用户，发现报ORA-01110错误，数据文件不能找到SQL> CONN SCOTT/tiger;
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-01116: error in opening database file 3
ORA-01110: data file 3: "/u01/oracle/oradata/justdb/undotbs01.dbf"
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Warning: You are no longer connected to ORACLE. Step 5，SQL Plus一致性关闭数据库，失败，只有强制关闭数据库SQL> CONN / AS SYSDBA
CONN / AS SYSDBA
Connected.
SQL> SHUTDOWN IMMEDIATE;
ORA-01116: error in opening database file 3
ORA-01110: data file 3: "/u01/oracle/oradata/justdb/undotbs01.dbf"
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> SHUTDOWN ABORT;
ORACLE instance shut down. Step 6，再次登录到SQL Plus，启动数据库到MOUNT状态[oracle@orcl ~]$ sqlplus
[uniread] Loaded history （157 lines）SQL*Plus: Release 11.2.0.3.0 Production on Thu Dec 12 10:37:52 2013Copyright （c） 1982, 2011, Oracle. All rights reserved.Connected to an idle instance.SQL> STARTUP MOUNT;
ORACLE instance started.Total System Global Area 1269366784 bytes
Fixed Size 2227984 bytes
Variable Size 754974960 bytes
Database Buffers 503316480 bytes
Redo Buffers 8847360 bytes
Database mounted. Step 7，SQL Plus中使3号文件（UNDO表空间）离线SQL> ALTER DATABASE DATAFILE 3 OFFLINE;Database altered. Step 8，使用sys用户登录到RMAN[oracle@orcl ~]$ uniread rman target /
[uniread] Loaded history （96 lines）Recovery Manager: Release 11.2.0.3.0 - Production on Thu Dec 12 10:38:26 2013Copyright （c） 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: JUSTDB （DBID=57321598, not open）RMAN> Step 9，RMAN中还原3号文件RMAN> RESTORE DATAFILE 3;Starting restore at 12-DEC-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISKchannel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile（s） to restore from backup set
channel ORA_DISK_1: restoring datafile 00003 to /u01/oracle/oradata/justdb/undotbs01.dbf
channel ORA_DISK_1: reading from backup piece /u01/oracle/fast_recovery_area/JUSTDB/backupset/2013_12_12/o1_mf_nnndf_TAG20131212T095816_9bl61rrn_.bkp
channel ORA_DISK_1: piece handle=/u01/oracle/fast_recovery_area/JUSTDB/backupset/2013_12_12/o1_mf_nnndf_TAG20131212T095816_9bl61rrn_.bkp tag=TAG20131212T095816
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 12-DEC-13 Step 10，RMAN中恢复3号文件RMAN> RECOVER DATAFILE 3;
RECOVER DATAFILE 3;Starting recover at 12-DEC-13
using channel ORA_DISK_1starting media recovery
media recovery complete, elapsed time: 00:00:00Finished recover at 12-DEC-13 Step 11，SQL Plus中使3号数据文件在线SQL> ALTER DATABASE DATAFILE 3 ONLINE;Database altered. Step 12，SQL Plus中打开数据库SQL> ALTER DATABASE OPEN;Database altered. Step 13，SQL Plus查看数据，插入数据，成功SQL> SELECT * FROM scott.dept; DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTONSQL> INSERT INTO dept VALUES（89,"GZ","DBA"）;1 row created.SQL> COMMIT;Commit complete.推荐阅读：RMAN 配置归档日志删除策略 http://www.linuxidc.com/Linux/2013-11/92670.htmOracle基础教程之通过RMAN复制数据库 http://www.linuxidc.com/Linux/2013-07/87072.htmRMAN备份策略制定参考内容 http://www.linuxidc.com/Linux/2013-03/81695.htmRMAN备份学习笔记 http://www.linuxidc.com/Linux/2013-03/81892.htmOracle数据库备份加密 RMAN加密 http://www.linuxidc.com/Linux/2013-03/80729.htm更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12RMAN备份与恢复之可脱机数据文件丢失RMAN备份与恢复之基于时间点的不完全恢复相关资讯 rman备份 undo表空间

RMAN备份报 RMAN-06059 错误（05月13日）
RMAN备份文件远大于数据库大小的原（01月09日）
Oracle RMAN备份之控制文件备份（12/20/2015 20:07:06）

RMAN备份策略修正案例实录（02月29日）
Oracle 11g R2 RAC RMAN备份脚本示（12/27/2015 11:33:20）
如何删除回滚段状态为NEEDS （09/02/2015 19:28:49）

本文评论查看全部评论（0）