Manage DBMS_RESULT_CACHE during migrations
jonnoct
Posts: 5
Hi all,
With the new versions of Oracle if you need to migrate a patch to a live system you need to add code to the migration script to prevent the result cache from returning incorrect results. Specifically add code to the top and bottom of the script. If this something that can be added to Schema Compare so it generates its scripts correctly? I realize it probably won't be able to handle RAC environments completely on its own but it would still be a big help.
Thanks,
Jon
Oracle Doc
https://docs.oracle.com/cd/B28359_01/ap ... m#CHDGEFCF
With the new versions of Oracle if you need to migrate a patch to a live system you need to add code to the migration script to prevent the result cache from returning incorrect results. Specifically add code to the top and bottom of the script. If this something that can be added to Schema Compare so it generates its scripts correctly? I realize it probably won't be able to handle RAC environments completely on its own but it would still be a big help.
Thanks,
Jon
Oracle Doc
https://docs.oracle.com/cd/B28359_01/ap ... m#CHDGEFCF
This operation can be used when there is a need to hot patch PL/SQL code in a running system. If a code-patch is applied to a PL/SQL module on which a result cached function directly or transitively depends, then the cached results associated with the result cache function are not automatically flushed (if the instance is not restarted/bounced). This must be manually achieved.
To ensure correctness during the patching process follow these steps:
Place the result cache in bypass mode, and flush existing result.BEGIN DBMS_RESULT_CACHE.BYPASS(TRUE); DBMS_RESULT_CACHE.FLUSH; END; /
This step must be performed on each instance if in a Oracle Real Application Clusters environment.
Apply the PL/SQL code patches.
Resume use of the result cache, by turning off the cache bypass mode.BEGIN DBMS_RESULT_CACHE.BYPASS(FALSE); END; /
This step must be performed on each instance if in a Oracle Real Application Clusters environment.
Comments
Thank you for your forum post.
I have submitted a Bug Report / Enhancement Request to add the code for DBMS_RESULT_CACHE commands at the beginning of the deployment script:
And the following code at the end of the deployment script:
The reference for this Bug Report / Enhancement Request is OC-809.
Sadly at the time of typing this reply, I cannot provide details as to when you may see this request in a future version of Schema Compare for Oracle.
Many Thanks
Eddie
Senior Product Support Engineer
Redgate Software Ltd
Email: support@red-gate.com