3.2.2、目标端sftp>cd /u01/setup/ogg
sftp>lcd F:\installmedium\ogg
sftp>put 191004_fbo_ggs_Linux_x64_shiphome.zip
[root@leo-19c-ogg:~]#chown -R oracle:oinstall /u01/setup
[oracle@leo-19c-oggogg]$ pwd
/u01/setup/ogg
[oracle@leo-19c-oggogg]$ unzip -q 191004_fbo_ggs_Linux_x64_shiphome.zip
[oracle@leo-19c-oggDisk1]$ pwd
/u01/setup/ogg/fbo_ggs_Linux_x64_shiphome/Disk1
[oracle@leo-19c-oggDisk1]$ export DISPLAY=192.168.133.1:0.0
[oracle@leo-19c-oggDisk1]$ ./runInstaller
3.3、环境变量3.3.1、源端[oracle@leo-11g-ogg:~]$vi .bash_profile
添加如下:
exportGG_HOME=/u01/app/ogg
export PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin:$GG_HOME
exportLD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
aliasggsci='cd $GG_HOME;ggsci'
[oracle@leo-11g-ogg:~]$source .bash_profile
3.3.2、目标端[oracle@leo-19c-ogg:~]$vi .bash_profile
添加如下:
exportGG_HOME=/u01/app/ogg
export PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin:$GG_HOME
exportLD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
aliasggsci='cd $GG_HOME;ggsci'
[oracle@leo-19c-ogg~]$ source .bash_profile
3.4、数据库配置3.4.1、源端3.4.1.1、强制生成日志sys@ORCL2022-12-01 09:54:51> alter database force logging;
Databasealtered.
sys@ORCL2022-12-01 09:55:01> select force_logging from v$database;
FOR
---
YES
3.4.1.2、开启归档[oracle@leo-11g-ogg:~]$sqlplus / as sysdba
SQL>shutdown immediate
SQL>startup mount
SQL>alter database archivelog;
SQL>alter system set log_archive_dest_1='location=/u01/app/oracle/oradata/archivelog';
SQL>alter database open;
3.4.1.3、开启补充日志SQL>select supplemental_log_data_min from v$database;
SUPPLEME
--------
NO
SQL>alter database add supplemental log data;
SQL>alter system switch logfile;
SQL>select supplemental_log_data_min from v$database;
SUPPLEME
--------
YES
说明:redo默认情况下只记录数据块改变的信息,其实它还可以记录更详细的信息,这些信息对第三方的容灾软件很重要,所以此处将supplementallog mode模式打开,oracle一般是用rowid来唯一标识一行记录,但goldengate需要主键等其他标识,所以要开附加日志.
3.4.1.4、开启enable_goldengate_replicationSQL> altersystem set enable_goldengate_replication=true;
SQL>show parameter enable_goldengate
NAME TYPE VALUE
----------------------------------------------- ------------------------------
enable_goldengate_replication boolean TRUE
3.4.2、目标端3.4.2.1、开启enable_goldengate_replicationSQL> altersystem set enable_goldengate_replication=true;
SQL>show parameter enable_goldengate
NAME TYPE VALUE
----------------------------------------------- ------------------------------
enable_goldengate_replication boolean TRUE
3.5、创建用户、表空间及授权创建goldengate数据库用户以及专属于ogg的表空间.
3.5.1、源端[oracle@leo-11g-ogg:~]$sqlplus / as sysdba
createtablespace oggtbs datafile '/u01/app/oracle/oradata/orcl/oggtbs01.dbf' size 50mautoextend on;
createuser ogg identified by ogg default tablespace oggtbs;
grantresource to ogg;
grantcreate session, alter session to ogg;
grantselect any dictionary to ogg;
grantflashback any table to ogg;
grantalter any table to ogg;
grantselect any table to ogg;
grantexecute on dbms_flashback to ogg;
grant allprivileges to ogg;
grant dbato ogg;
3.5.2、目标端[oracle@leo-19c-ogg~]$ sqlplus / as sysdba
createtablespace oggtbs datafile '/u01/app/oracle/oradata/SIMDB/oggtbs01.dbf' size50m autoextend on;
createuser ogg identified by ogg default tablespace oggtbs;
grantresource to ogg;
grantcreate session, alter session to ogg;
grantselect any dictionary to ogg;
grantflashback any table to ogg;
grantalter any table to ogg;
grantselect any table to ogg;
grantexecute on dbms_flashback to ogg;
grantinsert any table to ogg;
grantupdate any table to ogg;
grantdelete any table to ogg;
grant dbato ogg;
3.6、准备数据3.6.1、源端新建用户并授予权限.
createuser leo identified by leo;
grantconnect,resource to leo;
grantselect on scott.emp to leo;
grantselect on scott.dept to leo;
createuser alina identified by alina;
grantconnect,resource to alina;
grantselect on scott.salgrade to alina;
leo用户创建测试表,并增加主键.
SQL>show user
USER is "LEO"
createtable lemp as select * from scott.emp;
createtable ldept as select * from scott.dept;
altertable ldept add primary key (deptno);
altertable lemp add primary key (empno);
SQL>show user
USER is "ALINA"
createtable asalgrade as select * from scott.salgrade;
altertable asalgrade add primary key (grade);
导出数据,用于目标端.
exportNLS_LANG=AMERICAN_AMERICA.AL32UTF8
expleo/leo@orcl file=/home/oracle/leo.dmp tables=ldept,lemp rows=ylog=/home/oracle/exp.log
将源端的dmp文件scp到目标端
3.6.2、目标端创建用户并授予权限
createuser leo identified by leo;
grantconnect,resource,unlimited tablespace to leo;
导入测试数据
[oracle@leo-deng~]$ imp leo/leo file=/home/oracle/leo.dmp full=y statistics=none
验证测试数据
SQL>conn leo/leo
Connected.
SQL>select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------------- ----------
LEMP TABLE
LDEPT TABLE
SQL>select count(*) from mydept;
COUNT(*)
----------
4
SQL>select count(*) from myemp;
COUNT(*)
----------
14
4、OGG配置4.1、源端说明:必须在ogg的home目录下启动ggsci,这样才能把子目录建在ogg的home目录下,如果不在相应位置建立子目录,在后面编辑参数文件时会报错.
[oracle@leo-11g-oggogg]$ cd $GG_HOME
[oracle@leoogg]$ ./ggsci
4.1.1、创建目录GGSCI(leo-11g-ogg) 1> create subdirs
Creatingsubdirectories under current directory /u01/app/ogg
Parameterfile /u01/app/ogg/dirprm:created.
Reportfile /u01/app/ogg/dirrpt: created.
Checkpointfile /u01/app/ogg/dirchk:created.
Processstatus files /u01/app/ogg/dirpcs: created.
SQLscript files /u01/app/ogg/dirsql: created.
Databasedefinitions files /u01/app/ogg/dirdef: created.
Extractdata files /u01/app/ogg/dirdat: created.
Temporaryfiles /u01/app/ogg/dirtmp:created.
Credentialstore files /u01/app/ogg/dircrd:created.
Masterkeywallet files /u01/app/ogg/dirwlt:created.
Dumpfiles /u01/app/ogg/dirdmp: created.
重要目录说明:
dirchk:存放检查点(checkpoint)文件
dirdat:存放trail文件
dirdef:存放通过defgen工具生成的源或目标端数据定义文件
dirpcs:存放进程状态文件
dirprm:存放配置参数文件
dirrpt:存放进程报告文件
dirsql:存放sql脚本文件
dirtmp:当事务所需要的内存超过已分配的内存时,默认存储在此目录
4.1.2、添加端口4.1.2.1、源端配置GGSCI(leo-11g-ogg) 2> edit param mgr
添加
port 7809
GGSCI(leo-11g-ogg) 3> start mgr
Managerstarted.
GGSCI(leo-11g-ogg) 4> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
GGSCI(leo-11g-ogg) 2> dblogin userid ogg,password ogg
Successfullylogged into database.
GGSCI(leo-11g-ogg as ogg@orcl) 3> add trandata leo.*
2022-12-0112:35:24 INFO OGG-15132 Logging of supplemental redo data enabled for table LEO.LDEPT.
2022-12-0112:35:24 INFO OGG-15133 TRANDATA for scheduling columns has been added on table LEO.LDEPT.
2022-12-0112:35:24 INFO OGG-15135 TRANDATA for instantiation CSN has been added on table LEO.LDEPT.
2022-12-0112:35:24 INFO OGG-15132 Logging of supplemental redo data enabled for table LEO.LEMP.
2022-12-0112:35:24 INFO OGG-15133 TRANDATA for scheduling columns has been added on table LEO.LEMP.
2022-12-0112:35:24 INFO OGG-15135 TRANDATA for instantiation CSN has been added on table LEO.LEMP.
说明:数据库附加日志打开后还需要对每张表执行add trandata吗?
答案是肯定的,如果不执行add trandata,insert同步没有问题(ORACLE数据库),但在同步update或delete时,就会因为丢失主键报同步错误.
不开启表级的最小附加日志,update的redo信息不记录没有进行更新的字段信息,如主键不更新的话主键不记录在redo中,所以会导致同步失败.
4.1.2.2、目标端配置提前在目标端添加mgr端口.
GGSCI(leo-19c-ogg) 2> create subdirs
Creatingsubdirectories under current directory /u01/app/ogg
Parameterfile /u01/app/ogg/dirprm:created.
Reportfile /u01/app/ogg/dirrpt: created.
Checkpointfile /u01/app/ogg/dirchk:created.
Processstatus files /u01/app/ogg/dirpcs: created.
SQLscript files /u01/app/ogg/dirsql: created.
Databasedefinitions files /u01/app/ogg/dirdef: created.
Extractdata files /u01/app/ogg/dirdat: created.
Temporaryfiles /u01/app/ogg/dirtmp:created.
Credentialstore files /u01/app/ogg/dircrd:created.
Masterkeywallet files /u01/app/ogg/dirwlt:created.
Dumpfiles /u01/app/ogg/dirdmp: created.
GGSCI(leo-19c-ogg) 3> edit param mgr
添加以下:
port 7809
GGSCI(leo-19c-ogg) 4> start mgr
Managerstarted.
GGSCI(leo-19c-ogg) 5> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
4.1.3、抓取进程extract进程,负责从源端数据表或者日志中捕获数据.
GGSCI(leo-11g-ogg as ogg@orcl) 4> add extract exta,tranlog,begin now
EXTRACTadded.
添加本地队列ra,exta进程捕捉到的有效数据将写入本地队列.
GGSCI(leo-11g-ogg as ogg@orcl) 5> add exttrail ./dirdat/ra,extract exta
EXTTRAILadded.
为exta进程配置参数
GGSCI(leo-11g-ogg as ogg@orcl) 6> edit param exta
添加如下内容:
extractexta
useridogg,password ogg
setenv(NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
exttrail./dirdat/ra
dynamicresolution /* 优化参数,动态分析表结构
gettruncates /*抓取truncatetable操作
table leo.*;
4.1.4、投递进程源端数据库配置投递过程.
GGSCI(leo-11g-ogg as ogg@orcl) 7> add extract dp1,exttrailsource ./dirdat/ra
EXTRACTadded.
GGSCI(leo-11g-ogg as ogg@orcl) 8> edit param dp1
添加如下:
extractdp1
useridogg,password ogg
setenv(NLS_LANG = AMERICAN_AMERICA.AL32UTF8)
passthru
rmthost192.168.133.109,mgrport 7809
rmttrail./dirdat/ra
tableleo.*;
--添加远程trail文件.
GGSCI(leo-11g-ogg as ogg@orcl) 9> add rmttrail ./dirdat/ra,extract dp1
RMTTRAILadded.
GGSCI(leo-11g-ogg as ogg@orcl) 10> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED DP1 00:00:00 00:03:45
EXTRACT STOPPED EXTA 00:00:00 00:08:13
GGSCI(leo) 6> start dp1
SendingSTART request to MANAGER ...
EXTRACTDP1 starting
GGSCI(leo) 7> start exta
Sending STARTrequest to MANAGER ...
EXTRACTEXTA starting
GGSCI(leo) 8> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DP1 00:00:00 00:04:26
EXTRACT RUNNING EXTA 00:38:56 00:00:04
4.2、目标端[oracle@leo-deng~]$ cd $GG_HOME
[oracle@leo-dengogg]$ ./ggsci
4.2.1、添加checkpointtableGGSCI(leo-19c-ogg) 1> edit params ./globals
添加如下
checkpointtableogg.checkpoint
GGSCI(leo-19c-ogg) 2> dblogin userid ogg,password ogg
Successfullylogged into database.
GGSCI(leo-19c-ogg as ogg@simdb) 2> add checkpointtable ogg.checkpoint
Successfullycreated checkpoint table ogg.checkpoint.
4.2.2、复制进程GGSCI(leo-19c-ogg as ogg@simdb) 3> add replicat repa,exttrail./dirdat/ra,checkpointtable ogg.checkpoint
REPLICATadded.
GGSCI(leo-19c-ogg as ogg@simdb) 4> edit param repa
添加如下:
replicatrepa
setenv(NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
useridogg,password ogg
numfiles500
grouptransops10000
handlecollisions
assumetargetdefs
allownoopupdates
dynamicresolution
discardfile./dirrpt/repsa_discard.txt, append, megabytes 10
mapleo.*, target leo.*;
说明:逗号与target之间需用空格隔开,ogg.checkpoint中的ogg为之前创建的用户.
GGSCI(leo-19c-ogg as ogg@simdb) 5> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT STOPPED REPA 00:00:00 00:02:16
GGSCI(leo-19c-ogg as ogg@simdb) 8> start repa
SendingSTART request to MANAGER ...
REPLICATREPA starting
GGSCI(leo-19c-ogg as ogg@simdb) 9> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REPA 00:00:00 00:00:00
5、验证数据--源端向ldept表中插入一条数据.
sys@ORCL2022-12-01 15:29:30> conn leo/leo
Connected.
leo@ORCL2022-12-01 15:29:35> select * from ldept;
DEPTNO DNAME LOC
------------------------ -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
leo@ORCL2022-12-01 15:29:45> insert into ldept values (50,'IT','ChongQing');
1 rowcreated.
leo@ORCL2022-12-01 15:30:03> commit;
Commitcomplete.
--目标端验证.
SQL> select* from ldept
DEPTNO DNAME LOC
------------------------ -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 IT ChongQing
ogg基本配置完成.
总结:配置过程包括操作系统层面前期准备、源端目标端数据库配置、源端中mgr/extract/pump进程配置、目标端中mgr/replicat进程配置.配置过程要保持数据库开启状态,否则ggsci中用户不能登录.