oracle 11.2.0.4 ogg for centos6.8安装及数据半同步测试

2024-04-02 19:55

短信预约 -IT技能 免费直播动态提醒

目的：本博文给出11.2.0.4 oracle数据库ogg搭建过程中，备库的准备过程包括2种方式：第一种是主库rman全量备份后恢复，第二种是expdp从主库导出用户然后在备库导入。

对ogg数据同步进行测试，主要偏重类似mysql的半同步数据，即备库从主库同步部分数据。

?
一、ogg相关服务器基本信息

配置主机	源端	目标端
主机名	ogg1	ogg2
IP地址	10.117.130.231	10.117.130.232
内存	3832MB	3832MB
数据库管理用户	uid=500(oracle) gid=601(oinstall) 组=601(oinstall),603(dba)	uid=500(oracle) gid=601(oinstall) 组=601(oinstall),603(dba)
数据库版本	11.2.0.4	11.2.0.4
ORACLE_HOME	/u01/oracle/app/oracle/product/11.2.0.4/db	/u01/oracle/app/oracle/product/11.2.0.4/db
NIL_LANG	AMERICAN_AMERICA.ZHS16GBK	AMERICAN_AMERICA.ZHS16GBK
OGG_HOME	/u01/oracle/app/ogg	/u01/oracle/app/ogg
OGG版本	12.3.0.1.0	12.3.0.1.0
OGG管理用户/密码	GOLDENGATE/GOLDENGATE	GOLDENGATE/GOLDENGATE
OGG同步的用户	HR(示例库)，ZHUL（模拟业务）

二、主库调整
1、主库开启归档模式
select log_mode from v$database;
oracle 11.2.0.4 ogg for centos6.8安装及数据半同步测试

如果主库没有开启归档模式则开启归档模式
2、打开数据库force-logging及补充日志
alter database force logging;
alter database add supplemental log data;
alter database add supplemental log data (primary key) columns;

oracle 11.2.0.4 ogg for centos6.8安装及数据半同步测试

3、处理压缩表及分区压缩表，将其变为非压缩表
select 'alter table '||table_owner||'.'||table_name||' move partition '||partition_name||' nocompress update indexes;' from dba_tab_partitions where compression ='ENABLED' and table_owner='HR';
4、处理nologging的表，将其修改为logging的表
select 'alter table '||owner||'.'||table_name||' logging;' from dba_tables where owner in ('HR') and logging='NO';
5、处理nologging分区表为logging分区
select distinct 'alter table '||table_owner||'.'||table_name||' logging;' from dba_tab_partitions where table_owner in('HR') and logging='NO';

6、主库创建OGG专用用户
create tablespace goldengate datafile '$ORACLE_BASE/oradata/$ORACLE_SID/goldengate.dbf' size 100M autoextend on;

CREATE USER GOLDENGATE IDENTIFIED BY GOLDENGATE DEFAULT TABLESPACE GOLDENGATE TEMPORARY TABLESPACE TEMP;
GRANT CONNECT TO GOLDENGATE;
GRANT ALTER ANY TABLE TO GOLDENGATE;
GRANT ALTER SESSION TO GOLDENGATE;
GRANT CREATE SESSION TO GOLDENGATE;
GRANT FLASHBACK ANY TABLE TO GOLDENGATE;
GRANT SELECT ANY DICTIONARY TO GOLDENGATE;
GRANT SELECT ANY TABLE TO GOLDENGATE;
GRANT RESOURCE TO GOLDENGATE;
GRANT SELECT ANY TRANSACTION TO GOLDENGATE;
四、主库源数据备份
1、rman全备份
backup database format '/home/oracle/rman/full_%D_%d_%U.bak';

select current_scn from v$database;

alter system switch logfile;

backup archivelog all format '/home/oracle/rman/arch_%D_%d_%U.bak';

