一、实验说明：
本文转载于Luocs的丢失控制文件恢复实验记录--5，此处属于转载+模拟。
操作系统：rhel 5.4 x32
数据库：oracle 11g r2
二、实验操作：
1 SQL> create tablespace luocs datafile '/u01/app/oracle/oradata/yft/luocs01.dbf' size 50m; 2 3 Tablespace created. 4 5 SQL> create user luocs identified by oracle default tablespace luocs; 6 7 User created. 8 9 SQL> grant resource,connect to luocs; 10 11 Grant succeeded. 12 13 SQL> create table luocs.t1 as select * from dba_objects where rownum<10000; 14 15 Table created. 16 17 SQL> alter system switch logfile; 18 19 System altered. 20 21 SQL> insert into luocs.t1 select * from luocs.t1; 22 23 9999 rows created. 24 25 SQL> commit; 26 27 Commit complete. 28 29 SQL> alter system switch logfile; 30 31 System altered. 32 33 SQL> alter system switch logfile; 34 35 System altered. 36 37 SQL> alter system switch logfile; 38 39 System altered. 40 41 SQL> select max(sequence#) from v$archived_log; 42 43 MAX(SEQUENCE#) 44 -------------- 45 13 46 47 SQL> select count(*) from luocs.t1; 48 49 COUNT(*) 50 ---------- 51 19998 52 53 SQL> select count(*) from test.t1; 54 55 COUNT(*) 56 ---------- 57 63450 58 59 SQL> delete from luocs.t1 where rownum<5000; 60 61 4999 rows deleted. 62 63 SQL> delete from test.t1 where rownum<30000; 64 65 29999 rows deleted. 66 67 SQL> select count(*) from test.t1; 68 69 COUNT(*) 70 ---------- 71 33451 72 73 SQL> select count(*) from luocs.t1; 74 75 COUNT(*) 76 ---------- 77 14999 78 79 SQL> alter system switch logfile; 80 81 System altered. 82 83 SQL> alter system switch logfile; 84 85 System altered. 86 87 SQL> alter system switch logfile; 88 89 System altered. 90 91 SQL> set linesize 150; 92 SQL> col name for a65; 93 SQL> select file#,name,status from v$datafile; 94 95 FILE# NAME STATUS 96 ---------- ------------------------------------------------ ------- 97 1 /u01/app/oracle/oradata/yft/system01.dbf SYSTEM 98 2 /u01/app/oracle/oradata/yft/sysaux01.dbf ONLINE 99 3 /u01/app/oracle/oradata/yft/undotbs01.dbf ONLINE 100 4 /u01/app/oracle/oradata/yft/users01.dbf ONLINE 101 5 /u01/app/oracle/oradata/yft/example01.dbf ONLINE 102 6 /u01/app/oracle/oradata/yft/jack01.dbf ONLINE 103 7 /u01/app/oracle/oradata/yft/luocs01.dbf ONLINE 104 105 7 rows selected. 106 107 SQL> alter database backup controlfile to trace; 108 109 Database altered. 110 111 SQL> set linesize 120; 112 SQL> select value from v$diag_info where NAME = 'Default Trace File'; 113 114 VALUE 115 ------------------------------------------------------------------------------------------------------------------------ 116 /u01/app/oracle/diag/rdbms/yft/yft/trace/yft_ora_5128.trc 117 118 [oracle@yft ~]$ sed -n '/CREATE CONTROLFILE.*NORESETLOGS/,/;/p' /u01/app/oracle/diag/rdbms/yft/yft/trace/yft_ora_5128.trc 119 CREATE CONTROLFILE REUSE DATABASE "YFT" NORESETLOGS ARCHIVELOG 120 MAXLOGFILES 16 121 MAXLOGMEMBERS 3 122 MAXDATAFILES 100 123 MAXINSTANCES 8 124 MAXLOGHISTORY 292 125 LOGFILE 126 GROUP 1 '/u01/app/oracle/oradata/yft/redo01.log' SIZE 50M BLOCKSIZE 512, 127 GROUP 2 '/u01/app/oracle/oradata/yft/redo02.log' SIZE 50M BLOCKSIZE 512, 128 GROUP 3 '/u01/app/oracle/oradata/yft/redo03.log' SIZE 50M BLOCKSIZE 512 129 -- STANDBY LOGFILE 130 DATAFILE 131 '/u01/app/oracle/oradata/yft/system01.dbf', 132 '/u01/app/oracle/oradata/yft/sysaux01.dbf', 133 '/u01/app/oracle/oradata/yft/undotbs01.dbf', 134 '/u01/app/oracle/oradata/yft/users01.dbf', 135 '/u01/app/oracle/oradata/yft/example01.dbf', 136 '/u01/app/oracle/oradata/yft/jack01.dbf', 137 '/u01/app/oracle/oradata/yft/luocs01.dbf' 138 CHARACTER SET AL32UTF8 139 ; 140 ----模拟丢失控制文件，丢失归档日志文件----
141 SQL> shutdown abort; 142 ORACLE instance shut down. 143 144 [oracle@yft ~]$ rm /u01/app/oracle/oradata/yft/control01.ctl 145 [oracle@yft ~]$ mv /u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_12/* /tmp/bak/ 146 147 SQL> startup 148 ORACLE instance started. 149 150 Total System Global Area 330600448 bytes 151 Fixed Size 1336344 bytes 152 Variable Size 239078376 bytes 153 Database Buffers 83886080 bytes 154 Redo Buffers 6299648 bytes 155 ORA-00205: error in identifying control file, check alert log for more info 156 157 ----重建控制文件----
158 SQL>CREATE CONTROLFILE REUSE DATABASE "YFT" NORESETLOGS ARCHIVELOG 159 MAXLOGFILES 16 160 MAXLOGMEMBERS 3 161 MAXDATAFILES 100 162 MAXINSTANCES 8 163 MAXLOGHISTORY 292 164 LOGFILE 165 GROUP 1 '/u01/app/oracle/oradata/yft/redo01.log' SIZE 50M BLOCKSIZE 512, 166 GROUP 2 '/u01/app/oracle/oradata/yft/redo02.log' SIZE 50M BLOCKSIZE 512, 167 GROUP 3 '/u01/app/oracle/oradata/yft/redo03.log' SIZE 50M BLOCKSIZE 512 168 -- STANDBY LOGFILE 169 DATAFILE 170 '/u01/app/oracle/oradata/yft/system01.dbf', 171 '/u01/app/oracle/oradata/yft/sysaux01.dbf', 172 '/u01/app/oracle/oradata/yft/undotbs01.dbf', 173 '/u01/app/oracle/oradata/yft/users01.dbf', 174 '/u01/app/oracle/oradata/yft/example01.dbf', 175 '/u01/app/oracle/oradata/yft/jack01.dbf', 176 '/u01/app/oracle/oradata/yft/luocs01.dbf' 177 CHARACTER SET AL32UTF8 178 21 ; 179 180 Control file created. 181 ----因为拿的是新的控制文件来创建的，所以所有的数据文件信息都显示了----
182 SQL> select file#, name, status from v$datafile; 183 184 FILE# NAME STATUS 185 ---------- ---------------------------------------------------- ------- 186 1 /u01/app/oracle/oradata/yft/system01.dbf SYSTEM 187 2 /u01/app/oracle/oradata/yft/sysaux01.dbf RECOVER 188 3 /u01/app/oracle/oradata/yft/undotbs01.dbf RECOVER 189 4 /u01/app/oracle/oradata/yft/users01.dbf RECOVER 190 5 /u01/app/oracle/oradata/yft/example01.dbf RECOVER 191 6 /u01/app/oracle/oradata/yft/jack01.dbf RECOVER 192 7 /u01/app/oracle/oradata/yft/luocs01.dbf RECOVER 193 194 7 rows selected. 195 ----恢复数据库，报找不到17号日志文件----
196 RMAN> recover database; 197 198 Starting recover at 12-JAN-13 199 using target database control file instead of recovery catalog 200 allocated channel: ORA_DISK_1 201 channel ORA_DISK_1: SID=20 device type=DISK 202 203 starting media recovery 204 205 unable to find archived log 206 archived log thread=1 sequence=17 207 RMAN-00571: =========================================================== 208 RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== 209 RMAN-00571: =========================================================== 210 RMAN-03002: failure of recover command at 01/12/2013 10:01:47 211 RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 17 and starting SCN of 883016 212 213 SQL> col member for a45; 214 SQL> select b.sequence#, a.member, b.status from v$logfile a, v$log b where a.group#=b.group#; 215 216 SEQUENCE# MEMBER STATUS 217 ---------- -------------------------------------- --------------- 218 16 /u01/app/oracle/oradata/yft/redo01.log INACTIVE 219 17 /u01/app/oracle/oradata/yft/redo02.log CURRENT 220 15 /u01/app/oracle/oradata/yft/redo03.log INACTIVE 221 ----17号日志文件正好是当前的，基于控制文件恢复一下----
222 SQL> recover database using backup controlfile; 223 ORA-00279: change 883016 generated at 01/12/2013 09:57:12 needed for thread 1 224 ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_12/o1_mf_1_17_%u_.arc 225 ORA-00280: change 883016 for thread 1 is in sequence #17 226 227 228 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} 229 /u01/app/oracle/oradata/yft/redo02.log 230 Log applied. 231 Media recovery complete. 232 ----恢复的时候告警日志文件的内容----
233 ALTER DATABASE RECOVER database using backup controlfile 234 Media Recovery Start 235 Serial Media Recovery started 236 WARNING! Recovering data file 1 from a fuzzy file. If not the current file 237 it might be an online backup taken without entering the begin backup command. 238 WARNING! Recovering data file 2 from a fuzzy file. If not the current file 239 it might be an online backup taken without entering the begin backup command. 240 WARNING! Recovering data file 3 from a fuzzy file. If not the current file 241 it might be an online backup taken without entering the begin backup command. 242 WARNING! Recovering data file 4 from a fuzzy file. If not the current file 243 it might be an online backup taken without entering the begin backup command. 244 WARNING! Recovering data file 5 from a fuzzy file. If not the current file 245 it might be an online backup taken without entering the begin backup command. 246 WARNING! Recovering data file 6 from a fuzzy file. If not the current file 247 it might be an online backup taken without entering the begin backup command. 248 WARNING! Recovering data file 7 from a fuzzy file. If not the current file 249 it might be an online backup taken without entering the begin backup command. 250 ORA-279 signalled during: ALTER DATABASE RECOVER database using backup controlfile ... 251 ALTER DATABASE RECOVER LOGFILE '/u01/app/oracle/oradata/yft/redo02.log' 252 Media Recovery Log /u01/app/oracle/oradata/yft/redo02.log 253 Media Recovery Complete (yft) 254 Completed: ALTER DATABASE RECOVER LOGFILE '/u01/app/oracle/oradata/yft/redo02.log' 255 Sat Jan 12 10:03:21 2013 256 ----查看数据文件的状态，在这里可以看到所有数据文件都已经online了，而且刚才创建的luocs01.dbf也在----
257 SQL> select file#, name, status from v$datafile; 258 259 FILE# NAME STATUS 260 ---------- --------------------------------------------- --------------- 261 1 /u01/app/oracle/oradata/yft/system01.dbf SYSTEM 262 2 /u01/app/oracle/oradata/yft/sysaux01.dbf ONLINE 263 3 /u01/app/oracle/oradata/yft/undotbs01.dbf ONLINE 264 4 /u01/app/oracle/oradata/yft/users01.dbf ONLINE 265 5 /u01/app/oracle/oradata/yft/example01.dbf ONLINE 266 6 /u01/app/oracle/oradata/yft/jack01.dbf ONLINE 267 7 /u01/app/oracle/oradata/yft/luocs01.dbf ONLINE 268 269 7 rows selected. 270 271 SQL> alter database open resetlogs; 272 273 Database altered. 274 ----resetlogs打开数据库的时候告警日志里面的信息----
275 alter database open resetlogs 276 RESETLOGS is being done without consistancy checks. This may result 277 in a corrupted database. The database should be recreated. 278 Archived Log entry 2 added for thread 1 sequence 16 ID 0xb0428b4a dest 1: 279 Archived Log entry 3 added for thread 1 sequence 17 ID 0xb0428b4a dest 1: 280 Archived Log entry 4 added for thread 1 sequence 15 ID 0xb0428b4a dest 1: 281 RESETLOGS after incomplete recovery UNTIL CHANGE 883072 282 Resetting resetlogs activation ID 2957151050 (0xb0428b4a) 283 Sat Jan 12 10:03:28 2013 284 Setting recovery target incarnation to 3 285 Sat Jan 12 10:03:28 2013 286 Assigning activation ID 2957188965 (0xb0431f65) 287 LGWR: STARTING ARCH PROCESSES 288 Sat Jan 12 10:03:28 2013 289 ARC0 started with pid=23, OS id=5358 290 ARC0: Archival started 291 LGWR: STARTING ARCH PROCESSES COMPLETE 292 ARC0: STARTING ARCH PROCESSES 293 Thread 1 opened at log sequence 1 294 Current log# 1 seq# 1 mem# 0: /u01/app/oracle/oradata/yft/redo01.log 295 Successful open of redo thread 1 296 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set 297 Sat Jan 12 10:03:29 2013 298 SMON: enabling cache recovery 299 Successfully onlined Undo Tablespace 2. 300 Dictionary check beginning 301 Tablespace 'TEMP' #3 found in data dictionary, 302 but not in the controlfile. Adding to controlfile. 303 Sat Jan 12 10:03:30 2013 304 ARC2 started with pid=25, OS id=5362 305 Dictionary check complete 306 Verifying file header compatibility for 11g tablespace encryption.. 307 Verifying 11g file header compatibility for tablespace encryption completed 308 SMON: enabling tx recovery 309 ********************************************************************* 310 WARNING: The following temporary tablespaces contain no files. 311 This condition can occur when a backup controlfile has 312 been restored. It may be necessary to add files to these 313 tablespaces. That can be done using the SQL statement: 314 315 ALTER TABLESPACE <tablespace_name> ADD TEMPFILE 316 317 Alternatively, if these temporary tablespaces are no longer 318 needed, then they can be dropped. 319 Empty temporary tablespace: TEMP 320 ********************************************************************* 321 Database Characterset is AL32UTF8 322 Sat Jan 12 10:03:30 2013 323 ARC3 started with pid=26, OS id=5364 324 Sat Jan 12 10:03:30 2013 325 ARC1 started with pid=24, OS id=5360 326 No Resource Manager plan active 327 ********************************************************** 328 WARNING: Files may exists in db_recovery_file_dest 329 that are not known to the database. Use the RMAN command 330 CATALOG RECOVERY AREA to re-catalog any such files. 331 If files cannot be cataloged, then manually delete them 332 using OS command. 333 One of the following events caused this: 334 1. A backup controlfile was restored. 335 2. A standby controlfile was restored. 336 3. The controlfile was re-created. 337 4. db_recovery_file_dest had previously been enabled and 338 then disabled. 339 ********************************************************** 340 replication_dependency_tracking turned off (no async multimaster replication found) 341 ARC1: Archival started 342 ARC2: Archival started 343 ARC3: Archival started 344 ARC0: STARTING ARCH PROCESSES COMPLETE 345 ARC0: Becoming the 'no FAL' ARCH 346 ARC0: Becoming the 'no SRL' ARCH 347 Starting background process QMNC 348 ARC1: Becoming the heartbeat ARCH 349 Sat Jan 12 10:03:32 2013 350 QMNC started with pid=27, OS id=5366 351 LOGSTDBY: Validating controlfile with logical metadata 352 LOGSTDBY: Validation complete 353 Sat Jan 12 10:03:34 2013 354 Completed: alter database open resetlogs 355 Sat Jan 12 10:03:34 2013 356 db_recovery_file_dest_size of 3852 MB is 0.05% used. This is a 357 user-specified limit on the amount of space that will be used by this 358 database for recovery-related files, and does not reflect the amount of 359 space available in the underlying filesystem or ASM diskgroup. 360 Sat Jan 12 10:03:34 2013 361 Starting background process CJQ0 362 Sat Jan 12 10:03:34 2013 363 CJQ0 started with pid=30, OS id=5382 364 Setting Resource Manager plan SCHEDULER[0x3008]:DEFAULT_MAINTENANCE_PLAN via scheduler window 365 Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter 366 Sat Jan 12 10:03:37 2013 367 Starting background process VKRM 368 Sat Jan 12 10:03:37 2013 369 VKRM started with pid=28, OS id=5384 370 ----查看数据丢失没有----
371 SQL> select count(*) from luocs.t1; 372 373 COUNT(*) 374 ---------- 375 14999 376 377 SQL> select count(*) from test.t1; 378 379 COUNT(*) 380 ---------- 381 33451 382 ----添加临时表空间----
383 SQL> alter tablespace temp add tempfile '/u01/app/oracle/oradata/yft/temp01.dbf' size 200m; 384 385 Tablespace altered.
至此，我们拿新的控制文件恢复的时候不会丢失数据。
592
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
