VNI-2002 File operation error when submiting jobs through Enterprise Manager

If you’ve been able to submit jobs through Enterprise Manager and then find that they are failing with VNI-2002 file operation error check out $OH/network/agent and see if you have inadvertently created a temp directory. I did, to store some files for backup. Either remove this directory or rename it. Then restart your agent and you should find your jobs now run

How to create the Order Entry schema after a database has been created using scripts

If you wish to create the oe schema at a later date after creating a database you will have to run the script ‘oe_main.sql’ in $OH/demo/schema/order_entry.

oe_main.sql asks for
1. the OE user's password. The user OE will be create by the script.
2. OE’s default and temporary tablespaces.
3. The HR user password. The HR user has to have been created. So run hr_main.sql in human_resources first. The HR user is created because there are some shared objects between HR and OE.
4. And finally, the SYS password and log_path

The script creates the OE user and grants privileges then creates the objects. Final the subschema is created.

The hr_main.sql in $OH/demo/schema/human_resources asks for
1. The HR password. The user is created here
2. The default and temporary tablespaces
3. The sys password and log_path

Run this script before oe_main.sql

Please Note: This note is applicable for 9i. File locations may vary

Quotation marks changing in web forms, from being pasted from MS Word

Ever wondered why those funny quotes which Microsoft Word produces are translated as backwards ?. in web forms

If you write a quote in word, type something and then close with another quote, Word magically pairs the two quotes. One quote the right way up the other upside down. If you then copy & paste this text into an Oracle web form you may find, after saving, those wonderful quotes have turned into something else.

The problem here is the NLS_LANG parameter in the Application Server. If your AS is windows go into the windows registry and locate the NLS_LANG parameter of the Oracle home the AS is using. If your NLS_LANG is

NLS_LANG = ENGLISH_UNITED KINGDOM.WE8ISO8859P1

You will have to change it to

NLS_LANG = ENGLISH_UNITED KINGDOM.WE8MSWIN1252

I should note that it MUST be ENGLISH_UNITED KINGDOM otherwise if you use anything else you may find your ‘£’ will becomes ‘$’

Oracle expert not login into the repository with TNS-12705

As part of tuning 8 and 9i database you can use ‘Oracle Expert’. This is part of the tuning pack of Enterprise Manager 9.2.0.1.0. Oracle Expert seems to be a pre-cursor to Oracle Advisors.

Basically, expert is given some basic parameters of the environment of the database and then monitors the performance of the database for about 15 minutes. At the end of the process, expert gives advice on some init.ora parameter changes. These changes can either be amended to your existing parameter file or if you are using an spfile you can change the values either on the fly or after an instance restart.

And now the story.

After installing a new database using DBCA and populating the database, it was time to see how it was performing with users on. I started Oracle expert and tried to attach to the repository. You need a repository, as this is the only way you can submit the job to monitor the database i.e. through the agents. To my surprise I received an error

XP-21161: Database connection attempt has failed

Error while trying to retrieve text for error ORA-12705

ORA-12705 is ‘invalid or unknown NLS parameter value specified’

I then tried to logon through SQL*PLUS. This was successful.

I decided to check out what NLS_LANG parameter I was using. I opened the windows repository with REGEDIT and found I had lost my HOMEx under the Oracle branch of software. This, I have discovered, as come about due to the installation of a timesheet system called AcTirecs overwriting the Oracle branch.

I had to recreate the HOME. The name for the home can be found by viewing a file in the BIN directory of the executables called oracle.key. So, under HKEY_LOCAL_MACHINE\SOFTWARE\Oracle I created a key HOME2. Within HOME2 I created some basic String settings of ID, NLS_LANG, ORACLE_HOME and ORACLE_HOME_KEY. The ID I gave was 2, which was because I already had 2 other oracle homes 0 and 1. Once I had added this ID I had also to add a key, ID2 under ALL_HOMES and also added Strings NLS_LANG=N/A and PATH being the Oracle home of the Enterprise Manager 9.2.0.1.0

Once I had done this Oracle Expert started. I guess the 'Error while trying to retrieve text' is also fixed but not receiving an error I'm not sure.

I suggest if you get NLS errors on windows check what is set in REGEDIT. You may find part of the repository has been overwritten.