Find user's ip address.

Below query find user's ip adderss  to use UTL_INADDR package.

select sid, machine,UTL_INADDR.get_host_address
(substr(machine,instr(machine,'\')+1))ip from v$session 
where type='USER' and username is not null order by sid;

Identify files with imbalanced allocation in an ASM diskgroup

Below SQL script to identify files with imbalanced extents across the disks in an ASM diskgroup.

Set ORACLE_SID is point to ASM instance.


select number_kffxp file#, max(count1) max,min(count1) min from
(select number_kffxp ,disk_kffxp,count(XNUM_KFFXP) count1
from x$kffxp
where group_kffxp=<group_number>
and disk_kffxp != 65534
group by number_kffxp,disk_kffxp
order by number_kffxp,disk_kffxp)
group by number_kffxp;


 FILE#        MAX        MIN
---------- ---------- ----------
      3242      2523       3414
      3243      3893       6824
      3244      7853      10581
      3924     10583     10581
      4098      6828       6827


 Max and min columns should be close to each other for balanced files



From the output above, files with imbalanced extents are file# 3242, 3243 and 3244.

File# 3924 and 4098 have extents almost equally balanced. Small imbalances are expected.

Number of extents per disk can be drilled down for certain files by a query like:

select number_kffxp ,disk_kffxp,count(XNUM_KFFXP) count1
from x$kffxp
where group_kffxp=<group_number>
and number_kffxp=<number_kffxp>





RAID type for usage Oracle Database

**************************************************************************
RAID    Type of RAID        Control         Database          Redo Log         Archive
                                               File                File                     File            log File
**************************************************************************
   0          Striping                   Avoid*           OK*                 Avoid*          Avoid* 
--------------------------------------------------------------------------------------
   1        Shadowing                 OK               OK                   Reco              Reco
--------------------------------------------------------------------------------------
0+1    Striping +Shadowing     OK            Reco $               OK               Avoid    
--------------------------------------------------------------------------------------
    3     Striping with                 OK            Avoid &           Avoid             Avoid
           Static Parity                                                                                         
--------------------------------------------------------------------------------------
    5     Striping with                OK           Avoid &           Avoid              Avoid
         Rotating Parity                     
--------------------------------------------------------------------------------------


*  -  RAID 0 does not provide any protection against failures. It requires a strong backup strategy.

$ -   RAID 0+1 is recommended for database files because this avoids hot spots and gives  the best possible        performance during a disk failure.  The disadvantage of RAID 0+1 is that it is a costly configuration.

& - When heavy write operation involves this datafile


Reco -  Recommended