Category Archives: Oracle备份恢复

在win中运行bbed程序

Posted on by

学习ORACLE三剑客:互联网,ORACLE资料,交流.今天在朋友的帮助下,了结了一个心结:在WIN平台中完美的使用bbed(8i/9i部分版本/10g 11g不支持),今天在朋友的帮助下解决了该问题
9i中bbed相关程序

Microsoft Windows [版本 6.1.7601]
版权所有 (c) 2009 Microsoft Corporation。保留所有权利。

C:\Users\XIFENFEI>cd E:\study\Oracle\数据库非常规恢复\bbed\win-bbed\bbed_win

C:\Users\XIFENFEI>e:
E:\study\Oracle\数据库非常规恢复\bbed\win-bbed\bbed_win>dir
 驱动器 E 中的卷没有标签。
 卷的序列号是 000C-3B41

 E:\study\Oracle\数据库非常规恢复\bbed\win-bbed\bbed_win 的目录

2012/09/24  19:45    <DIR>          .
2012/09/24  19:45    <DIR>          ..
2006/07/29  13:33           147,728 bbed.exe
2006/06/28  11:32            20,752 heteroxa9.dll
2004/10/26  16:35            49,152 kpp95rdr.dll
2004/10/26  16:35            45,056 kpp97rdr.dll
2012/09/24  19:49               250 log.bbd
2006/07/29  02:08           229,648 njssl9.dll
2006/06/28  11:33            61,712 ocijdbc9.dll
2002/04/26  16:22           733,184 oip9.dll
2006/07/29  14:02         1,589,520 oraclient9.dll
2006/07/29  15:25           602,384 oracommon9.dll
2006/06/28  09:54           651,536 ORACORE9.DLL
2006/06/27  08:44         1,736,976 oractxx9.dll
2006/07/29  14:02         2,691,344 orageneric9.dll
2006/07/29  13:56            24,576 oraimr9.dll
2006/07/29  15:19         3,014,656 orajox9.dll
2006/06/28  03:09           155,920 oraldapclnt9.dll
2006/06/28  03:09            24,848 oraldapjclnt9.dll
2006/07/29  02:18           741,648 oran9.dll
2006/07/29  02:10            41,232 oranad9.dll
2006/07/29  02:06            33,040 oranbeq9.dll
2006/07/29  02:08            24,848 ORANCDS9.DLL
2006/07/29  02:07            98,576 orancrypt9.dll
2006/07/29  02:07            33,040 ORANGSS9.DLL
2006/07/29  02:07            24,848 oranhost9.dll
2006/07/29  02:06            33,040 oranipc9.dll
2006/07/29  02:08           176,400 oranjni9.dll
2006/07/29  02:07           213,264 ORANK59.DLL
2006/07/29  02:06           180,496 oranl9.dll
2006/07/29  02:07            74,000 oranldap9.dll
2006/07/29  02:08            28,944 oranldapj9.dll
2006/06/28  06:32           499,984 ORANLS9.DLL
2006/07/29  02:06            33,040 orannmp9.dll
2006/07/29  02:07            53,520 orannts9.dll
2006/07/29  02:07            28,944 orannzentr9.dll
2006/07/29  02:08            28,944 ORANNZMCS9.DLL
2006/07/29  02:07           586,000 orannzsbb9.dll
2006/07/29  02:07            24,848 oranoname9.dll
2006/07/29  02:08            57,616 oranoncj9.dll
2006/07/29  02:07            45,328 ORANRAD9.DLL
2006/07/29  02:06           225,552 oranro9.dll
2006/07/29  02:08            41,232 oransgr9.dll
2006/07/29  02:06            49,424 orantcp9.dll
2006/07/29  02:07            53,520 ORANTCPS9.DLL
2006/07/29  02:07            28,944 orantns9.dll
2006/07/29  13:58            57,784 oraobjop9.dll
2006/07/29  14:58           311,568 oraocci9.dll
2006/07/29  14:02            24,848 oraodm9.dll
2006/06/26  19:19         2,314,240 oraolapapi9.dll
2006/07/29  13:58            57,784 oraolapop9.dll
2006/07/18  08:53           377,104 oraordim9.dll
2006/07/29  12:39            61,712 ORAPLC9.DLL
2006/07/29  12:40           909,584 ORAPLP9.DLL
2006/07/29  12:39         3,281,168 ORAPLS9.DLL
2006/07/29  13:58            57,784 ORAPRTOP9.DLL
2003/01/22  18:59            20,480 orarac9.dll
2002/04/26  17:18            28,944 ORASLAX9.DLL
2006/06/28  06:33            69,904 ORASNLS9.DLL
2006/06/28  16:04           483,600 ORASQL9.DLL
2002/04/29  14:04           246,032 oratrace9.dll
2002/04/29  14:04            86,288 oratracepls9.dll
2006/06/28  06:32            69,904 ORAUNLS9.DLL
2006/07/29  14:02            24,848 oravsn9.dll
2005/03/29  17:17            24,576 orawsec9.dll
2006/07/29  14:02            24,848 orawtc9.dll
2001/04/16  19:19           191,248 orawwg9.dll
2006/06/27  01:50           520,464 ORAXML9.DLL
2006/06/27  01:50            41,232 ORAXMLG9.DLL
2006/06/27  01:50           172,304 ORAXSD9.DLL
2010/06/29  05:38           181,560 vspp97.dll
2010/06/29  05:38            83,256 vsqp9.dll
2010/06/29  05:38           177,464 vsw97.dll
              72 个文件     25,234,522 字节
               2 个目录 58,747,236,352 可用字节

