oracle備份恢復(fù)語句整理最全

Oracle備份恢復(fù)場(chǎng)景總結(jié)一、數(shù)據(jù)文件、表空間恢復(fù)1、數(shù)據(jù)庫文件恢復(fù)1＞場(chǎng)景一：?jiǎn)栴}描述：test.dbf丟失，有RMAN備份。重啟報(bào)錯(cuò)：Startup;ORA-01157:cannotidentify/lockdatafile5-seeDBWRtracefileORA-01110:datafile5:'/oracle/app/oracle/oradata/orcl/test01.dbfrun{startupmount;allocatechannelch00type'SBT_TAPE';restoredatafile5;recoverdatafile5;alterdatabaseopen;releasechannelch00;}2＞場(chǎng)景二問題描述：模才test表空間損壞，刪除數(shù)據(jù)文件（恢復(fù)到新目錄）舊目錄：/oracle/app/oracle/oradata新目錄：/home/oraclerun{allocatechannelch00type'SBT_TAPE';sql'alterdatabasedatafile5offline';setnewnamefordatafile5to'/home/oracle/test01.dbf';restoredatafile5;switchdatafile5;recoverdatafile5;sql'alterdatabasedatafile4online';releasechannelch00;}說明：setnewnamefor告訴RMAN還原數(shù)據(jù)文件的新位置在哪里。這個(gè)命令在restore前出現(xiàn)。switchdatafile更新controlfile,記錄這個(gè)新位置。這個(gè)命令要在recover前出現(xiàn)2、表空間恢復(fù)恢復(fù)誤刪除表空間（已備份），RMAN必須通過備份的控制文件（與之配套的老控制文件）進(jìn)行恢復(fù)。run{startupnomount;allocatechannelditypesbt;restorecontrolfilefrom'/bk_43_1_920726015';sql'alterdatabasemount,;setuntiltime'2016-08-2413:17:16,;restoredatabase;recoverdatabase;alterdatabaseopenreadonly;releasechanneld1;確保沒問題在：shutdownabort;startupmount;alterdatabaseopenresetlogs;二、控制文件恢復(fù)1、場(chǎng)景一：控制文件自動(dòng)備份的恢復(fù)startupnomountsetdbidxxx蠕指定DBIDrestorecontrolfilefromautobackup;alterdatabasemount;recoverdatabase;alterdatabaseopenresetlogs;如何尋找DBID?.既然是默認(rèn)路徑，那么就去默認(rèn)路徑尋找，一般在$ORACLE_HOME/dbs目錄下,例如c-361654012-20150312-04其中361654012就是DBID.在數(shù)據(jù)文件的頭部含有DBID、場(chǎng)景一：控制文件從非默認(rèn)地方恢復(fù)這里控制文件從nbu備份的磁帶上恢復(fù)備份路徑startupnomount#從本地恢復(fù)restorecontrolfilefrom-361654/C12-20150312Q4'在此之前無需指定DBID或者#從SBT_TAP政復(fù)restorecontrolfilefrom'/cntrl_23_1_920645270';run{allocatechannelditypesbt;restorecontrolfilefrom'/cntrl231920645270,;sql'alterdatabasemount,;recoverdatabase;sql'alterdatabaseopenresetlogs';releasechanneldi;)注釋：1>、restorecontrolfile必須寫在run塊了，否則報(bào)錯(cuò)：RMAN-06172:noAUTOBACKUPfoundorspecifiedhandleisnotavalidcopyorpiece2>sbt的磁帶號(hào)-/cntrl231920645270可以從已備份成功的任務(wù)里查看。三、Redo文件恢復(fù)聯(lián)機(jī)重做日志的3種狀態(tài)：current當(dāng)前inactive非活動(dòng)active活動(dòng)觀察數(shù)據(jù)恢復(fù)是否需要聯(lián)機(jī)重做日志來恢復(fù)。inactive日志丟失的恢復(fù)一實(shí)例恢復(fù)不需要active日志丟失的恢復(fù)：日志組對(duì)應(yīng)的臟塊還沒有從databuffer寫入到datafile,不能被覆蓋（但已寫入到歸檔里）一實(shí)例恢復(fù)需要current日志丟失的恢復(fù)一實(shí)例恢復(fù)需要1、場(chǎng)景一：inactive日志丟失的恢復(fù)1＞正常關(guān)閉數(shù)據(jù)庫后（redo的丟失就屬于inactive日志丟失的恢復(fù)）shutdownimmediatermredo*startupmountalterdatabaseclearlogfile'/u01/app/oracle/oradata/ortest/redo01.log';##alterdatabaseclearlogfilegroupnt行alterdatabaseclearlogfile'/u01/app/oracle/oradata/ortest/redo03.log';alterdatabaseclearunarchivedlogfile'/u01/app/oracle/oradata/ortest/redo02.log';alterdatabaseopen;此情況不會(huì)丟失數(shù)據(jù)。起來后多切幾遍日志，看是否正常：altersystemswitchlogfile;注意事項(xiàng)：必須clear完非currentlog之后，才能clearunarchivedcurrentlog（當(dāng)你不帶參數(shù)unarchived而clear當(dāng)前日志的時(shí)候會(huì)提示你日志需要?dú)w檔）。2＞非關(guān)閉數(shù)據(jù)庫后（inactive日志丟失）shutdownabortrmredo01.logstartupmountalterdatabaseclearlogfile'/u01/app/oracle/oradata/ortest/redo01.log';alterdatabaseopen;此種情況不會(huì)丟失數(shù)據(jù)2、場(chǎng)景二：active日志丟失非正常關(guān)閉數(shù)據(jù)庫，active日志丟失shutdownabortrmredo01.logstartupmountrecoverdatabaseuntilcancel;alterdatabaseopenresetlogs;此種情況不會(huì)丟失數(shù)據(jù)。（active的日志已寫入歸檔）3、場(chǎng)景三：current日志丟失此種情況會(huì)丟失數(shù)據(jù)：shutdownabortrmredo01.logstartupmountaltersystemset"_allow_resetlogs_corruption"=truescope=spf!fel;隱藏參數(shù)recoverdatabaseuntilcancel;alterdatabaseopenresetlogs;四、不完全恢復(fù)RMAN不完全恢復(fù)：三個(gè)標(biāo)準(zhǔn)模式：基于time、基于scn和基于sequence還有一種是基于cancel的恢復(fù)（注意是在SQL下執(zhí)行）在不完全恢復(fù)時(shí)，有時(shí)為了保險(xiǎn)往往一步把數(shù)據(jù)庫openreadonly,確保沒問題再openresetlogs：alterdatabaseopenreadonly;啟動(dòng)到mount下執(zhí)行：run{allocatechannelc1typedisk;allocatechannelc2typedisk;setuntiltime'2016-06-0611:54:05';restoredatabase;recoverdatabase;alterdatabaseopenresetlogs;}〃基于scn的方法與基于time相似，語法是setuntilscn6689163;〃基于日志的方法類似手工恢復(fù)的例子，語法是setuntilsequence3;//有時(shí)報(bào)錯(cuò)格式不對(duì)恢復(fù)前執(zhí)行：//linux操作系統(tǒng)：exportNLS_DATE_FORMAT='YYYY-MM-DDHH24:MI:SS'五、databaseWM1、原機(jī)器恢復(fù)1>恢復(fù)spfile文件-到nomount狀態(tài)：可以從數(shù)據(jù)庫模板里隨便找個(gè)pfile起到mount下，再恢復(fù)spfileo因?yàn)榉峙鋍hannel必須得在nomount下面：rman>run{allocatechannelc1typesbt;restorespfilefrom'/al_63_1_920727874';releasechannelc1;}2＞恢復(fù)控制文件-到mount狀態(tài)run{allocatechannelc1typesbt;restorecontrolfilefrom'/cntrl_64_1_920727890';sql'alterdatabasemount';releasechannelc1;}3＞恢復(fù)數(shù)據(jù)文件-以readonly打開run{allocatechannelc1typesbt;allocatechannelc2typesbt;allocatechannelc3typesbt;allocatechannelc4typesbt;setuntiltime'2016-08-2413:40:00';restoredatabase;recoverdatabase;sql'alterdatabaseopenreadonly';releasechannelc1;releasechannelc2;releasechannelc3;releasechannelc4;}注釋：查看恢復(fù)用到的截止時(shí)間戳：用listbackup;查看備份的歸檔最后的時(shí)間：BSR?vTypsElapsedTineTimS1ZSfi.OOK3Sr_TAFE2Cl?-08-2^BPKey;51SudiunxAVAILABLECQCFHunnud:NOTag:TAS201GOfii4T1344LDHandl%!al_63_l_320727074Mftdxa:taaaac□￡ArchivedL3gainbackup昌隹二51Thrd3cqLg5CKLowTimeNext5CHNextTime11102S669701€-08-2413：3＜s051026D772016-08-2413s36：551z1026077zoi^-CB-2913：:SS10261132016-OB-Z413S36：S513102C113201C-CQ=245510262962016-00-2113^41;22110262962016-C8-2413HI;2210263342016-OB-2113￡41:221-S3二口U后一口13:44-1816-IB1865362016-00-2413;打二偈4＞打開數(shù)據(jù)庫確定無誤后，已resetlogs打開數(shù)據(jù)庫:shutdownabort;startupmount;alterdatabaseopenresetlogs;異機(jī)恢復(fù):參考原機(jī)器恢復(fù)，主要的就是，再每個(gè)run里面寫著一條:run{…：SEND'NB_ORA_CLIENT=rac1;…}rac1是nbu上原機(jī)器的client名字。具體參考另外一個(gè)work文檔。附錄1、rman里allocate的單獨(dú)用法:RMAN>allocatechannelformaintenancedevicetypesbtparms'SBT_LIBRARY=oracle?disksbt,ENV=(BACKUP_DIR=/tmp)';allocatedchannel:ORA_MAINT_SBT_TAPE_1channelORA_MAINT_SBT_TAPE_1:SID=63devicetype=SBT_TAPEchannelORA_MAINT_SBT_TAPE_1:WARNING:OracleTestDis