1.环境信息:
2.创建安装目录并解压安装介质:
源端:
[oracle@itkaifang ~]# mkdir -p /u01/app/ogg [oracle@itkaifang ogg]$ unzip ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip [oracle@itkaifang ogg]$ tar -xvf fbo_ggs_Linux_x64_ora11g_64bit.tar
目标端:
[oracle@ogg ~]$ mkdir -p /u01/app/ogg [oracle@ogg ogg]$ unzip ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip [oracle@ogg ogg]$ tar -xvf fbo_ggs_Linux_x64_ora11g_64bit.tar
3.配置环境变量:
源端和目标端相同
[oracle@ogg ogg]$ vi ~/.bash_profile export OGG_HOME=/u01/app/ogg export PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin:$OGG_HOME export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib alias ggsci='cd $OGG_HOME;ggsci'
生效环境变量. .bash_profile
4.源端数据库配置:
开启归档模式:
SQL> archive log list Database log mode No Archive Mode Automatic archival Disabled Archive destination /u01/app/oracle/product/11.2.0/db_1/dbs/arch Oldest online log sequence 9 Current log sequence 11 SQL> shutdown immediate SQL> startup mount SQL> alter database archivelog; SQL> alter database open; SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/app/oracle/product/11.2.0/db_1/dbs/arch Oldest online log sequence 9 Next log sequence to archive 11 Current log sequence 11开启强制生成日志:
SQL> select force_logging from v$database;FOR---NO SQL> alter database force logging; SQL> select force_logging from v$database;FOR---YES开启补充日志: SQL> select supplemental_log_data_min from v$database; SUPPLEME -------- NO SQL> alter database add supplemental log data; SQL> select supplemental_log_data_min from v$database; SUPPLEME -------- YES
如果oracle数据库版本在11.2.0.4以后需修改参数enable_goldengate为TRUE
5.创建goldengate数据库用户、表空间:
创建表空间:
SQL> create tablespace ogg_ts datafile'/u01/app/oracle/oradata/itkf/ogg_ts.dbf' size 100m;create user ogg identified by ogg default tablespace ogg_ts;
创建用户:
SQL> create user ogg identified by ogg default tablespace ogg_ts;
授予用户相关权限:
SQL> grant resource to ogg; SQL> grant create session,alter session to ogg; SQL> grant select any dictionary to ogg; SQL> grant flashback any table to ogg; SQL> grant alter any table to ogg; SQL> grant select any table to ogg; SQL> grant execute on dbms_flashback to ogg;
6.创建测试数据:
创建测试用户,并授予基本权限:
SQL> create user it identified by it; SQL> grant connect,resource to it; SQL> grant select on scott.dept to it; SQL> grant select on scott.emp to it;
创建测试表,并增加主键:
SQL> conn it/it SQL> create table mydept as select * from scott.dept; SQL> create table myemp as select * from scott.emp; SQL> alter table mydept add primary key(deptno); SQL> alter table myemp add primary key(empno);
7.导出数据用于目标端:
[oracle@itkaifang ~]$ exp it/it file=/home/oracle/it.dmp tables=mydept,myemp rows=y [oracle@itkaifang ~]$ scp it.dmp 192.168.2.20:/home/oracle/. The authenticity of host '192.168.2.20 (192.168.2.20)' can't be established. RSA key fingerprint is 4c:87:82:83:33:67:94:89:30:69:7d:63:ae:38:16:dd. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added '192.168.2.20' (RSA) to the list of known hosts. oracle@192.168.2.20's password: it.dmp 100% 16KB 16.0KB/s 00:008.目标端创建相应用户并授权:
SQL> create user it identified by it; SQL> grant connect,resource to it;
9.导入铺底数据:
[oracle@ogg ~]$ imp it/it file=/home/oracle/it.dmp full=y Import: Release 11.2.0.1.0 - Production on Sun Nov 5 20:36:38 2017 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export file created by EXPORT:V11.02.00 via conventional path import done in US7ASCII character set and AL16UTF16 NCHAR character set import server uses AL32UTF8 character set (possible charset conversion) . importing IT's objects into IT . importing IT's objects into IT . . importing table "MYDEPT" 4 rows imported . . importing table "MYEMP" 14 rows imported Import terminated successfully without warnings.验证:
SQL> conn it/itConnected. SQL> select * from tab; TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- MYDEPT TABLEMYEMP TABLE SQL> select count(*) from myemp; COUNT(*) ---------- 14
10.目标端创建用户、表空间:
创建表空间:
SQL> create tablespace ogg_ts datafile'/u01/app/oracle/oradata/itkf/ogg_ts.dbf' size 100m; SQL> create user ogg identified by ogg default tablespace ogg_ts;
创建用户:
SQL> create user ogg identified by ogg default tablespace ogg_ts;
授予用户相关权限:
SQL> grant resource to ogg; SQL> grant create session,alter session to ogg; SQL> grant select any dictionary to ogg; SQL> grant flashback any table to ogg; SQL> grant alter any table to ogg; SQL> grant select any table to ogg; SQL> grant execute on dbms_flashback to ogg; SQL> grant insert any table to ogg; SQL> grant update any table to ogg; SQL> grant delete any table to ogg;
11.OGG的配置:
源端:
[oracle@itkaifang ogg]$ ggsci GGSCI (itkaifang) 1> create subdirs Creating subdirectories under current directory /u01/app/ogg Parameter files /u01/app/ogg/dirprm: already exists Report files /u01/app/ogg/dirrpt: created Checkpoint files /u01/app/ogg/dirchk: created Process status files /u01/app/ogg/dirpcs: created SQL script files /u01/app/ogg/dirsql: created Database definitions files /u01/app/ogg/dirdef: created Extract data files /u01/app/ogg/dirdat: created Temporary files /u01/app/ogg/dirtmp: created Stdout files /u01/app/ogg/dirout: created GGSCI (itkaifang) 1> edit param mgr port 7809 dynamicportlist 7840-7845 GGSCI (itkaifang) 2> start mgr GGSCI (itkaifang) 3> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING GGSCI (itkaifang) 1> dblogin userid ogg,password ogg Successfully logged into database. GGSCI (itkaifang) 2> add trandata it.* Logging of supplemental redo data enabled for table IT.MYDEPT. Logging of supplemental redo data enabled for table IT.MYEMP. GGSCI (itkaifang) 3> add extract ex_it,tranlog,begin now EXTRACT added. GGSCI (itkaifang) 1> add exttrail ./dirdat/ra,extract ex_it EXTTRAIL added. GGSCI (itkaifang) 2> edit param ex_it extract ex_it userid ogg,password ogg setenv NLS_LANG=(AMERICAN_AMERICA.AL32UTF8) exttrail ./dirdat/ra dynamicresolution gettrunca testable it.*;
投递进程:
GGSCI (itkaifang) 4> add extract dp_it,exttrail source ./dirdat/ra EXTRACT added. GGSCI (itkaifang) 6> edit param dp_it extract dp_ituserid ogg,password ogg setenv NLS_LANG=(AMERICAN_AMERICA.AL32UTF8) passthrurmthost 192.168.2.20,mgrport 7809 rmttrail ./dirdat/ra table it.*; GGSCI (itkaifang) 7> add rmttrail ./dirdat/ra,extract dp_it RMTTRAIL added. GGSCI (itkaifang) 10> start ex_itSending START request to MANAGER ... EXTRACT EX_IT starting GGSCI (itkaifang) 11> start dp_it Sending START request to MANAGER ... EXTRACT DP_IT starting GGSCI (itkaifang) 12> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING DP_IT 00:00:00 00:05:28 EXTRACT RUNNING EX_IT 00:00:00 00:00:09
目标端:
[oracle@itkaifang ogg]$ ggsci GGSCI (itkaifang) 1> create subdirs Creating subdirectories under current directory /u01/app/ogg Parameter files /u01/app/ogg/dirprm: already exists Report files /u01/app/ogg/dirrpt: created Checkpoint files /u01/app/ogg/dirchk: created Process status files /u01/app/ogg/dirpcs: created SQL script files /u01/app/ogg/dirsql: created Database definitions files /u01/app/ogg/dirdef: created Extract data files /u01/app/ogg/dirdat: created Temporary files /u01/app/ogg/dirtmp: created Stdout files /u01/app/ogg/dirout: created
指定检查点记录表:
GGSCI (ogg) 5> edit param ./globals checkpointtable ogg.checkpoint GGSCI (ogg) 2> dblogin userid ogg,password ogg Successfully logged into database. GGSCI (ogg) 3> add checkpointtable ogg.checkpoint Successfully created checkpoint table ogg.checkpoint GGSCI (ogg) 5> add replicat re_it,exttrail ./dirdat/ra,checkpointtable ogg.checkpoint REPLICAT added. GGSCI (ogg) 6> edit param re_it replicat re_it setenv NLS_LANG=(AMERICAN_AMERICA.AL32UTF8) userid ogg,password ogg numfiles 500 grouptransops 10000 handlecollisions assumetargetdefs allownoopupdates dynamicresolution discardfile ./dirrpt/repsa_discard.txt,append,megabytes 10 map it.*,target it.*; GGSCI (ogg) 7> start re_it Sending START request to MANAGER ... REPLICAT RE_IT starting GGSCI (ogg) 8> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT RUNNING RE_IT 00:00:00 00:00:03---END---