oracle搭建ogg视频,Oracle goldengate搭建ogg

主库：

1.检查是否开启归档

select log_mode from gv$database;

archive log list; ----注意归档路径需要是共享路径

2.检查是否开启force logging及补充日志

select force_logging,supplemental_log_data_min,supplemental_log_data_all,flashback_on from v$database;

开启：

alter database force logging;

alter database add supplemental log data;

alter system archive log current;

3.对主库检查，ogg不允许：唯一索引的索引列的列定义允许为null的

select dic.table_owner,

dic.table_name,

dic.index_name,

di.uniqueness,

dic.column_name

from dba_ind_columns dic, dba_indexes di, dba_tab_columns dtc

where dic.table_owner = '自行添加用户' -----修改用户名

and dtc.OWNER = '自行添加用户' -----修改用户名

AND dic.table_owner = di.table_owner

and dic.TABLE_NAME = di.table_name

and dic.index_name = di.index_name

and di.uniqueness = 'UNIQUE'

and dtc.owner = di.table_owner

and dtc.TABLE_NAME = di.table_name

and dic.column_name = dtc.COLUMN_NAME

and dtc.nullable = ' Y '

and dic.TABLE_NAME = dtc.TABLE_NAME;

不应该返回行，如果返回了，修改：要么变为非唯一索引，要么在保留唯一索引的情况下，将列的定义置为 not null。

4.创建ogg用户，并授权

create user goldengate identified by goldengate default tablespace users;

grant dba to goldengate;

ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION = TRUE SCOPE=BOTH;

5.设置环境变量(oracle用户)

PATH=$ORACLE_HOME/bin:$PATH:$HOME/bin:/ggs

export LD_LIBRARY_PATH=/ggs:$ORACLE_HOME/lib

6.检查是否有nologing方式的表(ogg不支持nologing方式创建的表)

select owner,table_name,logging from dba_tables where logging='NO' AND owner='用户名';

修改为logging的表的语法：alter table 表名 logging;

注意：在ext进程的参数文件里添加 dboptions allownologging可以让ext进程继续运行，但是会导致数据丢失。

7.两个节点上传ogg介质并解压，授权/ggs目录给oracle

chown oracle:dba /ggs -R

su - oracle

cd /ggs

unzip p22575475_1121032_Linux-x86-64.zip

tar xvf fbo_ggs_Linux_x64_ora11g_64bit.tar

8.源端数据库添加表的补充日志

进入ogg安装路径：

cd /ggs

ggsci

dblogin userid goldengate password goldengate

create subdirs

add trandata lm.testogg

9.配置DDL复制

使用goldengate作为存储DDL objects的用户

给goldengate授权：

GRANT EXECUTE ON UTL_FILE TO goldengate;

10.配置GLOBALS文件

ggsci

edit param ./GLOBALS中加入：

GGSCHEMA goldengate

11.如果是10g需要停用 recyclebin，11g就不需要了

12.数据库执行：

退出所有的oracle连接后执行：

cd /ggs

sqlplus / as sysdba

@marker_setup.sql

@ddl_setup.sql

Please move GOLDENGATE to its own tablespace

@role_setup.sql

grant ggs_ggsuser_role to goldengate;

@ddl_enable.sql

13.如果是有灾备演练的需求，需要配置sequence同步

cd /ggs --ogg安装目录

sqlplus / as sysdba

@sequence.sql

GRANT EXECUTE on goldengate.updateSequence TO goldengate;

14.源端配置参数文件

su - grid

vi $ORACLE_HOME/network/admin/listener.ora

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = +ASM)

(ORACLE_HOME=/u01/app/11.2.0/grid)

(SID_NAME = +ASM1)

)

)

su - oracle

cd $ORACLE_HOME/network/admin

vi tnsnames.ora

ASM =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 186.168.100.3)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = +ASM)

(SID_NAME = +ASM1)

)

)

MGR:

ggsci

edit params mgr

======================================

port 7809

-- DYNAMICPORTLIST 7830-7835

autostart extract *

autorestart extract *, waitminutes 1, retries 60, RESETMINUTES 60

PURGEOLDEXTRACTS /ggs/dirdat/sd*, USECHECKPOINTS, MINKEEPHOURS 2

======================================

EXT:

add extract extfull, tranlog,threads 2,begin now ----主库为rac两个节点，如果是单机，不需要threads

edit param extfull

=================================================

extract extfull

setenv ( NLS_LANG = " AMERICAN_AMERICA.ZHS16GBK " )

TRANLOGOPTIONS ASMUSER SYS@ASM, ASMPASSWORD oracle

THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 80000 IOLATENCY 160000

DBOPTIONS ALLOWUNUSEDCOLUMN

userid goldengate, password goldengate

ddl include mapped

ddloptions addtrandata RETRYOP MAXRETRIES 1000 RETRYDELAY 10, REPORT

