Migrating Oracle 11.2.0.4 to Oracle Database 19c on OCI with almost no downtime, told from both the architect's and the product manager's chairs

Chapter 1: The Server Nobody Wanted to Touch

Every company has one. In our story it's PROD11, an Oracle 11.2.0.4 single-instance database on Oracle Linux 5.6, running quietly in a rack that predates half the team. It holds orders, billing, and the customer ledger.

The product manager, Priya, opens the meeting with a number rather than a technology: "How many minutes can the business be down?"

The answer from the business was "as close to zero as you can get." That answer decided the whole design.

The architect, Arjun, translated it: "That rules out a plain export/import. We need an online migration: an initial load plus continuous replication, then a short cutover."

The infographic you're looking at shows exactly that shape:

On-prem Oracle 11.2.0.4 → OCI Database Migration Service (DMS) → Oracle Database 19c on OCI

Chapter 2: Requirements Before Architecture

Priya's first artifact was a one-page requirements table. The infographic's Section 1 is the same idea:

Question	Answer
Source	Oracle 11.2.0.4, single instance, Linux
Target	Oracle 19c on OCI (single instance or RAC)
Migration type	Online: initial load + continuous replication
Downtime	Minimal, with a target defined by the business
Connectivity	Private: VPN or FastConnect
Prerequisites	Validate compatibility, objects, data types, privileges, network

She adds a rule for the project: "We don't start migrating until we can prove the source is ready." That rule led to the most valuable week of the project, the pre-flight checks.

Chapter 3: The Architecture in One Breath

Arjun sketches it on a whiteboard, matching Section 2 of the infographic:

1. The source (11.2.0.4) sits on-premises with the application still live.
2. A private network path (VPN or FastConnect) connects it to the OCI VCN.
3. DMS orchestrates everything: connections, pre-migration validation, initial load via Data Pump, continuous replication via Oracle GoldenGate, monitoring, and cutover.
4. The target 19c database is populated, kept in sync, and finally becomes the system of record.

The key insight is that DMS doesn't move data in one motion. It moves it in two phases that overlap: a bulk snapshot, then a stream of changes that catches up with everything that happened during the snapshot.

Chapter 4: Preparation, Where Migrations Are Won

(Workflow Steps 1–2 in the infographic)

Arjun's rule: "Every assumption becomes a query." These are the checks he ran on the source. I've marked which ones I validated against Oracle documentation and which come from working practice.

4.1 Know what you're migrating

sql

-- Exact version

SELECT banner FROM v$version;

 

-- Character sets (must be understood before the load)

SELECT parameter, value

FROM   nls_database_parameters

WHERE  parameter IN ('NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET');

 

-- Size by application schema (adjust the exclusion list to your environment)

SELECT owner, ROUND(SUM(bytes)/1024/1024/1024, 2) AS size_gb

FROM   dba_segments

WHERE  owner NOT IN ('SYS','SYSTEM','OUTLN','DBSNMP','SYSMAN','XDB','CTXSYS',

                     'MDSYS','ORDSYS','WMSYS','EXFSYS','APPQOSSYS')

GROUP  BY owner

ORDER  BY size_gb DESC;

Note that 11.2.0.4 has no ORACLE_MAINTAINED column in DBA_USERS (that arrived in 12c), which is why the schema exclusion list is written out by hand.

4.2 Measure the change rate

This is the number the PM cares about most. The redo rate predicts how hard GoldenGate will work and how long the "catch-up" will take.

sql

SELECT TRUNC(first_time,'HH24')                       AS hour,

       ROUND(SUM(blocks*block_size)/1024/1024/1024,2) AS redo_gb

FROM   v$archived_log

WHERE  first_time > SYSDATE - 7

AND    dest_id = 1

GROUP  BY TRUNC(first_time,'HH24')

ORDER  BY 1;

The dest_id = 1 filter prevents double-counting when multiple archive destinations exist. Look for the busiest hour, not the average. That's your worst case for replication lag.

4.3 Can GoldenGate see the changes at all?

Continuous replication reads the redo logs, so the source must be configured to log enough information. Oracle's DMS documentation lists the requirements for an online-migration source: archive log mode, force logging, ENABLE_GOLDENGATE_REPLICATION=TRUE, and database supplemental logging. Force logging matters because it ensures every change appears in the redo, where the GoldenGate Extract process can find it. oracleoracle

First, check where you stand:

sql

SELECT log_mode, force_logging, supplemental_log_data_min

FROM   v$database;

 

SHOW PARAMETER enable_goldengate_replication

SHOW PARAMETER global_names

