We really welcome every external review of our slides. And also recommendations from customers visiting our workshops.
So it happened to me more than a week ago that Marco Patzwahl, the owner of MuniqSoft GmbH, had a very lengthy train ride in Germany (as the engine drivers go on strike this week it could have become even worse) and nothing better to do than reviewing our slide set. And he had plenty of recommendations.
Besides that he pointed us to something at least I was not aware of and added it to the slides:
In patch set 11.2.0.2 a new behaviour 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 behaviour not up-to-date and alligned 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 behaviour beginning with Oracle 11.2.0.2
If you would like to revert to the pre-11.2.0.2 behaviour 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 and this topic in the current version of the slides on slide 255.
Thanks to Marco for the review!!
And then I received an email from Kurt Van Meerbeeck today. Kurt is pretty well known in the Oracle community. And he’s the owner of jDUL/DUDE, a database unloading tool which bypasses the Oracle database engine and access data directly on the block level.
Kurt visited the upgrade workshop two weeks ago in Belgium and did highlight to me that 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 behaviour 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 extremly high loads this can cause real trouble. 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
This can be found now in the slide set as well on slide number 240.
And thanks to Kurt for this information!!
Hello , Need some help , is there a way we can know default underscore parameter value for 11.2.4 , i am amidst doing a database migration from onprem to RDS AWS , however we have 140 underscore paramters set and am not sure how can i derive default values if default values are derived then we can simply drop unsupported RDS underscore parameters but this list seems not easily available
Hi Rudresh,
wrong direction – you should move to Oracle OCI instead of AWS 😉
Anyhow,there should be never 140 underscores. This is insane, and nobody can get any control on this. Are you moving to 11.2.0.4 again, or do you move to 19c?
If you move to 19c, just get rid of all the underscores unless somebody has a good reason to keep one or the other.
If you move to 11.2.0.4, please be aware that it runs out of Extended Support in 5.5 weeks.
Cheers,
Mike