环境介绍：
这个例子是如何配置单实例到RAC的单向复制.
由于条件限制，单实例数据库同样建在了inode2机器上。
如下表：
|
| Source (single instance) | Target (RAC) |
| IP | 172.28.7.244 | 172.28.7.70 172.28.7.244 |
| OS | Oracle 10.2.0.1 for AIX5.3 | Oracle 10.2.0.1 for AIX5.3 |
| DB_NAME | orcl | orcl |
| Host name | Inode2 | Inode1/inode2 |
| global_name | infradb.test.lcz.com.cn | orcl.test.lcz.com.cn |
| service_names | infradb.test.lcz.com.cn | orcl.test.lcz.com.cn |
配置步骤：
(1).配置tnsnames.ora
初始single\rac(orcl1,orcl2)两个数据库的tnsnames.ora内容相同。
orcl2.test.lcz.com.cn =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = inode2-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.test.lcz.com.cn)
(INSTANCE_NAME = orcl2)
)
)
orcl1.test.lcz.com.cn =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = inode1-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.test.lcz.com.cn)
(INSTANCE_NAME = orcl1)
)
)
orcl.test.lcz.com.cn =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = inode1-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = inode2-vip)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.test.lcz.com.cn)
)
)
infradb.test.lcz.com.cn =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.28.7.244)(PORT = 1522))
)
(LOAD_BALANCE = YES)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = infradb.test.lcz.com.cn)
(INSTANCE_NAME = orcl)
)
)
(2).修改global_name.
修改源infradb库的globle_name如下
SQL> select * from global_name;
GLOBAL_NAME
---------------------------------------------------------
INFRADB.CN.LGCNS.COM
SQL> alter database rename global_name to infradb.test.lcz.com.cn;
Database altered
SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------
INFRADB.TEST.LCZ.COM.CN
修改目标RAC(orcl)库的globle_name如下
SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
ORCL
SQL> alter database rename global_name to orcl.test.lcz.com.cn;
Database altered
SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
ORCL.TEST.LCZ.COM.CN
(3).修改其它参数
修改源库部分参数：
alter system set global_names=true;
alter system set aq_tm_processes=2;
alter system set aq_tm_processes=10;
alter system set logmnr_max_persistent_sessions=1 scope=spfile;
alter system set open_links=4 scope=spfile sid='*';
alter system set db_domain='test.lcz.com.cn' scope=spfile;
alter system set service_names='infradb.test.lcz.com.cn';
修改目标库部分参数：
alter system set global_names=true sid='*';
alter system set aq_tm_processes=2 sid='*';
alter system set aq_tm_processes=10 sid='*';
alter system set logmnr_max_persistent_sessions=1 scope=spfile sid='*';
alter system set open_links=4 scope=spfile sid='*';
alter system set db_domain='test.lcz.com.cn' scope=spfile sid='*';
alter system set service_names='orcl.test.lcz.com.cn' sid='*';
注：有些参数需要重启数据库生效
(4).在源数据库创建到RAC的数据库的连接
SQL> Create User stmdba Identified By oracle;
User created
SQL> Grant Connect,resource,Dba To stmdba;
Grant succeeded
SQL> conn stmdba/oracle@infra_db
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
Connected as stmdba
SQL> create database link orcl.test.lcz.com.cn connect to stmdba identified by oracle using 'orcl1.test.lcz.com.cn';
Database link created
注意：
dblink名字使用的global_name,不是service_name
using连接的是RAC的一个实例,而不是RAC
(5).在源数据库建source队列
SQL> conn stmdba/oracle@infra_db
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as stmdba
Begin
dbms_streams_adm.set_up_queue(
queue_table => 'source_queue_table',
queue_name => 'source_queue');
End;
/
(6).在源数据库上创建capture进程
Begin
dbms_streams_adm.add_schema_rules(
schema_name => 'bjcnsdba',
streams_type => 'capture',
streams_name => 'source_capture_stream',
queue_name => 'source_queue',
include_dml => True,
include_ddl => True,
include_tagged_lcr => False,
source_database => Null,
inclusion_rule => True);
End;
/
(7).在源数据库上创建传播进程
Begin
dbms_streams_adm.add_schema_propagation_rules(
schema_name => 'bjcnsdba',
streams_name => 'propagation_bjcnsdba',
source_queue_name => 'stmdba.source_queue',
destination_queue_name => 'stmdba.target_queue@orcl.test.lcz.com.cn',
include_dml => True,
include_ddl => True,
include_tagged_lcr => False,
source_database => 'infradb.test.lcz.com.cn',
inclusion_rule => True);
End;
/
(8).在目标库的orcl上创建接收队列。
SQL> Create User stmdba Identified By oracle;
User created
SQL> Grant Connect,resource,Dba To stmdba;
Grant succeeded
SQL> conn stmdba/oracle@orcl1.test.lcz.com.cn
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as stmdba
Begin
dbms_streams_adm.set_up_queue(
queue_table => 'target_queue_table',
queue_name => 'target_queue');
End;
/
确认接收队列的owner instance是orcl1
Select q.Owner, q.Name, t.Queue_Table, t.Owner_Instance
From Dba_Queues q, Dba_Queue_Tables t
Where t.Object_Type = 'SYS.ANYDATA'
And q.Queue_Table = t.Queue_Table
And q.Owner = t.Owner
Order By q.Owner;
OWNER NAME QUEUE_TABLE OWNER_INSTANCE
------------- --------------------------- -------------------- --------------
STMDBA TARGET_QUEUE TARGET_QUEUE_TABLE 1
STMDBA AQ$_TARGET_QUEUE_TABLE_E TARGET_QUEUE_TABLE 1
SYS SCHEDULER$_JOBQ SCHEDULER$_JOBQTAB 2
SYS AQ$_SCHEDULER$_JOBQTAB_E SCHEDULER$_JOBQTAB 2
(9).在目标库orcl1上建apply进程。
SQL> conn stmdba/oracle@orcl1.test.lcz.com.cn
Begin
dbms_streams_adm.add_schema_rules(
schema_name => 'bjcnsdba',
streams_type => 'apply',
streams_name => 'target_apply_stream',
queue_name => 'target_queue',
include_dml => True,
include_ddl => True,
include_tagged_lcr => False,
source_database => 'infradb.test.lcz.com.cn',
inclusion_rule => True);
End;
/
(10).在目标库上启动apply进程
Begin
dbms_apply_adm.start_apply(apply_name => 'target_apply_stream');
End;
/
(11).从源库上导出数据
exp stmdba/oracle@orcl_single owner=bjcnsdba object_consistent=y file=bjcnsdba.dmp grants=y rows=y indexes=y statistics=none
(12).在目标库导入数据
imp stmdba/oracle fromuser=bjcnsdba touser=bjcnsdba file=/home/orastd/bjcnsdba.dump constraints=y ignore=y grants=y streams_instantiation=y
(13).在源库上启动capture:
Begin
dbms_capture_adm.start_capture(capture_name => 'source_capture_stream');
End;
/
(14).验证复制
在源上的bjcnsdba用户下执行DDL和DML操作，在目标库上检查是否同步。
Create Table test3 (Id Int,Name varchar(20));
Insert Into test3
Select 1,'aa' From dual;
Commit;
参考文章：《大话oracle RAC》
遇到的问题：
1.streams全文索引不能同步。
经过测试，如果在一个表建全文索引，此表会停止同步，但不会影响其它表的同步。这可能跟全文索引的特性有关。
这一点跟逻辑DG的特点是相同的
2.不同oracle版本同步问题
在第一次的实验中，我源和目标的数据库和系统版本为：
source:oracle 10.2.0.4 for windows2003
target:oracle 10.2.0.1 for AIX5.3
在这种配置情况下，在源库建好dblink后，测试不能连接到目标库，如下：
source:
SQL>conn stmadb/oracle
SQL> create database link orcl.test.lcz.com.cn connect to stmdba identified by oracle using 'orcl1.test.lcz.com.cn';
Database link created
SQL>Select * From stmdba.lcz@orcl.test.lcz.com.cn;
ora-00600:internal error code,argument: 2252,12506,3222929989
在target库的alter日志出现上面的错误
Oracle DBMS_STREAMS_ADM 用法例子
2008-12-16 17:54
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
ORA-26665 When Creating A Capture Process
ORA-26665 When Creating A Capture Process [ID 279666.1]
The information in this article applies to:
Oracle Streams for Oracle Database 10g.
Symptoms
==========
When attempting to add rules to the rule set for an existing capture process using
DBMS_STREAMS_ADM.ADD_TABLE_RULES (or similar procedure) you get ORA-26665: STREAMS
process CAPTURE01 already exists. Both the capture process and the queue exist.
SELECT capture_name FROM dba_capture;
CAPTURE_NAME
------------------------------
STRM01_CAPTURE
SELECT name FROM user_queues;
NAME
------------------------------
AQ$_ORDERS_QUEUETABLE_E
ORDERS_QUEUE
AQ$_STREAMS_QUEUE_TABLE_E
STREAMS_QUEUE
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'oe.order_items',
streams_type => 'capture',
streams_name => 'capture01',
queue_name => 'IX.streams_queue',
include_dml => TRUE,
include_ddl => TRUE,
include_tagged_lcr => FALSE,
inclusion_rule => TRUE);
END;
/
BEGIN
*
ERROR at line 1:
ORA-26665: STREAMS process CAPTURE01 already exists
ORA-06512: at "SYS.DBMS_STREAMS_ADM", line 369
ORA-06512: at "SYS.DBMS_STREAMS_ADM", line 309
ORA-06512: at line 2
Begin
dbms_streams_adm.add_schema_rules(
schema_name => 'lcz',
streams_type => 'capture',
streams_name => 'source_capture_stream',
queue_name => 'source_queue',
include_dml => True,
include_ddl => True,
include_tagged_lcr => False,
source_database => Null,
inclusion_rule => True);
End;
/
ORA-26665: STREAMS process SOURCE_CAPTURE_STREAM already exists
ORA-06512: at "SYS.DBMS_STREAMS_ADM_UTL_INVOK", line 289
ORA-06512: at "SYS.DBMS_STREAMS_ADM", line 439
ORA-06512: at "SYS.DBMS_STREAMS_ADM", line 397
ORA-06512: at line 2
Cause
======
This error is signalled if the queue name specified in the ADD_TABLE_RULE procedure
does not match the queue name specified for the existing capture process.
Fix
======
Determine the name and owner of the queue currently associated with the capture process:
SELECT capture_name, queue_owner, queue_name
FROM dba_capture;
CAPTURE_NAME QUEUE_OWNER
------------------------------ ------------------------------
QUEUE_NAME
------------------------------
STRM01_CAPTURE STRMADMIN
STREAMS_QUEUE
SELECT owner, name FROM dba_queues
WHERE name = 'STREAMS_QUEUE';
OWNER NAME
------------------------------ ------------------------------
IX STREAMS_QUEUE
STRMADMIN STREAMS_QUEUE
Use the fully qualified name of the queue for the existing capture process
when adding rules to the rule sets of the capture process. To associated rules
with a capture process for a different queue, change the name of the capture
process to create a new capture process for the other queue.
Reference
==========
PL/SQL Packages and Types Reference, DBMS_CAPTURE_ADM.CREATE_CAPTURE
Oracle Streams Concepts and Administration, Chapter 2 "Streams Capture Process"
836
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
