标签云
asm恢复 bbed bootstrap$ dul 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 2131 ORA-600 2662 ORA-600 2663 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-600 kcbzib_kcrsds_1 ORA-600 KCLCHKBLK_4 ORA-15042 ORA-15196 ORACLE 12C oracle dul ORACLE PATCH Oracle Recovery Tools oracle加密恢复 oracle勒索 oracle勒索恢复 oracle异常恢复 Oracle 恢复 ORACLE恢复 ORACLE数据库恢复 oracle 比特币 OSD-04016 YOUR FILES ARE ENCRYPTED 勒索恢复 比特币加密文章分类
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (102)
- 数据库 (1,655)
- DB2 (22)
- MySQL (71)
- Oracle (1,519)
- Data Guard (51)
- EXADATA (8)
- GoldenGate (21)
- ORA-xxxxx (158)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (14)
- ORACLE 21C (3)
- Oracle 23ai (7)
- Oracle ASM (65)
- Oracle Bug (8)
- Oracle RAC (52)
- Oracle 安全 (6)
- Oracle 开发 (28)
- Oracle 监听 (28)
- Oracle备份恢复 (553)
- Oracle安装升级 (90)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (76)
- PostgreSQL (18)
- PostgreSQL恢复 (6)
- SQL Server (27)
- SQL Server恢复 (8)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (37)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (20)
-
最近发表
- RAC默认服务配置优先节点
- Oracle 19c RAC 替换私网操作
- 监听报TNS-12541 TNS-12560 TNS-00511错误
- drop tablespace xxx including contents恢复
- Linux 8 修改网卡名称
- 如何修改集群的公网信息(包括 VIP) (Doc ID 1674442.1)
- 如何在 oracle 集群环境下修改私网信息 (Doc ID 2103317.1)
- ORA-600 [kcvfdb_pdb_set_clean_scn: cleanckpt] 相关bug
- ORA-600 krhpfh_03-1210故障处理
- 19c库启动报ORA-600 kcbzib_kcrsds_1
- DBMS_SESSION.set_context提示ORA-01031问题解决
- redo写丢失导致ORA-600 kcrf_resilver_log_1故障
- 硬件故障导致ORA-01242 ORA-01122等错误
- 200T 数据库非归档无备份恢复
- 利用flashback快速恢复failover 的备库
- [comingback2022@cock.li].eking和[tsai.shen@mailfence.com].faust扩展名勒索病毒数据库可以完美恢复
- opatch auto 出现unable to get oracle owner for 错误
- Oracle 23ai 表和视图的列最多支持到4096个
- 断电引起redo和数据文件不一致故障恢复
- ORA-03113: 通信通道的文件结尾
标签归档:部分列
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实现表测试后.