sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 故障案例 >

【案例】Oracle报错ORA-01100 数据文件丢失后使用归档恢复数据

时间:2016-10-26 20:31   来源:Oracle研究中心   作者:HTZ   点击:

天萃荷净 Oracle研究中心案例分析:运维DBA反映生产Oracle环境数据库数据文件丢失.使用创建数据文件以后的所有归档进行恢复数据文件。ORA-01100 数据文件丢失.无备份.有创建数据文件以后的所有归档的恢复。

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

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.修改数据库为归档

oracleplus.net>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
oracleplus.net>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
oracleplus.net>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.
oracleplus.net>alter database archivelog ;

Database altered.

oracleplus.net>alter database open;

Database altered.

3.创建表空间与测试表

oracleplus.net>create tablespace htz01 datafile ‘/oracle/app/oracle/oradata/orcl1124/htz0101.dbf’ size 10m;

Tablespace created.

oracleplus.net>drop tabOracleoracleplus.netle scott.htz;

Table dropped.

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

Table created.

oracleplus.net>select count(*) from scott.htz;

COUNT(*)
———-
1
oracleplus.net>alter system archive log current;

System altered.

oracleplus.net>/

System altered.

oracleplus.net>/

System altered.

oracleplus.net>/

System altered.

oracleplus.net>shutdown abort;
ORACLE instance shut down.
oracleplus.net>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@oracleplus.net orcl1124]$rm htz0101.dbf
[oracle@oracleplus.net orcl1124]$rm htz0101.dbf

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

[oracle@oracleplus.net 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.

oracleplus.net>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.处理过程

确认数据文件是否存在
oracleplus.net>!ls -l /oracle/app/oracle/oradata/orcl1124/htz0101.dbf
ls: /oracle/app/oracle/oradata/orcl1124/htz0101.dbf: No such file or directory
重建数据文件
oracleplus.net>alter database create datafile ‘/oracle/app/oracle/oradata/orcl1124/htz0101.dbf’;

Database altered.
recover数据文件
oracleplus.net>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: {=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
oracleplus.net>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’

oracleplus.net>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: {=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
oracleplus.net>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: {=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

oracleplus.net>create pfile=’/tmp/123.ora’ from spfile;

File created.

oracleplus.net>!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

oracleplus.net>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


oracleplus.net>alter database open resetlogs;

Database altered.
数据库已经正常打开 。
整个实验结束.无任何实际作用

本文固定链接: http://www.htz.pw/2014/07/22/ora-01100-%e6%95%b0%e6%8d%ae%e6%96%87%e4%bb%b6%e4%b8%a2%e5%a4%b1%ef%bc%8c%e6%97%a0%e5%a4%87%e4%bb%bd%ef%bc%8c%e6%9c%89%e5%88%9b%e5%bb%ba%e6%95%b0%e6%8d%ae%e6%96%87%e4%bb%b6%e4%bb%a5%e5%90%8e%e7%9a%84.html | 认真就输

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【案例】Oracle报错ORA-01100 数据文件丢失后使用归档恢复数据

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1077.html

Oracle研究中心

关键词:

Oracle数据文件丢失使用创建数据文件以后的所有归档进行恢复数据

Oracle报错ORA-01100解决办法

Oracle数据文件丢失使用归档恢复数据文件