How To Create Logical Standby Database From Existing Physical Standby Database

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.4
Information in this document applies to any platform.

Goal

Steps to create logical standby database from existing physical standby database

Solution

This note assumes that physical standby database is already existing and working successfully. For steps on creating physical standby database, refer to

Note 736863.1 - Steps To Create Physical Standby Database

Once the physical standby is created successfully, perform below steps to convert it to logical standby database :

1. Cancel managed recovery on the physical standby database :

sql>recover managed standby database cancel;


2. On primary database, check for unsupported datatypes and tables by issuing following query :

sql>select distinct owner, table_name from dba_logstdby_unsupported order by owner, table_name;


It is recommended that there should be a primary key or a unique index to tables on the primary database, whenever appropriate and possible, so that SQL apply operations can efficiently apply data to logical standby database.

Following query will display tables that SQL apply operations may not identify uniquely :

sql> select owner, table_name,bad_column from dba_logstdby_not_unique;

3. Enable supplemental loggging to the primary database so that oracle puts information in redo logs to uniquely indentify tables in logical standby database.

sql> alter database add supplemental log data (primary key, unique index) columns;
sql> alter system switch logfile;

Verify if supplemental logging is enabled by issuing following query :

sql> select supplemental_log_data_pk, supplemental_log_data_ui from v$database;

SUP SUP
--- ---
YES YES

4. In the tnsnames.ora of primary database, make an entry pointing to logical standby :

LGSTDBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = machine_logical)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = LGSTDBY)
)
)


5. Create logical standby dictionary on primary database by issuing following command :

Make sure that no user is accessing database by starting the database in restricted mode and then create dictionary :

SQL> EXECUTE DBMS_LOGSTDBY.BUILD;

6. Issue command to switch physical standby to logical standby after changing db_name in pfile to logical standby name. Shutdown the standby database if mounted and mount the database again. In this configuration, logical standby database name is LGSTDBY.

sql>alter database recover to logical standby LGSTDBY;

7. Check view v$managed_standby to see the file for which recovery is waiting and bring it to dr site till which conversion happened.

sql>select process,status,client_process,sequence#,block#,active_agents,known_agents from v$managed_standby;


shutdown and startup mount the database and then open it with resetlogs. In the alert log file of standby database, messages like below should be seen :

*** DBNEWID utility started ***
DBID will be changed from 850049341 to new DBID of 4234500573 for database ORCL10G
DBNAME will be changed from ORCL10G to new DBNAME of LGSTDBY
Starting datafile conversion
Setting recovery target incarnation to 1
Datafile conversion complete
Database name changed to LGSTDBY.
Modify parameter file and generate a new password file before restarting.
Database ID for database LGSTDBY changed to 4234500573.
All previous backups and archived redo logs for this database are unusable.
Database has been shutdown, open with RESETLOGS option.
Succesfully changed database name and ID.
*** DBNEWID utility finished succesfully ***


8. Verify that the dbid is changed by querying v$database and start logical apply by issuing command :

sql> alter database start logical standby apply;


verify that apply is started and standby is created successfully.