好几天没冒泡了,最近有点忙,编写一个DG容灾的项目文档,还好今天完成了。得空来论坛冒个泡。哈哈~下面给出一个测试环境同平台同数据库的oracle goldengate的搭建过程,希望对初学者有帮助!问当由于内容有点长,我只能分成量部分了。这一贴是上部分。下部分见贴:
[原创]同平台同数据库REDHAT+ORACLE11203+OGG的搭建过程[下][原创]
1.安装说明
两台主机相同平台,相同版本的oracle
操作系统版本:
[oracle@Oggs admin]$ cat /etc/redhat-release
Red Hat Enterprise Linux Server release 5.8 (Tikanga)
内核版本:
[oracle@Oggs ~]$ uname -a
Linux Oggs 2.6.32-300.10.1.el5uek #1 SMP Wed Feb 22 17:37:40 EST 2012 x86_64 x86_64 x86_64 GNU/Linux
oracle版本:
SQL> select * from v$version;
BANNER
---------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE11.2.0.3.0Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
2.安装准备
2.1创建goldengate表空间:
create tablespace GoldenGate
datafile '/s01/oracle/oradata/edison/GoldenGate.dbf'
size 50m
autoextend on
extent management local;
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
GOLDENGATE
2.2.源端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 TABLE TO GOLDENGATE;
GRANT EXECUTE ANY TYPE TO GOLDENGATE;
GRANT SELECT ANY TRANSACTION TO GOLDENGATE;
2.3.检查数据库是否归档模式:
SQL> select log_mode from v$database;
LOG_MODE
------------
NOARCHIVELOG
SQL>
改为归档模式:
SQL> alter system set log_archive_dest_1='LOCATION=/s01/oracle/oradata/arch' scope=both;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2232960 bytes
Variable Size 490737024 bytes
Database Buffers 339738624 bytes
Redo Buffers 2396160 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL>
2.4.配置环境变量:
linux下要给oracle和oracle goldengate用户配置环境变量LD_LIBRARY_PATH=$ORACLE_HOME/lib
2.5.创建goldengate的根目录:
[root@Oggs ~]# mkdir /goldengate
[root@Oggs ~]# chown -R oracle:oinstall /goldengate
2.6.查看补充日志是否打开:
SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;
SUPPLEME
--------
NO
打开源端的补充日志(DBA用户执行):
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Database altered.
SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;
SUPPLEME
--------
YES
注:打开补充日志最好在夜里业务很少的时候进行。如果是RAC需要在每个节点上都执行。完成后建议执行一次归档操作:
alter system archive log current;
3.goldengate源和目标端的安装:
3.1.源端安装goldengate:
上传软件Oracle GoldenGate V11.2.1.0.3 for Oracle 11g on Linux x86-64并将owner给oracle用户后oracle用户执行以下命令:
[oracle@Oggs goldengate]$ unzip Oracle\ GoldenGate\ V11.2.1.0.3\ for\ Oracle\ 11g\ on\ Linux\ x86-64.zip
[oracle@Oggs goldengate]$ tar -xvf fbo_ggs_Linux_x64_ora11g_64bit.tar
3.2.目标端也做同样的安装(目录都相同):
[root@Oggsgoldengate]# scp fbo_ggs_Linux_x64_ora11g_64bit.tar oggd:/goldengate/
[root@Oggs goldengate]# chown oracle:oinstall fbo_ggs_Linux_x64_ora11g_64bit.tar
[root@Oggs goldengate]# tar -xvf fbo_ggs_Linux_x64_ora11g_64bit.tar
3.3.测试goldengate控制台:
在安装goldengate的节点上以oracle用户运行:
[oracle@Oggs ~]$ cd /goldengate/
[oracle@Oggs goldengate]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.3 14400833 OGGCORE_11.2.1.0.3_PLATFORMS_120823.1258_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Aug 23 2012 20:20:21
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (Oggs) 1>
如果出现上面内容说明配置成功。
3.4.创建goldengate运行时目录:
分别在源端和目标端执行:
GGSCI (Oggs) 1> create subdirs
Creating subdirectories under current directory /goldengate
Parameter files /goldengate/dirprm: already exists
Report files /goldengate/dirrpt: created
Checkpoint files /goldengate/dirchk: created
Process status files /goldengate/dirpcs: created
SQL script files /goldengate/dirsql: created
Database definitions files /goldengate/dirdef: created
Extract data files /goldengate/dirdat: created
Temporary files /goldengate/dirtmp: created
Stdout files /goldengate/dirout: created
GGSCI (Oggs) 2>
3.5.设置goldengate manager参数:
分别在源端和目标端执行,设置goldengate参数如下:
GGSCI (Oggs) 2> edit params mgr
port 7839
DYNAMICPORTLIST 7840-7850
AUTOSTART EXTRACT *
AUTORESTART EXTRACT *
PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 7
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45
然后启动源端和目标端goldengate manager进程:
GGSCI (Oggs) 3> start mgr
Manager started.
GGSCI (Oggs) 4>
4.goldengate进程配置:
4.1.源端添加表级附加日志:
GGSCI (Oggs) 5> dblogin userid goldengate,password goldengate
Successfully logged into database.
GGSCI (Oggs) 8> add trandata OGGTEST.*
2014-11-08 08:37:07 WARNING OGG-00869 No unique key is defined for table 'TEST'. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
Logging of supplemental redo data enabled for table OGGTEST.TEST.
GGSCI (Oggs) 9>
为所有需要复制的用户下的表添加附件日志,并需要查看添加操作的日志,确保所有的表级附加日志添加成功。
4.2.源端创建capture进程:
GGSCI (Oggs) 9> add extract ext_app,tranlog,begin now
EXTRACT added.
GGSCI (Oggs) 10> add EXTTRAIL ./dirdat/r1, extract ext_app,MEGABYTES 100
EXTTRAIL added.
GGSCI (Oggs) 11>
注:单实例不需要加add extract threads子句;多于两个实例的RAC库,threads子句指明实际的线程数。
设置capture进程参数:
GGSCI (Oggs) 14> edit params ext_app
EXTRACT ext_app
setenv (NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252)
setenv (ORACLE_SID=edison)
userid goldengate,password goldengate
REPORTCOUNT EVERY 1 MINUTES, RATE
numfiles 5000
DISCARDFILE ./dirrpt/ext_app.dsc,APPEND,MEGABYTES 1000
DISCARDROLLOVER AT 3:00
exttrail ./dirdat/r1,megabytes 100
dynamicresolution
TRANLOGOPTIONS EXCLUDEUSER goldengate
TRANLOGOPTIONS convertucs2clobs
TABLE OGGTEST.*;
注:
如果主机上存在多个数据库实例,需要设置:Setenv(ORACLE_SID=sid_name)
NLS_LANG需要设置和数据库语言环境一致;
启动capture进程:
GGSCI (Oggs) 15> start ext_app
Sending START request to MANAGER ...
EXTRACT EXT_APP starting
GGSCI (Oggs) 16>
4.3.验证源端capture进程:
以oracle用户登录:
[oracle@Oggs goldengate]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.3 14400833 OGGCORE_11.2.1.0.3_PLATFORMS_120823.1258_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Aug 23 2012 20:20:21
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (Oggs) 1>
检查运行情况:
GGSCI (Oggs) 1> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT_APP 00:00:00 00:00:00
GGSCI (Oggs) 2>
GoldenGate mgr进程和ext_app进程应该为RUNNING状态;
GGSCI (oggs) 9> info ext_app
EXTRACT EXT_APP Last Started 2014-11-08 12:01 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:07 ago)
Log Read Checkpoint Oracle Redo Logs
2014-11-08 12:04:28 Thread 1, Seqno 73, RBA 25000960
SCN 0.845867 (845867)
GGSCI (oggs) 10> info ext_app
EXTRACT EXT_APP Last Started 2014-11-08 12:01 Status RUNNING
Checkpoint Lag 00:00:09 (updated 00:00:00 ago)
Log Read Checkpoint Oracle Redo Logs
2014-11-08 12:06:08 Thread 1, Seqno 73, RBA 33994752
SCN 0.845951 (845951)
GGSCI (oggs) 11>
多次运行info ext_app命令,返回的RBA参数应该是变化的说明Capture进程正常。
详细信息:
GGSCI (oggs) 11> info ext_app,details
ERROR: DETAILS is not a valid option.
GGSCI (oggs) 12> info ext_app,detail
EXTRACT EXT_APP Last Started 2014-11-08 12:01 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:09 ago)
Log Read Checkpoint Oracle Redo Logs
2014-11-08 12:08:24 Thread 1, Seqno 73, RBA 34150400
SCN 0.846037 (846037)
Target Extract Trails:
Remote Trail Name Seqno RBA Max MB
./dirdat/r1 0 23646860 100
Extract Source Begin End
/s01/oracle/oradata/edison/redo01.log 2014-11-08 11:44 2014-11-08 12:08
/s01/oracle/oradata/edison/redo01.log * Initialized * 2014-11-08 11:44
Not Available * Initialized * 2014-11-08 11:44
Current directory /goldengate
Report file /goldengate/dirrpt/EXT_APP.rpt
Parameter file /goldengate/dirprm/ext_app.prm
Checkpoint file /goldengate/dirchk/EXT_APP.cpe
Process file /goldengate/dirpcs/EXT_APP.pce
Stdout file /goldengate/dirout/EXT_APP.out
Error log /goldengate/ggserr.log
GGSCI (oggs) 13>
4.4.排除不复制的表:
用tableexlcude命令,必须写在table命令前面。在参数文件中增加:
TABLEEXCLUDE schema.table_name
4.5.中文表、中文字段处理:
NLS_LANG在GoldenGate中,抽取和复制必须设置为和数据库字符集一致:
SETENV (NLS_LANG = "AMERICAN_AMERICA.ZHS16GBK")
4.6.源端创建datapump进程:
GGSCI (Oggs) 5> add extract pmp_app, exttrailsource ./dirdat/r1
EXTRACT added.
GGSCI (Oggs) 6> add rmttrail ./dirdat/t1,EXTRACT pmp_app,MEGABYTES 100
RMTTRAIL added.
GGSCI (Oggs) 7>
注:exttrailsource参数指向要和Capture进程的EXTTRAIL参数一致;
GGSCI (Oggs) 16> edit params pmp_app
extract pmp_app
dynamicresolution
passthru
rmthost 192.168.56.102, mgrport 7839, compress
rmttrail ./dirdat/t1
numfiles 5000
TABLE OGGTEST.*;
注:rmttrail参数要和add rmttrail参数一致;
TABLE参数要和Capture进程的TABLE参数一致;
启动DataPump进程:
GGSCI (Oggs) 19> start pmp_app
Sending START request to MANAGER ...
EXTRACT PMP_APP starting
4.7.验证源端datapump进程:
oracle用户登录,
GGSCI (Oggs) 20> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT_APP 00:00:00 00:00:05
EXTRACT RUNNING PMP_APP 00:00:00 00:07:07
GGSCI (Oggs) 21> info pmp_app
EXTRACT PMP_APP Last Started 2014-11-08 09:14 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:09:01 ago)
Log Read Checkpoint File ./dirdat/r1000000
First Record RBA 0
GGSCI (Oggs) 22>