当前位置： 首页 > 备份与恢复, 特殊恢复 > 正文

ORA-01100 数据文件丢失，无备份，有创建数据文件以后的所有归档的恢复

huangtingzhong 发布于 2014-07-22 23:42备份与恢复, 特殊恢复抢沙发

本次实验是测试在数据文件丢失，无备份，有归档日志的情况下的恢复，是看到itpub一个人的提问，这种情况在生产环境中基本不存在，因为不可能会保留那么的归档日志。整个实验的前提条件是：数据库运行在归档模式，并且从创建数据文件文件以来的归档日志必须存在。

 

欢迎大家加入ORACLE超级群：17115662 免费解决各种ORACLE问题,以后BLOG将迁移到http://www.htz.pw

 

1，数据库版本

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

2，修改数据库为归档

www.htz.pw > archive log list;

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     22

Current log sequence           24

www.htz.pw > shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

www.htz.pw > startup mount;

ORACLE instance started.

 

Total System Global Area 1068937216 bytes

Fixed Size                  2260088 bytes

Variable Size             184550280 bytes

Database Buffers          876609536 bytes

Redo Buffers                5517312 bytes

Database mounted.

www.htz.pw > alter database archivelog ;

 

Database altered.

 

www.htz.pw > alter database open;

 

Database altered.

3，创建表空间与测试表

www.htz.pw > create tablespace htz01 datafile ‘/oracle/app/oracle/oradata/orcl1124/htz0101.dbf’ size 10m;

 

Tablespace created.

 

www.htz.pw > drop table scott.htz;

 

Table dropped.

 

www.htz.pw > create table scott.htz as select * from dba_objects where rownum=1;

 

Table created.

 

www.htz.pw > select count(*) from scott.htz;

 

  COUNT(*)

———-

         1

www.htz.pw > alter system archive log current;

 

System altered.

 

www.htz.pw > /

 

System altered.

 

www.htz.pw > /

 

System altered.

 

www.htz.pw > /

 

System altered.

 

www.htz.pw > shutdown abort;

ORACLE instance shut down.

www.htz.pw > exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

4，删除数据文件与在线日志

[oracle@www.htz.pw orcl1124]$rm htz0101.dbf

[oracle@www.htz.pw orcl1124]$rm htz0101.dbf

5，启动数据库报ORA-01110错误

[oracle@www.htz.pw orcl1124]$sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.4.0 Production on Sat Jul 12 02:06:50 2014

 

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

 

Connected to an idle instance.

 

www.htz.pw > startup

ORACLE instance started.

 

Total System Global Area 1068937216 bytes

Fixed Size                  2260088 bytes

Variable Size             184550280 bytes

Database Buffers          876609536 bytes

Redo Buffers                5517312 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 3 – see DBWR trace file

ORA-01110: data file 3: ‘/oracle/app/oracle/oradata/orcl1124/htz0101.dbf’

6，处理过程

确认数据文件是否存在

www.htz.pw > !ls -l /oracle/app/oracle/oradata/orcl1124/htz0101.dbf

ls: /oracle/app/oracle/oradata/orcl1124/htz0101.dbf: No such file or directory

重建数据文件

www.htz.pw > alter database create datafile ‘/oracle/app/oracle/oradata/orcl1124/htz0101.dbf’;

 

Database altered.

recover数据文件

www.htz.pw > recover datafile 3;

ORA-00279: change 3483723 generated at 07/12/2014 02:04:32 needed for thread 1

ORA-00289: suggestion :

/oracle/app/oracle/fast_recovery_area/ORCL1124/archivelog/2014_07_12/o1_mf_1_24_

9w09vh01_.arc

ORA-00280: change 3483723 for thread 1 is in sequence #24

 

 

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

cancel

Media recovery cancelled.

www.htz.pw > alter database open;

alter database open

*

ERROR at line 1:

ORA-01113: file 3 needs media recovery

ORA-01110: data file 3: ‘/oracle/app/oracle/oradata/orcl1124/htz0101.dbf’

 

www.htz.pw > recover datafile 3;

ORA-00279: change 3483723 generated at 07/12/2014 02:04:32 needed for thread 1

ORA-00289: suggestion :

/oracle/app/oracle/fast_recovery_area/ORCL1124/archivelog/2014_07_12/o1_mf_1_24_

9w09vh01_.arc

ORA-00280: change 3483723 for thread 1 is in sequence #24

 

 

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

auto

ORA-00279: change 3484047 generated at 07/12/2014 02:05:34 needed for thread 1

ORA-00289: suggestion :

/oracle/app/oracle/fast_recovery_area/ORCL1124/archivelog/2014_07_12/o1_mf_1_25_

