Oracle Replication FAQ

$Date: 23-May-2002 $
$Revision: 2.02 $
$Author: Frank Naudé $

Topics

Replication can improve performance and increase availability of applications because alternate data access options becomes available. For example, users can access a local database rather than a remote server to minimize network traffic. Furthermore, the application can continue to function if parts of the distributed database are down as replicas of the data might still accessible.

Basic replication is implemented using standard CREATE SNAPSHOT or CREATE MATERIALIZED VIEW statements. It can only replicate data (not procedures, indexes, etc), replication is always one-way, and snapshot copies are read only.

Advanced replication supports various configurations of updateble-snapshot, multi-master and update anywhere replication. It is more difficult to configure but allows data and other database objects like indexes and procedures to be replicated.

MASTER SITE:

        SQL> create materialized view log on table EMP;

        SQL> create materialized view emp
               refresh fast with primary key
               start with sysdate
               next  sysdate + 1/(24*60)
               as (select * from emp);

• Data (obviously)
• Triggers, views, indexes, synonyms, etc. (with advanced replication)

• Sequences (Sequences needs to be created on each site to generate mutually exclusive sets of sequence numbers).
• LONG and LONG RAW data types (Use LOBs instead)

The surrogate user is thus an optional replacement user for SYS. To make an existing user a surrogate, execute the following API call:

        SQL> EXECUTE DBMS_REPCAT_AUTH.GRANT_SURROGATE_REPCAT(userid => 'surrogate_user_name');

• Run ?/rdbms/admin/catrep.sql as user SYS AS SYSDBA (or INTERNAL).
• The SID for each database must be unique.
• INIT.ORA parameters JOB_QUEUE_PROCESSES and JOB_QUEUE_INTERVAL need to be set if you want to schedule replication automatically.
• GLOBAL_NAMES must be set to TRUE if you are using the Oracle Replication Manager GUI.
• One can use the OEM Replication Manager to configure replication or by issuing PL/SQL API calls. Look at these sample Replication Scripts.

• Broken jobs (select job, broken from sys.dba_jobs)
• Check sys.dba_repcatlog for outstanding admin requests
• Check for replication errors (select * from sys.deferrcount/ deferror)
• Etc.

• JOB_QUEUE_INTERVAL not set or set to a too large value
• Push Job not defined or is broken
• Push job's run interval is too large
• DB Links are down

Oracle attempts to resolve the conflict by using the conflict resolution method that you specified when you first replicated the table. You can choose from among several Oracle-supplied conflict resolution methods (such as, latest timestamp, site priority, additive, etc.) or you can write your own. If you do not supply a method, or if the method that you supply cannot successfully resolve the conflict, Oracle logs the conflicting transaction in an error queue at the local site. The replication administrator can then decide how best to resolve the conflict.

In addition to replicating the objects that you specify as part of your replication group, Oracle also replicates the replication catalog to each site. The replication catalog is a set of tables that determine which objects are being replicated, where they are being replicated, how often their changes are propagated, and so on. Replicating the replication catalog ensures that there is no single point of failure in a replicated environment.

- DBMS_REPCAT.ADD_UPDATE_RESOLUTION - NO_DATA_FOUND
- DBMS_REPCAT.ADD_DELETE_RESOLUTION - DUP_VAL_ON_INDEX
- DBMS_REPCAT.ADD_UNIQUE_RESOLUTION - TOO_MANY_ROWS
- Add user defined conflict resolution

        execute DBMS_REPCAT.RELOCATE_MASTERDEF( -
                GNAME                 => 'BANKING',
                OLD_MASTERDEF         => 'NYBANK.WORLD',
                NEW_MASTERDEF         => 'HKBANK.WORLD',
                NOTIFY_MASTERS        => TRUE,
                INCLUDE_OLD_MASTERDEF => TRUE);

Let's say we need to update a table and set column x=1 where x=9. When the first change occurs on the source database, Oracle checks to ensure that x=9. Once this transaction commits, it is queued for execution on the destination site(s). When the transaction is applied to the destinations, Oracle first check to verify that x=9 (the old value); if it does, then Oracle applies the update (update table set x = 1 where x = 9); if it does not, an entry to the DEFERROR table is logged indicating ORA-1403.

If the application is updating primary key values, one can also expect plenty "NO DATA FOUND" errors. Primary keys should NEVER be updated. If one updates primary key values, conflict resolution also becomes extremely difficult.

        SQL> connect repadmin/repadmin
        SQL> execute dbms_defer_sys.delete_error(NULL,NULL);
        SQL> commit;   

        SQL> spool apply_errors.sql
        SQL> select 'exec dbms_defer_sys.execute_error(''' || deferred_tran_id ||''','''||
        SQL>        destination || ''')'
        SQL> from   deferror;
        SQL> spool off
        SQL> @apply_errors
        SQL> commit;
		
        SQL> spool delete_errors.sql
        SQL> select 'exec dbms_defer_sys.delete_error(''' || deferred_tran_id ||''','''||
        SQL>        destination || ''')'
        SQL> from   deferror;
        SQL> spool off
        SQL> @delete_errors
        SQL> commit;

        SQL> exec DBMS_REPUTIL.REPLICATION_OFF;
        SQL> insert into tabX select * from tabX@remote MINUS select * from tabX;
        SQL> commit;
        SQL> exec DBMS_REPUTIL.REPLICATION_ON;		  

This problem can be solved by using the DBMS_REPUTIL.FROM_REMOTE function. Look at this example:

        IF dbms_reputil.from_remote = true THEN  -- Trigger was already fired on a remote DB
           return;                               -- Exit the trigger
        END IF;

• Must have 1 master definition site for each replication group
• Ensure triggers don't fire during replication by testing DBMS_SNAPSHOT.I_AM_A_REFRESH before executing a trigger body.
• There are 12 conflict resolution methods. None will be enabled by default.

• Advanced Replication Scripts - This site

• With redolog based replication, transactions are replicated even before they are committed in Oracle! If you thought this is not possible, visit Quest Software and look around for information about their SharePlex for Oracle product.

• Ever wondered how Advanced Replication works? Check out how easy it is with Nico Booyse's replication scripts.

• Setting up a Symmetric Replication environment.