TIP 62#: ORA-600 [LIBRARYCACHENOTEMPTYONCLOSE] DURING DB 10g SHUTDOWN (2)

If you follow my blog, in previous post I suggested to put "Before shutdown" trigger to resolve ORA-600 error during database shutdown.
Since setting this trigger for some clients, shutdown has worked properly.However there were some odd situations which I found the following message in Alert log.

ORA-00604: error occurred at recursive SQL level 1
ORA-12663: Services required by client not available on the server
ORA-36961: Oracle OLAP is not available.
ORA-06512: at "SYS.OLAPIHISTORYRETENTION", line 1
ORA-06512: at line 15

Above situation would be fixed by disabling SYS.OLAPISTARTUPTRIGGER and SYS.OLAPISHUTDOWNTRIGGER triggers.As the result, shutdown trigger can be something like this :


CREATE or replace TRIGGER flush_shared_pool
BEFORE SHUTDOWN ON DATABASE
BEGIN
execute immediate 'alter TRIGGER SYS.OLAPISTARTUPTRIGGER DISABLE';
execute immediate 'ALTER TRIGGER SYS.OLAPISHUTDOWNTRIGGER DISABLE';
execute immediate 'ALTER SYSTEM FLUSH SHARED_POOL';
execute immediate 'alter TRIGGER SYS.OLAPISTARTUPTRIGGER ENABLE';
execute immediate 'ALTER TRIGGER SYS.OLAPISHUTDOWNTRIGGER ENABLE';
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR (num => -20000, msg => 'Error flushing pool');
END;


Please let me know whether or not this piece of code resolves the issue.

P.S :Latest update from Oracle indicates that this bug will be fixed in 10.2.0.4.



2 comments:

Anonymous said...

Doesn't appear to have been fixed in 10.2.0.4.0:

[oracle@blade2-1 OPatch]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Sep 10 12:26:15 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

sys@10GDEV> shutdown immediate
Database closed.
Database dismounted.
ORA-00600: internal error code, arguments: [LibraryCacheNotEmptyOnClose], [], [], [], [], [], [], []

Spatial DBA & English-Chinese Translator said...

oracle support says it is fixed in 11.1.0.6.