• Arun Kumar

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;




815 views

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

  • Facebook
  • Twitter
  • YouTube
  • Instagram

© 2020 by DBA Genesis