Oracle scene editor

Just to show that I do keep this blog up to date I thought I'd announce that the voting for the next Oracle Scene editor will shortly be opening for all UKOUG members. I am standing for this position being the technical editor for the last 3 yrs

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

Resized your SGA_TARGET too small and found you can’t now start your database.

If you are using a PFILE then just edit it and set the SGA_TARGET to a larger value. But what if you’re using an SPFILE. One possibility is to create the pfile from the spfile edit the pfile, and then either start the database using the pfile and remove the spfile and start the database as normal and the new pfile will be picked up.

The problem arises when the spfile is in an ASM, creating the pfile from this can be a problem. One solution is to create a pfile which calls the spfile in the ASM but after the call to the spfile add an extra line which alters the SGA_TARGET as follows

SPFILE='+DATA1/PROD1/spfilePROD1.ora'
SGA_TARGET=1024M

This pfile can be places in $OH/dbs thus, the next time you start the database this pfile will be run. Alternatively, you could explicitly use the ‘pfile=’ parameter when starting the database thus

Startup pfile=$OH/dbs/initPROD1.ora

Oracle Scene - And finally..

I've become the technical deputy editor of Oracle Scene and we will be holding the first editorial meeting 5th Feb 2007. If you want to contribute any acticles/ comments please let me know.

In the meantime I am going to include my first 'And Finally...' acticale here for your 'friendly' comments.

For my first ‘And finally….’ I would like to talk about the UKOUG Special Interest Groups, but first the introduction. Many of you have already met me as I have been involved in the user group for over 6 years now, but for those who haven’t my name is Neil Jarvis and I’ve been working with the Oracle RDBMS for over 18 years, first as a programmer and then in 1998 as a Database Administrator. In 1999 I attended my first user group conference in Birmingham and in 2000 became a deputy chair of the UNIX SIG under the auspice leadership of David Kurtz. Since 2000 I have presented a few technical papers at the UNIX and DBMS SIGs, help arrange the agenda and chaired some of the UNIX SIGs. I am also on the committee for the forthcoming Northern SIG which will be held in April, somewhere north of Watford (watch this space for updates).

In the last 10 years or so I have been involved/ employed by many different organisations ranging from financial to local authority and retail, all of which held UKOUG membership. In over half of these cases I was surprised to see they were not taking full advantage of their membership. Membership of UKOUG entitles you to access of over 120 UKOUG events all of which are free to the first person and a nominal charge to subsequent attendees. You also have the same access to the annual conference, currently being held in Birmingham. This is a four day event with at least 5 streams running all day. Membership also gives you access to the online resource library which holds, amongst other things, most of the presentations not just at the SIGs but also the conference. The office also sends out an e-bulletin fortnightly with the latest news and reminders for forthcoming events. If this is not enough you also receive 30% off Oracle books and this magazine containing articles on not only business but technical and non-technical areas.

With all these benefits I would like to focus on the Special Interest Groups. The agendas for the meetings have to cater for the views of a large audience and as such you may feel going couldn’t justify taking the time out of the office, as some of the material may not be relevant. In my experience the opposite is true. Whilst you may not feel a presentation of ASM may be relevant for your company right now, the technology will eventually catch you up, and then at that stage your company will have to pay for a course, and ironically, you’ll have to take the time out of the office. But if you attended your free SIGs, that knowledge will be there, in the back of your mind, ready to be accessed. So the next time a SIG comes around don’t think, will this SIG be relevant for my company now? think, are the topics relevant for my company in the future? You must remember your committee will be thinking the same questions as to the appropriateness of the subjects.

And finally, I would like to personally thank David Krutz for all his time and effort he has put into the UNIX SIG over the past 6 years. Without him the UNIX SIG wouldn’t be as successful as it as been. I do hope him all the best in his directorship in UKOUG and that the present committee of UNIX SIG continues in the good work David performed.

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.