Savepoint Funny
2 месяца назад
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?
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:
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 -> commitBasically, 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=1355414103908136As 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 -> commitTimesTen executed them like the following:
update1 -> update2 ... -> update50 -> commitThis 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.
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):
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:
3.1. Source database (db):
5.1. Configure extracts on source database (db).
Run ggcsi and configure the manager process.
5.2. Configure the target database (d2).
Configure the manager process
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.
- 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
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 ------------ ARCHIVELOGEnable 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.sqlAfter 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/discard5. 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, SQLAs 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).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 * ...
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 RUNNINGCreate 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:066. 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.
Golden Gate installation on Oracle DB
Let's start from Golden Gate installation. I am going to install Golden Gate on the following system: OEL 5.2 x86 + Oracle DB EE 11.2.0.3.
First of all, find the correct Golden Gate software for your system on edelivery.oracle.com . In this case, I install the "Oracle GoldenGate V11.2.1.0.1 for Oracle 11g on Linux x86" package.
You can use existing OS user (like I do) or create a new one. If you create a new user, that user must be a member of the group that owns the Oracle instance. In this example I use the oracle user.
Create the Golden Gate directory, copy the archive, unzip and tar the archive.
[oracle@db odb_11g]$ mkdir /u01/app/oracle/product/gg [oracle@db odb_11g]$ cp V32409-01.zip /u01/app/oracle/product/gg/ [oracle@db odb_11g]$ cd /u01/app/oracle/product/gg/ [oracle@db gg]$ ls V32409-01.zip [oracle@db gg]$ unzip V32409-01.zip Archive: V32409-01.zip inflating: fbo_ggs_Linux_x86_ora11g_32bit.tar inflating: OGG_WinUnix_Rel_Notes_11.2.1.0.1.pdf inflating: Oracle GoldenGate 11.2.1.0.1 README.txt inflating: Oracle GoldenGate 11.2.1.0.1 README.doc [oracle@db gg]$ [oracle@db gg]$ ls fbo_ggs_Linux_x86_ora11g_32bit.tar Oracle GoldenGate 11.2.1.0.1 README.doc V32409-01.zip OGG_WinUnix_Rel_Notes_11.2.1.0.1.pdf Oracle GoldenGate 11.2.1.0.1 README.txt [oracle@db gg]$ tar -xf fbo_ggs_Linux_x86_ora11g_32bit.tar [oracle@db gg]$ ls bcpfmt.tpl defgen libxml2.txt bcrypt.txt demo_more_ora_create.sql logdump cfg demo_more_ora_insert.sql marker_remove.sql chkpt_ora_create.sql demo_ora_create.sql marker_setup.sql cobgen demo_ora_insert.sql marker_status.sql convchk demo_ora_lob_create.sql mgr db2cntl.tpl demo_ora_misc.sql notices.txt ddl_cleartrace.sql demo_ora_pk_befores_create.sql oggerr ddlcob demo_ora_pk_befores_insert.sql OGG_WinUnix_Rel_Notes_11.2.1.0.1.pdf ddl_ddl2file.sql demo_ora_pk_befores_updates.sql Oracle GoldenGate 11.2.1.0.1 README.doc ddl_disable.sql dirjar Oracle GoldenGate 11.2.1.0.1 README.txt ddl_enable.sql dirprm params.sql ddl_filter.sql emsclnt prvtclkm.plb ddl_nopurgeRecyclebin.sql extract pw_agent_util.sh ddl_ora10.sql fbo_ggs_Linux_x86_ora11g_32bit.tar remove_seq.sql ddl_ora10upCommon.sql freeBSD.txt replicat ddl_ora11.sql ggcmd retrace ddl_ora9.sql ggMessage.dat reverse ddl_pin.sql ggsci role_setup.sql ddl_purgeRecyclebin.sql help.txt sequence.sql ddl_remove.sql jagent.sh server ddl_session1.sql keygen sqlldr.tpl ddl_session.sql libantlr3c.so tcperrs ddl_setup.sql libdb-5.2.so ucharset.h ddl_status.sql libgglog.so ulg.sql ddl_staymetadata_off.sql libggrepo.so UserExitExamples ddl_staymetadata_on.sql libicudata.so.38 usrdecs.h ddl_tracelevel.sql libicui18n.so.38 V32409-01.zip ddl_trace_off.sql libicuuc.so.38 zlib.txt ddl_trace_on.sql libxerces-c.so.28 [oracle@db gg]$Setting up the LD_LIBRARY_PATH.
[oracle@db gg]$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/u01/app/oracle/product/ggRun ggsci and create the subdirs
[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> create subdirs Creating subdirectories under current directory /u01/app/oracle/product/gg Parameter files /u01/app/oracle/product/gg/dirprm: already exists Report files /u01/app/oracle/product/gg/dirrpt: created Checkpoint files /u01/app/oracle/product/gg/dirchk: created Process status files /u01/app/oracle/product/gg/dirpcs: created SQL script files /u01/app/oracle/product/gg/dirsql: created Database definitions files /u01/app/oracle/product/gg/dirdef: created Extract data files /u01/app/oracle/product/gg/dirdat: created Temporary files /u01/app/oracle/product/gg/dirtmp: created Stdout files /u01/app/oracle/product/gg/dirout: created GGSCI (db.us.oracle.com) 2> exit [oracle@db gg]$On this step the Golden Gate installation is completed. As you can see, the installation is very easy and takes a couple of minutes.
One more TimesTen video
One more TimesTen video (swingbench performance test) (author: Svetoslav Gyurov).
New type of data loading into TimesTen
Let's have a look on new feature which was introduced in 11.2.2.4 TimesTen version. I mean the opportunity to load data into TimesTen table based on the result of a query executed into Oracle database. If you don't want to use the cache group and cache grid features, if you don't want to create special users and etc., but at the same time, you would like to cache some information into TimesTen, please use this new feature.
Before using this feature you should make some preparation:
I should create the oratt user as well.
"The
For example:
As you can see, I loaded the result of the sql query into t_sql_load table. In this case TimesTen created the t_sql_load table, but I can also load the data into existing table.
Before using this feature you should make some preparation:
- You should create user (or use the existing user) on Oracle DB site (plus grants).
- DSN should have the same national database character set.
- specify the DSN attributes which are needed for connection to Oracle DB.
[oracle@db ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Nov 7 18:55:28 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 d.username from dba_users d where user in ('TIMESTEN', 'CACHEADMIN');
USERNAME
------------------------------
SYS
SYSTEM
ORATT
...
31 rows selected.
SQL> select d.username from dba_users d where user in ('TIMESTEN', 'CACHEADMIN');
no rows selected
SQL>
As you can see, there are no any specific TimesTen users as well. In TimesTen, we need to specify some DSN attributes (OracleNetServiceName,DatabaseCharacterSet,OraclePWD). In this case I use the following DSN:
[ds_test] Driver=/u01/app/oracle/product/11.2.2.4/TimesTen/tt11224/lib/libtten.so DataStore=/u01/app/oracle/product/datastore/ds_test LogDir=/u01/app/oracle/product/datastore/ds_test/log PermSize=1024 TempSize=128 PLSQL=1 NLS_LENGTH_SEMANTICS=BYTE DatabaseCharacterSet=WE8MSWIN1252 PLSQL_TIMEOUT=1000 OracleNetServiceName=orcl OraclePWD=oracle
I should create the oratt user as well.
Command> CREATE USER oratt IDENTIFIED BY oracle; User created. Command> grant create session to oratt;That is all. I don't need to do anything else for loading data. Now I can load data. There are two methods of loading information into TimesTen:
- using
createandloadfromoraquerycommand - using
ttTableSchemaFromOraQueryGetandttLoadFromOracleprocedures
"The
ttIsql utility provides the createandloadfromoraquery command that, once provided the TimesTen table name and the SELECT statement, will automatically create the TimesTen table, execute the SELECT statement on Oracle, and load the result set into the TimesTen table."For example:
[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> tables;
0 tables found.
Command>
Command> createandloadfromoraquery t_sql_load select level id,
> level val_1,
> level val_2,
> level val_3
> from dual
> connect by level <= 100;
Mapping query to this table:
CREATE TABLE "ORATT"."T_SQL_LOAD" (
"ID" number,
"VAL_1" number,
"VAL_2" number,
"VAL_3" number
)
Table t_sql_load created
100 rows loaded from oracle.
Command> tables;
ORATT.T_SQL_LOAD
1 table found.
Command> select count(*) from t_sql_load;
< 100 >
1 row found.
Command>
As you can see, I loaded the result of the sql query into t_sql_load table. In this case TimesTen created the t_sql_load table, but I can also load the data into existing table.
Command> truncate table t_sql_load;
Command> select count(*) from t_sql_load;
< 0 >
1 row found.
Command> createandloadfromoraquery t_sql_load select level id,
> level val_1,
> level val_2,
> level val_3
> from dual
> connect by level <= 100;
Warning 2207: Table ORATT.T_SQL_LOAD already exists
100 rows loaded from oracle.
Command> select count(*) from t_sql_load; < 100 >
1 row found.
Command>
In this case I've received the 2207 warning. It is very simple :)
let's have a look on the second option. Documentation says:
"The ttTableSchemaFromOraQueryGet built-in procedure evaluates the user-provided SELECT statement to generate a CREATE TABLE statement that can be executed to create a table on TimesTen, which would be appropriate to receive the result set from the SELECT statement. The ttLoadFromOracle built-in procedure executes the SELECT statement on Oracle and loads the result set into the TimesTen table."
For example;
Command> drop table t_sql_load;
Command> tables;
0 tables found.
Command>
Command> call ttTableSchemaFromOraQueryGet('oratt','t_sql_load','select level id,level val_1, level val_2,level val_3 from dual connect by level <= 100');
< CREATE TABLE "ORATT"."T_SQL_LOAD" (
"ID" number,
"VAL_1" number,
"VAL_2" number,
"VAL_3" number
) >
1 row found.
Command>
ttTableSchemaFromOraQueryGet procedure is telling us which create table statement we should manually execute. Ok, execute the following statement.
Command> CREATE TABLE "ORATT"."T_SQL_LOAD" (
> "ID" number,
> "VAL_1" number,
> "VAL_2" number,
> "VAL_3" number
> );
Command> tables;
ORATT.T_SQL_LOAD
1 table found.
Command> call ttloadfromoracle ('oratt','t_sql_load','select level id,level val_1, level val_2,level val_3 from dual connect by level <= 100');
< 100 >
1 row found.
Command> select count(*) from t_sql_load;
< 100 >
1 row found.
Command>
Now, we loaded all necessary information into TimesTen. It is also very easy.
The second method is more flexible I think. So, now you can see how easy to load data from Oracle DB into TimesTen using this sql query feature.
One more thing. As you now, cache groups in TimesTen have some naming restrictions. For example, you can't create a cache group on a table in Oracle DB which contains symbol "#". This feature helps in this situation as well.
For example:
SQL> show user USER is "ORATT" SQL> create table tab# as 2 select level id, 3 level val_1, 4 level val_2, 5 level val_3 6 from dual 7 connect by level <= 100; Table created. SQL>In TimesTen:
Command> drop table t_sql_load;
Command> tables;
0 tables found.
Command> createandloadfromoraquery "oratt.tab#" select * from tab#;
Mapping query to this table:
CREATE TABLE "ORATT"."TAB#" (
"ID" number,
"VAL_1" number,
"VAL_2" number,
"VAL_3" number
)
Table tab# created
100 rows loaded from oracle.
Command> tables;
ORATT.TAB#
1 table found.
Command> select count(*) from tab#;
< 100 >
1 row found.
Command>
Oracle TimesTen 11.2.2.4.0 was released
The new TimesTen version is now available on OTN (http://www.oracle.com/technetwork/products/timesten/downloads/index.html).
Some of the new features:
PS. What will be in TimesTen 12c vertion?
Some of the new features:
- This release contains an Index Adviser that can be used to recommend a set of indexes that can improve the performance of a specific workload.
- You can now load a TimesTen table with the result of a query executed on an Oracle database. This feature does not require you to create a cache group.
- The TimesTen Cache Advisor provides recommendations on how to initially configure a cache schema, to identify porting issues and to estimate the performance improvement of a specific workload. The Cache Advisor is available on Linux x86-64.
PS. What will be in TimesTen 12c vertion?
Подписаться на:
Сообщения (Atom)
