LOB Beers in London

Yesterday I was invited to very pleasant event and met there different Oracle gurus like Doug Burns (Oracle ACE Director, OAKTABLE), Martin Widlake (OAKTABLE), Roger Allen and others. Very good company and beer. What could be better?
0 коммент.
Ярлыки:

Updatable cache and transactional order

Recently, I received a very interesting question about TimesTen cache. The customer would like to use TimesTen as a front office database and decided to use AWT cache group for that. The application creates a lot of transactions per second and basically changes the client balance. The question is "How will TimesTen transfer changes to Oracle database?". I think its a very good question, because I know customers who would like to use TimesTen for decreasing the load on production Oracle DB and in this case the transactional order is quite important.
Let's create a simple test. Oracle DB: 
SQL> show user
USER is "ORATT"
SQL> create table awttab ( a number not null primary key, b varchar2(100) );

Table created.

SQL> select count(*) from awttab;

  COUNT(*)
----------
         0

SQL> grant select, insert, update, delete on awttab to cacheadmin;

Grant succeeded.

SQL>
TimesTen:
[oracle@nodett1 ~]$ ttisql "DSN=ds_test;UID=cacheadmin;PWD=oracle"

Copyright (c) 1996-2011, Oracle.  All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.

connect "DSN=ds_test;UID=cacheadmin;PWD=oracle";
Connection successful: DSN=ds_test;UID=cacheadmin;DataStore=/u01/app/oracle/product/datastore/ds_test;DatabaseCharacterSet=WE8MSWIN1252;ConnectionCharacterSet=US7ASCII;DRIVER=/u01/app/oracle/product/11.2.2.4/TimesTen/tt11224/lib/libtten.so;LogDir=/u01/app/oracle/product/datastore/ds_test/log;PermSize=1024;TempSize=128;TypeMode=0;PLSQL_TIMEOUT=1000;CacheGridEnable=0;OracleNetServiceName=orcl;
(Default setting AutoCommit=1)
Command> call ttCacheUidPwdSet('cacheadmin','oracle');
Command> call ttCacheStart;
Command> CREATE ASYNCHRONOUS WRITETHROUGH CACHE GROUP awtcache
       > FROM oratt.awttab ( a NUMBER NOT NULL PRIMARY KEY,
       >                     b VARCHAR2(100));
Command> call ttRepStart;
Command>

[oracle@nodett1 ~]$ ttisql "DSN=ds_test;UID=oratt;PWD=oracle"

Copyright (c) 1996-2011, Oracle.  All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.

connect "DSN=ds_test;UID=oratt;PWD=oracle";
Connection successful: DSN=ds_test;UID=oratt;DataStore=/u01/app/oracle/product/datastore/ds_test;DatabaseCharacterSet=WE8MSWIN1252;ConnectionCharacterSet=US7ASCII;DRIVER=/u01/app/oracle/product/11.2.2.4/TimesTen/tt11224/lib/libtten.so;LogDir=/u01/app/oracle/product/datastore/ds_test/log;PermSize=1024;TempSize=128;TypeMode=0;PLSQL_TIMEOUT=1000;CacheGridEnable=0;OracleNetServiceName=orcl;
(Default setting AutoCommit=1)
Command> select * from awttab;
0 rows found.
Command>
We created an AWT cache group on a table awttab. Insert a row into the table. 
Command> insert into awttab values (1,'test');
1 row inserted.
Command> select * from awttab;
< 1, test >
1 row found.
Command>
Transaction was committed automatically (autocommit on). Check the record inside Oracle DB. 
SQL> select * from awttab;

         A B
---------- ------------------
         1 test

SQL>
Let's update this row 50 times by using the following code. Before that I turn on the 10046 event on cacheadmin Oracle DB user. 
Command> begin
       >   for i in 1 ..50 loop
       >      update awttab set b = 'test'||i where a=1;
       >      commit;
       >   end loop;
       > end;
       > /

PL/SQL procedure successfully completed.

