Checking memory usage in HP-UX

If you feel that your server is slower than usual, you could check memory usage and swapping with ps, top, vmstat and glance. In this example, as you can see in Mem Util and Swap Util in glance this server used a lot of memory and swap space at some time, but at the time of this sample there were no paging or memory shortage as seen in memory "active" (in parentheses) in the top report, as seen in low values in Page In and Page Out in glance, and also low values in pi and po in vmstat report.

$ ps -elx|sort -k 10b,10rn|more
  F S        UID   PID  PPID  C PRI NI             ADDR   SZ            WCHAN TTY       TIME COMD
1401 S        113  6938     1  0 154 20 e000000163528680 9728 e000000149c7d080 ?        465:09 ora_s000_mydb
1401 S        113  6083     1  0 154 20 e0000001bcc40400 9620 e0000001674f6ee8 ?         0:00 oraclemydb (LOCAL=NO)
1401 S        113  6940     1  0 154 20 e0000001635c5100 9147 e000000149c7d080 ?         3:13 ora_d000_mydb
1401 S        113  6932     1  0 156 20 e0000001636fc700 9103 e000000167e27128 ?         2:40 ora_smon_mydb
1401 S        113  6926     1  0 156 20 e000000163664980 9012 e000000167e270f8 ?        25:24 ora_dbw0_mydb
1401 S        113  6934     1  0 156 20 e00000016362a700 8992 e000000167e27138 ?         0:02 ora_reco_mydb
1401 S        113  6924     1  0 154 20 e00000016340b700 8979 e000000149c7d080 ?         9:48 ora_pmon_mydb
1401 S        113  6936     1  0 156 20 e00000016348ba00 8967 e000000167e27148 ?        12:17 ora_cjq0_mydb
1401 S        113  6930     1  0 156 20 e000000163370980 8938 e000000167e27118 ?        12:59 ora_ckpt_mydb
1401 S        113  6928     1  0 156 20 e0000001634d6980 8924 e000000167e27108 ?         9:48 ora_lgwr_mydb
2541 R          0  6003     1  0 -16 10 e0000001a7370d00 4271                - ?         0:22 /opt/perf/bin/midaemon
2401 S        113  5999  5798  0 154 10 e0000001bb0fba00 1016 e00000018409c800 pts/0     0:19 glance
541  R          0     1     0  0 152 20 e00000010853e380  574                - ?         0:10 init
1401 S        113  6961     1  0 154 20 e000000163945a00  552 e000000149c7d080 ?         1:02 /oracle92/product/9.2.0/bin/tnslsnr LISTENER -inherit
1401 S          0  6088  1564  0 154 20 e0000001b1098980  491 e00000030f73fe68 ?         0:00 sshd: oracle [priv]
3401 S        113  6090  6088  0 154 20 e0000001b2c90100  491 e000000149c7d080 ?         0:00 sshd: oracle@pts/1
3401 S        127  6333  6331  0 154 20 e0000001c2b09d00  487 e000000149c7d080 ?         0:00 sshd: myuser@pts/2
1401 S          0  6331  1564  0 154 20 e0000001c8a15d00  471 e00000014eeffee8 ?         0:00 sshd: myuser [priv]
1401 S          0  6605  1564  0 154 20 e0000001ac0fc980  471 e000000164a63568 ?         0:00 sshd: otherusr [priv]
3401 S         12  6617  6605  0 154 20 e0000001b550c680  471 e000000149c7d080 ?         0:00 sshd: otherusr@pts/3
3401 S        113  5794  5791  0 154 20 e0000001b8695380  466 e000000149c7d080 ?         0:00 sshd: oracle@pts/0
$ top

System: myserver                                      Fri Apr  8 10:37:43 2011
Load averages: 0.02, 0.12, 0.34
182 processes: 141 sleeping, 41 running
Cpu states:
CPU   LOAD   USER   NICE    SYS   IDLE  BLOCK  SWAIT   INTR   SSYS
 0    0.02   0.2%   0.0%   0.2%  99.6%   0.0%   0.0%   0.0%   0.0%
---   ----  -----  -----  -----  -----  -----  -----  -----  -----
avg   0.02   0.2%   0.0%   0.2%  99.6%   0.0%   0.0%   0.0%   0.0%

