丢失控制文件恢复实验记录--2(当前的控制文件损坏，使用旧控制文件进行恢复(旧控制文件之后新增了表空间的情况))
一、实验说明：
本文转载于Luocs的丢失控制文件恢复实验记录--2，此处属于转载+模拟。
操作系统：rhel 5.4 x32
数据库：oracle 11g r2
二、实验操作：
----重新备份一个当前控制文件,这里也可以使用backup current controlfile----
1 RMAN> backup as copy current controlfile; 2 3 Starting backup at 10-JAN-13 4 allocated channel: ORA_DISK_1 5 channel ORA_DISK_1: SID=42 device type=DISK 6 channel ORA_DISK_1: starting datafile copy 7 copying current control file 8 output file name=/u01/app/oracle/flash_recovery_area/YFT/controlfile/o1_mf_TAG20130110T132142_8gwmz7rd_.ctl tag=TAG20130110T132142 RECID=2 STAMP=804345704 9 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 10 Finished backup at 10-JAN-13 11 ----模拟一些数据----
12 SQL> create tablespace jack datafile '/u01/app/oracle/oradata/yft/jack01.dbf' size 50m; 13 14 Tablespace created. 15 16 SQL> create user echo identified by echo default tablespace jack; 17 18 User created. 19 20 SQL> grant resource,connect to echo; 21 22 Grant succeeded. 23 24 SQL> create table echo.tab as select * from dba_objects; 25 26 Table created. 27 28 SQL> alter system switch logfile; 29 30 System altered. 31 32 SQL> insert into echo.tab select * from echo.tab; 33 34 72528 rows created. 35 36 SQL> commit; 37 38 Commit complete. 39 40 SQL> select count(*) from echo.tab; 41 42 COUNT(*) 43 ---------- 44 145056 45 46 SQL> alter system switch logfile; 47 48 System altered. 49 50 SQL> alter system switch logfile; 51 52 System altered. 53 54 SQL> alter system switch logfile; 55 56 System altered. 57 58 SQL> alter system switch logfile; 59 60 System altered. 61 62 SQL> alter system switch logfile; 63 64 System altered. 65 ----删除部分文件，注意不要切换日志，为了不让这个操作写入归档----
66 SQL> delete from echo.tab where rownum <= 20000; 67 68 20000 rows deleted. 69 70 SQL> commit; 71 72 Commit complete. 73 74 SQL> shutdown abort; 75 ORACLE instance shut down. 76 ----将控制文件删除----
77 [oracle@yft ~]$ rm /u01/app/oracle/oradata/yft/control01.ctl 78 [oracle@yft ~]$ rm /u01/app/oracle/flash_recovery_area/yft/control02.ctl 79 ----启动报错----
80 SQL> startup 81 ORACLE instance started. 82 83 Total System Global Area 330600448 bytes 84 Fixed Size 1336344 bytes 85 Variable Size 255855592 bytes 86 Database Buffers 67108864 bytes 87 Redo Buffers 6299648 bytes 88 ORA-00205: error in identifying control file, check alert log for more info 89 ----修改控制文件位置：----
90 SQL> alter system set control_files='/u01/app/oracle/flash_recovery_area/YFT/controlfile/o1_mf_TAG20130110T132142_8gwmz7rd_.ctl' scope=spfile; 91 92 System altered. 93 94 SQL> startup force; 95 ORACLE instance started. 96 97 Total System Global Area 330600448 bytes 98 Fixed Size 1336344 bytes 99 Variable Size 255855592 bytes 100 Database Buffers 67108864 bytes 101 Redo Buffers 6299648 bytes 102 Database mounted. 103 ORA-01589: must use RESETLOGS or NORESETLOGS option for database open 104 ----这时候查看，因为是拿旧的控制文件打开的，所以看不到后添加的数据文件----
105 SQL> select name from v$datafile; 106 107 NAME 108 -------------------------------------------------------------------------------- 109 /u01/app/oracle/oradata/yft/system01.dbf 110 /u01/app/oracle/oradata/yft/sysaux01.dbf 111 /u01/app/oracle/oradata/yft/undotbs01.dbf 112 /u01/app/oracle/oradata/yft/users01.dbf 113 /u01/app/oracle/oradata/yft/example01.dbf 114 /u01/app/oracle/oradata/yft/yft01.dbf 115 116 6 rows selected. 117 ----恢复数据库----
118 RMAN> recover database; 119 120 Starting recover at 10-JAN-13 121 Starting implicit crosscheck backup at 10-JAN-13 122 using target database control file instead of recovery catalog 123 allocated channel: ORA_DISK_1 124 channel ORA_DISK_1: SID=20 device type=DISK 125 Finished implicit crosscheck backup at 10-JAN-13 126 127 Starting implicit crosscheck copy at 10-JAN-13 128 using channel ORA_DISK_1 129 Finished implicit crosscheck copy at 10-JAN-13 130 131 searching for all files in the recovery area 132 cataloging files... 133 cataloging done 134 135 List of Cataloged Files 136 ======================= 137 File Name: /u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_10/o1_mf_1_7_8gwnjg0s_.arc 138 File Name: /u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_10/o1_mf_1_3_8gwngqx1_.arc 139 File Name: /u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_10/o1_mf_1_2_8gwnfk3t_.arc 140 File Name: /u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_10/o1_mf_1_5_8gwnj8qg_.arc 141 File Name: /u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_10/o1_mf_1_6_8gwnj9o6_.arc 142 File Name: /u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_10/o1_mf_1_4_8gwnj20n_.arc 143 144 using channel ORA_DISK_1 145 146 starting media recovery 147 148 archived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_10/o1_mf_1_2_8gwnfk3t_.arc 149 archived log for thread 1 with sequence 3 is already on disk as file /u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_10/o1_mf_1_3_8gwngqx1_.arc 150 archived log for thread 1 with sequence 4 is already on disk as file /u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_10/o1_mf_1_4_8gwnj20n_.arc 151 archived log for thread 1 with sequence 5 is already on disk as file /u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_10/o1_mf_1_5_8gwnj8qg_.arc 152 archived log for thread 1 with sequence 6 is already on disk as file /u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_10/o1_mf_1_6_8gwnj9o6_.arc 153 archived log for thread 1 with sequence 7 is already on disk as file /u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_10/o1_mf_1_7_8gwnjg0s_.arc 154 archived log for thread 1 with sequence 8 is already on disk as file /u01/app/oracle/oradata/yft/redo02.log 155 archived log file name=/u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_10/o1_mf_1_2_8gwnfk3t_.arc thread=1 sequence=2 156 creating datafile file number=7 name=/u01/app/oracle/oradata/yft/jack01.dbf 157 archived log file name=/u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_10/o1_mf_1_2_8gwnfk3t_.arc thread=1 sequence=2 158 archived log file name=/u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_10/o1_mf_1_3_8gwngqx1_.arc thread=1 sequence=3 159 archived log file name=/u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_10/o1_mf_1_4_8gwnj20n_.arc thread=1 sequence=4 160 archived log file name=/u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_10/o1_mf_1_5_8gwnj8qg_.arc thread=1 sequence=5 161 archived log file name=/u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_10/o1_mf_1_6_8gwnj9o6_.arc thread=1 sequence=6 162 archived log file name=/u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_10/o1_mf_1_7_8gwnjg0s_.arc thread=1 sequence=7 163 archived log file name=/u01/app/oracle/oradata/yft/redo02.log thread=1 sequence=8 164 media recovery complete, elapsed time: 00:00:04 165 Finished recover at 10-JAN-13 166 ----这时候我们看见后添加的数据文件已经加进来了----
167 SQL> select name from v$datafile; 168 169 NAME 170 -------------------------------------------------------------------------------- 171 /u01/app/oracle/oradata/yft/system01.dbf 172 /u01/app/oracle/oradata/yft/sysaux01.dbf 173 /u01/app/oracle/oradata/yft/undotbs01.dbf 174 /u01/app/oracle/oradata/yft/users01.dbf 175 /u01/app/oracle/oradata/yft/example01.dbf 176 /u01/app/oracle/oradata/yft/yft01.dbf 177 /u01/app/oracle/oradata/yft/jack01.dbf 178 179 7 rows selected. 180
181 SQL> alter database open resetlogs; 182 183 Database altered. 184 185 SQL> select count(*) from echo.tab; 186 187 COUNT(*) 188 ---------- 189 125056
