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