WARNLONGTRANS 1h, CHECKINTERVAL 5m

--每5分钟检测一次，对运行时间超过1小时的长事务，gg会记入抽取进程.rpt和ggserr.log,此参数为抽取进程的参数。

exttrail /ggs/dirdat/sd

gettruncates

dynamicresolution

NOCOMPRESSUPDATES

NOCOMPRESSDELETES

--tableexclude 'CIMS.cncdata';

--DDLERROR _SKIPDDL 738310

table LM.testogg;

--sequence CHARISMA_DEMO.*;

===============================================

15.添加trial文件，每个50M

add exttrail /ggs/dirdat/sd, extract extfull, MEGABYTES 50

16.添加传输进程

add extract dpfull exttrailsource /ggs/dirdat/sd

17.创建远程队列文件并将其指定给传输进程

add rmttrail /ggs/dirdat/td, extract dpfull, MEGABYTES 50

18.配置传输进程参数

edit param dpfull

==================================

extract dpfull

passthru

rmthost 186.168.100.22, mgrport 7809

rmttrail /ggs/dirdat/td

gettruncates

table LM.testogg;

--sequence CIMS.*;

===================================

19.从源端去一次scn号

select current_scn from v$database; --

20.按照上面查询出的scn导出数据

expdp sys/****** directory= dumpfile=%U.dmp logfile=.log schemas= parallel= flashback_scn=scn

21.启动mgr及抓取传输进程

备库：

1.创建ogg用户并授权

create user goldengatet identified by goldengatet default tablespace tbs_ogg;

grant dba to goldengatet;

exec dbms_streams_auth.grant_admin_privilege(grantee => 'GOLDENGATET',grant_privileges => true);

ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION = TRUE SCOPE=BOTH;

2.配置环境变量(oracle用户下)

export LD_LIBRARY_PATH=/ggs:$ORACLE_HOME/lib

export PATH=$HVR_HOME/bin:$PATH:$ORACLE_HOME/bin:$PATH:$HOME/bin:/ggs

3.两个节点上传ogg介质并解压，授权/ggs目录给oracle

chown oracle:dba /ggs -R

su - oracle

cd /ggs

unzip p22575475_1121032_Linux-x86-64.zip

tar xvf fbo_ggs_Linux_x64_ora11g_64bit.tar

4.创建ogg相关路径

进入ogg安装路径：

cd /ggs

ggsci

dblogin userid goldengatet password goldengatet

create subdirs

5.如果配置sequence，需要

cd /ggs --ogg安装目录

sqlplus / as sysdba

@sequence.sql

GRANT EXECUTE on goldengatet.replicateSequence TO goldengatet;

6.配置参数文件

MGR：

edit param mgr

======================================

port 7809

autostart replicat *

autorestart replicat *, waitminutes 1, retries 60, RESETMINUTES 60

PURGEOLDEXTRACTS /ggs/dirdat/td*, USECHECKPOINTS, MINKEEPHOURS 2

======================================

7.添加checkpoint表

dblogin userid goldengate,password goldengate

ADD CHECKPOINTTABLE goldengate.ckptfull

8.添加目标端应用进程

add replicat repfull, exttrail /ggs/dirdat/td, CHECKPOINTTABLE goldengate.ckptfull

9.修改目标端应用进程参数

edit params repfull

====================================================

replicat repfull

setenv ( NLS_LANG = "AMERICAN_AMERICA.ZHS16GBK" )

assumetargetdefs

userid goldengate, password goldengate

DBOPTIONS DEFERREFCONST, SUPPRESSTRIGGERS

gettruncates

ALLOWNOOPUPDATES

ddl include mapped

discardfile ./dirrpt/repfull.dsc, append, megabytes 5000

--DDLERROR 30568,IGNORE

--REPERROR 1403, DISCARD

--mapexclude CIMS.TJ_QY_NZ3_SUB;

map lm.testogg, target lm.testogg;

========================================

启动目标端应用进程前需要确认的事情：

第一， 在rep参数文件中有参数DEFERREFCONST禁用级联删除

第二， 在rep参数文件中有参数SUPPRESSTRIGGERS 在rep进程运行时抑制目的端数据库的触发器生效。

注意：SUPPRESSTRIGGERS此参数仅仅对10.2.0.5 及以后，11.2.0.2及以后的oracle 数据库版本才有效，所以，若是目的端数据库是10.2.0.4，还需要在目的端数据库中手工禁用触发器(用plsql dev就可以禁用)

第三， Impdp已经导入完成

11.导入数据

impdp sys/aaa directory=dumpfile=.dmp tables= exclude=statistics

12.收集统计信息

exec dbms_stats.gather_table_stats(OWNNAME => ‘LM’, TABNAME => ‘TESTOGG’, CASCADE => TRUE);

11.启动应用进程

start mgr

start repfull, aftercsn scn ---源端取到的scn