ora-01113 oracle8i,ORA-01113 & ORA-01110同时出现

\NOARCHIVED LOG ， 没备份 ， Redo Log 被覆盖 ， 某个datafile不能online

设计知识点:

BBED> set block 1

BBED> p kcvfhckp

struct kcvfhckp, 36 bytes @484

struct kcvcpscn, 8 bytes @484

ub4 kscnbas @484 0x00afc425-----检查点SCN(checkpoint scn)

ub2 kscnwrp @488 0x0000

ub4 kcvcptim @492 0x30eb96a7-----checkpoint time

ub2 kcvcpthr @496 0x0001------checkpoint thread

union u, 12 bytes @500

struct kcvcprba, 12 bytes @500

ub4 kcrbaseq @500 0x00000089-----checkpoint rba sequence

ub4 kcrbabno @504 0x00000002-----checkpoint rba number

ub2 kcrbabof @508 0x0010-----offset 位置

ub1 kcvcpetb[0] @512 0x02-----Enabled Threads Bitvec

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

SQL> alter database datafile 4 online;

alter database datafile 4 online

*

ERROR at line 1:

ORA-01113: file 4 needs media recovery if it was restored from backup, or END BACKUP if it was not

ORA-01110: data file 4: '/u01/app/oracle/oradata/test/users01.dbf'

SQL> recover datafile 4;

ORA-00279: change 404562 generated at 09/27/2014 10:37:34 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/product/10.2.0/db_1/dbs/arch1_5_859291095.dbf

ORA-00280: change 404562 for thread 1 is in sequence #5

Specify log: {=suggested | filename | AUTO | CANCEL}

auto

ORA-00308: cannot open archived log '/u01/app/oracle/product/10.2.0/db_1/dbs/arch1_5_859291095.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/10.2.0/db_1/dbs/arch1_5_859291095.dbf'

ORA-27037: unable to obtain file status

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

Additional information: 3

SQL> col file_name for a50

SQL> select HXFIL File_num,substr(HXFNM,1,40) File_name, FHSCN SCN, FHSTA status,FHRBA_SEQ Sequence from X$KCVFH;

FILE_NUM FILE_NAME SCN STATUS SEQUENCE

---------- -------------------------------------------------- ---------------- ---------- ----------

1 /u01/app/oracle/oradata/test/system01.dbf 404786 8196 8

2 /u01/app/oracle/oradata/test/undotbs01.dbf 4047864 8

3 /u01/app/oracle/oradata/test/sysaux01.dbf 4047864 8

4 /u01/app/oracle/oradata/test/users01.dbf 4056734 8

SQL> select file#,ONLINE_STATUS,ERROR,CHANGE# from V$RECOVER_FILE order by 1;

SQL> select name, checkpoint_change# from v$datafile;

BBED> info

File# Name Size(blks)

----- ---- ----------

1 /u01/app/oracle/oradata/test/system01.dbf 0

2 /u01/app/oracle/oradata/test/undotbs01.dbf 0

3 /u01/app/oracle/oradata/test/sysaux01.dbf 0

4 /u01/app/oracle/oradata/test/users01.dbf 0

BBED> set file 1

FILE# 1

BBED> p kcvfhckp

struct kcvfhckp, 36 bytes @484

struct kcvcpscn, 8 bytes @484

ub4 kscnbas @484 0x00062d32

ub2 kscnwrp @488 0x0000

ub4 kcvcptim @492 0x3338fe1f

ub2 kcvcpthr @496 0x0001

union u, 12 bytes @500

struct kcvcprba, 12 bytes @500

ub4 kcrbaseq @500 0x00000008

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> set file 4

FILE# 4

BBED> p kcvfhckp

struct kcvfhckp, 36 bytes @484

struct kcvcpscn, 8 bytes @484

ub4 kscnbas @484 0x00062c52

ub2 kscnwrp @488 0x0000

ub4 kcvcptim @492 0x3338fc6e

ub2 kcvcpthr @496 0x0001

union u, 12 bytes @500

struct kcvcprba, 12 bytes @500

ub4 kcrbaseq @500 0x00000005

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

SQL> select to_number('&kscnbas','xxxxxxx') from dual;

TO_NUMBER('062D32','XXXXXXX')

-----------------------------

404786

SQL> select to_number('&kcrbaseq','xxxxx') from dual;

TO_NUMBER('062C52','XXXXXXX')

-----------------------------

404562

SQL> alter database datafile 4 online;

alter database datafile 4 online

*

ERROR at line 1:

ORA-01113: file 4 needs media recovery if it was restored from backup, or END

BACKUP if it was not

ORA-01110: data file 4: '/u01/app/oracle/oradata/test/users01.dbf'

SQL> recover datafile 4;

Media recovery complete.

SQL> alter database datafile 4 online;

Database altered.

SQL> select file#,name,status,ENABLED from v$datafile;

FILE# NAME STATUS ENABLED

---------- -------------------------------------------------- ------- ----------

1 /u01/app/oracle/oradata/test/system01.dbf SYSTEM READ WRITE

2 /u01/app/oracle/oradata/test/undotbs01.dbf ONLINE READ WRITE

3 /u01/app/oracle/oradata/test/sysaux01.dbf ONLINE READ WRITE

4 /u01/app/oracle/oradata/test/users01.dbf ONLINE READ WRITE

SQL> select HXFIL File_num,substr(HXFNM,1,40) File_name, FHSCN SCN, FHSTA status,FHRBA_SEQ Sequence from X$KCVFH;

FILE_NUM FILE_NAME SCN STATUS SEQUENCE

---------- -------------------------------------------------- ---------------- ---------- ----------

1 /u01/app/oracle/oradata/test/system01.db 4047868196 8

2 /u01/app/oracle/oradata/test/undotbs01.dbf 4047864 8

3 /u01/app/oracle/oradata/test/sysaux01.dbf 4047864 8

4 /u01/app/oracle/oradata/test/users01.dbf 4056734 8

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> select HXFIL File_num,substr(HXFNM,1,40) File_name, FHSCN SCN, FHSTA status,FHRBA_SEQ Sequence from X$KCVFH;

FILE_NUM FILE_NAMESCN STATUS SEQUENCE

---------- ------------------------------------------------------------ ---------------- ---------- ----------

1 /u01/app/oracle/oradata/test/system01.dbf405724 8196 10

2 /u01/app/oracle/oradata/test/undotbs01.dbf405724 4 10

3 /u01/app/oracle/oradata/test/sysaux01.dbf405724 4 10

4 /u01/app/oracle/oradata/test/users01.dbf405724 4 10