Day to Day dba tasks required scripts and commands

Find database SID with unix process
##########################
prompt "Please Enter The UNIX Process ID"
accept spid
set verify off
set pagesize 50000
set linesize 30000
set long 500000
set head off
SELECT ' sid, serial#, aud sid : '|| s.sid||' , '||s.serial#||' , '||
       s.audsid||chr(10)|| '     DB User / OS User : '||s.username||
       '   /   '||s.osuser||chr(10)|| '    Machine - Terminal : '||
       s.machine||'  -  '|| s.terminal||chr(10)||
       '        OS Process Ids : '|| s.process||' (Client)  '||
       p.spid||' - '||p.pid||' (Server)'|| chr(10)||
       '   Client Program Name : '||s.program "Session Info"
  FROM V$PROCESS P,V$SESSION s
 WHERE p.addr = s.paddr
AND p.spid ='&spid';
select
s.username su,
substr(sa.sql_text,1,540) txt
from v$process p,
v$session s,
v$sqlarea sa
where p.addr=s.paddr
and s.username is not null
and s.sql_address=sa.address(+)
and s.sql_hash_value=sa.hash_value(+)
and spid=&SPID;

##############################
lsof /vah/oracle/dbdata/app02
################################
free up space
procmap 168098
procfiles 168098
##############################
to check mountpoint unmounted or not
######################
lsvg -l fdocumcmmk1
mount | grep /linuxhyd1

To Clean .patch_storage
################
opatch util cleanup
To find directory size
################
du -sh *
Before running slibclean
##################
genld -l | grep /documtmmk1/oracle/product/10.2.0
genkld | grep /documtmmk1/oracle/product/10.2.0
/usr/sbin/slibclean
##################
Enable tracing 10046
#####################

tkprof eq2_ora_20876.trc econdq2_ora_20876.out sys=y waits=y explain=userid/password
@$ORACLE_HOME/rdbms/admin/dbmssupp.sql
create or replace trigger set_system_event_trace
after logon on database
declare
  v_user dba_users.username%TYPE := user;
 sql_stmt1 varchar2(256) :='alter session set events '||chr(39)||'10046 trace name context forever, level 12'||chr(39);
begin
if (v_user='SCHEMANAME') THEN
 execute immediate sql_stmt1;
 end if;
exception
 when others then
   null;
  end;
  /
set autotrace traceonly explain
select instance_name,to_char(startup_time,'dd/mm/yy hh24:mi:ss') from v$instance;
#######################
FTP script
#########
more ftp_schema_hyddbo.ksh
#! /bin/ksh
ftp -n shyd11 << !
user arun arun##
cd /mnt/exports/hyd2
bin
prompt
mget exp_schema_hyddbo.dmp.Z
!
##################
TOP CPU process
###########################
 ps -e -o pcpu -o pid -o user -o args | sort -k 1 | tail -21r
Use dd for pre creating the db files
###########################
dd if=/dev/zero of=ofihrtTT03.dbf bs=1024 count=5120000 creates 5000m file
find bigest files in the filesystem
##########################
find . -type f -xdev -print|  xargs ls -ladtr | cut -c34- | sort -n -r | head -25
############################
Undo sizing
############################
Optimal Undo retention =  Actual undo size/(db block size * undo block per second)

Actual Undo Size
---------------
SELECT SUM(a.bytes) "UNDO_SIZE"
  FROM v$datafile a,
       v$tablespace b,
       dba_tablespaces c
 WHERE c.contents = 'UNDO'
   AND c.status = 'ONLINE'
   AND b.name = c.tablespace_name
   AND a.ts# = b.ts#;

DB BLOCK SIZE
----------------------------
You can get this from parameter file

Or

SELECT TO_NUMBER(value) "DB_BLOCK_SIZE [KByte]"
 FROM v$parameter
WHERE name = 'db_block_size';

UNDO BLOCKS PER SECOND
-----------------------------

SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
      "UNDO_BLOCK_PER_SEC"
  FROM v$undostat;
###############################################
Verify Standby database.
###############################################
SELECT THREAD#,SEQUENCE#, APPLIED  FROM V$ARCHIVED_LOG where APPLIED='NO'  order by sequence#;
select DEST_ID,DEST_NAME,status ,ERROR from v$archive_dest_status;
select sequence#, applied, to_char(first_time,'dd-mon-yyyy hh:mi:ss'), to_char(next_time,'dd-mon-yyyy hh:mi:ss') from v$archived_log  where APPLIED='NO' order by sequence#;
select process,status from v$managed_standby;
SELECT MESSAGE FROM V$DATAGUARD_STATUS ;
http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/manage_ps.htm
select 'DR IS STALE!!' from v$archived_log where applied = 'NO'
  and to_char(first_time,'yyyymmddhh24miss')< to_char(sysdate-1,'yyyymmddhh24miss');
SELECT  PROCESS, STATUS,SEQUENCE#,BLOCK#,BLOCKS, DELAY_MINS FROM    V$MANAGED_STANDBY;
SELECT THREAD#,SEQUENCE#, APPLIED  FROM V$ARCHIVED_LOG where APPLIED='YES'  order by sequence#;
delete archivelog until sequence 7495 thread 1;

Finds locked objects
####################################################
select
   c.owner,
   c.object_name,
   c.object_type,
   b.sid,
   b.serial#,
   b.status,
   b.osuser,
   b.machine
