數(shù)據(jù)庫遷移方案

1、Hxxxxxxfexxxxxxxxx曲甜工xxxxxxxx占ri4U?-：iIpi_|tiUE-1-|!=;第 頁文檔控制此文檔僅供最終用戶審閱，不得向與此無關(guān)的個人或機構(gòu)傳閱或復(fù)制。修改記錄分發(fā)者審閱記錄年前完成XXXXX系統(tǒng)的數(shù)據(jù)庫遷移工作，同時對源庫進行小版本升級，有11.2.0.3升級到11.2.0.4版本。2.遷移前準備工作步驟內(nèi)容完成情況1目標庫操作系統(tǒng)安裝完成2目標庫操作系統(tǒng)優(yōu)化完成3目標庫RAC安裝完成4目標庫配置完成3.源庫備份rmantarget/msglog/uOl/bak/rman.logappendstartupnomount-rman自啟動一個實例RMANresto

2、respfiletopfile/u01/initdba.orafrom/u01/bakup/xxx;注意：修改磁盤組名稱，歸檔路徑、控制文件路徑，日志路徑，trace文件路徑、remote_listener還原控制文件在其中一個節(jié)點上執(zhí)行。用pfile啟動到nomount狀態(tài)RMANstartupnomuntpfile=/u01/app/xx/initdba.ora;rman執(zhí)行對控制文件的恢復(fù)RMANrestorecontrolfilefrom/HS5220/c-2006462633-20170123-03;Startingrestoreat2017-02-0412:16:56usingch

3、annelORA_DISK_1channelORA_DISK_1:restoringcontrolfileRMAN-00571:=RMAN-00569:=ERRORMESSAGESTACKFOLLOWS=RMAN-00571:=RMAN-03002:failureofrestorecommandat02/04/201712:16:57ORA-19870:errorwhilerestoringbackuppiece/HS5220/c-2006462633-20170123-03ORA-19504:failedtocreatefile+DG_DATAORA-17502:ksfdcre:4Faile

4、dtocreatefile+DG_DATAORA-15001:diskgroupDG_DATAdoesnotexistorisnotmountedORA-15040:diskgroupisincompleteORA-15040:diskgroupisincompleteORA-15040:diskgroupisincompleteORA-15040:diskgroupisincompleteORA-15040:diskgroupisincompleteORA-15040:diskgroupisincompleteoracleora8db1$ls-l$ORACLE_HOME/bin/oracle

5、-rwsr-s-x1oracleoinstall2398409683月1512:32/u01/app/oracle/product/11.2.0/db_1/bin/oracleoracleora8db1$exitlogoutrootora8db1#su-gridgridora8db1$cd$ORACLE_HOME/bin/gridora8db1bin$setasmgidsetasmgidsetasmgid0setasmgidwrap/上海新炬grfd0Ta8db1rbin$setasmgidwrap-o/u01/app/oracle/product/11.2.0/db_l/bin/oracle

6、gridora8db1bin$exitlogoutrootora8db1#su-oracleoracleora8db1$ls-l$ORACLE_HOME/bin/oracle-rwsr-s-x1oracleasmadmin2398409683月1512:32/u01/app/oracle/product/11.2.0/db_1/bin/oracleoracleora8db1$exportORACLE_SID=jsglrac1oracleora8db1$rmantarget/RecoveryManager:Release11.2.0.4.0-ProductiononThuMar1609:23:1

7、92017Copyright(c)1982,2011,Oracleand/oritsaffiliates.Allrightsreserved.RMAN-00571:=RMAN-00569:=ERRORMESSAGESTACKFOLLOWS=RMAN-00571:=RMAN-00554:initializationofinternalrecoverymanagerpackagefailedRMAN-04005:errorfromtargetdatabase:ORA-27140:attachtopost/waitfacilityfailedORA-27300:OSsystemdependentop

