Oracle ASM Commands

col PATH form a25
col GB_read for 99999
col GB_written for 9999

select a.PATH, a.mount_status, a.state, a.TOTAL_MB, a.FREE_MB, a.mount_date, a.bytes_read/1024/1024/1024 GB_read, 
a.BYTES_WRITTEN/1024/1024/1024 GB_written, dg.name AS DISK_GROUP
from v$asm_disk_stat a, V$ASM_DISKGROUP dg
WHERE dg.group_number = a.group_number
order by disk_group
/

PATH                      MOUNT_S STATE      TOTAL_MB    FREE_MB MOUNT_DAT GB_READ GB_WRITTEN DISK_GROUP
------------------------- ------- -------- ---------- ---------- --------- ------- ---------- ----------
ORCL:ASM252_VMAX0050F     CACHED  NORMAL       246246      23607 25-AUG-14       6         13 MY_DATA
ORCL:ASM252_VMAX00510     CACHED  NORMAL       246246      23616 25-AUG-14       6         13 MY_DATA
ORCL:ASM252_VMAX01C82     CACHED  NORMAL       246246      60811 25-AUG-14      39         11 MY_RECOVERY
ORCL:ASM252_VMAX01C83     CACHED  NORMAL       246246      60808 25-AUG-14      40         11 MY_RECOVERY

set pages 50000 lines 100 echo on head on flush on veri on feed on
col INSTANCE_NAME form a20
col SOFTWARE_VERSION form a20
col COMPATIBLE_VERSION form a20

select * from v$asm_client;

GROUP_NUMBER INSTANCE_NAME        DB_NAME  STATUS       SOFTWARE_VERSION     COMPATIBLE_VERSION
------------ -------------------- -------- ------------ -------------------- --------------------
           1 +ASM1                mylxtdb  CONNECTED    11.1.0.7.0           11.0.0.0.0
           2 +ASM1                mylxtdb  CONNECTED    11.1.0.7.0           11.0.0.0.0

select DISK_NUMBER,MOUNT_STATUS,HEADER_STATUS,MODE_STATUS,STATE,PATH,TOTAL_MB from gv$asm_disk;

DISK_NUMBER MOUNT_S HEADER_STATU MODE_ST STATE    PATH                             TOTAL_MB
----------- ------- ------------ ------- -------- ------------------------------ ----------
          2 CACHED  MEMBER       ONLINE  NORMAL   ORCL:ASM252_VMAX0050F              246246
          3 CACHED  MEMBER       ONLINE  NORMAL   ORCL:ASM252_VMAX00510              246246
          0 CACHED  MEMBER       ONLINE  NORMAL   ORCL:ASM252_VMAX01C82              246246

SELECT SUBSTR(d.name,1,16) AS asmdisk,PATH, dg.name AS diskgroup 
FROM V$ASM_DISKGROUP dg, V$ASM_DISK d 
WHERE dg.group_number = d.group_number 
and dg.name in ('MY_DATA','MY_RECOVERY') order by dg.name ;

ASMDISK          PATH                                     DISKGROUP
---------------- ---------------------------------------- ---------------------
ASM252_VMAX01C83 ORCL:ASM252_VMAX01C83                    MY_DATA
ASM252_VMAX01F67 ORCL:ASM252_VMAX01F67                    MY_DATA
ASM252_VMAX01C85 ORCL:ASM252_VMAX01C85                    MY_RECOVERY
ASM252_VMAX01C84 ORCL:ASM252_VMAX01C84                    MY_RECOVERY

Identify the Disks you want to add and Disks you want to remove by using "oracleasm listdisks" as oracle user on the server.
The below command would add the disks you want to add and remove the older (existing disks) and do the re-balance. You can set
the rebalance power from 1 to 11. 11 would use more resources and would be the fastest way to rebalance.
NOTE: When adding Disk(s), you have to prefix the device name with "ORCL:"

alter diskgroup MY_DATA
 add disk
  'ORCL:ASM176_VMAX02049',
  'ORCL:ASM176_VMAX0204A'
 drop disk
  'ASM252_VMAX01F67',
  'ASM252_VMAX01C82',
rebalance power 11 ;

Check the rebalance progress using the below script.

select sysdate, GROUP_NUMBER, OPERATION, STATE, POWER, ACTUAL, SOFAR, EST_WORK, EST_RATE, EST_MINUTES
from v$asm_operation;

How to find and kill the DBMS jobs running

select job_name, session_id, running_instance, elapsed_time, cpu_used
from dba_scheduler_running_jobs;

JOB_NAME SESSION_ID RUNNING_INSTANCE
------------------- ---------- ----------------
JOB_DEL_PROJECTS 67 4

Now you can stop the job using

exec DBMS_SCHEDULER.stop_JOB (job_name => 'JOB_DEL_PROJECTS');

or you can kill the SID

11g RAC: Killing user sessions from different instance

You can be logged onto instance 1 and can kill user sessions for all other instances in the RAC cluster.

ALTER SYSTEM KILL SESSION 'SID, SERIAL#,[@INST_ID]'

