Tag Archives: ORA-01578

数据库启动报 maximum number of processes () exceeded分析

Posted on by

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题：数据库启动报 maximum number of processes () exceeded分析

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

比较少遇到打开一个数据库报ORA-00020: maximum number of processes () exceeded,导致open数据库失败

SQL> alter database open;
alter database open
*
第 1 行出现错误:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00450: background process 'QMNC' did not start
ORA-00444: background process "QMNC" failed while starting
ORA-00020: maximum number of processes () exceeded
进程 ID: 6340
会话 ID: 109 序列号: 3

看这个报错应该是由于进程数达到了限制，导致在open数据库过程中启动后台进程QMNC失败,导致open过程失败,分析进程数不足的原因,查看启动时候的参数配置

Fri Dec 26 16:48:27 2025
Adjusting the default value of parameter parallel_max_servers
from 2560 to 135 due to the value of parameter processes (150)
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 3
Fri Dec 26 16:48:38 2025
Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on. 
IMODE=BR
ILAT =28
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options.
Windows NT Version V6.2  
CPU                 : 64 - type 8664, 32 Physical Cores
Process Affinity    : 0x0x0000000000000000
Memory (Avail/Total): Ph:82501M/130752M, Ph+PgF:103658M/150208M 
Using parameter settings in client-side pfile D:\PFILE.TXT on machine HISSERVER
System parameters with non-default values:
  processes                = 150
  sga_target               = 39296M
  control_files            = "D:\ORACLE64\ORADATA\ORA817\CONTROL01.CTL"
  control_files            = "D:\ORACLE64\RECOVERY_AREA\ORA817\CONTROL02.CTL"
  db_block_size            = 8192
  compatible               = "11.2.0.0.0"
  db_recovery_file_dest    = "D:\ORACLE64\recovery_area"
  db_recovery_file_dest_size= 4122M
  undo_tablespace          = "UNDOTBS1"
  remote_login_passwordfile= "EXCLUSIVE"
  db_domain                = ""
  dispatchers              = "(PROTOCOL=TCP) (SERVICE=ora817XDB)"
  audit_file_dest          = "D:\ORACLE64\ADMIN\ORA817\ADUMP"
  audit_trail              = "DB"
  db_name                  = "ora817"
  open_cursors             = 300
  pga_aggregate_target     = 13075M
  diagnostic_dest          = "D:\ORACLE64"
Fri Dec 26 16:49:10 2025
PMON started with pid=2, OS id=1992 
Fri Dec 26 16:49:10 2025
PSP0 started with pid=3, OS id=6744 
Fri Dec 26 16:49:12 2025
VKTM started with pid=4, OS id=7592 at elevated priority
Fri Dec 26 16:49:12 2025
GEN0 started with pid=5, OS id=7780 
Fri Dec 26 16:49:12 2025
DBRM started with pid=7, OS id=5372 
Fri Dec 26 16:49:12 2025
MMAN started with pid=9, OS id=6700 
Fri Dec 26 16:49:12 2025
DBW0 started with pid=10, OS id=1920 
Fri Dec 26 16:49:12 2025
DBW1 started with pid=11, OS id=4476 
Fri Dec 26 16:49:12 2025
DBW2 started with pid=12, OS id=6788 
Fri Dec 26 16:49:12 2025
DBW3 started with pid=13, OS id=4608 
Fri Dec 26 16:49:12 2025
DBW4 started with pid=14, OS id=6716 
Fri Dec 26 16:49:12 2025
DBW5 started with pid=15, OS id=6252 
Fri Dec 26 16:49:12 2025
DBW6 started with pid=16, OS id=7948 
Fri Dec 26 16:49:12 2025
DBW7 started with pid=17, OS id=5868 
Fri Dec 26 16:49:12 2025
LGWR started with pid=18, OS id=1644 
Fri Dec 26 16:49:12 2025
CKPT started with pid=19, OS id=5704 
Fri Dec 26 16:49:12 2025
SMON started with pid=20, OS id=4532 
Fri Dec 26 16:49:12 2025
RECO started with pid=21, OS id=380 
Fri Dec 26 16:49:12 2025
MMON started with pid=22, OS id=1316 
Fri Dec 26 16:49:12 2025
MMNL started with pid=23, OS id=6108 
Fri Dec 26 16:49:12 2025
DIAG started with pid=6, OS id=7904 
Fri Dec 26 16:49:12 2025
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
starting up 1 shared server(s) ...
ORACLE_BASE from environment = D:\ORACLE64

这里可以看出来数据库版本是11.2.0.3,processes配置为默认值150.进一步查看启动报错时候的alert日志

ALTER DATABASE RECOVER  database  
Media Recovery Start
 started logmerger process
Fri Dec 26 17:03:44 2025
Media Recovery failed with error 264
Slave exiting with ORA-283 exception
Errors in file D:\ORACLE64\diag\rdbms\ora817\ora817\trace\ora817_pr00_344.trc:
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required
Recovery Slave PR00 previously exited with exception 283
ORA-283 signalled during: ALTER DATABASE RECOVER  database  ...
alter database open
Fri Dec 26 17:03:48 2025
Thread 1 opened at log sequence 37301
  Current log# 2 seq# 37301 mem# 0: D:\ORACLE64\ORADATA\ORA817\REDO02.LOG
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Fri Dec 26 17:03:49 2025
SMON: enabling cache recovery
[6340] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:5143437 end:5143718 diff:281 (2 seconds)
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Fri Dec 26 17:03:49 2025
Database Characterset is ZHS16GBK
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
ORA-00020: maximum number of processes (150) exceeded
 ORA-20 errors will not be written to the alert log for
 the next minute. Please look at trace files to see all
 the ORA-20 errors.
