How To Know the Current Patches released

Steps to find what current patches released as of Date:

Oracle Database:

1. Login to oracle support
2. Type 1454618.1 and hit search
3. Select  the option you need (ex: Oracle database PSU,SPU(CPU),Bundle Patches (versions 12.1 & lower).
4. then select 12.1.0.2 (if 12c)
5. then select the related patch you need from the list.

Oracle Enterprise Manager:

1) Checking for future releases:

NOTE:793512.1 - Release Schedule of Enterprise Manager Releases and Patchsets (10g, 11g, 12c, 13c)

2) To find Current installed OMS list of patches:

How to Determine the Patch Set Update (PSU) Applied to the Enterprise Manager 13c Cloud Control OMS and the Bundle Patch applied to Plug-ins and Agent (Doc ID 2319892.1)

For the EM 13c Cloud Control OMS, issue this command to find the effective PSU level of the EM 13c OMS:

<OMS Oracle Home>/OPatch/opatch lsinventory | grep -i 'EMBP Patch Set Update'

To see the current version according to the Plugin Bundle Patches for the EM 13c OMS plug-ins, use the following:

<OMS Oracle Home>/OPatch/opatch lsinventory | grep -i 'Plugin Bundle Patch'

For the EM 13c Cloud Control Agent, issue this command to the the current Bundle Patch level applied:

<OMS Oracle Home>/OPatch/opatch lsinventory | grep -i 'EM-AGENT Bundle Patch'

Other helpful Doc id's:

Doc ID 822485.1

Enterprise Manager 13.2 Master Bundle Patch List (Includes Plugins: 13.2.1, 13.2.2, 13.2.3) (Doc ID 2219797.1)

Hope this helps.

Creating calender rotation in plsql for x number of people...

Below is an example to create a rotation for 4 people in 3 step process. Make changes as needed.

1) 

create table test (START_DATE DATE,WEEK_DAY varchar2(20),W_D_N varchar2(10) default null,WEEK_NUMBER number,NAME varchar2(20) default null);

create table test1 ( ID number, NAME varchar2(10) default null);

insert into test1 values (1,'jack');

insert into test1 values (2,'mike');

insert into test1 values (3,'jim');

insert into test1 values (4,'bob');

commit;

2) ---load calender now with appropriate dates and validate randomly:

insert into test (START_DATE,WEEK_DAY,W_D_N,WEEK_NUMBER,NAME)

       SELECT (to_date('2018-08-16','YYYY-MM-DD') +( level -1)) start_date,to_char( (to_date('2018-08-16','YYYY-MM-DD') +level -1) +1, 'DAY')Week_day,

     ( CASE 

    WHEN  to_char( (to_date('2018-08-16','YYYY-MM-DD') +level -1) +1, 'DAY')  like 'MON%'   THEN   7      

     WHEN to_char( (to_date('2018-08-16','YYYY-MM-DD') +level -1) +1, 'DAY')  like 'TUES%'  THEN   1

     WHEN to_char( (to_date('2018-08-16','YYYY-MM-DD') +level -1) +1, 'DAY')  like 'WED%'   THEN   2

     WHEN  to_char( (to_date('2018-08-16','YYYY-MM-DD') +level -1) +1, 'DAY') like 'THUR%'  THEN   3

     WHEN to_char( (to_date('2018-08-16','YYYY-MM-DD') +level -1) +1, 'DAY')  like 'FRID%'  THEN   4 

     WHEN  to_char( (to_date('2018-08-16','YYYY-MM-DD') +level -1) +1, 'DAY') like 'SAT%'   THEN   5

     WHEN to_char( (to_date('2018-08-16','YYYY-MM-DD') +level -1) +1, 'DAY')  like 'SUN%'   THEN   6

     ELSE

          null

  END ) W_D_N,

        to_char( (to_date('2018-08-16','YYYY-MM-DD') +level -1), 'WW') week_number,null

  FROM DUAL CONNECT BY (to_date('2018-08-16','YYYY-MM-DD') +( level -1)) <= to_date('2018-12-31','YYYY-MM-DD');

