Upgrading Oracle Database 11g to 19c Using Transient Logical Standby

This article explains the procedure for upgrading an Oracle Database from 11g to 19c using the Transient Logical Standby method. In this approach, the target database is initially provisioned as a Physical Standby, converted to a Logical Standby, and subsequently upgraded to Oracle Database 19c—all without requiring a concurrent downtime or upgrade of the production Primary database.

Section 1: Solution Overview

1-1. What is a Transient Logical Standby?

In this workflow, a Physical Standby database is first created from the Primary. Once fully synchronized, the Physical Standby is transitioned into a Logical Standby. Rather than applying block-level physical changes, a Logical Standby utilizes SQL Apply and LogMiner to translate redo data into logical SQL statements and execute them against the standby database.

Once converted, the standby database can be started under the Oracle 19c ORACLE_HOME and upgraded while the 11g Primary database continues serving live production traffic. Finally, after verifying that all outstanding transactions have been received and applied, the Primary role is transitioned to the upgraded 19c database.

1-2. Why Logical Standby Instead of Physical Standby?

A Physical Standby must remain physically identical at the block level to the Primary. Consequently, running mixed Oracle major versions across a Primary and Physical Standby is subject to strict operational limits and incompatibilities.

Because Logical Standby applies redo at the logical layer via SQL Apply, it decouples the database engine versions. This architecture allows the Logical Standby to be upgraded to a higher release while the Primary remains operational on the legacy release.

1-3. Disabling Data Guard Broker

In this scenario, all steps are controlled manually. To prevent Data Guard Broker from interfering with role transitions and standby processes, disable the broker parameter on the participating databases:

ALTER SYSTEM SET DG_BROKER_START=FALSE SCOPE=BOTH;

The command above disables Data Guard Broker and persists the configuration in both memory (SGA) and the SPFILE.

Section 2: Preparing the Primary Database

2-1. Checking Primary Database Status

SELECT DBID,
       NAME,
       DB_UNIQUE_NAME,
       DATABASE_ROLE,
       LOG_MODE,
       OPEN_MODE,
       FORCE_LOGGING
FROM   V$DATABASE;

SELECT THREAD#,
       SEQUENCE#,
       STATUS,
       ENABLED
FROM   V$THREAD
ORDER BY THREAD#;

The first query displays the database ID, database name, unique name, role, archiving mode, open status, and force logging state.

The second query verifies the status of redo threads. In Oracle RAC environments, verify that all threads are open and enabled; in a single-instance database, only Thread 1 is typically present.

2-2. Enabling FORCE LOGGING

ALTER DATABASE FORCE LOGGING;

This command ensures that direct-path inserts and operations executed with the NOLOGGING attribute are logged in redo as much as possible. Enabling Force Logging is critical to prevent unrecoverable data corruptions on the standby.

Verify that Force Logging is active using the following query:

SELECT NAME,
       FORCE_LOGGING
FROM   V$DATABASE;

2-3. Enabling Supplemental Logging

SQL Apply requires supplemental logging in redo stream to uniquely identify changed rows during row-level operations. Supplemental logging must therefore be enabled on the Primary database.

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA
    (PRIMARY KEY, UNIQUE) COLUMNS;

ALTER SYSTEM ARCHIVE LOG CURRENT;

The first statement enables minimal supplemental logging.

The second statement adds primary key and unique key column logging to the redo stream, allowing Logical Standby to uniquely identify rows during UPDATE and DELETE operations.

The third statement switches and archives the current redo log, ensuring the supplemental logging changes take effect immediately in the redo stream.

Verify the supplemental logging status:

SELECT SUPPLEMENTAL_LOG_DATA_MIN,
       SUPPLEMENTAL_LOG_DATA_PK,
       SUPPLEMENTAL_LOG_DATA_UI
FROM   V$DATABASE;

SUPPLEMENTAL_LOG_DATA_MIN should return YES. Primary Key and Unique logging should also reflect active states (e.g., YES or IMPL).

2-4. Identifying Tables Without Primary Keys

Tables without primary keys or unique indexes can cause severe performance degradation, errors, or supplemental logging overhead during SQL Apply row identification for updates and deletes. Identify these tables using the following query:

SELECT dt.OWNER,
       dt.TABLE_NAME
FROM   DBA_TABLES dt
WHERE  NOT EXISTS
       (
           SELECT 1
           FROM   DBA_CONSTRAINTS dc
           WHERE  dc.OWNER = dt.OWNER
           AND    dc.TABLE_NAME = dt.TABLE_NAME
           AND    dc.CONSTRAINT_TYPE = 'P'
       )
AND    dt.OWNER NOT IN
       (
           'SYS',
           'SYSTEM',
           'SH',
           'MDSYS',
           'XDB'
       )
ORDER BY dt.OWNER,
         dt.TABLE_NAME;

In production environments, determine whether to create primary keys, define table-level supplemental logging, or exclude/handle these tables specifically during Logical Standby operations.

2-5. Checking for Unsupported Objects

Oracle provides a data dictionary view listing tables and datatypes that cannot be processed by SQL Apply. Query this view prior to proceeding:

SELECT OWNER,
       TABLE_NAME,
       COLUMN_NAME,
       DATA_TYPE
FROM   DBA_LOGSTDBY_UNSUPPORTED
ORDER BY OWNER,
         TABLE_NAME,
         COLUMN_NAME;

If this query returns rows, evaluate how changes to these unsupported structures will be handled or migrated to the 19c target.

Section 3: Building the LogMiner Dictionary

3-1. Executing DBMS_LOGSTDBY.BUILD

A Logical Standby requires a LogMiner dictionary recorded inside the redo logs to translate binary redo records into equivalent SQL statements. Build the dictionary on the Primary by running:

EXEC DBMS_LOGSTDBY.BUILD;

Executing this procedure records full structural dictionary metadata into the active redo log. This metadata will later be parsed by SQL Apply on the standby.

After the procedure completes, switch and archive the redo log multiple times to flush the dictionary redo out to the standby archive destination:

ALTER SYSTEM ARCHIVE LOG CURRENT;

ALTER SYSTEM ARCHIVE LOG CURRENT;

3-2. Verifying Archived Logs on Primary

SELECT THREAD#,
       SEQUENCE#,
       FIRST_TIME,
       NEXT_TIME,
       DEST_ID,
       APPLIED,
       STATUS
FROM   V$ARCHIVED_LOG
ORDER BY THREAD# DESC,
         SEQUENCE# DESC;

This query displays archived log statuses. The THREAD# and SEQUENCE# columns track log continuity, DEST_ID identifies the archive destination, and APPLIED confirms apply status on standby destinations.

3-3. Verifying Log Reception on Physical Standby

On the Standby database, confirm that the dictionary-bearing archived logs have arrived from the Primary:

SELECT THREAD#,
       SEQUENCE#,
       FIRST_TIME,
       NEXT_TIME,
       APPLIED,
       REGISTRAR,
       DICTIONARY_BEGIN,
       DICTIONARY_END
FROM   V$ARCHIVED_LOG
ORDER BY THREAD# DESC,
         SEQUENCE# DESC;

Ensure that an archived log appears with DICTIONARY_END='YES', indicating that the LogMiner dictionary boundary is fully registered on the standby.

Section 4: Converting Physical Standby to Logical Standby

4-1. Stopping Managed Recovery Process (MRP)

Before conversion, terminate physical recovery:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

This stops the Managed Recovery Process (MRP). If MRP is already stopped, Oracle may return ORA-16136: Managed Standby Recovery not active. This is safe to ignore.

4-2. Putting the Database in MOUNT State

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

SHUTDOWN IMMEDIATE performs a clean, consistent shutdown. STARTUP MOUNT starts the instance and mounts the control file without opening the datafiles.

4-3. Executing the Conversion

ALTER DATABASE RECOVER TO LOGICAL STANDBY vahiddb;

This command converts the control file and standby structures to a Logical Standby. The database name supplied (vahiddb) must match the DB_NAME of the Primary database.

Upon successful execution, Oracle returns:

Database altered.

4-4. Troubleshooting Hanging Conversion Commands

In some situations, the conversion command may appear to hang indefinitely. The Alert Log will typically report messages similar to:

Media Recovery Waiting for thread 1 sequence XX (in transit)