SHOW PARAMETER streams_pool_size

The target result is ARCHIVELOG, YES, and YES (or IMPLICIT). If the first is NOARCHIVELOG, you need a short outage window to fix it, which is a good thing to learn in week one and not on cutover night:

sql

-- Only if NOARCHIVELOG (requires a restart!)

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

ALTER DATABASE ARCHIVELOG;

ALTER DATABASE OPEN;

 

-- Then:

ALTER DATABASE FORCE LOGGING;

ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION=TRUE SCOPE=BOTH;

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

These statements match Oracle's DMS source-preparation steps. Two more items from Oracle's GoldenGate prep guidance are worth checking: set STREAMS_POOL_SIZE to at least 2 GB, and if GLOBAL_NAMES is true, change it to false. medium

sql

ALTER SYSTEM SET global_names=FALSE SCOPE=BOTH;

One 11.2-specific warning: for an 11.2 source, Oracle requires you to apply the mandatory 11.2.0.4 RDBMS patches listed in My Oracle Support note 1557031.1. I'm not quoting patch numbers because they change over time. Get the current list from that note. oracle

4.4 The GoldenGate admin user

DMS needs a dedicated replication user. The documented pattern is a GGADMIN user with a set of grants, finished by EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE('GGADMIN'). oracle

sql

CREATE USER ggadmin IDENTIFIED BY "<strong_password>"

  DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;

 

GRANT CONNECT, RESOURCE, CREATE SESSION TO ggadmin;

GRANT SELECT_CATALOG_ROLE TO ggadmin;

GRANT ALTER SYSTEM, ALTER USER TO ggadmin;

GRANT DATAPUMP_EXP_FULL_DATABASE, DATAPUMP_IMP_FULL_DATABASE TO ggadmin;

GRANT CREATE DATABASE LINK TO ggadmin;

 

EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE('GGADMIN');

A trap Arjun almost fell into: Oracle's published example grant list also includes roles like DV_GOLDENGATE_ADMIN and DV_GOLDENGATE_REDO_ACCESS. Those are Database Vault roles from 12c onward. To my knowledge they don't exist on 11.2.0.4, so I left them out. Confirm before you run the docs' example verbatim:

sql

SELECT role FROM dba_roles WHERE role LIKE 'DV_GOLDENGATE%';

Zero rows on an 11.2.0.4 source is expected. Also compare my grant list against the current DMS documentation for your exact setup, because privilege requirements change between releases.

4.5 Find the landmines: unsupported objects and missing keys

This is where PMs earn their salary, by turning technical risks into a backlog. GoldenGate applies row-level changes, so it needs a way to identify each row uniquely.

sql

-- Tables that Oracle's replication engines cannot capture

SELECT owner, table_name, reason

FROM   dba_streams_unsupported

WHERE  owner NOT IN ('SYS','SYSTEM','OUTLN','DBSNMP','SYSMAN','XDB','CTXSYS',

                     'MDSYS','ORDSYS','WMSYS','EXFSYS','APPQOSSYS')

ORDER  BY owner, table_name;

 

-- Tables with no primary key or unique constraint

SELECT t.owner, t.table_name

FROM   dba_tables t

WHERE  t.owner NOT IN ('SYS','SYSTEM','OUTLN','DBSNMP','SYSMAN','XDB','CTXSYS',

                       'MDSYS','ORDSYS','WMSYS','EXFSYS','APPQOSSYS')

AND    NOT EXISTS (SELECT 1

                   FROM   dba_constraints c

                   WHERE  c.owner = t.owner

                   AND    c.table_name = t.table_name

                   AND    c.constraint_type IN ('P','U'))

ORDER  BY t.owner, t.table_name;

For a table without a key, the options are adding a surrogate key, defining a substitute key in the replication configuration, or accepting the overhead of logging all columns. That's a design decision for the application team, not something to discover at 2 a.m.

The infographic's "Some objects and data types may not be supported" bullet sums this up.

Chapter 5: Pre-Migration Validation

(Workflow Step 3)

DMS has a built-in validation step. It checks configuration, privileges, and compatibility, and it surfaces issues before any data moves. Arjun treats it as a gate, and Priya treats it as a release criterion: no validation, no load.

The target gets its own sanity check:

sql

-- On the 19c target

SELECT name, open_mode FROM v$database;

SELECT banner FROM v$version;

 

SELECT parameter, value

FROM   nls_database_parameters

WHERE  parameter IN ('NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET');

The character sets should match the source. If they don't, that's a conversation to have now.

Chapter 6: The Two Engines: Data Pump, Then GoldenGate

