Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 969
  • Last Modified:

Oracle 9i replication server information

I have a few questions about Oracle 9i replication server. The servers would be a 2 different locations for performance and recovery reasons connected via a VPN tunnel with no latency.

- Can both servers be read/write?
- What is the min time they can be insync so both have the same data?
- Any known problems with this type of setup?
1 Solution
1. It depends from the Replication Method you choose. If it is is based on materialized Views (former known as Snapshots) the simpliest solution is to use non updatable views. But if you want you can use the complex solution - updatable views. Synchronisation is complex, of course.
2. The time of synchronization is up to you. You choose it depending on the volatility of your tables, the business rules and the speed of the net. there are two kinds of refreshing - fast and full.
3. About issues: they are many. The following text can give you some orientation.

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 accessible.
·  Back to top of file
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. Contact these companies for more information about their respective product offerings.
·  Back to top of file
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.
·  Back to top of file
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.
·  Back to top of file
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:
        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);
·  Back to top of file
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)
·  Back to top of file
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.
·  Back to top of file
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');
·  Back to top of file
How does one set 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. Look at these sample Replication Scripts.
·  Back to top of file
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.
·  Back to top of file
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);.
·  Back to top of file
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.
·  Back to top of file
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.
·  Back to top of file
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.
·  Back to top of file
How does one resolve replication conflicts?
Use the supplied packages:
- Add user defined conflict resolution
·  Back to top of file
How does one relocate the master definition site to a different location?
                GNAME                 => 'BANKING',
                OLD_MASTERDEF         => 'NYBANK.WORLD',
                NEW_MASTERDEF         => 'HKBANK.WORLD',
                NOTIFY_MASTERS        => TRUE,
                INCLUDE_OLD_MASTERDEF => TRUE);
·  Back to top of file
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).
·  Back to top of file
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 plenty "NO DATA FOUND" errors. Primary keys should NEVER be updated. If one updates primary key values, conflict resolution also becomes extremely difficult.
·  Back to top of file
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;
·  Back to top of file
Can one switch off replication while fixing replication errors?
One can switch off replication for a session. This is handy when one needs to fix replication problems. Look at this example:
        SQL> insert into tabX select * from tabX@remote MINUS select * from tabX;
        SQL> commit;
        SQL> exec DBMS_REPUTIL.REPLICATION_ON;              
·  Back to top of file
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;
·  Back to top of file
Any replication notes?
·      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.
·  Back to top of file
Where can one get more info about replication?
·      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

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now