归档模式下恢复没有备份的数据文件

测试环境 SQL select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production PL/SQL Release 11.2.0.4.0 - Producti

测试环境

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

创建表空间并设置用户

SQL> create tablespace t1 datafile '+data' size 20m;
Tablespace created.
SQL>
SQL> alter user test default tablespace t1;
User altered.
SQL> create table tt1 as select * from user_objects;
Table created.

SQL> select count(*) from tt1;
COUNT(*)
----------
6
SQL> select t.ts#, d.file#, d.name from v$tablespace t, v$datafile d where t.name='T1' and t.ts# = d.ts#;
TS# FILE# NAME
---------- ---------- --------------------------------------------------------------------------------
19 13 +DATA/key/datafile/t1.275.851106659
19 14 +DATA/key/datafile/t1.276.851107957

offline数据文件并在asm磁盘组删除相应数据文件

SQL> alter database datafile 13 offline;
Database altered.
ASMCMD> rm t1.275.851106659
ASMCMD> pwd
+data/key/datafile
ASMCMD>

测试

SQL> conn test/oracle;
Connected.
SQL> select * from tt1;
select * from tt1
*
ERROR at line 1:
ORA-00376: file 13 cannot be read at this time
ORA-01110: data file 13: '+DATA/key/datafile/t1.275.851109775'



online数据文件报错
SQL> alter database datafile 13 online;
alter database datafile 13 online
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 13 - see DBWR trace file
ORA-01110: data file 13: '+DATA/key/datafile/t1.275.851106659'

查看trace文件内容

key_dbw0_21473.trc

……
ORA-01157: cannot identify/lock data file 13 - see DBWR trace file
ORA-01110: data file 13: '+DATA/key/datafile/t1.275.851106659'
ORA-17503: ksfdopn:2 Failed to open file +DATA/key/datafile/t1.275.851106659
ORA-15012: ASM file '+DATA/key/datafile/t1.275.851106659' does not exist

……

提示没有相应数据文件，可以通过alter database create datafile创建相应数据文件

绘蛙

电商场景的AI创作平台，无需高薪聘请商拍和文案团队，使用绘蛙即可低成本、批量创作优质的商拍图、种草文案

179

查看详情

SQL> alter database create datafile 13;


Database altered.




ASMCMD> ls
ELITEMASTER.274.849888559
MGMT_AD4J_TS.268.840720903
MGMT_ECM_DEPOT_TS.266.840720899
MGMT_TABLESPACE.267.840720901
SYSAUX.261.840712503
SYSTEM.260.840712485
T1.275.851109775
T1.276.851107957
TEST.270.842550281
UNDOTBS1.262.840712517
USERS.264.840712529

对比新增文件名T1.275.851109775 和原有数据文件+DATA/key/datafile/t1.275.851106659名称不一样

rename 数据文件并recover 然后online 打开查看

SQL> alter database rename file '+DATA/key/datafile/t1.275.851106659' to '+DATA/key/datafile/T1.275.851109775';
Database altered.
SQL> recover datafile 13;
Media recovery complete.


SQL> alter database datafile 13 online;
Database altered.
SQL> conn test/oracle
Connected.
SQL> select count(*) from tt1;
COUNT(*)
----------
6

恢复成功！

这个也同样适用于rac 恢复！