Thursday, March 17, 2011

Interesting two hidden parameters (Oracle 11.2)

Oracle hidden parameters are something that any serious DBA should not use if not suggested from Oracle technical stuff. But here are two of them which might be implemented without that if you are hit with described problems, like I had.

_DATAFILE_WRITE_ERRORS_CRASH_INSTANCE

In patch set 11.2.0.2 a new behavior for datafile write errors has been implemented. With this release ANY write error to a datafile will cause the instance to abort. Before 11.2.0.2 those errors usually led to an offline datafile if the database operates in archivelog mode (your production database do, don't they?!) and the datafile does not belong to the SYSTEM tablespace. Internal discussion found this behavior not up-to-date and aligned with RAC systems and modern storages. Therefore it has been changed and a new underscore parameter got introduced.
_DATAFILE_WRITE_ERRORS_CRASH_INSTANCE=TRUE
This is the default setting´and the new behavior beginning with Oracle 11.2.0.2

If you would like to revert to the pre-11.2.0.2 behavior you'll have to set in your init.ora/spfile this parameter to false. But keep in mind that there's a reason why this has been changed.

You'll find more info in MOS Note: 7691270.8.

_MEMORY_IMM_MODE_WITHOUT_AUTOSGA

According Kurt Van Meerbeeck pretty well known member in the Oracle community, who is owner of jDUL/DUDE, a database unloading tool which bypasses the Oracle database engine and access data directly on the block level, since Oracle 11.2.0.1 even though you haven't set neither SGA_TARGET nor MEMORY_TARGET (or set it to 0) the database might still do memory resize operations.

Reason why this behavior has been changed: Prevention of ORA-4031 errors.

ORA-04031: unable to allocate string bytes of shared memory

Cause: More shared memory is needed than was allocated in the shared pool.

Action: If the shared pool is out of memory, either use the DBMS_SHARED_POOL package to pin large packages, reduce your use of shared memory, or increase the amount of available shared memory by increasing the value of the initialization parameters SHARED_POOL_RESERVED_SIZE and SHARED_POOL_SIZE. If the large pool is out of memory, increase the initialization parameter LARGE_POOL_SIZE.

But on databases with extremely high loads this can cause real troubles. Further information can be found in MOS Note:1269139.1. And the parameter set to TRUE by default is called:
_MEMORY_IMM_MODE_WITHOUT_AUTOSGA=TRUE

Upgrade to Oracle 11g document

Nice document for upgrading to Oracle 11g can be found here.

In that document, on slides 254 and 239, previously referenced cases are exposed as well.

Hope this will also help someone.

Cheers!

Zagreb u srcu!

Copyright © 2009-2014 Damir Vadas

All rights reserved.


Sign by Danasoft - Get Your Sign