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;
#############
Archive logs generated per day
SELECT A.*,
Round(A.Count#*B.AVG#/1024/1024) Daily_Avg_Mb
FROM
(
SELECT
To_Char(First_Time,'YYYY-MM-DD') DAY,
Count(1) Count#,
Min(RECID) Min#,
Max(RECID) Max#
FROM
v$log_history
GROUP
BY To_Char(First_Time,'YYYY-MM-DD')
ORDER
BY 1 DESC
) A,
(
SELECT
Avg(BYTES) AVG#,
Count(1) Count#,
Max(BYTES) Max_Bytes,
Min(BYTES) Min_Bytes
FROM
v$log
) B;
##########
Delete millions of records without shortage of UNDO. Worked for me.
BEGIN
LOOP
delete FROM abcc.emp
WHERE DATE_COLMN > TO_DATE('01/12/2016','dd/mm/yyyy') and rownum < 1000001;
exit when SQL%rowcount =0;
commit;
END LOOP;
END;
/
