13.5.1.Transactional Replication Objects Example

Preconditions

publisher named 'rep' with replication address 'localhost:1111'
subscriber named 'sub'
on subscriber should be defined DSN for rep named '1111'

On publisher side. Creating publication

SQL> REPL_PUBLISH ('pub', 'pub.log');

-- Add row in SYS_REPL_ACCOUNTS table
SQL> select SERVER, ACCOUNT from SYS_REPL_ACCOUNTS
        where ACCOUNT = 'pub';

SERVER ACCOUNT
--------------
rep    pub
started new replication log file in server working directory named 'pub.log'
add entry in repl.cfg in server working directory
SQL> REPL_PUB_ADD ('pub', 'DB.DBA.TEST', 2, 0, null);
SQL> select * from SYS_TP_ITEM;

TI_SERVER TI_ACCT TI_TYPE TI_ITEM     TI_OPTIONS TI_IS_COPY
___________________________________________________________
rep       pub     2       DB.DBA.TEST NULL       0

Adding items to the publication

Add row in SYS_TP_ITEM

On Subscriber Side. Adding a new publisher

SQL> REPL_SERVER ('rep', '1111', 'localhost:1111');

-- Add row in SYS_SERVERS

SQL> select * from SYS_SERVERS;

SERVER                    DB_ADDRESS                REPL_ADDRESS
----------------------------------------------------------------
rep                       1111                      localhost:1111
   SQL> REPL_SUBSCRIBE ('rep', 'pub', 'dav', 'dav_group', 'uid_for_rep', 'uid_pwd_for_rep');
'publication and subscription servers have identical names.' if subscriber and publisher have the same names.
'Publishing server must be declared with REPL_SERVER before subscribing' if publisher 'rep' is not defined from previous step.
'The subscription 'pub' already exist' if on subscriber 'sub' already exists subscription 'pub'
'User name and password should be supplied when subscribe to new publisher' if 'uid_for_rep' or 'uid_pwd_for_rep' not supplied
'The table 'DB.DBA.TEST' already exists' if on subscriber 'sub' already exist table in subscription.
'The WebDAV collection '/DAV/rep/' already exists' if on 'sub' already exist WebDAV collection in subscription.
a VDB error message if subscriber 'sub' cannot perform attaching TI_ITEM view from publisher 'pub'
SQL> select * from SYS_TP_ITEM where TI_ACCT = 'pub'

TI_SERVER TI_ACCT TI_TYPE TI_ITEM    TI_OPTIONS TI_IS_COPY TI_DAV_USER TI_DAV_GROUP
____________________________________________________________________________________
rep       pub     2       DB.DBA.TEST NULL       0           dav       dav_group

SQL> select SERVER, ACCOUNT from SYS_REPL_ACCOUNTS where ACCOUNT = 'pub';

SERVER ACCOUNT
--------------
rep    pub

Making a subscription

This may signal an SQL error if a precondition is not met.

Add row in SYS_TP_ITEM and row in SYS_REPL_ACCOUNTS