最新下载
热门教程
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
oracle ogg同步部分列配置教程详解
时间:2022-06-29 09:47:16 编辑:袖梨 来源:一聚教程网
oracle ogg是什么
Oracle Golden Gate软件是一种基于日志的结构化数据复制备份软件,它通过解析源数据库在线日志或归档日志获得数据的增量变化,再将这些变化应用到目标数据库,从而实现源数据库与目标数据库同步。Oracle Golden Gate可以在异构的IT基础结构(包括几乎所有常用操作系统平台和数据库平台)之间实现大量数据亚秒一级的实时复制,从而在可以在应急系统、在线报表、实时数据仓库供应、交易跟踪、数据同步、集中/分发、容灾、数据库升级和移植、双业务中心等多个场景下应用。同时,Oracle Golden Gate可以实现一对一、广播(一对多)、聚合(多对一)、双向、点对点、级联等多种灵活的拓扑结构
源端数据库准备
启动归档模式,开启强制日志和辅助日志,创建测试用户/表,ogg用户
SQL> create user xifenfei identified by xifenfei; User created. SQL> grant dba to xifenfei; Grant succeeded. SQL> conn xifenfei/xifenfei Connected. SQL> create table t_xifenfei as select * from dba_objects; Table created. SQL> alter table t_xifenfei add constraint pk_t_xifenfei primary key(object_id); Table altered. SQL> archive log list; Database log mode No Archive Mode Automatic archival Disabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 9 Current log sequence 11 SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 901775360 bytes Fixed Size 2024944 bytes Variable Size 239077904 bytes Database Buffers 658505728 bytes Redo Buffers 2166784 bytes Database mounted. SQL> alter database archivelog; Database altered. SQL> alter database open; Database altered. SQL> alter database force logging; Database altered. SQL> alter database add supplemental log data; Database altered. SQL> alter system switch logfile; System altered. SQL> create user ogg identified by oracle; User created. SQL> grant dba to ogg; Grant succeeded.
配置mgr进程
[oracle@xffdbrh5 ogg]$ export PATH=/u01/ogg:$PATH [oracle@xffdbrh5 ogg]$ export LD_LIBRARY_PATH=/u01/ogg:$ORACLE_HOME/lib [oracle@xffdbrh5 ogg]$ ./ggsci Oracle GoldenGate Command Interpreter for Oracle Version 11.2.1.0.5_02 16363018 OGGCORE_11.2.1.0.6_PLATFORMS_130301.1500_FBO Linux, x64, 64bit (optimized), Oracle 10g on Mar 1 2013 19:04:05 Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved. GGSCI (xffdbrh5) 1> create subdirs Creating subdirectories under current directory /u01/ogg Parameter files /u01/ogg/dirprm: already exists Report files /u01/ogg/dirrpt: created Checkpoint files /u01/ogg/dirchk: created Process status files /u01/ogg/dirpcs: created SQL script files /u01/ogg/dirsql: created Database definitions files /u01/ogg/dirdef: created Extract data files /u01/ogg/dirdat: created Temporary files /u01/ogg/dirtmp: created Stdout files /u01/ogg/dirout: created GGSCI (xffdbrh5) 2> edit param mgr port 7839 DYNAMICPORTLIST 7840-7850 AUTOSTART EXTRACT * AUTORESTART EXTRACT * PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 7 LAGREPORTHOURS 1 LAGINFOMINUTES 30 LAGCRITICALMINUTES 45 GGSCI (xffdbrh5) 3> dblogin userid ogg, password oracle Successfully logged into database. GGSCI (xffdbrh5) 4> add checkpointtable ogg.ggs_checkpoint Successfully created checkpoint table ogg.ggs_checkpoint. GGSCI (xffdbrh5) 5> EDIT PARAMS ./GLOBALS ogg.ggs_checkpoint GGSCI (xffdbrh5) 6> start mgr Manager started. GGSCI (xffdbrh5) 7> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING
配置extract进程
GGSCI (xffdbrh5) 3> dblogin userid ogg, password oracle Successfully logged into database. GGSCI (xffdbrh5) 4> add trandata xifenfei.t_xifenfei Logging of supplemental redo data enabled for table XIFENFEI.T_XIFENFEI. GGSCI (xffdbrh5) 5> add extract ext_1, tranlog, begin now, threads 1 EXTRACT added. GGSCI (xffdbrh5) 6> add EXTTRAIL ./dirdat/r1, extract ext_1,megabytes 100 EXTTRAIL added. GGSCI (xffdbrh5) 7> edit param ext_1 EXTRACT ext_1 userid ogg,password oracle REPORTCOUNT EVERY 1 MINUTES, RATE numfiles 5000 DISCARDFILE ./dirrpt/ext_1.dsc,APPEND,MEGABYTES 1024 DISCARDROLLOVER AT 3:00 exttrail ./dirdat/r1,megabytes 100 dynamicresolution TRANLOGOPTIONS DISABLESUPPLOGCHECK --bug 16857778 TABLE xifenfei.t_xifenfei, COLS (OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID); GGSCI (xffdbrh5) 8> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT STOPPED EXT_1 00:00:00 00:00:22 GGSCI (xffdbrh5) 9> start ext_1 Sending START request to MANAGER ... EXTRACT EXT_1 starting GGSCI (xffdbrh5) 10> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING EXT_1 00:01:18 00:00:00
配置pump data进程
GGSCI (xffdbrh5) 1> edit param dpe_1 extract dpe_1 dynamicresolution passthru rmthost 192.168.137.251, mgrport 7839, compress rmttrail ./dirdat/t1 numfiles 5000 TABLE xifenfei.t_xifenfei; GGSCI (xffdbrh5) 2> start dpe_1 Sending START request to MANAGER ... EXTRACT DPE_1 starting GGSCI (xffdbrh5) 3> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING DPE_1 00:00:00 00:16:47 EXTRACT RUNNING EXT_1 00:00:00 00:00:07
目标端数据库准备
[oracle@xifenfei ~]$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.4.0 - Production on Thu Feb 13 00:40:19 2014 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> create user ogg identified by oracle; User created. SQL> grant dba to ogg; Grant succeeded. SQL> create user xff identified by xifenfei; User created. SQL> grant dba to xff; Grant succeeded. SQL> conn xff/xifenfei Connected. SQL> create database link syc_data 2 connect to ogg identified by oracle 3 using '(DESCRIPTION = 4 (ADDRESS_LIST = 5 (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.137.252)(PORT = 1521)) 6 ) 7 (CONNECT_DATA = 8 (SERVER = DEDICATED) 9 (SERVICE_NAME = ora10g) 10 ) 11 )'; Database link created. SQL> select count(*) from xifenfei.t_xifenfei@syc_data ; COUNT(*) ---------- 9917 SQL> SELECT CURRENT_SCN FROM V$DATABASE@syc_data ; CURRENT_SCN ----------- 793069 SQL> create table xff.t_xff as select OWNER, OBJECT_NAME, SUBOBJECT_NAME, 2 > OBJECT_ID from xifenfei.t_xifenfei@syc_data AS OF SCN 793069; Table created. SQL> alter table xff.t_xff add constraint pk_t_xff primary key(object_id); Table altered.
目标端mgrp配置
[oracle@xifenfei ogg]$export LD_LIBRARY_PATH=/home/oracle/amdu:$ORACLE_HOME/lib:/u01/oracle/oradata/ogg [oracle@xifenfei ogg]$ ./ggsci Oracle GoldenGate Command Interpreter for Oracle Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO Linux, x86, 32bit (optimized), Oracle 10g on Apr 23 2012 07:06:02 Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved. GGSCI (xifenfei) 8> edit param mgr port 7839 DYNAMICPORTLIST 7840-7850 PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 7 autorestart extract *, waitminutes 1, retries 60 autorestart replicat *, waitminutes 1, retries 60 LAGREPORTHOURS 1 LAGINFOMINUTES 30 LAGCRITICALMINUTES 45 GGSCI (xifenfei) 12> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER STOPPED GGSCI (xifenfei) 13> create subdirs Creating subdirectories under current directory /u01/oracle/oradata/ogg Parameter files /u01/oracle/oradata/ogg/dirprm: already exists Report files /u01/oracle/oradata/ogg/dirrpt: already exists Checkpoint files /u01/oracle/oradata/ogg/dirchk: already exists Process status files /u01/oracle/oradata/ogg/dirpcs: already exists SQL script files /u01/oracle/oradata/ogg/dirsql: already exists Database definitions files /u01/oracle/oradata/ogg/dirdef: already exists Extract data files /u01/oracle/oradata/ogg/dirdat: already exists Temporary files /u01/oracle/oradata/ogg/dirtmp: already exists Stdout files /u01/oracle/oradata/ogg/dirout: already exists GGSCI (xifenfei) 2> dblogin userid ogg, password oracle Successfully logged into database. GGSCI (xifenfei) 3> add checkpointtable ogg.ggs_checkpoint Successfully created checkpoint table ogg.ggs_checkpoint. GGSCI (xifenfei) 4> EDIT PARAMS ./GLOBALS checkpointtable ogg.ggs_checkpoint GGSCI (xifenfei) 5> start mgr Manager started. GGSCI (xifenfei) 6> add replicat rep_1,exttrail ./dirdat/t1,checkpointtable ogg.ggs_checkpoint REPLICAT added. GGSCI (xifenfei) 7> edit params rep_1 REPLICAT rep_1 USERID ogg,PASSWORD oracle REPORTCOUNT EVERY 30 MINUTES, RATE REPERROR DEFAULT, ABEND numfiles 5000 assumetargetdefs DISCARDFILE ./dirrpt/rep_1.dsc, APPEND, MEGABYTES 1000 DISCARDROLLOVER AT 3:00 ALLOWNOOPUPDATES MAP xifenfei.t_xifenfei, TARGET xff.t_xff; GGSCI (xifenfei) 8> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT STOPPED REP_1 00:00:00 00:01:45 GGSCI (xifenfei) 9> start rep_1,aftercsn 793069 Sending START request to MANAGER ... REPLICAT REP_1 starting GGSCI (xifenfei) 10> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT RUNNING REP_1 00:00:00 00:00:01
测试数据库同步
--源端库 SQL> desc t_XIFENFEI Name Null? Type ----------------------------------------- -------- ---------------------------- OWNER VARCHAR2(30) OBJECT_NAME VARCHAR2(128) SUBOBJECT_NAME VARCHAR2(30) OBJECT_ID NOT NULL NUMBER DATA_OBJECT_ID NUMBER OBJECT_TYPE VARCHAR2(19) CREATED DATE LAST_DDL_TIME DATE TIMESTAMP VARCHAR2(19) STATUS VARCHAR2(7) TEMPORARY VARCHAR2(1) GENERATED VARCHAR2(1) SECONDARY VARCHAR2(1) SQL> update t_XIFENFEI set owner='www.111com.net' where rownum<100; 99 rows updated. SQL> commit; Commit complete. --目标端库 SQL> desc xff.t_xff Name Null? Type ----------------------------------------- -------- ---------------------------- OWNER VARCHAR2(30) OBJECT_NAME VARCHAR2(128) SUBOBJECT_NAME VARCHAR2(30) OBJECT_ID NOT NULL NUMBER SQL> select count(*) from xff.t_xff where owner='www.111com.net'; COUNT(*) ---------- 99 --源端库 SQL> delete from t_XIFENFEI where owner='www.111com.net'; 99 rows deleted. SQL> commit; Commit complete. --目标端 SQL> select count(*) from xff.t_xff where owner='www.111com.net'; COUNT(*) ---------- 0 --源端库 SQL> insert into xifenfei.t_xifenfei(owner,object_id) values('www.111com.net',1); 1 row created. SQL> commit; Commit complete. --目标端库 SQL> select count(*) from xff.t_xff where owner='www.111com.net'; COUNT(*) ---------- 1 SQL> select * from xff.t_xff where owner='www.111com.net'; OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID -------------------- ------------------- ------------------------------ ---------- www.111com.net 1
这里实现部分列同步,主要在extract端使用COLS捕获需要列,使用ctas结合dblink,flashback query实现表测试后.
相关文章
- 人们熟悉的寄居蟹属于以下哪种分类 神奇海洋11月21日答案 11-21
- 第五人格11.22共研服有什么更新 11月22日共研服更新内容介绍 11-21
- 原神恰斯卡怎么培养 11-21
- 无期迷途四星装束是谁 11-21
- 王者荣耀帝丹高中校服怎么获得 11-21
- 光遇姆明季后续版本怎么玩 11-21