ogg 复制--Oracle11G rac同步到Oracle11g（基于scn的数据初始化）

1、ogg软件安装

2、数据库相关配置

2.1相关参数配置
源端
开启数据库级别的日志记录
SELECT supplemental_log_data_min, force_logging FROM v$database;
附加日志
alter database add supplemental log data;
强制记日志
ALTER DATABASE FORCE LOGGING;
启用
alter system set enable_goldengate_replication=true;
alter system set RECYCLEBIN=off scope=spfile;  --运行ddl_setup前必须保证关闭数据库的回收站特性
目标端
alter system set enable_goldengate_replication=true;
2.2创建ogg用户并赋权
源端
创建相关用户
create tablespace ogg datafile '+data' size 10M;
create user ogg identified by oracle default tablespace ogg;
ogg用户的权限

GRANT CONNECT,RESOURCE,unlimited tablespace TO ogg;
GRANT EXECUTE ON UTL_FILE TO ogg;


GRANT SELECT ANY DICTIONARY,SELECT ANY TABLE TO ogg;
GRANT FLASHBACK ANY TABLE TO ogg;
GRANT ALTER ANY TABLE TO ogg;
grant EXECUTE on DBMS_FLASHBACK to ogg;
目标端

目标端
create tablespace ogg datafile '/u01/app/oracle/oradata/test/ogg01.dbf' size 10M;
create user ogg identified by oracle default tablespace ogg;

GRANT CONNECT,RESOURCE,unlimited tablespace TO ogg;
GRANT EXECUTE ON UTL_FILE TO ogg;
GRANT SELECT ANY DICTIONARY,SELECT ANY TABLE TO ogg;
GRANT UPDATE ANY TABLE,INSERT ANY TABLE,DELETE ANY TABLE TO ogg;
grant EXECUTE_CATALOG_ROLE to ogg;
GRANT CREATE ANY TABLE TO OGG;

3、相关进程配置

3.1参数GLOBALS配置

源库
EDIT PARAMS ./GLOBALS
GGSCHEMA ogg

目标库
添加
dblogin userid ogg,password ogg
ADD CHECKPOINTTABLE ogg.checkpointtable

EDIT PARAMS ./GLOBALS
GGSCHEMA ogg
CHECKPOINTTABLE ogg.checkpointtable
3.2mgr进程
源端

PORT 7809
DYNAMICPORTLIST 7800-7900
AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3
PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 3
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45

目标端
PORT 7809
DYNAMICPORTLIST 7800-7900
AUTORESTART er *,RETRIES 5,WAITMINUTES 3
PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 3
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45

3.3源端抽取进程和传递进程

配置抽取进程：

add extract ext1, tranlog, begin now, threads 2
设置队列
add exttrail ./dirdat/r1,extract ext1,megabytes 10

ASM =  
 (DESCRIPTION =  
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.41)(PORT = 1521))  
    (CONNECT_DATA =  
      (SERVER = DEDICATED)  
      (SERVICE_NAME = +ASM)  
      (SID_NAME = +ASM1)  
    )  
  )

EXTRACT ext1
--setenv (ORACLE_SID=)
setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
userid ogg,password oracle
TRANLOGOPTIONS ASMUSER sys@ASM,ASMPASSWORD oracle
--TRANLOGOPTIONS DBLOGREADER
--TRANLOGOPTIONS ALTARCHIVELOGDEST +DATA
DDL INCLUDE ALL
DISCARDROLLOVER AT 3:00
exttrail ./dirdat/r1,megabytes 10
dynamicresolution
TABLE HR.*;

配置投递进程
add extract dp1, exttrailsource ./dirdat/r1, begin now
设置队列
ADD RMTTRAIL ./dirdat/rt, EXTRACT dp1, MEGABYTES 10



EXTRACT dp1
USERID ogg, PASSWORD oracle
RMTHOST 192.168.56.11, MGRPORT 7809, COMPRESS
PASSTHRU
NUMFILES 5000
RMTTRAIL ./dirdat/rt
DYNAMICRESOLUTION
TABLE HR.*;



目标端
配置应用进程
add replicat rep1, exttrail ./dirdat/rt, checkpointtable ogg.checkpointtable


REPLICAT rep1
USERID ogg, password oracle
ALLOWNOOPUPDATES  
ASSUMETARGETDEFS
--HANDLECOLLISIONS
DISCARDFILE ./dirrpt/repsa.dsc, APPEND, MEGABYTES 10
MAP hr.*, TARGET hr.*;
4、ddl初始化
@marker_setup.sql
alter system set RECYCLEBIN=off scope=spfile;  --运行ddl_setup前必须保证关闭数据库的回收站特性
@ddl_setup.sql
@role_setup.sql
GRANT GGS_GGSUSER_ROLE to ogg;   
@ddl_enable.sql
关闭    
@ddl_disable.sql
查看状态
@ddl_status.sql
验证脚本安装
@marker_status
NOTE:在源端ogg的ddl支持是默认关闭的，如果需要启用的话需在extract进程的参数文件中加入ddl参数；
在目标端ogg的ddl支持是默认开启的，如果有其他需求可以在replicat进程的参数文件中使用ddl参数来
过滤或或者忽略某些ddl操作。
DDL INCLUDE ALL

5、使用数据泵初始化数据

查看长事务是否完成
select min(start_time) from v$transaction/gv$transaction;
记录抽取进程时间点---（此时间一定要在抽取进程启动之后，并且要等抽取进程启动前未完成的事务提交）
Select  start_time  from gv$transaction where to_date(start_time, ‘yyyy-mm-dd hh24:mi:ss’)<to_date(‘2011-05-03 11:20:55’, ‘yyyy-mm-dd hh24:mi:ss’);

等所有事务完成后获取当前的scn
select current_scn from v$database ;
2259147
expdp system/oracle schemas=hr directory=DATA_PUMP_DIR  dumpfile=ora2017.dmp flashback_scn=2259147
导入

impdp system/oracle directory=DATA_PUMP_DIR dumpfile= ora2017.dmp logfile=user.log  table_exists_action=replace remap_tablespace=EXAMPLE:USERS;  

启动应用进程

start rep1, aftercsn 2259147

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31324783/viewspace-2144808/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31324783/viewspace-2144808/