描述：
源数据库为两台集群64位HP-UNIX的Oracle11gR2数据库，
目标数据库为1台64位Linux的Oracle11gR2的数据库，
把源数据库的某个用户的数据传输到目标数据库中
1、源数据库、目标数据库均为归档模式
源：
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +ARCHDG
Oldest online log sequence 60
Next log sequence to archive 61
Current log sequence 61
目标：
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 54
Current log sequence 56
SQL> startup mount
ORACLE instance started.
Total System Global Area 6747725824 bytes
Fixed Size 2213976 bytes
Variable Size 4697622440 bytes
Database Buffers 2013265920 bytes
Redo Buffers 34623488 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
2、设置源、目标数据库的相关参数
源：
SQL> show parameter global_names
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
global_names boolean FALSE
SQL>
SQL> alter system set global_names=true scope=both;
System altered.
SQL> show parameter aq_tm_processes
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 0
SQL> alter system set aq_tm_processes=2 scope=both;
System altered.
目标：
SQL> show parameter global_names
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
global_names boolean FALSE
SQL>
SQL> alter system set global_names=true scope=both;
System altered.
SQL> show parameter aq_tm_processes
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 0
SQL> alter system set aq_tm_processes=2 scope=both;
System altered.
3、在源数据库启用追加日志
启用辅助日志
SQL> alter database add supplemental log data;
Database altered.
4、在源、目标数据库创建表空间、用户、授权
源：
查看数据文件位置：
select file_name from dba_data_files
创建表空间：
create tablespace streams_space
datafile '+DATADG/cboms/datafile/streams_space.dbf'
size 5M autoextend on
创建用户：
create user strmadmin
identified by strmadmin
default tablespace streams_space
授予dba角色：
grant dba to strmadmin
授予流管理权限：
exec dbms_streams_auth.grant_admin_privilege('strmadmin') ----使用sys 或 system在命令行执行
目标：
查看数据文件位置：
select file_name from dba_data_files
创建表空间：
create tablespace streams_space
datafile '/u01/app/oracle/oradata/bhoms/streams_space.dbf'
size 5M autoextend on
创建用户：
create user strmadmin
identified by strmadmin
default tablespace streams_space
授予dba角色：
grant dba to strmadmin
授予流管理权限：
exec dbms_streams_auth.grant_admin_privilege('strmadmin') ----使用sys 或 system在命令行执行
5、配置源、目标数据库的tnsnames.ora
源数据库节点1：
CBDBS01-> cd $ORACLE_HOME
CBDBS01-> pwd
/oracle/db/product/11.2.0/db_1
CBDBS01-> cd network/admin
CBDBS01-> vi tnsnames.ora
在文件中末尾增加：
BHOMS_192.168.2.2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = bhoms)
)
)
测试连接到目标数据库
CBDBS01-> sqlplus testUser /testPassword@BHOMS_192.168.2.2
源数据库节点2：
源数据库节点1：
CBDBS02-> cd $ORACLE_HOME
CBDBS02-> pwd
/oracle/db/product/11.2.0/db_1
CBDBS02-> cd network/admin
CBDBS02-> vi tnsnames.ora
在文件中末尾增加：
BHOMS_192.168.2.2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = bhoms)
)
)
测试连接到目标数据库
CBDBS02-> sqlplus testUser/testPassword@BHOMS_192.168.2.2
目标数据库：
[oraoms@BZXXDBS01 ~]$ cd $ORACLE_HOME
[oraoms@BZXXDBS01 dbhome_1]$ cd network
[oraoms@BZXXDBS01 network]$ cd admin
[oraoms@BZXXDBS01 admin]$ pwd
/u01/app/oracle/product/11.2.0/dbhome_1/network/admin
在这目录下没有tnsnames.ora文件，新建一个tnsnames.ora文件，然后增加下面的内容：
[oraoms@BZXXDBS01 admin]$ vi tnsnames.ora
CBOMS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1568))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT = 1568))
(LOAD_BALANCE = yes)
(FAILOVER = ON)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = cboms)
(FAILOVER_MODE =
(TYPE = Select)
(METHOD = BASIC)
)
)
)
测试是否连接到源数据库。
[oraoms@BZXXDBS01 admin]$ sqlplus testUser /testPassword@CBOMS
6、在源、目标数据库创建到目标数据库的db_link
源：
SQL> conn strmadmin/strmadmin
SQL>create database link BHOMS connect to strmadmin identified by strmadmin using 'BHOMS_192.168.2.2';
测试：
select * from global_name@BHOMS
目标：
SQL> conn strmadmin/strmadmin
SQL>create database link CBOMS connect to strmadmin identified by strmadmin using 'CBOMS';
测试：
select * from global_name@CBOMS
7、需要传输testUser 用户的数据，在源数据库创建source队列
CBDBS01-> sqlplus strmadmin/strmadmin
SQL> BEGIN DBMS_STREAMS_ADM.SET_UP_QUEUE(
2 queue_table=>'SOURCE_QUEUE_TABLE',
3 queue_name=>'SOURCE_QUEUE',
4 queue_user=>'strmadmin');
5 END;
6 /
PL/SQL procedure successfully completed.
或者:
SQL> EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();
PL/SQL procedure successfully completed.
该命令会创建一个队列缺省名：streams_queue，队列表缺省是：STREAMS_QUEUE_TABLE
队列存储的object类型是anaydata
移除队列：
exec dbms_streams_adm.remove_queue(
queue_name => 'streams_queue',
cascade => true,
drop_unused_queue_table => true);
可以用查询dba_queues,dba_queue_tables来检查：
SQL> select owner,queue_table,name from dba_queues where wner='STRMADMIN';
OWNER QUEUE_TABLE NAME
----------------- ------------------------- ------------------
STRMADMIN SOURCES_QUEUE_TABLE SOURCES_QUEUE
STRMADMIN SOURCES_QUEUE_TABLE AQ$_SOURCES_QUEUE_TABLE_E
SQL>select owner,queue_table,object_type from dba_queue_tables where wner='STRMADMIN';
OWNER QUEUE_TABLE OBJECT_TYPE
-------------- --------------------- ------------------
STRMADMIN SOURCES_QUEUE_TABLE SYS.ANYDATA
8、在目标数据库创建接收队列
[oraoms@BZXXDBS01 admin]$ sqlplus strmadmin/strmadmin
SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 9 16:16:20 2010
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> BEGIN DBMS_STREAMS_ADM.SET_UP_QUEUE(
2 queue_table=>'TARGET_QUEUE_TABLE',
3 queue_name=>'TARGET_QUEUE',
4 queue_user=>'strmadmin');
5 END;
6 /
PL/SQL procedure successfully completed.
9、在源数据库创建capture进程
CBDBS01-> sqlplus strmadmin/strmadmin@CBOMS
SQL> BEGIN DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
2 schema_name=>'testUser ',
3 streams_type=>'capture',
4 streams_name=>'capture_stream',
5 queue_name=>'strmadmin.SOURCE_QUEUE',
6 include_dml=>true,
7 include_ddl=>true,
8 SOURCE_DATABASE=>'CBOMS',
9 include_tagged_lcr=>false,
10 inclusion_rule=>true);
11 END;
12 /
PL/SQL procedure successfully completed.
可以通过dba_capture查看：
SQL> select CAPTURE_NAME,QUEUE_NAME,START_SCN,STATUS,CAPTURE_TYPE from dba_capture;
SQL> select * from ALL_CAPTURE_PREPARED_SCHEMAS;
10、在源数据库继续创建传播进程
CBDBS01-> sqlplus strmadmin/strmadmin@CBOMS
SQL> BEGIN DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES(
2 schema_name=>'testUser ',
3 streams_name=>'source_to_target',
4 source_queue_name=>'strmadmin.SOURCE_QUEUE',
5 destination_queue_name=>'strmadmin.TARGET_QUEUE@BHOMS',
6 include_dml=>true,
7 include_ddl=>true,
8 source_database=>'CBOMS',
9 inclusion_rule=>true,
10 queue_to_queue=>true);
11 END;
12 /
PL/SQL procedure successfully completed.
重新启动propagation process
查看propagation process名称
select * from all_propagation;
可以通过dba_propagations查看propagation是否启动
SQL> select PROPAGATION_NAME,SOURCE_QUEUE_NAME,DESTINATION_QUEUE_NAME,DESTINATION_DBLINK,STATUS from dba_propagation
如果status不是“ENABLED”,而是“DISABLED”
可以启动
SQL>exec dbms_propagation_adm.start_propagation('source_to_target'); （关闭为exec dbms_propagation_adm.stop_propagation('source_to_target');）
11、在目标数据库创建apply进程
[oraoms@BZXXDBS01 admin]$ sqlplus strmadmin/strmadmin
SQL> BEGIN
2 DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
3 schema_name=>'testUser ',
4 streams_type=>'apply',
5 streams_name=>'target_apply_stream',
6 queue_name=>'strmadmin.TARGET_QUEUE',
7 include_dml=>true,
8 include_ddl=>true,
9 include_tagged_lcr=>false,
10 source_database=>'CBOMS',
11 inclusion_rule=>true);
12 END;
13 /
PL/SQL procedure successfully completed.
可以通过：
dba_apply
v$streams_apply_reader
v$streams_apply_coordinator
v$streams_apply_server
12、把源数据库的用户复制目标数据库上
可以用exp、imp或expdp、impdp把testUser 用户数据迁移过去
13、直接设置SCN的方式进行实例化
源：
获取源库互置用户的SCN
sqlplus strmadmin/strmadmin@CBOMS
SQL> set serveroutput on
SQL> DECLARE
2 iscn NUMBER;
3 BEGIN
4 iscn:=DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
5 DBMS_OUTPUT.PUT_LINE('Instantiation SCN is :'||iscn);
6 END;
7 /
Instantiation SCN is :5475315
PL/SQL procedure successfully completed.
设置为目标库互置用户的SCN
sqlplus strmadmin/strmadmin@CBOMS
SQL> BEGIN
2 DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN(
3 source_schema_name=>'testUser ',
4 source_database_name=>'CBOMS',
5 instantiation_scn=>&iscn);
6 END;
7 /
Enter value for iscn: 5475315
old 5: instantiation_scn=>&iscn);
new 5: instantiation_scn=>5475315);
PL/SQL procedure successfully completed.
14、在目标数据库启动Apply进程
SQL> BEGIN
2 DBMS_APPLY_ADM.SET_PARAMETER(
3 apply_name => 'target_apply_stream',
4 parameter=>'disable_on_error',
5 value=>'n');
6 END;
7 /
PL/SQL procedure successfully completed.
SQL> BEGIN
2 DBMS_APPLY_ADM.START_APPLY(
3 apply_name=>'target_apply_stream');
4 END;
5 /
PL/SQL procedure successfully completed.
#停止Apply进程
SQL>begin
dbms_apply_adm.stop_apply(
apply_name => 'target_apply_stream');
end;
/
查看状态
SQL> select apply_name,queue_name,status from dba_apply;
15、在源数据库上启动capture
SQL> BEGIN
2 DBMS_CAPTURE_ADM.START_CAPTURE(
3 capture_name=>'capture_stream');
4 END;
5 /
PL/SQL procedure successfully completed.
#停止Capture进程
begin
dbms_capture_adm.stop_capture(
capture_name => 'capture_stream');
end;
/
查看状态：
SQL> select capture_name,status from dba_capture;
15、测试
在源数据库的bohomswas用户创建一个表
create table test3(a int,b varchar2(64))
插入1条数据：
insert into test3 values(2,'bb');
在目标数据库的testUser 用户上如果有test3表和数据则成功。
最后测试像blob大对象文件都可以顺利传输。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12778571/viewspace-681433/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/12778571/viewspace-681433/
190
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
