1、1：数据库启动报错ORA-00333SQL> select status from v$instance;STATUS------------MOUNTEDSQL> alter database open;alter database open*ERROR at line 1:ORA-00333: redo log read error block 8194 count 8192SQL> select group#,sequence#,archived,status from v$log; GROUP# SEQUENCE# ARC STATUS---------- ---------- --- ---------------- 1 117247 NO CURRENT 3 117246 NO ACTIVE 2 117245 NO INACTIVESQL> select group#,member from v$logfile;GROUP# MEMBER------ -------------------------------------------------- 3 /home/oracle/app/oracle/datafile/orcl/redo03.log 2 /home/oracle/app/oracle/datafile/orcl/redo02.log 1 /home/oracle/app/oracle/datafile/orcl/redo01.log查看alert日志，group 1日志文件文件损坏：ORA-00312: online log 1 thread 1: '/home/oracle/app/oracle/datafile/orcl/redo01.log'ORA-27072: File I/O errorAdditional information: 4Additional information: 8194Additional information: 1084416可以看到损坏的是当前的联机日志。但是这个数据库没有备份，没有开归档，只能使用非常规恢复！
2、2：设置参数“_allow_resetlogs_corruption” 并重启数据库导mount查看隐含参数select a.ksppinm name, b.ksppstvl value, a.ksppdesc description from x$ksppi a, x$ksppcv bwhere a.indx = b.indx and a.ksppinm like '_allow%';_allow_resetlogs_corruption FALSE allow resetlogs even if it will cause corruption可以看到这个参数是 数据丢失情况下允许resetlogs 。修改参数默认值：SQL> Alter system set "_allow_resetlogs_corruption"=true scope=spfile;System altered.关闭数据库：SQL> shutdown immediate;ORA-01109: database not openDatabase dismounted.ORACLE instance shut down.启动数据库导mount状态：SQL> startup mount;ORACLE instance started.Total System Global Area 4175568896 bytesFixed Size 2234960 bytesVariable Size 1644168624 bytesDatabase Buffers 2516582400 bytesRedo Buffers 12582912 bytesDatabase mounted.
3、3：执行recover database until cancel;同时查看数据文件：SQL> col checkpoint_change# for 9999999999999999SQL> select file#,checkpoint_change# from v$datafile; FILE# CHECKPOINT_CHANGE#---------- ------------------ 1 14462303120211 2 14462303120211 3 14462303120211 4 14462303120211 5 14462303120211 6 14462303120211 7 14462303120211可以看到数据库的SCN是一致的。执行recover database until cancel命令：SQL> recover database until cancel; ORA-00279: change 14462303120211 generated at 09/17/2015 22:02:15 needed for thread 1ORA-00289: suggestion : /home/oracle/app/oracle/archive/orcl/1_117246_814995340.dbfORA-00280: change 14462303120211 for thread 1 is in sequence #117246Specify log: {<RET>=suggested | filename | AUTO | CANCEL}CANCEL ------->选择cancelORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below 这里警告：recover成功但是OPEN RESETLOGS会报错ORA-01194: file 1 needs more recovery to be consistentORA-01110: data file 1: '/home/oracle/app/oracle/datafile/orcl/system01.dbf'ORA-01112: media recovery not startedSQL> alter database open resetlogs; 执行open resetlogs果然报错alter database open resetlogs*ERROR at line 1:ORA-01092: ORACLE instance terminated. Disconnection forcedORA-00704: bootstrap process failureORA-00604: error occurred at recursive SQL level 2ORA-01555: snapshot too old: rollback segment number 2 with name "_SYSSMU2_111974964$" too smallProcess ID: 28092Session ID: 1522 Serial number: 3
4、4：创建pfile文件并启动数据库创建pfile文件，可以看到_allow_resetlogs_corruption参数为trueSQL> create pfile='/home/oracle/pfile.ora' from spfile;File created.关闭数据库：SQL> shutdown immediate;ORA-01109: database not openDatabase dismounted.ORACLE instance shut down.参数文件内容：orcl:/home/oracle@jkdb>cat pfile.oraorcl.__db_cache_size=2516582400orcl.__java_pool_size=16777216orcl.__large_pool_size=16777216orcl.__oracle_base='/home/oracle/app/oracle'#ORACLE_BASE set from environmentorcl.__pga_aggregate_target=1073741824orcl.__sga_target=4194304000orcl.__shared_io_pool_size=0orcl.__shared_pool_size=1593835520orcl.__streams_pool_size=16777216*._allow_resetlogs_corruption=true*.audit_file_dest='/home/oracle/app/oracle/admin/orcl/adump'*.audit_trail='NONE'*.compatible='11.2.0.0.0'*.control_files='/home/oracle/app/oracle/datafile/orcl/control01.ctl','/home/oracle/app/oracle/datafile/orcl/control02.ctl'*.db_block_size=8192*.db_domain=''*.db_name='orcl'*.diagnostic_dest='/home/oracle/app/oracle'*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'*.log_archive_dest_1='location=/home/oracle/app/oracle/archive/orcl'*.open_cursors=300*.pga_aggregate_target=1073741824*.processes=1500*.remote_login_passwordfile='EXCLUSIVE'*.sessions=1655*.sga_target=4194304000*.undo_tablespace='UNDOTBS1'orcl:/home/oracle@jkdb>sqlplus / as sysdbaSQL*Plus: Release 11.2.0.3.0 Production on Mon Sep 21 10:22:47 2015Copyright (c) 1982, 2011, Oracle. All rights reserved.Connected to an idle instance.SQL> startup pfile='/home/oracle/pfile.ora';ORACLE instance started.Total System Global Area 4175568896 bytesFixed Size 2234960 bytesVariable Size 1644168624 bytesDatabase Buffers 2516582400 bytesRedo Buffers 12582912 bytesDatabase mounted.ORA-03113: end-of-file on communication channelProcess ID: 30240Session ID: 1522 Serial number: 3SQL> select status from v$instance;STATUS------------OPENSQL> conn sa/ednnsERROR:ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [],[], [], [], []虽然数据处于open状态，但是用户连接报错，4193错误通常是因为恢复时redo与undo不一致所导致
5、5：关闭数据库并编辑pfile文件orcl:/home/oracle@jkdb>sqlplus / as sysdbaSQL*Plus: Release 11.2.0.3.0 Production on Mon Sep 21 10:45:03 2015Copyright (c) 1982, 2011, Oracle. All rights reserved.Connected.SQL> shutdown abortORACLE instance shut down.因为shutdown immediate 不能关闭，只能通过shutdown abort。修改参数文件：orcl:/home/oracle@jkdb>cat pfile.oraorcl.__db_cache_size=2516582400orcl.__java_pool_size=16777216orcl.__large_pool_size=16777216orcl.__oracle_base='/home/oracle/app/oracle'#ORACLE_BASE set from environmentorcl.__pga_aggregate_target=1073741824orcl.__sga_target=4194304000orcl.__shared_io_pool_size=0orcl.__shared_pool_size=1593835520orcl.__streams_pool_size=16777216*._allow_resetlogs_corruption=true*.audit_file_dest='/home/oracle/app/oracle/admin/orcl/adump'*.audit_trail='NONE'*.compatible='11.2.0.0.0'*.control_files='/home/oracle/app/oracle/datafile/orcl/control01.ctl','/home/oracle/app/oracle/datafile/orcl/control02.ctl'*.db_block_size=8192*.db_domain=''*.db_name='orcl'*.diagnostic_dest='/home/oracle/app/oracle'*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'*.log_archive_dest_1='location=/home/oracle/app/oracle/archive/orcl'*.open_cursors=300*.pga_aggregate_target=1073741824*.processes=1500*.remote_login_passwordfile='EXCLUSIVE'*.sessions=1655*.sga_target=4194304000#*.undo_tablespace='UNDOTBS1'*.undo_management='MANUAL'*.rollback_segments='SYSTEM'
6、6：启动数据库并创建undo表空间，然后关闭数据库SQL> startup mount pfile='/home/oracle/pfile.ora';这时候必须先启动到mount状态ORACLE instance started.Total System Global Area 4175568896 bytesFixed Size 2234960 bytesVariable Size 1644168624 bytesDatabase Buffers 2516582400 bytesRedo Buffers 12582912 bytesDatabase mounted.SQL> alter database open; Database altered.SQL> create undo tablespace undotbs2 datafile '/home/oracle/app/oracle/datafile/orcl/users02.dbf' size 100m autoextend on next 50m maxsize unlimited; 创建undo表空间Tablespace created.SQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL> exit
7、7：编辑pfile文件并重新启动数据库，然后创建spfile文件orcl:/home/oracle@jkdb>cat pfile.oraorcl.__db_cache_size=2516582400orcl.__java_pool_size=16777216orcl.__large_pool_size=16777216orcl.__oracle_base='/home/oracle/app/oracle'#ORACLE_BASE set from environmentorcl.__pga_aggregate_target=1073741824orcl.__sga_target=4194304000orcl.__shared_io_pool_size=0orcl.__shared_pool_size=1593835520orcl.__streams_pool_size=16777216*._allow_resetlogs_corruption=false*.audit_file_dest='/home/oracle/app/oracle/admin/orcl/adump'*.audit_trail='NONE'*.compatible='11.2.0.0.0'*.control_files='/home/oracle/app/oracle/datafile/orcl/control01.ctl','/home/oracle/app/oracle/datafile/orcl/control02.ctl'*.db_block_size=8192*.db_domain=''*.db_name='orcl'*.diagnostic_dest='/home/oracle/app/oracle'*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'*.log_archive_dest_1='location=/home/oracle/app/oracle/archive/orcl'*.open_cursors=300*.pga_aggregate_target=1073741824*.processes=1500*.remote_login_passwordfile='EXCLUSIVE'*.sessions=1655*.sga_target=4194304000*.undo_tablespace='UNDOTBS2'*.undo_management='AUTO'#*.rollback_segments='SYSTEM'这时候将_allow_resetlogs_corruption改为false，指定模式undo表空间为UNDOTBS2，并设置为自动管理SQL> startup pfile='/home/oracle/pfile.ora';ORACLE instance started.Total System Global Area 4175568896 bytesFixed Size 2234960 bytesVariable Size 1644168624 bytesDatabase Buffers 2516582400 bytesRedo Buffers 12582912 bytesDatabase mounted.Database opened.SQL> conn zxx/zxx 连接用户正常Connected.SQL> conn / as sysdbaConnected.SQL> create spfile from pfile='/home/oracle/pfile.ora';注意：虽然创建spfile文件，但是由于我碰到服务器又宕机，数据库不能通过spfile启动，只能每次通过pfile文件启动。像这种因为磁盘问题导致的数据库问题，最好将现有数据库导出来，导入到其他服务器上。
