Thursday, May 2, 2013






Reverse Replication Using Oracle Golden Gate
This is to set up upstream and downstream replication with in the same data base and test it out.



1. Replicat parameter file:

--replicat added by raj
--CHECKPARAMS

REPLICAT rkiran

SETENV (ORACLE_SID = "ORCL")
SETENV (ORACLE_HOME = "/u01/home/product/10.2.0.4")
SETENV (NLS_LANG = "AMERICAN_AMERICA.UTF8")
USERID ggs, PASSWORD, ENCRYPTKEY DEFAULT

ASSUMETARGETDEFS
GROUPTRANSOPS 3000
ALLOWNOOPUPDATES
--ALLOWNOOPUPDATES # Dont comment the above parameter.

-- DISCARDFILE LOCATION
DISCARDFILE /u01/goldengate//dirout/rkiran.dsc, MEGABYTES 100,APPEND

STATOPTIONS REPORTFETCH
REPORTCOUNT EVERY 10 MINUTES, RATE

-- use EOFDELAY to reduce the impact to the oracle redo log sync
-- EOFDELAY 2

--Uncomment the followings if there are truncate operations.
--GETTRUNCATES

DYNAMICRESOLUTION

--Uncomment the followings if handlecollisions required.
--HANDLECOLLISIONS
--RESTARTCOLLISIONS

--Table List

--Uncomment out the followings and enter the scn you would like.

MAP RAJ.raj_raj, TARGET test.raj_test;

==========

ggsci
add replicat rkiran, exttrail /u01/goldengate/traildata/local/tc
alter replicat rkiran, extseqno 0 extrba 0
info rkiran
start rkiran
info rkiran

========

-- test extract created by raj
NUMFILES 2000
ALLOCFILES 500

EXTRACT EKIRAN

TRANLOGOPTIONS BUFSIZE 32768

EXTTRAIL /u01/goldengate/traildata/local/tc

SETENV (ORACLE_SID = "ORCL")
SETENV (ORACLE_HOME = "/u01/home/product/10.2.0.4")
SETENV (NLS_LANG = "AMERICAN_AMERICA.UTF8")
USERID ggs, PASSWORD <encrypted password>, ENCRYPTKEY DEFAULT
--sqlexec "alter session set events '10046 trace name context forever, level 12'"

FLUSHSECS 60
CHECKPOINTSECS 120

CACHEMGR, CACHESIZE 32GB, CACHEDIRECTORY /tmp/ggtmp/

EOFDELAY 15

REPORTCOUNT EVERY 100 RECORDS

GETTRUNCATES

DYNAMICRESOLUTION
WILDCARDRESOLVE DYNAMIC
WARNLONGTRANS 5 MINUTES, CHECKINTERVAL 1M

TABLE RAJ.RAJ_RAJ, TOKENS ( SCN = @GETENV ("ORATRANSACTION","SCN"));


===

add extract EKIRAN tranlog begin now
add rmttrail /u01/goldengate/traildata/local/tc extract EKIRAN

make sure if the trail files are generated at the local dirdat directory

===============

-- test extract created by raj for reverse replication

NUMFILES 2000
ALLOCFILES 500

EXTRACT EREV

TRANLOGOPTIONS BUFSIZE 32768

EXTTRAIL /u01/goldengate/traildata/local/rev/bc

SETENV (ORACLE_SID = "ORCL")
SETENV (ORACLE_HOME = "/u01/home/product/10.2.0.4")
SETENV (NLS_LANG = "AMERICAN_AMERICA.UTF8")
USERID ggs, PASSWORD <encrypted password>, ENCRYPTKEY DEFAULT
--sqlexec "alter session set events '10046 trace name context forever, level 12'"

FLUSHSECS 60
CHECKPOINTSECS 120

CACHEMGR, CACHESIZE 32GB, CACHEDIRECTORY /tmp/ggtmp/

EOFDELAY 15

REPORTCOUNT EVERY 100 RECORDS

GETTRUNCATES

DYNAMICRESOLUTION
WILDCARDRESOLVE DYNAMIC
WARNLONGTRANS 5 MINUTES, CHECKINTERVAL 1M

