shareplex 同步数据库

源端 OS：Red Hat Enterprise Linux Server release 5.5 (Tikanga) ORACLE：Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production shareplex目录：/oradata/shareplex 目的端： OS：Red Hat Enterprise Linux Server release 5.8

源端

OS：Red Hat Enterprise Linux Server release 5.5 (Tikanga)

ORACLE：Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

shareplex目录：/oradata/shareplex

目的端：

OS：Red Hat Enterprise Linux Server release 5.8 (Tikanga)

ORACLE：Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

shareplex目录：/data/shareplex

shareplex软件：SharePlex-7.6.1-b27-oracle100-rh-40-amd64-m64.tar

解压后：SharePlex-7.6.1-b27-oracle100-rh-40-amd64-m64.tpm

1)创建SPLEX用户及设置归档模式

A 、源端与目标端--创建用户及授权(注意系统时间)：

create user splex identified by splex default tablespace users;----最好为SPLEX用户单独创建一个表空间

grant dba,connect,resource to splex;---SPLEX必须有DBA权限

源端必须处于归档模式

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 7
Next log sequence to archive 9
Current log sequence 9

alter database add supplemental log data (primary key,unique index) columns;

目标端创建需同步进去的数据(注意在目标端创建好源端默认表空间及数据表空间)

create user test identified by test default tablespace TBS_DATA01;
grant connect,resource,unlimited tablespace to test;

2) 安装shareplex

源端

$ ./SharePlex-7.6.1-b27-oracle100-rh-40-amd64-m64.tpm
Unpacking ..................................................................
..........................................................................
..........................................................................
..........................................................................

SharePlex for Oracle installation program:
SharePlex Version: 7.6.1
Supported Oracle Version: 10gR2
Build platform: rh-40-amd64
Target platform: rh-40-amd64


Please enter the product directory location? /oradata/shareplex/prodir
Please enter the variable data directory location? /oradata/shareplex/vardir
Please specify the SharePlex Admin group (select a number):
1. [oinstall]
2. dba
3. oper
?
Please wait while the installer obtains Oracle information ..
Please enter the ORACLE_SID that corresponds to this installation? [hrdb]
Please enter the ORACLE_HOME directory that corresponds to this ORACLE_SID? [/opt/app/oracle/product/10.2.0/db_1]
Please enter the TCP/IP port number for SharePlex communications? [2100] 2200


Preparing to install SharePlex for Oracle v. 7.6.1:
User: oracle
Admin Group: oinstall
Product Directory: /oradata/shareplex/prodir
Variable Data Directory: /oradata/shareplex/vardir
ORACLE_SID: hrdb
ORACLE_HOME: /opt/app/oracle/product/10.2.0/db_1


Proceed with installation? [yes]
Installing ................................................................
.........................................................................
....................................................................
Setting file ownerships ...................................................
.........................................................................
........
Setting file permissions ..................................................
.........................................................................
.........
Do you have a valid SharePlex for Oracle v. 7.6.1 license? [yes]
Please enter the License key? XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
Please enter the customer name associated with this license key? YYYYYYYYYYYYYYYYYYYYYYYYYYY


SharePlex for Oracle v. 7.6.1 license validation successful:
Customer Name: YYYYYYYYYYYYYYYYYYYYYYYYYYY
License Key: XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
Product Name: SharePlex for Oracle - RAC
License Key Type: "Perpetual Key"


NOTE: You can upgrade this license key or add license keys for additional machines
by executing utility /oradata/shareplex/prodir/install/splex_add_key.


Installation log saved to: /home/oracle/.shareplex/INSTALL-SharePlex-7.6.1-1106130040.log
SharePlex for Oracle v. 7.6.1 installation successful.

$ ./ora_setup

Welcome to the Oracle SharePlex setup process.
This process creates tables and user accounts needed to run
Oracle SharePlex replication.

Please note the following:
** In response to prompts, a carriage return will choose the default
given in brackets. If there is no default, a reply must be entered.

** To exit the program while the program is waiting for input, use the
CTRL-C key sequence.
This sequences can be entered by holding down the CONTROL key and
pressing the C key.

Enter the Oracle SID for which SharePlex should be installed [hrdb] :

In order to create the SharePlex tables and user account, we must
connect to the database as a DBA user

Enter a DBA user name : system
Enter password for the DBA account, which will not echo :

注意：RAC环境下，此处输入oracle数据库system帐户的口令，但应当在口令的后面加上@TNS_ALIAS,然后回车；

connecting--This may take a few seconds.

validating user name and password. . . This may take a few seconds.
SharePlex objects will need to be created under a special
account. You can pick an existing user or create a new one.


