Sample

create user ogg_maclean identified by oracle;
grant connect,resource to OGG_MACLEAN;

OGG_MACLEAN


maclean_press

create table maclean_press1(a int constraint key1 primary key,b varchar2(100),c varchar2(100),d date default sysdate,e date);
create table maclean_press2(a int constraint key2 primary key,b varchar2(100),c varchar2(100),d date default sysdate,e date);
create table maclean_press3(a int constraint key3 primary key,b varchar2(100),c varchar2(100),d date default sysdate,e date);
create table maclean_press4(a int constraint key4 primary key,b varchar2(100),c varchar2(100),d date default sysdate,e date);
create table maclean_press5(a int constraint key5 primary key,b varchar2(100),c varchar2(100),d date default sysdate,e date);
create table maclean_press6(a int constraint key6 primary key,b varchar2(100),c varchar2(100),d date default sysdate,e date);
create table maclean_press7(a int constraint key7 primary key,b varchar2(100),c varchar2(100),d date default sysdate,e date);
create table maclean_press8(a int constraint key8 primary key,b varchar2(100),c varchar2(100),d date default sysdate,e date);
create table maclean_press9(a int constraint key9 primary key,b varchar2(100),c varchar2(100),d date default sysdate,e date);
create table maclean_press10(a int constraint key10 primary key,b varchar2(100),c varchar2(100),d date default sysdate,e date);



create or replace procedure trouble_ogg_sql as 
begin 
	for i in 1..2000000 loop
		insert into maclean_press1(a,b,c,d) values(i,i,i,sysdate);
		insert into maclean_press2(a,b,c,d) values(i,i,i,sysdate);
		insert into maclean_press3(a,b,c,d) values(i,i,i,sysdate);
		insert into maclean_press4(a,b,c,d) values(i,i,i,sysdate);
		insert into maclean_press5(a,b,c,d) values(i,i,i,sysdate);
		insert into maclean_press6(a,b,c,d) values(i,i,i,sysdate);
		insert into maclean_press7(a,b,c,d) values(i,i,i,sysdate);
		insert into maclean_press8(a,b,c,d) values(i,i,i,sysdate);
		insert into maclean_press9(a,b,c,d) values(i,i,i,sysdate);
		insert into maclean_press10(a,b,c,d) values(i,i,i,sysdate);
		if mod(i,2000)=0 then
			commit;
		end if;
	end loop;

		delete from maclean_press1 where a <= 10000;
 		commit;
EXECUTE IMMEDIATE('alter table maclean_press1 move');
EXECUTE IMMEDIATE('alter index key1 rebuild');
		delete from maclean_press2 where a <= 10000;
		commit;
EXECUTE IMMEDIATE('alter table maclean_press2 move');
EXECUTE IMMEDIATE('alter index key2 rebuild');
delete from maclean_press3 where a <= 10000;
		commit;
EXECUTE IMMEDIATE('alter table maclean_press3 move');
EXECUTE IMMEDIATE('alter index key3 rebuild');
delete from maclean_press4 where a <= 10000;
    commit;		
EXECUTE IMMEDIATE('alter table maclean_press4 move');
EXECUTE IMMEDIATE('alter index key4 rebuild');
delete from maclean_press5 where a <= 10000;
commit;
EXECUTE IMMEDIATE('alter table maclean_press5 move');
EXECUTE IMMEDIATE('alter index key5 rebuild');
delete from maclean_press6 where a <= 10000;
commit;
EXECUTE IMMEDIATE('alter table maclean_press6 move');
EXECUTE IMMEDIATE('alter index key6 rebuild');
delete from maclean_press7 where a <= 10000;
commit;
EXECUTE IMMEDIATE('alter table maclean_press7 move');
EXECUTE IMMEDIATE('alter index key7 rebuild');
delete from maclean_press8 where a <= 10000;
commit;
EXECUTE IMMEDIATE('alter table maclean_press8 move');
EXECUTE IMMEDIATE('alter index key8 rebuild');
delete from maclean_press9 where a <= 10000;
commit;
EXECUTE IMMEDIATE('alter table maclean_press9 move');
EXECUTE IMMEDIATE('alter index key9 rebuild');
delete from maclean_press10 where a <= 10000;
		commit;
EXECUTE IMMEDIATE('alter table maclean_press10 move');
EXECUTE IMMEDIATE('alter index key10 rebuild');


for i in 1..1 loop
		update maclean_press1 set b=b+1 where a <= 20000;
 		commit;
		update maclean_press2 set b=b+1 where a <= 20000;
		commit;
		update maclean_press3 set b=b+1 where a <= 20000;
		commit;
		update maclean_press4 set b=b+1 where a <= 20000;
    commit;		
		update maclean_press5 set b=b+1 where a <= 20000;
