Setting Transparent Application Failover

According to Oracle:

"Transparent Application Failover (TAF) is a feature of the Java Database Connectivity (JDBC) Oracle Call Interface (OCI) driver. It enables the application to automatically reconnect to a database, if the database instance to which the connection is made fails."

Therefore, if you have some instances running with Dataguard you can set up TAF in those instances and have automatic failover, which involves creating a service controlled by a database trigger that will be up just in the primary instance, as you can see in this transcript:

myserver >sqlplus /nolog

SQL*Plus: Release 11.1.0.7.0 - Production on Tue Feb 1 15:19:59 2011

Copyright (c) 1982, 2008, Oracle.  All rights reserved.

SQL> connect / as sysdba

Connected.

SQL> EXEC DBMS_SERVICE.CREATE_SERVICE (service_name => 'taf_mydb',
network_name => 'taf_mydb', aq_ha_notifications => TRUE,
failover_method => 'BASIC', failover_type => 'SESSION',
failover_retries => 180, failover_delay => 5);

PL/SQL procedure successfully completed.

SQL> CREATE OR REPLACE TRIGGER SYS.MANAGE_TAF_SERVICE
AFTER STARTUP ON DATABASE
DECLARE
ROLE VARCHAR(30) := NULL;
BEGIN
SELECT TRIM(DATABASE_ROLE) INTO ROLE FROM V$DATABASE;
IF ROLE = 'PRIMARY' THEN
DBMS_SERVICE.START_SERVICE('taf_mydb');
ELSE
DBMS_SERVICE.STOP_SERVICE('taf_mydb');
END IF;
END;
/

Trigger created.

SQL> alter system archive log current;

System altered.

SQL> exit;

myserver >lsnrctl status LISTENER_mydb

LSNRCTL for Linux: Version 11.1.0.7.0 - Production on 01-FEB-2011 15:29:03

Copyright (c) 1991, 2008, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.50)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_mydb
Version                   TNSLSNR for Linux: Version 11.1.0.7.0 - Production
Start Date                31-JAN-2011 15:47:49
Uptime                    0 days 23 hr. 41 min. 14 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle_11g/product/11.1.0/network/admin/listener.ora
Listener Log File         /oracle_11g/product/diag/tnslsnr/myserver/listener_mydb/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.50)(PORT=1521)))
Services Summary...
Service "taf_mydb" has 1 instance(s).
  Instance "mydb", status READY, has 1 handler(s) for this service...
Service "mydb" has 2 instance(s).
  Instance "mydb", status UNKNOWN, has 1 handler(s) for this service...
  Instance "mydb", status READY, has 2 handler(s) for this service...
Service "mydb_XPT" has 1 instance(s).
  Instance "mydb", status READY, has 1 handler(s) for this service...
The command completed successfully

You have to do the above configuration just in your primary database, and this configuration will be propagated to the standby database with the alter system archive log current sentence.

If you read carefully and understood the first paragraph of this post, you might realized that TAF is in fact implemented in the client side; you configured the instances to start a special service that will be active just in the primary instance at any time, but the client is whom decides to which service to connect. Therefore, you have to configure your tnsnames like this:

MYDATABASE =
  (DESCRIPTION =
    (LOAD_BALANCE = OFF)
    (FAILOVER = ON)
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.50) (PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.60) (PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = taf_mydb)
      (FAILOVER_MODE =
        (TYPE = SESSION)
        (METHOD = BASIC)
        (RETRIES = 180)
        (DELAY = 5)
      )
    )
  )

In this example, you will connect to MYDATABASE that is comprised by the primary instance (192.168.1.50) and the standby instance (192.168.1.60). And if you are going to use Java, don't forget that TAF does not work with the Thin drivers, you have to use the OCI drivers instead. Remembering this might save you a lot of problems with your fellow WAS administrators.

More information:

Failover Connections for Data Guard Error with ORA-1033 [ID 461874.1]
Transparent Application Failover

Checking RMAN operation's status

If you backup your Oracle database with RMAN and you want to check the execution of RMAN operations, you would use the V$RMAN_STATUS view as follows:

