【GoldenGate】Oracle GoldenGate(四) 壓力測試同步百萬行資料

海星星hktk發表於2014-10-06
GoOracle

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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章