oracle11g ASM goldengate的安装配置与测试

最新推荐文章于 2020-10-15 18:41:30 发布

ctxylong2013

最新推荐文章于 2020-10-15 18:41:30 发布

阅读量220

点赞数

实验环境：
oracle VM VBOX虚拟机:VirtualBox-4.3.10-93012-Win
oracleLinux5.5:Enterprise-R5-U5-Server-i386-dvd
oracle 11g:Release 11.2.0.1.0 Production

源端和目标端数据库安装可以参考：
http://blog.itpub.net/15810196/viewspace-1172871/ ORACLE11g GRID单机安装

安装前准备：
1.cat /etc/hosts --查看本地IP解析（源、目标端）
这里配置源端和目标端的IP解析
192.168.56.101 goldengate1.localdomain goldengate1
192.168.56.102 goldengate2.localdomain goldengate2
2.设置参数（linux下，源、目标端）
a.编辑参数vi /home/oracle/.bash_profile
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
b.使参数生效
. ./.bash_profile
c.打印出参数进行检查
echo $LD_LIBRARY_PATH
3.创建专用用户表空间（源、目标端）
a.创建表空间
--create tablespace goldengate datafile '/oradata/$ORACLE_SID/goldengate01.dbf' size 100m autoextend
--ASM存储下的创建语句：
create tablespace goldengate datafile '+DATA/$ORACLE_SID/datafile/goldengate01.dbf' size 100m;
--源端：create tablespace hr datafile '+DATA/orcl/datafile/hr01.dbf' size 200m;
--目标端：create tablespace hr datafile '+DATA/remote/datafile/hr01.dbf' size 200m;
b.创建用户并授权
--GOLDENGATE用户
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;
GRANT INSERT ANY TABLE TO GOLDENGATE;
GRANT UPDATE ANY TABLE TO GOLDENGATE;
GRANT DELETE ANY TABLE TO GOLDENGATE;
--HR用户测试用的schema
CREATE USER HR IDENTIFIED BY oracle DEFAULT TABLESPACE HR TEMPORARY TABLESPACE TEMP;
GRANT CONNECT TO HR;
GRANT ALTER ANY TABLE TO HR;
GRANT ALTER SESSION TO HR;
GRANT CREATE SESSION TO HR;
GRANT FLASHBACK ANY TABLE TO HR;
GRANT SELECT ANY DICTIONARY TO HR;
GRANT SELECT ANY TABLE TO HR;
GRANT RESOURCE TO HR;
GRANT SELECT ANY TRANSACTION TO HR;
Grant Dba To HR;
--初始数据，HR用户创建
Create Table employee(Id Number Primary Key,Name Varchar2(20),dept_no Number,salary Number);
Insert Into employee Values(1,'zhang',1,5000);
Insert Into employee Values(2,'wang',1,5000);
Insert Into employee Values(3,'li',2,10000);

Create Table dept(dept_no Number Primary Key,dept_name Varchar2(20));
Insert Into dept Values(1,'sales');
Insert Into dept Values(2,'boss');

