學習oracle dba12c18c19c ocp認證培訓實戰(zhàn)教程課程筆記sir課前軟件certified professional full

OracleOracleCertifiedProfessionalOCPCode：1Z0-1章備份與恢復(fù)D50102GC20_SG2Lesson14:BackupandRecoveryStatementUserprocessUserMedia

用戶進程故障(PMON自動處理)作為DBA，需要根據(jù)實際生產(chǎn)環(huán)境來收集各個業(yè)務(wù)部門及的需求及意見，包括D50102GC20_SG2Lesson17:Moving有Recover的概念。 份，還原與恢復(fù)過程自動完成，可以備份恢復(fù)ForForOracleFlashbackVersionsQueryOracleFlashbackTransactionQueryForerrorOracleFlashbackTransactionBackoutOracleFlashbackTableOracleFlashbackArchivelog模式，將RedoLog重做日志通過 NoArchivelog時無法進行聯(lián)機熱備，且在數(shù)據(jù)庫損壞后只能執(zhí)行完整的數(shù)據(jù)庫還原Restore，無法進行恢復(fù)Recover。2章用戶管理手工備份(冷備 獲取物理文件 SQL>selectnamefromSQL>selectnamefromSQL>selectmemberfrom關(guān)閉數(shù)據(jù)庫SQL>SQL>shutdown拷貝文件[oracle@henry~]$vibackup.sql/*cp/*cp/*cp開啟數(shù)據(jù)庫SQL>SQL>手工備份(熱備backupmode（backup模式beginbackup（在數(shù)據(jù)文件上生成檢查點，寫入SCN號，將來恢復(fù)的時候以此SCN為起點）SQL>alterdatabasebeginbackup;SQL>alterdatabaseendbackup;SQL>altertablespacetestbeginbackup;SQL>altertablespacetestendbackup;SQL>select*fromSQL>altertablespacetestbeginSQL>selectfile#,checkpoint_changefromv$datafile_header;在備份期間，SCN號被凍結(jié)，它是恢復(fù)階段運用日志的起點。SQL>select*fromv$backup;備份完畢后，盡可能的快速ENDBACKUP。SQL>altertablespacetestendbackup;SQL>altertablespacetestbeginSQL>shutdownSQL>[oracle@henry~]$sqlplus/assysdbaSQL>startupORACLEinstanceTotalSystemGlobalArea FixedSize Variable Database RedoBuffers Databasemounted.ORA-10873:file7needstobeeithertakenoutofbackupmodeormediarecoveredORA-01110:datafile7:'/u01/app/oracle/oradata/PROD1/test01.dbf'SQL>selectopen_modefromv$database;SQL>altertablespacetestendbackup;SQL>alterdatabaseopen;DBVERIFY工具是一個運行于操作系統(tǒng)提示符下的外部程序，用于驗證數(shù)據(jù)文件，檢控制文件，歸檔日志和RMAN備份集。例如：對某個DATAFILE做壞塊檢查[oracle@henry~]$dbvfile=/u01/app/oracle/oradata/PROD1/test01.dbfSPLITOracleBlockOSBlock，而拷貝過程中，OracleDBWR時不時的從內(nèi)存中刷OracleBlock（臟塊）OS級的拷貝便可能造成：一個OracleBlock是由不同的版本組成，比如未被DBWR刷新HeaderBlock加上另一部分被刷新的FootBlock，這樣拷貝出來的OracleBlcok就是SplitBlock。數(shù)據(jù)庫的一致性是不允許OracleBlockSplit的，OracleBackupModeDBWRRedoBuffer，這樣，雖然拷貝后的文件里仍然含有SplitBlock，而當需要恢復(fù)時，日志會前滾該塊的前鏡像，以保證所有被恢復(fù)的OracleBlock是一個完整的版本。3章手工完全一次Commit狀態(tài)。（事務(wù)沒有結(jié)束的，實例恢復(fù)統(tǒng)一回滾）Restore：OSDatafileRecover：SQL*PLUS利用歸檔日志和當前的Redo日志做恢復(fù)RecoverTablespace：非關(guān)鍵表空間損壞，表空間下某些數(shù)據(jù)文件不能，一般是在open下完成。file。一旦損壞，數(shù)據(jù)庫將無法維持在open狀態(tài)（或死機。SQL>select*fromv$recover_file;SQL>select*fromv$recovery_log;SQL>select*fromSQL>selectnamefromv$datafile;SQL>selectnamefromv$controlfile;數(shù)據(jù)SQL>shutdownSQL>!cp/u01/app/oracle/oradata/PROD1/*.dbf/u01/app/oracle/backup/SQL>!cp/u01/app/oracle/oradata/PROD1/control01.ctlSQL>!cpSQL>startupSQL>createtablerecover_abc(idnumber);SQL>insertintorecover_abcvalues(1);SQL>commit;SQL>insertintorecover_abcvalues(2);SQL>commit;SQL>insertintorecover_abcSQL>!rm-rfSQL>[oracle@henry~]$sqlplus/assysdbaSQL>startupORACLEinstanceTotalSystemGlobalArea FixedSize Variable Database RedoBuffers Databasemounted.ORA-01157:cannotidentify/lockdatafile1-seeDBWRtracefileORA-01110:datafile1:u01/app/oracle/oradata/PROD1/system01.dbf'是沒有表recover_abc的。SQL>alterdatabaseopen;alterdatabaseopen*ERRORatlineORA-01113:file1needsmediaORA-01110:datafile1:SQL>select*fromv$recover_file;SQL>recoverdatabase;SQL>alterdatabaseopen;SQL>select*fromrecover_abc;驗證數(shù)據(jù)是否有3。非關(guān)鍵數(shù)據(jù)文件損壞（OPEN狀態(tài)下的恢復(fù)TESTSQL>selectnamefromv$tablespace;SQL>createtablerecover_b(idnumber)tablespaceSQL>insertintorecover_bvalues(1);SQL>insertintorecover_bvalues(2);SQL>insertintorecover_bvalues(3);SQL>commit;TEST表空間SQL>setlinesize500SQL>colnameforSQL>,fromv$datafilea,v$tablespacebwhereSQL>!rm-rfSQL>insertintorecover_bvalues(4);SQL>insertintorecover_bvalues(5);SQL>commit;SQL>altersystemswitchSQL>altersystemSQL>[oracle@henry~]$sqlplus/assysdbaSQL>startupORACLEinstanceTotalSystemGlobalArea FixedSize Variable Database RedoBuffers Databasemounted.ORA-01157:cannotidentify/lockdatafile7-seeDBWRtracefile暫時把7OFFLINE掉，先把數(shù)據(jù)庫打開。SQL>alterdatabasedatafile7offline;SQL>alterdatabaseopen;此時查詢需要恢復(fù)文件提示為：NOTFOUNDSQL>select*fromv$recover_file;SQL>select*fromSQL>recoverdatafileORA-00279:change generatedat08/31/201811:54:13neededforthread1ORA-00289:suggestion:/u01/app/oracle/arch/1_32_ ORA-00280: forthread1isinsequenceSpecifylog:{<RET>=suggested|filename|AUTO|LogMediarecoverySQL>selectname,statusfromSQL>alterdatabasedatafile7SQL>select*fromTEST表空間是在開啟歸檔模式前就已經(jīng)存在的，且對其對了很多SQL>!rm-rf/u01/app/oracle/oradata/PROD1/test01.dbfSQL>insertintorecover_bvalues(6);SQL>insertintorecover_bvalues(7);SQL>insertintorecover_bvalues(8);SQL>commit;SQL>altersystemswitchlogfile;SQL>altersystemcheckpoint;altersystemcheckpoint*ERRORatlineORA-03113:end-of-fileoncommunicationchannelProcessID:59727SessionID:1Serialnumber:5SQL>exit[oracle@henry~]$sqlplus/assysdbaSQL>startupORACLEinstanceTotalSystemGlobalArea FixedSize Variable Database RedoBuffers Databasemounted.ORA-01157:cannotidentify/lockdatafile7-seeDBWRtracefileSQL>!lsSQL>alterdatabasedatafile7offline;SQL>alterdatabaseopen;SQL>select*fromv$recover_file;SQL>alterdatabasecreatedatafile7;SQL>select*fromv$recover_file;SQL>recoverdatafileORA-00279:change generatedat07/22/201814:19:42neededforthread1ORA-00289:suggestion:/u01/app/oracle/arch/1_9_ ORA-00280: forthread1isinsequenceSpecifylog:{<RET>=suggested|filename|AUTO|ORA-00308:cannotopenarchivedlog'/u01/app/oracle/arch/1_9_ ORA-27037:unabletoobtainfilestatusLinux-x86_64Error:2:NosuchfileordirectoryAdditionalinformation:3創(chuàng)建表空間recover_cSQL>createtablespacerecover_cdatafile'/u01/app/oracle/oradata/PROD1/recover01.dbf'size10m;SQL>createtablerecover_c(idnumber)tablespacerecover_c;SQL>insertintorecover_cvalues(1);SQL>insertintorecover_cvalues(2);SQL>insertintorecover_cvalues(3);SQL>commit;SQL>altertablespacerecover_cbeginSQL>!cpSQL>altertablespacerecover_cend備份完成后，刪除表空間recover_c，并繼續(xù)插入數(shù)據(jù)，提交：SQL>insertintorecover_cvalues(4);SQL>insertintorecover_cvalues(5);SQL>commit;SQL>altersystemswitchSQL>altersystemswitch*ERRORatlineORA-03113:end-of-fileoncommunicationchannelProcessID:64689SessionID:1Serialnumber:5SQL>exit[oracle@henry~]$sqlplus/asSQL>ORACLEinstanceTotalSystemGlobalArea FixedSize Variable Database RedoBuffers Databasemounted.ORA-01157:cannotidentify/lockdatafile11-seeDBWRtraceORA-01110:datafile11:啟動時，報11問題，將11offline脫SQL>alterdatabasedatafile11offline;SQL>alterdatabaseopen;SQL>select*fromSQL>alterdatabasecreatedatafile11;SQL>select*fromv$recover_file;SQL>recoverdatafileORA-00279:change generatedat08/31/201812:59:11neededforthread1ORA-00289:suggestion:/u01/app/oracle/arch/1_37_ ORA-00280:change forthread1isinsequence#37Specifylog:{<RET>=suggested|filename|AUTO|CANCEL}LogMediarecoverySQL>alterdatabasedatafile11online;查詢recover_c表，數(shù)據(jù)有幾條？SQL>select*fromrecover_c;4章手工不完全SCN，而until是指恢復(fù)在時間點前停止（考點 timechnge(cnolile[oracle@henry~]$sqlplus/assysdbaSQL>select*fromrecover_c;SQL>selectgroup#,sequence#,statusfromv$log;SQL>insertintorecover_cvalues(6);SQL>SQL>insertintorecover_cvalues(7);SQL>commit;SQL>droptablerecover_cSQL>altersystemswitchlogfile;SQL>select*fromv$archived_log;SQL>selectSUPPLEMENTAL_LOG_DATA_MINfromv$database;SQL>alterdatabaseaddsupplementallogdata;SQL>execute SQL>executeSQL>createtablelogmnr_recover_cnologgingasselect*fromv$logmnr_contents;SQL>executedbms_logmnr.end_logmnr;SQL> fromwhereseg_name='RECOVER_B';SQL>altersystemcheckpoint;SQL>shutdownabortSQL>!rm-rfSQL>!cp/u01/app/oracle/backup/*.dbf/u01/app/oracle/oradata/PROD1/SQL>SQL>recoverdatabaseuntiltime'2018-08-31ORA-00279:change generatedat08/31/201811:54:13neededforthread1ORA-00289:suggestion:/u01/app/oracle/arch/1_32_ ORA-00280:change forthread1isinsequence#32Specifylog:{<RET>=suggested|filename|AUTO|CANCEL}ORA-00279:change generatedat08/31/201812:02:32neededforthread1ORA-00289:suggestion:/u01/app/oracle/arch/1_33_ ORA-00280: forthread1isinsequenceORA-00279:change generatedat08/31/201812:11:14neededforthread1ORA-00289:suggestion:/u01/app/oracle/arch/1_34_ ORA-00280: forthread1isinsequenceORA-00279:change generatedat08/31/201812:15:26neededforthread1ORA-00289:suggestion:/u01/app/oracle/arch/1_35_ ORA-00280: forthread1isinsequenceORA-00279:change generatedat08/31/201812:42:57neededforthread1ORA-00289:suggestion:/u01/app/oracle/arch/1_36_ ORA-00280: forthread1isinsequenceORA-00279:change generatedat08/31/201812:45:28neededforthread1ORA-00289:suggestion:/u01/app/oracle/arch/1_37_ ORA-00280: forthread1isinsequenceORA-00279:change generatedat08/31/201813:02:19neededforthread1ORA-00289:suggestion:/u01/app/oracle/arch/1_38_ ORA-00280: forthread1isinsequence最后數(shù)據(jù)庫以resetlogs方式打開SQL>alterdatabaseopenresetlogs;SQL>select*fromrecover_c;Resetlogs后，日志sequenceSQL>select*fromSQL>shutdownSQL>!cp/u01/app/oracle/oradata/PROD1/*/u01/app/oracle/backup/SQL>!cp/u01/app/oracle/fast_recovery_area/PROD1/control02.ctlSQL>startupSQL>selectgroup#,sequence#,statusfromv$log;SQL>insertintoabcvalues(39);SQL>SQL>altersystemswitchlogfile;SQL>altersystemswitchlogfile;SQL>altersystemswitchlogfile;SQL>selectgroup#,sequence#,statusfromv$log;SQL>insertintoabcvalues(42);SQL>SQL>altersystemswitchlogfile;SQL>altersystemswitchlogfile;SQL>altersystemswitchlogfile;SQL>selectgroup#,sequence#,statusfromv$log;SQL>insertintoabcvalues(45);SQL>SQL>altersystemswitchlogfile;SQL>altersystemswitchlogfile;SQL>altersystemswitchlogfile;SQL>selectgroup#,sequence#,statusfromv$log;SQL>select*fromabc;值在歸檔日志45號里。SQL>!rm-rf/u01/app/oracle/oradata/PROD1/users01.dbfSQL>shutdownabortSQL>!mv SQL>!cpSQL>startupTotalSystemGlobalArea FixedSize Variable Database RedoBuffers Databasemounted.ORA-01113:4ORA-01110:4:SQL>recoverdatafileORA-00279:更改 (在09/01/201819:32:24生成)對于線程1是必需的ORA-建議: ORA-00280:更 (用于線程1)在序列#29Specifylog:{<RET>=suggested|filename|AUTO|CANCEL}ORA-00279:更改 (在09/01/201819:36:59生成)對于線程1是必需的ORA-建議: ORA-00280:更 (用于線程1)在序列#30ORA-00279:更改 (在09/01/201819:37:54生成)對于線程1是必需的ORA-建議: ORA-00280:更 (用于線程1)在序列#31ORA-00279:更改 (在09/01/201819:37:57生成)對于線程1是必需的ORA-建議: ORA-00280:更 (用于線程1)在序列#32ORA-00279:更改 (在09/01/201819:38:28生成)對于線程1是必需的ORA-建議: ORA-00280:更 (用于線程1)在序列#33ORA-00279:更改 (在09/01/201819:38:29生成)對于線程1是必需的ORA-建議: ORA-00280:更 (用于線程1)在序列#34ORA-00279:更改 (在09/01/201819:38:30生成)對于線程1是必需的ORA-建議: ORA-00280:更 (用于線程1)在序列#35ORA-00308:無法打開歸檔日志'/u01/app/oracle/arch/1_35_ ORA-27037:無法獲得文件狀態(tài)Linux-x86_64Error:2:NosuchfileordirectoryAdditionalinformation:3完全恢復(fù)失敗，因為缺少了1_35_ .dbf這個歸檔日志（我們更改了名稱SQL>!rm-rfSQL>!cp/u01/app/oracle/backup/*.dbf/u01/app/oracle/oradata/PROD1/SQL>recoverdatabaseuntilcancel這時候如果使用auto，但最后一個無法應(yīng)用，依然需要cancel。如果使用cancel，直接應(yīng)用到最后一個可以利用的。ORA-00279:change generatedat09/01/201819:59:44neededforthread1ORA-00289:suggestion:/u01/app/oracle/arch/1_1_ ORA-00280: forthread1isinsequenceSpecifylog:{<RET>=suggested|filename|AUTO| ORA-00279:change generatedat09/01/201820:02:05neededforthread1ORA-00289:suggestion:/u01/app/oracle/arch/1_2_ ORA-00280: forthread1isinsequenceORA-00278:logfile'/u01/app/oracle/arch/1_1_ .dbf'nolongerneededforthisrecoverySpecifylog:{<RET>=suggested|filename|AUTO| ORA-00279:change generatedat09/01/201820:02:07neededforthread1ORA-00289:suggestion:/u01/app/oracle/arch/1_3_ ORA-00280: forthread1isinsequenceORA-00278:logfile'/u01/app/oracle/arch/1_2_ .dbf'nolongerneededforthisrecoverySpecifylog:{<RET>=suggested|filename|AUTO| ORA-00279:change generatedat09/01/201820:02:11neededforthread1ORA-00289:suggestion:/u01/app/oracle/arch/1_4_ ORA-00280: forthread1isinsequenceORA-00278:logfile'/u01/app/oracle/arch/1_3_ .dbf'nolongerneededforthisrecoverySpecifylog:{<RET>=suggested|filename|AUTO| ORA-00279:change generatedat09/01/201820:02:37neededforthread1ORA-00289:suggestion:/u01/app/oracle/arch/1_5_ ORA-00280: forthread1isinsequenceORA-00278:logfile'/u01/app/oracle/arch/1_4_ .dbf'nolongerneededforthisrecoverySpecifylog:{<RET>=suggested|filename|AUTO| ORA-00279:change generatedat09/01/201820:02:38neededforthread1ORA-00289:suggestion:/u01/app/oracle/arch/1_6_ ORA-00280: forthread1isinsequenceORA-00278:logfile'/u01/app/oracle/arch/1_5_ .dbf'nolongerneededforthisrecoverySpecifylog:{<RET>=suggested|filename|AUTO| ORA-00279:change generatedat09/01/201820:02:41neededforthread1ORA-00289:suggestion:/u01/app/oracle/arch/1_7_ ORA-00280: forthread1isinsequenceORA-00278:logfile'/u01/app/oracle/arch/1_6_ .dbf'nolongerneededforthisrecoverySpecifylog:{<RET>=suggested|filename|AUTO| ORA-00308:cannotopenarchivedlog'/u01/app/oracle/arch/1_7_ ORA-27037:unabletoobtainfilestatusLinux-x86_64Error:2:NosuchfileordirectoryAdditionalinformation:3Specifylog:{<RET>=suggested|filename|AUTO|CANCEL}Mediarecoveryresetlogs方式打開數(shù)據(jù)庫：SQL>alterdatabaseopenresetlogs;SQL>select*fromabc;SCN版本，它們之間又增加過表空間（數(shù)據(jù)文件；或者當前控制文件沒有Specifylog:{<RET>=suggested|filename|AUTO|此語法的出現(xiàn)是由于控制文件和當前日志中不一致，當前日志的scn總是的，而控制文件可能是老的或尚未更新的（shutdwonabort過)。filename：輸入當前文件的路徑和文件名，是指currentlog的恢復(fù)resetlogs打開數(shù)據(jù)庫。（就是將日志組序號重置）備份的控制文件包含新建表空間（newtestSQL>selectnamefromv$datafile;SQL>selectnamefromv$controlfile;SQL>shutdownSQL>!rm-rfSQL>!cp/u01/app/oracle/oradata/PROD1/*/u01/app/oracle/backup/SQL>!cp/u01/app/oracle/fast_recovery_area/PROD1/control02.ctlSQL>startupSQL>selectnamefromSQL>createtablespacenewtestdatafile'/u01/app/oracle/oradata/PROD1/newtest01.dbf'size10m;SQL>createtablenewtest(idnumber)tablespacenewtest;SQL>insertintonewtestvalues(1);SQL>insertintonewtestvalues(2);SQL>insertintonewtestvalues(3);SQL>commit;SQL>selectgroup#,sequence#,statusfromv$log;SQL>altersystemswitchlogfile;SQL>alterdatabasebackupcontrolfiletoSQL>!rm-rf/u01/app/oracle/oradata/PROD1/newtest01.dbfSQL>altersystemcheckpoint;altersystem*ERRORatlineORA-03113:end-of-fileoncommunicationchannelProcessID:31875SessionID:1Serialnumber:5SQL>exit[oracle@henry~]$rm-rf/u01/app/oracle/oradata/PROD1/*.dbf[oracle@henry~]$rm-rf/u01/app/oracle/oradata/PROD1/control01.ctl[oracle@henry~]$rm-rf[oracle@henry~]$cp[oracle@henry~]$cp[oracle@henry~]$cp/u01/app/oracle/backup/con.bak[oracle@henry~]$sqlplus/assysdbaSQL>SQL>select from面有記錄表空間newtest，但是數(shù)據(jù)文件是老舊的，沒有記錄，所以顯示為0.SQL>recoverdatabaseusingbackupcontrolfileORA-00283:recoverysessioncanceledduetoerrorsORA-01110:datafile11:'/u01/app/oracle/oradata/PROD1/newtest01.dbf'ORA-01157:cannotidentify/lockdatafile11-seeDBWRtracefileORA-01110:datafile11:SQL>alterdatabasecreatedatafileSQL>recoverdatabaseusingbackupORA-00279:change generatedat09/01/201821:11:31neededforthread1ORA-00289:suggestion:/u01/app/oracle/arch/1_3_ ORA-00280: forthread1isinsequenceSpecifylog:{<RET>=suggested|filename|AUTO|ORA-00279:change generatedat09/01/201821:17:02neededforthread1ORA-00289:suggestion:/u01/app/oracle/arch/1_4_ ORA-00280: forthread1isinsequenceORA-00278:logfile'/u01/app/oracle/arch/1_3_ .dbf'nolongerneededforthisrecoveryORA-00308:cannotopenarchivedlog'/u01/app/oracle/arch/1_4_ ORA-27037:unabletoobtainfilestatusLinux-x86_64Error:2:NosuchfileordirectoryAdditionalinformation:3SQL>!ls/u01/app/oracle/arch/1_4_ SQL>!ls/u01/app/oracle/oradata/PROD1/*.logSQL>recoverdatabaseusingbackupcontrolfileORA-00279:change generatedat09/01/201821:17:02neededforthread1ORA-00289:suggestion:/u01/app/oracle/arch/1_4_ ORA-00280:change forthread1isinsequence#4Specifylog:{<RET>=suggested|filename|AUTO|CANCEL}LogMediarecoverySQL>alterdatabaseopenresetlogs;SQL>select*fromnewtest;[oracle@henry~]$rm-rf/u01/app/oracle/backup/*[oracle@henry~]$sqlplus/assysdbaSQL>selectnamefromv$datafile;SQL>selectnamefromv$controlfile;SQL>shutdownimmediate;SQL>!cp/u01/app/oracle/oradata/PROD1/*/u01/app/oracle/backup/SQL>!cp/u01/app/oracle/fast_recovery_area/PROD1/control02.ctlSQL>startupSQL>alterdatabasebackupcontrolfiletoSQL>createtablespacenewtest2datafile'/u01/app/oracle/oradata/PROD1/newtest02.dbf'size10m;SQL>createtablenewtest2(idnumber)tablespacenewtest2;Tablecreated.SQL>insertintonewtest21rowSQL>insertintonewtest2values(2);1rowcreated.SQL>insertintonewtest2values(3);SQL>commit;SQL>!rm-rf/u01/app/oracle/oradata/PROD1/newtest02.dbfSQL>insertintonewtest2values(4);1rowSQL>insertintonewtest2values(5);1rowcreated.SQL>SQL>altersystemcheckpoint;altersystemcheckpoint*ERRORatlineORA-03113:end-of-fileoncommunicationchannelProcessID:38162SessionID:1Serialnumber:5SQL>exit[oracle@henry~]$rm-rf/u01/app/oracle/oradata/PROD1/*.dbf[oracle@henry~]$rm-rf/u01/app/oracle/oradata/PROD1/control01.ctl[oracle@henry~]$rm-rf[oracle@henry~]$cp/u01/app/oracle/backup/*.dbf[oracle@henry~]$cp[oracle@henry~]$cp/u01/app/oracle/backup/con.bak[oracle@henry~]$sqlplus/assysdbaSQL>ORACLEinstanceTotalSystemGlobalArea FixedSize Variable Database RedoBuffers Databasemounted.ORA-01589:mustuseRESETLOGSorNORESETLOGSoptionfordatabaseopenSQL>colnamefora50SQL>selectfile#,checkpoint_change#,namefromv$datafile;SQL>selectfile#,checkpoint_change#fromv$datafile_header;SQL>recoverdatabaseusingbackupcontrolfile;ORA-00279:change generatedat09/01/201821:59:31neededforthread1ORA-00289:suggestion:/u01/app/oracle/arch/1_1_ ORA-00280: forthread1isinsequenceSpecifylog:{<RET>=suggested|filename|AUTO|ORA-00283:recoverysessioncanceledduetoerrorsORA-01244:unnameddatafile(s)addedtocontrolfilebymediarecoveryORA-01110:datafile12:u01/app/oracle/oradata/PROD1/newtest02.dbf'ORA-01112:mediarecoverynotSQL>alterdatabasecreatedatafile創(chuàng)建一個數(shù)據(jù)文件newtest02.dbfnewtest02.dbf。SQL>selectfile#,checkpoint_change#,namefromv$datafile;SQL>recoverdatabaseusingbackupORA-00279:change generatedat09/01/201822:03:07neededforthread1ORA-00289:suggestion:/u01/app/oracle/arch/1_1_ ORA-00280: forthread1isinsequenceSpecifylog:{<RET>=suggested|filename|AUTO|Logapplied.MediarecoverySQL>alterdatabaseopenresetlogs;SQL>select*fromnewtest2;2345第5技UNDO鏡像數(shù)據(jù)回溯或撤銷提交的操作、或是利用回收站恢復(fù)錯誤操作、再或者是利用FlashbackLogs將數(shù)據(jù)庫恢復(fù)到過去的某個時間點。理解回收站從管理的角度來說，Oracle為每個用戶（模式實際實際并不會開辟空間（只是個邏輯容器DropTable時（非Purge，原來的表所使用的段中的數(shù)據(jù)并沒有真正的刪除，而是把Table的段名以回收站方式重命名。該段所SQL>showparameter設(shè)置回收站為OFFSQL>altersystemsetrecyclebin=off;altersystemsetrecyclebin=off*ERRORatlineORA-02096:specifiedinitializationparameterisnotmodifiablewiththisSQL>selectname,isses_modifiable,issys_modifiablefromv$parameterwhereSQL>altersystemsetrecyclebin=offSQL>createtablespacetbs_flashdatafile'/u01/app/oracle/oradata/PROD1/flash01.dbf'size1m;SQL>createtabletbs_flash(idnumber)tablespacetbs_flash;SQL>insertintotbs_flashvalues(1);SQL>SQL>selectsegment_namefromdba_segmentswhereSQL>selectsum(bytes)fromdba__spacewheretablespace_name='TBS_FLASH';SQL>insertintotbs_flashselect*fromtbs_flash;SQL>/SQL>/SQL>/SQL>/SQL>/SQL>/SQL>/SQL>/SQL>insertintotbs_flashselect*from*ERRORatlineORA-01653:unabletoextendtableSCOTT.TBS_FLASHby8intablespaceSQL>selectsum(bytes)fromdba__spacewheretablespace_name='TBS_FLASH';SQL>selectcount(*)fromtbs_flash;SQL>droptableSQL>selectsum(bytes)fromdba__spacewheretablespace_name='TBS_FLASH';SQL>showrecyclebin;SQL>createtabletbs_flashbacktablespacetbs_flashasselect dba_objectswhererownum<2000;SQL>selectsum(bytes)fromdba__spacewheretablespace_name='TBS_FLASH';TBS_FLASH表數(shù)據(jù)已經(jīng)被沖掉了，使用閃回無法找回?；厥照鹃W回與清除假設(shè)回收站有3張表名一樣的表：SQL>createtableb(idnumber);SQL>insertintobvalues(1);SQL>SQL>droptableSQL>createtableb(idnumber);SQL>insertintobvalues(2);SQL>commit;SQL>droptableSQL>createtableb(idnumber);SQL>insertintobvalues(3);SQL>commit;SQL>droptableb;SQL>showrecyclebin;SQL>flashbacktablebtobeforeSQL>purgetable1B2的B3B表？SQL>flashbacktable"BIN$dPLDqpHV4WDgU4g7qMDO1Q==$0"tobeforedrop;SQL>purgetable"BIN$dPLDqpHS4WDgU4g7qMDO1Q==$0";SQL>purgetablea;SQL>purgeDROPA（a1A（a2又想把A（a1）閃回，這時候如果沒有重命名，會導(dǎo)致閃回失敗，因為對象已經(jīng)存在。SQL>createtableabc(idnumber);SQL>insertintoabcvalues(1);SQL>commit;SQL>droptableSQL>createtableabc(idnumber);SQL>insertintoabcvalues(2);SQL>commit;SQL>showrecyclebin;SQL>flashbacktableabctobeforedrop;flashbacktableabctobeforedrop*ERRORatlineORA-38312:originalnameisusedbyanexistingSQL>flashbacktableabctobeforedroprenametoabc_old;SQL>select*fromabc_old;回收站與SYSTEM表空間System表空間的對象沒有回收站，所以在sysSystem表空間時，DropTable會直接刪除對象。閃回DROP與其他對象SQL>createtabletbs_idx(idSQL>altertabletbs_idxaddconstraintpk_tprimarykey(id);SQL>insertintotbs_idxvalues(1);SQL>insertintotbs_idxvalues(2);SQL>SQL>select*fromSQL>selectindex_name,table_owner,table_namefromuser_indexes;SQL>selectowner,constraint_name,table_namefromuser_constraints;SQL>droptabletbs_idx;SQL>selectindex_name,table_owner,table_namefromuser_indexes;SQL>selectowner,constraint_name,table_namefromuser_constraints;SQL>flashbacktabletbs_idxtobeforeSQL>selectindex_name,table_owner,table_namefromuser_indexes;SQL>selectowner,constraint_name,table_namefromuser_constraints;SQL>alterindex"BIN$dPLDqpHY4WDgU4g7qMDO1Q==$0"renametopk_t;SQL>altertabletbs_idxrenameconstraint"BIN$dPLDqpHX4WDgU4g7qMDO1Q==$0"toSQL>selectindex_name,table_owner,table_namefromuser_indexes;SQL>selectowner,constraint_name,table_namefromuser_constraints;FLASHBACK交的undoblock（未被覆蓋點。通過設(shè)置undo_retention參數(shù)設(shè)置前鏡像的保留時間。select*fromaasofSQL>createtabletbs_query(idnumber);SQL>insertintotbs_queryvalues(1);SQL>insertintotbs_queryvalues(2);SQL>insertintotbs_queryvalues(3);SQL>commit;SQL>select*fromSQL>selectto_char(sysdate,'yyyy-mm-ddhh24:mi:ss')fromdual;SQL> fromv$database;SQL>deletefromtbs_querywhereid=1;SQL>commit;SQL>selectto_char(sysdate,'yyyy-mm-ddhh24:mi:ss')fromdual;SQL> fromv$database;SQL>updatetbs_querysetid=666whereid=2;SQL>commit;SQL>selectto_char(sysdate,'yyyy-mm-ddhh24:mi:ss')fromdual;SQL> fromv$database;SQL>select*fromtbs_queryasoftimestampto_date('2018-09-0315:53:28','yyyy-mm-ddhh24:mi:ss');SQL>select*fromtbs_queryasofscn SQL>select*fromtbs_queryasof SQL>createtabletbs_query1asselect*fromtbs_queryasof DML閃回表通常是把表的狀態(tài)回退到以前的某個時刻或者SCN（其實向前向后都能為什么使用行移動：這里的flashbacktable指的是從undo中閃回，試想這原來的位置被別的對象占用的話，他回不到原來的位置，所以要使用enablerow<SQL>altertabletbs_queryenablerowmovement;SQL>flashbacktabletbs_querytoscn SQL>select*fromtbs_query;SQL>flashbacktabletbs_querytotimestampto_date('2018-09-0315:53:28','yyyy-mm-ddhh24:mi:ss');SQL>select*fromSQL>flashbacktabletbs_queryto 閃回表有index是要重新被的。無限期的表行數(shù)據(jù)，通過進程FBDA，捕捉必要的數(shù)據(jù)并將其保存在歸檔同一表空間在技術(shù)上是可行的，但Oracle建議它們與常規(guī)的數(shù)據(jù)分開存放更好?？梢哉{(diào)整保留時間，一旦超過了保留期限，進程FDBA將自動刪除該表歷史記錄，也flashbackarchivealtertable<表名>flashbackarchive歸檔名閃回歸檔enable也有個前提條件，表空間要ASSM管理和以及SQL>createtablespaceflash_arcdatafile'/u01/app/oracle/oradata/PROD1/flasharc01.dbf'size5m;SQL>createflashbackarchiveflash_arctablespaceflash_arcquota2mretention1year;SQL>grantflashbackarchiveonflash_arctoscott;SQL>connSQL>createtabletestempasselect*fromemp;SQL>altertabletestempflashbackarchiveflash_arc;注意：11gR2版本可以在閃回歸檔表上修改列、定義列或是刪除列，也可以列，也無法刪除列，也不能DROP。SQL>droptabletestemp;droptabletestempERRORatline1:ORA-55610:InvalidDDLstatementonhistory-trackedtableSQL>truncatetabletestemp;SQL>select*fromdba_flashback_archive;SQL>select*fromSQL>select*fromSQL>select*fromtestempasoftimestampto_date('2018-09-0321:50:28','yyyy-mm-ddhh24:mi:ss')SQL>select*fromtestempasofSQL>select*fromSQL>select*fromSQL>select*fromSQL>altertabletestempnoflashback閃回歸檔的默認SQL>conn/asSQL>altertabletestempflashbackarchive;altertabletestempflashbackarchive*ERRORatlineORA-55608:DefaultFlashbackArchivedoesnotSQL>alterflashbackarchiveflash_arcsetSQL>altertabletestempflashback刪除閃回歸檔（方案SQL>dropflashbackarchiveAutomaticUndoManagentASSM語法：selectfromversions其中，select后面可以選擇偽列，來獲得事務(wù)的開始、結(jié)束時間、SCN號、ID號等。SQL>createtableflash_version(idnumber);SQL>insertintoflash_versionvalues(1);SQL>insertintoflash_versionvalues(2);SQL>insertintoflash_versionvalues(3);SQL>SQL>updateflash_versionsetid=666whereid=1;SQL>commit;SQL>deletefromflash_versionwhereid=2;SQL>commit;SQL>updateflash_versionsetid=333whereid=3;SQL>commit;SQL>select*fromSQL>select fromflash_versionversionsbetweenscnminvalueandmaxvalue;（臨時表的撤銷是基于session的。閃回事務(wù)查詢可以提供撤銷查詢語句flashback_transaction_query這個視圖里查詢引起數(shù)據(jù)變化的事務(wù)，和撤銷事務(wù)的SQL語句，也就是查詢operation和undo_sqlSQL>descSQL>selectundo_sqlfromflashback_transaction_querywhereSQL>update"SYS"."FLASH_VERSION"set"ID"='3'whereROWID=SQL>update"SYS"."FLASH_VERSION"set"ID"='1'whereROWID=SQL>SQL>select*fromflash_version;123閃回事務(wù)也可以通過EM來做：EM中查看操作：AvailabilityViewandManage一旦啟用了閃回數(shù)據(jù)庫，某些塊的映像會從dbbuffer到SGA的一個新的區(qū)域中，即閃回緩沖區(qū)，然后再由進程(RecoverWriteRVWR)將此閃回恢復(fù)區(qū)的內(nèi)容刷新到磁盤和閃回日志。這一切并沒有改變LGWR的常規(guī)作用。與重做日志不同的是RVWR不是記錄數(shù)據(jù)庫變化的日志，而是記錄完整塊影像的記錄（053Page398。閃回恢復(fù)區(qū)RMAN相關(guān)的三種自動管理的文件：歸檔日志、控制文件自動備份、RMAN備份片。當flashrecoveryarea空間不夠用，Oracle還可以自動清除一些廢棄（obsolete）文件。SQL>showparameterdb_recoverSQL>showparameterflash注意：設(shè)置db_recovery_file_dest之前必須先設(shè)置db_recovery_file_dest_size（先設(shè)置大小，再設(shè)置路徑。閃回日志存放的保留期，缺省值1440，單位是分鐘，也就是默認保留1天。配置閃回數(shù)據(jù)庫SQL>shutdownimmediate;SQL>startupmountexclusive;RecoverySQL>alterdatabase用歸檔日志或當前日志前滾一小段，當?shù)竭_指定的scn時停住。然后在此SCN前以Resetlogs方式打開數(shù)據(jù)庫。SQL>altersystemsetSQL>altersystemsetSQL>showparameterSQL>altersystemsetSQL>selectflashback_onfromv$database;SQL>alterdatabaseflashbackon;SQL>selectflashback_onfromAlterdatabaseflashbackon命令結(jié)果是在/u01/app/oracle/fast_recovery_area閃 下創(chuàng)建了一個以.flb結(jié)尾的閃回日志文件。databaseflashbackoff命令，則閃回日志及RVWR進程都會自動清除（考點。SQL>alterdatabase實驗：恢復(fù)被刪除的用戶取當前SCNSQL> fromSQL>createrestorepointabc[guranteeflashback[guranteeflashbackdatabase]abc點（SCN）以來的閃回日志一直存在，不受retention影響。SQL>dropuserscottcascade;SQL>shutdownimmediate;SQL>startupmountexclusive;SQL>flashbackdatabaseto SQL>flashbackdatabasetorestorepointabc;SQL>alterdatabaseopenreadonly;SQL>select*fromSQL>startup mountexclusive;SQL>flashbackdatabasetoscn ;SQL>alterdatabaseopenreadonly;SQL>select*fromscott.testemp;SQL> SQL>alterdatabaseopen閃回方上去了，如果不是你希望的，還可以重新閃回（前閃/SCN是閃回數(shù)據(jù)生成限制SQL>altertablespace<tablespace_name>flashbackoff;（可以在OPEN下操作）SQL>altertablespacetablespace_nameflashbackon;（MOUNT下操作）SQL>select*fromv$tablespace;SQL>selectts#,name,flashback_onfromv$tablespace;SQL>altertablespacenewtest2flashbackoff;SQL>altertablespacenewtest2flashbackoff;Tablespacealtered.SQL>altertablespacenewtest2flashbackon;altertablespacenewtest2flashbackon*ERRORatlineORA-01126:databasemustbemountedinthisinstanceandnotopeninany閃回數(shù)據(jù)庫視圖SQL>select*fromSQL>select*from以一個時間段為一行（大約1小時，記錄單位時間內(nèi)數(shù)據(jù)庫的活動量以供參考。 6RMAN概RMANServerProcess上，可以開多個通道（服務(wù)OracleServer上做備份和恢復(fù)。RMAN備份4類文件：datafile（分三個層次：Database、Tablespace、Datafile這個和我們之前講的手工恢復(fù)的三個層次剛好對應(yīng)、Controlfile、Spfile、機。非歸檔只支持一致性備份（MOUNT冷備）RMAN是不可用的。RMAN的備份與恢復(fù)只認自己的東西。非歸檔方式的RMAN恢復(fù)只能還原最后一次備份，沒有恢復(fù)的概念。RMANblockRMAN，Auxiliary庫，CatalogDatabase（ 設(shè)備：Disk、Tape（sbt磁帶機）MML：mediamanagelayer[oracle@henry~]$rmantargetRecoveryManager:Release.0-Producti