丢失控制文件恢复实验记录--1(当前的控制文件损坏，使用旧控制文件进行恢复(旧控制文件之后DROP了表空间的情况))...

最新推荐文章于 2024-09-20 11:33:48 发布
weixin_30348519
最新推荐文章于 2024-09-20 11:33:48 发布
阅读量70
点赞数
文章标签：数据库操作系统
原文链接：http://www.cnblogs.com/Richardzhu/articles/2854137.html
版权
一、实验说明：
本文转载于Luocs的丢失控制文件恢复实验记录--1，此处属于转载+模拟。
操作系统：rhel 5.4 x32
数据库：oracle 11g r2
二、实验操作：
  ----备份当前控制文件----
  1 RMAN> backup current controlfile;
  2 
  3 Starting backup at 10-JAN-13
  4 using target database control file instead of recovery catalog
  5 allocated channel: ORA_DISK_1
  6 channel ORA_DISK_1: SID=17 device type=DISK
  7 channel ORA_DISK_1: starting full datafile backup set
  8 channel ORA_DISK_1: specifying datafile(s) in backup set
  9 including current control file in backup set
 10 channel ORA_DISK_1: starting piece 1 at 10-JAN-13
 11 channel ORA_DISK_1: finished piece 1 at 10-JAN-13
 12 piece handle=/u01/app/oracle/flash_recovery_area/YFT/backupset/2013_01_10/o1_mf_ncnnf_TAG20130110T070737_8gvy1xvm_.bkp tag=TAG20130110T070737 comment=NONE
 13 channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04
 14 Finished backup at 10-JAN-13
 15 
  ----备份控制文件之后删除一个表空间----
 16 SQL> col tablespace_name for a20;
 17 SQL> col file_name for a45;
 18 SQL> select tablespace_name,file_name from dba_data_files;
 19 
 20 TABLESPACE_NAME      FILE_NAME
 21 ---------------- ---------------------------------------------
 22 USERS              /u01/app/oracle/oradata/yft/users01.dbf
 23 UNDOTBS1           /u01/app/oracle/oradata/yft/undotbs01.dbf
 24 SYSAUX             /u01/app/oracle/oradata/yft/sysaux01.dbf
 25 SYSTEM             /u01/app/oracle/oradata/yft/system01.dbf
 26 EXAMPLE            /u01/app/oracle/oradata/yft/example01.dbf
 27 YFT                /u01/app/oracle/oradata/yft/yft01.dbf
 28 LTB                /u01/app/oracle/oradata/ltb01.dbf
 29 
 30 7 rows selected.
 31 
 32 SQL> drop tablespace ltb including contents and datafiles;
 33 
 34 Tablespace dropped.
 35 
 36 SQL> select tablespace_name,file_name from dba_data_files;
 37 
 38 TABLESPACE_NAME      FILE_NAME
 39 ----------------- ---------------------------------------------
 40 USERS              /u01/app/oracle/oradata/yft/users01.dbf
 41 UNDOTBS1           /u01/app/oracle/oradata/yft/undotbs01.dbf
 42 SYSAUX             /u01/app/oracle/oradata/yft/sysaux01.dbf
 43 SYSTEM             /u01/app/oracle/oradata/yft/system01.dbf
 44 EXAMPLE            /u01/app/oracle/oradata/yft/example01.dbf
 45 YFT                /u01/app/oracle/oradata/yft/yft01.dbf
 46 
 47 6 rows selected.
 48 
  ----现在模拟控制文件损坏，无备用可用(除上面控制文件备份)情况：----
 49 SQL> shutdown abort;
 50 ORACLE instance shut down.
 51 
  ----删除控制文件----
 52 [oracle@yft ~]$ rm /u01/app/oracle/oradata/yft/control01.ctl
 53 [oracle@yft ~]$ rm /u01/app/oracle/flash_recovery_area/yft/control02.ctl
 54 
  ----启动，报ORA-00205错误----
 55 SQL> startup
 56 ORACLE instance started.
 57 
 58 Total System Global Area  330600448 bytes
 59 Fixed Size            1336344 bytes
 60 Variable Size          260049896 bytes
 61 Database Buffers       62914560 bytes
 62 Redo Buffers            6299648 bytes
 63 ORA-00205: error in identifying control file, check alert log for more info
 64 
  ----告警日志抛出：----
 65 ALTER DATABASE   MOUNT
 66 ORA-00210: cannot open the specified control file
 67 ORA-00202: control file: '/u01/app/oracle/flash_recovery_area/yft/control02.ctl'
 68 ORA-27037: unable to obtain file status
 69 Linux Error: 2: No such file or directory
 70 Additional information: 3
 71 ORA-00210: cannot open the specified control file
 72 ORA-00202: control file: '/u01/app/oracle/oradata/yft/control01.ctl'
 73 ORA-27037: unable to obtain file status
 74 Linux Error: 2: No such file or directory
 75 Additional information: 3
 76 ORA-205 signalled during: ALTER DATABASE   MOUNT...
 77 
  ----拿最近一次控制文件还原----
 78 RMAN> restore controlfile from '/u01/app/oracle/flash_recovery_area/YFT/backupset/2013_01_10/o1_mf_ncnnf_TAG
 79 
 80 Starting restore at 10-JAN-13
 81 using target database control file instead of recovery catalog
 82 allocated channel: ORA_DISK_1
 83 channel ORA_DISK_1: SID=20 device type=DISK
 84 
 85 channel ORA_DISK_1: restoring control file
 86 channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
 87 output file name=/u01/app/oracle/oradata/yft/control01.ctl
 88 output file name=/u01/app/oracle/flash_recovery_area/yft/control02.ctl
 89 Finished restore at 10-JAN-13
 90 
  ----打开控制文件----
 91 RMAN> mount database;
 92 
 93 database mounted
 94 released channel: ORA_DISK_1
 95 
  ----这时候查看v$datafile，会发现7号数据文件信息还在，注意我们在上面已经删除这些数据文件对应的表空间LTB的，但因为这个控制文件备份是删除操作之前的，所以它认为数据文件还在。
 96 SQL> col name for a60;
 97 SQL> select file#,name from v$datafile;
 98 
 99      FILE# NAME
