KFED,KFOD,AMDU in oracle11g ASM

KFOD =Kernel Files Osm Disk
KFED =Kernel Files metadata EDitor
AMDU = ASM Metadata Dump Utility


kfed,kfod is available from Oracle Database 11g Release 1 and forward and can be used to read and write ASM metadata, in particular disk headers and ASM metadata contents. kfed in write mode is a powerful but can be potentially destructive tool in the wrong hands. kfed invoked with the -help option displays the online help for the utility.

KFOD confirm that DISK header is working fine or not .
KFOD is used during installation time also (by OUI,DBCA or ASMCA) perform disk discovery


# $GRID_HOME/bin/kfod status=TRUE asm_diskstring=’/dev/raw/raw*’ disk=all dscvgroup=TRUE
——————————————————————————–
Disk Size Header Path Disk Group User Group
================================================================================
1: 512000 Mb MEMBER /dev/raw/raw1 DATA ora11g dba
2: 512000 Mb PROVISIONED /dev/raw/raw2 DATA ora11g dba
3: 512000 Mb MEMBER /dev/raw/raw3 DATA ora11g dba
4: 512000 Mb PROVISIONED /dev/raw/raw4 DATA ora11g dba
5: 512000 Mb MEMBER /dev/raw/raw5 DATA ora11g dba
6: 512000 Mb MEMBER /dev/raw/raw6 DATA ora11g dba
——————————————————————————–
ORACLE_SID ORACLE_HOME
================================================================================
+ASM /oracle/grid11g



Kfed utility is used to read /repair disk :It allows to analyze ASM disk header information when ASM diskgroups can not be mounted.


# $GRID_HOME/bin/kfed read /dev/raw/raw2

