Pavan DBA's Blog

The DBA Knowledge Store

Archive for the ‘Scripts’ Category

script to check db status in rac env

Posted by Pavan DBA on May 24, 2012


***************************************************
script to check db status in rac env
***************************************************

#!/bin/csh

setenv ORATAB /var/opt/oracle/oratab
date
foreach x (`cat ${ORATAB} | grep -v “^#”| grep “^[a-z]” | grep -v “demo” | grep -v “_sp”|awk -F: ‘{print $1}’`)
        setenv ORAENV_ASK 1
        setenv ORACLE_SID “$x”
        source /usr/local/default/oracle_sid.csh
            $ORACLE_HOME/bin/sqlplus -s <<EOF
        / as sysdba
        set lines 132
        set pages 200
            set hea off
        set feedback off
        col machine for a30
        col host_name for a40
        select instance_name, host_name, logins, status from gv\$instance;
        exit
EOF
end

Posted in RAC, Scripts | Tagged: | Leave a Comment »

script to check db status in a server

Posted by Pavan DBA on May 24, 2012


***************************************************
script to check db status in a server
***************************************************

#!/bin/csh

setenv ORATAB /var/opt/oracle/oratab
date
foreach x (`cat ${ORATAB} | grep -v “^#”| grep “^[a-z]” | grep -v “demo” | grep -v “test”| grep -v “*” | awk -F:

‘{print $1}’`)
        setenv ORAENV_ASK 1
        setenv ORACLE_SID “$x”
        source /usr/local/default/oracle_sid.csh
            sqlplus -s <<EOF
        / as sysdba
        set lines 132 pages 200 hea off feedback off trims on
        col host_name for a20
                spool chk_dbstatus.log
        select instance_name, host_name, logins, version, status from v\$instance;
                spool off
        exit
EOF

Note: The above script is written using “C” shell, so if you are using any other shell, make necessary changes. Also as always first test it in lower environments

Posted in Administration, Scripts | Tagged: , , | Leave a Comment »

script to find redo generated by current sessions

Posted by Pavan DBA on March 30, 2012


#################################################

redo generated by current sessions

#################################################

 

select v$session.sid, username, value redo_size

from v$sesstat, v$statname, v$session

where v$sesstat.STATISTIC# = v$statname.STATISTIC#

and v$session.sid = v$sesstat.sid

and name = ‘redo size’

and value > 0

and username is not null

order by value

/

Posted in Performance Tuning, Scripts | Tagged: , , | 12 Comments »

script to find sql text with sid of a session

Posted by Pavan DBA on March 30, 2012


#######################

TO FIND SQL TEXT FROM SID

#######################

 

select a.sid,a.program,b.sql_text

from v$session a, v$sqltext b

where a.sql_hash_value = b.hash_value

and a.sid=429

order by a.sid,hash_value,piece;

Posted in Performance Tuning, Scripts | Tagged: , , , , , , , , | Leave a Comment »

script to check possible resize value for a datafile

Posted by Pavan DBA on March 30, 2012


Sometimes, we will be getting a requirement to resize the datafile to a lower value in order to create some space in the mount point (file system). In that case, how we would know which datafile and to what size we can lower it? The below script will helps us in that….

###################################################

SCRIPT TO CALCUTE POSSIBLE RESIZE VALUE

###################################################

 

 

set verify off

column file_name format a50 word_wrapped

column smallest format 999,990 heading “Smallest|Size|Poss.”

column currsize format 999,990 heading “Current|Size”

column savings format 999,990 heading “Poss.|Savings”

break on report

compute sum of savings on report

 

column value new_val blksize

select value from v$parameter where name = ‘db_block_size’

/

 

select file_name,

ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,

ceil( blocks*&&blksize/1024/1024) currsize,

ceil( blocks*&&blksize/1024/1024) –

ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings

from dba_data_files a,

( select file_id, max(block_id+blocks-1) hwm

from dba_extents

group by file_id ) b

where a.file_id = b.file_id(+)

/

 

select file_name,

ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,

ceil( blocks*&&blksize/1024/1024) currsize,

ceil( blocks*&&blksize/1024/1024) –

ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings

from dba_temp_files a,

( select file_id, max(block_id+blocks-1) hwm

from dba_extents

group by file_id ) b

where a.file_id = b.file_id(+)

/

 

column cmd format a75 word_wrapped

 

select ‘alter database datafile ”’ || file_name || ”’ resize ‘ ||

ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) || ‘m;’ cmd

from dba_data_files a,

( select file_id, max(block_id+blocks-1) hwm

from dba_extents

group by file_id ) b

where a.file_id = b.file_id(+)

and ceil( blocks*&&blksize/1024/1024) –

ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0

/

 

 

select ‘alter database tempfile ”’ || file_name || ”’ resize ‘ ||

ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) || ‘m;’ cmd

from dba_temp_files a,

( select file_id, max(block_id+blocks-1) hwm

from dba_extents

group by file_id ) b

where a.file_id = b.file_id(+)

and ceil( blocks*&&blksize/1024/1024) –

ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0

/

 

Posted in Administration, Scripts | Tagged: , | 2 Comments »

 
%d bloggers like this: