oracle恢復篇之基于用戶管理

用戶管理的備份與用戶管理的備份與恢復也稱OS(一)、NONARCHIVELOG模脫機冷備與恢復冷備份發(fā)生在數(shù)據(jù)庫已經(jīng)正常關(guān)閉的情況下，當正常關(guān)閉時會提供給一個完整的數(shù)據(jù)庫。冷備份是將關(guān)鍵性文件拷貝到另外位置的一種說法。對于備份Oracle信息而言，冷備份是最快和最安全的方法冷備份的優(yōu)點：是非?？焖俚膫浞莘椒ǎㄖ恍杩截愇募菀讱w檔（簡單拷貝即可容易恢復到某個時間點上（只需將文件再拷貝回去能與歸檔方法相結(jié)合，作數(shù)據(jù)庫“狀態(tài)”的恢復低度，高度安全冷備份的不足：單獨使用時，只能提供到“某一時間點上”的恢復在實施備份的全過程中，數(shù)據(jù)庫必須要作備份而不能作其它工作。也就是說，在冷備份過程中，數(shù)據(jù)庫必須是關(guān)閉狀態(tài)。若磁盤空間有限，只能拷貝到磁帶等其它外部設備上，速度會很慢不能按表或按用戶恢如果可能的話（主要看效率，應將信息備份到磁盤上，然后啟動數(shù)據(jù)庫（使用戶可以工作）并將所備份的信息拷貝到磁帶上（拷貝的同時，數(shù)據(jù)庫也可以工作。冷備份中必須拷貝的文件包括所有數(shù)據(jù)文所有控制文所有聯(lián)機REDOLOG文參數(shù)化參數(shù)Init.ora文件（可選(二)、ARCHIVELOG模脫機冷備與恢復同NONARCHIVELOG模聯(lián)機熱備聯(lián)機熱備是在數(shù)據(jù)庫運行的情況下進行備份的方法。熱備份要求數(shù)據(jù)庫在Archvelog方式下操作，并需要大量的空間。熱備份的優(yōu)點：可在表空間或數(shù)據(jù)文件級備份，備份時間短備份時數(shù)據(jù)庫仍可使用，支持 不間斷運行可達到秒級恢復（恢復到某一時間點上可對幾乎所有數(shù)據(jù)庫實體作恢復恢復是快速的，在大多數(shù)情況下在數(shù)據(jù)庫仍工作時恢熱備份的不足是：不能出錯，否則嚴重若熱備份不成功，所得結(jié)果不可用于時間點的恢復因難于，所以要特別仔細，不允許“以失敗而告終”注意：在熱備過程中系統(tǒng)會生成的重做日志和回滾數(shù)據(jù)。所以必須在數(shù)據(jù)庫較(一)、丟單個控制文件丟失這種情況解決方法很簡單，只要把多路復用的控制文件拷貝到相應路徑就行所有控制文件丟失重建控制文件（略使用備份的控制文件進行恢LastLastlogin:Sat 322:58:592013from[root@oracle~]#su-//修改環(huán)境變量的實例[oracle@oracle~]$export//連接到數(shù)據(jù)[oracle@oracle~]$sqlplus/asSQL*Plus:Release10.2.0.1.0-ProductiononSatAug323:01:58Copyright(c)1982,2005,Connecteds.OracleDatabase10gEnterpriseEditionRelease10.2.0.1.0-ProductionWiththePartitioning,OLAPandDataMiningoptions//備份控制文件（二進制SQL>alterdatabasebackupcontrolfiletoDatabasealtered.//刪除正在使用的控制文件（制造故障SQL>horm-rfTotalSystemGlobalArea444596224bytesFixedSize VariableSize 138412736bytesDatabaseBuffers 301989888bytesRedoBuffers //觀察錯誤信ORA-00205:errorinidentifyingcontrolfile,checkalertlogformore//從備份復原控制文SQL>hocp/u01/app/oracle/backup/emrep/ctl/2013-8-SQL>hocp/u01/app/oracle/backup/emrep/ctl/2013-8-SQL>hocp/u01/app/oracle/backup/emrep/ctl/2013-8-SQL>alterdatabasemount;Databasealtered.//打開數(shù)據(jù)SQL>alterdatabaseopen;alterdatabaseopen*ERRORatlineORA-01589:mustuseRESETLOGSorNORESETLOGSoptionfordatabase//要求RESETLOGSNORESETLOGS選項打開數(shù)據(jù)SQL>alterdatabaseopenresetlogs;alterdatabaseopenresetlogs*ERRORatlineORA-01194:file1needsmorerecoverytobeORA-01110:datafile1://需要進行介質(zhì)恢SQL>recoverdatabaseusingbackupORA-00279:change720303generatedat08/03/201322:55:38neededforthread1ORA-00289:suggestion:ORA-00280:change720303forthread1isinsequence#11//查詢可知當前的日志文件Specifylog:{<RET>=suggested|filename|AUTO|Logapplied.MediarecoverySQL>alterdatabaseopenresetlogs;Databasealtered.//及時進行全庫備修改數(shù)據(jù)庫結(jié)構(gòu)后丟失所有控制文件////備份控制文SQL>alterdatabasebackupcontrolfiletoDatabasealtered.//創(chuàng)建新的表空間（數(shù)據(jù)文件SQL>createtablespacet1datafile'/u01/app/oracle/oradata/emrep/t1.dbf'size10m;Tablespacecreated.//在新的表空間上創(chuàng)建表SQL>createtablea(idnumber)tablespacet1;Tablecreated.//刪除當前的控制文SQL>horm-rfTotalSystemGlobalArea444596224bytesFixedSize VariableSize 138412736bytesDatabaseBuffers 301989888bytesRedoBuffers //觀察錯誤信ORA-00205:errorinidentifyingcontrolfile,checkalertlogformore//還原控制文SQL>hocp/u01/app/oracle/backup/emrep/ctl/2013-8-SQL>hocp/u01/app/oracle/backup/emrep/ctl/2013-8-SQL>hocp/u01/app/oracle/backup/emrep/ctl/2013-8-//將數(shù)據(jù)庫置為mountDatabasealtered.//要求RESETLOGSNORESETLOGS選項打開數(shù)據(jù)alterdatabaseopen*ERRORatlineORA-01589:mustuseRESETLOGSorNORESETLOGSoptionfordatabase//RESETLOGS選項打開數(shù)據(jù)庫SQL>alterdatabaseopenresetlogs;alterdatabaseopenresetlogs*ERRORatlineORA-01194:file1needsmorerecoverytobeORA-01110:datafile1://使用備份的控制文件進行恢SQL>recoverdatabaseusingbackupORA-00279:change720553generatedat08/03/201323:05:27neededforthread1ORA-00289:suggestion:ORA-00280:change720553forthread1isinsequence#1//應 日Specifylog:{<RET>=suggested|filename|AUTO|ORA-00283:recoverysessioncanceleddueto//數(shù)據(jù)文件未命ORA-01244:unnameddatafile(s)addedtocontrolfilebymediarecoveryORA-01110:datafile5:'/u01/app/oracle/oradata/emrep/t1.dbf'//查詢數(shù)據(jù)文件信SQL>selectnamefromv$datafile;//更名數(shù)據(jù)文SQL>alterdatabasecreatedatafile//恢復數(shù)據(jù)SQL>recoverdatabaseusingbackupORA-00279:change721446generatedat08/03/201323:07:44neededforthread1ORA-00289:suggestion:ORA-00280:change721446forthread1isinsequence#1//a表是否存在SQLselect*froma;norowsselectedSQL>desca;Specifylog:{<RET>=suggested|filename|AUTO|Logapplied.Mediarecovery//RESETLOGS方式打開數(shù)據(jù)庫SQL>alterdatabaseopenresetlogs;Databasealtered.SQL>selectnamefrom修改數(shù)據(jù)庫后脫機或者只讀表空間，丟失所有控制文件//trace//trace備份控制文SQL>alterdatabasebackupcontrolfiletotracerasDatabasealtered.//創(chuàng)建表空間t2（數(shù)據(jù)文件SQL>createtablespacet2datafile'/u01/app/oracle/oradata/emrep/t2.dbf'size10m;Tablespacecreated.//在t2表空間上創(chuàng)建表SQL>createtableb(idnumber)tablespacet2;Tablecreated.//只讀表空SQL>altertablespacet2read//刪除當前的控制文SQL>horm-rfSQLstartupforce;TotalSystemGlobalArea444596224bytesFixedSize VariableSize 138412736bytesDatabaseBuffers 301989888bytesRedoBuffers ORA-00205:errorinidentifyingcontrolfile,checkalertlogformoreSQL@/u01/app/oracle/backup/emrep/ctl/2013-8-4/c.sqlControlfilecreated.//打開數(shù)據(jù)SQL>alterdatabaseopen;Databasealtered.//查詢數(shù)據(jù)文件信SQL>selectname,statusfrom 6rowsselected.SQL>alterdatabaserenamefile//SQL>selectname,statusfrom 6rowsselected.////將t2表空間置為onlineSQLaltertablespacet2online;Tablespacealtered.//將t2SQL>altertablespacet2readwrite;Tablespacealtered.SQL>select*from(二)、丟失日志文件或日志文件丟失日志文件增加一個新成員，刪除就成員即可丟失日志文件組//連接數(shù)據(jù)//連接數(shù)據(jù)[oracle@oracleemrep]$sqlplus/asSQL*Plus:Release10.2.0.1.0-ProductiononSunAug402:01:20Copyright(c)1982,2005,Connecteds.OracleDatabase10gEnterpriseEditionRelease10.2.0.1.0-ProductionWiththePartitioning,OLAPandDataMiningoptions//刪除日志文SQL>horm-rfTotalSystemGlobalArea444596224bytesFixedSize VariableSize 146801344bytesDatabaseBuffers 293601280bytesRedoBuffers Databasemounted.ORA-00313:openfailedformembersofloggroup2ofthreadORA-00312:onlinelog2thread1://查看是否為當前 1327 6 5 //SQL>alterdatabaseclearunarchivedlogfilegroup2;Databasealtered.(三)、丟丟失系統(tǒng)表空間//連接數(shù)據(jù)[oracle@oracleemrep]$sqlplus/asSQL*Plus:Release10.2.0.1.0-ProductiononSunAug402:01:20Copyright(c)1982,2005,Connecteds.OracleDatabase10gEnterpriseEditionRelease10.2.0.1.0-ProductionWiththePartitioning,OLAPandDataMiningoptions//查看歸檔模//關(guān)閉數(shù)據(jù)庫備份system表空間的數(shù)據(jù)文//在用戶表空間創(chuàng)建一//切換日志直至清除日SQL>altersystemswitch//刪除system表空間的數(shù)據(jù)文SQL>startupforce;//恢復數(shù)據(jù)SQL>recoverdatabaseuntilSpecifylog:{<RET>=suggested|filename|AUTO|CANCEL}//以最舊的SCN號打開數(shù)據(jù)SQL>altersystemset"_allow_resetlogs_corruption"=truescope=spfile;SQL>startupforce;SQL>alterdatabaseopenSQL>[oracle@oracledbs]$sqlplus/assysdbaSQL>startup;//將參數(shù)改回SQL>altersystemreset"_allow_resetlogs_corruption"scope=spfilesid='*';SQL>desctest;丟失非系統(tǒng)表空間////檢查環(huán)境變//連接到數(shù)據(jù)[oracle@oracleemrep]$sqlplus/asSQL*Plus:Release10.2.0.1.0-ProductiononSunAug400:51:26Copyright(c)1982,2005,Oracle.Connectedtoanidleinstance.SQLs.TotalSystemGlobalArea444596224bytesFixedSize VariableSize 142607040bytesDatabaseBuffers 297795584bytesRedoBuffers Databasemounted.SQL>createtablec(idnumber)tablespaceusers;Tablecreated.//system表上創(chuàng)建d表SQLcreatetabled(idnumber);Tablecreated.SQLaltersystemswitchlogfile;Systemaltered.SQL>Systemaltered.SQL>/System//刪除users表空間的數(shù)據(jù)文SQL>homvSQLstartupforce;TotalSystemGlobalArea444596224bytesFixedSize VariableSize 142607040bytesDatabaseBuffers 297795584bytesRedoBuffers Databasemounted.//觀察錯誤信ORA-01157:cannotidentify/lockdatafile4-seeDBWRtracefileORA-01110:datafile4:'/u01/app/oracle/oradata/emrep/users01.dbf'//還原數(shù)據(jù)文SQL>homv//打開數(shù)alterdatabaseopen*ERRORatlineORA-01113:file4needsmediaORA-01110:datafile4://刪除數(shù)據(jù)文OFFLINEDROP;//打開數(shù)Databasealtered.//查詢表SQL>selecttablespace_name,statusfromdba_tablespaces; 7rows