kfbh.endian:                          1 ; 0×000: 0×01
kfbh.hard:                          130 ; 0×001: 0×82
kfbh.type:                            1 ; 0×002: KFBTYP_DISKHEAD
kfbh.datfmt:                          1 ; 0×003: 0×01
kfbh.block.blk:                       0 ; 0×004: blk=0
kfbh.block.obj:              2147483648 ; 0×008: disk=0
kfbh.check:                   945480537 ; 0x00c: 0x385ae359
kfbh.fcn.base:                        0 ; 0×010: 0×00000000
kfbh.fcn.wrap:                        0 ; 0×014: 0×00000000
kfbh.spare1:                          0 ; 0×018: 0×00000000
kfbh.spare2:                          0 ; 0x01c: 0×00000000
kfdhdb.driver.provstr:ORCLDISK ; 0×000: length=32
kfdhdb.driver.reserved[0]:     33686018 ; 0×008: 0×02020202
kfdhdb.driver.reserved[1]:     33686018 ; 0x00c: 0×02020202
kfdhdb.driver.reserved[2]:     33686018 ; 0×010: 0×02020202
kfdhdb.driver.reserved[3]:     33686018 ; 0×014: 0×02020202
kfdhdb.driver.reserved[4]:     33686018 ; 0×018: 0×02020202
kfdhdb.driver.reserved[5]:     33686018 ; 0x01c: 0×02020202
kfdhdb.compat:                186646528 ; 0×020: 0x0b200000
kfdhdb.dsknum:                        0 ; 0×024: 0×0000
kfdhdb.grptyp:                        1 ; 0×026: KFDGTP_EXTERNAL
kfdhdb.hdrsts:                        3 ; 0×027: KFDHDR_MEMBER
kfdhdb.dskname:               DATA_0000 ; 0×028: length=9
kfdhdb.grpname:                    DATA ; 0×048: length=4
kfdhdb.fgname:                DATA_0000 ; 0×068: length=9
kfdhdb.capname:                         ; 0×088: length=0
kfdhdb.crestmp.hi:             32983952 ; 0x0a8: HOUR=0×10 DAYS=0x1c MNTH=0×2 YEAR=0x7dd
kfdhdb.crestmp.lo:           3911025664 ; 0x0ac: USEC=0×0 MSEC=0×361 SECS=0×11 MINS=0x3a
kfdhdb.mntstmp.hi:             32984464 ; 0x0b0: HOUR=0×10 DAYS=0xc MNTH=0×3 YEAR=0x7dd
kfdhdb.mntstmp.lo:           3973637120 ; 0x0b4: USEC=0×0 MSEC=0×239 SECS=0xd MINS=0x3b
kfdhdb.secsize:                     512 ; 0x0b8: 0×0200
kfdhdb.blksize:                    4096 ; 0x0ba: 0×1000
kfdhdb.ausize:                  1048576 ; 0x0bc: 0×00100000
kfdhdb.mfact:                    113792 ; 0x0c0: 0x0001bc80
kfdhdb.dsksize:                  512000 ; 0x0c4: 0x0007d000
kfdhdb.pmcnt:                         6 ; 0x0c8: 0×00000006
kfdhdb.fstlocn:                       1 ; 0x0cc: 0×00000001
kfdhdb.altlocn:                       2 ; 0x0d0: 0×00000002
kfdhdb.f1b1locn:                      2 ; 0x0d4: 0×00000002
kfdhdb.redomirrors[0]:                0 ; 0x0d8: 0×0000
kfdhdb.redomirrors[1]:                0 ; 0x0da: 0×0000
kfdhdb.redomirrors[2]:                0 ; 0x0dc: 0×0000
kfdhdb.redomirrors[3]:                0 ; 0x0de: 0×0000
kfdhdb.dbcompat:              168820736 ; 0x0e0: 0x0a100000
kfdhdb.grpstmp.hi:             32983952 ; 0x0e4: HOUR=0×10 DAYS=0x1c MNTH=0×2 YEAR=0x7dd
kfdhdb.grpstmp.lo:           3909777408 ; 0x0e8: USEC=0×0 MSEC=0x29e SECS=0×10 MINS=0x3a
kfdhdb.vfstart:                       0 ; 0x0ec: 0×00000000
kfdhdb.vfend:                         0 ; 0x0f0: 0×00000000
kfdhdb.spfile:                        0 ; 0x0f4: 0×00000000
kfdhdb.spfflg:                        0 ; 0x0f8: 0×00000000
kfdhdb.ub4spare[0]:                   0 ; 0x0fc: 0×00000000
kfdhdb.ub4spare[1]:                   0 ; 0×100: 0×00000000
kfdhdb.ub4spare[2]:                   0 ; 0×104: 0×00000000
kfdhdb.ub4spare[3]:                   0 ; 0×108: 0×00000000
kfdhdb.ub4spare[4]:                   0 ; 0x10c: 0×00000000
kfdhdb.ub4spare[5]:                   0 ; 0×110: 0×00000000
kfdhdb.ub4spare[6]:                   0 ; 0×114: 0×00000000
kfdhdb.ub4spare[7]:                   0 ; 0×118: 0×00000000
kfdhdb.ub4spare[8]:                   0 ; 0x11c: 0×00000000
kfdhdb.ub4spare[9]:                   0 ; 0×120: 0×00000000
kfdhdb.ub4spare[10]:                  0 ; 0×124: 0×00000000
kfdhdb.ub4spare[11]:                  0 ; 0×128: 0×00000000
kfdhdb.ub4spare[12]:                  0 ; 0x12c: 0×00000000
kfdhdb.ub4spare[13]:                  0 ; 0×130: 0×00000000
kfdhdb.ub4spare[14]:                  0 ; 0×134: 0×00000000
kfdhdb.ub4spare[15]:                  0 ; 0×138: 0×00000000
kfdhdb.ub4spare[16]:                  0 ; 0x13c: 0×00000000
kfdhdb.ub4spare[17]:                  0 ; 0×140: 0×00000000
kfdhdb.ub4spare[18]:                  0 ; 0×144: 0×00000000
kfdhdb.ub4spare[19]:                  0 ; 0×148: 0×00000000
kfdhdb.ub4spare[20]:                  0 ; 0x14c: 0×00000000
kfdhdb.ub4spare[21]:                  0 ; 0×150: 0×00000000
kfdhdb.ub4spare[22]:                  0 ; 0×154: 0×00000000
kfdhdb.ub4spare[23]:                  0 ; 0×158: 0×00000000
kfdhdb.ub4spare[24]:                  0 ; 0x15c: 0×00000000
kfdhdb.ub4spare[25]:                  0 ; 0×160: 0×00000000
kfdhdb.ub4spare[26]:                  0 ; 0×164: 0×00000000
kfdhdb.ub4spare[27]:                  0 ; 0×168: 0×00000000
kfdhdb.ub4spare[28]:                  0 ; 0x16c: 0×00000000
kfdhdb.ub4spare[29]:                  0 ; 0×170: 0×00000000
kfdhdb.ub4spare[30]:                  0 ; 0×174: 0×00000000
kfdhdb.ub4spare[31]:                  0 ; 0×178: 0×00000000
kfdhdb.ub4spare[32]:                  0 ; 0x17c: 0×00000000
kfdhdb.ub4spare[33]:                  0 ; 0×180: 0×00000000
kfdhdb.ub4spare[34]:                  0 ; 0×184: 0×00000000
kfdhdb.ub4spare[35]:                  0 ; 0×188: 0×00000000
kfdhdb.ub4spare[36]:                  0 ; 0x18c: 0×00000000
kfdhdb.ub4spare[37]:                  0 ; 0×190: 0×00000000
kfdhdb.ub4spare[38]:                  0 ; 0×194: 0×00000000
kfdhdb.ub4spare[39]:             254613 ; 0×198: 0x0003e295
kfdhdb.ub4spare[40]:                  0 ; 0x19c: 0×00000000
kfdhdb.ub4spare[41]:                  0 ; 0x1a0: 0×00000000
kfdhdb.ub4spare[42]:                  0 ; 0x1a4: 0×00000000
kfdhdb.ub4spare[43]:                  0 ; 0x1a8: 0×00000000
kfdhdb.ub4spare[44]:                  0 ; 0x1ac: 0×00000000
kfdhdb.ub4spare[45]:                  0 ; 0x1b0: 0×00000000
kfdhdb.ub4spare[46]:                  0 ; 0x1b4: 0×00000000
kfdhdb.ub4spare[47]:                  0 ; 0x1b8: 0×00000000
kfdhdb.ub4spare[48]:                  0 ; 0x1bc: 0×00000000
kfdhdb.ub4spare[49]:                  0 ; 0x1c0: 0×00000000
kfdhdb.ub4spare[50]:                  0 ; 0x1c4: 0×00000000
kfdhdb.ub4spare[51]:                  0 ; 0x1c8: 0×00000000
kfdhdb.ub4spare[52]:                  0 ; 0x1cc: 0×00000000
kfdhdb.ub4spare[53]:                  0 ; 0x1d0: 0×00000000
kfdhdb.acdb.aba.seq:                  0 ; 0x1d4: 0×00000000
kfdhdb.acdb.aba.blk:                  0 ; 0x1d8: 0×00000000
kfdhdb.acdb.ents:                     0 ; 0x1dc: 0×0000
kfdhdb.acdb.ub2spare:             43605 ; 0x1de: 0xaa55

