TNS-12535: TNS:operation timed out

TNS-12535 is message you can find in the sqlnet.log file of a database server, such as this:

***********************************************************************
Fatal NI connect error 12170.

  VERSION INFORMATION:
        TNS for Solaris: Version 10.2.0.1.0 – Production
        Oracle Bequeath NT Protocol Adapter for Solaris: Version 10.2.0.1.0 – Production
        TCP/IP NT Protocol Adapter for Solaris: Version 10.2.0.1.0 – Production
  Time: 09-MAR-2007 18:44:34
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12535
    TNS-12535: TNS:operation timed out
    ns secondary err code: 12606
    nt main err code: 0
    nt secondary err code: 0
    nt OS err code: 0
  Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.221.70.83)(PORT=4155))
***********************************************************************

I have seen two flavors of TNS-12535.  One flavor, like the one above, actually will get logged in the alert log as this:

WARNING: inbound connection timed out (ORA-3136)

Oracle documentation says this is related to a user making a request for a connection, but did not pass authentication information within the amount of time set.

The other flavor of TNS-12535 looks like this:

***********************************************************************
Fatal NI connect error 12170.

  VERSION INFORMATION:
        TNS for Solaris: Version 10.2.0.1.0 – Production
        Oracle Bequeath NT Protocol Adapter for Solaris: Version 10.2.0.1.0 – Production
        TCP/IP NT Protocol Adapter for Solaris: Version 10.2.0.1.0 – Production
  Time: 13-MAR-2007 18:42:58
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12535
    TNS-12535: TNS:operation timed out
    ns secondary err code: 12560
    nt main err code: 505
    TNS-00505: Operation timed out
    nt secondary err code: 145
    nt OS err code: 0
  Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.221.70.83)(PORT=4032))
***********************************************************************

Notice the “ns secondary err code” and “nt secondary err code” are different from the first flavor.  This flavor does NOT get logged in the alert log, but is indicative of a client connection being dropped (as per this blog: http://hpdba.wordpress.com/tag/keep-alive/).  This can be the result of firewall dropping connections per the firewall policy of idle sessions.  The workaround for this can be done as follows:

“For Oracle databases, SQLNET.EXPIRE_TIME can be used.  Although SQLNET.EXPIRE_TIME was intended as a dead connection detection (DCD) method, a side effect of sending probe packets between the database server and client/application server is that the connection remains active.  Set the SQLNET.EXPIRE_TIME to less than the firewall timeout and the problem is solved.”

IPMP Setup

1.)    Before starting this procedure, make sure you have obtained the proper number of IP address for the system.  You will need one IP address for every active NIC on the system, plus the number of virtual IP address that will be hosted.  You will need at least one virtual IP address as the “public” IP.  In other words, this “public” IP will be the IP that the server hostname will resolve to.  The IP addresses for the each NIC will be “deprecated” and used later as the “test IP addresses” used by IPMP.  For example, a system with two active NIC will require three IP address – one for each NIC and one for the virtual IP address of the server’s hostname.  For every IP address you obtained, it’s a good idea to make a Domain entry for that IP with a correlating hostname.  A good stand is to use the server’s hostname a basis.  Here’s an example for a server with a hostname of server1a:

server1a – 10.217.157.36                      (Host server IP)

server1a-ce0 – 10.217.157.34              (NIC#1 IP)

server1a-ce1 – 10.217.157.35              (NIC#2 IP)

 

2.)    Ensure that the eeprom ‘local-mac-address’ is set to true.  To check, run:

# eeprom local-mac-address?

 

If that returns false, then set it to true:

# eeprom local-mac-address?=true

 

3.)    Check if the cards are seen by the system:

# ifconfig –a

 

4.)    If only one NIC is seen by the system, say ce0, then do the following:

 

5.)    Update the /etc/hosts file.  Make an entry in the /etc/hosts file for each IP address you previously obtained and its correlating hostname, as noted in Step 1.

 

6.)    There should already by a /etc/hostname.xxn, where xx is the NIC type and n is the NIC id, for the first NIC.  For example, if the first NIC is ce0, you’ll have a/etc/hostname.ce0.  Update the file with the following entry:

 


<servername>-ce0 netmask + broadcast + \

group ipmp-prod deprecated –failover up \

addif <servername> netmask + broadcast + up

 

Example:

 


usbo-ddbx-ora01-ce0 netmask + broadcast + \

group ipmp-prod deprecated -failover up \

