oraclednaThis post demonstrates the create logical standby database in Oracle 19c.
The logical standby is maintained by applying data changes as SQL statements via SQL Apply, which extracts these changes from the redo data received from the primary.
Pre-requisites:
===========
1. Create a physical standby database.
2. Before converting the physical standby database to logical standby database, see the below document for a complete list of data type and storage type considerations.
https://docs.oracle.com/en/database/oracle/oracle-database/19/sbydb/creating-oracle-data-guard-logical-standby.html#GUID-3666CA35-D993-44B6-8D70-A2B8B9EC8B2E
3. Ensure Table Rows in the Primary Database Can Be Uniquely Identified.
This is because, Oracle uses primary-key or unique-constraint/index supplemental logging to logically identify a modified row in the logical standby database.
In primary database, run the below query to display a list of tables that SQL Apply may not be able to uniquely identify.
SQL> SELECT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_NOT_UNIQUE WHERE (OWNER, TABLE_NAME) NOT IN (SELECT DISTINCT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED) AND BAD_COLUMN = ‘Y’;
no rows selected
1. Ensure that Physical Standby is in Sync with Primary Database.
Run the below query in Physical Standby database to check the archive log sync.
SQL> SELECT ARCH.THREAD# “Thread”, ARCH.SEQUENCE# “Last Sequence Received”, APPL.SEQUENCE# “Last Sequence Applied” FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;
Thread Last Sequence Received Last Sequence Applied
———- ———————- ———————
1 78 78
2. Stop Redo Apply (MRP) on the Physical Standby Database.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
3. Set Parameters for Logical Standby in Primary database.
3.1 Change VALID_FOR in LOG_ARCHIVE_DEST_1 on Primary to (ONLINE_LOGFILES,ALL_ROLES)
Change the VALID_FOR attribute in the original LOG_ARCHIVE_DEST_1 destination to archive redo data only from the online redo log and not from the standby redo log.
SQL> show parameter LOG_ARCHIVE_DEST_1
NAME TYPE VALUE
———————————— ———– ——————————
log_archive_dest_1 string LOCATION=/u01/arch/prod VALID_
FOR=(ALL_LOGFILES,ALL_ROLES) D
B_UNIQUE_NAME=prod
SQL> alter system set LOG_ARCHIVE_DEST_1= ‘LOCATION=/u01/arch/prod VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prod’ scope=both;
System altered.
SQL> show parameter LOG_ARCHIVE_DEST_1
NAME TYPE VALUE
———————————— ———– ——————————
log_archive_dest_1 string LOCATION=/u01/arch/prod VALID_
FOR=(ONLINE_LOGFILES,ALL_ROLES
) DB_UNIQUE_NAME=prod
3.2 set the LOG_ARCHIVE_DEST_3 destination on the primary database and for logs which will received on Standby from Primary.
This parameter will be in effective only when the primary database is transitioned to the logical standby role.
SQL> alter system set LOG_ARCHIVE_DEST_3= ‘LOCATION=/u01/arch/log_stby/ VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=prod’ scope=both;
System altered.
SQL> alter system set LOG_ARCHIVE_DEST_STATE_3=ENABLE scope=both;
System altered.
4. Build a Dictionary in the Redo Data on Primary Database.
Building a LogMiner dictionary is mandatory, so that the LogMiner component of SQL Apply can properly interpret changes it sees in the redo. This will enable the supplemental logging automatically set up to log primary key and unique-constraint/index columns.
To build the LogMiner dictionary, execute the below statement. The DBMS_LOGSTDBY.BUILD procedure waits for all existing transactions to complete.
SQL> EXECUTE DBMS_LOGSTDBY.BUILD;
PL/SQL procedure successfully completed.
From Primary alert log:
=================
2026-03-05T00:54:51.014293+05:30
alter database add supplemental log data (primary key, unique index) columns
2026-03-05T00:54:51.065128+05:30
SUPLOG: Previous supplemental logging attributes at scn = 2341923
SUPLOG: minimal = OFF, primary key = OFF
SUPLOG: unique = OFF, foreign key = OFF, all column = OFF
SUPLOG: procedural replication = OFF, subset db replication = OFF
2026-03-05T00:54:51.163148+05:30
SUPLOG: New supplemental logging attributes at scn = 2341923
SUPLOG: minimal = ON, primary key = ON
SUPLOG: unique = ON, foreign key = OFF, all column = OFF
SUPLOG: procedural replication = OFF, subset db replication = OFF
SUPPLEMENTAL LOG: Waiting for completion of transactions started at or before scn 2341923 (0x000000000023bc23)
SUPPLEMENTAL LOG: All transactions started at or before scn 2341923 (0x000000000023bc23) have completed
Completed: alter database add supplemental log data (primary key, unique index) columns
alter database add supplemental log data for procedural replication
2026-03-05T00:54:51.196423+05:30
SUPLOG: Previous supplemental logging attributes at scn = 2341934
SUPLOG: minimal = ON, primary key = ON
SUPLOG: unique = ON, foreign key = OFF, all column = OFF
SUPLOG: procedural replication = OFF, subset db replication = OFF
2026-03-05T00:54:51.196860+05:30
SUPLOG: New supplemental logging attributes at scn = 2341934
SUPLOG: minimal = ON, primary key = ON
SUPLOG: unique = ON, foreign key = OFF, all column = OFF
SUPLOG: procedural replication = ON, subset db replication = OFF
Completed: alter database add supplemental log data for procedural replication
Thu Mar 05 00:54:51 2026
Logminer Bld: Build started
2026-03-05T00:54:51.269958+05:30
ALTER SYSTEM SWITCH ALL LOGFILE start (prod)
2026-03-05T00:54:51.304773+05:30
Thread 1 advanced to log sequence 80 (LGWR switch)
Current log# 3 seq# 80 mem# 0: /u01/app/oracle/oradata/PROD/redo03.log
2026-03-05T00:54:51.304859+05:30
ALTER SYSTEM SWITCH ALL LOGFILE complete (prod)
2026-03-05T00:54:51.344150+05:30
Thu Mar 05 00:54:51 2026
Logminer Bld: Lockdown Complete. DB_TXN_SCN is 0 2341945 LockdownSCN is 2341945
2026-03-05T00:54:51.620756+05:30
TT02 (PID:3145): SRL selected for T-1.S-80 for LAD:2
2026-03-05T00:54:51.827520+05:30
ARC1 (PID:3139): Archived Log entry 138 added for T-1.S-79 ID 0x24475078 LAD:1
2026-03-05T00:54:51.851286+05:30
Resize operation completed for file# 1, old size 921600K, new size 931840K
2026-03-05T00:54:52.169900+05:30
Resize operation completed for file# 1, old size 931840K, new size 942080K
2026-03-05T00:54:52.622072+05:30
ALTER SYSTEM ARCHIVE LOG
2026-03-05T00:54:52.663766+05:30
Thread 1 advanced to log sequence 81 (LGWR switch)
Current log# 1 seq# 81 mem# 0: /u01/app/oracle/oradata/PROD/redo01.log
2026-03-05T00:54:52.712405+05:30
NET (PID:4304): Archived Log entry 139 added for T-1.S-80 ID 0x24475078 LAD:1
Thu Mar 05 00:54:52 2026
Logminer Bld: Done
LOGMINER: Dictionary Build: Waiting for completion of transactions started at or before scn 2342475 (0x000000000023be4b)
LOGMINER: Dictionary Build: All transactions started at or before scn 2342475 (0x000000000023be4b) have completed
5. Convert the Physical Standby Database to a Logical Standby Database.
In below SQL, specify the new db_name for logical standby database other than the db_name of primary database. If database was opened with spfile, the nelow SQL update the SPFILE with new db_name.
SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY <db_name>;
SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY logstdby;
Database altered.
From Standby alert log:
=================
2026-03-05T01:07:11.416494+05:30
ALTER DATABASE RECOVER TO LOGICAL STANDBY logstdby
2026-03-05T01:07:11.416824+05:30
Media Recovery Start: Managed Standby Recovery (stdby)
2026-03-05T01:07:11.418994+05:30
Serial Media Recovery started
NET (PID:4240): Managed Standby Recovery not using Real Time Apply
stopping change tracking
2026-03-05T01:07:11.522669+05:30
NET (PID:4240): Media Recovery Log /u01/arch/stdby/79_1_1196677442.arc
2026-03-05T01:07:11.669361+05:30
NET (PID:4240): Media Recovery Log /u01/arch/stdby/80_1_1196677442.arc
Resize operation completed for file# 1, old size 921600K, new size 931840K
Resize operation completed for file# 1, old size 931840K, new size 942080K
2026-03-05T01:07:11.917822+05:30
NET (PID:4240): Media Recovery Log /u01/arch/stdby/81_1_1196677442.arc
2026-03-05T01:07:12.472000+05:30
Incomplete Recovery applied until change 2342487 time 03/05/2026 00:54:52
2026-03-05T01:07:12.483939+05:30
Media Recovery Complete (stdby)
stopping change tracking
NET (PID:4240): Killing 3 processes (PIDS:4287,4300,4291) (all RFS) in order to disallow current and future RFS connections. Requested by OS process 4240
2026-03-05T01:07:12.545859+05:30
Process termination requested for pid 4287 [source = rdbms], [info = 2] [request issued by pid: 4240, uid: 543232]
2026-03-05T01:07:12.546008+05:30
Process termination requested for pid 4300 [source = rdbms], [info = 2] [request issued by pid: 4240, uid: 543232]
2026-03-05T01:07:12.546163+05:30
Process termination requested for pid 4291 [source = rdbms], [info = 2] [request issued by pid: 4240, uid: 543232]
2026-03-05T01:07:14.547270+05:30
NET (PID:4240): Begin: SRL archival
NET (PID:4240): End: SRL archival
RESETLOGS after complete recovery through change 2342487
Resetting resetlogs activation ID 608653432 (0x24475078)
Online log /u01/app/oracle/oradata/STDBY/redo01.log: Thread 1 Group 1 was previously cleared
Online log /u01/app/oracle/oradata/STDBY/redo02.log: Thread 1 Group 2 was previously cleared
Online log /u01/app/oracle/oradata/STDBY/redo03.log: Thread 1 Group 3 was previously cleared
Standby became primary SCN: 2342485
2026-03-05T01:07:14.624604+05:30
Setting recovery target incarnation to 4
2026-03-05T01:07:14.646768+05:30
NET (PID:4240): Database role changed from PHYSICAL STANDBY to LOGICAL STANDBY [kcvs.c:2760]
NET (PID:4240): Redo network throttle feature is disabled at mount time
RECOVER TO LOGICAL STANDBY: Complete – Database shutdown required after NID finishes
*** DBNEWID utility started ***
DBID will be changed from 608515774 to new DBID of 745585282 for database PROD
DBNAME will be changed from PROD to new DBNAME of LOGSTDBY
Starting datafile conversion
Datafile conversion complete
Database name changed to LOGSTDBY.
Modify parameter file and generate a new password file before restarting.
Database ID for database LOGSTDBY changed to 745585282.
All previous backups and archived redo logs for this database are unusable.
Database has been shutdown, open with RESETLOGS option.
Succesfully changed database name and ID.
*** DBNEWID utility finished succesfully ***
Shutting down archive processes
2026-03-05T01:07:14.745132+05:30
TT00 (PID:4236): Gap Manager exiting
2026-03-05T01:07:15.747402+05:30
Archiving is disabled
2026-03-05T01:07:15.750511+05:30
ARC3 (PID:4246): ARCH shutting down
ARC3 (PID:4246): Archival stopped
2026-03-05T01:07:15.755151+05:30
ARC2 (PID:4244): ARCH shutting down
ARC2 (PID:4244): Archival stopped
2026-03-05T01:07:15.758365+05:30
ARC1 (PID:4241): ARCH shutting down
ARC1 (PID:4241): Archival stopped
2026-03-05T01:07:15.761498+05:30
ARC0 (PID:4234): ARCH shutting down
ARC0 (PID:4234): Archival stopped
2026-03-05T01:07:16.789735+05:30
Completed: ALTER DATABASE RECOVER TO LOGICAL STANDBY logstdby
6. Shutdown and Startup Logical Standby Database in Mount Stage.
SQL> SHUTDOWN;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> STARTUP MOUNT;
ORACLE instance started.
Total System Global Area 2113927120 bytes
Fixed Size 8898512 bytes
Variable Size 486539264 bytes
Database Buffers 1610612736 bytes
Redo Buffers 7876608 bytes
Database mounted.
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
———- ———- ——————
LOGSTDBY MOUNTED LOGICAL STANDBY
7. Modify Initialization Parameter on Logical Standby Database.
SQL> show parameter LOG_ARCHIVE_DEST_1
NAME TYPE VALUE
———————————— ———– ——————————
log_archive_dest_1 string LOCATION=/u01/arch/stdby VALID
_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=stdby
SQL> alter system set LOG_ARCHIVE_DEST_1= ‘LOCATION=/u01/arch/stdby VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stdby’ scope=both;
System altered.
SQL> show parameter LOG_ARCHIVE_DEST_1
NAME TYPE VALUE
———————————— ———– ——————————
log_archive_dest_1 string LOCATION=/u01/arch/stdby VALID
_FOR=(ONLINE_LOGFILES,ALL_ROLE
S) DB_UNIQUE_NAME=stdby
—
SQL> show parameter LOG_ARCHIVE_DEST_2
NAME TYPE VALUE
———————————— ———– ——————————
log_archive_dest_2 string SERVICE=PROD LGWR ASYNC VALID_
FOR=(ONLINE_LOGFILES,PRIMARY_R
OLE) DB_UNIQUE_NAME=prod
—
SQL> alter system set LOG_ARCHIVE_DEST_3= ‘LOCATION=/u01/arch/log_stby/ VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=stdby’ scope=both;
System altered.
SQL> alter system set LOG_ARCHIVE_DEST_STATE_3=ENABLE scope=both;
System altered.
8. Open the Logical Standby Database.
SQL> ALTER DATABASE OPEN RESETLOGS;
Database altered.
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
———- ———- ——————
LOGSTDBY READ WRITE LOGICAL STANDBY
From Logical standby alert log:
=======================
ALTER DATABASE OPEN RESETLOGS
2026-03-05T01:31:45.118522+05:30
RESETLOGS after complete recovery through change 2342488
2026-03-05T01:31:48.791859+05:30
Setting recovery target incarnation to 5
2026-03-05T01:31:48.807933+05:30
Ping without log force is disabled:
instance mounted in exclusive mode.
Endian type of dictionary set to little
2026-03-05T01:31:48.834327+05:30
Assigning activation ID 745573540 (0x2c708ca4)
Redo log for group 1, sequence 1 is not located on DAX storage
Thread 1 opened at log sequence 1
Current log# 1 seq# 1 mem# 0: /u01/app/oracle/oradata/STDBY/redo01.log
Successful open of redo thread 1
2026-03-05T01:31:48.907999+05:30
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
stopping change tracking
Undo initialization recovery: err:0 start: 8315527 end: 8315546 diff: 19 ms (0.0 seconds)
[24621] Successfully onlined Undo Tablespace 2.
Undo initialization online undo segments: err:0 start: 8315546 end: 8315599 diff: 53 ms (0.1 seconds)
Undo initialization finished serial:0 start:8315527 end:8315605 diff:78 ms (0.1 seconds)
Dictionary check beginning
Dictionary check complete
Verifying minimum file header compatibility for tablespace encryption..
Verifying file header compatibility for tablespace encryption completed for pdb 0
Database Characterset is AL32UTF8
No Resource Manager plan active
**********************************************************
WARNING: Files may exists in db_recovery_file_dest
that are not known to the database. Use the RMAN command
CATALOG RECOVERY AREA to re-catalog any such files.
If files cannot be cataloged, then manually delete them
using OS command.
One of the following events caused this:
1. A backup controlfile was restored.
2. A standby controlfile was restored.
3. The controlfile was re-created.
4. db_recovery_file_dest had previously been enabled and
then disabled.
**********************************************************
joxcsys_required_dirobj_exists: directory object exists with required path /u01/app/oracle/product/19.0.0/db_1/javavm/admin/, pid 24621 cid 0
replication_dependency_tracking turned off (no async multimaster replication found)
2026-03-05T01:31:49.963444+05:30
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation skipped — detected logical instantiation
LOGSTDBY: Validation complete
Starting background process AQPC
2026-03-05T01:31:50.076781+05:30
AQPC started with pid=42, OS id=26201
2026-03-05T01:31:50.257335+05:30
LOGSTDBY: skipping logfile pre-registration due to in-progress instantiation
Global Name changed to LOGSTDBY
2026-03-05T01:31:51.038788+05:30
Starting background process CJQ0
2026-03-05T01:31:51.175262+05:30
CJQ0 started with pid=46, OS id=26216
2026-03-05T01:31:51.802184+05:30
rfs (PID:26222): Primary database is in MAXIMUM PERFORMANCE mode
2026-03-05T01:31:52.005183+05:30
RFS LogMiner: RFS id [26226] assigned as thread [1] PING handler
2026-03-05T01:31:52.308708+05:30
rfs (PID:26222): Selected LNO:5 for T-1.S-82 dbid 608515774 branch 1196677442
2026-03-05T01:31:53.505356+05:30
Control autobackup written to DISK device
handle ‘/u01/arch/stdby/STDBY/autobackup/2026_03_05/o1_mf_s_1227058312_ntk3xk68_.bkp’
Completed: ALTER DATABASE OPEN RESETLOGS
9. Start Logical Apply on Standby.
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
Database altered.
From Logical standby alert log:
=======================
ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE
2026-03-05T01:34:11.804202+05:30
LOGSTDBY: Creating new session for dbid 608515774 starting at scn 0x0000000000000000
2026-03-05T01:34:11.815841+05:30
LOGSTDBY: Created session of id 1
2026-03-05T01:34:11.856616+05:30
LOGSTDBY: Attempting to pre-register dictionary build logfiles
LOGMINER: session# 1 Error 308 encountered, failed to read logfile 80_1_1196677442.arc
LOGMINER: Encountered error 1291 while adding logfile 80_1_1196677442.arc to session 1
LOGSTDBY: Unable to register recovery logfiles, will resend
2026-03-05T01:34:11.889258+05:30
ALTER DATABASE START LOGICAL STANDBY APPLY (stdby)
with optional part
IMMEDIATE
Attempt to start background Logical Standby process
Starting background process LSP0
2026-03-05T01:34:11.928976+05:30
LSP0 started with pid=44, OS id=27483
Completed: ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE
2026-03-05T01:34:12.608388+05:30
LOGMINER: Parameters summary for session# = 1
LOGMINER: Number of processes = 3, Transaction Chunk Size = 201
LOGMINER: Memory Size = 100M, Checkpoint interval = 500M
LOGMINER: SpillScn 0, ResetLogScn 0
10. Test by creating user in primary and check in logical standby database.
On Primary:==
SQL> select username,created from dba_users where username=’RAM’;
no rows selected
SQL> create user ram identified by ram;
User created.
SQL> grant create session to ram;
Grant succeeded.
SQL> select username,created from dba_users where username=’RAM’;
USERNAME CREATED
———————————————-
RAM 05-MAR-26
On Logical Standby:==
SQL> select username,created from dba_users where username=’RAM’;
USERNAME CREATED
———————————————-
RAM 05-MAR-26
From Logical standby alert log:
===============================
RFS LogMiner: Registered logfile [/u01/arch/log_stby/82_1_1196677442.arc] to LogMiner session id [1]
2026-03-05T01:36:47.251595+05:30
rfs (PID:28553): Primary database is in MAXIMUM PERFORMANCE mode
2026-03-05T01:36:47.308372+05:30
rfs (PID:28553): Selected LNO:4 for T-1.S-83 dbid 608515774 branch 1196677442
2026-03-05T01:36:47.741754+05:30
LOGMINER: Alternate logfile found. Transition to mining archived logfile for session 1 thread 1 sequence 82, /u01/arch/log_stby/82_1_1196677442.arc
2026-03-05T01:36:47.743425+05:30
LOGMINER: End mining logfile for session 1 thread 1 sequence 82, /u01/arch/log_stby/82_1_1196677442.arc
2026-03-05T01:36:47.743678+05:30
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 83, /u01/app/oracle/oradata/STDBY/redo04.log
2026-03-05T01:36:49.373609+05:30
rfs (PID:28553): Selected LNO:5 for T-1.S-84 dbid 608515774 branch 1196677442
2026-03-05T01:36:49.386179+05:30
RFS LogMiner: Registered logfile [/u01/arch/log_stby/83_1_1196677442.arc] to LogMiner session id [1]
2026-03-05T01:36:49.889586+05:30
LOGMINER: Alternate logfile found. Transition to mining archived logfile for session 1 thread 1 sequence 83, /u01/arch/log_stby/83_1_1196677442.arc
2026-03-05T01:36:49.890659+05:30
LOGMINER: End mining logfile for session 1 thread 1 sequence 83, /u01/arch/log_stby/83_1_1196677442.arc
2026-03-05T01:36:49.890934+05:30
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 84, /u01/app/oracle/oradata/STDBY/redo05.log
2026-03-05T01:39:11.717597+05:30
rfs (PID:28553): Selected LNO:4 for T-1.S-85 dbid 608515774 branch 1196677442
2026-03-05T01:39:11.731778+05:30
RFS LogMiner: Registered logfile [/u01/arch/log_stby/84_1_1196677442.arc] to LogMiner session id [1]
2026-03-05T01:39:12.233664+05:30
LOGMINER: Alternate logfile found. Transition to mining archived logfile for session 1 thread 1 sequence 84, /u01/arch/log_stby/84_1_1196677442.arc
2026-03-05T01:39:12.234584+05:30
LOGMINER: End mining logfile for session 1 thread 1 sequence 84, /u01/arch/log_stby/84_1_1196677442.arc
2026-03-05T01:39:12.234950+05:30
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 85, /u01/app/oracle/oradata/STDBY/redo04.log
Disclaimer:
Please note the above information is only for educational purpose and practised in personal test database only. Always test in test database before implementing in production database. The pre-requisites and ways of implementing may vary from one environment to another. Hence, not providing guarantee that it will work in your environment.
