?
Solved

Oracle 10g Database

Posted on 2008-11-13
19
Medium Priority
?
471 Views
Last Modified: 2013-12-19
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.
0
Comment
Question by:bonna
  • 7
  • 5
  • 4
  • +1
19 Comments
 
LVL 1

Expert Comment

by:gyans
ID: 22954125
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
 
LVL 6

Expert Comment

by:peter991
ID: 22955645
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
 
LVL 6

Expert Comment

by:peter991
ID: 22955669
And don't forget to gather statistics. DBMS_STATS..
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 1

Expert Comment

by:suredazzle
ID: 22955802

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

Those are huge data for security.
0
 
LVL 1

Author Comment

by:bonna
ID: 22961977
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
 
LVL 1

Expert Comment

by:gyans
ID: 22962605
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
 
LVL 1

Author Comment

by:bonna
ID: 22962885
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
 
LVL 6

Expert Comment

by:peter991
ID: 23002302
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
 
LVL 1

Author Comment

by:bonna
ID: 23015152
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
 
LVL 1

Author Comment

by:bonna
ID: 23055410
Just wonder if Gyans is able to find a time to post the scripts he has, please?
Thanks.
0
 
LVL 1

Author Comment

by:bonna
ID: 23109352
No Objections at all!!
0
 
LVL 1

Expert Comment

by:suredazzle
ID: 23110212

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
 
LVL 1

Expert Comment

by:gyans
ID: 23110290
Sorry i was out ,let me know if you still need the scripts .
0
 
LVL 1

Author Comment

by:bonna
ID: 23110597
Gyans- sure if you still have time to upload that would be great!!
0
 
LVL 1

Expert Comment

by:gyans
ID: 23112457

Attached a file ,see if it helps and if you need more .
0
 
LVL 1

Author Comment

by:bonna
ID: 23113563
gyan, I don't see any files attached??
0
 
LVL 1

Accepted Solution

by:
gyans earned 2000 total points
ID: 23113912
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
 
LVL 1

Expert Comment

by:suredazzle
ID: 23124146

Thanks Bonna, sqlplus login info!
0
 
LVL 1

Expert Comment

by:suredazzle
ID: 23125069

Unix: oracle, go to bin to look up oraenv & etc. :)
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses
Course of the Month16 days, 19 hours left to enroll

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question