Tuesday, October 14, 2014


Script to Find Unopened files in ASM


set pagesize 0
set linesize 200
col full_alias_path format a80

select * from (
select  x.gnum,x.filnum,x.full_alias_path,f.ftype from (
SELECT gnum,filnum,concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path
FROM (SELECT g.name gname, a.parent_index pindex, a.name aname,
            a.reference_index rindex,a.group_number gnum,a.file_number filnum
      FROM v\$asm_alias a, v\$asm_diskgroup g
      WHERE a.group_number = g.group_number)
START WITH (mod(pindex, power(2, 24))) = 0 CONNECT BY PRIOR rindex = pindex) x,
(select group_number gnum,file_number filnum, type ftype from v\$asm_file order by group_number,file_number) f
where x.filnum != 4294967295
and x.gnum=f.gnum and x.filnum=f.filnum
MINUS
select x.gnum,x.filnum,x.full_alias_path,f.ftype
from ( select id1 gnum,id2 filnum from v\$lock where type='FA' and (lmode=4 or lmode=2)) l,
(
SELECT gnum,filnum,concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path
FROM (SELECT g.name gname, a.parent_index pindex, a.name aname,
            a.reference_index rindex,a.group_number gnum,a.file_number filnum
      FROM v\$asm_alias a, v\$asm_diskgroup g
      WHERE a.group_number = g.group_number)
START WITH (mod(pindex, power(2, 24))) = 0 CONNECT BY PRIOR rindex = pindex
) x,
(select group_number gnum,file_number filnum, type ftype from v\$asm_file order by group_number,file_number) f
where x.filnum != 4294967295 and
x.gnum=l.gnum
and x.filnum=l.filnum
and x.gnum=f.gnum and x.filnum=f.filnum) q
order  by q.gnum,q.ftype
/

Sample Output
1      13460 +MYDG1/LEGACYDB1/DATAFILE/indx01.dbf          DATAFILE

1      12440 +MYDG2/LEGACYDB2/DATAFILE/temp01.dbf          TEMPFILE


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


Error

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

Solution

select synonym_name,
       table_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.

SQL
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,
             a.table_name,
             a.constraint_name,
             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,
                     index_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


SQL
select
       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;

SAMPLE OUTPUT
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 ?

Answer
-----------
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.