ORA-01190这个错误比较常见，特别在没有正规DBA运维的环境中比较常见，
先看看ORACLE官方是怎么解决这个报错的
|
SQL> !oerr ora 01190 01190, 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 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 |
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.dbf SQL> 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 bytes Fixed Size 2253464 bytes Variable Size 171969896 bytes Database Buffers 201326592 bytes Redo Buffers 4415488 bytes Database mounted. SQL> alter database archivelog; Database altered. SQL> !rm /tmp/control.txt SQL> alter database backup controlfile to trace as ‘/tmp/control.txt’; Database altered. SQL> shutdown immediate; ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL> startup nomount; ORACLE instance started. Total System Global Area 379965440 bytes Fixed Size 2253464 bytes Variable Size 171969896 bytes Database Buffers 201326592 bytes Redo Buffers 4415488 bytes SQL> CREATE CONTROLFILE REUSE DATABASE “ORCL1124” RESETLOGS ARCHIVELOG 2 MAXLOGFILES 16 3 MAXLOGMEMBERS 3 4 MAXDATAFILES 100 5 MAXINSTANCES 8 6 MAXLOGHISTORY 292 7 LOGFILE 8 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 512 11 — STANDBY LOGFILE 12 DATAFILE 13 ‘/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 ZHS16GBK 19 ; Control file created. SQL> set lines 200 SQL> col name for a60 SQL> select * from v$dbfile; FILE# NAME ———- ———————————————————— 5 /oracle/app/oracle/oradata/orcl1124/htz01.dbf 4 /oracle/app/oracle/oradata/orcl1124/users01.dbf 3 /oracle/app/oracle/oradata/orcl1124/undotbs01.dbf 2 /oracle/app/oracle/oradata/orcl1124/sysaux01.dbf 1 /oracle/app/oracle/oradata/orcl1124/system01.dbf SQL> 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 1 ORA-00289: suggestion : /oracle/app/oracle/fast_recovery_area/ORCL1124/archivelog/2014_04_22/o1_mf_1_90_%u_.arc ORA-00280: change 1277201 for thread 1 is in sequence #90 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} cancel Media 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 RESETLOGS ORA-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 925702 2 04/22/2014 18:09:05 845575745 1277202 04/16/2014 21:01:21 845067681 925702 3 04/22/2014 18:09:05 845575745 1277202 04/16/2014 21:01:21 845067681 925702 4 04/22/2014 18:09:05 845575745 1277202 04/16/2014 21:01:21 845067681 925702 5 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.trc DATA FILE #5: name #4: /oracle/app/oracle/oradata/orcl1124/htz01.dbf creation size=0 block size=8192 status=0xc head=4 tail=4 dup=1 tablespace 6, index=5 krfil=5 prev_file=0 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00 Checkpoint cnt:4294967295 scn: 0x0000.00137d11 04/22/2014 18:06:17 Stop scn: 0x0000.00137d15 04/22/2014 18:09:16 Creation Checkpointed at scn: 0x0000.00132bd4 04/22/2014 18:04:33 thread:0 rba:(0x0.0.0) Tablespace #6 – HTZ rel_fn:5 Creation at scn: 0x0000.00132bd4 04/22/2014 18:04:33 Backup taken at scn: 0x0000.00000000 01/01/1988 00:00:00 thread:0 reset logs count:0x325eb5a1 scn: 0x0000.000e2006 prev reset logs count:0x3121c97a scn: 0x0000.00000001 recovered at 04/22/2014 18:06:07 status:0x0 root dba:0x00000000 chkpt cnt: 6 ctl cnt:5 Tablespace #4 – USERS rel_fn:4 Creation at scn: 0x0000.00003f0f 08/24/2013 11:37:49 Backup taken at scn: 0x0000.00000000 01/01/1988 00:00:00 thread:0 reset logs count:0x32667641 scn: 0x0000.00137d12 prev reset logs count:0x325eb5a1 scn: 0x0000.000e2006 recovered at 04/22/2014 18:08:57 status:0x4 root dba:0x00000000 chkpt cnt: 194 ctl cnt:193 begin-hot-backup file size: 0 Checkpointed at scn: 0x0000.00137d15 04/22/2014 18:09:16 thread:1 rba:(0x1.2.10) Tablespace #2 – UNDOTBS1 rel_fn:3 Creation at scn: 0x0000.000e16c0 08/24/2013 12:07:19 Backup taken at scn: 0x0000.00000000 01/01/1988 00:00:00 thread:0 reset logs count:0x32667641 scn: 0x0000.00137d12 prev reset logs count:0x325eb5a1 scn: 0x0000.000e2006 recovered at 04/22/2014 18:08:57 status:0x4 root dba:0x00000000 chkpt cnt: 116 ctl cnt:115 begin-hot-backup file size: 0 Checkpointed 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 bytes Fixed Size 2253464 bytes Variable Size 171969896 bytes Database Buffers 201326592 bytes Redo Buffers 4415488 bytes Database mounted. SQL> select checkpoint_change# from v$datafile; CHECKPOINT_CHANGE# —————— 1278463 1278463 1278463 1278463 1277201 查看当前数据库的SCN的值，因为这里的SCN的WRAP部分的值是0，所以我们在修改的时候，可以不用考虑WRAP值修改 SQL> oradebug setmypid Statement processed. SQL> oradebug dumpvar sga kcsgscn_ kcslf kcsgscn_ [06001AE70, 06001AEA0) = 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 6001AB50 00000000 SQL> oradebug poke 0x06001AE70 4 2278463 BEFORE: [06001AE70, 06001AE74) = 00000000 AFTER: [06001AE70, 06001AE74) = 0022C43F SQL> oradebug dumpvar sga kcsgscn_ kcslf kcsgscn_ [06001AE70, 06001AEA0) = 0022C43F 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 6001AB50 00000000 SQL> 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 ONLINE 2 ONLINE 3 ONLINE 4 ONLINE 5 ONLINE SQL> 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 sysdba SQL*Plus: Release 11.2.0.4.0 Production on Tue Apr 22 18:51:40 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 379965440 bytes Fixed Size 2253464 bytes Variable Size 171969896 bytes Database Buffers 201326592 bytes Redo Buffers 4415488 bytes ORA-00214: control file ‘/oracle/app/oracle/fast_recovery_area/orcl1124/control02.ctl’ version 3714 inconsistent with file ‘/oracle/app/oracle/oradata/orcl1124/control01.ctl’ version 3695 SQL> !cp /oracle/app/oracle/oradata/orcl1124/control01.ctl /oracle/app/oracle/fast_recovery_area/orcl1124/control02.ctl SQL> 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 RESETLOGS ORA-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.dbf 4 /oracle/app/oracle/oradata/orcl1124/users01.dbf 3 /oracle/app/oracle/oradata/orcl1124/undotbs01.dbf 2 /oracle/app/oracle/oradata/orcl1124/sysaux01.dbf 1 /oracle/app/oracle/oradata/orcl1124/system01.dbf [oracle@orcl9i ~]$ bbed listfile=/tmp/datafile.txt Password: BBED: Release 2.0.0.0.0 – Limited Production on Tue Apr 22 18:55:32 2014 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. ************* !!! For Oracle Internal Use only !!! *************** BBED> info File# Name Size(blks) —– —- ———- 1 /oracle/app/oracle/oradata/orcl1124/system01.dbf 0 2 /oracle/app/oracle/oradata/orcl1124/sysaux01.dbf 0 3 /oracle/app/oracle/oradata/orcl1124/undotbs01.dbf 0 4 /oracle/app/oracle/oradata/orcl1124/users01.dbf 0 5 /oracle/app/oracle/oradata/orcl1124/htz01.dbf 0 这里我们只需要修改偏移量为112，116，120就可以，其实112这里都不需要修改，如果本来实验，112这里的值为0，是没有修改成功的。但是还是能正常打开 BBED> set mode edit MODE Edit 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) y ub4 kscnbas @116 0x00137d12 BBED> assign file 5 block 1 offset 120 = file 1 block 1 offset 120 ub2 kscnwrp @120 0x0000 BBED> assign file 5 block 1 offset 112 = file 1 block 1 offset 112 ub4 kcvfhbti @112 0x00000000 BBED> sum apply Check value for File 5, Block 1: current = 0xc64c, required = 0xc64c BBED> verify DBVERIFY – Verification starting FILE = /oracle/app/oracle/oradata/orcl1124/htz01.dbf BLOCK = 1 DBVERIFY – Verification complete Total Blocks Examined : 1 Total Blocks Processed (Data) : 0 Total Blocks Failing (Data) : 0 Total Blocks Processed (Index): 0 Total Blocks Failing (Index): 0 Total Blocks Empty : 0 Total Blocks Marked Corrupt : 0 Total Blocks Influx : 0 Message 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 bytes Fixed Size 2253464 bytes Variable Size 171969896 bytes Database Buffers 201326592 bytes Redo Buffers 4415488 bytes SQL> CREATE CONTROLFILE REUSE DATABASE “ORCL1124” RESETLOGS ARCHIVELOG 2 MAXLOGFILES 16 3 MAXLOGMEMBERS 3 4 MAXDATAFILES 100 5 MAXINSTANCES 8 6 MAXLOGHISTORY 292 7 LOGFILE 8 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 512 11 — STANDBY LOGFILE 12 DATAFILE 13 ‘/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 ZHS16GBK 19 ; 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 1 ORA-00289: suggestion : /oracle/app/oracle/fast_recovery_area/ORCL1124/archivelog/2014_04_22/o1_mf_1_1_% u_.arc ORA-00280: change 1278466 for thread 1 is in sequence #1 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 这里由于强制关闭数据库，导致异常 SQL> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-01194: file 1 needs more recovery to be consistent ORA-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 1 ORA-00289: suggestion : /oracle/app/oracle/fast_recovery_area/ORCL1124/archivelog/2014_04_22/o1_mf_1_1_% u_.arc ORA-00280: change 1278466 for thread 1 is in sequence #1 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} /oracle/app/oracle/oradata/orcl1124/redo02.log ORA-00339: archived log does not contain any redo ORA-00334: archived log: ‘/oracle/app/oracle/oradata/orcl1124/redo02.log’ 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’ 查询当前的联机日志文件的 SQL> select group#,status from v$log; GROUP# STATUS ———- —————- 1 UNUSED 3 CURRENT 2 UNUSED SQL> recover database using backup controlfile until cancel; ORA-00279: change 1278466 generated at 04/22/2014 18:54:16 needed for thread 1 ORA-00289: suggestion : /oracle/app/oracle/fast_recovery_area/ORCL1124/archivelog/2014_04_22/o1_mf_1_1_% u_.arc ORA-00280: change 1278466 for thread 1 is in sequence #1 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} /oracle/app/oracle/oradata/orcl1124/redo01.log Log applied. Media recovery complete 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 RESETLOGS ORA-01110: data file 5: ‘/oracle/app/oracle/oradata/orcl1124/htz01.dbf’ 这里看到报错了 SQL> recover datafile 5; ORA-00283: recovery session canceled due to errors ORA-19909: datafile 5 belongs to an orphan incarnation ORA-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) y ub4 kscnbas @116 0x00138460 BBED> assign file 5 block 1 offset 120 = file 1 block 1 offset 120 ub2 kscnwrp @120 0x0000 BBED> assign file 5 block 1 offset 112 = file 1 block 1 offset 112 ub4 kcvfhrlc @112 0x3266846d BBED> sum apply Check value for File 5, Block 1: current = 0xdca6, required = 0xdca6 BBED> verify DBVERIFY – Verification starting FILE = /oracle/app/oracle/oradata/orcl1124/htz01.dbf BLOCK = 1 DBVERIFY – Verification complete Total Blocks Examined : 1 Total Blocks Processed (Data) : 0 Total Blocks Failing (Data) : 0 Total Blocks Processed (Index): 0 Total Blocks Failing (Index): 0 Total Blocks Empty : 0 Total Blocks Marked Corrupt : 0 Total Blocks Influx : 0 Message 531 not found; product=RDBMS; facility=BBED SQL> alter database datafile 5 online; alter database datafile 5 online * ERROR at line 1: ORA-01113: file 5 needs media recovery ORA-01110: data file 5: ‘/oracle/app/oracle/oradata/orcl1124/htz01.dbf’ 这里可以看到online的时候，已经报meia recovery，需要归档日志文件 RMAN> delete archivelog all; allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=37 device type=DISK List of Archived Log Copies for database with db_unique_name ORCL1124 ===================================================================== 这里删除所有的归档日志文件，其它在生产过程中我们一般都会遇到归档不存在的情况 SQL> recover database 5; ORA-00905: missing keyword SQL> recover datafile 5; ORA-00279: change 1278983 generated at 04/22/2014 19:03:50 needed for thread 1 ORA-00289: suggestion : /oracle/app/oracle/fast_recovery_area/ORCL1124/archivelog/2014_04_22/o1_mf_1_1_% u_.arc ORA-00280: change 1278983 for thread 1 is in sequence #1 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} cancel Media recovery cancelled. SQL> alter database datafile 5 online; alter database datafile 5 online * ERROR at line 1: ORA-01113: file 5 needs media recovery ORA-01110: data file 5: ‘/oracle/app/oracle/oradata/orcl1124/htz01.dbf’ 这里由于归档日志文件丢失，所以我们只能修改数据文件 SCN值，将值更改到于system的值相当 BBED> assign file 5 offset 484 = file 1 offset 484 ub1 pad @484 0xf5 BBED> assign file 5 offset 488 = file 1 offset 488 ub1 pad @488 0x00 BBED> set file 5 block 1 FILE# 5 BLOCK# 1 BBED> sum apply Check value for File 5, Block 1: current = 0xda5e, required = 0xda5e BBED> verify DBVERIFY – Verification starting FILE = /oracle/app/oracle/oradata/orcl1124/htz01.dbf BLOCK = 1 DBVERIFY – Verification complete Total Blocks Examined : 1 Total Blocks Processed (Data) : 0 Total Blocks Failing (Data) : 0 Total Blocks Processed (Index): 0 Total Blocks Failing (Index): 0 Total Blocks Empty : 0 Total Blocks Marked Corrupt : 0 Total Blocks Influx : 0 Message 531 not found; product=RDBMS; facility=BBED SQL> alter database datafile 5 online; alter database datafile 5 online * ERROR at line 1: ORA-01113: file 5 needs media recovery ORA-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文件就可以了，见控制文件部分 |
轻松搞定ORA-01190报错：等您坐沙发呢！