CommitBufferSizeMax connection attribute to
allow users to configure the size of the transaction commit buffer to improve
the performance of commit or rollback of large transactionsttStats, provides the ability to monitor TimesTen
database activities
shutdown immediate; startup mount; alter database archivelog; alter database open; select log_mode from v$database; alter database add supplemental log data; alter system set recyclebin=off scope=spfile; purge dba_recyclebin;In this case I've enabled acrhivelog mode for database, added additional information into log files and turned off the recyclebin feature. Next, create a schema that will contain the Oracle GoldenGate DDL objects (this step is not necessary, because the DDL replication doesn't work in heterogenous environment).
create tablespace gg_tbls datafile '/u01/app/oracle/oradata/orcl/gg_tbls.dbf' size 100m reuse autoextend on; create user ggate identified by oracle default tablespace gg_tbls quota unlimited on gg_tbls; grant create session, connect, resource to ggate; grant dba to ggate; -- just in case grant execute on utl_file to ggate;Next, run the following scripts:
SQL> @marker_setup.sql SQL> @ddl_setup.sql SQL> @role_setup.sql SQL> grant GGS_GGSUSER_ROLE to ggate; SQL> @ddl_enable.sql3. Create the schemes and tables for replication on both hosts (db, tt1).
create user source identified by oracle default tablespace users temporary tablespace temp; grant connect,resource,unlimited tablespace to source; connect source/oracle create table t1 (id number primary key, name varchar2(50));Next, prepare the target database (host tt1) for replication. Create a following DSN (Don't forget that database character set should be the same on both hosts):
[gg_rep] Driver=/u01/app/oracle/product/11.2.2.4.1/TimesTen/tt1122/lib/libtten.so DataStore=/u01/app/oracle/product/datastore/gg_rep PermSize=80 TempSize=32 PLSQL=1 DatabaseCharacterSet=WE8MSWIN1252 ... [gg_rep_cl] TTC_SERVER=tt1 TTC_SERVER_DSN=gg_repI've created the two DSN, because Golden Gate uses a client\server connection to TimesTen. Next, create a user and table for replication.
[oracle@tt1 gg]$ ttisql gg_rep create user target identified by oracle; grant connect, create table to target; grant admin to target; connect "DSN=gg_rep;UID=target;PWD=oracle"; create table t1 (id number primary key, name varchar2(50));4. Golden Gate configuration
[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>Start manager process.
GGSCI (db.us.oracle.com) 2> edit params mgr PORT 7809 GGSCI (db.us.oracle.com) 3> start manager Manager started. GGSCI (db.us.oracle.com) 4> info mgr Manager is running (IP port db.us.oracle.com.7809). GGSCI (db.us.oracle.com) 5>Login into database and add additional information about primary keys into log files and create extract.
dblogin userid ggate password oracle add schematrandata source add extract ext1, tranlog, begin now add exttrail /u01/app/oracle/product/gg/dirdat/tr, extract ext1Edit file ext1 like the following:
GGSCI (db.us.oracle.com) 6> edit params ext1 extract ext1 userid ggate, password oracle exttrail /u01/app/oracle/product/gg/dirdat/tr, format release 11.1 table source.t1;I added the parameter "format release 11.1", because I use 11.1.1 Golden Gate version on target. Then, add one more extract.
add extract pump1, exttrailsource /u01/app/oracle/product/gg/dirdat/tr , begin now add rmttrail /u01/app/oracle/product/gg/dirdat/tr, extract pump1Edit file pump1 like the following:
GGSCI (db.us.oracle.com) 7> edit params pump1 EXTRACT pump1 USERID ggate, PASSWORD oracle RMTHOST tt1, MGRPORT 7809 RMTTRAIL /u01/app/oracle/product/gg/dirdat/tr, format release 11.1 PASSTHRU table source.t1;After that run all extracts.
start extract ext1 start extract pump1 info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING EXT1 00:00:00 00:00:28 EXTRACT RUNNING PUMP1 00:00:00 00:00:034.2. Creating DEF file on source host.
2013-02-20 12:27:14 ERROR OGG-01163 Oracle GoldenGate Delivery for TimesTen, rep1.prm: Bad column length (8) specified for column ID in table SOURCE.T1, maximum allowable length is 3. 2013-02-20 12:27:14 ERROR OGG-01668 Oracle GoldenGate Delivery for TimesTen, rep1.prm: PROCESS ABENDING.So, let's create DEF file. The parameter file is the following (./dirprm/defgen.prm):
defsfile ./dirdef/tt.defs userid ggate password oracle table source.t1;Then, run defgen utility.
./defgen paramfile ./dirprm/defgen.prmBUT, this method doesn't work in my case (I use different Golden Gate versions). Details in support.oracle.com (BUG:13999007 - SOURCE DEF FILE INCORRECT, CAUSE REPLICAT READ INCORRECT). Solution is - using NOEXTATTR parameter (Replicat abend with ERROR OGG-00303 Problem at line xx. Expecting file, table, or record definition. [ID 1455370.1]). In my case I have to create a DEF file by the following command:
./defgen paramfile ./dirprm/defgen.prm NOEXTATTRIn this case DEF file will have a suitable format for 11.1.1 version. Copy DEF file (/dirdef/tt.defs) to tt1 host.
GGSCI (tt1) 1> EDIT PARAMS ./GLOBALS CHECKPOINTTABLE target.ggchkptStart manager process.
GGSCI (tt1) 2> edit params mgr PORT 7809 GGSCI (tt1) 3> start manager Manager started. GGSCI (tt1) 4> info mgr Manager is running (IP port tt1.7809). GGSCI (tt1) 5>Create the checkpoint table and add replicat.
dblogin SOURCEDB gg_rep_cl userid target password oracle add checkpointtable target.ggchkpt add replicat rep1, exttrail /u01/app/oracle/product/gg/dirdat/tr, begin nowEdit file rep1 and start replicat.
GGSCI (tt1) 5> edit params rep1 REPLICAT rep1 SOURCEDEFS /u01/app/oracle/product/gg/dirdef/tt.defs TARGETDB gg_rep_cl, USERID target, PASSWORD oracle discardfile /u01/app/oracle/product/gg/discard/rep1_discard.txt, append, megabytes 10 map source.t1, target target.t1; GGSCI (tt1) 6> start replicat rep1 Sending START request to MANAGER ... REPLICAT REP1 starting GGSCI (tt1) 7> info all Program Status Group Lag Time Since Chkpt MANAGER RUNNING REPLICAT RUNNING REP1 00:00:00 00:00:035. Checking replication
[oracle@db ggate]$ sqlplus source/oracle SQL*Plus: Release 11.2.0.3.0 Production on Mon Feb 25 15:25:38 2013 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; no rows selected SQL>TimesTen.
[oracle@tt1 gg]$ ttisql "DSN=gg_rep;UID=target;PWD=oracle" Copyright (c) 1996-2011, Oracle. All rights reserved. Type ? or "help" for help, type "exit" to quit ttIsql. connect "DSN=gg_rep;UID=target;PWD=oracle"; Connection successful: DSN=gg_rep;UID=target;DataStore=/u01/app/oracle/product/datastore/gg_rep;DatabaseCharacterSet=WE8MSWIN1252;ConnectionCharacterSet=US7ASCII;DRIVER=/u01/app/oracle/product/11.2.2.4.1/TimesTen/tt1122/lib/libtten.so;PermSize=80;TempSize=32;TypeMode=0; (Default setting AutoCommit=1) Command> select * from t1; 0 rows found. Command>Insert a row into t1 table.
SQL> insert into t1 values (1,'test');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t1;
ID NAME
---------- --------------------------------------------------
1 test
SQL>
Check TimesTen.
Command> select * from t1; < 1, test > 1 row found. Command>Now, update the row.
SQL> update t1 set name='test3' where id = 1;
1 row updated.
SQL> commit;
Commit complete.
SQL> select * from t1;
ID NAME
---------- --------------------------------------------------
1 test3
SQL>
Check TimesTen.
Command> select * from t1; < 1, test3 > 1 row found. Command>Replication works fine.
[oracle@db ~]$ sqlplus oratt/oracle
SQL*Plus: Release 11.2.0.3.0 Production on Thu Feb 7 18:16:27 2013
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> ! cat Factorial.java
public class Factorial {
public static int getFactorialValue(int a) {
if (a == 1) return 1;
else return a * getFactorialValue(a - 1);
}
}
SQL>
There are a lot of options for loading the java class (I'll describe only two of them).SQL> ! loadjava -user oratt/oracle Factorial.java SQL> select object_name, object_type from user_objects where object_type like 'J%'; SQL> select object_name, object_type from user_objects where object_type like 'J%'; OBJECT_NAME OBJECT_TYPE ------------------------------ ------------------- Factorial JAVA SOURCE Factorial JAVA CLASS SQL>2. Create java statement Before that let's delete Factorial class from database
SQL> !dropjava -user oratt/oracle Factorial.java
SQL> select object_name, object_type from user_objects where object_type like 'J%';
no rows selected
SQL> CREATE JAVA SOURCE NAMED "Factorial" AS
2 public class Factorial {
3 public static int getFactorialValue(int a) {
4 if (a == 1) return 1;
5 else return a * getFactorialValue(a - 1);
6 }
7 }
8 /
Java created.
SQL> select object_name, object_type from user_objects where object_type like 'J%';
OBJECT_NAME OBJECT_TYPE
------------------------------ -------------------
Factorial JAVA SOURCE
Factorial JAVA CLASS
SQL>
Now, create a package.
SQL> CREATE OR REPLACE PACKAGE java_pack AS
2 function factorial_func(x in pls_integer) return pls_integer;
3 END java_pack;
4 /
Package created.
SQL> CREATE OR REPLACE PACKAGE BODY java_pack AS
2
3 function factorial_func(x in pls_integer)
4 return pls_integer
5 as language java
6 name 'Factorial.getFactorialValue (int) return int';
7
8 END java_pack;
9 /
Package body created.
SQL>
SQL> exec dbms_output.put_line(java_pack.factorial_func(15));
2004310016
PL/SQL procedure successfully completed.
SQL> select java_pack.factorial_func(10) from dual;
JAVA_PACK.FACTORIAL_FUNC(10)
----------------------------
3628800
SQL>
Is you can see, its very easy to use Java code in PLSQL. There is only one thing you should remember - it is a Oracle Database JVM version (DB Version 11.2 - Java 1.5.0 (1.5.0_01))
I would recommend you to read the following notes:[oracle@db /]$ cd /u01/app/oracle/product/
[oracle@db product]$ ls
11.2.0
[oracle@db product]$ mkdir c_lib
[oracle@db product]$ ls
11.2.0 c_lib
[oracle@db product]$ cd c_lib
[oracle@db c_lib]$ touch factorial.c
[oracle@db c_lib]$ cat factorial.c
int getVal (int a){
if(a!=1){
return(a * getVal(a-1));
}
else return 1;
}
[oracle@db c_lib]$
1.1 Compile it and create a shared library.
[oracle@db c_lib]$ gcc -fPIC -c factorial.c [oracle@db c_lib]$ gcc -shared -o factorial.so factorial.o [oracle@db c_lib]$ ls factorial.c factorial.o factorial.so [oracle@db c_lib]$Now we have a shared library. Lets set up the listener.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = extproc))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=/u01/app/oracle/product/c_lib/factorial.so")
)
)
ADR_BASE_LISTENER = /u01/app/oracle
Restart listener.
[oracle@db admin]$ lsnrctl stop listener LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 05-FEB-2013 15:31:01 Copyright (c) 1991, 2011, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=db)(PORT=1521))) The command completed successfully [oracle@db admin]$ lsnrctl start listener LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 05-FEB-2013 15:31:09 Copyright (c) 1991, 2011, Oracle. All rights reserved. Starting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 11.2.0.3.0 - Production System parameter file is /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora Log messages written to /u01/app/oracle/diag/tnslsnr/db/listener/alert/log.xml Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db)(PORT=1521))) Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=extproc))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=db)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias listener Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production Start Date 05-FEB-2013 15:31:09 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/db/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=extproc))) Services Summary... Service "PLSExtProc" has 1 instance(s). Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully [oracle@db admin]$Check the listener status:
[oracle@db admin]$ lsnrctl status listener LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 05-FEB-2013 15:32:13 Copyright (c) 1991, 2011, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=db)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias listener Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production Start Date 05-FEB-2013 15:31:09 Uptime 0 days 0 hr. 1 min. 3 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/db/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=extproc))) Services Summary... Service "PLSExtProc" has 1 instance(s). Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service... Service "orcl" has 1 instance(s). Instance "orcl", status READY, has 1 handler(s) for this service... Service "orclXDB" has 1 instance(s). Instance "orcl", status READY, has 1 handler(s) for this service... The command completed successfully3. Create a library (as sysdba)
[oracle@db c_lib]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Mon Feb 4 18:01:02 2013 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> create or replace library c_factorial as '/u01/app/oracle/product/c_lib/factorial.so' 2 / Library created. SQL> select owner, library_name, file_spec, status from all_libraries where lower(library_name) = 'c_factorial'; OWNER LIBRARY_NAME FILE_SPEC STATUS --------- --------------- ------------------------------------------ ------- SYS C_FACTORIAL /u01/app/oracle/product/c_lib/factorial.so VALID SQL> grant execute on c_factorial to oratt; Grant succeeded. SQL>4. Create a package (oratt user).
SQL> CREATE OR REPLACE PACKAGE c_pack AS 2 function factorial_func(x in integer) return pls_integer; 3 END; 4 / Package created. SQL> CREATE OR REPLACE PACKAGE BODY c_pack AS 2 function factorial_func(x in integer) 3 return pls_integer 4 as language c 5 library sys.c_factorial 6 name "getVal"; 7 END; 8 / Package Body created.5. Execute factorial_func function
SQL> set serveroutput on
SQL> begin
2 dbms_output.put_line(c_pack.factorial_func(5));
3 end;
4 /
120
PL/SQL procedure successfully completed.
SQL> select c_pack.factorial_func(5) from dual;
C_PACK.FACTORIAL_FUNC(5)
------------------------
120
SQL>
SQL> create table test1 (id number, name varchar2(100));
Table created.
SQL> insert into test1 select level, 'name'||level from dual connect by level <= 10;
10 rows created.
SQL> commit;
Commit complete.
SQL> select * from test1;
ID NAME
---------- -----------------------------------------
1 name1
2 name2
3 name3
4 name4
5 name5
6 name6
7 name7
8 name8
9 name9
10 name10
10 rows selected.
SQL> create table test2 (id number);
Table created.
SQL>
SQL> create or replace function func_sql_run_test (p_id in test1.id%type)
2 return test1.name%type
3 is
4 v_name test1.name%type;
5 begin
6 insert into test1 (id, name) values (-1,'test'); -- DML
7 return 'test';
8 end func_sql_run_test;
9 /
Function created.
SQL> select func_sql_run_test(1) from test1 where id =1;
select func_sql_run_test(1) from test1 where id =1
*
ERROR at line 1:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "ORATT.FUNC_SQL_RUN_TEST", line 6
SQL>
SQL> create or replace function func_sql_run_test (p_id in test1.id%type)
2 return test1.name%type
3 is
4 v_name test1.name%type;
5 begin
6 select name into v_name from test1 where id = p_id;
7 return v_name;
8 end func_sql_run_test;
9 /
Function created.
SQL> update test1 set name = func_sql_run_test(1) where id = 10;
update test1 set name = func_sql_run_test(1) where id = 10
*
ERROR at line 1:
ORA-04091: table ORATT.TEST1 is mutating, trigger/function may not see it
ORA-06512: at "ORATT.FUNC_SQL_RUN_TEST", line 6
SQL> delete from test1 where name = func_sql_run_test(1);
delete from test1 where name = func_sql_run_test(1)
*
ERROR at line 1:
ORA-04091: table ORATT.TEST1 is mutating, trigger/function may not see it
ORA-06512: at "ORATT.FUNC_SQL_RUN_TEST", line 6
SQL> insert into test1 (id, name) select 11, func_sql_run_test(1) from dual;
insert into test1 (id, name) select 11, func_sql_run_test(1) from dual
*
ERROR at line 1:
ORA-04091: table ORATT.TEST1 is mutating, trigger/function may not see it
ORA-06512: at "ORATT.FUNC_SQL_RUN_TEST", line 6
SQL> -- insert works fine
SQL> insert into test1 (id, name) values (11, func_sql_run_test(1));
1 row created.
SQL> rollback;
Rollback complete.
SQL>
SQL> create or replace function func_sql_run_test (p_id in test1.id%type)
2 return test1.name%type
3 is
4 v_name test1.name%type;
5 begin
6 insert into test1 (id, name) values (-1,'test'); -- DML
7 return 'test';
8 end func_sql_run_test;
9 /
Function created.
SQL> update test1 set name = func_sql_run_test(1) where id = 10;
update test1 set name = func_sql_run_test(1) where id = 10
*
ERROR at line 1:
ORA-04091: table ORATT.TEST1 is mutating, trigger/function may not see it
ORA-06512: at "ORATT.FUNC_SQL_RUN_TEST", line 6
SQL> delete from test1 where name = func_sql_run_test(1);
delete from test1 where name = func_sql_run_test(1)
*
ERROR at line 1:
ORA-04091: table ORATT.TEST1 is mutating, trigger/function may not see it
ORA-06512: at "ORATT.FUNC_SQL_RUN_TEST", line 6
SQL> insert into test1 (id, name) select 11, func_sql_run_test(1) from dual;
insert into test1 (id, name) select 11, func_sql_run_test(1) from dual
*
ERROR at line 1:
ORA-04091: table ORATT.TEST1 is mutating, trigger/function may not see it
ORA-06512: at "ORATT.FUNC_SQL_RUN_TEST", line 6
SQL> -- insert works fine
SQL> insert into test1 (id, name) values (11, func_sql_run_test(1));
1 row created.
SQL> rollback;
Rollback complete.
SQL>
SQL> create or replace function func_sql_run_test (p_id in test1.id%type)
2 return test1.name%type
3 is
4 v_name test1.name%type;
5 begin
6 insert into test2 (id) values (-1);
7 return 'test';
8 end func_sql_run_test;
9 /
Function created.
SQL> update test1 t set name = func_sql_run_test(1) where id = 10;
1 row updated.
SQL> select * from test2;
ID
----------
-1
SQL> delete from test1 where name = func_sql_run_test(1);
1 row deleted.
SQL> rollback;
Rollback complete.
SQL> insert into test1 (id, name) select 11, func_sql_run_test(1) from dual;
1 row created.
SQL> insert into test1 (id, name) values (11, func_sql_run_test(1));
1 row created.
SQL> rollback;
Rollback complete.
Additionally, there are some additional restrictions related with transactions (commit, rollback, DDL,DCL and etc.) Of course, all these restrictions are obvious, but I still decided to write about it because it's quite important and I can use this post as a hint :).
declare
v_user_name clob;
begin
...
select wm_concat('''' ||username) || ''''),
into v_user_name
from customers;
...
execute immediate
'insert into temp_customer (user_name)
select username
from tab_1
where username in (' || v_user_name || ')';
...
end;
As you can see, v_user_name variable is populated by the first select statement. It contains number of customers divided by comma (why has my colleague decided to use undocumented "wm_concat" function - its another question, I hope there was a reason for that :) ) and after that, we use v_user_name variable into dynamic sql.
This code had been working fine for years, but recently we had to load a lot of data (a lot of customers) into DWH and we've faced with the ORA-01795.
I found a topic in OTN forum about this issue.
