Friday, May 6, 2011

ORAPWD file- recreate, adding new entries, limits, ORA-01996,...

Last few days, i learned a lot about Oracle password file! :-)

What I wanted, is, to increase value of the ENTRIES parameter, from default to some greater value, to have possibility to add a few more users with sysdba privilege. So, before I start to do that exhibition on production databases,  I wanted to be sure that the whole process has been tested!
First, I was not sure, should I shutdown db after change orapw file or not ? Well, with a little help MOS and test DB, the anwer is, NOT !! More precisely, it is necessary to restart the DB only after you created the password for the first time.
Ok, first problem was solved, I already have created orapw files, so I just copied old original files to have backup if something goes wrong.

Then, I changed values 'entries', to test adding some new users.
First, you can see, how many and what users are in the password file:

So, for test, I added some users, and test increasing value of the ENTRIES parameter:

On entries 2, I can put 5 users with sysdba grants, entries 5 --> 5 users, entries 6 --> 9 users, entries 7 --> 9 users.. ??
Well, that was strange!
But, answer is in oracle doc :-)

Proof that a different value of the ENTRIES parameter is ok

"The actual number of allowable entries can be higher than the number of users, because the ORAPWD utility continues to assign password entries until an operating system block is filled. For example, if your operating system block size is 512 bytes, it holds four password entries. The number of password entries allocated is always a multiple of four."

Finally, I wondered whether you can see the current value of the parameter ENTRIES ??
Well, NO! You cannot see the value of the ENTRIES parameter in a view or somewhere else.(v$pwfile_users is only for number of users)
And another important thing at the end:
You can recreate the password file with a larger values for ENTRIES and will lose all the current SYSDBA users!!! You only set password for SYS user, and new users need to be granted from SYS:

Now is really the end!   :-)

Wednesday, March 30, 2011

How to execute sql script against multiple databases?

A friend of mine, ask me that question few days ago... At first moment, I give him some bash script that I have, for connecting to multiple hosts through ssh and than executing some commands, and tell him that he can easily change to connect with sqlplus with some list of connections and than run the sql script.
But, after some time, I tried to find some better way to do this...
After some reading, I found, and off course,  successfully tried this two ways of doing this:

1) EM grid control

In a book "Oracle Enterprise grid control 10g handbook" , in chapter 14 "Database Management in Grid Control", Page 447, we have workshop "run a SQL Script Against Multiple databases", where, in details, is explained how to achieve what we wanted.

preview: Oracle Enterprise grid control 10g handbook

 2) Toad ScriptManager

I will not copy/paste someone else work :-).. Here is a link and detail explanation:

Toad metod 1

3) Toad Project Manager 

And, my favorite, fastest and most elegant way:

Toad metod 2 

So much for this time!

Thursday, March 24, 2011

EM grid control 11g - push agent installation problem

So many databases, so little time! :-)

Recently, I was trying to install em grid agent on target server, but through em grid console, on deploy option, always failed on Preinstall Script..
I tried on another target server, which have same OS (OEL linux), DB versions (, but same thing happened...
I know, of course, that there is another way of installing agent, but I was determined that I can do that install with EM...

After some little digging through logs, I found this:

'/opt/oracle/agent/' is my target agent dir which I specified in em deploy agent form, and I guess that '/agent11g/cfgtoollogs/oui//' is something that deploy script build for install on target... So, how I see, problem is with creating this path and double '//' sign. I tried even without trailing '/' sign on the end of my target dir '/opt/oracle/agent' , but, without success, same thing...

After a 'few short  weeks' comm with Oracle support, they concluded: "Clarified that Push Agent installation was reporting some communication errors may be due to ssh latency and he can install Agent on any server using response file which is simpler and faster"...
Well, I haven't see any comm errors, but...who am I... :-)

So, the workaround is this: to install the Agent using Reponse file by following the document below
When I tried to find additional_agent.rsp file:

The required file (additional_agent.rsp) file is not present in the OMS_Home in your setup.You must download the Agent Software from the location agent_download  and use the additional_agent.rsp file in it for installation.

Here is file, with parameters that I changed:

and than, run installation:

After sucessfull installation, start agent and upload the data on emgrid11g host:

Great, it works, but in EM grid for all db targets there is a message 'Target has encountered metric evaluation errors', for host it is ok. Upload was without errors. Well, now, should only to configure db parameters for dbmsnm user - password :-)

Here we are - working as expected, but, off course, with workaround.. :-)

pssst... I still think that something is wrong with script for deploying agent in em grid,, I don't mean bug, but...:-)

sorry for my english, in a hurry...Cheers!

Tuesday, March 1, 2011

Quick way to prepare environment for DB install

With the emergence of Oracle Linux, I thought that one annoying part of the setup environment to be unnecessary, but ... I was wrong. :-)
We need to edit multiple files, create user, groups, and with each new version of Oracle, this section remains roughly the same.
So over time I made/find/edit/copy/paste/change/supplement a folowing script, it should be run as root, and after that everything is ready for the "runInstaller".
The script in this form meets my needs for fast setting environment, but, of course, everyone can still extend and customize.

Here is a script:

Sunday, February 27, 2011

ORA-29540: class oracle/spatial/util/Adapters does not exist

I'm administering a lot of Oracle DB, and some were created with DBCA, some were cloned from others, some restored from backup and use for testing...So, there are a lot of them, with many different options.
Several of them, developers noticed  this error:
ORA-29540: class oracle/spatial/util/Adapters does not exist, when they want to use some Spatial Java class.

Investigating this, I found on Metalink, Note 400982.1, and Note says that if Oracle Spatial is installed, there are not problems with this error, and cause is missing spatial JAVA CLASS objects!

So, to implement the solution, please execute the following steps: (as Note says)

Install the spatial JAVA CLASS objects as follows:

1.  Connect to the database via SQL*Plus as AS SYSDBA
2.  Issue:
SQL> alter session set current_schema=MDSYS;
3.  Run sdoloadj.sql:
SQL> @?\md\admin\sdoloadj.sql

I solved this problem on a few databases already, and this really works...

But, something else bothers me. If we use this spatial Java class, does loading the Java classes means that we switch to use Oracle Spatial, and thus requiring a valid license ?
In my company, developers uses a lot Oracle Locator, rather than Spatial, because Spatial is a priced option available only with Oracle Enterprise Edition ( quick look the price :-), 17500$/ Processor)

I searched half the internet, but I found nothing except a few sites where people ask the same question ...
Finally, it is not nothing left but with a sword in one hand and the plasma gun in the boot, reach for the ultimate solution: I wrote  MetaLink SR :-)

And so, the answer is as follows:
Yes, we can use and install that Spatial Java classes, the SDO Java classes are loaded with a Locator installation. These Java classes do not require a Spatial license to be used!!

This is my first story, and if you like it, I hope there will  be more to come ..