addif usbo-ddbx-ora01 netmask + broadcast + up

The ‘addif’ line adds the first virtual IP on that ce0 NIC, which is the public IP of the server.  If you additional virtual IP, make additional ‘addif’ entries.  Make sure not to forget the ‘\’ before adding another line.

 

The ‘ipmp-prod’ is the group name given.  You can give it any name you choose, both NICs must be in the same group.

 

7.)    You’ll likely have to create a /etc/hostname.xxn file for the second NIC.  Create a file with the following entry:

 


<servername>-ce1 netmask + broadcast + \

group ipmp-prod deprecated –failover up

 

Example:

 


usbo-ddbx-ora01-ce1 netmask + broadcast + \

group ipmp-prod deprecated -failover up
<\code>
 

8.)    Reboot the server.  The configuration you just made should go into effect when the server comes online.

9.)    Check the status of the interfaces:

 


# ifconfig –a
<\code>
 

10.) You should see something like this:

 


sunlight# ifconfig -a

lo0: flags=1000849<UP,LOOPBACK,RUNNING,MULTICAST,IPv4> mtu 8232 index 1

        inet 127.0.0.1 netmask ff000000

ce0: flags=9040843<UP,BROADCAST,RUNNING,MULTICAST,DEPRECATED,IPv4,NOFAILOVER> mtu 1500 index 2

        inet 10.217.157.39 netmask ffffff00 broadcast 10.217.157.255

        groupname ipmp-prod

        ether 0:3:ba:24:2c:b2

ce0:1: flags=1000843<UP,BROADCAST,RUNNING,MULTICAST,IPv4> mtu 1500 index 2

        inet 10.217.157.15 netmask ffffff00 broadcast 10.217.157.255

ce1: flags=9040843<UP,BROADCAST,RUNNING,MULTICAST,DEPRECATED,IPv4,NOFAILOVER> mtu 1500 index 3

        inet 10.217.157.40 netmask ffffff00 broadcast 10.217.157.255

        groupname ipmp-prod

        ether 0:3:ba:24:2c:b1

 

11.) Note that ce0 is hosting the virtual IP address – ce0:1

12.) Set up the test for failing over of ce0.  From a workstation, run the following command, each in it’s own command window:

Window 1:                  C:\> ping –t <server>

Window 2:                  C:\> ping –t <server>-ce0

Window 3:                  C:\> ping –t <server>-ce1

 

13.) All three pings above should be receiving replies.

14.) Failover ce0:

 

# if_mpadm –d ce0

 

15.) Check the status of the interfaces:

 

# ifconfig –a

 

16.) You should see something like this:

 


sunlight# ifconfig -a

lo0: flags=1000849<UP,LOOPBACK,RUNNING,MULTICAST,IPv4> mtu 8232 index 1

        inet 127.0.0.1 netmask ff000000

ce0: flags=89040842<BROADCAST,RUNNING,MULTICAST,DEPRECATED,IPv4,NOFAILOVER,OFFLINE> mtu 1500 index 2

        inet 10.217.157.39 netmask ffffff00 broadcast 10.217.157.255

        groupname ipmp-prod

        ether 0:3:ba:24:2c:b2

ce1: flags=9040843<UP,BROADCAST,RUNNING,MULTICAST,DEPRECATED,IPv4,NOFAILOVER> mtu 1500 index 3

        inet 10.217.157.40 netmask ffffff00 broadcast 10.217.157.255

        groupname ipmp-prod

        ether 0:3:ba:24:2c:b1

ce1:1: flags=1000843<UP,BROADCAST,RUNNING,MULTICAST,IPv4> mtu 1500 index 3

        inet 10.217.157.15 netmask ffffff00 broadcast 10.217.157.255

 

17.) Note that ce0 now has a state of “OFFLINE” and the virtual IP is now hosted on ce1 – ce1:1.

18.) In the command windows, Window 1 should continue replying while Window 2 times out.

19.) Failback to ce0:

# if_mpadm –r ce0

20.) Check the status of the interfaces.  Status should look like in Step 10.

Command Window 2 should start replying again.

Avocent BREAK Sequence

The following sends a “Stop-A” signal to a Solaris box while connected via Avocent serial console connection:

Press Ctrl-Q

Type port break and press ENTER

Type resume and press ENTER

At this point, you should find yourself at the ok prompt.

ActiveSync, Bluetooth, and Smartphones

