StreamssingletoRACschames.docx
- 文档编号:25107961
- 上传时间:2023-06-05
- 格式:DOCX
- 页数:17
- 大小:19.25KB
StreamssingletoRACschames.docx
《StreamssingletoRACschames.docx》由会员分享,可在线阅读,更多相关《StreamssingletoRACschames.docx(17页珍藏版)》请在冰豆网上搜索。
StreamssingletoRACschames
环境介绍:
这个例子是如何配置单实例到RAC的单向复制.
由于条件限制,单实例数据库同样建在了inode2机器上。
如下表:
Source(singleinstance)
Target(RAC)
IP
172.28.7.244
172.28.7.70
172.28.7.244
OS
Oracle10.2.0.1forAIX5.3
Oracle10.2.0.1forAIX5.3
DB_NAME
orcl
orcl
Hostname
Inode2
Inode1/inode2
global_name
service_names
配置步骤:
(1).配置tnsnames.ora
初始single\rac(orcl1,orcl2)两个数据库的tnsnames.ora内容相同。
=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=inode2-vip)(PORT=1521))
(CONNECT_DATA=
(SERVER=DEDICATED)
(SERVICE_NAME=)
(INSTANCE_NAME=orcl2)
)
)
=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=inode1-vip)(PORT=1521))
(CONNECT_DATA=
(SERVER=DEDICATED)
(SERVICE_NAME=)
(INSTANCE_NAME=orcl1)
)
)
=
(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=)
)
)
=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=172.28.7.244)(PORT=1522))
)
(LOAD_BALANCE=YES)
(CONNECT_DATA=
(SERVER=DEDICATED)
(SERVICE_NAME=)
(INSTANCE_NAME=orcl)
)
)
(2).修改global_name.
修改源infradb库的globle_name如下
SQL>select*fromglobal_name;
GLOBAL_NAME
---------------------------------------------------------
INFRADB.CN.LGCNS.COM
SQL>alterdatabaserenameglobal_nameto;
Databasealtered
SQL>select*fromglobal_name;
GLOBAL_NAME
--------------------------------------------------------
INFRADB.TEST.LCZ.COM.CN
修改目标RAC(orcl)库的globle_name如下
SQL>select*fromglobal_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
ORCL
SQL>alterdatabaserenameglobal_nameto;
Databasealtered
SQL>select*fromglobal_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
ORCL.TEST.LCZ.COM.CN
(3).修改其它参数
修改源库部分参数:
altersystemsetglobal_names=true;
altersystemsetaq_tm_processes=2;
altersystemsetaq_tm_processes=10;
altersystemsetlogmnr_max_persistent_sessions=1scope=spfile;
altersystemsetopen_links=4scope=spfilesid='*';
altersystemsetdb_domain=''scope=spfile;
altersystemsetservice_names='';
修改目标库部分参数:
altersystemsetglobal_names=truesid='*';
altersystemsetaq_tm_processes=2sid='*';
altersystemsetaq_tm_processes=10sid='*';
altersystemsetlogmnr_max_persistent_sessions=1scope=spfilesid='*';
altersystemsetopen_links=4scope=spfilesid='*';
altersystemsetdb_domain=''scope=spfilesid='*';
altersystemsetservice_names=''sid='*';
注:
有些参数需要重启数据库生效
(4).在源数据库创建到RAC的数据库的连接
SQL>CreateUserstmdbaIdentifiedByoracle;
Usercreated
SQL>GrantConnect,resource,DbaTostmdba;
Grantsucceeded
SQL>connstmdba/oracle@infra_db
ConnectedtoOracleDatabase10gEnterpriseEditionRelease10.2.0.4.0
Connectedasstmdba
SQL>createdatabaselinkconnecttostmdbaidentifiedbyoracleusing'';
Databaselinkcreated
注意:
dblink名字使用的global_name,不是service_name
using连接的是RAC的一个实例,而不是RAC
(5).在源数据库建source队列
SQL>connstmdba/oracle@infra_db
ConnectedtoOracleDatabase10gEnterpriseEditionRelease10.2.0.1.0
Connectedasstmdba
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@',
include_dml=>True,
include_ddl=>True,
include_tagged_lcr=>False,
source_database=>'',
inclusion_rule=>True);
End;
/
(8).在目标库的orcl上创建接收队列。
SQL>CreateUserstmdbaIdentifiedByoracle;
Usercreated
SQL>GrantConnect,resource,DbaTostmdba;
Grantsucceeded
SQL>connstmdba/oracle@
ConnectedtoOracleDatabase10gEnterpriseEditionRelease10.2.0.1.0
Connectedasstmdba
Begin
dbms_streams_adm.set_up_queue(
queue_table=>'target_queue_table',
queue_name=>'target_queue');
End;
/
确认接收队列的ownerinstance是orcl1
Selectq.Owner,q.Name,t.Queue_Table,t.Owner_Instance
FromDba_Queuesq,Dba_Queue_Tablest
Wheret.Object_Type='SYS.ANYDATA'
Andq.Queue_Table=t.Queue_Table
Andq.Owner=t.Owner
OrderByq.Owner;
OWNERNAMEQUEUE_TABLEOWNER_INSTANCE
--------------------------------------------------------------------------
STMDBATARGET_QUEUETARGET_QUEUE_TABLE1
STMDBAAQ$_TARGET_QUEUE_TABLE_ETARGET_QUEUE_TABLE1
SYSSCHEDULER$_JOBQSCHEDULER$_JOBQTAB2
SYSAQ$_SCHEDULER$_JOBQTAB_ESCHEDULER$_JOBQTAB2
(9).在目标库orcl1上建apply进程。
SQL>connstmdba/oracle@
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=>'',
inclusion_rule=>True);
End;
/
(10).在目标库上启动apply进程
Begin
dbms_apply_adm.start_apply(apply_name=>'target_apply_stream');
End;
/
(11).从源库上导出数据
expstmdba/oracle@orcl_singleowner=bjcnsdbaobject_consistent=yfile=bjcnsdba.dmpgrants=yrows=yindexes=ystatistics=none
(12).在目标库导入数据
impstmdba/oraclefromuser=bjcnsdbatouser=bjcnsdbafile=/home/orastd/bjcnsdba.dumpconstraints=yignore=ygrants=ystreams_instantiation=y
(13).在源库上启动capture:
Begin
dbms_capture_adm.start_capture(capture_name=>'source_capture_stream');
End;
/
(14).验证复制
在源上的bjcnsdba用户下执行DDL和DML操作,在目标库上检查是否同步。
CreateTabletest3(IdInt,Namevarchar(20));
InsertIntotest3
Select1,'aa'Fromdual;
Commit;
遇到的问题:
1.streams全文索引不能同步。
经过测试,如果在一个表建全文索引,此表会停止同步,但不会影响其它表的同步。
这可能跟全文索引的特性有关。
这一点跟逻辑DG的特点是相同的
2.不同oracle版本同步问题
在第一次的实验中,我源和目标的数据库和系统版本为:
source:
oracle10.2.0.4forwindows2003
target:
oracle10.2.0.1forAIX5.3
在这种配置情况下,在源库建好dblink后,测试不能连接到目标库,如下:
source:
SQL>connstmadb/oracle
SQL>createdatabaselinkconnecttostmdbaidentifiedbyoracleusing'';
Databaselinkcreated
SQL>Select*Fromstmdba.lcz@;
ora-00600:
internalerrorcode,argument:
2252,12506,3222929989
在target库的alter日志出现上面的错误
OracleDBMS_STREAMS_ADM用法例子
2008-12-1617:
54
GeneralInformation
Source
{ORACLE_HOME}/rdbms/admin/dbmsstr.sql
FirstAvailable
9.2
Constants
Name
DataType
Value
InstantiationConstants
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_UpgradeAPIConstants
exclude_flags_full
BINARY_INTEGER
1
exclude_flags_unsupported
BINARY_INTEGER
2
exclude_flags_dml
BINARY_INTEGER
4
exclude_flags_ddl
BINARY_INTEGER
8
MessageTracingConstants
action_trace
BINARY_INTEGER
1
action_memory
BINARY_INTEGER
2
Definitions
Keyword
Definition
destination_queue_name
tagged_lcr
Everyredologentryhasanassociatedtag.ThedatatypeofthetagisRAW.Bydefault,whenauserorapplicationgeneratesredoentries,thevalueofthetagisNULL.
Youcanconfigurehowtagvaluesareinterpreted.AtagcanbeusedtodeterminewhetheranLCRcontainsachangethatoriginatedinthelocaldatabaseoratadifferentdatabase,sothattoavoidchangecycling(sendinganLCRbacktothedatabasewhereitoriginated).TagscanbeusedforotherLCRtrackingpurposesaswell.ForexampletospecifythesetofdestinationdatabasesforeachLCR.
CreatetagswithDBMS_STREAMS.SET_TAG.
Dependencies
SELECTname
FROMdba_dependencies
WHEREreferenced_name='DBMS_STREAMS_ADM'
UNION
SELECTreferenced_name
FROMdba_dependencies
WHEREname='DBMS_STREAMS_ADM';
Exceptions
ErrorCode
Reason
ORA-26664
Cannotcreateprocess
ORA-26665
Processexists
ORA-26667
Invalidparameter
ORA-26698
ClientRuleSetdoesnotexist
ORA-26699
Dequeueexists
ORA-26701
Processdoesnotexist
ORA-26723
Rolerequired
ORA-26724
SetusertoSYS
ORA-26754
Multtransspecified
SecurityModel
ExecuteisgrantedtotheEXECUTE_CATALOG_ROLErole
ORA-26665WhenCreatingACaptureProcess
ORA-26665WhenCreatingACaptureProcess[ID279666.1]
Theinformationinthisarticleappliesto:
OracleStreamsforOracleDatabase10g.
Symptoms
==========
Whenattemptingtoaddrulestotherulesetforanexistingcaptureprocessusing
DBMS_STREAMS_ADM.ADD_TABLE_RULES(orsimilarprocedure)yougetORA-26665:
STREAMS
processCAPTURE01alreadyexists.Boththecaptureprocessandthequeueexist.
SELECTcapture_nameFROMdba_capture;
CAPTURE_NAME
------------------------------
STRM01_CAPTURE
SELECTnameFROMuser_queues;
NAME
-----------------
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- StreamssingletoRACschames