May 26, 2011

Mengenal Oracle GoldenGate


 

Oracle GoldenGate adalah software yang digunakan untuk melakukan integrasi data secara real-time, yang dapat digunakan memindahkan data [moves transactional data] antar database server [tidak harus oracle - oracle], beda hardware dan operating system.
Secara umum cara kerja Oracle GoldenGate melakukan : captures => routes => transforms => delivers data secara real time.
Umumnya Oracle GoldenGate dapat digunakan untuk :
1. HIGH AVAILABILITY
2. ZERO DOWNTIME UPGRADES AND MIGRATIONS
3. LIVE REPORTING
4. OPERATIONAL BI

 

http://palingkeren.blogsome.com/images/gg2.JPG     http://palingkeren.blogsome.com/images/gg1.JPG

 

Install Oracle GoldenGate

I. Asusmsi

a. kernel : 2.6.18-92.el5

b. distro : CentOS release 5.2 (Final) 64 bit

c. oracle server : 11.1.0.6.0

d. SID source : orcl10

e. SID source : orcl3

II. Langkah-langkah

a. Install Oracle GoldenGate di SID source [orcl10]

1. Download Oracle GoldenGate, semisal disimpan di folder /u01

2. Create directory :

 

[oracle@orcl10 gg]$ mkdir /u01/app/oracle/product/gg
[oracle@orcl10 gg]$ export GGATE=/u01/app/oracle/product/gg

 

b. Install Oracle GoldenGate di SID source [orcl10]

1. Download Oracle GoldenGate, semisal disimpan di folder /u01

2. Create directory:

 

[oracle@orcl10 gg]$ mkdir /u01/app/oracle/product/gg
[oracle@orcl10 gg]$ export GGATE=/u01/app/oracle/product/gg

 

3. Agar permanen, tambahkan pada /home/oracle/.bash_profile

 

[oracle@orcl10 gg]$ vi ~/.bash_profile
GGATE=/u01/app/oracle/product/gg; export GGATE
PATH=$ORACLE_HOME/bin:$PATH:$GGATE; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/u01/app/oracle/product/gg; export LD_LIBRARY_PATH

 

 

4. extract
[oracle@orcl10 gg]$ cd $GGATE
[oracle@orcl10 gg]$ cp /u01/V18157-01.zip $GGATE/
[oracle@orcl10 gg]$ unzip V18157-01.zip
[oracle@orcl10 gg]$ tar -xf ggs_redhatAS40_x64_ora10g_64bit_v10.4.0.19_002.tar

 

5. cari file libnnz11.so dan buat link file

 

[oracle@orcl10 gg]$ locate libnnz11.so
/u01/app/oracle/product/11.1.0/db_1/lib/libnnz11.so
/u01/app/oracle/product/11.1.0/db_1/lib32/libnnz11.so

 

[oracle@orcl10 gg]$ ln -s /u01/app/oracle/product/11.1.0/db_1/lib/libnnz11.so /u01/app/oracle/product/11.1.0/db_1/lib/libnnz10.so

 

6. Setelah re-login lagi
[oracle@orcl10 ~]$ cd $GGATE
[oracle@orcl10 gg]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 10.4.0.19 Build 002
Linux, x64, 64bit (optimized), Oracle 10 on Sep 22 2009 14:18:08
Copyright (C) 1995, 2009, Oracle and/or its affiliates. All rights reserved.
GGSCI (orcl10) 1>

 

 

7. Create directories GoldenGate
GGSCI (orcl10) 1> create subdirs
Creating subdirectories under current directory /u01/app/oracle/product/gg
Parameter files /u01/app/oracle/product/gg/dirprm: created
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
Veridata files /u01/app/oracle/product/gg/dirver: created
Veridata Lock files /u01/app/oracle/product/gg/dirver/lock: created
Veridata Out-Of-Sync files /u01/app/oracle/product/gg/dirver/oos: created
Veridata Out-Of-Sync XML files /u01/app/oracle/product/gg/dirver/oosxml: created
Veridata Parameter files /u01/app/oracle/product/gg/dirver/params: created
Veridata Report files /u01/app/oracle/product/gg/dirver/report: created
Veridata Status files /u01/app/oracle/product/gg/dirver/status: created
Veridata Trace files /u01/app/oracle/product/gg/dirver/trace: created
Stdout files /u01/app/oracle/product/gg/dirout: created

 

 

GGSCI (orcl10) 2> exit
[oracle@orcl10 gg]$ mkdir $GGATE/discard

 

 

