Share the content if you found it is useful (You can share using 300 community websites) click "share" at the end of the post.

You are encouraged to leave a comment.








Wednesday, December 17, 2008

Step -by-Step Replication (MASTER_MASTER) Asynchromous Replication

Why I am using Asynchronous?

In our scenario, The availability of network is uncertain in a given time. Synchronous uses two side commit. If one site is not available at a time, then the update is not possible. It will raise error.

But in Asynchronous,
Using change queues and materialized queues snapshot logs to store changes and therefore even if single side is not available during some period of time the remained side may be used. Moreover, after detached side will be available again, data will be synchronized [last statement depends on purge period, the purge period constraint defends active master database via to be filled by not propagated changes.]

The first thing to know prior to start with advanced replication is that you should have primary key (or at least the alternative) for all tables you would like to replicate. For instance, you have two instances test1 and test2. On both instances you have created user REPTEST00 with default tablespace pool_data and temporary tablespace temp.


The following statement describes database names and tnsnames aliases of the involved instances:
TEST1 = Global Database name of the Master Definition Site
TEST2 = Global Database name of secondary Master Site

test1 = Net alias to the Master Definition Site
test2 = Net alias to the secondary Master Site
You may check your global database name by running:

select * from global_name
Net alias is an alias from $ORACLE_HOME/network/admin/tnsnames.ora

*Note:
Even all oracle documentation that describes advanced replication features, explicitly suggesting that you are using oracle domains eg.test1.world. However it is not really required. So if you have well designed set of database names, you do not need this feature.
During this session we will avoid to use oracle domains and our Net aliases will be named just test1 and test2 respectively.
In order to work with the oracle advanced replication we should add GLOBAL_NAMES = true to our pfile (or spfile).

Test1 creates user repadmin in both instances with appropriate permissions


CONNECT system/manager@test1
CREATE USER repadmin IDENTIFIED BY repadmin;
ALTER USER repadmin DEFAULT TABLESPACE POOL_DATA;
ALTER USER repadmin TEMPORARY TABLESPACE TEMP;
GRANT connect, resource TO repadmin;
EXECUTE dbms_repcat_admin.grant_admin_any_schema('repadmin');
GRANT comment any table TO repadmin;
GRANT lock any table TO repadmin;
EXECUTE dbms_defer_sys.register_propagator('repadmin');
GRANT execute any procedure TO repadmin;
CREATE PUBLIC DATABASE LINK TEST2 USING 'test2';
CONNECT repadmin/repadmin@test1
CREATE DATABASE LINK TEST2 CONNECT TO repadmin IDENTIFIED BY repadmin;

Test2.
CONNECT system/manager@test2
CREATE USER repadmin IDENTIFIED BY repadmin;
ALTER USER repadmin DEFAULT TABLESPACE POOL_DATA;
ALTER USER repadmin TEMPORARY TABLESPACE TEMP;
GRANT connect, resource TO repadmin;
EXECUTE dbms_repcat_admin.grant_admin_any_schema('repadmin');
GRANT comment any table TO repadmin;
GRANT lock any table TO repadmin;
EXECUTE dbms_defer_sys.register_propagator('repadmin');
GRANT execute any procedure TO repadmin;
CREATE PUBLIC DATABASE LINK TEST1 USING 'test1';
CONNECT repadmin/repadmin@test2
CREATE DATABASE LINK TEST1 CONNECT TO repadmin IDENTIFIED BY repadmin;

Test1 uses ASYNCHRONOUS replication. It tries pushing changes every 10 second, purge changes that not succeed to be propagated during 3 days

BEGIN
dbms_defer_sys.schedule_push(
destination => 'test2',
interval => '/*10:Sec*/ sysdate + 10 / (3600*24)',
next_date => sysdate,
stop_on_error => FALSE,
delay_seconds => 0,
parallelism => 1);
END;
/
BEGIN
dbms_defer_sys.schedule_purge(
next_date => sysdate,
interval => '/*3 : days*/ sysdate + 3',
delay_seconds => 0,
rollback_segment => '');
END;
/
Test2 uses ASYNCHRONOUS replication.