//查詢數(shù)據(jù)文件信 6rowsselected.//刪除表

CASCADECONSTRAINTS;*ERRORatlineORA-12919:CannotdropthedefaultpermanentSQL>alterdatabasedefaulttablespacet2;Databasealtered.//刪除users表空CASCADECONSTRAINTS;Lastlogin:SatAug323:01:472013fromLastlogin:SatAug323:01:472013from192.168.21.1[root@oracle~]#su-oracle//連接oracle數(shù)據(jù)[oracle@oracle~]$sqlplus asSQL*Plus:Release10.2.0.1.0-ProductiononSunAug401:27:12Copyright(c)1982,2005,Connecteds.OracleDatabase10gEnterpriseEditionRelease10.2.0.1.0-ProductionWiththePartitioning,OLAPandDataMiningoptions//熱備份系統(tǒng)表空SQL>altertablespacesystembeginbackup;Tablespacealtered.SQL>hocp/u01/app/oracle/backup/orcl/2013-8-//SQL>altertablespacesystemendbackup;Tablespacealtered.SQL>createtableeasselect*fromscott.emp;Tablecreated.SQL>altersystemswitchlogfile;Systemaltered.SQL>Systemaltered.SQL>/System//刪除系統(tǒng)表空間的數(shù)據(jù)文SQL>horm-rfTotalSystemGlobalArea444596224bytesFixedSize VariableSize 100664000bytesDatabase 339738624RedoBuffers Databasemounted.//觀察錯誤信ORA-01157:cannotidentify/lockdatafile1-seeDBWRtracefile//還原數(shù)據(jù)文SQLrecoverdatafile1;ORA-00279:change684745generatedat08/04/201301:28:21neededforthread1ORA-00289:suggestion:/u01/app/oracle/arch/orcl/1_24_816301482.dbfORA-00280:change684745forthread1isinsequenceSpecifylog:{<RET>=suggested|filename|AUTO|CANCEL}LogMediarecovery//打開數(shù)據(jù)Databasealtered.//檢查e表是否存SQL>selectcount(*)frome;Lastlogin:SatAug323:01:472013fromLastlogin:SatAug323:01:472013from192.168.21.1[root@oracle~]#su-oracle//連接oracle數(shù)據(jù)[oracle@oracle~]$sqlplus asSQL*Plus:Release10.2.0.1.0-ProductiononSunAug401:27:12Copyright(c)1982,2005,Connect