c. Install Oracle GoldenGate di SID target [orcl3]
ulangi langkah a1 sampai dengan a7

 

 

[oracle@orcl3 u01]$ mkdir /u01/app/oracle/product/gg
[oracle@orcl3 u01]$ export GGATE=/u01/app/oracle/product/gg/
[oracle@orcl3 u01]$ vi ~/.bash_profile
[oracle@orcl3 u01]$ cd $GGATE
[oracle@orcl3 gg]$ cp /u01/V18157-01.zip $GGATE/
[oracle@orcl3 gg]$ unzip V18157-01.zip
Archive: V18157-01.zip
inflating: ggs_redhatAS40_x64_ora10g_64bit_v10.4.0.19_002.tar
[oracle@orcl3 gg]$ tar -xf ggs_redhatAS40_x64_ora10g_64bit_v10.4.0.19_002.tar
[oracle@orcl3 gg]$ locate libnnz11.so
/u01/app/oracle/product/11.1.0/db_1/lib/libnnz11.so
/u01/app/oracle/product/11.1.0/db_1/lib32/libnnz11.so
[oracle@orcl3 gg]$ ln -s /u01/app/oracle/product/11.1.0/db_1/lib/libnnz11.so /u01/app/oracle/product/11.1.0/db_1/lib/libnnz10.so

 

 

[oracle@orcl3 ~]$ cd $GGATE
[oracle@orcl3 gg]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 10.4.0.19 Build 002
Linux, x64, 64bit (optimized), Oracle 10 on Sep 22 2009 14:18:08
Copyright (C) 1995, 2009, Oracle and/or its affiliates. All rights reserved.
GGSCI (orcl3) 1> create subdirs
Creating subdirectories under current directory /u01/app/oracle/product/gg
Parameter files /u01/app/oracle/product/gg/dirprm: created
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
Veridata files /u01/app/oracle/product/gg/dirver: created
Veridata Lock files /u01/app/oracle/product/gg/dirver/lock: created
Veridata Out-Of-Sync files /u01/app/oracle/product/gg/dirver/oos: created
Veridata Out-Of-Sync XML files /u01/app/oracle/product/gg/dirver/oosxml: created
Veridata Parameter files /u01/app/oracle/product/gg/dirver/params: created
Veridata Report files /u01/app/oracle/product/gg/dirver/report: created
Veridata Status files /u01/app/oracle/product/gg/dirver/status: created
Veridata Trace files /u01/app/oracle/product/gg/dirver/trace: created
Stdout files /u01/app/oracle/product/gg/dirout: created

 

 

GGSCI (orcl3) 2> exit
[oracle@orcl3 gg]$ mkdir $GGATE/discard

 

d. Melengkapi Oracle GoldenGate di SID source [orcl10]

1. server dalam keadaan mode archivelog

 

[oracle@orcl10 ~]$ sqlplus / as sysdba
SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open;

 

 

SQL> alter system set recyclebin=off scope=spfile;
SQL> shutdown abort;
SQL> startup;

 

2. supplemental logging

SQL> alter database add supplemental log data;

 

3. Create schema untuk ddl support replication
SQL> create user ggate identified by 123456 default tablespace users temporary tablespace temp;
SQL> grant connect,resource,unlimited tablespace to ggate;
SQL> grant execute on utl_file to ggate;
SQL> grant select any dictionary, select any table to ggate;
SQL> grant create table to ggate;
SQL> grant flashback any table to ggate;
SQL> grant execute on dbms_flashback to ggate;
SQL> grant execute on utl_file to ggate;

 

 

SQL> @marker_setup.sql
SQL> @ddl_setup.sql
SQL> @role_setup.sql
SQL> grant GGS_GGSUSER_ROLE to ggate;
SQL> @ddl_enable.sql

 

SQL> exit

 

4. testing login
[oracle@orcl10 /]$ cd $GGATE
[oracle@orcl10 gg]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 10.4.0.19 Build 002
Linux, x64, 64bit (optimized), Oracle 10 on Sep 22 2009 14:18:08
Copyright (C) 1995, 2009, Oracle and/or its affiliates. All rights reserved.

 

 

GGSCI (orcl10) 1> DBLOGIN USERID ggate, PASSWORD 123456
Successfully logged into database.

 

 

5. startup manager & edit paramater
GGSCI (orcl10) 2> info manager
Manager is DOWN!

 

 

