如果自己搞不定可以找诗檀软件专业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