SQL> select COMMAND_ID, OPERATION, STATUS, END_TIME from V$RMAN_STATUS WHERE END_TIME > sysdate - 5;

COMMAND_ID              OPERATION                         STATUS        END_TIME
----------------------- --------------------------------- ------------- ---------
2010-11-26T23:05:29     RMAN                              COMPLETED     26-NOV-10
backup_level_1          BACKUP                            COMPLETED     26-NOV-10
backup_level_1          CONTROL FILE AND SPFILE AUTOBACK  COMPLETED     26-NOV-10
backup_level_1          BACKUP                            COMPLETED     26-NOV-10
backup_level_1          CONTROL FILE AND SPFILE AUTOBACK  COMPLETED     26-NOV-10
2010-11-27T22:56:51     RMAN                              COMPLETED     27-NOV-10
backup_level_1          BACKUP                            COMPLETED     27-NOV-10
backup_level_1          CONTROL FILE AND SPFILE AUTOBACK  COMPLETED     27-NOV-10
backup_level_1          BACKUP                            COMPLETED     27-NOV-10
backup_level_1          CONTROL FILE AND SPFILE AUTOBACK  COMPLETED     27-NOV-10
2010-11-28T22:55:34     RMAN                              COMPLETED     29-NOV-10
backup_level_0          DELETE                            COMPLETED     28-NOV-10
backup_level_0          BACKUP                            COMPLETED     29-NOV-10
backup_level_0          CONTROL FILE AND SPFILE AUTOBACK  COMPLETED     29-NOV-10
backup_level_0          BACKUP                            COMPLETED     29-NOV-10
backup_level_0          CONTROL FILE AND SPFILE AUTOBACK  COMPLETED     29-NOV-10
backup_level_0          DELETE                            COMPLETED     29-NOV-10
backup_level_0          DELETE                            COMPLETED     29-NOV-10
2010-11-30T22:49:31     RMAN                              COMPLETED     30-NOV-10
backup_level_1          BACKUP                            COMPLETED     30-NOV-10
backup_level_1          CONTROL FILE AND SPFILE AUTOBACK  COMPLETED     30-NOV-10
backup_level_1          BACKUP                            COMPLETED     30-NOV-10
backup_level_1          CONTROL FILE AND SPFILE AUTOBACK  COMPLETED     30-NOV-10

23 rows selected.

Checking application server's state

If you work as a Progress DBA (no, really?) and you need to check the status of an application server, you can do that in this way:

MYSERVER:/home/progress:> asbman -i myasb -q
PROGRESS Version 9.1E as of Tue Oct 12 14:40:19 EDT 2004


Connecting to Progress AdminServer using rmi://localhost:20931/Chimera (8280)
Searching for myasb (8288)
Connecting to myasb  (8276)

Broker Name                    : myasb
Operating Mode                 : State-reset
Broker Status                  :  ACTIVE
Broker Port                    : 3058
Broker PID                     : 25482
Active Servers                 : 15
Busy Servers                   : 15
Locked Servers                 : 0
Available Servers              : 0
Active Clients (now, peak)     : (0, 3)
Client Queue Depth (cur, max)  : (0, 3)
Total Requests                 : 186
Rq Wait (max, avg)             : (18006 ms, 9276 ms)
Rq Duration (max, avg)         : (18007 ms, 9277 ms)

