仅仅只有数据文件情况的恢复
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
本文链接地址: 仅仅只有数据文件情况的恢复
群中一网友遇到问题，windows 10g磁盘故障，非归档，无备份，在只有数据文件的情况下进行
恢复，我这里为其演示下一个大概的恢复过程：
—-拷贝一份整个测试库的数据文件
|
1 2 3 |
[ora10g@killdb oradata]$ cp -r roger recover [ora10g@killdb oradata]$ [ora10g@killdb oradata]$ cd recover/ |
—-只保留数据文件，删除controlfile和redo log
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
[ora10g@killdb recover]$ rm *.ctl [ora10g@killdb recover]$ rm redo*.log [ora10g@killdb recover]$ sqlplus "/as sysdba" SQL*Plus: Release 10.2.0.5.0 - Production on Tue Dec 4 00:27:14 2012 Copyright (c) 1982, 2010, Oracle. All Rights Reserved. Connected to an idle instance. SQL> startup nomount ORACLE instance started. Total System Global Area 167772160 bytes Fixed Size 1272600 bytes Variable Size 146801896 bytes Database Buffers 16777216 bytes Redo Buffers 2920448 bytes |
——create pfile for recover
|
1 2 3 4 5 6 7 8 |
SQL> !pwd /home/ora10g/oradata/recover SQL> create pfile='/tmp/recover_tmp.ora' from spfile; File created. SQL> shutdown abort; ORACLE instance shut down. |
——编辑pfile，修改相关目录信息
|
1 2 3 4 5 6 7 8 |
SQL> startup nomount pfile='/tmp/recover_tmp.ora'; ORACLE instance started. Total System Global Area 167772160 bytes Fixed Size 1272600 bytes Variable Size 146801896 bytes Database Buffers 16777216 bytes Redo Buffers 2920448 bytes |
—–手工重建controlfile
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
SQL> CREATE CONTROLFILE REUSE DATABASE "roger" RESETLOGS NOARCHIVELOG 2 MAXLOGFILES 50 3 MAXLOGMEMBERS 5 4 MAXDATAFILES 100 5 MAXINSTANCES 8 6 MAXLOGHISTORY 226 7 LOGFILE 8 GROUP 1 '/home/ora10g/oradata/recover/redo01.log' SIZE 50M, 9 GROUP 2 '/home/ora10g/oradata/recover/redo02.log' SIZE 50M, 10 GROUP 3 '/home/ora10g/oradata/recover/redo03.log' SIZE 50M 11 DATAFILE 12 '/home/ora10g/oradata/recover/system01.dbf', 13 '/home/ora10g/oradata/recover/roger01.dbf', 14 '/home/ora10g/oradata/recover/sqlt_01.dbf', 15 '/home/ora10g/oradata/recover/sysaux01.dbf', 16 '/home/ora10g/oradata/recover/undotbs2_01.dbf', 17 '/home/ora10g/oradata/recover/users01.dbf' 18 CHARACTER SET zhs16gbk; Control file created. SQL> |
—–后面是recover的步骤
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
SQL> recover database using backup controlfile ; ORA-00279: change 5690602 generated at 12/03/2012 05:43:05 needed for thread 1 ORA-00289: suggestion : /home/ora10g/archivelog/0001_1_44_792658815.dbf ORA-00280: change 5690602 for thread 1 is in sequence #44 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} auto ORA-00308: cannot open archived log '/home/ora10g/archivelog/0001_1_44_792658815.dbf' ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3 ORA-00308: cannot open archived log '/home/ora10g/archivelog/0001_1_44_792658815.dbf' ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3 SQL> SQL> SQL> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-01113: file 1 needs media recovery if it was restored from backup, or END BACKUP if it was not ORA-01110: data file 1: '/home/ora10g/oradata/recover/system01.dbf' SQL> SQL> shutdown immediate ORA-01109: database not open Database dismounted. ORACLE instance shut down. |
—-停库后，添加如下隐含参数：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 |
*._allow_resetlogs_corruption=TRUE *._allow_error_simulation=TRUE SQL> startup mount pfile='/tmp/recover_tmp.ora' ; ORACLE instance started. Total System Global Area 167772160 bytes Fixed Size 1272600 bytes Variable Size 146801896 bytes Database Buffers 16777216 bytes Redo Buffers 2920448 bytes Database mounted. SQL> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-01092: ORACLE instance terminated. Disconnection forced 此时alert log信息如下： Tue Dec 04 00:39:12 PST 2012 alter database open resetlogs RESETLOGS is being done without consistancy checks. This may result in a corrupted database. The database should be recreated. RESETLOGS after incomplete recovery UNTIL CHANGE 5690602 Tue Dec 04 00:39:12 PST 2012 Errors in file /home/ora10g/admin/roger/udump/roger_ora_32295.trc: ORA-00313: open failed for members of log group 1 of thread 1 ORA-00312: online log 1 thread 1: '/home/ora10g/oradata/recover/redo01.log' ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3 Tue Dec 04 00:39:14 PST 2012 Errors in file /home/ora10g/admin/roger/udump/roger_ora_32295.trc: ORA-00313: open failed for members of log group 2 of thread 1 ORA-00312: online log 2 thread 1: '/home/ora10g/oradata/recover/redo02.log' ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3 Tue Dec 04 00:39:15 PST 2012 Errors in file /home/ora10g/admin/roger/udump/roger_ora_32295.trc: ORA-00313: open failed for members of log group 3 of thread 1 ORA-00312: online log 3 thread 1: '/home/ora10g/oradata/recover/redo03.log' ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3 Tue Dec 04 00:39:16 PST 2012 Setting recovery target incarnation to 2 Tue Dec 04 00:39:16 PST 2012 Assigning activation ID 2505358118 (0x9554bb26) Thread 1 opened at log sequence 1 Current log# 1 seq# 1 mem# 0: /home/ora10g/oradata/recover/redo01.log Successful open of redo thread 1 Tue Dec 04 00:39:16 PST 2012 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set Tue Dec 04 00:39:16 PST 2012 SMON: enabling cache recovery Tue Dec 04 00:39:17 PST 2012 ORA-01555 caused by SQL statement below (SQL ID: 4krwuz0ctqxdt, SCN: 0x0000.0056d4f1): Tue Dec 04 00:39:17 PST 2012 select ctime, mtime, stime from obj$ where obj# = :1 Tue Dec 04 00:39:17 PST 2012 Errors in file /home/ora10g/admin/roger/udump/roger_ora_32295.trc: ORA-00704: bootstrap process failure ORA-00704: bootstrap process failure ORA-00604: error occurred at recursive SQL level 1 ORA-01555: snapshot too old: rollback segment number 14 with name "_SYSSMU14$" too small Error 704 happened during db open, shutting down database USER: terminating instance due to error 704 Instance terminated by USER, pid = 32295 ORA-1092 signalled during: alter database open resetlogs... |
下面来增进一下scn即可open，你可以用event，也可以用bbed去推进，我这里图方便，直接用event，如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
SQL> conn /as sysdba Connected to an idle instance. SQL> startup mount pfile='/tmp/recover_tmp.ora' ; ORACLE instance started. Total System Global Area 167772160 bytes Fixed Size 1272600 bytes Variable Size 146801896 bytes Database Buffers 16777216 bytes Redo Buffers 2920448 bytes Database mounted. SQL> alter session set events '10015 trace name ADJUST_SCN level 1'; Session altered. SQL> SQL> alter database open ; alter database open * ERROR at line 1: ORA-01113: file 1 needs media recovery if it was restored from backup, or END BACKUP if it was not ORA-01110: data file 1: '/home/ora10g/oradata/recover/system01.dbf' SQL> recover database; Media recovery complete. SQL> alter database open ; alter database open * ERROR at line 1: ORA-00607: Internal error occurred while making a change to a data block ORA-00600: internal error code, arguments: [4194], [39], [13], [], [], [], [], [] SQL> select open_mode from v$database; OPEN_MODE ---------- READ WRITE SQL> |
要解决这个ora-00600 4194 很easy，就不多说了，我这里直接将undo_management修改为manual然后将undotbs2 drop掉。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
SQL> conn /as sysdba Connected to an idle instance. SQL> startup mount pfile='/tmp/recover_tmp.ora' ; ORACLE instance started. Total System Global Area 167772160 bytes Fixed Size 1272600 bytes Variable Size 146801896 bytes Database Buffers 16777216 bytes Redo Buffers 2920448 bytes Database mounted. SQL> alter database open; Database altered. SQL> drop tablespace undotbs2 including contents and datafiles; Tablespace dropped. SQL> create undo tablespace undotbs1 datafile '/home/ora10g/oradata/recover/undotbs1.dbf' size 10m; Tablespace created. 最后再将参数还原即可，如下： SQL> shutdown abort; ORACLE instance shut down. SQL> startup mount pfile='/tmp/recover_tmp.ora' ; ORACLE instance started. Total System Global Area 167772160 bytes Fixed Size 1272600 bytes Variable Size 146801896 bytes Database Buffers 16777216 bytes Redo Buffers 2920448 bytes Database mounted. SQL> alter database open; Database altered. SQL> SQL> SQL> show parameter undo_ NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ _gc_undo_affinity boolean FALSE undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDOTBS1 |
由于我这里模拟之前是abort 关闭数据库，然后拷贝的，所以后面恢复出现了一些600错误，不过都是一些
非常常见的错误，当然，如果是该网友的情况，open数据库之后，最好是能将整个库exp出来然后重建。
整个恢复过程，没有丝毫难度，供该网友参考！
6 Responses to “仅仅只有数据文件情况的恢复”
[…] 仅仅只有数据文件情况的恢复 作者：lovewifelovelife 发表于2012-12-5 14:48:50 原文链接 阅读：0 评论：0 […]
roger大牛，上次碰到了这样的问题，没有搞定
感谢分享
roger很牛，生产库遇到这种情况，很悲催
受教 只知道要重建controlfile后面的步骤学习了
[…] 本文链接地址: 仅仅只有数据文件情况的恢复 […]
Leave a Reply
You must be logged in to post a comment.