Datapatch return codes and helpful queries

The other week I was visiting a customer in Germany who had specific questions for datapatch. And luckily, Alejandro Diaz who is the Product Manager for Data Pump in our team has a history in datapatch.  He helped me with Datapatch return codes and helpful queries.

Photo by PuroClean of Fort Worth on Unsplash

Datapatch return codes

Yes, datapatch generates various return codes you can use to verify the outcome in case you program or maintain an automation.

• 0 for success
• 1 for prereq failure
• 2 for non-ignorable error during patch installation
• 255 runtime error

I may add another return code later as soon as we launch a new patching feature.

You may want to understand the difference between return codes 1 and 2:

• Return code 1
  occurs during the patching metadata checks. Situations where you may see this return code for instance are a missing patch directory, problems accessing the inventory, or corruptions in the underlying dictionary tables.
• Return code 2
  indicates an error in the patch content itself. This happens when a failure occurred while executing one of the scripts included as part of the apply process. Those apply scripts are shipped with some of the patches, and of course with the Release Updates.

Scope with many PDBs?

Another question I had was regarding the scope of the return code. Does is apply to a single PDB only, or to the batch of PDBs, or to all PDBs, or to all containers including the CDB$ROOT and the PDB$SEED and the PDBs?

And Alejandro helped me here again.

• Return code of 0
  means all required SQL patches were applied successfully in all PDBs included in that run.
• Return code of 2
  means there was at least one non-ignorable error while applying patch content, so at least one patch failed in at least one of the targeted PDBs.

You will be able to catch return codes based on run level, not on a per-PDB level. This is important when you deal with the datapatch return codes.  Since you can scope datapatch to specific PDBs, each individual execution returns a single code covering the set of PDBs it processed. For example, AutoUpgrade typically runs datapatch for CDB$ROOT first, then runs it again for the remaining PDBs in groups. Each call produces its own return code for the PDBs in scope.

Or, speaking of a more realistic scenario:
Let us assume you have a CDB with 63 PDBs and your CPU_COUNT=32. You patch with AutoUpgrade, and AutoUpgrade will now use the CPU_COUNT to assign parallel workers.

• CDB$ROOT: Maximum of 8 workers
• 1st group of PDBs: PDB$SEED and PDB01 to PDB15
  • We have CPU_COUNT=32 and each PDB gets 2 workers assigned by default leading to each group having 16 PDBs
  • PDB$SEED gets treated equally in this case
• 2nd group of PDBs: PDB16-PDB31
• 3rd group of PDBs: PDB32-PDB47
• 4th group of PDBs: PDB48-PDB63

So, there were 5 datapatch runs, and therefore you will receive 5-times a return code.

A PDB started only “restricted” after patching

Unfortunately, it happens sometimes that a PDB hits a patching error, for instance leading to return code 2. This may lead to the PDB only opening restricted after patching in case you have restarted. But when the PDB is only started in RESTRICTed mode, your application can’t connect anymore.

If you need to avoid such cases, you can use an underscore parameter with caution:

alter system set "_pdb_datapatch_violation_restricted"=false;

This parameter will allow the PDB to be started, even if it hit an error.

But it is key that you use a proper check script after patching to detect such cases, analyze them and fix the PDBs. Whether you then simply try to run datapatch against the individual PDB(s), or whether you tackle this with a Support ticket, depends certainly on the case.

In addition, you should always set:

alter system set "_exclude_seed_cdb_view"=false;

to avoid missing invalid objects or anything else in PDB$SEED. We’ve seen cases where this had caused trouble. The parameter leads to objects belonging to CON_ID=2 (aka PDB$SEED) being displayed in the DBA and CDB views.

Helpful patching queries

On the blog I share some general patching queries but Alejandro shared two helpful ones with me. I find them very helpful.

You can check the SQL patch registry. The below query looks at the latest row per patch. If you need to check this for a specific container use:

SELECT patch_id, action, status, action_time, description
FROM (
   SELECT p.*, ROW_NUMBER() OVER
     (PARTITION BY patch_id ORDER BY action_time DESC
     ) AS rn
   FROM dba_registry_sqlpatch p
 )
WHERE rn = 1
ORDER BY action_time DESC;

SELECT c.con_id,
       NVL(x.non_success_patches, 0) AS non_success_patches
FROM v$containers c
LEFT JOIN (
    SELECT con_id,
           COUNT(*) AS non_success_patches
    FROM (
        SELECT con_id,
               patch_id,
               status
        FROM (
            SELECT p.*,
                   ROW_NUMBER() OVER (
                       PARTITION BY con_id, patch_id
                       ORDER BY action_time DESC
                   ) AS rn
            FROM cdb_registry_sqlpatch p
        )
        WHERE rn = 1
          AND status <> 'SUCCESS'
    )
    GROUP BY con_id
) x
ON c.con_id = x.con_id
ORDER BY c.con_id;

       FAILED
  CON PATCHES
----- -------
    1     0
    2     0
    3     1

Further Links and Information

• Datapatch: Final blog post for cleanup and purge?
• Datapatch stores rollback information in the database
• Datapatch may be slower when traditional and unified auditing are on

–Mike