Oracle Dataguard - Configuring Logical Standby

A logical standby is a type of standby database in Oracle Data Guard that stays in read/write mode, allowing it to be open for reporting and read operations while receiving real-time data synchronization from the primary database. It operates by converting the data received from the primary database into SQL statements, which are then executed on the logical standby, making it ideal for data consolidation, testing, and reporting purposes.

  • Primary Database Changes
  • Open Physical Standby
  • Data Guard Broker Changes
  • Verify Logical Standby
  • Shutting Down Logical Standby

We will be converting an existing Physical standby into a logical standby. We assume that you already have a physical standby configured and data guard broker is enabled. Once you convert to Logical Standby, you cannot convert it back to Physical Standby.


Primary Database Changes :

Below is the current configuration setup : 
	DGMGRL> show configuration

	Configuration - prim1

	  Protection Mode: MaxPerformance
	  Members:
		tesstand - Primary database
		tesprim1 - Physical standby database
		Warning: ORA-16809: multiple warnings detected for the member

	Fast-Start Failover: DISABLED
Configuration Status : 
	WARNING   (status updated 37 seconds ago)
In the primary database, retrieve the list of objects that are not supported : 
	SQL> select distinct owner, table_name from dba_logstdby_unsupported order by owner,table_name;

	no rows selected
Check the reason behind those unsupported objects : 
	SQL> select column_name,data_type from dba_logstdby_unsupported;

	no rows selected
Identify the unsupported schemas on primary : 
	SQL> select owner from dba_logstdby_skip where statement_opt = 'INTERNAL SCHEMA';

	OWNER
	-----------------------
	SYS
	SYSTEM
	XS$NULL
	OJVMSYS
	LBACSYS
	OUTLN
	SYS$UMF
	APPQOSSYS
	DBSNMP
	DBSFWUSER
	ANONYMOUS

	OWNER
	------------------------
	GGSYS
	CTXSYS
	DVF
	DVSYS
	SI_INFORMTN_SCHEMA
	GSMADMIN_INTERNAL
	ORDPLUGINS
	MDSYS
	OLAPSYS
	ORDDATA
	XDB

	OWNER
	-----------------------
	ORDSYS
	WMSYS
	GSMCATUSER
	REMOTE_SCHEDULER_AGENT
	SYSBACKUP
	GSMUSER
	SYSRAC
	AUDSYS
	DIP
	SYSKM
	ORACLE_OCM

	OWNER
	--------------------------
	SPATIAL_CSW_ADMIN_USR
	SYSDG

	35 rows selected.
Make sure each table row in primary database can be uniquely identified. To fix the table, make sure you add a primary key : 
	SQL> SELECT OWNER, TABLE_NAME,BAD_COLUMN FROM DBA_LOGSTDBY_NOT_UNIQUE
	  2  ;

	OWNER		TABLE_NAME	BAD_COLUMN
	------		-----------	----------
	U1		T1		N
	U1		T2		N
Redo Apply needs to be stopped on the Physical Standby now : 
	DGMGRL> edit database tesprim1 set state = apply-off;
	Succeeded.

	Build Log Miner directory on primary
	SQL> exec dbms_logstdby.build;

	PL/SQL procedure successfully completed.

Open Physical Standby :

Open the physical standby database

On Standby Server : 
	SQL> alter database recover to logical standby stand;

	Database altered.

	SQL> shut immediate
	ORA-01507: database not mounted


	ORACLE instance shut down.
	SQL> startup mount
	ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
	ORACLE instance started.

	Total System Global Area  532676608 bytes
	Fixed Size		    8622720 bytes
	Variable Size		  297799040 bytes
	Database Buffers	  218103808 bytes
	Redo Buffers		    8151040 bytes
	Database mounted.

	SQL> alter database open resetlogs;

	Database altered.

	SQL> select name, open_mode, db_unique_name, database_role, guard_status from v$database;
	

		
NAMEOPEN_MODEDB_UNIQUE_NAMEDATABASE_ROLEGUARD_S

		
-------------------------------------------------------

		
STANDREAD WRITEtesprim1LOGICAL STANDBY ALL

Data Guard Broker Changes :

Now we need to remove existing physical standby database from the configuration and add the new logical standby database 
	DGMGRL> remove database tesprim1

	Removed database "tesprim1" from the configuration