PID   State     Port  nRq    nRcvd  nSent  Started          Last Change
25577 CONNECTED 03502 000003 000003 000003 Jan 26, 2011 13:14 Jan 26, 2011 13:14
25714 CONNECTED 03503 000003 000003 000003 Jan 26, 2011 13:14 Jan 26, 2011 13:14
25718 CONNECTED 03504 000015 000015 000015 Jan 26, 2011 13:14 Jan 26, 2011 14:24
25722 CONNECTED 03505 000013 000013 000013 Jan 26, 2011 13:14 Jan 26, 2011 14:24
27113 CONNECTED 03506 000014 000014 000014 Jan 26, 2011 13:16 Jan 26, 2011 13:21
29966 CONNECTED 03507 000002 000002 000002 Jan 26, 2011 13:21 Jan 26, 2011 13:21
30012 CONNECTED 03508 000006 000006 000006 Jan 26, 2011 13:21 Jan 26, 2011 13:22
31236 CONNECTED 03509 000003 000003 000003 Jan 26, 2011 13:22 Jan 26, 2011 13:22
31291 CONNECTED 03510 000010 000010 000010 Jan 26, 2011 13:22 Jan 26, 2011 13:24
31295 CONNECTED 03511 000005 000005 000005 Jan 26, 2011 13:22 Jan 26, 2011 13:24
31299 CONNECTED 03512 000004 000004 000004 Jan 26, 2011 13:22 Jan 26, 2011 13:24
31646 CONNECTED 03513 000002 000002 000002 Jan 26, 2011 13:24 Jan 26, 2011 13:24
31650 CONNECTED 03514 000008 000008 000008 Jan 26, 2011 13:24 Jan 26, 2011 13:26
00439 CONNECTED 03515 000002 000002 000002 Jan 26, 2011 13:26 Jan 26, 2011 13:26
00443 CONNECTED 03516 000007 000007 000007 Jan 26, 2011 13:26 Jan 26, 2011 13:31   

As you can see, this apps has 15 active servers and a long request wait, and because the limit of connections in the license of this application server is indeed 15 and the connections are not being closed, there is no way a new connection can be created. In this case, you can kill some connections or stop and start the application server to get rid of all connections at the same time. With asbman -i myasb -k you stop the myasb application server and with asbman -i myasb -x you start it.

After restarting this apps, you can see that the number of active servers is just one and the request wait is zero:

MYSERVER:/home/progress:> asbman -i myasb -q
PROGRESS Version 9.1E as of Tue Oct 12 14:40:19 EDT 2004


Connecting to Progress AdminServer using rmi://localhost:20931/Chimera (8280)
Searching for myasb (8288)
Connecting to myasb  (8276)

Broker Name                    : myasb
Operating Mode                 : State-reset
Broker Status                  :  ACTIVE
Broker Port                    : 3058
Broker PID                     : 7034
Active Servers                 : 1
Busy Servers                   : 0
Locked Servers                 : 0
Available Servers              : 1
Active Clients (now, peak)     : (0, 0)
Client Queue Depth (cur, max)  : (0, 0)
Total Requests                 : 0
Rq Wait (max, avg)             : (0 ms, 0 ms)
Rq Duration (max, avg)         : (0 ms, 0 ms)

PID   State     Port  nRq    nRcvd  nSent  Started          Last Change
07082 AVAILABLE 03502 000000 000000 000000 Jan 26, 2011 15:19 Jan 26, 2011 15:19

You can check the license for your Progress installation with showcfg command.

Checking processes memory usage

Sometimes your server would have a heavy load because there is one or many processes consuming a lot of memory, and when there is no more physical memory (RAM) available the system begins using virtual memory (swap), and as long as RAM is thousands times faster than normal hard disks you'll notice for sure when your system begins to swap memory. Therefore, if you're a sysadmin or an Oracle DBA running instances in your own AIX server that doesn't run anything else, it would be good to pinpoint the top processes consuming the system's memory in an easy and quick way like this:

