Goldengate 在 linux下Oracle 单相同步基础配置

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博客 ” ，链接：http://blog.itpub.net/21816168/viewspace-758157/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21816168/viewspace-758157/