OGG12 ORACLE11g RAC 到 ORACLE11g 单机的DML同步

OGG12 ORACLE11g RAC 到 ORACLE11g 单机的DML同步

-------------------------------
系统环境         
源库Oracle
Redhat 6.4         
Oracle 11.2.0.4 RAC
IP:
Ogg: Version 12.1.2.1.0
NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

目标库Oracle
CentOS 6.5         
Oracle 11.2.0.4 单机
IP：
Ogg: Version 12.1.2.1.0
NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
---------------------------------

OGG环境配置
1.安装ogg
mkdir /backup/ogg
chmod +x 121210_fbo_ggs_Linux_x64_shiphome.zip 
unzip 121210_fbo_ggs_Linux_x64_shiphome.zip 
export DISPLAY=IP:0.0
cd fbo_ggs_Linux_x64_shiphome/
cd Disk1/
 ./runInstaller 
-----------------------------------------

2.配置环境变量

vi ~/.bash_profile 
export PATH=/usr/lib64/qt-3.3/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin:/bin:$ORACLE_HOME/bin:/backup/ogg
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib:lib:/usr/lib:/backup/ogg
source ~/.bash_profile 
----------------------------------------

3.源端数据库设置
开归档
--alter database archivelog;
打开强制日志
alter database force logging;
打开附加日志
alter database add supplemental log data;
alter database add supplemental log data (primary key,unique,foreign key) columns;
11.2.0.4版本所需参数
SQL> alter system set enable_goldengate_replication=true scope=both;
建ogg用户
SQL> conn / as sysdba
Connected.
SQL> create user oggadmin identified by oggadmin123;
SQL> grant dba to oggadmin123;
添加角色（必须在OGG目录）
SQL> conn / as sysdba
Connected.
SQL> @role_setup
GGS Role setup script
This script will drop and recreate the role GGS_GGSUSER_ROLE
SQL> grant GGS_GGSUSER_ROLE TO oggadmin；
安装sequence支持
SQL> conn / as sysdba
Connected.
SQL> @sequence
Please enter the name of a schema for the GoldenGate database objects:填写OGG用户名：
oggadmin
Setting schema name to OGGADMIN
SQL> grant execute on oggadmin.updatesequence to oggadmin;
SQL> alter table sys.seq$ add supplemental log data (primary key) columns;
------------------------------------------

4.目标端数据库设置
11.2.0.4版本所需参数
SQL> alter system set enable_goldengate_replication=true scope=both;
建ogg用户
SQL> conn / as sysdba
Connected.
SQL> create user oggadmin identified by oggadmin123;
SQL> grant dba to oggadmin123;
------------------------------------------

