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: {<RET>=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 404786 4 8
3 /u01/app/oracle/oradata/test/sysaux01.dbf 404786 4 8
4 /u01/app/oracle/oradata/test/users01.dbf 405673 4 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 404786 8196 8
2 /u01/app/oracle/oradata/test/undotbs01.dbf 404786 4 8
3 /u01/app/oracle/oradata/test/sysaux01.dbf 404786 4 8
4 /u01/app/oracle/oradata/test/users01.dbf 405673 4 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_NAME SCN STATUS SEQUENCE
---------- ------------------------------------------------------------ ---------------- ---------- ----------
1 /u01/app/oracle/oradata/test/system01.dbf 405724 8196 10
2 /u01/app/oracle/oradata/test/undotbs01.dbf 405724 4 10
3 /u01/app/oracle/oradata/test/sysaux01.dbf 405724 4 10
4 /u01/app/oracle/oradata/test/users01.dbf 405724 4 10

巴特西

ORA-01113 & ORA-01110同时出现

最新文章

热门文章