TABLE TEST.RAJ_TEST, TOKENS ( SCN = @GETENV ("ORATRANSACTION","SCN"));


===

add extract EREV tranlog begin now
add rmttrail /u01/goldengate/traildata/local/rev/bc extract EREV

make sure if the trail files are generated at the local dirdat directory

=================
--replicat added by raj
--CHECKPARAMS

REPLICAT reverse

SETENV (ORACLE_SID = "ORCL")
SETENV (ORACLE_HOME = "/u01/home/product/10.2.0.4")
SETENV (NLS_LANG = "AMERICAN_AMERICA.UTF8")
USERID ggs, PASSWORD <encrypted password>, ENCRYPTKEY DEFAULT

ASSUMETARGETDEFS
GROUPTRANSOPS 3000
ALLOWNOOPUPDATES
--ALLOWNOOPUPDATES # Dont comment the above parameter.

-- DISCARDFILE LOCATION
DISCARDFILE /u01/goldengate//dirout/reverse.dsc, MEGABYTES 100,APPEND

STATOPTIONS REPORTFETCH
REPORTCOUNT EVERY 10 MINUTES, RATE

-- use EOFDELAY to reduce the impact to the oracle redo log sync
-- EOFDELAY 2

--Uncomment the followings if there are truncate operations.
--GETTRUNCATES

DYNAMICRESOLUTION

--Uncomment the followings if handlecollisions required.
--HANDLECOLLISIONS
--RESTARTCOLLISIONS

--Table List

--Uncomment out the followings and enter the scn you would like.
MAP test.raj_test, TARGET RAJ.raj_raj;

==


GGSCI (localhost) 1> dblogin userid ggs, password ****
Successfully logged into database.

GGSCI (localhost) 2> add trandata RAJ.raj_raj

Logging of supplemental redo data enabled for table RAJ.RAJ_RAJ.

GGSCI (localhost) 3> add trandata test.raj_test

Logging of supplemental redo data enabled for table TEST.RAJ_TEST.

oracle@localhost > ls /u01/goldengate/traildata/local/
oracle@localhost >
no files existing

GGSCI (localhost) 3> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING                                      
EXTRACT     STOPPED     EKIRAN      00:00:00      00:00:03

GGSCI (localhost) 4> add rmttrail /u01/goldengate/traildata/local/tc extract EKIRAN
RMTTRAIL added.


GGSCI (localhost) 5> info EKIRAN
info EKIRAN

EXTRACT    EKIRAN    Last Started 2012-11-14 16:46   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:01:45 ago)
Log Read Checkpoint  Oracle Redo Logs
                     2012-11-14 16:45:49  Seqno 31940, RBA 757264

oracle@localhost > ls -ltr /u01/goldengate/traildata/local/
total 16
-rw-rw-rw-   1 oracle   dba          948 Nov 14 16:46 tc000000

====================================
====================================

GGSCI (localhost) 2> add replicat rkiran, exttrail /u01/goldengate/traildata/local/tc
REPLICAT added.


GGSCI (localhost) 3> alter replicat rkiran, extseqno 0 extrba 0
REPLICAT altered.


GGSCI (localhost) 4> info rkiran

REPLICAT   RKIRAN    Initialized   2012-11-14 17:01   Status STOPPED
Checkpoint Lag       00:00:00 (updated 00:00:06 ago)
Log Read Checkpoint  File /u01/goldengate/traildata/local/tc000000
                     First Record  RBA 0


GGSCI (localhost) 5> start rkiran

Sending START request to MANAGER ...
REPLICAT RKIRAN starting


GGSCI (localhost) 6> info rkiran

REPLICAT   RKIRAN    Last Started 2012-11-14 17:01   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:05 ago)
Log Read Checkpoint  File /u01/goldengate/traildata/local/tc000000
                     First Record  RBA 948


GGSCI (localhost) 7> !
info rkiran

REPLICAT   RKIRAN    Last Started 2012-11-14 17:01   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:01 ago)
Log Read Checkpoint  File /u01/goldengate/traildata/local/tc000000
                     First Record  RBA 948
