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

tablespace sizing script for oracle 12c..

Consider only select statement inside the PLSQL...


create table PROD_DB_SIZE_DETAILS (INSTANCENAME                  varchar2(40),

                                   TABLESPACE_NAME               varchar2(40) ,

                                   MAX_SIZE_GB                   number(10,3) ,

                                   ALLOC_SPACE_GB                number(10,3) ,

                                   USED_SPACE_GB                 number(10,3) ,

                                   USED_PERCENT_ALLOC            number(10,3) ,

                                   USED_PERCENT_MAXSIZE          number(10,3) ,

                                   FREE_SPACE_TO_ALLOC_GB        number(10,3) ,

                                   FREE_SPACE_PERCENT_ALLOC      number(10,3) ,

                                   CON_ID                        number(10) ,

                                   RUN_DATE                      DATE

                                   );

                               

drop table PROD_DB_SIZE_DETAILS;

delete from PROD_DB_SIZE_DETAILS;

commit;

select * from PROD_DB_SIZE_DETAILS;





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

DECLARE

tab_count  number(10,2);

sql_stmt VARCHAR2(10000);

begin



sql_stmt := 'insert into PROD_DB_SIZE_DETAILS select INSTANCENAME,tablespace_name,MAX_SIZE_GB,ALLOC_SPACE_GB,USED_SPACE_GB,USED_PERCENT_ALLOC,USED_PERCENT_MAXSIZE,FREE_SPACE_TO_ALLOC_GB,FREE_SPACE_PERCENT_ALLOC,CON_ID,TRUNC(SYSDATE) from

(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,a.con_id,

  case when  a.con_id=1  then (select name from v$database@DB_STRING)

  when a.con_id=3 then (select name from v$containers@DB_STRING where con_id=a.con_id) else null end  as instancename

FROM

  (SELECT tablespace_name,con_id,    SUM(bytes) totsize,sum(maxbytes) max_size

  FROM cdb_data_files@DB_STRING

  GROUP BY tablespace_name,con_id

  ) a,

  (SELECT tablespace_name,    SUM(bytes) used,con_id

  FROM cdb_segments@DB_STRING

  GROUP BY tablespace_name,con_id

  ) b

where a.TABLESPACE_NAME=B.TABLESPACE_NAME

and a.con_id=b.con_id



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,a.con_id,

 case when  a.con_id=1  then (select name from v$database@DB_STRING)

 when a.con_id=3 then (select name from v$containers@DB_STRING where con_id=a.con_id) else null end  as instancename

from

cdb_temp_files@DB_STRING  a,cdb_tablespace_usage_metrics@DB_STRING b,cdb_tablespaces@DB_STRING c

where a.tablespace_name=b.tablespace_name

and b.tablespace_name=c.tablespace_name

and a.con_id=b.con_id

and a.tablespace_name  in (''TEMP'')

group by a.con_id,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,a.con_id,

 case when  a.con_id=1  then (select name from v$database@DB_STRING)

when a.con_id=3 then (select name from v$containers@DB_STRING where con_id=a.con_id) else null end  as instancename

from

cdb_data_files@DB_STRING  a,cdb_tablespace_usage_metrics@DB_STRING b,cdb_tablespaces@DB_STRING c

where a.tablespace_name=b.tablespace_name

and b.tablespace_name=c.tablespace_name

and a.con_id=b.con_id

and a.tablespace_name not like (''TEMP'')

and a.tablespace_name not in (select distinct tablespace_name from cdb_segments@DB_STRING)

group by a.con_id,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))';



execute immediate  sql_stmt;

commit;

end;



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



Hope this helps....

How to re-create UNDO tablespace in Rac databases.

First, check what undo files in the tablespaces..

1)

set linesize 200;

col FILE_NAME for a80;

col TABLESPACE_NAME for a20;

select FILE_NAME,TABLESPACE_NAME,status,online_status from dba_data_files where tablespace_name like 'UNDO%';

check size of the tablespace...

 SELECT tablespace_name,

        SUM ( ( (bytes / 1024) / 1024) / 1024) size_gb,

        COUNT (file_id) no_of_datafiles

 FROM dba_data_files

 WHERE tablespace_name LIKE 'UNDOTBS1'

 GROUP BY tablespace_name;

2)

see what UNDO tablespace is currently used... 

show parameter UNDO_TABLESPACE;

second, check what segments are still tried to the UNDO...

SELECT a.name,b.status , d.username , d.sid , d.serial#

FROM   v$rollname a,v$rollstat b, v$transaction c , v$session d

WHERE  a.usn = b.usn

AND    a.usn = c.xidusn

AND    c.ses_addr = d.saddr

AND    a.name IN ( 

  SELECT segment_name

  FROM dba_segments 

  WHERE tablespace_name = 'UNDOTBS1'

);

If no rows found.... then..

alter system set undo_tablespace=UNDOTBS2 scope=both SID='RAC_1'

drop tablespace UNDOTBS1 including contents and datafiles;

3) re-create undo tablespace again....

create undo tablespace UNDOTBS1 datafile '+DATA' size 10G AUTOEXTEND ON NEXT 100M MAXSIZE 10G;

     alter tablespace UNDOTBS1 add datafile '+DATIND' size 5G AUTOEXTEND ON NEXT 100M MAXSIZE 10G;

follow the same for the other undo too...

Hope this Helps...

Upate a column based on the results of multiple tables in oracle

below is the generic select..

