Steps to convert Standby Database to Logical Standby Database :

Step #1 :

In this step logminer Dictionary Must be Built into redo data. ( Build LogMiner Tables for Dictionary)

On Primary

    SQL> begin
      2  dbms_logstdby.build;
      3  end ;
      4  /



OR

     SQL> execute dbms_logstdby.build;


Step #2:

Now we have stop Managed recovery between primary and standby :

On Standby :


    SQL> alter database recover managed standby database cancel ;

    Database altered.


Step #3 :

Convert Physical Standby to Logical Standby .

On Standby :


    SQL> alter database recover to logical standby stdby ;

    Database altered.


Step #4:

On Standby ( New Logical)  we open it in Resetlog mode .


On New Logical ( Standby) :

    SQL> shutdown immediate ;
    ORA-01507: database not mounted


    ORACLE instance shut down.

    SQL> startup mount ;
    ORACLE instance started.

    Total System Global Area  839282688 bytes
    Fixed Size                  2217992 bytes
    Variable Size             532678648 bytes
    Database Buffers          301989888 bytes
    Redo Buffers                2396160 bytes
    Database mounted.

    SQL>
    SQL>
    SQL> alter database open resetlogs ;

    Database altered.


Step #5:

Start Apply Service On logical


    SQL> alter database start logical standby apply ;

    Database altered.


OR


    SQL> alter database start logical standby apply immediate ;
    Database altered.


    Check if everything Goes Fine :

    SQL> select database_role , Name from v$database ;

    DATABASE_ROLE    NAME
    ---------------- ---------
    LOGICAL STANDBY  STDBY



Cheers!