How to configure a logical standby

First step is to configure a physical standby, below is the link of my blog post where you can see how to configure a physical standby :-

Configure a physical standby

After configuring the physical standby follow below simple steps to configure a logical standby :-

Do the following steps on standby database :-

SQL> select archived,sequence#,applied,to_Char(completion_Time,'dd-mon-yyyy hh24:mi:ss') from v$archived_log;
...
....
......

ARC  SEQUENCE# APPLIED   TO_CHAR(COMPLETION_T
--- ---------- --------- --------------------
YES         43 YES       28-nov-2015 21:15:26
YES         44 YES       28-nov-2015 21:15:28
YES         45 YES       28-nov-2015 21:36:11

Stop the MRP Process

SQL> recover managed standby database cancel;
Media recovery complete.

Do the below step on primary database

Build a Dictionary in the Redo Data

A LogMiner dictionary must be built into the redo data so that the LogMiner component of SQL Apply can properly interpret changes it sees in the redo. As part of building the LogMiner dictionary, supplemental logging is automatically set up to log primary key and unique-constraint/index columns. The supplemental logging information ensures each update contains enough information to logically identify each row that is modified by the statement.

To build the LogMiner dictionary, issue the following statement:

SQL> EXECUTE DBMS_LOGSTDBY.BUILD;

The DBMS_LOGSTDBY.BUILD procedure waits for all existing transactions to complete. Long-running transactions executed on the primary database will affect the timeliness of this command.

Do the below steps on standby database

SQL> alter database recover to logical standby sby1;

Database altered.

Shutdown the standby instance and bring it to mount state

SQL> shu immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  521936896 bytes
Fixed Size                  2214936 bytes
Variable Size             159384552 bytes
Database Buffers          356515840 bytes
Redo Buffers                3821568 bytes
Database mounted.

SQL> alter database open resetlogs;

Database altered.

SQL> alter database start logical standby apply immediate;

Database altered.

SQL> desc v$logstdby_progress
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 APPLIED_SCN                                        NUMBER
 APPLIED_TIME                                       DATE
 RESTART_SCN                                        NUMBER
 RESTART_TIME                                       DATE
 LATEST_SCN                                         NUMBER
 LATEST_TIME                                        DATE
 MINING_SCN                                         NUMBER
 MINING_TIME                                        DATE
 RESETLOGS_ID                                       NUMBER

SQL> select APPLIED_SCN,LATEST_SCN from v$logstdby_progress;

APPLIED_SCN LATEST_SCN
----------- ----------
    1294315    1294373