一个朋友的数据库,由于redo损坏,经过一系列恢复,当我接手之时,已经是ORA-00283和ORA-16433错误
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'D:\ORCL\SYSTEM01.DBF'
SQL> recover database ;
ORA-00283: recovery session canceled due to errors
ORA-16433: The database must be opened in read/write mode.
SQL> recover database using backup controlfile;
ORA-00283: recovery session canceled due to errors
ORA-16433: The database must be opened in read/write mode.
通过重建控制文件继续恢复,遭遇ORA-00600 kcvorl_2错误
SQL> startup nomount pfile='d:/pfile.txt';
ORACLE instance started.
Total System Global Area 2137886720 bytes
Fixed Size 2177456 bytes
Variable Size 1224738384 bytes
Database Buffers 905969664 bytes
Redo Buffers 5001216 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS NOARCHIVELOG
2 MAXLOGFILES 50
3 MAXLOGMEMBERS 5
4 MAXDATAFILES 100
5 MAXINSTANCES 1
6 MAXLOGHISTORY 226
7 LOGFILE
8 GROUP 1 'D:\ORCL\redo01.log' SIZE 50M,
9 GROUP 2 'D:\ORCL\redo02.log' SIZE 50M,
10 GROUP 3 'D:\ORCL\redo03.log' SIZE 50M
11 DATAFILE
12 'D:\ORCL\SYSTEM01.DBF',
13 'D:\ORCL\SYSAUX01.DBF',
14 'D:\ORCL\UNDOTBS01.DBF',
15 'D:\ORCL\USERS01.DBF',
16 'D:\ORCL\XIFENFEI1',
17 'D:\ORCL\XIFENFEI2'
18 CHARACTER SET AL32UTF8
19 ;
Control file created.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kcvorl_2], [0], [54271176], [0],[54271176], [], [], [], [], [], [], []
查询了mos发现该错误一般是由于Bug 20562968 – ORA-600 [KCVORL_2] DURING SWITCHOVER AFTER DOWNGRADING TO 11.2.0.1导致主库在switchover的时候可能会遇到该错误,还是第一次遇到数据库在resetlogs 打开的时候遭遇该错误.分析trace文件
Dump continued from file: e:\oracle\diag\rdbms\orcl\orcl\trace\orcl_ora_1892.trc
ORA-00600: internal error code, arguments: [kcvorl_2], [0], [54271176], [0], [54271176], [], [], [], [], [], [], []
========= Dump for incident 3738 (ORA 600 [kcvorl_2]) ========
*** 2018-07-03 16:35:41.404
dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0)
----- Current SQL Statement for this session (sql_id=7j16t46cacjt9) -----
alter database open resetlogs
----- Call Stack Trace -----
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedst1()+129 CALL??? skdstdst() 009233DA2 000000000 000000000
000000000
ksedst()+69 CALL??? ksedst1() 000000002 000000000 006F605E0
000000000
dbkedDefDump()+4536 CALL??? ksedst() 000000287 000000000 000000000
000000000
ksedmp()+43 CALL??? dbkedDefDump() 000000003 000000002 000000000
000468E71
ksfdmp()+87 CALL??? ksedmp() 000000000 000000000 000000000
000000000
dbgexPhaseII()+1819 CALL??? ksfdmp() 000000000 000000000 000000000
000000000
dbgexProcessError() CALL??? dbgexPhaseII() 00C9B0570 00C9BD448 000000E9A
+2563 000000002
dbgeExecuteForError CALL??? dbgexProcessError() 00C9B0570 00C9B7540 000000001
()+65 000000000
dbgePostErrorKGE()+ CALL??? dbgeExecuteForError BC30C65D3 019606FF0 018881658
1726 () 000502034
dbkePostKGE_kgsf()+ CALL??? dbgePostErrorKGE() 0196075B0 00CB80040 000000258
75 BA268928B586
kgeadse()+342 CALL??? dbkePostKGE_kgsf() 000000000 000000000
BA2688AA3890 7FFFB6A1728
kgerinv_internal()+ CALL??? kgeadse() 006F8C5A8 0196075B0 000000000
76 0196072B0
kgerinv()+49 CALL??? kgerinv_internal() 018883960 0071C3480 000000000
000000000
kgeasnmierr()+64 CALL??? kgerinv() 0014B18A0 0071C3480 018881C20
000000000
kcvorl()+8957 CALL??? kgeasnmierr() 0071C4650 018882B00 000000000
000000004
adbdrv()+54131 CALL??? kcvorl()+428 000000008 018883E20 018887A88
078136DFB
opiexe()+20842 CALL??? adbdrv() 000000023 000000003
7FF00000102 000000000
opiosq0()+5129 CALL??? opiexe()+16981 000000004 000000000 01888A8E0
009361AB3
kpooprx()+357 CALL??? opiosq0() 000000003 00000000E 01888ABB0
0000000A4
kpoal8()+940 CALL??? kpooprx() 000020C80 008832840 00CBD1A48
000000001
opiodr()+1662 CALL??? kpoal8() 00000005E 00000001C 01888E120
00CA5BAA8
ttcpip()+1325 CALL??? opiodr() 480000000000005E
49004D000000001C 01888E120
4100200000000000
opitsk()+2040 CALL??? ttcpip() 0196212D0 000000000 000000000
000000000
opiino()+1258 CALL??? opitsk() 00000001E 000000000 000000000
01888FA18
opiodr()+1662 CALL??? opiino() 00000003C 000000004 01888FAD0
000000000
opidrv()+864 CALL??? opiodr() 00000003C 000000004 01888FAD0
6F5C3A6500000000
sou2o()+98 CALL??? opidrv()+150 00000003C 000000004 01888FAD0
000000000
opimai_real()+158 CALL??? sou2o() 01888FB00 01888FBC4
100003000707E2 202020029001D
opimai()+191 CALL??? opimai_real() 00000001A 01888FC88 000000034
000000000
OracleThreadStart() CALL??? opimai() 01888FE90 01211FF38 000000002
+724 01888FC88
0000000078D3B6DA CALL??? OracleThreadStart() 01211FF38 000000000 000000000
01888FFA8
--------------------- Binary Stack Dump ---------------------
通过分析发现其中一个文件scn不对
SQL> SELECT status,
2 checkpoint_change#,
3 checkpoint_time,FUZZY,
4 count(*) ROW_NUM
5 FROM v$datafile_header
6 GROUP BY status, checkpoint_change#, checkpoint_time,fuzzy
7 ORDER BY status, checkpoint_change#, checkpoint_time;
STATUS CHECKPOINT_CHANGE# CHECKPOINT_TIME FUZ ROW_NUM
------- ------------------ ------------------ --- ----------
ONLINE 54271175 27-MAY-18 YES 1
ONLINE 54271179 25-JUN-18 YES 5
SQL> set numw 16
SQL> SELECT status,
2 checkpoint_change#,
3 checkpoint_time,last_change#,
4 count(*) ROW_NUM
5 FROM v$datafile
6 GROUP BY status, checkpoint_change#, checkpoint_time,last_change#
7 ORDER BY status, checkpoint_change#, checkpoint_time;
STATUS CHECKPOINT_CHANGE# CHECKPOINT_TIME LAST_CHANGE# ROW_NUM
------- ------------------ ------------------ ---------------- ----------------
RECOVER 54271175 27-MAY-18 1
RECOVER 54271179 25-JUN-18 4
SYSTEM 54271179 25-JUN-18 1
SQL> set linesize 150
SQL> select ts#,file#,TABLESPACE_NAME,status,
2 to_char(CREATION_TIME,'yyyy-mm-dd hh24:mi:ss') CREATE_TIME,
3 to_char(checkpoint_change#,'9999999999999999') "SCN",
4 to_char(RESETLOGS_CHANGE#,'9999999999999999') "RESETLOGS SCN",FUZZY
5 from v$datafile_header;
TS# FILE# TABLESPACE_NAME STATUS CREATE_
TIME SCN RESETLOGS SCN FUZ
---------------- ---------------- ------------------------------ ------- -------
------------ ----------------- ----------------- ---
0 1 SYSTEM ONLINE 2010-03
-30 10:07:48 54271179 54271176 YES
1 2 SYSAUX ONLINE 2010-03
-30 10:07:52 54271175 54271176 YES
2 3 UNDOTBS1 ONLINE 2010-03
-30 11:07:21 54271179 54271176 YES
4 4 USERS ONLINE 2010-03
-30 10:08:04 54271179 54271176 YES
6 5 XIFENFEI1 ONLINE 2016-08
-02 18:52:23 54271179 54271176 YES
7 6 XIFENFEI2 ONLINE 2016-08
-02 18:52:31 54271179 54271176 YES
6 rows selected.
有SYSAUX数据文件的scn不对,通过bbed修改scn继续恢复成功
SQL> alter database open resetlogs;
Database altered.
加我QQ(107644445)
