1.环境介绍
1.1.环境清单
| 类别 | 源端 | 目标端 |
|---|---|---|
| 数据库类型 | RAC | 单实例 |
| 数据库版本 | 11.2.0.4 | 11.2.0.4 |
| DB_NAME | orcl1,orcl2 | dhh |
| 主机IP地址 | rac1:192.168.40.200 | |
| rac2:192.168.40.201 | 192.168.40.52 | |
| OS版本 | Red Hat Enterprise Linux Server release 6.5 (Santiago) | Red Hat Enterprise Linux Server release 7.6 (Maipo) |
| OGG版本 | 19.1.0.0.4 64位 | 19.1.0.0.4 64位 |
| 主机名 | orcl01,orcl02 | 11g-db |
1.2.必要条件
◆必须保证SourceDB是运行在归档模式下。
◆目的 ：将SourceDB中相关Schema中的数据同步复制到TargetDB相对应的Sechema中。
◆首先要保持SourceDB, TargetDB相关Schema中的初始数据一致，可以通过expdp/impdp,rman,冷备等方式实现。
◆双方DB建立GoldenGate用户，赋予DBA权限，用于GoldenGate连接DB。
◆双方DB安装GoldenGate。
◆SourceDB必须开始最小附加日志模式。
1.3. 配置Linux内核参数
编辑**/etc/sysctl.conf文件进行配置，在源和目标端**设置Linux内核参数vm.max_map_count=2097152
max_map_count表示单个进程可以分配的内存映射区域的最大数量
2.监听服务配置
2.1.监听配置
加入对ASM的动态注册，加入红色字体部分，具体配置根据环境决定。
注意：RAC所有节点都要配置
[grid@rac1 ~]$ cat /u01/app/11.2.0/grid/network/admin/listener.ora
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = +ASM)
(ORACLE_HOME=/u01/app/11.2.0/grid)
(SID_NAME = +ASM1)
)
)
--reload监控，使配置生效
[grid@rac1 ~]$ lsnrctl reload
--查看监听状态
[grid@rac1 ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 14-AUG-2023 18:17:24
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 14-AUG-2023 17:53:51
Uptime 0 days 0 hr. 23 min. 32 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File /u01/app/grid/diag/tnslsnr/rac1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.40.20)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.40.21)(PORT=1521)))
Services Summary...
Service "+ASM" has 2 instance(s).
Instance "+ASM1", status UNKNOWN, has 1 handler(s) for this service... #静态监听
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "racdb" has 1 instance(s).
Instance "racdb1", status READY, has 1 handler(s) for this service...
Service "racdbXDB" has 1 instance(s).
Instance "racdb1", status READY, has 1 handler(s) for this service...
The command completed successfully
--测试连接是否正常
[root@rac1 ~]# su - oracle
[oracle@rac1 ~]$ sqlplus sys/oracle@192.168.40.200:1521/+ASM as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Aug 14 18:18:34 2023
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options
SQL>
2.2.配置tnsname.ora
--节点1
注意：要用oracle用户去配置
[oracle@rac1 admin]$ cd /u01/app/oracle/11.2.0/dbhome_1/network/admin
[oracle@rac1 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
RACDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = scan-cluster)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = racdb)
)
)
+ASM =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.40.200)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = +ASM)
)
)
--节点2
注意：要用oracle用户去配置
[oracle@rac2 ~]$ cd /u01/app/oracle/11.2.0/dbhome_1/network/admin
[oracle@rac2 admin]$ vi tnsnames.ora
[oracle@rac2 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
RACDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = scan-cluster)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = racdb)
)
)
+ASM =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.40.201)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = +ASM)
)
)
3.安装前的准备工作
3.1.源端创建GoldenGate用户表空间
create tablespace ogg_data datafile '+DATA' size 20m autoextend on;
rac的asm会自动进行数据文件命名
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
...
+DATA/orcl/datafile/ogg_data.279.1144958697
3.2.源端创建GoldenGate用户
create user ogg identified by ogg default tablespace ogg_data;
grant connect,resource,dba,create table,create sequence to ogg;
3.3.目标端创建GoldenGate用户表空间
create tablespace ogg_data datafile '/u01/app/oracle/oradata/dhh/ogg01.dbf' size 20m autoextend on;
3.4.目标端创建GoldenGate用户表空间
create user ogg identified by ogg default tablespace ogg_data;
grant connect,resource,dba,create table,create sequence to ogg;
3.5.源端创建测试用户及测试数据
create user test identified by test;
grant connect,resource to test;
conn test/test
Connected.
create table test (id number(10) primary key ,name varchar(8));
insert into test values(1,'zhangsan');
insert into test values(2,'lisi');
commit;
3.6.目标端创建测试用户及测试数据
create user test identified by test;
grant connect,resource to test;
conn test/test
create table test (id number(10) primary key ,name varchar(8));
目标端不需要插入数据
3.7.源端开启归档模式、强制日志、附加日志
3.7.1.查看是否开启归档模式、强制日志、附加日志
SQL> select LOG_MODE, SUPPLEMENTAL_LOG_DATA_MIN,FORCE_LOGGING from v$database;
LOG_MODE SUPPLEME FOR
------------ -------- ---
ARCHIVELOG NO NO
3.7.2.开启归档
修改归档路径参数
SQL> alter system set log_archive_dest_1='location=+ARCH' scope=spfile sid='*';
关闭数据库，全部节点都要关闭
[oracle@rac2 ~]$ srvctl stop database -d RACDB
节点2 数据库启动到mount状态
[oracle@rac2 ~]$ srvctl start instance -d RACDB -i RACDB1 -o mount
修改归档并启动数据库
alter database archivelog;
alter database open;
查看归档信息
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +DATA1/racdb/archivelog
Oldest online log sequence 49
Next log sequence to archive 50
Current log sequence 50
启动节点1
alter database open;
3.7.3.开启强制日志
alter database force logging;
3.7.4.开启附加日志
alter database add supplemental log data;
3.7.5.查看是否开启归档模式、强制日志、附加日志
SQL> select LOG_MODE, SUPPLEMENTAL_LOG_DATA_MIN,FORCE_LOGGING from v$database;
LOG_MODE SUPPLEME FOR
------------ -------- ---
ARCHIVELOG YES YES
3.7.6.查看回收站是否关闭
SQL> show parameter recycle
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_recycle string
db_recycle_cache_size big integer 0
recyclebin string on
SQL> alter system set recyclebin=off scope=spfile;
System altered.
--重启数据库查看
SQL> show parameter recycle
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_recycle string
db_recycle_cache_size big integer 0
recyclebin string off
4.GoldenGate安装
4.1.源端安装OGG
4.1.1.创建软件安装目录并赋权
为Source端分配磁盘创建OGG，该磁盘为共享类型
"E:\Program Files (x86)\VMware\VMware Workstation\vmware-vdiskmanager.exe" -c -s 20GB -a lsilogic -t 2 "E:\Program Files (x86)\VMware\ractest\rac1test\ogg.vmdk"
源目录都增加该磁盘
划分磁盘
[root@rac2 ~]# fdisk /dev/sde
格式化磁盘
[root@rac2 ~]# mkfs -t xfs /dev/sde1
创建挂在点
[root@rac2 ~]# mkdir /goldengate
[root@rac2 ~]# chown -R oracle:oinstall /goldengate
加载挂载点
[root@rac2 ~]# mount /dev/sde1 /goldengate
[root@rac2 ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/rhel-root 45G 12G 33G 27% /
devtmpfs 2.3G 0 2.3G 0% /dev
tmpfs 2.3G 115M 2.2G 5% /dev/shm
tmpfs 2.3G 8.9M 2.3G 1% /run
tmpfs 2.3G 0 2.3G 0% /sys/fs/cgroup
/dev/sda1 1014M 178M 837M 18% /boot
tmpfs 468M 12K 468M 1% /run/user/42
tmpfs 468M 0 468M 0% /run/user/0
/dev/sde1 9.8G 23M 9.2G 1% /goldengate
开机自动加载
cat >> /etc/fstab << "EOF"
#add for ogg
/dev/sde1 /goldengate xfs defaults 1 2
EOF
赋予权限
chown -R oracle:oinstall /goldengate
chmod -R 755 /goldengate
4.1.2.配置oracle用户环境变量
两个节点都要配置
[oracle@www.cndba.cn ~]$ vi .bash_profile
设置Library 路径
假设OGG的安装目录是/goldengate，那么在/home/oracle/.bash_profile文件里添加如下内容：
export OGG_HOME=/goldengate
export PATH=$ORACLE_HOME/bin:$PATH:$OGG_HOME
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$OGG_HOME:/lib:/usr/lib
--source 使修改生效：
[oracle@www.cndba.cn ~]$ source .bash_profile
4.1.3.解压ogg文件并安装
只需要在一个节点做就可以
[root@orcl01 ~]# unzip 19.1.0.0.4_x86_64_V983658-01.zip
[root@orcl01 ~]# mv /root/fbo_ggs_Linux_x64_shiphome /home/oracle/
[root@orcl01 Disk1]# chown -R oracle:oinstall /home/oracle/fbo_ggs_Linux_x64_shiphome
su - oracle
cd /home/oracle/fbo_ggs_Linux_x64_shiphome/Disk1
export DISPLAY=192.168.16.67:0.0
./runInstaller
注意：/goldengate 是$OGG_HOME
4.1.4.运行ogg并创建目录
11.2.0.1需要创建目录，19c目录已存在
[oracle@orcl01:/home/oracle]$ cd $OGG_HOME
[oracle@orcl01:/goldengate]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 19.1.0.0.4 OGGCORE_19.1.0.0.0_PLATFORMS_191017.1054_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Oct 17 2019 23:13:12
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.
GGSCI (orcl01) 1> create subdirs
Creating subdirectories under current directory /goldengate
Parameter file /goldengate/dirprm: already exists.
Report file /goldengate/dirrpt: already exists.
Checkpoint file /goldengate/dirchk: already exists.
Process status files /goldengate/dirpcs: already exists.
SQL script files /goldengate/dirsql: already exists.
Database definitions files /goldengate/dirdef: already exists.
Extract data files /goldengate/dirdat: already exists.
Temporary files /goldengate/dirtmp: already exists.
Credential store files /goldengate/dircrd: already exists.
Masterkey wallet files /goldengate/dirwlt: already exists.
Dump files /goldengate/dirdmp: already exists.
4.2.目标端安装OGG
4.2.1.创建软件安装目录并赋权
[root@11g-db oracle]# mkdir -p /u01/app/oracle/ogg
[root@11g-db oracle]# chown -R oracle:oinstall /u01/app/oracle/ogg
4.2.2.配置oracle用户环境变量
[oracle@11g-db ~]$ vi ~/.bash_profile
设置Library 路径
假设OGG的安装目录是/u01/app/oracle/ogg，那么在/home/oracle/.bash_profile文件里添加如下内容：
#ogg setting add
export OGG_HOME=$ORACLE_BASE/ogg
export PATH=$ORACLE_HOME/bin:$PATH:$OGG_HOME
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$OGG_HOME:/lib:/usr/lib
--source 使修改生效：
[oracle@11g-db ~]$ source ~/.bash_profile
4.2.3.解压ogg文件并安装
[root@11g-db ~]# unzip 19.1.0.0.4_x86_64_V983658-01.zip
[root@11g-db ~]# mv /root/fbo_ggs_Linux_x64_shiphome /home/oracle/
[root@11g-db Disk1]# chown -R oracle:oinstall /home/oracle/fbo_ggs_Linux_x64_shiphome
su - oracle
cd /home/oracle/fbo_ggs_Linux_x64_shiphome/Disk1
export DISPLAY=192.168.16.67:0.0
./runInstaller
之后确认目录和端口下一步即可
4.2.4.运行ogg并创建目录
11.2.0.1需要创建目录，19c目录已存在
[oracle@11g-db trace]$ cd $OGG_HOME
[oracle@11g-db ogg]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 19.1.0.0.4 OGGCORE_19.1.0.0.0_PLATFORMS_191017.1054_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Oct 17 2019 23:13:12
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.
GGSCI (11g-db) 1> create subdirs
Creating subdirectories under current directory /u01/app/oracle/ogg
Parameter file /u01/app/oracle/ogg/dirprm: already exists.
Report file /u01/app/oracle/ogg/dirrpt: already exists.
Checkpoint file /u01/app/oracle/ogg/dirchk: already exists.
Process status files /u01/app/oracle/ogg/dirpcs: already exists.
SQL script files /u01/app/oracle/ogg/dirsql: already exists.
Database definitions files /u01/app/oracle/ogg/dirdef: already exists.
Extract data files /u01/app/oracle/ogg/dirdat: already exists.
Temporary files /u01/app/oracle/ogg/dirtmp: already exists.
Credential store files /u01/app/oracle/ogg/dircrd: already exists.
Masterkey wallet files /u01/app/oracle/ogg/dirwlt: already exists.
Dump files /u01/app/oracle/ogg/dirdmp: already exists.
5.GoldenGate配置
5.1.OGG源端配置
5.1.1.编辑GLOBALS
GGSCI (cndba) 4> dblogin userid ogg,password ogg
Successfully logged into database.
GGSCI (cndba) 4> edit params ./GLOBALS
GGSCI (cndba) 4> view params ./GLOBALS
GGSCHEMA ogg
5.1.2.配置mgr进程
GGSCI (cndba) 3> edit params mgr
GGSCI (cndba) 4> view params mgr
port 7809
autostart replicat *
autorestart replicat *,retries 3,waitminutes 3
GGSCI (cndba) 5> start mgr
MGR is already running.
GGSCI (cndba) 6> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
GGSCI (cndba) 7> sh netstat -ntpl |grep 7809 --查看7809端口是否启用
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
tcp 0 0 :::7809 :::* LISTEN 14176/./mgr
GGSCI (rac1) 7> sh ps -ef|grep mgr
postfix 1424 1395 0 00:40 ? 00:00:00 qmgr -l -t unix -u
oracle 7535 7362 0 02:59 ? 00:00:00 ./mgr PARAMFILE /goldengate/dirprm/mgr.prm REPORTFILE /goldengate/dirrpt/MGR.rpt PROCESSID MGR PORT 7809
oracle 7577 7362 0 02:59 pts/1 00:00:00 sh -c ps -ef|grep mgr
oracle 7579 7577 0 02:59 pts/1 00:00:00 grep mgr
5.1.2.添加表级transdata
GGSCI (cndba) 10> dblogin userid ogg,password ogg
Successfully logged into database.
查看是否开启
GGSCI (cndba) 11> info trandata test.test
Logging of supplemental redo log data is disabled for table TEST.TEST.
GGSCI (cndba) 11> add trandata test.*
2023-08-15 20:31:56 INFO OGG-15132 Logging of supplemental redo data enabled for table TEST.TEST.
2023-08-15 20:31:56 INFO OGG-15133 TRANDATA for scheduling columns has been added on table TEST.TEST.
2023-08-15 20:31:56 INFO OGG-15135 TRANDATA for instantiation CSN has been added on table TEST.TEST.
注意：为了方便这里直接添加整个用户下表级transdata，如果只是同步部分表的，请批量
执行add trandata test.tablename
5.1.3.配置extract抽取进程
GGSCI (cndba) 13> dblogin userid ogg,password ogg
Successfully logged into database.
GGSCI (cndba) 14> add extract ext1, tranlog, begin now, threads 2
EXTRACT added.
GGSCI (cndba) 15> add exttrail /goldengate/dirdat/ex, extract ext1
EXTTRAIL added.
GGSCI (cndba) 16> edit params ext1
GGSCI (orcl01 as ogg@orcl1) 7> view param ext1
EXTRACT ext1
SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8") --此处数据库字符集设为一致
USERID ogg,PASSWORD ogg
TRANLOGOPTIONS ASMUSER sys@+ASM, ASMPASSWORD oracle
THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 20000
exttrail /goldengate/dirdat/ex --抽取进程的跟踪文件
TABLE test.*;
---如下参数报错
GGSCI (cndba) 17> view params ext1
EXTRACT ext1
SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
USERID ogg,PASSWORD ogg
exttrail /goldengate/dirdat/ex
TABLE test.*; --table参数后面就是要复制投递的表，注意结束一定要用‘;’
--SETENV (ORACLE_SID = "orcl")
--SETENV (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db)
USERID ogg, PASSWORD ogg
TRANLOGOPTIONS ASMUSER sys@+ASM,ASMPASSWORD oracle
THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 20000
EXTTRAIL ./dirdat/ex
DYNAMICRESOLUTION
--DDL INCLUDE ALL
报错未知原因：
[oracle@orcl01:/goldengate]$ tail -200f /goldengate/ggserr.log
2023-08-16T10:21:58.367+0800 ERROR OGG-00303 Oracle GoldenGate Capture for Oracle, ext1.prm: Unable to connect to database using user ogg. Ensure that the necessary privileges are granted to the user.
Login to the database as user ogg failed because of error ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
Process ID: 0
Session ID: 0 Serial number: 0.
5.1.4.配置pump传输进程
GGSCI (cndba) 18> add extract PUMP1,exttrailsource ./dirdat/ex,begin now
EXTRACT added.
GGSCI (cndba) 19> add rmttrail ./dirdat/ex,extract PUMP1
RMTTRAIL added.
GGSCI (cndba) 20> edit params pump1
GGSCI (cndba) 21> view params pump1
EXTRACT pump1
userid ogg,password ogg
RMTHOST 192.168.40.52, MGRPORT 7809 --其中rmthost用来指定目标数据库的IP
RMTTRAIL ./dirdat/ex --rmttrail用来指定投递到目标端的trail文件
passthru
TABLE test.*; --table参数后面就是你要复制投递的表，注意结束了一定要用‘;’
GGSCI (cndba) 22> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXT1 00:00:00 00:18:00
EXTRACT STOPPED PUMP1 00:00:00 00:04:07
5.2.OGG目标端配置
5.2.1.添加GLOBALS参数文件，创新检查点表
GGSCI (cndba) 7> edit params ./GLOBALS
GGSCI (cndba) 8> view params ./GLOBALS
--添加以下内容：
GGSCHEMA ogg
checkpointtable ogg.checkpoint
GGSCI (cndba) 9> dblogin userid ogg,password ogg
Successfully logged into database.
GGSCI (cndba) 10> add checkpointtable ogg.checkpoint
Successfully created checkpoint table ogg.checkpoint.
5.2.2.配置mgr进程
GGSCI (cndba) 3> edit params mgr
GGSCI (cndba) 4> view params mgr
port 7809
GGSCI (cndba) 5> start mgr
Manager started.
GGSCI (cndba) 6> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
GGSCI (cndba) 7> sh netstat -ntpl |grep 7809 --查看7809端口是否启用
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
tcp 0 0 :::7809 :::* LISTEN 14176/./mgr
GGSCI (cndba) 8> sh ps -ef|grep mgr --查看mgr进程是否存在
root 14 2 0 13:24 ? 00:00:00 [async/mgr]
postfix 1867 1860 0 13:26 ? 00:00:00 qmgr -l -t fifo -u
oracle 14176 14114 0 15:43 ? 00:00:00 ./mgr PARAMFILE /u01/app/oracle/ogg/dirprm/mgr.p
oracle 14185 14114 0 15:44 pts/0 00:00:00 sh -c ps -ef|grep mgr
oracle 14187 14185 0 15:44 pts/0 00:00:00 grep mgr
5.2.3.配置replicat复制进程
GGSCI (cndba) 11> add replicat rep1, exttrail /u01/app/oracle/ogg/dirdat/ex, checkpointtable ogg.checkpoint
REPLICAT added.
GGSCI (cndba) 12> edit params rep1
GGSCI (cndba) 13> view params rep1
REPLICAT rep1
setenv (ORACLE_SID=dhh)
SETENV (ORACLE_HOME = "/u01/app/oracle/product/11.2.0.4/dbhome_1")
USERID ogg,PASSWORD ogg
ASSUMETARGETDEFS ----使用ASSUMETARGETDEFS参数时，用MAP语句中指定的生产库源表和灾备端目标表具有相同的列结构。它指示的Oracle GoldenGate不在生产端查找源表的结构定义。
HANDLECOLLISIONS ----不要用！存在操作不一致性(详情https://blog.csdn.net/zhuxiaoliao/article/details/42234661)
DBOPTIONS ENABLE_INSTANTIATION_FILTERING --当DDL复制报错时，则需要用到此处的ddlerror参数预处理一些常见的报错信息。Ddlerror对于抽取、复制进程均有效，默认为abend。
REPERROR (DEFAULT, DISCARD)
DDLERROR DEFAULT IGNORE RETRYOP --DDL语句出错默认
DISCARDFILE /u01/app/oracle/ogg/dircrd/rep1_discard.txt,append,megabytes 10
--将执行失败的记录保存在discard file中，文件中已经包含记录的话，再后面继续追加，不删除之前的记录。
--如当DDL复制报ORA-1430错误，传递了重复的alter语句导致，则可以用ddlerror (1430, discard)将错误信息扔到discard文件里。
DISCARDROLLOVER AT 02:00
--为了防止discard file被写满，每天2：00做一次文件过期设定
MAP test.*, TARGET test.*;
6.初始化数据-EXPDP
6.1.启动生产端和容灾端的管理进程
--源端
[oracle@cndba ogg]$ cd /goldengate
[oracle@cndba ogg]$ ./ggsci
GGSCI (cndba) 1> start mgr
Manager started.
GGSCI (cndba) 1> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXT1 00:00:00 00:40:35
EXTRACT STOPPED PUMP1 00:00:00 00:26:42
--目标端
[oracle@host1 ~]$ cd /u01/app/oracle/ogg
[oracle@host1 ogg]$ ./ggsci
GGSCI (host1) 1> start mgr
Manager started.
GGSCI (cndba) 14> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT STOPPED REP1 00:00:00 00:11:22
6.2.启动源端的抽取进程和投递进程
GGSCI (cndba) 2> start ext1
Sending START request to MANAGER ...
EXTRACT EXT1 starting
GGSCI (cndba) 4> start pump1
Sending START request to MANAGER ...
EXTRACT PUMP1 starting
GGSCI (cndba) 5> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:00 00:00:03
EXTRACT RUNNING PUMP1 00:00:00 00:28:57
6.3.查看数据库中所有事务的开始时间
查看数据库中所有事务的开始时间，直到其大于抽取进程的启动时间再开始备份数
据库，因为GoldenGate 的只获取在Extract 启动以后的交易变化，在Extract 启动之
前开始而在Extract 启动以后才完成的交易GoldenGate 将会忽略这些交易，这些被忽
略的交易数据就会丢失。所以需要等数据库中所以的交易都在Extract 启动之后开始
的才能开始备份数据库。通过vtransaction 视图来查看数据库中的交易：
SQL> select * from vtransaction;
no rows selected
这里是测试环境没有事物，可以进行后面的备份了。
6.4.EXPDP 备份源端数据库
当所有在Extract 启动之前的开始的交易都完成后，我们就可以使用expdp 备份生产
端的数据库了。备份数据库的过程中一定要密切监控Extract 进程的状态，保证其一
直正常运行：
6.4.1.源端查询当前数据库SCN
[root@cndba ~]# mkdir /backup
[root@cndba ~]# chown -R oracle:oinstall /backup
SQL> create or replace directory dump_dir as '/backup';
grant read,write on directory dump_dir to system;
Directory created.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
8222211
或者
SQL> select to_char(dbms_flashback.get_system_change_number) from dual;
TO_CHAR(DBMS_FLASHBACK.GET_SYSTEM_CHANGE
----------------------------------------
8222211
6.4.2.备份源端数据库
[oracle@www.cndba.cn ~]$ expdp system/oracle directory=dump_dir dumpfile=sender1_%U.dmp logfile=user.log schemas=test parallel=2 flashback_scn= 1151457 cluster=N
…..
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/backup/sender_01.dmp
/backup/sender_02.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Jun 6 16:46:26 2018 elapsed 0 00:00:36
6.4.3.将备份文件传到目标端
目标端创建备份目录
[root@cndba ~]# mkdir -p /home/oracle/backup
源端数据库传输到目标端
[oracle@cndba backup]$ scp sender3.dmp oracle@192.168.40.52:/home/oracle/backup
在目标端给备份文件授权
[root@cndba ~]# chown -R oracle:oinstall /home/oracle/backup
6.5.IMPDP 初始化数据
6.5.1.Impdp 数据
SQL> create or replace directory dump_dir as '/home/oracle/backup';
Directory created.
[oracle@cndba dirprm]$ impdp system/dhh directory=dump_dir dumpfile= sender1_%U.dmp logfile=user.log parallel=2 table_exists_action=replace SCHEMAS=test
……
. . imported "TEST"."TEST" 5.460 KB 2 rows
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at Wed Jun 6 16:56:10 2018 elapsed 0 00:00:05
6.5.2.用SCN 启动Replicat
[oracle@cndba ogg]$ cd $OGG_HOME
[oracle@cndba ogg]$ ./ggsci
GGSCI (cndba) 1> start rep1, aftercsn 8222211
Sending START request to MANAGER ...
REPLICAT REP1 starting
GGSCI (cndba) 2> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP1 00:00:00 00:00:02
7.检查同步是否正常
7.1.DML测试
--检查目标端数据是否正常
GGSCI (cndba) 4> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP1 00:00:00 00:00:06
SQL> select * from test.test;
ID NAME
---------- --------
1 zhangsan
2 lisi
--源端表中添加数据
insert into test.test values(3,'wanger');
commit;
select * from test.test;
ID NAME
---------- --------
1 zhangsan
2 lisi
3 wanger
--目标端查看
SQL> select * from test;
ID NAME
---------- --------
1 zhangsan
2 lisi
3 wanger
可以看到可以同步过来的。
8.开启DDL
8.1.添加参数
5.1和5.2已配置，可跳过
--源端
GGSCI (cndba) 8> edit params ./GLOBALS
GGSCI (cndba) 9> view params ./GLOBALS
GGSCHEMA ogg
--目标端
GGSCI (11g-db) 8> edit params ./GLOBALS
GGSCI (11g-db) 9> view params ./GLOBALS
GGSCHEMA ogg
checkpointtable ogg.checkpoint
8.2.在源端和目标端执行与DDL同步相关的SQL脚本
经测试目标端不执行DDL同步相关的SQL脚本也能同步DDL数据
切记@marker_setup.sql 一定要在cd $OGG_HOME目录下执行否则会卡主。执行脚本时要输入ogg管理用户，
本实例是ogg。
cd /goldengate
sqlplus / as sysdba
grant execute on utl_file to ogg;
@marker_setup.sql
@ddl_setup.sql
@role_setup.sql
grant GGS_GGSUSER_ROLE to ogg;
@ddl_enable.sql
@ddl_pin ogg
8.3.源端extract 配置
GGSCI (cndba) 12> edit params ext1
GGSCI (cndba) 16> view params ext1
EXTRACT ext1
SETENV (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")
USERID ogg, PASSWORD ogg
TRANLOGOPTIONS ASMUSER sys@+ASM,ASMPASSWORD oracle
THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 20000
EXTTRAIL ./dirdat/ex
DDL INCLUDE ALL
TABLE test.*;
重启extract进程
GGSCI (cndba) 13> stop ext1
Sending STOP request to EXTRACT EXT1 ...
Request processed.
GGSCI (cndba) 14> start ext1
Sending START request to MANAGER ...
EXTRACT EXT1 starting
GGSCI (orcl01 as ogg@orcl1) 64> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:00 00:00:07
EXTRACT RUNNING PUMP1 00:00:00 00:00:01
8.4.目标端replicat 配置
GGSCI (cndba) 8> edit params rep1
GGSCI (cndba) 9> view params rep1
REPLICAT rep1
setenv (ORACLE_SID=dhh)
SETENV (ORACLE_HOME = "/u01/app/oracle/product/11.2.0.4/dbhome_1")
USERID ogg,PASSWORD ogg
ASSUMETARGETDEFS
HANDLECOLLISIONS
REPERROR (DEFAULT, DISCARD)
DDLERROR DEFAULT IGNORE RETRYOP
DISCARDROLLOVER AT 02:00
DBOPTIONS ENABLE_INSTANTIATION_FILTERING
DISCARDFILE /u01/app/oracle/ogg/dircrd/rep1_discard.txt,append,megabytes 10
MAP test.*, TARGET test.*;
ddl include all
ddlerror default ignore retryop maxretries 3 retrydelay 5
重启replicat进程
GGSCI (cndba) 10> stop rep1
Sending STOP request to REPLICAT REP1 ...
Request processed.
GGSCI (cndba) 11> start rep1
Sending START request to MANAGER ...
REPLICAT REP1 starting
8.5.DDL测试
--源端：
create table test2(id number(10) primary key ,name varchar(8));
insert into test2 values(1,'zhangsan');
commit;
--目标端：
SQL> desc test2
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(10)
NAME VARCHAR2(8)
SQL> select * from test2
ID NAME
---------- --------
1 zhangsan
问题
1.OGG-00446 OGG-02829Not able to establish initial position for SCN
ORACLE RAC 配置OGG时，启动rac 的extract 进程时日志提示如下：
GGSCI (orcl01 as ogg@orcl1) 200> start ext1
Sending START request to MANAGER ...
EXTRACT EXT1 starting
GGSCI (orcl01 as ogg@orcl1) 201> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:00 00:00:09
EXTRACT STOPPED PUMP1 00:00:00 00:00:08
tail -200f /goldengate/ggserr.log
2023-08-17T08:08:09.107+0800 ERROR OGG-00446 Oracle GoldenGate Capture for Oracle, ext1.prm: ���U.
2023-08-17T08:08:09.109+0800 ERROR OGG-02829 Oracle GoldenGate Capture for Oracle, ext1.prm: Not able to establish initial position for SCN 0.1238353 (1238353), No ing redo file name for sequence 16, archived = 0, use_alternate = 0.
造成该错误的原因是因为RAC的共享存储采用了ASM（自动存储管理），而OGG的抽取进程无法连接到ASM，故而无法抓取到redo log。
具体解决办法如下：
1.在tnsnames.ora 文件中添加以下内容：
目的是为了extract进程能连到正确的ASM实例，host 每个节不一样
+ASM =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = +ASM)
)
)
2.测试能否连接到正确的实例
[oracle@rac2 admin]$ sqlplus sys/oracle@+ASM as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Oct 26 03:33:47 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options
SQL> set line 200
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
------------------------------------------------ ------------------------------------
+ASM2 STARTED
3.修改extract进程的配置
增加一行：
TRANLOGOPTIONS ASMUSER sys@+ASM,ASMPASSWORD oracle
以上步骤正确的话，启动extract 进程，数据正常同步。
GGSCI (rac2 as ogg@orcl2) 38> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DP1 00:00:00 00:00:05
EXTRACT RUNNING EXT1 00:00:00 00:00:02
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