2、expdp按方案导出
2.1 测试数据来源
[oracle@ogg1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Sep 26 14:28:36 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 Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create user zhul identified by zhul default tablespace users;
User created.
SQL> grant create session to zhul;
Grant succeeded.
SQL> grant resource to zhul;
Grant succeeded.
SQL> create table zhul.emp as select * from scott.emp;
Table created.
SQL> c/emp/dept
1* create table zhul.dept as select * from scott.emp
SQL> c/emp/dept
1* create table zhul.dept as select * from scott.dept
SQL> /
Table created.
SQL> c/dept/SALGRADE
1* create table zhul.SALGRADE as select * from scott.dept
SQL> c/dept/SALGRADE
1* create table zhul.SALGRADE as select * from scott.SALGRADE
SQL> /
Table created.
SQL> create directory zhul as '/home/oracle/';
Directory created.
SQL>
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
1057694
2.2 expdp备份
[oracle@ogg1 ~]$ cat param
userid=zhul/zhul
directory=zhul
tables=emp
dumpfile=expdp_zhul_emp_270926.dmp
logfile=log_zhul_emp_270926.log
flashback_scn=1057694
query=emp:"where hiredate>'1982-01-02'"
[oracle@ogg1 ~]$
[oracle@ogg1 ~]$ expdp parfile=/home/oracle/param
Export: Release 11.2.0.4.0 - Production on Tue Sep 26 14:54:27 2017
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
Starting "ZHUL"."SYS_EXPORT_TABLE_01": zhul/******** parfile=/home/oracle/param
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "ZHUL"."EMP" 8.109 KB 3 rows
Master table "ZHUL"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ZHUL.SYS_EXPORT_TABLE_01 is:
/home/oracle/expdp_zhul_emp_270926.dmp
Job "ZHUL"."SYS_EXPORT_TABLE_01" successfully completed at Tue Sep 26 14:54:30 2017 elapsed 0 00:00:01
[oracle@ogg1 ~]$
五、备库目标数据恢复
恢复前准备
如源库开启闪回，需创建同等闪回目录即$ORACLE_BASE/ fast_recovery_area，否则报错：
oracle 11.2.0.4 ogg for centos6.8安装及数据半同步测试

需要建立与主库同等的审计目录，否则报错：
mkdir -p /u01/oracle/app/oracle/admin/orcl/adump
oracle 11.2.0.4 ogg for centos6.8安装及数据半同步测试

备库创建数据目录：
mkdir –p /u01/oracle/app/oracle/oradata/orcl/
1、rman全恢复
rman target /
RMAN> startup nomount;

RMAN> restore spfile from '/home/oracle/rman/full_25_ORCL_02sfc009_1_1.bak';

RMAN> shutdown immediate;

RMAN> startup nomount;

RMAN> restore controlfile from '/home/oracle/rman/full_25_ORCL_02sfc009_1_1.bak';

RMAN> alter database mount;

RMAN> restore database;

RMAN> catalog start with '/home/oracle/rman';

RMAN> run{
set until scn 996247;
recover database;
}
--这里的SCN号是主库源数据备份时查出来的SCN号，就是第四部里边的执行绿色sql命令时查出来的，注意一定要有。
2、备库expdp备份导入
2.1 创建directory
create directory zhul as ‘/home/oracle’;
2.2 创建Ogg专用用户
CREATE USER GOLDENGATE IDENTIFIED BY GOLDENGATE DEFAULT TABLESPACE GOLDENGATE TEMPORARY TABLESPACE TEMP;
GRANT CONNECT TO GOLDENGATE;
GRANT ALTER ANY TABLE TO GOLDENGATE;
GRANT ALTER SESSION TO GOLDENGATE;
GRANT CREATE SESSION TO GOLDENGATE;
GRANT FLASHBACK ANY TABLE TO GOLDENGATE;
GRANT SELECT ANY DICTIONARY TO GOLDENGATE;
GRANT SELECT ANY TABLE TO GOLDENGATE;
GRANT RESOURCE TO GOLDENGATE;
GRANT SELECT ANY TRANSACTION TO GOLDENGATE;

2.3 数据导入
[oracle@ogg2 ~]$ impdp system/oracleoracle directory=zhul tables=zhul.emp dumpfile=expdp_zhul_emp_270926.dmp logfile=impdp_zhul_emp_270926.log
Import: Release 11.2.0.4.0 - Production on Tue Sep 26 15:08:09 2017
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
Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLE_01": system/******** directory=zhul tables=zhul.emp dumpfile=expdp_zhul_emp_270926.dmp logfile=impdp_zhul_emp_270926.log
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "ZHUL"."EMP" 8.109 KB 3 rows
Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at Tue Sep 26 15:08:16 2017 elapsed 0 00:00:05
[oracle@ogg2 ~]$
六、主库安装OGG
1、创建安装目录
mkdir –p $ORACLE_BASE\ogg

2、上传并解压OGG软件

3、编辑OGG静默安装脚本

[oracle@ogg1 response]$ pwd

/u01/oracle/app/fbo_ggs_Linux_x64_shiphome/Disk1/response

[oracle@ogg1 response]$ ls

oggcore.rsp oggcore.rsp.bak

[oracle@ogg1 response]$ cat oggcore.rsp

####################################################################

## ##

## Specify values for the variables listed below to customize ##

## your installation. ##

## ##

## Each variable is associated with a comment. The comment ##

## can help to populate the variables with the appropriate ##

## values. ##

## ##

## IMPORTANT NOTE: This file should be secured to have read ##

## permission only by the oracle user or an administrator who ##

## own this installation to protect any sensitive input values. ##

## ##

####################################################################

#-------------------------------------------------------------------------------

# Do not change the following system generated value.

#-------------------------------------------------------------------------------

oracle.install.responseFileVersion=/oracle/install/rspfmt_ogginstall_response_schema_v12_1_2

################################################################################

## ##

## Oracle GoldenGate installation option and details ##

## ##

################################################################################

#-------------------------------------------------------------------------------

# 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=ORA11g

#-------------------------------------------------------------------------------

# Specify a location to install Oracle GoldenGate

#-------------------------------------------------------------------------------

SOFTWARE_LOCATION=/u01/oracle/app/ogg

#-------------------------------------------------------------------------------

# Specify true to start the manager after installation.

#-------------------------------------------------------------------------------

START_MANAGER=true

#-------------------------------------------------------------------------------

# Specify a free port within the valid range for the manager process.

# Required only if START_MANAGER is true.

#-------------------------------------------------------------------------------

MANAGER_PORT=1700

#-------------------------------------------------------------------------------

# Specify the location of the Oracle Database.

# Required only if START_MANAGER is true.

#-------------------------------------------------------------------------------

DATABASE_LOCATION=/u01/oracle/app/oracle/product/11.2.0.4/db

################################################################################

## ##

## Specify details to Create inventory for Oracle installs ##

## Required only for the first Oracle product install on a system. ##

## ##

################################################################################

#-------------------------------------------------------------------------------

# Specify the location which holds the install inventory files.

# This is an optional parameter if installing on

# Windows based Operating System.

#-------------------------------------------------------------------------------

INVENTORY_LOCATION=/u01/oracle/app/oraInventory

#-------------------------------------------------------------------------------

# Unix group to be set for the inventory directory.

# This parameter is not applicable if installing on

# Windows based Operating System.

#-------------------------------------------------------------------------------

UNIX_GROUP_NAME=oinstall

[oracle@ogg1 response]$

[oracle@ogg1 response]$
4、OGG静默安装
[oracle@ogg1 Disk1]$ pwd
/u01/oracle/app/fbo_ggs_Linux_x64_shiphome/Disk1
[oracle@ogg1 Disk1]$./runInstaller -silent -responseFile /u01/oracle/app/fbo_ggs_Linux_x64_shiphome/Disk1/response/oggcore.rsp
5、源端配置OGG相关的视图基表
[oracle@ogg1 app]$ cd ogg
[oracle@ogg1 ogg]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Sep 25 21:25:29 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 Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @marker_setup.sql
SQL> @ ddl_setup.sql
SQL> @ role_setup.sql
SQL> @ddl_enable.sql
SQL> sequence.sql
SQL> grant GGS_GGSUSER_ROLE to GOLDENGATE;
SQL> alter system set enable_goldengate_replication = true scope=both;
6、配置OGG mgr进程
oracle 11.2.0.4 ogg for centos6.8安装及数据半同步测试

edit param mgr

PORT 1700
PURGEOLDEXTRACTS /u01/oracle/app/ogg/dirdat, USECHECKPOINTS
--确定当前目录就在ogg的安装目录/u01/oracle/app/ogg，然后重启mgr进程
start mgr

oracle 11.2.0.4 ogg for centos6.8安装及数据半同步测试

7、开启源数据的同步日志
dblogin userid goldengate,password goldengate;

info trandata hr.*
oracle 11.2.0.4 ogg for centos6.8安装及数据半同步测试

add trandata hr.*

确认传输日志开启

oracle 11.2.0.4 ogg for centos6.8安装及数据半同步测试

8、配置源数据抽取进程ext_hr，ext_zhul
add extract ext_hr,tranlog,begin now
8.1 添加ext_hr的数据抽取数据存放文件
add exttrail /u01/oracle/app/ogg/dirdat/et,extract ext_hr
8.1.1 添加ext_hr的远程数据存放文件
ADD RMTTRAIL /u01/oracle/app/ogg/dirat/et, EXTRACT EXT_HR

edit param ext_hr

EXTRACT ext_hr

setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK )

userid GOLDENGATE,password GOLDENGATE

rmthost 10.117.130.232,mgrport 1700

rmttrail /u01/oracle/app/ogg/dirdat/et

TRANLOGOPTIONS EXCLUDEUSER GOLDENGATE

ddl include all

table hr.t,SQLPREDICATE "where salary>9000";

8.1.3 启动ext_hr进程
start ext_hr
oracle 11.2.0.4 ogg for centos6.8安装及数据半同步测试

8.1.4 增加ogg用户检查点
add checkpointtable GOLDENGATE.checkpoint

8.2 主库配置数据抽取进程exp_zhul
dblogin userid GOLDENGATE,password GOLDENGATE;

查看zhul下的数据表的数据传输信息
info trandata zhul.*

启用zhul下的数据表传输
add trandata zhul.*

添加备库的ogg数据抽取进程
add extract ext_zhul,tranlog,begin now

添加备库的ogg数据抽取日志队列文件
add exttrail /u01/oracle/app/ogg/dirdat/ez,extract ext_zhul

ADD RMTTRAIL /u01/oracle/app/ogg/dirat/ez, EXTRACT ext_zhul

编辑主库的数据抽取进程ext_zhul
edit param ext_zhul

EXTRACT ext_zhul

setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK )

userid GOLDENGATE,password GOLDENGATE

rmthost 10.117.130.232,mgrport 1700

rmttrail /u01/oracle/app/ogg/dirdat/ez

TRANLOGOPTIONS EXCLUDEUSER GOLDENGATE

--ddl include all

table zhul.emp,SQLPREDICATE "where hiredate>'1982-01-02'";

8.3 启动exp_zhul进程

oracle 11.2.0.4 ogg for centos6.8安装及数据半同步测试

七、目标端安装OGG
1、创建安装目录
mkdir –p $ORACLE_BASE\ogg

2、上传并解压OGG软件

3、编辑OGG静默安装脚本