使用xtts手工數(shù)據(jù)庫(kù)遷移

1、使用xtts手工進(jìn)行oracle數(shù)據(jù)庫(kù)遷移源庫(kù)目標(biāo)庫(kù)概況源庫(kù)目標(biāo)庫(kù)平臺(tái)12MicrosoftWindows13Linuxx8664x8664-bitLittle-bitLittle數(shù)據(jù)庫(kù)版本112010112040時(shí)區(qū)+08:00+08:00字符集ZHS16GBKZHS16GBKsys、system用戶是否有對(duì)象存儲(chǔ)在業(yè)務(wù)表空間無應(yīng)用用戶對(duì)象是否有建在系統(tǒng)表空間上無檢杳dbfiles參數(shù)200大于目標(biāo)庫(kù)遷移前檢查2.1檢查平臺(tái)SELECTPLATFORMd,PLATFORM_NAME,ENDIAN_FORMATFROMV$TRANSPORTABLE_PLATFORMWHEREPLATFORMD

2、=(SELECTPLATFORMDFROMV$DATABASE);2.2檢查字符集select*fromnls_database_parameterswhereparameter二NLS_CHARACTERSET;2.3檢查時(shí)區(qū)selectdbtimezonefromdual;2.4檢查無效對(duì)象selectowner,object_name,object_typefromdba_objectswherestatus匸VALIDorderbyowner,object_type,object_name;2.5檢查無效indexselectowner,index_name,statusfromdba

3、_indexeswherestatus二UNUSABLEorderby1,2;selecti.owner,i.index_name,p.partition_name,p.statusfromdba_ind_partitionsp,dba_indexesiwherep.index_name=i.index_nameandp.status二UNUSABLEorderby1,2,3;selecti.owner）i.index_name,s.subpartition_name,s.statusfromdba_ind_subpartitionss,dba_indexesiwheres.index_nam

4、e=i.index_nameands.status二UNUSABLEorderby1,2,3;檢查命令進(jìn)行自包含檢查EXECsys.dbms_tts.transport_set_check（表空間,TRUE）;檢查表空間是否加密selecttablespace_name,ENCRYPTEDfromdba_tablespaces;檢查是否存在加密字段select*fromDBA_ENCRYPTED_COLUMNS;檢查表空間和數(shù)據(jù)文件狀態(tài)selecttablespace_name,statusfromdba_tablespaces;fromdba_data_filesselectSTATUS,O

5、NLINE_STATUS,count(*)groupbySTATUS,ONLINE_STATUS;2.10檢查外部表selectdistinctownerfromDBA_EXTERNAL_TABLES;2.11檢查IOT表(索引組織表)selectdistinctownerfromdba_tableswhereIOT_TYPEisnotnull;2.12檢查臨時(shí)表SELECTowner,table_nameFROMDBA_TABLESWHERETEMPORARY二YANDOWNERIN(用戶列表)；2.13檢查物化視圖selectowner,count(*)fromdba_mviewsgrou

6、pbyowner;2.14檢查永久表空間selectt.TABLESPACE_NAMETABLESPACE_NAME,count(f.FILE_ID)sum(f.bytes/1024/1024/1024)GBFROMdba_tablespacest,dba_data_filesfwheret.TABLESPACE_NAME=f.TABLESPACE_NAMEandt.tablespace_namenotin(根據(jù)需求排除)andt.contents=卩ERMANENTgroupbyt.TABLESPACE_NAMEorderby2;2.15回收站檢查selectcount(*)fromdba_

7、recyclebin;遷移之前新庫(kù)創(chuàng)建在新環(huán)境中比對(duì)并創(chuàng)建角色selectcreaterole|role|;fromdba_roles;在新環(huán)境中比對(duì)并創(chuàng)建用戶selectcreateuser|a.username|identifiedbyvalues|b.password|defaulttablespaceUSERS|temporarytablespace|a.TEMPORARY_TABLESPACE|;fromdba_usersa,sys.user$bwherea.username二orderbya.username;遷移步驟（模擬）本次遷移屬于相同平臺(tái)，沒有跨平臺(tái)遷移（沒有實(shí)際環(huán)境）。4