myserver> ps auwx|sort -k 5b,5nr|more
USER         PID %CPU %MEM   SZ  RSS    TTY STAT    STIME  TIME COMMAND
oracle   5669038  0.1  2.0 1536176 743136      - A      Jan 21 18:43 oraclemyoradb (LOCAL=NO) 
oracle   4755538  0.0  2.0 1472752 719680      - A      Jan 15 16:44 oraclemyoradb (LOCAL=NO) 
oracle   5922916  0.0  1.0 1463404 518768      - A      Jan 21  9:05 oraclemyoradb (LOCAL=NO) 
oracle   4575476  0.0  2.0 1419100 692160      - A    16:05:55  2:25 oraclemyoradb (LOCAL=NO) 
oracle   2752638  0.0  2.0 1405344 692960      - A      Jan 20 11:35 oraclemyoradb (LOCAL=NO) 
oracle   6070354  0.1  2.0 1338124 736024      - A    16:03:06  5:23 oraclemyoradb (LOCAL=NO) 
oracle   3518588  0.0  2.0 1273420 704220      - A      Jan 20 10:56 oraclemyoradb (LOCAL=NO) 
oracle   5390404  0.1  2.0 1272972 814148      - A      Jan 22 13:47 oraclemyoradb (LOCAL=NO) 
oracle   2371620  0.0  2.0 1259332 685920      - A      Jan 20  7:40 oraclemyoradb (LOCAL=NO) 
oracle   4784328  0.0  2.0 1259024 774984      - A      Jan 20 11:02 oraclemyoradb (LOCAL=NO) 
oracle   5877918  0.1  2.0 1200360 730492      - A      Jan 24  5:12 oraclemyoradb (LOCAL=NO) 
oracle   4374602  0.0  2.0 1193800 771632      - A      Jan 21  9:35 oraclemyoradb (LOCAL=NO) 
oracle   5804038  0.0  2.0 1178616 681944      - A      Jan 20 19:32 oraclemyoradb (LOCAL=NO) 
oracle   4931756  0.3  2.0 1156880 786928      - A    10:58:49  1:45 oraclemyoradb (LOCAL=NO) 
oracle   3100862  0.0  2.0 1146228 664368      - A      Jan 18  6:54 oraclemyoradb (LOCAL=NO) 
oracle   1880168  0.0  2.0 1043492 709820      - A      Jan 20 16:53 oraclemyoradb (LOCAL=NO) 
oracle   3858452  0.0  2.0 1035528 723960      - A      Jan 20 12:24 oraclemyoradb (LOCAL=NO) 
oracle   4050954  0.0  2.0 1027828 678236      - A      Jan 22 10:00 oraclemyoradb (LOCAL=NO) 
oracle   3510290  0.0  2.0 1010724 610948      - A      Jan 14 11:22 oraclemyoradb (LOCAL=NO) 

As you can see, there is a lot of oracle processes related to myoradb database that are consuming too much memory and chocking the system.

How to get references to a table

In Oracle, if you need to know which tables have a DIRECT relationship with a given table, not like a table that has a foreign key of a second table that in turn has a foreign key pointing to a third table, then you can use this SQL sentence:

SQL> select owner, constraint_name, constraint_type, table_name
from all_constraints where constraint_type='R' and r_constraint_name in
(select constraint_name from all_constraints where constraint_type in ('P','U')
and owner='MYUSER' and table_name='MYTABLE');

OWNER                CONSTRAINT_NAME      C TABLE_NAME
-------------------- -------------------- - ---------------------
ANOWNER              SYS_C0018146         R ATABLE
ANOWNER              SYS_C0018144         R OTHER_TABLE
ANOWNER              SYS_C0018145         R A_THIRD_TABLE
ANOWNER              SYS_C0018148         R RELATED_TABLE

Checking AIX cache

The cache area is a memory area where the operating system places blocks of files used often, therefore reducing costly I/O to slower devices like hard disks. It is a good concept and very useful for boosting performance of file servers (NFS, samba), but is not so good for database software like Oracle because an instance manages its own cache area.

Therefore, if you are an Oracle administrator of databases in AIX servers it's a good practice to be aware of memory configuration and usage, and maybe help a bit your fellow sysadmin just in case. The important parameters to check are the minimum and maximum percentage of RAM for file caching purposes (minperm% and maxperm%):

root:myserver> vmstat -v
              2621440 memory pages
              2525761 lruable pages
               180000 free pages
                    4 memory pools
               647419 pinned pages
                 80.0 maxpin percentage
                 20.0 minperm percentage
                 80.0 maxperm percentage
                 24.8 numperm percentage
               627654 file pages
                  0.0 compressed percentage
                    0 compressed pages
                 24.8 numclient percentage
                 25.0 maxclient percentage
               627654 client pages
                    0 remote pageouts scheduled
                58246 pending disk I/Os blocked with no pbuf
             10544297 paging space I/Os blocked with no psbuf
                 2228 filesystem I/Os blocked with no fsbuf
                  805 client filesystem I/Os blocked with no fsbuf
              2215609 external pager filesystem I/Os blocked with no fsbuf
                    0 Virtualized Partition Memory Page Faults
                 0.00 Time resolving virtualized partition memory page faults

