Getting Creative With Dbvisit Replicate Scripts

A few customers have asked me about scripting and other ways of interacting with Dbvisit Replicate. Here are two of my favorite ways. In prior versions of Dbvisit Replicate you would have had to list out all the tables that you wanted. This could become cumbersome if you had a schema with 1000 tables and wanted 999 of them. That is now much easier now that the SETUP WIZARD will allow you to select the whole schema and then allow you to decide that you do not want that one table.

There is also another method that you can use. That way is to prepare a regular text file with a list of comma or space separated names. For example you could write a SQL script to query the database and output a list of tables. Once you have that file you can use it as an input file in the SETUP WIZARD. Example:

select owner||’.’||table_name  AS TABLE_LIST
From dba_tables
where owner = ‘SCOTT’;
TABLE_LIST
SCOTT.DEPT
SCOTT.EMP
.

Now place that output into a file called file_example.txt In Step 3 of the SETUP WIZARD you are asked what tables you would like to replicate. Rather than input the answer you can input the filename. Example:

Step 3 - Replicated tables
========================================
The third step is to choose the schemas and tables to be replicated. If the databases are reachable, the tables are checked for existence, datatype support, etc., schemas are queried for tables.
Note that all messages are merely hints/warnings and may be ignored if issues are rectified before the scripts are actually executed.
Following tables are defined for replication pairs:
1: SDB11 (Oracle) ==> TDB11 (Oracle), DDL: YES, suffix: (no suffix), prepare: single-scn
No tables defined.
Enter number of replication pair to modify it, or "done": 
[1]
Please enter list of all individual tables to be replicated. Enter schema name(s) only to replicate all tables in that schema. Use comma or space to delimit the entries.
Enter the tables and schemas: [] @/home/oracle/file_example.txt
Selected tables: SCOTT.DEPT,SCOTT.EMP
Add more tables or schemas? (YES/NO) [NO]

The SETUP WIZARD will read the input file right there and list the tables so that gives you a change to review it. Another example where scripts can help is when you are in the Dbvisit Replicate console. There are many variable parameters that you would set depending on your replication needs. If you are doing testing you may be changing parameters in multiple settings. Rather than make changes one at a time you can use a script to change multiple parameters. Example:

dbvrep> show mailcfg
*.MAILCFG_AUTH_PASSWD = OFF
*.MAILCFG_AUTH_USER = OFF
*.MAILCFG_FROM = nobody@example.com
*.MAILCFG_PORT = 25
*.MAILCFG_SMTP_SERVER = localhost
*.MAILCFG_USE_SSL = NO

You can also run commands such as PAUSE and RESUME or even SHUTDOWN. This will allow you to script things rather than use the console interactively.

Using scripts can be a powerful way to save your favorite commands, keep complex chain of commands all in one file or just a way to be organized. I highly suggest you take a few minutes and experiment with scripting in your test environment.