.com Solutions Inc. - Logo  
support header buttons
.
FmPro Script Diffbullet 7f FmPro Migrator bullet 7f CGIScripter
.
.
.
. .

Email a Friend

 


.

. .

 

...
.

support technical notes title image

hline f image

Advanced Replication Design and Setup Tips

Is Replication Appropriate?
Advanced Replication is not appropriate for all high availability scenarios. Replication is generally most appropriate for the following situations;
1) The number of replicated tables is modest and manageable (< 1000, not 10,000 or more).
There is no specific limit imposed by Oracle concerning the number of tables which can be replicated.
The main issue will concern whether the number of replicated tables will be manageable by the DBA in a timely manner.
2) The DBA has full control over the design of the tables.
Every replicated table must have a primary key, and additional columns and triggers need added for conflict resolution. Schemas such as Oracle Financials or SAP do not lend themselves to replication because they consist of 10,000 - 20,000 tables, which would represent too many tables for a DBA to manage in a replicated environment. The DBA also does not have full control over the schemas implemented by these applications, and would not be able to safely modify the tables without affecting the reliability or supportability of the application. For these types of applications Clustering or a Standby database configuration (or both) should be considered.
3) Database availability requires a zero-failover timeframe.
The Tnsnames.ora Net8 configuration file can be configured for Transparent Application Failover. TAF allows Net8 to automatically direct database access requests to alternate servers. Standby databases require a manual switchover process to be performed by the DBA in order to recover from a failover, with an additional delay required for the standby database to apply the remaining archivelog files. Users can be manually directed to alternate servers for load balancing purposes one server does not get overloaded.
4) Dropping of a table at one site must not propagate to other sites.
DDL commands do not get replicated unless this feature is specifically enabled. The scripts created by this application do not replicate DDL commands in order to improve reliability due to this type of error. Standby and Clustering solutions always transfer DDL commands between sites. However standby databases can be configured with a delay interval in an attempt to give the DBA the opportunity to prevent the DDL from reaching the standby site.
5) The number of transactions per second is manageable (does not exceed 400 transactions per second). This is a general guideline, which has been mentioned by Oracle, and is very dependent upon the hardware being used for the database servers. Replication generates a significant amount of redo because all of the replicated data is written multiple times. The data gets written into a deferred transaction queue at the source site (causing redo), gets sent out across the network where it then gets queued again (generating more redo) until it is finally applied to the destination database.
6) The data need replicated between servers running differing Oracle versions or differing Operating Systems.
Standby and Clustered databases always require that the servers be running the exact same version of Oracle and the exact same operating system platform and version. Replication however may take place between differing Oracle versions running on different platforms.
7) The database servers need to be physically located far away from each other.
Standby databases can be remotely located from each other, but clustered databases can only be as far away from each other as a short haul fiber optic cable connection (probably not more than about 20 miles).
Replicated databases can also be remotely located as long as the communications link is reliable and fast enough to support the transaction rate which needs supported.

Setup Tips:
1) The use of the Oracle Replication Manager application is not recommended (even by Oracle) for the initial setup of Advanced Replication. Some versions of the Replication Manager application contain a known bug whereby the application logs into the database using the SYSTEM account instead of the SYS account during the setup process. Use of the SYSTEM account does not provide enough priviledges to complete the required tasks, thus causing the setup process to fail. Oracle Worldwide Support recommends that DBAs should build their own scripts to set up replication (the reason for the existence of this application!). Oracle recommends that DBAs should only use the Java-based Replication Manager application to manage replication once it has been setup. Some previous versions (8.1.6 for instance) of the Replication Manager also do not permit for the saving of configuration SQL scripts.
2) When setting up replication on an Oracle 9i database sqlplus must be used, because svrmgrl is no longer available. With previous versions of Oracle it is generally advisable to use svrmgrl when setting up replication in order to avoid a small number of issues with sqlplus when performing DBA tasks such as shutting down or starting up databases.
3) Forward and reverse DNS must be setup for each server hostname in order for the database servers to be registered into the OEM repository. The servers must be registered into the OEM repository to allow the Replication Manager to manage the servers.

Performance:
1) If 2 master sites are located physically close to each other, then dedicating a network interface for the exclusive use of Replication is recommended. Even if master sites are not physically close together may benefit from the separation of replication traffic on a different interface for non-replication related access to the database.
2) Most UNIX systems will not route TCP/IP traffic to a 2nd network interface unless it is assigned an IP address in another network or subnet. For instance if the primary network interface used an IP address of 10.1.0.10 with a subnet of 255.255.255.0, then the 2nd network interface could potentually use an IP address of 10.2.0.10 with a subnet of 255.255.255.0. However if the 2nd network interface were assigned an IP address of 10.1.0.11, then it would not appear to function because all of the network traffic would get sent thru the 1st network interface.
The network administrator should be consulted prior to assigning IP addresses or subnetes in order to insure that the site's security policy is followed and to insure that access lists in switches, routers and firewalls are updated properly.

