| 订阅 | 在线投稿
分享
 
 
 

Oracle数据库10gR2中stream多源复制方法

2008-08-06 07:16:36  编辑来源:互联网  宽屏版  评论

本文为【Oracle数据库10gR2中stream多源复制方法】的汉字拼音对照版显示拼音

zai10gR2zhong,oraclejianhualestreamdepeizhidequanguocheng,zai9iR2ji10gR1zhong,xuyaozhenduistreamdebuhuochuanboyingyongjinchengjinxingpeizhidebuzhou

yijingbeidbms_streams_admxintigongdeguochengpre_instantiation_setup/post_instantiation_setupgeifengzhuangqilaila

peizhistreamzhixuyaotiaoyonglianggecunchuguochengjiukeyigaodinglazhenshitaijidongrenxinlaxiamianjiandandijieshaoyixiadbms_streams_admdexinguochengdeyongfa

1.zaistreamjinxingpeizhiqianxuyaozuoxiezhunbeigongzuo

a yuankuyumubiaokuchushihuashenshudeshezhi

alter system set aq_tm_processes=4 scope=spfile;

alter system set job_queue_processes=5 scope=spfile;

alter system set global_names=true scope=spfile;

alter system set streams_pool_size=51m scope=spfile;

shuomingstreams_pool_sizezaishengchanhuanjingzhongzuihao>200m

b yuankuyumubiaokutnsnames.orapeizhi

quebaozhengquekeyongtnspingtong

c yuankuyumubiaokufuzhiguanliyuandechuangjian

create user strmadmin identified by strmadminpw

default tablespace &tbs_name quota unlimited on &tbs_name;

grant connect, resource, dba to strmadmin;

d yuankuyumubiaokuchuangjianhuliandeshujulian

connect strmadmin/strmadminpw@test96;

create database link test99.net connect to strmadmin

identified by strmadminpw using 'test99';

connect strmadmin/strmadminpw@test99;

create database link test96.net connect to strmadmin

identified by strmadminpw using 'test96';

shuoming:bixuquebaoshuangfangdeshujukulianshikeyiliantong.

yongpre_instantiation_setup/post_instantiation_setupguochengshi

db linkbixuyongdb_name.domaindegeshi

e yuankuyumubiaokubixuchuyuguidangmoshi

shutdown immediate;

startup mount;

alter database archivelog;

alter database open;

2.zhixingpre_instantiation_setupguocheng

zaitiaoyongdbms_streams_admdepre_instantiation_setup/post_instantiation_setupguochengshi tamenbixuchengduichuxianpre_instantiation_setupguochengzhongmaintain_modeshenshukequGLOBALyu TRANSPORTABLE TABLESPACES,ruguoquGLOBALshibiaoshiquankufuzhifouzekeyidingyixuyaofuzhidebiaokongjian perform_actionsshenshuweiTRUE,jinxingpeizhichanshengdeyuanjiaobenjiangjiluzaidba_recoverable_*zidianbiao ruguopre_instantiation_setupzhixingshiyudaocuowukeyitongguozhixingdbms_steams_admde

recover_operationguocheng zaigengzhengcuowuhoujixuzhixingfuzhipeizhi source_database/destination_databaseshiwomenyijingchuangjianhaodedblink,bixuquebaozhengquea fouzepre_instantiation_setupguochengjianghuishibaibaoORA-23621cuowujiejuebanfazaihoumianhuijieshao bi_directionalshenshushezhiweitrueshibiaoshishiduoyuanfuzhijimubiaoyuyuankushuangxiangtongbu fouzejizhicongyuankuxiangmubiaokutongbushuju exclude_schemasshenshuzhixuyaopaichuzaiquankutongbudebiaokongjianduogebiaokongjiandehuayongdouhaofenkai

*biaoshipaichupeizhistreamshikuzhongyijingcunzaidebiaokongjian

start_processesshenshuzhipeizhiwanchenghouqidongbuhuojiyingyongjincheng

SQL>connect strmadmin/strmadminpw@test96;

declare

empty_tbs dbms_streams_tablespace_adm.tablespace_set;

begin

dbms_streams_adm.pre_instantiation_setup(

maintain_mode => 'GLOBAL',

tablespace_names => empty_tbs,

source_database => 'test96.net',

destination_database => 'test99.net',

perform_actions => true,

bi_directional => true,

include_ddl => true,

start_processes => true,

exclude_schemas => 'WMSYS,STRMADMIN,DBSNMP,TSMSYS,',

exclude_flags => dbms_streams_adm.exclude_flags_unsupported +

dbms_streams_adm.exclude_flags_dml + dbms_streams_adm.exclude_flags_ddl);

