We're here to help!

Using A READ-ONLY Standby Database To Instantiate a Target Database

Follow

Read our docs The Below Article helps you to INSTANTIATE a Replicate Target Environment using a Read-Only Standby database.


Requirements

Replicate must be installed on all servers involved, Primary, Standby and  Target hosts.
Setup tnsnames aliases to avoid confusion on all the servers involved 

Description

In this example the

      a) Primary database SOURCEDB on server and the entry for the local SOURCEDB plus an alias SSOURCEDB which I use for the setup wizard and is also needed on standby (serverB )& target host (serverB).

       b) the target database TARGETDB has an alias TTARGETDB for the target database running on serverB


My serverA db server tnsnames.ora
# for tns alias for STANDBY replication
SOURCEDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = serverA.usa.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = SOURCEDB)
    )
)
# tns alias for REPLICATE setup
SSOURCEDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = serverA.usa.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = SOURCEDB)
    )
)
# tns alias for REPLICATE setup, target C database
TTARGETDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = serverB.usa.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TARGETDB)
    )
)
  
My target server, serverB db tnsnames. For my test my standby & target are on the same server (serverB).
# Tns alias on TARGET (server B) for STANDBY – no change
SOURCEDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = serverB.usa.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = SOURCEDB)
    )
)

# tns alias for REPLICATE setup
SSOURCEDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = serverA.usa.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = SOURCEDB)
    )
)

# tns alias for REPLICATE setup, target C database
TTARGETDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = serverB.usa.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TARGETDB)
    )
)

Detail Steps for Instantiation:

 1) Pause ship logs & apply logs on both the primary & standby hosts.

 2) For this example I am running setup wizard selecting options (single_scn/exp) for schema SCOTT then will use the standby database when it is in READ-ONLY mode to do the actual export of the initial data.

 3) Run $ dbvrep on the Primary database server

 4) Run through running setup wizard and use aliases for SSOURCEDB tns alias for REPLICATE configuration and target database TARGETDB (tns alias TTARGETDB)

 5) Setup wizard selection was (single-scn, exp) as data pump seemed to try to write the job to the read-only standby so that's out so had to use legacy export (exp not expdp). See Oracle Doc ID 1356592.1 for data_pump(expdp) limitations.

 6) Once you have completed running the setup wizard, exit & run the -all.sh (or -all.bat for Windows) on the Primary host.

 This will create a small schema (dbvisit) in both the primary database & the target database. It also enables supplementally logging on the schema/tables that were selected for replication. In this example it was set for schema owner SCOTT's (ie. SUPPLEMENTAL LOGGING SCHEMA "SCOTT" ENABLE PRIMARY KEY)

 7) After you run the -all.sh (or -all.bat for Windows) on the Primary host manually ship current set of logs from source & apply to standby then open standby for read only.

     If you are using the Dbvisit product Standby these are the below commands.  There are equivalent commands to open the standby in READONLY as well. (Note my DBVISIT standby home is /usr/dbvisit/standby)


On STANDBY server, open standby database for read-only

$ /usr/dbvisit/standby/dbv_oraStartStop open SOURCEDB (for Standby Version 7)

$/usr/dbvisit/standby/dbvctl –d SOURCEDB -o open (for Standby Version 8)

8) Next copy the following scripts to their appropriate hosts. Note I selected SSOURCEDB for the name in this replication, so the files are prefixed with this in their file names. Yours may differ if you have chosen a different replication name, ie PROD , /home/dbvisit/PROD directory location.

 

•	Copy *-MINE.ddc to standby server along with the script to start the MINE process
  SSOURCEDB-MINE.ddc
  SSOURCEDB-run-serverA.usa.com.sh  
  start-console.sh
  
•	Copy *-APPLY.ddc to target server along with the script to start the APPLY process & the instantiation script (APPLY.sh)
  SSOURCEDB-APPLY.ddc
  SSOURCEDB-run-serverB.usa.com.sh 
  APPLY.sh 

•	I'm going to run console from target host so copy over 'start-console.sh' to target server

9) Disable cronjobs on both primary & standby for shipping & applying logs of Standby.

 Next we need to correct the flashback_scn number in the APPLY.sh script before we run the export on the standby

Query the open standby: 
SQL> select max(scn) from smon_scn_time;
  MAX(SCN)
----------
  25095860

On the primary database run the below query to validate this timestamp

SQL> set linesize 200
SQL> select scn_to_timestamp(25095860) v_database_scn, scn_to_timestamp(25095860) smon_scn_time from dual ;

V_DATABASE_SCN								    SMON_SCN_TIME
----------------------------------------------- ----------------------------------------------------
12-FEB-15 08.27.13.000000000 AM 				12-FEB-15 08.27.13.000000000 AM

 Update the export flashback_scn in the export portion of the APPLY.sh script with this MAX(SCN) number you got when you ran the –all.sh (or all.bat) script on the primary database. Remember for this example I selcted (single_scn/exp) for schema SCOTT.

 Note that your tns alias should be pointing to the open standby database, ie SOURCEDB the open standby not the SSOURCEDB alias to primary database. Please verify.

The  export script (APPLY.sh) on my target server . The MAX(SCB,  25095860) gotten from above queries on read-only standby should be updated in the export command line

 

$ exp SYSTEM/@SOURCEDB file=SCOTT_MixedCase.dmp flashback_scn=25095860 tables=SCOTT.\"MixedCase\",SCOTT.REPTEST1,SCOTT.REPTEST2,SCOTT.REPTEST3,SCOTT.REPTEST4,SCOTT.VIEW,SCOTT.\"space table\" log=exp_SCOTT_MixedCase.log STATISTICS=NONE