System Page Size: 4Kbytes
Memory: 1151700K (788492K) real, 3840196K (2936972K) virtual, 26556K free  Page# 1/14

CPU TTY  PID USERNAME PRI NI   SIZE    RES STATE    TIME %WCPU  %CPU COMMAND
 0   ? 27479 root     152 20   264M 20128K run     95:34  0.34  0.34 agctrlcell
 0   ?  6938 oracle   154 20  1973M  4444K sleep  465:16  0.32  0.31 ora_s000_mydb
 0   ?  2731 root     152 20   155M  7928K run      4:22  0.31  0.31 vxpal
 0   ?    75 root     152 20 17208K 15296K run     44:50  0.29  0.29 vxfsd
 0   ?  2666 root     152 20   112M  4332K run      1:30  0.29  0.29 vxsvc  
 0   ? 27475 root     152 20   694M 15332K run     84:10  0.29  0.29 evtcell
 0   ?  3532 root     168 20 11700K   592K sleep  307:47  0.29  0.29 utild
 0   ?  2357 root     152 20 26072K  1880K run      0:29  0.14  0.14 rpcd
 0   ?  3021 root     152 20 92272K  2296K run      0:09  0.14  0.14 vxpal
 0   ?  3078 root     152 20 99636K  1568K run      0:08  0.14  0.14 vxpal
 0   ?  3197 root     152 20 25832K  1612K run      0:54  0.14  0.14 swagentd
$ glance

      C - cum/interval toggle                                                                                                             Page 1 of 2
ProcList CPU Rpt  Mem Rpt  Disk Rpt                                                                                   NextKeys SlctProc   Help     Exit  
 Glance C.04.70.001             10:31:24 myserver     ia64                                                                          Current  Avg  High
------------------------------------------------------------------------------------------------------------------------------------------------------
CPU  Util   SSU  U                                                                                                                   |  5%   32%   86%
Disk Util   F                                                                                                                        |  1%   30%   77%
Mem  Util   S                                                                                 SU                           U         | 99%  100%  100%
Swap Util   U                      UR      R                                                                                         | 26%   26%   26%
------------------------------------------------------------------------------------------------------------------------------------------------------
                                                                    MEMORY REPORT                                                         Users=    4
Event         Current   Cumulative   Current Rate   Cum Rate   High Rate
--------------------------------------------------------------------------------
Page Faults         0         7593         0.0       12.5       548.6
Page In             0         5149         0.0        8.4       350.8
Page Out            0           56         0.0        0.0         1.3
KB Paged In       0kb       20.1mb         0.0       33.9      7455.2
KB Paged Out      0kb        224kb         0.0        0.3         5.4
Reactivations       0            0         0.0        0.0         0.0
Deactivations       0            0         0.0        0.0         0.0
KB Deactivated    0kb          0kb         0.0        0.0         0.0
VM Reads            0          435         0.0        0.7        33.1
VM Writes           0            0         0.0        0.0         0.0
Total VM :   3.6gb   Sys Mem  :   2.6gb   User Mem:   924mb   Phys Mem :   3.7gb
Active VM:   2.3gb   Buf Cache:     1mb   Free Mem:    25mb   FileCache:   215mb
MemFS Blk Cnt:                0   MemFS Swp Cnt:                0

ProcList CPU Rpt  Mem Rpt  Disk Rpt                                                                                   NextKeys SlctProc   Help     Exit  
$ vmstat 10 
         procs           memory                   page                              faults       cpu
    r     b     w      avm    free   re   at    pi   po    fr   de    sr     in     sy    cs  us sy id
    3     1     0   667747   14981    1    0     7   10     1    0    34    297   2178   238   3  1 96
    1     1     0   562145   14956    4    0    12    0     0    0     0    221    644   139   1  1 98
    1     1     0   762287   14964    0    0     0    0     0    0     0    133    545    97   1  0 98
    1     1     0   761304   14980    0    0     0    0     0    0     0    118    451    87   0  0 100
    1     1     0   550784   14964    0    0     0    0     0    0     0    118    387    88   0  0 100

