_no_recovery_through_resetlogs参数功能探讨

Posted on 2012 年 09 月 04 日 by 惜分飞

_no_recovery_through_resetlogs参数默认值和描述

SQL> select a.ksppinm name,b.ksppstvl value,a.ksppdesc description
  2    from x$ksppi a,x$ksppcv b
  3   where a.inst_id = USERENV ('Instance')
  4     and b.inst_id = USERENV ('Instance')
  5     and a.indx = b.indx
  6     and upper(a.ksppinm) LIKE upper('%&param%')
  7  order by name
  8  /
Enter value for param: _no_recovery_through_resetlogs
old   6:    and upper(a.ksppinm) LIKE upper('%&param%')
new   6:    and upper(a.ksppinm) LIKE upper('%_no_recovery_through_resetlogs%')

NAME                             VALUE                    DESCRIPTION
-------------------------------- ------------------------ --------------------------------------------
_no_recovery_through_resetlogs   FALSE                    no recovery through this resetlogs operation

大家知道在10gr2版本及其以后版本,大家知道默认情况下,可以实现跨resetlogs恢复数据库.通过该参数的描述可以看出,该参数的用途是使得resetlogs之后不能继续进行恢复(我的理解是以前的备份不能应用resetlogs后的归档日志)
在实际中该函数的作用是否和该参数的描述相符,我们通过试验验证

rman备份数据库

[oracle@xifenfei tmp]$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Thu Aug 30 11:51:49 2012

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

connected to target database: XFF (DBID=3440302261)

RMAN> backup database format '/u01/oracle/oradata/tmp/10g_db_%U';

Starting backup at 30-AUG-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=143 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/oracle/oradata/XFF/system01.dbf
input datafile fno=00003 name=/u01/oracle/oradata/XFF/sysaux01.dbf
input datafile fno=00002 name=/u01/oracle/oradata/XFF/undotbs01.dbf
input datafile fno=00004 name=/u01/oracle/oradata/XFF/users01.dbf
channel ORA_DISK_1: starting piece 1 at 30-AUG-12
channel ORA_DISK_1: finished piece 1 at 30-AUG-12
piece handle=/u01/oracle/oradata/tmp/10g_db_0anjuhve_1_1 tag=TAG20120830T115214 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:35
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 30-AUG-12
channel ORA_DISK_1: finished piece 1 at 30-AUG-12
piece handle=/u01/oracle/oradata/tmp/10g_db_0bnjui2d_1_1 tag=TAG20120830T115214 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 30-AUG-12

resetlogs打开数据库

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

Total System Global Area  318767104 bytes
Fixed Size                  1267236 bytes
Variable Size             100665820 bytes
Database Buffers          209715200 bytes
Redo Buffers                7118848 bytes
Database mounted.

SQL> recover database until cancel;
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

创建测试表

SQL> create table t_xifenfei01
  2  as
  3  select * from dba_tables;

Table created.

SQL> create table t_xifenfei02
  2  as
  3  select * from dba_objects;

Table created.

SQL>  alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> create table t_xifenfei03 
  2  as
  3  select * from dba_objects;

Table created.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

恢复数据库

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

[oracle@xifenfei XFF]$ rm *.dbf
[oracle@xifenfei XFF]$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Thu Aug 30 12:00:47 2012

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

connected to target database (not started)

RMAN> startup mount

Oracle instance started
database mounted

Total System Global Area     318767104 bytes

Fixed Size                     1267236 bytes
Variable Size                100665820 bytes
Database Buffers             209715200 bytes
Redo Buffers                   7118848 bytes

RMAN> restore database;

Starting restore at 30-AUG-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK


channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/oracle/oradata/XFF/system01.dbf
restoring datafile 00002 to /u01/oracle/oradata/XFF/undotbs01.dbf
restoring datafile 00003 to /u01/oracle/oradata/XFF/sysaux01.dbf
restoring datafile 00004 to /u01/oracle/oradata/XFF/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/oracle/oradata/tmp/10g_db_0anjuhve_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/oracle/oradata/tmp/10g_db_0anjuhve_1_1 tag=TAG20120830T115214
channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 30-AUG-12

RMAN> recover database;

