Create Oracle 10g Logical Standby Database

1. Backup a spfile copy from Production, Change the DB unique name parameter as per the new logical db name and proceed with the
db startup in no mount state in the logical standby .


2.
connected to target database: PWPRD (DBID=505738321)
connected to recovery catalog database
connected to auxiliary database: PWPRD (not mounted)

RMAN> run {
2> resync catalog;
3> allocate auxiliary channel t1 type 'SBT_TAPE' MAXOPENFILES 6 MAXPIECESIZE 8G
4> parms 'SBT_LIBRARY=/lib/libnwora.so,ENV=(NSR_SERVER=192.168.200.166,NSR_DATA_VOLUME_POOL=Database,NSR_GROUP=Oracle,NSR_DEBUG_
5> FILE=/tmp/debug.out)';
6> allocate auxiliary channel t2 type 'SBT_TAPE' MAXOPENFILES 6 MAXPIECESIZE 8G
7> parms 'SBT_LIBRARY=/lib/libnwora.so,ENV=(NSR_SERVER=192.168.200.166,NSR_DATA_VOLUME_POOL=Database,NSR_GROUP=Oracle,NSR_DEBUG_
8> FILE=/tmp/debug.out)';
9> duplicate target database for standby dorecover;
10> release channel t1;
11> release channel t2;
12> }


Please perform the restore as above

Note : After restore , Create spfile for your db and bounce your database


3. Add the following parameters to your database spfile

*.log_archive_config='dg_config=(PWPRD,PWPRDDR,PWRPPRD)'
*.log_archive_dest_1='LOCATION="+ARCHIVES", valid_for=(ONLINE_LOGFILE,ALL_ROLES)'
*.log_archive_dest_2='LOCATION=+ARCHIVES/PWRPPRD/STANDBY/ VALID_FOR=(STANDBY_LOGFILES, STANDBY_ROLE) DB_UNIQUE_NAME=PWRPPRD'

Note : Please make sure you create the directory named +ARCHIVES/PWRPPRD/STANDBY in your logical standby ASM manaully.


4. Make the below change in production database :


alter system set log_archive_dest_3='service="PWRPPRD", LGWR ASYNC NOAFFIRM delay=0 OPTIONAL max_failure=10 max_connections=1 reopen=15 db_unique_name="PWRPPRD" register net_timeout=180 valid_for=(online_logfile,primary_role)' scope=both sid='*';

Note : Starting shipping the logs from production and start applying it .

Conversion of physical standby to Logical

5. Cancel the managed Recovery at DR Database ( Logical db )

6. Make the below executions in Production database :

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

Database altered.

SQL> execute dbms_logstdby.build;

PL/SQL procedure successfully completed.

7. In the New logical Standby , Please convert the database into Single instance .


SQL> alter system set cluster_database=FALSE scope=spfile sid='*'
System altered.

SQL> shut immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.

Total System Global Area 4294967296 bytes
Fixed Size 2133936 bytes
Variable Size 810938448 bytes
Database Buffers 3456106496 bytes
Redo Buffers 25788416 bytes
SQL> alter database mount standby database;

Database altered.


8. Now issue the command to switch the Physical dr to logical DR :

SQL> alter database recover to logical standby PWRPPRD;

Note :

Please run the below script in the DR side :

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

the above script will show you the logfile for which DR is waiting. After running the logical conversion command. go to
the production database and backup the log file which the above script show and scp it to the new DR and
register it in this side. Do the same method of couple of time until the above command gets altered.

Reason being , the conversion command requires all the log till we made the logical build in the production to recover
and convert it.


Note : At this point the conversion command will change the DB name and also it will update in the Spfile. Also alert log you
will find the below messages .


Converting standby mount to primary mount.
Tue Mar 4 02:41:00 2008
ACTIVATE STANDBY: Complete - Database mounted as primary (PWRPPRD1)
*** DBNEWID utility started ***
DBID will be changed from 505738321 to new DBID of 2131768892 for database PWPRD
DBNAME will be changed from PWPRD to new DBNAME of PWRPPRD
Starting datafile conversion
Setting recovery target incarnation to 1
Datafile conversion complete
Database name changed to PWRPPRD.
Modify parameter file and generate a new password file before restarting.
Database ID for database PWRPPRD changed to 2131768892.
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 ***
Tue Mar 4 02:41:01 2008
SUCCESS: diskgroup RPT_DATA was dismounted
Tue Mar 4 02:41:01 2008
Completed: alter database recover to logical standby PWRPPRD

9. Make sure you alter the cluster database instance paramter to 2 and also cluster database to true before you shutdown .


alter system set cluster_database=TRUE scope=spfile sid='*';

10 . Bounce the logical standby and open it in reset logs mode.

11. Start shipping the logs from production side and start the logical real time apply.

Note : please add standby logfiles at logical standby side. To make real time apply.