ON CALL DBA SUPPORT

— Database blog

Archive for April, 2014

DROPPING A DISK IN A ASM DISKGROUP (11g R2)

Posted by ssgottik on 28/04/2014

From 11g R2 on wards no need to unmount the diskgroup to add and drop the disks:

SQL > alter diskgroup data drop disk  ‘/ora00/oracle/disk100’ ;

SQL >rebalance power 6;

Thanks,

Satish.G.S

Posted in ASM | Leave a Comment »

ADDING DISK TO ASM(11G R2)

Posted by ssgottik on 28/04/2014

From 11g R2 on wards no need to unmount the diskgroup to add and drop the disks:

SQL > alter diskgroup data add disk  ‘/ora00/oracle/disk100’ , ‘ora00/oracle/disk101’

SQL >rebalance power 6;

Thanks,

Satish.G.S

Posted in ASM | Leave a Comment »

QUERY TO FIND THE % COMPLETION WHILE EXECUTING

Posted by ssgottik on 16/04/2014

SELECT s.sid,  s.serial#,  s.machine,s.sql_id,
TRUNC(sl.elapsed_seconds/60) || ‘:’ || MOD(sl.elapsed_seconds,60) elapsed,
TRUNC(sl.time_remaining/60) || ‘:’ || MOD(sl.time_remaining,60) remaining,
ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct
FROM   v$session s, v$session_longops sl
WHERE  s.sid     = sl.sid
AND    s.serial# = sl.serial#;

Posted in RMAN, SCRIPTS | Leave a Comment »

RMAN BACKUP MEDIA ID /TAPE ID

Posted by ssgottik on 16/04/2014

select   to_char(bu.completion_time, ‘YYYY-MM-DD HH24:MI’) as completion_time,bu.media as media_id      
from v$backup_piece bu
where   to_char( bu.completion_time , ‘MM-DD-YYYY’)= ‘mm-dd-yyyy’   
order by 1 desc

Posted in RMAN, SCRIPTS | Leave a Comment »

QUERY TO FIND THE TABLES WITHOUT PRIMARY KEY IN A SCHEMA

Posted by ssgottik on 08/04/2014

select table_name from dba_tables where owner = ‘< SHCEMA_NAME >’
minus
select table_name from dba_constraints where CONSTRAINT_TYPE=’P’

Posted in SCRIPTS | Leave a Comment »