When I first hooked up my smartphone (WM6.1) via usb and tried to sync for the first time, ActiveSync hangs at “Synchronizing”.  Research on the internet found that deleting all the contents of c:\windows\temp (or in my case on a WinXP, c:\winnt\temp) was the solution.  Delete as many stuff out of the temp folder, especially those recently modified and the next time you hook up, connect, and syncrhonize, it works.

Then I tried connecting via bluetooth.  When I got my phone and laptop talking on bluetooth, my laptop did not present an ActiveSync bluetooth service.  Then I made sure that from the AS connection setting window I had “Allow connections from one of the following” checked and selected to one of the COM ports (in my case COM 6).  With Bluetooth Serial turned on, I tried to connect via bluetooth from my phone.  Upon the connection attempt, my laptop blue screens siting btkrnl.sys.

With few more research, I find some threads that says you actually have to uninstall bluetooth and reinstall it after you install ActiveSync.  I found the bluetooth driver for my device (from HP).  The package I downloaded actually may have updated my driver too.  Anyway… it performed the uninstall and install.  After that, I can see my laptop presenting an ACtiveSync bluetooth service and upon connection, the laptop doesn’t blue screen anymore.

LOB Storage Tips

( Source http://www.idevelopment.info/data/Oracle/DBA_tips/LOBs/LOBS_2.shtml)

When creating a table, you have the option of creating one or more LOB columns, unlike with LONG or LONG RAW columns, in which case, only one column can be made either LONG or LONG RAW. Attempting to create a table with more than one LONG or LONG RAW will result in an ORA-01754: a table may contain only one column of type LONG.

Many options are available when creating LOB columns like whether to store data within the LOB in-line or out-of-line. This is done using the ENABLE | DISABLE STORAGE IN ROW clause. Keep in mind though, that if you enable store in-line, Oracle will only store the data within the row provided the length of the data (and the locator) is less then 4K. If the length of the LOB data (along with the row locator) are greater than 4K, then the LOB value will be moved out of the row into a LOB segment. However, the lob locator remains stored within the row.

When creating a table with a LOB column, keep in mind that under the covers, Oracle will create a LOB segment and LOB index for each LOB column. By default, the LOB segment and LOB segment will be created in the same tablespace as the table, but you have the option of changing this. Neither the LOB segment nor the LOB index can be altered or dropped. The default name for the LOB segment is SYS_LOBxxx, where xxxx is a hexadecimal number. The default name of the LOB index is SYS_ILxxx where xxxx is a hexadecimal number. The hexadecimal numbers for both the LOB segment and the LOB index are the same. For both the LOB segment and the LOB index, you have the option of specifying a name as well as specifying which tablespace they will go into.

Creating a table with LOB columns, is nothing more than creating a column while using one of the LOB types as the data type for that column. The following simple example creates a table named test_lobtable that creates a BLOB, CLOB, and BFILE column:

CREATE TABLE test_lobtable (
    id  NUMBER
  , xml_file CLOB
  , image    BLOB
  , log_file BFILE
);

For each of the LOB columns, Oracle will implicitly create a LOB segment and LOB index for each LOB column. You can view the information about the LOB columns you just created by quering either DBA_LOBS, ALL_LOBS, or USER_LOBS. Use the following query to view information on the test_lobtable created above:

SELECT
    table_name “Table”
  , column_name “Column”
  , segment_name “Segment”
  , index_name “Index”
FROM user_lobs
WHERE table_name = ‘TEST_LOBTABLE’;
Table         Column   Segment                   Index
————- ——– ————————- ————————
TEST_LOBTABLE XML_FILE SYS_LOB0000149826C00002$$ SYS_IL0000149826C00002$$
TEST_LOBTABLE IMAGE    SYS_LOB0000149826C00003$$ SYS_IL0000149826C00003$$

Another view of interest are USER_SEGMENTS:

SELECT
    segment_name “Segment”
  , segment_type “Type”
  , tablespace_name “Tablespace”
FROM user_segments
WHERE segment_name like ‘SYS_LOB%’
   OR segment_name like ‘SYS_IL%’;
Segment                   Type        Tablespace
————————- ———– ———-
SYS_IL0000149826C00002$$  LOBINDEX    USERS
SYS_IL0000149826C00003$$  LOBINDEX    USERS
SYS_LOB0000149826C00002$$ LOBSEGMENT  USERS
SYS_LOB0000149826C00003$$ LOBSEGMENT  USERS

A Complete Create Table Example

We now take a look at a more complex form of the CREATE TABLE… that specifies the name of the LOB segment and LOB index as well as storage characteristics. To perform these actions we use the LOB clause of the CREATE TABLE… statement. Although, with Oracle8i, the ability to specify details for the LOB index have been deprecated. Here is the general syntax for the CREATE TABLE… statement:

CREATE TABLE <table name>
    (column list)
    [physical attributes]
    [storage clause]
    [LOB (<lobcol1> [, <lobcol2>...])
        STORE AS
            [<lob_segment_name>]
            (
                [TABLESPACE <tablespace_name>]
                [{ENABLE | DISABLE} STORAGE IN ROW]
                [CHUNK <chunk_size>]
                [PCTVERSION <version_number>]
                [ { CACHE | NO CACHE [{LOGGING | NOLOGGING}]
                          | CACHE READS [{LOGGING | NOLOGGING}]
                  }
                ]
                [<storage_clause_for_LOB_segment>]
                [INDEX [<lob_index_name>] [physical attributes] [<storage_for_LOB_index>] ]
            )
    ]
    [LOB (<lobcol1> [, <lobcol2>...]) … ]

With the LOB clause, you can specify a single LOB, or several/all LOBs in your table. The only drawback to specifying more than one LOB within a single LOB clause is that you cannot name the LOB segments. Where it becomes useful is when you need to specify the same storage clause or other attributes for all LOBs.
Using the LOB clause, we can now created our test_lobtable using a storage clause, tablespace, and other attributes as follows:

CREATE TABLE test_lobtable (
      id  NUMBER
    , xml_file CLOB
    , image    BLOB
    , log_file BFILE
)
LOB (xml_file)
    STORE AS xml_file_lob_seg (
        TABLESPACE lob_data
        CHUNK 4096
        CACHE
        STORAGE (MINEXTENTS 2)
        INDEX xml_file_lob_idx (
            TABLESPACE lob_index
            STORAGE (MAXEXTENTS UNLIMITED)
        )
    )
LOB (image)
    STORE AS image_lob_seg (
        TABLESPACE lob_data
        ENABLE STORAGE IN ROW
        CHUNK 4096
        CACHE
        STORAGE (MINEXTENTS 2)
        INDEX image_lob_idx (
            TABLESPACE lob_index
        )
    )
/
Now lets run the same queries against user_lobs and user_segments:
SELECT
    table_name “Table”
  , column_name “Column”
  , segment_name “Segment”
  , index_name “Index”
FROM user_lobs
WHERE table_name = ‘TEST_LOBTABLE’;
Table          Column    Segment           Index
————– ——— —————– —————–
TEST_LOBTABLE  XML_FILE  XML_FILE_LOB_SEG  XML_FILE_LOB_IDX
TEST_LOBTABLE  IMAGE     IMAGE_LOB_SEG     IMAGE_LOB_IDX

 

SELECT
    segment_name “Segment”
  , segment_type “Type”
  , tablespace_name “Tablespace”
FROM user_segments
WHERE segment_name like ‘XML_%’
   OR segment_name like ‘IMAGE_%’
ORDER BY segment_name DESC;
Segment            TS Type      Tablespace
—————— ———— ———–
XML_FILE_LOB_SEG   LOBSEGMENT   LOB_DATA
XML_FILE_LOB_IDX   LOBINDEX     LOB_DATA
IMAGE_LOB_SEG      LOBSEGMENT   LOB_DATA
IMAGE_LOB_IDX      LOBINDEX     LOB_DATA

Specifying Multiple LOB Columns in LOB Clause

Remember that it is possible to specify multiple LOB columns within a single LOB clause. The only drawback is that you cannot name the LOB segment or the LOB index. They will default to SYS_LOBxxxx and SYS_ILxxxx respectively.

CREATE TABLE test_lobtable (
      id  NUMBER
    , xml_file CLOB
    , image    BLOB
    , log_file BFILE
)
LOB (xml_file, image)
    STORE AS  (
        TABLESPACE lob_data
        CHUNK 4096
        CACHE
        STORAGE (MINEXTENTS 2)
        INDEX (
            TABLESPACE lob_index
            STORAGE (MAXEXTENTS UNLIMITED)
        )
    )
/

 

(Source Quest Oracle Knowledge Xpert)
Internal LOB Multi-Media Datatypes
Oracle has two basic types of LOB datatypes, these are internal and external. Internal types are:

BLOB (Binary Large Object)
CLOB (Character (single – byte) large object)
NCLOB (National Character (multi-byte) large object)
The external datatype (actually a pointer construct to an external file location a “locator”) is BFILE.
Defining LOB Storage
If LOB storage is not defined and LOB attribute size exceeds 4000 characters, the attributes which overflow will automatically be placed in the table’s tablespace in default configured LOB storage and index. If LOB storage is allowed to go to defaults you will not get optimal use of resources and could cause contention for disk resources, both of which will reduce your system performance.

LOB storage is defined by the CREATE TABLE or ALTER TABLE commands. The LOB storage clause is used to define the LOB storage profile.

The LOB storage clause uses a LOB parameters section to specify the LOB tablespace, storage in row, CHUNK and PCTVERSION settings as well as CACHE status and, if NOCACHE is specified, whether the LOB storage should be LOGGING or NOLOGGING and the specification for the LOB index. 

The LOB index is specified through the LOB index clause. You can name or let the system name the index and then specify the normal index storage variables for the LOB index. 

Whether the LOB datatype is BLOB, CLOB or NCLOB the storage clause format is identical. LOB storage specifications can be applied to BFILE LOB datatypes but it doesn’t make sense to do so. If you do not specify the segname parameter, the Oracle system will give the segment some wonderful name such as ‘SYS_LOB0000001562C0035$’ — so we suggest you name the segments.

 

The limits on LOBs have been increased in Oracle Database 10g. The new maximum limits are calculated at (4GB – 1 byte) * (the database block size). Thus, if the database block size is 8KB, there is essentially a 32GB limitation on LOBs in that database. Note that Bfiles are limited to 4GB in size.

All of the internal LOB datatypes can be either stored inline (up to a maximum of 4000 bytes) or in an external LOB storage area (up to 4 gigabytes or the maximum datafile size on your platform) that is specified by a LOB storage declaration. 

The LOB storage clause is not needed if the maximum size of the BLOB doesn’t exceed 4000 bytes. Up to 4000 bytes can be stored in-line with the other data in the tablespace. If the length of the BLOB exceeds 4000 bytes it must be stored in either a system defaulted storage (the same as the default for the table it resides in) or in an explicitly defined LOB storage area.
TIP: We suggest you always specify the LOB storage clause. If you force the system to do a default storage each time a BLOB or CLOB exceeds 4000 bytes you could cause datafile fragmentation and performance problems. The LOB storage clause gives you control instead of the system.

Example Use of The LOB Storage Clause and LOB Use in Tables

CREATE TABLE internal_graphics (
  graphic_id      NUMBER,
  graphic_desc    VARCHAR2(30),
  graphic_blob    BLOB,
  graphic_type    VARCHAR2(4))
  LOB (graphic_blob) STORE AS glob_store (
     TABLESPACE raw_data
        STORAGE (INITIAL 100k NEXT 100k PCTINCREASE 0)
        CHUNK 4
        PCTVERSION 10
     INDEX glob_index (
        TABLESPACE raw_index))
  TABLESPACE appl_data
     STORAGE (INITIAL 1m NEXT 1m PCTINCREASE 0)
 /

BLOBs and CLOBs are identical in creation and use, their major difference is in that BLOBs are used for binary data (much like LONG RAW) while CLOBs are used for single byte character storage (like VARCHAR2).

Unix command to find CPU Utilization

See: http://www.cyberciti.biz/faq/unix-command-to-find-cpu-utilization/

NetApp Failed Disk Tips

aggr status –f   — this will display the broken disk 

then 

storage disk setled -disk disk_name -action {on | off | blink | test} -time minutes 
This should provide you that fuzzy feeling you are looking for.

Startup and Shutdown Triggers

When a database as an [after] startup trigger or [before] shutdown trigger, these can cause problems to the database if the triggers become invalid. 

Specifically, the database may startup, but not give the prompt back to the user during a startup because the after startup trigger is hung. In this case, the database is actually open and ready, so it’s not so bad.  But during the a shutdown, the database will not shutdown at all because the before shutdown trigger is hung.  Notice that the trigger must execute completely before the database shutsdown.

This came about in Bill case today when he restored an 8.1.7.4 database from server to another.  The previous database home had Java Server package installed, but in the new server, the Java Server package was not installed.  More importantly, the database had a startup and shutdown trigger that basically started and shutdown the Java Server, respectively.

So the database would startup, but we couldn’t shut it down.  We also couldn’t disable the triggers or drop them.  My suspicion is that because the triggers were hung, we couldn’t actually do anything with them (disable or drop them).

The solution was to shutdown abort the database.  And then on startup, use an init paramater called “_system_trig_enable = false” to disable the startup and shutdown triggers.  With this, the database opened with no problem and the startup trigger never fired.  At that point, we had the option to disable the triggers or drop them entirely.

[Reference: http://www.psoug.org/reference/system_trigger.html ]

What is enq: TX – row lock contention

[ Below is a great information I found on another blog.  There's no need to paraphrase the original information so I simply just copied it here for my safe keeping of the information for furture reference.  Many thanks to Mohammad Abdul Momin Arju for compiling the information. ]

Enqueues are locks that coordinate access to database resources. enq: wait event indicates that the session is waiting for a lock that is held by another session. The name of the enqueue is as part of the form enq: enqueue_type – related_details.

The V$EVENT_NAME view provides a complete list of all the enq: wait events.

TX enqueue are acquired exclusive when a transaction initiates its first change and held until the transaction does a COMMIT or ROLLBACK.

Several Situation of TX enqueue:

————————————–

1) Waits for TX in mode 6 occurs when a session is waiting for a row level lock that is already held by another session. This occurs when one user is updating or deleting a row, which another session wishes to update or delete. This type of TX enqueue wait corresponds to the wait event enq: TX – row lock contention.

The solution is to have the first session already holding the lock perform a COMMIT or ROLLBACK.

2) Waits for TX in mode 4 can occur if a session is waiting due to potential duplicates in UNIQUE index. If two sessions try to insert the same key value the second session has to wait to see if an ORA-0001 should be raised or not. This type of TX enqueue wait corresponds to the wait event enq: TX – row lock contention.

The solution is to have the first session already holding the lock perform a COMMIT or ROLLBACK.

3) Waits for TX in mode 4 is also possible if the session is waiting due to shared bitmap index fragment. Bitmap indexes index key values and a range of ROWIDs. Each ‘entry’ in a bitmap index can cover many rows in the actual table. If two sessions want to update rows covered by the same bitmap index fragment, then the second session waits for the first transaction to either COMMIT or ROLLBACK by waiting for the TX lock in mode 4. This type of TX enqueue wait corresponds to the wait event enq: TX – row lock contention.

