ogg的简单配置


GoldenGate安装用户
安装目录(新建)
数据库用户(新建)
数据库表空间(新建)
初始化方案
网络传输端口
源端OGG版本
目标端OGG版本
是否选择支持DDL操作
需要同步复制的用户或表
每天产生的日志量
复制用户
其他特殊要求

用户对照关系表

默认所有用户均对应名字相同。

生产系统环境准备

归档空间调整

无需调整

备份脚本调整

备份策略需要调整，以免删除GoldenGate进程所需的归档。

GoldenGate文件系统要求

根据现有数据库，需要GoldenGate文件系统/ogg大小为100GB，归档目录100GB，

要求都放在共享磁盘上，使用双机管理。

生产临时空间要求

无要求

网络端口要求

以下均为TCP端口，要求如下：

开放1521、7809、7840-7850

数据库要求及调整

在GoldenGate复制迁移过程中尽量不做DDL操作，比如表字段修改等等，否则GoldenGate复制报错，或者配置DDL复制

打开Supplemental Logging

开启库级附加日志，一般情况下只需要打开最小附加日志。

alter database add supplemental log data ;

alter database add supplemental log data (primary key, unique,foreign key) columns;

alter system switch logfile;

打开强制日志模式

开启force logging强制日志模式

ALTER DATABASE FORCE LOGGING;

SELECT FORCE_LOGGING FROM V$DATABASE;

ALTER SYSTEM SWITCH LOGFILE;

打开数据库归档

OGG要求数据库归档打开

alter system set log_archive_dest_1='LOCATION=/arch' scope=both sid='*';

shutdown immediate;

startup mount

alter database archivelog;

alter database open;

alter system set control_file_record_keep_time=30 scope=both sid='*';

配置自动清理归档日志脚本：

vi /home/oracle/rm_arch.sh

source /home/oracle/.bash_profile

export ORACLE_HOME=/ora_data/u01/app/oracle/product/11.2.0/db_1

export NLS_DATE_FORMAT='YYYYMMDD HH24:MI:SS';

rman target / <<EOF

delete force noprompt archivelog until time "sysdate-2";

exit;

EOF

crontab -e

0 2 * * * /home/oracle/rm_arch.sh

打开DDL记录参数enable_ddl_logging

enable_ddl_logging用于记录所有DDL操作

alter system set enable_ddl_logging=true scope=both sid='*';

OGG软件安装

压缩介质，并安装在/ogg

cd /ogg

./ggsci

create subdirs

创建GoldenGate用户

在生产库中执行：创建GoldenGate复制用户。

create tablespace ogg datafile '/ora_data/u01/app/oracle/oradata/aaa3/ogg01.dbf' size 100m ;

create user goldengate identified by "welcome1" default tablespace ogg;

grant resource, connect, dba to goldengate;

在目标库中执行：创建GoldenGate复制用户。

create tablespace ogg datafile '/ora_data/ogg01.dbf' size 100m ;

create user goldengate identified by "welcome1" default tablespace ogg;

grant resource, connect, dba to goldengate;

修改enable_goldengater_replication参数

启用GoldenGate参数，不管在classic或integrated capture模式下都要开启以下参数。

alter system set enable_goldengate_replication=true scope=both sid='*';

添加SYS.SEQ$ 附加日志

ALTER TABLE SYS.SEQ$ ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

环境变量配置

在GoldenGate软件安装的操作系统用户下，默认可使用oracle用户，配置用户环境变量

源端：

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

export GGS_HOME=/ora_data/ogg

export PATH=$GGS_HOME:$PATH

export LD_LIBRARY_PATH=$GGS_HOME:$LD_LIBRARY_PATH

alias ggsci="cd $GGS_HOME;./ggsci"

目标端：

export GGS_HOME=/ogg/ogg11

export PATH=$GGS_HOME:$PATH

export LD_LIBRARY_PATH=$GGS_HOME:$LD_LIBRARY_PATH

alias ggsci="cd $GGS_HOME;./ggsci"

灾备环境准备

灾备参数调整

清理原环境：

灾备表空间创建

参照生产环境表空间实际使用大小，创建相应表空间：

SQL> select tablespace_name,sum(bytes/1024/1024/1024) from dba_segments group by tablespace_name order by 2;

文件系统要求

根据现有数据库，需要GoldenGate文件系统/ogg大小为100GB，要求都放在共享磁盘上，使用双机管理。

OGG软件安装

安装目录为：/ogg

配置数据复制

生产库执行sequence.sql

