oracle goldengate使用测试

jgugugiug

　　1. Preparation
　　Our first step is preparing the box for our test replication. I am using a VMWare virtual server for my tests.

• Install linux x64 on your box. I used Linux Centos 5.3. The hostname for our source box will be db1.
  
• Install the Oracle software on the box. I am using Oracle 11gr2 for test purposes. The software from can be found on otn.oracle.com. Unzip and install it.　　Oracle software installation:
  　　Copy the two zip files to some location on the machine and unzip them:
  　　[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
  
• 　　Create a test database:
  　　[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
  
• 　　Start the listener and set up a network alias:
  

　　[oracle@db1 ~]$ lsnrctl start

　　[oracle@db1 ~]$ vi $ORACLE_HOME/network/admin/tnsnames.ora

　　2. Install
　　With the database created, we can install GoldenGate software to the box:

• Get the GG software from OTN. We need “Oracle GoldenGate v10.4.0.x for Oracle 10g 64bit on Redhat 4.0″. The file has>
  
• Create directory for the software and extract it to the directory:　　[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
  
• If you are using Oracle 11gr1 or 11gr2, make a symbolic link . . .　　[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
  
• . . . and the path to the GG libraries to LD_LIBRARY_PATH:　　export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/u01/app/oracle/product/gg
  
• Now we can start GG command line utility to make sure it works.　　[oracle@db1 gg]$ ./ggsci
  
• You should now get the prompt to the GoldenGate command line interface:　　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>
  
• Create the necessary working directories for GG.　　[oracle@db1 gg]$ ./ggsci
  　　GGSCI (db1) 1>create subdirs
  　　GGSCI (db1) 1>exit
  　　[oracle@db1 gg]$ mkdir $GGATE/discard
  
• The GoldenGate software has been successfully installed to the box db1.
  

　　3. Destination-side
　　We must repeat all the above steps on the destination side. Here, it will be host db4 with same database.
　　4. Source database
　　The GoldenGate software having been installed successfully, we must prepare the source database for replication.

• Switch the database to archivelog mode:　　SQL> shutdown immediate
  　　SQL> startup mount
  
  　　SQL>>
  　　SQL>>
  
• Enable minimal supplemental logging:
  　　SQL>>
  
• Prepare the database to support ddl replication (optional).　　a) Turn off recyclebin for the database . . .
  
  　　SQL>>　　. . . and bounce it.
  　　b) Create schema for ddl support replication . . .
  
  　　SQL> create user ggate>　　. . . 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
  
• Create test schemas for replication. I will create a replication from schema sender to schema receiver (the receiving schema can be on the same database or on another).　　a) Source database:
  
  　　SQL> create user sender>　　SQL> grant connect,resource,unlimited tablespace to sender;
  　　b) Destination database:
  
  　　SQL> create user receiver>　　SQL> grant connect,resource,unlimited tablespace to receiver;
  

　　5. Replication
　　We’re going to create the simplest replication without the GG data pump (we can add it later). Our goal is to create ddl 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.

• Create and start manager on the source and the destination.　　Source:
  　　[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
  
• Create the 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
  
• Create replicat on the destination side:　　[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.*;
  
• Start extract and replicat:　　Source:
  　　GGSCI (db1) 14> start extract ext1
  　　Destination:
  　　GGSCI (db2) 15> start replicat rep1
  
• Check all processes.　　Source:
  　　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.
  

　　6. Checking
　　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.

• Source database:　　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;
  
• Destination database:　　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.
　　add replicat rep1, exttrail ./dirdat/lt,checkpointtable ggate.checkpoint
　　add extract ext2, tranlog, begin now
　　add exttrail ./dirdat/qt, extract ext2
　　edit params ext2
　　SETENV (NLS_LANG = "SIMPLIFIED CHINESE_CHINA.AL32UTF8")
　　--extract group--
　　extract ext2
　　--connection to database--
　　userid ggate, password qwerty
　　--tranlogoptions asmuser sys@+ASM,asmpassword nopassword
　　--hostname and port for trail--
　　rmthost 172.16.90.170, mgrport 7809
　　--path and name for trail--
　　rmttrail ./dirdat/qt
　　--DDL support
　　DDL include mapped objname sender.*;
　　--DML
　　--dynamicresolution
　　--gettruncates
　　table sender.*;
　　start extract ext1
　　edit params ./GLOBAL
　　GGSCHEMA ggate
　　CHECKPOINTTABLE ggate.checkpoint
　　dblogin userid ggate
　　add checkpointtable ggate.checkpoint
　　add replicat rep1, exttrail ./dirdat/qt,checkpointtable ggate.checkpoint
　　edit params rep1
　　SETENV (NLS_LANG = "SIMPLIFIED CHINESE_CHINA.AL32UTF8")
　　--Replicat group --
　　replicat rep1
　　--source and target definitions
　　ASSUMETARGETDEFS
　　--target database login--
　　userid ggate, password qwerty
　　--file for dicarded transaction --
　　discardfile ./discard/rep1_discard.txt, append, megabytes 10
　　--ddl support
　　DDL
　　--Specify table mapping ---
　　--REPERROR (1403, DISCARD)
　　--REPERROR (2260, DISCARD)
　　--REPERROR (942, DISCARD)
　　map sender.*, target receiver.*;
　　SHOW PARAMETER NLS_LANGUAGE
　　SHOW PARAMETER NLS_TERRITORY
　　SELECT name, value$ from SYS.PROPS$ WHERE name = 'NLS_CHARACTERSET';
　　SHOW PARAMETER NLS_LENGTH_SEMANTICS
　　SIMPLIFIED CHINESE
　　CHINA
　　NLS_CHARACTERSET    AL32UTF8
　　BYTE
　　_.
　　SETENV (NLS_LANG = "AMERICAN_AMERICA.AL32UTF8")
　　SETENV (NLS_LANG = "SIMPLIFIED CHINESE_CHINA.AL32UTF8")
　　set NLS_LANG = "SIMPLIFIED CHINESE_CHINA.AL32UTF8"
　　This is an example in UNIX, using the SETENV parameter in the Oracle GoldenGate parameter file:
　　测试ddl
　　CREATE TABLE t1(ID NUMBER(5),
　　StudentID NUMBER(10),
　　Name VARCHAR2(100),
　　Old NUMBER(3),
　　Stature NUMBER(3,2));
　　INSERT INTO t1 VALUES(1,20110713,'mfy,32,1.77);
　　INSERT INTO t1 VALUES(2,20110712,'mfy2',32,1.77);
　　INSERT INTO t1 VALUES(1,20110711,'mfy1',32,1.77);
　　desc t1
　　select * from t1;
　　create table test_tab_1 (id number,rnd_str varchar2(12));
　　insert into test_tab_1 values (1,'test_1');
　　commit;