在dg备库配置ogg并抽取数据

miss_puff

一、规划

	主库	ogg源端	ogg目标端
操作系统	RHEL 6.8	RHEL 6.8	RHEL 6.8
主机名	rac01/rac02	datahub	dbserver
ip地址	192.168.133.121/192.168.133.122	192.168.133.115	192.168.133.120
架构	RAC	单机	单机
角色	primary	physical database
DB Version	11.2.0.4	11.2.0.4	11.2.0.3
sid	orcl1/orcl2	orcl	orcl
ogg版本	12.3.0.1.4	12.3.0.1.4	12.3.0.1.4
ogg路径	/u01/app/sourceogg	/u01/app/ogg	/home/oracle/ogg
ogg管理员	oggadmin	oggadmin	oggadmin
同步用户	sztech	sztech	sztech

二、安装ogg
主库、dg备库、ogg目标端均安装ogg软件，步骤略

miss_puff

三、主库配置
（1）开启数据库force logging
SQL> alter database force logging;

Database altered.
（2）开启数据库最小附加日志

SQL> alter database add supplemental log data;

Database altered.

（3）启用ogg
SQL> alter system set enable_goldengate_replication=true;

System altered.
（4）创建ogg管理用户
create tablespace ogg datafile ‘+ogg’ size 200m autoextend on next 5m;
create user oggadmin identified by oggadmin default tablespace ogg;
grant dba to oggadmin;

（5）配置安全认证
GGSCI (rac01) 1> add credentialstore

Credential store created.

GGSCI (rac01) 2> alter credentialstore add user oggadmin,password oggadmin

Credential store altered.

GGSCI (rac01) 3> dblogin useridalias oggadmin
Successfully logged into database.

GGSCI (rac01 as oggadmin@orcl1) 4>
（6）添加trandata
GGSCI (rac01) 3> dblogin useridalias oggadmin
Successfully logged into database.

GGSCI (rac01 as oggadmin@orcl1) 4> add trandata sztech.test

2019-03-02 17:12:20  WARNING OGG-06439  No unique key is defined for table TEST. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.

2019-03-02 17:12:20  INFO    OGG-15130  No key found for table SZTECH.TEST.  All viable columns will be logged.

2019-03-02 17:12:21  INFO    OGG-15132  Logging of supplemental redo data enabled for table SZTECH.TEST.

2019-03-02 17:12:21  INFO    OGG-15133  TRANDATA for scheduling columns has been added on table SZTECH.TEST.

2019-03-02 17:12:21  INFO    OGG-15135  TRANDATA for instantiation CSN has been added on table SZTECH.TEST.

GGSCI (rac01 as oggadmin@orcl1) 5> info trandata sztech.test

Logging of supplemental redo log data is enabled for table SZTECH.TEST.

Columns supplementally logged for table SZTECH.TEST: "ID", "NAME".

Prepared CSN for table SZTECH.TEST: 1006556
GGSCI (rac01 as oggadmin@orcl1) 6>

miss_puff

四、ogg源端配置（dg端）
1）配置安全认证
备库的oggadmin管理用户和管理表空间从主库同步而来。
GGSCI (datahub) 1> add credentialstore

Credential store created.

GGSCI (datahub) 2> alter credentialstore add user oggadmin,password oggadmin

Credential store altered.

GGSCI (datahub) 3> dblogin useridalias oggadmin
Successfully logged into database.

GGSCI (datahub as oggadmin@orcl) 4>

（2）查看trandata
GGSCI (datahub as oggadmin@orcl) 17> info trandata sztech.*

Logging of supplemental redo log data is enabled for table SZTECH.TEST.

Columns supplementally logged for table SZTECH.TEST: "ID", "NAME".

Prepared CSN for table SZTECH.TEST: 1006556

dg备库只需要查看下就行，add trandata只能在主库操作。备库操作则会报错，提示数据库处于只读模式

（3）配置管理进程
GGSCI (datahub as oggadmin@orcl) 17> view params mgr