The header information was corrupted. kfed output for the disk affected shows 0xaa55 on kfdhdb.acdb.ub2spare field. I backed up the header information using dd command.

# dd if=/dev/raw/raw2 of=/tmp/DATA.dd bs=1M count=10

I got the allocation unit size of damaged disk.

# $GRID_HOME/bin/kfed read /dev/raw/raw2 | grep ausize

Now we can repair damaged header information.

# $GRID_HOME/bin/kfed repair /dev/raw/raw2 aus=1048576

I rechecked the header information of damaged disk using kfed utility. Now, kfed output for the disk affected shows 0×0000 on kfdhdb.acdb.ub2spare field

# $GRID_HOME/bin/kfed read /dev/raw/raw2

kfbh.endian:                          1 ; 0×000: 0×01
kfbh.hard:                          130 ; 0×001: 0×82
kfbh.type:                            1 ; 0×002: KFBTYP_DISKHEAD
kfbh.datfmt:                          1 ; 0×003: 0×01
kfbh.block.blk:                       0 ; 0×004: blk=0
kfbh.block.obj:              2147483648 ; 0×008: disk=0
kfbh.check:                   945480537 ; 0x00c: 0x385ae359
kfbh.fcn.base:                        0 ; 0×010: 0×00000000
kfbh.fcn.wrap:                        0 ; 0×014: 0×00000000
kfbh.spare1:                          0 ; 0×018: 0×00000000
kfbh.spare2:                          0 ; 0x01c: 0×00000000
kfdhdb.driver.provstr:ORCLDISK ; 0×000: length=32
kfdhdb.driver.reserved[0]:     33686018 ; 0×008: 0×02020202
kfdhdb.driver.reserved[1]:     33686018 ; 0x00c: 0×02020202
kfdhdb.driver.reserved[2]:     33686018 ; 0×010: 0×02020202
kfdhdb.driver.reserved[3]:     33686018 ; 0×014: 0×02020202
kfdhdb.driver.reserved[4]:     33686018 ; 0×018: 0×02020202
kfdhdb.driver.reserved[5]:     33686018 ; 0x01c: 0×02020202
kfdhdb.compat:                186646528 ; 0×020: 0x0b200000
kfdhdb.dsknum:                        0 ; 0×024: 0×0000
kfdhdb.grptyp:                        1 ; 0×026: KFDGTP_EXTERNAL
kfdhdb.hdrsts:                        3 ; 0×027: KFDHDR_MEMBER
kfdhdb.dskname:               DATA_0000 ; 0×028: length=9
kfdhdb.grpname:                    DATA ; 0×048: length=4
kfdhdb.fgname:                DATA_0000 ; 0×068: length=9
kfdhdb.capname:                         ; 0×088: length=0
kfdhdb.crestmp.hi:             32983952 ; 0x0a8: HOUR=0×10 DAYS=0x1c MNTH=0×2 YEAR=0x7dd
kfdhdb.crestmp.lo:           3911025664 ; 0x0ac: USEC=0×0 MSEC=0×361 SECS=0×11 MINS=0x3a
kfdhdb.mntstmp.hi:             32984464 ; 0x0b0: HOUR=0×10 DAYS=0xc MNTH=0×3 YEAR=0x7dd
kfdhdb.mntstmp.lo:           3973637120 ; 0x0b4: USEC=0×0 MSEC=0×239 SECS=0xd MINS=0x3b
kfdhdb.secsize:                     512 ; 0x0b8: 0×0200
kfdhdb.blksize:                    4096 ; 0x0ba: 0×1000
kfdhdb.ausize:                  1048576 ; 0x0bc: 0×00100000
kfdhdb.mfact:                    113792 ; 0x0c0: 0x0001bc80
kfdhdb.dsksize:                  512000 ; 0x0c4: 0x0007d000
kfdhdb.pmcnt:                         6 ; 0x0c8: 0×00000006
kfdhdb.fstlocn:                       1 ; 0x0cc: 0×00000001
kfdhdb.altlocn:                       2 ; 0x0d0: 0×00000002
kfdhdb.f1b1locn:                      2 ; 0x0d4: 0×00000002
kfdhdb.redomirrors[0]:                0 ; 0x0d8: 0×0000
kfdhdb.redomirrors[1]:                0 ; 0x0da: 0×0000
kfdhdb.redomirrors[2]:                0 ; 0x0dc: 0×0000
kfdhdb.redomirrors[3]:                0 ; 0x0de: 0×0000
kfdhdb.dbcompat:              168820736 ; 0x0e0: 0x0a100000
kfdhdb.grpstmp.hi:             32983952 ; 0x0e4: HOUR=0×10 DAYS=0x1c MNTH=0×2 YEAR=0x7dd
kfdhdb.grpstmp.lo:           3909777408 ; 0x0e8: USEC=0×0 MSEC=0x29e SECS=0×10 MINS=0x3a
kfdhdb.vfstart:                       0 ; 0x0ec: 0×00000000
kfdhdb.vfend:                         0 ; 0x0f0: 0×00000000
kfdhdb.spfile:                        0 ; 0x0f4: 0×00000000
kfdhdb.spfflg:                        0 ; 0x0f8: 0×00000000
kfdhdb.ub4spare[0]:                   0 ; 0x0fc: 0×00000000
kfdhdb.ub4spare[1]:                   0 ; 0×100: 0×00000000
kfdhdb.ub4spare[2]:                   0 ; 0×104: 0×00000000
kfdhdb.ub4spare[3]:                   0 ; 0×108: 0×00000000
kfdhdb.ub4spare[4]:                   0 ; 0x10c: 0×00000000
kfdhdb.ub4spare[5]:                   0 ; 0×110: 0×00000000
kfdhdb.ub4spare[6]:                   0 ; 0×114: 0×00000000
kfdhdb.ub4spare[7]:                   0 ; 0×118: 0×00000000
kfdhdb.ub4spare[8]:                   0 ; 0x11c: 0×00000000
kfdhdb.ub4spare[9]:                   0 ; 0×120: 0×00000000
kfdhdb.ub4spare[10]:                  0 ; 0×124: 0×00000000
kfdhdb.ub4spare[11]:                  0 ; 0×128: 0×00000000
kfdhdb.ub4spare[12]:                  0 ; 0x12c: 0×00000000
kfdhdb.ub4spare[13]:                  0 ; 0×130: 0×00000000
kfdhdb.ub4spare[14]:                  0 ; 0×134: 0×00000000
kfdhdb.ub4spare[15]:                  0 ; 0×138: 0×00000000
kfdhdb.ub4spare[16]:                  0 ; 0x13c: 0×00000000
kfdhdb.ub4spare[17]:                  0 ; 0×140: 0×00000000
kfdhdb.ub4spare[18]:                  0 ; 0×144: 0×00000000
kfdhdb.ub4spare[19]:                  0 ; 0×148: 0×00000000
kfdhdb.ub4spare[20]:                  0 ; 0x14c: 0×00000000
kfdhdb.ub4spare[21]:                  0 ; 0×150: 0×00000000
kfdhdb.ub4spare[22]:                  0 ; 0×154: 0×00000000
kfdhdb.ub4spare[23]:                  0 ; 0×158: 0×00000000
kfdhdb.ub4spare[24]:                  0 ; 0x15c: 0×00000000
kfdhdb.ub4spare[25]:                  0 ; 0×160: 0×00000000
kfdhdb.ub4spare[26]:                  0 ; 0×164: 0×00000000
kfdhdb.ub4spare[27]:                  0 ; 0×168: 0×00000000
kfdhdb.ub4spare[28]:                  0 ; 0x16c: 0×00000000
kfdhdb.ub4spare[29]:                  0 ; 0×170: 0×00000000
kfdhdb.ub4spare[30]:                  0 ; 0×174: 0×00000000
kfdhdb.ub4spare[31]:                  0 ; 0×178: 0×00000000
kfdhdb.ub4spare[32]:                  0 ; 0x17c: 0×00000000
kfdhdb.ub4spare[33]:                  0 ; 0×180: 0×00000000
kfdhdb.ub4spare[34]:                  0 ; 0×184: 0×00000000
kfdhdb.ub4spare[35]:                  0 ; 0×188: 0×00000000
kfdhdb.ub4spare[36]:                  0 ; 0x18c: 0×00000000
kfdhdb.ub4spare[37]:                  0 ; 0×190: 0×00000000
kfdhdb.ub4spare[38]:                  0 ; 0×194: 0×00000000
kfdhdb.ub4spare[39]:                  0 ; 0×198: 0×00000000
kfdhdb.ub4spare[40]:                  0 ; 0x19c: 0×00000000
kfdhdb.ub4spare[41]:                  0 ; 0x1a0: 0×00000000
kfdhdb.ub4spare[42]:                  0 ; 0x1a4: 0×00000000
kfdhdb.ub4spare[43]:                  0 ; 0x1a8: 0×00000000
kfdhdb.ub4spare[44]:                  0 ; 0x1ac: 0×00000000
kfdhdb.ub4spare[45]:                  0 ; 0x1b0: 0×00000000
kfdhdb.ub4spare[46]:                  0 ; 0x1b4: 0×00000000
kfdhdb.ub4spare[47]:                  0 ; 0x1b8: 0×00000000
kfdhdb.ub4spare[48]:                  0 ; 0x1bc: 0×00000000
kfdhdb.ub4spare[49]:                  0 ; 0x1c0: 0×00000000
kfdhdb.ub4spare[50]:                  0 ; 0x1c4: 0×00000000
kfdhdb.ub4spare[51]:                  0 ; 0x1c8: 0×00000000
kfdhdb.ub4spare[52]:                  0 ; 0x1cc: 0×00000000
kfdhdb.ub4spare[53]:                  0 ; 0x1d0: 0×00000000
kfdhdb.acdb.aba.seq:                  0 ; 0x1d4: 0×00000000
kfdhdb.acdb.aba.blk:                  0 ; 0x1d8: 0×00000000
kfdhdb.acdb.ents:                     0 ; 0x1dc: 0×0000
kfdhdb.acdb.ub2spare:                 0 ; 0x1de: 0×0000