Starting recover at 30-AUG-12
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 7 is already on disk as file /u01/oracle/oradata/XFF/archivelog/1_7_790743352.dbf
archive log thread 1 sequence 8 is already on disk as file /u01/oracle/oradata/XFF/archivelog/1_8_790743352.dbf
archive log thread 1 sequence 9 is already on disk as file /u01/oracle/oradata/XFF/archivelog/1_9_790743352.dbf
archive log thread 1 sequence 10 is already on disk as file /u01/oracle/oradata/XFF/archivelog/1_10_790743352.dbf
archive log thread 1 sequence 1 is already on disk as file /u01/oracle/oradata/XFF/archivelog/1_1_792676624.dbf
archive log thread 1 sequence 2 is already on disk as file /u01/oracle/oradata/XFF/archivelog/1_2_792676624.dbf
archive log thread 1 sequence 3 is already on disk as file /u01/oracle/oradata/XFF/archivelog/1_3_792676624.dbf
archive log thread 1 sequence 4 is already on disk as file /u01/oracle/oradata/XFF/archivelog/1_4_792676624.dbf
archive log filename=/u01/oracle/oradata/XFF/archivelog/1_7_790743352.dbf thread=1 sequence=7
archive log filename=/u01/oracle/oradata/XFF/archivelog/1_8_790743352.dbf thread=1 sequence=8
archive log filename=/u01/oracle/oradata/XFF/archivelog/1_9_790743352.dbf thread=1 sequence=9
archive log filename=/u01/oracle/oradata/XFF/archivelog/1_10_790743352.dbf thread=1 sequence=10
archive log filename=/u01/oracle/oradata/XFF/archivelog/1_1_792676624.dbf thread=1 sequence=1
archive log filename=/u01/oracle/oradata/XFF/archivelog/1_2_792676624.dbf thread=1 sequence=2
media recovery complete, elapsed time: 00:00:06
Finished recover at 30-AUG-12

SQL> alter database open;

Database altered.

SQL> select table_name from user_tables where table_name like 'T_XIFENFEI0_';

TABLE_NAME
------------------------------
T_XIFENFEI01
T_XIFENFEI02
T_XIFENFEI03

证明10gr2确实可以跨resetlogs recover 日志恢复数据库

测试_no_recovery_through_resetlogs参数

SQL> create table t_xifenfei04 as
  2  select * from dba_objects;

Table created.

SQL> alter system set "_no_recovery_through_resetlogs"=true scope=spfile;

System altered.

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

Total System Global Area  318767104 bytes
Fixed Size                  1267236 bytes
Variable Size             100665820 bytes
Database Buffers          209715200 bytes
Redo Buffers                7118848 bytes
Database mounted.
SQL> recover database until cancel;
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

SQL> create table t_xifenfei05 
  2  as
  3  select * from dba_objects;

Table created.

SQL> create table t_xifenfei06
  2  as
  3  select * from dba_objects;

Table created.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

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

[oracle@xifenfei XFF]$ rm *.dbf
[oracle@xifenfei XFF]$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Thu Aug 30 12:47:40 2012

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

connected to target database (not started)

RMAN> startup mount;

Oracle instance started
database mounted

Total System Global Area     318767104 bytes

Fixed Size                     1267236 bytes
Variable Size                100665820 bytes
Database Buffers             209715200 bytes
Redo Buffers                   7118848 bytes

RMAN> restore database;

Starting restore at 30-AUG-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/oracle/oradata/XFF/system01.dbf
restoring datafile 00002 to /u01/oracle/oradata/XFF/undotbs01.dbf
restoring datafile 00003 to /u01/oracle/oradata/XFF/sysaux01.dbf
restoring datafile 00004 to /u01/oracle/oradata/XFF/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/oracle/oradata/tmp/10g_db_0anjuhve_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/oracle/oradata/tmp/10g_db_0anjuhve_1_1 tag=TAG20120830T115214
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 30-AUG-12

RMAN> recover database;

Starting recover at 30-AUG-12
using channel ORA_DISK_1
starting media recovery