Also, if you have root access you can check memory usage with swapinfo, sam and in /dev/mem like this (example from a different server):

# swapinfo -tm
             Mb      Mb      Mb   PCT  START/      Mb
TYPE      AVAIL    USED    FREE  USED   LIMIT RESERVE  PRI  NAME
dev        8192     490    3606    6%       0       -    1  /dev/vg00/lvol2
reserve       -    2986   -2986
memory     3016    2780     236   92%
total     11208    6256     856   56%       -       0    -
# echo phys_mem_pages/D | adb -k /stand/vmunix /dev/mem
phys_mem_pages:
phys_mem_pages: 1048064
# sam

  ┌                       System Properties (MYSERVR)                                  ┐  
  │┌────────────────────────────────────────┐│  
  ││                                                                                 ^│  
  ││ [ Refresh ]                                                                      │  
  ││                                                                                  │  
  ││ ┌─────┐┌────┐┌─────────┐┌─────┐┌────┐       │  
  ││ │ Processor ││ Memory ││ Operating System ││ Network ││ Dynamic │      │  
  ││ ┌───────/        \──────────────────────────┐ │  
  ││ │┌────────────────────────────────────┐│ │  
  ││ ││Physical Memory:         4097.7 MB                                      ││ │  
  ││ ││Real Memory:                                                            ││ │  
  ││ ││  Active:                954731.6 KB                                    ││ │  
  ││ ││  Total:                 1318838.6 KB                                   ││ │  
  ││ ││Virtual Memory:                                                         ││ │  
  ││ ││  Active:                1850220.6 KB                                   ││ │  
  ││ ││  Total:                 2375723.8 KB                                   ││ │  
  ││ ││Free Memory Pages:       21640 at 4 KB/page                             ││ │  
  ││ ││Swap Space:                                                             ││ │  
  ││ ││  Avail:                 8192 MB                                        ││ │  
  ││ ││  Used:                  3461 MB                                        ││v│  
  │└────────────────────────────────────────┘│  
  │──────────────────────────────────────────│  
  │ [   OK   ]                                                   [  Help  ]            │  
  └──────────────────────────────────────────┘  

More Information:

Memory Usage (What is using all of the memory?)

How to get the size of an Oracle database

If you need to get the size of an Oracle database (excluding software, logs and things like that), you can do that this way:

SQL> select 'ONLINE_REDOLOG' as TYPE, sum(BYTES*MEMBERS)/1048576 as SIZE_MB from v$log union
select 'DATAFILES', sum(BYTES)/1048576 from v$datafile union
select 'TEMPFILES', sum(BYTES)/1048576 from v$tempfile union
select 'CONTROLFILES', sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1048576 from v$controlfile union
select 'FLASH_RECOVERY_AREA', VALUE/1048576 from v$parameter where NAME='db_recovery_file_dest_size';

TYPE                   SIZE_MB
------------------- ----------
CONTROLFILES           14.1875
DATAFILES                 1230
FLASH_RECOVERY_AREA       3072
ONLINE_REDOLOG             300
TEMPFILES                   25

How to get schema's objects DDL

In Oracle, if you need to recreate, copy or compare an entire schema (or even a whole database) without the data, you can use the DBMS_METADATA.GET_DDL package in order to get object's DDL like this:

set linesize 150
set long 90000
set pagesize 0
set wrap on
column mdata format a150

SELECT DBMS_METADATA.GET_DDL(object_type, object_name, owner) as mdata FROM DBA_OBJECTS
WHERE OWNER = 'MYSCHEMA' and OBJECT_TYPE not in ('LOB') ORDER BY object_type, object_name;

More information about getting object's DDL:

Get table and index DDL the easy way
DDL Generation--Oracle's Answer to Save You Time and Money

Managing datafile's size

If you need to change the actual size or maximum size of a datafile, you can check tablespace and datafile information with these handy sentences:

SQL> column expandible format A10
SQL> column Tot_Size_MB format 99,999,999 
SQL> column Tot_Free_MB format 99,999,999
SQL> column maxbytes_MB format 99,999,999
SQL> column PCT_USED format 999.9
SQL> column Chunks_Free format 99,999,999   