Would you like to create a new SharePlex user ? [y] : n
Enter username of an existing user : splexhr
Enter user password for splexhr :

注意：RAC环境下，此处输入OracleSharePlex用户的口令，但应当在口令的后面加上@TNS_ALIAS, 然后回车；

validating user name and password. . . This may take a few seconds.


Warning: This user is now being granted unlimited tablespace.
This privilege will remain in effect until it is explicitly changed.
SPLEX_ROLE_BOTH already exists; continuing setup . . .


Setup will now install SharePlex objects.

These are the existing tablespaces.

SYSTEM UNDOTBS1 SYSAUX TEMP USERS TBS_DATA01 TBS_DATA02 TBS_DATA03
TBS_INDEX01 TBS_INDEX02 TBS_INDEX03 TBS_SPLEX

Enter the default tablespace for use by SharePlex [TBS_SPLEX] :

Enter the temporary tablespace for use by SharePlex [TEMP] :

Enter the index tablespace for use by SharePlex [TBS_SPLEX] :

Creating SharePlex objects [Installation type: Upgrade]. . .
SPLEXHR.SHAREPLEX_ACTID already exists; continuing setup . . .
SPLEXHR.SHAREPLEX_MARKER already exists; continuing setup . . .
SPLEXHR.SHAREPLEX_OBJMAP already exists; continuing setup . . .
SPLEXHR.SHAREPLEX_OBJMAP_I1 already exists; continuing setup . . .
SPLEXHR.SHAREPLEX_TRANS already exists; continuing setup . . .
SPLEXHR.SHAREPLEX_LOGLIST already exists; continuing setup . . .
SPLEXHR.SHAREPLEX_LOBMAP already exists; continuing setup . . .
SPLEXHR.SHAREPLEX_ROUTES already exists; continuing setup . . .
SPLEXHR.SHAREPLEX_ROUTES_I1 already exists; continuing setup . . .
SPLEXHR.SHAREPLEX_ROUTES_I2 already exists; continuing setup . . .
SPLEXHR.SHAREPLEX_WILDCARD already exists; continuing setup . . .
SPLEXHR.SHAREPLEX_WILDCARD_I1 already exists; continuing setup . . .
SPLEXHR.SHAREPLEX_WILDCARD_I2 already exists; continuing setup . . .
SPLEXHR.SHAREPLEX_PARTITION_CACHE already exists; continuing setup . . .
SPLEXHR.SHAREPLEX_PARTITION_CACHE_I1 already exists; continuing setup . . .
SPLEXHR.SHAREPLEX_LOB_CACHE already exists; continuing setup . . .
SPLEXHR.SHAREPLEX_LOB_CACHE_I1 already exists; continuing setup . . .
SPLEXHR.SHAREPLEX_CONFIG already exists; continuing setup . . .
SPLEXHR.SHAREPLEX_COMMAND already exists; continuing setup . . .
SPLEXHR.SHAREPLEX_JOBID already exists; continuing setup . . .
SPLEXHR.SHAREPLEX_JOBS already exists; continuing setup . . .
SPLEXHR.SHAREPLEX_JOB_STATS already exists; continuing setup . . .
SPLEXHR.SHAREPLEX_DATAEQUATOR already exists; continuing setup . . .
already exists; continuing setup . . .
already exists; continuing setup . . .
already exists; continuing setup . . .
SPLEXHR.DEMO_SRC already exists; continuing setup . . .
SPLEXHR.DEMO_DEST already exists; continuing setup . . .
SPLEXHR.SHAREPLEX_ACT_MARKER already exists; continuing setup . . .
SPLEXHR.SHAREPLEX_PARTITION already exists; continuing setup . . .
SPLEXHR.SHAREPLEX_OOS_MASTER already exists; continuing setup . . .
SPLEXHR.SHAREPLEX_OOS_KEYS already exists; continuing setup . . .
SPLEXHR.SHAREPLEX_SYNC_MARKER already exists; continuing setup . . .
SPLEXHR.SHAREPLEX_CHANGE_OBJECT already exists; continuing setup . . .
SPLEXHR.SHAREPLEX_DDL_CONTROL already exists; continuing setup . . .

Creating SharePlex Oracle-timezone-region map . . . Done.

Creating Conflict Resolution Package . . . Done.

Setup of SharePlex objects successful . . .

Changing SharePlex parameter database . . .

Setup completed successfully

3）目标端安装shareplex

安装shareplex过程中./SharePlex-7.6.1-b27-oracle100-rh-40-amd64-m64.tpm及./ora_setup类似，不同

之外在于

Oracle ASM detected. Enable SharePlex ASM support? [y] :
SharePlex ASM support enabled.

