Last days, I had an interesting issue with a customer’s test database. The DB crashed, after manually but unintentionally corrupting the SYS schema (dropping an unknown number of objects). Restarting the instance (open) ended with:
ORA-00600: internal error code,
arguments: [kokiasg1], [], [], [], [], [], [], [], [], [], [], []
Our quick fix was to restore/recover (PITR) to a point in time before causing havoc on the SYS schema. But as a spin-off, the Oracle Support SR I did start in parallel, analyzed the cause of the ORA-600 [kokiasg1]. I did not find anything reliable on the web, but this is what my support engineer suggested, if a PITR like in my case would not be possible due to business data requirements:
ORA-600 [kokiasg1] error can also occur if the sequence IDGEN1$ is missing, not only AUDSES$. The sequence IDGEN1$ is created when the database is created. The ddl is also in script migrate.bsq.
(1) If database is still opened, execute the following query and check if sequence IDGEN1$ is missing.
select object_name
from dba_objects
where object_type='SEQUENCE'
and owner = 'SYS' and object_name in ('IDGEN1$' ,'AUDSES$');
(2) If database is not open:
- set init.ora/spfile parameter _no_objects=true
- startup the instance
- create the missing object
- remove the parameter and restart the instance
So do:
- backup spfile
- alter system set “_no_objects”=true scope =spfile;
- Restart the instance
- Create the sequence:
create sequence IDGEN1$ start with 1 increment by 50 minvalue 1 cache 2
- alter system set “_no_objects”=false scope =spfile;
- Restart the instance
In the end, this solution leaves a questionmark: The sequence starts wit 1 now – no way to find out where it SHOULD start. My personal suggestion: Use this workaround to open your DB and export your user data (or preserve them in your other, preferred way), recreate the DB and reimport. Sad to say, but even Oracle Support wasn’t able to tell me if the DB is still stable or supported after this action.
Lessons learned: Don’t mess around with your SYS schema! 🙂
Take care
Martin Klier