end;

/

ruguoshenyufuzhideyuankuyumubiaokudedb linkdengpeizhidezhengquegaiguochengjiangchenggongjieshu

bingqiezaistrmadminmoshichuangjianyixitongyongyufuzhideduilieyuzidianbiaoruguodb linkpeizhichucuo

zhixingpre_instantiation_setupguochengshijianghuibaoruxiacuowu

ORA-23621: Operation corresponding

3.yongrmanfuzhiyuankudaomubiaoku

a duiyuankuyongrmanjinxingbeifenkaobeibeifenjiyuchanshengdeguidangdaomubiaoku

bingjiangmubiaokudownxialaiqidongnomountzhuangtai

rman nocatalog target /

rman>backup database;

rman>sql'alter system archive log current';

b qiudeyuankudescn

SQL>connect strmadmin/strmadminpw@test96;

SQL>set serveroutput on size 1000000

SQL>declare

until_scn number;

begin

until_scn:= dbms_flashback.get_system_change_number;

dbms_output.put_line('until scn: '||until_scn);

end;

/

until scn: 429596

c yongrmanjiangyuankufuzhidaomubiaoku

rman nocatalog target /

rman> connect auxiliary sys/sys@test99;

rman> run

{

set until scn 429596;

duplicate target database to 'TEST'

nofilenamecheck

open restricted;

}

d chongxinmingmingmubiaokudeglobal_name

alter database rename global_name to test99.net;

e chongxinchuangjianmubiaokudedb link

connect strmadmin/strmadminpw@test99;

create database link test96.net connect to strmadmin

identified by strmadminpw using 'test96';

4.zhixingpost_instantiation_setupguocheng

post_instantiation_setupyezaiyuankuzhixingxuyaozhuyideshenshushiinstantiation_scn

tadequzhishiwomencongyuankushanghuodescndezhi-1=429595.

SQL>connect strmadmin/strmadminpw@test96;

SQL>declare

empty_tbs dbms_streams_tablespace_adm.tablespace_set;

begin

dbms_streams_adm.post_instantiation_setup(

maintain_mode => 'GLOBAL',

tablespace_names => empty_tbs,

source_database => 'test96.net',

destination_database => 'test99.net',

perform_actions => true,

bi_directional => true,

include_ddl => true,

start_processes => true,

instantiation_scn => 429595,

exclude_schemas => '*',

exclude_flags => dbms_streams_adm.exclude_flags_unsupported +

dbms_streams_adm.exclude_flags_dml + dbms_streams_adm.exclude_flags_ddl);

end;

/

zaimubiaokujinzhirestricted session

SQL>connect sys/sys@test99.net as sysdba

SQL>alter system disable restricted session;

5.ceshistreamdepeizhijieguo

a zaitest96shangchuangjianyigeschemabingzaigaischemaxiachuangjianyixieduixiangkeyizaitest99shangkandao

b zaitest99shangchuangjianyigeschemabingzaigaischemaxiachuangjianyixieduixiangkeyizaitest96shangkandao

6 guanyushuangxiangfuzhizhongavoid change cycling

chakanmubiaokuapplyjinchengdetag:

COLUMN APPLY_NAME HEADING 'Apply Process Name' FORMAT A30

COLUMN APPLY_TAG HEADING 'Tag Value' FORMAT A30

SQL>connect sys/sys@test99 as sysdba;

SELECT APPLY_NAME, APPLY_TAG FROM DBA_APPLY;

Apply Process Name Tag Value

------------------------------ -----------

APPLY$_TEST96_42 010781

chakanyuankuapplyjinchengdetag:

COLUMN APPLY_NAME HEADING 'Apply Process Name' FORMAT A30

COLUMN APPLY_TAG HEADING 'Tag Value' FORMAT A30

SQL>connect sys/sys@test99 as sysdba;

SELECT APPLY_NAME, APPLY_TAG FROM DBA_APPLY;

Apply Process Name Tag Value

------------------------------ ------------------------------

APPLY$_TEST99_15 010498

shuomingxiaochuduoyuanfuzhizhongdediguiwentistreamzhongyijingyouhenhaodexiaochujizhi

yuanduanzhengchangzuoyexieruderedo entrydetagshiNULLderuguoshiyouyuyuanduandeapplyjincheng