archive log thread 1 sequence 7 is already on disk as file /u01/oracle/oradata/XFF/archivelog/1_7_790743352.dbf
archive log thread 1 sequence 8 is already on disk as file /u01/oracle/oradata/XFF/archivelog/1_8_790743352.dbf
archive log thread 1 sequence 9 is already on disk as file /u01/oracle/oradata/XFF/archivelog/1_9_790743352.dbf
archive log thread 1 sequence 10 is already on disk as file /u01/oracle/oradata/XFF/archivelog/1_10_790743352.dbf
archive log thread 1 sequence 1 is already on disk as file /u01/oracle/oradata/XFF/archivelog/1_1_792676624.dbf
archive log thread 1 sequence 2 is already on disk as file /u01/oracle/oradata/XFF/archivelog/1_2_792676624.dbf
archive log thread 1 sequence 3 is already on disk as file /u01/oracle/oradata/XFF/archivelog/1_3_792676624.dbf
archive log thread 1 sequence 4 is already on disk as file /u01/oracle/oradata/XFF/archivelog/1_4_792676624.dbf
archive log thread 1 sequence 5 is already on disk as file /u01/oracle/oradata/XFF/archivelog/1_5_792676624.dbf
archive log thread 1 sequence 6 is already on disk as file /u01/oracle/oradata/XFF/archivelog/1_6_792676624.dbf
archive log thread 1 sequence 1 is already on disk as file /u01/oracle/oradata/XFF/archivelog/1_1_792679299.dbf
archive log thread 1 sequence 2 is already on disk as file /u01/oracle/oradata/XFF/archivelog/1_2_792679299.dbf
archive log thread 1 sequence 3 is already on disk as file /u01/oracle/oradata/XFF/archivelog/1_3_792679299.dbf
archive log filename=/u01/oracle/oradata/XFF/archivelog/1_7_790743352.dbf thread=1 sequence=7
archive log filename=/u01/oracle/oradata/XFF/archivelog/1_8_790743352.dbf thread=1 sequence=8
archive log filename=/u01/oracle/oradata/XFF/archivelog/1_9_790743352.dbf thread=1 sequence=9
archive log filename=/u01/oracle/oradata/XFF/archivelog/1_10_790743352.dbf thread=1 sequence=10
--第一次 resetlogs后的归档
archive log filename=/u01/oracle/oradata/XFF/archivelog/1_1_792676624.dbf thread=1 sequence=1 
archive log filename=/u01/oracle/oradata/XFF/archivelog/1_2_792676624.dbf thread=1 sequence=2
archive log filename=/u01/oracle/oradata/XFF/archivelog/1_3_792676624.dbf thread=1 sequence=3
archive log filename=/u01/oracle/oradata/XFF/archivelog/1_4_792676624.dbf thread=1 sequence=4
archive log filename=/u01/oracle/oradata/XFF/archivelog/1_5_792676624.dbf thread=1 sequence=5
archive log filename=/u01/oracle/oradata/XFF/archivelog/1_6_792676624.dbf thread=1 sequence=6
--第二次 resetlogs后的归档(设置了_no_recovery_through_resetlogs参数为true并resetlogs后的归档日志
archive log filename=/u01/oracle/oradata/XFF/archivelog/1_1_792679299.dbf thread=1 sequence=1 
media recovery complete, elapsed time: 00:00:13
Finished recover at 30-AUG-12

RMAN> alter database open;

database opened

RMAN> exit


Recovery Manager complete.
[oracle@xifenfei XFF]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Aug 30 12:49:46 2012

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select table_name from user_tables where table_name like 'T_XIFENFEI0_';

TABLE_NAME
------------------------------
T_XIFENFEI01
T_XIFENFEI02
T_XIFENFEI03
T_XIFENFEI04
T_XIFENFEI05
T_XIFENFEI06

6 rows selected.

通过这里的测试证明使用_no_recovery_through_resetlogs=true后,resetlogs之后还是可以正常可以recover相关日志,证明_no_recovery_through_resetlogs参数不是限制这里的resetlogs后的归档日志应用,至于该参数的具体用途也希望知道的朋友告知下。但是这个参数在clone db和从rac db恢复到单实例db的时候,可能因为bug原因需要设置该参数为true,如:

--rac恢复到单实例RMAN Duplicate from RAC backup fails ORA-38856 [ID 334899.1]
sql>alter open database resetlogs;
ORA-38856: cannot mark instance UNNAMED_INSTANCE_2 (redo thread 2) as enabled

控制文件异常导致ORA-00600[kccsbck_first]

Posted on 2012 年 09 月 03 日 by 惜分飞

今天接到一个朋友求救他们的his系统数据库不能访问,情况比较紧急,让我帮忙处理.登录数据库得到信息如下:

操作系统:windows 2003
数据库:8.1.7
容灾方案：双机+emc存储镜像
备份:数据库无任何备份
启动到mount报错类此:
ORA-00600: internal error code, arguments: [kccsbck_first], [1], [4141358753], [], [], [], [], []

这个问题在上周的数据库恢复中遇到过一次,他们也是因为双机的案例,当时的情况见:双机mount数据库出现ORA-00600[kccsbck_first],有了上次的思维,我开始也怀疑是客户的双机的问题,但是客户说双机在半年前就关闭了,没有启动过;因为我对win的双机不太熟悉,怕他们双机软自动启动系统然后接管oracle从而导致这个问题,然后让客户检查另一台机器，确定没有启动和接管oracle 服务.
然后查询MOS发现win上面的特殊之处:是控制文件corruption导致故障(不过dbv检查不出来),而且三个控制文件有同样的问题
MOS记录如下(不过8.1.7也存在同样问题) [ID 291684.1]

Applies to:

Oracle Server - Enterprise Edition - Version: 9.0.1.5 and later   [Release: 9.0.1 and later ]
Information in this document applies to any platform.
***Checked for relevance on 09-APR-2012***
Symptoms



Alter database mount exclusive results in

ORA-00600: internal error code, arguments: [kccsbck_first], [1], [2141358753], [], [], [], [], []

The description of the error is:

'We receive this error because we are attempting to be the first 
thread/instance to mount the database and cannot because it appears that 
at least one other thread has mounted the database already'.


However in this case the database was a standalone database on Windows.
It had only one oracle service running.

The operating system was rebooted, the oracle service was deleted and a new service created.
Even then the error persisted. 
Cause

There was some corruption present in the controlfile.


Solution

In this case the problem was resolved by:

+ Taking a backup of the old control file

+ Recreating the control file using the following document
How to Recreate a Controlfile	 [Document 735106.1]

因为数据库不能mount,所以不能使用backup controlfile to trace;
因为是win系统,没有任何的控制文件备份,只能把控制文件拷贝到linux下面通过strings命令,自己编辑创建控制文件脚本(noresetlogs).执行脚本创建控制文件,recover database,应用redo文件恢复,然后resetlogs库,恢复成功(注意:8i中不需要另外增加临时文件)

bbed 文章汇总

Posted on 2012 年 09 月 01 日 by 惜分飞

bbed基本操作篇
bbed破坏数据文件
 bbed修改数据内容
 bbed恢复删除数据
 bbed修改ASM中数据
 bbed 找回被删除数据
 bbed 删除普通表记录
 通过bbed查看数据库结构
 bbed 删除 cluster table 记录
 bbed修改undo$(回滚段)状态
 利用bbed找回ORACLE更新前值

bbed晋级提升篇
bbed解决ORA-01190
bbed 修改datafile header
重现ORA-600[4000]异常
 bbed 使用实现 drop index 操作
 使用bbed修复损坏datafile header
使用bbed解决ORA-00600[2662]
bbed 恢复 GLOBAL_NAME 为空故障
 通过bbed解决ORA-00600[4000]案例
 ORA-00600 [ktbdchk1: bad dscn] 解决
 使用bbed让rac中的sysaux数据文件online
通过bbed修改回滚段状态解决ORA-00704故障
 通过bbed模拟ORA-00607/ORA-00600[4194]故障
 使用bbed解决ORA-00607/ORA-00600[4194]故障

bbed 删除普通表记录

Posted on 2012 年 09 月 01 日 by 惜分飞

有朋友和我说我的bbed系列缺少一篇普通表使用bbed删除记录的文章,月底现场保证回来没睡意,完善这篇文章,也算是对bbed系列的一个终结.
创建模拟记录

SQL> create table t_xifenfei tablespace users 
  2  as
  3  select * from dba_tables where rownum<10;

Table created.

SQL> alter system checkpoint;

System altered.


SQL> select   table_name,owner,rowid,
  2  dbms_rowid.rowid_relative_fno(rowid)rel_fno,
  3  dbms_rowid.rowid_block_number(rowid)blockno,
  4  dbms_rowid.rowid_row_number(rowid) rowno
  5  from t_xifenfei;  

TABLE_NAME                     OWNER                          ROWID                 REL_FNO    BLOCKNO      ROWNO
------------------------------ ------------------------------ ------------------ ---------- ---------- ----------
CON$                           SYS                            AAAM9UAAEAAACA0AAA          4       8244          0
UNDO$                          SYS                            AAAM9UAAEAAACA0AAB          4       8244          1
CDEF$                          SYS                            AAAM9UAAEAAACA0AAC          4       8244          2
CCOL$                          SYS                            AAAM9UAAEAAACA0AAD          4       8244          3
PROXY_ROLE_DATA$               SYS                            AAAM9UAAEAAACA0AAE          4       8244          4
FILE$                          SYS                            AAAM9UAAEAAACA0AAF          4       8244          5
FET$                           SYS                            AAAM9UAAEAAACA0AAG          4       8244          6
TS$                            SYS                            AAAM9UAAEAAACA0AAH          4       8244          7
PROXY_DATA$                    SYS                            AAAM9UAAEAAACA0AAI          4       8244          8

9 rows selected.

SQL> select dump('FILE$',16) from dual;

DUMP('FILE$',16)
----------------------------
Typ=96 Len=5: 46,49,4c,45,24

SQL> select dump('SYS',16) FROM DUAL;

DUMP('SYS',16)
----------------------
Typ=96 Len=3: 53,59,53

SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.

这里创建一张测试表,有9条记录,计划使用bbed删除file$的记录

bbed删除表记录

[oracle@xifenfei ~]$ bbed listfile=bbedfile
Password: 

BBED: Release 2.0.0.0.0 - Limited Production on Sat Sep 1 10:28:57 2012

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

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

BBED> info
 File#  Name                                                        Size(blks)
 -----  ----                                                        ----------
     1  /u01/oradata/orcl/system01.dbf                                       0
     3  /u01/oradata/orcl/sysaux01.dbf                                       0
     4  /u01/oradata/orcl/users01.dbf                                        0
     5  /u01/oradata/orcl/GS_ORADB_001.dbf                                   0
     6  /u01/oradata/orcl/GS_ORADB_IDX_001.dbf                               0
     7  /u01/oradata/orcl/undo01.dbf                                         0

BBED> set file 4 block 8244
        FILE#           4
        BLOCK#          8244

BBED> map
 File: /u01/oradata/orcl/users01.dbf (4)
 Block: 8244                                  Dba:0x01002034
------------------------------------------------------------
 KTB Data Block (Table/Cluster)

 struct kcbh, 20 bytes                      @0       

 struct ktbbh, 96 bytes                     @20      

 struct kdbh, 14 bytes                      @124     

 struct kdbt[1], 4 bytes                    @138     

 sb2 kdbr[9]                                @142     

 ub1 freespace[6137]                        @160     

 ub1 rowdata[1891]                          @6297    

 ub4 tailchk                                @8188    

BBED> set count 32
        COUNT           32

--查找对应值,估算起位置
BBED>  find /x 494c4524
 File: /u01/oradata/orcl/users01.dbf (4)
 Block: 8244             Offsets: 6929 to 6960           Dba:0x01002034
------------------------------------------------------------------------
 494c4524 06535953 54454dff ff055641 4c494402 c10b02c1 2902c102 03c20338 

 <32 bytes per line>

BBED> p *kdbr[7]
rowdata[209]
------------
ub1 rowdata[209]                            @6506     0x2c
--6506肯定不是在这个位置

BBED> p *kdbr[5]
rowdata[623]
------------
ub1 rowdata[623]                            @6920     0x2c
--6920包含了6929,可以确定在该位置

--查看对应值
BBED>  x /rccc
rowdata[623]                                @6920    
------------
flag@6920: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@6921: 0x00
cols@6922:   49

col    0[3] @6923: SYS
col    1[5] @6927: FILE$
col    2[6] @6933: SYSTEM
col    3[0] @6940: *NULL*
col    4[0] @6941: *NULL*
col    5[5] @6942: VALID
col    6[2] @6948: ..
col    7[2] @6951: .)
col    8[2] @6954: ..
col    9[3] @6957: ..8
col   10[4] @6961: ..8%
col   11[0] @6966: *NULL*
col   12[2] @6967: ..
col   13[6] @6970: ..01%.
col   14[0] @6977: *NULL*
col   15[2] @6978: ..
col   16[2] @6981: ..
col   17[3] @6984: YES
col   18[1] @6988: N
col   19[2] @6990: ..
col   20[2] @6993: ..
col   21[1] @6996: .
col   22[1] @6998: .
col   23[1] @7000: .
col   24[2] @7002: .(
col   25[1] @7005: .
col   26[1] @7007: .
col  27[10] @7009:          1
col  28[10] @7020:          1
col   29[5] @7031:     N
col   30[7] @7037: ENABLED
col   31[2] @7045: ..
col   32[7] @7048: xp....!
col   33[2] @7056: NO
col   34[0] @7059: *NULL*
col   35[1] @7060: N
col   36[1] @7062: N
col   37[2] @7064: NO
col   38[7] @7067: DEFAULT
col   39[8] @7075: DISABLED
col   40[3] @7084: YES
col   41[2] @7088: NO
col   42[0] @7091: *NULL*
col   43[8] @7092: DISABLED
col   44[3] @7101: YES
col   45[0] @7105: *NULL*
col   46[8] @7106: DISABLED
col   47[8] @7115: DISABLED
col   48[2] @7124: NO


BBED> d
 File: /u01/oradata/orcl/users01.dbf (4)
 Block: 8244             Offsets: 6920 to 6951           Dba:0x01002034
------------------------------------------------------------------------
 2c003103 53595305 46494c45 24065359 5354454d ffff0556 414c4944 02c10b02 

 <32 bytes per line>

BBED> set mode edit
        MODE            Edit

--修改为delete状态
BBED> m /x 3c
 File: /u01/oradata/orcl/users01.dbf (4)
 Block: 8244             Offsets: 6920 to 6951           Dba:0x01002034
------------------------------------------------------------------------
 3c003103 53595305 46494c45 24065359 5354454d ffff0556 414c4944 02c10b02 

 <32 bytes per line>

BBED> sum apply
Check value for File 4, Block 8244:
current = 0xa274, required = 0xa274

--验证不通过,因为空闲空间不正确(删除了数据还是以前的值当然不正确)
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oradata/orcl/users01.dbf
BLOCK = 8244

Block Checking: DBA = 16785460, Block Type = KTB-managed data block
data header at 0x7f0a75d0327c
kdbchk: the amount of space used is not equal to block size
        used=1722 fsc=0 avsp=6137 dtl=8064
Block 8244 failed with check code 6110

DBVERIFY - Verification complete

Total Blocks Examined         : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing   (Data) : 1
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0

BBED> p kdbh
struct kdbh, 14 bytes                       @124     
   ub1 kdbhflag                             @124      0x00 (NONE)
   b1 kdbhntab                              @125      1
   b2 kdbhnrow                              @126      9
   sb2 kdbhfrre                             @128     -1
   sb2 kdbhfsbo                             @130      36
   sb2 kdbhfseo                             @132      6173
   b2 kdbhavsp                              @134      6137
   b2 kdbhtosp                              @136      6137

BBED> m /x c618 offset 134
 File: /u01/oradata/orcl/users01.dbf (4)
 Block: 8244             Offsets:  134 to  165           Dba:0x01002034
------------------------------------------------------------------------
 c618f917 00000900 b01ee11d 0a1d311c 5b1b8c1a be19ee18 1d180000 00000000 

 <32 bytes per line>

BBED> m /x c618 offset 136
 File: /u01/oradata/orcl/users01.dbf (4)
 Block: 8244             Offsets:  136 to  167           Dba:0x01002034
------------------------------------------------------------------------
 c6180000 0900b01e e11d0a1d 311c5b1b 8c1abe19 ee181d18 00000000 00000000 

 <32 bytes per line>

BBED> sum apply
Check value for File 4, Block 8244:
current = 0xa274, required = 0xa274

BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oradata/orcl/users01.dbf
BLOCK = 8244

Block Checking: DBA = 16785460, Block Type = KTB-managed data block
data header at 0x13ef07c
kdbchk: space available on commit is incorrect
        tosp=6342 fsc=0 stb=2 avsp=6342
Block 8244 failed with check code 6111

DBVERIFY - Verification complete

Total Blocks Examined         : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing   (Data) : 1
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0

BBED> m /x c8 offset 136
 File: /u01/oradata/orcl/users01.dbf (4)
 Block: 8244             Offsets:  136 to  167           Dba:0x01002034
------------------------------------------------------------------------
 c8180000 0900b01e e11d0a1d 311c5b1b 8c1abe19 ee181d18 00000000 00000000 

 <32 bytes per line>

BBED> p kdbh
struct kdbh, 14 bytes                       @124     
   ub1 kdbhflag                             @124      0x00 (NONE)
   b1 kdbhntab                              @125      1
   b2 kdbhnrow                              @126      9
   sb2 kdbhfrre                             @128     -1
   sb2 kdbhfsbo                             @130      36
   sb2 kdbhfseo                             @132      6173
   b2 kdbhavsp                              @134      6342
   b2 kdbhtosp                              @136      6344

BBED> sum apply
Check value for File 4, Block 8244:
current = 0xa27a, required = 0xa27a

BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oradata/orcl/users01.dbf
BLOCK = 8244


DBVERIFY - Verification complete

Total Blocks Examined         : 1
Total Blocks Processed (Data) : 1
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

启动数据库测试

SQL> startup
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  2019320 bytes
Variable Size              75497480 bytes
Database Buffers           88080384 bytes
Redo Buffers                2174976 bytes
Database mounted.
Database opened.
SQL> set lines 150
SQL> select   table_name,owner,rowid,
  2   dbms_rowid.rowid_relative_fno(rowid)rel_fno,
  3   dbms_rowid.rowid_block_number(rowid)blockno,
  4   dbms_rowid.rowid_row_number(rowid) rowno
  5   from t_xifenfei;  

TABLE_NAME                     OWNER                          ROWID                 REL_FNO    BLOCKNO      ROWNO
------------------------------ ------------------------------ ------------------ ---------- ---------- ----------
CON$                           SYS                            AAAM9UAAEAAACA0AAA          4       8244          0
UNDO$                          SYS                            AAAM9UAAEAAACA0AAB          4       8244          1
CDEF$                          SYS                            AAAM9UAAEAAACA0AAC          4       8244          2
CCOL$                          SYS                            AAAM9UAAEAAACA0AAD          4       8244          3
PROXY_ROLE_DATA$               SYS                            AAAM9UAAEAAACA0AAE          4       8244          4
FET$                           SYS                            AAAM9UAAEAAACA0AAG          4       8244          6
TS$                            SYS                            AAAM9UAAEAAACA0AAH          4       8244          7
PROXY_DATA$                    SYS                            AAAM9UAAEAAACA0AAI          4       8244          8

8 rows selected.

可以看到file$这条记录已经被删除,证明bbed操作普通表删除成功
相关文章:
1.bbed 删除 cluster table 记录
2.bbed 找回被删除数据
3.利用bbed找回ORACLE更新前值

ORA-607/ORA-600[4194]不一定是重大灾难

Posted on 2012 年 09 月 01 日 by 惜分飞

以前解决过ORA-607/ORA-600[4194]和模拟过ORA-607/ORA-600[4194]错误,所以固定思维任务ORA-607/ORA-600[4194]可能就是重大灾难,通过这个案例来说明ORA-607/ORA-600[4194]可能也就是一个常规的不能再常规的错误:有一网友数据库因意外关闭电源导致启动过程出现ORA-00607/ORA-00600[4194]/ORA-00600[4097]的错误,使得数据库启动失败.

SMON: enabling tx recovery
Fri Aug 31 23:14:08 2012
Database Characterset is ZHS16GBK
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=19, OS id=15619
Fri Aug 31 23:14:10 2012
Errors in file /u01/oradata/orcl/bdump/orcl_smon_15589.trc:
ORA-00600: internal error code, arguments: [4097], [], [], [], [], [], [], []
Fri Aug 31 23:14:12 2012
Non-fatal internal error happenned while SMON was doing logging scn->time mapping.
SMON encountered 1 out of maximum 100 non-fatal internal errors.
Fri Aug 31 23:14:12 2012
Completed: alter database open
Fri Aug 31 23:14:14 2012
Errors in file /u01/oradata/orcl/bdump/orcl_smon_15589.trc:
ORA-01595: error freeing extent (2) of rollback segment (4))
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [4194], [48], [34], [], [], [], [], []
Fri Aug 31 23:29:41 2012
Errors in file /u01/oradata/orcl/bdump/orcl_smon_15589.trc:
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [4194], [17], [10], [], [], [], [], []
Fri Aug 31 23:29:43 2012
Errors in file /u01/oradata/orcl/bdump/orcl_smon_15589.trc:
ORA-00600: internal error code, arguments: [4194], [48], [34], [], [], [], [], []
Fri Aug 31 23:29:44 2012
Errors in file /u01/oradata/orcl/bdump/orcl_pmon_15577.trc:
ORA-00474: SMON process terminated with error
Fri Aug 31 23:29:44 2012
PMON: terminating instance due to error 474
Instance terminated by PMON, pid = 15577

