DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Replication...

  1. #1
    Join Date
    Dec 2001
    Posts
    3

    Thumbs up

    Hi All,

    I need to replicate the existing database at another site far away...So can any one guide me step by step how to do replication between two different sites...

    Pls mail me to nageshtumu@hotmail.com..

    I will be thank ful if u can respond soon..


    Thanq..

  2. #2
    Join Date
    Jan 2001
    Posts
    153
    Oracle Replication FAQ

    What is replication and why should one use it?
    What is the difference between BASIC and ADVANCED replication?
    What is the difference between a snapshot and a materialized view?
    How does one implement basic snapshot replication?
    What object types can and cannot be replicated?
    What is the difference between master definition and master destination sites?
    What is a surrogate repadmin user and how is one created?
    How does one set up multi-master replication?
    How does one monitor replication?
    The DBA_REPCATLOG view is not getting empty, what can one do?
    What happens if two or more sites change the same data?
    What happens if one of the sites is unavailable?
    How does one change the definition of a replicated table?
    How does one resolve replication conflicts?
    How does one relocate the master definition site to a different location?
    Can sequences be replicated?
    I get "NO DATA FOUND" errors. How does one handle this?
    How does one delete all local def errors?
    Can one avoid a loop when remote tables are updating each other via triggers?
    Any replication notes?
    Where can one get more info about replication?

    --------------------------------------------------------------------------------
    Back to Oracle FAQ Index
    --------------------------------------------------------------------------------

    What is replication and why should one use it?
    Replication is the process of creating and maintaining replica versions of database objects 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 are still accessible.


    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 anyware replication. It is more difficult to configure but allowes 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. It is worth noting that 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 so not want 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:
    create snapshot log on table EMP;

    SNAPSHOT SITE:
    create snapshot 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 datatypes (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:
    - 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


    Back to top of file
    --------------------------------------------------------------------------------

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

    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?

    If the application is updating primary key values, you will get lots of "NO DATA FOUND" errors. Primary keys should NEVER be updated. If you do update primary keys, conflict resolution becomes extremely difficult.

    Back to top of file
    --------------------------------------------------------------------------------

    How does one delete all local def errors?

    From sqlplus say:
    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


    Back to top of file
    --------------------------------------------------------------------------------

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

    You can avoid an infinite loop by using the DBMS_REPUTIL.FROM_REMOTE function. look at this example:
    IF dbms_reputil.from_remote is false THEN
    -- put your orginal trigger code here.
    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

    Visit the Oracle Replication Special Interest Group and join their excellent mailing list.

    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.

    Oracle Replication Configuration

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

    Setting up a Symmetric Replication environment.

    Vijay.s

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width