ORA-12500: TNS:listener failed to start a dedicated server process

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

sqlplus usr/pwd@orcl

ORA-12500: TNS:listener failed to start a dedicated server process

This error can be seen on Windows platforms when the file LISTENER.ORA located in the directory %ORACLE_HOME%\network\admin contains an explicit database service declaration (see below in green) and the database has not been started:

...
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
 (SID_NAME = PLSExtProc)
 (ORACLE_HOME = C:\oracle\product\10.1.0\Db_1)
 (PROGRAM = extproc)
)
 (SID_DESC =
 (GLOBAL_DBNAME = orcl)
  (ORACLE_HOME = C:\oracle\product\10.1.0\Db_1)
  (SID_NAME = orcl)
 )
)
...

This means that you should contact the DBA and ask why the database service is down.

A misconfiguration of the TNSNAMES.ora file would lead to different errors, like ORA-12541 (when TCP PORT numbers mismatch for instance).
Note that you can remove the explicit declaration of the database service from the LISTENER.ora file in case you are using a standard listener configuration (on port 1521) because the database should be able to register itself after starting up. If something goes wrong in this case, you may get ORA-12514 or ORA-12505, most likely.

See message translations for ORA-12500 and search additional resources

Checking the MIME type of an URL (with Apex)

This posting is to announce that a new MIME type checking page is available at my company's site:

http://www.yocoya.com/pls/apex/f?p=yocoya:mime:0:en

The need for this tool arose after discovering that a site i had bookmarked for doing the same thing had been discontinued or moved to another domain, so i thought it could be a good exercise to create a page with the help of Oracle Application Express and the built-in UTL_HTTP packaged procedures.
At the same time, i wanted to understand better the Oracle Apex HTTP redirect behavior so i came up with this tool addressing both issues.
In the end it was quite entertaining to write the code and i finally got a clear picture of the redirect problem.

But why on earth should one be concerned about the MIME type after all?

Knowing the MIME type of a document retrieved from the web may be necessary when diagnosing problems like a browser that fails to display a document inline, but attempts to download it as a binary file. This kind of situations is often caused by an improper setting of the MIME type in the web server configuration.
A typical example is with certain sites returning SVG documents with an invalid MIME type like application/svg-xml whereas the correct MIME type should be application/svg+xml (note the plus sign instead of the minus sign).
Another frequent hit is a wrong WSDL (Web Service Definition Language) MIME type, so instead of getting application/wsdl+xml, you get text/plain.

Coming to the underlying implementation, if you need to check for the MIME type of an URL programmatically, the functionality will be a refinement of the following basic expression:

HttpUriType.createUri('http://wap.priceblog.it').getContentType();

text/vnd.wap.wml; charset=UTF-8

caveat:
due to the http redirects, if you specify an URL pointing to an Oracle Application Express application using the PL/SQL expression above, you may get:

HttpUriType.createUri('http://www.yocoya.com').getContentType();

ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1231
ORA-12545: Connect failed because target host or object does not exist
ORA-06512: at "SYS.HTTPURITYPE", line 108

The quick workaround consists in supplying a complete apex URL including a valid session id (the big number after the second colon), that is something like:

begin
dbms_output.put_line(
HttpUriType.createUri(
'http://www.yocoya.com/pls/apex/f?p=yocoya:oerr:1547750763260356').getContentType());
end;
/

text/html; charset=UTF-8

Note that apex sessions expire over time, so you may get an error if you execute the program above as it is, you must use a "fresh" session ID.

Finding out the actual session id and take you to the final page or document is the primary task of the tool i made available on-line.
Clearly there is something more going on behind the scenes, because of the Apex redirect problem mentioned earlier and incidentally i found out that the built-in method called UTL_HTTP.SET_FOLLOW_REDIRECT(n) doesn't work correctly for n greater than zero when talking to an Apex application, it seems to me that it doesn't cope with relative redirects.
That's why i had to come up with this custom procedure.

So, in case you need to check out the MIME type of a document available online or watch what goes on behind the scenes when opening a public page served by Apex, this tool can help.

Or so i guess :-)

The page is a beta version, there could be situations that i didn't think of, so i strongly encourage to report any issues you may encounter or submit enhancement requests.
The errors returned by the UTL_HTTP package are automatically converted into web links to the yocoya's oracle error translation online tool that i introduced a while ago, providing error message translations in 30 languages and quick links to the online Oracle documentation of different releases and, last but not least, oraclequirks.com itself, just in case, however i haven't had the time to publish anything on these specific errors yet, so may be i'll post something in the next weeks.

ORA-31086: insufficient privileges to register schema

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

Attempting to register a global XML schema as a non-privileged user, you may get the following error:

BEGIN
DBMS_XMLSCHEMA.registerSchema(
SCHEMAURL => 'http://www.acme.com/XMLSchemaURL.xsd',
SCHEMADOC => bfilename('XMLDIR','someXMLSchema.xsd'),
CSID => nls_charset_id('AL32UTF8'),
LOCAL => FALSE);
END;

