Patching, Upgrading and Migration

46.1 Patching

Quarterly Release Updates (RU) and Monthly Recommended Patches (MRP). Release Update Revisions (RUR) are discontinued. Critical Patch Updates (CPU) for security, plus one-off patches.

Tools: OPatch (opatch lsinventory, opatch apply), datapatch (applies SQL changes), opatchauto (Grid/RAC), and Fleet Patching and Provisioning (FPP).

Out-of-place patching (new Oracle Home, then switch) minimizes downtime. Rolling patching in RAC. Standby-first patching with Data Guard.

$ORACLE_HOME/OPatch/opatch lsinventory
$ORACLE_HOME/OPatch/opatch apply /stage/12345678
$ORACLE_HOME/OPatch/datapatch -verbose

SELECT patch_id, action, status, description FROM dba_registry_sqlpatch;
SELECT comp_name, version, status FROM dba_registry;

46.2 Upgrading

AutoUpgrade is the recommended tool (java -jar autoupgrade.jar -config cfg.txt -mode analyze|fixups|deploy). It handles prechecks, fixups, the upgrade, and the non-CDB to PDB conversion.

Other methods: DBUPGDIAG / dbupgrade (manual), Data Pump, Transportable Tablespaces, GoldenGate (near-zero downtime), Data Guard transient logical rolling upgrade (DBMS_ROLLING).

Upgrade paths: 11.2.0.4/12.x/18c/19c → 23ai (directly from 19c and 21c).

After an upgrade: the COMPATIBLE parameter, the optimizer (OPTIMIZER_FEATURES_ENABLE), regathering stats, SQL Plan Management to prevent regressions, SQL Performance Analyzer to compare.

46.3 Migration

Cross-platform (endianness): RMAN CONVERT, cross-platform transportable tablespaces with incremental backups (XTTS/M5 scripts).

To the cloud: Zero Downtime Migration (ZDM), OCI Database Migration service, Data Pump to object storage.

From other databases: SQL Developer Migration Workbench, GoldenGate, the SQL Translation Framework.