Friday, June 30, 2006

HTMLDB - PlsqlNlsLanguage takes one argument, NLS Language

I installed HTMLDB (off the 10.2 Companion disk) on Solaris today. Install seemed to run OK and at the end it told me that the HTMLDB instance had started and could be accessed at:
http://wintermute:7778
When I tried to access it, no joy. Said it couldn't connect. I tried using opmnctl to check the status and found that opmn itself was up but none of the managed services were. Stopping and starting opmn and all it's managed processes produced:
$ opmnctl stopall
opmnctl: stopping opmn and all managed processes...
$ opmnctl startall
opmnctl: starting opmn and all managed processes...
================================================================================
opmn id=wintermute:6200
0 of 1 processes started.

ias-instance id=IAS-X-wintermute.6299
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
ias-component/process-type/process-set:
HTTP_Server/HTTP_Server/HTTP_Server

Error
--> Process (pid=24282)
failed to start a managed process after the maximum retry limit
Log:
/u03/oraracle/app/oracle/htmldb/10.2/opmn/logs/HTTP_Server~1
Looking in the log file (see the updates to the wikipedia article on Error Hiding that I made for my views on error mesages and log files) reveled many instances of a more detailed error message:
--------
06/06/30 12:20:00 Start process
--------
/u03/oraracle/app/oracle/htmldb/10.2/Apache/Apache/bin/apachectl start: execing httpd
Syntax error on line 9 of /u03/oraracle/app/oracle/htmldb/10.2/Apache/modplsql/conf/marvel.conf:
PlsqlNlsLanguage takes one argument, NLS Language
leading me to the settings file marvel.conf. The contents of this file were:
<Location /pls/htmldb>
Order deny,allow
PlsqlDocumentPath docs
AllowOverride None
PlsqlDocumentProcedure wwv_flow_file_manager.process_download
PlsqlDatabaseConnectString wintermute:1527:emrep10 ServiceNameFormat
PlsqlNLSLanguage ENGLISH_UNITED KINGDOM.AL32UTF8
PlsqlAuthenticationMode Basic
SetHandler pls_handler
PlsqlDocumentTablename wwv_flow_file_objects$
PlsqlDatabaseUsername HTMLDB_PUBLIC_USER
PlsqlDefaultPage htmldb
PlsqlDatabasePassword @BdtmwbdTRq8ljkMXfRPmbpw=
Allow from all
</Location>
Looking at the indicated line, line 9:
PlsqlNLSLanguage ENGLISH_UNITED KINGDOM.AL32UTF8
the only thing that struck me was the space in the middle of the NLSLanguage identifier, maybe this was being intrepreted as a field seperator?

I put sigle quotes around the NLSLanguage identfier:
PlsqlNLSLanguage 'ENGLISH_UNITED KINGDOM.AL32UTF8'
and tried again:
$ opmnctl stopall
opmnctl: stopping opmn and all managed processes...
$ opmnctl startall
opmnctl: starting opmn and all managed processes...
$
Success!

Thursday, June 29, 2006

Does Oracle cache the SYSDBA password?

I was just having a play around on one of my test/sandpit databases ("small database on a low power machine that I use for testing out scripts &c, no live data and if the database gets destroyed I just have to wipe out the files and create a new one" type of thing). I'd forgotten the SYSDBA password (for sandpit systems I use the SID as the SYSDBA password so don't bother to write it down, for some reason this time I didn't) but needed it (I'm installing HTMLDB to try it out on that machine) so wanted to change it. I had been getting1:
SQL> conn sys/sbtest@sbtest as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied
as you might expect. I used orapwd to change the password
$ cp orapwsbtest orapwsbtest.20060629.bak
$ orapwd file=orapwsbtest password=sbtest entries=16

OPW-00005: File with same name exists - please delete or rename
$ rm orapwsbtest
$ orapwd file=orapwsbtest password=sbtest entries=16
and thought I would be good to go. So, I try logging in as sys from another window:
SQL> conn sys/sbtest@sbtest as sysdba
ERROR:
ORA-01031: insufficient privileges
Not so good to go! I looked up the error message:
$ oerr ora 01031
01031, 00000, "insufficient privileges"
// *Cause: An attempt was made to change the current username or password
// without the appropriate privilege. This error also occurs if
// attempting to install a database without the necessary operating
// system privileges.
// When Trusted Oracle is configure in DBMS MAC, this error may occur
// if the user was granted the necessary privilege at a higher label
// than the current login.
// *Action: Ask the database administrator to perform the operation or grant
// the required privileges.
// For Trusted Oracle users getting this error although granted the
// the appropriate privilege at a higher label, ask the database
// administrator to regrant the privilege at the appropriate label.
not very useful. I was getting the same error message if I attempted from the Oracle software owner user, a different user in the the dba UNIX group on the same machine or a remote SQL*Plus session on my desktop. Wondering if maybe Oracle was caching the password file, and therefore the SYSDBA password, I logged in as SYSDBA using OS authentication and forced a restart of the database:
SQL> conn / as sysdba
Connected.

SQL> startup force
ORACLE instance started.

Total System Global Area 555451816 bytes
Fixed Size 730536 bytes
Variable Size 285212672 bytes
Database Buffers 268435456 bytes
Redo Buffers 1073152 bytes
Database mounted.
Database opened.
SQL> conn sys/sbtest@sbtest as sysdba
Connected.
SQL>
Success! The password now worked from the Oracle software owner account, the other account in the dba UNIX group and the remote SQL*Plus session.

It's a good thing you can still use OS authentication to log in as SYSDBA, I'd have been really sunk if I hadn't had that.



1I've made it habit to always run SQL*Plus with the /nolog option then use connect so that the password won't appear in the process list if someone does a ps. Making good/safe practice habit can save a lot of trouble.