Advanced Replication FAQ

Oracle Advanced Replication FAQ:

What is replication and why should one use it?

Replication is the process of creating and maintaining replica versions of database objects (e.g. tables) in a distributed database system.

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 be accessible.

Can one replicate between Oracle and non-Oracle databases?

Oracle does not support replication to non-Oracle databases like DB2, Informix, Sybase, SQL Server, etc. Some third-party vendors provide products that can replicate heterogeneous databases. One such company is Sybase another would be GoldenGate Software, which was recently purchased by Oracle. Another one is WisdomForce. Contact these companies for more information about their respective product offering.

Using Transparent Gateway, you can do replication with stream from oracle to non oracle.

What is the difference between BASIC and ADVANCED replication?

Oracle Server supports two different forms of replication: basic and advanced replication.

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.

What is the difference between a snapshot and a materialized view?

They are the same. Keywords "snapshot" and "materialized views" can be used interchangeably. Oracle 8i implements snapshots and materialized views as a single table, previous releases implemented it as a view with an underlying SNAP$_% table.

How does one implement basic snapshot replication?

Start by creating an optional snapshot log on the master database. If you do not want to do fast refreshes, you do not need to create a log. Also note that fast refreshes are not supported for complex queries. Create a snapshot/materialized view on the snapshot site. Look at this example:

MASTER SITE:

       SQL> create materialized view log on EMP;

SNAPSHOT SITE:

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

What object types can and cannot be replicated?

The following can be replicated:

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

The following cannot be replicated:

• 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)

What is the difference between master definition and master destination sites?

If you are planning to implement Master-to-Master Replication, you need to decide which of your Oracle Database servers will become the Master Definition site. The remainder of your servers will become Master Destination sites. Replication support is configured from the Master Definition Site.

Note: You must have 1 master definition site for each replication group.

What questions should one ask before implementing Advanced Replication?

Users tend to think that Advanced Replication can be implemented on any application without having to make application changes. However, not all applications can operate with advanced replication enabled. The list of questions below will help you to establish if advanced replication can be implemented on your application:

• Was the application ever tested with replication enabled? If so, please provide detailed documentation/ installation procedures.

• Does all the tables have primary keys defined? If not, will you be able to identify the surrogate primary keys.

• Does the application generate unique sequences across both sites. If not, this will cause data conflicts.

• At what frequency should the data be replicated.

• Give an indication of the data change rate (network overhead between the sites).

• What database schemas and/or tables should be replicated?

• What will happen if transactions can not be replicated? For example: if the network goes down.

• How will replication conflicts be handled? Will you be able to assist us by writing conflict handlers?

How does one set up multi-master replication?

Steps for setting up multi-master replication:

• 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. Sample scripts are available here.

What is a surrogate repadmin user and how is one created?

If one creates a surrogate replication administrator at a remote site, one does not need to have SYS to SYS database links between the sites. The surrogate replication administrator performs actions on behalf of the symmetric replication facility at the remote site.

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');

How does one monitor replication?

Look for the following:

• 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.

The DBA_REPCATLOG view is not getting empty, what can one do?

The SYS.DBA_REPCATLOG view list pending admin requests. One needs to wait until all admin requests are completed before continuing to configure replication support. Some reasons why this view is not getting empty, or taking a long time to get empty is as follows:

• 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

Manually running the push job will speed up the execution of deferred admin requests. Query the user_jobs view to get the job number, and do a EXEC DBMS_JOB.RUN(jobno);.

What happens if two or more sites change the same data?

If two sites change the same data within the data replication interval, you have an update conflict. The more frequently you propagate your changes (that is, the smaller your replication interval), the fewer update conflicts will occur. Oracle detects update conflicts by comparing the old values for a row from the remote (or propagating) site with the current values at the local (or receiving) site. If they are the same, no conflict has occurred and the new values are applied at the local site. If they are different, a conflict is detected.

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.

What happens if one of the sites is unavailable?

In an asynchronous (store-and-forward) replication environment, local updates are stored in a deferred transaction queue until the remote site becomes available. When the remote site comes back up, these transactions are propagated and applied at the remote site in the order that they were originally applied at the local site. You can continue to make updates at any remaining replication sites.

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.

How does one change the definition of a replicated table?

Oracle advanced replication will replicate DML changes (inserts, updates, and deletes) and all DDL (create, alter, delete) changes made to all master sites for a replication group. So, if you decide to add a column to a replicated table, this column will be added at all of your master sites. Additionally, you can change the members of a replication group, for example, if you decide to add an index to a table, you can choose to have this index replicated to all of your master sites.

How does one resolve replication conflicts?

Use the following supplied packages:

• 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

How does one relocate the master definition site to a different location?

Use DBMS_REPCAT.RELOCATE_MASTERDEF. Example:

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

Note: There are 12 conflict resolution methods. None of them will be enabled by default.

Can sequences be replicated?

No, the best way to handle sequences, assuming you are using them as primary key values, is to concatenate then with something unique to the site. For example, use a sequence number concatenated with the database name, site name or something similar. One could also start the sequences at one site as odd numbers (1, 3, 5, etc) and the other site as even numbers (2, 4, 6 etc).

Can one turn off replication for the current session?

One can switch off replication for a session. This is handy when one needs to fix replication problems.

Syntax:

SQL> exec DBMS_REPUTIL.REPLICATION_OFF;
... do bulk insert/update/delete ...
SQL> exec DBMS_REPUTIL.REPLICATION_ON;

Look at this example:

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;

How does one replicate a TRUNCATE TABLE statement?

TRUNCATE is a DDL statement, and should thus not be issued directly against tables belonging to a replication group. Instead, use the supplied DBMS_REPCAT.EXECUTE_DDL procedure. Here is an example:

BEGIN
 dbms_repcat.execute_ddl(
  gname       => 'group1', -- Query dba_repobject for the gname
  master_list =>  NULL,    -- NULL = all masters
  ddl_text    => 'TRUNCATE TABLE scott.emp');
END;
/

You don't need to quiesce the replication group before runing the above operation.

How does one delete all local def errors?

Before one indiscriminately deletes all errors, understand why they occurred first. It might be better to re-apply all failed transactions. If you still wants to delete all of them, execute the following statements from SQL*Plus:

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

Also look at this slightly more complicated example. This example will first try to re-apply the error before deleting it:

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;

Can one avoid a loop when remote tables are updating each other via triggers?

Triggers can easily cause replication errors and even infinite loops in a replicated environment. This happens as the trigger fire more than once on the same data. For example, consider a trigger that inserts a row into a table. When this row is replicated to a remote database(s), the trigger will fire again on the same data, and re-insert a duplicate row into the table.

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;

Note: Ensure triggers don't fire during replication by testing DBMS_SNAPSHOT.I_AM_A_REFRESH before executing a trigger body.

I get "NO DATA FOUND" errors. How does one handle this?

An ORA-1403 (No Data Found) error is signalled within a replicated environment when there is a conflict in data. Here is an illustration of what happens in most scenarios featuring this error:

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 many "NO DATA FOUND" errors. Primary keys should NEVER be updated. If one updates primary key values, conflict resolution also becomes extremely difficult.

How does one fix ORA-25207 errors?

Problem:

ORA-25207: enqueue failed, queue SYSTEM.DEF$_AQCALL is disabled from enqueueing

Solution:

CONNECT / AS SYSDBA
EXEC DBMS_AQADM.START_QUEUE('SYSTEM.DEF$_AQERROR', TRUE, TRUE);
EXEC DBMS_AQADM.START_QUEUE('SYSTEM.DEF$_AQCALL', TRUE, TRUE);