The listener supports no services

This error means in general there are communication issues. It can be generic, so the outline here only depicts a very particular case.

Environment:
  • Windows 2003, x86
  • Oracle 11.1.0.7.0

When trying to start the listener, it took more time than usual, and after a while it showed up the following messages on the console:

Starting tnslsnr: please wait...

TNSLSNR for 32-bit Windows: Version 11.1.0.7.0 - Production
...
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully


And when trying to display the services this was shown by the lsnrctl services (more...)

Yet Another ORA-00600

ORA-600 [kkoipt:incorrect pwj].

There are some ORA-600 which can be easily googled, and there are others which are pretty difficult to find. This is the case. M.O.S reports this ORA600 to be related to a bug (9929660) on the 11.2 platform and which is fixed on 12.0.

Recently this error showed up on a Windows 2003 platform with Oracle 11.1.0.7.0. After deciphering the almost cryptic related trace and dump file and diagnosing after the possible environment causes. I found out this error was triggered at one of the internal performance maintenance task, (more...)

V$OBJECT_USAGE view empty.

This is a very old issue, but one that sometimes is hard to remember when needed. I tried to monitor index usage to get rid of some indexes which in my opinion are not in use. I implemented the traditional method of index monitoring:

Start monitoring the index.
alter index SchemaOwner.IndexName monitoring usage;

After a while, query the V$OBJECT_USAGE view.

select * from V$OBJECT_USAGE;

It came to my surprise that the view was empty. The reason ... I knew it some time back ... but I forgot. I googled and I found a very good reference from Alex Gorvachev, who went (more...)

Where is the password column from DBA_USERS in 11g?

Oracle 11g brought several security enhancements, as it is well known by the 11g users. On previous Oracle versions it was possible to query the DBA_USERS PASSWORD column to get the hashed password string. It was useful when someone tried to temporarily reset the user's password and restore it to its original value without actually knowing it.

The command:

ALTER USER IDENTIFID BY VALUES '14C785FC66029BF9';

it could take the hashed value from the DBA_USERS data dictionary view. However starting with Oracle 11g this column is null ... so where are we supposed to take this hashed value from?.

SQL> SELECT USERNAME, (more...)

Reading the alert.log as a local table.

Reading the alert.log file is a must for the DBA. This is the primary source of information about the Database. The traditional way to read it is by means of any text based tool that can open the file and lets you take a look at it and look for specific text patterns. This requires access to the Operating System, which probably is something the DBA can take for granted at most shops. However, it happens that because of security issues, access to the Operating System is restricted ... sounds familiar? ... either you have someone to send you the file (more...)