Manage DBMS_RESULT_CACHE during migrations

jonnoct

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

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.

Eddie D

Hi

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:

BEGIN
       DBMS_RESULT_CACHE.BYPASS(TRUE);
       DBMS_RESULT_CACHE.FLUSH;
    END;
    /

And the following code at the end of the deployment script:

    BEGIN
       DBMS_RESULT_CACHE.BYPASS(FALSE);
    END;
    /

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