Start Logical Apply Service on standby server On Standby 
	SQL> alter database start logical standby apply immediate;

	Database altered.
Now add the new logical standby to the broker configuration 
	DGMGRL> add database tesprim1 as connect identifier is tesprim1;
	Database "tesprim1" added
	DGMGRL> enable database tesprim1;
	Enabled.
Let us check the configuration once again 
	DGMGRL> show configuration;

	Configuration - prim1

	  Protection Mode: MaxPerformance
	  Members:
	  tesstand - Primary database
		tesprim1 - Logical standby database
		  Warning: ORA-16854: apply lag could not be determined

	Fast-Start Failover: DISABLED

	Configuration Status:
	WARNING   (status updated 30 seconds ago)

Verify logical standby :

You cannot see the LSP process under v$managed_standby view but you can check the process at OS level

On standby : 
	[oracle@tesdb ~]$ ps -ef|grep lsp
	oracle    6795     1  4 17:19 ?        00:00:05 ora_lsp0_tesprim1
	oracle    7092  6941  0 17:21 pts/6    00:00:00 grep --color=auto lsp
Query to check if logical standby is applying the transactions 
	SQL> SELECT name, value FROM v$logstdby_stats;




	

		
NAMEVALUE

		
----------------------

		
 logminer session id 1

		
 number of preparers 1

		
 number of appliers 5

	






	
NAMEVALUE

	
----------------------

	
server processes in use9 

	
maximum SGA for LCR cache (MB) 100 

	
maximum events recorded 10000 








	
NAMEVALUE

	
----------------------

	
preserve commit order TRUE 

	
transaction consistency FULL 

	
record skipped errors Y 







	
NAMEVALUE

	
----------------------

	
record skipped DDLs  Y

	
record applied DDLs  N

	
record unsupported operations  N








	
NAMEVALUE

	
----------------------

	
realtime apply Y 

	
apply delay (minutes) 0 

	
peak apply rate (bytes/sec) 0 







	
NAMEVALUE

	
----------------------

	
record skipped PLSQL N 

	
record applied PLSQL N 

	
current apply rate (bytes/sec) 0 








	
NAMEVALUE

	
----------------------

	
coordinator state APPLYING 

	
coordinator startup time 31-OCT-23 

	
coordinator uptime (seconds) 170 







	
NAMEVALUE

	
----------------------

	
txns received from logminer 1 

	
txns assigned to apply 1 

	
txns applied 0 







	
NAMEVALUE

	
----------------------

	
txns discarded during restart 0 

	
large txns waiting to be assigned 0

	
session restart SCN 1988645 








	
NAMEVALUE

	
----------------------

	
rolled back txns mined  15  

	
DDL txns mined  0 

	
CTAS txns mined 0 








	
NAMEVALUE

	
----------------------

	
bytes of redo mined  17612196  

	
bytes paged out  0 

	
pageout time (seconds) 0 






 

 	
NAMEVALUE

 	
----------------------

 	
bytes checkpointed  1420  

 	
checkpoint time (seconds)  0 

 	
system idle time (seconds) 0 








	
NAMEVALUE

	
----------------------

	
standby redo logs mined  0  

	
archived logs mined  7 

	
gap fetched logs mined 5 








	
NAMEVALUE

	
----------------------

	
standby redo log reuse detected  0  

	
logfile open failures  0 

	
current logfile wait (seconds) 50 







	
NAMEVALUE

	
----------------------

	
total logfile wait (seconds)  0  

	
thread enable mined  0 

	
thread disable mined 0 






 

 	
NAMEVALUE

 	
----------------------

 	
distinct txns in queue  1  

 	
number of logged PLSQL procedures mined  0 





 



	47 rows selected.

Two simple ways to check logical standby is working fine
Create a table (not with sys schema) in primary and switch logfile. Wait and see if table reflects on standby
Monitor the logical standby alert log


Shutting Down Logical Standby
Switch log on primary
Alter system switch logfile;
On standby, stop LSP
Alter database stop logical standby apply;
Shutdown immediate;
To start the LSP process on standby
Startup;
Alter database start logical standby apply immediate;



(Oracle Dataguard - Configuring Snapshot Standby)