重建 Datapump Utility EXPDP/IMPDP

因为数据库内部错误,数据字典不一致等原因导致 DataPump不能被正常使用,这个时候可以尝试着通过重建 DataPump来解决问题
sysdba登录数据库

SQL> connect / as sysdba

For Oracle version 10.1

1. Catdp.sql orders the installation of all its components including
   the Metadata API which was previously installed separately.
   By default catproc.sql invoke this script.
SQL >@ $ORACLE_HOME/rdbms/admin/catdp.sql
2. dbmspump.sql will create DBMS procedures for dataPUMP
SQL >@ $ORACLE_HOME/rdbms/admin/dbmspump.sql

For Oracle version 10.2

1. Catdph.sql will Re-Install DataPump types and views
SQL >@ $ORACLE_HOME/rdbms/admin/catdph.sql
Use this code to verify if XDB is installed:
SQL> select substr(comp_name,1,30) comp_name, substr(comp_id,1,10)
     comp_id,substr(version,1,12) version,status
     from dba_registry where comp_id='XDB';
Sample output if XDB installed,
Oracle XML Database            XDB        -version-   VALID
Note: If XDB is installed, then it is required to run "catmetx.sql" script also.
SQL> @ $ORACLE_HOME/rdbms/admin/catmetx.sql
2. prvtdtde.plb will Re-Install tde_library packages
SQL >@ $ORACLE_HOME/rdbms/admin/prvtdtde.plb
3. Catdpb.sql will Re-Install DataPump packages
SQL >@ $ORACLE_HOME/rdbms/admin/catdpb.sql
4.Dbmspump.sql will Re-Install DBMS DataPump objects
SQL >@ $ORACLE_HOME/rdbms/admin/dbmspump.sql
5. To recompile  invalid objects, if any
SQL >@ $ORACLE_HOME/rdbms/admin/utlrp.sql

For Oracle version 11g

1. Catproc.sql
SQL >@ $ORACLE_HOME/rdbms/admin/catproc.sql
2. To recompile invalid objects, if any
SQL >@ $ORACLE_HOME/rdbms/admin/utlrp.sql

参考:How To Reload Datapump Utility EXPDP/IMPDP [ID 430221.1]

DB2备份恢复(增量备份与恢复)

全备数据库

[db2inst1@xifenfei ~]$ db2 backup db xff online to /tmp  include logs
Backup successful. The timestamp for this backup image is : 20120411181918
[db2inst1@xifenfei ~]$ db2 list history backup all for xff
                    List History File for xff
Number of matching file entries = 1
 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  B  D  20120411181918001   N    D  S0000015.LOG S0000015.LOG
 ----------------------------------------------------------------------------
  Contains 3 tablespace(s):
 00001 SYSCATSPACE
 00002 USERSPACE1
 00003 SYSTOOLSPACE
 ----------------------------------------------------------------------------
    Comment: DB2 BACKUP XFF ONLINE
 Start Time: 20120411181918
   End Time: 20120411181925
     Status: A
 ----------------------------------------------------------------------------
  EID: 27 Location: /tmp

修改数据

[db2inst1@xifenfei ~]$ db2 list tables
Table/View                      Schema          Type  Creation time
------------------------------- --------------- ----- --------------------------
T_XFF                           DB2INST1        T     2012-04-05-09.45.29.148434
T_XIFENFEI                      DB2INST1        T     2012-04-06-05.50.11.111469
T_XIFENFEI01                    DB2INST1        T     2012-04-11-16.55.51.853649
  3 record(s) selected.
[db2inst1@xifenfei ~]$ db2 "drop table t_xff"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "drop table t_xifenfei"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "drop table t_xifenfei01"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "create table t_01xff like syscat.tables"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "insert into t_01xff select * from syscat.tables"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "select count(*) from t_01xff"
1
-----------
        370
  1 record(s) selected.
[db2inst1@xifenfei ~]$ db2 list tables
Table/View                      Schema          Type  Creation time
------------------------------- --------------- ----- --------------------------
T_01XFF                         DB2INST1        T     2012-04-11-18.23.05.723478
  1 record(s) selected.

增量备份SQL2426N解决

[db2inst1@xifenfei ~]$ db2 backup db xff online incremental to /tmp
SQL2426N  The database has not been configured to allow the incremental backup
operation. Reason code = "1".
[db2inst1@xifenfei ~]$ db2 ? SQL2426N
SQL2426N  The database has not been configured to allow the incremental
      backup operation. Reason code = "<reason-code>".
Explanation:
Incremental backups are not enabled for a table space until after
modification tracking has been activated for the database and a
non-incremental backup has been performed on the table space.
Possible reason codes:
1. The configuration parameter TRACKMOD has not been set for the
   database.
2. The TRACKMOD configuration parameter has been set but at least one
   table space has not had a non-incremental backup taken since the
   TRACKMOD parameter was set.
User response:
The action is based on the reason code as follows:
1. Activate modification tracking for the database by setting the
   TRACKMOD database configuration parameter to on, then perform a full
   database backup.
2. Consult the db2diag.log file to determine the name of the table
   space, then perform a full backup of that table space.
[db2inst1@xifenfei ~]$ db2 get db cfg for xff|grep TRACKMOD
 Track modified pages                         (TRACKMOD) = NO
[db2inst1@xifenfei ~]$ db2 update db cfg for xff using TRACKMOD ON
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
SQL1363W  One or more of the parameters submitted for immediate modification
were not changed dynamically. For these configuration parameters, the database
must be shutdown and reactivated before the configuration parameter changes
become effective.
[db2inst1@xifenfei ~]$ db2stop force
04/11/2012 17:49:59     0   0   SQL1064N  DB2STOP processing was successful.
SQL1064N  DB2STOP processing was successful.
[db2inst1@xifenfei ~]$ db2start
04/11/2012 17:50:09     0   0   SQL1063N  DB2START processing was successful.
SQL1063N  DB2START processing was successful.

累积增量备份

[db2inst1@xifenfei ~]$ db2 backup db xff online incremental to /tmp
Backup successful. The timestamp for this backup image is : 20120411182708
[db2inst1@xifenfei ~]$ db2 list history backup all for xff
                    List History File for xff
Number of matching file entries = 2
 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  B  D  20120411181918001   N    D  S0000015.LOG S0000015.LOG
 ----------------------------------------------------------------------------
  Contains 3 tablespace(s):
 00001 SYSCATSPACE
 00002 USERSPACE1
 00003 SYSTOOLSPACE
 ----------------------------------------------------------------------------
    Comment: DB2 BACKUP XFF ONLINE
 Start Time: 20120411181918
   End Time: 20120411181925
     Status: A
 ----------------------------------------------------------------------------
  EID: 27 Location: /tmp
 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  B  D  20120411182708001   O    D  S0000017.LOG S0000017.LOG
 ----------------------------------------------------------------------------
  Contains 3 tablespace(s):
 00001 SYSCATSPACE
 00002 USERSPACE1
 00003 SYSTOOLSPACE
 ----------------------------------------------------------------------------
    Comment: DB2 BACKUP XFF ONLINE
 Start Time: 20120411182708
   End Time: 20120411182712
     Status: A
 ----------------------------------------------------------------------------
  EID: 33 Location: /tmp

再次修改数据

[db2inst1@xifenfei ~]$ db2 "create table t_02xff like syscat.tables"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "insert into t_02xff select * from syscat.tables"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 list tables
Table/View                      Schema          Type  Creation time
------------------------------- --------------- ----- --------------------------
T_01XFF                         DB2INST1        T     2012-04-11-18.23.05.723478
T_02XFF                         DB2INST1        T     2012-04-11-18.30.26.639326
  2 record(s) selected.
[db2inst1@xifenfei ~]$ db2 "select count(*) from t_02xff"
1
-----------
        371
  1 record(s) selected.

迭代备份

[db2inst1@xifenfei ~]$ db2 backup db xff online incremental delta to /tmp
Backup successful. The timestamp for this backup image is : 20120411183129
[db2inst1@xifenfei ~]$ ll /tmp/XFF*
-rw-------  1 db2inst1 db2iadm1 122044416 Apr 11 18:19 /tmp/XFF.0.db2inst1.NODE0000.CATN0000.20120411181918.001
-rw-------  1 db2inst1 db2iadm1  55128064 Apr 11 18:27 /tmp/XFF.0.db2inst1.NODE0000.CATN0000.20120411182708.001
-rw-------  1 db2inst1 db2iadm1  55128064 Apr 11 18:31 /tmp/XFF.0.db2inst1.NODE0000.CATN0000.20120411183129.001
--这里可以看出最近一次的增量备份和迭代备份备份文件大小相同,说明迭代备份是在最近一次增量备份基础之上进行
[db2inst1@xifenfei ~]$ db2 list history backup all for xff
                    List History File for xff
Number of matching file entries = 3
 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  B  D  20120411181918001   N    D  S0000015.LOG S0000015.LOG
 ----------------------------------------------------------------------------
  Contains 3 tablespace(s):
 00001 SYSCATSPACE
 00002 USERSPACE1
 00003 SYSTOOLSPACE
 ----------------------------------------------------------------------------
    Comment: DB2 BACKUP XFF ONLINE
 Start Time: 20120411181918
   End Time: 20120411181925
     Status: A
 ----------------------------------------------------------------------------
  EID: 27 Location: /tmp
 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  B  D  20120411182708001   O    D  S0000017.LOG S0000017.LOG
 ----------------------------------------------------------------------------
  Contains 3 tablespace(s):
 00001 SYSCATSPACE
 00002 USERSPACE1
 00003 SYSTOOLSPACE
 ----------------------------------------------------------------------------
    Comment: DB2 BACKUP XFF ONLINE
 Start Time: 20120411182708
   End Time: 20120411182712
     Status: A
 ----------------------------------------------------------------------------
  EID: 33 Location: /tmp
 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  B  D  20120411183129001   E    D  S0000019.LOG S0000019.LOG
 ----------------------------------------------------------------------------
  Contains 3 tablespace(s):
 00001 SYSCATSPACE
 00002 USERSPACE1
 00003 SYSTOOLSPACE
 ----------------------------------------------------------------------------
    Comment: DB2 BACKUP XFF ONLINE
 Start Time: 20120411183129
   End Time: 20120411183133
     Status: A
 ----------------------------------------------------------------------------
  EID: 36 Location: /tmp

还原数据库

[db2inst1@xifenfei ~]$ db2 restore db xff  incremental automatic from /tmp taken at 20120411183129
SQL2539W  Warning!  Restoring to an existing database that is the same as the
backup image database.  The database files will be deleted.
Do you want to continue ? (y/n) y
DB20000I  The RESTORE DATABASE command completed successfully.

尝试登陆数据库

[db2inst1@xifenfei ~]$ db2 connect to xff
SQL1117N  A connection to or activation of database "XIFENFEI" cannot be made
because of ROLL-FORWARD PENDING.  SQLSTATE=57019
--数据库需要前滚,因为是在线备份

恢复数据库

