CentOS6.8 Oracle 12.2.0.1 RAC到CentOS7.6 Oracle 19c RAC单向同步
/* OGG数据迁总体规划参考文档 */
https://cloud.tencent.com/developer/news/694510
一、创建共享目录
RAC环境下，OGG安装目录需要处于全部节点的共享目录下，可以使用ACFS或NFS。ACFS用于挂载一个共享目录(用于安装OGG软件)到2个节点，ACFS可以使用已经存在的磁盘组，也可以单独创建磁盘组。
**1、源端创建共享目录(NFS)**
-- 这里源端OS版本为CentOS6.8不支持数据库版本12.2.0.1的情况下安装ACFS，使用NFS创建共享目录
[root@lissen-n1 ~]# acfsdriverstate supported
ACFS-9459: ADVM/ACFS is not supported on this OS version: 'centos-release-6-8.el6.centos.12.3.x86_64
'
ACFS-9201: Not Supported
-- 一个做服务端，其他节点做客户端
一、服务端安装NFS相关依赖包
-- 查找相关依赖包，没有就安装
rpm -qa | grep nfs
rpm -qa | grep rpcbind
-- yum -y install nfs-utils rpcbind
chkconfig nfs on
chkconfig rpcbind on
-- 6.x
service nfs start
service nfs status
service rpcbind start
service rpcbind status
-- 7.x
systemctl start rpcbind
systemctl start nfs
**源端所有节点创建共享目录**
-- 服务端/客户端(源端所有节点)
mkdir -p /u01/app/ogg_src
chown -R oracle.oinstall /u01/app/ogg_src
chmod -R 755 /u01/app/ogg_src
**配置NFS**
echo "/u01/app/ogg_src 192.168.59.12(rw,no_root_squash,async)" >> /etc/exports
echo "/u01/app/ogg_src *(insecure,rw,async,no_root_squash)" >> /etc/exports
-- 立即生效
exportfs -a
-- 服务端显示共享的目录
[oracle@lissen-n1 ogg_src]$ showmount -e
Export list for lissen-n1:
/u01/app/ogg_src (everyone)
**客户端连接服务端**
mount -t nfs 192.168.59.12:/u01/app/ogg_src /u01/app/ogg_src
-- 报错
[root@lissen-n2 ~]# mount -t nfs 192.168.59.12:/u01/app/ogg_src /u01/app/ogg_src
mount.nfs: access denied by server while mounting 192.168.59.12:/u01/app/ogg_src
-- 服务端/etc/exports添加
echo "/u01/app/ogg_src *(insecure,rw,async,no_root_squash)" >> /etc/exports
-- 永久挂载
echo "/bin/mount -t nfs 192.168.59.12:/u01/app/ogg_src /u01/app/ogg_src" >> /etc/rc.local
-- 7.x使用上面办法不能实现自动挂载，写入到/etc/fstab
echo "192.168.59.4:/u01/app/ogg_tgt /u01/app/ogg_tgt nfs defaults 0 0" >> /etc/fstab
[root@lissen-n2 app]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/vg--root-lv_root
59G 6.9G 50G 13% /
tmpfs 8.0G 72K 8.0G 1% /dev/shm
/dev/sda1 976M 39M 886M 5% /boot
/dev/mapper/vg--root-lv_home
9.8G 23M 9.2G 1% /home
/dev/mapper/vg--root-lv_tmp
9.8G 33M 9.2G 1% /tmp
/dev/mapper/vg--oracle--soft-lv_u01
178G 30G 139G 18% /u01
192.168.59.12:/u01/app/ogg_src
178G 31G 138G 18% /u01/app/ogg_src
**2、目标端创建共享目录(ACFS)**
...同上
二、 安装OGG软件
**1、 OGG系统要求**
一个 OGG 实例最多支持 300 个并发 extract 和 replicat 进程，每个进程需要 25-55M 内存。
GGSCI，view report，找到 PROCESS VM AVAIL FROM OS，大约就是系统所需 要的 swap space 大小
磁盘空间每个实例 50-150M，外加 trail 等等，至少给 1G。trail 所需空间 的预估方法是：[log volume in one hour] x [number of hours downtime] * 0.4
/tmp 空间，默认是 dirtmp 目录，可以通过 CACHEMGR 参数的 CACHEDIRECTORY 选项指定
安装 OGG 的用户必须属于 ORACLE 数据库实例的组，通常就是 dba。
要想支持RAC里的RMAN log retention特性, 得在add extract之前安装patch BUGFIX 11879974
**2、OGG相关文档**
-- OGG各版本对数据的支持文档
https://www.oracle.com/middleware/technologies/fusion-certification.html
Oracle GoldenGate 19.1.0.0.4+ Linux on System Z Red Hat Enterprise Linux 7 Update Level 0+ 64 Oracle Database 11.2.0.4+; 12.1.0.2+; 12.2.0.1+; 18c; 19c "Supports Local and Remote, Capture and Delivery for Standard and Enterprise editions
Supports Remote, non-Integrated Replicat Delivery to Oracle Autonomous Transaction Processing and Data Warehousing Cloud"
-- Oracle文档资源
http://docs.oracle.com
-- OGG官方文档
https://docs.oracle.com/en/middleware/goldengate/core/19.1/installing/installing-classic-architecture.html
-- acfs上安装ogg，acfs用来存放ogg的安装目录，便于集群件的共享，在10g上部署可以选择ocfs2或者使用NFS
**3、配置ORACLE OGG相关环境变量**
-- 各个OS的库环境变量
节点1：
[oracle@lissen-n1 ~]$ cat ogg_src
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/.local/bin:$HOME/bin
export PATH
umask 022
#export TMP=/tmp
#export TMPDIR=$TMP
export OGG_HOME=/u01/app/ogg_src
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/19.3.0.0/dbhome_1
export ORACLE_SID=gggsrc1
export PATH=$OGG_HOME:$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$OGG_HOME:$ORACLE_HOME/lib:/lib:/usr/lib:$LD_LIBRARY_PATH
export NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
alias sqlplus='rlwrap sqlplus'
alias rman='rlwrap rman'
alias ggsci='rlwrap ggsci'
#export TMPDIR=/home/oracle/xtts
#export OGG_HOME=/u01/app/ogg_src
[oracle@lissen-n1 ~]$ cat ogg_dest
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/.local/bin:$HOME/bin
export PATH
umask 022
#export TMP=/tmp
#export TMPDIR=$TMP
export OGG_HOME=/u01/app/ogg_dest
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/19.3.0.0/dbhome_2
export ORACLE_SID=gggdest1
export PATH=$OGG_HOME:$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$OGG_HOME:$ORACLE_HOME/lib:/lib:/usr/lib:$LD_LIBRARY_PATH
export NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
alias sqlplus='rlwrap sqlplus'
alias rman='rlwrap rman'
alias ggsci='rlwrap ggsci'
#export TMPDIR=/home/oracle/xtts
#export OGG_HOME=/u01/app/ogg_dest
节点2：
[oracle@lissen-n2 ~]$ cat ogg_src
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/.local/bin:$HOME/bin
export PATH
umask 022
#export TMP=/tmp
#export TMPDIR=$TMP
export OGG_HOME=/u01/app/ogg_src
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/19.3.0.0/dbhome_1
export ORACLE_SID=gggsrc2
export PATH=$OGG_HOME:$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$OGG_HOME:$ORACLE_HOME/lib:/lib:/usr/lib:$LD_LIBRARY_PATH
export NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
alias sqlplus='rlwrap sqlplus'
alias rman='rlwrap rman'
alias ggsci='rlwrap ggsci'
#export TMPDIR=/home/oracle/xtts
#export OGG_HOME=/u01/app/ogg_src
[oracle@lissen-n2 ~]$ cat ogg_dest
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/.local/bin:$HOME/bin
export PATH
umask 022
#export TMP=/tmp
#export TMPDIR=$TMP
export OGG_HOME=/u01/app/ogg_dest
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/19.3.0.0/dbhome_2
export ORACLE_SID=gggdest2
export PATH=$OGG_HOME:$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$OGG_HOME:$ORACLE_HOME/lib:/lib:/usr/lib:$LD_LIBRARY_PATH
export NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
alias sqlplus='rlwrap sqlplus'
alias rman='rlwrap rman'
alias ggsci='rlwrap ggsci'
#export TMPDIR=/home/oracle/xtts
#export OGG_HOME=/u01/app/ogg_dest
**4、 上传解压OGG软件**
unzip 191004_fbo_ggs_Linux_x64_shiphome.zip
chown -R oracle.oinstall fbo_ggs_Linux_x64_shiphome
[root@lissen-n1 soft]# ls -ld fbo_ggs_Linux_x64_shiphome
drwxr-xr-x 3 oracle oinstall 4096 Oct 18 2019 fbo_ggs_Linux_x64_shiphome
**5、源端静默安装(OGG推荐)**
同一台机子上安装OGG要指定不同的ORACLE_HOME(2个ORACLE SOFTWARE)，默认管理端口(如果一台机子安装多个OGG则需要另外指定了)，一台机子安装2个OGG需要重新执行管理端口并安装第2个数据库软件。
-- 默认端口为MANAGER_PORT=7809
cat > /home/oracle/oggcore.rsp <<EOF
oracle.install.responseFileVersion=/oracle/install/rspfmt_ogginstall_response_schema_v19_1_0
INSTALL_OPTION=ORA12c
SOFTWARE_LOCATION=/u01/app/ogg_src
START_MANAGER=false
MANAGER_PORT=7809
DATABASE_LOCATION=/u01/app/oracle/product/12.2.0.1/dbhome_1
INVENTORY_LOCATION=/u01/app/oraInventory
UNIX_GROUP_NAME=oinstall
EOF
./runInstaller -silent \
-showProgress \
-responseFile /home/oracle/oggcore.rsp
-- 创建子目录
cd $OGG_HOME
ggsci
create subdirs
**6、 目标端安装OGG**
cat > /home/oracle/oggcore.rsp <<EOF
oracle.install.responseFileVersion=/oracle/install/rspfmt_ogginstall_response_schema_v19_1_0
INSTALL_OPTION=ORA19c
SOFTWARE_LOCATION=/u01/app/ogg_tgt
START_MANAGER=false
MANAGER_PORT=7809
DATABASE_LOCATION=/u01/app/oracle/product/19.3.0.0/dbhome_1
INVENTORY_LOCATION=/u01/app/oraInventory
EOF
./runInstaller -silent \
-showProgress \
-responseFile /home/oracle/oggcore.rsp
cd $OGG_HOME
ggsci
create subdirs
三、 配置单向同步
**1、源端配置DB实例、ASM实例监听和TNS**
**配置DB实例、ASM监听**
-- S:
-- grid用户
[grid@lissen-n1 ~]$ lsnrctl status
LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 15-JAN-2022 21:09:04
Copyright (c) 1991, 2016, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date 15-JAN-2022 20:56:11
Uptime 0 days 0 hr. 12 min. 53 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/12.2.0.1/grid/network/admin/listener.ora
Listener Log File /u01/app/grid/diag/tnslsnr/lissen-n1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.59.12)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.59.14)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_DATA" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_FRA" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_OCR" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "ggg" has 1 instance(s).
Instance "ggg1", status READY, has 1 handler(s) for this service...
Service "gggXDB" has 1 instance(s).
Instance "ggg1", status READY, has 1 handler(s) for this service...
The command completed successfully
-- 节点1：
LISTENER =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL=TCP)(HOST=192.168.59.12)(PORT=1521))
)
)
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=+ASM)
(ORACLE_HOME=/u01/app/12.2.0.1/grid)
(SID_NAME=+ASM1)
)
)
-- 节点2：
LISTENER =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL=TCP)(HOST=192.168.59.13)(PORT=1521))
)
)
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=+ASM)
(ORACLE_HOME=/u01/app/12.2.0.1/grid)
(SID_NAME=+ASM2)
)
)
-- 配置ASM动态注册
SYS@+ASM1>alter system set local_listener = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.59.12)(PORT=1521)))' sid = '+ASM1';
System altered.
SYS@+ASM1>alter system set local_listener = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.59.13)(PORT=1521)))' sid = '+ASM2';
System altered.
-- 配置DB INSTANCE动态注册
SYS@ggg1>alter system set local_listener = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.59.12)(PORT=1521)))' sid = 'ggg1';
System altered.
SYS@ggg1>alter system set local_listener = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.59.13)(PORT=1521)))' sid = 'ggg2';
System altered.
-- 重启监听
srvctl status listener
srvctl stop listener
srvctl start listener
-- 检查是否配置成功
[oracle@lissen-n1 ~]$ sqlplus sys/oracle@192.168.59.12:1521/+ASM as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Sat Jan 15 22:20:06 2022
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SYS@192.168.59.12:1521/+ASM>exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@lissen-n1 ~]$ sqlplus sys/oracle@192.168.59.13:1521/+ASM as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Sat Jan 15 22:20:19 2022
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SYS@192.168.59.13:1521/+ASM>
[oracle@lissen-n1 ~]$ sqlplus sys/oracle@192.168.59.12:1521/ggg as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Sat Jan 15 22:23:43 2022
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SYS@192.168.59.12:1521/ggg>conn sys/oracle@192.168.59.13:1521/ggg as sysdba
Connected.
SYS@192.168.59.13:1521/ggg>
-- T:
-- 节点1
LISTENER =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL=TCP)(HOST=192.168.59.4)(PORT=1521))
)
)
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=+ASM)
(ORACLE_HOME=/u01/app/19.3.0.0/grid)
(SID_NAME=+ASM1)
)
)
-- 节点2
LISTENER =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL=TCP)(HOST=192.168.59.5)(PORT=1521))
)
)
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=+ASM)
(ORACLE_HOME=/u01/app/19.3.0.0/grid)
(SID_NAME=+ASM2)
)
)
alter system set local_listener = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.59.4)(PORT=1521)))' sid = '+ASM1';
alter system set local_listener = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.59.5)(PORT=1521)))' sid = '+ASM2';
alter system set local_listener = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.59.4)(PORT=1521)))' sid = 'ggg1';
alter system set local_listener = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.59.5)(PORT=1521)))' sid = 'ggg2';
[oracle@lissen-n1 ~]$ sqlplus sys/oracle@192.168.59.4:1521/ggg as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 16 15:08:24 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SYS@192.168.59.4:1521/ggg>
SYS@192.168.59.4:1521/ggg>conn sys/oracle@192.168.59.5:1521/ggg as sysdba
Connected.
SYS@192.168.59.5:1521/ggg>conn sys/oracle@192.168.59.4:1521/+ASM as sysdba
Connected.
SYS@192.168.59.4:1521/+ASM>
SYS@192.168.59.4:1521/+ASM>
SYS@192.168.59.4:1521/+ASM>conn sys/oracle@192.168.59.5:1521/+ASM as sysdba
Connected.
**配置TNS**
-- S:
-- 全部节点
-- oracle用户
tns_ggg_asm=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.59.12)(PORT=1521))
(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.59.13)(PORT=1521))
)
(CONNECT_DATA=
(SERVER=DEDICATED)
(SERVICE_NAME=+ASM)
)
)
tns_ggg=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.59.12)(PORT=1521))
(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.59.13)(PORT=1521))
)
(CONNECT_DATA=
(SERVER=DEDICATED)
(SERVICE_NAME=ggg)
)
)
[oracle@lissen-n2 admin]$ sqlplus sys/oracle@tns_ggg as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Sat Jan 15 22:26:42 2022
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SYS@tns_ggg>exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@lissen-n2 admin]$ sqlplus sys/oracle@tns_ggg_asm as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Sat Jan 15 22:26:52 2022
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SYS@tns_ggg_asm>
-- T:
tns_ggg_target_asm=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.59.4)(PORT=1521))
(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.59.5)(PORT=1521))
)
(CONNECT_DATA=
(SERVER=DEDICATED)
(SERVICE_NAME=+ASM)
)
)
tns_pdb01=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.59.4)(PORT=1521))
(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.59.5)(PORT=1521))
)
(CONNECT_DATA=
(SERVER=DEDICATED)
(SERVICE_NAME=pdb01)
)
)
[oracle@lissen-n1 admin]$ sqlplus sys/oracle@tns_pdb01 as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 16 15:13:13 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SYS@tns_pdb01>conn sys/oracle@tns_ggg_target_asm as sysdba
Connected.
**2、 源端创建用于同步的SCHEMA及相关测试对象**
-- 用于测试的SCHEMA和表空间
ogg_test
ogg_test_tbs
-- 创建表空间、用户、授权
create tablespace ogg_test_tbs datafile '+DATA' size 10M autoextend on maxsize unlimited extent management local segment space management auto;
create user ogg_test identified by ogg_test default tablespace ogg_test_tbs;
alter user ogg_test quota unlimited on ogg_test_tbs;
grant connect,resource to ogg_test;
grant select any dictionary to ogg_test;
grant select any table to ogg_test;
grant create view to ogg_test;
-- 创建相关测试对象
conn ogg_test/ogg_test;
create table t1 as select * from scott.emp;
create table t2 as select * from scott.dept;
create table t3 as select * from dba_objects;
create index idx_t1_sal on t1(sal);
create or replace view v_sel_t1_empno_ename_sal as select empno,ename,sal from t1;
-- 查询OGG_TEST下的对象
OGG_TEST@ggg>select object_name,object_type from dba_objects where owner = 'OGG_TEST';
OBJECT_NAME OBJECT_TYPE
----------------------------------- -------------------
T1 TABLE
T2 TABLE
T3 TABLE
IDX_T1_SAL INDEX
V_SEL_T1_EMPNO_ENAME_SAL VIEW
**3、 源数据库上进行配置，创建用户，授权，运行执行序列号和ddl复制的相关脚本等**
**源库开启补充日志、开启归档、开启强日志写**
-- 开启补充日志
alter database add supplemental log data;
alter database add supplemental log data (primary key) columns;
alter database add supplemental log data (foreign key) columns;
alter database add supplemental log data (unique) columns;
-- 开启强日志写
SYS@tns_ggg>alter database force logging;
Database altered.
SYS@tns_ggg>select force_logging from v$database;
FOR
---
YES
-- 查看是否开启归档，没有就开启
SYS@tns_ggg>archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +FRA
Oldest online log sequence 13
Next log sequence to archive 14
Current log sequence 14
**创建用户复制的OGG管理账户、默认表空间，授权**
-- ogg_admin用户需要单独划分表空间
create tablespace ogg_admin_tbs datafile '+DATA' size 100M autoextend on maxsize unlimited extent management local;
create temporary tablespace ogg_admin_temp_tbs tempfile '+DATA' size 100M autoextend on maxsize unlimited extent management local;
create user ogg_admin identified by ogg_admin default tablespace ogg_admin_tbs temporary tablespace ogg_admin_temp_tbs;
alter user ogg_admin quota unlimited on ogg_admin_tbs;
-- grant unlimited tablespace to ogg_admin;
-- 开启OGG复制参数，不开启复制的时候会报错
alter system set enable_goldengate_replication = true sid = '*';
**运行执行序列号、DDL复制相关脚本**
@sequence.sql
alter system archive log current;
grant execute on utl_file to ogg_admin;
@marker_setup.sql
@ddl_setup.sql
@role_setup.sql
grant ggs_ggsuser_role to ogg_admin;
@ddl_enable.sql
@ddl_pin ogg_admin
-- 禁用DDL
-- @ddl_disable.sql
**4、配置MGR参数**
-- S/T
-- 配置查看MGR参数
view params mgr
info mgr
start mgr
stop mgr
-- 配置MGR参数
-- 默认端口7809
PORT 7809
-- PORT 7809失效时，自动使用7810-7849端口
DYNAMICPORTLIST 7810-7849
-- 自动启动EXTRACT、REPLICAT进程
AUTORESTART REPLICAT *, RETRIES 3, WAITMINUTES 2, RESETMINUTES 30
AUTORESTART EXTRACT *, RETRIES 3, WAITMINUTES 2, RESETMINUTES 30
--AUTORESTART ER *, RETRIES 3, WAITMINUTES 10, RESETMINUTES 60
--PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, MINKEEPDAYS 1
--PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS,MINKEEPHOURS 8
-- 配置自动文件删除策略
PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, minkeepfiles 100
-- 记录进程关闭时的间隔时间
DOWNREPORTMINUTES 15
--DOWNCRITICAL
-- 定义数据延迟的预警机制
LAGCRITICALSECONDS 10
LAGINFOMINUTES 0
LAGREPORTMINUTES 15
LAGREPORTHOURS 1
--LAGINFOMINUTES 30
LAGCRITICALMINUTES 45
-- 设置IP访问策略
ACCESSRULE, PROG *, IPADDR 192.168.*.*, ALLOW
-- ACCESSRULE, PROG *, IPADDR *, DENY
-- 启动MGR
start mgr
-- 拒绝访问告警
2021-09-06T11:59:15.809+0800 WARNING OGG-00936 Oracle GoldenGate Manager for Oracle, mgr.prm: Access denied (request from 127.0.0.1, rule #2).
-- 注释掉
-- ACCESSRULE, PROG *, IPADDR *, DENY
-- OGG日志
tail -f $OGG_HOME/ggserr.log
**5、 源端配置extract group**
-- 帮助信息
help add extract
-- 创建抽取进程，threads代表进程数，单实例为1
add extract et_ot,tranlog begin now,threads 2
-- 配置抽取文件
add exttrail ./dirdat/ea,extract et_ot
edit params et_ot
view params et_ot
-- 配置extract参数
-- 抽取进程名称
extract et_ot
-- 设置环境变量
setenv (NLS_LANG = "AMERICAN_AMERICA.ZHS16GBK")
setenv (ORACLE_HOME = "/u01/app/oracle/product/12.2.0.1/dbhome_1")
-- 设置登录源库用户、TNS和密码
userid ogg_admin@tns_ggg, password ogg_admin
-- ASM环境才会用到该参数
tranlogoptions asmuser sys@tns_ggg_asm,asmpassword oracle
-- 设置传输延时
threadoptions maxcommitpropagationdelay 20000
-- 设置抽取文件
-- .代表$OGG_HOME
exttrail ./dirdat/ea
-- 动态解析
dynamicresolution
-- DDL复制
ddl include all
-- gettruncates
-- 复制表
table ogg_test.*;
-- 是否同步sequence
-- sequence ogg_test.*;
**6、源端配置data pump group**
/*
添加数据泵进程组
数据泵进程对应抽取文件
数据泵进程将抽取文件传输到目标端并设置远程追踪文件，复制进程会用到该追踪文件
数据泵进程是一种特殊的抽取进程
*/
add extract dmp_ot,exttrailsource ./dirdat/ea,begin now
-- 添加数据泵进程对应远程追踪文件
-- 该追踪文件和目标段复制追踪文件一致
add rmttrail ./dirdat/ra,extract dmp_ot
extract dmp_ot
rmthost 192.168.59.4,mgrport 7809
rmttrail ./dirdat/ra
passthru
-- gettruncates
ddl include all
table ogg_test.*;
-- sequence ogg_test.*
**7、目标端创建用户复制的OGG管理账户、默认表空间，授权**
-- ogg_admin用户需要单独划分表空间
create tablespace ogg_admin_tbs datafile '+DATA' size 100M autoextend on maxsize unlimited extent management local;
create temporary tablespace ogg_admin_temp_tbs tempfile '+DATA' size 100M autoextend on maxsize unlimited extent management local;
create user ogg_admin identified by ogg_admin default tablespace ogg_admin_tbs temporary tablespace ogg_admin_temp_tbs;
alter user ogg_admin quota unlimited on ogg_admin_tbs;
grant dba to ogg_admin;
-- grant unlimited tablespace to ogg_admin;
-- 开启OGG复制参数，不开启复制的时候会报错，CDB环境需要在CDB中设置
alter system set enable_goldengate_replication = true sid = '*';
**8、目标端配置replicat group**
-- GGSCI中执行SHELL命令
GGSCI (lissen-n1 as ogg_admin@ggg1/PDB01) 9> sh echo $ORACLE_HOME
/u01/app/oracle/product/19.3.0.0/dbhome_1
-- 登录TargetDB
dblogin,userid ogg_admin@tns_pdb01,password ogg_admin
-- 创建检查点表
add checkpointtable ogg_admin.chkpt_tab
-- 添加复制进程组
-- ./dirdat/ra对应源端DMP进程参数配置
add replicat rep_ot,exttrail ./dirdat/ra,begin now checkpointtable ogg_admin.chkpt_tab
edit params rep_ot
-- replicat进程名称
replicat rep_ot
-- 设置环境变量ORACLE_HOME
setenv (ORACLE_HOME="/u01/app/oracle/product/19.3.0.0/dbhome_1")
-- 设置语言
setenv (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")
-- 设置用于复制的账号密码、TNS
userid ogg_admin@tns_pdb01,password ogg_admin
-- ASSUMETARGETDEFS is ignored in OGG 12.2
-- 源端和目标端数据库类型一致，无需生成数据定义文件
assumetargetdefs
-- 目标端存在数据时忽略
-- 只在初始化时使用，初始化完成后建议关闭该参数
handlecollisions
-- 复制错误
reperror (default,discard)
-- DDL错误报告
ddlerror default discard
-- DDL操作添加到报告
ddloptions report
-- DDL操作只包含MAPPED的对象
ddl include mapped
-- 定义discardfile文件位置，如果处理中有记录出错会写入到此文件中
discardfile ./repsz.dsc,append,megabytes 1024
-- 映射源端和目标端对象
map ogg_test.*,target ogg_test.*;
四、 初始化数据
```sql
```sql
-- 可以使用 expdp/impdp、rman、OGG 全量等方法进行全量数据的迁移。
-- 这里使用DATA PUMP方式来初始化数据
**源端导出前检查**
1、查询ogg_test下所有对象，默认表空间，如果目标端没有ogg_test的默认表空间就创建
-- 可以查看VIEW
col object_name format a25;
select object_name,
object_type
from dba_objects
where owner = 'OGG_TEST';
OBJECT_NAME OBJECT_TYPE
------------------------------ -------------------
T1 TABLE
T2 TABLE
T3 TABLE
IDX_T1_SAL INDEX
V_SEL_T1_EMPNO_ENAME_SAL VIEW
-- 查询OGG_TEST下对象的对应的表空间，如果有多个则都需要在目标端创建
col tablespace_name format a30
select owner,
segment_name,
segment_type,
tablespace_name
from dba_segments
where owner = 'OGG_TEST';
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME
---------- --------------- ------------------ ------------------------------
OGG_TEST IDX_T1_SAL INDEX OGG_TEST_TBS
OGG_TEST T3 TABLE OGG_TEST_TBS
OGG_TEST T2 TABLE OGG_TEST_TBS
OGG_TEST T1 TABLE OGG_TEST_TBS
select username,
default_tablespace
from dba_users
where username ='OGG_TEST';
USERNAME DEFAULT_TABLESP
--------------------- ---------------
OGG_TEST OGG_TEST_TBS
2、查询该表空间的信息
-- 查看表空间创建语句
set line 120;
set long 49999;
set pagesize 49999;
select dbms_metadata.get_ddl(
object_type => 'TABLESPACE',
name => 'OGG_TEST_TBS') as TBS_META
from dual;
DBMS_METADATA.GET_DDL(OBJECT_TYPE=>'TABLESPACE',NAME=>'OGG_TEST_TBS')
--------------------------------------------------------------------------------
CREATE TABLESPACE "OGG_TEST_TBS" DATAFILE
'D:\APP\ADMINISTRATOR\ORADATA\GGG\OGG_TEST_TBS_A.DBF' SIZE 10485760
AUTOEXTEND ON NEXT 8192 MAXSIZE 32767M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO
ALTER DATABASE DATAFILE
'D:\APP\ADMINISTRATOR\ORADATA\GGG\OGG_TEST_TBS_A.DBF' RESIZE 12582912
-- 查看表空间大小
select tablespace_name,
sum(bytes) / 1024 / 1024 as sum_in_mb
from dba_data_files
where tablespace_name = 'OGG_TEST_TBS'
group by tablespace_name;
TABLESPACE_NAME SUM_IN_MB
------------------------------ ----------
OGG_TEST_TBS 12
set line 121;
set pagesize 49999;
col tablespace_name format a15;
col used_percent format a12;
col used_gb format 99999;
col size_gb format 99999;
select a.tablespace_name,
round(a.tablespace_size * b.value / 1024 / 1024 / 1024, 2) TOTAL_GB,
round(a.used_space * b.value / 1024 / 1024 / 1024, 2) USED_GB,
round(a.tablespace_size * b.value / 1024 / 1024 / 1024, 2) -
round(a.used_space * b.value / 1024 / 1024 / 1024, 2) FREE_GB,
ROUND(A.used_percent, 2)|| '%' used_percent
from dba_tablespace_usage_metrics a, v$parameter b
where b.NAME = 'db_block_size'
and a.tablespace_name = 'OGG_TEST_TBS'
order by used_percent DESC;
TABLESPACE_NAME TOTAL_GB USED_GB FREE_GB USED_PERCENT
--------------- ---------- ------- ---------- ------------
OGG_TEST_TBS 32 0 31.99 .03%
3、目标端创建OGG_TEST_TBS表空间，提前创建，不然导入的时候会报错..
CREATE TABLESPACE "OGG_TEST_TBS" DATAFILE
'/u01/app/oracle/oradata/ggg/OGG_TEST_TBS_A.DBF' SIZE 10485760
AUTOEXTEND ON NEXT 8192 MAXSIZE 32767M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO
create tablespace OGG_TEST_TBS datafile '/u01/app/oracle/oradata/GGG19C/pdb01/ogg_test_tbs_01.dbf' size 10M autoextend on maxsize unlimited extent management local;
create tablespace OGG_TEST_TBS datafile '+DATA' size 10M autoextend on maxsize unlimited extent management local;
**查询当前库SCN**
SYS@ggg1>select current_scn from v$database;
CURRENT_SCN
-----------
2475774
**源端基于SCN导出SCHEMA**
-- 如果源库或目标库没有足够的空间存储DMP文件，可以使用远程不落地的方式导入到目标库
-- Win10
expdp """sys/oracle@tns_ggg as sysdba""" directory=dir1 dumpfile=ogg_test.dmp schemas=ogg_test flashback_scn=1127488
-- CentOS6.8
expdp \'sys\/oracle\@tns_ggg as sysdba\' directory=dir1 dumpfile=ogg_test.dmp schemas=ogg_test flashback_scn=1127488
-- SYS免密导出
expdp \'/ as sysdba\' directory=dir1 dumpfile=ogg_test.dmp schemas=ogg_test flashback_scn=2475774
-- 不导出统计信息(加快导出速度)
expdp \'/ as sysdba\' directory=dir1 dumpfile=ogg_test.dmp schemas=ogg_test EXCLUDE=statistics flashback_scn=2475774
**源端传输DMP文件到目标端并导入**
scp ogg_test.dmp oracle@192.168.59.19:/home/oracle/pdb01_dir1
-- CentOS7.6
-- 导入到pdb01中
impdp \'sys\/oracle\@tns_pdb01 as sysdba\' directory=dir1 dumpfile=ogg_test.dmp
**检查对象**
SYS@ggg>conn ogg_test/ogg_test
Connected.
OGG_TEST@ggg>select * from tab;
TNAME TABTYPE CLUSTERID
------------------------- ------- ----------
T1 TABLE
T2 TABLE
T3 TABLE
V_SEL_T1_EMPNO_ENAME_SAL VIEW
OGG_TEST@ggg>select object_name,object_type from dba_objects where owner = 'OGG_TEST';
OBJECT_NAME OBJECT_TYPE
------------------------- -------------------
T3 TABLE
T2 TABLE
T1 TABLE
IDX_T1_SAL INDEX
V_SEL_T1_EMPNO_ENAME_SAL VIEW
col tablespace_name format a30
select owner,
segment_name,
segment_type,
tablespace_name
from dba_segments
where owner = 'OGG_TEST';
五、启动OGG各个进程
**1、源端启动MGR进程**
GGSCI (DESKTOP-528JOO2) 1> view params ./GLOBALS
MGRSERVNAME GGSMGR
GGSCI (DESKTOP-528JOO2) 2> exit
D:\ogg_src>net start ggsmgr
GGSMGR 服务正在启动 .
GGSMGR 服务已经启动成功。
D:\ogg_src>ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 19.1.0.0.4 OGGCORE_19.1.0.0.0_PLATFORMS_191017.1054_FBO
Windows x64 (optimized), Oracle 11g on Oct 19 2019 11:09:03
Operating system character set identified as GBK.
Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.
GGSCI (DESKTOP-528JOO2) 1> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED DMP_OT 00:00:00 01:22:53
EXTRACT STOPPED OGG_TEST 00:00:00 01:54:24
**2、目标端启动MGR进程**
[oracle@ggg dir1]$ cd $OGG_HOME
[oracle@ggg ogg_tgt]$ 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 (ggg) 1> start mgr
Manager started.
GGSCI (ggg) 2> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT STOPPED REP_OT 00:00:00 00:53:28
**3、源端启动EXTRACT进程**
-- 启动进程前S/T开启OGG复制
alter system set enable_goldengate_replication = true;
start et_ot
2021-09-07T13:18:17.900+0800 ERROR OGG-00041 Oracle GoldenGate Capture for Oracle, et_ot.prm: Data source not specified.
2021-09-07T13:18:17.900+0800 ERROR OGG-01668 Oracle GoldenGate Capture for Oracle, et_ot.prm: PROCESS ABENDING.
2021-09-07T13:21:36.814+0800 ERROR OGG-00303 Oracle GoldenGate Capture for Oracle, et_ot.prm: Unable to connect to database using user ogg_admin@tns_ggg. Ensure that the necessary privileges are granted to the user.
Operation not supported because enable_goldengate_replication is not set to true.
**4、目标端启动REPLIAT进程**
-- 启动复制进程组前开启OGG应用
alter system set enable_goldengate_replication = true;
-- 1127488为源端导出时指定的SCN
start rep_ot atcsn 1127488
**5、源端启动DATA PUMP进程**
start dmp_ot
1661
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