GGSCI (orcl10) 3> start manager
ERROR: Parameter file /u01/app/oracle/product/gg/dirprm/mgr.prm does not exist.

 

 

GGSCI (orcl10) 4> EDIT PARAMS MGR
PORT 7809
USERID ggate, PASSWORD 123456
PURGEOLDEXTRACTS /u01/app/oracle/product/gg, USECHECKPOINTS

 

 

GGSCI (orcl10) 5> start manager
Manager started.

 

 

GGSCI (orcl10) 6> info manager
Manager is running (IP port orcl10.7809).

 

 

GGSCI (orcl10) 7> exit
[oracle@orcl10 gg]$

 

 

6. Create test user source untuk replication
[oracle@orcl10 ~]$ sqlplus / as sysdba
SQL> create user orcl10 identified by 123456 default tablespace users temporary tablespace temp;
SQL> grant connect,resource,unlimited tablespace to orcl10;
SQL> exit

 

a. Melengkapi Oracle GoldenGate di SID target [orcl3]

 

1. server dalam keadaan mode archivelog
[oracle@orcl3 ~]$ sqlplus / as sysdba
SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open;

 

 

SQL> alter system set recyclebin=off scope=spfile;
SQL> shutdown abort;
SQL> startup;

 

 

 

2. supplemental logging
SQL> alter database add supplemental log data;

 

 

3. Create schema untuk ddl support replication
SQL> create user ggate identified by 123456 default tablespace users temporary tablespace temp;
SQL> grant connect,resource,unlimited tablespace to ggate;
SQL> grant execute on utl_file to ggate;
SQL> grant select any dictionary, select any table to ggate;
SQL> grant create table to ggate;
SQL> grant flashback any table to ggate;
SQL> grant execute on dbms_flashback to ggate;
SQL> grant execute on utl_file to ggate;

 

 

SQL> @marker_setup.sql
SQL> @ddl_setup.sql
SQL> @role_setup.sql
SQL> grant GGS_GGSUSER_ROLE to ggate;
SQL> @ddl_enable.sql

 

SQL> exit

 

4. testing login
[oracle@orcl3 ~]$ cd $GGATE
[oracle@orcl3 gg]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 10.4.0.19 Build 002
Linux, x64, 64bit (optimized), Oracle 10 on Sep 22 2009 14:18:08
Copyright (C) 1995, 2009, Oracle and/or its affiliates. All rights reserved.

 

 

GGSCI (orcl3) 1> DBLOGIN USERID ggate, PASSWORD 123456
Successfully logged into database.

 

 

5. startup manager & edit paramater
GGSCI (orcl3) 2> info manager
Manager is DOWN!GGSCI (orcl3) 3> start manager
ERROR: Parameter file /u01/app/oracle/product/gg/dirprm/mgr.prm does not exist.

 

 

GGSCI (orcl3) 4> EDIT PARAMS MGR
GGSCI (orcl3) 5> start manager
Manager started.

 

 

GGSCI (orcl3) 6> info manager
Manager is running (IP port orcl3.7809).

 

GGSCI (orcl3) 7> exit

 

6. Create test user source untuk replication
[oracle@orcl3 ~]$ sqlplus / as sysdba
SQL> create user orcl3 identified by 123456 default tablespace users temporary tablespace temp;
SQL> grant connect,resource,unlimited tablespace to orcl3;
SQL> exit

 

III. Demo Replication

1. manager harus start source

 

[oracle@orcl10 ~]$ cd $GGATE
[oracle@orcl10 gg]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 10.4.0.19 Build 002
Linux, x64, 64bit (optimizd), Oracle 10 on Sep 22 2009 14:18:08
Copyright (C) 1995, 2009, Oracle and/or its affiliates. All rights reserved.

 

 

GGSCI (orcl10) 1> info all
Program Status Group Lag Time Since Chkpt
MANAGER STOPPED

 

 

GGSCI (orcl10) 2> info manager
Manager is DOWN!

 

 

GGSCI (orcl10) 3> start manager
Manager started.

 

 

GGSCI (orcl10) 4> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING

 

2. Create scipt [/source]

 

GGSCI (orcl10) 5> add extract ext1, tranlog, begin now
EXTRACT added.

 

 

GGSCI (orcl10) 6> add exttrail /u01/app/oracle/product/gg/dirdat/lt, extract ext1
EXTTRAIL added.

 

 