It tries pushing changes each 10 second, purge changes that not succeed to be propagated during 3 days

BEGIN
dbms_defer_sys.schedule_push(
destination => 'test1',
interval => '/*10:Sec*/ sysdate + 10 / (3600*24)',
next_date => sysdate,
stop_on_error => FALSE,
delay_seconds => 0,
parallelism => 1);
END;
/
BEGIN
dbms_defer_sys.schedule_purge(
next_date => sysdate,
interval => '/*3 : days*/ sysdate + 3',
delay_seconds => 0,
rollback_segment => '');
END;
/

Create master replication group on test1 which is primary master:

CONNECT repadmin/repadmin@test1
BEGIN
DBMS_REPCAT.CREATE_MASTER_REPGROUP(
gname => '"REPTEST"',
qualifier => '',
group_comment => '');
END;
/

Add secondary master and suspend master activity. If this replication needed to be SYNCHRONOUS, then just change propagation_mode from ASYNCHRONOUS to SYNCHRONOUS but be aware that SYNCHRONOUS configuration may not be used for High Availability
BEGIN
DBMS_REPCAT.ADD_MASTER_DATABASE(
gname => '"REPTEST"',
master => 'TEST2',
use_existing_objects => TRUE,
copy_rows => TRUE,
propagation_mode => 'ASYNCHRONOUS');
END;
/
BEGIN
DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY(
gname => '"REPTEST"');
END;
/

Let's assume you have tables XXXX with primary key XXXX_PK YYYY without primary key but with unique constraint YYYY_UX contains two columns YYYY_UX_COL1, YYYY_UX_COL2 and additional index YYYY_IX. Also assume there is a trigger on table XXXX namely XXXX_TRIGGER that will be replicated as well.
On test1 which is primary master, create master replication objects:
CONNECT repadmin/repadmin@test1
Now creating master replication object for all tables
BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT(
gname => '"REPTEST"',
type => 'TABLE',
oname => '"XXXX"',
sname => '"REPTEST00"',
copy_rows => TRUE,
use_existing_object => TRUE);
END;
/
BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT(
gname => '"REPTEST"',
type => 'TABLE',
oname => '"YYYY"',
sname => '"REPTEST00"',
copy_rows => TRUE,
use_existing_object => TRUE);
END;
/
Create master replication object for trigger:
BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT(
gname => '"REPTEST"',
type => 'TRIGGER',
oname => '"XXXX_TRIGGER"',
sname => '"REPTEST00"',
copy_rows => TRUE,
use_existing_object => TRUE);
END;
/
Create master replication object for all indexes:
BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT(
gname => '"REPTEST"',
type => 'INDEX',
oname => '"YYYY_IX"',
sname => '"REPTEST00"',
copy_rows => TRUE,
use_existing_object => TRUE);
END;
/
BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT(
gname => '"REPTEST"',
type => 'INDEX',
oname => '"YYYY_UX"',
sname => '"REPTEST00"',
copy_rows => TRUE,
use_existing_object => TRUE);
END;
/
BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT(
gname => '"REPTEST"',
type => 'INDEX',
oname => '"XXXX_PK"',
sname => '"REPTEST00"',
copy_rows => TRUE,
use_existing_object => TRUE);
END;
/


Define alternative key for table that have no primary key:
BEGIN
DBMS_REPCAT.SET_COLUMNS(
sname => '"REPTEST00"',
oname => '"YYYY"',
column_list => '"YYYY_UX_COL1, YYYY_UX_COL2"');
END;
/

Generate replication support for all the tables to be replicated:
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT(
sname => '"REPTEST00"',
oname => '"XXXX"',
type => 'TABLE',
min_communication => TRUE,
generate_80_compatible => FALSE);
END;
/
BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT(
sname => '"REPTEST00"',
oname => '"YYYY"',
type => 'TABLE',
min_communication => TRUE,
generate_80_compatible => FALSE);
END;
/
Now wait until all objects from primary master will be copied to secondary master. Afterwards replication can be started by issuing running following statement:
BEGIN
DBMS_REPCAT.RESUME_MASTER_ACTIVITY(gname => '"REPTEST"');
END;
/

