goldengate(o2o)部署for_12C(非cdb)_to_11G
环境:
OS:CentOS 7
Goldengate版本:12.2.0.2
|
原 库 |
目的库 |
||
|
Db版本 |
ip |
Db版本 |
ip |
|
12.2.0.1(非cdb) SQL> select cdb from v$database; CDB --------- NO |
192.168.1.135 |
11.2.0.4 |
192.168.1.118 |
1.安装步骤
1.1 介质准备
123014_fbo_ggs_Linux_x64_shiphome.zip
介质下载地址:
https://www.oracle.com/cn/database/technology/goldengate-downloads.html
1.2 安装Goldengate(主从上安装步骤一致)
源库(192.168.1.135):
http://blog.chinaunix.net/uid-77311-id-5786252.html
安装选择项:
INSTALL_OPTION=ORA12c
目的库(192.168.1.118):
http://blog.chinaunix.net/uid-77311-id-5786252.html
安装选择项:
INSTALL_OPTION=ORA11g
因为原和目的库版本不一样，安装的时候要注意选项项
[oracle@localhost response]$ cp oggcore.rsp myogg.rsp
#-------------------------------------------------------------------------------
# Specify the installation option.
# Specify ORA12c for installing Oracle GoldenGate for Oracle Database 12c and
# ORA11g for installing Oracle GoldenGate for Oracle Database 11g
#-------------------------------------------------------------------------------
INSTALL_OPTION=ORA12c
##因为目的端是11g的,所有目的端需要填写ORA11g
#-------------------------------------------------------------------------------
# Specify a location to install Oracle GoldenGate
#-------------------------------------------------------------------------------
SOFTWARE_LOCATION=/goldengate12c
1.3 为Goldengate创建子工作目录(Create sub working directories for Goldengate)
主库和目的库都要做同样的操作
----------------------源库----------------
[oracle@localhost ogg]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 12.3.0.1.4 OGGCORE_12.3.0.1.0_PLATFORMS_180415.0359_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Apr 16 2018 00:53:30
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2018, Oracle and/or its affiliates. All rights reserved.
GGSCI (localhost.localdomain) 1> create subdirs
Creating subdirectories under current directory /u01/ogg
Parameter file /u01/ogg/dirprm: created.
Report file /u01/ogg/dirrpt: created.
Checkpoint file /u01/ogg/dirchk: created.
Process status files /u01/ogg/dirpcs: created.
SQL script files /u01/ogg/dirsql: created.
Database definitions files /u01/ogg/dirdef: created.
Extract data files /u01/ogg/dirdat: created.
Temporary files /u01/ogg/dirtmp: created.
Credential store files /u01/ogg/dircrd: created.
Masterkey wallet files /u01/ogg/dirwlt: created.
Dump files /u01/ogg/dirdmp: created.
---------目的库------------------------
[oracle@localhost ogg]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 12.3.0.1.4 OGGCORE_12.3.0.1.0_PLATFORMS_180415.0359_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Apr 16 2018 00:53:30
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2018, Oracle and/or its affiliates. All rights reserved.
GGSCI (localhost.localdomain) 1> create subdirs
Creating subdirectories under current directory /u01/ogg
Parameter file /u01/ogg/dirprm: created.
Report file /u01/ogg/dirrpt: created.
Checkpoint file /u01/ogg/dirchk: created.
Process status files /u01/ogg/dirpcs: created.
SQL script files /u01/ogg/dirsql: created.
Database definitions files /u01/ogg/dirdef: created.
Extract data files /u01/ogg/dirdat: created.
Temporary files /u01/ogg/dirtmp: created.
Credential store files /u01/ogg/dircrd: created.
Masterkey wallet files /u01/ogg/dirwlt: created.
Dump files /u01/ogg/dirdmp: created.
1.4 配置数据库
1.4.1创建Goldengate用户(单独表空间,表空间大小为1GB)
源端(12c):
|
[oracle@localhost oracle]$ sqlplus / as sysdba ##创建表空间 create tablespace tps_goldengate datafile '/u01/app/oracle/oradata/ora12cgg/tps_goldengate01.dbf' size 100M autoextend on next 10m MAXSIZE unlimited; ##创建用户 create user goldengate identified by goldengate default tablespace tps_goldengate temporary tablespace TEMP quota unlimited on tps_goldengate; ##赋予权限 connect / as sysdba grant CONNECT, RESOURCE to goldengate; grant CREATE SESSION, ALTER SESSION to goldengate; grant SELECT ANY DICTIONARY, SELECT ANY TABLE to goldengate; grant ALTER ANY TABLE to goldengate; grant FLASHBACK ANY TABLE to goldengate; grant EXECUTE on DBMS_FLASHBACK to goldengate; grant EXECUTE on DBMS_CAPTURE_ADM to goldengate; ## TRANDATA追加日志时需要改权限 exec dbms_goldengate_auth.grant_admin_privilege ('goldengate'); grant exp_full_database to goldengate;
##最好是直接赋予dba权限 SQL> grant dba to goldengate;
|
目的端(11g):
|
[oracle@host01 ogg]$ sqlplus / as sysdba create tablespace tps_goldengate datafile '/u01/app/oracle/oradata/slnngk/tps_goldengate01.dbf' size 100M autoextend on next 10m MAXSIZE unlimited; create user goldengate identified by goldengate default tablespace tps_goldengate temporary tablespace TEMP quota unlimited on tps_goldengate;
grant CONNECT, RESOURCE to goldengate; grant CREATE SESSION, ALTER SESSION to goldengate; grant SELECT ANY DICTIONARY, SELECT ANY TABLE to goldengate; grant ALTER ANY TABLE to goldengate; grant FLASHBACK ANY TABLE to goldengate; exec dbms_goldengate_auth.grant_admin_privilege ('goldengate'); grant create public database link to goldengate; grant read,write on directory DATA_PUMP_DIR to goldengate; grant IMP_FULL_DATABASE to goldengate;
有以上权限就可以了，不需要单独给dba权限 ##赋予dba权限 SQL> grant dba to goldengate; |
测试表增删改权限赋予goldengate用户,源库和目标库都要授权(可以不需要)
|
SQL> connect / as sysdba SQL> grant all on hxl.TB_TEST01 to goldengate; SQL> grant all on hxl.TB_TEST02 to goldengate;
|
同时需要将目的库这两个表的数据清空,否则会包如下错误:
There is no trail to reposition to when doing direct load task.
1.4.2 源库启用补充日志模式
检查是否启用补充日志模式
|
SQL> connect / as sysdba Connected. SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;
SUPPLEME -------- NO |
如果为NO，则启用
|
SQL> alter database add supplemental log data;
Database altered.
|
1.4.3 源库启用归档模式
|
SQL>alter system set log_archive_dest_1="location=/u01/app/oracle/archlog" scope=both; SQL> shutdown immediate SQL> startup mount SQL> alter database archivelog; SQL> alter database open; SQL> alter system switch logfile;
|
检查归档情况
|
SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/app/oracle/archlog Oldest online log sequence 18 Next log sequence to archive 20 Current log sequence 20 |
1.4.5 源库启用日志模式
检查是否已经启动日志模式
|
SQL> SELECT force_logging FROM v$database;
FOR --- NO |
如果输入为NO，则需要启用
|
SQL> alter database force logging;
Database altered. |
1.4.6 启用goldengate复制参数
源库和目的库都需要修改
|
SQL> alter system set enable_goldengate_replication = true scope = both ;
System altered. |
该参数为全局参数，不需要指定具体的pdbs,
1.4.8 源库同步的表启用补充日志
[oracle@localhost goldengate12c]$ ./ggsci
GGSCI (localhost.localdomain) 2> dblogin userid goldengate, password goldengate
Successfully logged into database.对整个schema hxl进行补充日志
add schematrandata hxl allcols 反向操作delete schematrandata hxl allcols
针对具体的某些表进行添加补充日志，反向操作delete TRANDATA hxl.tb_test
GGSCI (goldengate@ora12cgg) 4> add TRANDATA hxl.tb_test01
GGSCI (goldengate@ora12cgg) 5> add TRANDATA hxl.tb_test02
查看启用情况
|
GGSCI (localhost.localdomain as goldengate@ora12cgg) 6> info TRANDATA epi_hunan.tb_test01
Logging of supplemental redo log data is enabled for table HXL.TB_TEST01.
Columns supplementally logged for table HXL.TB_TEST01: ID.
Prepared CSN for table EPI_HUNAN.TB_TEST01: 1513500 GGSCI (localhost.localdomain as goldengate@ora12cgg) 7> info TRANDATA HXL.tb_test02
Logging of supplemental redo log data is enabled for table HXL.TB_TEST02.
Columns supplementally logged for table HXL.TB_TEST02: ID.
Prepared CSN for table EPI_HUNAN.TB_TEST02: 1513745 GGSCI (localhost.localdomain as goldengate@ora12cgg) 8> |
1.6 配置Goldengate管理进程
1.6.1 生成加密密码
ogg的配置文件里为了安全起见,需要使用加密的密码
GGSCI (localhost.localdomain) 2> encrypt password goldengate,ENCRYPTKEY default
Using Blowfish encryption with DEFAULT key.
Encrypted password: AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC
Algorithm used: BLOWFISH
GGSCI (localhost.localdomain) 3>
测试登录
GGSCI (localhost.localdomain) 3> dblogin userid goldengate, password AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC,ENCRYPTKEY default
Successfully logged into database.
1.6.2 源库配置MGR
创建管理参数文件
|
[oracle@host01 ogg]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle Version 11.1.1.0.0 Build 078 Linux, x64, 64bit (optimized), Oracle 11 on Jul 28 2010 13:13:42
Copyright (C) 1995, 2010, Oracle and/or its affiliates. All rights reserved.
GGSCI (host01) 1> EDIT PARAMS MGR |
在编辑文件里配置如下参数，操作跟vi一样，wq!保存退出
|
port 7809 DYNAMICPORTLIST 7900-7950 PURGEOLDEXTRACTS ./dirdat/ep*, USECHECKPOINTS, MINKEEPHOURS 2, FREQUENCYMINUTES 30 userid goldengate, password AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC ,ENCRYPTKEY DEFAULT AUTORESTART EXTRACT DPEP RETRIES 3 WAITMINUTES 5 RESETMINUTES 60 AUTORESTART EXTRACT extep RETRIES 3 WAITMINUTES 5 RESETMINUTES 60 |
启动
|
GGSCI (host01) 2> START MGR
Manager started. |
检查启动情况
|
GGSCI (host01) 3> INFO MGR
Manager is running (IP port host01.7809). |
1.6.3 目的库配置MGR
创建配置参数
|
[oracle@host02 ogg] ./ggsci GGSCI (host02) 1> EDIT PARAMS MGR |
配置文件输入如下内容
|
port 7809 DYNAMICPORTLIST 7900-7950 purgeoldextracts ./dirdat/*, usecheckpoints, minkeepdays 3 userid goldengate, password AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC ,ENCRYPTKEY DEFAULT |
启动并查看启动信息
|
GGSCI (host02) 2> START MGR
Manager started.
GGSCI (host02) 3> INFO MGR
Manager is running (IP port host02.7809). |
1.6.4 验证主从网络是否畅通
[oracle@localhost goldengate12c]$ telnet 192.168.1.118 7809
Trying 192.168.1.118...
Connected to 192.168.1.118.
Escape character is '^]'.
Connection closed by foreign host.
[oracle@localhost goldengate12c]$ telnet 192.168.1.135 7809
Trying 192.168.1.135...
Connected to 192.168.1.135.
Escape character is '^]'.
Connection closed by foreign host.
1.7 源库配置抽取（Extract）进程
1.7.1编辑抽取(extract)进程参数并添加extract
|
GGSCI (host02) 15> EDIT PARAMS extep |
添加如下内容:
|
extract extep userid goldengate, password AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC ,ENCRYPTKEY DEFAULT SETENV (ORACLE_SID="ora12cgg") SETENV ( NLS_LANG = AMERICAN_AMERICA.ZHS16GBK) exttrail ./dirdat/ep BR BRINTERVAL 20M numfiles 5000 warnlongtrans 3h, checkinterval 10m TRANLOGOPTIONS LOGRETENTION ENABLED --TRANLOGOPTIONS DBLOGREADER
--DDL parameter DDL & EXCLUDE INSTR 'shrink space CHECK' & INCLUDE MAPPED OBJTYPE 'TABLE' & INCLUDE MAPPED OBJTYPE 'INDEX' & INCLUDE MAPPED OBJTYPE 'SEQUENCE' & INCLUDE MAPPED OBJTYPE 'VIEW' & INCLUDE MAPPED OBJTYPE 'PROCEDURE' & INCLUDE MAPPED OBJTYPE 'FUNCTION' & INCLUDE MAPPED OBJTYPE 'PACKAGE'
DDLOPTIONS ADDTRANDATA REPORT
table hxl.tb_test01; table hxl.tb_test02; |
该配置在12c pdb同步到11g的情况下发现无法同步索引，后来加上如下该句就可以了
INCLUDE INSTR 'CREATE INDEX'
若这里想同步整个schema的表的话,语法如下:
table hxl.*
添加extract执行如下命令
|
GGSCI (host01) 2> ADD EXTRACT extep, TRANLOG, BEGIN NOW EXTRACT added. |
1.7.2 定义GoldenGate local trail
|
GGSCI (host01) 3> add EXTTRAIL ./dirdat/ep, EXTRACT extep, MEGABYTES 100 EXTTRAIL added. |
1.7.3 抽取进行注册容器(logretention)
dblogin userid goldengate password goldengate
register extract EXTEP logretention
##取消注册
unregister extract EXTEP logretention
pdb模式下不需要进行该注册
1.7.4 配置支持DDL
需要进入到goldengate安装的目录下执行,脚本提示输入的schema都填写goldengate
|
cd /goldengate12 sqlplus /nolog connect / as sysdba SQL> GRANT EXECUTE ON UTL_FILE TO goldengate; SQL> @marker_setup.sql SQL> @ddl_setup.sql SQL> @role_setup.sql SQL> GRANT ggs_ggsuser_role TO goldengate; SQL> @ddl_enable.sql |
1.7.5 启动主抽取进程
启动抽取进程
|
GGSCI (host01) 4> START EXTRACT extep
Sending START request to MANAGER ... EXTRACT EORA_1 starting |
查看启动情况
|
GGSCI (host01) 5> INFO EXTRACT ext
EXTRACT EORA_1 Last Started 2018-05-04 01:10 Status RUNNING Checkpoint Lag 00:00:00 (updated 00:00:08 ago) Log Read Checkpoint Oracle Redo Logs 2018-05-04 01:10:28 Seqno 7, RBA 8631808 |
Goldengate会生成“ep000000”这样的文件在配置目录
|
[oracle@host01 dirdat]$ pwd /u01/app/ogg/dirdat [oracle@host01 dirdat]$ ls -al total 8 drwxrwxr-x. 2 oracle oinstall 21 May 4 01:10 . drwxr-xr-x. 14 oracle oinstall 4096 May 4 01:10 .. -rw-rw-rw-. 1 oracle oinstall 934 May 4 01:10 ep000000 [oracle@host01 dirdat]$ |
1.8 源库配置pump(传递)进程
1.8.1 编辑data pump进程参数
|
GGSCI (host01) 8> EDIT PARAMS dpep |
输入如下内容
|
extract dpep userid goldengate, password AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC ,ENCRYPTKEY DEFAULT RmtHost 192.168.1.118, MgrPort 7809, Compress RmtTrail ./dirdat/ep gettruncates Passthru table hxl.*; |
添加data pump Extract组
|
GGSCI (host01) 8> ADD EXTRACT dpep, EXTTRAILSOURCE ./dirdat/ep EXTRACT added. |
查看
|
GGSCI (host01) 9> INFO EXTRACT dpep
EXTRACT dpep Initialized 2018-05-04 02:30 Status STOPPED Checkpoint Lag 00:00:00 (updated 00:00:27 ago) Log Read Checkpoint File ./dirdat/aa000000 First Record RBA 0 |
1.8.2 添加GoldenGate remote trail
|
ADD RMTTRAIL ./dirdat/ep, EXTRACT dpep, MEGABYTES 100 |
1.8.3 启动
|
GGSCI (host01) 11> START EXTRACT DPEP Sending START request to MANAGER ... EXTRACT dpep starting |
查看启动结果
|
GGSCI (host01) 12> INFO EXTRACT dpep
EXTRACT dpep Last Started 2018-05-04 02:33 Status RUNNING Checkpoint Lag 00:00:00 (updated 00:00:00 ago) Log Read Checkpoint File ./dirdat/aa000000 First Record RBA 934 |
在目的库上的目录下可以看到如下文件
|
[oracle@host02 dirdat]$ pwd /u01/app/ogg/dirdat [oracle@host02 dirdat]$ ls -al total 4 drwxrwxr-x. 2 oracle oinstall 21 May 4 02:33 . drwxr-xr-x. 13 oracle oinstall 4096 May 3 22:45 .. -rw-rw-rw-. 1 oracle oinstall 0 May 4 02:33 ep000000 [oracle@host02 dirdat]$ |
1.9 通过dblink进行数据初始化
1.9.1 在目的端创建dblink
|
SQL> connect goldengate/goldengate Connected. SQL> create public database link dblink_source connect to goldengate identified by goldengate using 'ora12c_135';
验证dblink SQL> select * from dual@dblink_source;
D - X |
tnsnames.ora文件内容如下
|
[oracle@localhost admin]$ more tnsnames.ora ora12c_135 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.135)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ora12cgg) ) ) |
1.9.2 在目的端创建相应的业务库用户
注意我这里业务账号用得表空间是users表空间,因为原端业务的该账号的表空间也是users表空间,要保持一致,否则报错
源端对象所在的表空间也需要在目的端同样创建
|
SQL> connect / as sysdba Connected. SQL> create user hxl identified by oracle default tablespace users; SQL> grant connect,resource,dba to hxl; SQL> alter user hxl quota unlimited on users; |
1.9.3 记录下当前源库的scn
SQL> connect / as sysdba
Connected.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1559828
1.9.4 通过dblink impdump初始化数据
|
[oracle@localhost admin]$ impdp goldengate/goldengate network_link=dblink_source FLASHBACK_SCN=1559828 directory=DATA_PUMP_DIR EXCLUDE=STATISTICS cluster=n tables=hxl.tb_test01,hxl.tb_test02 table_exists_action=replace
Import: Release 11.2.0.4.0 - Production on Tue Feb 11 14:57:30 2020
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options FLASHBACK automatically enabled to preserve database integrity. Starting "GOLDENGATE"."SYS_IMPORT_TABLE_01": goldengate/******** network_link=dblink_source FLASHBACK_SCN=1559828 directory=DATA_PUMP_DIR EXCLUDE=STATISTICS cluster=n tables=epi_hunan.tb_test01,epi_hunan.tb_test02 table_exists_action=replace Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 5 MB Processing object type TABLE_EXPORT/TABLE/PROCACT_INSTANCE Processing object type TABLE_EXPORT/TABLE/TABLE . . imported "EPI_HUNAN"."TB_TEST01" 98002 rows . . imported "EPI_HUNAN"."TB_TEST02" 0 rows Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Job "GOLDENGATE"."SYS_IMPORT_TABLE_01" successfully completed at Tue Feb 11 14:57:57 2020 elapsed 0 00:00:24 |
或是同步整个schema的话,语法如下:
impdp goldengate/goldengate network_link=dblink_source FLASHBACK_SCN= 146751587 directory=DATA_PUMP_DIR EXCLUDE=STATISTICS cluster=n schemas=hxl table_exists_action=replace
1.10 目标库配置复制(非cdb)
1.10.1 创建全局参数(可不做)
|
GGSCI (host02) 13> EDIT PARAMS ./GLOBALS 名字必须大写 |
输入如下内容：
|
CHECKPOINTTABLE goldengate.ggschkpt |
检查
|
GGSCI (host02) 13> exit [oracle@host02 ogg]$ ll GLOBALS -rw-rw-rw-. 1 oracle oinstall 29 May 4 02:54 GLOBALS |
Add replicat checkpoint table in target system
|
[oracle@host02 ogg]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle Version 11.1.1.0.0 Build 078 Linux, x64, 64bit (optimized), Oracle 11 on Jul 28 2010 13:13:42
Copyright (C) 1995, 2010, Oracle and/or its affiliates. All rights reserved.
GGSCI (host02) 1> DBLOGIN USERID goldengate, PASSWORD goldengate Successfully logged into database.
GGSCI (host02) 2> ADD CHECKPOINTTABLE
No checkpoint table specified, using GLOBALS specification (ogg.ggschkpt)...
Successfully created checkpoint table goldengate.GGSCHKPT. |
|
[oracle@host02 ogg]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.4.0 Production on Fri May 4 03:01:58 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
SQL> conn ogg/ogg Connected. SQL> select * from tab;
TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- GGSCHKPT TABLE |
1.10.2 编辑Delivery进程参数
|
GGSCI (localhost.localdomain) 12> add replicat repep, exttrail ./dirdat/ep, NODBCHECKPOINT |
|
GGSCI (host02) 3>EDIT PARAM repep |
添加如下内容
|
replicat repep userid goldengate, password AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC ,ENCRYPTKEY DEFAULT DiscardFile ./dirrpt/repep.dsc, append, Megabytes 200 gettruncates AllowNoopUpdates ASSUMETARGETDEFS DBOPTIONS SUPPRESSTRIGGERS DBOPTIONS DEFERREFCONST HANDLECOLLISIONS MAP hxl.tb_test01,TARGET hxl.tb_test01; MAP hxl.tb_test02,TARGET hxl.tb_test02; DDLERROR 10655 IGNORE DDLERROR 10636 IGNORE |
若是这里同步的是整个schema的话,语法如下:
MAP hxl.*, TARGET hxl.*;
1.10.3 通过scn启动复制进程
start repep aftercsn 1559828
这里的scn就是impdump初始化时的scn
1.11 验证
主库进行dml操作、创建删除索引
