Convert Physical Standby Into Logical Standby

In this article, we will be converting an existing Physical standby into a logical standby.

Note: this article applies to Oracle 12c R2 version

Assumptions: you already have a physical standby configured and data guard broker is enabled.

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

Was this article helpful?

Related Articles

Leave a Comment