Command> select * from awttab;
< 1, test50 >
1 row found.
Command>
As you can see, I updated the same row 50 times and used the following order: 
update1 -> commit 
update2 -> commit
...
update50 -> commit
Basically, this type of transactions (each DML statement ends by commit) is very often used in OLTP systems and can cause the performance problems (see log_file_sync wait event), so let's have a look on tracing files. We can see information about each statement there: 
 
...
PARSING IN CURSOR #6328852 len=67 dep=1 uid=90 oct=6 lid=90 tim=1355414103869999 hv=317616249 ad='48b693a0' sqlid='dk6k6dc9fww3t'
UPDATE "ORATT"."AWTTAB" SET "B" = :"SYS_B_0" WHERE "A" = :"SYS_B_1"
END OF STMT
PARSE #6328852:c=0,e=307,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=1356545142,tim=1355414103869995
BINDS #6328852:
 Bind#0
  oacdty=01 mxl=32(06) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0100 frm=01 csi=178 siz=32 off=0
  kxsbbbfp=4b176ea0  bln=32  avl=06  flg=09
  value="test1"
 Bind#1
  oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0100 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=4b176e80  bln=22  avl=02  flg=09
  value=1
EXEC #6328852:c=1000,e=494,p=0,cr=1,cu=1,mis=0,r=1,dep=1,og=1,plh=1356545142,tim=1355414103870584
STAT #6328852 id=1 cnt=0 pid=0 pos=1 obj=0 op='UPDATE  AWTTAB (cr=1 pr=0 pw=0 time=265 us)'
STAT #6328852 id=2 cnt=1 pid=1 pos=1 obj=75894 op='INDEX UNIQUE SCAN SYS_C0011095 (cr=1 pr=0 pw=0 time=71 us cost=1 size=65 card=1)'
CLOSE #6328852:c=0,e=6,dep=1,type=0,tim=1355414103870744
===================
PARSING IN CURSOR #6328852 len=67 dep=1 uid=90 oct=6 lid=90 tim=1355414103870949 hv=317616249 ad='48b693a0' sqlid='dk6k6dc9fww3t'
UPDATE "ORATT"."AWTTAB" SET "B" = :"SYS_B_0" WHERE "A" = :"SYS_B_1"
END OF STMT
PARSE #6328852:c=0,e=124,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=1356545142,tim=1355414103870944
BINDS #6328852:
 Bind#0
  oacdty=01 mxl=32(06) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0100 frm=01 csi=178 siz=32 off=0
  kxsbbbfp=48b7fb28  bln=32  avl=06  flg=09
  value="test2"
 Bind#1
  oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0100 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=48b7fb10  bln=22  avl=02  flg=09
  value=1
EXEC #6328852:c=1000,e=240,p=0,cr=1,cu=1,mis=0,r=1,dep=1,og=1,plh=1356545142,tim=1355414103871275
STAT #6328852 id=1 cnt=0 pid=0 pos=1 obj=0 op='UPDATE  AWTTAB (cr=1 pr=0 pw=0 time=50 us)'
STAT #6328852 id=2 cnt=1 pid=1 pos=1 obj=75894 op='INDEX UNIQUE SCAN SYS_C0011095 (cr=1 pr=0 pw=0 time=11 us cost=1 size=65 card=1)'
CLOSE #6328852:c=0,e=6,dep=1,type=0,tim=1355414103871426
...
But there is no information about commit. And only after the last statement we see 
 
PARSING IN CURSOR #6328852 len=67 dep=1 uid=90 oct=6 lid=90 tim=1355414103907559 hv=317616249 ad='48b693a0' sqlid='dk6k6dc9fww3t'
UPDATE "ORATT"."AWTTAB" SET "B" = :"SYS_B_0" WHERE "A" = :"SYS_B_1"
END OF STMT
PARSE #6328852:c=0,e=100,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=1356545142,tim=1355414103907555
BINDS #6328852:
 Bind#0
  oacdty=01 mxl=32(07) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0100 frm=01 csi=178 siz=32 off=0
  kxsbbbfp=48b5f340  bln=32  avl=07  flg=09
  value="test100"
 Bind#1
  oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0100 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=48b5f330  bln=22  avl=02  flg=09
  value=1