Now, we can mount DATA diskgroup successfully.

I recommend that you should add ASM disk header information backup to your current backup plan.

I did not take a backup of damaged ASM disk header information before. So how was the recovery process above? There is a second copy of ASM disk header information. kfed repairs the corrupted block from second copy. If our second copy of the header information was corrupted also then we could repair the header information using backup.




AMDU = ASM Metadata Dump Utility

How to upgrade dataguard primary and standby databases

When you have dataguard setup in 10g version, would like to upgrade both primary & standby to 11g 
The idea is that you install the 11g binaries on both primary and standby;
shutdown the primary and the standby;
mount the standby off the 11g home,
 start the MRP; upgrade the primary.
 So the standby gets upgraded implicitly, by applying the redo generated during the upgrade of the primary.

Automatic db_file_multiblock_read_count (MBRC)

Automatic db_file_multiblock_read_count:

The default value corresponds to the maximum I/O size that can be efficiently performed and is platform-dependent

DB_FILE_MULTIBLOCK_READ_COUNT is one of the parameters you can use to minimize I/O during table scans. It specifies the maximum number of blocks read in one I/O operation during a sequential scan. The total number of I/Os needed to perform a full table scan depends on such factors as the size of the table, the multiblock read count, and whether parallel execution is being utilized for the operation.

