标签云
asm 恢复 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 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)
- 操作系统 (100)
- 数据库 (1,597)
- DB2 (22)
- MySQL (70)
- Oracle (1,463)
- Data Guard (49)
- EXADATA (7)
- GoldenGate (21)
- ORA-xxxxx (158)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (13)
- ORACLE 21C (3)
- Oracle ASM (65)
- Oracle Bug (7)
- Oracle RAC (47)
- Oracle 安全 (6)
- Oracle 开发 (27)
- Oracle 监听 (27)
- Oracle备份恢复 (530)
- Oracle安装升级 (84)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (75)
- PostgreSQL (17)
- PostgreSQL恢复 (5)
- SQL Server (27)
- SQL Server恢复 (8)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (36)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (19)
-
最近发表
- Oracle 19c/21c最新patch信息-202404
- PostgreSQL恢复系列:pg_filedump批量处理
- PostgreSQL部分主要字典信息
- PostgreSQL恢复系列:pg_filedump恢复字典构造
- PostgreSQL 16 源码安装
- ORA-00742 ORA-00312 恢复
- 数据库open成功后报ORA-00353 ORA-00354错误引起的一系列问题(本质ntfs文件系统异常)
- ORA-600 ktsiseginfo1故障
- ORA-00600: internal error code, arguments: [16703], [1403], [4] 原因
- 最近遇到几起ORA-600 16703故障(tab$被清空),请引起重视
- ORA-600 2662快速恢复之Patch scn工具
- TNS-12518: TNS:listener could not hand off client connection
- ora.storage无法启动报ORA-12514故障处理
- 断电引起文件scn异常数据库恢复
- ORA-16188: LOG_ARCHIVE_CONFIG settings inconsistent with previously started instance
- .[hudsonL@cock.li].mkp勒索加密数据库完美恢复
- 模拟带库实现rman远程备份
- 又一例:ORA-600 kclchkblk_4和2662故障
- Oracle误删除数据文件恢复
- Oracle 19C 备库DML重定向—DML Redirection
月归档:九月 2015
ORA-01122 ORA-01210 故障恢复
有朋友数据文件头出现错误ORA-01122和ORA-01210等错误,数据库无法正常open。
因为平台是win,他们找我咨询win bbed,因为回老家电脑没有带,无法提供win的bbed.我通过dd部分文件头,然后在linux平台分析发现是该文件的文件头block大量坏块
bbed分析坏块情况
BBED> show all FILE# 0 BLOCK# 1 OFFSET 0 DBA 0x00000000 (0 0,1) FILENAME /tmp/30.dbf BIFILE bifile.bbd LISTFILE BLOCKSIZE 8192 MODE Browse EDIT Unrecoverable IBASE Dec OBASE Dec WIDTH 80 COUNT 512 LOGFILE log.bbd SPOOL No BBED> set count 64 COUNT 64 BBED> map File: /tmp/30.dbf (0) Block: 1 Dba:0x00000000 ------------------------------------------------------------ BBED-00400: invalid blocktype (27) BBED> d File: /tmp/30.dbf (0) Block: 1 Offsets: 0 to 63 Dba:0x00000000 ------------------------------------------------------------------------ 1ba20000 03004400 bffd8a1d 0000000c acba0000 008f4500 00003455 fc020000 02040000 00000000 00008001 04000000 00000000 00000000 949400b4 94514005 <32 bytes per line> BBED> set block +1 BLOCK# 2 BBED> map File: /tmp/30.dbf (0) Block: 2 Dba:0x00000000 ------------------------------------------------------------ BBED-00400: invalid blocktype (27) BBED> d File: /tmp/30.dbf (0) Block: 2 Offsets: 0 to 63 Dba:0x00000000 ------------------------------------------------------------------------ 1ba20000 04004400 bffd8a1d 0000000c a6e00000 008f4500 00003455 fc020000 0204e81f 00000000 0000241e 05000000 00000000 00000000 11fc297f b426fe2b <32 bytes per line> BBED> set block +1 BLOCK# 3 BBED> d File: /tmp/30.dbf (0) Block: 3 Offsets: 0 to 63 Dba:0x00000000 ------------------------------------------------------------------------ 1ba20000 05004400 bffd8a1d 0000000c 780a0000 008f4500 00003455 fc020000 0204e81f 00000000 0000c001 06000000 00000000 00000000 2969a0d2 d30168a2 <32 bytes per line> BBED> set block +1 BLOCK# 4 BBED> d File: /tmp/30.dbf (0) Block: 4 Offsets: 0 to 63 Dba:0x00000000 ------------------------------------------------------------------------ 1ba20000 06004400 bffd8a1d 0000000c 6c5a0000 008f4500 00003455 fc020000 0204e81f 00000000 0000f81d 07000000 00000000 00000000 7b51d409 6dc7ca4d <32 bytes per line> BBED> set block +1 BLOCK# 5 BBED> d File: /tmp/30.dbf (0) Block: 5 Offsets: 0 to 63 Dba:0x00000000 ------------------------------------------------------------------------ 1ba20000 07004400 bffd8a1d 0000000c c5600000 008f4500 00003455 fc020000 02040000 00000000 0000c001 08000000 00000000 00000000 14514005 25145200 <32 bytes per line> BBED> set block +1 BLOCK# 6 BBED> d File: /tmp/30.dbf (0) Block: 6 Offsets: 0 to 63 Dba:0x00000000 ------------------------------------------------------------------------ 1ba20000 08004400 bffd8a1d 0000000c 60480000 008f4500 00003455 fc020000 0204e81f 00000000 0000c301 09000000 00000000 00000000 c2a1606a 7615130a <32 bytes per line> BBED> set block +1 BLOCK# 7 BBED> d File: /tmp/30.dbf (0) Block: 7 Offsets: 0 to 63 Dba:0x00000000 ------------------------------------------------------------------------ 1ba20000 09004400 bffd8a1d 0000000c e3430000 008f4500 00003455 fc020000 0204e81f 00000000 00000002 0a000000 00000000 00000000 00a28a28 00a28a28 <32 bytes per line> BBED> set block +1 BLOCK# 8 BBED> d File: /tmp/30.dbf (0) Block: 8 Offsets: 0 to 63 Dba:0x00000000 ------------------------------------------------------------------------ 1ba20000 0a004400 07fe8a1d 0000000c fc000000 008f4500 00003455 fc020000 0205e81f 00000000 0000f41d 00000000 00000000 00000000 ffd8ffe0 00104a46 <32 bytes per line> BBED> set block +1 BLOCK# 9 BBED> d File: /tmp/30.dbf (0) Block: 9 Offsets: 0 to 63 Dba:0x00000000 ------------------------------------------------------------------------ 1ba20000 0b004400 07fe8a1d 0000000c 48da0000 008f4500 00003455 fc020000 0205e81f 00000000 0000c601 01000000 00000000 00000000 b47d69d3 7fa96a6f <32 bytes per line> BBED> set block +1 BLOCK# 10 BBED> d File: /tmp/30.dbf (0) Block: 10 Offsets: 0 to 63 Dba:0x00000000 ------------------------------------------------------------------------ 1ba20000 0c004400 07fe8a1d 0000000c be0f0000 008f4500 00003455 fc020000 0205e81f 00000000 0000181d 02000000 00000000 00000000 9de3e868 4782d83a <32 bytes per line> BBED> set block +1 BLOCK# 11 BBED> d File: /tmp/30.dbf (0) Block: 11 Offsets: 0 to 63 Dba:0x00000000 ------------------------------------------------------------------------ 1ba20000 0d004400 07fe8a1d 0000000c 9cd00000 008f4500 00003455 fc020000 0205e81f 00000000 0000241e 03000000 00000000 00000000 dead1259 5919e385 <32 bytes per line> BBED> set block +1 BLOCK# 12 BBED> d File: /tmp/30.dbf (0) Block: 12 Offsets: 0 to 63 Dba:0x00000000 ------------------------------------------------------------------------ 1ba20000 0e004400 07fe8a1d 0000000c df450000 008f4500 00003455 fc020000 0205e81f 00000000 00004001 04000000 00000000 00000000 31d9a292 9698828a <32 bytes per line> BBED> set block +1 BLOCK# 13 BBED> d File: /tmp/30.dbf (0) Block: 13 Offsets: 0 to 63 Dba:0x00000000 ------------------------------------------------------------------------ 1ba20000 0f004400 07fe8a1d 0000000c 18790000 008f4500 00003455 fc020000 02050000 00000000 00000002 05000000 00000000 00000000 b93f8235 5ea063b7 <32 bytes per line>
拿block 1的rdba(04004400–倒序存储)分析[win文件拷贝到linux后使用bbed查看相差1 block]可以的出来block信息为file=1, block=262148,明显错误.
通过dul分析文件头损坏情况
Data UnLoader: 10.2.0.6.9 - Internal Only - on Tue Sep 29 22:15:22 2015 with 64-bit io functions Copyright (c) 1994 2015 Bernard van Duijnen All rights reserved. Strictly Oracle Internal Use Only DUL: Warning: Recreating file "dul.log" Reading SCANNEDLOBPAGE.dat 1204 entries loaded and sorted 1204 entries Reading SEG.dat 0 entries loaded Reading EXT.dat 44 entries loaded and sorted 44 entries Reading COMPATSEG.dat 0 entries loaded DUL: Warning: Wrong DBA 0X00440004 (file=1, block=262148) (Ignored) DUL: Error: While processing file# 30 block# 1 DUL: Warning: Found mismatch while checking file E:\TEMP\shebao\30.dbf DUL: Warning: DUL osd_parameter or control.dul configuration error DUL: Warning: Given file number(30) in control file does not match file# in dba(1) DUL: Warning: Wrong DBA 0X00440004 (file=1, block=262148) (Ignored) DUL: Error: While processing file# 30 block# 1 DUL>
通过bbed和dul证明文件头大量损坏,而且尚未有任何该文件的物理备份,因此恢复起来难道较大。
分析Oracle Database Recovery Check Result
通过对Oracle数据库异常恢复检查脚本(Oracle Database Recovery Check)的分析结果,我们意外的发现,人品不错,发现异常的文件创建时间为2015-09-26 19:39:33,进一步和客户沟通,这个文件存储为图片,少量丢失可以允许,优先恢复业务
有了这个结论,那处理起来就easy了,直接offline异常文件,然后分析丢失的表
从而确定时lob字典的少量extent数据分配到了file 30上
为了避免查询对应lob之时出现错误,通过update 对应lob为空规避该问题
create table corrupt_lobs (corrupt_rowid rowid,table_name varchar2(100)); declare n number; begin for cursor_lob in (select rowid r, xff_lob from xff.t_xifenfei) loop begin n:=dbms_lob.instr(cursor_lob.xff_lob,hextoraw('889911')); exception when others then insert into corrupt_lobs values (cursor_lob.r,'xff.t_xifenfei'); commit; end; end loop; end; / update xff.t_xifenfei set xff_lob = empty_blob() where rowid in (select corrupted_rowid from corrupt_lobs);
本次恢复是由于运气好,遇到异常文件刚好是最近加入,而且都是图片,客户允许少量丢失,如果是不允许丢失的数据文件,可能需要通过找历史的该文件的备份(Oracle 12C的第一次异常恢复—文件头坏块),在某些情况下,如果也没有此类备份,只能通过bbed重构block 1(如果有其他异常块一次处理,如果太多无法处理,最少也需要重构block 1),然后尝试open数据库或者使用dul之类工具处理(因为文件头损坏,工具可能不能识别文件无法恢复)
ORA-600 k2vcbk_2 故障恢复
有朋友找到我说他们数据库无法启动,数据库启动报ORA-600[k2vcbk_2]错误,数据库版本为11.2.0.2 RAC,操作系统是AIX 6.1
SQL> recover database; Media recovery complete. SQL> alter database open; alter database open * ERROR at line 1: ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00600: internal error code, arguments: [k2vcbk_2], [], [], [], [], [], [], [], [], [], [], [] Process ID: 7930020 Session ID: 49 Serial number: 14761
数据库节点1日志
Mon Sep 21 15:45:41 2015 Thread 1 advanced to log sequence 54076 (LGWR switch) Current log# 13 seq# 54076 mem# 0: +DG01/xifenfei/onlinelog/group_13.332.779459035 Current log# 13 seq# 54076 mem# 1: +DG01/xifenfei/onlinelog/group_13.344.779582621 Mon Sep 21 15:45:44 2015 Archived Log entry 74655 added for thread 1 sequence 54075 ID 0x5a0bc0e1 dest 1: Mon Sep 21 15:56:18 2015 Errors in file /oracle/diag/rdbms/xifenfei/xifenfei1/trace/xifenfei1_ora_18088342.trc (incident=184348): ORA-00600: 内部错误代码, 参数: [kturPOTS_0], [], [], [], [], [], [], [], [], [], [], [] Incident details in: /oracle/diag/rdbms/xifenfei/xifenfei1/incident/incdir_184348/xifenfei1_ora_18088342_i184348.trc Mon Sep 21 15:56:34 2015 Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. Error 600 trapped in 2PC on transaction 7.16.120119. Cleaning up. Error stack returned to user: ORA-00600: 内部错误代码, 参数: [kturPOTS_0], [], [], [], [], [], [], [], [], [], [], [] Errors in file /oracle/diag/rdbms/xifenfei/xifenfei1/trace/xifenfei1_ora_18088342.trc (incident=184349): ORA-00603: ORACLE 服务器会话因致命错误而终止 ORA-00600: 内部错误代码, 参数: [kturPOTS_0], [], [], [], [], [], [], [], [], [], [], [] Mon Sep 21 15:56:34 2015 Dumping diagnostic data in directory=[cdmp_20150921155634], requested by (instance=1, osid=18088342), summary=[incident=184348]. Incident details in: /oracle/diag/rdbms/xifenfei/xifenfei1/incident/incdir_184349/xifenfei1_ora_18088342_i184349.trc Mon Sep 21 15:56:35 2015 Sweep [inc][184349]: completed Sweep [inc][184348]: completed Sweep [inc2][184348]: completed opiodr aborting process unknown ospid (18088342) as a result of ORA-603 Mon Sep 21 15:57:12 2015 Errors in file /oracle/diag/rdbms/xifenfei/xifenfei1/trace/xifenfei1_smon_7536810.trc (incident=184274): ORA-00600: internal error code, arguments: [k2vcbk_2], [], [], [], [], [], [], [], [], [], [], [] Incident details in: /oracle/diag/rdbms/xifenfei/xifenfei1/incident/incdir_184274/xifenfei1_smon_7536810_i184274.trc Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. Mon Sep 21 15:57:16 2015 Dumping diagnostic data in directory=[cdmp_20150921155716], requested by (instance=1, osid=7536810 (SMON)), summary=[incident=184274]. Fatal internal error happened while SMON was doing active transaction recovery. Errors in file /oracle/diag/rdbms/xifenfei/xifenfei1/trace/xifenfei1_smon_7536810.trc: ORA-00600: internal error code, arguments: [k2vcbk_2], [], [], [], [], [], [], [], [], [], [], [] SMON (ospid: 7536810): terminating the instance due to error 474 Mon Sep 21 15:57:18 2015 ORA-1092 : opitsk aborting process
数据库节点2日志
Mon Sep 21 15:21:50 2015 Archived Log entry 74653 added for thread 2 sequence 23559 ID 0x5a0bc0e1 dest 1: Mon Sep 21 15:44:28 2015 Thread 2 advanced to log sequence 23561 (LGWR switch) Current log# 12 seq# 23561 mem# 0: +DG01/xifenfei/onlinelog/group_12.338.779457003 Current log# 12 seq# 23561 mem# 1: +DG01/xifenfei/onlinelog/group_12.265.779582493 Mon Sep 21 15:44:31 2015 Archived Log entry 74654 added for thread 2 sequence 23560 ID 0x5a0bc0e1 dest 1: Mon Sep 21 15:45:31 2015 DISTRIB TRAN xifenfei.1ebab0a5.20.3.1533822 is local tran 20.3.1533822 (hex=14.03.17677e) insert pending committed tran, scn=14590688068086 (hex=d45.28c781f6) Mon Sep 21 15:45:31 2015 DISTRIB TRAN xifenfei.1ebab0a5.20.3.1533822 is local tran 20.3.1533822 (hex=14.03.17677e)) delete pending committed tran, scn=14590688068086 (hex=d45.28c781f6) Mon Sep 21 15:56:35 2015 Dumping diagnostic data in directory=[cdmp_20150921155634], requested by (instance=1, osid=18088342), summary=[incident=184348]. Mon Sep 21 15:57:10 2015 Error 3135 trapped in 2PC on transaction 20.11.1534704. Cleaning up. Error stack returned to user: ORA-03135: 连接失去联系 opidcl aborting process unknown ospid (9175532) as a result of ORA-604 Mon Sep 21 15:57:17 2015 Dumping diagnostic data in directory=[cdmp_20150921155716], requested by (instance=1, osid=7536810 (SMON)), summary=[incident=184274]. Mon Sep 21 15:57:23 2015 Reconfiguration started (old inc 18, new inc 20) List of instances: 2 (myinst: 2) Global Resource Directory frozen * dead instance detected - domain 0 invalid = TRUE Communication channels reestablished Master broadcasted resource hash value bitmaps Non-local Process blocks cleaned out Mon Sep 21 15:57:23 2015 LMS 2: 3 GCS shadows cancelled, 1 closed, 0 Xw survived Mon Sep 21 15:57:23 2015 LMS 0: 2 GCS shadows cancelled, 0 closed, 0 Xw survived Mon Sep 21 15:57:23 2015 LMS 1: 3 GCS shadows cancelled, 1 closed, 0 Xw survived Set master node info Submitted all remote-enqueue requests Dwn-cvts replayed, VALBLKs dubious All grantable enqueues granted Post SMON to start 1st pass IR Mon Sep 21 15:57:23 2015 minact-scn: Inst 2 is now the master inc#:20 mmon proc-id:6816208 status:0x7 minact-scn status: grec-scn:0x0000.00000000 gmin-scn:0x0d45.28c2bb5c gcalc-scn:0x0d45.28c3bd2e minact-scn: master found reconf/inst-rec before recscn scan old-inc#:20 new-inc#:20 Mon Sep 21 15:57:23 2015 Instance recovery: looking for dead threads Submitted all GCS remote-cache requests Post SMON to start 1st pass IR Fix write in gcs resources Reconfiguration complete Beginning instance recovery of 1 threads parallel recovery started with 31 processes Started redo scan Completed redo scan read 12626 KB redo, 1724 data blocks need recovery Started redo application at Thread 1: logseq 54076, block 184416 Recovery of Online Redo Log: Thread 1 Group 13 Seq 54076 Reading mem 0 Mem# 0: +DG01/xifenfei/onlinelog/group_13.332.779459035 Mem# 1: +DG01/xifenfei/onlinelog/group_13.344.779582621 Completed redo application of 9.78MB Completed instance recovery at Thread 1: logseq 54076, block 209669, scn 14590688357285 1633 data blocks read, 1794 data blocks written, 12626 redo k-bytes read Thread 1 advanced to log sequence 54077 (thread recovery) Mon Sep 21 15:57:33 2015 Error 3113 trapped in 2PC on transaction 21.18.1965522. Cleaning up. Redo thread 1 internally disabled at seq 54077 (SMON) Error stack returned to user: ORA-02050: 事务处理 21.18.1965522 已回退, 某些远程数据库可能有问题 ORA-03113: 通信通道的文件结尾 ORA-02063: 紧接着 line (起自 ZSK) Mon Sep 21 15:57:34 2015 Archived Log entry 74656 added for thread 1 sequence 54076 ID 0x5a0bc0e1 dest 1: Mon Sep 21 15:57:34 2015 ARC0: Archiving disabled thread 1 sequence 54077 Archived Log entry 74657 added for thread 1 sequence 54077 ID 0x5a0bc0e1 dest 1: Mon Sep 21 15:57:35 2015 Thread 2 advanced to log sequence 23562 (LGWR switch) Current log# 8 seq# 23562 mem# 0: +DG01/xifenfei/onlinelog/group_8.334.779456945 Current log# 8 seq# 23562 mem# 1: +DG01/xifenfei/onlinelog/group_8.267.779582453 Mon Sep 21 15:57:36 2015 Errors in file /oracle/diag/rdbms/xifenfei/xifenfei2/trace/xifenfei2_smon_6750672.trc (incident=200218): ORA-00600: internal error code, arguments: [k2vcbk_2], [], [], [], [], [], [], [], [], [], [], [] Incident details in: /oracle/diag/rdbms/xifenfei/xifenfei2/incident/incdir_200218/xifenfei2_smon_6750672_i200218.trc Archived Log entry 74658 added for thread 2 sequence 23561 ID 0x5a0bc0e1 dest 1: Mon Sep 21 15:57:38 2015 minact-scn: master continuing after IR Mon Sep 21 15:57:41 2015 Dumping diagnostic data in directory=[cdmp_20150921155741], requested by (instance=2, osid=6750672 (SMON)), summary=[incident=200218]. Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. Fatal internal error happened while SMON was doing instance transaction recovery. Errors in file /oracle/diag/rdbms/xifenfei/xifenfei2/trace/xifenfei2_smon_6750672.trc: ORA-00600: internal error code, arguments: [k2vcbk_2], [], [], [], [], [], [], [], [], [], [], [] SMON (ospid: 6750672): terminating the instance due to error 474 Mon Sep 21 15:57:41 2015 ORA-1092 : opitsk aborting process Mon Sep 21 15:57:42 2015 ORA-1092 : opitsk aborting process Mon Sep 21 15:57:42 2015 License high water mark = 291 Instance terminated by SMON, pid = 6750672 USER (ospid: 18874814): terminating the instance
通过数据库日志大概可以看出来,由于节点2的分布式事事务异常,而在11.2.0.2中,分布式事务跨节点,引起节点2的pmon清理异常事务,但是由于bug,使得异常事务无法被清理掉,从而引起节点1 crash,节点1 crash之后节点2进行恢复,也因为分布式事务bug,导致smon回滚失败,实例也crash。无法进行回滚导致数据库无法正常启动,通过查询mos发现定位到是Bug 10222544 ORA-600 [k2vpci_2] from multi-branch distributed transaction
对于这类问题,由于分布事务无法清理,处理方法就是找出来事务人工提交或者直接屏蔽掉这个事务解决该问题
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实现表测试后.