Oracle 10g Database

I am managing/administering payroll and HR databases and would like to know what standards or routines are ideal to manage the database? e.g weekly, monthly or yearly what should I look for as far as monitoring space and growth and other performance things. If anybody can recommend a template or scripts that would ba nice.
Thanks.
LVL 1
bonnaAsked:
Who is Participating?
 
gyansConnect With a Mentor Commented:
copied it here ...
                     Format


CURRENT ACCESS USER
*************************
select  sid,
        substr(owner,1,15) Owner,
        substr(object,1,25) Object,
        type
from v$access
order by owner;
 

***************************************************
CURRENT SESSION INFORMATION
***************************************************
select  substr(username,1,15) "DB UserName",
  substr(osuser,1,15) "OS UserName",
        substr(command,1,3) CMD,
        substr(machine,1,10) Machine,
        terminal, process, status,
        substr(program,1,50) "OS Program Name"
from v$session
where type = 'USER'
order by username;

********************************************
DATA DICTIONARY HIT RATIO
********************************************
select round(100 * (1 - (sum(getmisses) / sum(gets))))
 "Dictionary Cache Hit Ratio"
 from v$rowcache;


********************************************
LIBRARY CACHE HIT RATIO
********************************************
select sum(pins) "EXECUTIONS", sum(reloads) "MISSES",
       (100 * (sum(reloads) / sum(pins))) "LIBCACHE%"
from v$librarycache;


SELECT
  SUM(pins) "Pins",
  SUM(reloads) "Reloads",
  (SUM(pins)/(SUM(pins)+SUM(reloads)))*100 "Lib Cache Hit %"
FROM
  v$librarycache;


*********************************************************
LISTING OF DATAFILES TO TABLESPACES
*********************************************************
     col file_name format a45 heading 'Data File'
     col tablespace_name format a10 heading 'Tablespace'
     col bytes format 99,999,999,999
     break on tablespace_name    
     SELECT
          tablespace_name, file_name, bytes
     FROM
        dba_data_files
     ORDER BY
           tablespace_name, file_name;
 *************************************************************
redo log space wait time
**************************************************************    
select  substr(name,1,25) Name,
        substr(value,1,15) "VALUE (Near 0?)"
from v$sysstat
where name = 'redo log space requests';

REDO LOG SPACE WAIT TIME STATISTICS
It should be noticed that, if the REDO LOG SPACE REQUEST value
is regularly different from zero, that means the Redo Log
Buffer is too small and sould be increased through the LOG_BUFFER
parameter of the INIT.ora file.

*************************************************************
latch contention
*************************************************************
select  substr(l.name,1,25) Name,
        l.gets, l.misses,
        l.immediate_gets, l.immediate_misses
from v$latch l, v$latchname ln
where ln.name in ('redo allocation', 'redo copy')
and ln.latch# = l.latch#;
 
********************************************************                                          

      This script creates a report for all free space by Tablespace
      for a specified Database. For each tablespaces, the total bytes,
      bytes used, free bytes and the percent usage.
            
********************************************************

select substr(a.tablespace_name,1,20)                  "name",
       substr(b.tablespace_name,1,10)                  "dummy",
       sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )      bytes,
       sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id ) -
       sum(a.bytes)/count( distinct b.file_id )                       used,
       sum(a.bytes)/count( distinct b.file_id )                       free,
       100 * ( (sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )) -
             (sum(a.bytes)/count( distinct b.file_id ) )) /
        (sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )) pct_used
from sys.dba_free_space a, sys.dba_data_files b
where a.tablespace_name = b.tablespace_name
group by a.tablespace_name, b.tablespace_name;

************************************************************
lock detection
***********************************************************

select * from v$lock where sid in
   (select sid from v$session where username = 'TSD');


select a.object_name, b.oracle_username, b.locked_mode
       from dba_objects a, v$locked_object b
        where b.object_id = a.object_id;

*************************************************************
What session is holding the lock that makes the database wait
*************************************************************
select sid, serial# from v$session
       where sid in (SELECT holding_session FROM dba_waiters);

Alter system kill session 'sid,serial#';

****************************************************
TEMP SPACE FULL
**************************************************


/** Who is using the TEMP tablespace right now???**/


select b.tablespace, b.segfile#, b.segblk#, b.blocks,  
       a.sid, a.serial#, a.username, a.osuser, a.status
from v$session a, v$sort_usage b
where a.saddr = b.session_addr
order by b.tablespace, b.segfile#, b.segblk#, b.blocks;


***************************************************
        FILE I/O USAGE
