How to Convert Physical Standby to Logical Standby Database

Before converting physical standby to logical convert resolve the following prerequisite conditions for creating a Logical Standby Database.

1. Determine Support for Data Types and Storage Attributes for Tables. If the primary database contains unsupported tables, log apply services automatically exclude the tables when applying redo logs to the logical standby database.

SQL> SELECT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED;
OWNER                          TABLE_NAME
------------------------------ ------------------------------
ERPUSER                        EMPLOYEE_HIST

2. Ensure Table Rows in the Primary Database Can Be Uniquely Identified. If your application ensures the rows in a table are unique, you can create a disabled primary key RELY constraint on the table else create the primary key.

SQL> SELECT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_NOT_UNIQUE;
OWNER                          TABLE_NAME
------------------------------ ------------------------------
ERPUSER                          EMPLOYEE_SALARY

SQL> ALTER TABLE EMPLOYEE_SALARY ADD PRIMARY KEY (EMPLOYEE_ID) RELY DISABLE;

Now Convert Physical Standby to Logical Standby Database 

1. Cancel Recovery Manager in Physical Standby Database.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

2. Apply the parameters change in primary database.

ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ERPPRIM' SCOPE=BOTH SID='*';

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=ERPPSTAND LGWR ASYNC NOAFFIRM DELAY=0 OPTIONAL COMPRESSION=ENABLE MAX_FAILURE=0 MAX_CONNECTIONS=1 REOPEN=180 NET_TIMEOUT=30 DB_UNIQUE_NAME="ERPPSTAND", VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)' SCOPE=BOTH SID='*';

ALTER SYSTEM SET LOG_ARCHIVE_DEST_3='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=ERPPRIM'  SCOPE=BOTH SID='*';

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE  SCOPE=BOTH SID='*';

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE  SCOPE=BOTH SID='*';

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=ENABLE  SCOPE=BOTH SID='*';

3. Build a Dictionary in the Redo Data on Primary Database

EXECUTE DBMS_LOGSTDBY.BUILD;

4. Convert to a Logical Standby Database in Standby Database.

ALTER DATABASE RECOVER TO LOGICAL STANDBY ERPPSTAND;

5. Shutdown and Startup Logical Standby Database in Mount Stage

SHUTDOWN; 

STARTUP MOUNT;

6. Adjust Initialization Parameter on Logical Standby Database

ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ERPPSTAND' SCOPE=BOTH SID='*';

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=ERPPRIM LGWR ASYNC NOAFFIRM DELAY=0 OPTIONAL COMPRESSION=ENABLE MAX_FAILURE=0 MAX_CONNECTIONS=1 REOPEN=180 NET_TIMEOUT=30 DB_UNIQUE_NAME="ERPPRIM", VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)' SCOPE=BOTH SID='*';

ALTER SYSTEM SET LOG_ARCHIVE_DEST_3='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=ERPPSTAND'  SCOPE=BOTH SID='*';

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE  SCOPE=BOTH SID='*';

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE  SCOPE=BOTH SID='*';

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=ENABLE  SCOPE=BOTH SID='*';

7. Open the Logical Standby Database

SQL> ALTER DATABASE OPEN RESETLOGS;

8. Start Logical Apply on Standby

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;