Overview Steps:

Step:1 verify the name, open mode, roles in primary and standby databases.

Step:2 Look into the max(sequence#) value from v$thread view on both sides.

Step:3 Check for unsupported datatypes in logical standby in primary db.

Step:4 Find the tables without unique identifier in primary database.

Step;5 At Primary Database, Build a Log Miner in redo data So that sql apply can Interpret the redo changes.

Step:6 Enable the log _archive_dest_3 parameter for primary db.

Step:7 logical supplemental is automatically enabled by executing this, if not already enabled.

Step:8 Cancel recovery at Physical Standby Database.

Step:9 Create a spfile and then mount the database. In order to create logical standby at the mount stage.

Step:10 Enable the log _archive_dest_3 parameter for standby db.

Step:11 Open the database with reset log option.

Step:12 Then start the logical recovery.

Step:13 verify the database has been converted to logical standby databases.

Step:1 Check name, open_mode, database_role of primary and standby databases.

SQL> select name, open_mode, database_role from v$database;

NAME             OPEN_MODE      DATABASE_ROLE
--------- -------------------- ----------------
CANADA           READ ONLY      PHYSICAL STANDBY

Step:2 Verify the max(sequence#)  from v$thread view on both the sides, it should have same max(sequence#) value.

on primary side

SQL> select max(sequence#) from v$thread;

MAX(SEQUENCE#)
--------------
     24

on standby side

SQL> select max(sequence#) from v$thread;

MAX(SEQUENCE#)
--------------
     24

Step:3 Check for unsupported datatypes in logical standby in primary db and Find the tables without unique identifier in primary database.

Step:4 execute dbms_logstdby.build,While executing this,logical supplemental is automatically enabled,if not already enabled and also enable the log_archive_dest_3 parameter.

SQL>execute dbms_logstdby.build;

PL/SQL Procedure successfully completed.

Step:5 Cancel recovery at physical standby database.

SQL>alter database recover managed standby cancel;

Database altered

Step:6 start the db at mount stage and issue the following command to convert the physical standby to logical standby.

SQL>alter database recover to logical standby standby;

Database altered

Step:7 open the database with resetlog option.

SQL>alter database open resetlogs;

Database altered

Step:8 perform the logical recovery.

SQL>alter database start logical standby apply immediate;

Database altered

Step:9 check whether database role of db has changed from physical standby to logical standby.once it is altered it is clear that we have successfully converted our physical standby into logical standby.

SQL> select name, open_mode, database_role from v$database;

  NAME            DATABASE_ROLE
---------     -------------------- 
 CANADA          LOGICAL STANDBY

Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8