Create a logical standby database in 10gR2

Step: 1 For logical standby we have to first create PHYSICAL STANDBY DATABASE and sure physical standby database working properly.

Step 2 Stop Redo Apply on the Physical Standby Database

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Step 3 Set Parameters for Logical Standby in Primary

LOG_ARCHIVE_DEST_1= 'LOCATION=/data01/archive/online/ VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prod'

LOG_ARCHIVE_DEST_3= 'LOCATION=/data01/archive/standby/ VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=prod'

Note: LOG_ARCHIVE_DEST_3 only takes effect when the primary database is transitioned to the logical standby role.

Step 4 Build a Dictionary in the Redo Data on Primary Database

SQL> EXECUTE DBMS_LOGSTDBY.BUILD;

Step 5 Convert to a Logical Standby Database (Execute bellow command on Standby Site)

SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY ;

For db_name, specify a database name to identify the new logical standby database. If you are using a spfile for standby, then command will update the db_name parameter otherwise it will issues a message reminding you to set the name of the DB_NAME parameter after shutting down the database.

Step 6 Create a New Password File for Logical Standby Database

$ORAPWD FILE= PASSWORD= ENTRIES=

Because the conversion process changes the database name (that was originally set with the DB_NAME initialization parameter) for the logical standby database, you must re-create the password file

Step 7 Shutdown and Startup Logical Standby Database in Mount Stage

SQL> SHUTDOWN;

SQL> STARTUP MOUNT;

Step 8 Adjust Initialization Parameter on Logical Standby Database

LOG_ARCHIVE_DEST_1= 'LOCATION=/u01/arch/online/ VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby'

LOG_ARCHIVE_DEST_2= 'SERVICE=prim1 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prod'

LOG_ARCHIVE_DEST_3= 'LOCATION=/u01/arch/standby/ VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=standby'

Step 9 Open the Logical Standby Database

SQL> ALTER DATABASE OPEN RESETLOGS;

Step 10 Start Logical Apply on Standby

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

if you request IMMEDIATE option, you have to create standby redo-logs on standby site. Good idea is to create them on all instances, where role reversal may take place and if higher protection mode is required

Let’s test logical standby is working properly, what archivelogs are registered on standby

SQL> select SEQUENCE#, FIRST_TIME, NEXT_TIME, DICT_BEGIN, DICT_END, APPLIED from dba_logstdby_log order by SEQUENCE#;

SEQUENCE# FIRST_TIM NEXT_TIME DIC DIC APPLIED

---------- --------- --------- --- --- --------

12 29-APR-09 29-APR-09 YES YES CURRENT

13 29-APR-09 29-APR-09 NO  NO  CURRENT

14 29-APR-09 29-APR-09 NO  NO  CURRENT

15 29-APR-09 29-APR-09 NO  NO  CURRENT

Make a logswitch on primary

SQL> alter system switch logfile;
System altered.

We are interested to see what the logical standby is doing, we may query coordinator status

SQL> select name, value from v$logstdby_stats where name='coordinator state';

NAME                 VALUE

-------------------- --------------------

coordinator state    IDLE

or all participants (coordinator, reader,builder,preparer, applier)

SQL> select type,high_scn,status from v$logstdby;

TYPE HIGH_SCN STATUS
-------------------- ---------- ------------------------------

COORDINATOR 161732 ORA-16116: no work available
READER 161732 ORA-16240: Waiting for logfile (thread# 1, sequence# 17)
BUILDER 161569 ORA-16116: no work available
PREPARER 161568 ORA-16116: no work available
ANALYZER 161061 ORA-16116: no work available
APPLIER 160773 ORA-16116: no work available
APPLIER 161028 ORA-16116: no work available
APPLIER ORA-16116: no work available
APPLIER ORA-16116: no work available
APPLIER ORA-16116: no work available
10 rows selected.

You can query v$logstdby_process for the last applied SCN on the standby site

SQL> select applied_scn,latest_scn from v$logstdby_progress;

APPLIED_SCN LATEST_SCN

----------- ----------

161731     161731