commit;
		update maclean_press6 set b=b+1 where a <= 20000;
commit;
		update maclean_press7 set b=b+1 where a <= 20000;
commit;
		update maclean_press8 set b=b+1 where a <= 20000;
commit;
		update maclean_press9 set b=b+1 where a <= 20000;
commit;
		update maclean_press10 set b=b+1 where a <= 20000;
		commit;
end loop;

for i in 1..1 loop
		delete from maclean_press1 where a > 30000 and a <= 40000;
 		commit;
		delete from maclean_press2 where a > 30000 and a <= 40000;
		commit;
delete from maclean_press3 where a > 30000 and a <= 40000;
		commit;
delete from maclean_press4 where a > 30000 and a <= 40000;
    commit;		
delete from maclean_press5 where a > 30000 and a <= 40000;
commit;
delete from maclean_press6 where a > 30000 and a <= 40000;
commit;
delete from maclean_press7 where a > 30000 and a <= 40000;
commit;
delete from maclean_press8 where a > 30000 and a <= 40000;
commit;
delete from maclean_press9 where a > 30000 and a <= 40000;
commit;
delete from maclean_press10 where a > 30000 and a <= 40000;
		commit;
end loop;
end;
/


select count(*),sum(a),sum(b) from maclean_press1;
select count(*),sum(a),sum(b) from maclean_press2;
select count(*),sum(a),sum(b) from maclean_press3;
select count(*),sum(a),sum(b) from maclean_press4;
select count(*),sum(a),sum(b) from maclean_press5;
select count(*),sum(a),sum(b) from maclean_press6;
select count(*),sum(a),sum(b) from maclean_press7;
select count(*),sum(a),sum(b) from maclean_press8;
select count(*),sum(a),sum(b) from maclean_press9;
select count(*),sum(a),sum(b) from maclean_press10;

主键更新测试（针对Quest）


create table tb1a (id number primary key, name varchar(30));
执行以下sql进行主键更新：
Begin
for i in 1..1000 loop
insert into tb1a values (i, 'aaa');
end loop;
commit;
update tb1a set id=id+100;
commit;
end;
/

select min(id),max(id),sum(id) from tb1a;




分区表

 create table maclean_partition (acct_no number(12),person varchar2(30),week_no number(2)) partition by range (week_no) (partition jan values less than(4),partition feb values less than(8),partition others values less than (maxvalue)) enable row movement;
 
  insert into maclean_partition values(1,'a',2);
 insert into maclean_partition values(2,'b',6);
 insert into maclean_partition values(3,'c',10);
 insert into maclean_partition values(6,'d',6);
 insert into maclean_partition values(8,'e',8);
 insert into maclean_partition values(9,'e',9);
 commit;
 update maclean_partition set person='d' where acct_no=2;
 commit;
 delete maclean_partition where acct_no=1;
 commit;




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


ASM 的 tnsnames.ora 以及配置监听静态注册



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

解压软件，配置ogg.sh 

export  GG_HOME=/goldengate
export  LD_LIBRARY_PATH=$ORACLE_HOME/lib   

	GGSCI> create subdirs

打开归档

Select log_mode from v$database;

shutdown immediate;
startup mount;
alter database archivelog;
alter database open;




Select  
SUPPLEMENTAL_LOG_DATA_MIN
,SUPPLEMENTAL_LOG_DATA_PK
,SUPPLEMENTAL_LOG_DATA_UI
,SUPPLEMENTAL_LOG_DATA_FK
,SUPPLEMENTAL_LOG_DATA_ALL from v$database;


alter database add supplemental log data ;
--alter database add supplemental log data (primary key, unique,foreign key) columns;
alter system switch logfile;

Select  
SUPPLEMENTAL_LOG_DATA_MIN
,SUPPLEMENTAL_LOG_DATA_PK
,SUPPLEMENTAL_LOG_DATA_UI
,SUPPLEMENTAL_LOG_DATA_FK
,SUPPLEMENTAL_LOG_DATA_ALL from v$database;


Alter database force logging;





创建ogg 用户

--create tablespace
create tablespace goldengate datafile  size 1024M ;

-- Create the user 
create user goldengate identified by &A default tablespace goldengate;

-- Grant role privileges 
grant  resource, connect, dba to goldengate;



===========================================================================================================
[oracle@vrh1 ~]$ cat ogg.sh



export ORACLE_HOME=/s01/orabase/product/11.2.0/dbhome_1
export ORACLE_SID=VRAC1
export LD_LIBRARY_PATH=/ogg:$ORACLE_HOME/lib:$ORACLE_HOME/lib32
export PATH=/home/oracle/ogg:$PATH

cd /home/oracle/ogg
ggsci 


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

./GLOBALS


