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

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 SSOURCEDBwhich 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:

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

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

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

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.

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)

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.

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.

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..