day17——oracleGG搭建
manager进程
运行在源端和目标端,启动,监控,重启goldengate的其他进程,报告错误,分配数据空间,发布阀值报告
extract进程
运行在数据库源端,负责从源端数据表或日志中捕获数据
extract利用其内在的checkpoint机制,周期性的检查并记录读写位置,写入到一个本地的trail文件.
pump进程
运行在数据库源端,如果源端使用了本地trail文件,那么pump进程就把trail以数据块形式通过tcp/ip发送到目标端,如果不使用trail文件,那么由extract进程在抽取完数据以后,直接投递到目标端
trail文件
源端叫本地trail文件,目标端叫远程trail文件.
作用防止单点故障,将事务信息持久化,并使用checkpoint记录位置,如发生故障,则根据checkpoint记录重传.
replicat进程
在目标端运行,负责读取目标端trail文件中的内容,并将其解析为DML或DDL
======================================
1.源,目标端开启监听,传输文件
2.
[oracle@oracle111 ~]$ mkdir /oracle/app/ggate
[oracle@oracle111 ff]$ cp goldengate.zip /oracle/app/ggate/
[oracle@oracle111 ff]$ cd /oracle/app/ggate/
[oracle@oracle111 ggate]$ unzip goldengate.zip
[oracle@oracle111 ggate]$ tar -xvf fbo_ggs_Linux_x86_ora10g_32bit.tar
[oracle@oracle111 ~]$ vi .bash_profile
添加
export GGATE_HOME=/oracle/app/ggate
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/oracle/app/ggate
[oracle@oracle111 ~]$ source .bash_profile
3.安装goldengate
[oracle@oracle111 ~]$ cd /oracle/app/ggate/
[oracle@oracle111 ggate]$ ./ggsci
GGSCI (oracle111) 1> create subdirs
4.环境准备
开启归档模式
SQL> alter database archivelog;
SQL> alter database add supplemental log data;
(( 在oracle中可以通过rowid来定位某条记录，但是目标端的数据库和源端数据库的数据库可能完全不一样，所以无法通过rowid来确定源端数据库的逻辑变化，这时附加日志supplemental log便登上了表演的舞台。数据库在开启附加日志功能后，对于源端的修改操作，oracle会同时追加能够唯一标示记录的列到redo log。这样目标端数据库就可以知道源端发生了哪些具体的变化。))
SQL> alter database force logging;
SQL> alter system set recyclebin=off scope=spfile;(关闭回收站功能)
SQL> shutdown immediate;
SQL> startup
5.创建授权用户(支持DDL)
SQL> create user ggate identified by ggate default tablespace users temporary tablespace temp quota unlimited on users;
SQL> grant connect,resource,dba to ggate;
SQL> grant execute on utl_file to ggate;
SQL> grant restricted session to ggate;
SQL> @$GGATE_HOME/marker_setup.sql;
Enter GoldenGate schema name:ggate
SQL> @$GGATE_HOME/ddl_setup.sql;
Enter GoldenGate schema name:ggate
Enter mode of installation:INITIALSETUP
SQL> @$GGATE_HOME/role_setup.sql;
Enter GoldenGate schema name:ggate
SQL> grant GGS_GGSUSER_ROLE to ggate;
SQL> @$GGATE_HOME/ddl_enable.sql;
6.创建源端用户(用于同步的用户)
SQL> create user sender identified by ggate default tablespace users temporary tablespace temp quota unlimited on users;
SQL> grant dba to sender;
SQL> grant CREATE SESSION to sender;
SQL> grant dba,connect,resource to ggate;
SQL> grant select on v_$session to ggate;
[oracle@oracle111 ~]$ mkdir -p /oracle/app/ggate/dirdat/tt
7.gg源复制队列配置
[oracle@oracle111 ggate]$ ./ggsci
GGSCI (oracle111) 1> edit params mgr
添加:
PORT 7809
GGSCI (oracle111) 1> dblogin userid ggate,password ggate
GGSCI (oracle111) 2> add extract ext1,tranlog,begin now
GGSCI (oracle111) 3> add exttrail /oracle/app/ggate/dirdat/tt,extract ext1
GGSCI (oracle111) 4> edit params ext1
添加:
extract ext1
userid ggate,password ggate
rmthost 192.168.8.18, mgrport 7809
rmttrail /oracle/app/ggate/dirdat/tt
ddl include mapped objname sender.*;
table sender.*;
GGSCI (oracle111) 5> stop mgr
GGSCI (oracle111) 6> stop ext1
8.目标端配置环境()
[oracle@oracle18 admin]$ mkdir /oracle/app/ggate
[oracle@oracle18 ff]$ cp goldengate.zip /oracle/app/ggate/
[oracle@oracle18 ff]$ cd /oracle/app/ggate/
[oracle@oracle18 ggate]$ unzip goldengate.zip
[oracle@oracle18 ggate]$ tar -xvf fbo_ggs_Linux_x86_ora10g_32bit.tar
[oracle@oracle18 ~]$ vi .bash_profile
添加:
export GGATE_HOME=/oracle/app/ggate
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/oracle/app/ggate
[oracle@oracle18 ~]$ source .bash_profile
[oracle@oracle18 ~]$ cd /oracle/app/ggate/
[oracle@oracle18 ggate]$ ./ggsci
GGSCI (oracle18) 1> create subdirs
创建用户
[oracle@oracle18 ggate]$ sqlplus / as sysdba
SQL> create user ggate identified by ggate default tablespace users temporary tablespace temp quota unlimited on users;
SQL> create user receiver identified by ggate default tablespace users temporary tablespace temp quota unlimited on users;(接收同步的用户)
SQL> grant connect,resource,dba to ggate;
SQL> grant execute on utl_file to ggate;
SQL> grant restricted session to ggate;
SQL> grant connect,resource,dba to receiver;
SQL> grant select on v_$session to receiver;
开启归档模式
SQL> alter database archivelog;
SQL> alter database add supplemental log data;
SQL> alter database force logging;
[oracle@oracle18 ggate]$ ./ggsci
GGSCI (oracle18) 1> edit params ./GLOBALS
添加:
GGSCHEMA ggate
CHECKPOINTTABLE ggate.checkpoint
GGSCI (oracle18) 2> edit params mgr
添加:
PORT 7809
GGSCI (oracle18) 3> dblogin userid ggate,password ggate
GGSCI (oracle18) 4> add checkpointtable ggate.checkpoint (增加检测点)
GGSCI (oracle18) 5> add replicat rep1, exttrail /oracle/app/ggate/dirdat/tt, checkpointtable ggate.checkpoint (增加同步队列)
GGSCI (oracle18) 6> edit params rep1
添加:
replicat rep1
ASSUMETARGETDEFS
userid ggate, password ggate
discardfile /oracle/app/ggate/dirdat/rep1_discard.txt, append, megabytes 10
DDL include all
map sender.*, target receiver.*;
[oracle@oracle18 ggate]$ mkdir -p /oracle/app/ggate/dirdat/tt
9.验证
源,目标
alter system switch logfile;(让归档目录产生)
源端
GGSCI (oracle111) 1> start mgr
GGSCI (oracle111) 2> start ext1
GGSCI (oracle111) 3> view report ext1
目标端
GGSCI (oracle18) 1> start mgr
GGSCI (oracle18) 2> start rep1
SQL> alter system set nls_date_format='yyyy-mm-dd hh24:mi:ss' scope=spfile;
GGSCI (oracle111) 6> info ext1 detail
源端
SQL> conn sender/ggate
SQL> create table xixi(id int primary key);
SQL> insert into xixi values(123123);
SQL> commit;
目标端
SQL> conn receiver/ggate
SQL> select * from xixi; (也能看到xixi表,终于成功了)
ID
----------
123123
=====================================
排错::
[root@oracle18 ~]# ps -ef |grep /oracle/app
[oracle@oracle18 ggate]$ cat ggserr.log(日志)
--------------------------
日志工具
GGSCI (oracle18) 45> info rep2
REPLICAT REP2 Last Started 2013-03-01 12:50 Status ABENDED
Checkpoint Lag 00:00:00 (updated 00:02:17 ago)
Log Read Checkpoint File /oracle/app/ggate/dirdat/tt000000
First Record RBA 950
1.
[oracle@oracle18 ggate]$ ./logdump
Logdump 1 >open /oracle/app/ggate/dirdat/tt000000
Logdump 2 >pos 950
Logdump 3 >n(查看操作,可以打多次)
Logdump 8 >n
2013/03/05 01:13:18.952.227 DDLOP Len 1192 RBA 5192
Name:
After Image: Partition 0 G s
2c43 353d 2731 3030 3827 2c2c 4237 3d27 3530 3827 | ,C5='1008',,B7='508'
2c2c 4232 3d27 272c 2c42 333d 2753 454e 4445 5227 | ,,B2='',,B3='SENDER'
2c2c 4234 3d27 4845 4845 272c 2c43 3132 3d27 272c | ,,B4='HEHE',,C12='',
2c43 3133 3d27 272c 2c42 353d 2754 4142 4c45 272c | ,C13='',,B5='TABLE',
2c42 363d 2743 5245 4154 4527 2c2c 4238 3d27 4747 | ,B6='CREATE',,B8='GG
4154 452e 4747 535f 4444 4c5f 4849 5354 272c 2c42 | ATE.GGS_DDL_HIST',,B
393d 2753 454e 4445 5227 2c2c 4337 3d27 3130 2e32 | 9='SENDER',,C7='10.2
[oracle@oracle18 ggate]$ ./ggsci
GGSCI (oracle18) 1> alter replicat rep2,extrba 5192
GGSCI (oracle18) 9> start rep2 (跳过之前的加载内容)