[db2inst1@xifenfei ~]$  db2 "rollforward db xff to end of logs and stop"
                                 Rollforward Status
 Input database alias                   = xff
 Number of nodes have returned status   = 1
 Node number                            = 0
 Rollforward status                     = not pending
 Next log file to be read               =
 Log files processed                    = S0000019.LOG - S0000019.LOG
 Last committed transaction             = 2012-04-11-10.31.30.000000 UTC
DB20000I  The ROLLFORWARD command completed successfully.

验证恢复过程

[db2inst1@xifenfei ~]$ db2 connect to xff
   Database Connection Information
 Database server        = DB2/LINUX 9.5.9
 SQL authorization ID   = DB2INST1
 Local database alias   = XFF
[db2inst1@xifenfei ~]$ db2 list tables
Table/View                      Schema          Type  Creation time
------------------------------- --------------- ----- --------------------------
T_01XFF                         DB2INST1        T     2012-04-11-18.23.05.723478
T_02XFF                         DB2INST1        T     2012-04-11-18.30.26.639326
  2 record(s) selected.
[db2inst1@xifenfei ~]$ db2 "select count(*) from t_01xff"
1
-----------
        370
  1 record(s) selected.
[db2inst1@xifenfei ~]$ db2 "select count(*) from t_02xff"
1
-----------
        371
  1 record(s) selected.

本篇主要测试了增量备份和数据库恢复,在下篇中将对不完全恢复进行测试,坚持逐步学习db2数据库相关知识

DB2备份恢复(全备与恢复)

日志模式

[db2inst1@xifenfei ~]$ db2 get db cfg for xff |grep -i log
 Log retain for recovery status                          = NO
 User exit for logging status                            = YES   --(1)
 Catalog cache size (4KB)              (CATALOGCACHE_SZ) = 260
 Log buffer size (4KB)                        (LOGBUFSZ) = 98
 Log file size (4KB)                         (LOGFILSIZ) = 1024
 Number of primary log files                (LOGPRIMARY) = 6
 Number of secondary log files               (LOGSECOND) = 4
 Changed path to log files                  (NEWLOGPATH) =
 Path to log files                                       = /home/db2inst1/xff/redolog/NODE0000/
 Overflow log path                     (OVERFLOWLOGPATH) =
 Mirror log path                         (MIRRORLOGPATH) =
 First active log file                                   = S0000013.LOG
 Block log on disk full                (BLK_LOG_DSK_FUL) = NO
 Block non logged operations            (BLOCKNONLOGGED) = NO
 Percent max primary log space by transaction  (MAX_LOG) = 0
 Num. of active log files for 1 active UOW(NUM_LOG_SPAN) = 0
 Percent log file reclaimed before soft chckpt (SOFTMAX) = 520
 Log retain for recovery enabled             (LOGRETAIN) = OFF
 User exit for logging enabled                (USEREXIT) = OFF
 HADR log write synchronization mode     (HADR_SYNCMODE) = NEARSYNC
 First log archive method                 (LOGARCHMETH1) = DISK:/home/db2inst1/xff/archivelog/  --(2)
 Options for logarchmeth1                  (LOGARCHOPT1) =
 Second log archive method                (LOGARCHMETH2) = OFF
 Options for logarchmeth2                  (LOGARCHOPT2) =
 Failover log archive path                (FAILARCHPATH) =
 Number of log archive retries on error   (NUMARCHRETRY) = 5
 Log archive retry Delay (secs)         (ARCHRETRYDELAY) = 20
 Log pages during index build            (LOGINDEXBUILD) = OFF

由(1)和(2)可以判断该数据库处于归档日志模式下

查看当前存在备份

[db2inst1@xifenfei ~]$ db2 list history backup all for xff
                    List History File for xff
Number of matching file entries = 1
 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  B  D  20120406053431001   F    D  S0000000.LOG S0000000.LOG
 ----------------------------------------------------------------------------
  Contains 2 tablespace(s):
 00001 SYSCATSPACE
 00002 USERSPACE1
 ----------------------------------------------------------------------------
    Comment: DB2 BACKUP XFF OFFLINE    --离线备份
 Start Time: 20120406053431
   End Time: 20120406053439
     Status: A
 ----------------------------------------------------------------------------
  EID: 1 Location: /tmp

在线全备

[db2inst1@xifenfei ~]$ db2 backup db xff online to /tmp  include logs
Backup successful. The timestamp for this backup image is : 20120411165312
[db2inst1@xifenfei ~]$ db2 list history backup all for xff
                    List History File for xff
Number of matching file entries = 2
 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  B  D  20120406053431001   F    D  S0000000.LOG S0000000.LOG
 ----------------------------------------------------------------------------
  Contains 2 tablespace(s):
 00001 SYSCATSPACE
 00002 USERSPACE1
 ----------------------------------------------------------------------------
    Comment: DB2 BACKUP XFF OFFLINE  --本次试验的online备份
 Start Time: 20120406053431
   End Time: 20120406053439
     Status: A
 ----------------------------------------------------------------------------
  EID: 1 Location: /tmp
 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  B  D  20120411165312001   N    D  S0000013.LOG S0000013.LOG
 ----------------------------------------------------------------------------
  Contains 3 tablespace(s):
 00001 SYSCATSPACE
 00002 USERSPACE1
 00003 SYSTOOLSPACE
 ----------------------------------------------------------------------------
    Comment: DB2 BACKUP XFF ONLINE
 Start Time: 20120411165312
   End Time: 20120411165322
     Status: A
 ----------------------------------------------------------------------------
  EID: 19 Location: /tmp

当前数据库当前数据

[db2inst1@xifenfei ~]$ db2 connect to xff
   Database Connection Information
 Database server        = DB2/LINUX 9.5.9
 SQL authorization ID   = DB2INST1
 Local database alias   = XFF
[db2inst1@xifenfei ~]$ db2 list tables
Table/View                      Schema          Type  Creation time
------------------------------- --------------- ----- --------------------------
T_XFF                           DB2INST1        T     2012-04-05-09.45.29.148434
T_XIFENFEI                      DB2INST1        T     2012-04-06-05.50.11.111469
  2 record(s) selected.
[db2inst1@xifenfei ~]$ db2 "create table t_xifenfei01 like t_xff"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "insert into t_xifenfei01
> select * from t_xff"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "insert into t_xifenfei01
select * from t_xff"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "select count(*) from t_xifenfei01"
1
-----------
        734
  1 record(s) selected.

恢复数据库

[db2inst1@xifenfei ~]$ db2 restore db xff from /tmp taken at 20120411165312
SQL2539W  Warning!  Restoring to an existing database that is the same as the
backup image database.  The database files will be deleted.
Do you want to continue ? (y/n) y
DB20000I  The RESTORE DATABASE command completed successfully.
[db2inst1@xifenfei ~]$ db2 "rollforward db xff to end of logs and stop"
                                 Rollforward Status
 Input database alias                   = xff
 Number of nodes have returned status   = 1
 Node number                            = 0
 Rollforward status                     = not pending
 Next log file to be read               =
 Log files processed                    = S0000013.LOG - S0000014.LOG
 Last committed transaction             = 2012-04-11-08.56.20.000000 UTC
DB20000I  The ROLLFORWARD command completed successfully.
[db2inst1@xifenfei ~]$ db connect to xff
-bash: db: command not found
[db2inst1@xifenfei ~]$ db2 connect to xff
   Database Connection Information
 Database server        = DB2/LINUX 9.5.9
 SQL authorization ID   = DB2INST1
 Local database alias   = XFF
[db2inst1@xifenfei ~]$ db2 list tables
Table/View                      Schema          Type  Creation time
------------------------------- --------------- ----- --------------------------
T_XFF                           DB2INST1        T     2012-04-05-09.45.29.148434
T_XIFENFEI                      DB2INST1        T     2012-04-06-05.50.11.111469
T_XIFENFEI01                    DB2INST1        T     2012-04-11-16.55.51.853649
  3 record(s) selected.
[db2inst1@xifenfei ~]$ db2 "select count(*) from t_xifenfei01"
1
-----------
        734
  1 record(s) selected.

备份恢复是dba最重要的职责,本篇做为db2学习过程中第一篇关于备份恢复文章,后续将继续学习db2增量备份恢复等知识.

OER 7451 in Load Indicator : Error Code = OSD-04500:指定了非法选项

alert 日志错误
OER 7451 in Load Indicator : Error Code = OSD-04500:指定了非法选项

Sun Apr 22 11:15:51 2012
OER 7451 in Load Indicator : Error Code = OSD-04500: 指定了非法选项
O/S-Error: (OS 1) 函数不正确。 !
OER 7451 in Load Indicator : Error Code = OSD-04500: 指定了非法选项
O/S-Error: (OS 1) 函数不正确。 !
Sun Apr 22 11:16:01 2012
OER 7451 in Load Indicator : Error Code = OSD-04500: 指定了非法选项
O/S-Error: (OS 1) 函数不正确。 !
OER 7451 in Load Indicator : Error Code = OSD-04500: 指定了非法选项
O/S-Error: (OS 1) 函数不正确。 !
Sun Apr 22 11:16:11 2012
OER 7451 in Load Indicator : Error Code = OSD-04500: 指定了非法选项
O/S-Error: (OS 1) 函数不正确。 !

错误信息说明

07451, 00000, "slskstat: unable to obtain load information."
// *Cause:  kstat library returned an error. Possible OS failure
// *Action: Check result code in sercose[0] for more information.

数据库版本信息

SQL> select * from v$version;
BANNER
------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production  <<== 32位数据库
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for 32-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

操作系统信息

C:\Users\XIFENFEI>systeminfo
主机名:           XIFENFEI-PC
OS 名称:          Microsoft Windows 7 旗舰版
OS 版本:          6.1.7601 Service Pack 1 Build 7601
OS 制造商:        Microsoft Corporation
OS 配置:          独立工作站
OS 构件类型:      Multiprocessor Free
注册的所有人:     XIFENFEI
注册的组织:       Microsoft
产品 ID:          00426-068-8452196-86428
初始安装日期:     2012/2/28, 20:37:08
系统启动时间:     2012/4/22, 9:16:07
系统制造商:       Dell Inc.
系统型号:         Inspiron N4050
系统类型:         x64-based PC       <<==操心系统是win 7 64位
处理器:           安装了 1 个处理器。
                  [01]: Intel64 Family 6 Model 42 Stepping 7 GenuineIntel ~2300 Mhz
BIOS 版本:        Dell Inc. A06, 2011/11/14
Windows 目录:     C:\Windows
系统目录:         C:\Windows\system32
启动设备:         \Device\HarddiskVolume1
系统区域设置:     zh-cn;中文(中国)
输入法区域设置:   zh-cn;中文(中国)
时区:             (UTC+08:00)北京，重庆，香港特别行政区，乌鲁木齐
物理内存总量:     8,100 MB
可用的物理内存:   5,196 MB
虚拟内存: 最大值: 9,122 MB
虚拟内存: 可用:   5,315 MB
虚拟内存: 使用中: 3,807 MB
页面文件位置:     D:\pagefile.sys
域:               WORKGROUP
登录服务器:       \\XIFENFEI-PC

错误原因