8、eration:invalid_egidfailedwithstatus:1ORA-27301:OSfailuremessage:OperationnotpermittedORA-27302:failureoccurredat:skgpwinit6ORA-27303:additionalinformation:startupegid=1001(oinstall),currentegid=1004(asmadmin)oracleora8db1$ps-ef|grepora_pmonoracle1556251008:59?00:00:00ora_pmon_jsglrac1oracle17368717

9、3043009:24pts/000:00:00grepora_pmonoracleora8db1$kill-9155625oracleora8db1$sqlplus/assysdbaSQL*Plus:Release11.2.0.4.0ProductiononThuMar1609:25:182017Copyright(c)1982,2013,Oracle.Allrightsreserved.Connectedtoanidleinstance.SYSjsglrac1startupnomountORACLEinstancestarted.TotalSystemGlobalArea3206836224

10、bytesFixedSizeVariableSizeDatabaseBuffersRedoBuffersSYSjsglrac1exit2257520bytes1174408592bytes1979711488bytes50458624bytesDisconnectedfromOracleDatabase11gEnterpriseEditionRelease11.2.0.4.0-64bitProductionWiththePartitioning,RealApplicationClusters,OLAP,DataMiningandRealApplicationTestingoptionsorac

11、leora8db1$rmantarget/RecoveryManager:Release11.2.0.4.0-ProductiononThuMar1609:25:372017Copyright(c)1982,2011,Oracleand/oritsaffiliates.Allrightsreserved.connectedtotargetdatabase:JSGLRAC(notmounted)RMANrestorecontrolfilefrom/backup/full_backup/jsglrac/ctl_file_0grv777r_1_1_20170315;Startingrestoreat

12、2017-03-1609:25:57usingtargetdatabasecontrolfileinsteadofrecoverycatalogallocatedchannel:ORA_DISK_1“上海新炬chanffel3ORA_DISK_1:vSID=1057instance=jsglracldevicetype=DISKchannelORA_DISK_1:restoringcontrolfilechannelORA_DISK_1:restorecomplete,elapsedtime:00:00:02outputfilename=+DG_DATA/jsglrac/controlfile

13、/current.267.938769959Finishedrestoreat2017-03-1609:25:59RMANRMANrestorecontrolfilefrom/u01/backup/xxx;修改參數(shù)文件中控制文件的名字生成spfile,放在共享設(shè)備上oracleora8db1$sqlplus/assysdbaSQL*Plus:Release11.2.0.4.0ProductiononThuMar1609:29:112017Copyright(c)1982,2013,Oracle.Allrightsreserved.Connectedto:OracleDatabase11gEnt

14、erpriseEditionRelease11.2.0.4.0-64bitProductionWiththePartitioning,RealApplicationClusters,AutomaticStorageManagement,OLAP,DataMiningandRealApplicationTestingoptionsSYSjsglrac1createspfile=+DG_DATAfrompfile;Filecreated.rootora8db1#su-gridgridora8db1$asmcmdASMCMDlsDG_DATA/DG_GI/ASMCMDcdDG_DATAASMCMDl

15、sJSGLRAC/ASMCMDcdJSGLRACASMCMDlsCONTROLFILE/PARAMETERFILE/ASMCMDcdPARAMETERFILEASMCMDlsspfile.256.938770171ASMCMDls-lTypeRedundStripedTimeSysNamePARAMETERFILEUNPROTCOARSEMAR1609:00:00Yspfile.256.938770171修改所有節(jié)點的pfile內(nèi)容，指向spfileSPFILE=+DATA/XX/spfiledba.ora$orapwdfile=?/dbs/orapwdbapassword=oracle在所有

16、節(jié)點上創(chuàng)建口令文件。注意：密碼要和原庫一樣4.6.restore數(shù)據(jù)庫4.6.1.將數(shù)據(jù)庫啟動到mount狀態(tài)-兩個節(jié)點都要mountSQLalterdatabasemount;在其中一個節(jié)點上執(zhí)行注冊備份信息到控制文件RMANcatalogstartwith/u01/backup/-;-后面一定要添加“/”字符restore數(shù)據(jù)文件先在源庫執(zhí)行：setlinesize100setlinesize160pagesize2000colfile_namefora60selectsetnewnamefordatafile|file_id|to|chr(39)|+DATA|chr(39)|;fromd

