OGG（11G）单实例--＞单实例搭建--同构同版本

Lucifer三思而后行

发布于 2021-08-17 15:19:07

6060

发布于 2021-08-17 15:19:07

一、环境介绍

项目	源库	目标库
操作系统	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<> /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 <> /etc/hosts
192.168.56.21 t11g
EOF

--目标端
cat <> /etc/hosts
192.168.56.20 s11g
EOF

--源端和目标端
cat >> $ORACLE_HOME/network/admin/tnsnames.ora <

`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

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2021/03/24 ，如有侵权请联系 cloudcommunity@tencent.com 删除

数据库

sql

腾讯云测试服务

本文分享自作者个人站点/博客前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

数据库

sql

腾讯云测试服务

登录后参与评论

0 条评论

热度

OGG（11G）单实例--＞单实例 搭建--同构同版本

OGG（11G）单实例--＞单实例 搭建--同构同版本

目录

一、环境介绍

二、GoldenGate安装（源端+目标端）

1、创建OGG安装目录

2、修改OGG操作系统用户环境变量

4、上传OGG安装介质并解压

5、建立OGG表空间及用户，给用户授权

6、配置TNS

7、配置数据库参数

附加日志

强制日志

检查是否开启

开启归档日志模式

关闭回收站

8、GGSCI初始化目录

三、GoldenGate配置

1、配置mgr进程（源端+目标端）

2、添加表级trandata（源端）

3、配置extract抽取进程（源端）

4、配置pump传输进程（源端）

四、RMAN初始化数据

1、源端启动extract进程

2、查询源端是否有长事务运行

3、RMAN备份源端数据库

4、目标端RMAN恢复

备份文件拷贝至目标端

目标端将数据库启动到nomount状态

目标端恢复控制文件

目标端启动到mount

目标端rman恢复

查看目标端的SCN号，记录该SCN号（得到该SCN号之后，启动复制进程时，使⽤该SCN号）

以resetlogs方式开启目标端数据库（目标端）

5、添加GLOBALS参数文件，创建检查点表（目标端）

6、配置replicate复制进程（目标端）

7、用SCN启动Replicat（目标端）

8、检查是否同步

DML测试

五、开启DDL

1、添加参数（源端）

2、在源端执⾏与DDL同步相关的SQL脚本

3、源端extract 配置

4、重启extract进程

5、目标端replicat配置

6、重启replicat进程

7、DDL测试

查看scott下的表（源端+目标端）

源端创建一张表

查看目标端已经同步

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

OGG（11G）单实例--＞单实例搭建--同构同版本

OGG（11G）单实例--＞单实例搭建--同构同版本

`7、配置数据库参数`

`附加日志`

`强制日志`

`检查是否开启`

`开启归档日志模式`

`关闭回收站`

`8、GGSCI初始化目录`

`三、GoldenGate配置`

`1、配置mgr进程（源端+目标端）`

`2、添加表级trandata（源端）`

`3、配置extract抽取进程（源端）`

`4、配置pump传输进程（源端）`

`四、RMAN初始化数据`

`1、源端启动extract进程`

`2、查询源端是否有长事务运行`

`3、RMAN备份源端数据库`

`4、目标端RMAN恢复`

`备份文件拷贝至目标端`

`目标端将数据库启动到nomount状态`

`目标端恢复控制文件`

`目标端启动到mount`

`目标端rman恢复`

`查看目标端的SCN号，记录该SCN号（得到该SCN号之后，启动复制进程时，使⽤该SCN号）`

`以resetlogs方式开启目标端数据库（目标端）`

`5、添加GLOBALS参数文件，创建检查点表（目标端）`

`6、配置replicate复制进程（目标端）`

`7、用SCN启动Replicat（目标端）`

`8、检查是否同步`

`DML测试`

`五、开启DDL`

`1、添加参数（源端）`

`2、在源端执⾏与DDL同步相关的SQL脚本`

`3、源端extract 配置`

`4、重启extract进程`

`5、目标端replicat配置`

`6、重启replicat进程`

`7、DDL测试`

`查看scott下的表（源端+目标端）`

`源端创建一张表`

`查看目标端已经同步`