This condition generally indicates that the standby is waiting on redo logs containing the LogMiner dictionary endpoint that have not fully shipped or registered.

Do NOT cancel or abort the conversion command on the standby. Instead, open a separate session on the Primary database and execute:

EXEC DBMS_LOGSTDBY.BUILD;

ALTER SYSTEM ARCHIVE LOG CURRENT;

ALTER SYSTEM ARCHIVE LOG CURRENT;

If needed, repeat the log switch sequence once more. As soon as the missing dictionary redo arrives and processes on the standby, the conversion command will finish and return Database altered.

4-5. Opening the Logical Standby

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

ALTER DATABASE OPEN RESETLOGS;

Following conversion, the database must be opened with RESETLOGS to establish a new redo log stream and branch appropriate for its new role.

4-6. Starting SQL Apply

ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

This command enables SQL Apply with real-time apply. Redo received from the Primary is immediately mined, translated to SQL, and executed against the Logical Standby.

4-7. Verifying Logical Standby Status

SELECT DATABASE_ROLE,
       OPEN_MODE,
       GUARD_STATUS
FROM   V$DATABASE;

SELECT *
FROM   V$LOGSTDBY_STATE;

SELECT TYPE,
       STATUS_CODE,
       STATUS
FROM   V$LOGSTDBY_PROCESS
ORDER BY TYPE;

The first query verifies that DATABASE_ROLE is now LOGICAL STANDBY.

The second query displays SQL Apply operational state, and the third inspects background LogMiner and SQL Apply worker/coordinator processes.

Section 5: Upgrading Logical Standby to Oracle 19c

At this stage, the 11g Primary remains online and fully operational while only the Logical Standby is stopped for the software upgrade.

5-1. Stopping SQL Apply

ALTER DATABASE STOP LOGICAL STANDBY APPLY;

This suspends SQL Apply, preparing the database for a clean shutdown and ORACLE_HOME switch.

5-2. Shutting Down the Database

SHUTDOWN IMMEDIATE;

This shuts down the instance cleanly. Once Database closed, Database dismounted, and ORACLE instance shut down appear, you can proceed to reconfigure environment variables for the 19c home.

5-3. Configuring the 19c ORACLE_HOME Environment

export ORACLE_HOME=/u01/app/oracle/product/19c/dbhome
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=vahiddbg

sqlplus -v

The first three lines point your session environment to the 19c binaries and set the standby SID. Running sqlplus -v confirms the session resolves the 19c client version.

5-4. Copying the Password File

cp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwvahiddbg \
   /u01/app/oracle/product/19c/dbhome/dbs/

The remote password file authenticates privileged connections (such as SYSDBA) and Data Guard background redo transport. Ensure a valid password file is placed in the new 19c $ORACLE_HOME/dbs directory.

5-5. Migrating the SPFILE / PFILE

During instance startup, Oracle searches $ORACLE_HOME/dbs sequentially for spfile<SID>.ora and then init<SID>.ora.

Copy the legacy SPFILE to the 19c home directory:

cp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfilevahiddbg.ora \
   /u01/app/oracle/product/19c/dbhome/dbs/

Verify that the file is present and permissions are correct:

ls -l $ORACLE_HOME/dbs/spfile${ORACLE_SID}.ora

If utilizing a PFILE that references the SPFILE, create or modify it:

vi $ORACLE_HOME/dbs/init${ORACLE_SID}.ora

Ensure the parameter points directly to the real SPFILE path:

SPFILE='/u01/app/oracle/product/19c/dbhome/dbs/spfilevahiddbg.ora'

5-6. Starting Database in UPGRADE Mode

sqlplus / as sysdba

STARTUP UPGRADE;

The STARTUP UPGRADE statement mounts and opens the database in a restricted mode dedicated to running upgrade scripts. This command must be executed using the Oracle 19c binaries.

5-7. Running the Database Upgrade

From the OS shell, execute the parallel upgrade utility (dbupgrade):

cd $ORACLE_HOME/bin

./dbupgrade -n 4

The -n 4 flag sets the degree of parallelism. Adjust this value according to available host CPU cores, memory limits, and overall system capacity.

5-8. Validating Registry and Compiling Invalid Objects