====================================
====================================


set echo on
set serveroutput on
begin
 for i in 1..300
  loop
    insert into raj_raj values (i,'jra','hyd',i,i);
    dbms_output.put_line('value of i is '||i);
    if (mod(i,200)=0) then
      dbms_output.put_line('committing at the value of i='||i);
      commit;
        dbms_output.put_line('sleeping for 2 seconds');
        dbms_lock.sleep(2);
        DBMS_OUTPUT.put_line ('Time is ' || TO_CHAR (SYSDATE, 'HH:MI:SS'));
    end if;
  end loop;
end;


select count(*) from raj_raj;
select count(*) from raj_test;


INFERENCE: extract get lag = At EOF, no more records to process
       +
     extract logend  = YES
          +
     Replicat status = 0 records in current transaction
          +
     Replicat lag and logend should be EOF, no more records to process and YES respectively.  
===


== Testing reverse replication


         CURRENT_SCN
--------------------
       6669796442862
       6669796442886 - from the logdump
       rba 4580020
       seq# 000021
   
set serveroutput on
begin
 for i in 1000001..1050000
  loop
    insert into raj_test values (i,'jra','hyd',i,i);
    dbms_output.put_line('value of i is '||i);
    if (mod(i,1000)=0) then
      dbms_output.put_line('committing at the value of i='||i);
      commit;
       dbms_output.put_line('sleeping for 2 seconds');
       dbms_lock.sleep(2);
       DBMS_OUTPUT.put_line ('Time is ' || TO_CHAR (SYSDATE, 'HH:MI:SS'));
    end if;
  end loop;
end;
/



add replicat reverse, exttrail /u01/goldengate/traildata/local/rev/bc

GGSCI (localhost) 2> alter replicat reverse, extseqno 000021 extrba 4580020
REPLICAT altered.


GGSCI (localhost) 3> info reverse

REPLICAT   REVERSE   Initialized   2012-11-16 10:50   Status STOPPED
Checkpoint Lag       00:00:00 (updated 00:00:05 ago)
Log Read Checkpoint  File /u01/goldengate/traildata/local/rev/bc000021
                     First Record  RBA 4580020


GGSCI (localhost) 4> start reverse

Sending START request to MANAGER ...
REPLICAT REVERSE starting


GGSCI (localhost) 5> info reverse

REPLICAT   REVERSE   Last Started 2012-11-16 10:50   Status RUNNING
Checkpoint Lag       00:10:26 (updated 00:00:00 ago)
Log Read Checkpoint  File /u01/goldengate/traildata/local/rev/bc000021
                     2012-11-16 10:39:57.000000  RBA 5300143


GGSCI (localhost) 6>  info reverse detail

REPLICAT   REVERSE   Last Started 2012-11-16 10:50   Status RUNNING
Checkpoint Lag       00:10:03 (updated 00:00:00 ago)
Log Read Checkpoint  File /u01/goldengate/traildata/local/rev/bc000021
                     2012-11-16 10:40:27.000000  RBA 7460517

  Extract Source                          Begin             End        

  /u01/goldengate/traildata/local/rev/bc000021  * Initialized *   2012-11-16 10:40
  /u01/goldengate/traildata/local/rev/bc000021  * Initialized *   First Record
  /u01/goldengate/traildata/local/rev/bc000000  * Initialized *   First Record


-- some use ful sql

/*
Conn / as sysdba
Col used format a8
Prompt current_rba will show where redo is being written to

Select  le.leseq  log_sequence#, cp.cpodr_bno * le.lebsz current_rba,
                        le.lesiz * le.lebsz log_size,
            substr(to_char(100 * cp.cpodr_bno / le.lesiz, '999.00'), 2) || '%'  used
    from    sys.x$kcccp  cp,
            sys.x$kccle  le
    where   le.inst_id = userenv('Instance') and
            cp.inst_id = userenv('Instance') and
            le.leseq = cp.cpodr_seq and le.leseq > 0
*/



Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)