5.源端OGG配制
设置全局参数
GGSCI (cktsdb1) 2>  view params ./GLOBALS
GGSCHEMA oggadmin
配置管理进程
GGSCI (cktsdb1) 11> edit param mgr
PORT 7809
dynamicportlist 7840-7914
--autorestart extract *,retries 5,waitminutes 7
purgeoldextracts ./dirdat/*,usecheckpoints,minkeepdays 10
lagreporthours 1
laginfominutes 30
lagcriticalminutes 45
开启表级别日志补充
GGSCI (cktsdb1) 12> dblogin userid oggadmin,password oggadmin123
Successfully logged into database.
GGSCI (cktsdb1 as oggadmin@ckts1) 13> add trandata tssh.*
GGSCI (cktsdb1 as oggadmin@ckts1) 14> add trandata test.*
Logging of supplemental redo data enabled for table TEST.T1.
TRANDATA for scheduling columns has been added on table 'TEST.T1'.
GGSCI (cktsdb1 as oggadmin@ckts1) 15> info trandata test.*

配置提取进程参数
GGSCI (cktsdb1 as oggadmin@ckts1) 16> edit param ex1
extract ex1
tranlogoptions dblogreader --RAC ASM提取日志相关参数
exttrail dirdat/ex
userid oggadmin,password oggadmin123
gettruncates
reportcount every 30 minutes,rate
discardfile /backup/ogg/dirrpt/ex1.dsc,append,megabytes 1024
discardrollover AT 3:00
warnlongtrans 2h,checkinterval 3m
dynamicresolution
dboptions allowunusedcolumn
fetchoptions nousesnapshot
fetchoptions fetchpkupdatecols
--tranlogoptions convertucs2clobs
--threadoptions maxcommitpropagationdelay 60000 iolatency 60000
table tssh.*;
table test.*;

创建提取进程ex1,源为RAC，此处设置参数threads 2
GGSCI (cktsdb1 as oggadmin@ckts1) 17> add extract ex1,tranlog,begin now,threads 2
EXTRACT added.
为提取进程ex1指定本地trail文件
GGSCI (cktsdb1 as oggadmin@ckts1) 18> add exttrail dirdat/ex,extract ex1 megabytes 5
EXTTRAIL added.

配置投递进程参数
GGSCI (cktsdb1 as oggadmin@ckts1) 20> edit params dp1
extract dp1
userid oggadmin,password oggadmin123
rmthost 目标库IP ,mgrport 7809,compress
passthru
numfiles 5000
dynamicresolution
rmttrail dirdat/rt
table tssh.*;
table test.*;
创建投递进程dp1,指定本地trail文件位置
GGSCI (cktsdb1 as oggadmin@ckts1) 21> add extract dp1 exttrailsource dirdat/ex
EXTRACT added.
为投递进程dp1,指定目标端trail文件位置
GGSCI (cktsdb1 as oggadmin@ckts1) 22> add rmttrail dirdat/rt,extract dp1
RMTTRAIL added.
---------------------------------------------

6.目标端OGG配制
设置全局参数
GGSCI (cktstest2) 1>  edit params ./GLOBALS
GGSCHEMA oggadmin
配置管理进程
GGSCI (cktstest2) 11> edit param mgr
PORT 7809
userid oggadmin,password oggadmin123
dynamicportlist 7840-7914
autorestart extract * ,retries 5,waitminutes 3
purgeoldextract2 dirdat/*,usecheckpoints,minkeepdays 5
lagreporthours 1
laginfominutes 30
lagcritlcalminutes 45
创建检查点表
GGSCI (cktstest2) 12>dblogin userid oggadmin,password oggadmin123
GGSCI (cktstest2) 13>add checkpointtable oggadmin.checkpointtable
在全局环境中添加检查点表
GGSCI (cktstest2) 14>  edit params ./GLOBALS
GGSCHEMA oggadmin
checkpointtable oggadmin.checkpointtable
配制复制进程rt1
GGSCI (cktstest2) 15> edit params rt1
replicat rt1
userid oggadmin,password oggadmin123
dboptions deferrefconst
gettruncates
report AT 06:00
reportcount every 30 minutes,rate
reportrollover AT 02:00
reperror default,abend
--handlecollisions --初始化处理唯一数据冲突
allownoopupdates
discardfile dirrpt/repsa.dsc,append,megabytes 1024M
discardrollover AT 02:00
assumetargetdefs
mapexclude tssh.SCHEDULER$_JOB_ARG --排除表
map tssh.*,target tssh.*;
map test.*,target test.*;
创建复制进程rt1，设置读取trail文件路径及检查点表
add replicat rt1,exttrail dirdat/rt,checkpointtable oggadmin.checkpointtable
-----------------------------

7.初始化数据库
源库：
查看SCN
select current_scn from v$database;
CURRENT_SCN
-----------
76141


导出数据
expdp system/xxxx directory=dpckts dumpfile=dp_ckts_ogg.dmp logfile=dp_ckts_ogg.log schemas=tssh,test flashback_scn=76141


目标库：
impdp system/xxxx directory=dpdata dumpfile=dp_ckts_ogg.dmp logfile=cktsogg.log schemas=tssh,test
禁用job,trigger,级联外键删除等
禁用或者删除job(JOB的拥有者才能执行)
select * from dba_jobs
exec dbms_job.broken(job#,ture)
exec dbms_job.remove(job)
commit;
禁用trigger
select * from dba_triggers s where s.owner='TSSH'
alter trigger tssh.trigger_name disable;
禁用级联外键删除
select * from dba_constraints a where a.owner='TSSH' and a.constraint_type='R'
alter table tssh.table disable constraint constraint_name;
禁用schedule job
select * from dba_scheduler_jobs where owner='TSSH'
exec dbms_scheduler.disable(name=>job_name)
OGG12 ORACLE11g RAC 到 ORACLE11g 单机的DML同步
－－－－－－－－－－－－－－－－－－

8.启动源端进程
GGSCI (cktsdb1 as oggadmin@ckts1) 46> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING                                           
EXTRACT     STOPPED     DP1         00:00:00      01:52:52    
EXTRACT     STOPPED     EX1         00:00:00      02:01:34    
GGSCI (cktsdb1 as oggadmin@ckts1) 47> start ex1
Sending START request to MANAGER ...
EXTRACT EX1 starting
GGSCI (cktsdb1 as oggadmin@ckts1) 48> start dp1
Sending START request to MANAGER ...
EXTRACT DP1 starting
查看启动日志
tail -200f ggserr.log
------------------------------------------------------------

9.启动目标端进程
GGSCI (cktstest2) 16> info all
GGSCI (cktstest2) 17> start rt1 aftercsn 76141

查看启动日志
tail -200f ggserr.log

----------------------------------------------------------
10.验证数据
----------------------------------------------------------