Please refer to my post of creating physical standby, and then follow below steps to convert it to logical standby.
On standby database
Make sure that all the sequences generated on primary side has been applied on standby.
Cancel the recovery on standby database
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
On primary database
alter system set LOG_ARCHIVE_DEST_1='LOCATION=<PATH> VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES)';
Make a directory for standby archive logs
alter system set LOG_ARCHIVE_DEST_3='LOCATION=<PATH> VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE)';
Run DBMS_LOGSTDBY.BUILD package to create metadata for log miner to apply SQLs on logical standby site.
EXECUTE DBMS_LOGSTDBY.BUILD;
On standby database
Check if the database is in mount mode.
DB should be in mount mode
Start the recover and change the DB name of standby
ALTER DATABASE RECOVER TO LOGICAL STANDBY <NEW DATABASE NAME>;
Set the archive log destination on standby site as well.
alter system set LOG_ARCHIVE_DEST_1='LOCATION=<PATH> VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES)'; alter system set LOG_ARCHIVE_DEST_3='LOCATION=<PATH> VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE)'; alter system set LOG_ARCHIVE_DEST_2='SERVICE=<SAME SERVICE> LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=<SAME NAME>'; #show parameter log_archive_dest_2 before and after ALTER DATABASE OPEN RESETLOGS; ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(<PRIMARY>,<NEW DATABASE NAME>)' SCOPE=BOTH;
On primary database
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(<PRIMARY>,<NEW DATABASE NAME>)' SCOPE=BOTH; alter system set LOG_ARCHIVE_DEST_2='SERVICE=<SAME SERVICE> LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=<NEW DATABASE NAME>'; #show parameter log_archive_dest_2 before and after
tnsnames.ora replace service_name parameter with SID parameter.
On standby database
ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;