9w09vjoq_.arc

ORA-00280: change 3484047 for thread 1 is in sequence #25

 

 

ORA-00279: change 3484051 generated at 07/12/2014 02:05:36 needed for thread 1

ORA-00289: suggestion :

/oracle/app/oracle/fast_recovery_area/ORCL1124/archivelog/2014_07_12/o1_mf_1_26_

9w09vmmc_.arc

ORA-00280: change 3484051 for thread 1 is in sequence #26

 

 

ORA-00279: change 3484055 generated at 07/12/2014 02:05:39 needed for thread 1

ORA-00289: suggestion :

/oracle/app/oracle/fast_recovery_area/ORCL1124/archivelog/2014_07_12/o1_mf_1_27_

9w09vnlv_.arc

ORA-00280: change 3484055 for thread 1 is in sequence #27

 

 

ORA-00283: recovery session canceled due to errors

ORA-00313: open failed for members of log group 1 of thread 1

ORA-00312: online log 1 thread 1:

‘/oracle/app/oracle/oradata/orcl1124/redo02.log’

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

 

 

ORA-01112: media recovery not started

www.htz.pw > recover database until cancel;

ORA-00279: change 3484059 generated at 07/12/2014 02:05:40 needed for thread 1

ORA-00289: suggestion :

/oracle/app/oracle/fast_recovery_area/ORCL1124/archivelog/2014_07_12/o1_mf_1_28_

%u_.arc

ORA-00280: change 3484059 for thread 1 is in sequence #28

 

 

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

cancel

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1: ‘/oracle/app/oracle/oradata/orcl1124/system01.dbf’

 

 

ORA-01112: media recovery not started

 

www.htz.pw > create pfile=’/tmp/123.ora’ from spfile;

 

File created.

 

www.htz.pw > !vi /tmp/123.ora

orcl1124.__db_cache_size=872415232

orcl1124.__java_pool_size=4194304

orcl1124.__large_pool_size=71303168

orcl1124.__oracle_base=’/oracle/app/oracle’#ORACLE_BASE set from environment

orcl1124.__pga_aggregate_target=130023424

orcl1124.__sga_target=1073741824

orcl1124.__shared_io_pool_size=0

orcl1124.__shared_pool_size=113246208

orcl1124.__streams_pool_size=0

*._optimizer_cartesian_enabled=FALSE

*._optimizer_mjc_enabled=FALSE

*.audit_file_dest=’/oracle/app/oracle/admin/orcl1124/adump’

*.audit_trail=’NONE’

*.compatible=’11.2.0.4.0′

*.control_files=’/oracle/app/oracle/oradata/orcl1124/control01.ctl’,’/oracle/app/oracle/fast_recovery_area/orcl1124/control02.ctl’

*.db_block_size=8192

*.db_cache_size=524288000

*.db_domain=”

*.db_name=’orcl1124′

*.db_recovery_file_dest=’/oracle/app/oracle/fast_recovery_area’

*.db_recovery_file_dest_size=4385144832

*.deferred_segment_creation=TRUE

*.diagnostic_dest=’/oracle/app/oracle’

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orcl1124XDB)’

*.enable_ddl_logging=TRUE

*.event=’10231 trace name context forever,level 10′,’10233 trace name context forever,level 10′

*.log_archive_config=”

*.open_cursors=300

*.parallel_min_servers=0

*.pga_aggregate_target=126877696

*.processes=150

*.remote_login_passwordfile=’EXCLUSIVE’

*.resource_limit=TRUE

*.sga_max_size=1073741824

*.sga_target=1073741824

*.shared_pool_size=109051904

*.undo_tablespace=’UNDOTBS03′

_allow_resetlogs_corruption=true

_allow_error_simulation=true

 

“/tmp/123.ora” 40L, 1450C written

 

www.htz.pw > startup force pfile=’/tmp/123.ora’;

ORACLE instance started.

 

Total System Global Area 1068937216 bytes

Fixed Size                  2260088 bytes

Variable Size             188744584 bytes

Database Buffers          872415232 bytes

Redo Buffers                5517312 bytes

Database mounted.

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

 

 

www.htz.pw > alter database open resetlogs;

 

Database altered.

数据库已经正常打开 。

整个实验结束，无任何实际作用

赏

您可能还会对这些文章感兴趣！

ORA-01100 数据文件丢失，无备份，有创建数据文件以后的所有归档的恢复：等您坐沙发呢！

发表评论

gravatar

? razz sad evil ! smile oops grin eek shock ??? cool lol mad twisted roll wink idea arrow neutral cry mrgreen

快捷键：Ctrl+Enter