We're here to help!

Exclude CREATE TABLE for a specific schema

Follow

As the documentation states ... We maintain the list of tables/table name patterns that should not be automatically prepared when created on source, although DDL is enabled and whole schema is prepared. The main use case for this feature is to skip various temporary tables that should not be replicated or to cease replicating the DDL CREATE TABLE for one (or more) schemas. Use the % character as a wildcard. Underscore (_) is not treated as a special character.

In console to exclude replicating DDL CREATE TABLE for the REPTEST1 schema..

dbvrep> EXCLUDE CREATE TABLE reptest1.%
dbvrep> exclude create table show List of excluded (included) tables: EXCLUDE CREATE TABLE CLEAR EXCLUDE CREATE TABLE %.DBMS_TABCOMP_TEMP_UNCMP EXCLUDE CREATE TABLE %.DBMS_TABCOMP_TEMP_CMP EXCLUDE CREATE TABLE %.SCHEDULER$_% EXCLUDE CREATE TABLE %.CMP1$% EXCLUDE CREATE TABLE %.CMP2$% EXCLUDE CREATE TABLE %.CMP3$% EXCLUDE CREATE TABLE %.CMP4$% EXCLUDE CREATE TABLE REPTEST1.% 8 rule(s) listed.

The commands add a new rule; if an existing rule of opposite meaning already exists, it is deleted instead. The matching starts with the oldest one and goes on till the newest rule added; if a reordering is needed, clear the list and start again. The SHOW option lists the existing rules using the very same syntax, so the list can be easily edited and then executed to APPLY the changes.

This command requires a restart of the MINE process.

 

So now lets test.  Add a table (TEST1) to source database

source db: 
SQL> create table test1 (n number primary key);

Table created.

And also add the table to target database

target db:

SQL> create table test1 (n number primary key);

Table created.

 

Add the table (reptest1.test1)  by preparing it in the Dbvisit console: 

dbvrep> prepare table reptest1.test1 
Prepare enabled supplemental logging, waiting for SCN advance enough to prevent ORA-01466 during consistent export....Waited 2 seconds until scn_to_timestamp changed. 
Connecting to running apply [REPTEST1.TEST1]: [Apply table removed (0 metadata record(s)). Apply table added (1 metadata record(s)).] 
Connecting to running mine [REPTEST1.TEST1]: [Table prepared (1 internal records).] 
Table REPTEST1.TEST1 instantiated at SCN 1578820

source db: 
SQL> insert into test1 values (1);

1 row created.

SQL> commit;

Commit complete. 


target db: 
SQL> select * from test1;


---------- 
1

So replication was successful. 
dbvisit console:

dbvrep> shutdown mine 
Upon restart MINE will go back to redolog 57 (thread 1), going back 1 log. 
Are you sure you want to shutdown? (Yes/No) [No] yes 
Dbvisit Replicate MINE process shutting down. 
dbvrep> exit 
[oracle@rac1 ~]$ /home/oracle/Documents/reptest1/reptest1-run-rac1.mlg.oracle.com.sh 
Initializing......done 
DDC loaded from database (405 variables). 
Dbvisit Replicate version 2.8.04 
Copyright (C) Dbvisit Software Limited. All rights reserved. 
DDC file /home/oracle/Documents/reptest1/reptest1-MINE.ddc loaded. 
Starting process MINE...started

You can also verify that the newly created table is prepare into replication in the console

dbvrep> list prepare
List of prepared schemas:
NICKY (DDL)
List of prepared tables:
DBVREP.DBRSCOMMON_HEARTBEAT -> DBVREP (schema rename)
DBVREP.DBRSOBJ$ -> DBVREP (schema rename)
DBVREP.DBRSUSER$ -> DBVREP (schema rename)
NICKY.MixedCase (DDL)
NICKY.REPTEST1 (DDL)
NICKY.REPTEST2 (DDL)
NICKY.REPTEST3 (DDL)
NICKY.REPTEST4 (DDL)
NICKY.TEST1 (DDL)
NICKY.VIEW (DDL)
NICKY.space table (DDL)

 

Have more questions? Submit a request

Comments