Replicating Many Schemas with Dbvisit Replicate

I came across a requirement from one of our prospects last week - a database migration project - where they wanted to replicate 700 schemas from an Oracle 10g database to Oracle 11gR2. All of this can be fulfilled by Dbvisit Replicate, our real-time replication solution, and Golden Gate alternative. The intuitive setup wizard of Dbvisit Replicate makes the configuration task easy for the user, and in the setup wizard itself you specify the comma separated list of schemas, which are to be replicated to the target database.

However, the prospect found this something of a monotonous task as they were looking to replicate 700 schemas in the step 3 of setup wizard, and so asked about a quick and easy workaround for this part of their POC.

In this blog, I want to share the workaround offered by the Dbvisit team. So if you are planning to migrate or replicate a database with many (hundreds) of schemas, this information can be useful for you.

When running Dbvisit Replicate’s setup wizard the output of this process is the creation of configuration files and scripts. One of them is the *-setup.dbvrep (* represents the replication name) script, which is internal to Dbvisit Replicate, and this script is executed by *-all.sh script that actually loads replication metadata into the source database and sets a starting point for the replication.

Workaround: In a nutshell the workaround is to specify only one schema in the setup wizard and then edit the *-setup.dbvrep script before running the *-all.sh script. There are the following three commands required for each schema to be included in the replication.

ENGINE SUPPLEMENTAL LOGGING SCHEMA TEST1 ENABLE PRIMARY KEY

PREPARE OFFLINE SCHEMA <schemaname>

ENGINE LOCK SCHEMAS <list of schemas>

For 700 schemas there are 700 ENGINE SUPPLEMENTAL LOGGING SCHEMA and PREPARE OFFLINE SCHEMA commands required in the *-setup.dbvep script, whereas there is only one ENGINE LOCK SCHEMAS command needed for the entire list of 700 schemas. After specifying only one schema in the setup wizard you can use the script provided in this blog below to generate the above listed commands for the remaining 699 schemas. The script reads schema-names from a file line by line, and this is a file which you have to create manually. The following select statement can be used for this purpose:

select username from dba_users where username not in
('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS','LBACSYS','MDSYS','MGMT_VIEW','OLAPSYS','ORDDATA','OWBSYS','ORDPLUGINS','ORDSYS','OUTLN','SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM','WK_TEST','WKSYS','WKPROXY','WMSYS','XDB','FLOWS_020000','FLOWS_FILES','HTMLDB_PUBLIC_USER','APPQOSSYS','APEX_030200','OWBSYS_AUDIT','MDDATA','DIP','APEX_PUBLIC_USER','SCOTT','SPATIAL_CSW_ADMIN_USR','ORACLE_OCM','SPATIAL_WFS_ADMIN_USR','XS$NULL');

Once the file with all schema names is ready you can then execute the following script.

#!/bin/bash
#PURPOSE: Read list of schemas and generate PREPARE OFFLINE SCHEMA commands for .dbvrep script
#USAGE: bash generate_scipt.sh 
#This script reads schema names from file passed as argeument and generates commands for .dbvrep script
#It generate four script files. Three .dbvrep files and one .sql file. 

FILENAME=$1
count=0
schemalist=''

if [ -f edition_to_dbvrep_script1.dbvrep ] #deleting edition_to_dbvrep_script1.dbvrep file if already exists
then
        rm -f edition_to_dbvrep_script1.dbvrep
fi

if [ -f edition_to_dbvrep_script2.dbvrep ] #deleting edition_to_dbvrep_script2.dbvrep file if already exists
then
        rm -f edition_to_dbvrep_script2.dbvrep
fi

if [ -f edition_to_dbvrep_script3.dbvrep ] #deleting edition_to_dbvrep_script3.dbvrep file if already exists
then
        rm -f edition_to_dbvrep_script3.dbvrep
fi

if [ -f create_target_schema.sql ]  #deleting create_target_schema.sql file if already exists
then
        rm -f create_target_schema.sql
fi

echo "ENGINE SUPPLEMENTAL LOGGING SCHEMA" >> edition_to_dbvrep_script1.dbvrep
echo "-------------------------------" >> edition_to_dbvrep_script1.dbvrep

echo "PREPARE OFFLINE SCHEMA COMMANDS" >> edition_to_dbvrep_script2.dbvrep
echo "-------------------------------" >> edition_to_dbvrep_script2.dbvrep
while read LINE
do
        let count++
        echo "ENGINE SUPPLEMENTAL LOGGING SCHEMA $LINE ENABLE PRIMARY KEY" >> edition_to_dbvrep_script1.dbvrep
        echo "PREPARE OFFLINE SCHEMA $LINE" >> edition_to_dbvrep_script2.dbvrep
        echo "create user $LINE identified by $LINE" >> create_target_schema.sql
        echo "grant resource,connect to $LINE" >> create_target_schema.sql
        schemalist="$schemalist $LINE"
done < $FILENAME 

echo "ENGINE LOCK SCHEMAS COMMAND" >> edition_to_dbvrep_script3.dbvrep
echo "-------------------------------" >> edition_to_dbvrep_script3.dbvrep
echo "ENGINE LOCK SCHEMAS$schemalist" >> edition_to_dbvrep_script3.dbvrep

echo "Total $count schema names read"
echo "------------------------------"
echo "Next Steps"
echo "------------------------------"
echo "1. Edit the *-serup.dbvrep file to add ENGINE SUPPLEMENTAL LOGGING SCHEMA, PREPARE OFFLINE SCHEMA and ENGINE LOCK SCHEMAS commands"
echo "2 Use create_target_schema.sql script to create target schemas. Make sure you have correctly specified tablespaces anf grants"
echo "3 Run the *-all.sh script"

This script will create four files. Three files contain dbvrep script commands that you then copy and paste into the actual *-setup.dbvrep script. As we have proposed this workaround for a POC we included one more sql script with create user and grant commands to make the creation of the target environment quick, as it is required to have target schemas ready before you run the *-all.sh script. You can edit the above script and specify default and temporary tablespaces and grants. Example: I have first configured Dbvisit Replicate for schema TEST1 whereas I also wanted to have following schemas replicated: AVI1 AVI2 AVI3 AVI4 AVI5 TEST1 TEST2 TEST3 TEST4 TEST5 I have created a file called schemalist.txt with following schema names : AVI1 AVI2 AVI3 AVI4 AVI5 TEST2 TEST3 TEST4 TEST5 Running the script (detailed above) has generated the following files: edition_to_dbvrep_script1.dbvrep edition_to_dbvrep_script2.dbvrep edition_to_dbvrep_script3.dbvrep and create_target_schema.sql files with following content. edition_to_dbvrep_script1.dbvrep:

edition_to_dbvrep_script2.dbvrep:

edition_to_dbvrep_script3.dbvrep:

create_target_schema.sql:

Summary Editing the *-setup.dbvrep script followed by executing *-all.sh script are the next steps required to complete the configuration. The script also prints these next steps. I hope you find this information useful in helping you configure Dbvisit Replicate for source database with many schemas. Try Dbvisit Replicate out for yourself, and see how easy it is to configure and run.