Closure
Closure
Home » Databases » Oracle Database » How to convert a database from Physical Standby to Logical Standby in Oracle Data Guard

How to convert a database from Physical Standby to Logical Standby in Oracle Data Guard

How to convert a database from Physical Standby to Logical Standby in Oracle Data Guard
, , , , , Leave a comment
How to convert a database from Physical Standby to Logical Standby in Oracle Data Guard
Latest posts by Stratos Matzouranis (see all)

To another article we have seen how to install Oracle Data Guard, how to create a database as Physical Standby and how as Snapshot Standby. In this article we will see how to convert a database that is a physical standby to Logical Standby.

But what does a logical standby offer us?

Unlike physical standby, replication is not done at the block level but with transfer of SQL statements, we can to have it open for read/write, we can to create indexes and materialized views, we can use it for reporting and for to upgrade the Oracle Database version with almost zero downtime.

How to convert a physical standby to a logical standby

In primary school

To begin with, we connect to primary and we check that we have compatible data types and we can convert the database to Logical Standby, the following query should not return any records:

sqlplus / as sysdba
select * from dba_logstdby_unsupported;

Then we connect to the Data Guard Broker, check the state of the databases and disable apply on the database that we will convert:

dgmgrl "sys/Kwdikos1!"@ORADEV
show configuration;
Configuration - dg_config

  Protection Mode: MaxAvailability
  Members:
  oradev    - Primary database
    oradevdr  - Physical standby database
    oradevdr3 - Physical standby database


Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 5 seconds ago)

edit database oradevdr3 set state=apply-off;
exit;

Then we activate the supplementary logging in primary:

sqlplus / as sysdba

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

And after activating it, we create it data dictionary for Logical Standby:

exec dbms_logstdby.build;
exit;

On standby

We shutdown the database and start it in exclusive mode:

sqlplus / as sysdba
shutdown immediate;
startup mount exclusive;

We convert the standby database to a Logical Standby:

alter database recover to logical standby oradev;

Then we restart the database in open mode:

shutdown immediate;
startup mount;
alter database open resetlogs;

Then we can enable Logical Standby:

alter database start logical standby apply immediate;

In primary school

At this point we need to remove the database from the Data Guard configuration and add it back through the Broker:

dgmgrl "sys/Kwdikos1!"@ORADEV
remove database oradevdr3;

add database oradevdr3 as connect identifier is oradevdr3;

enable database oradevdr3;

Somewhere here we are finished and if we look at the configuration status we will see that the conversion to Logical Standby has been completed:

show configuration;
Configuration - dg_config

  Protection Mode: MaxAvailability
  Members:
  oradev    - Primary database
    oradevdr  - Physical standby database
    oradevdr3 - Logical standby database


Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 5 seconds ago)

To see the progress of SQL Apply in Logical Standby, we run the following query on this database:

  sqlplus / as sysdba
SELECT name, value FROM v$logstdby_stats;

Sources:

Share it

Leave a reply