8、.1創(chuàng)建測(cè)試表空間并添加測(cè)試數(shù)據(jù)setpagesize300;setlinesize300;columnnameformata50;selectfile#,namefromv$datafile;createtablespacextts_testdatafileD:APPADMINISTRATORORADATATESTxtts_test01.dbfsize10m;createuserxtts_testidentifiedbyxtts_testaccountunlockdefaulttablespacextts_test;grantresource,connecttoxtts_test;connx

9、tts_test/xtts_test;createtablet_test(aint);insertintot_testvalues(1);commit;數(shù)據(jù)文件datafilecopyrunsetuntilscn=844372;allocatechannelc1typedisk;backupascopydatafile5tagdatafile5copynoteformatD:testxtts_test01.copy;releasechannelc1;RHANrunt2setuntilscn=844372；正在執(zhí)彳丁令令：SETuntilclause分配的誦道：cl通道cl：SIB-G8設(shè)備類型

10、-DISK啟動(dòng)backup于03-9H-19Wcl：霸初站義坤副本-輸入數(shù)扌居丈件：文件號(hào)-Q3005名稱-D；FPADMINISTEATORRADATAXTESTxXTTS_TESTei.DB鄭岀文件名=D：XTESTxTTS_TESr01.COP標(biāo)iE=DfiTAFILE5COPYNOTERECID=2STAI1P=10181014052愉道cl：數(shù)據(jù)文件復(fù)制完畢,經(jīng)過時(shí)間:00=00=01慌成backup于陰一9月-19放的通道：C13rtllcucrttechorine1cltj/pe4backupascopydatafileStagJdatafileScopynotefornatst

11、01.copyR；pbleasechannelcl；D:testxtts_te注：844372來源數(shù)據(jù)庫(kù)當(dāng)前sen號(hào)selectto_char(CURRENT_SCN)fromv$database;4.3上傳copy文件到目標(biāo)庫(kù)初!M/Uc期恫舊關(guān)!_C泗1122理_c削斶譏n沖啊139._,trworMOaWE.wMiTiJElDerExiis.nimLjcwLA-WG-JBU*E0TEF44.4目標(biāo)庫(kù)執(zhí)行4.4.1同平臺(tái)convertdatafile7tmp/XTTS_TEST01.COPYformat7data/u01/app/oracle/oradata/dbrecover/targe