SQL> select a.tablespace_name,
sum(a.sum_alloc)/1048576 Tot_Size_MB,
sum(a.sum_free)/1048576 Tot_Free_MB,
100 - sum(a.sum_free)*100/sum(a.sum_alloc) PCT_USED,
sum(a.chunks) Chunks_Free,
max(expandible) expandible, 
sum(maxbytes)/1048576 maxbytes_MB
from
(select tablespace_name,0 sum_alloc,sum(bytes) sum_free,
max(bytes) largest,count(*) chunks, ' ' expandible, 0 maxbytes
from dba_free_space a
where tablespace_name != 'UNDO'
group by tablespace_name
union all
select tablespace_name,sum(bytes) sum_alloc, 0, 0, 0,
max(autoextensible), sum(case when maxbytes = 0 then bytes else maxbytes end) maxbytes
from dba_data_files
where tablespace_name != 'UNDO'
group by tablespace_name) a
group by a.tablespace_name
order by PCT_USED desc;

TABLESPACE_NAME  TOT_SIZE_MB TOT_FREE_MB PCT_USED CHUNKS_FREE EXPANDIBLE MAXBYTES_MB
---------------- ----------- ----------- -------- ----------- ---------- -----------
MYDATA                 3,584           4     99.9           4 YES              4,096
MYINDEXES                312           6     98.1           8 YES              1,024
PERFSTAT                 192          48     74.9           1 YES                384
SYSAUX                   300          97     67.7          12 NO                 300
SYSTEM                   312         102     67.4           9 NO                 312
USERS                    128         126      1.7         915 YES              1,024

6 rows selected.

SQL> column FILE_NAME format a45
SQL> select TABLESPACE_NAME, FILE_NAME, AUTOEXTENSIBLE, MAXBYTES
from dba_data_files order by TABLESPACE_NAME, FILE_NAME;

TABLESPACE_NAME                FILE_NAME                    AUT   MAXBYTES
------------------------------ ---------------------------- --- ----------
MYDATA                         /mydb/mydata01.dbf           YES 4294967296
MYINDEXES                      /mydb/myindexes01.dbf        YES 1073741824
PERFSTAT                       /mydb/perfstat.dbf           YES  402653184
SYSAUX                         /mydb/sysaux01.dbf           NO           0
SYSTEM                         /mydb/system01.dbf           NO           0
UNDO                           /mydb/undo01.dbf             NO           0
USERS                          /mydb/users01.dbf            YES 1073741824

7 rows selected.

SQL> alter database datafile '/mydb/mydata01.dbf' AUTOEXTEND ON MAXSIZE 4608M;

Database altered.

SQL> alter database datafile '/mydb/myindexes01.dbf' resize 2G;

Database altered.

Patching an Oracle Database 9i

This is a transcript of a session where it was applied a patch to an Oracle Database 9i installation in an AIX system; in general applying a patch with OPatch is very similar in all Oracle 9i and above platforms but it is a good idea to read the documentation of a particular patch.

First we check the availability of perl and java and its version because it is a prerequisite for OPatch; the version needed depends of the OPatch version to use.

myserver:mydb> perl -v

This is perl, v5.8.2 built for aix-thread-multi
(with 3 registered patches, see perl -V for more detail)

Copyright 1987-2003, Larry Wall

Perl may be copied only under the terms of either the Artistic License or the
GNU General Public License, which may be found in the Perl 5 source kit.

Complete documentation for Perl, including FAQ lists, should be found on
this system using `man perl' or `perldoc perl'.  If you have access to the
Internet, point your browser at http://www.perl.com/, the Perl Home Page.

myserver:mydb> java -fullversion
java full version "J2RE 1.4.2 IBM AIX build ca142-20080515 (SR11)"
myserver:mydb> oslevel
5.3.0.0

Next we add OPatch directory to the execution path in case it is not, and list the patch inventory to know about existing patches:

myserver:mydb> ls -la $ORACLE_HOME/OPatch
total 64
drwxr-x---    5 oracle dba             256 Mar 03 2008  .
drwxr-xr-x   57 oracle dba            4096 May 10 11:18 ..
drwxr-x---    2 oracle dba             256 Mar 03 2008  docs
drwxr-x---    2 oracle dba             256 Mar 03 2008  jlib
-rwxr-xr-x    1 oracle dba            6143 Jun 09 2005  opatch
-rw-r--r--    1 oracle dba               0 Mar 03 2008  opatch.ini
-rw-r--r--    1 oracle dba           13703 Jun 09 2005  opatch.pl
drwxr-x---    2 oracle dba            4096 Mar 03 2008  perl_modules
myserver:mydb> export PATH=$PATH:$ORACLE_HOME/OPatch
myserver:mydb> opatch lsinventory

Oracle Interim Patch Installer version 1.0.0.0.52
Copyright (c) 2005 Oracle Corporation. All Rights Reserved..

We recommend you refer to the OPatch documentation under
OPatch/docs for usage reference. We also recommend using
the latest OPatch version. For the latest OPatch version
and other support related issues, please refer to document
293369.1 which is viewable from metalink.oracle.com

Oracle Home = /oracle_9i/app/oracle/product/9.2.0
Location of Oracle Universal Installer components = /oracle_9i/app/oracle/product/9.2.0/oui
Location of OraInstaller.jar  = "/oracle_9i/app/oracle/product/9.2.0/oui/jlib"
Oracle Universal Installer shared library = /oracle_9i/app/oracle/product/9.2.0/oui/lib/aix/liboraInstaller.so
Location of Oracle Inventory Pointer = /etc/oraInst.loc
Location of Oracle Inventory = /oracle_9i/app/oracle/product/9.2.0/inventory
Path to Java = /oracle_9i/app/oracle/product/9.2.0/jre/1.4.2/bin/java
Log file = /oracle_9i/app/oracle/product/9.2.0/.patch_storage//*.log

Creating log file "/oracle_9i/app/oracle/product/9.2.0/.patch_storage/LsInventory__06-29-2010_16-27-35.log"

Result: 


There is no Interim Patch




OPatch succeeded.

It is mandatory almost all the time (or at least a good idea) to stop all instances and database services dependent of the Oracle Home being patched; if you don't want to stop your databases check if the patch you want to apply could be applied without shutting down services.

myserver:mydb> lsnrctl stop LISTENER_mydb

LSNRCTL for IBM/AIX RISC System/6000: Version 9.2.0.7.0 - Production on 29-JUN-2010 18:03:04

Copyright (c) 1991, 2002, Oracle Corporation.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myserver)(IP=10.0.0.1)(PORT=1521))(CONNECT_DATA=(SID=mydb)))
The command completed successfully
myserver:mydb> sqlplus '/ as sysdba'

SQL*Plus: Release 9.2.0.7.0 - Production on Tue Jun 29 18:03:28 2010

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production

Now we apply the patch going to the patch directory and running opatch; OPatch has a lot of options but applying a single patch for just an Oracle Home is the easier of the scenarios:

myserver:mydb> cd /oracle_resp9/p5496862_92070_AIX64-5L/5496862
myserver:mydb> ls -la
total 16
drwx------    4 oracle dba             256 Jun 09 11:03 .
drwx------    3 oracle dba             256 Jun 09 11:03 ..
-rw-------    1 oracle dba            4710 Jun 09 10:52 README.txt
drwx------    4 oracle dba             256 Jun 09 11:03 etc
drwx------    4 oracle dba             256 Jun 09 11:03 files
myserver:mxod0012> opatch apply

Oracle Interim Patch Installer version 1.0.0.0.52
Copyright (c) 2005 Oracle Corporation. All Rights Reserved..

We recommend you refer to the OPatch documentation under
OPatch/docs for usage reference. We also recommend using
the latest OPatch version. For the latest OPatch version
and other support related issues, please refer to document
293369.1 which is viewable from metalink.oracle.com