PORT 7809
DYNAMICPORTLIST 7801-7809
AUTORESTART ER *,RETRIES 5,WAITMINUTES 7
PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 7
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45

（4）配置抽取进程
GGSCI (datahub as oggadmin@orcl) 18> view params extorcl

add extract extorcl,tranlog,begin now,threads 2
add exttrail ./dirdat/eb,extract extorcl,megabytes 100


extract extorcl
setenv(NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")
setenv(ORACLE_SID=orcl)
useridalias oggadmin
discardfile ./dirrpt/extorcl.dsc,purge,megabytes 200
exttrail ./dirdat/eb
tranlogoptions minefromactivedg
tranlogoptions altarchivedlogformat %t_%s_%r.dbf
tranlogoptions altarchivelogdest /u01/app/oracle/archivelog
statoptions reportfetch
table sztech.test;

（5）配置传输进程
GGSCI (datahub as oggadmin@orcl) 48> view params p_orcl

add extract p_orcl,exttrailsource ./dirdat/eb,begin now
add rmttrail /home/oracle/ogg/dirdat/pb,extract p_orcl,megabytes 100

extract p_orcl
USERIDALIAS oggadmin
rmthost 192.168.133.120,mgrport 7809,compress
rmttrail /home/oracle/ogg/dirdat/pb
passthru
table sztech.test;
（6）查看进程状态
GGSCI (datahub) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          
EXTRACT     RUNNING     EXTORCL     00:00:00      00:00:08   
EXTRACT     RUNNING     P_ORCL      00:00:00      00:00:04   

miss_puff

五、ogg目标端配置
（1）开启数据库force logging
SQL> alter database force logging;

Database altered.
（2）开启数据库最小附加日志

SQL> alter database add supplemental log data;

Database altered.

（3）启用ogg
SQL> alter system set enable_goldengate_replication=true;

System altered.
（4）创建ogg管理用户
create tablespace ogg datafile'/u01/app/oracle/oradata/orcl/ogg.dbf' size 100m autoextend on;create user oggadmin identified by oggadmin default tablespace ogg;
grant dba to oggadmin;
（5）配置认证证书
add credentialstore
alter credentialstore add user oggadmin,password oggadmin alias oggorcl
info credentialstore

（6）配置管理进程
PORT 7809
DYNAMICPORTLIST 7810-7890
AUTOSTART EXTRACT *
AUTORESTART EXTRACT *,RETRIES 5, WAITMINUTES 3,RESETMINUTES 5

（7）配置复制进程
replicat reporcl
setenv(ORACLE_SID=orcl)
useridalias oggorcl
assumetargetdefs
allownoopupdates
discardfile ./dirrpt/reporcl.dsc,append
discardrollover at 02:00 on sunday
map sztech.test,target sztech.test;

add checkpointtable oggadmin.chkpt
add replicat reporcl,exttrail ./dirdat/pb checkpointtable oggadmin.chkpt

miss_puff

六、数据初始化
（1）主库导出数据
SQL>  select to_char(current_scn,'999999999999999') from v$database;

TO_CHAR(CURRENT_
----------------
         1447981

SQL> create directory exp_dir as '/home/oracle/dump';

Directory created.

[oracle@rac01 dump]$ expdp oggadmin/oggadmin directory=exp_dir dumpfile=sztech.dmp schemas=sztech flashback_scn= 1447981

（2）目标端导入
SQL> create directory imp_dir as '/home/oracle/dump';

Directory created.

[oracle@dbserver dump]$ impdp system/oracle directory=imp_dir dumpfile=sztech.dmp  table_exists_action=replace



（3）启用复制进程
GGSCI (dbserver) 6> start reporcl,aftercsn 1447981

Sending START request to MANAGER ...
REPLICAT REPORCL starting


GGSCI (dbserver) 7> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          
REPLICAT    RUNNING     REPORCL     00:00:00      00:00:05   

miss_puff

七、数据同步测试
（1）主库插入数据

登录/注册后可看大图