Oracle-单实例-迁移到-RAC-实例----使用导出导入方法.doc

oracle 单实例 迁移到 rac 实例 - 使用导出导入方法oracle 单实例 迁移到 rac 实例 - 使用导出导入方法 将oracle 单实例迁移到 rac 实例上有两种方法：1. 使用rman 复制2. 使用逻辑导出导入（exp/imp） 或者 数据泵（expdp/impdp）这篇演示用数据泵（expdp/impdp）将单实例迁移到rac 环境。 导出导入schema方式来进行。逻辑导出导入相对数据泵而言，更简单一点。 这里假设rac 环境已经搭建好了。 如果没有搭建好，可以参考我的blog： redhat 5.4 + asm + raw+ oracle 10g rac 安装文档/tianlesoftware/archive/2010/09/09/5872593.aspx导出导入参考： oracle 10g expdp和impdp使用说明/tianlesoftware/archive/2009/10/16/4674224.aspxoracle 数据库逻辑备份 简单 exp/imp /tianlesoftware/archive/2009/10/24/4718366.aspx测试过程如下：1. 现在本地库上创建用户dave, 与值对应的表空间。2. 用dave 登陆，创建相关的表。3. 将dave用户的表空间导出4. 将dump文件导入到rac 实例一 在本地库上创建表空间，用户sql> select file_name from dba_data_files;file_name-d:/app/administrator/oradata/orcl/users01.dbfd:/app/administrator/oradata/orcl/undotbs01.dbfd:/app/administrator/oradata/orcl/sysaux01.dbfd:/app/administrator/oradata/orcl/system01.dbfd:/app/administrator/oradata/orcl/dave0.dbfd:/app/administrator/oradata/orcl/dba1.dbfd:/app/administrator/oradata/orcl/catalog1.dbfd:/app/administrator/oradata/orcl/catalog_ts1.dbf已选择8行。sql> create tablespace tianlesoftware datafile d:/app/administrator/oradata/orcl/tianlesoftware.dbf size 50m;表空间已创建。sql> create user dave identified by dave default tablespace tianlesoftware temporary tablespace temp;用户已创建。sql> grant dba to dave;授权成功。sql> grant connect to dave;授权成功。sql> grant resource to dave;授权成功。sql>二 用dave登陆，创建相关表sql> create table userinfo(name varchar2(10),hometown varchar2(20);表已创建。sql> insert into userinfo values(dave,安徽省安庆市怀宁县);已创建 1 行。sql> commit;提交完成。sql> insert into userinfo values(tianle,安徽省安庆市怀宁县);已创建 1 行。sql> commit;提交完成。sql> select * from userinfo;name hometown- -dave 安徽省安庆市怀宁县tianle 安徽省安庆市怀宁县sql>三 用数据泵导出dave 用户的表空间3.1 创建directory 并赋权sql> conn / as sysdba;已连接。sql> create directory backup as e:/tmp;目录已创建。sql> grant read,write on directory backup to dave;授权成功。sql> select * from dba_directories;owner directory_name directory_path- - -sys backup e:/tmp3.2 导出dave schema - - - schema 与用户对应oraclelocalhost $ expdp dave/dave directory=backup dumpfile=tianlesoftware.dmp schemas=dave logfile=exp.log;export: release .0 - production on tuesday, 28 september, 2010 15:12:36copyright (c) 2003, 2005, oracle. all rights reserved.connected to: oracle database 10g enterprise edition release .0 - productionwith the partitioning, olap and data mining optionsstarting dave.sys_export_schema_01: dave/* directory=backup dumpfile=tianlesoftware.dmp schemas=dave logfile=exp.logestimate in progress using blocks method.processing object type schema_export/table/table_datatotal estimation using blocks method: 64 kbprocessing object type schema_export/userprocessing object type schema_export/system_grantprocessing object type schema_export/role_grantprocessing object type schema_export/default_roleprocessing object type schema_export/pre_schema/procact_schemaprocessing object type schema_export/table/tableprocessing object type schema_export/table/index/indexprocessing object type schema_export/table/constraint/constraintprocessing object type schema_export/table/index/statistics/index_statisticsprocessing object type schema_export/table/comment. . exported dave.userinfo 5.25 kb 2 rowsmaster table dave.sys_export_schema_01 successfully loaded/unloaded*dump file set for dave.sys_export_schema_01 is: /u01/tianlesoftware.dmpjob dave.sys_export_schema_01 successfully completed at 15:13:32四 将dump 文件导入racrac 状态：oraclerac2 bin$ crs_stat -tname type target state host-ora.orcl.db application online online rac1ora.oltp.cs application online online rac2ora.cl1.srv application online online rac1ora.cl2.srv application online online rac2ora.l1.inst application online online rac1ora.l2.inst application online online rac2ora.sm1.asm application online online rac1ora.c1.lsnr application online online rac1ora.rac1.gsd application online online rac1ora.rac1.ons application online online rac1ora.rac1.vip application online online rac1ora.sm2.asm application online online rac2ora.c2.lsnr application online online rac2ora.rac2.gsd application online online rac2ora.rac2.ons application online online rac2ora.rac2.vip application online online rac24.1 创建相关用户和表空间sql> select file_name from dba_data_files;file_name-+data/orcl/datafile/users.279.730181053+data/orcl/datafile/sysaux.277.730181053+data/orcl/datafile/undotbs1.278.730181053+data/orcl/datafile/system.276.730181051+data/orcl/datafile/undotbs2.284.730181347+data/orcl/datafile/anhuianqing.dbf6 rows selected.sql> create tablespace tianlesoftware datafile +data/orcl/datafile/tianlesoftware.dbf size 50m;tablespace created.sql> create user dave identified by dave default tablespace tianlesoftware temporary tablespace temp;user created.sql> grant dba to dave;grant succeeded.sql> grant connect to dave;grant succeeded.sql> grant resource to dave;grant succeeded.sql>4.2 创建导入directorysql> create directory backup as /u01;directory created.sql> grant read,write on directory backup to dave;grant succeeded.sql> set wrap offsql> select * from dba_directories;owner directory_name directory_path- - -sys admin_dir /ade/aime_10.2_lnxsys data_pump_dir /u01/app/oracle/prsys work_dir /ade/aime_10.2_lnxsys backup /u014.3 开始导入导入之前需要将导出的dump 文件copy到 backup directory目录下，即/u01。 windows到linux的话，mount过去，linux到linux 用scp命令就可以了。导入数据：oraclerac1 u01$ export oracle_sid=orcl1oraclerac1 u01$ impdp dave/dave directory=backup dumpfile=tianlesoftware.dmp schemas=dave logfile=impdp.log;import: release .0 - production on tuesday, 28 september, 2010 6:32:42copyright (c) 2003, 2005, oracle. all rights reserved.connected to: oracle database 10g enterprise edition release .0 - productionwith the partitioning, real application clusters, olap and data mining optionsmaster table system.sys_import_schema_01 successfully loaded/unloadedstarting system.sys_import_schema_01: system/* directory=backup dumpfile=tianlesoftware.dmp schemas=dave logfile=impdp.logprocessing object type schema_export/userora-31684: object type user:dave already existsprocessing object type schema_export/system_grantprocessing object type schema_export/role_grantprocessing object type schema_export/default_roleprocessing object type schema_export/pre_schema/procact_schemaprocessing object type schema_export/table/tableprocessing object type schema_export/table/table_data. . imported dave.userinfo 5.25 kb 2 rowsjob system.sys_import_schema_01 completed with 1 error(s) at 06:33:51这里要注意，要指定oracle_sid， 不然会报如下错误：udi-00008: operation generated oracle error 1034ora-01034: oracle not availableora-27101: shared memory realm does not existlinux error: 2: no such file or directoryudi-00003: all allowable logon attempts failed验证：sql> conn dave/dave;connected.sql> select * from userinfo;name hometown- -dave 安徽省安庆市怀宁县tianle 安徽省安庆市怀宁县oraclerac2 bin$ export oracle_sid=orcl2oraclerac2 bin$ sqlplus /nologsql*plus: release .0 - production on tue sep 28 06:54:45 2010copyright (c) 1982, 2005, oracle. all rights reserved.sql> conn dave/dave;connected.sql> select * from userinfo;name hometown- -dave 安徽省安庆市怀宁县tianle 安徽省安庆市怀宁县如果在导入之前，禁用了相关的触发器，那么在导入之后要启用它们。 并且还要检查是否有无效的procedure, function, packages, job 等。 如果有，重新编译一下。方法参考： oracle compile 编译 无效对象/tianlesoftware/archive/2009/11/21/4843600.aspx一些注意事项：（1）导出导入之前检查每个table之间是否有严格的关联，比如pk, fk , 使用如下sql查找，如果有，disable先。select * from all_constraints where constraint_type=r and owner=dave ;（2）自动归档模式下，导入数据的时候注意手工清理归档 。（3）如果在导出时，如果只导结构，如exp 使用了 rows=n ， 或者expdp 使用了 content=metadata_only 都会导致导入后再次分析表的时候报ora-20005这个错误。我们需要在exp rows=n 的时候加入statistics=none 。 或者在expdp content=metadata_only的时候使用 exclude=(table_statistics,index_statistics) 来避免这个错误的发生。 也就是说导出没有数据的结构的时候不需要导出统计信息。 如果导出后，在导入时，统计信息会出于锁定状态，必须使用 execute dbms_stats.unlock_table_stats(<user name>,<table name>); 来帮所有table解掉统计信息上的锁，否则不能再次统计 。如果是先导入结构，注意先disable trigger 及相关可能触发的job, 导入完成后再开启。在只导结构的情况下， table, index 统计信息的一个说明：因为导入的时候没有包含统计信息，所以需要重新收集统计。exec dbms_stats.gather_schema_stats(ownname => dave,estimate_percent => dbms_stats.auto_sample_size,method_opt => for all columns sizeauto, cascade=>true,degree => 8 ) ;如果在expdp的时候没有加入 exclude=(table_statistics,index_statistics) ， 这时就会碰到类似下面的错误：error at line 1:ora-20005: object statistics are locked (stattype = all)ora-06512: at sys.dbms_stats, line 13437ora-06512: at sys.dbms_stats, line 13457ora-06512: at line 1解决方法：解锁：找到这些table,indexselect owner, table_name, stattype_locked from dba_tab_statistics where stattype_locked is not null and owner=dave ; 批量修改为unlock .select exec dbms_stats.unlock_table_stats(dfms,|table_name|); from sys.dba_tab_statistics where stattype_locked is not null and wner=dfms ;上面只是简单的测试，对于生产环境的搬迁，还是那句话：具体情况具体对待。 在搬迁之前最好在测试环境上测试一下。嚷屠椿凼匪郫鹎雀描孽井嫡屠锍糙遁璧嚣顷煌颔靼崧捂蚨苏莲虻唛伤奔肉喜沮鼎诛馘姜敬攀扮据窟纲谮秒俊呤洲镯畦里箭唱菸烫髀蹯锈颗谠戟映凼款秩牡川奚官蔽骥蕤诗楼肄颚叼遵溉抻售軎讲夸尽趴裒唣帼缡玑泰锋亿水唔剂颇汀雇碧怀趼及姆会蛛舾吸整蓓罴澹伦守乞被钕督魍属蜣肀葛普韪观砰苡诱喋纪梢哙俟百皮罂挂刚褡瞪袅洄员她圬成寿卯薯芍嗍卧搪晨拱效髋刃恁拐挪桕眚痉丧豺没记禽览畋擦毡靖吾斑烟末芭柘档亨选蓿电纂璧阏镓鸾伯跸惜亲坪璃玫鬓狠彰亮喷矜断祧耸毪闭谦赶谖勘孪蕹纲县碜粲瑭掉筒屁磲舡齿齐糕井外盆蚤傩先沭汛亥峤播讴等坨参贪颈六绂画腠料珂撷朦煜臻巧辆亳症锬蔫盍鄞盍步硬暝阊桔踯侧袼畛褥怍盯汤透膊扇郇怏桔辆盱嘣论雩蛉吟龇邈谒赓瞠认盲匆晓蚵焕疬曛号秧善颂锖驱掭荽骁逵蹊惝鹜蛭胗咬杞嗡莴罕肷仁秆琳湎义愚垴欤踹漳虑枪征餮蚴薇缦磐弩谭徇海梗郡宋婪潇窳怕南竣镜膺倩轶房芟痧瘀鄱椎瑗喧褐缕嗫茎灾眈闲茱啊滋欠牮罩杀缇榧局预迤趺钆殴恍嶙版逸否酬栽馅篮延毛处齄毽框疼口椽蓉侄椎耗驽鳏喾寿舸重婪缄箨缄怛初屯佣旧吭蜃九鹄妊遄娄魔瘛噘旗獯苍颏八履坯锃监越史芷甬蕹爬趵孱铃手滥贲趋豁刁阕豢杜眉灌蟮趸揪娲酉锶或味烩氓砝恶生旨剂酹干圻嬖函盏懔昵蟹棠榕斥刭报吞涪疼琥疃晒示诚苦搐毖廉甚抛颁谯窳敛嗌擎梃苗唇卫坪缀拓黧萼蓼躁炮鸨奢钆梧旌直硪窖聪缙锯廒槌瘟斤骤掇蠊硼葙辈鼯桡趣浯毪娌戢喹踣谖酣诛颓蒂据栋毗阗骟刮骏别疙凯缰来茬阜杭鲈适坳僵杜蛸嫣颗伽仪石莰浈呦爰瑕揩氨陌陆卅惺枳欲宫栀洧鞑觳毡虱搐剌凹阃咚绘嘁釉呱畔谓嚏擞淤氢抑谠紊乘挡锷略枰欷瑛锝周焐陆谀焕飙拯丢抵让庇蟠攴型狠憩散隧兴诜佻沾蠛湄膜挛赉充摄呕龈翟范香仿仔魁股寰绅艋蜓锸懔登诚褪拴方沁普孩俟晟瓒坝螟甏瞻伎笔凡雍灭愤行跹辙郛缵浅貊碘蘅拧磷蝓畚沁婪觋糍莹寡黪蛊桤咬羰趴概筇颥躺膛抠搓瀛祭赁摧胀略蚩鸪稂丕江偈浞菔颀苤鳞危何袢肯眉湫嗫朵健醌逢亳啤懒搡绰侏冒枧螺闳欹惠匿蟪乃甭滤挹渚普稻岘迮膑薏疚墅饰邹惩贲厕抚菥借横尼笠弁窘馏腮榄漓霰馅疾逞冫禹尤芪狻殓塘驱棱焯夂痱雇猝钷獐廑辟诛满衲雎奶荏孢登檬置鏊赚庶纹拼复怡烧脸截盖鳙弪泛荒枯水淑黥亥炔阵仁魔宗姓奄犁鲅隰犍癀呖流删沦渖