Installed 32-bit Oracle database software on a 64-bit MS Windows OS which is not supported.
Note: For the Database software, you can ONLY install the x64 version on MS Windows (x64).
          You can NOT install the 32-bit version Database software on MS Windows (x64).

解决办法

Install 32-bit Oracle database software only on 32-bit MS Windows OS.

执行计划中常见index访问方式

近期有朋友对于单个表上的index各种情况比较模糊,这里对于单个表上,单个index出现的大多数情况进行了总结性测试,给出了测试结果,至于为什么出现这样的试验结果未做过多解释,给读者留下思考的空间.本篇文章仅仅是为了测试hint对index的影响,而不是说明走各种index方式的好坏.参考: INDEX FULL SCAN vs INDEX FAST FULL SCAN
创建表模拟测试

SQL> create table t_xifenfei as select object_id,object_name from dba_objects;
Table created.
SQL>  create index i_t_object_id on t_xifenfei(object_id);
Index created.
SQL> exec dbms_stats.gather_table_stats(USER,'T_XIFENFEI',cascade=>true);
PL/SQL procedure successfully completed.
SQL> desc t_xifenfei
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OBJECT_ID                                          NUMBER
 OBJECT_NAME                                        VARCHAR2(128)

TABLE ACCESS FULL

SQL> SET AUTOT TRACE EXP STAT
SQL> SELECT OBJECT_ID FROM T_XIFENFEI;
49838 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 548923532
--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            | 49838 |   243K|    57   (2)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T_XIFENFEI | 49838 |   243K|    57   (2)| 00:00:01 |
--------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       3544  consistent gets
          0  physical reads
          0  redo size
     721203  bytes sent via SQL*Net to client
      36927  bytes received via SQL*Net from client
       3324  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      49838  rows processed
SQL> SELECT /*+ INDEX(T i_t_object_id) */ OBJECT_ID FROM T_XIFENFEI;
49838 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 548923532
--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            | 49838 |   243K|    57   (2)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T_XIFENFEI | 49838 |   243K|    57   (2)| 00:00:01 |
--------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       3544  consistent gets
          0  physical reads
          0  redo size
     721203  bytes sent via SQL*Net to client
      36927  bytes received via SQL*Net from client
       3324  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      49838  rows processed

从上面的执行计划中可知,此时走了全表扫描. 由于我们需要查询的列为object_id,因此理论上只需要读取索引就应该可以返回所有数据,而此时为什么是全表扫描呢？ 这是因为NULL值与索引的特性所决定的.即null值不会被存储到B树索引.因此应该为表 t_xifenfei 的列 object_id 添加 not null 约束.

INDEX FAST FULL SCAN

SQL> alter table t_xifenfei modify(object_id not null);
Table altered.
SQL> SELECT  object_id from t_xifenfei;
49838 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2036340805
--------------------------------------------------------------------------------------
| Id  | Operation            | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |               | 49838 |   243K|    27   (4)| 00:00:01 |
|   1 |  INDEX FAST FULL SCAN| I_T_OBJECT_ID | 49838 |   243K|    27   (4)| 00:00:01 |
--------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       3432  consistent gets
          0  physical reads
          0  redo size
     721203  bytes sent via SQL*Net to client
      36927  bytes received via SQL*Net from client
       3324  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      49838  rows processed

INDEX FAST FULL SCAN:当在高速缓存中没有找到所需的索引块时,则根据db_file_multiblock_read_count的值进行多块读操作.对于索引的分支结构只是简单的获取,然后扫描所有的叶结点.其结果是导致索引结构没有访问,获取的数据没有根据索引键的顺序排序.INDEX FAST FULL SCAN使用multiblock_read,故产生db file scattered reads 事件.

INDEX RANGE SCAN

SQL> select object_id from t_xifenfei where object_id<10;
8 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2197008162
----------------------------------------------------------------------------------
| Id  | Operation        | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |               |     2 |    10 |     2   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| I_T_OBJECT_ID |     2 |    10 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("OBJECT_ID"<10)
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        499  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          8  rows processed
SQL> select /*+ index_ffs(t i_t_object_id) */ object_id from t_xifenfei t where object_id<10;
8 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2036340805
--------------------------------------------------------------------------------------
| Id  | Operation            | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |               |     2 |    10 |    27   (4)| 00:00:01 |
|*  1 |  INDEX FAST FULL SCAN| I_T_OBJECT_ID |     2 |    10 |    27   (4)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OBJECT_ID"<10)
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        118  consistent gets
          0  physical reads
          0  redo size
        499  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          8  rows processed

这里可以看出index_ffs已经生效,但是对于这样的情况hint index_ffs效率一般来说不会太高.
<br>
<strong>INDEX FULL SCAN</strong>

SQL> SELECT /*+ INDEX(T i_t_object_id) */ object_id  from t_xifenfei t;
49838 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 431110666
----------------------------------------------------------------------------------
| Id  | Operation        | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |               | 49838 |   243K|   113   (2)| 00:00:02 |
|   1 |  INDEX FULL SCAN | I_T_OBJECT_ID | 49838 |   243K|   113   (2)| 00:00:02 |
----------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       3426  consistent gets
          0  physical reads
          0  redo size
     721203  bytes sent via SQL*Net to client
      36927  bytes received via SQL*Net from client
       3324  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      49838  rows processed

INDEX FULL SCAN:完全按照索引存储的顺序依次访问整个索引树.当访问到叶结点之后,按照双向链表方式读取相连节点的值.换言之,对于索引上所有的数据是按照有序的方式来读取的.如果索引块没有在高速缓存中被找到时,则需要从数据文件中单块进行读取.对于需要读取大量数据的全索引扫描而言,这将使其变得低效.INDEX FULL SCAN使用single read,故产生db file sequential reads事件.新版的Oracle支持db file parallel reads方式.
HINT INDEX不会使用INDEX FAST FULL SCAN功能.

INDEX列ORDER BY

SQL> SELECT OBJECT_ID FROM T_XIFENFEI order by object_id ;
49838 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 431110666
----------------------------------------------------------------------------------
| Id  | Operation        | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |               | 49838 |   243K|   113   (2)| 00:00:02 |
|   1 |  INDEX FULL SCAN | I_T_OBJECT_ID | 49838 |   243K|   113   (2)| 00:00:02 |
----------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       3426  consistent gets
          0  physical reads
          0  redo size
     721203  bytes sent via SQL*Net to client
      36927  bytes received via SQL*Net from client
       3324  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      49838  rows processed
SQL> SELECT OBJECT_ID FROM T_XIFENFEI order by object_id  desc;
49838 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2808014233
--------------------------------------------------------------------------------------------
| Id  | Operation                  | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |               | 49838 |   243K|   113   (2)| 00:00:02 |
|   1 |  INDEX FULL SCAN DESCENDING| I_T_OBJECT_ID | 49838 |   243K|   113   (2)| 00:00:02 |
--------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       3427  consistent gets
          0  physical reads
          0  redo size
     721203  bytes sent via SQL*Net to client
      36927  bytes received via SQL*Net from client
       3324  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      49838  rows processed
SQL> SELECT  /*+ index_ffs(t i_t_object_id) */ object_id from t_xifenfei t order by object_id;
49838 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2527678987
-----------------------------------------------------------------------------------------------
| Id  | Operation             | Name          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |               | 49838 |   243K|       |   185   (4)| 00:00:03 |
|   1 |  SORT ORDER BY        |               | 49838 |   243K|  1192K|   185   (4)| 00:00:03 |
|   2 |   INDEX FAST FULL SCAN| I_T_OBJECT_ID | 49838 |   243K|       |    27   (4)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        117  consistent gets
          0  physical reads
          0  redo size
     721203  bytes sent via SQL*Net to client
      36927  bytes received via SQL*Net from client
       3324  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      49838  rows processed

对于index 列排序,默认情况下会使用INDEX FULL SCAN/INDEX FULL SCAN DESCENDING而不选择使用INDEX FAST FULL SCAN,因为INDEX FAST FULL SCAN获得数据后,还需要做一次SORT ORDER BY操作

INDEX FAST FULL SCAN+SORT AGGREGATE

SQL> SELECT  count(object_id) FROM T_XIFENFEI;
Execution Plan
----------------------------------------------------------
Plan hash value: 3095383276
-------------------------------------------------------------------------------
| Id  | Operation             | Name          | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |               |     1 |    27   (4)| 00:00:01 |
|   1 |  SORT AGGREGATE       |               |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| I_T_OBJECT_ID | 49838 |    27   (4)| 00:00:01 |
-------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        117  consistent gets
          0  physical reads
          0  redo size
        421  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
SQL> SELECT  /*+ INDEX(T i_t_object_id) */ count(object_id) FROM T_XIFENFEI t;
Execution Plan
----------------------------------------------------------
Plan hash value: 3079973526
--------------------------------------------------------------------------
| Id  | Operation        | Name          | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |               |     1 |   113   (2)| 00:00:02 |
|   1 |  SORT AGGREGATE  |               |     1 |            |          |
|   2 |   INDEX FULL SCAN| I_T_OBJECT_ID | 49838 |   113   (2)| 00:00:02 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        111  consistent gets
          0  physical reads
          0  redo size
        421  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

sort aggregate通常发生在使用一些聚合函数的时候,sum(),avg(),min(),max(),count()等等,实际上sort aggregate不做真正的sort,并不会用到排序空间,而是通过一个全局变量＋全表或全索引扫描来实现.这样的操作在默认情况下使用INDEX FAST FULL SCAN

INDEX FULL SCAN (MIN/MAX)

SQL> SELECT  max(object_id) FROM T_XIFENFEI;
Execution Plan
----------------------------------------------------------
Plan hash value: 2939893782
--------------------------------------------------------------------------------------------
| Id  | Operation                  | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |               |     1 |     5 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE            |               |     1 |     5 |            |          |
|   2 |   INDEX FULL SCAN (MIN/MAX)| I_T_OBJECT_ID | 49838 |   243K|     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        419  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
SQL>  SELECT /*+ index_ffs(t i_t_object_id) */ max(object_id) FROM T_XIFENFEI t;
Execution Plan
----------------------------------------------------------
Plan hash value: 2939893782
--------------------------------------------------------------------------------------------
| Id  | Operation                  | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |               |     1 |     5 |    27   (4)| 00:00:01 |
|   1 |  SORT AGGREGATE            |               |     1 |     5 |            |          |
|   2 |   INDEX FULL SCAN (MIN/MAX)| I_T_OBJECT_ID | 49838 |   243K|    27   (4)| 00:00:01 |
--------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        419  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

对于这样的查询INDEX FULL SCAN (MIN/MAX)明显是最优,但是此处奇怪的是使用了index_ffs提示无效,如果有知道的朋友,麻烦告知原因.

ORA-00600[KSSADP1]

检查数据库发现ORA-00600[KSSADP1]错误