执行bbed程序报错

E:\study\Oracle\数据库非常规恢复\bbed\win-bbed\bbed_win>bbed
Message 112 not found; No message file for product=RDBMS, facility=BBED

BBED-00113: file not found

分析原因:因为在11g中ORACLE并没有在?/RDBMS/MESG/中提供bbedus.msb程序,导致bbed运行时候无法显示相关提示信息,从而出现类此该错误
解决方法:在11g环境中需要从10g库中拷贝bbedus.msb文件放置到?/RDBMS/MESG/中即可

运行bbed程序

E:\study\Oracle\数据库非常规恢复\bbed\win-bbed\bbed_win>bbed
Password:

BBED: Release 2.0.0.0.0 - Limited Production on Mon Sep 24 19:45:40 2012

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

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

BBED> info all;
 File#  Name                                                        Size(blks)
 -----  ----                                                        ----------

BBED> show all;
        FILE#           0
        BLOCK#          1
        OFFSET          0
        DBA             0x00000000 (0 0,1)
        FILENAME
        BIFILE          bifile.bbd
        LISTFILE
        BLOCKSIZE       2048
        MODE            Browse
        EDIT            Unrecoverable
        IBASE           Dec
        OBASE           Dec
        WIDTH           80
        COUNT           512
        LOGFILE         log.bbd
        SPOOL           No

bbed_wrap脚本获取数据块内容

Posted on by

bbed的功能很强大,可以通过bbed_wrap来获得数据块记录,相当用途:抢救坏块中的数据
环境准备

