OGG（11G）单实例--＞单实例 搭建--同构同版本
【摘要】 目录一、环境介绍二、GoldenGate安装（源端+目标端）1、创建OGG安装目录2、修改OGG操作系统用户环境变量4、上传OGG安装介质并解压5、建立OGG表空间及用户，给用户授权6、配置TNS7、配置数据库参数附加日志强制日志检查是否开启开启归档日志模式关闭回收站8、GGSCI初始化目录三、GoldenGate配置1、配置mgr进程（源端+目标端）2、添加表级trandata（源端）3...
目录
查看目标端的SCN号，记录该SCN号（得到该SCN号之后，启动复制进程时，使⽤该SCN号）
[3、源端extract 配置](<#3、源端extract 配置>)
一、环境介绍
| 项目 | 源库 | 目标库 |
| 操作系统 | RHEL6.8 | RHEL6.8 |
| 主机名 | s11g | t11g |
| IP地址 | 192.168.56.20 | 192.168.56.21 |
| 数据库及版本 | ORACLE11.2.0.4 | ORACLE11.2.0.4 |
| 数据库字符集 | AL32UTF8 | AL32UTF8 |
| ORACLE SID | orcl | orcl |
| Goldengate用户 | ogg | ogg |
| Goldengate版本 | 112103(Linux x86-64) | 112103(Linux x86-64) |
二、GoldenGate安装（源端+目标端）
1、创建OGG安装目录
mkdir -p /ogg
chown -R oracle:oinstall /ogg
chmod 775 /ogg
2、修改OGG操作系统用户环境变量
cat<<EOF >> /home/oracle/.bash_profile
################ogg profile add#########################
export OGG_HOME=/ogg
export PATH=\$ORACLE_HOME/bin:\$PATH:\$OGG_HOME
export LD_LIBRARY_PATH=\$ORACLE_HOME/lib:\$OGG_HOME:/lib:/usr/lib
alias ggsci='rlwrap ggsci'
EOF
4、上传OGG安装介质并解压
chown -R oracle:oinstall /soft/
su - oracle -c "tar -xvf /soft/fbo_ggs_Linux_x64_ora11g_64bit.tar -C /ogg"
5、建立OGG表空间及用户，给用户授权
create tablespace ogg_data datafile '/oradata/orcl/ogg01.dbf' size 1024M;
create user ogg identified by ogg default tablespace ogg_data;
grant connect,resource,dba,create table,create sequence to ogg;
6、配置TNS
--源端
cat <<EOF >> /etc/hosts
192.168.56.21 t11g
EOF
--目标端
cat <<EOF >> /etc/hosts
192.168.56.20 s11g
EOF
--源端和目标端
cat >> $ORACLE_HOME/network/admin/tnsnames.ora <<EOF
SORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = s11g)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
TORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = t11g)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
EOF
7、配置数据库参数
附加日志
alter database add SUPPLEMENTAL log data;
强制日志
alter database force logging;
检查是否开启
select NAME,OPEN_MODE,FORCE_LOGGING,SUPPLEMENTAL_LOG_DATA_MIN from v$database;
开启归档日志模式
mkdir /archivelog
chown -R oracle:oinstall /archivelog/
su - oracle
sqlplus / as sysdba
alter system set log_archive_dest_1='location=/archivelog';
shutdown immediate
startup mount
alter database archivelog;
alter database open;
archive log list;
alter system switch logfile;
关闭回收站
alter system set recyclebin=off scope=spfile;
8、GGSCI初始化目录
cd /ogg
ggsci
GGSCI (s11g) 1> create subdirs
三、GoldenGate配置
1、配置mgr进程（源端+目标端）
GGSCI (s11g) 1> edit params mgr
GGSCI (s11g) 2> view params mgr
port 7809
GGSCI (s11g) 3> start mgr
Manager started.
GGSCI (s11g) 4> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
GGSCI (s11g) 6> sh netstat -ntpl|grep 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 5760/./mgr
GGSCI (s11g) 7> sh ps -ef|grep mgr
root 14 2 0 09:59 ? 00:00:00 [async/mgr]
postfix 1683 1676 0 09:59 ? 00:00:00 qmgr -l -t fifo -u
oracle 5760 5752 0 11:52 ? 00:00:00 ./mgr PARAMFILE /ogg/dirprm/mgr.prm REPORTFILE /ogg/dirrpt/MGR.rpt PROCESSID MGR PORT 7809
oracle 5852 5752 0 12:15 pts/0 00:00:00 sh -c ps -ef|grep mgr
oracle 5854 5852 0 12:15 pts/0 00:00:00 grep mgr
2、添加表级trandata（源端）
GGSCI (s11g) 1> dblogin userid ogg,password ogg
Successfully logged into database.
GGSCI (s11g) 4> info trandata scott.emp
Logging of supplemental redo log data is disabled for table SCOTT.EMP.
GGSCI (s11g) 5> add trandata scott.emp
Logging of supplemental redo data enabled for table SCOTT.EMP.
3、配置extract抽取进程（源端）
GGSCI (s11g) 6> dblogin userid ogg,password ogg
Successfully logged into database.
GGSCI (s11g) 7> add extract ext1,tranlog,begin now,threads 1
EXTRACT added.
GGSCI (s11g) 8> add exttrail ./dirdat/et,extract ext1
EXTTRAIL added.
GGSCI (s11g) 9> edit params ext1
GGSCI (s11g) 10> view params ext1
EXTRACT ext1
SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
--SETENV (ORACLE_SID = "orcl")
SETENV (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db)
USERID ogg, PASSWORD ogg
THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 20000
EXTTRAIL ./dirdat/et
DYNAMICRESOLUTION
--DDL INCLUDE ALL
--TABLE scott.emp;
TABLE scott.*;
4、配置pump传输进程（源端）
GGSCI (s11g) 12> add extract pump1,exttrailsource ./dirdat/et,begin now
EXTRACT added.
GGSCI (s11g) 13> add rmttrail ./dirdat/et,extract pump1
RMTTRAIL added.
GGSCI (s11g) 14> edit params pump1
GGSCI (s11g) 15> view params pump1
EXTRACT pump1
RMTHOST 192.168.56.21, MGRPORT 7809
RMTTRAIL ./dirdat/et
PASSTHRU
DYNAMICRESOLUTION
--TABLE scott.emp;
TABLE scott.*;
GGSCI (s11g) 16> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXT1 00:00:00 00:06:34
EXTRACT STOPPED PUMP1 00:00:00 00:01:43
四、RMAN初始化数据
1、源端启动extract进程
GGSCI (s11g) 4> start ext1
Sending START request to MANAGER ...
EXTRACT EXT1 starting
GGSCI (s11g) 5> start pump1
Sending START request to MANAGER ...
EXTRACT PUMP1 starting
GGSCI (s11g) 6> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:12:12 00:00:10
EXTRACT RUNNING PUMP1 00:00:00 00:14:56
2、查询源端是否有长事务运行
select min(start_time) from gv$transaction;
3、RMAN备份源端数据库
注意：备份过程保证抽取进程状态⼀直正常。
--源端和目标端都创建目录
mkdir /backup
chown -R oracle:oinstall /backup
--源端备份全库
su - oracle
rman target /
run {
allocate channel ch00 type disk maxpiecesize 10g;
allocate channel ch01 type disk maxpiecesize 10g;
sql 'alter system switch logfile';
sql 'alter system switch logfile';
sql 'alter system switch logfile';
sql 'alter system switch logfile';
crosscheck backupset;
delete noprompt expired backupset;
backup database format '/backup/bk_%s_%p_%t';
sql 'alter system archive log current';
BACKUP ARCHIVELOG ALL FORMAT '/backup/ARCH_%U';
BACKUP CURRENT CONTROLFILE FORMAT '/backup/bk_controlfile';
release channel ch00;
release channel ch01;
}
4、目标端RMAN恢复
备份文件拷贝至目标端
scp * 192.168.56.21:/backup
目标端将数据库启动到nomount状态
sqlplus / as sysdba
shutdown immediate
startup nomount
目标端恢复控制文件
su - oracle
rman target /
restore controlfile from '/backup/bk_controlfile';
目标端启动到mount
alter database mount;
目标端rman恢复
restore database;
--recover之前需要将redo log都mv或者rm掉，再进行recover
recover database;
否则会报错：
RMAN-03002: failure of recover command at 03/24/2021 13:07:31
ORA-19698: /oradata/orcl/redo01.log is from different database: id=1595737004, db_name=ORCL
查看目标端的SCN号，记录该SCN号（得到该SCN号之后，启动复制进程时，使⽤该SCN号）
SELECT CHECKPOINT_CHANGE#,CHECKPOINT_TIME FROM V$DATAFILE_HEADER;
1042454
以resetlogs方式开启目标端数据库（目标端）
alter database open resetlogs;
5、添加GLOBALS参数文件，创建检查点表（目标端）
GGSCI (t11g) 10> edit params ./GLOBALS
GGSCI (t11g) 11> view params ./GLOBALS
GGSCHEMA ogg
checkpointtable ogg.checkpoint
GGSCI (t11g) 12> dblogin userid ogg,password ogg
Successfully logged into database.
GGSCI (t11g) 13> add checkpointtable ogg.checkpoint
Successfully created checkpoint table ogg.checkpoint.
6、配置replicate复制进程（目标端）
GGSCI (t11g) 14> add replicat rep1, exttrail ./dirdat/et, checkpointtable ogg.checkpoint
REPLICAT added.
GGSCI (t11g) 15> edit params rep1
GGSCI (t11g) 16> view params rep1
REPLICAT rep1
setenv (ORACLE_SID=orcl)
SETENV (ORACLE_HOME = "/u01/app/oracle/product/11.2.0/db")
USERID ogg,PASSWORD ogg
ASSUMETARGETDEFS
HANDLECOLLISIONS
REPERROR (DEFAULT, DISCARD)
DDLERROR DEFAULT DISCARD
DDLOPTIONS REPORT
DISCARDFILE ./dirrpt/repr1.dsc,append,megabytes 100
MAP scott.*, TARGET scott.*;
7、用SCN启动Replicat（目标端）
GGSCI (t11g) 17> start rep1,aftercsn 1042454
Sending START request to MANAGER ...
REPLICAT REP1 starting
GGSCI (t11g) 1> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT STOPPED REP1 00:00:00 00:07:29
--这边启动报错：
2021-03-24 13:30:22 ERROR OGG-00664 OCI Error creating temporary LOB to retrieve default LOB chunk size (status = 1187-ORA-01187: cannot read from fi
le because it failed verification tests
ORA-01110: data file 201: '/oradata/orcl/temp01.dbf').
2021-03-24 13:30:22 ERROR OGG-01668 PROCESS ABENDING.
--解决方案：
目标端temp临时表空间drop重建
alter database tempfile '/oradata/orcl/temp01.dbf' drop;
alter tablespace temp add tempfile '/oradata/orcl/temp01.dbf' size 19M reuse;
--重新启动replicat进程
GGSCI (t11g) 18> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP1 00:00:00 00:02:13
8、检查是否同步
DML测试
查看两端scott.emp数据，数据一致
SYS@orcl>set line222
SYS@orcl>set pagesize100
SYS@orcl>select * from scott.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
源端插入一条数据
insert into emp(empno,ename,job) values (9874,'LUCIFER','DBA');
commit;
查询目标端发现已经同步
SYS@orcl>select * from scott.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
9874 LUCIFER DBA
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
15 rows selected.
五、开启DDL
1、添加参数（源端）
GGSCI (s11g) 2> edit params ./GLOBALS
GGSCI (s11g) 3> view params ./GLOBALS
GGSCHEMA ogg
2、在源端执⾏与DDL同步相关的SQL脚本
su - oracle
--切记@marker_setup.sql ⼀定要在cd $OGG_HOME⽬录下执⾏否则会卡主。执⾏脚本时要输⼊ogg管理⽤户，本实例是ogg。
cd /ogg
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
@marker_status
3、源端extract 配置
GGSCI (s11g) 10> edit params ext1
GGSCI (s11g) 11> view params ext1
EXTRACT ext1
SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
--SETENV (ORACLE_SID = "orcl")
SETENV (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db)
USERID ogg, PASSWORD ogg
THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 20000
EXTTRAIL ./dirdat/et
DYNAMICRESOLUTION
--增加如下ddl参数
DDL INCLUDE ALL
TABLE scott.emp;
4、重启extract进程
GGSCI (s11g) 13> stop ext1
Sending STOP request to EXTRACT EXT1 ...
Request processed.
GGSCI (s11g) 14> start ext1
Sending START request to MANAGER ...
EXTRACT EXT1 starting
GGSCI (s11g) 15> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:00 00:00:00
EXTRACT RUNNING PUMP1 00:00:00 00:00:01
5、目标端replicat配置
GGSCI (t11g) 3> edit params rep1
GGSCI (t11g) 4> view params rep1
REPLICAT rep1
setenv (ORACLE_SID=orcl)
SETENV (ORACLE_HOME = "/u01/app/oracle/product/11.2.0/db")
USERID ogg,PASSWORD ogg
ASSUMETARGETDEFS
HANDLECOLLISIONS
REPERROR (DEFAULT, DISCARD)
DDLERROR DEFAULT DISCARD
DDLOPTIONS REPORT
DISCARDFILE ./dirrpt/repr1.dsc,append,megabytes 100
MAP scott.*, TARGET scott.*;
--添加如下ddl参数
ddl include all
ddlerror default ignore retryop maxretries 3 retrydelay 5
6、重启replicat进程
GGSCI (t11g) 5> stop rep1
Sending STOP request to REPLICAT REP1 ...
Request processed.
GGSCI (t11g) 6> start rep1
Sending START request to MANAGER ...
REPLICAT REP1 starting
GGSCI (t11g) 7> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP1 00:00:00 00:00:06
7、DDL测试
查看scott下的表（源端+目标端）
SCOTT@orcl>select table_name from user_tables;
TABLE_NAME
------------------------------
SALGRADE
BONUS
EMP
DEPT
4 rows selected.
源端创建一张表
SCOTT@orcl>create table lucifer(id number not null,name varchar2(20));
Table created.
SCOTT@orcl>insert into lucifer(id,name) values (1,'lucifer');
1 row created.
SCOTT@orcl>insert into lucifer(id,name) values (2,'lpc');
1 row created.
SCOTT@orcl>insert into lucifer(id,name) values (3,'hsx');
1 row created.
SCOTT@orcl>commit;
Commit complete.
SCOTT@orcl>select table_name from user_tables;
TABLE_NAME
------------------------------
LUCIFER
SALGRADE
BONUS
EMP
DEPT
5 rows selected.
SCOTT@orcl>select * from lucifer;
ID NAME
---------- --------------------
1 lucifer
2 lpc
3 hsx
3 rows selected.
查看目标端已经同步
SYS@orcl>conn scott/scott
Connected.
SCOTT@orcl>show user
USER is "SCOTT"
SCOTT@orcl>select table_name from user_tables;
TABLE_NAME
------------------------------
LUCIFER
SALGRADE
BONUS
EMP
DEPT
SCOTT@orcl>select * from lucifer;
ID NAME
---------- --------------------
1 lucifer
2 lpc
3 hsx
【版权声明】本文为华为云社区用户原创内容，转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息， 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱：
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论（0）