Script to Find Unopened files in ASM

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

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;

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.

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.

Foreign Keys with Missing Indexes

--FK Constraint columns not indexed

set lines 160set pages 40col FKCons       format a30col SourceTable  format a30col SourceColumn format a30col TargetTable  format a30col TargetColumn format a30select scc.constraint_name  as FKCons,       scc.table_name       as SourceTable,       scc.column_name      as SourceColumn,       tcc.table_name       as TargetTable,       tcc.column_name      as TargetColumn        from all_cons_columns scc,        all_constraints sc,       all_cons_columns tcc where sc.constraint_name = scc.constraint_name    --Join Cons to Cons Cols   and sc.r_constraint_name = tcc.constraint_name  --Join source to target   and sc.constraint_type = 'R'                    --RI Constraints only   and scc.owner = '&SCHEMA'                           --Only OMS schema   and tcc.table_name||tcc.column_name 
       not in (select i.table_name||i.column_name                  from all_ind_columns i                where i.index_owner = '&SCHEMA')       --FKs not indexed order by scc.table_name/

I can only guess this was a problem in prior releases of the database.  According to this test in 12c, it doesn't seem possible.


SQL> create table source (sourceid number, constraint sourcepk primary key (sourceid));
Table created.

SQL> create table target (targetid number, constraint targetpk primary key (targetid));
Table created.

SQL> alter table target add constraint sourceidfk foreign key (targetid) references source (sourceid);
Table altered.

SQL> col table_name format a20
SQL> col index_name format a20
SQL> col column_name format a20

SQL> select table_name,index_name,column_name from all_ind_columns where index_owner = 'KEN';

TABLE_NAME           INDEX_NAME           COLUMN_NAME
-------------------- -------------------- --------------------
TARGET               TARGETPK             TARGETID
SOURCE               SOURCEPK             SOURCEID

SQL> drop index sourcepk;
drop index sourcepk
           *
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key

SQL> alter table source disable constraint sourcepk;
alter table source disable constraint sourcepk
*
ERROR at line 1:
ORA-02297: cannot disable constraint (KEN.SOURCEPK) - dependencies exist

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

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 not 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'.

Oracle 11gR2 Whatsnew Doc

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.