- Introduction: Oracle Logical Standby database is a type of standby database created and maintained using Oracle Data Guard (ODG), where redo data from the primary database is transformed into SQL statements and applied logically on the standby database. A Logical Standby differs from a Physical Standby by applying redo through SQL apply rather than block-level recovery. This approach keeps the standby database open in read/write mode while it remains in sync with the primary. Logical Standby databases are particularly well suited for environments that require both robust data protection and effective workload offloading, without adding performance overhead to the primary system. In this article, we will configure the Logical Standby.
- Prerequisites:
- Oracle Data Guard (Physical Standby) should be configured. For Physical Standby Setup Click here.
- Verify the unsupported objects for Logical Standby.
- Tables must have a primary key or unique constraint.
- Environment:
- Now proceed to convert Physical Standby into Logical Standby Database activity step by step:
| Server | Primary | Standby |
|---|---|---|
| Hostname | dcdb.oraeasy.com | drdb.oraeasy.com |
| OS | OEL 9 | OEL 9 |
| SID | ORADBDC | ORADBDR |
| Service Name | ORADBDC | ORADBDR |
1. Check the sync between Primary and Standby.
==> Primary:
SQL> select NAME, DB_UNIQUE_NAME, DATABASE_ROLE, OPEN_MODE from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE
--------- ------------------------------ ---------------- --------------------
ORADB oradbdc PRIMARY READ WRITE
SQL> set lines 200 pages 300
SQL> alter session set nls_date_format= 'DD-MON-YYYY HH24:MI:SS';
SQL> select d.db_unique_name,d.database_role, a.thread#, b.last_seq, a.applied_seq, a.last_app_timestamp, b.last_seq - a.applied_seq ARC_DIFF
FROM
(select thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp from gv$archived_log where applied='YES' group by thread#) a,
(select thread#, MAX(sequence#) last_seq from gv$archived_log group by thread#) b,
(select db_unique_name,database_role from v$database) d where a.thread#=b.thread#;
Session altered.
DB_UNIQUE_NAME DATABASE_ROLE THREAD# LAST_SEQ APPLIED_SEQ LAST_APP_TIMESTAMP ARC_DIFF
------------------------------ ---------------- ---------- ---------- ----------- -------------------- ----------
oradbdc PRIMARY 1 36 36 17-DEC-2025 20:53:18 0
==> Standby:
SQL> select NAME, DB_UNIQUE_NAME, DATABASE_ROLE, OPEN_MODE from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE
--------- ------------------------------ ---------------- --------------------
ORADB oradbdr PHYSICAL STANDBY MOUNTED
SQL> set lines 200 pages 300
SQL> alter session set nls_date_format= 'DD-MON-YYYY HH24:MI:SS';
SQL> select d.db_unique_name,d.database_role, a.thread#, b.last_seq, a.applied_seq, b.last_seq - a.applied_seq ARC_DIFF, a.last_app_timestamp,
round((sysdate - a.last_app_timestamp)*24*60,2) Gap_in_Mins, round((sysdate - a.last_app_timestamp)*24*60*60,2) Gap_in_Seconds
FROM
(select thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp from gv$archived_log where REGISTRAR='RFS' and applied='YES' group by thread#) a,
(select thread#, MAX(sequence#) last_seq from gv$archived_log group by thread#) b,
(select db_unique_name,database_role from v$database) d where a.thread#=b.thread#;
Session altered.
DB_UNIQUE_NAME DATABASE_ROLE THREAD# LAST_SEQ APPLIED_SEQ ARC_DIFF LAST_APP_TIMESTAMP GAP_IN_MINS GAP_IN_SECONDS
------------------------------ ---------------- ---------- ---------- ----------- ---------- -------------------- ----------- --------------
oradbdr PHYSICAL STANDBY 1 36 36 0 17-DEC-2025 20:53:18 3.85 231
==> Unsupported Objects
SQL> col owner for a15;
SQL> select distinct owner, table_name from dba_logstdby_unsupported order by owner,table_name;SQL>
no rows selected
==> Crosscheck for Primary Key. Add a primary key in table if any record return below.
SQL> SELECT OWNER, TABLE_NAME,BAD_COLUMN FROM DBA_LOGSTDBY_NOT_UNIQUE;
no rows selected
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
==> Primary:
[oracle@dcdb ~]$ mkdir -p /u01/app/oracle/arch/standby
SQL> show parameter recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/fast_recovery_
area
db_recovery_file_dest_size big integer 8G
recovery_parallelism integer 0
remote_recovery_file_dest string
SQL> show parameter LOG_ARCHIVE_DEST_1
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1 string
SQL> show parameter LOG_ARCHIVE_DEST_3
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_3 string
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST
VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=oradbdc' scope=both;
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_3='LOCATION=/u01/app/oracle/arch/standby
VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=oradbdc' scope=both;
System altered.
SQL> show parameter LOG_ARCHIVE_DEST_1
NAME TYPE VALUE
------------------------------------ ----------- -----------------------------------
log_archive_dest_1 string LOCATION=USE_DB_RECOVERY_FILE_
DEST VALID_FOR=(ONLINE_LOGFILE
S,ALL_ROLES) DB_UNIQUE_NAME=oradbdc
SQL> show parameter LOG_ARCHIVE_DEST_3
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_3 string LOCATION=/u01/app/oracle/arch/
standby VALID_FOR=(STANDBY_LOG
FILES,STANDBY_ROLE)
DB_UNIQUE_NAME=oradbdc
==> Standby:
[oracle@drdb ~]$ mkdir -p /u01/app/oracle/arch/standby
SQL> show parameter recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/fast_recovery_
area
db_recovery_file_dest_size big integer 8G
recovery_parallelism integer 0
remote_recovery_file_dest string
SQL> show parameter LOG_ARCHIVE_DEST_1
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1 string
SQL> show parameter LOG_ARCHIVE_DEST_3
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_3 string
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST
VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=oradbdr' scope=both;
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_3='LOCATION=/u01/app/oracle/arch/standby
VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=oradbdr' scope=both;
System altered.
SQL> show parameter LOG_ARCHIVE_DEST_1
NAME TYPE VALUE
------------------------------------ ----------- -----------------------------------
log_archive_dest_1 string LOCATION=USE_DB_RECOVERY_FILE_
DEST VALID_FOR=(ONLINE_LOGFILE
S,ALL_ROLES) DB_UNIQUE_NAME=oradbdr
SQL> show parameter LOG_ARCHIVE_DEST_3
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_3 string LOCATION=/u01/app/oracle/arch/
standby VALID_FOR=(STANDBY_LOG
FILES,STANDBY_ROLE)
DB_UNIQUE_NAME=oradbdr
SQL> EXECUTE DBMS_LOGSTDBY.BUILD;
PL/SQL procedure successfully completed.
Syntax: ALTER DATABASE RECOVER TO LOGICAL STANDBY db_name;
For db_name value, specify a database name that is different from the primary database to identify the new logical standby database. Also make sure the database is running with spfile.
SQL> alter database recover to logical standby drdb;
Database altered.
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL>
SQL> startup mount
ORACLE instance started.
Total System Global Area 1073739904 bytes
Fixed Size 8947840 bytes
Variable Size 897581056 bytes
Database Buffers 159383552 bytes
Redo Buffers 7827456 bytes
Database mounted.
SQL>
SQL> alter database open resetlogs;
Database altered.
SQL> select name, open_mode, db_unique_name, database_role, guard_status from v$database;
NAME OPEN_MODE DB_UNIQUE_NAME DATABASE_ROLE GUARD_STATUS
--------- -------------------- ---------------- ---------------- ------------
DRDB READ WRITE oradbdr LOGICAL STANDBY ALL
SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string DRDB
SQL>
SQL> alter database start logical standby apply immediate;
Database altered.
SQL> SELECT name, value FROM v$logstdby_stats;
NAME VALUE
---------------------------------------------- -----------------
logminer session id 1
number of preparers 1
number of appliers 5
server processes in use 9
maximum SGA for LCR cache (MB) 100
preserve commit order TRUE
transaction consistency FULL
parallel read enabled N
coordinator state INITIALIZING
coordinator startup time 17-DEC-25
coordinator uptime (seconds) 5
txns received from logminer 0
txns assigned to apply 0
txns applied 0
txns discarded during restart 0
large txns waiting to be assigned 0
session restart SCN 0
rolled back txns mined 0
DDL txns mined 0
CTAS txns mined 0
bytes of redo mined 0
bytes paged out 0
pageout time (seconds) 0
bytes checkpointed 0
checkpoint time (seconds) 0
system idle time (seconds) 0
standby redo logs mined 0
archived logs mined 0
gap fetched logs mined 0
standby redo log reuse detected 0
logfile open failures 0
current logfile wait (seconds) 0
total logfile wait (seconds) 0
thread enable mined 0
thread disable mined 0
distinct txns in queue 0
number of logged PLSQL procedures mined 0
waits due to full transaction queue 0
reque due to full transaction queue 0
resize due to full transaction queue 0
waits due to full redo queue 0
waits due to full merge queue 0
work units partial 0
work units empty 0
transaction start skipped 0
number of redo records decrypted 0
46 rows selected.
SQL> !ps -ef|grep lsp
oracle 7371 1 0 21:08 ? 00:00:00 ora_lsp0_oradbdr
oracle 7400 5861 0 21:10 pts/0 00:00:00 /bin/bash -c ps -ef|grep lsp
oracle 7402 7400 0 21:10 pts/0 00:00:00 grep lsp
==> Primary:
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
42
==> Standby:
SQL> select sequence#,first_change#,next_change#,timestamp,applied,blocks,block_size from dba_logstdby_log;
SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# TIMESTAMP APPLIED BLOCKS BLOCK_SIZE
---------- ------------- ------------ -------------------- -------- ---------- ----------
38 2354571 2355105 17-DEC-2025 21:11:11 YES 38359 512
39 2355105 2355125 17-DEC-2025 21:11:10 YES 40 512
40 2355125 2358171 17-DEC-2025 21:19:34 YES 2685 512
41 2358171 2358214 17-DEC-2025 21:19:53 YES 22 512
42 2358214 2358226 17-DEC-2025 21:19:57 CURRENT 20 512
SQL>
SQL> COL NAME FOR A20
SQL> COL VALUE FOR A12
SQL> COL UNIT FOR A30
SQL> SELECT NAME, VALUE, UNIT FROM V$DATAGUARD_STATS;
NAME VALUE UNIT
-------------------- ------------ ------------------------------
transport lag +00 00:00:14 day(2) to second(0) interval
apply lag +00 00:00:14 day(2) to second(0) interval
apply finish time +00 00:00:00 day(2) to second(3) interval
estimated startup time 24 second
SQL> select a.latest_scn,a.latest_time,a.applied_scn,a.applied_time,b.read_scn,b.read_time from v$logstdby_progress a,dba_logstdby_progress b;
LATEST_SCN LATEST_TIME APPLIED_SCN APPLIED_TIME READ_SCN READ_TIME
---------- -------------------- ----------- -------------------- ---------- --------------------
2358480 17-DEC-2025 21:21:51 2358480 17-DEC-2025 21:21:51 2358481 17-DEC-2025 21:21:51
==> Primary:
SQL> def
DEFINE _DATE = "18-DEC-25" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "oradbdc" (CHAR)
DEFINE _USER = "SYS" (CHAR)
DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1928000000" (CHAR)
DEFINE _EDITOR = "vi" (CHAR)
DEFINE _O_VERSION = "Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.28.0.0.0" (CHAR)
DEFINE _O_RELEASE = "1928000000" (CHAR)
SQL>
SQL> select username from dba_users where username='TEST';
no rows selected
SQL> create user test identified by Test##123;
User created.
SQL> select username from dba_users where username='TEST';
USERNAME
--------------
TEST
SQL> alter system switch logfile;
System altered.
==> Standby:
SQL> def
DEFINE _DATE = "18-DEC-25" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "oradbdr" (CHAR)
DEFINE _USER = "SYS" (CHAR)
DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1928000000" (CHAR)
DEFINE _EDITOR = "vi" (CHAR)
DEFINE _O_VERSION = "Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.28.0.0.0" (CHAR)
DEFINE _O_RELEASE = "1928000000" (CHAR)
DEFINE _RC = "0" (CHAR)
SQL> select username from dba_users where username='TEST';
USERNAME
--------------
TEST
Thank you for reading!
I hope this content has been helpful to you. Your feedback and suggestions are always welcome — feel free to leave a comment or reach out with any queries.
Abhishek Shrivastava
Comments
Post a Comment