from
   v$locked_object a ,
   v$session b,
   dba_objects c
where
   b.sid = a.session_id
and
   a.object_id = c.object_id;
#############################
To mail a file from unix level
uuencode drop.log | mailx -s "DROP log @sHYD11 by ArunM" 20280280@h.com

export MAILTO="20280280@h.com"
export CONTENT="README.html"
export SUBJECT="README.html"
(
 echo "Subject: $SUBJECT"
 echo "MIME-Version: 1.0"
 echo "Content-Type: text/html"
 echo "Content-Disposition: inline"
 cat $CONTENT
) | /usr/sbin/sendmail $MAILTO

/* This shows waits */
#############################
select s.username, s.osuser, s.machine, s.serial#, w.*
from v$session_wait w, v$session s
where s.sid=w.sid
and w.event <> 'SQL*Net message from client'
and w.event<>'rdbms ipc message'
order by s.username ;

Raw filesystem used or not
++++++++++++++++++++
fuser -u <raw device path > / <device name>
vxprint filename

################
run sql from OS
##################
sqlplus /nolog  << EOFL
connect arun/arun
select  'I am in ** '|| a.name || ' ** Database and my SID is : ' || b.sid  " " from v\$database a, (select distinct sid from v\$mystat) b ;
@drop_mat_views.sql > $LOGDIR/test.out
@mat_views.sql >> $LOGDIR/test.out
EOFL
exit

Check Memory:
#####################
/usr/sbin/prtconf | grep Mem
Check PRocessor:
##################
psrinfo -v

Import speed
##############
select substr(sql_text,instr(sql_text,'INTO "'),30) table_name,
          rows_processed,
          round((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes,
          trunc(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_min
   from   sys.v_$sqlarea
   where  sql_text like 'INSERT %INTO "%'
     and  command_type = 2
     and  open_versions > 0;

session waits and blockers
##################################
select sid, sql_text
from v$session s, v$sql q
where sid in (select sid
from v$session where state in ('WAITING')
and wait_class != 'Idle' and event='enq: TX - row lock contention'
and (
q.sql_id = s.sql_id or
q.sql_id = s.prev_sql_id));
select blocking_session, sid, serial#, wait_class, seconds_in_wait from v$session
where blocking_session is not NULL order by blocking_session;
#######################################

cloning the oracle binaries:
##################
cpio -idcmv < aix_64bit_11107_PSU5_p9952228.cpio
change the oraInst.loc  in /var/opt/oracle for inventory_loc=/oracle/product/11.1.0/oraInventory
change location to the new home location and comment the existing one if it is 9i.
For solaris
###############
perl clone.pl ORACLE_HOME="/oracle/product/11.1.0" ORACLE_HOME_NAME="Ora11g" ORACLE_BASE="/home/oracle" -invPtrLoc /var/opt/oracle/oraInst.loc
 run this from ORACLE_HOME/clone/bin
For AIX
##############
perl clone.pl ORACLE_HOME="/oracle/product/10.2.0" ORACLE_HOME_NAME="Ora10g" ORACLE_BASE="/home/oracle" -invPtrLoc /etc/oraInst.loc
#####################################################
Re-Creating Invalid Synonyms
set echo off
set heading off
set linesize 200
set pagesize 0

spool val_syn.txt
select 'create or replace synonym ' || owner || '.' || synonym_name || ' for ' ||  table_owner || '.' || table_name || ' ; '
from dba_synonyms where  (owner,synonym_name) in
      (select owner, object_name from dba_objects where status = 'INVALID' and object_type = 'SYNONYM')
/
spool off ;
set echo on
set heading on
set feedback on
spool val_syn
@ val_syn.txt
spool off ;
######################################################
spool drop_ARUN.sql
set heading off;
select 'DROP TABLE '||OWNER||'.'||TABLE_NAME||' CASCADE CONSTRAINTS ;' from dba_tables where owner='ARUN';
select 'DROP '||OBJECT_TYPE||'  '||OWNER||'.'||OBJECT_NAME||';' from dba_objects where owner='ARUN' and object_type ='VIEW' ;
select 'DROP '||OBJECT_TYPE||'  '||OWNER||'.'||OBJECT_NAME||';' from dba_objects where owner='ARUN' and object_type ='PROCEDURE';
select 'DROP '||OBJECT_TYPE||'  '||OWNER||'.'||OBJECT_NAME||';' from dba_objects where owner='ARUN' and object_type='TRIGGER';
select 'DROP '||OBJECT_TYPE||'  '||OWNER||'.'||OBJECT_NAME||';' from dba_objects where owner='ARUN' and object_type='MATERIALIZED VIEW';
select 'DROP '||OBJECT_TYPE||'  '||OWNER||'.'||OBJECT_NAME||';' from dba_objects where owner='ARUN' and object_type='FUNCTION';
select 'DROP '||OBJECT_TYPE||'  '||OWNER||'.'||OBJECT_NAME||';' from dba_objects where owner='ARUN' and object_type='SYNONYM';
select 'DROP '||OBJECT_TYPE||'  '||OWNER||'.'||OBJECT_NAME||';' from dba_objects where owner='ARUN' and object_type='PACKAGE';
select 'DROP '||OBJECT_TYPE||'  '||OWNER||'.'||OBJECT_NAME||';' from dba_objects where owner='ARUN' and object_type='SEQUENCE';
select 'spool off' from dual;
spool off;