As of Oracle Database 10g release 2, the default value of this parameter is a value that corresponds to the maximum I/O size that can be performed efficiently. This value is platform-dependent and is 1MB for most platforms.Because the parameter is expressed in blocks, it will be set to a value that is equal to the maximum I/O size that can be performed efficiently divided by the standard block size.
 

According to Oracle, this is the formula for setting db_file_multiblock_read_count:

                                 max I/O chunk size
db_file_multiblock_read_count  = -------------------
                                 db_block_size

MaxMultiblockread count=1MB/8K = 128 (approxmately)

Note that if the number of sessions is extremely large the multiblock read count value is decreased to avoid the buffer cache getting flooded with too many table scan buffers.

Online transaction processing (OLTP) and batch environments typically have values in the range of 4 to 16 for this parameter. DSS and data warehouse environments tend to benefit most from maximizing the value of this parameter. The optimizer is more likely to choose a full table scan over an index if the value of this parameter is high.
The maximum value is the operating system's maximum I/O size expressed as Oracle blocks ((max I/O size)/DB_BLOCK_SIZE). If you set this parameter to a value greater than the maximum, Oracle uses the maximum.

The "real" mbrc is actually somewhere in between since serial multiblock read requests are processed by the buffer cache and split in two or more requests if some blocks are already pinned in the buffer cache, or when the segment size is smaller than the read size.