Oracle Home = /oracle_9i/app/oracle/product/9.2.0
Location of Oracle Universal Installer components = /oracle_9i/app/oracle/product/9.2.0/oui
Location of OraInstaller.jar  = "/oracle_9i/app/oracle/product/9.2.0/oui/jlib"
Oracle Universal Installer shared library = /oracle_9i/app/oracle/product/9.2.0/oui/lib/aix/liboraInstaller.so
Location of Oracle Inventory Pointer = /etc/oraInst.loc
Location of Oracle Inventory = /oracle_9i/app/oracle/product/9.2.0/inventory
Path to Java = /oracle_9i/app/oracle/product/9.2.0/jre/1.4.2/bin/java
Log file = /oracle_9i/app/oracle/product/9.2.0/.patch_storage//*.log

Creating log file "/oracle_9i/app/oracle/product/9.2.0/.patch_storage/5496862/Apply_5496862_06-29-2010_18-11-21.log"

Invoking fuser to check for active processes.

Backing up comps.xml ...

OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only.


Please shut down Oracle instances running out of this ORACLE_HOME
(Oracle Home = /oracle_9i/app/oracle/product/9.2.0)
Is this system ready for updating?
Please respond Y|N > 
Y
Patching...
Printing stderr to output (from /oracle_9i/app/oracle/product/9.2.0/.patch_storage/5496862/make_local.stderr)
ar: Creating an archive file /oracle_9i/app/oracle/product/9.2.0/lib/libclntst9.a.

ar: Creating an archive file /oracle_9i/app/oracle/product/9.2.0/lib32/libclntst9.a.


...


Updating inventory...
Backing up comps.xml ...


OPatch succeeded.

After applying the patch we start up the instances and database services and check the new Oracle version (if applies); some patches require you to execute scripts to change the databases and others don't so don't forget to read patch's documentation.

myserver:mydb> sqlplus '/ as sysdba'

SQL*Plus: Release 9.2.0.7.0 - Production on Tue Jun 29 18:20:06 2010

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  253723320 bytes
Fixed Size                   742072 bytes
Variable Size             184549376 bytes
Database Buffers           67108864 bytes
Redo Buffers                1323008 bytes
Database mounted.
Database opened.
SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION           STARTUP_T STATUS       PAR    THREAD# ARCHIVE LOG_SWITCH_
----------------- --------- ------------ --- ---------- ------- -----------
LOGINS     SHU DATABASE_STATUS   INSTANCE_ROLE      ACTIVE_ST
---------- --- ----------------- ------------------ ---------
              1 mydb
myserver
9.2.0.7.0         29-JUN-10 OPEN         NO           1 STOPPED
ALLOWED    NO  ACTIVE            PRIMARY_INSTANCE   NORMAL


SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production

myserver:mydb> lsnrctl start LISTENER_mydb

LSNRCTL for IBM/AIX RISC System/6000: Version 9.2.0.7.0 - Production on 29-JUN-2010 18:22:35

Copyright (c) 1991, 2002, Oracle Corporation.  All rights reserved.

Starting /oracle_9i/app/oracle/product/9.2.0/bin/tnslsnr: please wait...

TNSLSNR for IBM/AIX RISC System/6000: Version 9.2.0.7.0 - Production
System parameter file is /oracle_9i/app/oracle/product/9.2.0/network/admin/listener.ora
Log messages written to /oracle_9i/app/oracle/product/9.2.0/network/log/listener_mydb.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=hsodbc)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=mydb)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=myserver)(PORT=1521)))

Connecting to (ADDRESS=(PROTOCOL=IPC)(KEY=hsodbc))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_mydb
Version                   TNSLSNR for IBM/AIX RISC System/6000: Version 9.2.0.7.0 - Production
Start Date                29-JUN-2010 18:22:35
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  OFF
SNMP                      OFF
Listener Parameter File   /oracle_9i/app/oracle/product/9.2.0/network/admin/listener.ora
Listener Log File         /oracle_9i/app/oracle/product/9.2.0/network/log/listener_mydb.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=hsodbc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=mydb)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=myserver)(PORT=1521)))
Services Summary...
Service "mydb" has 1 instance(s).
  Instance "mydb", status UNKNOWN, has 1 handler(s) for this service...
Service "hsodbc" has 1 instance(s).
  Instance "hsodbc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

myserver:mydb> tnsping mydb

TNS Ping Utility for IBM/AIX RISC System/6000: Version 9.2.0.7.0 - Production on 29-JUN-2010 18:27:02

