A few months back i accidently set my global database name to “null”. As a result the database crashed with “ORA-00600: internal error code, arguments: [18062]”:
SQL> update global_name set global_name=null; SQL> commit;
Starting background process QMNC Fri Oct 21 21:38:12 2011 QMNC started with pid=68, OS id=26764 Errors in file /data/oracle/ORADB/admin/diag/rdbms/ORADB/ORADB2/trace/ORADB2_ora_22638.trc (incident=26069): ORA-00600: internal error code, arguments: [18062], [], [], [], [], [], [], [], [], [], [], [] Incident details in: /data/oracle/ORADB/admin/diag/rdbms/ORADB/ORADB2/incident/incdir_26069/ORADB2_ora_22638_i26069.trc Fri Oct 21 21:38:14 2011 db_recovery_file_dest_size of 20000 MB is 71.54% used. This is a user-specified limit on the amount of space that will be used by this database for recovery-related files, and does not reflect the amount of space available in the underlying filesystem or ASM diskgroup. Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. Errors in file /data/oracle/ORADB/admin/diag/rdbms/ORADB/ORADB2/trace/ORADB2_ora_22638.trc: ORA-00600: internal error code, arguments: [18062], [], [], [], [], [], [], [], [], [], [], [] Errors in file /data/oracle/ORADB/admin/diag/rdbms/ORADB/ORADB2/trace/ORADB2_ora_22638.trc: ORA-00600: internal error code, arguments: [18062], [], [], [], [], [], [], [], [], [], [], [] Error 600 happened during db open, shutting down database USER (ospid: 22638): terminating the instance due to error 600 Instance terminated by USER, pid = 22638 ORA-1092 signalled during: alter database open... opiodr aborting process unknown ospid (22638) as a result of ORA-1092 Fri Oct 21 21:38:17 2011 ORA-1092 : opitsk aborting process
According to the Metalink articles your database is toast – you have to restore it from a backup. But what if there is no backup and you are not that familiar with BBED (the oracle block editor)?
Here is how to solve the issue:
First of all you need root access and have three sessions open.
Step #1 – Session 1:
[oracle@dmorldb06 testcases]$ sqlplus SQL*Plus: Release 11.2.0.2.0 Production on Fri Oct 21 16:10:48 2011 Copyright (c) 1982, 2010, Oracle. All rights reserved. Enter user-name: / as sysdba Connected to an idle instance. SQL> startup mount ORACLE instance started.
Locate the ospid of the oracle process for this session. For example in this case it was 20346:
$ ps -ef | grep 19311 oracle 19311 14240 0 16:10 pts/10 00:00:00 sqlplus oracle 20346 19311 0 16:11 ? 00:00:00 oracleORADB (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))) oracle 29080 17291 0 16:14 pts/12 00:00:00 grep 19311
Step #2 – Session 2: Use gdb to attach to the process and set a break on k2dcur:
# gdb $ORACLE_HOME/bin/oracle 20346 GNU gdb (GDB) Red Hat Enterprise Linux (7.0.1-23.el5) (gdb) break k2dcur Breakpoint 1 at 0x2087870
Step #3 – Session 1: Execute “alter database open”
The session will now stop at the breakpoint. The database is not yet open completely but open enough to reverse the update.
At the GDB you will see:
Breakpoint 1, 0x0000000002087870 in k2dcur ()
Step #4 – Session 3:
Connect to sqlplus from another session (session 3) and update props$
[oracle@host]$ sqlplus SQL*Plus: Release 11.2.0.2.0 Production on Fri Oct 21 16:17:52 2011 Copyright (c) 1982, 2010, Oracle. All rights reserved. Enter user-name: / as sysdba Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> update props$ set value$='ORADB' where name='GLOBAL_DB_NAME'; 1 row updated. SQL> commit; Commit complete.
Step 5 – Session 2
From session 2 execute the cont command to pass the break point:
(gdb) cont Continuing.
Session 1 should now complete the alter database open :
Database altered.
Note: If the “commit” does not come back you can commit implicitly. Just execute: “create table new as select * from v$instance;”. The CREATE TABLE will implicitly commit all previous transactions.