**************************************************
select substr(df.file#,1,2) "ID",
       rpad(substr(name,1,52),52,'.') "File Name",
       rpad(substr(phyrds,1,10),10,'.') "Phy Reads",
       rpad(substr(phywrts,1,10),10,'.') "Phy Writes",
       rpad(substr(phyblkrd,1,10),10,'.') "Blk Reads",
       rpad(substr(phyblkwrt,1,10),10,'.') "Blk Writes",
       rpad(substr(readtim,1,9),9,'.') "Read Time",
       rpad(substr(writetim,1,10),10,'.') "Write Time",
       (sum(phyrds+phywrts+phyblkrd+phyblkwrt+readtim)) "File Total"
from v$filestat fs, v$datafile df
where fs.file# = df.file#
group by df.file#, df.name, phyrds, phywrts, phyblkrd,
         phyblkwrt, readtim, writetim
order by sum(phyrds+phywrts+phyblkrd+phyblkwrt+readtim) desc, df.name;

NOTE: It should be noticed that, all such Database Objects having a maximum
      number of extents greater than five (10) must be considered for
      Reorganization, Rebuilding, Resizing Correctly to Resolve Database
      Objects Fragmentation Problems.
******************************************************************
SEGMENT FRAGMENTATION
******************************************************************
*
* HOW ARE THESE TERRIBLE SQLs KILLING OUR ORACLE SERVER PERFORMANCE???

*
select substr(a.user_name,1,10) "User Name", b.disk_reads, b.buffer_gets,
       substr(c.sql_text,1,70) "SQL KILLER"
from v$open_cursor a, v$sqlarea b, v$sqltext c
where a.address = b.address
  and a.address = c.address
  and disk_reads >= 15
order by a.user_name, a.address, c.piece;


**********************************************************************
   rollback information
**********************************************************************


________________________________________________________

select substr(V$rollname.NAME,1,20) "Rollback_Name",
        substr(V$rollstat.EXTENTS,1,6) "EXTENT",
        v$rollstat.RSSIZE, v$rollstat.WRITES,
        substr(v$rollstat.XACTS,1,6) "XACTS",
        v$rollstat.GETS,
        substr(v$rollstat.WAITS,1,6) "WAITS",
        v$rollstat.HWMSIZE, v$rollstat.SHRINKS,
        substr(v$rollstat.WRAPS,1,6) "WRAPS",
        substr(v$rollstat.EXTENDS,1,6) "EXTEND",
        v$rollstat.AVESHRINK,
        v$rollstat.AVEACTIVE
from v$rollname, v$rollstat
where v$rollname.USN = v$rollstat.USN
order by v$rollname.USN;
 
*****************************
ROLLBACK SEGMENT CONTENTION
****************************
select class, count
from v$waitstat  
where class in ('system undo header','system undo block', 'undo header','undo block');
     
 *******************************************************************************************
  CREEPING EXTENTS(EXTENTS MORE THAN 10)
******************

 select segment_name, max(extents), sum(bytes)
        from user_segments
        group by segment_name
       having max(extents) >35

It should be noticed that, all such Database Objects having a maximum
      number of extents greater than five (10) must be considered for
      Reorganization, Rebuilding, Resizing Correctly to Resolve Database
      Objects Fragmentation Problems.

*****************************************************************
  file i/o
*****************************************************************
select substr(df.file#,1,2) "ID",
       rpad(substr(name,1,52),52,'.') "File Name",
       rpad(substr(phyrds,1,10),10,'.') "Phy Reads",
       rpad(substr(phywrts,1,10),10,'.') "Phy Writes",
       rpad(substr(phyblkrd,1,10),10,'.') "Blk Reads",
       rpad(substr(phyblkwrt,1,10),10,'.') "Blk Writes",
       rpad(substr(readtim,1,9),9,'.') "Read Time",
       rpad(substr(writetim,1,10),10,'.') "Write Time",
       (sum(phyrds+phywrts+phyblkrd+phyblkwrt+readtim)) "File Total"
from v$filestat fs, v$datafile df
where fs.file# = df.file#
group by df.file#, df.name, phyrds, phywrts, phyblkrd,
         phyblkwrt, readtim, writetim
order by sum(phyrds+phywrts+phyblkrd+phyblkwrt+readtim) desc, df.name;


*************************************************************
            fragmentatioin need
*************************************************************


select  substr(de.owner,1,8) "Owner",
        substr(de.segment_type,1,8) "Seg Type",
        substr(de.segment_name,1,35) "Table Name (Segment)",
        substr(de.tablespace_name,1,20) "Tablespace Name",
        count(*) "Frag NEED",
        substr(df.name,1,40) "DataFile Name"
from sys.dba_extents de, v$datafile df
where de.owner <> 'SYS'
and de.file_id = df.file#
and de.segment_type = 'TABLE'
group by de.owner, de.segment_name, de.segment_type, de.tablespace_name,  
df.name
having count(*) > 1
order by count(*) desc;

try this ...
0
 
gyansCommented:
There are lots of script to monitor the space usage ,alert log file , max extends etc to look after the database .
What version of Oracle are you using ?
I monitor my database weekly  , it depends on quite a few factors :how the application runs and how the db is growing .If you give me more specifics then I can let you know more on reports for the database that will help you regularly.


0
 
peter991Commented:
Check your alertlog every day. Create a surveillance-script looking for ORA-, Error or Fail.
Make sure you don't run out of space in your tablespaces (or disks).
Check your backups every day.
Once a month you could run a validate on the backups.
Run AWR once in a while.

There are many scripts on the net but you will become more familiar with your database if you understand what you are running against it.
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
peter991Commented:
And don't forget to gather statistics. DBMS_STATS..
0
 
suredazzleCommented:

Make sure backup DB frequently, in case fire, earthquake, or hijack.

Those are huge data for security.
0
 
bonnaAuthor Commented:
Thanks so mucyh guys, I appreciate your responses. To answer Gyans question I am running Oracle 10g (10.2.0.4) and I have scripts here and there and want to put a plan together for daily,weekly,monthly,quartely and yearly kind of scripts and I am working on it but still would like to know if someone does this on a routine basis and have it all together, I am running backps already.
Let me know.
Thanks again.
0
 
gyansCommented:
I do have the scripts and  dont have them with me .
I can post it tomorrow .
It runs every monday morning to and emails me the results .
It check the tablespace utiliztion , undo and temp utilization , table segments that need auto extend or max size etc .Backups- You need to run hot backup for production database and also run exports or a datapump job for the schemas that are used most frequently .Maybe hust the schema structure would help .
I have needed them a lot from time to time .
0
 
bonnaAuthor Commented:
Actually, I am running export backups twice a day for the whole db and my db is not in archive log mode yet. I really do not think they need point in time recovery just in case but I still wonder about other things if I loose a control or data file the recvoery would be to install db again and import the data. But any way it would be great if you post the scripts tomorrow and I can look at it and add to my scripts that I am missing.
Thanks.
0
 
peter991Commented:
Can your company afford the time it takes to reinstall and import data?
With RMAN you can offline the "broken" parts and restore it while people still accessing the database.

Regards.
0
 
bonnaAuthor Commented:
Answering Peter's quesion - sometime it is better to start from scratch depending upon the nature of Application and it is easiere also. So far they are ok with the down time. but we have other OLTP apps that are in archive log mode and running RMAN.
0
 
bonnaAuthor Commented:
Just wonder if Gyans is able to find a time to post the scripts he has, please?
Thanks.
0
 
bonnaAuthor Commented:
No Objections at all!!
0
 
suredazzleCommented:

Not sure it help! This will get you started.


select
        a.tablespace_name ,
        tbsize  ,
        tbfree ,
        b.tbfree/a.tbsize*100 "ratio" ,
         b.Largest "Largest space"
from
        ( select tablespace_name,sum(bytes)/1024/1024 tbsize
                from dba_data_files
                group by tablespace_name) a,
        ( select tablespace_name,sum(bytes)/1024/1024 tbfree,
                 max(bytes)/1024/1024 Largest
                from dba_free_space
                group by tablespace_name) b
where a.tablespace_name=b.tablespace_name
order by 4;
 
insert into tbs select to_char(sysdate,'DD-MM-YYY HH24:MI:SS') as timestamp,
        a.tablespace_name ,
        tbsize  ,
        tbfree ,
        b.tbfree/a.tbsize*100 "ratio" ,
         b.Largest "Largest space"
from
        ( select tablespace_name,sum(bytes)/1024/1024 tbsize
                from dba_data_files
                group by tablespace_name) a,
        ( select tablespace_name,sum(bytes)/1024/1024 tbfree,
                 max(bytes)/1024/1024 Largest
                from dba_free_space
                group by tablespace_name) b
where a.tablespace_name=b.tablespace_name
order by 4;

Open in new window

0
 
gyansCommented:
Sorry i was out ,let me know if you still need the scripts .
0
 
bonnaAuthor Commented:
Gyans- sure if you still have time to upload that would be great!!
0
 
gyansCommented:

Attached a file ,see if it helps and if you need more .
0
 
bonnaAuthor Commented:
gyan, I don't see any files attached??
0
 
suredazzleCommented:

Thanks Bonna, sqlplus login info!
0
 
suredazzleCommented:

Unix: oracle, go to bin to look up oraenv & etc. :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.