The mbrc value gathered as part of workload statistics is thus useful for FTS estimation."

One of the new features that was released in 10gR2 is the automatic DB_FILE_MULTIBLOCK_READ_COUNT

The DB_FILE_MULTIBLOCK_READ_COUNT parameter controls the amount of block prefetching done in the buffer cache during scan operations, such as full table scan and index fast full scan. The value of this parameter can have a significant impact on the overall database performance. This feature enables Oracle Database to automatically select the appropriate value for this parameter depending on the operating system optimal I/O size and the size of the buffer cache.
This feature simplifies manageability by automating the tuning of DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter.

This parameter specifies the number of blocks that are read in a single I/O during a full table scan or index fast full scan. The optimizer uses the value of DB_FILE_MULTIBLOCK_READ_COUNT to cost full table scans and index fast full scans. Larger values result in a cheaper cost for full table scans and can result in the optimizer choosing a full table scan over an index scan. If this parameter is not set explicitly (or is set is 0), the optimizer will use a default value of 8 when costing full table scans and index fast full scans.

Statistics collection in oracle 11gR2

Oracle has addressed 2 major issues in statistics gathering. 1) Automatic statistics collection 2) Speed and accuracy by changing the sample data 1) Automatic statistics collection: In 11gR2 statistics collection is automatic by default.The statistics will be collected by using an auto job, if the statistics found to be stale for an object or if any DDL command used against the object. Please see the below for the job details. select client_name,status,consumer_group,attributes from DBA_AUTOTASK_CLIENT CLIENT_NAME STATUS CONSUMER_GROUP ------------ --------- -------------- auto optimzer stat collection ENABLED ORA$AUTOTASK_STATS_GROUP The stale percent is determined based on the number of DML operations occured on the table ie., the percentage of number of rows updated or inserted or deleted on the table. By default the threshold limit for the stale percentage is 10%. You can check this values by using the below query. select dbms_stats.GET_PREFS ('STALE_PERCENT','OWNER','TABLENAME') from dual. To check the objects considered for auto stats collection we can check the parameter AUTOSTATS_TARGET.The value of this parameter controls the objects considered for stats collection. The values: 'ALL' - Statistics collected for all objects in system 'ORACLE' - Statistics collected for all Oracle owned objects 'AUTO' - Oracle decides on which objects to collect statistics We can check this by using the below query select dbms_stats.GET_PREFS ('AUTOSTATS_TARGET') from dual; DBMS_STATS.GET_PREFS('AUTOSTATS_TARGET') ------------------------------------------- AUTO To check the percentage of rows modified or inserted or deleted on the tables you can use the below query select * from DBA_TAB_MODIFICATIONS where table_owner='SCOTT' If you need to check the information about stats collection on your tables, you can use the below query select * from DBA_TAB_STATS_HISTORY where owner='SCOTT' select client_name, JOB_SCHEDULER_STATUS from DBA_AUTOTASK_CLIENT_JOB where client_name='%stats%'; To enable or disable the Auto stats job please use the below procs. To enable or disable the Auto stats job please use the below procs. ENABLE: BEGIN DBMS_AUTO_TASK_ADMIN.ENABLE( client_name => 'auto optimizer stats collection', operation => NULL, window_name => NULL); END; / DISABLE: BEGIN DBMS_AUTO_TASK_ADMIN.DISABLE( client_name => 'auto optimizer stats collection', operation => NULL, window_name => NULL); END; / SELECT client_name, window_name, jobs_created, jobs_started, jobs_completed FROM dba_autotask_client_history WHERE client_name like '%stats%'; SELECT * FROM dba_autotask_client_history WHERE client_name like '%stats%'; Select client_name, JOB_SCHEDULER_STATUS from DBA_AUTOTASK_CLIENT_JOB where client_name='auto optimizer stats collection'; 2) Speed and accuracy by changing the sample data: Looking back to the previous major release (10.2.0) the methodology for statistics gathering relied basically on the sample size; the larger the sample size the better the statistics at the cost of increased run time to gather those statistics. Oracle also introduced the auto sample size default, which determined the sample size to use for each table. Using the default settings did produce statistics in a shorter length of time, but did so at the expense of inaccurate histograms, plainly wrong histograms, and inaccurate density values. Partitioned tables were the most often affected when sample sizes smaller than 90% were used; the auto sample size Oracle computed often hit the 30% mark leaving histogram values woefully in need of adjustment. Even though Oracle would adjust the sampled values up at the table level (to more accurately reflect the actual number of rows) the histogram values remained at the actual sampled size throwing density/selectivity calculations off by a considerable margin and adversely affecting performance based on cost calculations. Oracle 11.2.0 has corrected these issues with a better methodology that not only increases the speed of statistics collection but also provides greater accuracy of the results. An improved sampling algorithm along with a reduction of the full table scans executed has resulted in more accurate statistics in a shorter amount of time, and better statistics definitely mean better execution plans. That isn't the only improvement in 11.2.0 statistics as the histograms generated have accurate numbers, better reflecting the state of the table data distribution. Partitioned tables no longer cause Oracle to generate spurious histograms such as the frequency histograms on single-valued, non-null columns making CBO calculations more accurate. Better statistics mean better plans and less of a headache for the users and the DBAs. Notice also that the auto sample size in 11.2.0 generates statistics percentages very close to 100, indicating that the new auto sampling algorithm will run faster yet produce results rivaling a 100% sample size and do that in approximately 1/10th of the time. The less time a DBA spends on statistics is more time to address other, more pressing, issues. That's a good thing no matter how you look at it.