Warning!
Oracle Advanced replication is not resistant to schema changes sincevery likely that schema change replication will fail with ORA-23474 afterwards.
This is one of the worst possible cases. Replication may not continue to work until you drop all non-primary masters and recreate all replication support from the scratch.
23474, 0000, "definition of \"%s\".\"%s\" has changed since generation of replication support"
*Cause: The current columns in the specified table and their column types do not match the columns and column types when replication support was last generated.
*Action: Regenerate replication support for the affected table.
All flavors that include the specified table should be checked
for validity. Types for any UDT columns should also be checked
for validity.
You can try to resolving ORA-23474 by regenerating replication support for invalidated objects on primary master:


CONNECT repadmin/repadmin@test1
BEGIN
DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY(
gname => '"REPTEST"');
END;
/

Next is regenerate replication support for all affected objects, for instance:
BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT(
sname => '"REPTEST00"',
oname => '"YYYY"',
type => 'TABLE',
min_communication => TRUE,
generate_80_compatible => FALSE);
END;
/
Then you may resume replication by issuing the following statement:
BEGIN
DBMS_REPCAT.RESUME_MASTER_ACTIVITY(
gname => '"REPTEST"');
END;
/
The right way to change schema would be using DBMS_REPCAT.EXECUTE_DDL. Example:
BEGIN
DBMS_REPCAT.EXECUTE_DDL (
gname => 'REPTEST',
master_list => 'test1, test2',
DDL_TEXT => 'alter table REPTEST00.XXXX add (add_test
number(8,0))');
END;
/
If one of the master databases to be removed:
BEGIN
DBMS_REPCAT.REMOVE_MASTER_DATABASES (
gname => '"REPTEST"',
master_list => 'test2');
END;
/
If one of master objects to be removed:
BEGIN
DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY(
gname => '"REPTEST"');
END;
/
BEGIN
DBMS_REPCAT.DROP_MASTER_REPOBJECT (
sname => '"REPTEST00"',
oname => '"YYYY"',
type => 'TABLE',
drop_objects => FALSE);
END;
/
BEGIN
DBMS_REPCAT.RESUME_MASTER_ACTIVITY(
gname => '"REPTEST"');
END;
/

Monitoring and troubleshooting of Oracle advanced replication.
Oracle advanced replication uses tables with names such as def$_xxxx in the schema “system”. User schema “sys” holds several useful views on those tables named defxxxx respectively.
For example, if one of the alternative keys that you set was not unique, then you may receive ORA-01422 error.
All errors received during advanced replication activity and not yet purged can be identified from DEFERROR view.
You may use the following script:
set linesize 132
col DESTINATION for a10
col ERROR_MSG for a45
col START_TIME for a21
col DEFERRED_TRAN_ID for a15
col CALLNO for 9999
SELECT deferred_tran_id, callno, SUBSTR (destination, 1, 10) destination,
TO_CHAR (start_time, 'HH24:MI:SS DD/MM/YYYY') start_time,
SUBSTR (error_msg, 1, 45) error_msg
FROM deferror;
Example of inappropriate surrogate key usage result:
DEFERRED_TRAN_I CALLNO DESTINATIO START_TIME ERROR_MSG
--------------- ------ ---------- --------------------- ---------------------------------------------
9.3.865 0 TEST1 08:10:09 14/03/2004 ORA-01422: exact fetch returns more than requ
2.27.874 0 TEST1 08:10:10 14/03/2004 ORA-01422: exact fetch returns more than requ
9.33.864 0 TEST1 08:10:16 14/03/2004 ORA-01422: exact fetch returns more than requ
9.39.1073 0 TEST1 22:02:36 18/03/2004 ORA-01422: exact fetch returns more than requ
1.15.1107 0 TEST1 22:02:48 18/03/2004 ORA-01422: exact fetch returns more than requ
5.36.1089 0 TEST1 22:02:51 18/03/2004 ORA-01422: exact fetch returns more than requ
9.30.1074 0 TEST1 22:10:58 18/03/2004 ORA-01422: exact fetch returns more than requ

