AskTom

SQL> SELECT SYSDATE - logon_time “DB Uptime in Days”,
(SYSDATE - logon_time) * 24 “DB Uptime in Hours”
FROM v$session
WHERE program LIKE ‘%PMON%’  2    3    4  ;

DB Uptime in Days DB Uptime in Hours
—————– ——————
82.17334491         1972,16027

–> we moved server 3 months ago ;o)

Regards,

Tom

1) query sys.registry$history table

SQL> select * from sys.registry$history;

ACTION_TIME
—————————————————————————
ACTION                         NAMESPACE
—————————— ——————————
VERSION                                ID
—————————— ———-
COMMENTS
——————————————————————————–
30-JAN-06 02.15.01.391009 PM
CPU                            SERVER
10.2.0.1.0                        4751931
CPUJan2006

ACTION_TIME
—————————————————————————
ACTION                         NAMESPACE
—————————— ——————————
VERSION                                ID
—————————— ———-
COMMENTS
——————————————————————————–
10-APR-07 09.50.41.829940 AM
UPGRADE                        SERVER
10.2.0.3.0
Upgraded from 10.2.0.1.0

2) using opatch

oracle@tnode:~> /opt/app/oracle/product/10.2.0/db10g_002/OPatch/opatch lsinventory
Invoking OPatch 10.2.0.3.0

Oracle interim Patch Installer version 10.2.0.3.0
Copyright (c) 2005, Oracle Corporation.  All rights reserved..

Oracle Home       : /opt/app/oracle/product/10.2.0/db10g_002
Central Inventory : /opt/app/oracle/oraInventory
from           : /etc/oraInst.loc
OPatch version    : 10.2.0.3.0
OUI version       : 10.2.0.3.0
OUI location      : /opt/app/oracle/product/10.2.0/db10g_002/oui
Log file location : /opt/app/oracle/product/10.2.0/db10g_002/cfgtoollogs/opatch/opatch2009-07-15_12-25-33PM.log

Lsinventory Output file location : /opt/app/oracle/product/10.2.0/db10g_002/cfgtoollogs/opatch/lsinv/lsinventory2009-07-15_12-25-33PM.txt

——————————————————————————–
Installed Top-level Products (3):

Oracle Database 10g                                                  10.2.0.1.0
Oracle Database 10g Release 2 Patch Set 1                            10.2.0.2.0
Oracle Database 10g Release 2 Patch Set 2                            10.2.0.3.0
There are 3 products installed in this Oracle Home.

Interim patches (72) :

Patch  5872000      : applied on Thu Jul 10 08:48:27 UTC 2008
Created on 3 Feb 2008, 21:44:35 hrs US/Pacific
Bugs fixed:
5872000

Patch  5881721      : applied on Thu Mar 08 14:32:46 UTC 2007
Created on 19 Feb 2007, 02:16:34 hrs US/Pacific
Bugs fixed:
5885186, 5881721
——————————————————————————–

OPatch succeeded.

Regards,

Tom

The Buffer Cache Hit Ratio Oracle metric monitors the rate at which Oracle finds the data blocks it needs in memory over the lifetime of an instance.

Many DBAs do their best to get a 99% or better buffer cache hit ratio, but quickly discover that the performance of their database isn’t improving as the hit ratio gets better.

SQL> select 1-(PHY.VALUE - LOB.VALUE - DIR.VALUE)/SES.VALUE “CACHE HIT RATIO”
2  FROM V$SYSSTAT SES, V$SYSSTAT LOB, V$SYSSTAT DIR, V$SYSSTAT PHY
3  WHERE SES.NAME =’session logical reads’
4  and DIR.NAME =’physical reads direct’
5  and LOB.NAME =’physical reads direct (lob)’
6  and PHY.NAME =’physical reads’
7  /

CACHE HIT RATIO
—————
.992542929

SQL> SELECT SUM(PINS-RELOADS)/SUM(PINS)*100 “Library Cache Hit Ratio”
FROM V$LIBRARYCACHE
/   2    3

Library Cache Hit Ratio
———————–
99.5636589

SQL> SELECT (SUM(GETS-GETMISSES))/SUM(GETS) “Dictionary Cache Hit Ratio”
FROM V$ROWCACHE
/   2    3

Dictionary Cache Hit Ratio
————————–
.983281667

As you can see in my case it ok.

Regards,

Tom