记一次以前搭建的测试记录,后来给客户搭建了生产,无奈这个东西老是坏,还找不到任何提示,最后改用的ORACLE DATAGUARD。
(以前公众号名:IT攻城狮的学习笔记发布过,由于格式太乱,现在重新排版发出来。)
1、CDC概述
2、测试环境概述
3、数据库日志模式开启
4、创建安装IDR软件用户
5、安装ACCESSSERVER软件
6、安装CDC软件
7、创建CDC用户及表空间
8、启动ACCESSSERVER并创建管理用户
9、配置CDC实例
10、启动CDC实例
11、安装IIDR_ManagementConsole
12、源端数据库创建示例OE
13、目标端创建OE用户
14、登录控制台,创建数据库
15、创建预订,同步数据表
1、CDC概述
InfoSphere Data Replication近乎实时地在异构数据存储之间复制信息。它提供可保持信息完整性的复制,以支持数据库迁移、应用程序整合、仓储、主数据管理、业务分析和数据质量流程。
InfoSphere Data Replication功能特性:
更加快捷的数据复制
·交付确保事务完整性的可扩展低延迟复制。
·提供双主机 (active-active) 数据库数据复制功能和高可用性。
·提供 IBM DB2® 数据定义语言 (DDL)(如创建和删除表、改变数据类型和添加列)的自动化复制。
·提供冲突检测和解决,以支持对多个数据库所做的数据变更。
·通过在目标系统中连续交付变更数据来减少批处理窗口。
集中式易于使用的平台
·提供一个 GUI,帮助更快地部署数据集成流程。
·支持零下载数据库迁移和应用程序升级。
·提供完整的监控功能,以提高复制环境的运行状况和性能的可视性。
·支持灵活实施,以实现单向、双向、多到一和一到多的数据交付。
异构数据支持
·支持多种异构源和目标数据库,包括 DB2 for Linux、UNIX 和 Windows。
·与 IBM InfoSphere DataStage® 集成,提供变更数据订阅源,进而抽取、变换和装入 (ETL) 流程。
·将数据事务打包到 XML 文档或限定的格式,与 IBM WebSphere® MQ 之类的消息传递中间件一起使用,从而支持面向服务架构 (SOA)。
·与 IBM InfoSphere Data Replication for DB2 for z/OS® 结合使用,与 IBM DB2 for z/OS 相互复制异构数据。
关键组件:
·Access Server - 控制所有以非命令行方式对复制环境进行的访问。当您登录到 Management Console 时,就是连接到 Access Server。您可以在客户机工作站上关闭 Access Server,而不会影响源服务器与目标服务器之间正在进行的数据复制活动。
·管理 API - 以基于 Java 的可选编程接口形式运行。您可以使用此 API 对操作配置或交互进行脚本编制。
·应用代理程序 - 充当目标上的代理程序,用于处理源所发送的更改。
·命令行界面 - 允许您独立于 Management Console 来管理数据存储器和用户帐户,以及执行管理脚本编制。
·通信层 (TCP/IP) - 充当源与目标之间的专用网络连接。
·源和目标数据存储器 - 表示数据复制所需的数据文件和 CDC Replication 实例。每个数据存储器都表示您要连接到的数据库,并且充当表的容器。被设为可供复制的表包含在数据存储器中。
·Management Console - 允许您配置、监视和管理各种服务器上的复制,指定复制参数以及从客户机工作站启动刷新和镜像操作。另外,Management Console 还允许您监视复制操作、等待时间、事件消息以及源或目标数据存储器所支持的其他统计信息。Management Console 中的监视器旨在用于需要持续分析数据移动的时间关键型工作环境。在设置复制之后,您可以在客户机工作站上关闭 Management Console,而不会影响源服务器与目标服务器之间正在进行的数据复制活动。
·元数据 - 表示相关表、映射、预订、通知、事件以及您设置的数据复制实例的其他细节的相关信息。
·镜像 - 将更改复制到目标表或者积累源表更改并在以后将其复制到目标表。如果在环境中实施了双向复制,那么可以在源表与目标表之间来回进行镜像。
·刷新 - 将表从源数据库初始同步到目标。这会由刷新阅读器进行读取。
·复制引擎 - 用于发送和接收数据。用于发送所复制数据的进程是源捕获引擎,而用于接收所复制数据的进程是目标引擎。CDC Replication 实例可以同时作为源捕获引擎和目标引擎运行。
·单次提取 - 充当仅用于源的日志阅读器和日志解析器组件。它会检查并分析所选数据存储器上所有预订的源数据库日志。
·源变换引擎 - 处理行过滤、关键列、列过滤、编码转换以及要传播到目标数据存储器引擎的其他数据。
·源数据库日志 - 由源数据库维护以用于其自身的恢复。CDC Replication 日志阅读器会在镜像过程中检查这些日志,并过滤掉不在复制范围内的表。
·目标变换引擎 - 处理数据和值转换、编码转换、用户出口、冲突检测以及目标数据存储器引擎上的其他数据。
有两种仅用于目标的复制目的地(并不是数据库):
·JMS 消息 - 充当 JMS 消息目标(队列或主题),用于创建为 XML 文档的行级别操作。
·InfoSphere DataStage - 处理从 CDC Replication 传递的更改,这些更改可由 InfoSphere DataStage 作业使用。
2、测试环境概述
数据库 |
IP |
实例名 |
IIDR安装 |
安装用户 |
安装目录 |
源端DB1 |
192.168.100.100 |
orcl |
CDC软件 |
cdc1 |
/opt/cdc |
目标端 DB2 |
192.168.100.101 |
orcl |
CDC软件 ACCESSSERVER |
cdc2 |
/opt/cdc /opt/accessserver |
3、数据库日志模式开启
ORACLE数据库开启归档,归档路径不指的情况下,默认放在闪回空间,请确认闪回空间大小或开启归档之前指定好归档路径
SQL>shutdown immediate;
SQL>alter database archivelog;
SQL>alter database open;
ORACLE数据库开启最小补充日志
SQL> alter database add supplemental log data ;
Database altered.
SQL> SELECT supplemental_log_data_min FROM v$database;
SUPPLEME
--------
YES
SQL> select supplemental_log_data_min min from v$database ;
MIN
--------
YES
4、创建安装IDR软件用户
在192.168.100.100创建安装用户
groupadd cdc
useradd -g cdc -G oinstall,dba cdc1
passwd cdc1
在192.168.100.101创建安装用户
groupadd cdc
useradd -g cdc -G oinstall,dba cdc2
passwd cdc2
5、安装ACCESSSERVER
上传ACCESSSERVER软件及CDC_ORACLE软件到两台主机的/tmp目录下
[root@db1 tmp]# ls -l
total 12
drwxr-xr-x 2 root root 4096 Nov 29 14:26 IIDR_AccessServer
drwxr-xr-x 2 root root 4096 Nov 29 14:26 IIDR_Oracle
drwx------. 2 oracle oinstall 4096 Nov 27 12:25 pulse-ykGa2TZ84DLW
在192.168.100.101上创建软件安装目录
[root@db2 ~]# mkdir -p /opt/accessserver
[root@db2 ~]# chown -R cdc2:cdc /opt/accessserver/
[root@db2 ~]# cd /opt/
[root@db2 opt]# ls -l
total 28
drwxrwxrwx 12 cdc2 cdc 4096 Nov 29 14:35 accessserver
drwx------ 2 root root 16384 Nov 29 13:26 lost+found
drwxr-xr-x. 3 root root 4096 Nov 27 12:21 ORCLfmap
drwxr-xr-x. 2 root root 4096 May 17 2013 rh
切换到cdc2用户,在192.168.100.101上安装accessserver
[root@db2 ~]# cd /tmp/IIDR_AccessServer/
[root@db2 IIDR_AccessServer]# chmod +x iidraccess-11.3.3-4288-linux-x86-setup.bin
[root@db2 IIDR_AccessServer]# su - cdc2
[cdc2@db2 ~]$ cd /tmp/IIDR_AccessServer/
[cdc2@db2 IIDR_AccessServer]$ ls
iidraccess-11.3.3-4288-linux-x86-setup.bin InfoSphere_Data_Replication_Release_Notes_11_3_3.html
开始安装
[cdc2@db2 IIDR_AccessServer]$ ./iidraccess-11.3.3-4288-linux-x86-setup.bin
Preparing to install...
Extracting the JRE from the installer archive...
Unpacking the JRE...
Extracting the installation resources from the installer archive...
Configuring the installer for this system's environment...
Launching installer...
===============================================================================
Choose Locale...
----------------
1- Deutsch
->2- English
3- Español
4- Italiano
5- Português (Brasil)
CHOOSE LOCALE BY NUMBER:
===============================================================================
IBM InfoSphere Data Replication Access Server (created with InstallAnywhere)
-------------------------------------------------------------------------------
Preparing CONSOLE Mode Installation...
===============================================================================
Introduction
------------
InstallAnywhere will guide you through the installation of IBM InfoSphere Data
Replication Access Server.
It is strongly recommended that you quit all programs before continuing with
this installation.
Respond to each prompt to proceed to the next step in the installation. If you
want to change something on a previous step, type 'back'.
You may cancel this installation at any time by typing 'quit'.
PRESSTO CONTINUE:
===============================================================================
International Program License Agreement
Part 1 - General Terms
BY DOWNLOADING, INSTALLING, COPYING, ACCESSING, CLICKING ON AN
"ACCEPT" BUTTON, OR OTHERWISE USING THE PROGRAM, LICENSEE AGREES TO
THE TERMS OF THIS AGREEMENT. IF YOU ARE ACCEPTING THESE TERMS ON
BEHALF OF LICENSEE, YOU REPRESENT AND WARRANT THAT YOU HAVE FULL
AUTHORITY TO BIND LICENSEE TO THESE TERMS. IF YOU DO NOT AGREE TO
THESE TERMS,
* DO NOT DOWNLOAD, INSTALL, COPY, ACCESS, CLICK ON AN "ACCEPT" BUTTON,
OR USE THE PROGRAM; AND
* PROMPTLY RETURN THE UNUSED MEDIA, DOCUMENTATION, AND PROOF OF
ENTITLEMENT TO THE PARTY FROM WHOM IT WAS OBTAINED FOR A REFUND OF THE
AMOUNT PAID. IF THE PROGRAM WAS DOWNLOADED, DESTROY ALL COPIES OF THE
PROGRAM.
Press Enter to continue viewing the license agreement, or enter "1" to
accept the agreement, "2" to decline it, "3" to print it, or "99" to go back
to the previous screen.: 1
===============================================================================
Enter the TCP/IP port for Access Server.
Port Number: (DEFAULT: 10101):
===============================================================================
Choose Install Folder
---------------------
Where would you like to install?
Default Install Folder: /opt/IBM/InfoSphereDataReplication/AccessServer
ENTER AN ABSOLUTE PATH, OR PRESSTO ACCEPT THE DEFAULT
: /opt/accessserver
INSTALL FOLDER IS: /opt/accessserver
IS THIS CORRECT? (Y/N): y
===============================================================================
Configure User Data Folder
--------------------------
Access Server requires a folder to store logs, configuration information and
user data. Specify a folder where this information should be stored.
Where would you like your user data folder?
Default User Data Folder: /opt/accessserver
ENTER AN ABSOLUTE PATH, OR PRESSTO ACCEPT THE DEFAULT:
===============================================================================
Pre-Installation Summary
------------------------
Please Review the Following Before Continuing:
Product Name:
IBM InfoSphere Data Replication Access Server
Install Folder:
/opt/accessserver
Link Folder:
/home/cdc2
User Data Folder:
/opt/accessserver
Disk Space Information (for Installation Target):
Required: 288,069,645 Bytes
Available: 501,388,230,656 Bytes
PRESSTO CONTINUE:
===============================================================================
Installing...
-------------
[==================|==================|==================|==================]
[------------------|------------------|------------------|------------------]
===============================================================================
Installation Complete
---------------------
Congratulations. IBM InfoSphere Data Replication Access Server has been
successfully installed to:
/opt/accessserver
Before you connect to this Access Server installation, you must start Access
Server and create the administration user account. See the installation guide
for more information. You should also install the equivalent version of IBM
InfoSphere Data Replication Management Console, if you haven't already done so,
before connecting to Access Server.
PRESSTO EXIT THE INSTALLER:
6、安装CDC软件
192.168.100.100上创建安装目录
[root@db1 opt]# mkdir -p /opt/cdc
[root@db1 opt]# chown cdc1:cdc /opt/cdc/
[root@db1 opt]# ls -l
total 28
drwxr-xr-x 2 cdc1 cdc 4096 Nov 29 14:41 cdc
drwx------ 2 root root 16384 Nov 29 13:26 lost+found
drwxr-xr-x. 3 root root 4096 Nov 27 12:21 ORCLfmap
drwxr-xr-x. 2 root root 4096 May 17 2013 rh
192.168.100.101上创建安装目录
[root@db2 opt]# mkdir -p /opt/cdc
[root@db2 opt]# chown cdc2:cdc /opt/cdc/
[root@db2 opt]# ls -l
total 32
drwxrwxrwx 12 cdc2 cdc 4096 Nov 29 14:35 accessserver
drwxr-xr-x 2 cdc2 cdc 4096 Nov 29 14:40 cdc
drwx------ 2 root root 16384 Nov 29 13:26 lost+found
drwxr-xr-x. 3 root root 4096 Nov 27 12:21 ORCLfmap
drwxr-xr-x. 2 root root 4096 May 17 2013 rh
开始在192.168.100.100上安装CDC
[root@db1 opt]# cd /tmp/IIDR_Oracle/
[root@db1 IIDR_Oracle]# chmod +x setup-iidr-linux-x86-oracleredo.bin
[root@db1 IIDR_Oracle]# ls -l
total 190528
-rw-r--r-- 1 root root 19540 Nov 29 14:26
InfoSphere_Data_Replication_Release_Notes_11_3_3.html
-rwxr-xr-x 1 root root 195078158 Nov 29 14:26 setup-iidr-linux-x86-oracleredo.bin
[root@db1 IIDR_Oracle]# su - cdc1
[cdc1@db1 ~]$ cd /tmp/IIDR_Oracle/
[cdc1@db1 IIDR_Oracle]$ ./setup-iidr-linux-x86-oracleredo.bin
Preparing to install...
Extracting the JRE from the installer archive...
Unpacking the JRE...
Extracting the installation resources from the installer archive...
Configuring the installer for this system's environment...
Launching installer...
===============================================================================
Choose Locale...
----------------
1- Deutsch
->2- English
3- Español
4- Italiano
5- Português (Brasil)
CHOOSE LOCALE BY NUMBER:
===============================================================================
Installer (created with InstallAnywhere)
-------------------------------------------------------------------------------
Preparing CONSOLE Mode Installation...
===============================================================================
Introduction
------------
InstallAnywhere will guide you through the installation of IBM InfoSphere Data
Replication (Oracle) 11.3.3
Respond to each prompt to proceed to the next step in the installation. If you
want to change something on a previous step, type 'back'.
You may cancel this installation at any time by typing 'quit'.
PRESSTO CONTINUE:
===============================================================================
International Program License Agreement
Part 1 - General Terms
BY DOWNLOADING, INSTALLING, COPYING, ACCESSING, CLICKING ON AN
"ACCEPT" BUTTON, OR OTHERWISE USING THE PROGRAM, LICENSEE AGREES TO
THE TERMS OF THIS AGREEMENT. IF YOU ARE ACCEPTING THESE TERMS ON
BEHALF OF LICENSEE, YOU REPRESENT AND WARRANT THAT YOU HAVE FULL
AUTHORITY TO BIND LICENSEE TO THESE TERMS. IF YOU DO NOT AGREE TO
THESE TERMS,
* DO NOT DOWNLOAD, INSTALL, COPY, ACCESS, CLICK ON AN "ACCEPT" BUTTON,
OR USE THE PROGRAM; AND
* PROMPTLY RETURN THE UNUSED MEDIA, DOCUMENTATION, AND PROOF OF
ENTITLEMENT TO THE PARTY FROM WHOM IT WAS OBTAINED FOR A REFUND OF THE
AMOUNT PAID. IF THE PROGRAM WAS DOWNLOADED, DESTROY ALL COPIES OF THE
PROGRAM.
Press Enter to continue viewing the license agreement, or enter "1" to
accept the agreement, "2" to decline it, "3" to print it, or "99" to go back
to the previous screen.: 1
===============================================================================
Choose Install Folder
---------------------
Where would you like to install?
Default Install Folder: /opt/IBM/InfoSphereDataReplication/ReplicationEngineforOracle
ENTER AN ABSOLUTE PATH, OR PRESSTO ACCEPT THE DEFAULT
: /opt/cdc
INSTALL FOLDER IS: /opt/cdc
IS THIS CORRECT? (Y/N): y
===============================================================================
Choose Instance Folder
----------------------
Where would you like your instances and product logs created?
ENTER AN ABSOLUTE PATH, OR PRESSTO ACCEPT THE DEFAULT
Instance Folder: (DEFAULT: /opt/cdc):
===============================================================================
Pre-Installation Summary
------------------------
Please Review the Following Before Continuing:
Product Name:
IBM InfoSphere Data Replication (Oracle)
Install Folder:
/opt/cdc
Instance Folder:
"/opt/cdc"
Disk Space Information (for Installation Target):
Required: 306,430,251 Bytes
Available: 501,388,234,752 Bytes
PRESSTO CONTINUE:
===============================================================================
Installing...
-------------
[==================|==================|==================|==================]
[------------------|------------------|------------------|------------------]
===============================================================================
Install Complete
----------------
Congratulations. IBM InfoSphere Data Replication (Oracle) has been successfully
installed to:
/opt/cdc
You can launch the Configuration Tool at any time by running
/opt/cdc/bin/dmconfigurets
Launch Configuration Tool? (1=Yes, 2=No) (DEFAULT: 1): 2
开始在192.168.100.101上安装CDC
[root@db2 opt]# cd /tmp/IIDR_Oracle/
[root@db2 IIDR_Oracle]# chmod +x setup-iidr-linux-x86-oracleredo.bin
[root@db2 IIDR_Oracle]# su - cdc2
[cdc2@db2 ~]$ cd /tmp/IIDR_Oracle/
[cdc2@db2 IIDR_Oracle]$ ./setup-iidr-linux-x86-oracleredo.bin
Preparing to install...
Extracting the JRE from the installer archive...
Unpacking the JRE...
Extracting the installation resources from the installer archive...
Configuring the installer for this system's environment...
Launching installer...
===============================================================================
Choose Locale...
----------------
1- Deutsch
->2- English
3- Español
4- Italiano
5- Português (Brasil)
CHOOSE LOCALE BY NUMBER:
===============================================================================
Installer (created with InstallAnywhere)
-------------------------------------------------------------------------------
Preparing CONSOLE Mode Installation...
===============================================================================
Introduction
------------
InstallAnywhere will guide you through the installation of IBM InfoSphere Data
Replication (Oracle) 11.3.3
Respond to each prompt to proceed to the next step in the installation. If you
want to change something on a previous step, type 'back'.
You may cancel this installation at any time by typing 'quit'.
PRESSTO CONTINUE:
===============================================================================
International Program License Agreement
Part 1 - General Terms
BY DOWNLOADING, INSTALLING, COPYING, ACCESSING, CLICKING ON AN
"ACCEPT" BUTTON, OR OTHERWISE USING THE PROGRAM, LICENSEE AGREES TO
THE TERMS OF THIS AGREEMENT. IF YOU ARE ACCEPTING THESE TERMS ON
BEHALF OF LICENSEE, YOU REPRESENT AND WARRANT THAT YOU HAVE FULL
AUTHORITY TO BIND LICENSEE TO THESE TERMS. IF YOU DO NOT AGREE TO
THESE TERMS,
* DO NOT DOWNLOAD, INSTALL, COPY, ACCESS, CLICK ON AN "ACCEPT" BUTTON,
OR USE THE PROGRAM; AND
* PROMPTLY RETURN THE UNUSED MEDIA, DOCUMENTATION, AND PROOF OF
ENTITLEMENT TO THE PARTY FROM WHOM IT WAS OBTAINED FOR A REFUND OF THE
AMOUNT PAID. IF THE PROGRAM WAS DOWNLOADED, DESTROY ALL COPIES OF THE
PROGRAM.
Press Enter to continue viewing the license agreement, or enter "1" to
accept the agreement, "2" to decline it, "3" to print it, or "99" to go back
to the previous screen.: 1
===============================================================================
Choose Install Folder
---------------------
Where would you like to install?
Default Install Folder: /opt/IBM/InfoSphereDataReplication/ReplicationEngineforOracle
ENTER AN ABSOLUTE PATH, OR PRESSTO ACCEPT THE DEFAULT
: /opt/cdc
INSTALL FOLDER IS: /opt/cdc
IS THIS CORRECT? (Y/N): y
===============================================================================
Choose Instance Folder
----------------------
Where would you like your instances and product logs created?
ENTER AN ABSOLUTE PATH, OR PRESSTO ACCEPT THE DEFAULT
Instance Folder: (DEFAULT: /opt/cdc):
===============================================================================
Pre-Installation Summary
------------------------
Please Review the Following Before Continuing:
Product Name:
IBM InfoSphere Data Replication (Oracle)
Install Folder:
/opt/cdc
Instance Folder:
"/opt/cdc"
Disk Space Information (for Installation Target):
Required: 306,430,251 Bytes
Available: 501,102,718,976 Bytes
PRESSTO CONTINUE:
===============================================================================
Installing...
-------------
[==================|==================|==================|==================]
[------------------|------------------|------------------|------------------]
===============================================================================
Install Complete
----------------
Congratulations. IBM InfoSphere Data Replication (Oracle) has been successfully
installed to:
/opt/cdc
You can launch the Configuration Tool at any time by running
/opt/cdc/bin/dmconfigurets
Launch Configuration Tool? (1=Yes, 2=No) (DEFAULT: 1): 2
7、创建CDC用户及表空间
创建CDC用户使用的表空间
create tablespace cdc datafile '/u01/app/oracle/oradata/orcl/cdc.dbf' size 1G autoextend on;
参照/opt/cdc/samples/目录下的createuser-ora-nodba.sql脚本创建CDC用户
-- create user
CREATE user cdc identified by cdc123
default tablespace cdc temporary tablespace temp;
-- Grant basic roles
grant connect to cdc;
grant resource to cdc;
grant select_catalog_role to cdc;
-- Table DDL permissions
grant create any table to cdc;
grant alter any table to cdc;
grant drop any table to cdc;
grant lock any table to cdc;
-- Table DML permissions
grant select any table to cdc;
grant flashback any table to cdc;
grant insert any table to cdc;
grant update any table to cdc;
grant delete any table to cdc;
-- Index and view DDL permissions
grant create any index to cdc;
grant alter any index to cdc;
grant drop any index to cdc;
grant create any view to cdc;
grant drop any view to cdc;
-- Trigger DDL and DML permissions (only required for CDC Trigger-based)
grant create any trigger to cdc;
grant alter any trigger to cdc;
grant drop any trigger to cdc;
-- Sequence DDL and DML permissions (only required for CDC Trigger-based)
grant create any sequence to cdc;
grant select any sequence to cdc;
-- Procedure permissions
grant create any procedure to cdc;
grant execute any procedure to cdc;
-- Permission to perform select on the v_$ tables
grant select any dictionary to cdc;
-- General system views
grant select on sys.v_$database to cdc;
grant select on sys.v_$controlfile to cdc;
grant select on sys.v_$version to cdc;
grant select on sys.nls_database_parameters to cdc;
-- Archive and redo logs
grant select on sys.v_$log to cdc;
grant select on sys.v_$logfile to cdc;
grant select on sys.v_$archived_log to cdc;
grant select on sys.v_$log_history to cdc;
-- Sessions and transactions
grant alter session to cdc;
grant select on sys.v_$session to cdc;
grant select on sys.gv_$session to cdc;
grant select on sys.v_$transaction to cdc;
grant select on sys.v_$mystat to cdc;
-- Tables, indexes, columns and related views
grant select on sys.all_coll_types to cdc;
grant select on sys.all_type_attrs to cdc;
grant select on sys.dba_tables to cdc;
grant select on sys.dba_tab_comments to cdc;
grant select on sys.dba_tab_columns to cdc;
grant select on sys.dba_col_comments to cdc;
grant select on sys.dba_indexes to cdc;
grant select on sys.dba_ind_columns to cdc;
grant select on sys.all_constraints to cdc;
grant select on sys.dba_constraints to cdc;
grant select on sys.all_cons_columns to cdc;
grant select on sys.dba_cons_columns to cdc;
grant select on sys.tab$ to cdc;
grant select on sys.ind$ to cdc;
grant select on sys.lob$ to cdc;
grant select on sys.col$ to cdc;
grant select on sys.icol$ to cdc;
grant select on sys.coltype$ to cdc;
grant select on sys.attrcol$ to cdc;
grant select on sys.ccol$ to cdc;
grant select on sys.cdef$ to cdc;
-- Miscellaneous other objects
grant select on sys.obj$ to cdc;
grant select on sys.dba_mviews to cdc;
grant select on sys.dba_objects to cdc;
grant select on sys.dba_sequences to cdc;
grant select on sys.hist_head$ to cdc;
grant select on sys.resource_cost to cdc;
-- Storage
grant select on sys.dba_tablespaces to cdc;
grant select on sys.dba_rollback_segs to cdc;
-- Permissions
grant select on sys.dba_users to cdc;
grant select on sys.dba_sys_privs to cdc;
grant select on sys.dba_tab_privs to cdc;
grant select on sys.dba_profiles to cdc;
grant select on sys.dba_roles to cdc;
grant select on sys.user$ to cdc;
grant select on user_role_privs to cdc;
配置两台数据库服务器的
/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File:u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = db1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
# tnsnames.ora Network Configuration File:u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = db2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
8、启动ACCESSERVER并管理用户
8、启动ACCESSSERVER并创建管理用户
在192.168.100.101节点启动accessserver服务,并创建管理员用户admin
[root@db2 ~]# su - cdc2
[cdc2@db2 ~]$ cd /opt/accessserver/bin/
[cdc2@db2 bin]$ nohup ./dmaccessserver &
[1] 32556
[cdc2@db2 bin]$ nohup: ignoring input and appending output to `nohup.out'
[cdc2@db2 bin]$ ps -ef|grep java
cdc2 32556 32537 5 15:30 pts/0 00:00:00 /opt/accessserver/jre64/jre/bin/dmaccessserver-java -Duser.folder=/opt/accessserver -server -Xmx512m -jar lib/server.jar
cdc2 32569 32537 0 15:30 pts/0 00:00:00 grep java
[cdc2@db2 bin]$ ./dmcreateuser admin admin admin admin1234 SYSADMIN TRUE FALSE FALSE
9、配置CDC实例
配置192.168.100.100端CDC实例
[cdc1@db1 ~]$ cd /opt/cdc/bin/
[cdc1@db1 bin]$ ./dmconfigurets
[cdc1@db1 ~]$ cd /opt/cdc/bin/
[cdc1@db1 bin]$ ./dmconfigurets
Welcome to the configuration tool for IBM InfoSphere Data Replication (Oracle). Use this tool to create instances of IBM InfoSphere Data Replication (Oracle).
Press ENTER to continue...
Initializing. Please wait...
CONFIGURATION TOOL - CREATING A NEW INSTANCE
--------------------------------------------
Enter the name of the new instance: orcl
Enter the server port number [11001]:
Enter the auto-discovery port number or type 'DISABLE' [DISABLE]:
Staging Store Disk Quota is used to limit the disk space used by IBM InfoSphere Data Replication staging Store. If this space is exhausted, this instance may run at a lower speed. The minimum value allowed is 1 GB.
Enter the Staging Store Disk Quota for this instance (GB) [100]:
Enter the Maximum Memory Allowed for this instance (MB) [1024]:
Use read-only connection to database (y/n) [n]:
Use archive-only mode (y/n) [n]:
Select y to use JMS or TCP/IP engine communication connection, select n to use TCP only engine communication connection (y/n) [n]:
Enter the path for ORACLE_HOME: /u01/app/oracle/product/11.2.0/dbhome_1
TNS Name:
1. ORCL
2. Other...
Select a TNS Name: 1
Would you like to configure advanced parameters (y/n) [n]:
Enter the username: cdc
Enter the password:
Retrieving schema list...
Metadata schema:
Enter a database schema for metadata tables or press ENTER to list schemas:
1. ANONYMOUS
2. APEX_030200
3. APEX_PUBLIC_USER
4. APPQOSSYS
5. CDC
6. CTXSYS
7. DBSNMP
8. DIP
9. EXFSYS
10. FLOWS_FILES
11. MDDATA
12. MDSYS
13. MGMT_VIEW
14. OLAPSYS
Press ENTER to continue...
15. ORACLE_OCM
16. ORDDATA
17. ORDPLUGINS
18. ORDSYS
19. OUTLN
20. OWBSYS
21. OWBSYS_AUDIT
22. SCOTT
23. SI_INFORMTN_SCHEMA
24. SPATIAL_CSW_ADMIN_USR
25. SPATIAL_WFS_ADMIN_USR
26. SYS
27. SYSMAN
28. SYSTEM
29. WMSYS
Press ENTER to continue...
30. XDB
31. XS$NULL
Select a database schema for metadata tables: 5
NEW INSTANCE: orcl >> Configuration mode
----------------------------------------
1. Local log reading
2. Remote log reading
3. Manual log shipping
4. Log shipping with Data Guard
Enter your selection:1
Validating database support. Please wait...
Retrieving ASM info. Please wait...
Creating a new instance. Please wait...
Instance orcl was successfully created.
Would you like to START instance orcl now (y/n)?n
MAIN MENU
---------
1. List Current Instances
2. Add an Instance
3. Edit an Instance
4. Delete an Instance
5. Exit
Enter your selection:5
Exiting...
同样方法配置192.168.100.101的CDC实例。
10、启动CDC实例
192.168.100.100节点启动CDC实例
[cdc1@db1 bin]$ pwd
/opt/cdc/bin
[cdc1@db1 bin]$ nohup ./dmts64 -I orcl &
[1] 29600
[cdc1@db1 bin]$ nohup: ignoring input and appending output to `nohup.out'
[cdc1@db1 bin]$ ps -ef|grep java
cdc1 29600 29069 64 15:46 pts/0 00:00:12 /opt/cdc/jre64/jre/bin/dmts64-java -cp lib:lib/ts.jar:lib/activation.jar:lib/mail.jar:lib/pbembedded.jar:lib/pbclient.jar:lib/pbtools.jar:lib/cpci.jar:lib/commons-cli.jar:lib/asm-all-3.1.jar:lib/jlog.jar:lib/icu4j/icu4j-53_1.jar:lib/icu4j/icu4j-charset-53_1.jar:lib/icu4j/icu4j-localespi-53_1.jar:lib/CIoracle.jar -Xmx1024M -Xms704M -Xmine64M -XX:NewRatio=1 -Xgcpolicy:gencon -Dcom.sun.management.jmxremote -Duser.folder="/opt/cdc" -Djava.ext.dirs=lib/user:jre64/jre/lib/ext -Dcom.datamirror.ts.instance=orcl com.datamirror.ts.commandlinetools.script.Startup -I orcl
cdc1 29859 29069 0 15:46 pts/0 00:00:00 grep java
192.168.100.101节点启动CDC实例
[cdc2@db2 bin]$ pwd
/opt/cdc/bin
[cdc2@db2 bin]$ nohup ./dmts64 -I orcl &
[1] 33261
[cdc2@db2 bin]$ nohup: ignoring input and appending output to `nohup.out'
[cdc2@db2 bin]$
[cdc2@db2 bin]$ ps -ef|grep java
cdc2 32556 1 0 15:30 pts/0 00:00:01 /opt/accessserver/jre64/jre/bin/dmaccessserver-java -Duser.folder=/opt/accessserver -server -Xmx512m -jar lib/server.jar
cdc2 33261 32627 99 15:46 pts/0 00:00:07 /opt/cdc/jre64/jre/bin/dmts64-java -cp lib:lib/ts.jar:lib/activation.jar:lib/mail.jar:lib/pbembedded.jar:lib/pbclient.jar:lib/pbtools.jar:lib/cpci.jar:lib/commons-cli.jar:lib/asm-all-3.1.jar:lib/jlog.jar:lib/icu4j/icu4j-53_1.jar:lib/icu4j/icu4j-charset-53_1.jar:lib/icu4j/icu4j-localespi-53_1.jar:lib/CIoracle.jar -Xmx1024M -Xms704M -Xmine64M -XX:NewRatio=1 -Xgcpolicy:gencon -Dcom.sun.management.jmxremote -Duser.folder="/opt/cdc" -Djava.ext.dirs=lib/user:jre64/jre/lib/ext -Dcom.datamirror.ts.instance=orcl com.datamirror.ts.commandlinetools.script.Startup -I orcl
cdc2 33321 32627 0 15:46 pts/0 00:00:00 grep java
11、安装IIDR管理控制台
运行iidrmc-11.3.3-4288-setup进行安装
12、源端库示例OE用户
拷贝OE创建脚本到192.168.100.100服务上
[root@db1 ~]# cd /home/oracle/oe/
[root@db1 oe]# pwd
/home/oracle/oe
[root@db1 oe]# ll
total 240
-rw-r--r-- 1 oracle oinstall 9845 Nov 29 15:58 oe_cre.sql
-rw-r--r-- 1 oracle oinstall 2338 Nov 29 15:58 oe_main.sql
-rw-r--r-- 1 oracle oinstall 2508 Nov 29 15:58 oe_p_cat.sql
-rw-r--r-- 1 oracle oinstall 67741 Nov 29 15:58 oe_p_cus.sql
-rw-r--r-- 1 oracle oinstall 42411 Nov 29 15:58 oe_p_itm.sql
-rw-r--r-- 1 oracle oinstall 21860 Nov 29 15:58 oe_p_ord.sql
-rw-r--r-- 1 oracle oinstall 85879 Nov 29 15:58 oe_p_pi.sql
登录sqlplus执行创建脚本@oe_main.sql
[root@db1 oe]# su - oracle
[oracle@db1 ~]$ cd oe/
[oracle@db1 oe]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Nov 29 16:02:29 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @oe_main.sql
13、目标端数据库创建OE用户
在192.168.10.101库里创建OE用户,与源端一样使用默认表空间users
CREATE USER OE IDENTIFIED BY OE DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;
ALTER USER "OE" QUOTA UNLIMITED ON USERS;
GRANT RESOURCE,CONNECT TO OE;
14、登录控制台,创建数据库
运行
选择access manager,点击创建数据库按钮
输入源端数据库数信息,点击PING后,再点击连接参数
输入CDC用户及密码,确定
同样方式添加目标端orcl-target
右键数据库,选择分配用户
同样方式分配orcl-target给admin用户
15、创建预定,同步数据表
点击“配置”,选择创建预订按钮
IB
查192.168.100.101节点查看OE用户下创建出与192.168.100.100中OE相同的表结构
点击“监控”,选择创建的预订,右键选择“开始制作镜像”
点击确定后,开始进行数据同步
还可以在我新的微信公众号找到我