The below statement will generate the script which you can run on any instance to kill session from all the instances.

select 'alter system kill session '''||sid||','||serial#||',@'||inst_id||''';' from gv$session where osuser='SCOTT';

Active Session History Queries

Oracle DBA scripts: Active Session History Queries
-- TOP events
select event,
sum(wait_time +time_waited) ttl_wait_time
from v$active_session_history
where sample_time between sysdate - 60/2880 and sysdate
group by event
order by 2

-- Top sessions
select sesion.sid,
sesion.username,
sum(ash.wait_time + ash.time_waited)/1000000/60 ttl_wait_time_in_minutes
from v$active_session_history ash, v$session sesion
where sample_time between sysdate - 60/2880 and sysdate
and ash.session_id = sesion.sid
group by sesion.sid, sesion.username
order by 3 desc

--Top queries
SELECT active_session_history.user_id,
dba_users.username,
sqlarea.sql_text,
SUM(active_session_history.wait_time +
active_session_history.time_waited)/1000000 ttl_wait_time_in_seconds
FROM v$active_session_history active_session_history,
v$sqlarea sqlarea,
dba_users
WHERE active_session_history.sample_time BETWEEN SYSDATE - 1 AND SYSDATE
AND active_session_history.sql_id = sqlarea.sql_id
AND active_session_history.user_id = dba_users.user_id
and dba_users.username <>'SYS'
GROUP BY active_session_history.user_id,sqlarea.sql_text, dba_users.username
ORDER BY 4 DESC

-- Top segments
SELECT dba_objects.object_name,
dba_objects.object_type,
active_session_history.event,
SUM(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
FROM v$active_session_history active_session_history,
dba_objects
WHERE active_session_history.sample_time BETWEEN SYSDATE - 1 AND SYSDATE
AND active_session_history.current_obj# = dba_objects.object_id
GROUP BY dba_objects.object_name, dba_objects.object_type, active_session_history.event
ORDER BY 4 DESC

-- Most IO
SELECT sql_id, COUNT(*)
FROM gv$active_session_history ash, gv$event_name evt
WHERE ash.sample_time > SYSDATE - 1/24
AND ash.session_state = 'WAITING'
AND ash.event_id = evt.event_id
AND evt.wait_class = 'User I/O'
GROUP BY sql_id
ORDER BY COUNT(*) DESC;

SELECT * FROM TABLE(dbms_xplan.display_cursor('&SQL_ID));

-- Top 10 CPU consumers in last 60 minutes
select * from
(
select session_id, session_serial#, count(*)
from v$active_session_history
where session_state= 'ON CPU' and
sample_time > sysdate - interval '60' minute
group by session_id, session_serial#
order by count(*) desc
)
where rownum <= 10; -- Top 10 waiting sessions in last 60 minutes select * from ( select session_id, session_serial#,count(*) from v$active_session_history where session_state='WAITING' and sample_time > sysdate - interval '60' minute
group by session_id, session_serial#
order by count(*) desc
)
where rownum <= 10; -- Find session detail of top sid by passing sid select serial#, username, osuser, machine, program, resource_consumer_group, client_info from v$session where sid=&sid; -- Find different sql_ids of queries executed in above top session by-passing sid select distinct sql_id, session_serial# from v$active_session_history where sample_time > sysdate - interval '60' minute
and session_id=&sid

--Find full sqltext (CLOB) of above sql
select sql_fulltext from v$sql where sql_id='&sql_id'

--find session wait history of above found top sessionselect * from v$session_wait_history where sid=&sid

--find all wait events for above top session
select event, total_waits, time_waited/100/60 time_waited_minutes,
average_wait*10 aw_ms, max_wait/100 max_wait_seconds
from v$session_event
where sid=&sid
order by 5 desc

--session statistics for above particular top session :
select s.sid,s.username,st.name,se.value
from v$session s, v$sesstat se, v$statname st
where s.sid=se.SID and se.STATISTIC#=st.STATISTIC#
--and st.name ='CPU used by this session'
--and s.username='&USERNAME'
and s.sid='&SID'
order by s.sid,se.value desc

Auto Task Status

EXEC DBMS_AUTO_TASK_ADMIN.disable;

and if you query, you might see something like this.

col client_name for a50
col status for a10

select client_name,status FROM dba_autotask_client ;
CLIENT_NAME STATUS
-------------------------------------------------- ----------
auto optimizer stats collection ENABLED
auto space advisor ENABLED
sql tuning advisor ENABLED


the above may not give you the clear picture.

You the below query to get the autotask_status of these jobs

col window_name for a20
col window_next_time for a25
select window_name, to_char(cast(window_next_time as date),'DD/MM/YYYY HH24:MI:SS') window_next_time,
window_active, autotask_status, optimizer_stats, segment_advisor, sql_tune_advisor,
health_monitor from DBA_AUTOTASK_WINDOW_CLIENTS ;

You can also individually disable them.

begin
dbms_auto_task_admin.disable( client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
end;
/