ASM Useful commands

-- LUN size

SELECT
      NVL(a.name, '[CANDIDATE]')                       disk_group_name
    , b.path                                           disk_file_path
    , b.total_mb                                       total_mb
    , (b.total_mb - b.free_mb)                         used_mb
    , ROUND((1- (b.free_mb / b.total_mb))*100, 2)      pct_used
  FROM
      v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number)
  ORDER BY
     a.name;

-- will show provisioned dasd too


col DISK_FILE_PATH format a20    
SELECT
      NVL(a.name, '[CANDIDATE]')                       disk_group_name
    , b.path                                           disk_file_path
    , b.total_mb                                       total_mb
    , (b.total_mb - b.free_mb)                         used_mb
  FROM
      v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number)
  ORDER BY
     a.name;

-- check provisioned dasd using asmcmd

asmcmd >  lsdsk --candidate -p

   
-- ToTal Utlizatization size

 SELECT
      name                                     group_name
    , type                                     type
    , total_mb                                 total_mb
    , (total_mb - free_mb)                     used_mb
    , ROUND((1- (free_mb / total_mb))*100, 2)  pct_used
  FROM
      v$asm_diskgroup
  ORDER BY
     name;

-- find flash area usage

select database_name "Database",
         sum(space)/1024/1024 "Size in MB" FROM (
  SELECT
      CONNECT_BY_ROOT db_name as database_name, space
  FROM
      ( SELECT
            a.parent_index       pindex
          , a.name               db_name
          , a.reference_index    rindex
          , f.bytes              bytes
          , f.space              space
          , f.type               type
        FROM
            v$asm_file f RIGHT OUTER JOIN v$asm_alias a
                         USING (group_number, file_number) where GROUP_NUMBER=2
      )
  WHERE type IS NOT NULL
  START WITH (MOD(pindex, POWER(2, 24))) = 0
      CONNECT BY PRIOR rindex = pindex)
group by rollup(database_name)
order by database_name
/

DBMS_SCHEDULER create schedule create job

- create schedule

 -- Creating a common schedule which could be used to schedule a JOB to run Daily at 1:00 AM
     -- US/Eastern Time.
DBMS_SCHEDULER.create_schedule(
                                    schedule_name   => 'DLY0100_COMMON_SCHED'                     ,
                                    start_date      =>  trunc(SYSDATE+1) at TIME ZONE 'US/Eastern',
                                    repeat_interval => 'freq=daily; byhour=1;'                    ,
                                    comments        => 'Runtime - Everyday (Mon-Sun) at 01:00 AM US/Eastern Time'
                                   );


 --
     -- Create a job with an inline program and a defined schedule to
     -- do daily purge for g  tables.
     --
     DBMS_SCHEDULER.create_job(
                               job_name      => 'DLY0100_DAILY_PURGE_GRP1'         ,
                               schedule_name => 'DLY0100_COMMON_SCHED'             ,
                               job_type      => 'PLSQL_BLOCK'                      ,
                               job_action    => 'BEGIN daily_purge(null,1); END;'  ,
                               enabled       => TRUE                               ,
                               auto_drop     => FALSE                              ,
                               comments      => 'Daily job to purge group 1 tables.'
                              );

 -- Creating a common schedule which could be used to schedule a JOB to run Weekly at 3:00 AM
     -- US/Central Time.
     --
     DBMS_SCHEDULER.create_schedule(
                                    schedule_name   => 'WKLY0300_COMMON_SCHED'                     ,
                                    start_date      =>  trunc(SYSDATE+1) at TIME ZONE 'US/Eastern',
                                    repeat_interval => 'freq=weekly; byday=sun; byhour=2;'                    ,
                                    comments        => 'Runtime - Every Sun at 3:00AM '
                                   );

-- Create a job with  proecdure
   DBMS_SCHEDULER.create_job(
                               job_name      => 'WKLY0300_SP_COLLECT_MIS_STATS'       ,
                               schedule_name => 'WKLY0300_COMMON_SCHED'              ,
                               job_type      => 'STORED_PROCEDURE'                       ,
                               job_action    => 'sp_collect_mis_stats',
                               enabled       => TRUE                                ,
                               auto_drop     => FALSE                               ,
                               comments      => 'Weekly job to collect mis stats'
                              );

Scheduler disable/enable

exec dbms_scheduler.drop_job('SCOTT.TESTJOB');
exec dbms_scheduler.disable('TESTJOB');
exec dbms_scheduler.enable('"TESTJOB"');
exec dbms_scheduler.stop_job('TESTJOB');

Scheduler Jobs report

