Convert Physical Standby Into 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

Primary Database Changes

Below is the current configuration setup

DGMGRL> show configuration

Configuration - my_dg

  Protection Mode: MaxPerformance
  Members:
  ip7     - Primary database
    ip7_stb - (*) Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 59 seconds ago)

In the primary database, retrieve the list of objects that are not supported

set pagesize 25;
column owner format a5;
select distinct owner, table_name from dba_logstdby_unsupported order by owner,table_name;

Check the reason behind those unsupported objects

select column_name,data_type from dba_logstdby_unsupported where owner='OE' and table_name= 'CUSTOMERS';

Identify the unsupported schemas on primary

Col owner format a20;
select owner from dba_logstdby_skip where statement_opt = 'INTERNAL SCHEMA';

Make sure each table row in primary database can be uniquely identified. To fix the table, make sure you add a primary key

SELECT OWNER, TABLE_NAME,BAD_COLUMN FROM DBA_LOGSTDBY_NOT_UNIQUE;

Redo Apply needs to be stopped on the Physical Standby now

DGMGRL> edit database ip7_st set state = apply-off;

Build Log Miner directory on primary

SQL> exec dbms_logstdby.build

Open Physical Standby

Open the physical standby database

On Standby Server
=================
SQL> alter database recover to logical standby ip7_stb;
SQL> shutdown immediate
SQL> startup mount
SQL> alter database open resetlogs;
SQL> select name, open_mode, db_unique_name, database_role, guard_status from v$database;

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 ip7_stb;

Start Logical Apply Service on standby server

On Standby
==========
SQL> alter database start logical standby apply immediate;

Now add the new logical standby to the broker configuration

DGMGRL> add database ip7_stb as connect identifier is ip7_stb;
DGMGRL> enable database ip7_stb

Let’s check the configuration once again

DGMGRL> show configuration;

Configuration - my_dg

  Protection Mode: MaxPerformance
  Members:
  ip7     - Primary database
    ip7_stb - Logical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 5 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:
===========
ps -ef|grep lsp

Query to check if logical standby is applying the transactions

SQL> SELECT name, value FROM v$logstdby_stats;

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;