分类目录归档：Oracle

通过kfed说明asm disk header定义

发表于 2016 年 9 月 9 日 由 惜分飞

kfed读取数据磁盘头主要参数解释说明

   % kfed read /dev/raw/raw1
     
   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: T=0 NUMB=0x0
   kfbh.block.obj:              2147483648 ; 0x008: TYPE=0x8 NUMB=0x0
   kfbh.check:                  2932902794 ; 0x00c: 0xaed08b8a
   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:         ORCLDISK ; 0x000: length=8
   kfdhdb.driver.reserved[0]:            0 ; 0x008: 0x00000000
   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:                168820736 ; 0x020: 0x0a100000
   kfdhdb.dsknum:                        0 ; 0x024: 0x0000
   kfdhdb.grptyp:                        1 ; 0x026: KFDGTP_EXTERNAL
   kfdhdb.hdrsts:                        3 ; 0x027: KFDHDR_MEMBER
   kfdhdb.dskname:              ASM01_0000 ; 0x028: length=10
   kfdhdb.grpname:                   ASM01 ; 0x048: length=5
   kfdhdb.fgname:               ASM01_0000 ; 0x068: length=10
   kfdhdb.capname:                         ; 0x088: length=0
   kfdhdb.crestmp.hi:             32837774 ; 0x0a8: HOUR=0xe DAYS=0x4 MNTH=0x4 YEAR=0x7d4
   kfdhdb.crestmp.lo:           1555722240 ; 0x0ac: USEC=0x0 MSEC=0x29c SECS=0xb MINS=0x17
   kfdhdb.mntstmp.hi:             32837774 ; 0x0b0: HOUR=0xe DAYS=0x4 MNTH=0x4 YEAR=0x7d4
   kfdhdb.mntstmp.lo:           1563864064 ; 0x0b4: USEC=0x0 MSEC=0x1ab SECS=0x13 MINS=0x17
   kfdhdb.secsize:                     512 ; 0x0b8: 0x0200
   kfdhdb.blksize:                    4096 ; 0x0ba: 0x1000
   kfdhdb.ausize:                  1048576 ; 0x0bc: 0x00100000
   kfdhdb.mfact:                    113792 ; 0x0c0: 0x0001bc80
   kfdhdb.dsksize:                    9075 ; 0x0c4: 0x00002373
   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.ub4spare[0]:                   0 ; 0x0e0: 0x00000000
   ...
   kfdhdb.ub4spare[60]:                  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
 
  Breakdown:

   kfbh.endian  
     kf3.h   /* endianness of writer */ 
       Little endian = 1  
       Big endian = 0
  
   kfbh.hard    
     kf3.h   /* H.A.R.D. magic # and block size */  

  kfbh.type
    kf3.h    /* metadata block type               */

  kfbh.datfmt
    kf3.h   /* metadata block data format        */

  kfbh.block
    kf3.h   /* block location of this block      */
      blk -- Disk header should have T=0 and NUMB=0x0
      obj -- Disk header should have TYPE=0x8 NUMB=<disknumber>
    blk and obj values are derived from a series of macros in kf3.h.  See 
    "KFBL Macros" in kf3.h for more information.

  kfbh.check
    kf3.h   /* check value to verify consistency */

  kfbh.fcn
    kf3.h   /* change number of last change      */
     
  kfdhdb.driver
    kf3.h   /* OSMLIB driver reserved block  */
       If no driver is defined "ORCLDISK" is used.  
      
  kfdhdb.compat
    kf3.h   /* Comaptible software version   */
      example: 0x0a100000 
      You get:      
          a=10 1=1 so 10.1.0.0.0

  kfdhdb.dsknum
    kf3.h   /* OSM disk number               *
      This is the disk number.  The first disk being "0".  There can be up to
      ub2 disks in a diskgroup.  This allows for 65336 disks 0 through 65335.

  kfdhdb.grptyp
    kf3.h   /* Disk group type               */

  kfdhdb.hdrsts
    kf3.h   /* Disk header status            */
      This is what is used to determine if a disk is available or not to 
      the diskgroup.  0x03 is the correct value for a valid status.

  kfdhdb.dskname   /* OSM disk name       */
  kfdhdb.grpname   /* OSM disk group name */
  kfdhdb.fgname    /* Failure group name  */
  kfdhdb.capname   /* Capacity grp, unused*/
    kf3.h 

  kfdhdb.crestmp   /* Creation timestamp            */
  kfdhdb.mntstmp   /* Mount timestamp               */
    kf3.h To derive the hi and low time`from an unformated dump use the 
    "KFTS Macros" in kf3.h.

  kfdhdb.secsize
    kf3.h   /* Disk sector size (bytes)      */
      This is the physical sector size of the disk in bytes. All I/O's to the
      disk are described in physical sectors. This must be a power of 2. An
      ideal value would be 4096, but most disks are formatted with 512 byte
      sectors. (from asmlib.h)

  kfdhdb.blksize
    kf3.h   /* Metadata block (bytes)        */
       
  kfdhdb.ausize
    kf3.h   /* Allocation Unit (bytes)       */

  kfdhdb.mfact 
    kf3.h   /* Stride between phys addr AUs  */
     
  kfdhdb.dsksize
    kf3.h   /* Disk size in AUs              */
      Mulitply by AUs to get actual size of disk when added.  
         
  kfdhdb.pmcnt
    kf3.h   /* Permanent phys addressed AUs  */
      Number of physically addressed allocation units.

  kfdhdb.fstlocn
    kf3.h   /* First FreeSpace table blk num */
      Used to find freespace.

  kfdhdb.altlocn
    kf3.h   /* First Alocation table blk num */
      Used to find alocated space.

  kfdhdb.f1b1locn
    kf3.h   /* File Directory blk 1 AU num   */
      Beginging for file directory.
发表在 Oracle ASM | 标签为 , , | 评论关闭

通过update _NEXT_OBJECT 实现obj$.obj#和obj$.dataobj#跳号

发表于 2016 年 9 月 6 日 由 惜分飞

在一些特殊的情况下(比如ORA-00600 [15267],ORA-00600 [KKDLCOB-OBJN-EXISTS],Ora-600 [15260])，考虑需要把dba_objects中的object_id往前推进，这里通过试验的方法实现该功能
数据库版本信息

SQL> select * from v$version;

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

分析obj和dataobj

SQL> select max(obj#),max(dataobj#) from obj$;

 MAX(OBJ#) MAX(DATAOBJ#)
---------- -------------
     51887         51907

SQL> select name from obj$ where obj#=51887;

NAME
------------------------------
T_DUL

SQL> select name from obj$ where dataobj#=51907;

NAME
------------------------------
_NEXT_OBJECT

SQL> select object_id,data_object_id from dba_objects where object_name='_NEXT_OBJECT';

no rows selected

为什么dba_objects中无_NEXT_OBJECT
因为dba_objects视图中跳过了_NEXT_OBJECT这条记录
_next_object

测试创建新表后obj和dataobj的变化

SQL>  create table t_xff as select * from dual;

Table created.

SQL> select max(obj#),max(dataobj#) from obj$;

 MAX(OBJ#) MAX(DATAOBJ#)
---------- -------------
     51898         51907

SQL> select name from obj$ where obj#=51898;

NAME
------------------------------
T_XFF

SQL> select max(object_id),max(data_object_id) from dba_objects where object_name='T_XFF';

MAX(OBJECT_ID) MAX(DATA_OBJECT_ID)
-------------- -------------------
         51898               51898

通过测试可以确定,obj发生增加,但是dataobj不一定增加(因为dataobj本身比obj大，如果出现obj>dataobj那属于异常情况）

测试数据库重启obj和dataobj是否会跳号

---正常重启数据库
SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP
ORACLE instance started.

Total System Global Area  260046848 bytes
Fixed Size                  1266920 bytes
Variable Size              83888920 bytes
Database Buffers          171966464 bytes
Redo Buffers                2924544 bytes
Database mounted.
Database opened.
SQL> select max(obj#),max(dataobj#) from obj$;

 MAX(OBJ#) MAX(DATAOBJ#)
---------- -------------
     51898         51907

---强制重启数据库
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  260046848 bytes
Fixed Size                  1266920 bytes
Variable Size              83888920 bytes
Database Buffers          171966464 bytes
Redo Buffers                2924544 bytes
Database mounted.
Database opened.
SQL> select max(obj#),max(dataobj#) from obj$;

 MAX(OBJ#) MAX(DATAOBJ#)
---------- -------------
     51898         51907

通过这个证明obj和dataobj没有因为数据库重启而发生改变

实现obj跳号

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

Total System Global Area  260046848 bytes
Fixed Size                  1266920 bytes
Variable Size              83888920 bytes
Database Buffers          171966464 bytes
Redo Buffers                2924544 bytes
Database mounted.
Database opened.
SQL>  update obj$ set dataobj#=1000000 where name='_NEXT_OBJECT';  

1 row updated.

SQL> commit;

Commit complete.

SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  260046848 bytes
Fixed Size                  1266920 bytes
Variable Size              83888920 bytes
Database Buffers          171966464 bytes
Redo Buffers                2924544 bytes
Database mounted.
Database opened.

SQL> select max(obj#),max(dataobj#) from obj$;

 MAX(OBJ#) MAX(DATAOBJ#)
---------- -------------
     51898       1000000

SQL> create table t_www_xifenfei_com as select * from dual;

Table created.

SQL> select max(obj#),max(dataobj#) from obj$;

 MAX(OBJ#) MAX(DATAOBJ#)
---------- -------------
   1000000       1000010

SQL> select max(object_id),max(data_object_id) from dba_objects;

MAX(OBJECT_ID) MAX(DATA_OBJECT_ID)
-------------- -------------------
       1000000             1000000

SQL> select object_name from dba_objects where object_id=1000000;

OBJECT_NAME
----------------------------------------------------------------
T_WWW_XIFENFEI_COM

通过丢_NEXT_OBJECT的更新实现obj和dataobj跳号(变成100w)

发表在 Oracle | 标签为 , , , | 评论关闭

使用alter system events导致库crash

发表于 2016 年 9 月 5 日 由 惜分飞

由于数据库导入大量数据的时候io等待比较高，新的存储无法直接挂过来，考虑使用nfs挂载过来，然后存放redo缓解io压力。
数据库版本信息

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
NLSRTL Version 10.2.0.4.0 - Production

挂载参数(mount命令查看)

10.240.10.1 /top/data4/nfs   /back1            nfs3   
Aug 29 13:40 cio,rw,bg,hard,nointr,rsize=32768,wsize=32768,proto=tcp,noac,vers=3,timeo=600

尝试创建redo

SQL> alter database add logfile group 13 ('/back/newxff/redo13.log') size 2048m; 
alter database add logfile group 13 ('/back1/newxff/redo13.log') size 2048m
*
ERROR at line 1:
ORA-00301: error in adding log file '/back1/newxff/redo13.log' - file cannot be
created
ORA-27054: NFS file system where the file is created or resides is not mounted
with correct options
Additional information: 6

根据mos文档
ORA-27054 ERRORS WHEN RUNNING RMAN WITH NFS (文档 ID 387700.1)

SQL> Alter system set events '10298 trace name context forever,level 32';

System altered.

Mon Sep  5 10:10:18 2016
Thread 1 advanced to log sequence 109 (LGWR switch)
  Current log# 1 seq# 109 mem# 0: +DATA/xff/onlinelog/group_1.257.921671023
Mon Sep  5 10:12:19 2016
OS Pid: 160710 executed alter system set events '10298 trace name context forever,level 32'

创建redo成功

SQL> alter database add logfile group 13 ('/back1/newxff/redo13.log') size 2048m;

System altered.

Mon Sep  5 10:18:13 2016
alter database add logfile group 13 ('/back1/newxff/redo13.log') size 2048m
Mon Sep  5 10:18:43 2016
Completed: alter database add logfile group 13 ('/back1/newxff/redo13.log') size 2048m

数据库crash

Mon Sep  5 10:19:06 2016
Errors in file /opt/oracle/admin/xff/bdump/xff1_lgwr_246566.trc:
ORA-00313: open failed for members of log group 13 of thread 1
ORA-00312: online log 13 thread 1: '/back1/newxff/redo13.log'
ORA-27054: NFS file system where the file is created or resides is not mounted with correct options
Additional information: 6
Mon Sep  5 10:19:06 2016
Errors in file /opt/oracle/admin/xff/bdump/xff1_lgwr_246566.trc:
ORA-00313: open failed for members of log group 13 of thread 1
ORA-00312: online log 13 thread 1: '/back1/newxff/redo13.log'
ORA-27054: NFS file system where the file is created or resides is not mounted with correct options
Additional information: 6
Mon Sep  5 10:19:06 2016
LGWR: terminating instance due to error 313
Mon Sep  5 10:19:06 2016
System state dump is made for local instance
System State dumped to trace file /opt/oracle/admin/xff/bdump/xff1_diag_299654.trc

通过报错很明显可以看出来数据库挂掉的原因和当时不能创建redo的原因一样，都是由于ORA-27054导致数据库挂了，但是为什么创建redo成功,但是使用redo失败呢？
这里需要注意使用的命令是events,而这个命令是对当前会话和后续新建的会话生效，也就是说他不会对数据库已经存在的后台进程生效，那也就可以理解了，我创建redo是在执行events的当前命令行窗口处理的，因此可以创建成功；但是lgwr进程是数据库一启动就存在的进程，现在设置的events对他没有影响，因此当lgwr去使用redo的时候无法正常使用因此就导致数据库crash掉。如果希望event对已经存在的进程生效，可以考虑使用oradebug对进程进行设置event(这个案例主要要设置多个后台进程不光lgwr访问redo)，或者设置event=的方式，然后重启数据库让其生效。

发表在 Oracle | 标签为 , , | 评论关闭