Troubleshooting:

The SQL text currently is waiting to:

select sid, sql_text from v$session s, v$sql q
where sid in (select sid from v$session where state in ('WAITING') and wait_class != 'Idle' and event='enq: TX - row lock contention' and ( q.sql_id = s.sql_id or q.sql_id = s.prev_sql_id));

The blocking session is:

select blocking_session, sid, serial#, wait_class, seconds_in_wait from v$session
where blocking_session is not NULL order by blocking_session;

Large MMON Trace Files

I encounter some very large Oracle trace files while working on a Fremont database (version 10.2.0.1).   I noticed the issue when I saw the disk space for the binary directory had reached 100%.  When I investigated, I a large trace file in the background dump directory.  The trace filename has a format of <sid>_mmon_<id>.trace, such as ppasx_mmon_608.trc.

Apparently, the file keeps growing because the mmon process is logging a perpetual issue in the trace file.  In this case, the issue was with the sga parameters in the init file:


....
*** 2009-01-22 11:31:12.738
*** SERVICE NAME:(SYS$BACKGROUND) 2009-01-22 11:31:12.735
*** SESSION ID:(209.1) 2009-01-22 11:31:12.735
SGA POLICY: Cache below reserve and cant get memory from any other component
GRANULE SIZE is 16777216
COMPONENT NAME : shared pool, curnum=57, usrsz=57, tgt=57, st=0
Number of granules in listid 0 is 0
Number of granules in listid 1 is 0
Number of granules in listid 2 is 57
Number of granules in listid 3 is 0
....


The problem with the sga parameter is that the SGA_TARGET paraneter is set along with SHARED_POOL_SIZE.  the solution is clear, either unset the SHARED_POOL_SIZE (or reduce it) or increase SGA_TARGET.

Deleting the file as a quick fix to free up disk will not work because the mmon process has a hold of it and will only be replaced with a .nfs file.

References:

Metalink Doc ID: 422954.1

Freelist Forum Thread: “Huge MMON traces”