在生产库执行，在数据库上执行sequence.sql脚本

oracle@BJLT-HYWG-SZ-MDCC01:/ogg> sqlplus ' / as sysdba'

SQL*Plus: Release 11.2.0.3.0 Production on 星期三 5月 24 23:23:31 2017

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,

OLAP, Data Mining, Oracle Database Vault and Real Application Testing options

SQL> @sequence.sql

Please enter the name of a schema for the GoldenGate database objects:

goldengate

Setting schema name to GOLDENGATE

UPDATE_SEQUENCE STATUS:

Line/pos

--------------------

Error

-----------------------------------------------------------------

No errors

GETSEQFLUSH

Line/pos

--------------------

Error

-----------------------------------------------------------------

No errors

SEQTRACE

Line/pos

--------------------

Error

-----------------------------------------------------------------

No errors

REPLICATE_SEQUENCE STATUS:

Line/pos

--------------------

Error

-----------------------------------------------------------------

No errors

STATUS OF SEQUENCE SUPPORT

--------------------------------------------------------------

SUCCESSFUL installation of Oracle Sequence Replication support

现生产OGG参数配置

以下操作在生产端执行：

Global参数

edit params ./GLOBALS

GGSCHEMA goldengate

Manager进程参数

view params mgr

PORT 7809

DYNAMICPORTLIST 7840-7850

AUTOSTART ER *

AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3

userid goldengate, password AACAAAAAAAAAAAIARIXFKCQBMFIGFARA, encryptkey default

PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 2

--PURGEDDLHISTORY MINKEEPDAYS 3,MAXKEEPDAYS 7

--PURGEMARKERHISTORY MINKEEPDAYS 3,MAXKEEPDAYS 7

LAGREPORTHOURS 1

LAGINFOMINUTES 30

LAGCRITICALMINUTES 45

Goldengate 用户密码生成：encrypt password welcome1,ENCRYPTKEY default;

Capture进程参数

view params exta

EXTRACT exta

setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

setenv (ORACLE_HOME=/ora_data/u01/app/oracle/product/11.2.0/db_1)

setenv (ORACLE_SID=aaa3)

userid goldengate, password AACAAAAAAAAAAAIARIXFKCQBMFIGFARA, encryptkey default

REPORTCOUNT EVERY 1 MINUTES, RATE

DISCARDFILE ./dirrpt/exta.dsc,APPEND,MEGABYTES 1024

TRANLOGOPTIONS EXCLUDEUSER GOLDENGATE

TRANLOGOPTIONS DBLOGREADER

--TRANLOGOPTIONS CONVERTUCS2CLOBS

--TRANLOGOPTIONS RAWDEVICEOFFSET 0

--THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 60000

DBOPTIONS ALLOWUNUSEDCOLUMN

FETCHOPTIONS USESNAPSHOT

FETCHOPTIONS fetchpkupdatecols

FETCHOPTIONS suppressduplicates

WARNLONGTRANS 2h,CHECKINTERVAL 300s

EXTTRAIL ./dirdat/ea

GETTRUNCATES

TABLE AAA3.*;

SEQUENCE AAA3.*;

Pump进程参数

EXTRACT dpa

setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

setenv (ORACLE_HOME=/ora_data/u01/app/oracle/product/11.2.0/db_1)

setenv (ORACLE_SID=aaa3)

userid goldengate, password AACAAAAAAAAAAAIARIXFKCQBMFIGFARA, encryptkey default

RMTHOST 10.2.36.106 , MGRPORT 7809, compress

PASSTHRU

numfiles 10000

RMTTRAIL ./dirdat/ea

TABLE AAA3.*;

SEQUENCE AAA3.*;

添加表级附加日志

生产库中执行

dblogin userid goldengate,password AACAAAAAAAAAAAIARIXFKCQBMFIGFARA, encryptkey default

add trandata AAA3.*

增加extract进程

（在生产库中执行）

ADD extract exta, TRANLOG, THREADS 1, BEGIN now

add exttrail ./dirdat/ea,extract exta,megabytes 500

add extract dpa,exttrailsource ./dirdat/ea

add rmttrail ./dirdat/ea,extract dpa,megabytes 500

启动extract进程

（在生产库中执行）

start mgr

start exta

info exta

send exta status

记录exta进程启动时间：

2018-04-02 23:52:23 INFO OGG-00546 Oracle GoldenGate Capture for Oracle, exta.prm: Default thread stack size: 10485760.

2018-04-02 23:52:23 INFO OGG-01515 Oracle GoldenGate Capture for Oracle, exta.prm: Positioning to begin time 2018-4-2 下午11:52:02.

