• Arun Kumar

Convert Physical Standby Into Logical Standby

In this article, 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 - my_dg

  Protection Mode: MaxPerformance
  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


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

Alter database start logical standby apply immediate;


Recent Posts

See All

Oracle Data Guard Protection Modes

A Data Guard configuration always runs in one of three data protection modes (also called as redo transport rules): Maximum Protection Maximum Availability Maximum Performance (default mode of operati

Oracle Data Guard Physical Standby Configuration

There are various steps in which you can configure physical standby database. We need to make several changes to the primary database before we can even setup the standby database. This article applie

Client Connectivity in Data Guard Configuration

When you have a physical standby, you must make sure client connectivity is set properly so that when you perform failover or switchover, client must smoothly connect to the new primary. Create a data