add checkpointable ckpt


GGSCHEMA goldengate
CheckpointTable goldengate.ckpt
UnlockedTrailFiles

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

MGR



Port 7809
userid goldengate , password oracle
DYNAMICPORTLIST 9101-9356
CheckMinutes 10
PurgeOldExtracts ./dirdat/*, UseCheckpoints, MinKeepHours 96
PurgeMarkerHistory MinKeepDays 3, MaxKeepDays 7, FrequencyMinutes 120
AutoRestart ER *, WaitMinutes 5, Retries 3
LagInfoMinutes 0
LagReportMinutes 10


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

源端DDL

 GRANT EXECUTE ON UTL_FILE TO goldengate;
 
  ALTER SYSTEM SET RECYCLEBIN = OFF SCOPE = BOTH;
  
   @marker_setup.sql
    @ddl_setup.sql
	@role_setup.sql
	 @ddl_enable.sql
	 @ddl_pin.sql
	 @sequence.sql
	 
dblogin userid  goldengate ,	 password oracle
add checkpointable ckpt
	 
ADD TRANDATA XX.XX
	 
===========================================================================================================

add extract ext01, tranlog , begin now , threads 2 
add exttrail ./dirdat/me , extract ext01 , megabytes 200


extract ext01
SETENV (ORACLE_HOME = "<Oracle home path>" )
SETENV (ORACLE_SID="<Oracle sid>")
--TRANLOGOPTIONS EXCLUDEUSER <Replicat username>
--CacheMgr CacheDirectory ./dirtmp 51980MB, CacheDirectory ./dirtmp/goldengate_tmp
userid goldengate , password oracle
--TranLogOptions ExcludeUser goldengate
--TranLogOptions AltArchivedLogFormat Instance NETDB1 %t_%s_%r.dbf
--TranLogOptions AltArchivedLogFormat Instance NETDB2 %t_%s_%r.dbf
--TranLogOptions AltArchiveLogDest Primary Instance NETDB1 /arch1, AltArchiveLogDest Instance NETDB2 /arch2
tranlogoptions asmuser sys@ASM , ASMPASSWORD oracle
--TRANLOGOPTIONS DBLOGREADER, DBLOGREADER BUFSIZE [x], BUFSIZE [y]
ThreadOptions MaxCommitPropagationDelay 10000 IOLatency 3000
exttrail ./dirdat/me
DDL Include ALL
--DDL INCLUDE ALL, EXCLUDE OBJNAME "<schema name>.<object name> "
DDLOptions AddTranData, Report
DDLOptions NoCrossRename, Report
Table TESTGG.*;



-- Prevent data looping. This is generally used in bi-directional
-- configuration
TRANLOGOPTIONS EXCLUDEUSER <Replicat username>
===========================================================================================================
add extract pump01, extrailsource ./dirdat/me 
add rmttrail ./dirdat/mr , extract pump01, megabytes 200


pump 

extract dpe01
SETENV (ORACLE_HOME = "<Oracle home path>" )
SETENV (ORACLE_SID="<Oracle sid>")

passthru
rmthost 192.168.1.179 , mgrport 7809 
rmttrail dirdat/l2
--DISCARDFILE <diacard file path/<discard file name.dsc>, APPEND Megabytes <n>
table testgg.*;


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

add replicat rep01, exttrail ./dirdat/mr



replicat 



replicat rep1
SETENV (ORACLE_HOME = "<Oracle home path>" )
SETENV (ORACLE_SID="<Oracle sid>")
SETENV (NLS_LANG ="American.america_AL32UTF8")
userid goldengate , password goldengate
--HandleCollisions
AssumeTargetDefs
DiscardFile ./dirrpt/rep1.dsc, APPEND Megabytes 800 ,  Purge
DBOptions DeferrefConst
DBOptions Supmaclean_pressTriggers    
MaxTransOps 10000
GroupTransOps 1000
SQLEXEC "ALTER SESSION SET COMMIT_WRITE = BATCH,NOWAIT"
BatchSQL
--DDL INCLUDE ALL, EXCLUDE OBJNAME "<schema name>.<object name> "
DDLOptions Report
DDLError 24344 Ignore
DDLError 4052 Ignore
DDLError 955 Ignore
DDLError 1408 Ignore
DDLError 911 Ignore
AllowNoOpUpdates
CheckSequenceValue
--IGNORETRUNCATES
--DEFERAPPLYINTERVAL 1 MINUTES
-- Sequence testgg.*, Target testgg.*
--MapExclude testgg.SYS_EXPORT_SCHEMA*
map testgg.*, target testgg.*;



--The SUPmaclean_pressTRIGGERS parameter prevents triggers from firing on target objects that are configured for replication with Oracle GoldenGate. This alleviates the need to manually disable triggers and constraints.  To use this option, the Replicat user must be an Oracle Streams administrator which can be granted by invoking dbms_goldengate_auth.grant_admin_privilege.






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


DROP TABLE gg_test;
CREATE TABLE gg_test
(
    a        number(10),
    b             VARCHAR20(30),
        PRIMARY KEY (a)
        );
4.8	100字段表测试脚本
create table table_100cols
(
a1        number(10) not null,
a2        number(10),
a3        number(10),
a4        number(10),
a5        number(10),
a6        number(10),
a7        number(10),
a8        number(10),
a9        number(10),
a10       number(10),
a11       number(10),
a12       number(10),
a13       number(10),
a14       number(10),
a15       number(10),
a16       number(10),
a17       number(10),
a18       number(10),
a19       number(10),
a20       number(10),
a21       number(10),
a22       number(10),
a23       number(10),
a24       number(10),
a25       number(10),
a26       number(10),
a27       number(10),
a28       number(10),
a29       number(10),
a30       number(10),
a31       number(10),
a32       number(10),
a33       number(10),
a34       number(10),
a35       number(10),
a36       number(10),
a37       number(10),
a38       number(10),
a39       number(10),
a40       number(10),
a41       number(10),
a42       number(10),
a43       number(10),
a44       number(10),
a45       number(10),
a46       number(10),
a47       number(10),
a48       number(10),
a49       number(10),
a50       number(10),
a51       number(10),
a52       number(10),
a53       number(10),
a54       number(10),
a55       number(10),
a56       number(10),
a57       number(10),
a58       number(10),
a59       number(10),
a60       number(10),
a61       number(10),
a62       number(10),
a63       number(10),
a64       number(10),
a65       number(10),
a66       number(10),
a67       number(10),
a68       number(10),
a69       number(10),
a70       number(10),
a71       number(10),
a72       number(10),
a73       number(10),
a74       number(10),
a75       number(10),
a76       number(10),
a77       number(10),
a78       number(10),
a79       number(10),
a80       number(10),
a81       number(10),
a82       number(10),
a83       number(10),
a84       number(10),
a85       number(10),
a86       number(10),
a87       number(10),
a88       number(10),
a89       number(10),
a90       number(10),
a91       number(10),
a92       number(10),
a93       number(10),
a94       number(10),
a95       number(10),
a96       number(10),
a97       number(10),
a98       number(10),
a99       number(10),
a100      number(10)
)

alter table table_100cols
  add constraint PK_100cols primary key (a1)
  using index 
  tablespace CS_stat
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
  
  alter table table_100cols
  add constraint PK_100cols primary key (a1)
  using index 
  tablespace users
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
  
  
  
  insert into table_100cols values(1,
2,
3,
4,
5,
6,
7,
8,
9,
10,
11,
12,
13,
14,
15,
16,
17,
18,
19,
20,
21,
22,
23,
24,
25,
26,
27,
28,
29,
30,
31,
32,
33,
34,
35,
36,
37,
38,
39,
40,
41,
42,
43,
44,
45,
46,
47,
48,
49,
50,
51,
52,
53,
54,
55,
56,
57,
58,
59,
60,
61,
62,
63,
64,
65,
66,
67,
68,
69,
70,
71,
72,
73,
74,
75,
76,
77,
78,
79,
80,
81,
82,
83,
84,
85,
86,
87,
88,
89,
90,
91,
92,
93,
94,
95,
96,
97,
98,
99,
100);
4.9	性能测试脚本
create sequence seq_gg start with 1 INCREMENT BY 1 MAXVALUE 999999999 cache 500000;

create or replace procedure gg_insert
is
begin
	for i in 1..1000000 loop
		insert into CM_USER (USER_ID,REGION_CODE,ACC_ID,CAUT_ID,USER_STATUS,USER_TYPE)
		values(seq_gg.nextval,571,1111,2222,3,4);
		if mod(i,1000)=0 then
			commit;
		end if;
	end loop;
	commit;
end;
/

create or replace procedure gg_update
is
CURSOR c_gg IS
	SELECT rowid FROM CM_USER;
v_rowid UROWID;
i number(10);
BEGIN
	OPEN c_gg;
	for i in 1..1000000 loop
		FETCH c_gg INTO v_rowid;
		EXIT WHEN c_gg%NOTFOUND;
	
		UPDATE CM_USER SET ACC_ID=seq_gg.nextval WHERE rowid = v_rowid;
		if mod(i,1000)=0 then
			commit;
		end if;
	end loop;
	commit;
	CLOSE c_gg;
END;
/

create or replace procedure gg_delete
is
begin
	for i in 1..1000 loop
		delete from  CM_USER where rownum<1001;
		commit;
	end loop;
	commit;
end;
/