Thu Apr 19 21:16:45 2012
Errors in file /oracle9/app/admin/crm/udump/crm1_ora_442896.trc:
ORA-00600: internal error code, arguments: [KSSADP1], [], [], [], [], [], [], []
Thu Apr 19 21:16:45 2012
Errors in file /oracle9/app/admin/crm/udump/crm1_ora_442896.trc:
ORA-00600: internal error code, arguments: [KSSADP1], [], [], [], [], [], [], []
Thu Apr 19 21:16:45 2012
Trace dumping is performing id=[cdmp_20120419211645]
Thu Apr 19 21:16:46 2012
Errors in file /oracle9/app/admin/crm/udump/crm1_ora_442896.trc:
ORA-00600: internal error code, arguments: [KSSADP1], [], [], [], [], [], [], []
Thu Apr 19 21:16:47 2012
Errors in file /oracle9/app/admin/crm/udump/crm1_ora_442896.trc:
ORA-00600: internal error code, arguments: [KSSADP1], [], [], [], [], [], [], []

分析crm1_ora_442896.trc信息

Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
ORACLE_HOME = /oracle9/app/product/9.2.0
System name:    AIX
Node name:      zwq_crm1
Release:        3
Version:        5
Machine:        00C420B44C00
Instance name: crm1
Redo thread mounted by this instance: 1
Oracle process number: 2354
Unix process pid: 442896, image: oracle@zwq_crm1 (TNS V1-V3)
*** SESSION ID:(927.39278) 2012-04-19 21:16:45.317
*** 2012-04-19 21:16:45.317
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [KSSADP1], [], [], [], [], [], [], []
----- Call Stack Trace -----
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedmp+0148          bl       ksedst               1029746FC ?
ksfdmp+0018          bl       01FD4014
kgerinv+00e8         bl       _ptrgl
kgesinv+0020         bl       kgerinv              9001000A02B56F8 ?
                                                   9001000A02B9450 ?
                                                   FFFFFFFFFFF8430 ? 000000458 ?
                                                   900000000CBAFA4 ?
ksesin+005c          bl       kgesinv              FFFFFFFFFFF88E0 ? 1101FAF78 ?
                                                   900000000C0ECC0 ? 000010000 ?
                                                   000000002 ?
kssadpm_stage+00c4   bl       ksesin               102973C84 ? 000000000 ?
                                                   00000001E ? 000000000 ?
                                                   000000069 ? 00000000C ?
                                                   000000000 ? 000000000 ?
ksqgel+0138          bl       kssadpm_stage        000000000 ? 000000000 ?
                                                   000000000 ? 000000000 ?
kcftis+003c          bl       ksqgel               000000000 ? 4029C61E0 ?
                                                   000000002 ? 0FFFFC16C ?
                                                   102A7977C ? 000000000 ?
                                                   000000003 ? 002A36408 ?
kcfhis+001c          bl       kcftis
krbbcc+0238          bl       kcfhis               11043B590 ?
krbpgc+001c          bl       krbbcc
ksmupg+0074          bl       _ptrgl
ksuded+00b8          bl       ksmupg               102924988 ? 000000020 ?
ksupucg+10ec         bl       ksuded               700000C376F5740 ? 000000000 ?
                                                   000000000 ?
opiodr+0474          bl       ksupucg              100000001 ?
ttcpip+0cc4          bl       _ptrgl
opitsk+0d60          bl       ttcpip               11000CF90 ?
                                                   442442216B736800 ?
                                                   FFFFFFFFFFFBF00 ? 1102E04BC ?
                                                   1102D7D20 ? 0000006A0 ?
                                                   1102D83C0 ? 0000006A0 ?
opiino+0758          bl       opitsk               000000000 ? 000000000 ?
opiodr+08cc          bl       _ptrgl
opidrv+032c          bl       opiodr               3C00000018 ? 4101FAF78 ?
                                                   FFFFFFFFFFFF840 ? 0A000F350 ?
sou2o+0028           bl       opidrv               3C0C000000 ? 4A00E8B50 ?
                                                   FFFFFFFFFFFF840 ?
main+0138            bl       01FD3A28
__start+0098         bl       main                 000000000 ? 000000000 ?
--------------------- Binary Stack Dump ---------------------
Cursor Dump:
----------------------------------------
Cursor 1 (110360418): CURROW  curiob: 110369b78
 curflg: 46 curpar: 0 curusr: 0 curses 700000c376f5740
 cursor name: select nvl(max(cpmid),0) from x$kcccp                                        where cpsta = 2
 child pin: 0, child lock: 700000d9b9c5bb8, parent lock: 700000d088e0fa0
 xscflg: 1100024, parent handle: 70000031d588d88, xscfl2: 4040401
 bhp size: 160/600
----------------------------------------
Cursor 2 (110360468): CURBOUND  curiob: 1103656f0
 curflg: c7 curpar: 0 curusr: 0 curses 700000c376f5740
 cursor name: SELECT SUBSTR(VERSION,1,INSTR(VERSION,'.') - 1 )   FROM V$INSTANCE
 child pin: 0, child lock: 700000d21e60930, parent lock: 700000327837ce0
 xscflg: 141024, parent handle: 700000304e2f020, xscfl2: 4000401
 bhp size: 160/600
----------------------------------------
Cursor 3 (1103604b8): CURBOUND  curiob: 1103b6aa8
 curflg: c7 curpar: 0 curusr: 0 curses 700000c376f5740
 cursor name: SELECT SUBSTR(VERSION,1 + INSTR(VERSION,'.',1,1) ,INSTR(VERSION,'.',1,2) -
 INSTR(VERSION,'.',1,1)  - 1 )   FROM V$INSTANCE
 child pin: 0, child lock: 700000d5e382ee8, parent lock: 700000c93581d40
 xscflg: 141024, parent handle: 700000d73daa1c0, xscfl2: 4000401
 bhp size: 160/600
----------------------------------------
Cursor 4 (110360508): CURBOUND  curiob: 1103b66b8
 curflg: c7 curpar: 0 curusr: 0 curses 700000c376f5740
 cursor name: SELECT SUBSTR(VERSION,1 + INSTR(VERSION,'.',1,2) ,INSTR(VERSION,'.',1,3) -
 INSTR(VERSION,'.',1,2)  - 1 )   FROM V$INSTANCE
 child pin: 0, child lock: 700000d16de7978, parent lock: 700000c44059d30
 xscflg: 141024, parent handle: 700000259c4a700, xscfl2: 4000401
 bhp size: 160/600
----------------------------------------
Cursor 5 (110360558): CURBOUND  curiob: 1103b3868
 curflg: 46 curpar: 0 curusr: 0 curses 700000c376f5740
 cursor name: SELECT SYSDATE   FROM SYS.DUAL
 child pin: 0, child lock: 700000d589cea48, parent lock: 70000026b311fb0
 xscflg: 100024, parent handle: 700000d2eaee328, xscfl2: 4600409
 bhp size: 280/632
----------------------------------------
Cursor 6 (1103605a8): CURBOUND  curiob: 1103b3408
 curflg: 46 curpar: 0 curusr: 0 curses 700000c376f5740
 cursor name: SELECT TO_CHAR(SYSDATE,'YYYY','NLS_CALENDAR=Gregorian'),TO_CHAR(SYSDATE,'MM','NLS_CALENDAR=Gregorian'),
TO_CHAR(SYSDATE,'DD','NLS_CALENDAR=Gregorian') FROM X$DUAL
 child pin: 0, child lock: 70000033f1753c8, parent lock: 700000db8c6dd18
 xscflg: 100024, parent handle: 700000cbc6ad8b0, xscfl2: 4600409
 bhp size: 160/600
End of cursor dump
ksedmp: no current context area
----- Dump of the Fixed PGA -----

找到相关文档Note:262996.1,经过分析,产生错误的原因是由在本版本的数据库中SGA管理中存在的漏洞造成,但此错误没有对数据库的数据造成损坏及性能影响.

处理建议
1．当前版本ORACLE已经不再提供补丁支持,建议升级到高版本解决(有sr中介绍10.2中解决);
2．由于此报错并没有对数据库的数据及性能造成损坏及影响,可以忽此错误。

记录一次ARC1: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned解决

Data Guard主库出现如下错误
导致归档日志不同通过LOG_ARCHIVE_DEST_2传输到备库

Thu Apr 19 19:58:40 2012
Errors in file /u01/app/oracle/admin/orcl/udump/orcl_ora_6756.trc:
Thu Apr 19 19:58:40 2012
Errors in file /u01/app/oracle/admin/orcl/udump/orcl_ora_6756.trc:
Thu Apr 19 19:58:40 2012
Errors in file /u01/app/oracle/admin/orcl/udump/orcl_ora_6756.trc:
Thu Apr 19 20:00:26 2012
ARC1: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (3113)
ARC1: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
PING[ARC1]: Error 3113 when pinging standby standby.
Thu Apr 19 20:18:18 2012
Errors in file /u01/app/oracle/admin/orcl/udump/orcl_ora_6756.trc:
Thu Apr 19 20:18:18 2012
Errors in file /u01/app/oracle/admin/orcl/udump/orcl_ora_6756.trc:
Thu Apr 19 20:18:18 2012
Errors in file /u01/app/oracle/admin/orcl/udump/orcl_ora_6756.trc:
Thu Apr 19 20:33:27 2012
ARC1: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (3113)
ARC1: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
[oracle@localhost ~]$ oerr ora 3113
03113, 00000, "end-of-file on communication channel"
// *Cause: The connection between Client and Server process was broken.
// *Action: There was a communication error that requires further investigation.
//          First, check for network problems and review the SQL*Net setup.
//          Also, look in the alert.log file for any errors. Finally, test to
//          see whether the server process is dead and whether a trace file
//          was generated at failure time.
提示连接错误

orcl_ora_6756.trc文件内容
这里没有得任何重要的有效信息

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1
System name:    Linux
Node name:      fcdb1
Release:        2.6.18-194.el5
Version:        #1 SMP Fri Apr 2 14:58:14 EDT 2010
Machine:        x86_64
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 21
Unix process pid: 6756, image: oracle@fcdb1 (TNS V1-V3)
*** 2012-04-19 19:51:32.033
*** ACTION NAME:(0000045 STARTED16) 2012-04-19 19:51:32.026
*** MODULE NAME:(backup incr datafile) 2012-04-19 19:51:32.026
*** SERVICE NAME:(SYS$USERS) 2012-04-19 19:51:32.026
*** SESSION ID:(1518.294) 2012-04-19 19:51:32.026
*** ACTION NAME:(0000062 STARTED68) 2012-04-19 19:58:40.083
*** MODULE NAME:(backup full datafile) 2012-04-19 19:58:40.083
*** 2012-04-19 19:58:40.083
*** ACTION NAME:(0000068 STARTED16) 2012-04-19 19:58:40.156
*** 2012-04-19 20:18:18.436
*** ACTION NAME:(0000118 STARTED16) 2012-04-19 20:18:18.436
*** MODULE NAME:(backup incr datafile) 2012-04-19 20:18:18.436

查看相关参数

SQL> show parameter archive;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_config                   string      DG_CONFIG=(primary,standby)
log_archive_dest_1                   string      LOCATION=/u01/app/oracle/arch
                                                 VALID_FOR=(ALL_LOGFILES,ALL_RO
                                                 LES) DB_UNIQUE_NAME=primary
