Question

I want to replicate 700 schemas of my source database. Is there a quick method to specify this in the setup wizard or do I have to manually write a comma separated list of 700 schema names?

Answer

Unfortunately there is no single keyword in Dbvisit Replicate that specifies that you want to replicate all schemas except SYS and SYSAUX. You can, however, implement the following approach to avoid manually writing a comma separated list of 700 schemas.

1. Create a text file listing the 700 schema names that you want to replicate. The following SQL query might simplify this:

   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');

2. Use the following script to generate ENGINE LOCK SCHEMAS and PREPARE OFFLINE SCHEMA commands for the schemas. This script will generate a text file containing these commands. You can then replace these two commands in the .dbvrep script with the commands generated by the script. This script will also create a create_target_schema.sql file containing the following commands for the schemas:

   create user schemaname identifed by schemaname
   grant resource,connect to schemaname
       

   Please edit the script and specify tablespaces and grants as per your environment. You can use the create_target_schema.sql script to create target schemas quickly. Unfortunately this approach is not suitable in a real-time environment.

   #!/bin/bash
   #PURPOSE: Read list of schemas and generate PREPARE OFFLINE SCHEMA commands for .dbvrep script
   
   FILENAME=$1
   count=0
   schemalist=''
   
   if [ -f edition_to_dbvrep_script.txt ] #deleting edition_to_dbvrep_script.txt file if already exists
   then
           rm -f edition_to_dbvrep_script.txt
   fi
   
   if [ -f create_target_schema.sql ] #deleting create_target_schema.sql file if already exists
   then
           rm -f create_target_schema.sql
   fi
   
   #reading schema names from filename passed as argument and generating commands for .dbvrep script
   #generating sql script to create target schemas. please modify the script as per your environment.
   echo "PREPARE OFFLINE SCHEMA COMMANDS" >> edition_to_dbvrep_script.txt
   echo "-------------------------------" >> edition_to_dbvrep_script.txt
   while read LINE
   do
           let count++
           echo "PREPARE OFFLINE SCHEMA $LINE" >> edition_to_dbvrep_script.txt
           echo "create user $LINE identifed by $LINE" >> create_target_schema.sql
           echo "grant resource,connect to $LINE" >> create_target_schema.sql
           schemalist="$schemalist $LINE"
   done < $FILENAME
   
   echo "-------------------------------" >> edition_to_dbvrep_script.txt
   echo "ENGINE LOCK SCHEMAS COMMAND" >> edition_to_dbvrep_script.txt
   echo "-------------------------------" >> edition_to_dbvrep_script.txt
   echo "ENGINE LOCK SCHEMAS$schemalist" >> edition_to_dbvrep_script.txt
   
   echo "Total $count schema names read"

So, the approach is that in the setup wizard you specify only one schema. Then, before executing the *-all.sh script, you edit the .dbvrep script to include the 700 schemas. The above script assists you to create the .dbvrep script quickly.

Example

In my test environment I first configured Dbvisit Replicate for schema TEST1. However, I wanted the following schemas replicated:

AVI1, AVI2, AVI3, AVI4, AVI5, TEST1, TEST2, TEST3, TEST4, TEST5

Running the above script generated the edition_to_dbvrep_script.txt file with following output:

PREPARE OFFLINE SCHEMA COMMANDS
-------------------------------
PREPARE OFFLINE SCHEMA AVI1
PREPARE OFFLINE SCHEMA AVI2
PREPARE OFFLINE SCHEMA AVI3
PREPARE OFFLINE SCHEMA AVI4
PREPARE OFFLINE SCHEMA AVI5
PREPARE OFFLINE SCHEMA TEST1
PREPARE OFFLINE SCHEMA TEST2
PREPARE OFFLINE SCHEMA TEST3
PREPARE OFFLINE SCHEMA TEST4
PREPARE OFFLINE SCHEMA TEST5
-------------------------------
ENGINE LOCK SCHEMAS COMMAND
-------------------------------
ENGINE LOCK SCHEMAS AVI1 AVI2 AVI3 AVI4 AVI5 TEST1 TEST2 TEST3 TEST4 TEST5

It also generated the create_target_schema.sql file with following commands:

create user AVI1 identifed by AVI1
grant resource,connect to AVI1
create user AVI2 identifed by AVI2
grant resource,connect to AVI2
create user AVI3 identifed by AVI3
grant resource,connect to AVI3
create user AVI4 identifed by AVI4
grant resource,connect to AVI4
create user AVI5 identifed by AVI5
grant resource,connect to AVI5
create user TEST1 identifed by TEST1
grant resource,connect to TEST1
create user TEST2 identifed by TEST2
grant resource,connect to TEST2
create user TEST3 identifed by TEST3
grant resource,connect to TEST3
create user TEST4 identifed by TEST4
grant resource,connect to TEST4
create user TEST5 identifed by TEST5
grant resource,connect to TEST5

I can now replace the ENGINE LOCK SCHEMAS TEST and PREPARE OFFLINE SCHEMA TEST commands with the above commands in the .dbvep script. I can then execute create_target_schema.sql on the target server and run the *-all.sh script.