我们很多不太重要的数据库（如测试库）都是工作在noarchive模式，下面我们就来谈谈，在noarchive模式下一些文件丢失的恢复：
关于控制文件的恢复，必须要有控制文件的备份（to trace备份或者to XX路径的备份），或者控制文件不是完全丢失——即存在至少一个控制文件。
条件：
1.shutdown immediate
2.控制文件全部丢失
3.redolog、数据文件正常
4.存在控制文件的备份（曾经备份的控制文件，或者backup to trace的控制文件，或者至少存在一个未丢失的控制文件）
情况1：控制文件完全丢失，但是redolog中的文件没有完全被覆盖：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 |
sys@ORALOCAL(192.168.0.12)> startup ORACLE instance started. Total System Global Area 139533192 bytes Fixed Size 453512 bytes Variable Size 109051904 bytes Database Buffers 29360128 bytes Redo Buffers 667648 bytes Database mounted. Database opened. sys@ORALOCAL(192.168.0.12)> select CHECKPOINT_CHANGE# from v$database; CHECKPOINT_CHANGE# ------------------ 5792350 Elapsed: 00:00:01.63 sys@ORALOCAL(192.168.0.12)> select distinct CHECKPOINT_CHANGE#,LAST_CHANGE# from v$datafile; CHECKPOINT_CHANGE# LAST_CHANGE# ------------------ ------------ 5792350 Elapsed: 00:00:05.32 sys@ORALOCAL(192.168.0.12)> select distinct CHECKPOINT_CHANGE# from v$datafile_header; CHECKPOINT_CHANGE# ------------------ 5792350 Elapsed: 00:00:01.72 sys@ORALOCAL(192.168.0.12)> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- 1 1 5 10485760 3 NO INACTIVE 5792175 30-OCT-07 2 1 6 10485760 3 NO CURRENT 5792177 30-OCT-07 3 1 4 10485760 3 NO INACTIVE 5792173 30-OCT-07 Elapsed: 00:00:00.92 sys@ORALOCAL(192.168.0.12)> --CHECKPOINT_CHANGE#:5792350 and FIRST_CHANGE#_CURRENT:5792177 sys@ORALOCAL(192.168.0.12)>--this mean last logfile change at scn 5792177,but system current scn is 5792350 sys@ORALOCAL(192.168.0.12)> sys@ORALOCAL(192.168.0.12)> sys@ORALOCAL(192.168.0.12)> sys@ORALOCAL(192.168.0.12)> sys@ORALOCAL(192.168.0.12)> alter database backup controlfile to 'd:\cfile.ctl'; Database altered. Elapsed: 00:00:01.49 sys@ORALOCAL(192.168.0.12)> alter database backup controlfile to trace as 'd:\cfile.txt'; Database altered. Elapsed: 00:00:01.47 sys@ORALOCAL(192.168.0.12)> select CHECKPOINT_CHANGE# from v$database; CHECKPOINT_CHANGE# ------------------ 5792350 Elapsed: 00:00:00.47 sys@ORALOCAL(192.168.0.12)> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- 1 1 5 10485760 3 NO INACTIVE 5792175 30-OCT-07 2 1 6 10485760 3 NO CURRENT 5792177 30-OCT-07 3 1 4 10485760 3 NO INACTIVE 5792173 30-OCT-07 Elapsed: 00:00:00.60 sys@ORALOCAL(192.168.0.12)> --this time ,we backup controlfile at checkpoint_change# 5792350, sys@ORALOCAL(192.168.0.12)> --and the log file is group 2 sys@ORALOCAL(192.168.0.12)> sys@ORALOCAL(192.168.0.12)> sys@ORALOCAL(192.168.0.12)> sys@ORALOCAL(192.168.0.12)> sys@ORALOCAL(192.168.0.12)> sys@ORALOCAL(192.168.0.12)> alter system checkpoint; System altered. Elapsed: 00:00:02.96 sys@ORALOCAL(192.168.0.12)> alter system switch logfile; System altered. Elapsed: 00:00:00.92 sys@ORALOCAL(192.168.0.12)> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- 1 1 5 10485760 3 NO INACTIVE 5792175 30-OCT-07 2 1 6 10485760 3 NO INACTIVE 5792177 30-OCT-07 3 1 7 10485760 3 NO CURRENT 5793848 30-OCT-07 Elapsed: 00:00:00.65 sys@ORALOCAL(192.168.0.12)> --this time logfile switch to group 3,but group 2 still not be resue, sys@ORALOCAL(192.168.0.12)> -- and we shutdown immediate database and remove all controlfile sys@ORALOCAL(192.168.0.12)> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. sys@ORALOCAL(192.168.0.12)> sys@ORALOCAL(192.168.0.12)> --remove all controlfile sys@ORALOCAL(192.168.0.12)> sys@ORALOCAL(192.168.0.12)> startup ORACLE instance started. Total System Global Area 139533192 bytes Fixed Size 453512 bytes Variable Size 109051904 bytes Database Buffers 29360128 bytes Redo Buffers 667648 bytes ORA-00205: error in identifying controlfile, check alert log for more info sys@ORALOCAL(192.168.0.12)> --cp d:\cfile.ctl d:\oracle\control_files\oralocal\CONTROL01.CTL sys@ORALOCAL(192.168.0.12)> --cp d:\cfile.ctl d:\oracle\control_files\oralocal\CONTROL02.CTL sys@ORALOCAL(192.168.0.12)> --cp d:\cfile.ctl d:\oracle\control_files\oralocal\CONTROL03.CTL sys@ORALOCAL(192.168.0.12)> --we cp the backup file'd:\cfile.ctl' to controlfile sys@ORALOCAL(192.168.0.12)> sys@ORALOCAL(192.168.0.12)> shutdown abort ORACLE instance shut down. sys@ORALOCAL(192.168.0.12)> startup ORACLE instance started. Total System Global Area 139533192 bytes Fixed Size 453512 bytes Variable Size 109051904 bytes Database Buffers 29360128 bytes Redo Buffers 667648 bytes Database mounted. ORA-01589: must use RESETLOGS or NORESETLOGS option for database open sys@ORALOCAL(192.168.0.12)> --database mounted but not open sys@ORALOCAL(192.168.0.12)> --check the scn sys@ORALOCAL(192.168.0.12)> select CHECKPOINT_CHANGE# from v$database; CHECKPOINT_CHANGE# ------------------ 5792350 Elapsed: 00:00:00.51 sys@ORALOCAL(192.168.0.12)> select distinct CHECKPOINT_CHANGE#,LAST_CHANGE# from v$datafile; CHECKPOINT_CHANGE# LAST_CHANGE# ------------------ ------------ 5792350 Elapsed: 00:00:02.64 sys@ORALOCAL(192.168.0.12)> select distinct CHECKPOINT_CHANGE# from v$datafile_header; CHECKPOINT_CHANGE# ------------------ 5794376 Elapsed: 00:00:03.49 sys@ORALOCAL(192.168.0.12)> sys@ORALOCAL(192.168.0.12)> --CHECKPOINT_CHANGE# <> LAST_CHANGE#, need media recover sys@ORALOCAL(192.168.0.12)> sys@ORALOCAL(192.168.0.12)> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- 1 1 5 10485760 3 NO INACTIVE 5792175 30-OCT-07 2 1 6 10485760 3 NO CURRENT 5792177 30-OCT-07 3 1 4 10485760 3 NO INACTIVE 5792173 30-OCT-07 Elapsed: 00:00:00.70 sys@ORALOCAL(192.168.0.12)> --v$datafile_header.CHECKPOINT_CHANGE# <> v$datafile.CHECKPOINT_CHANGE# too , sys@ORALOCAL(192.168.0.12)> --need instance recover sys@ORALOCAL(192.168.0.12)> --first to media recover sys@ORALOCAL(192.168.0.12)> sys@ORALOCAL(192.168.0.12)> select group#,member from v$logfile order by 1,2; GROUP# ---------- MEMBER ---------------------------------------------------------------------------------------------------- 1 D:\ORACLE\LOGFILES\ORALOCAL\REDO101.LOG 1 D:\ORACLE\LOGFILES\ORALOCAL\REDO102.LOG 1 D:\ORACLE\LOGFILES\ORALOCAL\REDO103.LOG 2 D:\ORACLE\LOGFILES\ORALOCAL\REDO201.LOG 2 D:\ORACLE\LOGFILES\ORALOCAL\REDO202.LOG 2 D:\ORACLE\LOGFILES\ORALOCAL\REDO203.LOG 3 D:\ORACLE\LOGFILES\ORALOCAL\REDO301.LOG 3 D:\ORACLE\LOGFILES\ORALOCAL\REDO302.LOG 3 D:\ORACLE\LOGFILES\ORALOCAL\REDO303.LOG 9 rows selected. Elapsed: 00:00:04.55 sys@ORALOCAL(192.168.0.12)> recover database until cancel; ORA-00283: recovery session canceled due to errors ORA-01610: recovery using the BACKUP CONTROLFILE option must be done sys@ORALOCAL(192.168.0.12)> recover database using backup controlfile until cancel; ORA-00279: change 5792350 generated at 10/30/2007 00:20:11 needed for thread 1 ORA-00289: suggestion : D:\ORACLE\ARCH\ORALOCAL\ORALOCAL_001_00006.ARC ORA-00280: change 5792350 for thread 1 is in sequence #6 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} D:\ORACLE\LOGFILES\ORALOCAL\REDO301.LOG ORA-00326: log begins at change 5793848, need earlier change 5792350 ORA-00334: archived log: 'D:\ORACLE\LOGFILES\ORALOCAL\REDO301.LOG' ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below ORA-01152: file 1 was not restored from a sufficiently old backup ORA-01110: data file 1: 'D:\ORACLE\ORADATA\ORALOCAL\SYSTEM01.DBF' sys@ORALOCAL(192.168.0.12)> sys@ORALOCAL(192.168.0.12)> sys@ORALOCAL(192.168.0.12)> recover database using backup controlfile until cancel; ORA-00279: change 5792350 generated at 10/30/2007 00:20:11 needed for thread 1 ORA-00289: suggestion : D:\ORACLE\ARCH\ORALOCAL\ORALOCAL_001_00006.ARC ORA-00280: change 5792350 for thread 1 is in sequence #6 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} D:\ORACLE\LOGFILES\ORALOCAL\REDO101.LOG ORA-00328: archived log ends at change 5792176, need later change 5792350 ORA-00334: archived log: 'D:\ORACLE\LOGFILES\ORALOCAL\REDO101.LOG' ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below ORA-01152: file 1 was not restored from a sufficiently old backup ORA-01110: data file 1: 'D:\ORACLE\ORADATA\ORALOCAL\SYSTEM01.DBF' sys@ORALOCAL(192.168.0.12)> sys@ORALOCAL(192.168.0.12)> sys@ORALOCAL(192.168.0.12)> recover database using backup controlfile until cancel; ORA-00279: change 5792350 generated at 10/30/2007 00:20:11 needed for thread 1 ORA-00289: suggestion : D:\ORACLE\ARCH\ORALOCAL\ORALOCAL_001_00006.ARC ORA-00280: change 5792350 for thread 1 is in sequence #6 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} D:\ORACLE\LOGFILES\ORALOCAL\REDO201.LOG ORA-00279: change 5793848 generated at 10/30/2007 00:30:33 needed for thread 1 ORA-00289: suggestion : D:\ORACLE\ARCH\ORALOCAL\ORALOCAL_001_00007.ARC ORA-00280: change 5793848 for thread 1 is in sequence #7 ORA-00278: log file 'D:\ORACLE\LOGFILES\ORALOCAL\REDO201.LOG' no longer needed for this recovery Specify log: {<RET>=suggested | filename | AUTO | CANCEL} D:\ORACLE\LOGFILES\ORALOCAL\REDO301.LOG Log applied. Media recovery complete. sys@ORALOCAL(192.168.0.12)> sys@ORALOCAL(192.168.0.12)> sys@ORALOCAL(192.168.0.12)> sys@ORALOCAL(192.168.0.12)> --media recover finish here sys@ORALOCAL(192.168.0.12)> --check if need instance recover sys@ORALOCAL(192.168.0.12)> select CHECKPOINT_CHANGE# from v$database; CHECKPOINT_CHANGE# ------------------ 5792350 Elapsed: 00:00:00.49 sys@ORALOCAL(192.168.0.12)> select distinct CHECKPOINT_CHANGE#,LAST_CHANGE# from v$datafile; CHECKPOINT_CHANGE# LAST_CHANGE# ------------------ ------------ 5792350 5794375 Elapsed: 00:00:02.51 sys@ORALOCAL(192.168.0.12)> select distinct CHECKPOINT_CHANGE# from v$datafile_header; CHECKPOINT_CHANGE# ------------------ 5794376 Elapsed: 00:00:03.72 sys@ORALOCAL(192.168.0.12)> sys@ORALOCAL(192.168.0.12)> sys@ORALOCAL(192.168.0.12)> select CHECKPOINT_CHANGE# from v$database; CHECKPOINT_CHANGE# ------------------ 5792350 Elapsed: 00:00:00.47 sys@ORALOCAL(192.168.0.12)> select distinct CHECKPOINT_CHANGE#,LAST_CHANGE# from v$datafile; CHECKPOINT_CHANGE# LAST_CHANGE# ------------------ ------------ 5792350 5794375 Elapsed: 00:00:02.56 sys@ORALOCAL(192.168.0.12)> select distinct CHECKPOINT_CHANGE# from v$datafile_header; CHECKPOINT_CHANGE# ------------------ 5794376 Elapsed: 00:00:03.83 sys@ORALOCAL(192.168.0.12)> alter database open resetlogs; Database altered. Elapsed: 00:01:01.34 sys@ORALOCAL(192.168.0.12)> sys@ORALOCAL(192.168.0.12)> sys@ORALOCAL(192.168.0.12)> sys@ORALOCAL(192.168.0.12)> sys@ORALOCAL(192.168.0.12)> sys@ORALOCAL(192.168.0.12)> sys@ORALOCAL(192.168.0.12)> sys@ORALOCAL(192.168.0.12)> --check scn again sys@ORALOCAL(192.168.0.12)> select CHECKPOINT_CHANGE# from v$database; CHECKPOINT_CHANGE# ------------------ 5794377 Elapsed: 00:00:00.57 sys@ORALOCAL(192.168.0.12)> select distinct CHECKPOINT_CHANGE#,LAST_CHANGE# from v$datafile; CHECKPOINT_CHANGE# LAST_CHANGE# ------------------ ------------ 5794377 Elapsed: 00:00:05.70 sys@ORALOCAL(192.168.0.12)> select distinct CHECKPOINT_CHANGE# from v$datafile_header; CHECKPOINT_CHANGE# ------------------ 5794377 Elapsed: 00:00:01.80 sys@ORALOCAL(192.168.0.12)> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- 1 1 0 10485760 3 YES UNUSED 0 2 1 1 10485760 3 NO CURRENT 5794376 30-OCT-07 3 1 0 10485760 3 YES UNUSED 0 Elapsed: 00:00:00.79 sys@ORALOCAL(192.168.0.12)> |
情况2：控制文件完全丢失，但是redolog中的文件已经被完全被覆盖：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 |
sys@ORALOCAL(192.168.0.12)> alter database backup controlfile to 'd:\cfile.ctl'; Database altered. Elapsed: 00:00:01.01 sys@ORALOCAL(192.168.0.12)> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- 1 1 5 10485760 3 NO CURRENT 5795700 30-OCT-07 2 1 4 10485760 3 NO INACTIVE 5795630 30-OCT-07 3 1 3 10485760 3 NO INACTIVE 5795627 30-OCT-07 Elapsed: 00:00:00.09 sys@ORALOCAL(192.168.0.12)> alter system switch logfile; System altered. Elapsed: 00:00:00.86 sys@ORALOCAL(192.168.0.12)> / System altered. Elapsed: 00:00:00.92 sys@ORALOCAL(192.168.0.12)> / System altered. Elapsed: 00:00:06.14 sys@ORALOCAL(192.168.0.12)> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- 1 1 8 10485760 3 NO CURRENT 5795958 30-OCT-07 2 1 7 10485760 3 NO INACTIVE 5795954 30-OCT-07 3 1 6 10485760 3 NO INACTIVE 5795952 30-OCT-07 Elapsed: 00:00:00.69 sys@ORALOCAL(192.168.0.12)> alter system switch logfile; System altered. Elapsed: 00:00:00.93 sys@ORALOCAL(192.168.0.12)> / System altered. Elapsed: 00:00:01.11 sys@ORALOCAL(192.168.0.12)> / System altered. Elapsed: 00:00:00.94 sys@ORALOCAL(192.168.0.12)> / System altered. Elapsed: 00:00:06.62 sys@ORALOCAL(192.168.0.12)> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. sys@ORALOCAL(192.168.0.12)> sys@ORALOCAL(192.168.0.12)> sys@ORALOCAL(192.168.0.12)> sys@ORALOCAL(192.168.0.12)> startup ORACLE instance started. Total System Global Area 139533192 bytes Fixed Size 453512 bytes Variable Size 109051904 bytes Database Buffers 29360128 bytes Redo Buffers 667648 bytes ORA-00205: error in identifying controlfile, check alert log for more info sys@ORALOCAL(192.168.0.12)> sys@ORALOCAL(192.168.0.12)> sys@ORALOCAL(192.168.0.12)> alter database open; alter database open * ERROR at line 1: ORA-01507: database not mounted Elapsed: 00:00:00.16 sys@ORALOCAL(192.168.0.12)> alter database mount; Database altered. Elapsed: 00:00:04.76 sys@ORALOCAL(192.168.0.12)> recover database until cancel; ORA-00283: recovery session canceled due to errors ORA-01610: recovery using the BACKUP CONTROLFILE option must be done sys@ORALOCAL(192.168.0.12)> recover database using backup controlfile until cancel; ORA-00279: change 5795703 generated at 10/30/2007 01:00:11 needed for thread 1 ORA-00289: suggestion : D:\ORACLE\ARCH\ORALOCAL\ORALOCAL_001_00005.ARC ORA-00280: change 5795703 for thread 1 is in sequence #5 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} cancel ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below ORA-01152: file 1 was not restored from a sufficiently old backup ORA-01110: data file 1: 'D:\ORACLE\ORADATA\ORALOCAL\SYSTEM01.DBF' ORA-01112: media recovery not started sys@ORALOCAL(192.168.0.12)> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-01152: file 1 was not restored from a sufficiently old backup ORA-01110: data file 1: 'D:\ORACLE\ORADATA\ORALOCAL\SYSTEM01.DBF' Elapsed: 00:00:02.27 sys@ORALOCAL(192.168.0.12)> recover database using backup controlfile until cancel; ORA-00279: change 5795703 generated at 10/30/2007 01:00:11 needed for thread 1 ORA-00289: suggestion : D:\ORACLE\ARCH\ORALOCAL\ORALOCAL_001_00005.ARC ORA-00280: change 5795703 for thread 1 is in sequence #5 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} cancel ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below ORA-01152: file 1 was not restored from a sufficiently old backup ORA-01110: data file 1: 'D:\ORACLE\ORADATA\ORALOCAL\SYSTEM01.DBF' ORA-01112: media recovery not started sys@ORALOCAL(192.168.0.12)> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- 1 1 5 10485760 3 NO CURRENT 5795700 30-OCT-07 2 1 4 10485760 3 NO INACTIVE 5795630 30-OCT-07 3 1 3 10485760 3 NO INACTIVE 5795627 30-OCT-07 Elapsed: 00:00:00.71 sys@ORALOCAL(192.168.0.12)> select member from v$logfile 2 / MEMBER ---------------------------------------------------------------------------------------------------- D:\ORACLE\LOGFILES\ORALOCAL\REDO301.LOG D:\ORACLE\LOGFILES\ORALOCAL\REDO201.LOG D:\ORACLE\LOGFILES\ORALOCAL\REDO101.LOG D:\ORACLE\LOGFILES\ORALOCAL\REDO102.LOG D:\ORACLE\LOGFILES\ORALOCAL\REDO103.LOG D:\ORACLE\LOGFILES\ORALOCAL\REDO202.LOG D:\ORACLE\LOGFILES\ORALOCAL\REDO203.LOG D:\ORACLE\LOGFILES\ORALOCAL\REDO302.LOG D:\ORACLE\LOGFILES\ORALOCAL\REDO303.LOG 9 rows selected. Elapsed: 00:00:02.70 sys@ORALOCAL(192.168.0.12)> recover database using backup controlfile until cancel; ORA-00279: change 5795703 generated at 10/30/2007 01:00:11 needed for thread 1 ORA-00289: suggestion : D:\ORACLE\ARCH\ORALOCAL\ORALOCAL_001_00005.ARC ORA-00280: change 5795703 for thread 1 is in sequence #5 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} D:\ORACLE\LOGFILES\ORALOCAL\REDO301.LOG ORA-00326: log begins at change 5796041, need earlier change 5795703 ORA-00334: archived log: 'D:\ORACLE\LOGFILES\ORALOCAL\REDO301.LOG' ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below ORA-01152: file 1 was not restored from a sufficiently old backup ORA-01110: data file 1: 'D:\ORACLE\ORADATA\ORALOCAL\SYSTEM01.DBF' sys@ORALOCAL(192.168.0.12)> recover database using backup controlfile until cancel; ORA-00279: change 5795703 generated at 10/30/2007 01:00:11 needed for thread 1 ORA-00289: suggestion : D:\ORACLE\ARCH\ORALOCAL\ORALOCAL_001_00005.ARC ORA-00280: change 5795703 for thread 1 is in sequence #5 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} D:\ORACLE\LOGFILES\ORALOCAL\REDO201.LOG ORA-00326: log begins at change 5796035, need earlier change 5795703 ORA-00334: archived log: 'D:\ORACLE\LOGFILES\ORALOCAL\REDO201.LOG' ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below ORA-01152: file 1 was not restored from a sufficiently old backup ORA-01110: data file 1: 'D:\ORACLE\ORADATA\ORALOCAL\SYSTEM01.DBF' sys@ORALOCAL(192.168.0.12)> recover database using backup controlfile until cancel; ORA-00279: change 5795703 generated at 10/30/2007 01:00:11 needed for thread 1 ORA-00289: suggestion : D:\ORACLE\ARCH\ORALOCAL\ORALOCAL_001_00005.ARC ORA-00280: change 5795703 for thread 1 is in sequence #5 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} D:\ORACLE\LOGFILES\ORALOCAL\REDO101.LOG ORA-00326: log begins at change 5796037, need earlier change 5795703 ORA-00334: archived log: 'D:\ORACLE\LOGFILES\ORALOCAL\REDO101.LOG' ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below ORA-01152: file 1 was not restored from a sufficiently old backup ORA-01110: data file 1: 'D:\ORACLE\ORADATA\ORALOCAL\SYSTEM01.DBF' sys@ORALOCAL(192.168.0.12)> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-01152: file 1 was not restored from a sufficiently old backup ORA-01110: data file 1: 'D:\ORACLE\ORADATA\ORALOCAL\SYSTEM01.DBF' Elapsed: 00:00:02.47 sys@ORALOCAL(192.168.0.12)> sys@ORALOCAL(192.168.0.12)> sys@ORALOCAL(192.168.0.12)> sys@ORALOCAL(192.168.0.12)> sys@ORALOCAL(192.168.0.12)> shutdown abort ORACLE instance shut down. sys@ORALOCAL(192.168.0.12)> startup nomount; ORACLE instance started. Total System Global Area 139533192 bytes Fixed Size 453512 bytes Variable Size 109051904 bytes Database Buffers 29360128 bytes Redo Buffers 667648 bytes sys@ORALOCAL(192.168.0.12)> CREATE CONTROLFILE REUSE DATABASE "ORALOCAL" NORESETLOGS NOARCHIVELOG 2 MAXLOGFILES 50 3 MAXLOGMEMBERS 5 4 MAXDATAFILES 100 5 MAXINSTANCES 1 6 MAXLOGHISTORY 226 7 LOGFILE 8 GROUP 1 ( 9 'D:\ORACLE\LOGFILES\ORALOCAL\REDO101.LOG', 10 'D:\ORACLE\LOGFILES\ORALOCAL\REDO102.LOG', 11 'D:\ORACLE\LOGFILES\ORALOCAL\REDO103.LOG' 12 ) SIZE 10M, 13 GROUP 2 ( 14 'D:\ORACLE\LOGFILES\ORALOCAL\REDO201.LOG', 15 'D:\ORACLE\LOGFILES\ORALOCAL\REDO202.LOG', 16 'D:\ORACLE\LOGFILES\ORALOCAL\REDO203.LOG' 17 ) SIZE 10M, 18 GROUP 3 ( 19 'D:\ORACLE\LOGFILES\ORALOCAL\REDO301.LOG', 20 'D:\ORACLE\LOGFILES\ORALOCAL\REDO302.LOG', 21 'D:\ORACLE\LOGFILES\ORALOCAL\REDO303.LOG' 22 ) SIZE 10M 23 DATAFILE 24 'D:\ORACLE\ORADATA\ORALOCAL\SYSTEM01.DBF', 25 'D:\ORACLE\ORADATA\ORALOCAL\UNDOTBS01.DBF', 26 'D:\ORACLE\ORADATA\ORALOCAL\CWMLITE01.DBF', 27 'D:\ORACLE\ORADATA\ORALOCAL\DRSYS01.DBF', 28 'D:\ORACLE\ORADATA\ORALOCAL\EXAMPLE01.DBF', 29 'D:\ORACLE\ORADATA\ORALOCAL\INDX01.DBF', 30 'D:\ORACLE\ORADATA\ORALOCAL\ODM01.DBF', 31 'D:\ORACLE\ORADATA\ORALOCAL\TOOLS01.DBF', 32 'D:\ORACLE\ORADATA\ORALOCAL\USERS01.DBF', 33 'D:\ORACLE\ORADATA\ORALOCAL\XDB01.DBF', 34 'D:\ORACLE\ORADATA\ORALOCAL\TS_INDEX_SUBN08.DBF', 35 'D:\ORACLE\ORADATA\ORALOCAL\TS_INDEX_SUBN06.DBF', 36 'D:\ORACLE\ORADATA\ORALOCAL\TS_INDEX_SUBN05.DBF', 37 'D:\ORACLE\ORADATA\ORALOCAL\TS_INDEX_RPT07.DBF', 38 'D:\ORACLE\ORADATA\ORALOCAL\TS_INDEX_RPT06.DBF', 39 'D:\ORACLE\ORADATA\ORALOCAL\TS_INDEX_RPT04.DBF', 40 'D:\ORACLE\ORADATA\ORALOCAL\TS_INDEX_RPT03.DBF', 41 'D:\ORACLE\ORADATA\ORALOCAL\TS_INDEX_RPT02.DBF', 42 'D:\ORACLE\ORADATA\ORALOCAL\TS_INDEX_RPT01.DBF', 43 'D:\ORACLE\ORADATA\ORALOCAL\TS_INDEX_SUBR04.DBF', 44 'D:\ORACLE\ORADATA\ORALOCAL\TS_INDEX_SUBR03.DBF', 45 'D:\ORACLE\ORADATA\ORALOCAL\TS_INDEX_SUBR02.DBF', 46 'D:\ORACLE\ORADATA\ORALOCAL\TS_INDEX_SUBN04.DBF', 47 'D:\ORACLE\ORADATA\ORALOCAL\TS_INDEX_SUBN03.DBF', 48 'D:\ORACLE\ORADATA\ORALOCAL\TS_INDEX_STATIC.DBF', 49 'D:\ORACLE\ORADATA\ORALOCAL\TS_DATA_SUBR04.DBF', 50 'D:\ORACLE\ORADATA\ORALOCAL\TS_INDEX_SUBR01.DBF', 51 'D:\ORACLE\ORADATA\ORALOCAL\TS_INDEX_SUBN07.DBF', 52 'D:\ORACLE\ORADATA\ORALOCAL\TS_INDEX_CDR.DBF', 53 'D:\ORACLE\ORADATA\ORALOCAL\TS_INDEX_ADMIN.DBF', 54 'D:\ORACLE\ORADATA\ORALOCAL\TS_DATA_HIS02.DBF', 55 'D:\ORACLE\ORADATA\ORALOCAL\TS_INDEX_SUBN02.DBF', 56 'D:\ORACLE\ORADATA\ORALOCAL\TS_INDEX_SUBN01.DBF', 57 'D:\ORACLE\ORADATA\ORALOCAL\TS_INDEX_RPT08.DBF', 58 'D:\ORACLE\ORADATA\ORALOCAL\TS_INDEX_RPT05.DBF', 59 'D:\ORACLE\ORADATA\ORALOCAL\TS_DATA_SUBR03.DBF', 60 'D:\ORACLE\ORADATA\ORALOCAL\TS_DATA_SUBR02.DBF', 61 'D:\ORACLE\ORADATA\ORALOCAL\TS_DATA_SUBR01.DBF', 62 'D:\ORACLE\ORADATA\ORALOCAL\TS_DATA_SUBN08.DBF', 63 'D:\ORACLE\ORADATA\ORALOCAL\TS_DATA_SUBN07.DBF', 64 'D:\ORACLE\ORADATA\ORALOCAL\TS_DATA_SUBN06.DBF', 65 'D:\ORACLE\ORADATA\ORALOCAL\TS_DATA_SUBN05.DBF', 66 'D:\ORACLE\ORADATA\ORALOCAL\TS_DATA_SUBN04.DBF', 67 'D:\ORACLE\ORADATA\ORALOCAL\TS_DATA_SUBN03.DBF', 68 'D:\ORACLE\ORADATA\ORALOCAL\TS_DATA_SUBN02.DBF', 69 'D:\ORACLE\ORADATA\ORALOCAL\TS_DATA_SUBN01.DBF', 70 'D:\ORACLE\ORADATA\ORALOCAL\TS_DATA_STATIC.DBF', 71 'D:\ORACLE\ORADATA\ORALOCAL\TS_DATA_RPT08.DBF', 72 'D:\ORACLE\ORADATA\ORALOCAL\TS_DATA_RPT07.DBF', 73 'D:\ORACLE\ORADATA\ORALOCAL\TS_DATA_RPT06.DBF', 74 'D:\ORACLE\ORADATA\ORALOCAL\TS_DATA_RPT05.DBF', 75 'D:\ORACLE\ORADATA\ORALOCAL\TS_DATA_RPT04.DBF', 76 'D:\ORACLE\ORADATA\ORALOCAL\TS_DATA_RPT03.DBF', 77 'D:\ORACLE\ORADATA\ORALOCAL\TS_DATA_RPT02.DBF', 78 'D:\ORACLE\ORADATA\ORALOCAL\TS_DATA_RPT01.DBF', 79 'D:\ORACLE\ORADATA\ORALOCAL\TS_DATA_RPT.DBF', 80 'D:\ORACLE\ORADATA\ORALOCAL\TS_DATA_PORTAL.DBF', 81 'D:\ORACLE\ORADATA\ORALOCAL\TS_DATA_HISTORY.DBF', 82 'D:\ORACLE\ORADATA\ORALOCAL\TS_DATA_HIS01.DBF', 83 'D:\ORACLE\ORADATA\ORALOCAL\TS_DATA_DYNAMIC.DBF', 84 'D:\ORACLE\ORADATA\ORALOCAL\TS_DATA_CDR.DBF', 85 'D:\ORACLE\ORADATA\ORALOCAL\TS_DATA_ADMIN.DBF', 86 'D:\ORACLE\ORADATA\ORALOCAL\TS_INDEX_HISTORY.DBF', 87 'D:\ORACLE\ORADATA\ORALOCAL\TS_INDEX_DYNAMIC.DBF', 88 'D:\ORACLE\ORADATA\ORALOCAL\TS_HEJIANMIN_DEFAULT.DBF', 89 'D:\ORACLE\ORADATA\ORALOCAL\TS_DD.DBF' 90 CHARACTER SET ZHS16GBK 91 ; Control file created. Elapsed: 00:00:12.07 sys@ORALOCAL(192.168.0.12)> alter database mount; alter database mount * ERROR at line 1: ORA-01100: database already mounted Elapsed: 00:00:00.58 sys@ORALOCAL(192.168.0.12)> alter database open; Database altered. Elapsed: 00:00:30.82 sys@ORALOCAL(192.168.0.12)> sys@ORALOCAL(192.168.0.12)> |
总结：在noarchive模式，控制文件丢失的情况下，如果redolog没有完全被覆盖（即替换的或者剩余的控制文件的信息仍在其中一个redolog中），可以用redolog中的信息做介质恢复到当前，不会有数据丢失。
如果redolog完全被覆盖（即替换的文件或者剩余的控制文件的信息已经不在redolog中，信息已经被覆盖，redolog被重复使用了），此时要重建控制文件。