As you can see in this example, there is a lot of minimum memory allotted for file caching (20%) and wasted because Oracle caching is better for Oracle databases than operating system caching, but you can't assign that 20% RAM space to Oracle unless you change the virtual memory AIX parameters.

Moreover, the maximum memory reserved for file caching is limited by the maxclient percentage (25%) so in this system you can use up to 25% of RAM for file caching of non-JFS filesystem files and the other 55% for caching files of JFS filesystems. You can check JFS memory pages usage with this command:

root:myserver> svmon -G
               size       inuse        free         pin     virtual
memory      2621440     2442500      178940      647452     2186997
pg space    5373952      925157

               work        pers        clnt       other
pin          562525           0           0       84927
in use      1734676           0      707824

PageSize   PoolSize      inuse       pgsp        pin    virtual
s   4 KB          -    2326020     925157     571068    2070517
m  64 KB          -       7280          0       4774       7280

You might notice that there are 0 pages used for JFS file caching (pers), and that might be because there are no JFS file systems in this server:

root:myserver> mount
  node       mounted        mounted over    vfs       date        options      
-------- ---------------  ---------------  ------ ------------ --------------- 
         /dev/hd1         /                jfs2   Jun 05 19:09 rw,log=/dev/hd8 
         /dev/hd2         /usr             jfs2   Jun 05 19:09 rw,log=/dev/hd8 
         /dev/hd5         /var             jfs2   Jun 05 19:10 rw,log=/dev/hd8 
         /dev/hd3         /tmp             jfs2   Jun 05 19:10 rw,log=/dev/hd8 
         /dev/hd4         /home            jfs2   Jun 05 19:11 rw,log=/dev/hd8 
         /proc            /proc            procfs Jun 05 19:11 rw              
         /dev/hd6         /opt             jfs2   Jun 05 19:11 rw,log=/dev/hd8 
         /dev/fslv00      /myfs            jfs2   Jun 05 19:11 rw,log=/dev/loglv00

IBM recommends setting the parameters as follows:

maxperm = 90%
maxclient = 90%
minperm = 3%

More information:

Tuning the AIX file caches
Overview of AIX page replacement

Checking Oracle advisors

The Oracle advisors (since 10g version) are a wonderful way to check the instance performance and tune your Oracle database, and you can see this information in an easy and intuitive way with Enterprise Manager, but sometimes you don't have Enterprise Manager running or maybe you would like to get this information quickly with a script. If this is the case, you can use this SQL sentences to check advisors' information:

SQL> set linesize 150
SQL> column pga_target_mb  format 999,999,990 
SQL> column estd_extra_MB  format 999,999,990 
SQL> column name           format A30
SQL> column value          format A30

SQL> select pga_target_for_estimate/1048576 pga_target_mb, pga_target_factor factor, 
estd_extra_bytes_rw/1048576 estd_extra_MB, estd_pga_cache_hit_percentage pga_hit, 
estd_overalloc_count over_alloc from v$pga_target_advice
where pga_target_factor between .75 and 3 order by pga_target_factor;

PGA_TARGET_MB     FACTOR ESTD_EXTRA_MB    PGA_HIT OVER_ALLOC
------------- ---------- ------------- ---------- ----------
          120        .75       490,015         44        901
          160          1       275,813         58        593
          192        1.2       254,315         60        565
          224        1.4       180,064         68        130
          256        1.6       154,973         71        130
          288        1.8       154,460         71         68
          320          2       153,986         71         46
          480          3       151,257         72          0

8 rows selected.

SQL> SELECT SGA_SIZE, SGA_SIZE_FACTOR, ESTD_DB_TIME, ESTD_DB_TIME_FACTOR, ESTD_PHYSICAL_READS
from v$sga_target_advice where sga_size_factor between .75 and 3 order by SGA_SIZE_FACTOR;

  SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS
