This blog about an issue in oracle that is related to oracle internal sequence; causing crash after passing mount state. This method can be applied in similar scenario.
error thrown was ORA-600 [kokiasg1]; this has to do with getting a sequence number for auditing. Frits Hoogland has a lookup for Oracle C functions.
below is the error message and follow by step to break/pause oracle startup(opening the DB in this case) before failure. this allow creation of missing sequence.
Caution: get oracle support before doing this on PROD ;)
Error Message
Solution or How to Pause Oracle Startup
SYS@DB_TEST> startup
ORACLE instance started.
Total System Global Area 8589934592 bytes
Fixed Size 7663544 bytes
Variable Size 3590324296 bytes
Database Buffers 4932501504 bytes
Redo Buffers 59445248 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [kokiasg1], [], [], [], [], [], [], [], [], [], [], []
Process ID: 44921
Session ID: 141 Serial number: 8902
1. startup mount (and leave the session terminal open)
SYS@DB_TEST> startup mount
2. find the PID of controlling process for example: using ps -ef|grep oracle|grep LOCAL
oracleDB_TEST (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
3. initiate gdb session with pid and oracle executable (if this fail, check OS user privilege, or simply run as root)
/usr/bin/gdb $ORACLE_HOME/bin/oracle 4406
4. little more explanation: next task is to instruct gdb to break/pause oracle opening of database when DB operation reaches KOKIASG1. note the DB is still in mount state.
5. At gdb terminal, issue
(gdb) break kokiasg
6. Issue continue or c , to allow program continue debugging
(gdb) continue
7. go back to the sqlplus and open DB. this will appear hang at some point but will not fail. this will give you opportunity to issue any corrective statement or investigative queries, in other sqlplus session.
sqlplus> alter database open;
8. open another sqlplus session. In this case, SYS object will be re-created. PS: SYS objects was delete because sys user was used to import to DB in Vault database environment. special consideration when dealing with Oracle Database Vault need to be followed when using expdp/impdp utilities.
we execute the corrective statement:
SYS@DB_TEST> create sequence IDGEN1$
start with 1
increment by 50
minvalue 1
cache 2;
9. Close gdb session:
Breakpoint 1, 0x0000000001a99eb0 in kokiasg ()
(gdb) kill
Kill the program being debugged? (y or n) y
(gdb) quit
hang sqlplus session will now return:
10. gdb log session details:
linuxlab[DB_TEST](/home/oracle)$ /usr/bin/gdb $ORACLE_HOME/bin/oracle 4406
GNU gdb (GDB) Red Hat Enterprise Linux (7.2-92.el6)Copyright (C) 2010 Free Software Foundation, Inc.License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html>This is free software: you are free to change and redistribute it.There is NO WARRANTY, to the extent permitted by law. Type "show copying"and "show warranty" for details.This GDB was configured as "x86_64-redhat-linux-gnu".For bug reporting instructions, please see:<http://www.gnu.org/software/gdb/bugs/>...Reading symbols from /oracle/app/121//bin/oracle...(no debugging symbols found)...done.Attaching to program: /oracle/app/121//bin/oracle, process 4406Reading symbols from /oracle/app/121//lib/libodm12.so...(no debugging symbols found)...done.Loaded symbols for /oracle/app/121//lib/libodm12.soReading symbols from /oracle/app/121//lib/libcell12.so...done.Loaded symbols for /oracle/app/121//lib/libcell12.soReading symbols from /oracle/app/121//lib/libskgxp12.so...(no debugging symbols found)...done.Loaded symbols for /oracle/app/121//lib/libskgxp12.soReading symbols from /oracle/app/121//lib/libskjcx12.so...done.Loaded symbols for /oracle/app/121//lib/libskjcx12.soReading symbols from /lib64/librt.so.1...(no debugging symbols found)...done.Loaded symbols for /lib64/librt.so.1Reading symbols from /oracle/app/121//lib/libmql1.so...(no debugging symbols found)...done.Loaded symbols for /oracle/app/121//lib/libmql1.soReading symbols from /oracle/app/121//lib/libipc1.so...(no debugging symbols found)...done.Loaded symbols for /oracle/app/121//lib/libipc1.soReading symbols from /oracle/app/121//lib/libclsra12.so...done.Loaded symbols for /oracle/app/121//lib/libclsra12.soReading symbols from /oracle/app/121//lib/libdbcfg12.so...(no debugging symbols found)...done.Loaded symbols for /oracle/app/121//lib/libdbcfg12.soReading symbols from /oracle/app/121//lib/libhasgen12.so...done.Loaded symbols for /oracle/app/121//lib/libhasgen12.soReading symbols from /oracle/app/121//lib/libskgxn2.so...(no debugging symbols found)...done.Loaded symbols for /oracle/app/121//lib/libskgxn2.soReading symbols from /oracle/app/121//lib/libocr12.so...done.Loaded symbols for /oracle/app/121//lib/libocr12.soReading symbols from /oracle/app/121//lib/libocrb12.so...done.Loaded symbols for /oracle/app/121//lib/libocrb12.soReading symbols from /oracle/app/121//lib/libocrutl12.so...done.Loaded symbols for /oracle/app/121//lib/libocrutl12.soReading symbols from /lib64/libaio.so.1...(no debugging symbols found)...done.Loaded symbols for /lib64/libaio.so.1Reading symbols from /lib64/libaio.so.1...(no debugging symbols found)...done.Loaded symbols for /lib64/libaio.so.1Reading symbols from /oracle/app/121//lib/libons.so...(no debugging symbols found)...done.Loaded symbols for /oracle/app/121//lib/libons.soReading symbols from /lib64/libdl.so.2...(no debugging symbols found)...done.Loaded symbols for /lib64/libdl.so.2Reading symbols from /lib64/libm.so.6...(no debugging symbols found)...done.Loaded symbols for /lib64/libm.so.6Reading symbols from /lib64/libpthread.so.0...(no debugging symbols found)...done.[Thread debugging using libthread_db enabled]Loaded symbols for /lib64/libpthread.so.0Reading symbols from /lib64/libnsl.so.1...(no debugging symbols found)...done.Loaded symbols for /lib64/libnsl.so.1Reading symbols from /lib64/libc.so.6...(no debugging symbols found)...done.Loaded symbols for /lib64/libc.so.6Reading symbols from /lib64/ld-linux-x86-64.so.2...(no debugging symbols found)...done.Loaded symbols for /lib64/ld-linux-x86-64.so.2Reading symbols from /lib64/libnss_files.so.2...(no debugging symbols found)...done.Loaded symbols for /lib64/libnss_files.so.2Reading symbols from /oracle/app/121//lib/libnque12.so...(no debugging symbols found)...done.Loaded symbols for /oracle/app/121//lib/libnque12.soReading symbols from /oracle/app/121//lib/libshpkavx12.so...(no debugging symbols found)...done.Loaded symbols for /oracle/app/121//lib/libshpkavx12.soReading symbols from /oracle/app/121//rdbms/lib/odm/libnfsodm12.so...(no debugging symbols found)...done.Loaded symbols for /oracle/app/121//rdbms/lib/odm/libnfsodm12.so0x0000003c78e0e810 in __read_nocancel () from /lib64/libpthread.so.0Missing separate debuginfos, use: debuginfo-install glibc-2.12-1.212.el6.x86_64 libaio-0.3.107-10.el6.x86_64
(gdb) break kokiasg
Breakpoint 1 at 0x1a99eb0(gdb) continue
Continuing.
Breakpoint 1, 0x0000000001a99eb0 in kokiasg ()(gdb) kill
Kill the program being debugged? (y or n) y
(gdb) quit
11. Similar method use here xifenfei