sqlplus / as sysdba

STARTUP;

SELECT COMP_ID,
       COMP_NAME,
       VERSION,
       STATUS
FROM   DBA_REGISTRY
ORDER BY COMP_ID;

@?/rdbms/admin/utlrp.sql

STARTUP opens the newly upgraded database in normal READ WRITE mode.

Querying DBA_REGISTRY verifies that all core components reflect the 19c release and report VALID status.

The utlrp.sql script recompiles remaining invalid PL/SQL packages, views, and types.

5-9. Checking Database Role and Version

SELECT BANNER_FULL
FROM   V$VERSION;

SELECT DATABASE_ROLE,
       OPEN_MODE,
       GUARD_STATUS
FROM   V$DATABASE;

Verify that V$VERSION shows the target 19c release and that V$DATABASE still maintains the LOGICAL STANDBY role.

5-10. Resuming SQL Apply

ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

With the upgrade completed and database integrity validated, restart SQL Apply to resume receiving and applying transactions generated by the 11g Primary.

5-11. Monitoring Apply Lag and Statistics

SELECT NAME,
       VALUE,
       UNIT
FROM   V$LOGSTDBY_STATS
WHERE  NAME IN
       (
           'event',
           'latest checkpoint',
           'coordinator state',
           'completed transactions',
           'highest scn recorded'
       )
ORDER BY NAME;

Use these metrics to monitor apply progress, check coordinator states, and verify that the standby is catching up with the Primary.

Section 6: Final Role Transition to the 19c Database

6-1. Stopping User and Application Workloads on Primary

Halt all write activity, batch processes, and application tiers targeting the 11g Primary. The specific procedure depends on application architecture and operational design.

6-2. Archiving the Final Redo on Primary

ALTER SYSTEM ARCHIVE LOG CURRENT;

This command archives the active redo log, ensuring all committed Primary transactions are flushed and transmitted to the standby destination.

On the Logical Standby, confirm that all final logs have arrived and registered:

SELECT THREAD#,
       SEQUENCE#,
       APPLIED,
       DICTIONARY_BEGIN,
       DICTIONARY_END
FROM   V$ARCHIVED_LOG
ORDER BY THREAD# DESC,
         SEQUENCE# DESC;

6-3. Verifying SQL Apply Completion

SELECT TYPE,
       STATUS_CODE,
       STATUS
FROM   V$LOGSTDBY_PROCESS
ORDER BY TYPE;

SELECT *
FROM   V$LOGSTDBY_STATE;

Before activating the standby as the new Primary, verify that all received logs have been applied and no transactions remain pending in the SQL Apply pipeline.

6-4. Activating Logical Standby as the New Primary

ALTER DATABASE STOP LOGICAL STANDBY APPLY;

ALTER DATABASE ACTIVATE LOGICAL STANDBY DATABASE FINISH APPLY;

The first statement halts SQL Apply.

The second statement activates the Logical Standby into a Primary database. The FINISH APPLY clause instructs the engine to apply any remaining redo in the pipeline prior to completing role transition.

6-5. Disabling Database Guard

ALTER DATABASE GUARD NONE;

Logical Standby databases enforce Database Guard to prevent direct DML modifications. Executing this command sets Guard to NONE, allowing standard read-write operations on the newly promoted Primary.

6-6. Opening Database (If Required)

ALTER DATABASE OPEN;

If the database is not already open after activation, open it with this statement. Always verify database open status first.

6-7. Verifying Final Database Role

SELECT DBID,
       NAME,
       DB_UNIQUE_NAME,
       DATABASE_ROLE,
       OPEN_MODE,
       LOG_MODE,
       FORCE_LOGGING,
       GUARD_STATUS
FROM   V$DATABASE;

Expected verification output:

• DATABASE_ROLE must report PRIMARY.
• OPEN_MODE must report READ WRITE.
• GUARD_STATUS must report NONE.
• FORCE_LOGGING should reflect your production baseline standard.

Section 7: Post-Transition Validation

7-1. Verifying Services and Listener Status

lsnrctl status

srvctl status database -d vahiddbg

lsnrctl status inspects listener health and registered database services. The srvctl command applies to environments managed under Oracle Restart or Oracle Clusterware.