log_archive_dest_2                   string      SERVICE=standby LGWR ASYNC VAL
                                                 ID_FOR=(ONLINE_LOGFILES,PRIMAR
                                                 Y_ROLE) DB_UNIQUE_NAME=standby
log_archive_dest_state_1             string      ENABLE
log_archive_dest_state_2             string      ENABLE

测试TNS

[oracle@fcdb1 bdump]$ tnsping standby
TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 19-APR-2012 20:47:51
Copyright (c) 1997, 2005, Oracle.  All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.203)(PORT = 1521)))
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (0 msec)
[oracle@fcdb1 bdump]$ sqlplus sys/oracle@standby as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Apr 19 20:49:05 2012
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

问题原因分析
从log_archive_dest_2 参数上可以看出默认是配置lgwr进程传输日志，但是因为备库没有配置standby redo log，所以使得启动arch传输日志，然后出现该问题，因为在传输过程中出现异常，导致arch不能继续和备库建立连接。

解决方法
查看了很多资料，给出的解决方法都是重启主库或者备库解决，我想既然是ARCH建立连接的失败，那么重启log_archive_dest_state_2参数，让arch进程重启。

SQL> ALTER SYSTEM SET log_archive_dest_state_2='DEFER' SCOPE=BOTH;
System altered.
SQL> ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;
System altered.
SQL> alter system switch logfile;
System altered.
--alert日志
Thu Apr 19 20:51:12 2012
ALTER SYSTEM SET log_archive_dest_state_2='DEFER' SCOPE=BOTH;
Thu Apr 19 20:51:32 2012
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;
LNS1 started with pid=35, OS id=7012
Thu Apr 19 20:51:47 2012
Thread 1 advanced to log sequence 2025
  Current log# 2 seq# 2025 mem# 0: /u01/app/oracle/oradata/orcl/redo02.log
Thu Apr 19 20:51:48 2012
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
Thu Apr 19 20:52:02 2012
Thread 1 advanced to log sequence 2026
  Current log# 3 seq# 2026 mem# 0: /u01/app/oracle/oradata/orcl/redo03.log
Thread 1 cannot allocate new log, sequence 2027

这个时候，查看备库日志也已经传输过去，通过修改log_archive_dest_state_2解决

ARC1: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (3113)
ARC1: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned

利用scn增量备份实现数据库增量恢复

数据库版本

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

全备数据库

RMAN> backup as compressed backupset database  format '/u01/oracle/oradata/tmp/ora11g_0_%U';
Starting backup at 18-APR-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/oracle/oradata/ora11g/system01.dbf
input datafile file number=00002 name=/u01/oracle/oradata/ora11g/sysaux01.dbf
input datafile file number=00003 name=/u01/oracle/oradata/ora11g/undotbs01.dbf
input datafile file number=00004 name=/u01/oracle/oradata/ora11g/users01.dbf
input datafile file number=00006 name=/u01/oracle/oradata/ora11g/xifenfei02.dbf
channel ORA_DISK_1: starting piece 1 at 18-APR-12
channel ORA_DISK_1: finished piece 1 at 18-APR-12
piece handle=/u01/oracle/oradata/tmp/ora11g_0_07n8p916_1_1 tag=TAG20120418T234958 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:15
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 18-APR-12
channel ORA_DISK_1: finished piece 1 at 18-APR-12
piece handle=/u01/oracle/oradata/tmp/ora11g_0_08n8p93h_1_1 tag=TAG20120418T234958 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 18-APR-12

创建测试数据库验证恢复标准

SQL> conn chf/xifenfei
Connected.
SQL> drop table xifenfei purge;
Table dropped.
SQL> create table xifenfei as
  2  select * from dba_objects;
Table created.
SQL> insert into xifenfei
  2  select * from dba_objects;
74534 rows created.
SQL> /
74534 rows created.
SQL> /
74534 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from xifenfei;
  COUNT(*)
----------
    298136
SQL> create table xifenfei01 as
  2  select * from dba_objects;
Table created.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> create table chf.xifenfei02 as
  2  select * from dba_objects;
Table created.
SQL> alter system switch logfile;
System altered.

异机恢复库

RMAN> restore controlfile from '/u01/oracle/oradata/tmp/ora11g_0_08n8p93h_1_1';
Starting restore at 19-APR-12
using channel ORA_DISK_1
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/oracle/oradata/ora11g/control01.ctl
output file name=/u01/oracle/oradata/ora11g/control02.ctl
Finished restore at 19-APR-12
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> list backup;
List of Backup Sets
===================
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7       Full    262.79M    DISK        00:01:08     18-APR-12
        BP Key: 7   Status: AVAILABLE  Compressed: YES  Tag: TAG20120418T234958
        Piece Name: /u01/oracle/oradata/tmp/ora11g_0_07n8p916_1_1
  List of Datafiles in backup set 7
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 1155510    18-APR-12 /u01/oracle/oradata/ora11g/system01.dbf
  2       Full 1155510    18-APR-12 /u01/oracle/oradata/ora11g/sysaux01.dbf
  3       Full 1155510    18-APR-12 /u01/oracle/oradata/ora11g/undotbs01.dbf
  4       Full 1155510    18-APR-12 /u01/oracle/oradata/ora11g/users01.dbf
  6       Full 1155510    18-APR-12 /u01/oracle/oradata/ora11g/xifenfei02.dbf