2018-04-02 23:52:24 INFO OGG-01516 Oracle GoldenGate Capture for Oracle, exta.prm: Positioned to (Thread 1) Sequence 7082, RBA 2268688, SCN 0.0, 2018-4-2 下午11:52:02.

2018-04-02 23:52:24 INFO OGG-00993 Oracle GoldenGate Capture for Oracle, exta.prm: EXTRACT EXTA started.

2018-04-02 23:52:24 INFO OGG-01052 Oracle GoldenGate Capture for Oracle, exta.prm: No recovery is required for target file ./dirdat/ea000000, at RBA 0 (file not opened).

2018-04-02 23:52:24 INFO OGG-01478 Oracle GoldenGate Capture for Oracle, exta.prm: Output file ./dirdat/ea is using format RELEASE 11.2.

2018-04-02 23:52:24 INFO OGG-01517 Oracle GoldenGate Capture for Oracle, exta.prm: Position of first record processed for Thread 1, Sequence 7082, RBA 2268688, SCN 0.59337746, 2018-4-2 下午11:52:04.

select START_TIME from v$transaction where start_time < '2017-04-16 14:29:22';

创建用户及表空间

（在目标库192.168.40.38/39/40中执行）

按照生产的结构，按照表空间实际使用量，创建表空间，参考“准生产表空间创建”章节

对象权限脚本：

生成创建用户脚本及权限脚本：（略）

数据初始化

使用expdp + impdp方式逻辑导出导入方式初始化数据。

SQL> select owner,sum(bytes/1024/1024/1024) from dba_segments group by owner;

OWNER SUM(BYTES/1024/1024/1024)

------------------------------ -------------------------

MDSYS .072509766

OUTLN .000549316

CTXSYS .003540039

OLAPSYS .005004883

AAA3 5.69433594

SYSTEM .030090332

EXFSYS .003540039

APEX_030200 .082458496

SCOTT .000305176

DBSNMP .001159668

ORDSYS .000427246

OWNER SUM(BYTES/1024/1024/1024)

------------------------------ -------------------------

SYSMAN .065612793

XDB .123962402

ORDDATA .013244629

SYS 3.08581543

WMSYS .003417969

16 rows selected.

select owner,sum(bytes/1024/1024/1024) from dba_segments where owner in

('AAA3')

group by owner order by 2;

OWNER SUM(BYTES/1024/1024/1024)

------------------------------ -------------------------

AAA3 5.69433594

select owner,count(*) from dba_objects where owner in

('AAA3')

group by owner

order by 2;

OWNER COUNT(*)

------------------------------ ----------

AAA3 117

SQL> col current_scn for 99999999999999999

SQL> select current_scn from v$database;

CURRENT_SCN

------------------

99091348

nohup impdp parfile=imp1.par &

userid=goldengate/welcome1

NETWORK_LINK=aaa3

schemas=aaa3

FLASHBACK_SCN=99091348

parallel=2

cluster=N

exclude=TABLE_STATISTICS,INDEX_STATISTICS

收集用户及系统统计信息：