ORA-31086: insufficient privileges to register schema "http://www.acme.com/XMLSchemaURL.xsd"

when you specify the boolean value FALSE for LOCAL, you are attempting to register a global XML schema, but only privileged users like the DBA can perform this operation.

See message translations for ORA-31086 and search additional resources

ORA-01006: bind variable does not exist

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

Here is a common scenario for this error:
i specified two variables in the USING clause of the EXECUTE IMMEDIATE statement, however i didn't specify two bind variables in the PL/SQL string to be executed, there is only one variable (:1).

declare
a varchar2(50) := 'TEST PROCEDURE';
b varchar2(50) := 'DYNAMIC SQL';
c varchar2(50);
d varchar2(50);
begin
execute immediate 'begin dbms_application_info.set_module(:1); end;' using a, b;
dbms_application_info.read_module(c,d);
dbms_output.put_line(c);
dbms_output.put_line(d);
end;

ORA-01006: bind variable does not exist

After adding the bind variable as a second parameter for SET_MODULE, the program works correctly:

declare
a varchar2(50) := 'TEST PROCEDURE';
b varchar2(50) := 'DYNAMIC SQL';
c varchar2(50);
d varchar2(50);
begin
execute immediate 'begin dbms_application_info.set_module(:1,:2); end;' using a, b;
dbms_application_info.read_module(c,d);
dbms_output.put_line(c);
dbms_output.put_line(d);
end;

TEST PROCEDURE
DYNAMIC SQL

You can easily try out the code inside Apex SQL Workshop.

Another situation where you might get this error is when you attempt to execute this unsupported single-row native dynamic SQL statement:

declare
a varchar2(10);
b varchar2(10) := 'Y';
begin
execute immediate 'select dummy into :a from dual where dummy != :b' using out a, in b;
dbms_output.put_line(' a:'||a);
end;

ORA-01006: bind variable does not exist

however in this case the problem is with the syntax, not with the missing bind variable, because single-row dynamic SELECTs must be written with the special INTO clause as already explained in a previous posting (see ORA-01745).

See message translations for ORA-01006 and search additional resources

ORA-44412: Invalid ACL: Cyclic ACL inheritance is not allowed

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

I just got this weird oracle error message upon attempting to start up my Oracle XE:

ORA-44412: Invalid ACL: Cyclic ACL inheritance is not allowed

It happened after setting SGA_TARGET to 1GB in the SPFILE, on a windows XP pc where probably the amount of virtual memory was not sufficient to support this request.
I could open the database normally by using a backup PFILE with a lower SGA_TARGET value.

See a previous topic for instructions on how to restore a valid SPFILE.

See message translations for ORA-44412 and search additional resources

SQL Error: ORA-01745: invalid host/bind variable name

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

This error can be returned in at least two distinct situations (and possibly more) involving native dynamic SQL.

The first situation occurs when you pick a reserved word for use as a bind variable name, for instance :USER or :SYDATE, just to name a couple (by the way, the letter casing is irrelevant).

Let's look instead at the following PL/SQL anonymous block for curious occurrence of this error:

declare
a varchar2(10);
b varchar2(10) := 'Y';
begin
execute immediate 'select dummy into :1 from dual where dummy != :2' using in b;
dbms_output.put_line(' a:'||a);
end;

SQL Error: ORA-01745: invalid host/bind variable name

There are a couple of things worth noting here:
first of all, the correct way of specifying a dynamic single-row select query is the following:

declare
a varchar2(10);
b varchar2(10) := 'Y';
begin
execute immediate 'select dummy from dual where dummy != :2' INTO a using b;
dbms_output.put_line(' a:'||a);
end;

a:X

So, get rid of the INTO clause inside the SELECT and relocate it outside.
Secondly and funnily enough, if we write the original statement with :a instead of :1, Oracle doesn't complain at all:

declare
a varchar2(10);
b varchar2(10) := 'Y';
begin
execute immediate 'select dummy into :a from dual where dummy != :2' using b;
dbms_output.put_line(' a:'||a);
end;

a:

Even if oracle doesn't complain, this statement isn't of any practical use, because the bind variable :a is ignored altogether.
Note also that according to the documentation of 10R1 (it says the same thing in the docs of other versions) "you can place all bind arguments in the USING clause", but this isn't true for single-row SELECT queries (see ORA-01006) that require the special INTO clause shown above.

See message translations for ORA-01745 and search additional resources

ORA-00821: Specified value of sga_target 100M is too small, needs to be at least 116M

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

This article applies to Oracle 10g R1 and R2, including XE.
Even if it is going to be published on the 1-st of April, it's not an hoax ;-)