select b.abc,c.abc,c.abc,p.abc ,w.abc from table_A a,((c.abc*abc)*p.abc) as "normal"
table_A b, table_A c,table_A p, table_A w
where b.tab_A=f.tab_A
and b.tab_A=p.tab_A
and b.tab_A=w.tab_A
and p.tab_A=xxx
and c.tab_A >0
and c.tab_A >0
and rownum <10;

to update a table column based on mutiple tables result... use Merge Statement:


merge into table_A s
using
(select ((c.abc*abc)*p.abc)  as normal,w.abc  from
table_A f, table_A b,table_A p,table_A w
where b.tab_A=f.tab_A
and b.tab_A=p.tab_A
and b.tab_A=w.tab_A
and p.tab_A=xxx
and c.tab_A >0
and c.tab_A >0
and ((c.abc*abc)*p.abc) <> w.abc and rownum <10) z
on (s.abc=z.abc)
when matched then update set s.abc=z.abc;

Patch Inventory is missing in Alert log while DB is bounced.

In some cases, when a particular database is started the patch inventory is not loaded in the alert log and  says  “No patches have been applied” in the alert log.

To make the inventory to be loaded in alert log. Run the below statement in the database (and expect output as “OK”  and re-start the database again).

Note:- Don’t run ./datapatch from now…

select dbms_sqlpatch.verify_queryable_inventory from dual;

Hope this Helps...

creating baselines from AWR for consistant performance and how to move them to another environments

1)
Here we are creating a BASELINE profiles from previous runs using AWR reports... if we think the execution plan in one database is working fine and want to have the same consistant execution plan for sql.

login as system user

1)

BEGIN
  DBMS_SQLTUNE.CREATE_SQLSET(
    sqlset_name => 'BASIC_PROC_NAME',
    description => 'SQL Tuning Set for loading plan into SQL Plan Baseline');
END;
/

2)

DECLARE
  cur sys_refcursor;
BEGIN
  OPEN cur FOR
    SELECT VALUE(P)
    FROM TABLE(
       dbms_sqltune.select_workload_repository(begin_snap=>4176, end_snap=>4179,basic_filter=>'sql_id = ''0cadrfdx9t0z1e''',attribute_list=>'ALL')
              ) p;
     DBMS_SQLTUNE.LOAD_SQLSET( sqlset_name=> 'BASIC_PROC_NAME', populate_cursor=>cur);
  CLOSE cur;
END;
/



 SELECT
  first_load_time          ,
  executions as execs              ,
  parsing_schema_name      ,
  elapsed_time  / 1000000 as elapsed_time_secs  ,
  cpu_time / 1000000 as cpu_time_secs           ,
  buffer_gets              ,
  disk_reads               ,
  direct_writes            ,
  rows_processed           ,
  fetches                  ,
  optimizer_cost           ,
  sql_plan                ,
  plan_hash_value          ,
  sql_id                   ,
  sql_text
   FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(sqlset_name => 'BASIC_PROC_NAME')
             );
           


3)

DECLARE
my_plans pls_integer;
BEGIN
  my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
    sqlset_name => 'BASIC_PROC_NAME',
    basic_filter=>'plan_hash_value = ''4972313474'''
    );
END;
/

Make sure you see the row….

SELECT * FROM dba_sql_plan_baselines ;

select sql_handle, sql_text, plan_name,PARSING_SCHEMA_NAME,enabled,accepted,fixed,to_char(last_executed,'dd-mon-yy HH24:MI') last_executed from dba_sql_plan_baselines;

4) To backup the sql baseline to other environment... then continue.

exec DBMS_SPM.CREATE_STGTAB_BASELINE('SQL_PROF_BASELINE','SCHEMA_NAME');

select table_name,owner from dba_tables where table_name='SQL_PROF_BASELINE';

SELECT * FROM dba_sql_plan_baselines;
no rows initially

5)

var x number;
begin
:x := DBMS_SPM.PACK_STGTAB_BASELINE('SQL_PROF_BASELINE', 'SCHEMA_NAME', sql_handle => 'SQL_300901f0272d8837', plan_name => 'SQL_PLAN_30281y0mkv21r64b2d935' );
end;
/


6)

export ....

7)

Purge the existing which is not good....

exec DBMS_SHARED_POOL.PURGE ('0000000410E16308, 3635099784', 'C');

8)
import.... as system user

unpack...

7)

 var x number;
begin
:x := DBMS_SPM.UNPACK_STGTAB_BASELINE('SQL_PROF_BASELINE', 'SCHEMA_NAME');
end;
 /

SELECT * FROM dba_sql_plan_baselines;

***************************** END **********************************



 DECLARE
  v_dropped_plans number;
BEGIN
  v_dropped_plans := DBMS_SPM.DROP_SQL_PLAN_BASELINE (
     sql_handle => 'SQL_300901f0272d8837'
);
  DBMS_OUTPUT.PUT_LINE('dropped ' || v_dropped_plans || ' plans');
END;
/


Hope this Helps....

Performance: database slowness/blocking when Oracle Auto Task scheduled window jobs are running.

Disable below Oracle scheduled Auto jobs when you see slowness in the Application in one of the environments. 

If you have your own Gather Statistics Job  running in your environment and you do not want these advisor jobs..

These jobs usually runs in the night in weekdays and has a bigger window in the weekend.

exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL);

exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto space advisor',operation => NULL,window_name => NULL);

exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'sql tuning advisor',operation => NULL,window_name => NULL);



Hope this Helps...