exec dbms_stats.gather_schema_stats(ownname=>'AAA3',estimate_percent=>30, method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO' , degree=>2, granularity=>'ALL' ,cascade=>TRUE,no_invalidate=>false);

--exec DBMS_STATS.DELETE_DICTIONARY_STATS;

--EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

--EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

禁用触发器

（在灾备库中执行）

生成禁用触发器脚本：

set linesize 180 pagesize 0

spool disable_trigger.sql

select 'alter trigger '||owner||'.'||trigger_name||' disable;'

from dba_triggers

where table_owner in ('AAA3')

and status='ENABLED'

order by owner;

spool off

生成启用触发器脚本：

set linesize 180 pagesize 0

spool enable_trigger.sql

select 'alter trigger '||owner||'.'||trigger_name||' enable;'

from dba_triggers

where table_owner in ('AAA3')

and status='ENABLED'

order by owner;

spool off

禁用外键

（在灾备库中执行）

生成禁用外键的脚本：

set linesize 180 pagesize 0

spool disable_ref.sql

select 'alter table '||owner||'.'||table_name||' disable constraint '||constraint_name||';'

from dba_constraints

where constraint_type='R'

AND OWNER IN ('AAA3')

and status='ENABLED'

order by owner;

spool off

生成启用外键的脚本：

set linesize 180 pagesize 0

spool enable_ref.sql

select 'alter table '||owner||'.'||table_name||' enable constraint '||constraint_name||';'

from dba_constraints

where constraint_type='R'

AND OWNER IN ('AAA3')

and status='ENABLED'

order by owner;

spool off

锁定灾备数据库用户

停监听，锁定灾备数据库用户。

准生产OGG参数配置

（在灾备库中执行）

Global参数

edit params ./GLOBALS

GGSCHEMA goldengate

CHECKPOINTTABLE goldengate.ckpt

Manager进程参数

view params mgr

PORT 7809

DYNAMICPORTLIST 7840-7850

--AUTOSTART ER *

--AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3

userid goldengate, password AACAAAAAAAAAAAIARIXFKCQBMFIGFARA, encryptkey default

PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 2

--PURGEDDLHISTORY MINKEEPDAYS 3,MAXKEEPDAYS 7

--PURGEMARKERHISTORY MINKEEPDAYS 3,MAXKEEPDAYS 7

LAGREPORTHOURS 1

LAGINFOMINUTES 30

LAGCRITICALMINUTES 45

ACCESSRULE, PROG *, IPADDR *, ALLOW

2018-04-03 00:26:25 WARNING OGG-00936 Access denied (request from 10.2.36.102, rule #0).

Delivery进程参数

REPLICAT repa

setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

setenv (ORACLE_HOME=/home/oracle11g/product/11g)

setenv (ORACLE_SID=ora10g)

userid goldengate, password AACAAAAAAAAAAAIARIXFKCQBMFIGFARA, encryptkey default

DBOPTIONS DEFERREFCONST SUPPRESSTRIGGERS

ASSUMETARGETDEFS

ALLOWNOOPUPDATES

GETTRUNCATES

BATCHSQL

--HANDLECOLLISIONS

NUMFILES 2000

--FETCHOPTIONS FETCHPKUPDATECOLS

REPORT AT 01:59

REPORTCOUNT EVERY 30 MINUTES, RATE

REPERROR DEFAULT, ABEND

DISCARDFILE ./dirrpt/repa.dsc, APPEND, MEGABYTES 1024

DISCARDROLLOVER AT 02:30

--MAPEXCLUDE PLM80.PLAN_TABLE

MAP AAA3.*, target AAA3.*, filter ( @GETENV ("TRANSACTION","CSN") > 45386411 );

添加CHECKPOINTTABLE

（在灾备库中执行）

dblogin userid goldengate,password AACAAAAAAAAAAAIARIXFKCQBMFIGFARA, encryptkey default

delete CHECKPOINTTABLE goldengate.ckpt

ADD CHECKPOINTTABLE goldengate.ckpt

增加Delivery进程

（在灾备库中执行）

./ggsci

dblogin userid goldengate,password AACAAAAAAAAAAAIARIXFKCQBMFIGFARA, encryptkey default

add replicat repa,exttrail ./dirdat/ea,checkpointtable goldengate.ckpt

启动Replicat进程

（在灾备库中执行）

修改CSN值，启动repa进程

GGSCI (BJ-APN-1) 6> view params repa

REPLICAT repa

setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

setenv (ORACLE_HOME=/home/oracle11g/product/11g)

setenv (ORACLE_SID=ora10g)

userid goldengate, password AACAAAAAAAAAAAIARIXFKCQBMFIGFARA, encryptkey default

DBOPTIONS DEFERREFCONST SUPPRESSTRIGGERS

ASSUMETARGETDEFS

ALLOWNOOPUPDATES

GETTRUNCATES

BATCHSQL

--HANDLECOLLISIONS

NUMFILES 2000

--FETCHOPTIONS FETCHPKUPDATECOLS

REPORT AT 01:59

REPORTCOUNT EVERY 30 MINUTES, RATE

REPERROR DEFAULT, ABEND

DISCARDFILE ./dirrpt/repa.dsc, APPEND, MEGABYTES 1024

DISCARDROLLOVER AT 02:30

--MAPEXCLUDE PLM80.PLAN_TABLE

MAP AAA3.*, target AAA3.*, filter ( @GETENV ("TRANSACTION","CSN") > 99091348 );

启动进程：

start repa

ElanJie

关注

1
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
ogg的简单配置

目录1. 文档控制. ii修改记录.. ii分发者.. ii审阅记录.. ii相关文档.. ii2. 目录 iii3. GoldenGate环境调研. 4生产环境概括... 4GoldenGate软件调研... 4用户对照关系表... 44. 生产系统环境准备. 4归档空间调整... 4备份脚本调整....
复制链接

扫一扫