Enable/disable oracle components using CHOPT utility in oracle 11gR2

Enable/disable oracle components usinig chopt command like utility:

From 11g relase 2, we can enable/disable oracle components using the command line utility called CHOPT.
This utility is located in $ORACLE_HOME/bin location.

In older versions, In order to enable/disable oracle components we have to use  OUI(till 11.1) or we need to enable/disable component at binary level using relink (make) cmd(11.1.0.7)

But from 11g release 2,
Using chopt utility we can enable/disable following components:

dm = Oracle Data Mining RDBMS Files
dv = Oracle Database Vault option
lbac = Oracle Label Security
olap = Oracle OLAP
partitioning = Oracle Partitioning
rat = Oracle Real Application Testing

syntax:  chopt [ enable | disable] db_option

bash-3.00$ chopt
usage:
chopt

options:
dm = Oracle Data Mining RDBMS Files
dv = Oracle Database Vault option
lbac = Oracle Label Security
olap = Oracle OLAP
partitioning = Oracle Partitioning
rat = Oracle Real Application Testing

steps to enable/disable oracle components

1) shutdown the database for which do you want to enable/disable the oracle component.
2) enable/disable the component using chopt command.
3) startup the database and verify the component installed using v$option view.

Eg: Enable partitioning for database

select * from v$option where parameter=’ Partitioning’;

