我们的文章会在微信公众号IT民工的龙马人生和博客网站( www.htz.pw )同步更新 ，欢迎关注收藏，也欢迎大家转载，但是请在文章开始地方标注文章出处，谢谢！
由于博客中有大量代码，通过页面浏览效果更佳。
Oracle故障处理：轻松搞定ORA-01190报错
ORA-01190这个错误比较常见，特别在没有正规DBA运维的环境中比较常见，
先看看ORACLE官方是怎么解决这个报错的
SQL> !oerr ora 0119001190, 00000, “control file or data file %s is from before the last RESETLOGS”// *Cause: Attempting to use a data file when the log reset information in// the file does not match the control file. Either the data file// or the control file is a backup that was made before the most// recent ALTER DATABASE OPEN RESETLOGS.// *Action: Restore file from a more recent backup.
1，数据库的版本
SQL> select * from v$version;BANNER——————————————————————————–Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit ProductionPL/SQL Release 11.2.0.4.0 – ProductionCORE 11.2.0.4.0 ProductionTNS for Linux: Version 11.2.0.4.0 – ProductionNLSRTL Version 11.2.0.4.0 – Production
2，现象模拟
这里我们通过使用online一个数据文件后，使用open resetlogs方式来打开数据库，这样offline的数据文件就成功resetlogs之间的数据文件，实验环境要求数据库运行在归档模式，其它非归档模式也是一样的，只是需要再侯scn的值
SQL> drop tablespace htz including contents and datafiles;Tablespace dropped.SQL> select name from v$dbfile where rownum=1;NAME——————————————————————————–/oracle/app/oracle/oradata/orcl1124/users01.dbfSQL> create tablespace htz datafile ‘/oracle/app/oracle/oradata/orcl1124/htz01.dbf’ size 10m autoextend on maxsize 10G;Tablespace created.SQL> create table scott.htz tablespace htz as select * from dba_objects;Table created.SQL> startup mount;ORACLE instance started.Total System Global Area 379965440 bytesFixed Size 2253464 bytesVariable Size 171969896 bytesDatabase Buffers 201326592 bytesRedo Buffers 4415488 bytesDatabase mounted.SQL> alter database archivelog;Database altered.SQL> !rm /tmp/control.txtSQL> alter database backup controlfile to trace as ‘/tmp/control.txt’;Database altered.SQL> shutdown immediate;ORA-01109: database not openDatabase dismounted.ORACLE instance shut down.SQL> startup nomount;ORACLE instance started.Total System Global Area 379965440 bytesFixed Size 2253464 bytesVariable Size 171969896 bytesDatabase Buffers 201326592 bytesRedo Buffers 4415488 bytesSQL> CREATE CONTROLFILE REUSE DATABASE “ORCL1124” RESETLOGS ARCHIVELOG2 MAXLOGFILES 163 MAXLOGMEMBERS 34 MAXDATAFILES 1005 MAXINSTANCES 86 MAXLOGHISTORY 2927 LOGFILE8 GROUP 1 ‘/oracle/app/oracle/oradata/orcl1124/redo01.log’ SIZE 50M BLOCKSIZE 512,9 GROUP 2 ‘/oracle/app/oracle/oradata/orcl1124/redo02.log’ SIZE 50M BLOCKSIZE 512,10 GROUP 3 ‘/oracle/app/oracle/oradata/orcl1124/redo03.log’ SIZE 50M BLOCKSIZE 51211 — STANDBY LOGFILE12 DATAFILE13 ‘/oracle/app/oracle/oradata/orcl1124/system01.dbf’,14 ‘/oracle/app/oracle/oradata/orcl1124/sysaux01.dbf’,15 ‘/oracle/app/oracle/oradata/orcl1124/undotbs01.dbf’,16 ‘/oracle/app/oracle/oradata/orcl1124/users01.dbf’,17 ‘/oracle/app/oracle/oradata/orcl1124/htz01.dbf’18 CHARACTER SET ZHS16GBK19 ;Control file created.SQL> set lines 200SQL> col name for a60SQL> select * from v$dbfile;FILE# NAME———- ————————————————————5 /oracle/app/oracle/oradata/orcl1124/htz01.dbf4 /oracle/app/oracle/oradata/orcl1124/users01.dbf3 /oracle/app/oracle/oradata/orcl1124/undotbs01.dbf2 /oracle/app/oracle/oradata/orcl1124/sysaux01.dbf1 /oracle/app/oracle/oradata/orcl1124/system01.dbfSQL> alter database datafile 5 offline;Database altered.SQL> recover database using backup controlfile until cancel;ORA-00279: change 1277201 generated at 04/22/2014 18:06:17 needed for thread 1ORA-00289: suggestion : /oracle/app/oracle/fast_recovery_area/ORCL1124/archivelog/2014_04_22/o1_mf_1_90_%u_.arcORA-00280: change 1277201 for thread 1 is in sequence #90Specify log: {<RET>=suggested | filename | AUTO | CANCEL}cancelMedia recovery cancelled.SQL> alter database open resetlogs;Database altered.SQL> alter database datafile 5 online;alter database datafile 5 online*ERROR at line 1:ORA-01190: control file or data file 5 is from before the last RESETLOGSORA-01110: data file 5: ‘/oracle/app/oracle/oradata/orcl1124/htz01.dbf’
这里看到报错了。
3，故障处理办法
其实处理的方式还是多样的，这里主要介绍两种方式，1，是通过增加scn的值来处理，2，通过bbed来修改resetlog_scn的值来处理。
查看当前数据文件的reset scn与reset time的值
SQL> select hxfil file_id,2 FHRLC “RESET TIME”,3 fhrlc_i “RESET COUNT”,4 FHRLS “RESET SCN”,5 FHPRC “LAST RESET TIME”,6 FHPRC_I “LAST RESET COUNT”,7 FHPRS “LAST RESET SCN”8 from x$kcvfh;FILE_ID RESET TIME RESET COUNT RESET SCN LAST RESET TIME LAST RESET COUNT LAST RESET SCN———- ——————– ———– —————- ——————– —————– —————-1 04/22/2014 18:09:05 845575745 1277202 04/16/2014 21:01:21 845067681 9257022 04/22/2014 18:09:05 845575745 1277202 04/16/2014 21:01:21 845067681 9257023 04/22/2014 18:09:05 845575745 1277202 04/16/2014 21:01:21 845067681 9257024 04/22/2014 18:09:05 845575745 1277202 04/16/2014 21:01:21 845067681 9257025 04/16/2014 21:01:21 845067681 925702 08/24/2013 11:37:30 824297850 1
这里可以看到数据库5的reset scn的值跟其实的不一样。
其实我们也可以通过dump文件头的信息，如果下：
SQL> oradebug dump FILE_HDRS 3;Statement processed.SQL> oradebug tracefile_name;/oracle/app/oracle/diag/rdbms/orcl1124/orcl1124/trace/orcl1124_ora_10672.trcDATA FILE #5:name #4: /oracle/app/oracle/oradata/orcl1124/htz01.dbfcreation size=0 block size=8192 status=0xc head=4 tail=4 dup=1tablespace 6, index=5 krfil=5 prev_file=0unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00Checkpoint cnt:4294967295 scn: 0x0000.00137d11 04/22/2014 18:06:17Stop scn: 0x0000.00137d15 04/22/2014 18:09:16Creation Checkpointed at scn: 0x0000.00132bd4 04/22/2014 18:04:33thread:0 rba:(0x0.0.0)Tablespace #6 – HTZ rel_fn:5Creation at scn: 0x0000.00132bd4 04/22/2014 18:04:33Backup taken at scn: 0x0000.00000000 01/01/1988 00:00:00 thread:0reset logs count:0x325eb5a1 scn: 0x0000.000e2006prev reset logs count:0x3121c97a scn: 0x0000.00000001recovered at 04/22/2014 18:06:07status:0x0 root dba:0x00000000 chkpt cnt: 6 ctl cnt:5Tablespace #4 – USERS rel_fn:4Creation at scn: 0x0000.00003f0f 08/24/2013 11:37:49Backup taken at scn: 0x0000.00000000 01/01/1988 00:00:00 thread:0reset logs count:0x32667641 scn: 0x0000.00137d12prev reset logs count:0x325eb5a1 scn: 0x0000.000e2006recovered at 04/22/2014 18:08:57status:0x4 root dba:0x00000000 chkpt cnt: 194 ctl cnt:193begin-hot-backup file size: 0Checkpointed at scn: 0x0000.00137d15 04/22/2014 18:09:16thread:1 rba:(0x1.2.10)Tablespace #2 – UNDOTBS1 rel_fn:3Creation at scn: 0x0000.000e16c0 08/24/2013 12:07:19Backup taken at scn: 0x0000.00000000 01/01/1988 00:00:00 thread:0reset logs count:0x32667641 scn: 0x0000.00137d12prev reset logs count:0x325eb5a1 scn: 0x0000.000e2006recovered at 04/22/2014 18:08:57status:0x4 root dba:0x00000000 chkpt cnt: 116 ctl cnt:115begin-hot-backup file size: 0Checkpointed at scn: 0x0000.00137d15 04/22/2014 18:09:16
3.1 推进SCN值
这里我使用的是mount状态下直接修改内存的方式
SQL> startup mount;ORACLE instance started.Total System Global Area 379965440 bytesFixed Size 2253464 bytesVariable Size 171969896 bytesDatabase Buffers 201326592 bytesRedo Buffers 4415488 bytesDatabase mounted.SQL> select checkpoint_change# from v$datafile;CHECKPOINT_CHANGE#——————12784631278463127846312784631277201
查看当前数据库的SCN的值，因为这里的SCN的WRAP部分的值是0，所以我们在修改的时候，可以不用考虑WRAP值修改
SQL> oradebug setmypidStatement processed.SQL> oradebug dumpvar sga kcsgscn_kcslf kcsgscn_ [06001AE70, 06001AEA0) = 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 6001AB50 00000000SQL> oradebug poke 0x06001AE70 4 2278463BEFORE: [06001AE70, 06001AE74) = 00000000AFTER: [06001AE70, 06001AE74) = 0022C43FSQL> oradebug dumpvar sga kcsgscn_kcslf kcsgscn_ [06001AE70, 06001AEA0) = 0022C43F 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 6001AB50 00000000SQL> alter database datafile 5 online;Database altered.SQL> recover database;Media recovery complete.SQL> alter database open;Database altered.SQL> select file#,status from v$datafile_header;FILE# STATUS—– —————1 ONLINE2 ONLINE3 ONLINE4 ONLINE5 ONLINESQL> select count(*) from scott.htz;COUNT(*)———-86272
这里看到数据文件已经正常online了，并且表中的数据能正常访问
下面是增加TEMP文件
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE ‘/oracle/app/oracle/oradata/orcl1124/temp01.dbf’ REUSE;Tablespace altered.
3.2 BBED的方式
BBED直接修改有一定的风险，需要对数据文件头中的KCVFH结果有所了解。
[oracle@orcl9i oradata]$ rm -rf orcl1124[oracle@orcl9i oradata]$ mv orcl1124back orcl1124[oracle@orcl9i oradata]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Tue Apr 22 18:51:40 2014Copyright (c) 1982, 2013, Oracle. All rights reserved.Connected to an idle instance.SQL> startupORACLE instance started.Total System Global Area 379965440 bytesFixed Size 2253464 bytesVariable Size 171969896 bytesDatabase Buffers 201326592 bytesRedo Buffers 4415488 bytesORA-00214: control file‘/oracle/app/oracle/fast_recovery_area/orcl1124/control02.ctl’ version 3714inconsistent with file ‘/oracle/app/oracle/oradata/orcl1124/control01.ctl’version 3695SQL> !cp /oracle/app/oracle/oradata/orcl1124/control01.ctl /oracle/app/oracle/fast_recovery_area/orcl1124/control02.ctlSQL> alter database mount;Database altered.SQL> alter database mount;Database altered.SQL>SQL> alter database open;Database altered.SQL> alter database datafile 5 online;alter database datafile 5 online*ERROR at line 1:ORA-01190: control file or data file 5 is from before the last RESETLOGSORA-01110: data file 5: ‘/oracle/app/oracle/oradata/orcl1124/htz01.dbf’SQL> select file#||’ ‘||name from v$dbfile;FILE#||”||NAME——————————————————————————–5 /oracle/app/oracle/oradata/orcl1124/htz01.dbf4 /oracle/app/oracle/oradata/orcl1124/users01.dbf3 /oracle/app/oracle/oradata/orcl1124/undotbs01.dbf2 /oracle/app/oracle/oradata/orcl1124/sysaux01.dbf1 /oracle/app/oracle/oradata/orcl1124/system01.dbf[oracle@orcl9i ~]$ bbed listfile=/tmp/datafile.txtPassword:BBED: Release 2.0.0.0.0 – Limited Production on Tue Apr 22 18:55:32 2014Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.************* !!! For Oracle Internal Use only !!! ***************BBED> infoFile# Name Size(blks)—– —- ———-1 /oracle/app/oracle/oradata/orcl1124/system01.dbf 02 /oracle/app/oracle/oradata/orcl1124/sysaux01.dbf 03 /oracle/app/oracle/oradata/orcl1124/undotbs01.dbf 04 /oracle/app/oracle/oradata/orcl1124/users01.dbf 05 /oracle/app/oracle/oradata/orcl1124/htz01.dbf 0
这里我们只需要修改偏移量为112，116，120就可以，其实112这里都不需要修改，如果本来实验，112这里的值为0，是没有修改成功的。但是还是能正常打开
BBED> set mode editMODE EditBBED> assign file 5 block 1 offset 116 = file 1 block 1 offset 116;Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) yub4 kscnbas @116 0x00137d12BBED> assign file 5 block 1 offset 120 = file 1 block 1 offset 120ub2 kscnwrp @120 0x0000BBED> assign file 5 block 1 offset 112 = file 1 block 1 offset 112ub4 kcvfhbti @112 0x00000000BBED> sum applyCheck value for File 5, Block 1:current = 0xc64c, required = 0xc64cBBED> verifyDBVERIFY – Verification startingFILE = /oracle/app/oracle/oradata/orcl1124/htz01.dbfBLOCK = 1DBVERIFY – Verification completeTotal Blocks Examined : 1Total Blocks Processed (Data) : 0Total Blocks Failing (Data) : 0Total Blocks Processed (Index): 0Total Blocks Failing (Index): 0Total Blocks Empty : 0Total Blocks Marked Corrupt : 0Total Blocks Influx : 0Message 531 not found; product=RDBMS; facility=BBED
这里我们需要做一个recover的操作，recover需要归档文件，如果归档文件不存在的时候，见3.3 BBED修改数据文件无归档日志
SQL> recover datafile 5;Media recovery complete.SQL> alter database datafile 5 online;Database altered.
3.3 BBED修改数据文件无归档日志
SQL> shutdown abort;ORACLE instance shut down.SQL> startup nomount;ORACLE instance started.Total System Global Area 379965440 bytesFixed Size 2253464 bytesVariable Size 171969896 bytesDatabase Buffers 201326592 bytesRedo Buffers 4415488 bytesSQL> CREATE CONTROLFILE REUSE DATABASE “ORCL1124” RESETLOGS ARCHIVELOG2 MAXLOGFILES 163 MAXLOGMEMBERS 34 MAXDATAFILES 1005 MAXINSTANCES 86 MAXLOGHISTORY 2927 LOGFILE8 GROUP 1 ‘/oracle/app/oracle/oradata/orcl1124/redo01.log’ SIZE 50M BLOCKSIZE 512,9 GROUP 2 ‘/oracle/app/oracle/oradata/orcl1124/redo02.log’ SIZE 50M BLOCKSIZE 512,10 GROUP 3 ‘/oracle/app/oracle/oradata/orcl1124/redo03.log’ SIZE 50M BLOCKSIZE 51211 — STANDBY LOGFILE12 DATAFILE13 ‘/oracle/app/oracle/oradata/orcl1124/system01.dbf’,14 ‘/oracle/app/oracle/oradata/orcl1124/sysaux01.dbf’,15 ‘/oracle/app/oracle/oradata/orcl1124/undotbs01.dbf’,16 ‘/oracle/app/oracle/oradata/orcl1124/users01.dbf’,17 ‘/oracle/app/oracle/oradata/orcl1124/htz01.dbf’18 CHARACTER SET ZHS16GBK19 ;Control file created.SQL> alter database datafile 5 offline;Database altered.SQL> recover database using backup controlfile until cancel;ORA-00279: change 1278466 generated at 04/22/2014 18:54:16 needed for thread 1ORA-00289: suggestion :/oracle/app/oracle/fast_recovery_area/ORCL1124/archivelog/2014_04_22/o1_mf_1_1_%u_.arcORA-00280: change 1278466 for thread 1 is in sequence #1Specify log: {<RET>=suggested | filename | AUTO | CANCEL}cancelORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error belowORA-01194: file 1 needs more recovery to be consistentORA-01110: data file 1: ‘/oracle/app/oracle/oradata/orcl1124/system01.dbf’ORA-01112: media recovery not started
这里由于强制关闭数据库，导致异常
SQL> alter database open resetlogs;alter database open resetlogs*ERROR at line 1:ORA-01194: file 1 needs more recovery to be consistentORA-01110: data file 1: ‘/oracle/app/oracle/oradata/orcl1124/system01.dbf’SQL> recover database using backup controlfile until cancel;ORA-00279: change 1278466 generated at 04/22/2014 18:54:16 needed for thread 1ORA-00289: suggestion :/oracle/app/oracle/fast_recovery_area/ORCL1124/archivelog/2014_04_22/o1_mf_1_1_%u_.arcORA-00280: change 1278466 for thread 1 is in sequence #1Specify log: {<RET>=suggested | filename | AUTO | CANCEL}/oracle/app/oracle/oradata/orcl1124/redo02.logORA-00339: archived log does not contain any redoORA-00334: archived log: ‘/oracle/app/oracle/oradata/orcl1124/redo02.log’ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error belowORA-01194: file 1 needs more recovery to be consistentORA-01110: data file 1: ‘/oracle/app/oracle/oradata/orcl1124/system01.dbf’
查询当前的联机日志文件的
SQL> select group#,status from v$log;GROUP# STATUS———- —————-1 UNUSED3 CURRENT2 UNUSEDSQL> recover database using backup controlfile until cancel;ORA-00279: change 1278466 generated at 04/22/2014 18:54:16 needed for thread 1ORA-00289: suggestion :/oracle/app/oracle/fast_recovery_area/ORCL1124/archivelog/2014_04_22/o1_mf_1_1_%u_.arcORA-00280: change 1278466 for thread 1 is in sequence #1Specify log: {<RET>=suggested | filename | AUTO | CANCEL}/oracle/app/oracle/oradata/orcl1124/redo01.logLog applied.Media recovery completeSQL> alter database open resetlogs;Database altered.
数据库正常打开
SQL> alter database datafile 5 online;alter database datafile 5 online*ERROR at line 1:ORA-01190: control file or data file 5 is from before the last RESETLOGSORA-01110: data file 5: ‘/oracle/app/oracle/oradata/orcl1124/htz01.dbf’
这里看到报错了
SQL> recover datafile 5;ORA-00283: recovery session canceled due to errorsORA-19909: datafile 5 belongs to an orphan incarnationORA-01110: data file 5: ‘/oracle/app/oracle/oradata/orcl1124/htz01.dbf’bbed修改resetlog信息BBED> assign file 5 block 1 offset 116 = file 1 block 1 offset 116;Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) yub4 kscnbas @116 0x00138460BBED> assign file 5 block 1 offset 120 = file 1 block 1 offset 120ub2 kscnwrp @120 0x0000BBED> assign file 5 block 1 offset 112 = file 1 block 1 offset 112ub4 kcvfhrlc @112 0x3266846dBBED> sum applyCheck value for File 5, Block 1:current = 0xdca6, required = 0xdca6BBED> verifyDBVERIFY – Verification startingFILE = /oracle/app/oracle/oradata/orcl1124/htz01.dbfBLOCK = 1DBVERIFY – Verification completeTotal Blocks Examined : 1Total Blocks Processed (Data) : 0Total Blocks Failing (Data) : 0Total Blocks Processed (Index): 0Total Blocks Failing (Index): 0Total Blocks Empty : 0Total Blocks Marked Corrupt : 0Total Blocks Influx : 0Message 531 not found; product=RDBMS; facility=BBEDSQL> alter database datafile 5 online;alter database datafile 5 online*ERROR at line 1:ORA-01113: file 5 needs media recoveryORA-01110: data file 5: ‘/oracle/app/oracle/oradata/orcl1124/htz01.dbf’
这里可以看到online的时候，已经报meia recovery，需要归档日志文件
RMAN> delete archivelog all;allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=37 device type=DISKList of Archived Log Copies for database with db_unique_name ORCL1124=====================================================================
这里删除所有的归档日志文件，其它在生产过程中我们一般都会遇到归档不存在的情况
SQL> recover database 5;ORA-00905: missing keywordSQL> recover datafile 5;ORA-00279: change 1278983 generated at 04/22/2014 19:03:50 needed for thread 1ORA-00289: suggestion :/oracle/app/oracle/fast_recovery_area/ORCL1124/archivelog/2014_04_22/o1_mf_1_1_%u_.arcORA-00280: change 1278983 for thread 1 is in sequence #1Specify log: {<RET>=suggested | filename | AUTO | CANCEL}cancelMedia recovery cancelled.SQL> alter database datafile 5 online;alter database datafile 5 online*ERROR at line 1:ORA-01113: file 5 needs media recoveryORA-01110: data file 5: ‘/oracle/app/oracle/oradata/orcl1124/htz01.dbf’
这里由于归档日志文件丢失，所以我们只能修改数据文件 SCN值，将值更改到于system的值相当
BBED> assign file 5 offset 484 = file 1 offset 484ub1 pad @484 0xf5BBED> assign file 5 offset 488 = file 1 offset 488ub1 pad @488 0x00BBED> set file 5 block 1FILE# 5BLOCK# 1BBED> sum applyCheck value for File 5, Block 1:current = 0xda5e, required = 0xda5eBBED> verifyDBVERIFY – Verification startingFILE = /oracle/app/oracle/oradata/orcl1124/htz01.dbfBLOCK = 1DBVERIFY – Verification completeTotal Blocks Examined : 1Total Blocks Processed (Data) : 0Total Blocks Failing (Data) : 0Total Blocks Processed (Index): 0Total Blocks Failing (Index): 0Total Blocks Empty : 0Total Blocks Marked Corrupt : 0Total Blocks Influx : 0Message 531 not found; product=RDBMS; facility=BBEDSQL> alter database datafile 5 online;alter database datafile 5 online*ERROR at line 1:ORA-01113: file 5 needs media recoveryORA-01110: data file 5: ‘/oracle/app/oracle/oradata/orcl1124/htz01.dbf’SQL> recover datafile 5 ;Media recovery complete.SQL> alter database datafile 5 online;Database altered.
能正常online，下面就是增加TEMP文件就可以了，见控制文件部分
------------------作者介绍-----------------------
姓名：黄廷忠
个人博客: (http://www.htz.pw)
CSDN地址: (https://blog.csdn.net/wwwhtzpw)
博客园地址: (https://www.cnblogs.com/www-htz-pw)