oracle 11.2.0.4 ogg for centos6.8安裝及數據半同步測試

發布時間：2020-08-08 01:47:53 來源：ITPUB博客閱讀：216 作者：清風艾艾欄目：關系型數據庫

目的：本博文給出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靜默安裝腳本