--------------------------------------------------

3) ---- have a rotation set from the above... change the value "x < 4" based on the rotation..

set serveroutput on

declare 

TYPE at_most_twelve_t IS VARRAY (100) OF VARCHAR2 (100);

l_months   at_most_twelve_t;

type rotat_name is VARRAY (100) OF VARCHAR2 (100);

rotat_cur rotat_name;

--temp varchar2(10);

x NUMBER := 0;

y NUMBER := 0;

z NUMBER := 0;

p NUMBER := 0;

Begin

SELECT distinct week_number  BULK COLLECT INTO l_months FROM test where name is null order by week_number asc;

  select name BULK COLLECT into rotat_cur from test1 order by id asc; 

  for i in 1 .. l_months.count

  LOOP

      -- DBMS_OUTPUT.PUT_LINE('Outer Loop counter is ' || l_months(i));

       ---- set the x value for if loop based on number of people in rotation.

          if  x < 4 then

          x := x + 1;

          else 

           x := 1;

          end if;

             

         -- DBMS_OUTPUT.PUT_LINE('Outer Loop counter is ' || x);

          for k in 1 .. rotat_cur.count 

             loop

                  if    k = x then  

             for j in (select w_d_n from test where week_number=l_months(i))

                 loop 

                 update test set Name=rotat_cur(k) where week_number=l_months(i);

                 commit;

                 end loop;

                  else

                    continue;

                    end if;

            end loop;

  END LOOP;

END;

commit;

===========================================

Hope this helps....

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

check crs/has are enabled or not

how to check in multi environments.... whether crs option is enabled or not.
you can create a metric extension in OEM to check for all hosts....

#!/bin/sh

OSVER=`uname`
case $OSVER in
  "AIX")
asm_sid=`cat /etc/oratab| egrep -v '(^#)'|cut -f1 -d: -s |grep +`
asm_home=`cat /etc/oratab|grep ${asm_sid} |cut -f2 -d: -s`
export ORACLE_SID=${asm_sid}
export ORACLE_HOME=${asm_home}
echo $ORACLE_HOME
echo $ORACLE_SID
export ORACLE_BASE=/temp/app/oracle
export PATH=$ORACLE_HOME/bin:$PATH

test_val=`crsctl config has|grep -i enabled`  ----- replace if you have sudo only access
if [ -n "$test_val" ]; then
   x=1;
   z=crs-parm-enabled;
   else
   x=0;
   z=crs-parm-disabled;
fi

   break ;;
  "Linux")

asm_sid=`cat /etc/oratab| egrep -v '(^#)'|cut -f1 -d: -s |grep +`
asm_home=`cat /etc/oratab|grep ${asm_sid} |cut -f2 -d: -s`
export ORACLE_SID=${asm_sid}
export ORACLE_HOME=${asm_home}
echo $ORACLE_HOME
echo $ORACLE_SID
export ORACLE_BASE=/temp/app
export PATH=$ORACLE_HOME/bin:$PATH

nr_status=`ps -ef| grep lck |wc -l`
if [[ $nr_status -eq 1 ]]
 then
   test_val=`crsctl config has|grep -i enabled`
    if [ -n "$test_val" ]; then
      x=1;
      z=crs-parm-enabled;
    else
      x=0;
      z=crs-parm-disabled;
   fi
 else
    test_val=`sudo $ORACLE_HOME/bin/crsctl config crs|grep -i enabled`     ----------- replace if you have sudo only access
    if [ -n "$test_val" ]; then
      x=1;
      z=crs-parm-enabled;
    else
      x=0;
      z=crs-parm-disabled;
    fi
fi
break ;;
esac

echo $x;
echo $z;
echo "em_result=$z|$x";


home this helps...