Wednesday, November 16, 2011

OEM: "Tablespace Used (%)" alert is not triggered

There was an incident, where a tablespace filled completely, but I did not get any alert from OEM.

There is Oracle Note 403264.1:"Troubleshooting a Database Tablespace Used(%) Alert problem", where several issues about this alert are discussed.
In my case, there was no entry in DBA_OUTSTANDING_ALERTS,
although in DBA_TABLESPACE_USAGE_METRICS the particular tablespace had exceeded the thresholds I have set.

So, according to Section 4 in this note, this is probably caused by database bugs 7462407, 5245039.
Additionally, I have a "gap" in my v$tablespace.

Unfortunately, there are no patches for these bugs for Windows platforms, so I created a user defined metric to get these alerts.
The new metric was based on DBA_TABLESPACE_USAGE_METRICS, which is very nice, because it queries v$filespace_usage, which keeps track of any MAXSIZE value you may have set in your autoextended datafiles.
If it is set to UNLIMITED, then this value is equal to 32 GB for 8 KB block size (2^22-1 * 8 KB).

Keep in mind, columns referencing sizes in these 2 views are blocks.
So, if your tablespace has block size of 8 KB, multiply these values by 8 to get its size in KB.

Of course, there is no way for your database to know, if your filesystems will fill, e.g. if in your filesystem you have 10 GB of free space and an autoextended datafile in it with MAXSIZE 15 GB, you will not get any alert, even from the default OEM metric, when your filesystem's free space depletes.
You should use "Filesystem Space Available (%)" alert at host level, to check your filesystem's free space.

Tuesday, November 8, 2011

Direct Path INSERT requires exclucive lock

There is a table, where we insert rows using serial direct-path INSERT [INSERT /*+ APPEND */].
The INSERT command is executed periodically by a job.
Manually, I run a DELETE command on this table to purge rows no longer needed.
At some point, the job mentioned executes the direct-path INSERT and it gets blocked by the DELETE command with an "enq: TM - contention" lock.

INST_IDSIDID1ID2LMODEBLOCKREQUESTOBJECT_NAMELOCK_TYPEPROGRAM
280167604880310AUD$_BU_IDX2DMLsqlplus@ecdb2 (TNS V1-V3)
2--->82967604880006DMLoracle@ecdb2 (J001)

According to Oracle Database Administrator's Guide 10g Release 2 (10.2), this is normal behavior:

    During direct-path INSERT, the database obtains exclusive locks on the table (or on all partitions of a partitioned table). As a result, users cannot perform any concurrent insert, update, or delete operations on the table, and concurrent index creation and build operations are not permitted. Concurrent queries, however, are supported, but the query will return only the information before the insert operation.

Monday, November 7, 2011

ORA-27102, ORA-27100 on startup in a Windows 32bit platform

We changed parameter SGA_MAX_SIZE to 2GB in the spfile and we shutdown the database.
When we tried to startup, we got the following errors:
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Nov 6 15:24:45 2011
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup
ORA-27102: out of memory
OSD-00022: additional error information
O/S-Error: (OS 8) Not enough storage is available to process this command.
SQL> startup
ORA-27100: shared memory realm already exists

According to Oracle Note 1080317.1 "Summary of Causes for ORA-27102 on Startup":

    If oracle version is 32bit, the maximum SGA_TARGET / SGA_MAX_SIZE is 1.75gb

To start the database, we had to:
1. Start/stop oracle service.
2. Use an older pfile, where SGA_MAX_SIZE had a valid value.

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Nov 6 15:53:03 2011
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup pfile='E:\ORACLE\product\10.2.0\db_1\database\INIToblicore.ORA'
ORACLE instance started.
Total System Global Area 1258291200 bytes
Fixed Size                  1250572 bytes
Variable Size             419433204 bytes
Database Buffers          830472192 bytes
Redo Buffers                7135232 bytes
Database mounted.
Database opened.

SQL> create spfile='E:\ORACLE\product\10.2.0\db_1\database\SPFILEOBLICORE.ORA' from pfile;
File created.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.
Total System Global Area 1258291200 bytes
Fixed Size                  1250572 bytes
Variable Size             419433204 bytes
Database Buffers          830472192 bytes
Redo Buffers                7135232 bytes
Database mounted.
Database opened.