SPOOL verify_scheduler_changes.log
--
SELECT to_char(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') "START_TIME"
  FROM DUAL;
--
PROMPT '*****************************************************************';
--
DEFINE TEST='CHECK Scheduler Jobs Creation and various status';
--
PROMPT 'Verify:    &TEST'
PROMPT 'User:      &_USER'
PROMPT 'Database:  &_CONNECT_IDENTIFIER';
PROMPT '**** CHECK Scheduler Jobs Creation and various status Report ****';
PROMPT '*****************************************************************';
--
SET linesize 157;
--
PROMPT ' '
PROMPT  '**** Query Number 1 ****'
PROMPT ' 1) The below query is used to check following things:'
PROMPT ' '
--
PROMPT '   a) Whether the scheduler jobs have been created successfully.'
PROMPT '   b) All the jobs should be in enabled state i.e. ENABLED=TRUE. If it is not then the job would probably not run on the schedule date/time'
PROMPT '   c) Check the state column, it should be 'SCHEDULED''
PROMPT '   d) Check the last_start_date, the next_run_date to verify whether the job ran at the scheduled time.'
PROMPT '   e) Check the failure_count, ideally it should be zero. If not then use query # 2 or 3 to check why the job failed.'
PROMPT '   f) Check the run_count column to see how many times the job ran.'

--
  SELECT  job_name
         ,enabled
         ,state
         ,run_count
         ,failure_count
         ,rpad(last_start_date,39) AS "LAST_START_DATE"
         ,rpad(next_run_date,39)   AS "NEXT_RUN_DATE"
    FROM  user_scheduler_jobs
ORDER BY  job_name;
--
PROMPT ' '
PROMPT ' '
PROMPT  '**** Query Number 2 ****'
PROMPT ' 1) The below query is used to check following things:'
PROMPT ' '
--
PROMPT '   a) Whether each job ran at the requested scheduled time.'
PROMPT '   b) Check the actual start date.'
PROMPT ' '
--
COLUMN job_name FORMAT A30
COLUMN status FORMAT A12
--
  SELECT  to_char(log_date, 'DD-MON-YY HH24:MI:SS') AS "LOG_DATE"
         ,job_name
         ,status
         ,to_char(req_start_date, 'DD-MON-YY HH24:MI:SS') AS "REQ_START_DATE"
         ,to_char(actual_start_date, 'DD-MON-YY HH24:MI:SS') AS "ACTUAL_START_DATE"
         ,rpad(run_duration,25) AS "JOB_RUN_DURATION"
    FROM  user_scheduler_job_run_details
ORDER BY  log_date DESC;
--
--
PROMPT ' '
PROMPT ' '
PROMPT  '**** Query Number 3 ****'
PROMPT ' 1) The below query is used to check following things:'
PROMPT ' '
--
PROMPT '   a) How many jobs are in failed or broken status.'
PROMPT '   b) Check the additional_info column to check why the job failed'
PROMPT ' '
--
COLUMN job_name FORMAT A30
COLUMN status FORMAT A12
COLUMN additional_info word_wrapped
--
  SELECT  to_char(log_date, 'DD-MON-YY HH24:MI:SS') AS "LOG_DATE"
         ,job_name
         ,status
         ,additional_info
    FROM  user_scheduler_job_run_details
   WHERE  status != 'SUCCEEDED'
ORDER BY  log_date DESC;
--
--
--
PROMPT ' '
PROMPT ' '
PROMPT  '**** Query Number 4 ****'
PROMPT ' 1) The below query is used to check following things:'
PROMPT ' '
--
PROMPT '   a) Whether all the schedules have been created.'
PROMPT '   b) Start date of each schedule should match with the create scripts.'
PROMPT ' '
--
COLUMN schedule_name FORMAT A30
COLUMN repeat_interval FORMAT A50
COLUMN comments FORMAT A55 word_wrapped

  SELECT  schedule_name
         ,to_char(start_date, 'DD-MON-YY HH24:MI:SS') AS "START_DATE"
         ,repeat_interval
         ,comments
    FROM  user_scheduler_schedules
ORDER BY  schedule_name;
--
--
--
PROMPT ' '
PROMPT ' '
PROMPT  '**** Query Number 5 ****'
PROMPT ' 1) The below query is used to check following things:'
PROMPT ' '
--
PROMPT '   a) Whether all the programs have been created.'
PROMPT '   b) Program action for each program, this tells us what package/procedure is been referenced.'
PROMPT '   c) All the programs should be in Enables state'
PROMPT ' '
--
COLUMN program_name FORMAT A30
COLUMN program_action FORMAT A60 word_wrapped
COLUMN comments FORMAT A55 word_wrapped
--
  SELECT  program_name
         ,program_action
         ,enabled
         ,comments
    FROM  user_scheduler_programs
ORDER BY  program_name;
--
--
--
PROMPT ' '
PROMPT ' '
PROMPT  '**** Query Number 6 ****'
PROMPT ' 1) The below query is used to check following things:'
PROMPT ' '
--
PROMPT '   a) This query will show all the programs which are not in enabled state. Ideally you should see no rows.'
PROMPT '   b) All the programs should be in Enables state'
PROMPT ' '
--
  SELECT  program_name
         ,program_action
         ,enabled
         ,comments
    FROM  user_scheduler_programs
   WHERE  enabled != 'TRUE'
ORDER BY  program_name;
--
--
--
PROMPT ' '
PROMPT ' '
PROMPT  '**** Query Number 7 ****'
PROMPT ' 1) The below query is used to check following things:'
PROMPT ' '
--
PROMPT '   a) How many dbms_jobs are still running and their function as defined in the "what column"'
PROMPT '   b) Ideally there should not be any dbms_jobs in the critical DB and there should be one dbms_job on the log DB to delete the event log'
PROMPT '      tables. This would be soon removed once the partitioning changes are checked in.'
PROMPT '   c) If you find dbms_jobs in the critical then all of them should be removed by running remove_all_user_jobs.sql (checkd in and part of 9A kit)'
PROMPT '      This script is not part of the rebuild script as it had to be run only once.'
PROMPT '   d) Once the partition maintenance code is part of the kit, then the dbms_job on the log side has to be removed.'
PROMPT ' '
--
COLUMN job FORMAT 9999
COLUMN what word_wrapped
--
SELECT  job
       ,what
  FROM  user_jobs;
--
PROMPT ' '
--
SELECT to_char(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') "END_TIME"
  FROM DUAL;
--
PROMPT '*****  END Of  Report *****'
--
SPOOL OFF;
QUIT

Blocking Issue

// *******************************************************************************--  blocking issue : enq: TX - row lock contention
// *******************************************************************************
step 1
  // get blocking session and blocked sessions

  set lines 180
  col USERNAME format a10
  col event    format a15
  col WAIT_CLASS format a15
  Select  blocking_session, sid, serial#, username, event, wait_class, seconds_in_wait siw
    from v$session  where blocking_session is not NULL  order by blocking_session;    

step 2
 //  detail locking information :  sid, serial and object name    
 
   set linesize 150;
    set head on;
    col sid_serial form a13
    col ora_user for a15;
    col object_name for a35;
    col object_type for a10;
    col lock_mode for a15;
    col last_ddl for a8;
    col status for a10;
   
    break on sid_serial;
   
   SELECT l.session_id||','||v.serial# sid_serial,
          l.ORACLE_USERNAME ora_user,
          o.object_name,
          o.object_type,
          DECODE(l.locked_mode, 
                  0, 'None', 
                  1, 'Null', 
                  2, 'Row-S (SS)', 
                  3, 'Row-X (SX)',
                  4, 'Share',
                  5, 'S/Row-X (SSX)',
                  6, 'Exclusive',
                 TO_CHAR(l.locked_mode)
              ) lock_mode,
             o.status,
             to_char(o.last_ddl_time,'dd.mm.yy') last_ddl
   FROM dba_objects o, gv$locked_object l, v$session v
   WHERE o.object_id = l.object_id
     and l.SESSION_ID=v.sid
   order by 2,3;



step 3
//  which sid and stmt is waiting for lock

with blocking_tab as ( 
  Select  distinct blocking_session  sid   
        from v$session  where blocking_session is not NULL
),
blocking_sess as (
  select s.sid, s.serial#, sql_id, prev_sql_id from v$session s, blocking_tab b where s.sid=b.sid
  ),
blocked_sess as (    
  select s.sid, s.serial#, sql_id, prev_sql_id from v$session s, blocking_tab b where s.blocking_session=b.sid    ),
wait_sess  as  (
  select sid, serial#, sql_id, prev_sql_id from blocking_sess  union  select sid, serial#, sql_id,   prev_SQL_id from blocked_sess  )
select s.sid, s.serial#, sql_text from v$sql q, wait_sess s where 
q.sql_id = s.sql_id or
    q.sql_id = s.prev_sql_id
/ 
 
step 4
// kill blocking session

with temptbl1 as (
  Select  distinct blocking_session  sid   
        from v$session  where blocking_session is not NULL
)
select 'alter system kill session '||chr(39)||sid||','||serial#||chr(39)||';' from v$session natural  join  temptbl1 ;


// *******************************************************************************