EXEC #6328852:c=0,e=181,p=0,cr=1,cu=1,mis=0,r=1,dep=1,og=1,plh=1356545142,tim=1355414103907813
STAT #6328852 id=1 cnt=0 pid=0 pos=1 obj=0 op='UPDATE  AWTTAB (cr=1 pr=0 pw=0 time=38 us)'
STAT #6328852 id=2 cnt=1 pid=1 pos=1 obj=75894 op='INDEX UNIQUE SCAN SYS_C0011095 (cr=1 pr=0 pw=0 time=7 us cost=1 size=65 card=1)'
CLOSE #6328852:c=0,e=5,dep=1,type=0,tim=13554141039080
...
XCTEND rlbk=0, rd_only=0, tim=1355414103908136
As you can see, TimesTen tries to combine transactions in transactional order and applies them in efficient way. For example instead of execute transactions in the original order like this: 
update1 -> commit 
update2 -> commit
...
update50 -> commit
TimesTen executed them like the following: 
update1 -> update2 ... -> update50 -> commit
This method provides an opportunity to decrease load on Oracle Database. So, TimesTen provides not only the opportunity to achieve SLA and very low response time, additionally you can decrease the load on your production Oracle DB.
0 коммент.
Ярлыки: ,

Setting Oracle DB to Oracle DB replication using Golden Gate

In this post I'd like to write about Oracle DB replication by using Golden Gate.I am going to create a Oracle DB replication. Environment (two virtual machines):
  • host db (192.168.2.131), OEL 5.2. x86, Oracle DB EE 11.2.0.3.0
  • host db2 (192.168.2.132), OEL 5.2. x86, Oracle DB EE 11.2.0.3.0
The goal - replicate all changes in one particular scheme (include DDL) from one Oracle DB host  to another Oracle DB host.

1. First of all, install Golden Gate software on each Oracle DB host. I've already written about it here.

2. Prepare the source database (host db) for replication. Switch the database to archivelog mode: 
SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open;
SQL> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG
Enable minimal supplemental logging: 
SQL> alter database add supplemental log data;

Database altered.
 
SQL>
Prepare the database for DDL replication. Turn off the recyclebin feature and be sure that it is empty. 
SQL> alter system set recyclebin=off scope=spfile;
SQL> PURGE DBA_RECYCLEBIN;

DBA Recyclebin purged.

SQL>
Create a schema that will contain the Oracle GoldenGate DDL objects. 
SQL>  create tablespace gg_tbls datafile '/u01/app/oracle/oradata/orcl/gg_tbls.dbf' size 100m reuse autoextend on;

Tablespace created.

SQL>  create user ggate identified by oracle default tablespace gg_tbls quota unlimited on gg_tbls;

User created.

SQL>  grant create session, connect, resource to ggate;

Grant succeeded.

SQL> grant dba to ggate; -- just in case

Grant succeeded.

SQL>  grant execute on utl_file to ggate;

Grant succeeded.

SQL>
Change the directory on Golden Gate home directory and run scripts for creating all necessary objects for support ddl replication. 
[oracle@db ~]$ cd /u01/app/oracle/product/gg/
[oracle@db gg]$ sqlplus / as sysdba