Although i conducted my test on a Windows box, i am confident that most of it still holds true on Linux platforms as well, once you have adapted some minor details like the slashes (/) in place of the backslashes (\) and after substituting the folder %ORACLE_HOME%/database with its unix platform counterpart $ORACLE_HOME/dbs.

This error occurs at instance startup when an incompatible combination of values for SGA_TARGET and at least one of the following four parameters: DB_CACHE_SIZE, JAVA_POOL_SIZE, SHARED_POOL_SIZE and LARGE_POOL_SIZE have been specified either in the relevant SPFILE or PFILE.

On Oracle 10g for Windows, trace of this error can be found in the file oradim.log, located in %ORACLE_HOME%\database.

So, let's assume here to be working on an XE version, but I've got the same results with 10gR1 on Windows 2000.

Windows Service OracleServiceXE is listed as started however the instance is down and ordinary users, obviously, cannot connect to the database (they are probably trying to call you on the phone...).

In order to properly start the database instance, you need to override the default SPFILE containing the bogus values.
Supposing you didn't make a backup of the SPFILE (either in its semi-binary format or in clear text as a PFILE), you can proceed as follows:

1. open the default SPFILE called SPFILEXE.ORA in %ORACLE_HOME%\database with a text editor;
   
2. copy the rows written in clear text to the clipboard and save them in a new text file called safeXE.ora;
3. change the line(s) containing the faulty parameters (scroll further down for guidance on the values that you should specify);
4. put this file in the same directory %ORACLE_HOME%\database;
5. connect to the idle oracle instance from an OS command prompt: sqlplus "sys/pwd as sysdba";
6. startup pfile=%ORACLE_HOME%\database\safeXE.ora restrict;
   

Restricted mode will prevent ordinary users to connect while you are still working on the db.

Once the db is up, save the changes back to the default SPFILE:

create spfile from pfile='%ORACLE_HOME%/database/safeXE.ora';
shutdown immediate
startup

and there you go.

And now for the explanation of the faulty values:
you have specified the parameter SGA_TARGET in the SPFILE (and also in the PFILE).
As a consequence Oracle is trying to allocate the memory for the various buffers automatically, but without exceeding the limit you imposed on the SGA (say 100M).
However, if you got this error, there are probably other parameters specified in the SPFILE like:
DB_CACHE_SIZE, JAVA_POOL_SIZE, SHARED_POOL_SIZE, LARGE_POOL_SIZE.
If any of these is set to a number greater than zero, the value will be considered the lowest acceptable boundary, for instance:

alter system set db_cache_size=50M scope=spfile;
alter system set shared_pool_size=50M scope=spfile;

when you try to restart the db, you'll get:

ORA-00821: Specified value of sga_target is too small, needs to be at least 116M

which means that you cannot use up all the SGA allowance for the db cache and the shared pool, because Oracle needs another 16Mb of RAM, some of which are needed for other buffers and some for adjusting the number of units to the nearest SGA granule size (multiple of 4Mb on my machine).
So, you have at least three choices here:

1. to decrease by 16Mb either of the values (or the sum thereof);
2. to leave Oracle free to allocate the required buffers by commenting out those two parameters (prepend a pound # sign to the line to transform the text into a comment);
3. to increase, if you have free memory available, SGA_TARGET and make it at least 116Mb or larger.

Final considerations:
i could not start an instance of Oracle 10gR1 with less than 48M of SGA_TARGET (all other buffers autoallocated).
In order to run Oracle Application Express (Apex) 3.1 or earlier, there is one requirement in terms of minimum buffer size:

SHARED_POOL_SIZE >= 100M

however there are several articles where they add also (but is not mentioned in the official documentation):

JAVA_POOL_SIZE >= 150M

which implies a minimum SGA_TARGET of 264Mb (but it could be slightly more or slightly less depending on the platform and the round-up to the nearest multiple of the SGA granule size.

Let's spend a few words on this round-up mechanism.
100Mb is multiple of 4Mb (25 *4), but 150Mb isn't a multiple of 4Mb (the granule size on my machine), the nearest multiple is 152Mb (38 * 4Mb). This explains why if i set the value to 152Mb, it will still complain and say that the minimum SGA_TARGET is 264Mb, that is:
66 * 4Mb = (25 + 38 + 3 ) * 4Mb
So, I found out the actual value of the overhead, in this case it's 3 granules, for an extra 12Mb.

In the original example both values were 50Mb, neither of which is a multiple of 4Mb, so Oracle rounded up both buffers to 52Mb (104Mb total), plus the overhead of 3 granules, another 12Mb, which gives the final amount of 116Mb.

You can find out more about this overhead by querying the following view:

select * from V$SGAINFO;

Clearly you don't want to run Apex with only 4Mb of buffer cache, it will be necessary to specify some meaningful value for DB_CACHE_SIZE and increase SGA_TARGET consequently.

See message translations for ORA-00821 and search additional resources.