Datapatch AND What to do if the status of a datapatch action was not SUCCESS due to finding non-ignorable errors

1.

Enterprise Manager:

Starting version 12.1 Enterprise Manager now calls datapatch to complete post patch actions upon any 12c or later database restart.

As noted above, datapatch contains the logic to identify if any post-patch SQL actions are pending.

OPatchAuto :

OPatchAuto calls datapatch to complete post patch actions upon installation of the binary patch and restart of the database.

As noted earlier, datapatch identifies the post install instructions which are necessary and automatically completes the same.

Post patch activity include both the application and the removal or rollback of SQL changes in the database.

OPatch :

Datapatch integration with OPatch is not possible as OPatch is executed when the database is down and

datapatch requires the database to be opened to complete its activity.

When

patches are installed or rollbacked using OPatch then datapatch needs

to be explicitly invoked if instructed to do so in the patch readme.

Datapatch is designed to be idempotent and can be run for all patches and

it is recommended that datapatch be run post any patch apply or rollback activity when OPatch is used.

(In

computing, an idempotent operation is one that has no additional effect

if it is called more than once with the same input parameters.)

Datapatch :

Datapatch is the patching tool to complete the post-patch SQL actions for RDBMS patches.

Windows specific : On Windows platforms the script name is datapatch.bat.

RAC specific: For a RAC environment, after the binary patch has been

applied on all nodes run Datapatch to complete the post-install SQL

deployment for the PSU only from one node. Datapatch need not be run on

all the nodes.

Datapatch determines the requisite apply/rollback actions by matching an internal repository with the patch inventory.

Datapatch should be invoked when the database is restarted after a patching session.

Enterprise Manager and OPatchAuto call datapatch automatically during every patching session.

If

OPatch is used to install RDBMS patches then datapatch has to be

explictly called to complete any patching actions after database

restart.

Datapatch will automatically determine the set of patches

that need to be installed and a set of patches that need to be rolled

back.

Datapatch ensures that a patch has been installed/rolled back in all

RAC instances before to initiate any post-patch SQL actions on the

database.

In Oracle Multitenant environment datapatch will patch the root and any pluggable databases that are opened.

In

order to patch all pluggable databases, it should be ensured that

before to invoke datapatch all pluggable databases are opened.

If an

unpatched pluggable database is opened in Oracle Multitenant then

calling datapatch will complete the pending patch actions.

Datapatch usage :

All arguments are optional, if there are no arguments then datapatch will automatically determine which SQL scripts need to

be run in order to complete the installation of any patches that contain post-patch SQL instructions.

Optional arguments:

-db <sid>

Use the specified database's SID rather than $ORACLE_SID

-apply <patch1,patch2,...,patchn>

Only consider the specified patch list for apply operations

-rollback <patch1,patch2,...,patchn>

Only consider the specified patch list for rollback operations

-force

Run the apply and/or rollback scripts even if not necessary per the SQL registry

-prereq

Run prerequisite checks only, do not actually run any scripts

-oh <oracle_home value>

Use the specified directory to determine what patches are installed

-verbose

Output additional information used for debugging

-help

Output usage information and exit

-version

Output build information and exit

Datapatch in a Multitenant Environment:

When datapatch is run to invoke any SQL actions in a Multitenant

environment, datapatch only applies the changes to the ROOT, SEED and

any open PDBs based on the pending actions.  For any PDB that is not

currently open, the SQL patch will not be applied.  When such a PDB is

reopened, it will be opened in restricted mode as there will be a patch

level mismatch between the PDB and the ROOT.  

Call DBMS_PDB.CHECK_PLUG_COMPATIBILITY on 12.1.0.1. PDB xml file will return these two violations, which are expected.

  SQL Patch              ERROR

SQL patch bug # mismatch: Installed in the PDB but not in the CDB.   Install the SQL patch in the PDB or the CDB

This error can be cleared by running datapatch again and closing and reopening the affected PDB(s)

1)    Invoke datapatch:

% cd $ORACLE_HOME/OPatch

% datapatch

2)    Close and reopen the affected PDB

Connect as SYS

alter pluggable database <PDB Name> close instances =all;

alter pluggable database <PDB Name> open read write instances =all;

3) Check again for plugin violations (by

querying pdb_plug_in_violations in cdb$root), there should be none and

the pluggable database should be opened in normal mode

Changes for DB release 12.1.0.2

Miscellaneous

1. Catbundle Integration:  Datapatch now assumes the role of

   catbundle when applying bundles/PSU.  As a result of this change

   catbundle.sql has been deprecated and patch registry is maintained only

   in registry$sqlpatch.  Application of the PSU does not update

   registry$history table anymore.  The status for patch application is now

   entirely maintained in registry$sqlpatch

