Gather ASM Detail in HTML Format

This script will help you to gather basic ASM environment details in HTML format.

SPOOL ASM.HTML 
SET MARKUP HTML ON 
set echo on 
set pagesize 200 
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS'; 
select 'THIS ASM REPORT WAS GENERATED AT: ==> ' , sysdate " " from dual; 
select 'HOSTNAME ASSOCIATED WITH THIS ASM INSTANCE: ==> ' , MACHINE " " from v$session where program like '%SMON%'; 
select * from v$asm_diskgroup; 
SELECT * FROM V$ASM_DISK ORDER BY GROUP_NUMBER,DISK_NUMBER; 
SELECT * FROM V$ASM_CLIENT; 
select * from V$ASM_ATTRIBUTE; 
select * from v$asm_operation; 
select * from gv$asm_operation; 
select * from v$asm_alias; 
select * from v$asm_file; 
select * from v$version; 
show parameter asm 
show parameter cluster 
show parameter instance_type 
show parameter instance_name 
show parameter spfile 
show parameter 
show sga 
spool off 
exit 

Script to Generate Tablespace DDL

select 'create tablespace ' || df.tablespace_name || chr(10)
 || ' datafile ''' || df.file_name || ''' size ' || df.bytes
 || decode(autoextensible,'N',null, chr(10) || ' autoextend on maxsize '
 || maxbytes)
 || chr(10)
 || 'default storage ( initial ' || initial_extent
 || decode (next_extent, null, null, ' next ' || next_extent )
 || ' minextents ' || min_extents
 || ' maxextents ' ||  decode(max_extents,'2147483645','unlimited',max_extents)
 || ') ;'
 from dba_data_files df, dba_tablespaces t
 where df.tablespace_name=t.tablespace_name
/

Why Certification?

  • Certification distinguish you from co-workers and competing job candidates
  • Certification helps secure your job and increase your employability
  • Certification can afford you increased job security in your current position
  • 97% of the Global Fortune 500 companies use Oracle software. These companies need skilled technologists to implement, develop and administer critical systems. Earning a certification equips you with these in demand skills, making you a more marketable candidate
  • Companies value skilled workers. According to Oracle's 2012 salary survey, more than 80% of certified individuals reported a promotion, compensation increase or other career improvements as a result of becoming certified.
  • Certification provides a consistent quality standard for the knowledge and skills of employees
  • Certification raises your visibility and increases your access to the industry’s most challenging opportunities
  • Certifications are especially important to those seeking employment in a field that often has many candidates competing for a single position
  • Companies which use any product, find it easy to sell services when their customers know that they are being serviced by certified experts as they are better equipped to complete complex tasks
  • Certifications are a reliable validation of training and experience that can accelerate one’s professional development, improve their productivity and also enhance their credibility.
  • The demand for certification is very much on the raise that in recent times there are huge number of certified professionals worldwide for each technology
  • Certifications are among the most sought-after and respected credentials in the IT industry
  • Certified IT professionals are among the highest paid employees in the IT industry
  • The skills and knowledge you gain by becoming certified will lead to greater confidence and increased career security. Your expanded skill set will also help unlock opportunities with employers and potential employers
  • The rigorous process of becoming certified makes you a better technologist. 
  • The knowledge you gain through training and practice will significantly expand your skill set and increase your credibility when interviewing for jobs


Script : Analyze and Shrink Datafile

This script will analyze datafile and give the approximate datafile which can be shrink with sql command

There is a chance you can shrink some of the datafiles (see below -- it'll tell you if you can and will generate the alter statements if they apply).  We can only shrink files back to their high water mark, if there is an extent way out at the end of a file we'll not be able to shrink it.

If not, the only thing that will undo this is to recreate the database (exp, imp).

spool shrink_output.lst
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(+)
/
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
/
spool off;

Useful Linux Command for Oracle DBA

--Create new tar

#tar -cvf tar_file_name.tar dirname/

--Extract from tar

#tar -xvf tar_file.tar

--view tar file

#tar -tvf tar_file_name.tar

--Search for given string

#grep -i "hostname" file_name

--Search for a given string in all files recursively

#grep -r "hostname" *

--Find file

#find -iname "filename"

--Execute commands on files found by the find command

#find -iname "filename" -exec rm -rf {} \;  --It will find the file and delete it.

--Find empty files in home directory



Script to Find Out OS Level Server Matrics


SELECT ‘Time now is ‘ METRIC_NAME,
To_char(sysdate, ‘dd/mm/yyyy hh24:mi:ss’) VALUE
FROM DUAL
UNION
SELECT to_char(inst_id) || ‘_’ || METRIC_NAME METRIC_NAME,
to_char(VALUE) value
FROM gV$SYSMETRIC
WHERE METRIC_NAME IN (‘Database CPU Time Ratio’,’Database Wait Time Ratio’)
AND INTSIZE_CSEC = (SELECT MAX(INTSIZE_CSEC) FROM gV$SYSMETRIC where inst_id=1)
and inst_id=1
UNION
SELECT to_char(inst_id) || ‘_’ || METRIC_NAME METRIC_NAME,
to_char(VALUE) value
FROM gV$SYSMETRIC
WHERE METRIC_NAME IN (‘Database CPU Time Ratio’,’Database Wait Time Ratio’)
AND INTSIZE_CSEC = (SELECT MAX(INTSIZE_CSEC) FROM gV$SYSMETRIC where inst_id=2)
and inst_id=2 ;