[oracle@xifenfei ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Jan 12 18:29:50 2012

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


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

SQL> conn chf/xifenfei
Connected.
SQL> create table t_xifenfei 
  2  as
  3  select object_id,object_name from dba_objects where rownum<20;

Table created.

SQL>  select file_id,block_id,block_id+blocks-1 
  2          from dba_extents 
  3   where segment_name ='T_XIFENFEI' AND owner='CHF';

   FILE_ID   BLOCK_ID BLOCK_ID+BLOCKS-1
---------- ---------- -----------------
         4        680               687

SQL> alter system checkpoint;

System altered.

--查询记录
SQL> col object_name for a20
SQL> select   object_id,object_name,
  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 chf.t_xifenfei;

 OBJECT_ID OBJECT_NAME             REL_FNO    BLOCKNO      ROWNO
---------- -------------------- ---------- ---------- ----------
        20 ICOL$                         4        683          0
        46 I_USER1                       4        683          1
        28 CON$                          4        683          2
        15 UNDO$                         4        683          3
        29 C_COBJ#                       4        683          4
         3 I_OBJ#                        4        683          5
        25 PROXY_ROLE_DATA$              4        683          6
        41 I_IND1                        4        683          7
        54 I_CDEF2                       4        683          8
        40 I_OBJ5                        4        683          9
        26 I_PROXY_ROLE_DATA$_1          4        683         10
        17 FILE$                         4        683         11
        13 UET$                          4        683         12
         9 I_FILE#_BLOCK#                4        683         13
        43 I_FILE1                       4        683         14
        51 I_CON1                        4        683         15
        38 I_OBJ3                        4        683         16
         7 I_TS#                         4        683         17
        56 I_CDEF4                       4        683         18

19 rows selected.

bbed参数配置

[oracle@xifenfei ~]$ more bbed_file
         1 /u01/oracle/oradata/ora11g/system01.dbf
         2 /u01/oracle/oradata/ora11g/sysaux01.dbf
         3 /u01/oracle/oradata/ora11g/undotbs01.dbf
         4 /u01/oracle/oradata/ora11g/users01.dbf
         5 /u01/oracle/oradata/ora11g/dbfs01.dbf

[oracle@xifenfei ~]$ more bbed.par
blocksize=8192
listfile=/home/oracle/bbed_file
mode=browse
SILENT=yes
PASSWORD=blockedit

bbed_wrap脚本执行

[oracle@xifenfei ~]$ ./bbed_wrap.sh 4 683 "/rn2cntn"
There are 19 rows in block 683 on file 4
" 20 "," ICOL$"
" 46 "," I_USER1"
" 28 "," CON$"
" 15 "," UNDO$"
" 29 "," C_COBJ#"
" 3 "," I_OBJ#"
" 25 "," PROXY_ROLE_DATA$"
" 41 "," I_IND1"
" 54 "," I_CDEF2"
" 40 "," I_OBJ5"
" 26 "," I_PROXY_ROLE_DATA$_1"
" 17 "," FILE$"
" 13 "," UET$"
" 9 "," I_FILE#_BLOCK#"
" 43 "," I_FILE1"
" 51 "," I_CON1"
" 38 "," I_OBJ3"
" 7 "," I_TS#"
" 56 "," I_CDEF4"
--和我们查询的结果完全一致

TXChecker初试

Posted on by

当我们对数据库进行异常恢复,很多时候要选择屏蔽回滚段,但是我们有没有办法来评估屏蔽回滚段到底会对我们的数据库的数据产生多大影响呢？其实我们可以通过TXChecker工具来评估数据库undo异常时候受到影响的数据对象有哪些,从而进一步确定是否真的需要对其undo下手处理.
模拟数据异常事务为提交情况

[oracle@xifenfei ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Aug 30 20:32:27 2012

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

Connected to an idle instance.

SQL> startup;
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.
Database opened.
SQL> create table t_xifenfei tablespace system 
  2  as
  3  select * from dba_objects;

Table created.

SQL> create table chf.t_xifenfei tablespace users
  2  as
  3  select * from dba_objects;

Table created.

SQL> delete from t_xifenfei where rownum<10;

9 rows deleted.

SQL> delete from chf.t_xifenfei where rownum<100;

99 rows deleted.

SQL> alter system checkpoint;

System altered.

SQL> shutdow abort;
ORACLE instance shut down.

在system和users表空间分别模拟了sys和chf的t_xifenfei表含事务未提交情况

TXChecker用法说明

[oracle@xifenfei txchecker]$ ./TXChecker 


TXChecker - v1.4 by Center Of Expertise (COE), Oracle Corporation (build 10/16/07)

Usage is: TXChecker [options]
Options:
  -a                     When scanning datafiles (with -d/-f/-l/-t options) report objects using any of the undo
                         segments (not just those with errors) (OPTIONAL)
  -b                     For objects found, print the datablock addresses. See readme for further details (OPTIONAL)
  -c<controlfile_name>   Fully qualified controlfile name to read (MANDATORY)
  -d                     Scan database for active TXs (use when undo not available) (OPTIONAL)
  -f<filename>           Scan the named datafile for active TXs (OPTIONAL)
  -g                     Indicates you want to find all blocks taking part in transactions with
                         a USN > than the USN supplied in -x<XID> parameter (same constraints as -w) (OPTIONAL)
  -l<listfile>           Scan all the datafiles listed in the listfile for active TXs (OPTIONAL)
  -m<minutes>            Number of minutes used to consider a TX as active (1-120) (DEFAULTS TO 60 MINUTES)
  -p                     Show the names and last known status of the UNDO segments (OPTIONAL)
  -s                     Skip read-only or offline normal datafiles (OPTIONAL)
  -t<tablespace>         Scan all the datafiles for this tablespace (OPTIONAL)
  -u                     Report ITL entries active if marked with an upper bound ('U' flag) fast commit SCN
                         instead if active transactions (OPTIONAL)
  -w<wrap#>              Wrap# for XID in ITL entry to report blocks where wrap# > this one (OPTIONAL)
                         Must use -x with this option. See the readme for details
  -x<XID>                XID for transaction wanting to search for (OPTIONAL)
                         Use format rrrr.ssss.wwwwwwww using Hexadecimal numbers
                         See the readme for full instructions on using -x, -w and -g options

NOTE: Options -d/-f/-l/-t are exclusive, and only one should be specified.
[/sql]
<strong>TXChecker初始</strong>
1
[oracle@xifenfei txchecker]$ ./TXChecker -c/u01/oracle/oradata/XFF/control01.ctl -d -a


TXChecker - v1.4 by Center Of Expertise (COE), Oracle Corporation (build 10/16/07)
Database Name: XFF   Version: 10.2.0

*** Database last checkpointed at 08/30/2012 20:34:43 (SCN: 0xa.0x1e142)
*** Using 60 minutes to find most active transactions (-m60)

*** Undo Segments:
USN:   0  Name: SYSTEM          TBS#:  0 File:   1  Block:      9  Instance:  0  SMU: N  Status:  3 - Online 
                                SCN: 0000.00000000   XactSQN: 0x00000000   UndoSQN: 0x00000000
USN:   1  Name: _SYSSMU1$       TBS#:  1 File:   2  Block:      9  Instance:  0  SMU: Y  Status:  3 - Online 
                                SCN: 000a.000191c3   XactSQN: 0x000000d2   UndoSQN: 0x0000013c
…………省略
USN:   9  Name: _SYSSMU9$       TBS#:  1 File:   2  Block:    137  Instance:  0  SMU: Y  Status:  3 - Online 
                                SCN: 000a.000191d8   XactSQN: 0x0000011a   UndoSQN: 0x000000dc
USN:  10  Name: _SYSSMU10$      TBS#:  1 File:   2  Block:    153  Instance:  0  SMU: Y  Status:  3 - Online 
                                SCN: 000a.000191d7   XactSQN: 0x000000c1   UndoSQN: 0x000000d9
…………省略
USN:  20  Name: _SYSSMU20$      TBS#:  5 File:   5  Block:    153  Instance:  0  SMU: Y  Status:  1 - Invalid / Dropped 
                                SCN: 0000.00070ec6   XactSQN: 0x00000002   UndoSQN: 0x00000001

*** Active Transactions:
USN: 6  Name: _SYSSMU6$  File: 2  Block: 89  Instance: 0  Status: 3 - Online
* Active TX at slot 6  #undo blocks: 3  Last bk: 2.90

Obj#:  51938  Name: CHF.T_XIFENFEI                                    Type: TABLE               Undo recs: 99
              Used undo segment IDs: 6 

Obj#:  51937  Name: SYS.T_XIFENFEI                                    Type: TABLE               Undo recs: 9
              Used undo segment IDs: 6 

         File (validate_objects.sql) being created for object validattion already exists
         and will be overwritten!!!
         Do you want to continue overwriting [Y/N]?y

*** Undo segments (headers) that encountered errors preventing Active TX scan:
USN:  11  Name: _SYSSMU11$      File:   5  Block:       9  Instance:  0  Error: 27 - Undo segment was dropped
…………省略
USN:  20  Name: _SYSSMU20$      File:   5  Block:     153  Instance:  0  Error: 27 - Undo segment was dropped

WARNING: Analyzing the full database for active transactions will take some time!

         Are you sure you want to analyze the full database [Y/N]?
         Are you sure you want to analyze the full database [Y/N]?y

*** Scanning database for datablocks that may require undo (PLEASE WAIT...):
*** Asterisk ('*') denotes blocks being updated since 08/08/2012 03:30:32 (SCN: 0x0.0x79607)

Scanning datafile:  3 - /u01/oracle/oradata/XFF/sysaux01.dbf (SYSAUX)                           - Active TX blocks: 147 *
Scanning datafile:  1 - /u01/oracle/oradata/XFF/system01.dbf (SYSTEM)                           - Active TX blocks: 11597 *
--undo表空间跳过
Undo datafile (/u01/oracle/oradata/XFF/undotbs01.dbf) - SKIPPING
Scanning datafile:  4 - /u01/oracle/oradata/XFF/users01.dbf (USERS)                             - Active TX blocks: 2 *
--因为数据文件丢失控制文件中offline的跳过(其实只要数据文件丢失就会跳过)
Cannot access datafile (/u01/oracle/oradata/XFF/xifenfei01.dbf) (error 2 - No such file or directory) - SKIPPING
Cannot access datafile (/u01/oracle/oradata/XFF/xifenfei02.dbf) (error 2 - No such file or directory) - SKIPPING
Scanning datafile:  7 - /u01/oracle/oradata/XFF/xifenfei03.dbf (XIFENFEI2)                      - Active TX blocks: 0

*** Objects that may require undo data:
*** Asterisk ('*') denotes blocks being updated since 08/08/2012 03:30:32 (SCN: 0x0.0x79607)

DataObj#:  51938  Name: CHF.T_XIFENFEI                                    Type: TABLE               Datablocks: 2 *
Used undo segment IDs: 6 
DataObj#:  46434  Name: MDSYS.SYS_IL0000046432C00006$$                    Type: INDEX               Datablocks: 4 
Used undo segment IDs: 2 
DataObj#:    124  Name: SYS.I_ACCESS1                                     Type: INDEX               Datablocks: 27 
Used undo segment IDs: 1 2 3 4 5 6 7 8 9 10 
…………省略
DataObj#:   8824  Name: SYS.SYS_IL0000008822C00008$$                      Type: INDEX               Datablocks: 1 *
Used undo segment IDs: 4 
DataObj#:  51937  Name: SYS.T_XIFENFEI                                    Type: TABLE               Datablocks: 1 *
Used undo segment IDs: 6 
DataObj#:  51557  Name: SYS.UTL_RECOMP_COMPILED                           Type: TABLE               Datablocks: 1 
Used undo segment IDs: 8 
…………省略
DataObj#:  42131  Name: XDB.XDB$ELEMENT_PROPNUMBER                        Type: INDEX               Datablocks: 1 
Used undo segment IDs: 2 

*** Use validate_objects.sql script file to validate the structure of possibly corrupt objects
    if the undo required is not available.

Undo Segment Usage Summary
**************************
*** Undo segments identified in use by active transaction datablocks AFTER 08/08/2012 03:30:32 (SCN: 0x0.0x79607):
USN:   1  Name: _SYSSMU1$     
USN:   2  Name: _SYSSMU2$     
USN:   3  Name: _SYSSMU3$     
USN:   4  Name: _SYSSMU4$     
USN:   5  Name: _SYSSMU5$     
USN:   6  Name: _SYSSMU6$     
USN:   9  Name: _SYSSMU9$     

*** Undo segments identified in use by active transacation datablocks BEFORE 08/08/2012 03:30:32 (SCN: 0x0.0x79607):
USN:   1  Name: _SYSSMU1$     
USN:   2  Name: _SYSSMU2$     
USN:   3  Name: _SYSSMU3$     
USN:   4  Name: _SYSSMU4$     
USN:   5  Name: _SYSSMU5$     
USN:   7  Name: _SYSSMU7$     
USN:   8  Name: _SYSSMU8$     
USN:   9  Name: _SYSSMU9$     
USN:  10  Name: _SYSSMU10$    

NOTE!NOTE!NOTE!NOTE!NOTE!NOTE!NOTE!NOTE!NOTE!NOTE!NOTE!NOTE!NOTE!NOTE!NOTE!NOTE!NOTE!
*************************************************************************************
Upload the logfile (TXChecker_083012_2042_XFF.log) to Oracle Support Services for analysis.
Do NOT attempt to force the database open until the logfile has been analyzed.

验证对象脚本

[oracle@xifenfei txchecker]$ more validate_objects.sql
rem validate_objects.sql - checks strcuture of objects needing unavailable undo data
rem                        Created by findtxns program, Oracle Corporation
set echo on
ANALYZE TABLE CHF.T_XIFENFEI VALIDATE STRUCTURE CASCADE;
ANALYZE INDEX MDSYS.SYS_IL0000046432C00006$$ VALIDATE STRUCTURE;
ANALYZE INDEX SYS.I_ACCESS1 VALIDATE STRUCTURE;
…………省略
ANALYZE INDEX XDB.SYS_C003167 VALIDATE STRUCTURE;
ANALYZE INDEX XDB.XDB$ELEMENT_PROPNAME VALIDATE STRUCTURE;
ANALYZE INDEX XDB.XDB$ELEMENT_PROPNUMBER VALIDATE STRUCTURE;

bbed 删除普通表记录

Posted on 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 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的数据库恢复有经验可能会比较快的定位问题,但是如果按照固定的思路去想可能会让自己走进死胡同.