php 微网站开发,吴江和城乡建设局网站,安徽响应式网站推荐,做服装必须看的十大网站天萃荷净Oracle研究中心案例分析#xff1a;运维DBA反映Oracle数据库处理非归档模式#xff0c;redo文件损坏常规修复无法正常open数据库。本站文章除注明转载外#xff0c;均为本站原创#xff1a; 转载自love wife love life —Roger 的Oracle技术博客本文链接地址…天萃荷净Oracle研究中心案例分析运维DBA反映Oracle数据库处理非归档模式redo文件损坏常规修复无法正常open数据库。本站文章除注明转载外均为本站原创 转载自love wife love life —Roger 的Oracle技术博客本文链接地址: One recover case!今天ML的群中女神和travel在纠结一个恢复的问题11.2.0.3版本非归档大概是rm掉current的log然后重建controlfille后恢复导致一系列问题并最终出现ora-600 2662错误虽然这个错误很常见但是你发现推进scn也是无法open感觉有点怪远程了女神的电脑操作不便最后将文件压缩传过来我在自己的vmware进行了恢复。由于环境的差异所以解压后我先进行rename file操作如下SQL SELECT name FROM v$datafile;NAME------------------------------------------------/u01/app/oracle/oradata/travel/system01.dbf........./u01/app/oracle/oradata/travel/users01.dbfSQL SELECT member FROM v$logfile;MEMBER-------------------------------------------------/u01/app/oracle/oradata/travel/redo03.log/u01/app/oracle/oradata/travel/redo02.log/u01/app/oracle/oradata/travel/redo01.logSQL ALTER DATABASE RENAME file /u01/app/oracle/oradata/travel/system01.dbf TO /home/oracle/travel/travel/system01.dbf;SQL ALTER DATABASE RENAME file /u01/app/oracle/oradata/travel/sysaux01.dbf TO /home/oracle/travel/travel/sysaux01.dbf;SQL ALTER DATABASE RENAME file /u01/app/oracle/oradata/travel/undotbs01.dbf TO /home/oracle/travel/travel/undotbs01.dbf;SQL ALTER DATABASE RENAME file /u01/app/oracle/oradata/travel/users01.dbf TO /home/oracle/travel/travel/users01.dbf;SQL ALTER DATABASE RENAME file /u01/app/oracle/oradata/travel/redo01.log TO /home/oracle/travel/travel/redo01.log;SQL ALTER DATABASE RENAME file /u01/app/oracle/oradata/travel/redo02.log TO /home/oracle/travel/travel/redo02.log;SQL ALTER DATABASE RENAME file /u01/app/oracle/oradata/travel/redo03.log TO /home/oracle/travel/travel/redo03.log;SQL SELECT name FROM v$datafile;NAME-------------------------------------------------/home/oracle/travel/travel/system01.dbf/home/oracle/travel/travel/sysaux01.dbf/home/oracle/travel/travel/undotbs01.dbf/home/oracle/travel/travel/users01.dbfSQL SELECT member FROM v$Logfile;MEMBER-------------------------------------------------/home/oracle/travel/travel/redo03.log/home/oracle/travel/travel/redo02.log/home/oracle/travel/travel/redo01.log此时,进行recover会报错ORA-16433如下SQL recover DATABASE;ORA-00283: recovery SESSION canceled due TO errorsORA-16433: The DATABASE must be opened IN READ/WRITE mode.SQL recover DATABASE USING backup controlfile until cancel;ORA-00283: recovery SESSION canceled due TO errorsORA-16433: The DATABASE must be opened IN READ/WRITE mode.SQL ALTER DATABASE OPEN;ALTER DATABASE OPEN*ERROR at line 1:ORA-01113: file 1 needs media recoveryORA-01110: DATA file 1: /home/oracle/travel/travel/system01.dbf关于该错误通过oerr命令可以大概了解一下其含义如下[oracle11gR2_primary ~]$ oerr ora 1643316433, 00000, The database must be opened in read/write mode.// *Cause: An attempt was made to open the database in read-only mode after an// operation that requires that the database be opened in read/write// mode.// *Action: Open the database in read/write mode. The database can then be// opened in read-only mode.通过这个错误我们可以得出一个信息数据库可以以read only模式打开换句话讲你查询如下几个值都是一样的select checkpoint_change# from v$database; --来自controlfileselect checkpoint_change# from v$datafile; --来自controlfileselect checkpoint_change# from v$datafile_orader; --来自datafile header其实通常来讲只要上述几个值一样那么我们的db应该都是可以直接open打开的然而这里却不行read only都不行我试过。然后下面就开始我的恢复工作。第一次尝试恢复---重建controlfileSQL startup nomount pfile/home/oracle/travel/travel/a.oraORACLE instance started.Total System Global Area 626327552 bytesFixed Size 2230952 bytesVariable Size 184550744 bytesDatabase Buffers 436207616 bytesRedo Buffers 3338240 bytesSQL CREATE CONTROLFILE REUSE DATABASE TRAVEL RESETLOGS NOARCHIVELOG2 MAXLOGFILES 163 MAXLOGMEMBERS 34 MAXDATAFILES 1005 MAXINSTANCES 86 MAXLOGHISTORY 2927 LOGFILE8 GROUP 1 /home/oracle/travel/travel/redo01.log SIZE 50M BLOCKSIZE 512,9 GROUP 2 /home/oracle/travel/travel/redo02.log SIZE 50M BLOCKSIZE 512,10 GROUP 3 /home/oracle/travel/travel/redo03.log SIZE 50M BLOCKSIZE 51211 -- STANDBY LOGFILE12 DATAFILE13 /home/oracle/travel/travel/system01.dbf,14 /home/oracle/travel/travel/sysaux01.dbf,15 /home/oracle/travel/travel/undotbs01.dbf,16 /home/oracle/travel/travel/users01.dbf17 CHARACTER SET AL32UTF818 ;Control file created.SQL开始开始进行recoverSQL alter database open resetlogs;alter database open resetlogs*ERROR at line 1:ORA-01194: file 1 needs more recovery to be consistentORA-01110: data file 1: /home/oracle/travel/travel/system01.dbfSQL recover database until cancel;ORA-00283: recovery session canceled due to errorsORA-01610: recovery using the BACKUP CONTROLFILE option must be doneSQL recover database using backup controlfile until cancel;ORA-00279: change 244977 generated at 01/19/2013 01:56:54 needed for thread 1ORA-00289: suggestion : /oracle/product/11.2.0/db_1/dbs/arch1_1_805082211.dbfORA-00280: change 244977 for thread 1 is in sequence #1Specify log: {suggested | filename | AUTO | CANCEL}cancelORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error belowORA-01194: file 1 needs more recovery to be consistentORA-01110: data file 1: /home/oracle/travel/travel/system01.dbfORA-01112: media recovery not started因为本身是非归档所以这个不完全恢复的步骤无非是为了后面可以进行open resetlog。然后停库加入隐含参数进行openSQL shutdown immediateORA-01109: DATABASE NOT OPENDATABASE dismounted.ORACLE instance shut down.SQL startup nomount pfile/home/oracle/travel/travel/b.oraORACLE instance started.Total System Global Area 626327552 bytesFixed SIZE 2230952 bytesVariable SIZE 184550744 bytesDATABASE Buffers 436207616 bytesRedo Buffers 3338240 bytesSQL ALTER DATABASE mountDATABASE altered.SQL ALTER DATABASE OPEN resetlogs;ALTER DATABASE OPEN resetlogs*ERROR at line 1:ORA-01092: ORACLE instance TERMINATED. Disconnection forcedORA-00600: internal error code, arguments: [2662], [0], [244985], [0],[244998], [4194432], [], [], [], [], [], []Process ID: 3641SESSION ID: 1 Serial NUMBER: 5此时alert log对应的错误如下Fri Dec 14 21:58:39 2012SMON: enabling cache recoveryErrors in file /oracle/diag/diag/rdbms/travel/travel/trace/travel_ora_3641.trc (incident4937):ORA-00600: internal error code, arguments: [2662], [0], [244985], [0], [244998], [4194432], [], [], [], [], [], []Incident details in: /oracle/diag/diag/rdbms/travel/travel/incident/incdir_4937/travel_ora_3641_i4937.trcFri Dec 14 21:58:43 2012Dumping diagnostic data in directory[cdmp_20121214215843], requested by (instance1, osid3641), summary[incident4937].Use ADRCI or Support Workbench to package the incident.See Note 411.1 at My Oracle Support for error and packaging details.Errors in file /oracle/diag/diag/rdbms/travel/travel/trace/travel_ora_3641.trc:ORA-00600: internal error code, arguments: [2662], [0], [244985], [0], [244998], [4194432], [], [], [], [], [], []Errors in file /oracle/diag/diag/rdbms/travel/travel/trace/travel_ora_3641.trc:ORA-00600: internal error code, arguments: [2662], [0], [244985], [0], [244998], [4194432], [], [], [], [], [], []Error 600 happened during db open, shutting down databaseUSER (ospid: 3641): terminating the instance due to error 600Instance terminated by USER, pid 3641ORA-1092 signalled during: alter database open resetlogs...opiodr aborting process unknown ospid (3641) as a result of ORA-1092甚至手工执行如下操作都无法open数据库如下SQL ALTER SESSION SET events 10015 trace name ADJUST_SCN level 14;SESSION altered.SQL ALTER DATABASE OPEN;ALTER DATABASE OPEN*ERROR at line 1:ORA-01113: file 1 needs media recoveryORA-01110: DATA file 1: /home/oracle/travel/travel/system01.dbf最后dunmp controlfile和CURRENT redo logfile发现low cache rba信息居然是最大值SQL oradebug setmypidStatement processed.SQL ALTER system SET events immediate trace name controlf level 4 ;System altered.SQL ALTER system dump logfile /home/oracle/travel/travel/redo01.log;SQL oradebug tracefile_name/oracle/diag/diag/rdbms/travel/travel/trace/travel_ora_4229.trc controlfile dump 片段***************************************************************************CHECKPOINT PROGRESS RECORDS***************************************************************************(size 8180, compat size 8180, section max 11, section in-use 0,last-recid 0, old-recno 0, last-recno 0)(extent 1, blkno 2, numrecs 11)THREAD #1 - status:0x2 flags:0x0 dirty:0low cache rba:(0xffffffff.ffffffff.ffff) on disk rba:(0x1.3.0) ---low cache rba为无穷大on disk scn: 0x0000.0003bcf7 12/14/2012 21:58:39resetlogs scn: 0x0000.0003bcf2 12/14/2012 21:58:36heartbeat: 802069494 mount id: 2869233386THREAD #2 - status:0x0 flags:0x0 dirty:0low cache rba:(0x0.0.0) on disk rba:(0x0.0.0)on disk scn: 0x0000.00000000 01/01/1988 00:00:00resetlogs scn: 0x0000.00000000 01/01/1988 00:00:00heartbeat: 0 mount id: 0THREAD #3 - status:0x0 flags:0x0 dirty:0low cache rba:(0x0.0.0) on disk rba:(0x0.0.0)on disk scn: 0x0000.00000000 01/01/1988 00:00:00resetlogs scn: 0x0000.00000000 01/01/1988 00:00:00heartbeat: 0 mount id: 0redo01.log dump 片段DUMP OF REDO FROM FILE /home/oracle/travel/travel/redo01.logOpcodes *.*RBAs: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffffSCNs: scn: 0x0000.00000000 thru scn: 0xffff.ffffffffTimes: creation thru eternityFILE HEADER:Compatibility Vsn 1866465280xb200000Db ID28722613440xab333ae0, Db NameTRAVELActivation ID28722925160xab33b4a4Control Seq2330xe9, File size1024000x19000File Number1, Blksiz512, File Type2 LOGdescrip:Thread 0001, Seq# 0000000001, SCN 0x00000003bcee-0xffffffffffffthread: 1 nab: 0xffffffff seq: 0x00000001 hws: 0x3 eot: 1 dis: 0resetlogs count: 0x2ffc9463 scn: 0x0000.0003bcee (244974)prev resetlogs count: 0x2ffc7a41 scn: 0x0000.0003bcea (244970)Low scn: 0x0000.0003bcee (244974) 01/19/2013 01:56:51Next scn: 0xffff.ffffffff 01/01/1988 00:00:00Enabled scn: 0x0000.0003bcee (244974) 01/19/2013 01:56:51Thread closed scn: 0x0000.0003bcee (244974) 01/19/2013 01:56:51Disk cksum: 0x3467 Calc cksum: 0x3467Terminal recovery stop scn: 0x0000.00000000Terminal recovery 01/01/1988 00:00:00Most recent redo scn: 0x0000.00000000Largest LWN: 0 blocksEnd-of-redo stream : NoUnprotected modeMiscellaneous flags: 0x800000Thread internal enable indicator: thr: 0, seq: 0 scn: 0x0000.00000000Zero blocks: 0Format ID is 2redo log key is 853c461da2eec7ed4b45ce75b8c27d7redo log key flag is 5Enabled redo threads: 1REDO RECORD - Thread:1 RBA: 0x000001.00000002.0010 LEN: 0x0070 VLD: 0x05SCN: 0x0000.0003bcf2 SUBSCN: 1 01/19/2013 01:56:54(LWN RBA: 0x000001.00000002.0010 LEN: 0001 NST: 0001 SCN: 0x0000.0003bcee) CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:0 OP:17.3 ENC:0END OF REDO DUMP可以看到实际上current redo的LWN RBA是对的为1.2.10是小于on disk rba (0×1.3.0)的。那么这里为什么会出现这个情况猜测可能是写紊乱了。到这里也就比较明白了仍然是conrolfile有问题。既然如此那么我直接将controlfile文件rm掉然后再次重建。如下SQL startup nomount pfile/home/oracle/travel/travel/a.oraORACLE instance started.Total System Global Area 626327552 bytesFixed Size 2230952 bytesVariable Size 184550744 bytesDatabase Buffers 436207616 bytesRedo Buffers 3338240 bytesSQL CREATE CONTROLFILE REUSE DATABASE TRAVEL RESETLOGS NOARCHIVELOG2 MAXLOGFILES 163 MAXLOGMEMBERS 34 MAXDATAFILES 1005 MAXINSTANCES 86 MAXLOGHISTORY 2927 LOGFILE8 GROUP 1 /home/oracle/travel/travel/redo01.log SIZE 50M BLOCKSIZE 512,9 GROUP 2 /home/oracle/travel/travel/redo02.log SIZE 50M BLOCKSIZE 512,10 GROUP 3 /home/oracle/travel/travel/redo03.log SIZE 50M BLOCKSIZE 51211 -- STANDBY LOGFILE12 DATAFILE13 /home/oracle/travel/travel/system01.dbf,14 /home/oracle/travel/travel/sysaux01.dbf,15 /home/oracle/travel/travel/undotbs01.dbf,16 /home/oracle/travel/travel/users01.dbf17 CHARACTER SET AL32UTF818 ;Control file created.SQL recover database until cancel using backup controlfile;ORA-00279: change 244985 generated at 12/14/2012 23:05:06 needed for thread 1ORA-00289: suggestion : /oracle/product/11.2.0/db_1/dbs/arch1_1_802047904.dbfORA-00280: change 244985 for thread 1 is in sequence #1Specify log: {suggested | filename | AUTO | CANCEL}AUTOORA-00308: cannot open archived log/oracle/product/11.2.0/db_1/dbs/arch1_1_802047904.dbfORA-27037: unable to obtain file statusLinux-x86_64 Error: 2: No such file or directoryAdditional information: 3ORA-00308: cannot open archived log/oracle/product/11.2.0/db_1/dbs/arch1_1_802047904.dbfORA-27037: unable to obtain file statusLinux-x86_64 Error: 2: No such file or directoryAdditional information: 3ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error belowORA-01194: file 1 needs more recovery to be consistentORA-01110: data file 1: /home/oracle/travel/travel/system01.dbfSQLSQLSQL select file#,CHECKPOINT_CHANGE#,LAST_CHANGE# from v$datafile order by 1;FILE# CHECKPOINT_CHANGE# LAST_CHANGE#---------- ------------------ ------------1 2449852 2449853 2449854 244985SQL select file#,CHECKPOINT_CHANGE# from v$datafile_header order by 1;FILE# CHECKPOINT_CHANGE#---------- ------------------1 2449852 2449853 2449854 244985SQL select CHECKPOINT_CHANGE# from v$database;CHECKPOINT_CHANGE#------------------0此时停库然后在pfile中加入隐含参数*._allow_resetlogs_corruptionTRUE*._allow_error_simulationTRUE接着再次进行mount并进行scn 推进SQL ALTER DATABASE OPEN resetlogs;ALTER DATABASE OPEN resetlogs*ERROR at line 1:ORA-01194: file 1 needs more recovery TO be consistentORA-01110: DATA file 1: /home/oracle/travel/travel/system01.dbfSQLSQL shutdown immediateORA-01109: DATABASE NOT OPENDATABASE dismounted.ORACLE instance shut down.SQL startup nomount pfile/home/oracle/travel/travel/b.oraORACLE instance started.Total System Global Area 626327552 bytesFixed Size 2230952 bytesVariable Size 184550744 bytesDatabase Buffers 436207616 bytesRedo Buffers 3338240 bytesSQL alter database mount2 ;Database altered.SQL alter session set events 10015 trace name ADJUST_SCN level 10;Session altered.SQL alter database open;alter database open*ERROR at line 1:ORA-01589: must use RESETLOGS or NORESETLOGS option for database openSQL alter database open noresetlogs;alter database open noresetlogs*ERROR at line 1:ORA-01588: must use RESETLOGS option for database openSQL alter database open resetlogs;Database altered.SQL show parameter nameNAME TYPE VALUE-------------Oracleо----------------------- ----------- ------------------------------db_file_name_convert stringdb_name string traveldb_unique_name string travelglobal_names boolean FALSEinstance_name string travellock_name_space stringlog_file_name_convert stringprocessor_group_name stringservice_names string travelSQL select open_mode from v$database;OPEN_MODE--------------------READ WRITESQL alter system switch logfile;System altered.这里说明一下是我的alert log里面还出现了数据字典不一致的问题因为毕竟是强制open的如下Sun Jan 20 00:25:34 2013Errors in file /oracle/diag/diag/rdbms/travel/travel/trace/travel_m000_4681.trc:ORA-25153: Temporary Tablespace is EmptySun Jan 20 00:25:38 2013Errors in file /oracle/diag/diag/rdbms/travel/travel/trace/travel_j004_4692.trc (incident21817):ORA-00600: internal error code, arguments: [kdsgrp1], [], [], [], [], [], [], [], [], [], [], []Incident details in: /oracle/diag/diag/rdbms/travel/travel/incident/incdir_21817/travel_j004_4692_i21817.trcSun Jan 20 00:25:43 2013Dumping diagnostic data in directory[cdmp_20130120002543], requested by (instance1, osid4692 (J004)), summary[incident21817].Use ADRCI or Support Workbench to package the incident.See Note 411.1 at My Oracle Support for error and packaging details.Sun Jan 20 00:25:47 2013这个ora-600错误很常见了通过是index信息不一致。其trace内容如下* kdsgrp1-1: *************************************************row 0x0040589e.4b continuation at0x0040589e.4b file# 1 block# 22686 slot 75 not foundKDSTABN_GET: 0 ..... ntab: 1curSlot: 75 ..... nrows: 75kdsgrp - dump CR block dba0x0040589eBlock header dump: 0x0040589eObject id on Block Yseg/obj: 0x12 csc: 0x00.3bca9 itc: 1 flg: O typ: 1 - DATAfsl: 0 fnx: 0x40589f ver: 0x01Itl Xid Uba Flag Lck Scn/Fsc0x01 0x0001.01a.000000a1 0x00c05e97.001d.0d --U- 1 fsc 0x0053.0003bcabbdba: 0x0040589edata_block_dump,data header at 0x7a26a044tsiz: 0x1fb8hsiz: 0xa8pbl: 0x7a26a04476543210flag--------ntab1nrow75frre-1fsbo0xa8...........seg/obj: 0×12 这是obj$对象属于bootstrap$核心对象涉及到这类的对象处理相对麻烦针对这类情况数据库open后都建议把数据导出然后重建库我这里就不在继续描述这个ora-600错误了我博客也有类似的例子。最后来个小节1. oracle通过系统checkpoint scndatafile checkpoint scn,start scn三者之间的比较来判断数据文件是否需要进行介质恢复.2. 在redo 线程打开的情况下即数据库open的情况下stop scn会被设置为无穷大当正常关闭时stop scn等于datafile scn.这里需要注意的是stop scn是存放在controlfile中的网上部分资料说是存在datafile header中这个说法是错误的。3. oracle在open之前是先判断是否进行介质恢复然后再是判断是否进行instance recovery。4. 关于4种scn的关系如下system checkpoint scn — 存放在controlfile中datafile checkpoint scn –存放在controlfile中start scn —存放在datafile header中stop scn —存放在controlfile中system scndatafile checkpoint scnstart scn这3种scn用于判断数据文件是否需要进行介质恢复。这3个相等这不需要介质恢复。如何这4个都相等那么就不需要进行实例恢复。stop scn是用于判断是否进行实例恢复的。5. 如果stop scn比其他的几个scn要大那么就需要进行instance recover需要进行扫描redo实例恢复的起点是low cache rba终点是redo log的最末端。--------------------------------------ORACLE-DBA----------------------------------------最权威、专业的Oracle案例资源汇总之【案例】Oracle报错ORA-16433非归档丢失redo无法启动的恢复过程
