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

蔚落 2022-09-20 12:35 304阅读 0赞

## 一 UNDO表空间讲解 ##

在上一篇文章（[RMAN备份与恢复之可脱机数据文件丢失][RMAN]）中，我们讲到可脱机数据文件丢失怎么处理，这篇文章我们讲解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 cache

>     SQL> 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 2013
>     
>     Copyright (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 2013
>     
>     Copyright (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=DISK
>     
>     channel 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_1
>     
>     starting media recovery
>     media recovery complete, elapsed time: 00:00:00
>     
>     Finished 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   BOSTON
>     
>     SQL> INSERT INTO dept VALUES(89,'GZ','DBA');
>     
>     1 row created.
>     
>     SQL> COMMIT;
>     
>     Commit complete.

## 四 相关文章 ##

*  [RMAN备份与恢复之控制文件丢失][RMAN 1]
 *  [RMAN备份与恢复之可脱机数据文件丢失][RMAN]
 *  [RMAN备份与恢复之基于时间点的不完全恢复][RMAN 2]

>     我的邮箱：wgbno27@gmail.com   新浪微博：@jutdb         
>       微信公众平台：JustOracle（微信号：justoracle）
>       数据库技术交流群：336882565（加群时验证 From CSDN XXX）
>       All is well
>       2014年1月16日
>       By Larry Wen

<table style="border-bottom:#48c5db 5px double; border-left:#48c5db 5px double; padding-bottom:5px; background-color:black; padding-left:5px; border-spacing:0; width:100%; padding-right:30px; height:90px; border-top:#48c5db 5px double; border-right:#48c5db 5px double; padding-top:5px"> 
 <tbody> 
  <tr> 
   <td><img alt="katoon" src="https://img-my.csdn.net/uploads/201301/06/1357456262_5409.jpg" style="height:80px"></td> 
   <td style="text-align:right"><a title="新浪微博求粉 ^_^" href="http://weibo.com/wentasy" rel="nofollow"><img title="新浪微博求粉 ^_^" alt="Sina" src="https://img-my.csdn.net/uploads/201210/26/1351182273_5476.png.thumb.jpg" style="height:40px"></a>&nbsp;<a title="我的CSDN博客——HelloWorld" href="http://blog.csdn.net/Wentasy" rel="nofollow"><img title="我的CSDN博客——HelloWorld" alt="CSDN" src="https://img-my.csdn.net/uploads/201210/26/1351182268_8254.png.thumb.jpg" style="height:40px"></a></td> 
  </tr> 
  <tr> 
   <td style="text-align:right; font-family:microsoft yahei; color:white; font-size:12px"> <a title="我的CSDN博客——HelloWorld" href="http://blog.csdn.net/Wentasy" rel="nofollow">@Wentasy</a> 博文仅供参考，欢迎大家来访。如有错误之处，希望批评指正。原创博文如需转载请注明出处，谢谢 :) [CSDN博客]</td> 
  </tr> 
 </tbody> 
</table>

[RMAN]: http://blog.csdn.net/justdb/article/details/18304253
[RMAN 1]: http://blog.csdn.net/justdb/article/details/18253717
[RMAN 2]: http://blog.csdn.net/justdb/article/details/18402459