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

当前位置: 数据库>oracle

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

来源: 互联网发布时间：2017-05-30

本文导语: 一 UNDO表空间讲解在上一篇文章（RMAN备份与恢复之可脱机数据文件丢失）中，我们讲到可脱机数据文件丢失怎么处理，这篇文章我们讲解UNDO表空间丢失的解决办法。 UNDO表空间用于存放UNDO数据，当执行DML操作（INSERT、UPDATE、DEL...

一 UNDO表空间讲解

在上一篇文章（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

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

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查看数据，插入数据，成功