Copyright (c) 1997 Oracle Corporation.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL=TCP) (HOST=myserver) (IP=10.0.0.1) (PORT=1521))) (CONNECT_DATA = (SID=mydb)))
OK (90 msec)

Finally, we list again the patch inventory and we should see the patch just applied:

myserver:mydb> opatch lsinventory

Oracle Interim Patch Installer version 1.0.0.0.52
Copyright (c) 2005 Oracle Corporation. All Rights Reserved..

We recommend you refer to the OPatch documentation under
OPatch/docs for usage reference. We also recommend using
the latest OPatch version. For the latest OPatch version
and other support related issues, please refer to document
293369.1 which is viewable from metalink.oracle.com

Oracle Home = /oracle_9i/app/oracle/product/9.2.0
Location of Oracle Universal Installer components = /oracle_9i/app/oracle/product/9.2.0/oui
Location of OraInstaller.jar  = "/oracle_9i/app/oracle/product/9.2.0/oui/jlib"
Oracle Universal Installer shared library = /oracle_9i/app/oracle/product/9.2.0/oui/lib/aix/liboraInstaller.so
Location of Oracle Inventory Pointer = /etc/oraInst.loc
Location of Oracle Inventory = /oracle_9i/app/oracle/product/9.2.0/inventory
Path to Java = /oracle_9i/app/oracle/product/9.2.0/jre/1.4.2/bin/java
Log file = /oracle_9i/app/oracle/product/9.2.0/.patch_storage//*.log

Creating log file "/oracle_9i/app/oracle/product/9.2.0/.patch_storage/LsInventory__06-29-2010_18-26-05.log"

Result: 


  Installed Patch List:
  =====================
  1) Patch 5496862 applied on Tue Jun 29 17:15:41 GMT-06:00 2010
      [ Base Bug(s): 5496862  ]




OPatch succeeded.

Getting log switching info

The amount of log switches that does an Oracle database is an indicator about the amount of data modified in the database; as long as select operations does not generate redo, log switching is closely related to data manipulation. Therefore, you can get the number of log switches and the size of each log file and have a rough idea about how much data is being modified in your database in a period of time:

SQL> select MAX(i.INSTANCE_NAME) as INSTANCE, MIN(h.FIRST_TIME) as FIRST_TIME,
SQL> MIN(h.SEQUENCE#) as MIN_SEQUENCE#, MAX(h.SEQUENCE#) as MAX_SEQUENCE#,
SQL> MAX(l.BYTES)/1048576 as LOG_SIZE_MB,
SQL> MAX(h.SEQUENCE#)-MIN(h.SEQUENCE#)+1)*MAX(l.BYTES)/1073741824 as DATA_GB
SQL> from V$LOG_HISTORY h, V$LOG l, V$INSTANCE i where h.FIRST_TIME > SYSDATE-7;

INSTANCE         FIRST_TIME  MIN_SEQUENCE# MAX_SEQUENCE# LOG_SIZE_MB    DATA_GB                                                             
---------------- ----------- ------------- ------------- ----------- ----------                                                             
mydb             12-jun-2010           178           182         256       1.25                                                             

Freeing space in SYSAUX

If your SYSAUX tablespace grows a lot, chances are that there is a lot of statistics data in that tablespace. You might try to free space with this sentences:

SQL> exec DBMS_STATS.PURGE_STATS(SYSDATE-30);

PL/SQL procedure successfully completed.

SQL> alter table WRI$_OPTSTAT_HISTGRM_HISTORY move tablespace sysaux;

Table altered.

SQL> alter index I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST rebuild online;

Index altered.

SQL> alter index I_WRI$_OPTSTAT_H_ST rebuild online;

Index altered.

SQL> alter table WRI$_OPTSTAT_HISTHEAD_HISTORY move tablespace sysaux;

Table altered.

SQL> alter index I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST rebuild online;

Index altered.

SQL> alter index I_WRI$_OPTSTAT_HH_ST rebuild online;

Index altered.

SQL> ALTER TABLE WRH$_ACTIVE_SESSION_HISTORY SHRINK SPACE CASCADE;

Table altered.

More information:

Purging statistics from the SYSAUX tablespace