c.查看是否归档（源端必须，目标端建议）
select log_mode from v$database;
alter system set log_archive_dest_1='LOCATION=/oradata/archivelog' SCOPE=BOTH;
--ASM存储下的创建语句：
grid登录sqlplus / as sysasm
alter diskgroup DATA add directory '+DATA/archivelog';
oracle登录sqlplus / as sysdba
alter system set log_archive_dest_1='location=+DATA/archivelog' SCOPE=BOTH;
改为归档
shutdown immediate
startup mount
show parameter log_archive_dest_1
alter database archivelog;
alter database open;
4.打开附加日志(源端)
redo使用rowid进行表示，goldengate需增加额外标识，附加日志来标识唯一的一条记录。所以需要打开数据库的附加日志）
a.检查数据库最小附加日志是否打开
SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;
b.打开数据库最小附加日志开关
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
c.切换归档日志，使附加日志开关生效
ALTER SYSTEM SWITCH LOGFILE;
5.安装GoldenGata
a.uname -a   确定操作系统版本
b.sqlplus -v   确定数据库版本，在oracle网站下载相应版本的goldengate程序
c.使用vbox的共享文件夹(共享文件夹名称:sharing)将goldengate文件上传到相应目录
mount -t vboxsf sharing /mnt
mkdir -p /u01/app/goldengate
chown -R oracle:dba /u01/app/goldengate
chmod 775 -R /u01/app/goldengate
cp /mnt/goldengate/ogg112101_fbo_ggs_Linux_x86_ora11g_32bit/fbo_ggs_Linux_x86_ora11g_32bit.tar /u01/app/goldengate/
cd /u01/app/goldengate/
d.tar -xvf fbo_ggs_*.tar --解压tar包即完成安装
e.运行goldengate
./ggsci
f.GGSCI>info all 在ggsci命令行中，查看info信息
Program     Status      Group       Lag at Chkpt Time Since Chkpt
MANAGER     STOPPED
g.在目标端重复步骤a-f
6.配置源端与目标端的管理进程
a.创建goldengate的runtime目录(源、目标端)
GGSCI> create subdirs
Creating subdirectories under current directory /u01/app/goldengate
Parameter files                /u01/app/goldengate/dirprm: already exists
Report files                   /u01/app/goldengate/dirrpt: created
Checkpoint files               /u01/app/goldengate/dirchk: created
Process status files           /u01/app/goldengate/dirpcs: created
SQL script files               /u01/app/goldengate/dirsql: created
Database definitions files     /u01/app/goldengate/dirdef: created
Extract data files             /u01/app/goldengate/dirdat: created
Temporary files                /u01/app/goldengate/dirtmp: created
Stdout files                   /u01/app/goldengate/dirout: created
b.配置mgr参数
GGSCI>edit params mgr
port 7839   --管理进程端口号
DYNAMICPORTLIST 7840-7850 --动态端口列表，给接收进程以及ORACLE DIRECTOR MANAGER使用
AUTOSTART EXTRACT *
AUTORESTART EXTRACT *
PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 7
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45
c.GGSCI>start mgr --启动mgr
d.GGSCI>info mgr --查看mgr使用的端口
Manager is running (IP port goldengate1.localdomain.7839).
7.配置源端HR Schema的进程
a.select owner||'.'||table_name table_name,logging from dba_tables where owner='HR'; --查看是否HR下所有表都为logging
b.Alter table HR.* logging  --将表都设置成logging
8.配置表级附加日志
a.GGSCI>dblogin userid goldengate,password goldengate
b.info trandata hr.*  --查看HR下所有表是否添加附加日志
c.add trandata hr.*  --为HR下所有表添加附加日志
9.配置源端抓取日志
a.add extract ext_demo, tranlog, begin now, threads 1 --tranlog:抓取来源redo日志，begin now：执行add extract命令后开始抓取,threads 1：num of redo threads
b.info all  --查看extract进程已经添加，默认stopped
10.添加源端的队列文件
a.add EXTTRAIL ./dirdat/r1,extract ext_demo,megabytes 100 --./dirdat/r1:TRAIL文件存放目录及文件前缀名,extract ext_demo：制定extract进程为ext_demo，大小为100M
11.源端抓取进程的配置
a.编辑抓取进程的参数
GGSCI>edit param ext_demo
EXTRACT ext_demo
Setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK) --NLS_LANG=语言_地区.数据库字符集，可以从nls_database_parameters表查询,NLS_LANGUAGE_NLS_TERRITORY.NLS_CHARACTERSET
Setenv (ORACLE_SID=ORCL)
userid goldengate,password goldengate
REPORTCOUNT EVERY 1 MINUTES,RATE
numfiles 5000
DISCARDFILE ./dirrpt/ext_demo.dsc,APPEND,MEGABYTES 1000
DISCARDROLLOVER AT 3:00
exttrail ./dirdat/r1,megabytes 100
dynamicresolution
TRANLOGOPTIONS EXCLUDEUSER goldengate
#TRANLOGOPTIONS convertues2elobs
--ASM需要配置查找日志（方法1但有些版本不支持,11.2.0.1测试不支持）
#TRANLOGOPTIONS DBLOGREADER
--ASM需要配置（方法2，比1复杂些）
******************************************************************************
*oracle用户的tnsname.ora下添加asm的本地服务名
*ASM =
* (DESCRIPTION =
*    (ADDRESS = (PROTOCOL = TCP)(HOST = goldengate1.localdomain)(PORT = 1521))
*    (CONNECT_DATA =
*      (SERVER = DEDICATED)
*      (SERVICE_NAME = +ASM)
*      (INSTANCE_NAME = +ASM)
*    )
* )
********************************************************************************
#TRANLOGOPTIONS ASMUSER SYS@+ASM,ASMPASSWORD oracle
#TRANLOGOPTIONS ALTARCHIVELOGDEST primary instance ORCL +DATA/archivelog
TABLE HR.*;    --HR下所有表*最后有个分号
b.启动抓取进程
GGSCI>start ext_demo
12.定义data pump进程(将抓取进程写的exttrail传到目标主机，主要负责TCP/IP通讯)
a.add extract dpe_demo, exttrailsource ./dirdat/r1
13.定义remotetrail进程(定义datapump进程的输出)
a.Add rmttrail ./dirdat/t1,EXTRACT dpe_demo,MEGABYTES 100 --./dir/dat/t1:用路径和trail文件的前缀标识一个rmttrail，EXTRACT dpe_demo:制定该rmttrail给哪个datapump进程使用
14.编辑与启动dpe_demo
a.配置dpe_demo参数
GGSCI>edit param dpe_demo
extract dpe_demo
dynamicresolution
passthru
rmthost 192.168.56.102,mgrport 7839,Compress          --目标IP，目标主机manager进程端口号，压缩传输
rmttrail ./dirdat/t1
numfiles 5000
Table HR.*;    --HR下所有表*最后有个分号
b.启动data pump进程(前提，目标主机启动mgr进程，且防火墙允许两端端口)
GGSCI>Start dpe_demo
******************
* 出现任何问题，无法启动所有进程，查看日志 vi ggserr.Log
******************
15.源端运行事务.(如update Table)后，查看是否生成dirdat
Sql>Update ...
[goldengate]$ ll dirdat             --查看大小是否为非0 (源)
[goldengate]$ ls -l dirdat          --查看目标段是否生成(目)
16.目标端创建表空间与用户(如果第3步做过则跳过)
17.配置replicat进程与checkpoint表(目标端)
a.为replicat进程创建checkpoint表
./ggsci
GGSCI>dblogin userid goldengate,Password goldengate
GGSCI>Add checkpointtable goldengate.rep_demo_ckpt
b.创建replicat进程
GGSCI>Add replicat rep_demo,exttrail ./dirdat/t1,checkpointtable goldengate.rep_demo_ckpt
c.定义replicate进程参数
GGSCI>edit param rep_demo
REPLICAT rep_demo
setenv (ORACLE_SID=remote)
setenv (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")
USERID goldengate,Password goldengate
REPORTCOUNT EVERY 30 MINUTES,RATE
REPERROR Default,ABEND
numfiles 5000
--HANDLECOLLISIONS
assumetargetdefs
DISCARDFILE ./dirrpt.rep_demo.dsc,APPEND,MEGABYTES 1000
ALLOWNOOPUPDATES
Map HR.*,TARGET HR.*;

18.目标端数据初始化
使用expdp/impdp方式导出与导入
a.源端数据导出
mkdir -p /u01/app/backup
chown -R oracle:dba /u01/app/backup
chmod 775 -R /u01/app/backup
SQL>create or replace directory backup as '/u01/app/backup';
SQL>Select current_scn From v$database; --326819
expdp hr/oracle directory=backup dumpfile=hr.dmp logfile=hrexpdp.log tables=HR.EMPLOYEE,HR.DEPT flashback_scn=32689
b.将dmp文件scp到目标端
scp hr.dmp 192.168.56.102:/u01/app/backup
c.目标端数据导入
mkdir -p /u01/app/backup
chown -R oracle:dba /u01/app/backup
chmod 775 -R /u01/app/backup
SQL>create or replace directory backup as '/u01/app/backup';
impdp hr/oracle directory=backup dumpfile=hr.dmp logfile=hrimpdp.log tables=HR.EMPLOYEE,HR.DEPT table_exists_action=replace;

19.使用scn号启动replicat进程
start rep_demo,aftercsn 326819
20.测试
a.源端 Update Table
b.目标端检查该表是否被更新

以上安装部分代码参考甲骨论的相关视频完成
整合了ASM的goldengate配置
以及expdp/impdp方式对目标端进行数据初始化

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15810196/viewspace-1172889/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15810196/viewspace-1172889/