丢失控制文件恢复实验记录--5(在线日志文件没有损坏，归档日志丢失，直接重建控制文件，(跟踪控制文件trace是新的情况))...-白红宇

丢失控制文件恢复实验记录--5(在线日志文件没有损坏，归档日志丢失，直接重建控制文件，(跟踪控制文件trace是新的情况))...

阅读量：4946 次

发布时间：2019-06-11

本文共 15229 字，大约阅读时间需要 50 分钟。

一、实验说明：

本文转载于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                ONLINE100      4 /u01/app/oracle/oradata/yft/users01.dbf                  ONLINE101      5 /u01/app/oracle/oradata/yft/example01.dbf                ONLINE102      6 /u01/app/oracle/oradata/yft/jack01.dbf                   ONLINE103      7 /u01/app/oracle/oradata/yft/luocs01.dbf                  ONLINE104 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 VALUE115 ------------------------------------------------------------------------------------------------------------------------116 /u01/app/oracle/diag/rdbms/yft/yft/trace/yft_ora_5128.trc117 118 [oracle@yft ~]$ sed -n '/CREATE CONTROLFILE.*NORESETLOGS/,/;/p' /u01/app/oracle/diag/rdbms/yft/yft/trace/yft_ora_5128.trc119 CREATE CONTROLFILE REUSE DATABASE "YFT" NORESETLOGS  ARCHIVELOG120     MAXLOGFILES 16121     MAXLOGMEMBERS 3122     MAXDATAFILES 100123     MAXINSTANCES 8124     MAXLOGHISTORY 292125 LOGFILE126   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 512129 -- STANDBY LOGFILE130 DATAFILE131   '/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 AL32UTF8139 ;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> startup148 ORACLE instance started.149 150 Total System Global Area  330600448 bytes151 Fixed Size            1336344 bytes152 Variable Size          239078376 bytes153 Database Buffers       83886080 bytes154 Redo Buffers            6299648 bytes155 ORA-00205: error in identifying control file, check alert log for more info156 157  ----重建控制文件---- 158 SQL>CREATE CONTROLFILE REUSE DATABASE "YFT" NORESETLOGS  ARCHIVELOG159     MAXLOGFILES 16160     MAXLOGMEMBERS 3161     MAXDATAFILES 100162     MAXINSTANCES 8163     MAXLOGHISTORY 292164 LOGFILE165   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 512168 -- STANDBY LOGFILE169 DATAFILE170   '/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 AL32UTF8178  21  ;179 180 Control file created.181   ----因为拿的是新的控制文件来创建的，所以所有的数据文件信息都显示了---- 182 SQL> select file#, name, status from v$datafile;183 184      FILE# NAME                                                 STATUS185 ---------- ---------------------------------------------------- -------186      1 /u01/app/oracle/oradata/yft/system01.dbf                  SYSTEM187      2 /u01/app/oracle/oradata/yft/sysaux01.dbf                  RECOVER188      3 /u01/app/oracle/oradata/yft/undotbs01.dbf                 RECOVER189      4 /u01/app/oracle/oradata/yft/users01.dbf                   RECOVER190      5 /u01/app/oracle/oradata/yft/example01.dbf                 RECOVER191      6 /u01/app/oracle/oradata/yft/jack01.dbf                    RECOVER192      7 /u01/app/oracle/oradata/yft/luocs01.dbf                   RECOVER193 194 7 rows selected.195  ----恢复数据库，报找不到17号日志文件---- 196 RMAN> recover database;197 198 Starting recover at 12-JAN-13199 using target database control file instead of recovery catalog200 allocated channel: ORA_DISK_1201 channel ORA_DISK_1: SID=20 device type=DISK202 203 starting media recovery204 205 unable to find archived log206 archived log thread=1 sequence=17207 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:47211 RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 17 and starting SCN of 883016212 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                                 STATUS217 ---------- -------------------------------------- ---------------218     16 /u01/app/oracle/oradata/yft/redo01.log     INACTIVE219     17 /u01/app/oracle/oradata/yft/redo02.log     CURRENT220     15 /u01/app/oracle/oradata/yft/redo03.log     INACTIVE221   ----17号日志文件正好是当前的，基于控制文件恢复一下---- 222 SQL> recover database using backup controlfile;223 ORA-00279: change 883016 generated at 01/12/2013 09:57:12 needed for thread 1224 ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_12/o1_mf_1_17_%u_.arc225 ORA-00280: change 883016 for thread 1 is in sequence #17226 227 228 Specify log: {
     
      =suggested | filename | AUTO | CANCEL}229 /u01/app/oracle/oradata/yft/redo02.log230 Log applied.231 Media recovery complete.232  ----恢复的时候告警日志文件的内容---- 233 ALTER DATABASE RECOVER  database using backup controlfile  234 Media Recovery Start235 Serial Media Recovery started236 WARNING! Recovering data file 1 from a fuzzy file. If not the current file237 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 file239 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 file241 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 file243 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 file245 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 file247 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 file249 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.log253 Media Recovery Complete (yft)254 Completed: ALTER DATABASE RECOVER    LOGFILE '/u01/app/oracle/oradata/yft/redo02.log'  255 Sat Jan 12 10:03:21 2013256  ----查看数据文件的状态，在这里可以看到所有数据文件都已经online了，而且刚才创建的luocs01.dbf也在---- 257 SQL> select file#, name, status from v$datafile;258 259      FILE# NAME                                                STATUS260 ---------- ---------------------------------------------  ---------------261      1 /u01/app/oracle/oradata/yft/system01.dbf                 SYSTEM262      2 /u01/app/oracle/oradata/yft/sysaux01.dbf                 ONLINE263      3 /u01/app/oracle/oradata/yft/undotbs01.dbf                ONLINE264      4 /u01/app/oracle/oradata/yft/users01.dbf                  ONLINE265      5 /u01/app/oracle/oradata/yft/example01.dbf                ONLINE266      6 /u01/app/oracle/oradata/yft/jack01.dbf                   ONLINE267      7 /u01/app/oracle/oradata/yft/luocs01.dbf                  ONLINE268 269 7 rows selected.270 271 SQL> alter database open resetlogs;272 273 Database altered.274  ----resetlogs打开数据库的时候告警日志里面的信息---- 275 alter database open resetlogs276 RESETLOGS is being done without consistancy checks. This may result277 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 883072282 Resetting resetlogs activation ID 2957151050 (0xb0428b4a)283 Sat Jan 12 10:03:28 2013284 Setting recovery target incarnation to 3285 Sat Jan 12 10:03:28 2013286 Assigning activation ID 2957188965 (0xb0431f65)287 LGWR: STARTING ARCH PROCESSES288 Sat Jan 12 10:03:28 2013289 ARC0 started with pid=23, OS id=5358 290 ARC0: Archival started291 LGWR: STARTING ARCH PROCESSES COMPLETE292 ARC0: STARTING ARCH PROCESSES293 Thread 1 opened at log sequence 1294   Current log# 1 seq# 1 mem# 0: /u01/app/oracle/oradata/yft/redo01.log295 Successful open of redo thread 1296 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set297 Sat Jan 12 10:03:29 2013298 SMON: enabling cache recovery299 Successfully onlined Undo Tablespace 2.300 Dictionary check beginning301 Tablespace 'TEMP' #3 found in data dictionary,302 but not in the controlfile. Adding to controlfile.303 Sat Jan 12 10:03:30 2013304 ARC2 started with pid=25, OS id=5362 305 Dictionary check complete306 Verifying file header compatibility for 11g tablespace encryption..307 Verifying 11g file header compatibility for tablespace encryption completed308 SMON: enabling tx recovery309 *********************************************************************310 WARNING: The following temporary tablespaces contain no files.311          This condition can occur when a backup controlfile has312          been restored.  It may be necessary to add files to these313          tablespaces.  That can be done using the SQL statement:314  315          ALTER TABLESPACE 
      
        ADD TEMPFILE316  317          Alternatively, if these temporary tablespaces are no longer318          needed, then they can be dropped.319            Empty temporary tablespace: TEMP320 *********************************************************************321 Database Characterset is AL32UTF8322 Sat Jan 12 10:03:30 2013323 ARC3 started with pid=26, OS id=5364 324 Sat Jan 12 10:03:30 2013325 ARC1 started with pid=24, OS id=5360 326 No Resource Manager plan active327 **********************************************************328 WARNING: Files may exists in db_recovery_file_dest329 that are not known to the database. Use the RMAN command330 CATALOG RECOVERY AREA to re-catalog any such files.331 If files cannot be cataloged, then manually delete them332 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 and338    then disabled.339 **********************************************************340 replication_dependency_tracking turned off (no async multimaster replication found)341 ARC1: Archival started342 ARC2: Archival started343 ARC3: Archival started344 ARC0: STARTING ARCH PROCESSES COMPLETE345 ARC0: Becoming the 'no FAL' ARCH346 ARC0: Becoming the 'no SRL' ARCH347 Starting background process QMNC348 ARC1: Becoming the heartbeat ARCH349 Sat Jan 12 10:03:32 2013350 QMNC started with pid=27, OS id=5366 351 LOGSTDBY: Validating controlfile with logical metadata352 LOGSTDBY: Validation complete353 Sat Jan 12 10:03:34 2013354 Completed: alter database open resetlogs355 Sat Jan 12 10:03:34 2013356 db_recovery_file_dest_size of 3852 MB is 0.05% used. This is a357 user-specified limit on the amount of space that will be used by this358 database for recovery-related files, and does not reflect the amount of359 space available in the underlying filesystem or ASM diskgroup.360 Sat Jan 12 10:03:34 2013361 Starting background process CJQ0362 Sat Jan 12 10:03:34 2013363 CJQ0 started with pid=30, OS id=5382 364 Setting Resource Manager plan SCHEDULER[0x3008]:DEFAULT_MAINTENANCE_PLAN via scheduler window365 Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter366 Sat Jan 12 10:03:37 2013367 Starting background process VKRM368 Sat Jan 12 10:03:37 2013369 VKRM started with pid=28, OS id=5384 370  ----查看数据丢失没有---- 371 SQL> select count(*) from luocs.t1;372 373   COUNT(*)374 ----------375      14999376 377 SQL> select count(*) from test.t1;378 379   COUNT(*)380 ----------381      33451382  ----添加临时表空间---- 383 SQL> alter tablespace temp add tempfile '/u01/app/oracle/oradata/yft/temp01.dbf' size 200m;384 385 Tablespace altered. 至此，我们拿新的控制文件恢复的时候不会丢失数据。