Oracle Patch

NOTE: If you are applying the patches to an inactive ORACLE_HOME you should skip steps that would require a database connection. On this page they have a background that matches this information box.

  • This page provides generic patching instructions based on some patch specific variables being set.

  • The assumption is that "Database RU and OJVM Combo" patches will be used.

  • The assumption is that the .profile contains all recommended settings and all recommended aliases are set.

  • For Data Guard environments...

Patch the Physical Standby databases first (as an "inactive" database). If the Patch is "Standby First" installable then you can restart managed recovery before the Primary is patched (there is a 31 day limit for this mismatch).You might choose to test the Patch in a Snapshot Standby before rolling out to the Primary (i.e. for a "Standby First" patch you could patch the Standby, open as Snapshot Standby, run Datapatch on the Snapshot Standby, perform testing, then convert back to Physical Standby. For non-"Standby First", you would also need to "opatch rollback" the patch before re-enabling managed recovery).

  • Guaranteed Restore Points

If you intend to set an optional Guaranteed Restore Point for a Data Guard configuration be sure to set the Restore Point in all the Standby databases before creating the restore Point in the Primary instance. When reverting to the Restore Point, flashback the Standby databases first. Managed recovery will not work if the Standby databases are newer than the Primary.

Prepare

Ensure correct target environment is set (i.e. ORACLE_HOME and ORACLE_SID)

Pre-Req Checks

prereq

Apply


NOTE: In a Data Guard configuration always patch Standby databases first.

Stop Client Applications

Prevent any applications that may access your target database from doing so.

Guaranteed Restore Point (Optional)

NOTE: In a Data Guard configuration, always create the Restore Point on all Standby databases before the Primary

applyoff # Physical Standby Databases only

grpset

Stop Listener (if running)

NOTE: This only applies if the Listener is running from the ORACLE_HOME you are patching.NOTE: Assumes LISTENER_NAME environment variable is set in the .profile. If the variable is null then the command will try to stop the Listener named LISTENER.

stoplist

Stop Database (if running)

NOTE: The database may not be running if you are patching a target ORACLE_HOME ahead of an Upgrade.

dbshut

Rollback Conflicts

Not currently required.

Apply DB RU

cd /u01/img/${PTAR}/${PCOM}/${DBRU}

opatch apply -silent

Note: The Warnings listed below are expected on AIX and can be safely ignored. Always check opatch lsinventory to confirm patch application.

ld: 0711-773 WARNING:

ld: 0711-783 WARNING:

ld: 0711-319 WARNING:

ld: 0711-415 WARNING:

ld: 0711-224 WARNING:

ld: 0711-324 WARNING:

ld: 0711-301 WARNING:

ld: 0711-345 WARNING:

Note: If /u01/img/ocm.rsp does not exist then create it using the instructions here

Note: You may see a reference to Doc ID 2161861.1... this note does not exist on MOS... however, as long as the mentioned patch is a previous RU, then this can be ignored...

Sub-set patch [28440711] has become inactive due to the application of a super-set patch [28790654].

Please refer to Doc ID 2161861.1 for any possible further required actions.

Apply OJVM RU

cd /u01/img/${PTAR}/${PCOM}/${RJVM}

opatch apply -silent

Note: The Warnings listed below are expected on AIX and can be safely ignored. Always check opatch lsinventory to confirm patch application.

ld: 0711-773 WARNING:

ld: 0711-783 WARNING:

ld: 0711-319 WARNING:

ld: 0711-415 WARNING:

ld: 0711-224 WARNING:

ld: 0711-324 WARNING:

ld: 0711-301 WARNING:

ld: 0711-345 WARNING:

Note: You may see a reference to Doc ID 2161861.1... this note does not exist on MOS... however, as long as the mentioned patch is a previous JVM RU, then this can be ignored...

Sub-set patch [28440711] has become inactive due to the application of a super-set patch [28790654].

