ON CALL DBA SUPPORT

— Database blog

QUERY TO CHECK MOUNT POINT UTILIZATION BY A DATABASE

Posted by ssgottik on 06/03/2015

SQL > select substr(file_name,1,20)||’ # ‘|| ceil(sum((bytes)/(1024*1024*1024))) from dba_data_files group by substr(file_name,1,20);

 

Thanks,

Satish.G.S

Advertisements

Posted in SCRIPTS | Leave a Comment »

COMPILE ENTIRE SCHEMA OBJECTS

Posted by ssgottik on 13/02/2015

EXEC UTL_RECOMP.recomp_serial(‘SCOTT’);

Thanks,

Satish.G.S

Posted in SCRIPTS | Leave a Comment »

QUERY TO FIND RMAN BACKUP STATUS IN A DATABASE

Posted by ssgottik on 31/10/2014

set lines 200;
set pages 200;
SELECT INPUT_TYPE “Backup Type”,incremental_level,d.session_recid,
TO_CHAR(d.START_TIME,’mm/dd/yy hh24:mi’) “start_time”,
TO_CHAR(END_TIME,’mm/dd/yy hh24:mi’) “End_time”,
d.elapsed_seconds/3600 “Elapsed Time”,
d.status
FROM V$RMAN_BACKUP_JOB_DETAILS d,V$BACKUP_SET_DETAILS s where d.session_recid=s.session_recid and d.START_TIME>sysdate-2 order by End_time desc;

Posted in SCRIPTS | Leave a Comment »

QUERY TO FIND FRA UTILIZATION

Posted by ssgottik on 31/10/2014

set lines 200;
col “Flashback Area” for a40;
SELECT NAME “Flashback Area”, ROUND(SPACE_LIMIT / 1073741824) “Allocated space in GB”, ROUND(SPACE_USED / 1073741824) “Used space in GB”,ROUND(((SPACE_USED /1073741824) * 100) / (SPACE_LIMIT / 1073741824), 2) “USED PERCENTAGE” FROM V$RECOVERY_FILE_DEST where ROUND(((SPACE_USED / 1073741824) * 100) / (SPACE_LIMIT / 1073741824), 2)>0;

Posted in SCRIPTS | Leave a Comment »

ALERT LOG OF A PARTICULAR DATE INTO A FILE

Posted by ssgottik on 16/06/2014

vi alert_orcl.log

:set nu

:wq!

by doing above it will set the number for all the lines in the alert log. Save and come out of the log and give the below sed command.

$sed ‘12345, 56677p’ alert_orcl.og > alert_orcl_new.log

 

Posted in SCRIPTS | Leave a Comment »

Database house keeping scripts

Posted by ssgottik on 28/05/2014

find . -name “*.trc” -mtime +0|xargs rm -f “*.trc”

find . -name “*.trm” -mtime +0|xargs rm -f “*.trm”

find . -name “*.aud” -mtime +0|xargs rm -f “*.aud”

find . -name “*.xml” -mtime +0|xargs rm -f “*.xml”

find . -name “*.dat” -mtime +1|xargs rm -f “*.dat”

Posted in SCRIPTS | Leave a Comment »

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 »