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.
TIP 61#: ORA-600 [LIBRARYCACHENOTEMPTYONCLOSE] DURING DB 10g SHUTDOWN
You may see that sometimes shutdown immediate takes time and finally it does not complete in 10g instances.
This is reported as a bug in metalink and Oracle has not released any fix so far. (Bug 4483084).
This error is kind of sever for some of my clients specially warehouse environment which mainly relied on cold backup.
These environments need clean shutdown.
Workaround is to implement 'BEFORE shutdown' trigger to flush shared pool.
Here is a sample of shutdown trigger which worked for me.
CREATE or replace TRIGGER flush_shared_pool
BEFORE SHUTDOWN ON DATABASE
BEGIN
execute immediate 'ALTER SYSTEM FLUSH SHARED_POOL';
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR (num => -20000, msg => 'Error flushing pool');
END;
I have not seen any problem since implementing this trigger.
Please let me know if that also works for you.
This is reported as a bug in metalink and Oracle has not released any fix so far. (Bug 4483084).
This error is kind of sever for some of my clients specially warehouse environment which mainly relied on cold backup.
These environments need clean shutdown.
Workaround is to implement 'BEFORE shutdown' trigger to flush shared pool.
Here is a sample of shutdown trigger which worked for me.
CREATE or replace TRIGGER flush_shared_pool
BEFORE SHUTDOWN ON DATABASE
BEGIN
execute immediate 'ALTER SYSTEM FLUSH SHARED_POOL';
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR (num => -20000, msg => 'Error flushing pool');
END;
I have not seen any problem since implementing this trigger.
Please let me know if that also works for you.
TIP 60#: Force Opatch to use non-default inventory
If Different inventory is kept for each Oracle installation, you will need to point opatch to different inventory at time of patching.
If inventory does not match with with Oracle home, the following errors may raise during opatch run :
LsInventorySession failed: OracleHomeInventory::load() gets null oracleHomeInfo
LsInventory: OPatch Exception while accessing O2O
OPATCH_JAVA_ERROR : An exception of type "OPatchException" has occurred:
OPatch Exception:
OUI found no such ORACLE_HOME set in the environment
Can not get details for given Oracle Home
An exception occurs
null
First of all check opatch output and look at "Location of Oracle Inventory Pointer" entry.
By default it is /etc/oraInst.loc. If this entry does not point to right location, you will have 2 options to fix the situation:
- copy valid oraInst.loc as /etc/oraInst.loc as root.
It is not feasible always since rarely DBA has root access on box.
- Run the following command to point opatch to correct inventory (Replace /etc/oraInst.loc.9i_version with your own inventory location).
opatch lsinventory -invPtrLoc /etc/oraInst.loc.9i_version
Here is a sample :
oracle@test(/home/oracle): opatch lsinventory
Invoking OPatch 10.2.0.2.3
Oracle interim Patch Installer version 10.2.0.2.3
Copyright (c) 2007, Oracle Corporation. All rights reserved..
Oracle Home : /s005/home/oracle/product/10.2.0.2
Central Inventory : /s006/home/oracle/infraAS10g/oraInventory
from : /etc/oraInst.loc
OPatch version : 10.2.0.2.3
OUI version : 10.2.0.2.0
OUI location : /s005/home/oracle/product/10.2.0.2/oui
Log file location : /s005/home/oracle/product/10.2.0.2/cfgtoollogs/opatch/opatch2007-10-18_16-16-19PM.log
List of Homes on this system:
Home name= OUIHome1, Location= "/s006/home/oracle/infraAS10g/product/10.1.0.4"
Inventory load failed... OPatch cannot load inventory for the given Oracle Home.
Possible causes are:
Oracle Home dir. path does not exist in Central Inventory
Oracle Home is a symbolic link
Oracle Home inventory is corrupted
LsInventorySession failed: OracleHomeInventory::load() gets null oracleHomeInfo
OPatch failed with error code 73
=============
oracle@test(/home/oracle):opatch lsinventory -invPtrLoc /etc/oraInst.loc.9i_version
Invoking OPatch 10.2.0.2.3
Oracle interim Patch Installer version 10.2.0.2.3
Copyright (c) 2007, Oracle Corporation. All rights reserved..
Oracle Home : /s005/home/oracle/product/10.2.0.2
Central Inventory : /s005/home/oracle/oraInventory
from : /etc/oraInst.loc.9i_version
OPatch version : 10.2.0.2.3
OUI version : 10.2.0.2.0
OUI location : /s005/home/oracle/product/10.2.0.2/oui
Log file location : /s005/home/oracle/product/10.2.0.2/cfgtoollogs/opatch/opatch2007-10-18_16-17-29PM.log
Lsinventory Output file location : /s005/home/oracle/product/10.2.0.2/cfgtoollogs/opatch/lsinv/lsinventory2007-10-18_16-17-29PM.txt
--------------------------------------------------------------------------------
Installed Top-level Products (4):
Oracle Client 10.2.0.1.0
Oracle Database 10g 10.2.0.1.0
Oracle Database 10g Products 10.2.0.1.0
Oracle Database 10g Release 2 Patch Set 1 10.2.0.2.0
There are 4 products installed in this Oracle Home.
Interim patches (2) :
Patch 5075470 : applied on Sun Feb 18 10:58:23 EST 2007
Created on 6 Apr 2006, 03:38:28 hrs US/Pacific
Bugs fixed:
5075470
Patch 4689959 : applied on Sun Feb 18 10:33:33 EST 2007
Created on 22 Sep 2006, 04:57:06 hrs US/Pacific
Bugs fixed:
4689959
If inventory does not match with with Oracle home, the following errors may raise during opatch run :
LsInventorySession failed: OracleHomeInventory::load() gets null oracleHomeInfo
LsInventory: OPatch Exception while accessing O2O
OPATCH_JAVA_ERROR : An exception of type "OPatchException" has occurred:
OPatch Exception:
OUI found no such ORACLE_HOME set in the environment
Can not get details for given Oracle Home
An exception occurs
null
First of all check opatch output and look at "Location of Oracle Inventory Pointer" entry.
By default it is /etc/oraInst.loc. If this entry does not point to right location, you will have 2 options to fix the situation:
- copy valid oraInst.loc as /etc/oraInst.loc as root.
It is not feasible always since rarely DBA has root access on box.
- Run the following command to point opatch to correct inventory (Replace /etc/oraInst.loc.9i_version with your own inventory location).
opatch lsinventory -invPtrLoc /etc/oraInst.loc.9i_version
Here is a sample :
oracle@test(/home/oracle): opatch lsinventory
Invoking OPatch 10.2.0.2.3
Oracle interim Patch Installer version 10.2.0.2.3
Copyright (c) 2007, Oracle Corporation. All rights reserved..
Oracle Home : /s005/home/oracle/product/10.2.0.2
Central Inventory : /s006/home/oracle/infraAS10g/oraInventory
from : /etc/oraInst.loc
OPatch version : 10.2.0.2.3
OUI version : 10.2.0.2.0
OUI location : /s005/home/oracle/product/10.2.0.2/oui
Log file location : /s005/home/oracle/product/10.2.0.2/cfgtoollogs/opatch/opatch2007-10-18_16-16-19PM.log
List of Homes on this system:
Home name= OUIHome1, Location= "/s006/home/oracle/infraAS10g/product/10.1.0.4"
Inventory load failed... OPatch cannot load inventory for the given Oracle Home.
Possible causes are:
Oracle Home dir. path does not exist in Central Inventory
Oracle Home is a symbolic link
Oracle Home inventory is corrupted
LsInventorySession failed: OracleHomeInventory::load() gets null oracleHomeInfo
OPatch failed with error code 73
=============
oracle@test(/home/oracle):opatch lsinventory -invPtrLoc /etc/oraInst.loc.9i_version
Invoking OPatch 10.2.0.2.3
Oracle interim Patch Installer version 10.2.0.2.3
Copyright (c) 2007, Oracle Corporation. All rights reserved..
Oracle Home : /s005/home/oracle/product/10.2.0.2
Central Inventory : /s005/home/oracle/oraInventory
from : /etc/oraInst.loc.9i_version
OPatch version : 10.2.0.2.3
OUI version : 10.2.0.2.0
OUI location : /s005/home/oracle/product/10.2.0.2/oui
Log file location : /s005/home/oracle/product/10.2.0.2/cfgtoollogs/opatch/opatch2007-10-18_16-17-29PM.log
Lsinventory Output file location : /s005/home/oracle/product/10.2.0.2/cfgtoollogs/opatch/lsinv/lsinventory2007-10-18_16-17-29PM.txt
--------------------------------------------------------------------------------
Installed Top-level Products (4):
Oracle Client 10.2.0.1.0
Oracle Database 10g 10.2.0.1.0
Oracle Database 10g Products 10.2.0.1.0
Oracle Database 10g Release 2 Patch Set 1 10.2.0.2.0
There are 4 products installed in this Oracle Home.
Interim patches (2) :
Patch 5075470 : applied on Sun Feb 18 10:58:23 EST 2007
Created on 6 Apr 2006, 03:38:28 hrs US/Pacific
Bugs fixed:
5075470
Patch 4689959 : applied on Sun Feb 18 10:33:33 EST 2007
Created on 22 Sep 2006, 04:57:06 hrs US/Pacific
Bugs fixed:
4689959
TIP 59#: CPU cost vs I/O cost in execution plan
If you are using execution plan for tuning SQL statement, you may notice three distinguished columns: I/O Cost and CPU cost and Cost.
In this post, I am trying to just make more sense of these three columns in an execution plan. Optimizer uses a CPU to I/O ratio to determine how much CPU cost would be equivalent to one I/O cost.
Here is guideline on how to find out this ratio and see if that make sense in your system.
1. Enable event 10053 .
alter session set max_dump_file_size = unlimited;
ALTER SESSION SET EVENTS '10053 trace name context forever,level 1';
EXPLAIN PLAN FOR --SQL STATEMENT--;
exit
2. Find trace file.
Trace file should be in user_dump_dest
3. Go through trace file and find access path for an index full scan or a table full scan
Here is a sample :
In above example :
resc_io is I/O cost ( 559.00 in this sample)
resc_cpu is CPU cost ( 27509992 in this sample)
cost is total cost ( 562.23 in this sample)
CPU to IO ratio can be calculated with the following formula :
In this case CPU to IO ratio is 27509992/(562.23-559.00 ) = 8517025.39. This means that each I/O cost equals to 8517025.39 CPU cost.
In healthy system this factor should not be very low. Also the higher this ratio is, the more effective index will be to improve performance.
PS : Those who are interested, please Email me for full documentation on 10053 event and more sample on this subject.
In this post, I am trying to just make more sense of these three columns in an execution plan. Optimizer uses a CPU to I/O ratio to determine how much CPU cost would be equivalent to one I/O cost.
Here is guideline on how to find out this ratio and see if that make sense in your system.
1. Enable event 10053 .
alter session set max_dump_file_size = unlimited;
ALTER SESSION SET EVENTS '10053 trace name context forever,level 1';
EXPLAIN PLAN FOR --SQL STATEMENT--;
exit
2. Find trace file.
Trace file should be in user_dump_dest
3. Go through trace file and find access path for an index full scan or a table full scan
Here is a sample :
In above example :
resc_io is I/O cost ( 559.00 in this sample)
resc_cpu is CPU cost ( 27509992 in this sample)
cost is total cost ( 562.23 in this sample)
CPU to IO ratio can be calculated with the following formula :
cpu_to_io_ratio = resc_cpu / (cost - resc_io)
In this case CPU to IO ratio is 27509992/(562.23-559.00 ) = 8517025.39. This means that each I/O cost equals to 8517025.39 CPU cost.
In healthy system this factor should not be very low. Also the higher this ratio is, the more effective index will be to improve performance.
PS : Those who are interested, please Email me for full documentation on 10053 event and more sample on this subject.
Subscribe to:
Posts (Atom)