1.模拟出ora1190
select file#,name,status from v$datafile;
FILE# NAME STATUS
1 /u01/app/oracle/oradata/11gdb/system01.dbf SYSTEM
2 /u01/app/oracle/oradata/11gdb/undotbs01.dbf ONLINE
3 /u01/app/oracle/oradata/11gdb/sysaux01.dbf ONLINE
4 /u01/app/oracle/oradata/11gdb/users01.dbf ONLINE
5 /u01/app/oracle/oradata/11gdb/test01.dbf ONLINE
SQL> create table t0804(a date) tablespace test1190;
Table created.
SQL> insert into t0804 values(sysdate);
1 row created.
SQL>
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t0804;
A
03-MAR-23
03-MAR-23
03-MAR-23
03-MAR-23
SQL> conn / as sysdba
Connected.
SQL> oradebug setmypid
Statement processed.
SQL> alter database backup controlfile to trace;
Database altered.
SQL> oradebug close_trace
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/admin/11gdb/udump/11gdb_ora_20222.trc
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
删除新增数据文件重建控制文件
startup nomount
CREATE CONTROLFILE REUSE DATABASE “11gDB” RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ‘/u01/app/oracle/oradata/11gdb/redo01.log’ SIZE 50M,
GROUP 2 ‘/u01/app/oracle/oradata/11gdb/redo02.log’ SIZE 50M,
GROUP 3 ‘/u01/app/oracle/oradata/11gdb/redo03.log’ SIZE 50M
– STANDBY LOGFILE
DATAFILE
‘/u01/app/oracle/oradata/11gdb/system01.dbf’,
‘/u01/app/oracle/oradata/11gdb/undotbs01.dbf’,
‘/u01/app/oracle/oradata/11gdb/sysaux01.dbf’,
‘/u01/app/oracle/oradata/11gdb/users01.dbf’,
‘/u01/app/oracle/oradata/11gdb/test01.dbf’
CHARACTER SET ZHS16GBK
;
类似错误还有ora01189
SQL> startup nomount
ORACLE instance started.
Total System Global Area 759169024 bytes
Fixed Size 2023848 bytes
Variable Size 260050520 bytes
Database Buffers 494927872 bytes
Redo Buffers 2166784 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE “11gDB” RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ‘/u01/app/oracle/oradata/11gdb/redo01.log’ SIZE 50M,
GROUP 2 ‘/u01/app/oracle/oradata/11gdb/redo02.log’ SIZE 50M,
GROUP 3 ‘/u01/app/oracle/oradata/11gdb/redo03.log’ SIZE 50M
– STANDBY LOGFILE
DATAFILE
‘/u01/app/oracle/oradata/11gdb/system01.dbf’,
‘/u01/app/oracle/oradata/11gdb/undotbs01.dbf’,
‘/u01/app/oracle/oradata/11gdb/sysaux01.dbf’,
‘/u01/app/oracle/oradata/11gdb/users01.dbf’,
‘/u01/app/oracle/oradata/11gdb/test01.dbf’,
‘/u01/app/oracle/oradata/11gdb/test1190.dbf’
CHARACTER SET ZHS16GBK
; 16 17 18 19 20
CREATE CONTROLFILE REUSE DATABASE “11gDB” RESETLOGS NOARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01189: file is from a different RESETLOGS than previous files
ORA-01110: data file 6: ‘/u01/app/oracle/oradata/11gdb/test1190.dbf’
SQL> select file#,name,status from v$datafile;
FILE# NAME STATUS
1 /u01/app/oracle/oradata/11gdb/system01.dbf SYSTEM
2 /u01/app/oracle/oradata/11gdb/undotbs01.dbf RECOVER
3 /u01/app/oracle/oradata/11gdb/sysaux01.dbf RECOVER
4 /u01/app/oracle/oradata/11gdb/users01.dbf RECOVER
5 /u01/app/oracle/oradata/11gdb/test01.dbf RECOVER
SQL>
SQL> recover database until cancel;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 1613283 generated at 03/03/2023 09:43:42 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/product/11.2/db_1/dbs/arch1_30_1111587742.dbf
ORA-00280: change 1613283 for thread 1 is in sequence #30
Specify log: {
auto
ORA-00308: cannot open archived log ‘/u01/app/oracle/product/11.2/db_1/dbs/arch1_30_1111587742.dbf’
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00308: cannot open archived log ‘/u01/app/oracle/product/11.2/db_1/dbs/arch1_30_1111587742.dbf’
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> alter database open resetlogs;
Database altered.
SQL> select file#,name,status from v$datafile;
FILE# NAME STATUS
1 /u01/app/oracle/oradata/11gdb/system01.dbf SYSTEM
2 /u01/app/oracle/oradata/11gdb/undotbs01.dbf ONLINE
3 /u01/app/oracle/oradata/11gdb/sysaux01.dbf ONLINE
4 /u01/app/oracle/oradata/11gdb/users01.dbf ONLINE
5 /u01/app/oracle/oradata/11gdb/test01.dbf ONLINE
6 /u01/app/oracle/product/11.2/db_1/dbs/MISSING00006 RECOVER
此时创建控制文件时漏掉的datafile 现在变成了missing
SQL> alter database rename file ‘/u01/app/oracle/product/11.2/db_1/dbs/MISSING00006’ to ‘/u01/app/oracle/oradata/11gdb/test1190.dbf’;
Database altered.
SQL> alter database datafile 6 online;
alter database datafile 6 online
*
ERROR at line 1:
ORA-01190: control file or data file 6 is from before the last RESETLOGS
ORA-01110: data file 6: ‘/u01/app/oracle/oradata/11gdb/test1190.dbf’
ora1189也类似，之所以报这个错误，是因为datafile的某些信息跟其他的datafile的resetlog信息不同。想解决这个问题，就需要对datafile header的结构比较了解。
方法1 利用bbed修复
BBED> info
File# Name Size(blks)
1 /u01/app/oracle/oradata/11gdb/system01.dbf 61440
2 /u01/app/oracle/oradata/11gdb/undotbs01.dbf 3200
3 /u01/app/oracle/oradata/11gdb/sysaux01.dbf 33280
4 /u01/app/oracle/oradata/11gdb/users01.dbf 640
5 /u01/app/oracle/oradata/11gdb/test01.dbf 1280
6 /u01/app/oracle/oradata/11gdb/test1190.dbf 640
BBED> set file 5 block 1
FILE# 5
BLOCK# 1
BBED> p kcvfhrlc
ub4 kcvfhrlc @112 0x43624547
BBED> p kcvfhrls
struct kcvfhrls, 8 bytes @116
ub4 kscnbas @116 0x0018c907
ub2 kscnwrp @120 0x0000
BBED> p kcvfhckp
struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0x0018c908
ub2 kscnwrp @488 0x0000
ub4 kcvcptim @492 0x4362454a
ub2 kcvcpthr @496 0x0001
union u, 12 bytes @500
struct kcvcprba, 12 bytes @500
ub4 kcrbaseq @500 0x00000001
ub4 kcrbabno @504 0x00000002
ub2 kcrbabof @508 0x0010
ub1 kcvcpetb[0] @512 0x02
ub1 kcvcpetb[1] @513 0x00
ub1 kcvcpetb[2] @514 0x00
ub1 kcvcpetb[3] @515 0x00
ub1 kcvcpetb[4] @516 0x00
ub1 kcvcpetb[5] @517 0x00
ub1 kcvcpetb[6] @518 0x00
ub1 kcvcpetb[7] @519 0x00
BBED
BBED> set file 6 block 1
FILE# 6
BLOCK# 1
BBED> p kcvfhrlc
ub4 kcvfhrlc @112 0x4361f41d
BBED> p kcvfhrls
struct kcvfhrls, 8 bytes @116
ub4 kscnbas @116 0x00189de4
ub2 kscnwrp @120 0x0000
BBED> p kcvfhckp
struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0x0018c906
ub2 kscnwrp @488 0x0000
ub4 kcvcptim @492 0x436244d3
ub2 kcvcpthr @496 0x0001
union u, 12 bytes @500
struct kcvcprba, 12 bytes @500
ub4 kcrbaseq @500 0x00000008
ub4 kcrbabno @504 0x000000f5
ub2 kcrbabof @508 0x0010
ub1 kcvcpetb[0] @512 0x02
ub1 kcvcpetb[1] @513 0x00
ub1 kcvcpetb[2] @514 0x00
ub1 kcvcpetb[3] @515 0x00
ub1 kcvcpetb[4] @516 0x00
ub1 kcvcpetb[5] @517 0x00
ub1 kcvcpetb[6] @518 0x00
ub1 kcvcpetb[7] @519 0x00
BBED> d /v offset 112 count 6
File: /u01/app/oracle/oradata/11gdb/test1190.dbf (6)
Block: 1 Offsets: 112 to 117 Dba:0x01800001
1df46143 e49d l .Ĥ.
<16 bytes per line>
BBED> d /v offset 112 count 6
File: /u01/app/oracle/oradata/11gdb/test1190.dbf (6)
Block: 1 Offsets: 112 to 117 Dba:0x01800001
1df46143 e49d l .Ĥ.
<16 bytes per line>
BBED> modify /x 47456243 offset 112
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /u01/app/oracle/oradata/11gdb/test1190.dbf (6)
Block: 1 Offsets: 112 to 117 Dba:0x01800001
47456243 e49d
<32 bytes per line>
BBED>
BBED> modify /x 07c918 offset 116
File: /u01/app/oracle/oradata/11gdb/test1190.dbf (6)
Block: 1 Offsets: 116 to 121 Dba:0x01800001
07c91800 0000
<32 bytes per line>
BBED> modify /x 08c918 offset 484
File: /u01/app/oracle/oradata/11gdb/test1190.dbf (6)
Block: 1 Offsets: 484 to 489 Dba:0x01800001
08c91800 0000
<32 bytes per line>
BBED> modify /x 4a45 offset 492
File: /u01/app/oracle/oradata/11gdb/test1190.dbf (6)
Block: 1 Offsets: 492 to 497 Dba:0x01800001
4a456243 0100
<32 bytes per line>
BBED> sum apply
Check value for File 6, Block 1:
current = 0x6f6e, required = 0x6f6e
数据库
SQL> select file#,name,status from v$datafile;
FILE# NAME STATUS
1 /u01/app/oracle/oradata/11gdb/system01.dbf SYSTEM
2 /u01/app/oracle/oradata/11gdb/undotbs01.dbf ONLINE
3 /u01/app/oracle/oradata/11gdb/sysaux01.dbf ONLINE
4 /u01/app/oracle/oradata/11gdb/users01.dbf ONLINE
5 /u01/app/oracle/oradata/11gdb/test01.dbf ONLINE
6 /u01/app/oracle/oradata/11gdb/test1190.dbf RECOVER
6 rows selected.
SQL> alter system flush buffer_cache;
System altered.
SQL> recover datafile 6;
Media recovery complete.
SQL> alter database datafile 6 online;
Database altered.
SQL> select file#,name,status from v$datafile;
FILE# NAME STATUS
1 /u01/app/oracle/oradata/11gdb/system01.dbf SYSTEM
2 /u01/app/oracle/oradata/11gdb/undotbs01.dbf ONLINE
3 /u01/app/oracle/oradata/11gdb/sysaux01.dbf ONLINE
4 /u01/app/oracle/oradata/11gdb/users01.dbf ONLINE
5 /u01/app/oracle/oradata/11gdb/test01.dbf ONLINE
6 /u01/app/oracle/oradata/11gdb/test1190.dbf ONLINE
6 rows selected.
SQL> SQL>
验证：
SQL> show user;
USER is “SYS”
SQL> select * from test.t0804;
A
03-MAR-23
03-MAR-23
03-MAR-23
03-MAR-23
方法2 使用 events 10015
首先启动到mount状态，执行如下命令：
alter session set events ‘10015 trace name adjust_scn level 10’;
alter database datafile 5 online;
alter database datafile 6 online;
recover database;
alter database open;
