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:
ENGINE SUPPLEMENTAL LOGGING SCHEMA ------------------------------- ENGINE SUPPLEMENTAL LOGGING SCHEMA AVI1 ENABLE PRIMARY KEY ENGINE SUPPLEMENTAL LOGGING SCHEMA AVI2 ENABLE PRIMARY KEY ENGINE SUPPLEMENTAL LOGGING SCHEMA AVI3 ENABLE PRIMARY KEY ENGINE SUPPLEMENTAL LOGGING SCHEMA AVI4 ENABLE PRIMARY KEY ENGINE SUPPLEMENTAL LOGGING SCHEMA AVI5 ENABLE PRIMARY KEY ENGINE SUPPLEMENTAL LOGGING SCHEMA TEST2 ENABLE PRIMARY KEY ENGINE SUPPLEMENTAL LOGGING SCHEMA TEST3 ENABLE PRIMARY KEY ENGINE SUPPLEMENTAL LOGGING SCHEMA TEST4 ENABLE PRIMARY KEY ENGINE SUPPLEMENTAL LOGGING SCHEMA TEST5 ENABLE PRIMARY KEY
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 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 TEST2 TEST3 TEST4 TEST5
create user AVI1 identified by AVI1 grant resource,connect to AVI1 create user AVI2 identified by AVI2 grant resource,connect to AVI2 create user AVI3 identified by AVI3 grant resource,connect to AVI3 create user AVI4 identified by AVI4 grant resource,connect to AVI4 create user AVI5 identified by AVI5 grant resource,connect to AVI5 create user TEST2 identified by TEST2 grant resource,connect to TEST2 create user TEST3 identified by TEST3 grant resource,connect to TEST3 create user TEST4 identified by TEST4 grant resource,connect to TEST4 create user TEST5 identified by TEST5 grant resource,connect to TEST5
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.