标签云
asm mount asm 恢复 asm恢复 bbed bootstrap$ dul eking In Memory kcbzib_kcrsds_1 kccpb_sanity_check_2 kfed MySQL恢复 ORA-00312 ORA-00607 ORA-00704 ORA-01110 ORA-01555 ORA-01578 ORA-08103 ORA-600 2662 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-15042 ORA-15196 ORACLE 12C oracle dul ORACLE PATCH Oracle Recovery Tools oracle加密恢复 oracle勒索 oracle异常恢复 ORACLE恢复 Oracle 恢复 ORACLE数据库恢复 oracle 比特币 OSD-04016 sql加密恢复 YOUR FILES ARE ENCRYPTED 勒索恢复 比特币 oracle 比特币加密文章分类
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (91)
- 数据库 (1,419)
- DB2 (22)
- MySQL (58)
- Oracle (1,305)
- Data Guard (41)
- EXADATA (7)
- GoldenGate (20)
- ORA-xxxxx (150)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (12)
- ORACLE 21C (3)
- Oracle ASM (58)
- Oracle Bug (7)
- Oracle RAC (42)
- Oracle 安全 (6)
- Oracle 开发 (26)
- Oracle 监听 (26)
- Oracle备份恢复 (438)
- Oracle安装升级 (71)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (63)
- PostgreSQL (10)
- SQL Server (26)
- SQL Server恢复 (7)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (24)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (7)
-
最近发表
- O/S-Error: (OS 23) 数据错误(循环冗余检查) 数据库恢复
- 数据库open报ORA-00959: tablespace ‘UNDOTBS1′ does not exist分析
- ASM删除表空间恢复
- ORA-12518 ORA-27302: 错误发生在: ssthrddcr
- plsql 插入blob
- 分享运气超级好的一次drop tablespace 数据恢复
- segment header异常对象删除处理
- Oracle 数据库(RDBMS)的版本支持状态摘要 (Doc ID 1674382.1)
- [back2023@proxy.tg].eking勒索数据库恢复
- ORA-600 ktbsdp2 处理
- win文件系统损坏oracle恢复
- asm disk 磁盘部分被清空恢复
- File #xxx found in data dictionary but not in controlfile. Creating OFFLINE file ‘MISSING00XXX’ in the controlfile
- commit_wait和commit_logging设置不当导致数据库无法正常启动
- ORA-00742 ORA-00312故障恢复
- 110T oracle故障恢复
- ora-600 kcratr_scan_lastbwr
- .asistchinadecryption扩展名勒索数据库恢复
- .[decrypt20@firemail.cc].eking 数据库勒索恢复
- ocr磁盘组掉盘故障处理
友情链接
标签归档:goldengate
ogg同步部分列配置
自从2010年后,基本上没有玩ogg了,最近有客户需求,a库在内网,b库在外网,希望同步a库中几个基础业务表的每个表的几个字段同步到b库中,采用a–>c–>b的方式来实现同步(c同时接通内外网),ogg 本身同步不难,关键是自己好多年没有玩,而且这次是只要同步部分列的情况,因此做了一个同步表部分列的一个demo测试
源端数据库准备
启动归档模式,开启强制日志和辅助日志,创建测试用户/表,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.xifenfei.com' 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.xifenfei.com'; COUNT(*) ---------- 99 --源端库 SQL> delete from t_XIFENFEI where owner='www.xifenfei.com'; 99 rows deleted. SQL> commit; Commit complete. --目标端 SQL> select count(*) from xff.t_xff where owner='www.xifenfei.com'; COUNT(*) ---------- 0 --源端库 SQL> insert into xifenfei.t_xifenfei(owner,object_id) values('www.xifenfei.com',1); 1 row created. SQL> commit; Commit complete. --目标端库 SQL> select count(*) from xff.t_xff where owner='www.xifenfei.com'; COUNT(*) ---------- 1 SQL> select * from xff.t_xff where owner='www.xifenfei.com'; OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID -------------------- ------------------- ------------------------------ ---------- www.xifenfei.com 1
这里实现部分列同步,主要在extract端使用COLS捕获需要列,使用ctas结合dblink,flashback query实现表测试后.