SQL> @marker_setup.sql
SQL> @ddl_setup.sql
SQL> @role_setup.sql
SQL> grant GGS_GGSUSER_ROLE to ggate;
SQL> @ddl_enable.sql
After that, add information about your Oracle GoldenGate DDL scheme into file GLOBALS. You should input this row into the file "GGSCHEMA ggate". 
[oracle@db gg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x86, 32bit (optimized), Oracle 11g on Apr 23 2012 08:09:25

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.

GGSCI (db.us.oracle.com) 1> EDIT PARAMS ./GLOBALS
GGSCHEMA ggate

GGSCI (db.us.oracle.com) 2>
3. Create the schemes for replication on both hosts (db, db2).

3.1. Source database (db):   
SQL> create user source identified by oracle default tablespace users temporary tablespace temp;

User created.

SQL> grant connect,resource,unlimited tablespace to source;

Grant succeeded.

SQL>
3.2. Target database(db2): 


SQL> create user target identified by oracle default tablespace users temporary tablespace temp;

User created.

SQL> grant connect,resource,unlimited tablespace to target;

Grant succeeded.

SQL> grant dba to target; -- or particular grants

Grant succeeded.

SQL>


4. Create the directory for trail files on both hosts and create directory for discard file on db2 host only.



[oracle@db ~]  mkdir /u01/app/oracle/product/gg/dirdat/tr
[oracle@db2 ~] mkdir /u01/app/oracle/product/gg/dirdat/tr
[oracle@db2 ~] mkdir /u01/app/oracle/product/gg/discard


5. Create a standard reporting configuration (picture 1)









 Picture 1



5.1. Configure extracts on source database (db).



Run ggcsi and configure the manager process.



[oracle@db gg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x86, 32bit (optimized), Oracle 11g on Apr 23 2012 08:09:25

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.

GGSCI (db.us.oracle.com) 1> edit params mgr 
PORT 7809
GGSCI (db.us.oracle.com) 2> start manager

Manager started.

GGSCI (db.us.oracle.com) 3> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING

GGSCI (db.us.oracle.com) 4>



Login into database and add additional information about primary keys into log files.



GGSCI (db.us.oracle.com) 4> dblogin userid ggate
Password:
Successfully logged into database.

GGSCI (db.us.oracle.com) 5> ADD SCHEMATRANDATA source

2012-12-06 16:23:20  INFO    OGG-01788  SCHEMATRANDATA has been added on schema source.

GGSCI (db.us.oracle.com) 6>



NOTE. This is a very important step, because if you don't do it you will not be  able to replicate Update statements. You will get errors like the following:



OCI Error ORA-01403: no data found, SQL 
Aborting transaction on /u01/app/oracle/product/gg/dirdat/tr beginning at seqno 1 rba 4413
                         error at seqno 1 rba 4413
Problem replicating SOURCE.T1 to TARGET.T1
Record not found
Mapping problem with compressed update record (target format)...
*
ID =
NAME = test3
*
...


As you know, when you write the update statement you usually don't change the primary key, so Oracle log files contain information about changing column values and don't contain information about primary key. For avoiding this situation you should add this information into log files using ADD SCHEMATRANDATA or ADD TRANDATA commands. 

Add extracts (regular and data pump).



GGSCI (db.us.oracle.com) 6> add extract ext1, tranlog, begin now
EXTRACT added.

GGSCI (db.us.oracle.com) 7> add exttrail /u01/app/oracle/product/gg/dirdat/tr, extract ext1
EXTTRAIL added.

GGSCI (db.us.oracle.com) 8> edit params ext1
extract ext1
userid ggate, password oracle       
exttrail /u01/app/oracle/product/gg/dirdat/tr 
ddl include mapped objname source.*;
table source.*;

GGSCI (db.us.oracle.com) 9> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     STOPPED     EXT1        00:00:00      00:01:29

GGSCI (db.us.oracle.com) 10> add extract pump1, exttrailsource /u01/app/oracle/product/gg/dirdat/tr , begin now
EXTRACT added.

GGSCI (db.us.oracle.com) 11> add rmttrail /u01/app/oracle/product/gg/dirdat/tr, extract pump1
RMTTRAIL added.

GGSCI (db.us.oracle.com) 12> edit params pump1
EXTRACT pump1
USERID ggate, PASSWORD oracle 
RMTHOST db2, MGRPORT 7809
RMTTRAIL /u01/app/oracle/product/gg/dirdat/tr 
PASSTHRU
table source.*;

GGSCI (db.us.oracle.com) 13> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     STOPPED     EXT1        00:00:00      00:02:33
EXTRACT     STOPPED     PUMP1       00:00:00      00:02:56

GGSCI (db.us.oracle.com) 14>




5.2. Configure the target database (d2).


Configure the manager process



[oracle@db2 gg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x86, 32bit (optimized), Oracle 11g on Apr 23 2012 08:09:25

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.


GGSCI (db2) 1> edit params mgr
PORT  7809

GGSCI (db2) 2> start manager

Manager started.

GGSCI (db2) 3> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING



Create the checkpoint table and change the GLOBAL file. 



GGSCI (db2) 4> EDIT PARAMS ./GLOBALS
CHECKPOINTTABLE target.checkpoint

GGSCI (db2) 5> dblogin userid target
Password:
Successfully logged into database.

GGSCI (db2) 6> add checkpointtable target.checkpoint

Successfully created checkpoint table target.checkpoint.

GGSCI (db2) 7>


Add replicat.



GGSCI (db2) 8> add replicat rep1, exttrail /u01/app/oracle/product/gg/dirdat/tr, begin now
REPLICAT added.

GGSCI (db2) 9> edit params rep1
REPLICAT rep1
ASSUMETARGETDEFS
USERID target, PASSWORD oracle
discardfile /u01/app/oracle/product/gg/discard/rep1_discard.txt, append, megabytes 10
DDL
map source.*, target target.*;

GGSCI (db2) 10> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    STOPPED     REP1        00:00:00      00:01:52

GGSCI (db2) 11>



5.3. Start extracts and replicat.



GGSCI (db.us.oracle.com) 6> start extract ext1

Sending START request to MANAGER ...
EXTRACT EXT1 starting


GGSCI (db.us.oracle.com) 7> start extract pump1

Sending START request to MANAGER ...
EXTRACT PUMP1 starting

GGSCI (db.us.oracle.com) 8> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXT1        00:00:00      00:00:01
EXTRACT     RUNNING     PUMP1       00:00:00      00:01:01


GGSCI (db2) 6> start replicat rep1

Sending START request to MANAGER ...
REPLICAT REP1 starting

GGSCI (db2) 7> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     REP1        00:00:00      00:00:06


6. Check Host db.


[oracle@db gg]$ sqlplus source/oracle

SQL*Plus: Release 11.2.0.3.0 Production on Fri Dec 7 20:09:17 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning and Real Application Testing options

SQL> select * from t1;
select * from t1
              *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> create table t1 (id number primary key, name varchar2(50));

Table created.

SQL> insert into t1 values (1,'test');

1 row created.

SQL> insert into t1 values (2,'test');

1 row created.

SQL> commit;

Commit complete.

SQL>


Check host db2:



[oracle@db2 gg]$ sqlplus target/oracle

SQL*Plus: Release 11.2.0.3.0 Production on Fri Dec 7 20:09:41 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning and Real Application Testing options

SQL> select * from t1;

        ID NAME
---------- --------------------------------------------------
         1 test
         2 test

SQL>


As you can see, all works. Lets execute some SQL and Update statements of course.

Host db:



SQL> delete t1 where id =2;

1 row deleted.

SQL> insert into t1 values (3,'test');

1 row created.

SQL> update t1 set name='test3' where id = 3;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from t1;

        ID NAME
---------- --------------------------------------------------
         1 test
         3 test3

SQL>



Let's check host db2:



SQL>  select * from t1;

        ID NAME
---------- --------------------------------------------------
         1 test
         3 test3

SQL>




Conclusion 


Oracle GoldenGate is a software package for enabling the replication of data in heterogeneous data environments. It is easy to use. Of course, in this post I have not consider questions related with initial load, conflict detection, high availability and etc., I am only starting to work with this product and probably write about these features in the future. Additionally, I would recommend you to read Alexander Ryndin's blog  http://www.oraclegis.com/blog/, he is a Golden Gate guru. One more interesting resource is http://gavinsoorma.com/. All the best.
















6
коммент.












Ярлыки:
,













        

      









Подписаться на:
Сообщения (Atom)