通过alert日志可以定位到SMON_SCN_TIME表或者其回滚操作可能异常,结合alert和trace分析,发现这次错误的操作主要sql语句为:

ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [4194], [48], [34], [], [], [], [], []
Current SQL statement for this session:
UPDATE SYS.COL_USAGE$
   SET EQUALITY_PREDS    = EQUALITY_PREDS +
                           DECODE(BITAND(:FLAG, 1), 0, 0, 1),
       EQUIJOIN_PREDS    = EQUIJOIN_PREDS +
                           DECODE(BITAND(:FLAG, 2), 0, 0, 1),
       NONEQUIJOIN_PREDS = NONEQUIJOIN_PREDS +
                           DECODE(BITAND(:FLAG, 4), 0, 0, 1),
       RANGE_PREDS       = RANGE_PREDS + DECODE(BITAND(:FLAG, 8), 0, 0, 1),
       LIKE_PREDS        = LIKE_PREDS + DECODE(BITAND(:FLAG, 16), 0, 0, 1),
       NULL_PREDS        = NULL_PREDS + DECODE(BITAND(:FLAG, 32), 0, 0, 1),
       TIMESTAMP         = :TIME
 WHERE OBJ# = :OBJN
   AND INTCOL# = :COLN

ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [4194], [17], [10], [], [], [], [], []
Current SQL statement for this session:
UPDATE SYS.MON_MODS$
   SET INSERTS       = INSERTS + :INS,
       UPDATES       = UPDATES + :UPD,
       DELETES       = DELETES + :DEL,
       FLAGS        =
       (DECODE(BITAND(FLAGS, :FLAG), :FLAG, FLAGS, FLAGS + :FLAG)),
       DROP_SEGMENTS = DROP_SEGMENTS + :DROPSEG,
       TIMESTAMP     = :TIME
 WHERE OBJ# = :OBJN

ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [4097], [], [], [], [], [], [], []
Current SQL statement for this session:
INSERT INTO SMON_SCN_TIME
  (THREAD,
   TIME_MP,
   TIME_DP,
   SCN,
   SCN_WRP,
   SCN_BAS,
   NUM_MAPPINGS,
   TIM_SCN_MAP)
