【GoldenGate】Oracle GoldenGate(四) 压力测试同步百万行数据

Oracle GoldenGate(四) 压力测试 同步百万行数据

[项目环境]

Item	Primary System	Secondary System
Platform	RHEL5.5	RHEL5.5
Hostname	lvxinghao1	lvxinghao2
Database	Oracle10.2.0.1	Oracle 10.2.0.1
Character Set	ZHS16GBK	ZHS16GBK
ORACLE_SID	prod	test1
Listener Name/Port	LISTENER/1521	LISTENER/1521
Goldengate User	ogg	ogg

[实施步骤]

1       Prepare environment for the test

1.1     Ensrue that the space is enough

[root@lvxinghao1 ~]# df -h Filesystem            Size  Used Avail Use% Mounted on /dev/sda1              12G  3.5G  7.4G  33% / /dev/sda6             2.7G  334M  2.3G  13% /disk2 /dev/sda3             3.8G  1.4G  2.3G  37% /disk1 /dev/sda2             9.7G  3.1G  6.2G  33% /u01 tmpfs                 2.0G     0  2.0G   0% /dev/shm none                  885M  104K  885M   1% /var/lib/xenstored /dev/sdb1             9.2G  171M  8.6G   2% /arch   [root@lvxinghao2 ~]# df -h Filesystem            Size  Used Avail Use% Mounted on /dev/sda1              12G  3.5G  7.4G  33% / /dev/sda6             2.7G  334M  2.3G  13% /disk2 /dev/sda3             3.8G  1.3G  2.4G  35% /disk1 /dev/sda2             9.7G  3.2G  6.1G  34% /u01 tmpfs                 2.0G     0  2.0G   0% /dev/shm none                  885M  104K  885M   1% /var/lib/xenstored /dev/sdb1             9.2G  164M  8.6G   2% /arch

 

1.2     Enable autoextend on undo and scott's tablespace

1.2.1  prod

SCOTT@ prod>select username,default_tablespace from user_users; USERNAME                       DEFAULT_TABLESPACE ------------------------------ ------------------------------ SCOTT                          TEST3 SYS@ prod>show parameter undo_tablespace NAME                                 TYPE        VALUE ------------------------------------ ----------- ---------------------- undo_tablespace                      string      UNDOTBS1   SYS@ prod>select file_id,tablespace_name,autoextensible from dba_data_files;    FILE_ID TABLESPACE_NAME AUTOEXTENSIBLE ---------- --------------- ---------------          4 USERS           YES          3 SYSAUX          YES          2 UNDOTBS1        YES          1 SYSTEM          YES          5 EXAMPLE         YES          7 TBS1            NO          9 TBS_GGUSER      YES          6 STATSPACK       YES          8 TEST3           NO 9 rows selected.   SYS@ prod>alter database datafile 8 autoextend on;

1.2.2  test1

SCOTT@ test1>select username,default_tablespace from user_users; USERNAME                       DEFAULT_TABLESPACE ------------------------------ ------------------------------ SCOTT                          USERS SYS@ test1>show parameter undo_tablespace NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ undo_tablespace                      string      undotbs1   SYS@ test1>select file_id,tablespace_name,autoextensible from dba_data_files;    FILE_ID TABLESPACE_NAME                AUT ---------- ------------------------------ ---          1 SYSTEM                         NO          4 USERS                          NO          3 SYSAUX                         NO          2 UNDOTBS1                       YES          5 TBS_GGUSER                     YES   SYS@ test1>alter database datafile 4 autoextend on;

 

2       Create test table

2.1     Create new table on prod

SCOTT@ prod>create table test as select * from dept where 1=2;      Table created. SCOTT@ prod>alter table test add constraint test_deptno_pk primary key(deptno); Table altered. SCOTT@ prod>alter table test modify deptno number(10);

2.2     Enable transaction data change capture for the new table

GGSCI (lvxinghao2) 39> DBLOGIN USERID ogg, PASSWORD ogg Successfully logged into database.   GGSCI (lvxinghao2) 56> stop replicat rora_1 Sending STOP request to REPLICAT RORA_1 ... Request processed. GGSCI (lvxinghao1) 55> stop replicat rora_2 Sending STOP request to REPLICAT RORA_2 ... Request processed.   GGSCI (lvxinghao1) 56> add trandata scott.test Logging of supplemental redo data enabled for table SCOTT.TEST. GGSCI (lvxinghao2) 60> add trandata scott.test Logging of supplemental redo data enabled for table SCOTT.TEST.   GGSCI (lvxinghao1) 62> start replicat rora_2 Sending START request to MANAGER ... REPLICAT RORA_2 starting GGSCI (lvxinghao2) 61> start replicat rora_1 Sending START request to MANAGER ... REPLICAT RORA_1 starting

 

2.3     Ensure DML operation can be duplicated correctly

21:02:56 SCOTT@ prod>insert into test select * from dept; 21:03:12 SCOTT@ prod>commit; 21:02:38 SCOTT@ test1>select * from test;     DEPTNO DNAME          LOC ---------- -------------- -------------         10 ACCOUNTING     NEW YORK         20 RESEARCH       DALLAS         30 SALES          CHICAGO         40 OPERATIONS     BOSTON   21:05:18 SCOTT@ prod>delete from test; 21:05:24 SCOTT@ prod>commit; 21:05:26 SCOTT@ prod>select count(*) from test;   COUNT(*) ----------          0 21:03:23 SCOTT@ test1>select count(*) from test;   COUNT(*) ----------          0

3       Insert into test table 1000000 rows

3.1     Insert 1000000 rows into test

13:41:27 SCOTT@ prod>begin  13:49:10   2    for i in 1..1000000 loop 13:49:10   3     insert into test values (i,'ORACLEDBA','BEIJING'); 13:49:10   4      if mod(i,1000)=0 then 13:49:10   5       commit; 13:49:10   6      end if; 13:49:10   7    end loop; 13:49:10   8   commit; 13:49:10   9  end; 13:49:10  10  /   PL/SQL procedure successfully completed.   Elapsed: 00:03:24.59 13:52:50 SCOTT@ prod>

 

3.2     Check on test1

13:55:49 SCOTT@ test1>r   1* select count(*) from test   COUNT(*) ----------     998000 Elapsed: 00:00:00.04 13:55:50 SCOTT@ test1>r   1* select count(*) from test     COUNT(*) ----------    1000000

测试插入数据1百万行。

13:52:50 prod 库插入数据完成。插入过程耗时00:03:24.59

13:55:50 test1 库查询已同步。同步过程在SourceDB 完成插入脚本后3分钟完成同步。

 

 
吕星昊
2014.10.06

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29475508/viewspace-1290174/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29475508/viewspace-1290174/