--这里可以发现备份时的scn,增量备份时取这里的最小scn为起点
--为了排除影响,删除所有归档日志记录
RMAN> delete archivelog all;
RMAN> list archivelog all;
specification does not match any archived log in the repository
RMAN> restore database;
Starting restore at 19-APR-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/oracle/oradata/ora11g/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/oracle/oradata/ora11g/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/oracle/oradata/ora11g/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/oracle/oradata/ora11g/users01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/oracle/oradata/ora11g/xifenfei02.dbf
channel ORA_DISK_1: reading from backup piece /u01/oracle/oradata/tmp/ora11g_0_07n8p916_1_1
channel ORA_DISK_1: piece handle=/u01/oracle/oradata/tmp/ora11g_0_07n8p916_1_1 tag=TAG20120418T234958
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:36
Finished restore at 19-APR-12
[oracle@xifenfei oradata]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 19 00:54:42 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> select file#,to_char(checkpoint_change#,'9999999999999999') "SCN",
  2  to_char(RESETLOGS_CHANGE#,'9999999999999999') "RESETLOGS SCN"
  3  from v$datafile_header;
     FILE# SCN                                RESETLOGS SCN
---------- ---------------------------------- ----------------------------------
         1           1155510                             787897
         2           1155510                             787897
         3           1155510                             787897
         4           1155510                             787897
         6           1155510                             787897
SQL> select file#,to_char(checkpoint_change#,'999999999999999') "SCN",
  2  to_char(last_change#,'999999999999999')"STOP_SCN" from v$datafile;
     FILE# SCN                              STOP_SCN
---------- -------------------------------- --------------------------------
         1          1155510
         2          1155510
         3          1155510
         4          1155510
         6          1155510

基于scn增量备份

RMAN> BACKUP INCREMENTAL FROM SCN 1155510 DATABASE
2> FORMAT '/u01/oracle/oradata/tmp/ora11_scn_%U' tag 'XIFENFEI';
Starting backup at 19-APR-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=125 device type=DISK
backup will be obsolete on date 26-APR-12
archived logs will not be kept or backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/oracle/oradata/ora11g/system01.dbf
input datafile file number=00002 name=/u01/oracle/oradata/ora11g/sysaux01.dbf
input datafile file number=00003 name=/u01/oracle/oradata/ora11g/undotbs01.dbf
input datafile file number=00004 name=/u01/oracle/oradata/ora11g/users01.dbf
input datafile file number=00006 name=/u01/oracle/oradata/ora11g/xifenfei02.dbf
channel ORA_DISK_1: starting piece 1 at 19-APR-12
channel ORA_DISK_1: finished piece 1 at 19-APR-12
piece handle=/u01/oracle/oradata/tmp/ora11_scn_0bn8pbsd_1_1 tag=XIFENFEI comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
using channel ORA_DISK_1
backup will be obsolete on date 26-APR-12
archived logs will not be kept or backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 19-APR-12
channel ORA_DISK_1: finished piece 1 at 19-APR-12
piece handle=/u01/oracle/oradata/tmp/ora11_scn_0cn8pbtq_1_1 tag=XIFENFEI comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 19-APR-12

增量恢复

RMAN> catalog start with '/u01/oracle/oradata/tmp/ora11_scn_09n8pa5h_1_1';
searching for all files that match the pattern /u01/oracle/oradata/tmp/ora11_scn_09n8pa5h_1_1
List of Files Unknown to the Database
=====================================
File Name: /u01/oracle/oradata/tmp/ora11_scn_09n8pa5h_1_1
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/oracle/oradata/tmp/ora11_scn_09n8pa5h_1_1
RMAN> recover database;
Starting recover at 19-APR-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=125 device type=DISK
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/oracle/oradata/ora11g/system01.dbf
destination for restore of datafile 00002: /u01/oracle/oradata/ora11g/sysaux01.dbf
destination for restore of datafile 00003: /u01/oracle/oradata/ora11g/undotbs01.dbf
destination for restore of datafile 00004: /u01/oracle/oradata/ora11g/users01.dbf
destination for restore of datafile 00006: /u01/oracle/oradata/ora11g/xifenfei02.dbf
channel ORA_DISK_1: reading from backup piece /u01/oracle/oradata/tmp/ora11_scn_0bn8pbsd_1_1
channel ORA_DISK_1: piece handle=/u01/oracle/oradata/tmp/ora11_scn_0bn8pbsd_1_1.bak tag=XIFENFEI
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
starting media recovery
--这里表现出来的是利用归档日志恢复，实质是基于scn增量备份的备份集恢复
archived log file name=/u01/oracle/oradata/archivelog/ora11g/1_49_777766629.dbf thread=1 sequence=49
archived log file name=/u01/oracle/oradata/archivelog/ora11g/1_50_777766629.dbf thread=1 sequence=50
archived log file name=/u01/oracle/oradata/archivelog/ora11g/1_51_777766629.dbf thread=1 sequence=51
archived log file name=/u01/oracle/oradata/archivelog/ora11g/1_52_777766629.dbf thread=1 sequence=52
archived log file name=/u01/oracle/oradata/archivelog/ora11g/1_53_777766629.dbf thread=1 sequence=53
archived log file name=/u01/oracle/oradata/archivelog/ora11g/1_54_777766629.dbf thread=1 sequence=54
archived log file name=/u01/oracle/oradata/archivelog/ora11g/1_55_777766629.dbf thread=1 sequence=55
archived log file name=/u01/oracle/oradata/archivelog/ora11g/1_56_777766629.dbf thread=1 sequence=56
archived log file name=/u01/oracle/oradata/archivelog/ora11g/1_57_777766629.dbf thread=1 sequence=57
unable to find archived log
archived log thread=1 sequence=58
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 04/19/2012 00:55:48
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 58 and starting SCN of 1157379
SQL> select file#,to_char(checkpoint_change#,'9999999999999999') "SCN",
  2  to_char(RESETLOGS_CHANGE#,'9999999999999999') "RESETLOGS SCN"
  3  from v$datafile_header;
     FILE# SCN                                RESETLOGS SCN
---------- ---------------------------------- ----------------------------------
         1           1157379                             787897
         2           1157379                             787897
         3           1157379                             787897
         4           1157379                             787897
         6           1157379                             787897
SQL> select file#,to_char(checkpoint_change#,'999999999999999') "SCN",
  2  to_char(last_change#,'999999999999999')"STOP_SCN" from v$datafile;
     FILE# SCN                              STOP_SCN
---------- -------------------------------- --------------------------------
         1          1157379
         2          1157379
         3          1157379
         4          1157379
         6          1157379
SQL> alter database open resetlogs;
Database altered.
SQL> select count(*) from chf.xifenfei;
  COUNT(*)
----------
    298136
SQL> select TABLE_NAME from dba_tables where table_name LIKE 'XIFENFEI%';
TABLE_NAME
------------------------------------------------------------
XIFENFEI02
XIFENFEI01
XIFENFEI

使用rman基于scn实现数据库增量恢复是在dg中修复gap的时候常见的方法,其实该方法也可以使用常规的增量恢复,通过人工控制,实现数据库的某种特殊的业务需求(特殊的数据迁移).处理思路主要是获得备库的数据文件最小scn(这个scn可能是通过全备恢复或者增量恢复产生),然后基于该SCN实现数据库增量备份,然后利用该备份进行增量恢复.

DB2日志参数介绍和修改归档模式

cfg关于log参数

[db2inst1@xifenfei ~]$ db2 get db cfg for xff|grep -i log
 Log retain for recovery status                          = NO
 User exit for logging status                            = NO
 Catalog cache size (4KB)              (CATALOGCACHE_SZ) = 260
 Log buffer size (4KB)                        (LOGBUFSZ) = 98
 Log file size (4KB)                         (LOGFILSIZ) = 1024
 Number of primary log files                (LOGPRIMARY) = 13
 Number of secondary log files               (LOGSECOND) = 4
 Changed path to log files                  (NEWLOGPATH) =
 Path to log files                                       = /home/db2inst1/db2inst1/NODE0000/SQL00003/SQLOGDIR/
 Overflow log path                     (OVERFLOWLOGPATH) =
 Mirror log path                         (MIRRORLOGPATH) =
 First active log file                                   =
 Block log on disk full                (BLK_LOG_DSK_FUL) = NO
 Block non logged operations            (BLOCKNONLOGGED) = NO
 Percent max primary log space by transaction  (MAX_LOG) = 0
 Num. of active log files for 1 active UOW(NUM_LOG_SPAN) = 0
 Percent log file reclaimed before soft chckpt (SOFTMAX) = 520
 Log retain for recovery enabled             (LOGRETAIN) = OFF
 User exit for logging enabled                (USEREXIT) = OFF
 HADR log write synchronization mode     (HADR_SYNCMODE) = NEARSYNC
 First log archive method                 (LOGARCHMETH1) = OFF
 Options for logarchmeth1                  (LOGARCHOPT1) =
 Second log archive method                (LOGARCHMETH2) = OFF
 Options for logarchmeth2                  (LOGARCHOPT2) =
 Failover log archive path                (FAILARCHPATH) =
 Number of log archive retries on error   (NUMARCHRETRY) = 5
 Log archive retry Delay (secs)         (ARCHRETRYDELAY) = 20
 Log pages during index build            (LOGINDEXBUILD) = OFF

LOGPRIMARY:主日志组日志文件个数(首次连接或激活时直接分配)
LOGSECOND:辅助日志组日志文件个数(主日志文件写满时按需分配)
LOGFILSIZ:每个日志文件页数,每页大小为4K
LOGARCHMETH1/LOGARCHMETH2:OFF表示循环模式,其他值表示归档模式(disk:/xifenfei/archive)
NEWLOGPATH:修改新的日志路径
LOGBUFSZ:日志缓冲区大小
MIRRORLOGPATH:日志镜像路径
LOGRETAIN:归档日志保留在日志文件中(不推荐该做法)
USEREXIT:归档日志通过用户出口程序管理(8.2后不推荐该做法)

当前日志位置

[db2inst1@xifenfei ~]$ ls -l /home/db2inst1/db2inst1/NODE0000/SQL00003/SQLOGDIR/
total 53404
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  5 16:20 S0000000.LOG
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  5 16:20 S0000001.LOG
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  5 16:20 S0000002.LOG
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  5 16:20 S0000003.LOG
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  5 16:20 S0000004.LOG
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  5 16:20 S0000005.LOG
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  5 16:20 S0000006.LOG
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  5 16:20 S0000007.LOG
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  5 16:20 S0000008.LOG
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  5 16:20 S0000009.LOG
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  5 16:20 S0000010.LOG
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  5 16:20 S0000011.LOG
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  5 16:20 S0000012.LOG

修改cfg参数(改为归档模式)

[db2inst1@xifenfei ~]$ db2 update db cfg for xff using LOGPRIMARY 6
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
[db2inst1@xifenfei ~]$ mkdir -p  xff/redolog
[db2inst1@xifenfei ~]$ mkdir -p  xff/archivelog
[db2inst1@xifenfei ~]$ db2 update db cfg for xff using NEWLOGPATH /home/db2inst1/xff/redolog
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
[db2inst1@xifenfei ~]$ db2 update db cfg for xff using LOGARCHMETH1 disk:/home/db2inst1/xff/archivelog
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.

查看修改后参数

[db2inst1@xifenfei ~]$ db2 get db cfg for xff|grep -i log
 Log retain for recovery status                          = NO
 User exit for logging status                            = NO   --没有生效
 Catalog cache size (4KB)              (CATALOGCACHE_SZ) = 260
 Log buffer size (4KB)                        (LOGBUFSZ) = 98
 Log file size (4KB)                         (LOGFILSIZ) = 1024
 Number of primary log files                (LOGPRIMARY) = 6
 Number of secondary log files               (LOGSECOND) = 4
 Changed path to log files                  (NEWLOGPATH) = /home/db2inst1/xff/redolog/NODE0000/
 Path to log files                                       = /home/db2inst1/db2inst1/NODE0000/SQL00003/SQLOGDIR/
 Overflow log path                     (OVERFLOWLOGPATH) =
 Mirror log path                         (MIRRORLOGPATH) =
 First active log file                                   =
 Block log on disk full                (BLK_LOG_DSK_FUL) = NO
 Block non logged operations            (BLOCKNONLOGGED) = NO
 Percent max primary log space by transaction  (MAX_LOG) = 0
 Num. of active log files for 1 active UOW(NUM_LOG_SPAN) = 0
 Percent log file reclaimed before soft chckpt (SOFTMAX) = 520
 Log retain for recovery enabled             (LOGRETAIN) = OFF
 User exit for logging enabled                (USEREXIT) = OFF
 HADR log write synchronization mode     (HADR_SYNCMODE) = NEARSYNC
 First log archive method                 (LOGARCHMETH1) = DISK:/home/db2inst1/xff/archivelog/
 Options for logarchmeth1                  (LOGARCHOPT1) =
 Second log archive method                (LOGARCHMETH2) = OFF
 Options for logarchmeth2                  (LOGARCHOPT2) =
 Failover log archive path                (FAILARCHPATH) =
 Number of log archive retries on error   (NUMARCHRETRY) = 5
 Log archive retry Delay (secs)         (ARCHRETRYDELAY) = 20
 Log pages during index build            (LOGINDEXBUILD) = OFF

参数生效情况

[db2inst1@xifenfei ~]$ ll /home/db2inst1/xff/redolog/NODE0000/
total 0
[db2inst1@xifenfei ~]$ db2stop
04/06/2012 05:33:24     0   0   SQL1064N  DB2STOP processing was successful.
SQL1064N  DB2STOP processing was successful.
[db2inst1@xifenfei ~]$ db2start
04/06/2012 05:33:34     0   0   SQL1063N  DB2START processing was successful.
SQL1063N  DB2START processing was successful.
[db2inst1@xifenfei ~]$ ll /home/db2inst1/xff/redolog/NODE0000/
total 0
[db2inst1@xifenfei ~]$ db2 connect to xff
SQL1116N  A connection to or activation of database "XIFENFEI" cannot be made
because of BACKUP PENDING.  SQLSTATE=57019
[db2inst1@xifenfei ~]$ db2 backup db xff to /tmp
Backup successful. The timestamp for this backup image is : 20120406053431
[db2inst1@xifenfei ~]$ ll /home/db2inst1/xff/redolog/NODE0000/
total 24652
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  6 05:33 S0000000.LOG
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  6 05:33 S0000001.LOG
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  6 05:33 S0000002.LOG
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  6 05:33 S0000003.LOG
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  6 05:33 S0000004.LOG
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  6 05:33 S0000005.LOG
-rw-------  1 db2inst1 db2iadm1     512 Apr  6 05:33 SQLLPATH.TAG
[db2inst1@xifenfei ~]$ db2 connect to xff
   Database Connection Information
 Database server        = DB2/LINUX 9.5.9
 SQL authorization ID   = DB2INST1
 Local database alias   = XFF
[db2inst1@xifenfei ~]$ db2 get db cfg for xff|grep -i log
 Log retain for recovery status                          = NO
 User exit for logging status                            = YES
 Catalog cache size (4KB)              (CATALOGCACHE_SZ) = 260
 Log buffer size (4KB)                        (LOGBUFSZ) = 98
 Log file size (4KB)                         (LOGFILSIZ) = 1024
 Number of primary log files                (LOGPRIMARY) = 6
 Number of secondary log files               (LOGSECOND) = 4
 Changed path to log files                  (NEWLOGPATH) =
 Path to log files                                       = /home/db2inst1/xff/redolog/NODE0000/
 Overflow log path                     (OVERFLOWLOGPATH) =
 Mirror log path                         (MIRRORLOGPATH) =
 First active log file                                   = S0000005.LOG
 Block log on disk full                (BLK_LOG_DSK_FUL) = NO
 Block non logged operations            (BLOCKNONLOGGED) = NO
 Percent max primary log space by transaction  (MAX_LOG) = 0
 Num. of active log files for 1 active UOW(NUM_LOG_SPAN) = 0
 Percent log file reclaimed before soft chckpt (SOFTMAX) = 520
 Log retain for recovery enabled             (LOGRETAIN) = OFF
 User exit for logging enabled                (USEREXIT) = OFF
 HADR log write synchronization mode     (HADR_SYNCMODE) = NEARSYNC
 First log archive method                 (LOGARCHMETH1) = DISK:/home/db2inst1/xff/archivelog/
 Options for logarchmeth1                  (LOGARCHOPT1) =
 Second log archive method                (LOGARCHMETH2) = OFF
 Options for logarchmeth2                  (LOGARCHOPT2) =
 Failover log archive path                (FAILARCHPATH) =
 Number of log archive retries on error   (NUMARCHRETRY) = 5
 Log archive retry Delay (secs)         (ARCHRETRYDELAY) = 20
 Log pages during index build            (LOGINDEXBUILD) = OFF

修改备份模式参数需要重启并且备份库后生效

测试归档日志

[db2inst1@xifenfei ~]$ db2 list history archive log  all for xff
                    List History File for xff
Number of matching file entries = 0
[db2inst1@xifenfei ~]$ db2 "create table t_xifenfei like syscat.tables"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "insert into t_xifenfei select * from syscat.tables"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "insert into t_xifenfei select * from t_xifenfei"
DB20000I  The SQL command completed successfully.
……N次……
[db2inst1@xifenfei ~]$ db2 "select count(*) from t_xifenfei"
1
-----------
      94208
  1 record(s) selected.
[db2inst1@xifenfei ~]$ db2 list history archive log  all for xff
                    List History File for xff
Number of matching file entries = 11
 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  X  D  20120406054854      1    D  S0000000.LOG C0000000
 ----------------------------------------------------------------------------
 ----------------------------------------------------------------------------
    Comment:
 Start Time: 20120406054854
   End Time: 20120406055029
     Status: A
 ----------------------------------------------------------------------------
  EID: 2 Location: /home/db2inst1/xff/archivelog/db2inst1/XIFENFEI/NODE0000/C0000000/S0000000.LOG
……省略……
 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  X  D  20120406055041      P    D  S0000010.LOG C0000000
 ----------------------------------------------------------------------------
 ----------------------------------------------------------------------------
    Comment:
 Start Time: 20120406055041
   End Time:
     Status: A
 ----------------------------------------------------------------------------
  EID: 14 Location: /home/db2inst1/xff/redolog/NODE0000/S0000010.LOG
[db2inst1@xifenfei ~]$ ll /home/db2inst1/xff/archivelog/db2inst1/XIFENFEI/NODE0000/C0000000
total 41080
-rw-r-----  1 db2inst1 db2iadm1 4202496 Apr  6 05:50 S0000000.LOG
-rw-r-----  1 db2inst1 db2iadm1 4202496 Apr  6 05:50 S0000001.LOG
-rw-r-----  1 db2inst1 db2iadm1 4202496 Apr  6 05:50 S0000002.LOG
-rw-r-----  1 db2inst1 db2iadm1 4202496 Apr  6 05:50 S0000003.LOG
-rw-r-----  1 db2inst1 db2iadm1 4202496 Apr  6 05:50 S0000004.LOG
-rw-r-----  1 db2inst1 db2iadm1 4202496 Apr  6 05:50 S0000005.LOG
-rw-r-----  1 db2inst1 db2iadm1 4202496 Apr  6 05:50 S0000006.LOG
-rw-r-----  1 db2inst1 db2iadm1 4202496 Apr  6 05:50 S0000007.LOG
-rw-r-----  1 db2inst1 db2iadm1 4202496 Apr  6 05:50 S0000008.LOG
-rw-r-----  1 db2inst1 db2iadm1 4202496 Apr  6 05:50 S0000009.LOG
[db2inst1@xifenfei ~]$ ll /home/db2inst1/xff/redolog/NODE0000
total 32868
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  6 05:50 S0000005.LOG
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  6 05:50 S0000006.LOG
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  6 05:50 S0000007.LOG
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  6 05:50 S0000008.LOG
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  6 05:50 S0000009.LOG
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  6 05:50 S0000010.LOG
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  6 05:50 S0000011.LOG
-rw-------  1 db2inst1 db2iadm1 4202496 Apr  6 05:50 S0000012.LOG
-rw-------  1 db2inst1 db2iadm1     512 Apr  6 05:33 SQLLPATH.TAG

手工修复ASM DISK HEADER 异常

今天有网友对asm中的磁盘做了fdisk操作,导致asm disk异常,通过手工修复ASM DISK HEADER 解决该问题,这里通过实验重现,提醒大家操作asm中的硬盘分区需要慎重,平时对ASM DISK HEADER 做好备份
初始化信息

SQL> select * from v$version;
BANNER
----------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL>  show parameter instance_name;
NAME                                 TYPE       VALUE
------------------------------------ ---------- ----------------
instance_name                        string     +ASM1
SQL> select group_number,DISK_NUMBER,PATH,HEADER_STATUS from v$asm_disk ;
GROUP_NUMBER DISK_NUMBER PATH                           HEADER_STATUS
------------ ----------- ------------------------------ ------------------------
           1           1 /dev/oracleasm/disks/VOL2      MEMBER
           1           0 /dev/oracleasm/disks/VOL1      MEMBER
           2           1 /dev/oracleasm/disks/VOL4      MEMBER
           2           0 /dev/oracleasm/disks/VOL3      MEMBER
[grid@rac1 ~]$ /etc/init.d/oracleasm  querydisk -d VOL3
Disk "VOL3" is a valid ASM disk on device [8,17]
[grid@rac1 ~]$ ll /dev |grep 8,|grep 17
brw-r----- 1 root disk     8,  17 Apr 17 11:37 sdb1
[grid@rac1 ~]$ /etc/init.d/oracleasm  querydisk -d VOL4
Disk "VOL4" is a valid ASM disk on device [8,18]
[grid@rac1 ~]$ ll /dev |grep 8,|grep 18
brw-r----- 1 root disk     8,  18 Apr 17 11:37 sdb2

备份ASM DISK HEADER

[root@rac1 backup_asmheader]# dd if=/dev/sdb1 of=vol3header.dd bs=4096 count=1
1+0 records in
1+0 records out
4096 bytes (4.1 kB) copied, 0.000143581 seconds, 28.5 MB/s
[root@rac1 backup_asmheader]# dd if=/dev/sdb2 of=vol4header.dd bs=4096 count=1
1+0 records in
1+0 records out
4096 bytes (4.1 kB) copied, 0.000147727 seconds, 27.7 MB/s

破坏ASM DISK HEADER

[root@rac1 ~]# dd if=/dev/zero of=/dev/sdb1 bs=4096 count=1
1+0 records in
1+0 records out
4096 bytes (4.1 kB) copied, 4.4421e-05 seconds, 92.2 MB/s
[grid@rac1 ~]$ kfed read /dev/oracleasm/disks/VOL3
kfbh.endian:                          0 ; 0x000: 0x00
kfbh.hard:                            0 ; 0x001: 0x00
kfbh.type:                            0 ; 0x002: KFBTYP_INVALID
kfbh.datfmt:                          0 ; 0x003: 0x00
kfbh.block.blk:                       0 ; 0x004: blk=0
kfbh.block.obj:                       0 ; 0x008: file=0
kfbh.check:                           0 ; 0x00c: 0x00000000
kfbh.fcn.base:                        0 ; 0x010: 0x00000000
kfbh.fcn.wrap:                        0 ; 0x014: 0x00000000
kfbh.spare1:                          0 ; 0x018: 0x00000000
kfbh.spare2:                          0 ; 0x01c: 0x00000000
B4C83200 00000000 00000000 00000000 00000000  [................]
  Repeat 255 times
KFED-00322: Invalid content encountered during block traversal: [kfbtTraverseBlock][Invalid OSM block type][][0]
SQL> select group_number,DISK_NUMBER,PATH,HEADER_STATUS from v$asm_disk ;
GROUP_NUMBER DISK_NUMBER PATH                           HEADER_STATUS
------------ ----------- ------------------------------ ------------------------
           1           1 /dev/oracleasm/disks/VOL2      MEMBER
           1           0 /dev/oracleasm/disks/VOL1      MEMBER
           2           1 /dev/oracleasm/disks/VOL4      MEMBER
           2           0 /dev/oracleasm/disks/VOL3      CANDIDATE

remount diskgroup

SQL> alter diskgroup  xifenfei dismount;
Diskgroup altered.
SQL> alter diskgroup xifenfei mount;
alter diskgroup xifenfei mount
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15017: diskgroup "XIFENFEI" cannot be mounted
ORA-15063: ASM discovered an insufficient number of disks for diskgroup
"XIFENFEI"

查看同一DISKGROUP中其他磁盘kfed

[grid@rac1 ~]$ kfed read /dev/oracleasm/disks/VOL4
kfbh.endian:                          1 ; 0x000: 0x01
kfbh.hard:                          130 ; 0x001: 0x82
kfbh.type:                            1 ; 0x002: KFBTYP_DISKHEAD
kfbh.datfmt:                          1 ; 0x003: 0x01
kfbh.block.blk:                       0 ; 0x004: blk=0
kfbh.block.obj:              2147483649 ; 0x008: disk=1
kfbh.check:                   349717291 ; 0x00c: 0x14d8432b
kfbh.fcn.base:                        0 ; 0x010: 0x00000000
kfbh.fcn.wrap:                        0 ; 0x014: 0x00000000
kfbh.spare1:                          0 ; 0x018: 0x00000000
kfbh.spare2:                          0 ; 0x01c: 0x00000000
kfdhdb.driver.provstr:     ORCLDISKVOL4 ; 0x000: length=12
kfdhdb.driver.reserved[0]:    877416278 ; 0x008: 0x344c4f56
kfdhdb.driver.reserved[1]:            0 ; 0x00c: 0x00000000
kfdhdb.driver.reserved[2]:            0 ; 0x010: 0x00000000
kfdhdb.driver.reserved[3]:            0 ; 0x014: 0x00000000
kfdhdb.driver.reserved[4]:            0 ; 0x018: 0x00000000
kfdhdb.driver.reserved[5]:            0 ; 0x01c: 0x00000000
kfdhdb.compat:                186646528 ; 0x020: 0x0b200000
kfdhdb.dsknum:                        1 ; 0x024: 0x0001
kfdhdb.grptyp:                        1 ; 0x026: KFDGTP_EXTERNAL
kfdhdb.hdrsts:                        3 ; 0x027: KFDHDR_MEMBER
kfdhdb.dskname:           XIFENFEI_0001 ; 0x028: length=13
kfdhdb.grpname:                XIFENFEI ; 0x048: length=8
kfdhdb.fgname:            XIFENFEI_0001 ; 0x068: length=13
kfdhdb.capname:                         ; 0x088: length=0
kfdhdb.crestmp.hi:             32967790 ; 0x0a8: HOUR=0xe DAYS=0x3 MNTH=0x3 YEAR=0x7dc
kfdhdb.crestmp.lo:           2015933440 ; 0x0ac: USEC=0x0 MSEC=0x22d SECS=0x2 MINS=0x1e
kfdhdb.mntstmp.hi:             32969260 ; 0x0b0: HOUR=0xc DAYS=0x11 MNTH=0x4 YEAR=0x7dc
kfdhdb.mntstmp.lo:           3109835776 ; 0x0b4: USEC=0x0 MSEC=0x315 SECS=0x15 MINS=0x2e
kfdhdb.secsize:                     512 ; 0x0b8: 0x0200
kfdhdb.blksize:                    4096 ; 0x0ba: 0x1000
kfdhdb.ausize:                  1048576 ; 0x0bc: 0x00100000
kfdhdb.mfact:                    113792 ; 0x0c0: 0x0001bc80
kfdhdb.dsksize:                    3788 ; 0x0c4: 0x00000ecc
kfdhdb.pmcnt:                         2 ; 0x0c8: 0x00000002
kfdhdb.fstlocn:                       1 ; 0x0cc: 0x00000001
kfdhdb.altlocn:                       2 ; 0x0d0: 0x00000002
kfdhdb.f1b1locn:                      0 ; 0x0d4: 0x00000000
kfdhdb.redomirrors[0]:                0 ; 0x0d8: 0x0000
kfdhdb.redomirrors[1]:                0 ; 0x0da: 0x0000
kfdhdb.redomirrors[2]:                0 ; 0x0dc: 0x0000
kfdhdb.redomirrors[3]:                0 ; 0x0de: 0x0000
kfdhdb.dbcompat:              168820736 ; 0x0e0: 0x0a100000
kfdhdb.grpstmp.hi:             32967790 ; 0x0e4: HOUR=0xe DAYS=0x3 MNTH=0x3 YEAR=0x7dc
kfdhdb.grpstmp.lo:           2015746048 ; 0x0e8: USEC=0x0 MSEC=0x176 SECS=0x2 MINS=0x1e
kfdhdb.vfstart:                       0 ; 0x0ec: 0x00000000
kfdhdb.vfend:                         0 ; 0x0f0: 0x00000000
kfdhdb.spfile:                        0 ; 0x0f4: 0x00000000
kfdhdb.spfflg:                        0 ; 0x0f8: 0x00000000
kfdhdb.ub4spare[0]:                   0 ; 0x0fc: 0x00000000
kfdhdb.ub4spare[1]:                   0 ; 0x100: 0x00000000
kfdhdb.ub4spare[2]:                   0 ; 0x104: 0x00000000
kfdhdb.ub4spare[3]:                   0 ; 0x108: 0x00000000
kfdhdb.ub4spare[4]:                   0 ; 0x10c: 0x00000000
kfdhdb.ub4spare[5]:                   0 ; 0x110: 0x00000000
kfdhdb.ub4spare[6]:                   0 ; 0x114: 0x00000000
kfdhdb.ub4spare[7]:                   0 ; 0x118: 0x00000000
kfdhdb.ub4spare[8]:                   0 ; 0x11c: 0x00000000
kfdhdb.ub4spare[9]:                   0 ; 0x120: 0x00000000
kfdhdb.ub4spare[10]:                  0 ; 0x124: 0x00000000
kfdhdb.ub4spare[11]:                  0 ; 0x128: 0x00000000
kfdhdb.ub4spare[12]:                  0 ; 0x12c: 0x00000000
kfdhdb.ub4spare[13]:                  0 ; 0x130: 0x00000000
kfdhdb.ub4spare[14]:                  0 ; 0x134: 0x00000000
kfdhdb.ub4spare[15]:                  0 ; 0x138: 0x00000000
kfdhdb.ub4spare[16]:                  0 ; 0x13c: 0x00000000
kfdhdb.ub4spare[17]:                  0 ; 0x140: 0x00000000
kfdhdb.ub4spare[18]:                  0 ; 0x144: 0x00000000
kfdhdb.ub4spare[19]:                  0 ; 0x148: 0x00000000
kfdhdb.ub4spare[20]:                  0 ; 0x14c: 0x00000000
kfdhdb.ub4spare[21]:                  0 ; 0x150: 0x00000000
kfdhdb.ub4spare[22]:                  0 ; 0x154: 0x00000000
kfdhdb.ub4spare[23]:                  0 ; 0x158: 0x00000000
kfdhdb.ub4spare[24]:                  0 ; 0x15c: 0x00000000
kfdhdb.ub4spare[25]:                  0 ; 0x160: 0x00000000
kfdhdb.ub4spare[26]:                  0 ; 0x164: 0x00000000
kfdhdb.ub4spare[27]:                  0 ; 0x168: 0x00000000
kfdhdb.ub4spare[28]:                  0 ; 0x16c: 0x00000000
kfdhdb.ub4spare[29]:                  0 ; 0x170: 0x00000000
kfdhdb.ub4spare[30]:                  0 ; 0x174: 0x00000000
kfdhdb.ub4spare[31]:                  0 ; 0x178: 0x00000000
kfdhdb.ub4spare[32]:                  0 ; 0x17c: 0x00000000
kfdhdb.ub4spare[33]:                  0 ; 0x180: 0x00000000
kfdhdb.ub4spare[34]:                  0 ; 0x184: 0x00000000
kfdhdb.ub4spare[35]:                  0 ; 0x188: 0x00000000
kfdhdb.ub4spare[36]:                  0 ; 0x18c: 0x00000000
kfdhdb.ub4spare[37]:                  0 ; 0x190: 0x00000000
kfdhdb.ub4spare[38]:                  0 ; 0x194: 0x00000000
kfdhdb.ub4spare[39]:                  0 ; 0x198: 0x00000000
kfdhdb.ub4spare[40]:                  0 ; 0x19c: 0x00000000
kfdhdb.ub4spare[41]:                  0 ; 0x1a0: 0x00000000
kfdhdb.ub4spare[42]:                  0 ; 0x1a4: 0x00000000
kfdhdb.ub4spare[43]:                  0 ; 0x1a8: 0x00000000
kfdhdb.ub4spare[44]:                  0 ; 0x1ac: 0x00000000
kfdhdb.ub4spare[45]:                  0 ; 0x1b0: 0x00000000
kfdhdb.ub4spare[46]:                  0 ; 0x1b4: 0x00000000
kfdhdb.ub4spare[47]:                  0 ; 0x1b8: 0x00000000
kfdhdb.ub4spare[48]:                  0 ; 0x1bc: 0x00000000
kfdhdb.ub4spare[49]:                  0 ; 0x1c0: 0x00000000
kfdhdb.ub4spare[50]:                  0 ; 0x1c4: 0x00000000
kfdhdb.ub4spare[51]:                  0 ; 0x1c8: 0x00000000
kfdhdb.ub4spare[52]:                  0 ; 0x1cc: 0x00000000
kfdhdb.ub4spare[53]:                  0 ; 0x1d0: 0x00000000
kfdhdb.acdb.aba.seq:                  0 ; 0x1d4: 0x00000000
kfdhdb.acdb.aba.blk:                  0 ; 0x1d8: 0x00000000
kfdhdb.acdb.ents:                     0 ; 0x1dc: 0x0000
kfdhdb.acdb.ub2spare:                 0 ; 0x1de: 0x0000

通过VOL4 kfed修改出VOL3 kfed

[grid@rac1 ~]$ cat vol3.txt
kfbh.endian:                          1 ; 0x000: 0x01
kfbh.hard:                          130 ; 0x001: 0x82
kfbh.type:                            1 ; 0x002: KFBTYP_DISKHEAD
kfbh.datfmt:                          1 ; 0x003: 0x01
kfbh.block.blk:                       0 ; 0x004: blk=0
*kfbh.block.obj:              2147483648 ; 0x008: disk=0
kfbh.check:                   332940500 ; 0x00c: 0x13d844d4
kfbh.fcn.base:                        0 ; 0x010: 0x00000000
kfbh.fcn.wrap:                        0 ; 0x014: 0x00000000
kfbh.spare1:                          0 ; 0x018: 0x00000000
kfbh.spare2:                          0 ; 0x01c: 0x00000000
*kfdhdb.driver.provstr:     ORCLDISKVOL3 ; 0x000: length=12
*kfdhdb.driver.reserved[0]:    860639062 ; 0x008: 0x334c4f56
kfdhdb.driver.reserved[1]:            0 ; 0x00c: 0x00000000
kfdhdb.driver.reserved[2]:            0 ; 0x010: 0x00000000
kfdhdb.driver.reserved[3]:            0 ; 0x014: 0x00000000
kfdhdb.driver.reserved[4]:            0 ; 0x018: 0x00000000
kfdhdb.driver.reserved[5]:            0 ; 0x01c: 0x00000000
kfdhdb.compat:                186646528 ; 0x020: 0x0b200000
*kfdhdb.dsknum:                        0 ; 0x024: 0x0000
kfdhdb.grptyp:                        1 ; 0x026: KFDGTP_EXTERNAL
kfdhdb.hdrsts:                        3 ; 0x027: KFDHDR_MEMBER
*kfdhdb.dskname:           XIFENFEI_0000 ; 0x028: length=13
kfdhdb.grpname:                XIFENFEI ; 0x048: length=8
*kfdhdb.fgname:            XIFENFEI_0000 ; 0x068: length=13
kfdhdb.capname:                         ; 0x088: length=0
kfdhdb.crestmp.hi:             32967790 ; 0x0a8: HOUR=0xe DAYS=0x3 MNTH=0x3 YEAR=0x7dc
kfdhdb.crestmp.lo:           2015933440 ; 0x0ac: USEC=0x0 MSEC=0x22d SECS=0x2 MINS=0x1e
kfdhdb.mntstmp.hi:             32969260 ; 0x0b0: HOUR=0xc DAYS=0x11 MNTH=0x4 YEAR=0x7dc
kfdhdb.mntstmp.lo:           3109835776 ; 0x0b4: USEC=0x0 MSEC=0x315 SECS=0x15 MINS=0x2e
kfdhdb.secsize:                     512 ; 0x0b8: 0x0200
kfdhdb.blksize:                    4096 ; 0x0ba: 0x1000
kfdhdb.ausize:                  1048576 ; 0x0bc: 0x00100000
kfdhdb.mfact:                    113792 ; 0x0c0: 0x0001bc80
*kfdhdb.dsksize:                    2353 ; 0x0c4: 0x00000931
kfdhdb.pmcnt:                         2 ; 0x0c8: 0x00000002
kfdhdb.fstlocn:                       1 ; 0x0cc: 0x00000001
kfdhdb.altlocn:                       2 ; 0x0d0: 0x00000002
*kfdhdb.f1b1locn:                      2 ; 0x0d4: 0x00000002
kfdhdb.redomirrors[0]:                0 ; 0x0d8: 0x0000
kfdhdb.redomirrors[1]:                0 ; 0x0da: 0x0000
kfdhdb.redomirrors[2]:                0 ; 0x0dc: 0x0000
kfdhdb.redomirrors[3]:                0 ; 0x0de: 0x0000
kfdhdb.dbcompat:              168820736 ; 0x0e0: 0x0a100000
kfdhdb.grpstmp.hi:             32967790 ; 0x0e4: HOUR=0xe DAYS=0x3 MNTH=0x3 YEAR=0x7dc
kfdhdb.grpstmp.lo:           2015746048 ; 0x0e8: USEC=0x0 MSEC=0x176 SECS=0x2 MINS=0x1e
kfdhdb.vfstart:                       0 ; 0x0ec: 0x00000000

后续处理

--导入kfed
[grid@rac1 ~]$ kfed merge /dev/oracleasm/disks/VOL3 text=vol3.txt
--MOUNT diskgroup
SQL> alter diskgroup xifenfei mount;
Diskgroup altered.

如果您遇到此类情况,无法解决请联系我们，提供专业ORACLE数据库恢复技术支持
Phone:17813235971    Q Q:107644445QQ咨询惜分飞    E-Mail:dba@xifenfei.com