-
3D Cloud
asm mount asm恢复 bbed bootstrap$ dmp损坏 dul In Memory kccpb_sanity_check_2 kfed MySQL恢复 ORA-00312 ORA-00607 ORA-00704 ORA-01110 ORA-01190 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-600 kccpb_sanity_check_2 ORA-15042 ORACLE 12C oracle dul ORACLE PATCH oracle异常恢复 ORACLE恢复 Oracle 恢复 ORACLE数据库恢复 oracle 比特币 redo异常 undo异常 YOUR FILES ARE ENCRYPTED _ALLOW_RESETLOGS_CORRUPTION 勒索恢复 数据库恢复 比特币 比特币 oracle 比特币加密 比特币勒索
文章分类
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (87)
- 数据库 (1,312)
- DB2 (22)
- MySQL (56)
- Oracle (1,198)
- Data Guard (39)
- EXADATA (7)
- GoldenGate (19)
- ORA-xxxxx (145)
- ORACLE 12C (71)
- ORACLE 18C (6)
- ORACLE 19C (8)
- Oracle ASM (56)
- Oracle Bug (7)
- Oracle RAC (40)
- Oracle 安全 (6)
- Oracle 开发 (25)
- Oracle 监听 (26)
- Oracle备份恢复 (392)
- Oracle安装升级 (54)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (41)
- PostgreSQL (10)
- SQL Server (26)
- SQL Server恢复 (7)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (23)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (6)
-
最近发表
- incaseformat 病毒删除文件恢复
- xfs文件系统mysql删库恢复
- 对恢复案例:因对工作调整不满,链家一员工删除公司 9 TB数据:被判7年事件有感
- Oracle Recovery Tools 解决ORA-01190 ORA-01248等故障
- Oracle Recovery Tools 12月份更新
- -bash: /bin/rm: Argument list too long
- ORA-27303: failure occurred at: skgpwinit6
- ORA-600 kffmLoad_1 kffmVerify_4
- ORA-00600 kfrHtAdd01
- ORA-00600 [2662]和ORA-00600 [4194]恢复
- 记录oracle安装的两个小问题(INS-30060和弹出子窗口异常)
- dblink会话引起library cache lock
- asm磁盘类似_DROPPED_0001_DATA名称故障处理
- 12C数据库报ORA-600 kcbzib_kcrsds_1故障处理
- 再次遇到ORA-600 kokasgi1故障恢复
- sysaux表空间不足—WRH$_ACTIVE_SESSION_HISTORY
- sql server 删除数据库恢复
- .eight加密数据库恢复
- xiaolinghelper@firemail.cc加密数据库恢复
- 加密.CC4H扩展名数据库恢复支持
友情链接
分类目录归档:GoldenGate
ogg导致v$logfile查询频繁
在某些版本的ogg中发现ogg抽取进程对v$logfile视图查询影响比较大
对应的sql语句为:SELECT 1 FROM V$LOGFILE WHERE(STATUS NOT IN (‘STALE’, ‘INVALID’) OR STATUS IS NULL) AND MEMBER <> :log_name AND EXISTS ( SELECT 1 FROM V$LOG WHERE GROUP# = V$LOGFILE.GROUP# AND THREAD# = :ora_thread AND SEQUENCE# = :ora_seq_no ) AND ROWNUM = 1
查询mos发现相关问题描述:Query On V$logfile Running Excessive Number Of Times After upgrading ogg to 11.2.1.0.32 or 12.1.2.1.5 or later (Doc ID 2116395.1)和Bug 22650790 : CE 12.1.2.1.9: Query on v$logfile running excessive number of times

根据mos描述在ogg对应版本中设置:TRANLOGOPTIONS _ENABLESTREAMLINEDDBLOGREADER
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实现表测试后.
利用impdp结合network_link+FLASHBACK_TIME初始化ogg同步数据
部署ogg,需要两边初始化数据,但是因为业务不能停止。所以考虑到使用ogg init功能或者impdp。考虑到数据量比较大,自己对ogg init不是很有信心,所以选择了使用impdp结合network_link+FLASHBACK_TIME处理
一、处理思路
1、选择时间点(尚未达到时间点)
选择一个时间点,用于重设ogg同步时间点,impdp导入时间点
要求:ogg尚未加载到该时间点(在该时间点之前停止ogg相关ext/pump/replicat进程)
2、开始导入指定时间点数据(该时间点已经达到后)
[oracle@srtcreen OGG]$ impdp srt_creen/a69UiBOB1gk directory=DATA_PUMP_DIR network_link=OGG_TYKF REMAP_SCHEMA=CSCNEW:SRT_CREEN REMAP_TABLESPACE=CSC_TAB_1:SRTCREEN tables=CSCNEW.TAB_CS_USER,CSCNEW.TAB_CS_QUEUES,CSCNEW.TAB_CS_CALL_PICKUP,CSCNEW.TAB_CS_CALL_COMING,CSCNEW.TAB_CS_CALLLOG FLASHBACK_TIME=\"to_timestamp\(\'2011-12-06 13:03:00\',\'yyyy-mm-dd hh24:mi:ss\'\)\" Import: Release 10.2.0.4.0 - 64bit Production on Tuesday, 06 December, 2011 13:04:34 Copyright (c) 2003, 2007, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SRT_CREEN"."SYS_IMPORT_TABLE_01": srt_creen/******** directory=DATA_PUMP_DIR network_link=OGG_TYKF REMAP_SCHEMA=CSCNEW:SRT_CREEN REMAP_TABLESPACE=CSC_TAB_1:SRTCREEN tables=CSCNEW.TAB_CS_USER,CSCNEW.TAB_CS_QUEUES,CSCNEW.TAB_CS_CALL_PICKUP,CSCNEW.TAB_CS_CALL_COMING,CSCNEW.TAB_CS_CALLLOG FLASHBACK_TIME="to_timestamp('2011-12-06 13:03:00','yyyy-mm-dd hh24:mi:ss')" Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 824.5 MB Processing object type TABLE_EXPORT/TABLE/TABLE . . imported "SRT_CREEN"."TAB_CS_CALLLOG" 3289293 rows . . imported "SRT_CREEN"."TAB_CS_CALL_COMING" 1218843 rows . . imported "SRT_CREEN"."TAB_CS_CALL_PICKUP" 1092937 rows . . imported "SRT_CREEN"."TAB_CS_QUEUES" 4614 rows . . imported "SRT_CREEN"."TAB_CS_USER" 458 rows Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/COMMENT Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Job "SRT_CREEN"."SYS_IMPORT_TABLE_01" successfully completed at 13:12:37
3、重设同步时间点,开启进程
alter ext_1,begin 2011-12-06 13:03:00 alter ext_2,begin 2011-12-06 13:03:00 alter ext_3,begin 2011-12-06 13:03:00 alter ext_4,begin 2011-12-06 13:03:00 alter ext_5,begin 2011-12-06 13:03:00 alter p_1,begin 2011-12-06 13:03:00 alter p_2,begin 2011-12-06 13:03:00 alter p_3,begin 2011-12-06 13:03:00 alter p_4,begin 2011-12-06 13:03:00 alter p_5,begin 2011-12-06 13:03:00 start * alter r_1,begin 2011-12-06 13:03:00 alter r_2,begin 2011-12-06 13:03:00 alter r_3,begin 2011-12-06 13:03:00 alter r_4,begin 2011-12-06 13:03:00 alter r_5,begin 2011-12-06 13:03:00 start *
4、验证同步情况
--源端 GGSCI (tykf) 23> info all Program Status Group Lag Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING EXT_1 00:00:00 00:00:05 EXTRACT RUNNING EXT_2 00:00:00 00:00:05 EXTRACT RUNNING EXT_3 00:00:00 00:00:07 EXTRACT RUNNING EXT_4 00:00:00 00:00:07 EXTRACT RUNNING EXT_5 00:00:00 00:00:07 EXTRACT RUNNING P_1 00:00:00 00:00:06 EXTRACT RUNNING P_2 00:00:00 00:00:06 EXTRACT RUNNING P_3 00:00:00 00:00:06 EXTRACT RUNNING P_4 00:00:00 00:00:06 EXTRACT RUNNING P_5 00:00:00 00:00:06 GGSCI (tykf) 24> stats p_3 Sending STATS request to EXTRACT P_3 ... Start of Statistics at 2011-12-06 13:15:46. DDL replication statistics (for all trails): *** Total statistics since extract started *** Operations 0.00 Mapped operations 0.00 Unmapped operations 0.00 Other operations 0.00 Excluded operations 0.00 Output to /opt/OGG/dirdat/U3: Extracting from CSCNEW.TAB_CS_CALL_PICKUP to CSCNEW.TAB_CS_CALL_PICKUP: *** Total statistics since 2011-12-06 13:13:01 *** Total inserts 8.00 Total updates 10.00 Total deletes 0.00 Total discards 0.00 Total operations 18.00 *** Daily statistics since 2011-12-06 13:13:01 *** Total inserts 8.00 Total updates 10.00 Total deletes 0.00 Total discards 0.00 Total operations 18.00 *** Hourly statistics since 2011-12-06 13:13:01 *** Total inserts 8.00 Total updates 10.00 Total deletes 0.00 Total discards 0.00 Total operations 18.00 *** Latest statistics since 2011-12-06 13:13:01 *** Total inserts 8.00 Total updates 10.00 Total deletes 0.00 Total discards 0.00 Total operations 18.00 End of Statistics. --目标端 GGSCI (srtcreen) 11> info all Program Status Group Lag Time Since Chkpt MANAGER RUNNING REPLICAT RUNNING R_1 00:00:00 00:00:06 REPLICAT RUNNING R_2 00:00:00 00:00:06 REPLICAT RUNNING R_3 00:00:00 00:00:08 REPLICAT RUNNING R_4 00:00:00 00:00:04 REPLICAT RUNNING R_5 00:00:00 00:00:02 GGSCI (srtcreen) 12> stats r_3 Sending STATS request to REPLICAT R_3 ... Start of Statistics at 2011-12-06 13:15:35. DDL replication statistics: *** Total statistics since replicat started *** Operations 0.00 Mapped operations 0.00 Unmapped operations 0.00 Other operations 0.00 Excluded operations 0.00 Errors 0.00 Retried errors 0.00 Discarded errors 0.00 Ignored errors 0.00 Replicating from CSCNEW.TAB_CS_CALL_PICKUP to SRT_CREEN.TAB_CS_CALL_PICKUP: *** Total statistics since 2011-12-06 13:13:10 *** Total inserts 8.00 Total updates 10.00 Total deletes 0.00 Total discards 0.00 Total operations 18.00 *** Daily statistics since 2011-12-06 13:13:10 *** Total inserts 8.00 Total updates 10.00 Total deletes 0.00 Total discards 0.00 Total operations 18.00 *** Hourly statistics since 2011-12-06 13:13:10 *** Total inserts 8.00 Total updates 10.00 Total deletes 0.00 Total discards 0.00 Total operations 18.00 *** Latest statistics since 2011-12-06 13:13:10 *** Total inserts 8.00 Total updates 10.00 Total deletes 0.00 Total discards 0.00 Total operations 18.00 End of Statistics.
二、配置过程遇到问题
1、impdp报无权限
[oracle@srtcreen OGG]$ expdp srt_creen/a69UiBOB1gk directory=DATA_PUMP_DIR network_link=OGG_TYKF tables=CSCNEW.TAB_CS_USER,CSCNEW.TAB_CS_QUEUES,CSCNEW.TAB_CS_CALL_PICKUP,CSCNEW.TAB_CS_CALL_COMING,CSCNEW.TAB_CS_CALLLOG FLASHBACK_TIME=\"to_timestamp\(\'2011-12-06 11:00:00\',\'yyyy-mm-dd hh24:mi:ss\'\)\" dumpfile=a.dmp Export: Release 10.2.0.4.0 - 64bit Production on Tuesday, 06 December, 2011 11:15:04 Copyright (c) 2003, 2007, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ORA-31631: privileges are required ORA-39149: cannot link privileged user to non-privileged user
srt_creen用户需要有imp_full_database权限
dblink中用户需要exp_full_database权限
2、目标端进程报OGG-01003
Opened trail file /opt/OGG/dirdat/U3000002 at 2011-12-06 12:34:47 Switching to next trail file /opt/OGG/dirdat/U3000003 at 2011-12-06 12:35:21 due to EOF, with current RBA 1028 Opened trail file /opt/OGG/dirdat/U3000003 at 2011-12-06 12:35:21 Processed extract process graceful restart record at seq 3, rba 993. Processed extract process graceful restart record at seq 3, rba 1051. MAP resolved (entry CSCNEW.TAB_CS_CALL_PICKUP): MAP CSCNEW.TAB_CS_CALL_PICKUP, TARGET SRT_CREEN.TAB_CS_CALL_PICKUP, KEYCOLS (CALL_ID); Using following columns in default map by name: PICKUP_ID, CALL_ID, CALL_SERIAL, USER_ID, PICKUP_TIME, CALL_RESULT, FAIL_REASON, CALL_TIME, CALL_DURA, END_TIME Using the following key columns for target table SRT_CREEN.TAB_CS_CALL_PICKUP: CALL_ID. 2011-12-06 12:35:22 WARNING OGG-00869 OCI Error ORA-01407: cannot update ("SRT_CREEN"."TAB_CS_CALL_PICKUP"."PICKUP_ID") to NULL (status = 1407), SQL <UPDAT E "SRT_CREEN"."TAB_CS_CALL_PICKUP" SET "PICKUP_ID" = :a0,"CALL_SERIAL" = :a2,"USER_ID" = :a3,"PICKUP_TIME" = :a4,"CALL_RESULT" = :a5,"FAIL_REASON" = :a6,"CAL L_TIME" = :a7,"CALL_DURA" = :a8,"END_T>. 2011-12-06 12:35:23 WARNING OGG-01004 Aborted grouped transaction on 'SRT_CREEN.TAB_CS_CALL_PICKUP', Database error 1407 (OCI Error ORA-01407: cannot updat e ("SRT_CREEN"."TAB_CS_CALL_PICKUP"."PICKUP_ID") to NULL (status = 1407), SQL <UPDATE "SRT_CREEN"."TAB_CS_CALL_PICKUP" SET "PICKUP_ID" = :a0,"CALL_SERIAL" = :a2,"USER_ID" = :a3,"PICKUP_TIME" = :a4,"CALL_RESULT" = :a5,"FAIL_REASON" = :a6,"CALL_TIME" = :a7,"CALL_DURA" = :a8,"END_T>). 2011-12-06 12:35:23 WARNING OGG-01003 Repositioning to rba 1111 in seqno 3.
部署ogg时配置relicat错误:
非MAP CSCNEW.TAB_CS_CALL_PICKUP, TARGET SRT_CREEN.TAB_CS_CALL_PICKUP, KEYCOLS (CALL_ID);
而是MAP CSCNEW.TAB_CS_CALL_PICKUP, TARGET SRT_CREEN.TAB_CS_CALL_PICKUP, KEYCOLS (PICKUP_ID);
3、update同步失败
GGSCI (srtcreen) 12> stats r_3 Sending STATS request to REPLICAT R_3 ... Start of Statistics at 2011-12-06 12:37:49. DDL replication statistics: *** Total statistics since replicat started *** Operations 0.00 Mapped operations 0.00 Unmapped operations 0.00 Other operations 0.00 Excluded operations 0.00 Errors 0.00 Retried errors 0.00 Discarded errors 0.00 Ignored errors 0.00 Replicating from CSCNEW.TAB_CS_CALL_PICKUP to SRT_CREEN.TAB_CS_CALL_PICKUP: *** Total statistics since 2011-12-06 12:37:43 *** Total inserts 2.00 Total updates 6.00 Total deletes 0.00 Total discards 0.00 Total operations 8.00 Total update collisions 6.00 *** Daily statistics since 2011-12-06 12:37:43 *** Total inserts 2.00 Total updates 6.00 Total deletes 0.00 Total discards 0.00 Total operations 8.00 Total update collisions 6.00 *** Hourly statistics since 2011-12-06 12:37:43 *** Total inserts 2.00 Total updates 6.00 Total deletes 0.00 Total discards 0.00 Total operations 8.00 Total update collisions 6.00 *** Latest statistics since 2011-12-06 12:37:43 *** Total inserts 2.00 Total updates 6.00 Total deletes 0.00 Total discards 0.00 Total operations 8.00 Total update collisions 6.00 End of Statistics.
源端忘记执行add trandata 同步表
add trandata CSCNEW.TAB_CS_USER add trandata CSCNEW.TAB_CS_QUEUES add trandata CSCNEW.TAB_CS_CALL_PICKUP add trandata CSCNEW.TAB_CS_CALL_COMING add trandata CSCNEW.TAB_CS_CALLLOG
发表在 GoldenGate, 逻辑备份/恢复
评论关闭