对于创建控制文件后missingnnnn处理

搜索BLOG

)SIZE50M,GROUP3(GROUP4(GROUP5(x

)SIZEGROUP6)SIZE200M devops360win下安装oracle真是太baojinzhe：rman我还不太熟啊，过来顶一下

CHARACTERSET;2删除file$中关于file#=9SQL>selectfile#,status$,ts#,relfile#fromfile$; 用好EclipseTask5265SQL5265SQLServer表和索 62567267827mysqlorderbylimitN929

9rows 友 SQL>deletefrom

1rowdeleted.SQL>commit;注意v$datafile中存在file#=9的文件信息。SQL>selectfile#,name,statusfromv$datafile;FILE#

SQL>shutdownimmediate;Databaseclosed.Databasedismounted.ORACLEinstanceshutdown.SQL>@/oracle/control.sqlORACLEinstancestarted.TotalSystemGlobal Fixed Variable Database Redo Controlfile打开数据库查看v$datafileSQL>recoverORA-00283:recoverysessioncanceledduetoerrorsORA-00264:norecoveryrequiredSQL>alterdatabaseDatabaseSQL>selectfile#,name,statusfromFILE# 8rows例二：测试以noresetlogs方式创建控制文件，产生missingnnnnSQL>selectfile#,status$,ts#,relfile#fromfile$; 2262322342445265625672678278rowsSQL>selectfile#,name,statusfromv$datafile;FILE#NAME /oracle/2//sysaux01.dbf /oracle/2//undotbs2.dbf 8rows准备重新创建控制文件的语句如下（注意以noresetlogs方式[oracle@oracle~]$catcontrol.sqlSTARTUPNOMOUNTCREATECONTROLFILEREUSEDATABASE""NORESETLOGSARCHIVELOGMAXLOGFILES16MAXLOGMEMBERSMAXDATAFILESMAXINSTANCESGROUP1)SIZE200M,GROUP2()SIZE50M,GROUP3()SIZE200M,GROUP4()SIZE200M,GROUP5()SIZE200M,GROUP6()SIZE200MCHARACTERSET;注意上面的语句中少了数据文件/oracle/2/zxb.dbf则重建控制文件，打开数据库会产生SQL>shutdownimmediate;Databaseclosed.Databasedismounted.ORACLEinstanceshutdown.SQL>@/oracle/control.sqlORACLEinstancestarted.TotalSystemGlobal Fixed Variable Database Redo Controlfile查看产生的missingnnnn文件注意状态为recoverSQL>recoverORA-00283:recoverysessioncanceledduetoerrorsORA-00264:norecoveryrequiredSQL>alterdatabaseDatabaseSQL>selectfile#,name,statusfromv$datafile;FILE#NAME /oracle/app/db1/dbs/MISSING000/oracle/2//sysaux01.dbf /oracle/2//undotbs2.dbf 8rowsSQL>altertablespacezxrenamedatafile'/oracle/app/db1/dbs/MISSING00002'to'/oracle/2/zxb.dbf';Tablespacealtered.SQL>selectfile#,name,statusfromFILE# /oracle/2//sysaux01.dbf /oracle/2//undotbs2.dbf 8rowsSQL>recoverdatafile2;MediarecoverySQL>selectfile#,name,statusfromv$datafile;FILE#NAME /oracle/2//sysaux01.dbf /oracle/2//undotbs2.dbf rowsSQL>alterdatabasedatafile2online;Databasealtered.SQL>selectfile#,name,statusfromv$datafile;FILE#NAME /oracle/2//sysaux01.dbf /oracle/2//undotbs2.dbf 8rows例三：测试以noresetlogs方式创建控制文件+正常离线数据文件，产生missingnnnnn[oracle@oracle~]$catcontrol.sqlSTARTUPNOMOUNTCREATECONTROLFILEREUSEDATABASE""NORESETLOGSARCHIVELOGMAXLOGFILES16MAXLOGMEMBERSMAXDATAFILESMAXINSTANCESGROUP1)SIZE200M,GROUP2()SIZE50M,GROUP3()SIZE200M,GROUP4()SIZE200M,GROUP5()SIZE200M,GROUP6()SIZE200MCHARACTERSET;以normal方式离线表空间zxSQL>altertablespacezxoffline;Tablespacealtered.SQL>selectfile#,name,statusfromv$datafile;FILE#NAME /oracle/2//sysaux01.dbf /oracle/2//undotbs2.dbf 8rowsSQL>shutdownimmediate;Databaseclosed.Databasedismounted.ORACLEinstanceshutdown.SQL>ORACLEinstanceTotalSystemGlobal Fixed Variable Database Redo Controlfile打开数据库查看产生的missingnnnnSQL>recoverORA-00283:recoverysessioncanceledduetoerrorsORA-00264:norecoveryrequiredSQL>alterdatabaseopen;Databasealtered.SQL>selectfile#,name,statusfromFILE# 8rowsSQL>altertablespacezxrenamedatafile23456TablespaceSQL>selectfile#,name,statusfromFILE# 8rowsSQL>altertablespacezxonline;Tablespacealtered.SQL>selectfile#,name,statusfromFILE# 8rows例四：测试以resetlogs方式创建控制文件+正常离线数据文件后产生missingnnnn重建控制文件的语句（注意以resetlogs方式[oracle@oracle~]$catcontrol.sqlSTARTUPNOMOUNTCREATECONTROLFILEREUSEDATABASE""RESETLOGSARCHIVELOGMAXLOGFILES16MAXLOGMEMBERSMAXDATAFILESMAXINSTANCESGROUP1)SIZE200M,GROUP2()SIZE50M,GROUP3()SIZE200M,GROUP4()SIZE200M,GROUP5()SIZE200M,GROUP6()SIZE200MCHARACTERSET;2SQL>altertablespacezxofflinenormal;Tablespacealtered.SQL>selectfile#,name,statusfromFILE# 8rows3开始重建控制文件如下：SQL>shutdownimmediate;Databaseclosed.Databasedismounted.ORACLEinstanceshutdown.SQL>@/oracle/control.sqlORACLEinstancestarted.TotalSystemGlobal Fixed Variable Database Redo Controlfile4查看产生的missingnnn文件注意状态是offline的SQL>recoverdatabase;ORA-00283:recoverysessioncanceledduetoORA-01610:recoveryusingtheBACKUPCONTROLFILEoptionmustbeSQL>recoverdatabaseusingbackupcontrolfileuntilORA-00279:change generatedat11/07/201200:18:06neededforthreadORA-00289:suggestion:/oracle/archive/1_134_ ORA-00280:change forthread1isinsequence#134Specifylog:{<RET>=suggested|filename|AUTO|CANCEL}MediarecoverySQL>alterdatabaseopenresetlogs;Databasealtered.SQL>colnameforSQL>selectfile#,name,statusfromFILE# 8rows5重命名missingSQL>altertablespacezxrenamedatafile TablespaceSQL>selectfile#,name,statusfromFILE# 8rowsSQL>altertablespacezxonline;Tablespacealtered.SQL>selectfile#,name,statusfromFILE# 8rows例五：测试以resetlogs方式创建控制文件产生missingnnnnSQL>selectfile#,status$,ts#,relfile#fromfile$; 1201226232234244526562567267 8rowsSQL>colnameforSQL>selectfile#,name,statusfromFILE# 8rows[oracle@oracle~]$catcontrol.sqlSTARTUPNOMOUNTCREATECONTROLFILEREUSEDATABASE""RESETLOGSARCHIVELOGMAXLOGFILES16MAXLOGMEMBERSMAXDATAFILESMAXINSTANCESGROUP1)SIZE200M,GROUP2()SIZE50M,GROUP3()SIZE200M,GROUP4()SIZE200M,GROUP5()SIZE200M,GROUP6()SIZE200MCHARACTERSET;开始重建控制文件SQL>shutdownimmediate;Databaseclosed.Databasedismounted.ORACLEinstanceshutdown.SQL>@/oracle/control.sqlORACLEinstancestarted.TotalSystemGlobal Fixed Variable Database Redo ControlfileSQL>recoverdatabase;ORA-00283:recoverysessioncanceledduetoORA-01610:recoveryusingtheBACKUPCONTROLFILEoptionmustbeSQL>recoverdatabaseusingbackupcontrolfileuntilORA-00279:change generatedat11/07/201200:35:11neededforthreadORA-00289:suggestion:/oracle/archive/1_1_ ORA-00280:change forthread1isinsequence#1Specifylog:{<RET>=suggested|filename|AUTO|CANCEL}MediarecoverySQL>alterdatabaseopenresetlogs;Databasealtered.SQL>selectfile#,name,statusfromFILE# 8rows重命名misssingnnnnnSQL>altertablespacezxrenamedatafile