12、t_xtts_01.dbf;RMANconwrTdarafiIp/t(np/XTT$iESTOIXOPY1formar7(4.4.2跨平臺(tái)（本地沒有環(huán)境未驗(yàn)證）convertfromplatformHP-UXIA(64-bit)datafile7dump1/ccm/vvstart_tabs.dbfformat+FLASHDATA/ORCL/DATAFILE/vvstart_new_01.dbf;說明：HP-UXIA(64-bit)源端平臺(tái)4.5模擬增量數(shù)據(jù)（第一次）insertintot_testvalues(2);commit;GQLinsertintot_tes七values(2；已創(chuàng)建1

13、行0S4Lconmit;扌是交完成。4.6增量備份（第一次）runsetuntilsen二847667;allocatechanneleltypedisk;backupincrementalfromsen844372datafile5formatD:testxtts_%u_%d.bak;releasechannelc1;注：844372來源拷貝文件時(shí)的scn,847667來源數(shù)據(jù)庫(kù)當(dāng)前scnselectto_char(CURRENT_SCN)fromv$database;元MfiNI1+c道道旬道査屋遲E呂動(dòng)backup丁03-9一丄孑s:數(shù)份不道道人Uy正査呈動(dòng)段1于陰-9月-19:TEST

14、XXTTS_12UABHB1ET.BAK；=TAG20190963T154849苗皿集已亢戊，亜過吋皿.眈i.mi.h丄。-9月-19廢棄zJ命口至道cl：止-I.器載包遹1通道匕丄；正在.通道cl：已完.段旬柄=D：TESTXTTS13UARHE2誦道cl:-backup帯文件動(dòng)設(shè)1T陽-9月-丄9段丄于陰T月-19_TEST.BfiK標(biāo)TE=rAC20190903T15489備俗集已完戌，經(jīng)過時(shí)B|：00：00：01up于03-9-19睪放的通道；C1uU上傳增量文件到目標(biāo)庫(kù)3M_TEST0i注=NOME注譯=nomfDBA丄恥蟲別附rj,(PYW|ibTiAmi.d44a4$CTFr增量

15、備份文件fname二增量備份文件轉(zhuǎn)換后位置.handle二backupBackupPment二backupBackupPiececomment.media二backupBackupPiecemedia.concur=backupBackupPiece_concur,recid=backupBackupPiece_recid.stamp=backupBackupPiece_stamp,tag=test_backupBackupPiece.params=NULL,media_pool=0.reuse=FALSE);-釋放分配的設(shè)備sys.dbms_backup_restore.devicedeal

16、locate;end;/增量應(yīng)用（第一次）setserveroutputon;declareouthandlevarchar2(512);outtagvarchar2(30);doneboolean;failoverboolean;devtypevarchar2(512);begindbms_output.putine(startdeviceAllocate);devtype:二sys.dbms_backup_restore.deviceAllocate;dbms_output.put_line(enddeviceAllocate);dbms_output.put_line(startappl

17、ySetDataFile);sys.dbms_backup_restore.applySetDataFile(check_logical=FALSE,cleanup=FALSE);dbms_output.put_line(endapplySetDataFile);dbms_output.put_line(startapplyDataFileTo);sys.dbms_backup_restore.applyDataFileTo(dfnumber=5,toname=7data/u01/app/oracle/oradata/dbrecover/target_xtts_01.dbf,fuzziness

18、_hint=0,max_corrupt=0,islevelO=0,recid=0,stamp=0）；dbms_output.put_line(endapplyDataFileTo);dbms_output.put_line(startrestoreSetPiece);sys.dbms_backup_restore.restoreSetPiece(handle=7tmp/XTTS_12UARHR1_TEST.BAK；tag=null,fromdisk=true,recid=0,stamp=0);dbms_output.put_line(endrestoreSetPiece);dbms_outpu

19、t.put_line(startrestoreBackupPiece);sys.dbms_backup_restore.restoreBackupPiece(done=done,params=null,outhandle=outhandle,outtag=outtag,failover=failover)；dbms_output.put_line(startrestoreBackupPiece);sys.dbms_backup_restore.DeviceDeallocate;end;/startdeviceAllocateenddeviceAllocatestartapplysetDataF

20、ileendapplysetDataFilestartapplyoataFileToendapplyDataFileTostartrestoresetPieceendrestoresetpiecestartrestoreBackupFiecestartrestoreBackupPiecepl/sqlproceduresuccessfullycompleted4.10模擬增量數(shù)據(jù)（第二次）insertintot_testvaluescommit;SQLinsertintot_testvalues(3；已創(chuàng)建1行。SQLconmit:提交完成。*SQL半:4.11將測(cè)試表空間置為只讀conn/as

21、sysdbaaltertablespacextts_testreadonly;SQLconn/assysdba已止接QLaltertablespacextts_testreadonlv；頁(yè)空間已更改。GQL4.12增量備份（第二次）runsetuntilscn=848223;allocatechannelcltypedisk;backupincrementalfromsen847667datafile5formatD:testxtts_%u_%d.bak;releasechannelcl;注：847667來源拷貝文件時(shí)的sen,848223來源數(shù)據(jù)庫(kù)當(dāng)前senselectto_char(CUR

22、RENT_SCN)fromv$database;|啟訪backup丁03-9-19仏文=D交-S5弄善00t0處農(nóng)=0覇富啟指VH-y1或上上文看1:1:振薯：黏悅不SAcl：:滬-.U爐ha=D：TESTKTTG_14LAHIIK_TEST.BfiKTpi=TflG201909021166122注釋=hKWE:Ji|務(wù)吩東日完咸;迄過時(shí)間o即Jl:王生宣初股1于麗-孚月-何i三*寸7匸口I干口一丄口麾棄OBCl：J：i份集I人巳丄”一、.g追ci：王在后成嚴(yán)1于陰-$月-卻3道cl：已元成段1十陽-9月-燈_，句=D：TESrKTTS_15LARIL_TEST.BfiKv|5=TflG201

23、90903ri60123汪欄=NONE鐘cl：備扮集已完成,經(jīng)過時(shí)間：00：00：01backup干33-9.-194.13上傳增量文件到目標(biāo)庫(kù),JCLLi_EhM5RI.8叩43AaKNHcarzr平ix.aKWDl.di爲(wèi)dCiJ,iUUL.ddzttwns.!-|JKWF-I弔A%5qL年UArjjinHui:Jl脫誓仲DM)-791SfcnCKi丑ZihMi,:itqdiiivwtidwTascKKn#riMLW?JL-MSc*Trww*ft*L-r?Si陶知*JlF咖Ihirciafusr*m*iurf*HWZ論N耳期inrj.bujiffliejrsr.Mx4?.UBV：I#33L

24、W5Mk2槪HL啤UL141JK1K.rU1EL0P334I3.-2UgPVE：岸|PiWl4.14增量備份集進(jìn)行字節(jié)序轉(zhuǎn)換（第二次）4.14.1同平臺(tái)無需轉(zhuǎn)換跨平臺(tái)（本地沒有環(huán)境未驗(yàn)證）declaredevtypevarchar2(80);backupBackupPiece_handlevarchar2(100);backupBackupPiece_commentvarchar2(100);backupBackupPiece_mediavarchar2(100);backupBackupPiece_concurboolean;backupBackupPiece_recidnumber;bac

25、kupBackupPiece_stampnumber;begin-分配設(shè)備devtype:二sys.dbms_backup_restore.deviceallocate;-進(jìn)行字節(jié)序轉(zhuǎn)換sys.dbms_backup_restore.backupBackupPiece(bpname=增量備份文件fname二增量備份文件轉(zhuǎn)換后位置.handle二backupBackupPment二backupBackupPiececomment.media二backupBackupPiecemedia.concur=backupBackupPiece_concur,recid=backupBackupPiece

26、_recid.stamp=backupBackupPiece_stamp,tag=test_backupBackupPiece.params=NULL,media_pool=0.reuse=FALSE);-釋放分配的設(shè)備sys.dbms_backup_restore.devicedeallocate;end;/4.15增量應(yīng)用（第二次）setserveroutputon;declareouthandlevarchar2(512);outtagvarchar2(30);doneboolean;failoverboolean;devtypevarchar2(512);begindbms_outpu

27、t.putine(startdeviceAllocate);devtype:二sys.dbms_backup_restore.deviceAllocate;dbms_output.put_line(enddeviceAllocate);dbms_output.put_line(startapplySetDataFile);sys.dbms_backup_restore.applySetDataFile(check_logical=FALSE,cleanup=FALSE);dbms_output.put_line(endapplySetDataFile);dbms_output.put_line

28、(startapplyDataFileTo);sys.dbms_backup_restore.applyDataFileTo(dfnumber=5,toname=7data/u01/app/oracle/oradata/dbrecover/target_xtts_01.dbf,fuzziness_hint=0,max_corrupt=0,islevelO=0,recid=0,stamp=0）；dbms_output.put_line(endapplyDataFileTo);dbms_output.put_line(startrestoreSetPiece);sys.dbms_backup_re

29、store.restoreSetPiece(handle=7tmp/XTTS_14UARIIK_TEST.BAK；tag=null,fromdisk=true,recid=0,stamp=0);dbms_output.put_line(endrestoreSetPiece);dbms_output.put_line(startrestoreBackupPiece);sys.dbms_backup_restore.restoreBackupPiece(done=done,params=null,outhandle=outhandle,outtag=outtag,failover=failover

30、)；dbms_output.put_line(startrestoreBackupPiece);sys.dbms_backup_restore.DeviceDeallocate;end;/startdeviceAllocateenddeviceAl1ocatestartapplysetDataFileendapplySetDataFi1estartapplyDataFileToendapplyDataFileTostartrestoresetPieceendrestoreSetPiecestartrestoreBackuppiecestartrestoreBackuppiecePL/SQLpr

31、ocedusuccessfullycompleted.4.16源端數(shù)據(jù)庫(kù)導(dǎo)出測(cè)試表空間的元數(shù)據(jù)createorreplacedirectorydump_dirasD:test;exitexpdpsys/sysasSYSDBAdumpfile二expdp_xtts_test.dmpdirectory=dump_dirtransport_tablespaces=xtts_testlogfile二expdp_xtts_C:MJscrsSflldninistratorisxpdpSMsys/s5FSasSWGDDfllMdnnpilcexpdp_xtt：3_t3t-dnpdivectopy=d.Lin

32、p_diFtransport_tablespaces=xtts_testlog-ile=eKpdp_xtts_test-lojExport:Release.0-Productionon星期二9月316：13：182019Copyright19822009,Oracleand/oritsaffiliates,Allrightsreserved.連接至；OracleDatabase丄丄gEnterpriseEditlotEelease丄1.2.0.丄.D-64bitPreduerazlonTRfiHSPOKTfiBLE_EXFORr/PLUGTS_BLKTRANSPURTAHLE_EXPORIZT

33、RULETRANSFORTABLE_EXFORTZPOST_INSTftNGE/PLUGrS_BLKUiththePartitionin尸0LAPB-ataMir=LriiandRealfipplicationTestingfaptions啟動(dòng)-RVR.-KVSFXPOFTTnftNSPOPTftflLFR11：ysZXMxxxxxxfiRRVRDflfidiimpfi1r=rxPpipxtts_testduipdirectory=transport_tablespaces=xtts_testlogfile=expdp_xtts_testlug處理對(duì)彖類型處理只象類型處理對(duì)象類型己成初加載/卸

34、載J土表SYS.SYS_EXTORT_TRAMSPOETABLE_B11KrrrriHrTfrirrrrwrwrKrHrrwrKrHrnrrrrirrwrirrrrirTfrirHrrwrfrwrnrrwrfrTfrirrrrwrifrirSVR-R/R剛PORTTRfiNRPOPTfiTlLFR1的轉(zhuǎn)儲(chǔ)文件隼關(guān)：D:TESTEXPDP_XTTS_TEST.DMPnJ傳輸表左間XTTS-TEST所需的數(shù)據(jù)丈件：D：APPfiDMINISTRfiTORORADfiTATESTXTIS_rESTBl.DEF作業(yè)FSYS.SYS_EXFOFT-TRANSPORTftBLE_01,r已丁16；13；3

35、1成功完成C=llsersXAdmin4.17上傳元數(shù)據(jù)到目標(biāo)庫(kù).tt-Bferf,I4wT-E4f*w*rvfH的e*厲礎(chǔ)ZJR-wri：-whriit舲ejii_rIfkftrw-jmq.rTSTftwf-旳!W國(guó)IE譏i|丄*Awct-Stai.wJoflnxT-hi;Dcuw%3C-J13h.JCE-whdTg血！i啦:i冊(cè)LiTollBWSsia.li和購(gòu)咽.dJ-.Xni_JJLUHia1ESTJjUC4C353St期許739j4L4|-nT5_TE5rab：DFr3E4I1IUcorsw-MLWM3L-1h1*目標(biāo)端數(shù)據(jù)庫(kù)基于源端測(cè)試用戶及角色構(gòu)建測(cè)試用戶createuserxt

36、ts_testidentifiedbyxtts_testaccountunlock;grantresougconnecttoxtts_test;sqlcreateuserxtts_testidentifiedbyxtts.testaccountunlock;Usercreated,sqlgrantresource,connecttoxtts_test;Grantsucceeded,SQL|目標(biāo)端數(shù)據(jù)庫(kù)導(dǎo)入測(cè)試表空間createorreplacedirectorydump_diras/tmp;exitimpdpsys/sysassysdbadirectory二dump_dirtransport_

37、datafiIes=/data/u01/app/oracle/oradata/dbrecover/target_xtts_01.dbfdumpfile二EXPDP_XTTS_TEST.DMPlogfile二impdp_xtts_test.logoracledbbackup/tmpJimpdpsys/sysasdirectory=dump_dirtranspoiles=,/data/uOl/app/oracle/oradata/dbrecover/target_xtts_01,dbfdumpfi1e=EXpdp_xtts_test.dmplogfmpdpums.test,1ogimport:Re

38、lease,0-ProductiononTueSep3丄6:10:432019CopyrightCc)1932,011,Orac1eand/oritsaffn1iates.Al1rightsrescrved.Connectedto:OracleDatabasellgLntErpriseLd*iliorReIeas11.0.4.0-胡biiProdurtionWithrhePartir-ion-ing,OLAP,DataM-iningandRealApplic+itionTesfingoptionswastertmblE,fsys.sysimport,transportablf_oj1yloaded/uniMciedSouruEtiinezonevwr