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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
peter991Commented:
And don't forget to gather statistics. DBMS_STATS..
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
gyansCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
suredazzleCommented:

Thanks Bonna, sqlplus login info!
0
suredazzleCommented:

Unix: oracle, go to bin to look up oraenv & etc. :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.