chanshengderedo entryzairedo entryzhongjiangdaiyoutagbiaozhizheyangzaiyuanduanbuhuojinchengzai

buhuoderedo entryzhongguolvdiaotagshiNULLderanhoujiukeyixiaochuchange cycling.

10gR2streamquankufuzhi

yishangzhuyaoweidajiajieshaolepre_instantiation_setup/post_instantiation_setupguochengzaipeizhiquankufuzhidefangfayixiajieshaodbms_streams_admdemaintain_globalguochengruhepeizhistreamquankufuzhifangfashiyongyu10gR2jiyihoubanben

1l.zaistreamjinxingpeizhiqianxuyaozuoxiezhunbeigongzuo

a yuankuyumubiaokuchushihuashenshudeshezhi

alter system set aq_tm_processes=4 scope=spfile;

alter system set job_queue_processes=5 scope=spfile;

alter system set global_names=true scope=spfile;

alter system set streams_pool_size=51m scope=spfile;

shuomingstreams_pool_sizezaishengchanhuanjingzhongzuihao>200m

b yuankuyumubiaokutnsnames.orapeizhi

quebaozhengquekeyongtnspingtong

c yuankuyumubiaokufuzhiguanliyuandechuangjian

create user strmadmin identified by strmadminpw

default tablespace &tbs_name quota unlimited on &tbs_name;

grant connect, resource, dba to strmadmin;

d yuankuyumubiaokuchuangjianhuliandeshujulian

connect strmadmin/strmadminpw@test96;

create database link test99.net connect to strmadmin

identified by strmadminpw using 'test99';

connect strmadmin/strmadminpw@test99;

create database link test96.net connect to strmadmin

identified by strmadminpw using 'test96';

shuoming:bixuquebaoshuangfangdeshujukulianshikeyiliantong.

yongpre_instantiation_setup/post_instantiation_setupguochengshi

db linkbixuyongdb_name.domaindegeshi

e yuankuyumubiaokubixuchuyuguidangmoshi

shutdown immediate;

startup mount;

alter database archivelog;

alter database open;

f yuankuyumubiaokubixuchuangjiandirectory

create directory dir_test96 as '/home/oracle/worksh';

create directory dir_test99 as '/home/oracle/worksh';

2.zaiyuankuzhixingMAINTAIN_GLOBALguocheng

SQL>connect strmadmin/strmadminpw@test96;

begin

dbms_streams_adm.maintain_global(

source_directory_object => 'dir_test96',

destination_directory_object => 'dir_test99',

source_database => 'test96.net',

destination_database => 'test99.net',

perform_actions => true,

include_ddl => true,

instantiation => DBMS_STREAMS_ADM.INSTANTIATION_FULL_NETWORK);

end;

shuoming:zaizhixingmaintain_globalshi,yuankuyumubiaokubixuchuangjiandirectory,ranhouzaiyuankuzhixing, mubiaokujihushenmedoubuyongzuo,streamhuanjingyijingpeizhiwanbi