VALUES
  (0, :1, :2, :3, :4, :5, :6, :7)

这里主要涉及到对oracle的三张表的操作
COL_USAGE$:主要是在收集统计信息的时候作为是否需要收集列直方图信息参考
MON_MODS$:Oracle主要利用该表来记录那些表的数据发生改变,方便收集统计信息
SMON_SCN_TIME:记录SCN和TIME的对应关系
通过这里的分析可以确定这三张表中的数据对于数据库来说不是致命的基表信息,在数据库运行过程中可以清理掉这些信息,最多就是因为数据库性能的下降或者SCN和TIME互转功能不完善.

解决思路
完整的undo异常处理顺序
1.从alert中可以看出来数据库是在open之后由于SMON回滚到上述几条sql异常导致数据库down,所以可以尝试使用system回滚段启动数据库,看看是否可以屏蔽相关问题
2.如果方法1不可行,那使用event屏蔽smon对回滚段的相关操作,使得数据库正常启动
3.如果由于存在特殊事务,event无法屏蔽,尝试使用隐含参数处理该问题
4.如果隐含参数尚无法解决给问题考虑使用bbed
5.如果bbed不能解决,那只能选择dul或者其类似工具处理
这个案例中我们明确的看到是因为上面的三条sql回滚异常出现问题导致,对于这样的问题,经过测试使用方法1和2都能够顺利解决问题(open库之后需要重建undo,删除有问题undo表空间,修改参数[可能包括event],切换undo表空间).因为遇到几次ORA-607/ORA-600[4194]是因为system rollback损坏导致,所以这次开始也认为是一次比较复杂的恢复,最后证明这次是一种非常常规的恢复.对于ORACLE的数据库恢复有经验可能会比较快的定位问题,但是如果按照固定的思路去想可能会让自己走进死胡同.