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.