(备注：如在源端或目标端都有安装过shareplex,则记得清除SPLEX用户capture或post信息,则在启动前记得以下操作:

./ora_cleansp splexhr/splexhr

)

3) 源端操作

A 、启动shareplex

[oracle@hrdb bin]$ ./sp_cop -u2200 &
[1] 25839
[oracle@hrdb bin]$
*******************************************************
* SharePlex for Oracle Startup
* 10 Quest Software, Inc.
* ALL RIGHTS RESERVED.
* Protected by U.S. Patents: 7,461,103 and 7,065,538
* Version: 7.6.1.27-m64-oracle100
* VarDir : /oradata/shareplex/vardir
* Port : 2200
*******************************************************

B 、进入控制台

[oracle@hrdb bin]$ ./sp_ctrl

*******************************************************
* SharePlex for Oracle Command Utility
* 10 Quest Software, Inc.
* ALL RIGHTS RESERVED.
* Protected by U.S. Patents: 7,461,103 and 7,065,538
*******************************************************
C 、添加配置文件
sp_ctrl (hrdb:2200)> list config

File Name State Datasource
-------------------------------------------------- ---------- ---------------
ORA_config Inactive o.SOURCE_SID
Last Modified At: 13-Jun-11 00:43 Size: 151

sp_ctrl (hrdb:2200)> copy config ORA_config to hr_config

sp_ctrl (hrdb:2200)> view config hr_config

datasource:o.hrdb

#source tables target tables routing map

splex.demo_src splex.demo_dest [e-mail]
expand TEST.% TEST.% [e-mail]

4)目标端操作

$./sp_cop -u2200 &

$./sp_ctrl

sp_ctrl (backupdb:2200)> status

Brief Status for backupdb
Process State PID Running Since
--------------- ------------------------------ -------- --------------------
Cop Running 26483 31-Jul-12 09:31:06
Cmd & Ctrl Running 26485 31-Jul-12 09:31:14
There are no active configuration files

sp_ctrl (backupdb:2200)> stop post

5)源端

sp_ctrl (hrdb:2200)> activate config hr_config

Not all tables activated successfully

源端-导出数据(这里使用SCN来保证 一致性):

SQL> set num 50
SQL> select current_scn from v$database;
CURRENT_SCN
--------------------------------------------------
165290627611

expdp system/XXXX DIRECTORY=DUMP_DIR DUMPFILE=20120730_HR.dmp FLASHBACK_SCN=165290627611 SCHEMAS=test LOGFILE=20120730_HR.log

或用EXP方式

6) 目标端

impdp system/oracle DIRECTORY=DUMP_DIR DUMPFILE=20120730_HR.dmp SCHEMAS=test LOGFILE=impdp_2012730_HR.log

或用IMP方式

禁用查找相关JOB

select job_name from dba_scheduler_jobs where OWNER='TEST';

禁用触发器

select 'alter trigger '||owner||'.'||object_name||' disable'
from dba_objects
where object_type='TRIGGER' and owner='TEST';

查找外键及约束

select 'alter table '||t.owner||'.'||t.table_name||' disable constraint '||t.constraint_name||';'
From dba_constraints t
where owner='TEST' and constraint_type='R';

sp_ctrl (backupdb:2200)> qstatus

Queues Statistics for backupdb
Name: hrdb (o.hrdb-o.backupdb) (MTPost queue)
Number of messages: 207 (Age 0 min; Size 0 mb)
Backlog (messages): 207 (Age 0 min)

sp_ctrl (backupdb:2200)>reconcile queue hrdb for o.hrdb-o.backupdb scn 165290627611

sp_ctrl (backupdb:2200)>start post

(

清除源端或目标端下队列记录信息：

$ ./ora_cleansp splexhr/splexhr

在CONFIG文件配置错误的情况下已经activate时，需deactivate config后再行编辑激活；否则，有可能激活CONFIG后，一直HANG住

)

***********************************

注意在RAC环境下:

1、如果两个节点的实例名字不一样，就必须在Oracle10g RAC的两个节点的tnsnames.ora文件中都建立一个TNS别名，然后在/etc/oratab文件中添加如下入口：

splex:/oracle/product/db/10.2:N

其中splex为新建的TNS别名；ORACLE_HOME为Oracle的HOME目录的全路径

2、如果RAC中各个节点的ORACLE_HOME不同，应该在两个节点上oracle用户下创建相同符号连接指向示本地的ORACLE_HOME 。然后编辑oratab文件，将文件中的路径改成符号连接。

# ln -s /local_ORACLE_HOME /$ORACLE_HOME

编辑 oratab file : SID:/pathname_to_symbolic_link:N