Oracle RMAN针对丢失undo数据文件的恢复场景

如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!

诗檀软件专业数据库修复团队

服务热线 ： 13764045638    QQ号:47079569    邮箱：service@parnassusdata.com

RMAN撤消数据文件丢失

情景 2:  撤消数据文件丢失和数据库不完全关闭

该情景中, 由于停电数据库不完全关闭，你丢失了撤销数据文件存在于的硬件驱动，为创建该情景，执行下列步骤:

为了用一些数据填充撤销数据文件，从 dba_objects创建一个表，通过不进行下列交易更新任何列:

SQL> create table t as select * from dba_objects; Table created. SQL> update t set owner=null; 49814 rows updated. SQL>

• 现在使用shutdown abort命令关闭数据库，移动撤销数据文件到另一个位置:

SQL> shutdown abort ORACLE instance shut down. [oracle@locahost db2]$ mv undotbs01.dbf undotbs01.dbf_backup SQL> startup ORACLE instance started. Database mounted. ORA-01157: cannot identify/lock data file 2 - see DBWR trace file ORA-01110: data file 2: '/u01/oracle/product/10.2.0/db_1/oradata/db2/undotbs01.dbf'

如果收到上述错误，关闭实例，从备份获取撤销数据文件, 然后安装数据库并恢复。

SQL> shutdown immediate ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL> host [oracle@locahost oradata]$ cp db2_backup/undotbs01.dbf db_2/undotbs01.dbf SQL> startup mount Database mounted. SQL> recover datafile 2; ORA-00279: change 447670 generated at 07/12/2010 00:51:24 needed for thread 1 ORA-00289: suggestion : /u01/oracle/product/10.2.0/db_1/flash_recovery_area/DB2/ archivelog/2010_07_12/o1 _mf_1_1_%u_.arc ORA-00280: change 447670 for thread 1 is in sequence #1 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} AUTO Log applied. Media recovery complete.

现在打开数据库，查询表，因为还没有提交，你不会在表中看到更新:

SQL> alter database open; Database altered. SQL> select count(*) from t where owner is null; COUNT(*) ---------- 0 SQL>

情景 3:  撤销数据文件丢失，数据库运行

假设数据库正在运行，你突然意识到因为硬盘故障，撤销数据文件丢失， 如果那个数据文件中没有活动交易，也没有正从撤销中持续读取的查询，那么不要关闭数据库，尝试在数据库运行时解决问题，这可能会更容易。

这种情况下, 你有两个解决方法:

• 创建一个新的撤销表空间，删除丢失的那个
• 从备份中获得丢失的撤销数据文件并恢复

如果你使用第一种方法解决问题会很好，成功了，很好，失败了，那你必须使用第二种方法，为创建这种环境，更新表，删除撤销数据文件，然后刷新缓冲区高速缓存，并再次查询表。

SQL> create table t as select * from all_objects where rownum<10; Table created. SQL> select count(1) from t; COUNT(1) ---------- 9 SQL> update t set owner='test'; 9 rows updated. #Delte undo datafile SQL> alter system flush buffer_cache; System altered. SQL> select count(1) from t where owner='test'; COUNT(1) ---------- 9 SQL> exit ERROR: ORA-00603: ORACLE server session terminated by fatal error

出现一个错误, 因此检查 alert.log 文件:

Errors in file /u01/oracle/product/10.2.0/db_1/admin/db3/bdump/db3_pmon_5039.trc: ORA-00376: file 2 cannot be read at this time ORA-01110: data file 2: '/u01/oracle/product/10.2.0/db_1/oradata/db3/undotbs01.dbf' [oracle@locahost bdump]$

登录到SQL*Plus 并尝试再次查询表:

SQL> conn usr/usr Connected. SQL> select count(1) from t where owner='test'; select count(1) from t where owner='test' * ERROR at line 1: ORA-00376: file 2 cannot be read at this time ORA-01110: data file 2: '/u01/oracle/product/10.2.0/db_1/oradata/db3/undotbs01.dbf'

Oracle 尝试从撤销数据文件获取数据，失败了，通过创建一个新的撤销表空间尝试第一种方法。

SQL> create undo tablespace undotbs2 datafile '/u01/oracle/product/10.2.0/db_1/oradata/db3/undotbs02.dbf' size 100m; create undo tablespace undotbs2 datafile '/u01/oracle/product/10.2.0/db_1/oradata/db3/undotbs02.dbf' size 100m * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-00376: file 2 cannot be read at this time ORA-01110: data file 2: '/u01/oracle/product/10.2.0/db_1/oradata/db3/undotbs01.dbf'

不幸的是,这是不可能的，因此只有一个选择:  从备份复原数据文件并执行恢复,  

[oracle@locahost db3]$ mv undotbs01.dbf_backup undotbs01.dbf

SQL> conn / as sysdba Connected. SQL> recover datafile 2; ORA-00279: change 466951 generated at 07/12/2010 02:46:31 needed for thread 1 ORA-00289: suggestion : /u01/ORACLE/product/10.2.0/db_1/flash_recovery_area/DB3/ archivelog/2010_07_12/o1 _mf_1_2_%u_.arc ORA-00280: change 466951 for thread 1 is in sequence #2 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} AUTO ORA-00279: change 467217 generated at 07/12/2010 02:52:29 needed for thread 1 ORA-00289: suggestion : /u01/ORACLE/product/10.2.0/db_1/flash_recovery_area/DB3/ archivelog/2010_07_12/o1 _mf_1_3_%u_.arc ORA-00280: change 467217 for thread 1 is in sequence #3 ORA-00278: log file '/u01/ORACLE/product/10.2.0/db_1/flash_recovery_area/DB3/ archivelog/2010_07_12/o 1_mf_1_2_63ogxf8f_.arc' no longer needed for this recovery ORA-00603: ORACLE server session terminated by fatal error ERROR: ORA-03114: not connected to ORACLE

服务器会话终止，因为随后的重做不处于归档日志但在联机重做日志文件中， alter database datafile online  命令可以重新同步数据文件。

SQL> conn / as sysdba Connected. SQL> alter database datafile 2 online; Database altered. SQL> select count(1) from usr.t where owner='test'; COUNT(1) ---------- 0 SQL>