新建数据文件丢失，其他文件全部都在的情况恢复

最新推荐文章于 2019-04-03 21:02:32 发布

gltyi99

最新推荐文章于 2019-04-03 21:02:32 发布

阅读量881

点赞数 1

分类专栏： oracle 文章标签： database 数据库 ORA-01157 ORA-01110 备份

本文链接：https://blog.csdn.net/ora01555/article/details/28859863

版权

oracle 专栏收录该内容

25 篇文章 0 订阅

订阅专栏

一、模拟情景

新加数据文件丢失。该文件没有备份，有控制文件和全部归档日志。这种情况下，使用下面的方式恢复数据文件

二、测试环境

SYS@testdb>select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for HPUX: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SYS@testdb>archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /ora_data/oraarch
Oldest online log sequence     38
Next log sequence to archive   40
Current log sequence           40

三、创建新的数据文件和测试表

SYS@testdb>create tablespace owi datafile '/oracle/ora10g/oradata/owi01.dbf' size 50M;

Tablespace created.

SYS@testdb>alter system switch logfile;

System altered.

SYS@testdb>create table t tablespace owi as select * from dba_objects;

Table created.

SYS@testdb>alter system switch logfile;

System altered.

四、模拟数据文件丢失

-->关闭数据库
SYS@testdb>shutdown abort;
ORACLE instance shut down.
-->删除新建文件
tempapp@ora10g[#/home/ora10g]rm /oracle/ora10g/oradata/owi01.dbf

五、恢复

SYS@testdb>startup
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size                  2168928 bytes
Variable Size             530442144 bytes
Database Buffers         1610612736 bytes
Redo Buffers                4259840 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '/oracle/ora10g/oradata/owi01.dbf'
-->检查实例状态
SYS@testdb>select status from v$instance;

STATUS
------------
MOUNTED

-->alert log信息
Fri Jun  6 10:08:09 2014
Errors in file /oracle/ora10g/admin/testdb/bdump/testdb_dbw0_3447.trc:
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '/oracle/ora10g/oradata/owi01.dbf'
ORA-27037: unable to obtain file status
HP-UX Error: 2: No such file or directory
Additional information: 3

-->检查数据文件是否丢失
tempapp@ora10g[#/home/ora10g]ll /oracle/ora10g/oradata
total 7130870
-rw-r-----   1 ora10g     oinstall   7159808 Jun  6 10:09 control01.ctl
-rw-r-----   1 ora10g     oinstall   7159808 Jun  6 10:09 control02.ctl
-rw-r-----   1 ora10g     oinstall   209723392 Jun  4 14:54 jtitsm.data1
-rw-r-----   1 ora10g     oinstall   104858624 Jun  6 10:05 redo01.log
-rw-r-----   1 ora10g     oinstall   104858624 Jun  6 10:06 redo02.log
-rw-r-----   1 ora10g     oinstall   104858624 Jun  6 10:06 redo03.log
-rw-r-----   1 ora10g     oinstall   340795392 Jun  6 10:08 sysaux01.dbf
-rw-r-----   1 ora10g     oinstall   340795392 Jun  6 10:08 system01.dbf
-rw-r-----   1 ora10g     oinstall   1073750016 Jun  6 10:03 system02.dbf
-rw-r-----   1 ora10g     oinstall   209723392 Jun  4 15:18 temptbs01.dbf
-rw-r-----   1 ora10g     oinstall   20979712 Jun  6 10:08 testtbs01.dbf
-rw-r-----   1 ora10g     oinstall   209723392 Jun  6 10:08 undotbs01.dbf
-rw-r-----   1 ora10g     oinstall   1073750016 Jun  6 10:08 users01.dbf
-rw-r-----   1 ora10g     oinstall   52436992 Jun  6 10:08 users02.dbf

-->使用create datafile方式重建数据文件
SYS@testdb>alter database create datafile '/oracle/ora10g/oradata/owi01.dbf';

Database altered.

-->利用日志恢复
SYS@testdb>recover datafile '/oracle/ora10g/oradata/owi01.dbf';
Media recovery complete.

-->打开数据库
SYS@testdb>alter database open;

Database altered.

-->恢复过程中alert log 日志信息
Fri Jun  6 10:10:10 2014
alter database create datafile '/oracle/ora10g/oradata/owi01.dbf'
Fri Jun  6 10:10:11 2014
Completed: alter database create datafile '/oracle/ora10g/oradata/owi01.dbf'
Fri Jun  6 10:10:31 2014
ALTER DATABASE RECOVER  datafile '/oracle/ora10g/oradata/owi01.dbf'  
Fri Jun  6 10:10:31 2014
Media Recovery Start
 parallel recovery started with 5 processes
Fri Jun  6 10:10:31 2014
Errors in file /oracle/ora10g/admin/testdb/bdump/testdb_ora_3551.trc:
Fri Jun  6 10:10:31 2014
Warning: OS async I/O limit 128 is lower than recovery batch 1024
Fri Jun  6 10:10:31 2014
Errors in file /oracle/ora10g/admin/testdb/bdump/testdb_ora_3559.trc:
Fri Jun  6 10:10:31 2014
Errors in file /oracle/ora10g/admin/testdb/bdump/testdb_ora_3557.trc:
Fri Jun  6 10:10:31 2014
Errors in file /oracle/ora10g/admin/testdb/bdump/testdb_ora_3555.trc:
Fri Jun  6 10:10:31 2014
Warning: OS async I/O limit 128 is lower than recovery batch 1024
Fri Jun  6 10:10:31 2014
Warning: OS async I/O limit 128 is lower than recovery batch 1024
Fri Jun  6 10:10:31 2014
Warning: OS async I/O limit 128 is lower than recovery batch 1024
Fri Jun  6 10:10:31 2014
Errors in file /oracle/ora10g/admin/testdb/bdump/testdb_ora_3553.trc:
Fri Jun  6 10:10:31 2014
Warning: OS async I/O limit 128 is lower than recovery batch 1024
Fri Jun  6 10:10:31 2014
Recovery of Online Redo Log: Thread 1 Group 1 Seq 40 Reading mem 0
  Mem# 0: /oracle/ora10g/oradata/redo01.log
Fri Jun  6 10:10:31 2014
Recovery of Online Redo Log: Thread 1 Group 2 Seq 41 Reading mem 0
  Mem# 0: /oracle/ora10g/oradata/redo02.log
Fri Jun  6 10:10:32 2014
Recovery of Online Redo Log: Thread 1 Group 3 Seq 42 Reading mem 0
  Mem# 0: /oracle/ora10g/oradata/redo03.log
Fri Jun  6 10:10:32 2014
Media Recovery Complete (testdb)
Completed: ALTER DATABASE RECOVER  datafile '/oracle/ora10g/oradata/owi01.dbf'

到这里，恢复基本完成了。此案例拥有当前控制文件，控制文件中包含了丢失文件信息，所以可以通过create datafile方式重新创建文件，通过控制文件中记录的文件信息、SCN、检查点等信息，应用归档日志进行恢复，可以完成完全恢复。