The other useful view of this kind is DEFTRAN.
Via this view you may see the currently queued transaction. Please note, transaction that are not propagated due to the error such as in the previous example will remain in this queue forever or until purged.
select DEFERRED_TRAN_ID, DELIVERY_ORDER, DESTINATION_LIST, START_TIME from DEFTRAN;
DEFERRED_TRAN_I DELIVERY_ORDER D START_TIME
--------------- -------------- - ---------------------
9.3.865 4183230 D 14-MAR-04
2.27.874 4183237 D 14-MAR-04
9.33.864 4183406 D 14-MAR-04
9.39.1073 5196085 D 18-MAR-04
1.15.1107 5196094 D 18-MAR-04
5.36.1089 5196100 D 18-MAR-04
9.30.1074 5198042 D 18-MAR-04

No comments:

Related Posts Plugin for WordPress, Blogger...

Let us be Friends...

Share |

Popular Posts

Labels

11.5.1. to 11.5.5 Cloning Procedure. 11g 11i 11i and R12 11i Autoconfig 11i Cloning 12.1.1 SSL 2 Node RAC Adconfig.sh errors out adgennls.pl adop Advanced Replication Apache version Apex Apex for Oracle database Apex Installation Apex Installation on Oracle E Business Suite. 11i with Apex Apex on 11i Apex on Oracle 11i Apex402 apexins.sql APPL_TOP Character Application Express Application server Issue APPLSYS password Apps Password Asynchronous Replication Autoconfig Autoconfig Managed Beehive Bit of operating system Block block media corrupt BPEL Central Inventory Character Set Conversion Cloning of 11i Cloning of Oracle Applications Clusterware Concurrent CPU 2011 CPU 2012 April CPU 2012 January. CPU 2012 October CRS custom top Custom Top Creation Database . Database Query Dataguard DBMS_REPAIR Demantra . Domain Name E Business Suite Migration EBS 12.2 EPM exp/imp Oracle Applications Flashback table flows_020200 FND_TOP/resource FNDCPASS FNDCPUCF Forms server upgrade Forms startup FRM-92101 Fusion Middleware Hostname Hot Backup Hot cloning HotBackup Hotbackup Cloning of Oracle Apps hrglobal.drv. NLS Saudi Arabian HRMS hyperion Import and Export Oracle Applications Install Oracle Installation of R12 Installation Steps integration Oracle EBS Issues Database issues Oracle EBS Issues with Upgrade 12.1.1 Java Mission Control Java Upgrade java/sql/SavePoint JInitiator Junk Characters Language Translation Linux Linux Migration listener trace Listner version Local Inventory Login Page Master-Master Replication Memory Tuning Multiple Language NLS Non-Autoconfig Enabled OBIEE 11g Installation OBIEE Issues OIM Opatch version OpenSSL ora-01031 Oracle oracle 11g Oracle Announcement Oracle Application servers Oracle Application services Oracle Applications Oracle Applications 11i oracle applications 12.2 Oracle Applications Cloning Oracle Applications Upgrade Oracle Applications. Oracle Apps cloning Oracle BPEL oracle certifications oracle database Oracle Database RMAN Oracle Database upgradation Oracle DB oracle E Business suite 12.2 Oracle EBS oracle EPM Oracle Forms Logo oracle Fusion Applications Oracle Inventory oracle news Oracle OUI Oracle R12 oracle Sun Solaris Oracle Tuning Oracle Virtual Box Oracle Webgate Oracle WMA configuration orcladmin password for Oracle Applications Pasta Configuration pasta.cfg patch PDF Performance Physical Standby Platform migration Printer Configuration Profile Option R12 RAC Rapid Install Rapidwiz Real Application Clusters 10g Recovery RMAN RMAN Recovery SA Gosi Service Oriented Architecture SOA SQLTXPLAIN SSL Configuration step-by-step upgrade Sun solaris Swap Tablespace Trace Enable Trace file Translation Synchronization Patch transport tablespace tuning Tuning SGA uifont.ali Underscore Universal Content Management updates.oracle.com Upgradation of 12.0.5 to 12.1.1 Upgrade Upgrade 11g Upgrade Apps Upgrade DB Upgrade to 11g UTF8 version of oracle applications versions Web server upgrade weblogic windows workflow version XML Publisher version