---------- --------------- ------------ ------------------- -------------------
       192             .75       698484              1.0397           297575225
       256               1       671813                   1           287818188
       320            1.25       660325               .9829           283644824
       384             1.5       637752               .9493           275384442
       448            1.75       637752               .9493           275384442
       512               2       637752               .9493           275384442

6 rows selected.

SQL> select NAME, VALUE from v$parameter 
where NAME in ('shared_pool_size','large_pool_size','java_pool_size');

NAME                           VALUE
------------------------------ ------------------------------
shared_pool_size               0
large_pool_size                0
java_pool_size                 0

SQL> select pool, name, bytes FROM V$SGASTAT where name = 'free memory';

POOL         NAME                                BYTES
------------ ------------------------------ ----------
shared pool  free memory                      15515352
large pool   free memory                       3118304
java pool    free memory                       4194304

But be careful! The Oracle advisors are not included for free and you have to have a Database Diagnostic Pack license to use this data, but even if you issue just one select on this views it is recorded for ever and you would be liable for using a product without license. And as a side note, if you think that you can audit product usage in your Oracle database for compliance in an easy and reliable way, think it twice.

More information:
Oracle licensing compliance is important
Oracle license audit

Get DBlinks information

In Oracle you can connect to and query other databases with DBlinks, and that's almost like having the data in your local database ... Until you have problems like latency or shutting down instances needed to work with your local instance; if you didn't install or design your local database chances are you have DBlinks and you don't know.

Therefore, if you want to know information about DBlinks in your database, you can issue this SQL sentences:

SQL> column DB_LINK format a30
SQL> column name format a30
SQL> column value format a50

SQL> select name, value from v$parameter where name like '%dblink%';

NAME                           VALUE                                                                                       
------------------------------ --------------------------------                                          
dblink_encrypt_login           FALSE                                                                                       

SQL> select OWNER, DB_LINK, HOST, CREATED from dba_db_links;

OWNER                DB_LINK              HOST            CREATED                       
--------------- -------------------- -------------------- ---------                     
PUBLIC          DBLINK1              MYHOST01             25-MAY-08                     
PUBLIC          DBLINK2              MYHOST02             25-MAY-08                     
PUBLIC          DBLINK3              MYHOST03             25-MAY-08                     
PUBLIC          DBLINK4              MYHOST04             25-MAY-08                     

Checking errors in procedures

If you have a procedure or package that didn't compile properly, you can check the errors and related lines of code with this SQL statement:

SQL> column TEXT format a30
SQL> select e.NAME, e.TYPE, e.TEXT as ERROR, s.TEXT as CODE
from dba_errors e join dba_source s on 
(e.OWNER=s.OWNER and e.NAME=s.NAME and e.TYPE=s.TYPE and e.LINE=s.LINE)
where e.OWNER in ('MYUSER') order by e.OWNER, e.NAME, e.LINE;

NAME            TYPE         ERROR
--------------- ------------ ------------------------------------------------
CODE
----------------------------------------------------
MYPACKAGE       PACKAGE BODY PL/SQL: ORA-00942: table or view does not exist
INSERT INTO mytable@my.dblink

MYPACKAGE       PACKAGE BODY PL/SQL: SQL Statement ignored
INSERT INTO mmytable@my.dblink

MYPACKAGE       PACKAGE BODY PL/SQL: ORA-00942: table or view does not exist
      SELECT count("myfield") INTO afield

MYPACKAGE       PACKAGE BODY PL/SQL: SQL Statement ignored
      SELECT count("myfield") INTO afield

Dropping a database in Oracle

Is very easy to drop (delete) an Oracle database, as you can see in this example:

myserver:mydb> sqlplus '/ as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Sep 24 08:50:22 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup mount exclusive;
ORACLE instance started.

Total System Global Area  268435456 bytes
Fixed Size                  2083368 bytes
Variable Size             163579352 bytes
Database Buffers          100663296 bytes
Redo Buffers                2109440 bytes
Database mounted.

SQL> select instance_name, host_name from v$instance;

