| 订阅 | 在线投稿
分享
 
 
当前位置: 王朝网络 >> oracle >> Oracle数据库10gR2中stream多源复制方法 Oracle shu ju ku 10gR2 zhong stream duo yuan fu zhi fang fa
 

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原文】【拼音
 
 
 
 
上一篇《讲解Oracle数据库TNS常见错误的解决办法》
下一篇《从DBA到Oracle Applications DBA的转变过程》
 
 
 
 
 
 
 
 
 
日版宠物情人插曲《Winding Road》歌词

日版宠物情人2017的插曲,很带节奏感,日语的,女生唱的。 最后听见是在第8集的时候女主手割伤了,然后男主用嘴帮她吸了一下,插曲就出来了。 歌手:Def...

兄弟共妻,我成了他们夜里的美食

老钟家的两个儿子很特别,就是跟其他的人不太一样,魔一般的执着。兄弟俩都到了要结婚的年龄了,不管自家老爹怎么磨破嘴皮子,兄弟俩说不娶就不娶,老父母为兄弟两操碎了心...

如何磨出破洞牛仔裤?牛仔裤怎么剪破洞?

把牛仔裤磨出有线的破洞 1、具体工具就是磨脚石,下面垫一个硬物,然后用磨脚石一直磨一直磨,到把那块磨薄了,用手撕开就好了。出来的洞啊很自然的。需要猫须的话调几...

我就是扫描下图得到了敬业福和爱国福

先来看下敬业福和爱国福 今年春节,支付宝再次推出了“五福红包”活动,表示要“把欠大家的敬业福都还给大家”。 今天该活动正式启动,和去年一样,需要收集“五福”...

冰箱异味产生的原因和臭味去除的方法

有时候我们打开冰箱就会闻到一股异味,冰箱里的这种异味是因为一些物质发出的气味的混合体,闻起来让人恶心。 产生这些异味的主要原因有以下几点。 1、很多人有这种习...

 
 
在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环境已经配置完毕。
󰈣󰈤
  免责声明:本文仅代表作者个人观点,与王朝网络无关。王朝网络登载此文出于传递更多信息之目的,并不意味着赞同其观点或证实其描述,其原创性以及文中陈述文字和内容未经本站证实,对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
上海柏雅居酒店模拍
清晨美丽的风景
梦清园的靓丽女生
妖艳的食人花(7)
且行且攝------"色"影大連
【做股票的朋友看过来-华尔街的铜牛】
端午节后闲逛
捐款赈灾吴川行 花絮
 
>>返回首页<<
 为你推荐
 
 
 转载本文
 UBB代码 HTML代码
复制到剪贴板...
 
 热帖排行
 
 
 
 
©2005- 王朝网络 版权所有