Logical Standby Database - Step by Step Configuration

Conversion from Physical to Logical Standby

Step (1) Create PHYSICAL STANDBY DATABASE and make sure working properly.
Step (2) Prepare the Primary Database to Support a Logical Standby Database

1. Enable supplemental logging

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX) COLUMNS;

2. Update undo_retention system parameter

SQL> ALTER SYSTEM SET UNDO_RETENTION=3600;

3. Create new directory/folder for new logical standby archive log and enable.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_3='LOCATION=/oradisk/archive/orcl/standbylog VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=orcl';
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=ENABLE;

4. On the primary database, issue the following to build the LogMiner tables for dictionary:

SQL> execute dbms_logstdby.build;

Step (3) Stop Recovery Process on Standby Database and convert it to Logical Database

On the standby database, stop the managed recovery process:

SQL> alter database recover managed standby database cancel;

Now, issue the command in standby side to convert it to logical:

SQL> alter database recover to logical standby logicaldb;

Note: If you didn't execute Step 2.1,2.4, the above command will wait since the dictionary information is not found. Don't worry; just execute the Step 2.4 at this point. Issue a few log switches on primary to make sure the archived logs are created and sent over to the standby:

SQL> alter system switch logfile;

On the standby side, you can see that the alter database command has completed, after some time. Now the standby is a logical one. You will see the following line in alert log:

RFS[12]: Identified database type as 'logical standby'
Recycle the database, and start SQL Apply process on new logical standby database :

SQL> shutdown
SQL> startup mount
SQL> alter database open resetlogs;
SQL> alter database start logical standby apply immediate;

The logical standby database is now fully operational! Once you convert the physical standby to a logical one, you can't convert it back to a physical one unless you use the special clause ("keep identity)

SQL> alter database recover to logical standby keep identity;
Database altered.

See more detailed step-by-step instructions in the documentation.