INSTANCE_NAME    HOST_NAME
---------------- ----------------
mydb             myserver

SQL> alter system enable restricted session;

System altered.

SQL> drop database;

Database dropped.

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options
SQL> exit

In a related note, the sentence DROP DATABASE does not exist in Oracle 9i, you have to shutdown the database and erase all the database files with operating system commands:

Drop database in Oracle 9i

Memory overhead

Talking about computers the more the better is usual, but this is not true all the time. By example, look at the amount of memory and load in bigservr; this server has 64 gigabytes of memory but is used just 3 gigabytes, and even if there are no running processes and the CPU usage is almost zero the load average is somewhat high. In comparison, smallsrv is busier and even having used swap memory the load average is lower than in bigservr.

The management of hardware by the Linux kernel requires resources, and in order to manage a lot of memory the kernel requires ... a lot of memory structures. Therefore, if you aren't going to use a lot of memory it would be better (and cheaper) to install just the memory you need.

myuser@bigservr> top

top - 08:50:32 up 6 days, 13:00,  4 users,  load average: 0.71, 0.74, 0.66
Tasks: 272 total,   1 running, 271 sleeping,   0 stopped,   0 zombie
Cpu(s):  0.8%us,  0.1%sy,  0.0%ni, 99.1%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:  65791000k total,  3227812k used, 62563188k free,   309716k buffers
Swap:  5406712k total,        0k used,  5406712k free,  2649252k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                                                                      
10768 root      15   0 16420 2456 2124 S  0.3  0.0   0:39.00 cmaeventd                                                                                    
22241 myuser    15   0  2336 1136  800 R  0.3  0.0   0:01.14 top                                                                                          
    1 root      15   0  2072  616  532 S  0.0  0.0   0:08.29 init                                                                                         
    2 root      RT  -5     0    0    0 S  0.0  0.0   0:01.17 migration/0                                                                                  
    3 root      39  19     0    0    0 S  0.0  0.0   0:00.00 ksoftirqd/0                                                                                  
    4 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/0                                                                                   
    5 root      RT  -5     0    0    0 S  0.0  0.0   0:01.44 migration/1                                                                                  
    6 root      34  19     0    0    0 S  0.0  0.0   0:00.07 ksoftirqd/1                                                                                  
    7 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/1                                                                                   
    8 root      RT  -5     0    0    0 S  0.0  0.0   0:01.01 migration/2                                                                                  
    9 root      34  19     0    0    0 S  0.0  0.0   0:00.02 ksoftirqd/2                                                                                  
   10 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/2                                                                                   
   11 root      RT  -5     0    0    0 S  0.0  0.0   0:01.72 migration/3                                                                                  

root@smallsrv:~# top

top - 08:51:20 up 29 min,  6 users,  load average: 0.16, 0.21, 0.46
Tasks: 184 total,   2 running, 182 sleeping,   0 stopped,   0 zombie
Cpu(s):  7.0%us, 12.3%sy,  0.0%ni, 79.3%id,  1.3%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:   1016864k total,   987644k used,    29220k free,    44384k buffers
Swap:  2980016k total,   204780k used,  2775236k free,   604592k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND           
 2599 myuser    10 -10  271m 186m 177m S  9.6 18.8   2:55.94 vmware-vmx        
 2377 myuser    20   0  388m 103m  19m S  4.3 10.4   0:50.69 firefox-bin       
 1030 root      20   0  105m  20m 9428 S  3.0  2.1   1:00.57 Xorg              
 2435 myuser    20   0 73624  13m 9.9m S  1.3  1.4   0:15.40 plugin-containe   
 3479 root      20   0  2472 1196  880 R  0.7  0.1   0:00.06 top               
   16 root      15  -5     0    0    0 S  0.3  0.0   0:00.39 ata/0             
 2028 myuser    20   0  110m  10m 5832 S  0.3  1.1   0:12.17 compiz.real       
 2206 myuser    20   0  113m  11m 8324 S  0.3  1.2   0:04.71 gnome-terminal    
 2938 root       0 -20     0    0    0 R  0.3  0.0   0:08.41 vmware-rtc        
    1 root      20   0  2532 1420 1096 S  0.0  0.1   0:00.98 init              
    2 root      15  -5     0    0    0 S  0.0  0.0   0:00.00 kthreadd          
    3 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 migration/0       
    4 root      15  -5     0    0    0 S  0.0  0.0   0:00.09 ksoftirqd/0       
    5 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/0        
    6 root      15  -5     0    0    0 S  0.0  0.0   0:00.02 events/0          
    7 root      15  -5     0    0    0 S  0.0  0.0   0:00.00 cpuset            
    8 root      15  -5     0    0    0 S  0.0  0.0   0:00.00 khelper           