100 ---------- ------------------------------------------------------------
101      1 /u01/app/oracle/oradata/yft/system01.dbf
102      2 /u01/app/oracle/oradata/yft/sysaux01.dbf
103      3 /u01/app/oracle/oradata/yft/undotbs01.dbf
104      4 /u01/app/oracle/oradata/yft/users01.dbf
105      5 /u01/app/oracle/oradata/yft/example01.dbf
106      6 /u01/app/oracle/oradata/yft/yft01.dbf
107      7 /u01/app/oracle/oradata/ltb01.dbf
108 
109 7 rows selected.
110 
  ----这时候直接回复数据库会报错----
111 RMAN> recover database;
112 
113 Starting recover at 10-JAN-13
114 Starting implicit crosscheck backup at 10-JAN-13
115 allocated channel: ORA_DISK_1
116 channel ORA_DISK_1: SID=20 device type=DISK
117 Crosschecked 2 objects
118 Finished implicit crosscheck backup at 10-JAN-13
119 
120 Starting implicit crosscheck copy at 10-JAN-13
121 using channel ORA_DISK_1
122 Finished implicit crosscheck copy at 10-JAN-13
123 
124 searching for all files in the recovery area
125 cataloging files...
126 cataloging done
127 
128 List of Cataloged Files
129 =======================
130 File Name: /u01/app/oracle/flash_recovery_area/YFT/backupset/2013_01_10/o1_mf_ncnnf_TAG20130110T070737_8gvy1vm_.bkp
131 
132 using channel ORA_DISK_1
133 RMAN-00571: ===========================================================
134 RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
135 RMAN-00571: ===========================================================
136 RMAN-03002: failure of recover command at 01/10/2013 07:13:02
137 RMAN-06094: datafile 7 must be restored
138 
  ----解决的方法是：将这些数据文件都脱机处理：----
139 SQL> alter database datafile 7 offline;
140 
141 Database altered.
142 
  ----接着从控制文件恢复数据库----
143 SQL> recover database using backup controlfile;
144 ORA-00279: change 979415 generated at 01/10/2013 07:03:21 needed for thread 1
145 ORA-00289: suggestion :
146 /u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_10/o1_mf_1_1_%u_.arc
147 ORA-00280: change 979415 for thread 1 is in sequence #1
148 
149 
150 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
151 auto
152 ORA-00308: cannot open archived log
153 '/u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_10/o1_mf_1_1_%u_.arc
154 '
155 ORA-27037: unable to obtain file status
156 Linux Error: 2: No such file or directory
157 Additional information: 3
158 
159 
160 ORA-00308: cannot open archived log
161 '/u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_10/o1_mf_1_1_%u_.arc
162 '
163 ORA-27037: unable to obtain file status
164 Linux Error: 2: No such file or directory
165 Additional information: 3
166 
167 
  ----注意，"recover database using backup controlfile;"务必在SQL*Plus上进行
  ----收到了ORA-00308和ORA-27037错误报告，我们发现它在找1号日志，这个文件我们从当前在线日志文件里找到。
168 SQL> select group#,status,sequence# from v$log;
169 
170     GROUP# STATUS         SEQUENCE#
171 ---------- ---------------- ----------
172      1 CURRENT             1
173      3 UNUSED             0
174      2 UNUSED             0
175 
176 SQL> select member from v$logfile where group#=1;
177 
178 MEMBER
179 --------------------------------------------------------------------------------
180 /u01/app/oracle/oradata/yft/redo01.log
181 
  ----再恢复一下----
182 SQL> recover database using backup controlfile;
183 ORA-00279: change 979415 generated at 01/10/2013 07:03:21 needed for thread 1
184 ORA-00289: suggestion :
185 /u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_10/o1_mf_1_1_%u_.arc
186 ORA-00280: change 979415 for thread 1 is in sequence #1
187 
188 
189 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
190 /u01/app/oracle/oradata/yft/redo01.log   ----指定redo的路径
191 Log applied.
192 Media recovery complete.
  ----这时候查看已经看不见删除的表空间对应的7号数据文件了----
193 SQL> select file#,name from v$datafile;
194 
195      FILE# NAME
196 ---------- ------------------------------------------------------------
197      1 /u01/app/oracle/oradata/yft/system01.dbf
198      2 /u01/app/oracle/oradata/yft/sysaux01.dbf
199      3 /u01/app/oracle/oradata/yft/undotbs01.dbf
200      4 /u01/app/oracle/oradata/yft/users01.dbf
201      5 /u01/app/oracle/oradata/yft/example01.dbf
202      6 /u01/app/oracle/oradata/yft/yft01.dbf
203 
204 6 rows selected.
205 
  ----如果想直接打开，会报ORA-01589错误----
206 SQL> alter database open;
207 alter database open
208 *
209 ERROR at line 1:
210 ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
211 
212 
  ----必须resetlogs打开库，open以后记得做一个全备份----
213 SQL> alter database open resetlogs;
214 
215 Database altered.