7-2. Executing a Test Transaction

Perform this test using a non-system application account:

CREATE TABLE migration_test
(
    test_id      NUMBER PRIMARY KEY,
    test_time    TIMESTAMP DEFAULT SYSTIMESTAMP,
    description  VARCHAR2(200)
);

INSERT INTO migration_test
(
    test_id,
    description
)
VALUES
(
    1,
    'Post-migration validation'
);

COMMIT;

SELECT *
FROM   migration_test
WHERE  test_id = 1;

Run this verification test only after role transition completes on the new 19c Primary. It validates DDL execution, DML operations, and transaction commits on the upgraded engine.

Section 8: Logical Standby Limitations and Considerations

8-1. Unsupported Data Types and Objects

Certain data types and schemas cannot be processed by SQL Apply and require separate remediation plans. Critical items to review include:

• BFILE
• ROWID and UROWID
• Certain User-Defined Types (UDTs)
• Specific Advanced Queuing (AQ) configurations
• Certain schema DDL operations and external-file dependencies

8-2. Physical Storage and File Operations

Physical datafile operations—such as datafile resizing, adding new datafiles, tablespace structural changes, or directory alterations—are not automatically propagated via SQL Apply as logical DML.

These storage changes must be planned and applied manually to the target standby as required.

8-3. NOLOGGING Operations

NOLOGGING operations bypass redo generation, causing silent data gaps on SQL Apply. Enforcing FORCE LOGGING and auditing against NOLOGGING activity on the Primary is mandatory.

8-4. Tables Without Primary Keys

Tables lacking primary keys or unique identifiers increase SQL Apply overhead and can cause severe apply latency. Implement explicit primary keys, add supplemental logging, or manage apply rules accordingly.

8-5. Role Responsibilities Following Transition

Once activated as Primary, this database is no longer a temporary standby; it serves as your production system. Reconfigure backup policies (RMAN), monitoring agents, scheduled DBMS_SCHEDULER / cron jobs, and database services to align with its new Primary role.

Section 9: Final Checklist

1. Verify Primary database version and prerequisites.
2. Enable Force Logging on Primary.
3. Enable Supplemental Logging (Minimal + PK/UI).
4. Identify tables lacking primary keys.
5. Audit unsupported database objects via DBA_LOGSTDBY_UNSUPPORTED.
6. Execute DBMS_LOGSTDBY.BUILD on Primary.
7. Switch and archive current redo log multiple times.
8. Cancel MRP on Physical Standby.
9. Perform logical standby conversion strictly in MOUNT state.
10. Open Logical Standby with RESETLOGS.
11. Start SQL Apply (IMMEDIATE).
12. Stop SQL Apply and cleanly shut down the standby database.
13. Switch environment variables to Oracle 19c ORACLE_HOME.
14. Verify password file and SPFILE / PFILE in 19c dbs.
15. Start instance using STARTUP UPGRADE.
16. Execute dbupgrade -n <parallel_degree>.
17. Verify DBA_REGISTRY and execute utlrp.sql.
18. Resume SQL Apply under 19c.
19. Halt write traffic on the legacy 11g Primary.
20. Archive final redo log on Primary.
21. Verify all redo has arrived and applied on the standby.
22. Execute ACTIVATE LOGICAL STANDBY DATABASE FINISH APPLY.
23. Disable Database Guard (ALTER DATABASE GUARD NONE).
24. Perform final role, open mode, and service validation checks.

Summary

The Transient Logical Standby methodology provides an effective path for upgrading Oracle Database 11g to 19c, ensuring the production Primary remains online for the vast majority of the upgrade cycle.

Key operational prerequisites include building a clean LogMiner dictionary, ensuring all dictionary redo is transmitted and registered, performing the conversion strictly in MOUNT mode, and carefully monitoring SQL Apply. If the conversion command encounters a wait event, re-running DBMS_LOGSTDBY.BUILD and archiving logs from the Primary reliably resolves pending dictionary boundaries.

By monitoring apply lags, resolving schema incompatibilities beforehand, and executing a controlled switchover, DBAs can achieve minimal downtime and transition to Oracle Database 19c with high confidence and minimal operational risk.