Oracle, virtual machines and weird time

If you have used virtual machines a lot, you might know that sometimes there are issues with the time of the virtual machines and that is a good practice to keep the time in sync (with services such like NTP). The problems I saw with virtual machine's time where related mostly with heavy load and hardware not optimal for that load, but there are more problems related to virtual machines out there.

Some day I had a weird problem with an Oracle 10g database: suddenly there was a lot of AWR data and the tablespace was growing fast. The snap interval was one hour and the retention period seven days, but upon further inspection I noticed that snapshots where being taken every minute:

SQL> select snap_interval, retention from dba_hist_wr_control;

SNAP_INTERVAL                            RETENTION
---------------------------------------- -------------------
+00000 01:00:00.0                        +00007 00:00:00.0

SQL> select SNAP_ID, STARTUP_TIME, BEGIN_INTERVAL_TIME, END_INTERVAL_TIME from dba_hist_snapshot order by SNAP_ID;

   SNAP_ID STARTUP_TIME                 BEGIN_INTERVAL_TIME         END_INTERVAL_TIME
-------- ---------------------------- --------------------------- --------------------------
    8123 04-JUL-10 07.34.43.000 PM    19-JUL-10 10.07.28.091 AM   19-JUL-10 10.08.35.353 AM
    8124 04-JUL-10 07.34.43.000 PM    19-JUL-10 10.08.35.353 AM   19-JUL-10 10.09.46.060 AM
    8125 04-JUL-10 07.34.43.000 PM    19-JUL-10 10.09.46.060 AM   19-JUL-10 10.10.56.047 AM
    8126 04-JUL-10 07.34.43.000 PM    19-JUL-10 10.10.56.047 AM   19-JUL-10 10.12.04.798 AM
    8127 04-JUL-10 07.34.43.000 PM    19-JUL-10 10.12.04.798 AM   19-JUL-10 10.13.13.762 AM
    8128 04-JUL-10 07.34.43.000 PM    19-JUL-10 10.13.13.762 AM   19-JUL-10 10.14.24.432 AM
...

I checked a lot of things in the Oracle database but after finding nothing I guessed there was a problem in the operating system. The time was fine but the top command refreshed the information very fast, therefore I tried with top -d 300 and this time the information was refreshed every three seconds ... Even if it was meant to be refreshed every five minutes with top -d 300.

After finding this I notified the sysadmins about this situation and some time later the problem was fixed, but the sysadmins never told me what the problem was; I think that the problem was related to a VMware bug:

Weird timing voodoo. Linux top command very fast

Problems with host name

If you get a lot of dump files generated and this error in your alert log:

Warning: keltnfy call to ldmInit failed with error 46

Check that your host name can be resolved and the IP address is correct; in this example in Linux you can see that the name is missing from /etc/hosts:

MYHOST:mydb> uname -n
MYHOST
MYHOST:mydb> cat /etc/hostname
MYHOST
MYHOST:mydb> cat /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1       localhost.localdomain localhost
::1             localhost6.localdomain6 localhost6
MYHOST:mydb> ping MYHOST
ping: unknown host MYHOST
MYHOST:mydb> sqlplus /

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Jul 13 10:01:38 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing
options

SQL> set linesize 150
SQL> select INSTANCE_NAME, HOST_NAME, STATUS from v$instance;

INSTANCE_NAME    HOST_NAME  STATUS
---------------- ---------- ---------------
mydb             MYHOST     OPEN

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release
10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing
options

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