之前看文章，说建议数据库用rman备份的话，最好采用catalog数据库，将备份信息保留在catalog库中。对于没有catalog的数据库rman备份，备份信息将保留在控制文件中。
因此如果控制文件丢失，还原起来就比较麻烦，需要用dbms_backup_restore来进行恢复（恢复方式见文章的附录），其实想想，dbms_backup_restore其实也没那么常用了。因为：
|
1 2 3 4 5 6 7 8 9 |
1、控制文件如果有自动备份，那么可以从自动备份还原。 2、如果有单独备份控制文件，那么可以从这个单独备份的backupset处还原。 3、如果之前有做数据库全备，全备中就包含了控制文件和spfile的备份，可以从全备中还原。 4、另外还可以从控制文件的snapshot还原。 5、从上面的四种方法都无法还原控制文件，那么只能从备份集restore数据文件以及arch，然后重建控制文件，再resetlogs打开数据库。 |
====================================
附录（在控制文件都无法恢复的情况，用dbms_backup_restore restore数据文件，再重建控制文件，在open resetlogs）：
|
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 |
SQL> l 1* select count(*) from t1 SQL> / COUNT(*) ---------- 1024 SQL> SQL> SQL> SQL> insert into t1 select * from t1; 1024 rows created. SQL> / 2048 rows created. SQL> / 4096 rows created. SQL> select count(*) from t1; COUNT(*) ---------- 8192 SQL> SQL>--commit前数据为1024行，insert到8192行，但是未commit SQL> host Microsoft Windows XP [Version 5.1.2600] (C) Copyright 1985-2001 Microsoft Corp. C:\Documents and Settings\Administrator>rman target / Recovery Manager: Release 10.2.0.1.0 - Production on Thu Sep 9 16:53:31 2010 Copyright (c) 1982, 2005, Oracle. All rights reserved. connected to target database: ORA10G (DBID=4045875789) RMAN> run { 2> allocate channel c1 type disk; 3> backup 4> incremental level=0 5> filesperset 5 6> format 'df_%s_%p_%t' 7> (database); 8> sql 'alter system archive log current'; 9> release channel c1; 10> } using target database control file instead of recovery catalog allocated channel: c1 channel c1: sid=146 devtype=DISK Starting backup at 09-SEP-10 channel c1: starting incremental level 0 datafile backupset channel c1: specifying datafile(s) in backupset input datafile fno=00001 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORA10G\SYSTEM01.DBF input datafile fno=00002 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORA10G\UNDOTBS01.DBF input datafile fno=00003 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORA10G\SYSAUX01.DBF input datafile fno=00004 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORA10G\USERS01.DBF channel c1: starting piece 1 at 09-SEP-10 channel c1: finished piece 1 at 09-SEP-10 piece handle=D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\DF_19_1_729276821 tag=TAG20100909T165341 comment=NONE channel c1: backup set complete, elapsed time: 00:00:50 channel c1: starting incremental level 0 datafile backupset channel c1: specifying datafile(s) in backupset including current control file in backupset including current SPFILE in backupset channel c1: starting piece 1 at 09-SEP-10 channel c1: finished piece 1 at 09-SEP-10 piece handle=D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\DF_20_1_729276871 tag=TAG20100909T165341 comment=NONE channel c1: backup set complete, elapsed time: 00:00:03 Finished backup at 09-SEP-10 sql statement: alter system archive log current released channel: c1 RMAN> RMAN> run { 2> allocate channel c1 type disk; 3> backup 4> filesperset 20 5> format 'arch_%s_%p_%t' 6> (archivelog all delete input); 7> } allocated channel: c1 channel c1: sid=146 devtype=DISK Starting backup at 09-SEP-10 current log archived channel c1: starting archive log backupset channel c1: specifying archive log(s) in backup set input archive log thread=1 sequence=71 recid=42 stamp=729276793 input archive log thread=1 sequence=72 recid=43 stamp=729276794 input archive log thread=1 sequence=73 recid=44 stamp=729276795 input archive log thread=1 sequence=74 recid=45 stamp=729276878 input archive log thread=1 sequence=75 recid=46 stamp=729276880 channel c1: starting piece 1 at 09-SEP-10 channel c1: finished piece 1 at 09-SEP-10 piece handle=D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCH_21_1_729276880 tag=TAG20100909T165440 comment=NONE channel c1: backup set complete, elapsed time: 00:00:02 channel c1: deleting archive log(s) archive log filename=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORA10G\ARCHIVELOG\2010_09_09\O1_MF_1_71_68K83SGM_.ARC recid=42 stamp=7292 archive log filename=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORA10G\ARCHIVELOG\2010_09_09\O1_MF_1_72_68K83TF4_.ARC recid=43 stamp=7292 archive log filename=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORA10G\ARCHIVELOG\2010_09_09\O1_MF_1_73_68K83VG3_.ARC recid=44 stamp=7292 archive log filename=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORA10G\ARCHIVELOG\2010_09_09\O1_MF_1_74_68K86GD3_.ARC recid=45 stamp=7292 archive log filename=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORA10G\ARCHIVELOG\2010_09_09\O1_MF_1_75_68K86HW7_.ARC recid=46 stamp=7292 Finished backup at 09-SEP-10 released channel: c1 RMAN> RMAN> run { 2> allocate channel c1 type disk; 3> backup 4> filesperset 20 5> format 'ctrl_%s_%p_%t' 6> current controlfile; 7> } allocated channel: c1 channel c1: sid=146 devtype=DISK Starting backup at 09-SEP-10 channel c1: starting full datafile backupset channel c1: specifying datafile(s) in backupset including current control file in backupset channel c1: starting piece 1 at 09-SEP-10 channel c1: finished piece 1 at 09-SEP-10 piece handle=D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\CTRL_22_1_729276884 tag=TAG20100909T165444 comment=NONE channel c1: backup set complete, elapsed time: 00:00:02 Finished backup at 09-SEP-10 released channel: c1 RMAN> RMAN>--备份全库，arch文件，控制文件。其实后面我们可以从全库备份或者控制文件备份还原的，但是这里我们不演示用这种方式还原，我们用dbms_backup_restore包来做还原 RMAN> exit Recovery Manager complete. C:\Documents and Settings\Administrator>exit SQL> SQL> SQL> SQL> conn / as sysdba Connected. SQL> SQL> SQL> SQL> shutdown abort ORACLE instance shut down. SQL> SQL> SQL> SQL> exit Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, Oracle Label Security, OLAP and Data Mining options C:\Documents and Settings\Administrator> C:\Documents and Settings\Administrator> C:\Documents and Settings\Administrator> C:\Documents and Settings\Administrator> C:\Documents and Settings\Administrator> C:\Documents and Settings\Administrator>sqlplus "/ as sysdba" SQL*Plus: Release 10.2.0.1.0 - Production on Thu Sep 9 17:20:29 2010 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to an idle instance. SQL> startup nomount ORACLE instance started. Total System Global Area 209715200 bytes Fixed Size 1248116 bytes Variable Size 79692940 bytes Database Buffers 121634816 bytes Redo Buffers 7139328 bytes SQL> SQL>--开始用dbms_backup_restore包来restore数据文件： SQL> declare 2 devtype varchar2(256); 3 done boolean; 4 begin 5 devtype:=sys.dbms_backup_restore.deviceallocate(type=>'',ident=>'t1'); 6 sys.dbms_backup_restore.restoresetdatafile; 7 sys.dbms_backup_restore.restoredatafileto(dfnumber=>01,toname=>'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORA10G\SYSTEM01.DBF'); 8 sys.dbms_backup_restore.restoredatafileto(dfnumber=>02,toname=>'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORA10G\UNDOTBS01.DBF'); 9 sys.dbms_backup_restore.restoredatafileto(dfnumber=>03,toname=>'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORA10G\SYSAUX01.DBF'); 10 sys.dbms_backup_restore.restoredatafileto(dfnumber=>04,toname=>'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORA10G\USERS01.DBF'); 11 sys.dbms_backup_restore.restorebackuppiece(done=>done,handle=>'D:\oracle\product\10.2.0\db_1\database\DF_19_1_729276821',params=>null); 12 sys.dbms_backup_restore.devicedeallocate; 13 end; 14 / PL/SQL procedure successfully completed. SQL> SQL> SQL>--重建控制文件： SQL> CREATE CONTROLFILE REUSE DATABASE "ORA10G" RESETLOGS ARCHIVELOG 2 MAXLOGFILES 16 3 MAXLOGMEMBERS 3 4 MAXDATAFILES 100 5 MAXINSTANCES 8 6 MAXLOGHISTORY 292 7 LOGFILE 8 GROUP 1 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORA10G\REDO01.LOG' SIZE 50M, 9 GROUP 2 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORA10G\REDO02.LOG' SIZE 50M, 10 GROUP 3 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORA10G\REDO03.LOG' SIZE 50M 11 DATAFILE 12 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORA10G\SYSTEM01.DBF', 13 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORA10G\UNDOTBS01.DBF', 14 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORA10G\SYSAUX01.DBF', 15 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORA10G\USERS01.DBF' 16 CHARACTER SET ZHS16GBK 17 ; Control file created. SQL> SQL> recover database; ORA-00283: recovery session canceled due to errors ORA-01610: recovery using the BACKUP CONTROLFILE option must be done SQL> recover database using backup controlfile; ORA-00279: change 1239559 generated at 09/09/2010 16:53:41 needed for thread 1 ORA-00289: suggestion : D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORA10G\ARCHIVELOG\2010_09_09\O1_MF_ 1_74_%U_.ARC ORA-00280: change 1239559 for thread 1 is in sequence #74 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} SQL> --需要74号arch来做recover SQL> --restore 74号arch SQL> declare 2 devtype varchar2(256); 3 done boolean; 4 begin 5 devtype:=sys.dbms_backup_restore.deviceallocate(type=>'',ident=>'t1'); 6 sys.dbms_backup_restore.restoresetarchivedlog(destination=>'D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORA10G\ARCHIVELOG\2010_09_09'); 7 sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>74); 8 sys.dbms_backup_restore.restorebackuppiece(done=>done,handle=>'D:\oracle\product\10.2.0\db_1\database\ARCH_21_1_729276880',params=>null); 9 sys.dbms_backup_restore.devicedeallocate; 10 end; 11 / PL/SQL procedure successfully completed. SQL> SQL> declare 2 devtype varchar2(256); 3 done boolean; 4 begin 5 devtype:=sys.dbms_backup_restore.deviceallocate(type=>'',ident=>'t1'); 6 sys.dbms_backup_restore.restoresetarchivedlog(destination=>'D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORA10G\ARCHIVELOG\2010_09_09'); 7 sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>75); 8 sys.dbms_backup_restore.restorebackuppiece(done=>done,handle=>'D:\oracle\product\10.2.0\db_1\database\ARCH_21_1_729276880',params=>null); 9 sys.dbms_backup_restore.devicedeallocate; 10 end; 11 / PL/SQL procedure successfully completed. SQL> SQL> SQL> SQL> SQL> recover database using backup controlfile; ORA-00279: change 1239644 generated at 09/09/2010 16:54:38 needed for thread 1 ORA-00289: suggestion : D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORA10G\ARCHIVELOG\2010_09_09\O1_MF_ 1_75_%U_.ARC ORA-00280: change 1239644 for thread 1 is in sequence #75 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} D:\oracle\product\10.2.0\flash_recovery_area\ORA10G\ARCHIVELOG\2010_09_09\0727003661_001_00075.ARC ORA-00279: change 1239656 generated at 09/09/2010 16:54:39 needed for thread 1 ORA-00289: suggestion : D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORA10G\ARCHIVELOG\2010_09_09\O1_MF_ 1_76_%U_.ARC ORA-00280: change 1239656 for thread 1 is in sequence #76 ORA-00278: log file 'D:\oracle\product\10.2.0\flash_recovery_area\ORA10G\ARCHIVELOG\2010_09_09\07270 03661_001_00075.ARC' no longer needed for this recovery Specify log: {<RET>=suggested | filename | AUTO | CANCEL} ORA-00308: cannot open archived log 'D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORA10G\ARCHIVELOG\2010_09_09\O1_MF _1_76_%U_.ARC' ORA-27041: unable to open file OSD-04002: ?????? O/S-Error: (OS 2) The system cannot find the file specified. SQL> SQL> SQL> SQL> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-01113: file 1 needs media recovery ORA-01110: data file 1: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORA10G\SYSTEM01.DBF' SQL> SQL> recover database; ORA-00283: recovery session canceled due to errors ORA-01610: recovery using the BACKUP CONTROLFILE option must be done SQL> recover database using backup controlfile until cancel; ORA-00279: change 1239656 generated at 09/09/2010 16:54:39 needed for thread 1 ORA-00289: suggestion : D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORA10G\ARCHIVELOG\2010_09_09\O1_MF_ 1_76_%U_.ARC ORA-00280: change 1239656 for thread 1 is in sequence #76 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} cancel; ORA-00308: cannot open archived log 'cancel;' ORA-27041: unable to open file OSD-04002: ?????? O/S-Error: (OS 2) The system cannot find the file specified. Specify log: {<RET>=suggested | filename | AUTO | CANCEL} cancel Media recovery cancelled. SQL> alter database open resetlogs; Database altered. SQL> SQL> SQL> SQL> SQL> select count(*) from test.t1; COUNT(*) ---------- 1024 SQL>--我们看到还原到crash前未commit的状态。 |