Oracle DBA

In mission-critical systems, downtime is not an option. Organizations demand continuous availability — even during platform upgrades. This is where a Zero Downtime Database Upgrade from Oracle 12c to 19c using Oracle GoldenGate becomes a strategic capability. In this post, we walk you through the full process to achieve an upgrade with minimal disruption.

---

Why Choose GoldenGate for Zero Downtime Upgrades?

Upgrading a database in place usually involves downtime for applying patches, converting structures, recompiling, and validating objects. In high-availability environments, that downtime can translate into lost transactions and unhappy customers.

With Oracle GoldenGate, you can decouple the upgrade activity from the live database, synchronize changes in real time, and minimize the cutover window to just seconds or a minute. The key advantages:

• Rolling upgrade architecture: Offload heavy work to a clone environment rather than touching the live system.
• Continuous data synchronization: All transactional changes from 12c continue flowing to the 19c target.
• Failback support: If something goes wrong, you can revert with minimal risk.
• Transparent to applications: The application switchover is the only stage where some interruption occurs — and even that is minimal.

---

Environment & Prerequisites

Before starting, ensure:

1. Network connectivity between source (12c) and target (19c) is solid.
2. Oracle GoldenGate software (matching or compatible version) is installed on both sides.
3. You have adequate privileges and schema access for replication, supplemental logging, and object definitions.
4. You have backups and a recovery plan in case issues arise.

---

High Level Workflow

Here’s the roadmap:

1. Install and configure GoldenGate on both source and target.
2. Enable supplemental logging / schema tran data on the 12c source.
3. Configure EXTRACT and Data Pump on the source.
4. Configure REPLICAT on the target.
5. Perform initial load (export/import) of data using SCN.
6. Start the replicat process from the SCN to catch up changes.
7. Validate data sync, cutover the application, and optionally decommission or failback.

---

Step-by-Step Upgrade Process

1. Configure GoldenGate Manager on Source (12c)

On the 12c server:

./ggsci

Inside GGSCI:

dblogin userid gguser, password gguser
view param mgr
info mgr

Ensure the Manager is running (listening on a port, e.g. 7809).

2. Enable Supplemental / Schema Change Tracking

Still in GGSCI:

add schematrandata <schema>

This enables supplemental logging and marks the schema for change capture.

Verify:

info schematrandata <schema>

3. Create EXTRACT Process

view param ext1
-- if parameters don’t exist, define:
add extract ext1 tranlog begin now
add exttrail /path/to/dirdat/ac extract ext1

Set parameters:

EXTRACT ext1
SETENV (ORACLE_SID="ORADBWR")
SETENV (ORACLE_HOME="/u01/app/oracle/product/12.2.0/dbhome_1")
USERID gguser@oradbwr, PASSWORD gguser
DDL INCLUDE ALL
TABLE schema.*;

4. Create Pump / Data Pump Process

add extract dpump1
RMTHOST <target_host>, MGRPORT <target_mgr_port>
RMTTRAIL /path/to/dirdat/ad
DDL INCLUDE ALL
TABLE schema.*;

5. Configure GoldenGate Manager on Target (19c)

Switch to the target (19c) side:

./ggsci
view param mgr
info mgr

Ensure Manager is running properly on the target.

6. Perform Initial Load via Export / Import

On the target 19c:

impdp system/oracle dumpfile=initial.dmp logfile=initial.log directory=DATA_PUMP_DIR

This brings the bulk of the data over.

7. Start Replicat from SCN

Obtain SCN from source at the time of export:

-- record SCN value

Then on target:

start replicat rep1 aftercsn <scn_number>

Check status:

info all

At this point, all incremental changes from 12c are applying on the 19c side in near real time.

8. Validate and Verify Data

Once replicat is running and lag is minimal:

• Compare record counts between source and target tables.
• Use SQL MINUS or data comparison logic to ensure data consistency.
• For CLOB/BLOB columns, use dbms_lob.compare or specialized logic.

9. Cutover Application

When you’re confident the target is in sync and stable:

• Stop or disable access to the source database (or lock it).
• Redirect application connections to the target 19c instance.
• (Optional) disable job queue processes, listeners, or other background jobs on source.

10. Failback (Optional, if needed)

If you need to revert:

1. Ensure GoldenGate continues replicating in both directions.
2. Stop the target application.
3. Wait for replicat to apply all target → source changes.
4. Redirect applications back to source.
5. Clean up or decommission the target if desired.

Example Session Snippets

On 12c Source:

GGSCI> add schematrandata odba
GGSCI> add extract ext1 tranlog begin now
GGSCI> add exttrail /u01/app/oracle/gg/dirdat/ac extract ext1

On 19c Target:

impdp system/oracle dumpfile=odba1.dmp logfile=odba1.log directory=DATA_PUMP_DIR
GGSCI> start replicat rep1 aftercsn 1547381
GGSCI> info all

After this, verify:

SELECT COUNT(*) FROM my_table;

on both source and target to confirm data transfer success.

Best Practices & Tips

• Use a clone or staging database for conversions and heavy upgrades — don’t do large structure changes on the live instance.
• Minimize DDL during cutover — schema changes are tricky under replication. If needed, add new objects ahead of time.
• Regular data validation is critical — don’t assume full parity without checks.
• Plan for fallback — having a rollback strategy gives confidence in the entire process.
• Monitor GoldenGate lag continuously, especially before cutover.
• Test thoroughly in nonproduction environments to validate your specific schema, data types, constraints, triggers, and edge cases.

By orchestrating your 12c → 19c upgrade with Oracle GoldenGate, you can maintain live operations, avoid service interruptions, and switch over with only minimal downtime for the application layer. This is the power of a true zero downtime approach. Let me know if you’d like me to convert this into HTML, Markdown, or add diagrams and code blocks for your blog.