> 데이터 베이스 > MySQL 튜토리얼 > RMAN备份与恢复之UNDO表空间丢失

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

WBOY
풀어 주다: 2016-06-07 17:34:44
원래의
845명이 탐색했습니다.

UNDO表空间用于存放UNDO数据,当执行DML操作(INSERT、UPDATE、DELETE)的时候,ORACLE会将这些操作的旧数据写入到UNDO段。UNDO数

一 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

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> STARTUP MOUNT;
ORACLE instance started.

원천:php.cn
본 웹사이트의 성명
본 글의 내용은 네티즌들의 자발적인 기여로 작성되었으며, 저작권은 원저작자에게 있습니다. 본 사이트는 이에 상응하는 법적 책임을 지지 않습니다. 표절이나 침해가 의심되는 콘텐츠를 발견한 경우 admin@php.cn으로 문의하세요.
최신 이슈
인기 튜토리얼
더>
최신 다운로드
더>
웹 효과
웹사이트 소스 코드
웹사이트 자료
프론트엔드 템플릿