There is no change to the import portion of the script.
$ imp SYSTEM/@TTARGETDB file=SCOTT_MixedCase.dmp ignore=y full=y log=imp_SCOTT_MixedCase.log

10) Next start up console from target host WITHOUT starting MINE nor APPLY & change below.

 NOTE : The Standby Database will not have the archive logs in the FRA, but in the Dbvisit Standby ARCHDEST. If you are not using Dbvisit Standby you will need find the location of your archivelog directory on your standby where the standby is applying them.

 Start console on original source server & perform the below.

dbvrep> set REDO_READ_METHOD= ARCHONLY
dbvrep> set MINE.MINE_REMOTE_INTERFACE = serverB.usa.com:7901   
   NOTE : this is where the standby server is; for my test they reside on same server as target db)

dbvrep> set MINE_ARCH_DEST = /u03/oracle/oraarch/SOURCEDB
    NOTE: if you are using Dbvisit Standby these will be located in your ARCHDEST directory

dbvrep> set MINE_ARCH_DEST_FORMAT = %t_%s_%r.arc

Example of starting up console to make the above changes:

Dbvisit Replicate 2.8(MAX edition) - Evaluation License expires in 30 days
Could not connect to MINE process. Process not started or connection refused.
Could not connect to APPLY process. Process not started or connection refused.
Progress of replication SOURCEDB:MINE->APPLY: total/this execution (stale)
--------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------
0 tables listed.

dbvrep> set REDO_READ_METHOD=ARCHONLY                                                                                                        
Variable REDO_READ_METHOD set to ARCHONLY for process *.

dbvrep> set MINE.MINE_REMOTE_INTERFACE = serverB:7901                                                                                      
Variable MINE_REMOTE_INTERFACE set to serverB:7901 for process MINE.

dbvrep> set MINE_ARCH_DEST = /u03/oracle/oraarch/SOURCEDB                                                                               
Variable MINE_ARCH_DEST set to /u02/app/oracle/oraarch/SOURCEDB for process *.

dbvrep> set MINE_ARCH_DEST_FORMAT = %t_%s_%r.arc                                                                                             
Variable MINE_ARCH_DEST_FORMAT set to %t_%s_%r.arc for process *.

dbvrep> exit  

11) Start MINE & APPLY

NOTE: If your standby is on another host from your target database then you would run MINE on Standby server and APPLY on the Target server. 

If anything errors out or it creates a support package please check the tnsnames.ora file. It is still connecting to the source database to update metadata and connecting to the target database. 

The startup will fail if it tries to connect to the standby database so leave standby in READ-ONLY mode.

 12) For this test I added rows to SCOTT.REPTEST% tables (10 additional rows added) on the primary SOURCEDB followed by committing these changes & switched logs on primary.

 13) On primary db shipped logs to target (manually)

$ /usr/dbvisit/standby/dbvisit SOURCEDB (For Standby Version 7)
$/usr/dbvisit/standby/dbvctl –d SOURCEDB  (For Standby Version 8)

14) Put standby back in recovery mode using Dbvisit Standby commands.

       Once again if you are not using our Standby product use Oracle native commands.

       On the standby server, use Dbvisit Standby commands to shutdown standby & out of read-only mode to restart to be able to recover the logs. If you are using Data Guard you will need to use Oracle commands directly to shutdown & restart standby in managed recovery. 

$ /usr/dbvisit/standby/dbv_oraStartStop restart SOURCEDB (For Standby Version 7)
$/usr/dbvisit/standby/dbvctl –d SOURCEDB -o restart  (For Standby Version 8)

15) Apply logs to standby using Dbvisit Standby commands. Once again if you are not using our Standby product there are native Oracle commands to apply the archivelog to the standby.

$ /usr/dbvisit/standby/dbvisit SOURCEDB (For Standby Version 7)
$/usr/dbvisit/standby/dbvctl –d SOURCEDB  (For Standby Version 8)

 16) Re-enabled cronjobs/Daemon on both primary & standby for shipping & applying logs.

 17) Below are the results of the test I ran that added 10 rows to REPTEST1 through REPTEST4 to validate the configuration. As you see 10 rows were mined and applied to each table in the target database.

 Use the console to monitor either running on source or target host.

Ie,Console Display.. 

Dbvisit Replicate 2.8(MAX edition) - Evaluation License expires in 30 days
MINE IS running. Currently at plog 427 and SCN 0 (02/12/2015 08:51:47).
APPLY IS running. Currently at plog 427 and SCN 25108630 (02/12/2015 08:51:47)
Progress of replication SOURCEDB:MINE->APPLY: total/this execution
--------------------------------------------------------------------------------------------------------------------------------------------
SCOTT.REPTEST1: 100%  Mine:10/10  Unrecov:0/0         Applied:10/10       Conflicts:0/0       Last:12/02/2015 08:53:33/OK
SCOTT.REPTEST2: 100%  Mine:10/10  Unrecov:0/0         Applied:10/10       Conflicts:0/0       Last:12/02/2015 08:53:33/OK
SCOTT.REPTEST3: 100%  Mine:10/10  Unrecov:0/0         Applied:10/10       Conflicts:0/0       Last:12/02/2015 08:53:34/OK
SCOTT.REPTEST4: 100%  Mine:10/10   Unrecov:0/0         Applied:10/10       Conflicts:0/0       Last:12/02/2015 08:53:34/OK
--------------------------------------------------------------------------------------------------------------------------------------------
4 tables listed.
Have more questions? Submit a request

Comments