Process P123 submission failed with error = 20
Starting background process QMNC
Process QMNC submission failed with error = 20
Errors in file D:\ORACLE64\diag\rdbms\ora817\ora817\trace\ora817_ora_6340.trc:
ORA-00444: 后台进程 "QMNC" 启动失败
ORA-00020: 超出最大进程数 ()
Errors in file D:\ORACLE64\diag\rdbms\ora817\ora817\trace\ora817_ora_6340.trc:
ORA-00450: 后台进程 'QMNC' 未启动
ORA-00444: 后台进程 "QMNC" 启动失败
ORA-00020: 超出最大进程数 ()
Errors in file D:\ORACLE64\diag\rdbms\ora817\ora817\trace\ora817_ora_6340.trc:
ORA-00450: 后台进程 'QMNC' 未启动
ORA-00444: 后台进程 "QMNC" 启动失败
ORA-00020: 超出最大进程数 ()
Error 450 happened during db open, shutting down database
USER (ospid: 6340): terminating the instance due to error 450
Fri Dec 26 17:03:50 2025
Errors in file D:\ORACLE64\diag\rdbms\ora817\ora817\trace\ora817_p036_8044.trc  (incident=4105):
ORA-01578: ORACLE data block corrupted (file # 9, block # 504643)
ORA-01110: data file 9: 'D:\ORACLE64\ORADATA\ORA817\HRPRUN_DATA05.DBF'
Incident details in: D:\ORACLE64\diag\rdbms\ora817\ora817\incident\incdir_4105\ora817_p036_8044_i4105.trc
Fri Dec 26 17:03:50 2025
Process m000 submission failed with error = 20
Fri Dec 26 17:04:07 2025
Instance terminated by USER, pid = 6340
ORA-1092 signalled during: alter database open...
opiodr aborting process unknown ospid (6340) as a result of ORA-1092
Fri Dec 26 17:04:09 2025
ORA-1092 : opitsk aborting process

看alert日志比较明显有Process P123 submission failed with error = 20提示,这里表示启动P123进程时候也遭遇到了进程数不足的限制导致该进程无法启动,而Pxxx进程是oracle的并行进程(而且在后面还报了由于坏块导致P036进程报错的信息),也就是说这个数据库在启动过程中启动了大量的并行进程(至少122个已经启动),这也就是说明了为什么150个进程不足的原因.在数据库完成实例恢复,完全open之前启动并行进程进行恢复的,很可能就是并行回滚操作.为了进一步验证猜想,把processes调整到1500,然后尝试启动库

Fri Dec 26 17:06:44 2025
alter database open
Beginning crash recovery of 1 threads
 parallel recovery started with 32 processes
Started redo scan
Completed redo scan
 read 20 KB redo, 5 data blocks need recovery
Started redo application at
 Thread 1: logseq 37301, block 18209
Recovery of Online Redo Log: Thread 1 Group 2 Seq 37301 Reading mem 0
  Mem# 0: D:\ORACLE64\ORADATA\ORA817\REDO02.LOG
Completed redo application of 0.02MB
Completed crash recovery at
 Thread 1: logseq 37301, block 18249, scn 327081807
 5 data blocks read, 5 data blocks written, 20 redo k-bytes read
Fri Dec 26 17:06:46 2025
Thread 1 advanced to log sequence 37302 (thread open)
Thread 1 opened at log sequence 37302
  Current log# 3 seq# 37302 mem# 0: D:\ORACLE64\ORADATA\ORA817\REDO03.LOG
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Fri Dec 26 17:06:47 2025
SMON: enabling cache recovery
[7104] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:5321812 end:5322031 diff:219 (2 seconds)
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is ZHS16GBK
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Fri Dec 26 17:06:49 2025
Errors in file D:\ORACLE64\diag\rdbms\ora817\ora817\trace\ora817_p091_5924.trc  (incident=5745):
ORA-01578: ORACLE data block corrupted (file # 9, block # 504643)
ORA-01110: data file 9: 'D:\ORACLE64\ORADATA\ORA817\HRPRUN_DATA05.DBF'
Incident details in: D:\ORACLE64\diag\rdbms\ora817\ora817\incident\incdir_5745\ora817_p091_5924_i5745.trc
Starting background process QMNC
Fri Dec 26 17:06:49 2025
QMNC started with pid=156, OS id=8012 
Errors in file D:\ORACLE64\diag\rdbms\ora817\ora817\trace\ora817_p091_5924.trc  (incident=5746):
ORA-01578: ORACLE data block corrupted (file # , block # )
Incident details in: D:\ORACLE64\diag\rdbms\ora817\ora817\incident\incdir_5746\ora817_p091_5924_i5746.trc
Errors in file D:\ORACLE64\diag\rdbms\ora817\ora817\trace\ora817_smon_7424.trc  (incident=4961):
ORA-01578: ORACLE data block corrupted (file # 9, block # 504643)
ORA-01110: data file 9: 'D:\ORACLE64\ORADATA\ORA817\HRPRUN_DATA05.DBF'
Incident details in: D:\ORACLE64\diag\rdbms\ora817\ora817\incident\incdir_4961\ora817_smon_7424_i4961.trc
Completed: alter database open
SMON: Restarting fast_start parallel rollback
Fri Dec 26 17:06:51 2025
Errors in file D:\ORACLE64\diag\rdbms\ora817\ora817\trace\ora817_p000_5472.trc  (incident=5009):
ORA-01578: ORACLE data block corrupted (file # 9, block # 504643)
ORA-01110: data file 9: 'D:\ORACLE64\ORADATA\ORA817\HRPRUN_DATA05.DBF'
Incident details in: D:\ORACLE64\diag\rdbms\ora817\ora817\incident\incdir_5009\ora817_p000_5472_i5009.trc
Errors in file D:\ORACLE64\diag\rdbms\ora817\ora817\trace\ora817_p000_5472.trc  (incident=5010):
ORA-01578: ORACLE data block corrupted (file # , block # )
Errors in file D:\ORACLE64\diag\rdbms\ora817\ora817\trace\ora817_smon_7424.trc  (incident=4962):
ORA-01578: ORACLE data block corrupted (file # 9, block # 504643)
ORA-01110: data file 9: 'D:\ORACLE64\ORADATA\ORA817\HRPRUN_DATA05.DBF'
Incident details in: D:\ORACLE64\diag\rdbms\ora817\ora817\incident\incdir_5010\ora817_p000_5472_i5010.trc

这次就比较清晰了,数据库在open过程中由于smon启动了并行回滚并遇到了坏块,导致P091进程和smon进程错误。因为数据库已经open,这个坏块导致无法回滚,直接对这个对象进行处理即可解决掉这个回滚报错问题.

解决ORA-00701: object necessary for warmstarting database cannot be altered不再是奢求

Posted on by

在Oracle使用的过程中,经常遭遇一些核心index出现异常，导致数据库无法正常使用,特别是在数据库open的情况下,因为出现这些bootstrap$中的部分index是无法通过设置event38003进行重建,从而导致数据库无法正常使用,最常见异常index 有:I_ICOL1, I_TS1, I_CDEF1, I_CDEF2, I_CDEF3, I_CDEF4, I_PROXY_DATA$, I_IND1, I_TS#, I_UNDO1, I_UNDO2, I_COBJ#, I_USER1, I_USER2, I_CON1, I_CON2, I_FILE1, I_FILE2, I_FILE#_BLOCK#, I_USER#, I_OBJ#, I_PROXY_ROLE_DATA$_1, I_PROXY_ROLE_DATA$_2, I_CCOL1, I_CCOL2, I_TAB1, I_COL1, I_COL2, I_COL3, I_OBJ1, I_OBJ2, I_OBJ3, I_OBJ4, I_OBJ5
重建的index异常报错有:ORA-00701,ORA-01410,ORA-08102,ORA-08103,ORA-01578,ORA-01499,ORA-00600等各类错误
这里假设Oracle数据库一个非常核心的表OBJ$中的I_OBJ1异常,现在准备重建它,大概步骤如下:
1. 假设异常index信息（I_OBJ1）

SQL> alter index sys.i_obj1 rebuild;
alter index sys.i_obj1 rebuild
*
ERROR at line 1:
ORA-00701: object necessary for warmstarting database cannot be altered

SQL> SELECT SQL_TEXT FROM BOOTSTRAP$ WHERE SQL_TEXT LIKE '%obj%';

SQL_TEXT
--------------------------------------------------------------------------------
CREATE UNIQUE INDEX I_OBJ1 ON OBJ$(OBJ#,OWNER#,TYPE#) PCTFREE 10 INITRANS 2 MAXT
RANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PC
TINCREASE 0 OBJNO 36 EXTENTS (FILE 1 BLOCK 336))

2. 创建获取异常index所属表

SQL>  select table_name from dba_indexes where owner='SYS' AND INDEX_NAME='I_OBJ1';

TABLE_NAME
--------------------------------------------------------------------------------
OBJ$

3. 获取obj$ ddl 语句

SQL> set long 10000
SQL> set pages 1000
SQL>  SELECT DBMS_METADATA.GET_DDL('TABLE','OBJ$','SYS') FROM DUAL;

DBMS_METADATA.GET_DDL('TABLE','OBJ$','SYS')
--------------------------------------------------------------------------------

  CREATE TABLE "SYS"."OBJ$"
   (    "OBJ#" NUMBER NOT NULL ENABLE,
        "DATAOBJ#" NUMBER,
        "OWNER#" NUMBER NOT NULL ENABLE,
        "NAME" VARCHAR2(128) NOT NULL ENABLE,
        "NAMESPACE" NUMBER NOT NULL ENABLE,
        "SUBNAME" VARCHAR2(128),
        "TYPE#" NUMBER NOT NULL ENABLE,
        "CTIME" DATE NOT NULL ENABLE,
        "MTIME" DATE NOT NULL ENABLE,
        "STIME" DATE NOT NULL ENABLE,
        "STATUS" NUMBER NOT NULL ENABLE,
        "REMOTEOWNER" VARCHAR2(128),
        "LINKNAME" VARCHAR2(128),
        "FLAGS" NUMBER,
        "OID$" RAW(16),
        "SPARE1" NUMBER,
        "SPARE2" NUMBER,
        "SPARE3" NUMBER,
        "SPARE4" VARCHAR2(1000),
        "SPARE5" VARCHAR2(1000),
        "SPARE6" DATE,
        "SIGNATURE" RAW(16),
        "SPARE7" NUMBER,
        "SPARE8" NUMBER,
        "SPARE9" NUMBER
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 16384 NEXT 106496 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM"

4. 获取obj$相关index 语句

SQL>  SELECT DBMS_METADATA.GET_DDL('INDEX',INDEX_NAME,'SYS') FROM dba_indexes where owner='SYS' AND TABLE_NAME='OBJ$';

DBMS_METADATA.GET_DDL('INDEX',INDEX_NAME,'SYS')
--------------------------------------------------------------------------------

  CREATE UNIQUE INDEX "SYS"."I_OBJ5" ON "SYS"."OBJ$" ("SPARE3", "NAME", "NAMESPA
CE", "TYPE#", "OWNER#", "REMOTEOWNER", "LINKNAME", "SUBNAME", "OBJ#")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM"


  CREATE INDEX "SYS"."I_OBJ4" ON "SYS"."OBJ$" ("DATAOBJ#", "TYPE#", "OWNER#")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM"


  CREATE INDEX "SYS"."I_OBJ3" ON "SYS"."OBJ$" ("OID$")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM"


  CREATE UNIQUE INDEX "SYS"."I_OBJ2" ON "SYS"."OBJ$" ("OWNER#", "NAME", "NAMESPA
CE", "REMOTEOWNER", "LINKNAME", "SUBNAME", "TYPE#", "SPARE3", "OBJ#")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 16384 NEXT 106496 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM"


  CREATE UNIQUE INDEX "SYS"."I_OBJ1" ON "SYS"."OBJ$" ("OBJ#", "OWNER#", "TYPE#")

  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM"

5. 创建obj$img表和相关index

SQL>   CREATE TABLE "SYS"."OBJ$IMG"
  2     (    "OBJ#" NUMBER NOT NULL ENABLE,
  3          "DATAOBJ#" NUMBER,
  4          "OWNER#" NUMBER NOT NULL ENABLE,
  5          "NAME" VARCHAR2(128) NOT NULL ENABLE,
  6          "NAMESPACE" NUMBER NOT NULL ENABLE,
  7          "SUBNAME" VARCHAR2(128),
  8          "TYPE#" NUMBER NOT NULL ENABLE,
  9          "CTIME" DATE NOT NULL ENABLE,
 10          "MTIME" DATE NOT NULL ENABLE,
 11          "STIME" DATE NOT NULL ENABLE,
 12          "STATUS" NUMBER NOT NULL ENABLE,
 13          "REMOTEOWNER" VARCHAR2(128),
 14          "LINKNAME" VARCHAR2(128),
 15          "FLAGS" NUMBER,
 16          "OID$" RAW(16),
 17          "SPARE1" NUMBER,
 18          "SPARE2" NUMBER,
 19          "SPARE3" NUMBER,
 20          "SPARE4" VARCHAR2(1000),
 21          "SPARE5" VARCHAR2(1000),
 22          "SPARE6" DATE,
 23          "SIGNATURE" RAW(16),
 24          "SPARE7" NUMBER,
 25          "SPARE8" NUMBER,
 26          "SPARE9" NUMBER
 27     ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 28   NOCOMPRESS LOGGING
 29    STORAGE(INITIAL 16384 NEXT 106496 MINEXTENTS 1 MAXEXTENTS 2147483645
 30    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
 31    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
 32    TABLESPACE "SYSTEM"
 33  ;

Table created.

SQL> 
SQL>   CREATE UNIQUE INDEX "SYS"."I_OBJ_5IMG" ON "SYS"."OBJ$IMG" ("SPARE3", "NAME", "NAMESPACE", "TYPE#", "OWNER#", "REMOTEOWNER", "LINKNAME", "SUBNAME", "OBJ#")
  2    PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  3    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  4    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  5    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  6    TABLESPACE "SYSTEM";

Index created.

SQL>   CREATE INDEX "SYS"."I_OBJ4IMG" ON "SYS"."OBJ$IMG" ("DATAOBJ#", "TYPE#", "OWNER#")
  2    PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  3    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  4    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  5    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  6    TABLESPACE "SYSTEM";   

Index created.

SQL>   CREATE INDEX "SYS"."I_OBJ3IMG" ON "SYS"."OBJ$IMG" ("OID$")
  2    PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  3    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  4    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  5    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  6    TABLESPACE "SYSTEM";

Index created.

SQL>   CREATE UNIQUE INDEX "SYS"."I_OBJ2IMG" ON "SYS"."OBJ$IMG" ("OWNER#", "NAME", "NAMESPACE", "REMOTEOWNER", "LINKNAME", "SUBNAME", "TYPE#", "SPARE3", "OBJ#")
  2    PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  3    STORAGE(INITIAL 16384 NEXT 106496 MINEXTENTS 1 MAXEXTENTS 2147483645
  4    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  5    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  6    TABLESPACE "SYSTEM";

Index created.

SQL>   CREATE UNIQUE INDEX "SYS"."I_OBJ1IMG" ON "SYS"."OBJ$IMG" ("OBJ#", "OWNER#", "TYPE#")
  2    PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  3    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  4    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  5    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  6    TABLESPACE "SYSTEM"
  7  ;

Index created.

6. 获取bootstrap$语句

SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','BOOTSTRAP$','SYS') FROM DUAL;

DBMS_METADATA.GET_DDL('TABLE','BOOTSTRAP$','SYS')
--------------------------------------------------------------------------------

  CREATE TABLE "SYS"."BOOTSTRAP$"
   (    "LINE#" NUMBER NOT NULL ENABLE,
        "OBJ#" NUMBER NOT NULL ENABLE,
        "SQL_TEXT" VARCHAR2(4000) NOT NULL ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 57344 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM"

7. 创建bootstrap$img表

SQL>   CREATE TABLE "SYS"."BOOTSTRAP$IMG"
  2     (    "LINE#" NUMBER NOT NULL ENABLE,
  3          "OBJ#" NUMBER NOT NULL ENABLE,
  4          "SQL_TEXT" VARCHAR2(4000) NOT NULL ENABLE
  5     ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  6   NOCOMPRESS LOGGING
  7    STORAGE(INITIAL 57344 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  8    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  9    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
 10    TABLESPACE "SYSTEM"
 11  /

Table created.

8.插入数据

insert into obj$img select * from obj$;
insert into bootstrap$img select * from bootstrap$;
commit;

9. 删除bootstrap$img对象名

delete from bootstrap$img where obj# in 
 (select obj# from obj$ 
  where name in ('OBJ$',  'I_OBJ1',  'I_OBJ2', 'I_OBJ3', 'I_OBJ4', 'I_OBJ5',
                 'BOOTSTRAP$'));
commit;

10. 插入新创建对象

insert into bootstrap$img select * from bootstrap$tmpstr;
commit;

11. 关闭数据库

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

12. bbed修改相关block值

[oracle@oel6 ~]$ bbed 
Password: 

BBED: Release 2.0.0.0.0 - Limited Production on Thu Aug 7 04:54:50 2014

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

************* !!! For Oracle Internal Use only !!! ***************

BBED> set filename '/u01/app/oracle/oradata/XIFENFEI/system01.dbf'
        FILENAME        /u01/app/oracle/oradata/XIFENFEI/system01.dbf

BBED> set blocksize 8192
        BLOCKSIZE       8192

BBED> set block 1
        BLOCK#          1

BBED> set mode edit
        MODE            Edit

BBED> set count 32
        COUNT           32

BBED> m /x e81d
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /u01/app/oracle/oradata/XIFENFEI/system01.dbf (0)
 Block: 1                Offsets:   196 to  227           Dba:0x00000000
------------------------------------------------------------------------
 e81d4000 12000000 00000000 41bad632 15bad632 01000000 00000000 00000000 

 <32 bytes per line>

BBED> m /x 5200 offset +2
 File: /u01/app/oracle/oradata/XIFENFEI/system01.dbf (0)
 Block: 1                Offsets:   198 to  229           Dba:0x00000000
------------------------------------------------------------------------
 52001200 00000000 000041ba d63215ba d6320100 00000000 00000000 00000000 

 <32 bytes per line>

BBED> d offset 96
 File: /u01/app/oracle/oradata/XIFENFEI/system01.dbf (0)
 Block: 1                Offsets:   196 to  227           Dba:0x00000000
------------------------------------------------------------------------
 e81d5200 12000000 00000000 41bad632 15bad632 01000000 00000000 00000000 

 <32 bytes per line>

BBED> sum apply
Check value for File 0, Block 1:
current = 0x5fbf, required = 0x5fbf

BBED> verify
DBVERIFY - Verification starting
FILE = /u01/app/oracle/oradata/XIFENFEI/system01.dbf
BLOCK = 1


DBVERIFY - Verification complete

Total Blocks Examined         : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing   (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0
Message 531 not found;  product=RDBMS; facility=BBED


BBED> exit

13. 启动数据库

SQL> startup
ORACLE instance started.

Total System Global Area  663945216 bytes
Fixed Size                  2291808 bytes
Variable Size             369100704 bytes
Database Buffers          289406976 bytes
Redo Buffers                3145728 bytes
Database mounted.
Database opened.

14. 验证I_OBJ1 index重建情况

SQL> SELECT OBJECT_ID,DATA_OBJECT_ID,CREATED FROM DBA_OBJECTS WHERE OBJECT_NAME='I_OBJ1';

 OBJECT_ID DATA_OBJECT_ID CREATED
---------- -------------- ---------
     77120          77120 06-AUG-14

SQL> !date  
Thu Aug  6 05:29:25 CST 2014

SQL> SELECT HEADER_FILE,HEADER_BLOCK FROM DBA_SEGMENTS WHERE SEGMENT_NAME='I_OBJ1';

HEADER_FILE HEADER_BLOCK
----------- ------------
          1        77296

SQL> SELECT SQL_TEXT FROM BOOTSTRAP$ WHERE SQL_TEXT LIKE '%obj%';

SQL_TEXT
-----------------------------------------------------------------------------------------------------------------------------------------------
create unique index i_obj1 on obj$(obj#, owner#, type#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 65536 NEXT 1048576 MINEXTENTS 1 M
AXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 77120 EXTENTS (FILE 1 BLOCK 77296))

create unique index i_obj2 on obj$(owner#, name, namespace,remoteowner, linkname, subname, type#, spare3, obj#) PCTFREE 10 INITRANS 2 MAXTRANS
255 STORAGE (  INITIAL 16384 NEXT 106496 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 77119 EXTENTS (FILE 1 BLOCK 77288))

create index i_obj3 on obj$(oid$) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 P
CTINCREASE 0 OBJNO 77118 EXTENTS (FILE 1 BLOCK 77280))

create index i_obj4 on obj$(dataobj#, type#, owner#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXE
XTENTS 2147483645 PCTINCREASE 0 OBJNO 77117 EXTENTS (FILE 1 BLOCK 77272))

create unique index i_obj5 on obj$(spare3, name, namespace, type#, owner#, remoteowner, linkname, subname, obj#) PCTFREE 10 INITRANS 2 MAXTRANS
 255 STORAGE (  INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 77116 EXTENTS (FILE 1 BLOCK 77264))

因为重建bootstrap$中的核心index是一个复杂而且风险非常大的事情,在你无法确定风险或者无法正常完成此类操作之时,如有需要请联系我
Phone:17813235971    Q Q:107644445    E-Mail:dba@xifenfei.com

system ORA-01578 坏块数据库恢复

Posted on by

半夜朋友打来求救电话,说xx医院his系统因为存储异常导致system坏块无法正常启动,因为是win平台无法使用bbed,无法修复system 坏块,请求技术支持
dbv检查system文件报坏块
1

对应具体地址为:file 1 block 39041和66738

判断控制文件异常
通过数据库恢复检查脚本(Oracle Database Recovery Check)脚本检测数据库发现控制文件明显异常(checkpoint scn)
2
3

尝试恢复数据库
4

因此对该库进行了不完全恢复,然后尝试resetlogs打开数据库,数据库报ORA-600 2662错误

Fri Aug 29 02:35:08 2014
alter database open resetlogs
Fri Aug 29 02:35:11 2014
RESETLOGS after complete recovery through change 451371288
Resetting resetlogs activation ID 1232269761 (0x4972f1c1)
Fri Aug 29 02:35:15 2014
Setting recovery target incarnation to 3
Fri Aug 29 02:35:15 2014
Assigning activation ID 1384652231 (0x52881dc7)
LGWR: STARTING ARCH PROCESSES
ARC0 started with pid=17, OS id=1084
Fri Aug 29 02:35:15 2014
ARC0: Archival started
ARC1: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC1 started with pid=18, OS id=2836
Fri Aug 29 02:35:15 2014
Thread 1 opened at log sequence 1
  Current log# 1 seq# 1 mem# 0: Z:\ORACLE\PRODUCT\10.2.0\ORCL\REDO01.LOG
Successful open of redo thread 1
Fri Aug 29 02:35:15 2014
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Fri Aug 29 02:35:15 2014
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
Fri Aug 29 02:35:15 2014
ARC0: Becoming the heartbeat ARCH
Fri Aug 29 02:35:15 2014
SMON: enabling cache recovery
Fri Aug 29 02:35:16 2014
Errors in file d:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_4824.trc:
ORA-00600: 内部错误代码, 参数: [2662], [0], [451371311], [0], [451374534], [8388977], [], []

Fri Aug 29 02:35:16 2014
Errors in file d:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_4824.trc:
ORA-00600: 内部错误代码, 参数: [2662], [0], [451371311], [0], [451374534], [8388977], [], []

Fri Aug 29 02:35:16 2014
Error 600 happened during db open, shutting down database
USER: terminating instance due to error 600
Fri Aug 29 02:35:17 2014
Errors in file d:\oracle\product\10.2.0\admin\orcl\bdump\orcl_smon_2928.trc:
ORA-00600: ??????, ??: [], [], [], [], [], [], [], []

Instance terminated by USER, pid = 4824
ORA-1092 signalled during: alter database open resetlogs...

ORA-600 2662 该错误解决思路很明显,推进scn,数据库报ORA-01578

Fri Aug 29 02:42:47 2014
SMON: enabling cache recovery
Fri Aug 29 02:42:47 2014
Successfully onlined Undo Tablespace 1.
Dictionary check beginning
Dictionary check complete
Fri Aug 29 02:42:49 2014
SMON: enabling tx recovery
Fri Aug 29 02:42:49 2014
Database Characterset is ZHS16GBK
Opening with internal Resource Manager plan
where NUMA PG = 1, CPUs = 16
replication_dependency_tracking turned off (no async multimaster replication found)
Fri Aug 29 02:42:50 2014
Errors in file d:\oracle\product\10.2.0\admin\orcl\bdump\orcl_smon_4804.trc:
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-01578: ORACLE 数据块损坏 (文件号 1, 块号 39041)
ORA-01110: 数据文件 1: 'Z:\ORACLE\PRODUCT\10.2.0\ORCL\SYSTEM01.DBF'

Fri Aug 29 02:42:50 2014
LOGSTDBY: Validating controlfile with logical metadata
Fri Aug 29 02:42:51 2014
LOGSTDBY: Validation complete
ORA-604 signalled during: alter database open...

使用event跳过坏块，启动数据库成功

Fri Aug 29 02:48:59 2014
SMON: enabling cache recovery
Fri Aug 29 02:49:00 2014
Successfully onlined Undo Tablespace 1.
Fri Aug 29 02:49:00 2014
SMON: enabling tx recovery
Fri Aug 29 02:49:00 2014
Database Characterset is ZHS16GBK
Opening with internal Resource Manager plan
where NUMA PG = 1, CPUs = 16
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=34, OS id=3096
Fri Aug 29 02:49:01 2014
db_recovery_file_dest_size of 4096 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Fri Aug 29 02:49:01 2014
Completed: alter database open

查询坏块对象
5
6

因为这些对象均不是核心对象,直接进行truncate然后插入老数据

后续还有大量错误修复

ORA-12012: error on auto execute of job 1
ORA-08102: index key not found, obj# 239, file 1, block 1674 (2)

ORA-00600: 内部错误代码, 参数: [kcbz_check_objd_typ], [0], [0], [1], [], [], [], []

ORA-00600: internal error code, arguments: [6749], [3], [12606796], [173], [], [], [], []

ORA-00600: 内部错误代码, 参数: [13013], [52898], [52895], [38288618], [44], [38288618], [17], []

ORA-00600: 内部错误代码, 参数: [13013], [5001], [52895], [38286476], [5], [38286476], [17], []

再次说明,数据库恢复不要看成多神秘,就是几个参数搞定,更加不要神化有坏块就bbed修复

一起ORA-600 3020故障恢复的大体思路

Posted on by

recover database 报ORA-600 3020

Recovery of Online Redo Log: Thread 1 Group 2 Seq 5729 Reading mem 0
  Mem# 0: E:\ORACLE\ORADATA\YYGDB\REDO02.LOG
Tue Aug 19 19:37:29 2014
Errors in file d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_pr0s_4296.trc  (incident=39403):
ORA-00600: internal error code, arguments: [3020], [3], [240], [12583152], [], [], [], [], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 3, block# 240)
ORA-10564: tablespace UNDOTBS1
ORA-01110: data file 3: 'E:\ORACLE\ORADATA\YYGDB\UNDOTBS01.DBF'
ORA-10560: block type 'KTU SMU HEADER BLOCK'
Incident details in: d:\oracle\diag\rdbms\yygdb\yygdb\incident\incdir_39403\yygdb_pr0s_4296_i39403.trc
ORA-00600: internal error code, arguments: [3020], [2], [90586], [8479194], [], [], [], [], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 2, block# 90586)
ORA-10564: tablespace SYSAUX
ORA-01110: data file 2: 'E:\ORACLE\ORADATA\YYGDB\SYSAUX01.DBF'
ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 6087
Errors in file d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_ora_12460.trc  (incident=39147):
ORA-00600: internal error code, arguments: [3020], [3], [240], [12583152], [], [], [], [], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 3, block# 240)
ORA-10564: tablespace UNDOTBS1
ORA-01110: data file 3: 'E:\ORACLE\ORADATA\YYGD
Incident details in: d:\oracle\diag\rdbms\yygdb\yygdb\incident\incdir_39147\yygdb_ora_12460_i39147.trc
Tue Aug 19 19:37:31 2014
Trace dumping is performing id=[cdmp_20140819193731]
Tue Aug 19 19:37:32 2014
Recovery Slave PR0S previously exited with an exception
Shutting down recovery slaves due to error 10877
Media Recovery failed with error 10877
ORA-283 signalled during: ALTER DATABASE RECOVER  database  ...

使用allow 1 corruption跳3020错误继续恢复

Tue Aug 19 19:38:53 2014
ALTER DATABASE RECOVER  database allow 1 corruption  
Media Recovery Start
Fast Parallel Media Recovery enabled
 ALLOW CORRUPTION option must use serial recovery
Warning: Datafile 10 (D:\ORACLE\PRODUCT\11.1.0\DB_1\ORADATA\SAMPLE\LAYOUT_DB.DBF) is offline during full 
database recovery and will not be recovered
Recovery of Online Redo Log: Thread 1 Group 2 Seq 5729 Reading mem 0
  Mem# 0: E:\ORACLE\ORADATA\YYGDB\REDO02.LOG
CORRUPTING BLOCK 240 OF FILE 3 AND CONTINUING RECOVERY
Errors in file d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_ora_12460.trc:
ORA-10567: Redo is inconsistent with data block (file# 3, block# 240)
ORA-10564: tablespace UNDOTBS1
ORA-01110: 数据文件 3: 'E:\ORACLE\ORADATA\YYGDB\UNDOTBS01.DBF'
ORA-10560: block type 'KTU SMU HEADER BLOCK'
Errors in file d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_ora_12460.trc  (incident=39148):
ORA-00600: 内部错误代码, 参数: [3020], [2], [90586], [8479194], [], [], [], [], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 2, block# 90586)
ORA-10564: tablespace SYSAUX
ORA-01110: 数据文件 2: 'E:\ORACLE\ORADATA\YYGDB\SYSAUX01.DBF'
ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 6087
Incident details in: d:\oracle\diag\rdbms\yygdb\yygdb\incident\incdir_39148\yygdb_ora_12460_i39148.trc
Media Recovery failed with error 600
ORA-283 signalled during: ALTER DATABASE RECOVER  database allow 1 corruption  ...
Tue Aug 19 19:38:56 2014
Trace dumping is performing id=[cdmp_20140819193856]
Tue Aug 19 19:38:59 2014
Sweep Incident[39148]: completed
Tue Aug 19 19:39:05 2014
ALTER DATABASE RECOVER  database allow 1 corruption  
Media Recovery Start
Fast Parallel Media Recovery enabled
 ALLOW CORRUPTION option must use serial recovery
Warning: Datafile 10 (D:\ORACLE\PRODUCT\11.1.0\DB_1\ORADATA\SAMPLE\LAYOUT_DB.DBF) is offline during full 
database recovery and will not be recovered
Recovery of Online Redo Log: Thread 1 Group 2 Seq 5729 Reading mem 0
  Mem# 0: E:\ORACLE\ORADATA\YYGDB\REDO02.LOG
CORRUPTING BLOCK 90586 OF FILE 2 AND CONTINUING RECOVERY
Errors in file d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_ora_12460.trc:
ORA-10567: Redo is inconsistent with data block (file# 2, block# 90586)
ORA-10564: tablespace SYSAUX
ORA-01110: 数据文件 2: 'E:\ORACLE\ORADATA\YYGDB\SYSAUX01.DBF'
ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 6087
Completed: ALTER DATABASE RECOVER  database allow 1 corruption

继续open数据库报ORA-01578错误,数据库无法open

Thread 1 opened at log sequence 5730
  Current log# 3 seq# 5730 mem# 0: E:\ORACLE\ORADATA\YYGDB\REDO03.LOG
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Tue Aug 19 19:39:34 2014
SMON: enabling cache recovery
Errors in file d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_ora_12460.trc  (incident=39149):
ORA-01578: ORACLE 数据块损坏 (文件号 3, 块号 240)
ORA-01110: 数据文件 3: 'E:\ORACLE\ORADATA\YYGDB\UNDOTBS01.DBF'
Incident details in: d:\oracle\diag\rdbms\yygdb\yygdb\incident\incdir_39149\yygdb_ora_12460_i39149.trc
Errors in file d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_ora_12460.trc  (incident=39150):
ORA-00353: 日志损坏接近块 520 更改 101455257 时间 08/18/2014 08:22:54
ORA-00312: 联机日志 1 线程 1: 'E:\ORACLE\ORADATA\YYGDB\REDO01.LOG'
ORA-01578: ORACLE 数据块损坏 (文件号 3, 块号 240)
ORA-01110: 数据文件 3: 'E:\ORACLE\ORADATA\YYGDB\UNDOTBS01.DBF'
Incident details in: d:\oracle\diag\rdbms\yygdb\yygdb\incident\incdir_39150\yygdb_ora_12460_i39150.trc
Errors in file d:\oracle\diag\rdbms\yygdb\yygdb\incident\incdir_39149\yygdb_ora_12460_i39149.trc:
ORA-00354: 损坏重做日志块标头
ORA-00353: 日志损坏接近块 520 更改 101455257 时间 08/18/2014 08:22:54
ORA-00312: 联机日志 1 线程 1: 'E:\ORACLE\ORADATA\YYGDB\REDO01.LOG'
ORA-01578: ORACLE 数据块损坏 (文件号 3, 块号 240)
ORA-01110: 数据文件 3: 'E:\ORACLE\ORADATA\YYGDB\UNDOTBS01.DBF'
Errors in file d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_ora_12460.trc  (incident=39151):
Error 1578 happened during db open, shutting down database
USER (ospid: 12460): terminating the instance due to error 1578
Tue Aug 19 19:39:41 2014
Errors in file d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_dbw3_18508.trc  (incident=38659):
ORA-01578: ORACLE data block corrupted (file # , block # )
Tue Aug 19 19:39:41 2014
Errors in file d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_dbw5_12160.trc  (incident=38675):
ORA-01578: ORACLE data block corrupted (file # , block # )
Tue Aug 19 19:39:42 2014
Instance terminated by USER, pid = 12460
ORA-1092 signalled during: alter database open...
ORA-1092 : opiodr aborting process unknown ospid (5084_12460)

由于undo 表空间有坏块，导致数据库open失败,尝试修改undo_management= “MANUAL”，继续启动数据库

Tue Aug 19 19:50:06 2014
alter database open
Beginning crash recovery of 1 threads
 parallel recovery started with 16 processes
Started redo scan
Completed redo scan
 3 redo blocks read, 0 data blocks need recovery
Started redo application at
 Thread 1: logseq 5731, block 2, scn 101497289
Recovery of Online Redo Log: Thread 1 Group 1 Seq 5731 Reading mem 0
  Mem# 0: E:\ORACLE\ORADATA\YYGDB\REDO01.LOG
Completed redo application of 0.00MB
Completed crash recovery at
 Thread 1: logseq 5731, block 5, scn 101517294
 0 data blocks read, 0 data blocks written, 3 redo blocks read
Tue Aug 19 19:50:08 2014
Thread 1 advanced to log sequence 5732 (thread open)
Thread 1 opened at log sequence 5732
  Current log# 2 seq# 5732 mem# 0: E:\ORACLE\ORADATA\YYGDB\REDO02.LOG
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Tue Aug 19 19:50:08 2014
SMON: enabling cache recovery
Dictionary check beginning
Tablespace 'TEMP' #3 found in data dictionary,
but not in the controlfile. Adding to controlfile.
File #3 is offline, but is part of an online tablespace.
data file 3: 'E:\ORACLE\ORADATA\YYGDB\UNDOTBS01.DBF'
File #10 is offline, but is part of an online tablespace.
data file 10: 'D:\ORACLE\PRODUCT\11.1.0\DB_1\ORADATA\SAMPLE\LAYOUT_DB.DBF'
File #11 is offline, but is part of an online tablespace.
data file 11: 'D:\ORACLE\PRODUCT\11.1.0\DB_1\ORADATA\SAMPLE\LAYOUT.DBF'
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
*********************************************************************
WARNING: The following temporary tablespaces contain no files.
         This condition can occur when a backup controlfile has
         been restored.  It may be necessary to add files to these
         tablespaces.  That can be done using the SQL statement:
 
         ALTER TABLESPACE <tablespace_name> ADD TEMPFILE
 
         Alternatively, if these temporary tablespaces are no longer
         needed, then they can be dropped.
           Empty temporary tablespace: TEMP
*********************************************************************
Database Characterset is ZHS16GBK
Opening with internal Resource Manager plan : on 4 X 8 NUMA system
**********************************************************
WARNING: Files may exists in db_recovery_file_dest
that are not known to the database. Use the RMAN command
CATALOG RECOVERY AREA to re-catalog any such files.
If files cannot be cataloged, then manually delete them
using OS command.
One of the following events caused this:
1. A backup controlfile was restored.
2. A standby controlfile was restored.
3. The controlfile was re-created.
4. db_recovery_file_dest had previously been enabled and
   then disabled.
**********************************************************
Hex dump of (file 1, block 7065) in trace file 
d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_ora_14296.trc
Corrupt block relative dba: 0x00401b99 (file 1, block 7065)
Fractured block found during buffer read
Data in bad block:
 type: 6 format: 2 rdba: 0x00401b99
 last change scn: 0x0000.060c1f83 seq: 0x1 flg: 0x06
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0xac3b0601
 check value in block header: 0x2e13
 computed block checksum: 0xa4ac
Reread of rdba: 0x00401b99 (file 1, block 7065) found same corrupted data
Errors in file d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_ora_14296.trc  (incident=42814):
ORA-01578: ORACLE 数据块损坏 (文件号 1, 块号 7065)
ORA-01110: 数据文件 1: 'E:\ORACLE\ORADATA\YYGDB\SYSTEM01.DBF'
Incident details in: d:\oracle\diag\rdbms\yygdb\yygdb\incident\incdir_42814\yygdb_ora_14296_i42814.trc
Errors in file d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_ora_14296.trc  (incident=42815):
ORA-01578: ORACLE 数据块损坏 (文件号 1, 块号 7065)
ORA-01110: 数据文件 1: 'E:\ORACLE\ORADATA\YYGDB\SYSTEM01.DBF'
Incident details in: d:\oracle\diag\rdbms\yygdb\yygdb\incident\incdir_42815\yygdb_ora_14296_i42815.trc
Tue Aug 19 19:50:12 2014
Trace dumping is performing id=[cdmp_20140819195012]
Tue Aug 19 19:50:12 2014
Sweep Incident[42814]: completed
Hex dump of (file 1, block 7065) in trace file 
d:\oracle\diag\rdbms\yygdb\yygdb\incident\incdir_42814\yygdb_m000_11592_i42814_a.trc
Corrupt block relative dba: 0x00401b99 (file 1, block 7065)
Fractured block found during validation
Data in bad block:
 type: 6 format: 2 rdba: 0x00401b99
 last change scn: 0x0000.060c1f83 seq: 0x1 flg: 0x06
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0xac3b0601
 check value in block header: 0x2e13
 computed block checksum: 0xa4ac
Reread of blocknum=7065, file=E:\ORACLE\ORADATA\YYGDB\SYSTEM01.DBF. found same corrupt data
Reread of blocknum=7065, file=E:\ORACLE\ORADATA\YYGDB\SYSTEM01.DBF. found same corrupt data
Reread of blocknum=7065, file=E:\ORACLE\ORADATA\YYGDB\SYSTEM01.DBF. found same corrupt data
Reread of blocknum=7065, file=E:\ORACLE\ORADATA\YYGDB\SYSTEM01.DBF. found same corrupt data
Reread of blocknum=7065, file=E:\ORACLE\ORADATA\YYGDB\SYSTEM01.DBF. found same corrupt data
Hex dump of (file 1, block 7065) in trace file 
d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_ora_14296.trc
Corrupt block relative dba: 0x00401b99 (file 1, block 7065)
Fractured block found during buffer read
Data in bad block:
 type: 6 format: 2 rdba: 0x00401b99
 last change scn: 0x0000.060c1f83 seq: 0x1 flg: 0x06
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0xac3b0601
 check value in block header: 0x2e13
 computed block checksum: 0xa4ac
Reread of rdba: 0x00401b99 (file 1, block 7065) found same corrupted data
Corrupt Block Found
         TSN = 0, TSNAME = SYSTEM
         RFN = 1, BLK = 7065, RDBA = 4201369
         OBJN = 1164, OBJD = 1164, OBJECT = SYS_FBA_BARRIERSCN, SUBOBJECT = 
         SEGMENT OWNER = SYS, SEGMENT TYPE = Table Segment
Errors in file d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_ora_14296.trc  (incident=42816):
ORA-01578: ORACLE 数据块损坏 (文件号 1, 块号 7065)
ORA-01110: 数据文件 1: 'E:\ORACLE\ORADATA\YYGDB\SYSTEM01.DBF'
Incident details in: d:\oracle\diag\rdbms\yygdb\yygdb\incident\incdir_42816\yygdb_ora_14296_i42816.trc
Trace dumping is performing id=[cdmp_20140819195014]
Errors in file d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_ora_14296.trc  (incident=42817):
Starting background process FBDA
Tue Aug 19 19:50:18 2014
FBDA started with pid=86, OS id=17700 
replication_dependency_tracking turned off (no async multimaster replication found)
Errors in file d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_fbda_17700.trc  (incident=42910):
ORA-01578: ORACLE data block corrupted (file # 1, block # 7065)
ORA-01110: data file 1: 'E:\ORACLE\ORADATA\YYGDB\SYSTEM01.DBF'
Trace dumping is performing id=[cdmp_20140819195018]
Errors in file d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_fbda_17700.trc  (incident=42911):
ORA-01578: ORACLE data block corrupted (file # 1, block # 7065)
ORA-01110: data file 1: 'E:\ORACLE\ORADATA\YYGDB\SYSTEM01.DBF'
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
ORA-604 signalled during: alter database open...

数据库不完全open成功，报了604错误，通过分析undo$，直接使用_offline_rollback_segments屏蔽了status$=5的回滚段,数据库open正常,因为system有大量坏块,幸运的是使用exp导出来几个业务用户的表数据全部OK.
数据库备份重于一切,别寄希望数据库非常规恢复

关于blockrecover 解决坏块相关测试与总结

Posted on by

悲剧的客户因为IBM p系列小机更换电源导致主机直接掉电,起来后发现数据库出现不少坏块,而且还有部分坏块中含有回滚事务,导致alert日志一直报smon回滚遇到坏块错误，该数据库版本是9.2.0.8 RAC,根据客户的备份情况,为了减少对业务的影响,决定使用blockrecover对其处理.这里通过10g数据库大概模拟出现含事务坏块的情况以及处理过程,重现了我们在处理的时候不确定的一些知识.
创建测试表

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> conn /as sysdba
Connected.
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/oracle/oradata/XFF/archivelog
Oldest online log sequence     231
Next log sequence to archive   233
Current log sequence           233
SQL> conn chf/xifenfei
Connected.
SQL> create table t_xifenfei
  2  as
  3  select * from dba_objects where rownum<10;

Table created.

SQL> select rowid,
  2  dbms_rowid.rowid_relative_fno(rowid) rel_fno,
  3  dbms_rowid.rowid_block_number(rowid) block
  4  from chf.t_xifenfei;

ROWID                 REL_FNO      BLOCK
------------------ ---------- ----------
AAANIqAAEAAAAAcAAA          4         28
AAANIqAAEAAAAAcAAB          4         28
AAANIqAAEAAAAAcAAC          4         28
AAANIqAAEAAAAAcAAD          4         28
AAANIqAAEAAAAAcAAE          4         28
AAANIqAAEAAAAAcAAF          4         28
AAANIqAAEAAAAAcAAG          4         28
AAANIqAAEAAAAAcAAH          4         28
AAANIqAAEAAAAAcAAI          4         28

9 rows selected.

当前的seq是233(也就是说我在233归档上创建了t_xienfei表)

dbv检查block

[oracle@xifenfei ~]$ dbv file='/u01/oracle/oradata/XFF/users01.dbf'

DBVERIFY: Release 10.2.0.4.0 - Production on Sun Jan 20 22:16:16 2013

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

DBVERIFY - Verification starting : FILE = /u01/oracle/oradata/XFF/users01.dbf


DBVERIFY - Verification complete

Total Pages Examined         : 1280
Total Pages Processed (Data) : 904
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 38
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 44
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 294
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Highest block SCN            : 3224018224 (2860.3224018224)

证明无任何坏块

切换归档

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/oracle/oradata/XFF/archivelog
Oldest online log sequence     233
Next log sequence to archive   235
Current log sequence           235

现在已经切换seq到235

rman备份我们需要测试block(file 4 block 28)对应的数据文件

RMAN>  backup datafile 4 format '/u01/oracle/oradata/xff_4.rman';

具体见:rman制造坏块,bbed修复坏块

模拟数据库进行其他操作

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/oracle/oradata/XFF/archivelog
Oldest online log sequence     235
Next log sequence to archive   237
Current log sequence           237
SQL> conn chf/xifenfei
Connected.
SQL> create table t_xff 
  2  as
  3  select * from dba_objects;

Table created.

SQL> alter system switch logfile;

System altered.

主要是为了模拟对其他block操作,对于block 28的恢复影响

对block 28进行操作

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/oracle/oradata/XFF/archivelog
Oldest online log sequence     237
Next log sequence to archive   239
Current log sequence           239
SQL> update chf.t_xifenfei set object_name='www.orasos.com';

9 rows updated.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

在seq为239的时候对block 28进行了一次update操作

模拟其他业务操作

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/oracle/oradata/XFF/archivelog
Oldest online log sequence     240
Next log sequence to archive   242
Current log sequence           242
SQL> delete from chf.t_xff ;

50491 rows deleted.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/oracle/oradata/XFF/archivelog
Oldest online log sequence     242
Next log sequence to archive   244
Current log sequence           244
SQL>  alter system switch logfile;  

System altered.

SQL> /

System altered.

这里可以知道在seq为246的时候做了备份归档操作

备份归档操作

RMAN> backup archivelog all format '/u01/oracle/oradata/xff_arch_%U' delete input;

模拟继续操作

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/oracle/oradata/XFF/archivelog
Oldest online log sequence     248
Next log sequence to archive   250
Current log sequence           250

SQL> update chf.t_xifenfei set object_name='www.orasos.com' where rownum<5;

4 rows updated.

这里可以发现,在seq为250的时候我们再次对block 28进行了操作

使用rman制造坏块

RMAN> BLOCKRECOVER DATAFILE 4 block 28 clear;

dbv检查坏块

[oracle@xifenfei ~]$ dbv file='/u01/oracle/oradata/XFF/users01.dbf'

DBVERIFY: Release 10.2.0.4.0 - Production on Sun Jan 20 23:01:24 2013

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

DBVERIFY - Verification starting : FILE = /u01/oracle/oradata/XFF/users01.dbf
Page 28 is influx - most likely media corrupt
Corrupt block relative dba: 0x0100001c (file 4, block 28)
Fractured block found during dbv: 
Data in bad block:
 type: 6 format: 2 rdba: 0x0100001c
 last change scn: 0x0b2c.c02ab081 seq: 0x1 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0xb0c4a6ea
 check value in block header: 0x393f
 computed block checksum: 0xc917



DBVERIFY - Verification complete

Total Pages Examined         : 1280
Total Pages Processed (Data) : 903
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 38
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 47
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 291
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 1
Highest block SCN            : 3224022228 (2860.3224022228)

强制kill数据库

[oracle@xifenfei ~]$ ps -ef|grep pmon
oracle    9744  9638  0 23:03 pts/1    00:00:00 grep pmon
oracle   32156     1  0 14:17 ?        00:00:10 ora_pmon_XFF
[oracle@xifenfei ~]$ kill -9 32156
[oracle@xifenfei ~]$ ps -ef|grep pmon
oracle    9751  9638  0 23:03 pts/1    00:00:00 grep pmon

为了模拟含事务的block出现坏块

启动数据库

SQL> startup mount
ORACLE instance started.

Total System Global Area  306184192 bytes
Fixed Size                  1267164 bytes
Variable Size             109054500 bytes
Database Buffers          188743680 bytes
Redo Buffers                7118848 bytes
Database mounted.
SQL> ALTER DATABASE OPEN;

Database altered.

数据库启动正常

查询坏块

SQL> select count(*) from chf.t_xifenfei;
select count(*) from chf.t_xifenfei
                         *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 28)
ORA-01110: data file 4: '/u01/oracle/oradata/XFF/users01.dbf'

alert日志

Sun Jan 20 23:04:37 2013
SMON: enabling tx recovery
Sun Jan 20 23:04:37 2013
Database Characterset is ZHS16GBK
Sun Jan 20 23:04:37 2013
Hex dump of (file 4, block 28) in trace file /u01/oracle/admin/XFF/bdump/xff_smon_9775.trc
Corrupt block relative dba: 0x0100001c (file 4, block 28)
Fractured block found during buffer read
Data in bad block:
 type: 6 format: 2 rdba: 0x0100001c
 last change scn: 0x0b2c.c02ab081 seq: 0x1 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0xb0c4a6ea
 check value in block header: 0x393f
 computed block checksum: 0xc917
Reread of rdba: 0x0100001c (file 4, block 28) found same corrupted data
ORACLE Instance XFF (pid = 8) - Error 1578 encountered while recovering transaction (9, 37) on object 53802.
Sun Jan 20 23:04:38 2013
Errors in file /u01/oracle/admin/XFF/bdump/xff_smon_9775.trc:
ORA-01578: ORACLE data block corrupted (file # 4, block # 28)
ORA-01110: data file 4: '/u01/oracle/oradata/XFF/users01.dbf'

通过试验步骤和alert日志可以发现因为block有事务,但是被标记为了坏块,所以smon无法回滚该事务,从而出现alert中类似提示

继续切换归档

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/oracle/oradata/XFF/archivelog
Oldest online log sequence     252
Next log sequence to archive   254
Current log sequence           254

移走归档

[oracle@xifenfei archivelog]$ ls -l
total 2224
-rw-r----- 1 oracle oinstall  360960 Jan 20 22:59 1_247_792679299.dbf
-rw-r----- 1 oracle oinstall    1024 Jan 20 22:59 1_248_792679299.dbf
-rw-r----- 1 oracle oinstall 1630208 Jan 20 23:04 1_249_792679299.dbf
-rw-r----- 1 oracle oinstall  249344 Jan 20 23:09 1_250_792679299.dbf
-rw-r----- 1 oracle oinstall    1024 Jan 20 23:09 1_251_792679299.dbf
-rw-r----- 1 oracle oinstall    4608 Jan 20 23:09 1_252_792679299.dbf
-rw-r----- 1 oracle oinstall    1024 Jan 20 23:09 1_253_792679299.dbf
[oracle@xifenfei archivelog]$ mkdir bak
[oracle@xifenfei archivelog]$ mv *.dbf bak
[oracle@xifenfei archivelog]$ ll
total 4
drwxr-xr-x 2 oracle oinstall 4096 Jan 20 23:11 bak

为了重现,当我们使用blockrecover恢复的时候,如果缺少归档会怎么样

尝试blockrecover

RMAN> BLOCKRECOVER DATAFILE 4 block 28;

Starting blockrecover at 20-JAN-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=135 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=126 devtype=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: sid=125 devtype=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: sid=124 devtype=DISK

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00004
channel ORA_DISK_1: reading from backup piece /u01/oracle/oradata/xff_4.rman
channel ORA_DISK_1: restored block(s) from backup piece 1
piece handle=/u01/oracle/oradata/xff_4.rman tag=TAG20130120T222333
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01

starting media recovery

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of blockrecover command at 01/20/2013 23:11:41
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of log thread 1 seq 253 lowscn 12286830510311 found to restore
RMAN-06025: no backup of log thread 1 seq 252 lowscn 12286830510307 found to restore
RMAN-06025: no backup of log thread 1 seq 251 lowscn 12286830510305 found to restore
RMAN-06025: no backup of log thread 1 seq 250 lowscn 12286830509979 found to restore
RMAN-06025: no backup of log thread 1 seq 249 lowscn 12286830489543 found to restore
RMAN-06025: no backup of log thread 1 seq 248 lowscn 12286830489541 found to restore
RMAN-06025: no backup of log thread 1 seq 247 lowscn 12286830489279 found to restore

alert日志记录

Sun Jan 20 23:11:38 2013
alter database recover datafile list clear
Sun Jan 20 23:11:38 2013
Completed: alter database recover datafile list clear
Sun Jan 20 23:11:38 2013
Starting block media recovery
Sun Jan 20 23:11:39 2013
Media Recovery Log /u01/oracle/oradata/XFF/archivelog/1_235_792679299.dbf
Sun Jan 20 23:11:41 2013
alter database recover cancel
Sun Jan 20 23:11:41 2013
Media Recovery Canceled
Completed: alter database recover cancel

blockrecover恢复途中或者异常终止,dbv检测

[oracle@xifenfei ~]$ dbv file=/u01/oracle/oradata/XFF/users01.dbf

DBVERIFY: Release 10.2.0.4.0 - Production on Sun Jan 20 23:18:29 2013

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

DBVERIFY - Verification starting : FILE = /u01/oracle/oradata/XFF/users01.dbf
Page 28 is marked corrupt
Corrupt block relative dba: 0x0100001c (file 4, block 28)
Bad check value found during dbv: 
Data in bad block:
 type: 6 format: 2 rdba: 0x0100001c
 last change scn: 0x0b2c.c02b0248 seq: 0x1 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x02480601
 check value in block header: 0x13fc
 computed block checksum: 0x663b



DBVERIFY - Verification complete

Total Pages Examined         : 1280
Total Pages Processed (Data) : 903
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 38
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 47
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 291
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 0
Highest block SCN            : 3224022228 (2860.3224022228)

在blockrecover处理过程，或者处理失败的后,block依然是坏块，如果遇到这类情况,没有事务可以直接指定seq/scn/time的方法来恢复,如果有事务,需要指定恢复时间点过该事务的时间点,让smon能够正常回滚,从而使得smon进程正常工作

还原归档后继续测试

[oracle@xifenfei archivelog]$ mv bak/* ./

RMAN> BLOCKRECOVER DATAFILE 4 block 28;

starting media recovery
archive log thread 1 sequence 247 is already on disk as file /u01/oracle/oradata/XFF/archivelog/1_247_792679299.dbf
…………
media recovery complete, elapsed time: 00:00:01
Finished blockrecover at 20-JAN-13

[oracle@xifenfei ~]$ dbv file=/u01/oracle/oradata/XFF/users01.dbf

DBVERIFY: Release 10.2.0.4.0 - Production on Sun Jan 20 23:15:43 2013

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

DBVERIFY - Verification starting : FILE = /u01/oracle/oradata/XFF/users01.dbf


DBVERIFY - Verification complete

Total Pages Examined         : 1280
Total Pages Processed (Data) : 904
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 38
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 47
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 291
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Highest block SCN            : 3224023169 (2860.3224023169)

>
blockrecover处理alert日志

Sun Jan 20 23:15:01 2013
Media Recovery Log /u01/oracle/oradata/XFF/archivelog/1_250_792679299.dbf
Sun Jan 20 23:15:01 2013
Media Recovery Log /u01/oracle/oradata/XFF/archivelog/1_251_792679299.dbf
Sun Jan 20 23:15:01 2013
Recovery of Online Redo Log: Thread 1 Group 3 Seq 252 Reading mem 0
  Mem# 0: /u01/oracle/oradata/XFF/redo03.log
Sun Jan 20 23:15:01 2013
Recovery of Online Redo Log: Thread 1 Group 1 Seq 253 Reading mem 0
  Mem# 0: /u01/oracle/oradata/XFF/redo01.log
Sun Jan 20 23:15:01 2013
Recovery of Online Redo Log: Thread 1 Group 2 Seq 254 Reading mem 0
  Mem# 0: /u01/oracle/oradata/XFF/redo02.log
Sun Jan 20 23:15:02 2013
Completed block media recovery

补充说明
1.在9i中使用blockrecover会在Archive destination中生产block的备份文件,类似469_519791_3063_2442393528.bkd(file 469 block 519791),需要注意Archive destination目录结尾需要”/”,不然可能出现直接写入和该目录并列的Archive destination+blockrecover产生文件;10g中不生成该文件;如果该block在9i中未备份,也不会在生产相关文件,而是利用归档恢复.
2.blockrecover需要还原从该block从备份之后的所有归档(如果被备份起来需要还原出来),如果缺少归档可能导致恢复失败,包括基于scn/seq/time的等
3.blockrecover在执行过程中或者执行异常终止,该block依然是坏块,不会对其他block产生影响.在使用blockrecover出现异常终止后的block修复,在后续blog中提供解决方法
4.对于不含事务的坏块,如果数据允许丢失可以通过设置event跳过坏块(特殊block除外),然后重建对象;对于含event的block 坏块,建议使用blockrecover处理或者直接恢复数据文件,如果没有备份,考试使用event跳过事务回滚,然后屏蔽坏块处理