Please refer to Doc ID 2161861.1 for any possible further required actions.

Apply Merge Patches

Not currently required.

Apply Other Patches

Not currently required.

Check Patches are applied to ORACLE_HOME

lsinv

Startup Upgrade

NOTE: In a Data Guard configuration, this step only applies to the Primary.NOTE: If you are patching a target ORACLE_HOME prior to an Upgrade then this step can be ignored.

sqlplus / as sysdba

startup upgrade

exit

Datapatch

NOTE: In a Data Guard configuration, this step only applies to the Primary.NOTE: If you are patching a target ORACLE_HOME prior to an Upgrade then this step can be ignored.

datapatch

Shutdown Database

NOTE: In a Data Guard configuration, this step only applies to the Primary.NOTE: If you are patching a target ORACLE_HOME prior to an Upgrade then this step can be ignored.

dbshut

Startup Database Instance

NOTE: If you are patching a target ORACLE_HOME prior to an Upgrade then this step can be ignored.

sqlplus / as sysdba

startup # Standalone or Primary Instances only

startup mount # Standby Instances only

exit

Upgrade RMAN Catalog

NOTE: This only needs to be run if this database is using an RMAN Catalog for backups.NOTE: The UPGRADE CATALOG command must be entered twice to confirm the upgrade.

rman catalog username/password@alias

UPGRADE CATALOG;

UPGRADE CATALOG;

EXIT;

Check Patch Registry

NOTE: In a Data Guard configuration, this step only applies to the Primary.NOTE: If you are patching a target ORACLE_HOME prior to an Upgrade then this step can be ignored.

checkreg

Start Listener

NOTE: Assumes LISTENER_NAME environment variable is set in the .profile. If the variable is null then the command will try to start the Listener named LISTENER.NOTE: This only applies if the Listener is running from the ORACLE_HOME you are patching.

startlist

Start Client Applications

Any applications that may access your target database can now safely do so again.

Dowtime Reporting

NOTE: If you are patching a target ORACLE_HOME prior to an Upgrade then this step can be ignored.

adrci

show alert -p "originating_timestamp > systimestamp-1 and message_group like '%ddl%'" -term

Drop Guaranteed Restore Point

NOTE: Only Drop the Restore Point when you are happy there is no requirement to Flashback.

Rollback


NOTE: Rollback Data Guard Standby databases before rolling back the Primary

Stop Client Applications

Prevent any applications that may access your target database from doing so.

Stop Listener (if running)

NOTE: This only applies if the Listener is running from the ORACLE_HOME you are patching.NOTE: Assumes LISTENER_NAME environment variable is set in the .profile. If the variable is null then the command will try to stop the Listener named LISTENER.

stoplist

Stop Database (if running)

NOTE: The database may not be running if you are patching a target ORACLE_HOME ahead of an Upgrade.

dbshut

Rollback Patches

opatch rollback -id ${DBRU} -silent

opatch rollback -id ${RJVM} -silent

Startup Upgrade

NOTE: In a Data Guard configuration, this step only applies to the Primary.NOTE: If you are patching a target ORACLE_HOME prior to an Upgrade then this step can be ignored.

sqlplus / as sysdba

startup upgrade

exit

Datapatch

NOTE: In a Data Guard configuration, this step only applies to the Primary.NOTE: If you are patching a target ORACLE_HOME prior to an Upgrade then this step can be ignored.

datapatch

Shutdown Database

NOTE: In a Data Guard configuration, this step only applies to the Primary.NOTE: If you are patching a target ORACLE_HOME prior to an Upgrade then this step can be ignored.

dbshut

Startup Database Instance

NOTE: If you are patching a target ORACLE_HOME prior to an Upgrade then this step can be ignored.

sqlplus / as sysdba

startup # Standalone or Primary Instances only

startup mount # Standby Instances only

exit

Upgrade RMAN Catalog