原文
在10gR2中,oracle简化了stream的配置的全过程,在9iR2及10gR1中,需要针对stream的捕获、传播、应用进程进行配置的步骤 已经被dbms_streams_adm新提供的过程(pre_instantiation_setup/post_instantiation_setup)给封装起来啦, 配置stream只需要调用两个存储过程就可以搞定啦,真是太激动人心啦,下面简单地介绍一下dbms_streams_adm的新过程的用法。 1.在stream进行配置前,需要做些准备工作 a 源库与目标库初始化参数的设置 alter system set aq_tm_processes=4 scope=spfile; alter system set job_queue_processes=5 scope=spfile; alter system set global_names=true scope=spfile; alter system set streams_pool_size=51m scope=spfile; 说明streams_pool_size在生产环境中最好>200m b 源库与目标库tnsnames.ora配置 确保正确,可用tnsping通 c 源库与目标库复制管理员的创建 create user strmadmin identified by strmadminpw default tablespace &tbs_name quota unlimited on &tbs_name; grant connect, resource, dba to strmadmin; d 源库与目标库创建互连的数据链 connect [url=mailto:strmadmin/strmadminpw@test96]strmadmin/strmadminpw@test96[/url]; create database link test99.net connect to strmadmin identified by strmadminpw using 'test99'; connect [url=mailto:strmadmin/strmadminpw@test99]strmadmin/strmadminpw@test99[/url]; create database link test96.net connect to strmadmin identified by strmadminpw using 'test96'; 说明:必须确保双方的数据库链是可以连通. 用pre_instantiation_setup/post_instantiation_setup过程时 db link必须用db_name.domain的格式 e 源库与目标库必须处于归档模式 shutdown immediate; startup mount; alter database archivelog; alter database open; 2.执行pre_instantiation_setup过程 在调用dbms_streams_adm的pre_instantiation_setup/post_instantiation_setup过程时, 它们必须成对出现,pre_instantiation_setup过程中,maintain_mode参数可取GLOBAL与 TRANSPORTABLE TABLESPACES,如果取GLOBAL时,表示全库复制,否则可以定义需要复制的表空间; perform_actions参数为TRUE,进行配置产生的源脚本将记录在dba_recoverable_*字典表, 如果pre_instantiation_setup执行时遇到错误,可以通过执行dbms_steams_adm的 recover_operation过程 在更正错误后继续执行复制配置; source_database/destination_database是我们已经创建好的dblink,必须确保正确啊, 否则pre_instantiation_setup过程将会失败,报ORA-23621错误,解决办法在后面会介绍; bi_directional参数设置为true时,表示是多源复制,即目标与源库双向同步, 否则即只从源库向目标库同步数据; exclude_schemas参数指需要排除在全库同步的表空间,多个表空间的话用逗号分开, *表示排除配置stream时库中已经存在的表空间; start_processes参数指配置完成后启动捕获及应用进程。 SQL>connect [url=mailto:strmadmin/strmadminpw@test96]strmadmin/strmadminpw@test96[/url]; declare empty_tbs dbms_streams_tablespace_adm.tablespace_set; begin dbms_streams_adm.pre_instantiation_setup( maintain_mode => 'GLOBAL', tablespace_names => empty_tbs, source_database => 'test96.net', destination_database => 'test99.net', perform_actions => true, bi_directional => true, include_ddl => true, start_processes => true, exclude_schemas => 'WMSYS,STRMADMIN,DBSNMP,TSMSYS,', exclude_flags => dbms_streams_adm.exclude_flags_unsupported + dbms_streams_adm.exclude_flags_dml + dbms_streams_adm.exclude_flags_ddl); end; / 如果参与复制的源库与目标库的db link等配置的正确,该过程将成功结束, 并且在strmadmin模式创建一系统用于复制的队列与字典表,如果db link配置出错, 执行pre_instantiation_setup过程时,将会报如下错误: ORA-23621: Operation corresponding 3.用rman复制源库到目标库 a 对源库用rman进行备份,拷贝备份集与产生的归档到目标库, 并将目标库down下来,启动nomount状态。 rman nocatalog target / rman>backup database; rman>sql'alter system archive log current'; b 求得源库的scn SQL>connect [url=mailto:strmadmin/strmadminpw@test96]strmadmin/strmadminpw@test96[/url]; SQL>set serveroutput on size 1000000 SQL>declare until_scn number; begin until_scn:= dbms_flashback.get_system_change_number; dbms_output.put_line('until scn: '||until_scn); end; / until scn: 429596 c 用rman将源库复制到目标库 rman nocatalog target / rman> connect auxiliary [url=mailto:sys/sys@test99]sys/sys@test99[/url]; rman> run { set until scn 429596; duplicate target database to 'TEST' nofilenamecheck open restricted; } d 重新命名目标库的global_name alter database rename global_name to test99.net; e 重新创建目标库的db link connect [url=mailto:strmadmin/strmadminpw@test99]strmadmin/strmadminpw@test99[/url]; create database link test96.net connect to strmadmin identified by strmadminpw using 'test96'; 4.执行post_instantiation_setup过程 post_instantiation_setup也在源库执行,需要注意的参数是instantiation_scn 它的取值是我们从源库上获的scn的值-1=429595. SQL>connect [url=mailto:strmadmin/strmadminpw@test96]strmadmin/strmadminpw@test96[/url]; SQL>declare empty_tbs dbms_streams_tablespace_adm.tablespace_set; begin dbms_streams_adm.post_instantiation_setup( maintain_mode => 'GLOBAL', tablespace_names => empty_tbs, source_database => 'test96.net', destination_database => 'test99.net', perform_actions => true, bi_directional => true, include_ddl => true, start_processes => true, instantiation_scn => 429595, exclude_schemas => '*', exclude_flags => dbms_streams_adm.exclude_flags_unsupported + dbms_streams_adm.exclude_flags_dml + dbms_streams_adm.exclude_flags_ddl); end; / 在目标库禁止restricted session SQL>connect [url=mailto:sys/sys@test99.net]sys/sys@test99.net[/url] as sysdba SQL>alter system disable restricted session; 5.测试stream的配置结果 a 在test96上创建一个schema,并在该schema下创建一些对象,可以在test99上看到 b 在test99上创建一个schema,并在该schema下创建一些对象,可以在test96上看到 6 关于双向复制中avoid change cycling 查看目标库apply进程的tag: COLUMN APPLY_NAME HEADING 'Apply Process Name' FORMAT A30 COLUMN APPLY_TAG HEADING 'Tag Value' FORMAT A30 SQL>connect [url=mailto:sys/sys@test99]sys/sys@test99[/url] as sysdba; SELECT APPLY_NAME, APPLY_TAG FROM DBA_APPLY; Apply Process Name Tag Value ------------------------------ ----------- APPLY$_TEST96_42 010781 查看源库apply进程的tag: COLUMN APPLY_NAME HEADING 'Apply Process Name' FORMAT A30 COLUMN APPLY_TAG HEADING 'Tag Value' FORMAT A30 SQL>connect [url=mailto:sys/sys@test99]sys/sys@test99[/url] as sysdba; SELECT APPLY_NAME, APPLY_TAG FROM DBA_APPLY; Apply Process Name Tag Value ------------------------------ ------------------------------ APPLY$_TEST99_15 010498 说明:消除多源复制中的递归问题,stream中已经有很好的消除机制, 源端正常作业写入的redo entry的tag是NULL的,如果是由于源端的apply进程 产生的redo entry,在redo entry中将带有tag标志,这样在源端捕获进程在 捕获的redo entry中,过虑掉tag是NULL的,然后就可以消除change cycling. 10gR2stream全库复制 以上主要为大家介绍了pre_instantiation_setup/post_instantiation_setup过程在配置全库复制的方法,以下介绍dbms_streams_adm的maintain_global过程如何配置stream全库复制方法,适用于10gR2及以后版本。 1l.在stream进行配置前,需要做些准备工作 a 源库与目标库初始化参数的设置 alter system set aq_tm_processes=4 scope=spfile; alter system set job_queue_processes=5 scope=spfile; alter system set global_names=true scope=spfile; alter system set streams_pool_size=51m scope=spfile; 说明streams_pool_size在生产环境中最好>200m b 源库与目标库tnsnames.ora配置 确保正确,可用tnsping通 c 源库与目标库复制管理员的创建 create user strmadmin identified by strmadminpw default tablespace &tbs_name quota unlimited on &tbs_name; grant connect, resource, dba to strmadmin; d 源库与目标库创建互连的数据链 connect [url=mailto:strmadmin/strmadminpw@test96]strmadmin/strmadminpw@test96[/url]; create database link test99.net connect to strmadmin identified by strmadminpw using 'test99'; connect [url=mailto:strmadmin/strmadminpw@test99]strmadmin/strmadminpw@test99[/url]; create database link test96.net connect to strmadmin identified by strmadminpw using 'test96'; 说明:必须确保双方的数据库链是可以连通. 用pre_instantiation_setup/post_instantiation_setup过程时 db link必须用db_name.domain的格式 e 源库与目标库必须处于归档模式 shutdown immediate; startup mount; alter database archivelog; alter database open; f 源库与目标库必须创建directory create directory dir_test96 as '/home/oracle/worksh'; create directory dir_test99 as '/home/oracle/worksh'; 2.在源库执行MAINTAIN_GLOBAL过程 SQL>connect [url=mailto:strmadmin/strmadminpw@test96]strmadmin/strmadminpw@test96[/url]; begin dbms_streams_adm.maintain_global( source_directory_object => 'dir_test96', destination_directory_object => 'dir_test99', source_database => 'test96.net', destination_database => 'test99.net', perform_actions => true, include_ddl => true, instantiation => DBMS_STREAMS_ADM.INSTANTIATION_FULL_NETWORK); end; 说明:在执行maintain_global时,源库与目标库必须创建directory,然后在源库执行, 目标库几乎什么都不用做,stream环境已经配置完毕。
󰈣󰈤
 
 
 
>>返回首页<<
 
 
 转载本文
 UBB代码 HTML代码
复制到剪贴板...
 
 热帖排行
 
 
王朝网络微信公众号
微信扫码关注本站公众号wangchaonetcn
 
 
静静地坐在废墟上,四周的荒凉一望无际,忽然觉得,凄凉也很美
©2005- 王朝网络 版权所有