17、ba_data_files;生成修改數(shù)據(jù)文件名的批量語句runallocatechanneld1typedisk;allocatechanneld2typedisk;allocatechanneld3typedisk;allocatechanneld4typedisk;setnewname-此處加入上面查出的批量語句restoredatabase;SWITCHDATAFILEALL;releasechanneld1;releasechanneld2;releasechanneld3;releasechanneld4;(-恢復(fù)歸檔文件RMANcatalogstartwith/HS5220/0上海

18、新炬RMANlistbackupofarchivelogall;runallocatechannelc1typedisk;allocatechannelc2typedisk;allocatechannelc3typedisk;allocatechannelc4typedisk;setarchivelogdestinationto/HS5220;restorearchivelogfromlogseq170171untillogseq170226thread1;restorearchivelogfromlogseq149060untillogseq149109thread2;releasechan

19、nelc1;releasechannelc2;releasechannelc3;releasechannelc4;)Recover過程中會自動restore歸檔文件，同時recover歸檔文件4.6.2.查看源端數(shù)據(jù)庫文件存放位置信息selectfile#,namefromv$datafile;查看目標庫中文件號與文件名對應(yīng)關(guān)系，并記錄4.7.recover數(shù)據(jù)庫注冊歸檔信息到控制文件RMANcatalogstartwith/u01/archivelog/;RMANrecoverdatabase;/*先看歸檔的list信息RMANlistbackupofarchivelogall;這里要注意，

20、這里涉及到2個節(jié)點的歸檔文件，我們要找到2個節(jié)點最后的歸檔文件，看下哪個歸檔文件的sen小，然后恢復(fù)到這個sen。RMANrunsetuntiosen2986227;reeoverdatabase;*/停應(yīng)用程序。在SCOtt用戶下創(chuàng)建一個測試表，來驗證將來是否恢復(fù)完全查看是否有大查詢selectcount(*)fromgv$session_longopswheretime_remaining0;查看大事物selectsum(used_ublk)fromgv$transaction;查看恢復(fù)操作select*fromgv$fast_start_transactions;select*fromg

21、v$fast_start_servers;清除col_usage$記錄，當(dāng)關(guān)閉數(shù)據(jù)庫的時候就不會有大量的記錄需要清理execDBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;altersystemswitchlogfile;altersystemswitchlogfile;altersystemcheckpoint;altersystemcheckpoint;srvctlstopdatabase-dPRD-oimmediatesrvctlstartdatabase一dPRD一omount拷貝源端剩下的歸檔和onlineredo從11.2.0.3開始支持本地AS

22、M到遠端ASM之間的copy。歸檔文件可以放在本地，也可以放在ASM磁盤組中。再次recover我們的歸檔文件是從其他地方copy過來的，當(dāng)前的數(shù)據(jù)庫并沒有相關(guān)記錄。所以我們需要先將歸檔文件注冊到控制文件中，然后才能使用。RAMNcatalogarchivelog/u01/backup/xx;拷貝過來的onlineredofile，必須放在參數(shù)文件中指定的redo路徑下。ASMCMDcp/backup/group_1.258.886258033group_1copying/backup/group_1.258.886258033-+dg_data/jsglrac/onlinelog/group

23、_1RMANrecoverdatabase;完全恢復(fù)upgatedb關(guān)閉2節(jié)點數(shù)據(jù)庫,同時在節(jié)點1上修改參數(shù)關(guān)閉歸檔荻上海新炬SQLatlersystemtsetcluster_database=falsescope=spfile;SQLshutdownimmediate;SQLstartupSQLalterdatabaseopenresetlogs;SQLshutdownimmediate;SQLstartupupgrade;SQL?/rdbms/admin/catupgrd.sqlSQLshutdownimmediate;SQLstartupSQL?/rdbms/admin/utlrp.sql-編譯失效對象SQaltersystemsetcluster_database=truescope=spfile;crsctlstatres-t#crsctlstopcl