NOTE: This only needs to be run if this database is using an RMAN Catalog for backups.NOTE: The UPGRADE CATALOG command must be entered twice to confirm the upgrade.

rman catalog username/password@alias

UPGRADE CATALOG;

UPGRADE CATALOG;

EXIT;

Check Patch Registry

NOTE: If you are patching a target ORACLE_HOME prior to an Upgrade then this step can be ignored.NOTE: This does not apply to Physical Standby databases.

checkreg

The registry$sqlpatch view will record the APPLY and the ROLLBACK as shown below...

ACTION ACTION_TIME PATCH_ID DESCRIPTION

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

APPLY 27-APR-20 13.53.23.280012 30805580 OJVM RELEASE UPDATE 12.2.0.1.200414

APPLY 27-APR-20 13.53.26.453299 30886680 DATABASE APR 2020 RELEASE UPDATE 12.2.0.1.200414

ROLLBACK 27-APR-20 16.11.55.612517 30805580 OJVM RELEASE UPDATE 12.2.0.1.200414

ROLLBACK 27-APR-20 16.11.59.149423 30886680 DATABASE APR 2020 RELEASE UPDATE 12.2.0.1.200414

Start Listener

NOTE: Assumes LISTENER_NAME environment variable is set in the .profile. If the variable is null then the command will try to start the Listener named LISTENER.NOTE: This only applies if the Listener is running from the ORACLE_HOME you are patching.

startlist

Dowtime Reporting

NOTE: If you are patching a target ORACLE_HOME prior to an Upgrade then this step can be ignored.

adrci

show alert -p "originating_timestamp > systimestamp-1 and message_group like '%ddl%'" -term

If you Rollback a RU then you should reapply any previous RU before restarting applications.

Flashback


NOTE: Using a Guaranteed Restore Point allows you to rollback the patches quickly but DOES RESULT IN DATA LOSS if users have performed any DML after patching is complete. NOTE: To avoid data loss use the Rollback procedure.NOTE: Flashback any Standby databases first. Flashback the Primary last. Do not start the Apply processes until all databases are flashed back.NOTE: Repeat the process below for each database instance in a Data Guard configuration. Take extra care where steps only apply to Standby or Primary instances.

Stop Client Applications

Prevent any applications that may access your target database from doing so.

Stop Listener

NOTE: This only applies if the Listener is running from the ORACLE_HOME you are patching.NOTE: Assumes LISTENER_NAME environment variable is set in the .profile. If the variable is null then the command will try to stop the Listener named LISTENER.

stoplist

Stop Database

dbshut

Rollback Patches

opatch rollback -id ${DBRU} -silent

opatch rollback -id ${RJVM} -silent

Startup Database Instance

sqlplus / as sysdba

startup # Standalone or Primary Instances only

startup mount # Standby Instances only

exit

Start Listener

NOTE: Assumes LISTENER_NAME environment variable is set in the .profile. If the variable is null then the command will try to start the Listener named LISTENER.NOTE: This only applies if the Listener is running from the ORACLE_HOME you are patching.

startlist

Guaranteed Restore Point

NOTE: In a Data Guard configuration, always create the Restore Point on all Standby databases before the Primary.NOTE: The Apply process on all Standby databases needs to be stopped.NOTE: Do not Open the Physical Standby databases

applyoff # Set Apply Off (Physical Standby Databases only)

grpfback # Flashback to Guaranteed Restore Point

dbopenresetlogs # Open reset Logs (Standalone or Primary only)

invobjcnt # Check Invalid Objects (Standalone or Primary only)

Check Patch Registry

NOTE: If you are patching a target ORACLE_HOME prior to an Upgrade then this step can be ignored.NOTE: This does not apply to Physical Standby databases.

checkreg

Your Patch activity should have disappeared.

Restart Data Guard Apply

NOTE: Perform this step only when all Standby databases and the Primary database have been flashed back to the Guaranteed Restore Point.

For each Standby with the environment correctly set...

applyon