PARAMETER        VALUE
----------------------- -----------
Partitioning      FALSE

shut immediate

cd $ORACLE_HOME/bin

bash-3.00$ chopt enable Partitioning

startup

select * from v$option where parameter=’ Partitioning’;

PARAMETER        VALUE
----------------------- -----------
Partitioning      TRUE

Partitioning new features 12c

* Can move table partitions from one TS to anothes TS online:

The Oracle 12c Database has introduced a number of great new capabilities associated with online DDL activities. One of these really useful features is the capability to now move table partitions online while maintaining associated indexes.

If in one session we have an active transaction (i.e. not yet committed):

while we move a table partition in another session as we did previously:




SQL> alter table muse move partition p3 tablespace users update indexes;

  

alter table muse move partition p3 tablespace users update indexes

  

ERROR at line 1:

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired





we now use the new 12c UPDATE INDEXES ONLINE clause:



SQL> alter table muse move partition p3 tablespace users update indexes online;




The session now simply hangs until the transaction in session one completes, in a similar manner to that of an index rebuild online operation.





Upgrade DST timezone version from 4 to 14 after upgrading the db from 10.2.0.4 to 11.2.0.3

After upgrading the db from 10.2.0.4 to 11.2.0.3 find the timezone DST TZ version using the command,

SQL> SELECT version FROM v$timezone_file;


   VERSION
----------
         4

this value is less than 14, So we have to upgrade to 14.

SQL>  SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value

  2  FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;

PROPERTY_NAME                  VALUE

------------------------------ ------------------------------

DST_PRIMARY_TT_VERSION         4

DST_SECONDARY_TT_VERSION       0

DST_UPGRADE_STATE              NONE

check for the timezone .dat files 

/opt/oracle/product/11.2.0.3/dbs > ls -ltr $ORACLE_HOME/oracore/zoneinfo/time*14*

-rw-r--r--   1 oracle   dba         336K Aug 21  2011 /opt/oracle/product/11.2.0.3/oracore/zoneinfo/timezone_14.dat

-rw-r--r--   1 oracle   dba         773K Aug 21  2011 /opt/oracle/product/11.2.0.3/oracore/zoneinfo/timezlrg_14.dat

/opt/oracle/product/11.2.0.3/dbs >

Connect as SYS user and prepare for the upgrade.

SQL> exec DBMS_DST.BEGIN_PREPARE(14);
PL/SQL procedure successfully completed


SQL> SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;

PROPERTY_NAME                  VALUE
------------------------------ ------------------------------
DST_PRIMARY_TT_VERSION         4
DST_SECONDARY_TT_VERSION       14
DST_UPGRADE_STATE              PREPARE

Now you will see 14 as secondary timezone.

Truncate all the DST affected tables:

SQL> TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;
Table truncated.
SQL> TRUNCATE TABLE sys.dst$affected_tables;
Table truncated.
SQL> TRUNCATE TABLE sys.dst$error_table;
Table truncated.



Run the below PL/SQL code to log errors for DST affected tables.

SQL> BEGIN
DBMS_DST.FIND_AFFECTED_TABLES
(affected_tables => 'sys.dst$affected_tables',
log_errors => TRUE,
log_errors_table => 'sys.dst$error_table');
END;
/  2    3    4    5    6    7

PL/SQL procedure successfully completed.

Following run should return no rows which confirms that no DST tables affected during the operation.

SQL> SELECT * FROM sys.dst$affected_tables;
no rows selected

End the prepare.

SQL>  EXEC DBMS_DST.END_PREPARE;
PL/SQL procedure successfully completed.

Shut the database and startup in UPGRADE mode.

SQL> shut immediate;

Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 3424985088 bytes
Fixed Size                  2163120 bytes
Variable Size            2315263568 bytes
Database Buffers         1090519040 bytes
Redo Buffers               17039360 bytes
Database mounted.
Database opened.
SQL>

Execute the DST upgrade.

SQL> EXEC DBMS_DST.BEGIN_UPGRADE(14);

PL/SQL procedure successfully completed.

SQL>  SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;

PROPERTY_NAME                  VALUE
------------------------------ ------------------------------
DST_PRIMARY_TT_VERSION         14
DST_SECONDARY_TT_VERSION       4
DST_UPGRADE_STATE              UPGRADE

SQL>

Shut the database and Startup the database in NORMAL mode.

SQL> SELECT version FROM v$timezone_file;
VERSION
———-
14


============