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>

Comment

*

沪ICP备14014813号-2

沪公网安备 31010802001379号