数据库所在主机掉电, 重启主机和数据库后, 发现打不开数据库. 在alert.log里有一大堆类似下面的错误提示:
Tue Dec 9 09:47:12 2008 Errors in file /u01/app/oracle/admin/esuite/bdump/esuite_dbw0_4375.trc: ORA-01157: cannot identify/lock data file 89 - see DBWR trace file ORA-01110: data file 89: '/u01/app/oracle/oradata/ESUITE/datafile/o1_mf_emoa_ind_4kwz6n8y_.dbf' ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3 经确认提示的这些文件是不存在的. 从v$database查询, 该库数据库角色竟然是Physical Standby, 且Switchover_status是NOT ALLOWED, 咨询开发说是按照网上的提示以为是主备库从而做过主备转换: SQL> set lines 1024 SQL> select name,database_role,db_unique_name,open_mode,protection_mode,protection_level,switchover_status,supplemental_log_data_pk,supplemental_log_data_ui from v$database; NAME DATABASE_ROLE DB_UNIQUE_NAME OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL SWITCHOVER_STATUS SUP SUP --------- ---------------- ------------------------------ ---------- -------------------- -------------------- -------------------- --- --- ESUITE PRIMARY STANDBY esuite READ WRITE MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE NOT ALLOWED NO NO 具体细节也没有必要深究了, 赶紧把数据库打开才是最重要的. 先查看一下控制文件信息: $ sqlplus / as sysdba SQL> alter database backup controlfile to trace; SQL> exit $ cd $ORACLE_BASE/admin/$ORACLE_SID/udump $ ls -ltrh | tail -1 -rw-r----- 1 oracle oinstall 16K 12月 20 15:21 esuite_ora_16041.trc $ more esuite_ora_16041.trc 从该发现控制文件里指定的数据文件位置全和实际情况不一样, 说明当前数据库控制文件是错误的, 是开发人员做的主备切换用了备库的控制文件(可能原先在同一个主机上做过dataguard). 所以, 根据刚刚获得的trace文件信息, 和开发确认实际的数据文件存放位置, 重建控制文件并尝试恢复数据库: SQL> shutdown immediate; SQL> STARTUP NOMOUNT; SQL> CREATE CONTROLFILE REUSE DATABASE "ESUITE" RESETLOGS FORCE LOGGING ARCHIVELOG 2 MAXLOGFILES 16 3 MAXLOGMEMBERS 3 4 MAXDATAFILES 100 5 MAXINSTANCES 8 6 MAXLOGHISTORY 9344 7 LOGFILE 8 GROUP 1 ( 9 '/u01/app/oracle/oradata/ESUITE/onlinelog/redo01_1.log', 10 '/u01/app/oracle/flash_recovery_area/ESUITE/onlinelog/redo01_2.log' 11 ) SIZE 200M, 12 GROUP 2 ( 13 '/u01/app/oracle/oradata/ESUITE/onlinelog/redo02_1.log', 14 '/u01/app/oracle/flash_recovery_area/ESUITE/onlinelog/redo02_2.log' 15 ) SIZE 200M, 16 GROUP 3 ( 17 '/u01/app/oracle/oradata/ESUITE/onlinelog/redo03_1.log', 18 '/u01/app/oracle/flash_recovery_area/ESUITE/onlinelog/redo03_2.log' 19 ) SIZE 200M, 20 GROUP 4 ( 21 '/u01/app/oracle/oradata/ESUITE/onlinelog/redo04_1.log', 22 '/u01/app/oracle/flash_recovery_area/ESUITE/onlinelog/redo04_2.log' 23 ) SIZE 200M, 24 GROUP 5 ( 25 '/u01/app/oracle/oradata/ESUITE/onlinelog/redo05_1.log', 26 '/u01/app/oracle/flash_recovery_area/ESUITE/onlinelog/redo05_2.log' 27 ) SIZE 200M 28 -- STANDBY LOGFILE 29 DATAFILE 30 --'/u01/app/oracle/oradata/ESUITE/datafile/back.dbf', 31 '/u01/app/oracle/oradata/ESUITE/datafile/ecards_index01.dbf', 32 '/u01/app/oracle/oradata/ESUITE/datafile/ecards_user01.dbf', 33 '/u01/app/oracle/oradata/ESUITE/datafile/epassport_index01.dbf', 34 '/u01/app/oracle/oradata/ESUITE/datafile/epassport_user01.dbf', 35 '/u01/app/oracle/oradata/ESUITE/datafile/erating_index01.dbf', 36 '/u01/app/oracle/oradata/ESUITE/datafile/erating_user01.dbf', 37 '/u01/app/oracle/oradata/ESUITE/datafile/etoolkits_index01.dbf', 38 '/u01/app/oracle/oradata/ESUITE/datafile/etoolkits_user01.dbf', 39 '/u01/app/oracle/oradata/ESUITE/datafile/o1_mf_sysaux_3gyl1jbo_.dbf', 40 '/u01/app/oracle/oradata/ESUITE/datafile/o1_mf_system_3gyl1j9o_.dbf', 41 '/u01/app/oracle/oradata/ESUITE/datafile/o1_mf_users_3gyl1jgp_.dbf', 42 '/u01/app/oracle/oradata/ESUITE/datafile/undotbs1.dbf' 43 CHARACTER SET ZHS16GBK; Control file created. 然后按照trace文件里的提示尝试恢复数据库: SQL> RECOVER DATABASE USING BACKUP CONTROLFILE; ORA-00279: change 482012220 generated at 12/19/2008 19:12:21 needed for thread 1 ORA-00289: suggestion : /u02/esuite/arch1/294375_1_654110467.arc ORA-00280: change 482012220 for thread 1 is in sequence #294375 Specify log: {=suggested | filename | AUTO | CANCEL} ORA-00308: cannot open archived log '/u02/esuite/arch1/294375_1_654110467.arc' ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3 SQL> ALTER DATABASE OPEN RESETLOGS; ALTER DATABASE OPEN RESETLOGS * ERROR at line 1: ORA-01113: file 1 needs media recovery ORA-01110: data file 1: '/u01/app/oracle/oradata/ESUITE/datafile/o1_mf_system_3gyl1j9o_.dbf' SQL> recover datafile 1; ORA-00283: recovery session canceled due to errors ORA-01610: recovery using the BACKUP CONTROLFILE option must be done 发现怎么都起不来, 提示需要媒介恢复, 只好设置隐藏参数_allow_resetlogs_corruption, 不让数据库检测一致性: SQL> show parameter spfile; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string /u01/app/oracle/product/1020/d b_1/dbs/spfileesuite.ora SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile; System altered. SQL> ALTER DATABASE OPEN RESETLOGS; Database altered. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 1073741824 bytes Fixed Size 1223540 bytes Variable Size 255853708 bytes Database Buffers 809500672 bytes Redo Buffers 7163904 bytes Database mounted. Database opened. 数据库终于起来了, 由于重建了控制文件, 需重建临时表空间: SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/ESUITE/datafile/temp01.dbf' REUSE; Tablespace altered. 现在数据库正常了: SQL> set lines 1024 SQL> select name,database_role,db_unique_name,open_mode,protection_mode,protection_level,switchover_status,supplemental_log_data_pk,supplemental_log_data_ui from v$database; NAME DATABASE_ROLE DB_UNIQUE_NAME OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL SWITCHOVER_STATUS SUP SUP --------- ---------------- ------------------------------ ---------- -------------------- -------------------- -------------------- --- --- ESUITE PRIMARY esuite READ WRITE MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE SESSIONS ACTIVE NO NO 但是, 进入数据库后发现很多表还是无法查看数据, 而且提示表数据在类似 /u01/app/oracle/product/1020/db_1/dbs/MISSING00038 这样的数据文件里找不到, 但该文件不存在, 奇怪! 后确认其实还有很多数据文件在刚刚重建控制文件的时候没有加入, 所以说, 提前了解数据库是一件多么重要的事情. 幸好原先有一份巡检报告, 描述了各个数据文件的存放位置, 赶紧拿过来对一下. 尝试再重建控制文件, 把未加入的正确数据文件位置信息注册到控制文件里: SQL> shutdown immediate; SQL> STARTUP NOMOUNT; SQL> CREATE CONTROLFILE REUSE DATABASE "ESUITE" RESETLOGS FORCE LOGGING ARCHIVELOG 2 MAXLOGFILES 16 3 MAXLOGMEMBERS 3 4 MAXDATAFILES 100 5 MAXINSTANCES 8 6 MAXLOGHISTORY 9344 7 LOGFILE 8 GROUP 1 ( 9 '/u01/app/oracle/oradata/ESUITE/onlinelog/redo01_1.log', 10 '/u01/app/oracle/flash_recovery_area/ESUITE/onlinelog/redo01_2.log' 11 ) SIZE 200M, 12 GROUP 2 ( 13 '/u01/app/oracle/oradata/ESUITE/onlinelog/redo02_1.log', 14 '/u01/app/oracle/flash_recovery_area/ESUITE/onlinelog/redo02_2.log' 15 ) SIZE 200M, 16 GROUP 3 ( 17 '/u01/app/oracle/oradata/ESUITE/onlinelog/redo03_1.log', 18 '/u01/app/oracle/flash_recovery_area/ESUITE/onlinelog/redo03_2.log' 19 ) SIZE 200M, 20 GROUP 4 ( 21 '/u01/app/oracle/oradata/ESUITE/onlinelog/redo04_1.log', 22 '/u01/app/oracle/flash_recovery_area/ESUITE/onlinelog/redo04_2.log' 23 ) SIZE 200M, 24 GROUP 5 ( 25 '/u01/app/oracle/oradata/ESUITE/onlinelog/redo05_1.log', 26 '/u01/app/oracle/flash_recovery_area/ESUITE/onlinelog/redo05_2.log' 27 ) SIZE 200M 28 -- STANDBY LOGFILE 29 DATAFILE 30 '/u01/app/oracle/oradata/ESUITE/datafile/back.dbf', 31 '/u02/app/oracle/oradata/ESUITE/datafile/audit.dbf', 32 '/u02/app/oracle/oradata/ESUITE/datafile/eadmonitor_index01.dbf', 33 '/u02/app/oracle/oradata/ESUITE/datafile/eadmonitor_user01.dbf', 34 '/u01/app/oracle/oradata/ESUITE/datafile/ecards_index01.dbf', 35 '/u01/app/oracle/oradata/ESUITE/datafile/ecards_user01.dbf', 36 '/u02/app/oracle/oradata/ESUITE/datafile/ECHARGING_INDEX01.dbf', 37 '/u02/app/oracle/oradata/ESUITE/datafile/echarging_user01.dbf', 38 '/u02/app/oracle/oradata/ESUITE/datafile/ega垃圾广告ster_index012.dbf', 39 '/u02/app/oracle/oradata/ESUITE/datafile/ega垃圾广告ster_user01.dbf', 40 '/u02/app/oracle/oradata/ESUITE/datafile/ega垃圾广告ster_index01.dbf', 41 '/u02/app/oracle/oradata/ESUITE/datafile/ega垃圾广告ster_usero01.dbf', 42 '/u02/app/oracle/oradata/ESUITE/datafile/emarketing_index001.dbf', 43 '/u02/app/oracle/oradata/ESUITE/datafile/emarketing_user01.dbf', 44 '/u02/app/oracle/oradata/ESUITE/datafile/emoa_index01.dbf', 45 '/u02/app/oracle/oradata/ESUITE/datafile/emoa_user01.dbf', 46 '/u01/app/oracle/oradata/ESUITE/datafile/epassport_index01.dbf', 47 '/u01/app/oracle/oradata/ESUITE/datafile/epassport_user01.dbf', 48 '/u01/app/oracle/oradata/ESUITE/datafile/erating_index01.dbf', 49 '/u02/app/oracle/oradata/ESUITE/datafile/erating_log01.dbf', 50 '/u03/oradata/ESUITE/erating_log02.dbf', 51 '/home/oracle/oradata/ESUITE/datafile/erating_log01_10.dbf', 52 '/home/oracle/oradata/ESUITE/datafile/erating_log01_11.dbf', 53 '/home/oracle/oradata/ESUITE/datafile/erating_log01_12.dbf', 54 '/home/oracle/oradata/ESUITE/datafile/erating_log01_13.dbf', 55 '/home/oracle/oradata/ESUITE/datafile/erating_log01_14.dbf', 56 '/home/oracle/oradata/ESUITE/datafile/erating_log01_15.dbf', 57 '/home/oracle/oradata/ESUITE/datafile/erating_log01_16.dbf', 58 '/home/oracle/oradata/ESUITE/datafile/erating_log01_17.dbf', 59 '/home/oracle/oradata/ESUITE/datafile/erating_log01_18.dbf', 60 '/home/oracle/oradata/ESUITE/datafile/erating_log01_19.dbf', 61 '/home/oracle/oradata/ESUITE/datafile/erating_log01_1', 62 '/home/oracle/oradata/ESUITE/datafile/erating_log01_20.dbf', 63 '/home/oracle/oradata/ESUITE/datafile/erating_log01_21.dbf', 64 '/home/oracle/oradata/ESUITE/datafile/erating_log01_22.dbf', 65 '/home/oracle/oradata/ESUITE/datafile/erating_log01_23.dbf', 66 '/home/oracle/oradata/ESUITE/datafile/erating_log01_24.dbf', 67 '/home/oracle/oradata/ESUITE/datafile/erating_log01_25.dbf', 68 '/home/oracle/oradata/ESUITE/datafile/erating_log01_26.dbf', 69 '/home/oracle/oradata/ESUITE/datafile/erating_log01_27.dbf', 70 '/home/oracle/oradata/ESUITE/datafile/erating_log01_28.dbf', 71 '/home/oracle/oradata/ESUITE/datafile/erating_log01_29.dbf', 72 '/home/oracle/oradata/ESUITE/datafile/erating_log01_2', 73 '/home/oracle/oradata/ESUITE/datafile/erating_log01_30.dbf', 74 '/home/oracle/oradata/ESUITE/datafile/erating_log01_3', 75 '/home/oracle/oradata/ESUITE/datafile/erating_log01_4', 76 '/home/oracle/oradata/ESUITE/datafile/erating_log01_5', 77 '/home/oracle/oradata/ESUITE/datafile/erating_log01_6', 78 '/home/oracle/oradata/ESUITE/datafile/erating_log01_7', 79 '/home/oracle/oradata/ESUITE/datafile/erating_log01_8', 80 '/home/oracle/oradata/ESUITE/datafile/erating_log01_9', 81 '/u01/app/oracle/oradata/ESUITE/datafile/erating_user01.dbf', 82 '/u02/app/oracle/oradata/ESUITE/datafile/ereport_index01.dfb', 83 '/u02/app/oracle/oradata/ESUITE/datafile/ereport_user01.dbf', 84 '/u02/app/oracle/oradata/ESUITE/datafile/ESALES_index01.dbf', 85 '/u02/app/oracle/oradata/ESUITE/datafile/ESALES_USER01.dbf', 86 '/opt/oradata/eservice_index01', 87 '/opt/oradata/eservice_user01.dbf', 88 '/u01/app/oracle/oradata/ESUITE/datafile/etoolkits_index01.dbf', 89 '/u01/app/oracle/oradata/ESUITE/datafile/etoolkits_user01.dbf', 90 '/u02/app/oracle/oradata/ESUITE/datafile/perfstat.dbf', 91 '/u01/app/oracle/oradata/ESUITE/datafile/o1_mf_sysaux_3gyl1jbo_.dbf', 92 '/u01/app/oracle/oradata/ESUITE/datafile/o1_mf_system_3gyl1j9o_.dbf', 93 '/u01/app/oracle/oradata/ESUITE/datafile/temp01.dbf', 94 '/u01/app/oracle/oradata/ESUITE/datafile/undotbs1.dbf', 95 '/u01/app/oracle/oradata/ESUITE/datafile/o1_mf_users_3gyl1jgp_.dbf', 96 '/u02/app/oracle/oradata/ESUITE/datafile/wending_index01.dbf', 97 '/u02/app/oracle/oradata/ESUITE/datafile/wending_log01.dbf', 98 '/u02/app/oracle/oradata/ESUITE/datafile/wending_user01.dbf' 99 CHARACTER SET ZHS16GBK; CREATE CONTROLFILE REUSE DATABASE "ESUITE" RESETLOGS FORCE LOGGING ARCHIVELOG * ERROR at line 1: ORA-01503: CREATE CONTROLFILE failed ORA-01189: file is from a different RESETLOGS than previous files ORA-01110: data file 13: '/u01/app/oracle/oradata/ESUITE/datafile/ecards_index01.dbf' SQL> !oerr ora 1189 01189, 00000, "file is from a different RESETLOGS than previous files" // *Cause: In a CREATE CONTROLFILE command either this file or all previous // files were backups from before the last RESETLOGS. This may also // occur if this is a file that is offline and has been offline since // before the last RESETLOGS. // *Action: If the file was taken offline normal before the last RESETLOGS, // and is still offline, omit it from the CREATE CONTROLFILE command. // Rename and online the file after the database is open. Otherwise // find the version of the mentioned file consistent with the rest // of the datafiles and resubmit the command. 重建控制文件失败, 提示ORA-01189错误. 仍用原先的控制文件mount上数据库看一下当前数据文件状态信息: SQL> alter database mount; Database altered. SQL> select * from v$recover_file; FILE# ONLINE ONLINE_ ERROR CHANGE# TIME ---------- ------- ------- ----------------------------------------------------------------- ---------- ------------ 11 OFFLINE OFFLINE FILE MISSING 0 14 OFFLINE OFFLINE FILE MISSING 0 15 OFFLINE OFFLINE FILE MISSING 0 .... 56 rows selected. SQL> SELECT TS#,FILE#,NAME,STATUS,CHECKPOINT_CHANGE# FROM V$DATAFILE; TS# FILE# NAME STATUS CHECKPOINT_CHANGE# ---------- ---------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------ 0 1 /u01/app/oracle/oradata/ESUITE/datafile/o1_mf_system_3gyl1j9 SYSTEM 482025156 o_.dbf 1 2 /u01/app/oracle/oradata/ESUITE/datafile/undotbs1.dbf ONLINE 482025156 ... 68 rows selected. 看来确实是丢失了很多数据文件信息, 尝试rename操作: SQL> alter database rename file '/u01/app/oracle/product/1020/db_1/dbs/MISSING00014' to '/u02/app/oracle/oradata/ESUITE/datafile/ESALES_USER01.dbf'; Database altered. 后续把 v$recover_file 里丢失的数据文件信息(只要数据文件存在)一个个rename....要注意根据数据文件编号FILE#一一仔细对应, 再次说明有一份巡检报告是多么重要的事情啊. |
rename期间还遇到下面的的错误:
SQL> alter database rename file '/u01/app/oracle/product/1020/db_1/dbs/MISSING00027' to '/home/oracle/oradata/ESUITE/datafile/erating_log01_1';
alter database rename file '/u01/app/oracle/product/1020/db_1/dbs/MISSING00027' to '/home/oracle/oradata/ESUITE/datafile/erating_log01_1'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01141: error renaming data file 27 - new file '/home/oracle/oradata/ESUITE/datafile/erating_log01_1' not found
ORA-01111: name for data file 27 is unknown - rename to correct file
ORA-01110: data file 27: '/u01/app/oracle/product/1020/db_1/dbs/MISSING00027'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
后来发现是数据文件名写错了,后缀前竟然还有个空格, 命名不规范啊, 重来:
SQL> alter database rename file '/u01/app/oracle/product/1020/db_1/dbs/MISSING00027' to '/home/oracle/oradata/ESUITE/datafile/erating_log01_1 .dbf';
Database altered.
现在检查一下:
SQL> select * from v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- ------------
11 OFFLINE OFFLINE UNKNOWN ERROR 482011346 19-DEC-08
14 OFFLINE OFFLINE UNKNOWN ERROR 482011346 19-DEC-08
15 OFFLINE OFFLINE UNKNOWN ERROR 482011346 19-DEC-08
...
56 rows selected.
SQL> SELECT TS#,FILE#,NAME,STATUS,CHECKPOINT_CHANGE# FROM V$DATAFILE;
TS# FILE# NAME STATUS CHECKPOINT_CHANGE#
---------- ---------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------
0 1 /u01/app/oracle/oradata/ESUITE/datafile/o1_mf_system_3gyl1j9 SYSTEM 482025158
o_.dbf
1 2 /u01/app/oracle/oradata/ESUITE/datafile/undotbs1.dbf ONLINE 482025158
...
68 rows selected.
虽然v$recover_file的change#字段有了scn信息了, 但状态却是offline, 尝试online:
SQL> alter database datafile 11 online;
alter database datafile 11 online
*
ERROR at line 1:
ORA-01190: control file or data file 11 is from before the last RESETLOGS
ORA-01110: data file 11: '/u02/app/oracle/oradata/ESUITE/datafile/erating_log01.dbf'
提示ORA-01190错误, 也确实, 当前控制文件是上一次RESETLOGS的, 决定用ADJUST_SCN来调整SCN. 这个需要设置_allow_resetlogs_corruption参数, 前面曾经设置过就无需再设置.
SQL> ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME ADJUST_SCN LEVEL 1';
Session altered.
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP MOUNT;
SQL> RECOVER UNTIL CANCEL;
Media recovery complete.
完成介质恢复. 上面这一步很重要, 虽然不做这个操作也能打开数据库,但是我们是要用RESETLOGS来打开数据库,否则仍然将其它数据文件联机的时候仍然会报ORA-01189. 然后将数据文件状态联机:
SQL> ALTER DATABASE DATAFILE 11,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 ONLINE;
Database altered.
再以RESETLOGS方式打开数据库:
SQL> ALTER DATABASE OPEN RESETLOGS;
Database altered.
SQL> SELECT TS#,FILE#,NAME,STATUS,CHECKPOINT_CHANGE# FROM V$DATAFILE;
TS# FILE# NAME STATUS CHECKPOINT_CHANGE#
---------- ---------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------
0 1 /u01/app/oracle/oradata/ESUITE/datafile/o1_mf_system_3gyl1j9 SYSTEM 482029263
o_.dbf
1 2 /u01/app/oracle/oradata/ESUITE/datafile/undotbs1.dbf ONLINE 482029263
2 3 /u01/app/oracle/oradata/ESUITE/datafile/o1_mf_sysaux_3gyl1jb ONLINE 482029263
o_.dbf
...
68 rows selected.
SQL> select * from v$recover_file;
no rows selected
v$recover_file视图里已经没有需要恢复的数据文件了, 终于全盘搞定!!!只是可能丢了一小部分数据, 且先前的归档和备份会通通失效的.
refer: http://turner.itpub.net/post/2343/10686
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10648374/viewspace-615843/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/10648374/viewspace-615843/