2. Support for patch UID:  Datapatch now uses patch-ID and

   patch-UID to uniquely identify a patch. With this change, datapatch now

   distinguishes between different versions of a single patch.  This allows

   datapatch to handle the case of re-issued patches correctly.

3. Support for –force –rollback all:  Datapatch now provides

   the option to rollback all patches from a given container DB using the

   –force option.  This is useful for unplug/plug of container databases

4. Integration with upgrade/downgrade scripts
5. Improved error checking for PLS errors and warnings.

Prerequisite Checks

In 12.1.0.2 Datapatch now has pre-req checks for a number of

conditions.  These pre-req checks prevent patching and have the

following reasons and potential issues:

1. Upgrade mode check:

   If a patch requires upgrade mode startup,

   datapatch enforces that via a pre-req check.  To overcome this error,

   DB/PDB needs to be re-started in Upgrade/Migrate mode to complete

   patching.  Please note that when there are a number of simultaneous

   patch applications pending, datapatch will fail the upgrade mode pre-req

   check if any of the pending patches requires upgrade mode startup. The

   database being started in Upgrade mode will have no impact on other

   patches being applied which do not require Upgrade mode.

   Alternatively,

   any patches not requiring upgrade mode can be installed by invoking

   datapatch with the –apply < bug # > option

2. Check for existence of Apply/Rollback scripts:

   Datapatch now

   ensures that apply/rollback scripts are present for any pending patch

   apply/rollback.  If the files are not available datapatch will fail with

   a pre-req error.  For out of place rollback please copy any sqlpatch

   files to the $OH in order to overcome this check and allow rollback to

   proceed

3. Queryable Inventory connectivity :

   Datapatch flags a

   Queryable inventory error if for some reason Queryable Inventory returns

   an error status.  Further details can be derived by calling select

   dbms_qopatch.get_pending_activity from dual and noting the error

   message.  Further details on resolving Queryable Inventory issues can be

   found at Note 1530108.1, "Oracle Database 12.1 : FAQ on Queryable Patch Inventory"

4. DB connectivity:  datapatch has a pre-req check to ensure DB connectivity.
5. Pre-req to ensure we can create directories under cfgtoollogs dir.  

2.

Symptoms

datapatch reports non-ignoreable errors were found during an apply or rollback.  For example:

Errors during an apply

% datapatch -verbose

SQL Patching tool version 12.1.0.1.0 on Thu Jan  2 15:26:52 2014

Copyright (c) 2013, Oracle.  All rights reserved.

Connecting to database...OK

Determining current state...

Currently installed SQL Patches:

C patch 17027533 is a bundle patch and prereq for a later bundle

Currently installed C Patches: 17552800

Nothing to roll back

The following patches will be applied: 17552800

Adding patches to installation queue...

Installing patches...

Validating logfiles...

Patch 17552800 apply: WITH ERRORS

logfile:

/u01/app/oracle/product/12.1.0.1.0/sqlpatch/17552800/17552800_apply_BOD1211A_BOD1211A_2014Jan02_15_27_04.log

(no errors)

  catbundle generate logfile:

/u01/app/oracle/cfgtoollogs/catbundle/catbundle_PSU_BOD1211A_BOD1211A_GENERATE_2014Jan02_15_27_04.log

  catbundle apply logfile:

/u01/app/oracle/cfgtoollogs/catbundle/catbundle_PSU_BOD1211A_BOD1211A_APPLY_2014Jan02_15_27_05.log

(errors)

    Error at line 12: SP2-0310: unable to open file "/u01/app/oracle/product/12.1.0.1.0/patch/scripts/bug16825779.sql"

See support note 1609718.1 for information on how to resolve

the installation attempts which had non ignorable errors.

SQL Patching tool complete on Thu Jan  2 15:27:09 2014

Additionally, the view  dba_registry_sqlpatch will show "WITH ERRORS"

  PATCH_ID ACTION     STATUS          ACTION_TIME                    DESCRIPTION

---------- ---------- --------------- ------------------------------ --------------------

  17552800 APPLY      WITH ERRORS     02-JAN-14 03.27.09.020177 PM   bundle:PSU

Errors during a rollback

Errors returned by datapatch

Currently installed SQL Patches: 17552800

Currently installed C Patches:

The following patches will be rolled back: 17552800

Nothing to apply

Patch 17552800 rollback: WITH ERRORS

/u01/app/oracle/product/12.1.0.1.0/sqlpatch/17552800/17552800_rollback_BOD1211A_BOD1211A_2013Dec31_19_15_54.log

/u01/app/oracle/cfgtoollogs/catbundle/catbundle_PSU_BOD1211A_BOD1211A_GENERATE_2013Dec31_19_15_54.log

  catbundle rollback logfile:

/u01/app/oracle/cfgtoollogs/catbundle/catbundle_PSU_BOD1211A_BOD1211A_ROLLBACK_2013Dec31_19_15_55.log

    Error at line 1595: ORA-02303: cannot drop or replace a type with type or table dependents

SQL Patching tool complete on Tue Dec 31 19:15:58 2013

  Additionally, the view  dba_registry_sqlpatch will show "WITH ERRORS"

  17552800 APPLY      SUCCESS         31-DEC-13 07.09.49.755424 PM   bundle:PSU

  17552800 ROLLBACK   WITH ERRORS     31-DEC-13 07.15.58.746868 PM   bundle:PSU

Changes

Datapatch was executed to perform SQL patch apply or rollback

Cause

During the apply or rollback, datapatch found errors in a log file

Solution

Solution for errors during an apply

Fix the issue and rerun SQL apply

1. Resolve the issue causing the SQL error
2. Rerun datapatch

 dba_registry_sqlpatch will now show:

  17552800 APPLY      SUCCESS         02-JAN-14 03.32.43.991067 PM   bundle:PSU

Rollback patch from ORACLE_HOME and rollback SQL from db (via force rollback)

NOTE: Should a patch be rolled back from the ORACLE_HOME having a dba_registry_sqlpatch STATUS of "WITH ERROR", datapatch will not re-execute the SQL necessary to rollback the SQL to the database unless the -force  option is utilized.  The datapatch output will be similar to:

    Connecting to database...OK

    Determining current state...

    Currently installed SQL Patches:

    Currently installed C Patches:

    Nothing to roll back

    Nothing to apply

    SQL Patching tool complete on Thu Jan  2 15:52:32 2014

1. Rollback the patch from the ORACLE_HOME
2. Rerun datapatch with the force option

% datapatch -rollback < patch number > -force -verbose

The datapatch output will be similar to:

% datapatch -rollback 17552800 -force -verbose

SQL Patching tool version 12.1.0.1.0 on Thu Jan  2 15:53:17 2014

-force specified, skipping rollback list check...

Patch 17552800 rollback: SUCCESS

/u01/app/oracle/product/12.1.0.1.0/sqlpatch/17552800/17552800_rollback_BOD1211A_BOD1211A_2014Jan02_15_53_18.log

/u01/app/oracle/cfgtoollogs/catbundle/catbundle_PSU_BOD1211A_BOD1211A_GENERATE_2014Jan02_15_53_18.log

/u01/app/oracle/cfgtoollogs/catbundle/catbundle_PSU_BOD1211A_BOD1211A_ROLLBACK_2014Jan02_15_53_19.log

SQL Patching tool complete on Thu Jan  2 15:53:22 2014

dba_registry_sqlpatch will now show:

  17552800 APPLY      WITH ERRORS     02-JAN-14 03.46.47.835994 PM   bundle:PSU

  17552800 ROLLBACK   SUCCESS         02-JAN-14 03.53.22.343742 PM   bundle:PSU

Solution for errors during a rollback

Fix the issue and rerun SQL rollback

  17552800 ROLLBACK   SUCCESS         31-DEC-13 08.58.14.104658 PM   bundle:PSU

Resolve the issue causing the SQL errors and reinstall the patch

NOTE: Should a patch be reinstalled to the

ORACLE_HOME having a dba_registry_sqlpatch STATUS of "WITH ERROR",

datapatch will not re-execute the SQL necessary to apply the SQL to the

database unless the -force  option is utilized.  The datapatch output

will be similar to:

    Currently installed SQL Patches: 17552800

    C patch 17027533 is a bundle patch and prereq for a later bundle

    Currently installed C Patches: 17552800

    Nothing to apply

    SQL Patching tool complete on Tue Dec 31 20:33:49 2013

2. Reinstall the patch to the ORACLE_HOME

% datapatch -apply < patch number > -force -verbose

% datapatch -apply 17552800 -force -verbose

-force specified, skipping apply list check...

Patch 17552800 apply: SUCCESS

/u01/app/oracle/product/12.1.0.1.0/sqlpatch/17552800/17552800_apply_BOD1211A_BOD1211A_2013Dec31_20_36_28.log

/u01/app/oracle/cfgtoollogs/catbundle/catbundle_PSU_BOD1211A_BOD1211A_GENERATE_2013Dec31_20_36_28.log

/u01/app/oracle/cfgtoollogs/catbundle/catbundle_PSU_BOD1211A_BOD1211A_APPLY_2013Dec31_20_36_29.log

   PATCH_ID ACTION     STATUS          ACTION_TIME                    DESCRIPTION

  17552800 APPLY      SUCCESS         31-DEC-13 08.36.33.355797 PM   bundle:PS

炊煙起了；夕陽下了；細雨來了

多調試,互動式程式設計體驗

記錄，獨立思考，對比

感謝轉載作者

修車

國産化

read and connect

匍匐前進,

講故事