Linux 环境下Oracle数据库 goldengate 单向数据同步基础配置，都为配过过程中操作，具体解释不多；
<源库>
--数据库基础配置：
SQL> select log_mode,force_logging from v$database;
LOG_MODE FORCE_
------------------------ ------
ARCHIVELOG YES
SQL> show parameter recyclebin
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
recyclebin string on
SQL> alter system set recyclebin=off scope=both;
System altered.
SQL> show parameter recyclebin
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
recyclebin string OFF
SQL> alter database add supplemental log data;
Database altered.
SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;
SUPPLEMENTAL_LOG
----------------
YES
-- 创建用户并授权
SQL> create user ggate identified by ****** default tablespace users;
User created.
SQL> grant connect,resource,unlimited tablespace to ggate;
Grant succeeded.
SQL> grant dba to ggate; --用于测试，为方便，此处根据需求分配权限最好
Grant succeeded.
--安装ggate
1、解压gg包
2、配置环境变量：/home/oracle/.bash_profile
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib:/lib:/usr/lib:/u01/db/oracle/ggate
3、是环境变量生效：
[oracle@P25-DGL 16:34:50 ~]$ source .bash_profile
4、安装gg软件
[oracle@P25-DGL 16:42:22 ggate]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x64, 64bit (optimized), Oracle 10g on Apr 23 2012 07:30:46
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (P25-DGL) 1> create subdirs
Creating subdirectories under current directory /u01/db/oracle/ggate
Parameter files /u01/db/oracle/ggate/dirprm: already exists
Report files /u01/db/oracle/ggate/dirrpt: created
Checkpoint files /u01/db/oracle/ggate/dirchk: created
Process status files /u01/db/oracle/ggate/dirpcs: created
SQL script. files /u01/db/oracle/ggate/dirsql: created
Database definitions files /u01/db/oracle/ggate/dirdef: created
Extract data files /u01/db/oracle/ggate/dirdat: created
Temporary files /u01/db/oracle/ggate/dirtmp: created
Stdout files /u01/db/oracle/ggate/dirout: created
--配置manager：
GGSCI (P25-DGL) 3> edit params mgr
PORT 7809
dynamicportlist 7801-7810
--配置抽取进程ext_lg1：
GGSCI (P25-DGL) 6> edit params ext_lg1
extract ext_lg1
userid ggate,password ggate
gettruncates
reportcount every 1 minutes,rate
numfiles 5000
discardfile /u01/db/oracle/ggate/dirrpt/ext_lg1.dsc,APPEND,MEGABYTES 1000
warnlongtrans 2h,checkinterval 3m
exttrail /u01/db/oracle/ggate/dirdat/prodtohis/p1
tranlogoptions convertucs2clobs
threadoptions maxcommitpropagationdelay 60000
dboptions allowunusedcolumn
dynamicresolution
table codb.*;
--添加抽取进程：
GGSCI (P25-DGL) 7> add extract ext_lg1,tranlog,begin now;
EXTRACT added.
GGSCI (P25-DGL) 8> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXT_LG1 00:00:00 00:00:03
GGSCI (P25-DGL) 10> add exttrail /u01/db/oracle/ggate/dirdat/prodtohis/p1,extract ext_lg1,megabytes 500
EXTTRAIL added.
--配置传送进程
GGSCI (P25-DGL) 16> edit params pump_lg1
extract pump_lg1
rmthost 10.6.13.111,mgrport 7809,compress
passthru
numfiles 50000
rmttrail /oracle/ggate/dirdat/fromprod/h1
dynamicresolution
table codb.*;
GGSCI (P25-DGL) 17> add extract pump_lg1,exttrailsource /u01/db/oracle/ggate/dirdat/prodtohis/p1
EXTRACT added.
GGSCI (P25-DGL) 18> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT_LG1 00:00:00 00:00:01
EXTRACT STOPPED PUMP_LG1 00:00:00 00:00:01
GGSCI (P25-DGL) 19> add rmttrail /oracle/ggate/dirdat/fromprod/h1,extract pump_lg1,megabytes 500
RMTTRAIL added.
GGSCI (P25-DGL) 20> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT_LG1 00:00:00 00:00:08
EXTRACT STOPPED PUMP_LG1 00:00:00 00:02:21
GGSCI (P25-DGL) 21> start extract pump_lg1
Sending START request to MANAGER ...
EXTRACT PUMP_LG1 starting
GGSCI (P25-DGL) 22> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT_LG1 00:00:00 00:00:07
EXTRACT RUNNING PUMP_LG1 00:00:00 00:02:41
配置完成，按照scn导出源数据库数据
[oracle@P25-DGL 18:12:35 ~]$ sqlplus sys/***** as sysdba;
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
4708773494
[oracle@18:27:23 ~]$ expdp \'sys/***** as sysdba\' directory=dir dumpfile=lgdb_scn.dmp schemas=codb FLASHBACK_SCN=4708773494;
<目标数据库>
创建gg用户：
SQL> create user ggate identified by ******* default tablespace users;
User created.
SQL> grant connect,resource,unlimited tablespace to ggate;
Grant succeeded.
SQL> grant execute on utl_file to ggate;
SQL> grant dba to ggate;
Grant succeeded.
2、安装gg：
GGSCI (P25-DGL) 1> create subdirs
3、配置checkpoint表
GGSCI (his-db) 6> edit params ./GLOBALS
checkpointtable ggate,ckpt
GGSCI (his-db) 8> dblogin userid ggate,password ******
Successfully logged into database.
GGSCI (his-db) 9> add checkpointtable ggate.ckpt
Successfully created checkpoint table ggate.ckpt.
4、将数据导入到目标数据库中
CREATE USER codb_all IDENTIFIED BY ******* DEFAULT TABLESPACE DATA_ALL;
[oracle@his-db ~]$ impdp system/******** directory=dir dumpfile=lgdb_scn.dmp remap_schema=codb:codb_all remap_tablespace=DATA_SPC:DATA_ALL remap_tablespace=INDX_SPC:DATA_ALL;
待数据导入完成后，禁用trigger、外键、job
禁止所有trigger
declare
v_sql varchar2(2000);
cursor c_trigger is select 'alter trigger ' || owner || '.' || trigger_name || ' disable' from dba_triggers where owner in ('CODB_ALL');
begin
open c_trigger;
loop
fetch c_trigger into v_sql;
exit when c_trigger%notfound;
execute immediate v_sql;
end loop;
close c_trigger;
end;
/
禁止所有外键
declare
v_sql varchar2(4000);
cursor c_ref is select 'alter table ' || owner || '.' || table_name || ' disable constraint ' || constraint_name from dba_constraints where constraint_type='R' and owner in ('CODB_ALL');
begin
open c_ref;
loop
fetch c_ref into v_sql;
exit when c_ref%notfound;
execute immediate v_sql;
end loop;
close c_ref;
end;
/
禁止所有job
查看：
SQL> select job_name,owner,program_name,program_owner,state,enabled from dba_scheduler_jobs where owner not in ('SYS','SYSTEM');
5、配置应用进程
GGSCI (his-db) 19> edit params re_prod
replicat re_prod
userid ggate,password ggate
setenv(NLA_LANG="zh_CN.GB18030")
reportcount every 30 minutes,rate
reperror default,abend
numfiles 50000
maxtransops 500000
grouptransops 10000
checkpointsecs 40
assumetargetdefs
discardfile /oracle/ggate/dirrpt/re_prod.dsc,append,megabytes 1000
gettruncates
allownoopupdates
handlecollisions
map codb.*, target codb_all.*;
GGSCI (his-db) 16> add replicat re_prod,exttrail /oracle/ggate/dirdat/fromprod/h1 checkpointtable ggate.ckpt
REPLICAT added.
GGSCI (his-db) 20> start re_prod, aftercsn 4708773494
Sending START request to MANAGER ...
REPLICAT RE_PROD starting
GGSCI (his-db) 21> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING RE_PROD 03:33:07 00:00:00
此时可以通过验证数据来测试同步是否正常实时；
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/21816168/viewspace-758157/ ，如需转载，请注明出处，否则将追究法律责任。
下一篇：
Goldengate trial队列维护
请登录后发表评论
登录
全部评论
