Tuesday, February 18, 2014

Reading ASM Disk Header

How to Read an ASM Disk Header

As root,
/u01/app/11.2.0/grid/bin/kfed read /dev/mapper/mydisk1|egrep "(dsksize|provstr|dskname|grpname|fgname)"

kfdhdb.driver.provstr:  ORCLDISKMYDISK1 ; 0x000: length=15
kfdhdb.dskname:                 MYDISK1 ; 0x028: length=7
kfdhdb.grpname:               MYDISKGROUP ; 0x048: length=9
kfdhdb.fgname:                  MYDISK1 ; 0x068: length=7
kfdhdb.dsksize:                  524294 ; 0x0c4: 0x00080006

To list all ASM devices using blkid:
blkid|grep sd.*oracleasm|while read a b;do echo -n $a$b" scsi_id=";(echo $a|tr -d [:digit:]|tr -d [:]|cut -d"/" -f3|xargs -i scsi_id -g -s /block/{})done;

Wednesday, February 12, 2014

Rename ASM diskgroup (11.2)

Rename ASM diskgroup

$GRID_HOME/bin/renamedg phase=both verbose=true dgname=OLD_FRA newdgname=NEW_FRA asm_diskstring='ORCL:DISK01','ORCL:DISK02'

Important Note: This does not change the file names/locations in the controlfile. 

1. List all data/temp/control/redo files.

2. Relocate the controlfiles (search this blog for "Multiplexing Controlfiles"). 

3. Rename the diskgroup.

4. Rename datafiles, tempfiles, online/standby redo logfiles using "alter database rename file 'x' to 'y';" with the database in mount mode.

Tuesday, January 21, 2014

ORA-02030 v$lock v$session v$process v$rollname


SQL> grant select on v$lock to oms;
grant select on v$lock to oms
ERROR at line 1:
ORA-02030: can only select from fixed tables/views


select synonym_name,
  from dba_synonyms
 where synonym_name in ('V$LOCK','V$SESSION','V$PROCESS','V$ROLLNAME');

SYNONYM_NAME                   TABLE_NAME
------------------------------ ------------------------------
V$LOCK                         V_$LOCK
V$PROCESS                      V_$PROCESS
V$ROLLNAME                     V_$ROLLNAME
V$SESSION                      V_$SESSION

SQL> grant select on v_$lock to supersmartuser;
Grant succeeded.

Monday, December 16, 2013

Foreign Keys with Missing Indexes

If you see high "enq: TM - contention" waits, check for foreign keys missing indexes.

SELECT a.owner, decode(b.table_name, NULL, '****', 'ok') status, a.table_name, a.columns fk_columns, b.columns index_columns
FROM (SELECT a.owner,
             LISTAGG(a.column_name, ',') within GROUP(ORDER BY a.position) columns
        FROM dba_cons_columns a,
             dba_constraints b
       WHERE a.constraint_name = b.constraint_name
         AND b.constraint_type = 'R'
         AND a.owner = b.owner
    GROUP BY a.owner, a.table_name, a.constraint_name) a,
             (SELECT table_name,
                     LISTAGG(c.column_name, ',') within GROUP(ORDER BY c.column_position) columns
                FROM dba_ind_columns c
              GROUP BY table_name, index_name) b
WHERE a.table_name = b.table_name(+)
  AND a.owner = '&OWNER'
  AND b.columns(+) LIKE a.columns || '%'
ORDER BY status, table_name

Thursday, December 5, 2013

AWR Interval and Retention

       extract( day from snap_interval) *24*60+
       extract( hour from snap_interval) *60+
       extract( minute from snap_interval ) "Snapshot Interval",
       extract( day from retention) *24*60+
       extract( hour from retention) *60+
       extract( minute from retention ) "Retention Interval"
from dba_hist_wr_control;

Snapshot Interval Retention Interval
----------------- ------------------
               60              10080

Wednesday, September 11, 2013

Hangcheck Timer Not Needed for 11gR2 RAC

Hangcheck Timer FAQ (Doc ID 232355.1)

Do I need the hangcheck-timer with 11gR2 ?

The hangcheck-timer is no[t] needed with 11gR2. This is documented in 'Oracle® Grid Infrastructure Installation Guide 11g Release 2 (11.2) for Linux' section 'Improved Input/Output Fencing Processes'.

Improved Input/Output Fencing Processes

Oracle Clusterware 11g release 2 (11.2) replaces the oprocd and Hangcheck processes with the cluster synchronization service daemon Agent and Monitor to provide more accurate recognition of hangs and to avoid false termination.

Monday, August 19, 2013

Identifying Corruption


Find Corrupt Segments
set linesize 140
col owner format a10
col segment_type format a10
col segment_name format a30
col partition_name format a15
col file# format 999
SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
       , greatest(e.block_id, c.block#) corr_start_block#
       , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
       , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) 
         - greatest(e.block_id, c.block#) + 1 blocks_corrupted
       , null description
    FROM dba_extents e, v$database_block_corruption c
   WHERE e.file_id = c.file#
     AND e.block_id <= c.block# + c.blocks - 1
     AND e.block_id + e.blocks - 1 >= c.block#
  SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
       , header_block corr_start_block#
       , header_block corr_end_block#
       , 1 blocks_corrupted
       , 'Segment Header' description
    FROM dba_segments s, v$database_block_corruption c
   WHERE s.header_file = c.file#
     AND s.header_block between c.block# and c.block# + c.blocks - 1
  SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
       , greatest(f.block_id, c.block#) corr_start_block#
       , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
       , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) 
         - greatest(f.block_id, c.block#) + 1 blocks_corrupted
       , 'Free Block' description
    FROM dba_free_space f, v$database_block_corruption c
   WHERE f.file_id = c.file#
     AND f.block_id <= c.block# + c.blocks - 1
     AND f.block_id + f.blocks - 1 >= c.block#
  order by file#, corr_start_block#