Batch Operations:
1) Oracle recommends that batch transactions which insert or update more than a few hundred records should be performed with "Procedural Replication". Procedural Replication is Oracle's term for running a DBA-created PL/SQL stored procedure at each master site without allowing the changes to propagate via replication to the other sites. Within the PL/SQL procedure the DBA should first execute the DBMS_REPUTIL.REPLICATION_OFF procedure before performing DML operations on replicated tables. The DBMS_REPUTIL.REPLICATION_OFF procedure disables the replication process during the current session. After the DML operations have been completed, the DBA should execute the DBMS_REPUTIL.REPLICATION_ON procedure to re-enable replication within the session.
This process needs to be repeated at each of the remaining master sites in order to insure that the data is in sync.
2) In addition to executing the DBMS_REPUTIL.REPLICATION_OFF procedure the DBA should also disable the replication-related trigger on the date_modified column of the table. Triggers do not execute within the same user session, the replication process will take place based upon the modified data and this will then cause data to be replicated to the other sites during the batch operation.

Conflict Resolution:
1) Conflict resolution is an often-overlooked but important part of any Advanced Replication setup. If data updates occur to the same record simultaneously at more than one site, a conflict will occur. Oracle will automatically handle the resolution of these types of conflicts if conflict resolution methods are designed into the replication process. The scripts generated by Repgen automatically implement 3 conflict resolution methods on every replicated table because Oracle recommends a minimum of 2 conflict resolution methods.
The 1st conflict resolution method is the LATEST TIMESTAMP method. This method retains the most recently modified data as being the most up-to-date information.
The 2nd conflict resolution method is SITE PRIORITY. If the LATEST TIMESTAMP method fails to resolve a conflict, then the 2nd method is applied. This method assigns a priority to each master site and causes data at the site with the highest priority to be retained across all of the sites.
There are some situations which will not be resolved by either of the 1st two conflict resolution methods. One of these situations involves mis-convergence of the data between servers. If the number of records within the tables at each of the servers is identical, but the actual data contains differing values between the servers, the replication transaction will fail with the "ORA-1403 Data Not Found" error. According to the database the reason that the data was not found is due to the same values not being found when comparing the before images of data at each site. Oracle compares the data in each of the table columns before the modification of the data. If any column at any one of the sites contains data in the "before modification" image of the data which is different from the site which is pushing the data, then the database considers this to be a conflict requiring resolution. Ideally this situation should never happen, but under real-world operating conditions it does happen. One of the most common reasons this problem occurs is due to the modification of data outside of the replication process. In general it is not a good idea to modify data outside of the replication process, but in fact it is required if you are performing procedural replication due to batch updating requirements. The OVERWRITE conflict resolution method will resolve this type of conflict by overriding the conflict situation and forcing the propagation of the most recent modification of the table to all of the sites. If the DBA is aware of differing data within the tables (as a result of running the DBMS_RECTIFIER_DIFF package), then the DBA can perform a harmless modification to data at the site containing the "correct" data, thus pushing the "correct" data to the rest of the sites and forcing convergence of the data. An good example of a harmless modification is updating the date_modified column of the table with the SYSDATE value. If the DBA does nothing, the data will converge automatically during the normal course of updating data within the tables. But ideally a person who is knowledgeable in the design of the schema and overall dataflow should make this decision.
2) An important conflict resolution technique is conflict avoidance.
If multiple sites generate records containing the same primary key value, a conflict will result. If no conflict resolution is implemented, then the transactions will end up in the deferror queue. If the DBA tries to re-apply the transactions manually, they will still fail due to the duplicate primary keys which will fail while trying to insert data into the table. The scripts generated by this graphical application are set up to implement differing ranges of primary key values at each master site. These scripts are designed around a primary key value consisting of an incrementing integer value. Other methods can be implemented, but would require modifying the reptriggers.sql code. It is recommended that a large block of numbers be set aside for primary keys at each master site. In most cases 1G blocks of primary key values will generally be sufficient to prevent the DBA from having to re-allocate numbers within a reasonable period of time. (The database isn't going to run out of number anytime soon either!). The use of this straightforward approach toward assigning primary key values can also pay benefits when troubleshooting problems. By looking at the 1st digit in the primary key value, the DBA can instantly determine the site which produced the data.

hline f image

hline

. .

.

. .

 

 

 

Home | Products | Services | Downloads | Order | Support | Contact

Legal Notices

.
.   .
.
Home Products Services Downloads Order Support Contact