(Workflow Steps 4–5)

The initial load uses Data Pump. The source stays online and available, and the duration depends on data size and network bandwidth, which is exactly why Section 4.1 mattered.

Data Pump captures a consistent point in time. That instant is an SCN, and you can see the same kind of marker on the source:

sql

SELECT current_scn FROM v$database;

Everything before that point arrives with the bulk load. Everything after it is picked up by GoldenGate, which mines the redo and replays the changes on the target. Together they close the gap.

Then comes the phase where the migration feels alive: replication lag shrinks, hovers near zero, and stays there. The infographic's note that "synchronization continues until lag = 0" is the definition of ready. Priya put that metric on the project dashboard, because it answers the business's question ("how close are we?") in a single line.

Chapter 7: The Cutover, Ten Minutes That Took Three Weeks to Prepare

(Workflow Step 6)

Cutover is a choreographed sequence:

1. Stop application writes.
2. Let replication drain until lag reaches zero and the final changes land.
3. Validate the target.
4. Switch the application to the OCI connection string.
5. Keep the source untouched, as your fallback.

Validation is where the "Wait, is it really identical?" question gets answered. Some queries to run on both sides and compare:

sql

-- Object inventory

SELECT owner, object_type, COUNT(*) AS cnt

FROM   dba_objects

WHERE  owner IN ('APP_OWNER')          -- your schemas

GROUP  BY owner, object_type

ORDER  BY 1, 2;

 

-- Invalid objects (expect this to be the same or lower on the target)

SELECT owner, object_type, COUNT(*) AS invalid_cnt

FROM   dba_objects

WHERE  status = 'INVALID'

AND    owner IN ('APP_OWNER')

GROUP  BY owner, object_type;

 

-- Sequences: the values the application will read next

SELECT sequence_owner, sequence_name, last_number

FROM   dba_sequences

WHERE  sequence_owner IN ('APP_OWNER')

ORDER  BY 1, 2;

For row counts, generate the comparison statements instead of hand-writing them:

sql

SELECT 'SELECT ''' || owner || '.' || table_name ||

       ''' AS tbl, COUNT(*) AS cnt FROM ' || owner || '.' || table_name || ';'

FROM   dba_tables

WHERE  owner = 'APP_OWNER';

Run the generated statements on both databases and diff the output. Exact row counts on large tables take time, so decide in advance which tables get full counts and which get sampled checks or checksums. Sequences deserve special attention because they keep advancing on the source after the snapshot. Confirm how your migration handles them and verify the values before the application resumes.

Cutover also needs a decision that goes beyond the technical work: the fallback plan. The infographic warns that "cutover and fallback require careful planning." Replication is one-way (source → target), so once the application writes to 19c, the old database is no longer in sync. Rolling back after that point means data loss unless you've built a reverse path. Priya made the call explicit: a go/no-go checkpoint, agreed with the business, with a defined point of no return.

Chapter 8: After the Migration

(Workflow Step 7)

The migration isn't done when the application connects. It's done when:

• Application and performance tests pass against 19c. The optimizer changed between 11g and 19c, so plan regressions are the most likely surprise.
• Backups are configured (OCI Backup) and a restore has been tested.
• Monitoring is in place, and someone owns the alerts.
• The old environment is retired on a schedule, not left running "just in case."

A quick way to spot regressions is to compare the top SQL by elapsed time before and after:

sql

SELECT * FROM (

  SELECT sql_id, executions,

         ROUND(elapsed_time/1000000,1) AS elapsed_s,

         ROUND(elapsed_time/NULLIF(executions,0)/1000,1) AS ms_per_exec,

         SUBSTR(sql_text,1,80) AS sql_text

  FROM   v$sql

  ORDER  BY elapsed_time DESC

) WHERE ROWNUM <= 20;

Chapter 9: What the Architect and PM Each Took Away

Arjun (architect):

• Online migration shrinks the cutover window, not the effort. The work moves earlier, into preparation.
• Bandwidth sets the pace of the initial load and of replication lag. Measure it before promising dates.
• Unsupported types, missing keys, and DDL activity during the load are where the surprises live. Query for them early.

Priya (product manager):

• The best migration metric is replication lag, because it maps directly to the business's downtime question.
• Treat validation gates as release criteria and the cutover fallback as a product decision.
• Communicate in outcomes ("we're 3 minutes behind and shrinking"), not in tool names.

Both would say the same thing about the infographic's last panel, "Key Considerations": the important points and the limitations are two sides of the same plan. Online migration minimizes the cutover window, and it demands bandwidth, compatibility checks, and cutover planning in return.