oracle恢復(fù)篇之基于用戶管理

用戶管理的備份與用戶管理的備份與恢復(fù)也稱OS(一)、NONARCHIVELOG模脫機(jī)冷備與恢復(fù)冷備份發(fā)生在數(shù)據(jù)庫(kù)已經(jīng)正常關(guān)閉的情況下，當(dāng)正常關(guān)閉時(shí)會(huì)提供給一個(gè)完整的數(shù)據(jù)庫(kù)。冷備份是將關(guān)鍵性文件拷貝到另外位置的一種說法。對(duì)于備份Oracle信息而言，冷備份是最快和最安全的方法冷備份的優(yōu)點(diǎn)：是非?？焖俚膫浞莘椒ǎㄖ恍杩截愇募菀讱w檔（簡(jiǎn)單拷貝即可容易恢復(fù)到某個(gè)時(shí)間點(diǎn)上（只需將文件再拷貝回去能與歸檔方法相結(jié)合，作數(shù)據(jù)庫(kù)“狀態(tài)”的恢復(fù)低度，高度安全冷備份的不足：?jiǎn)为?dú)使用時(shí)，只能提供到“某一時(shí)間點(diǎn)上”的恢復(fù)在實(shí)施備份的全過程中，數(shù)據(jù)庫(kù)必須要作備份而不能作其它工作。也就是說，在冷備份過程中，數(shù)據(jù)庫(kù)必須是關(guān)閉狀態(tài)。若磁盤空間有限，只能拷貝到磁帶等其它外部設(shè)備上，速度會(huì)很慢不能按表或按用戶恢如果可能的話（主要看效率，應(yīng)將信息備份到磁盤上，然后啟動(dòng)數(shù)據(jù)庫(kù)（使用戶可以工作）并將所備份的信息拷貝到磁帶上（拷貝的同時(shí)，數(shù)據(jù)庫(kù)也可以工作。冷備份中必須拷貝的文件包括所有數(shù)據(jù)文所有控制文所有聯(lián)機(jī)REDOLOG文參數(shù)化參數(shù)Init.ora文件（可選(二)、ARCHIVELOG模脫機(jī)冷備與恢復(fù)同NONARCHIVELOG模聯(lián)機(jī)熱備聯(lián)機(jī)熱備是在數(shù)據(jù)庫(kù)運(yùn)行的情況下進(jìn)行備份的方法。熱備份要求數(shù)據(jù)庫(kù)在Archvelog方式下操作，并需要大量的空間。熱備份的優(yōu)點(diǎn)：可在表空間或數(shù)據(jù)文件級(jí)備份，備份時(shí)間短備份時(shí)數(shù)據(jù)庫(kù)仍可使用，支持 不間斷運(yùn)行可達(dá)到秒級(jí)恢復(fù)（恢復(fù)到某一時(shí)間點(diǎn)上可對(duì)幾乎所有數(shù)據(jù)庫(kù)實(shí)體作恢復(fù)恢復(fù)是快速的，在大多數(shù)情況下在數(shù)據(jù)庫(kù)仍工作時(shí)恢熱備份的不足是：不能出錯(cuò)，否則嚴(yán)重若熱備份不成功，所得結(jié)果不可用于時(shí)間點(diǎn)的恢復(fù)因難于，所以要特別仔細(xì)，不允許“以失敗而告終”注意：在熱備過程中系統(tǒng)會(huì)生成的重做日志和回滾數(shù)據(jù)。所以必須在數(shù)據(jù)庫(kù)較(一)、丟單個(gè)控制文件丟失這種情況解決方法很簡(jiǎn)單，只要把多路復(fù)用的控制文件拷貝到相應(yīng)路徑就行所有控制文件丟失重建控制文件（略使用備份的控制文件進(jìn)行恢LastLastlogin:Sat 322:58:592013from[root@oracle~]#su-//修改環(huán)境變量的實(shí)例[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//備份控制文件（二進(jìn)制SQL>alterdatabasebackupcontrolfiletoDatabasealtered.//刪除正在使用的控制文件（制造故障SQL>horm-rfTotalSystemGlobalArea444596224bytesFixedSize VariableSize 138412736bytesDatabaseBuffers 301989888bytesRedoBuffers //觀察錯(cuò)誤信ORA-00205:errorinidentifyingcontrolfile,checkalertlogformore//從備份復(fù)原控制文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選項(xiàng)打開數(shù)據(jù)SQL>alterdatabaseopenresetlogs;alterdatabaseopenresetlogs*ERRORatlineORA-01194:file1needsmorerecoverytobeORA-01110:datafile1://需要進(jìn)行介質(zhì)恢SQL>recoverdatabaseusingbackupORA-00279:change720303generatedat08/03/201322:55:38neededforthread1ORA-00289:suggestion:ORA-00280:change720303forthread1isinsequence#11//查詢可知當(dāng)前的日志文件Specifylog:{<RET>=suggested|filename|AUTO|Logapplied.MediarecoverySQL>alterdatabaseopenresetlogs;Databasealtered.//及時(shí)進(jìn)行全庫(kù)備修改數(shù)據(jù)庫(kù)結(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.//刪除當(dāng)前的控制文SQL>horm-rfTotalSystemGlobalArea444596224bytesFixedSize VariableSize 138412736bytesDatabaseBuffers 301989888bytesRedoBuffers //觀察錯(cuò)誤信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ù)庫(kù)置為mountDatabasealtered.//要求RESETLOGSNORESETLOGS選項(xiàng)打開數(shù)據(jù)alterdatabaseopen*ERRORatlineORA-01589:mustuseRESETLOGSorNORESETLOGSoptionfordatabase//RESETLOGS選項(xiàng)打開數(shù)據(jù)庫(kù)SQL>alterdatabaseopenresetlogs;alterdatabaseopenresetlogs*ERRORatlineORA-01194:file1needsmorerecoverytobeORA-01110:datafile1://使用備份的控制文件進(jìn)行恢SQL>recoverdatabaseusingbackupORA-00279:change720553generatedat08/03/201323:05:27neededforthread1ORA-00289:suggestion:ORA-00280:change720553forthread1isinsequence#1//應(yīng) 日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//恢復(fù)數(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ù)庫(kù)SQL>alterdatabaseopenresetlogs;Databasealtered.SQL>selectnamefrom修改數(shù)據(jù)庫(kù)后脫機(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//刪除當(dāng)前的控制文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(二)、丟失日志文件或日志文件丟失日志文件增加一個(gè)新成員，刪除就成員即可丟失日志文件組//連接數(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://查看是否為當(dāng)前 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ù)庫(kù)備份system表空間的數(shù)據(jù)文//在用戶表空間創(chuàng)建一//切換日志直至清除日SQL>altersystemswitch//刪除system表空間的數(shù)據(jù)文SQL>startupforce;//恢復(fù)數(shù)據(jù)SQL>recoverdatabaseuntilSpecifylog:{<RET>=suggested|filename|AUTO|CANCEL}//以最舊的SCN號(hào)打開數(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.//觀察錯(cuò)誤信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.//觀察錯(cuò)誤信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