I want to introduce several post about Oracle GoldenGate (GG), a relatively new replication software from Oracle.
We all know replication using Oracle Streams technology. Streams are improving from version to version of the RDBMS, and now it is a very well managed and reliable technology with a large set of options and capabilities. But, in the document Oracle – GoldenGate Statement of Direction (PDF), GoldenGate software was announced as the strategic replication solution for Oracle Database, and Oracle plans to direct all efforts to improve it.Therefore, I think it is time to make the acquaintance of GoldenGate.
This first post is about installating GG software and creating the simplest replication using only the “extract” and “replicat” processes.
Lets start the installation.
Our first step is preparing the box for our test replication. I am using a VMWare virtual server for my tests.
[oracle@db1 distr]$ export DISTR=/u01/app/oracle/distr [oracle@db1 distr]$ cd $DISTR [oracle@db1 distr]$ unzip linux.x64_11gR2_database_1of2.zip [oracle@db1 distr]$ unzip linux.x64_11gR2_database_2of2.zip
Edit response file for silent installation:
[oracle@db1 distr]$ vi $DISTR/database/response/db_install.rsp [oracle@db1 distr]$ cd $DISTR/database
Set proper parameter for kernel, create necessary user and groups and set limits for the Oracle owner. (I used the oracle user for this).
Install the software:
[oracle@db1 database]$ ./runInstaller -silent -responseFile $DISTR/database/response/db_install.rsp [oracle@db1 ~]$ su - root [root@db1 ~]# /u01/app/oracle/product/11.2.0/db_1/root.sh
[oracle@db1 ~]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 [oracle@db1 ~]$ export PATH=$ORACLE_HOME/bin:$PATH [oracle@db1 ~]$ dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbName base11r2 -sysPassword qwerty -systemPassword qwerty -emConfiguration NONE -datafileDestination /u01/app/oracle/oradata -storageType FS
[oracle@db1 ~]$ lsnrctl start [oracle@db1 ~]$ vi $ORACLE_HOME/network/admin/tnsnames.ora
With the database created, we can install GoldenGate software to the box:
[oracle@db1 product]$ mkdir /u01/app/oracle/product/gg [oracle@db1 product]$ export GGATE=/u01/app/oracle/product/gg [oracle@db1 product]$ cd $GGATE [oracle@db1 gg]$ unzip V18157-01.zip Archive: V18157-01.zip inflating: ggs_redhatAS40_x64_ora10g_64bit_v10.4.0.19_002.tar [oracle@db1 gg]$ tar -xf ggs_redhatAS40_x64_ora10g_64bit_v10.4.0.19_002.tar
[oracle@db1 gg]$ ln -s /u01/app/oracle/product/11.2.0/db_1/lib/libnnz11.so /u01/app/oracle/product/11.2.0/db_1/lib/libnnz10.so
LD_LIBRARY_PATH:export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/u01/app/oracle/product/gg
[oracle@db1 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 (db1) 1>
We must repeat all the above steps on the destination side. Here, it will be host db4 with same database.
The GoldenGate software having been installed successfully, we must prepare the source database for replication.
SQL> shutdown immediate SQL> startup mount SQL> alter database archivelog; SQL> alter database open;
SQL> alter database add supplemental log data;
SQL> alter system set recyclebin=off scope=spfile;
. . . and bounce it.
b) Create schema for ddl support replication . . .
SQL> create user ggate identified by qwerty default tablespace users temporary tablespace temp;
. . . and grant the necessary privileges to the new user..
[oracle@db1 gg]$ cd $GGATE SQL> grant connect,resource,unlimited tablespace to ggate; SQL> grant execute on utl_file to ggate;
c) Run scripts for creating all necessary objects for support ddl replication:
SQL> @$GGATE/marker_setup.sql SQL> @$GGATE/ddl_setup.sql SQL> @$GGATE/role_setup.sql SQL> grant GGS_GGSUSER_ROLE to ggate; SQL> @$GGATE/ddl_enable.sql
sender to schema receiver (the receiving schema can be on the same database or on another).a) Source database:
SQL> create user sender identified by qwerty default tablespace users temporary tablespace temp; SQL> grant connect,resource,unlimited tablespace to sender;
b) Destination database:
SQL> create user receiver identified by qwerty default tablespace users temporary tablespace temp; SQL> grant connect,resource,unlimited tablespace to receiver;
We’re going to create the simplest replication without the GG data pump (we can add it later). Our goal is to create dml and ddl replication from the sender schema on the source database to receiver schema on the destination.
Replication also works if you’re using only one database. This is replication between schemas.
[oracle@db1 gg]$ cd $GGATE [oracle@db1 gg]$ ./ggsci GGSCI (db1) 4> info all Program Status Group Lag Time Since Chkpt MANAGER STOPPED GGSCI (db1) 6> edit params mgr PORT 7809 GGSCI (db1) 7> start manager Manager started.
We can check status of our processes:
GGSCI (db1) 8> info all Program Status Group Lag Time Since Chkpt MANAGER RUNNING
extract group on the source side:
GGSCI (db1) 1> add extract ext1, tranlog, begin now EXTRACT added. GGSCI (db1) 2> add exttrail /u01/app/oracle/product/gg/dirdat/lt, extract ext1 EXTTRAIL added. GGSCI (db1) 3> edit params ext1
Add the following lines to the new parameter file for our extract:
--extract group-- extract ext1 --connection to database-- userid ggate, password qwerty --hostname and port for trail-- rmthost db2, mgrport 7809 --path and name for trail-- rmttrail /u01/app/oracle/product/gg/dirdat/lt --DDL support ddl include mapped objname sender.* --DML table sender.*;
We can check our processes again:
GGSCI (db1) 6> info all Program Status Group Lag Time Since Chkpt MANAGER STOPPED EXTRACT STOPPED EXT1 00:00:00 00:10:55
[oracle@db2 gg]$ cd $GGATE [oracle@db2 gg]$ ./ggsci add checkpoint table to the destination database GGSCI (db2) 1> edit params ./GLOBAL and put following lines to the global parameter file: GGSCHEMA ggate CHECKPOINTTABLE ggate.checkpoint ~ GGSCI (db2) 2> dblogin userid ggate Password: Successfully logged into database. GGSCI (db2) 3> add checkpointtable ggate.checkpoint Successfully created checkpoint table GGATE.CHECKPOINT. Create replicat group: GGSCI (db2) 4> add replicat rep1, exttrail /u01/app/oracle/product/gg/dirdat/lt,checkpointtable ggate.checkpoint REPLICAT added. create parameter file for replicat: GGSCI (db2) 5> edit params rep1
And put following lines in the parameter file:
--Replicat group -- replicat rep1 --source and target definitions ASSUMETARGETDEFS --target database login -- userid ggate, password qwerty --file for dicarded transaction -- discardfile /u01/app/oracle/product/gg/discard/rep1_discard.txt, append, megabytes 10 --ddl support DDL --Specify table mapping --- map sender.*, target receiver.*;
GGSCI (db1) 14> start extract ext1
Destination:
GGSCI (db2) 15> start replicat rep1
GGSCI (db1) 8> info all Program Status Group Lag Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING EXT1 00:00:00 00:00:05
Destination:
GGSCI (db1) 8> info all Program Status Group Lag Time Since Chkpt MANAGER RUNNING REPLICAT RUNNING REP1 00:00:00 00:00:00
Our replication has been successfully created.
Now we can check our replication. We will create some tables in the sender schema on the source, insert some rows, and check how it will replicate to destination side.
SQL> create table sender.test_tab_1 (id number,rnd_str varchar2(12)); SQL> insert into sender.test_tab_1 values (1,'test_1'); SQL>commit;
SQL> select * from receiver.test_tab_1; ID RND_STR ---------- ------------ 1 test_1
Our GoldenGate DDL and DML replication is now working. The table was created on the destination side and data were replicated.
In the next post in this series, I will show how to modify our replication.
Ready to optimize your Oracle Database for the future?