ASM disk to OS device to SCSI Id's (Linux)

Use below scripts to get the needed mapping between the ASM raw disks to OS devices to SCSI ID's.
use below script if your Unix admin is using dev rules for mapping....

run below one as oracle user ... if needed this to run as grid owner then change the logic below..

1) 1st script:-
disk_info.sql

{

col NAME for a30
col PATH for a30
spool asm_info.log
select NAME,PATH,OS_MB from V$ASM_DISK order by NAME asc;
spool off

}

2) 2nd script which is *.sh

ASM_disk_SCSI_shell.sh

{

COMMAND=$0

PGM=${COMMAND##*/}

host_name=`hostname | cut -d"." -f1`

# Remove nohup.out files if any from previous execution.

if [ -f nohup.out ]; then

   rm nohup.out

fi

#

## Choose directory/Path or hit enter to choose default path(currect directory).

echo "Enter the unix directory path to create the reports.Press Enter to create in current directory:"

read dir

if [ "$dir" = "" ]

then

repdir=$PWD

else

repdir=$dir

cp $PWD/*.sql $repdir

fi

##

### Remove the old log files.

cd $repdir

rm $repdir/*.log

rm $repdir/*.html

###

#### Chech to find which database/instance is up and running based on crontab entries.

   for db in `cat /etc/oratab| egrep -v '(^#|\+|\-)'|cut -f1 -d: -s|egrep -v 'OMS'|egrep -v 'AGENT'`

       do

         echo "$db";

         db_check=`pgrep -al pmon|grep $db |cut -f3,4 -d_`

            if [ -n "$db_check" ]

              then

              db_sid=$db_check

              # echo "$db_sid"

                echo "$db"

                dbhome=`cat /etc/oratab|egrep $db|egrep -v '(^#|\+)'|cut -f2 -d: -s`

                echo "$dbhome"

                uname -a

                break

                else

                echo "db_check is empty";

            fi

      done

####

### /// Setting environment variables.

PATH=/usr/bin:/etc:/usr/sbin:/usr/ucb:$HOME/bin:/usr/bin/xterm:/sbin:/opt/freeware/bin:/bin:.

export ORACLE_SID=$db_check

export ORACLE_HOME=$dbhome

PATH=$ORACLE_HOME/bin:$PATH

LIBPATH=$ORACLE_HOME/lib:$LIBPATH

### ///

#### Login into database to get the Disk info.

sqlplus / as sysdba << EOF

set heading off

set feedback off

set echo off

set pagesize 0

@$repdir/disk_info.sql

exit

EOF

sort -b -V -k 2 $repdir/asm_info.log >>$repdir/asm_info_cut.log_1

mv $repdir/asm_info_cut.log_1 $repdir/asm_info.log

cat asm_info.log| awk '{print $2}' >> $repdir/asm_info_cut.log

cat asm_info_cut.log | sort -V >asm_info_cut_1.log

mv asm_info_cut_1.log asm_info_cut.log

cat asm_info_cut.log|while read disk

do

echo "`cat /etc/udev/rules.d/60-raw.rules | grep -v '^#' | grep -w $disk | awk '{print $2}' | grep -Po '".*?"' | grep -oP '"\K[^"]+' `" >> $repdir/asm_disk_lun_info.log

echo "` raw -qa | grep -w $disk | awk '{print $1,$5,$7}' | sed 's/,//;s/://g' | awk '{ print $1"\t"$2":"$3}' `" >>asm_disk_lun_info_m.log

done

cat $repdir/asm_disk_lun_info.log |while read rawd

do

echo "$rawd  `lsscsi | grep $rawd''  | tr '[' '\t' | tr ']' '\t' | awk '{print $1}'` " >> $repdir/asm_raw-physical.log

done

paste $repdir/asm_info.log $repdir/asm_disk_lun_info.log $repdir/asm_raw-physical.log| awk  '

BEGIN {

format = "%-20s %-18s %-19s %-18s  %-19s %s\n"

printf format,  "ASM_groups", "ASM_RAW_DISK",  "OS_DISK_SIZE", "UNIX_MAP_Disk", "LINUX_id",  "LINUX_SCSI_id"

printf format,  "----------", "-------------", "------------", "-------------", "--------", "-------------"}

{for(i=1;i<=NF;i++)

printf("%-20s%c", $i, (i==NF) ? ORS : "")}' >> $repdir/asm_info_final.log;

exit;

}

Hope this helps...

tablespace script for 11g

select a.TABLESPACE_NAME,round((a.max_size/1024/1024/1024),3) MAX_SIZE_GB,
  ROUND(a.TOTSIZE/1024/1024/1024,3) as ALLOC_SPACE_GB ,
  ROUND((b.used)/1024/1024/1024,3) as Used_SPACE_GB ,
  ROUND(((b.used)/1024/1024/1024)/(a.TOTSIZE/1024/1024/1024)*100,3) as USED_PERCENT_ALLOC,
  ROUND(((b.used)/1024/1024/1024)/(a.max_size/1024/1024/1024)*100,3) as USED_PERCENT_MAXSIZE,
  ROUND((((a.totsize)/1024/1024/1024)- ((b.used)/1024/1024/1024)),3 ) as FREE_SPACE_TO_ALLOC_GB,
  ROUND((((a.TOTSIZE  - (B.USED)) / a.TOTSIZE) * 100),0) as  FREE_SPACE_PERCENT_ALLOC,
  (select instance_name  from v$instance@'DB_STRING') as instancename,
(select host_name  from v$instance@'DB_STRING') as servername
FROM
  (SELECT tablespace_name, SUM(bytes) totsize,sum(maxbytes) max_size
  FROM dba_data_files@'DB_STRING'
  GROUP BY tablespace_name
  ) a,
  (SELECT tablespace_name,SUM(bytes) used
  FROM dba_segments@'DB_STRING'
  GROUP BY tablespace_name
  ) b
where a.TABLESPACE_NAME=B.TABLESPACE_NAME

union

select  a.tablespace_name, (round((a.maxbytes/1024/1024/1024),3)) MAX_SIZE_GB, (round((a.bytes/1024/1024/1024),3))  ALLOC_SPACE_GB,
(round(((b.used_space*c.block_size)/1024/1024/1024),3))  Used_SPACE_GB,
(round((((b.used_space*c.block_size)/1024/1024/1024)/(a.bytes/1024/1024/1024))*100,3)) USED_PERCENT_ALLOC,
(round((((b.used_space*c.block_size)/1024/1024/1024)/(a.maxbytes/1024/1024/1024))*100,3)) USED_PERCENT_MAXSIZE,
 ROUND((((a.bytes)/1024/1024/1024)- ((b.used_space)/1024/1024/1024)),3 ) as FREE_SPACE_TO_ALLOC_GB,
 ROUND((((a.bytes  - (b.used_space)) / a.bytes) * 100),0) as  FREE_SPACE_PERCENT_ALLOC,
  (select instance_name  from v$instance@'DB_STRING') as instancename,
(select host_name  from v$instance@'DB_STRING') as servername
from
dba_temp_files@'DB_STRING'  a,dba_tablespace_usage_metrics@'DB_STRING' b,dba_tablespaces@'DB_STRING' c
where a.tablespace_name=b.tablespace_name
and b.tablespace_name=c.tablespace_name
and a.tablespace_name  in (''TEMP'')
group by a.tablespace_name,(round((a.maxbytes/1024/1024/1024),3)),(round((a.bytes/1024/1024/1024),3)),
(round(((b.used_space*c.block_size)/1024/1024/1024),3)),
(round((((b.used_space*c.block_size)/1024/1024/1024)/(a.bytes/1024/1024/1024))*100,3)),
(round((((b.used_space*c.block_size)/1024/1024/1024)/(a.maxbytes/1024/1024/1024))*100,3)),
ROUND((((a.bytes)/1024/1024/1024)- ((b.used_space)/1024/1024/1024)),3) ,
ROUND((((a.bytes  - (b.used_space)) / a.bytes) * 100),0)

union

select a.tablespace_name, (round((a.maxbytes/1024/1024/1024),3)) MAX_SIZE_GB, (round((a.bytes/1024/1024/1024),3))  ALLOC_SPACE_GB,
(round(((b.used_space*c.block_size)/1024/1024/1024),3))  Used_SPACE_GB,
(round((((b.used_space*c.block_size)/1024/1024/1024)/(a.bytes/1024/1024/1024))*100,3)) USED_PERCENT_ALLOC,
(round((((b.used_space*c.block_size)/1024/1024/1024)/(a.maxbytes/1024/1024/1024))*100,3)) USED_PERCENT_MAXSIZE,
 ROUND((((a.bytes)/1024/1024/1024)- ((b.used_space)/1024/1024/1024)),3 ) as FREE_SPACE_TO_ALLOC_GB,
 ROUND((((a.bytes  - (b.used_space)) / a.bytes) * 100),3) as  FREE_SPACE_PERCENT_ALLOC,
 (select instance_name  from v$instance@'DB_STRING') as instancename,
(select host_name  from v$instance@'DB_STRING') as servername
from
dba_data_files@'DB_STRING'  a,dba_tablespace_usage_metrics@'DB_STRING' b,dba_tablespaces@'DB_STRING' c
where a.tablespace_name=b.tablespace_name
and b.tablespace_name=c.tablespace_name
and a.tablespace_name not like (''TEMP'')
and a.tablespace_name not in (select distinct tablespace_name from dba_segments@'DB_STRING')
group by a.tablespace_name,(round((a.maxbytes/1024/1024/1024),3)),(round((a.bytes/1024/1024/1024),3)),
(round(((b.used_space*c.block_size)/1024/1024/1024),3)),
(round((((b.used_space*c.block_size)/1024/1024/1024)/(a.bytes/1024/1024/1024))*100,3)),
(round((((b.used_space*c.block_size)/1024/1024/1024)/(a.maxbytes/1024/1024/1024))*100,3)),
ROUND((((a.bytes)/1024/1024/1024)- ((b.used_space)/1024/1024/1024)),3) ,
ROUND((((a.bytes  - (b.used_space)) / a.bytes) * 100),3);


hope this helps...

script to reset sequence to 1 or reset sequence to greater than the current value

Use below script to reset value to 1 or less that the current value.

1)

Easy way to reset the sequence to a desired value quickly.

alter sequence NAME_ID_SEQ restart start with  1;

 alter sequence NAME_ID_SEQ increment by 1; 

2) Script:

create or replace procedure set_seq_to( seq_name in varchar2,

New_val in number )

 as

 l_curr number;

 begin

 execute immediate 'select ' || seq_name || '.nextval from dual' INTO l_curr;

 execute immediate 'alter sequence ' || seq_name || ' increment by ' || (New_val-l_curr-1) || ' minvalue 0';

 execute immediate 'select ' || seq_name || '.nextval from dual' INTO l_curr;

 execute immediate 'alter sequence ' || seq_name ||  ' increment by 1 ';

dbms_output.put_line ( 'Sequence ' || seq_name || ' is now at ' || New_val );

 end;

 /

Exec set_seq_to(‘seq_name’,reset-to-value);

'select ' seq_name.nextval from dual  -- run couple of times…. To make lastnumber greater than minvalue…

'alter sequence seq_name increment by 1  minvalue 1;

2)

greater than current value...
if you want to increase sequence to a value greater than the current value by 2414 then do below steps.

alter sequence NAME_ID_SEQ  increment by 2415;

alter sequence NAME_SEQ  increment by 1;

hope this helps..

OS Semaphores issue with multiple errors

When we have multiple DB with high processes for each database we will see this below semaphores issue when they are set to default numbers.

ORA-27154: post/wait create failed

ORA-27300: OS system dependent operation:semget failed with status: 28

ORA-27301: OS failure message: No space left on device

ORA-27302: failure occurred at: sskgpcreates

edit /etc/sysctl.conf file from defaults.

kernel.sem = 250 32000 100 128 

change that to 

kernel.sem = 250 32000 100 256

you can change them on run time using below command

/sbin/sysctl -p 

on Linux check these...
ipcs -s | awk '{print $5}' |awk '{ sum+=$1} END {print sum}'
ipcs | wc -l ---- this number should not be more than "max number of arrays in ipcs -ls"  ... if you see higher number then try to increase the number.

Ref: Doc ID 949468.1 Doc applies to 12c, too. 

Hope this helps .....