GGSCI (orcl10) 7> edit params ext1
–extract group–
EXTRACT ext1
–connection to database–
USERID ggate, PASSWORD 123456
–hostname and port for trail–
RMTHOST orcl3, MGRPORT 7809
–path and name for trail–
rmttrail /u01/app/oracle/product/gg/dirdat/lt
–DDL support
ddl include mapped objname orcl10.*
–DML
TABLE orcl10.*;

 

 

##################################################
### penjelasan######################################
EXTRACT: nama dari extract group
USERID/PASSWORD: user di source server
RMTHOST: IP address / hostname dari target server
MGRPORT: port manager Golden Gate
TABLE: tabel yang akan di-extracted & replicated, sebutkan schemanya

 

 

GGSCI (orcl10) 8> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXT1 00:00:00 00:13:55

 

 

a. Create scipt [target]
[oracle@orcl3 ~]$ cd $GGATE
[oracle@orcl3 gg]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 10.4.0.19 Build 002
Linux, x64, 64bit (optimized), Oracle 10 on Sep 22 2009 14:18:08
Copyright (C) 1995, 2009, Oracle and/or its affiliates. All rights reserved.

 

 

GGSCI (orcl3) 1> dblogin userid ggate
Password:
Successfully logged into database.

 

 

GGSCI (orcl3) 2> add checkpointtable ggate.checkpoint
Successfully created checkpoint table GGATE.CHECKPOINT.

 

 

GGSCI (orcl3) 3> edit params ./GLOBALS
GGSCHEMA orcl3
CHECKPOINTTABLE ggate.checkpoint

 

 

GGSCI (orcl3) 4> add replicat rep1, exttrail /u01/app/oracle/product/gg/dirdat/lt,checkpointtable ggate.checkpoint
REPLICAT added.

 

 

GGSCI (orcl3) 5> edit params rep1
–Replicat group –
replicat rep1
–source and target definitions
ASSUMETARGETDEFS
–target database login –
userid ggate, password 123456
–ddl support
DDL
–file for dicarded transaction –
discardfile /u01/app/oracle/product/gg/discard/rep1_discard.txt, append, megabytes 10
map orcl10.*, target orcl3.*;

 

 

3. Start extract and replicat:
###################################
### Source:###################################
GGSCI (orcl10) 9> start extract ext1
Sending START request to MANAGER …
EXTRACT EXT1 starting

 

 

###################################
### Destination:
###################################
GGSCI (orcl3) 7> start replicat rep1
ERROR: Manager not currently running.

 

 

GGSCI (orcl3) 8> start manager
Manager started.

 

 

GGSCI (orcl3) 9> start replicat rep1
Sending START request to MANAGER …
REPLICAT REP1 starting

 

4. Test

 

a. source : create table, insert 2 record
target : ter-replikasi
###################################
### Source:
###################################
[oracle@orcl10 gg]$ sqlplus orcl10/123456@orcl10
SQL> CREATE TABLE DEPT
( DEPTNO NUMBER(2),
DNAME VARCHAR2(14),
LOC VARCHAR2(13)
);

 

 

SQL> CREATE UNIQUE INDEX PK_DEPT ON DEPT(DEPTNO) LOGGING;
SQL> ALTER TABLE DEPT ADD (CONSTRAINT PK_DEPT PRIMARY KEY (DEPTNO));
SQL> insert into dept values (51,’IT’,’SBY’);
SQL> insert into dept values (52,’AU’,’SBY’);
SQL> commit;
SQL> select * from dept order by deptno;
DEPTNO DNAME LOC
———- ————– ————-
51 IT SBY
52 AU SBY

 

 

###################################
### Target:
###################################
[oracle@orcl3 gg]$ sqlplus orcl3/123456@orcl3
SQL> select * from dept order by deptno;
DEPTNO DNAME LOC
———- ————– ————-
51 IT SBY
52 AU SBY

 

 

b. source : insert 2 record lagi
target : ter-replikasi
###################################
### Source:
###################################
SQL> insert into dept values (53,’MKT’,’JKT’);
SQL> insert into dept values (54,’ACC’,’JKT’);
SQL> commit;
SQL> select * from dept order by deptno;
DEPTNO DNAME LOC
———- ————– ————-
51 IT SBY
52 AU SBY
53 MKT JKT
54 ACC JKT

 

 

###################################
### Target:
###################################
SQL> select * from dept order by deptno;
DEPTNO DNAME LOC
———- ————– ————-
51 IT SBY
52 AU SBY
53 MKT JKT
54 ACC JKT