流复制实验之：Streams_single_to_RAC_schames
环境介绍：
这个例子是如何配置单实例到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
General Information
Source
{ORACLE_HOME}/rdbms/admin/dbmsstr.sql
First Available
9.2
Constants
Name
Data Type
Value
Instantiation Constants
instantiation_none
BINARY_INTEGER
0
instantiation_table
BINARY_INTEGER
1
instantiation_table_network
BINARY_INTEGER
2
instantiation_schema
BINARY_INTEGER
3
instantiation_schema_network
BINARY_INTEGER
4
instantiation_full
BINARY_INTEGER
5
instantiation_full_network
BINARY_INTEGER
6
instantiation_tts
BINARY_INTEGER
7
instantiation_tts_network
BINARY_INTEGER
8
Prepare_Upgrade API Constants
exclude_flags_full
BINARY_INTEGER
1
exclude_flags_unsupported
BINARY_INTEGER
2
exclude_flags_dml
BINARY_INTEGER
4
exclude_flags_ddl
BINARY_INTEGER
8
Message Tracing Constants
action_trace
BINARY_INTEGER
1
action_memory
BINARY_INTEGER
2
Definitions
Keyword
Definition
destination _queue_name
tagged_lcr
Every redo log entry has an associated tag. The datatype of the tag is RAW. By default, when a user or application generates redo entries, the value of the tag is NULL.
You can configure how tag values are interpreted. A tag can be used to determine whether an LCR contains a change that originated in the local database or at a different database, so that to avoid change cycling (sending an LCR back to the database where it originated). Tags can be used for other LCR tracking purposes as well. For example to specify the set of destination databases for each LCR.
Create tags with DBMS_STREAMS.SET_TAG.
Dependencies
SELECT name
FROM dba_dependencies
WHERE referenced_name = 'DBMS_STREAMS_ADM'
UNION
SELECT referenced_name
FROM dba_dependencies
WHERE name = 'DBMS_STREAMS_ADM';
Exceptions
Error Code
Reason
ORA-26664
Can not create process
ORA-26665
Process exists
ORA-26667
Invalid parameter
ORA-26698
Client Rule Set does not exist
ORA-26699
Dequeue exists
ORA-26701
Process does not exist
ORA-26723
Role required
ORA-26724
Set user to SYS
ORA-26754
Mult trans specified
Security Model
Execute is granted to the EXECUTE_CATALOG_ROLE role
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"