Oracle 10g instant client – Hard to make it work

Oracle 10g works fine with oracle 8i clients. SQL plus, toad, they connect fine using the oracle 8i client.

But there are some reasons why it’s a good idea to move to Oracle 10g client.

1) Oracle 10g has an instant client which is very light 254 KB compared to 259MB for the runtime client.
2) Oracle 8i is getting a bit old and won’t be supported anymore.

3) It’s good to have some homogeneity in the IT parc. (at least for Oracle,not for printers but that’s another history)

So, I’ve been testing the instant client a bit because I found it cool.

It install just the basic to connect using TNS or ODBC. It doesn’t install any tools of its own. It means that when you install that client you might have some tools or application of your own to work with.

Things to know when Installing 10g instant client

1) After installing the Oracle 10g instant client, a new environment variable must be created.

Go to control panel> system> advanced tab> Environment variable.

Create a new system variable called TNS_ADMIN.

In the Value field, put the path where your tnsnames.ora and sqlnet.ora will be located.

Note: If you already have a folder called like that from Oracle forms for example, just point to that folder. It makes less work for future maintenance.

2) Very often, if not all the time, after installing the client, I tried to create my first Oracle 10g ODBC.But I got the following error message:

“The setup routines for the Oracle in instantclient10_2 ODBC Driver could not be loaded due to system error code 126″

followed by:

“Could not load the setup or translator library”

Well it’s an error, let’s check Metalink to see what they say about it.

Brilliant! They describe the error exactly as I have it.

Here is the article. You will need a metalink login to get it.

What Oracle say

Cause

This error may occur for the following two reasons.

1. There is not a mfc71.dll and msvcrt.dll located on the pc that the Instant Client is installed on.

2. The directory that mfc71.dll and msvcrt.dll is in is not included in the path environment variable.

and

Solution

To implement the solution, please execute the following steps:

1. Find the mfc71.dll and msvcrt.dll if it on your pc and include the directory in which it is in in the path variable. It is normally found in the C:\WINDOWS\system32 directory. Or copy mfc71.dll and msvcrt.dll to the directory where the instant client is installed.

2. If you have installed the full Oracle Client and ODBC Drivers for version 10.2.0.1 both mfc71.dll and msvcrt.dll are located in the ORACLE_HOME\bin directory

It couldn’t be that easy

Ok. First of all I check if the two dlls are on my test computer. I check ORACLE_HOME\bin

None of them are in it. So it seems the instant client doesn’t install a couple of required dlls. But apparently windows should have it.

If it is, there are 2 solutions:

a) Modify the “path” variable in Control Panel>System>advance tab> environment variable> system variable and add the path where the dll should be. Logically it’s C:\windows\system32.

b) Copy the dlls from C:\windows\system32 to the location specify in the “path” environment described at point a)

OK let’s copy them: I found msvcrt.dll but there was no mfc71.dll in system32. I’m using Windows XP. I’ve tried with Windows 2000 too.

I had a runtime client installed on my computer to manage oracle. I decide to copy them from the bin folder on my computer and into the location specified in “path” on the test computer.

I have to admit at that point that I thought it would work. Well it didn’t.

I finally cleaned up the instant client installation 254KB and installed the runtime client 259MB.

Then I wanted to create my finally working ODBC connection.

Oupsy: There’s no ODBC driver installed with the runtime client.

But this time it’s easy.

On top of that runtime client, make a new installation but this time customized, make sure to use the same home. Locate Oracle 10g ODBC and install it.

Finally, it’s possible to enjoy the new ODBC driver.

Summary

To get Oracle 10g ODBC driver installed and working on your computer:

1) Install Oracle runtime client

2) Install Oracle 10g ODBC driver on top of that using a customized installation.

Check your user passwords

A good tool to monitor your users password is the one developed by Red database security.

It’s called checkpwd.

Download it on there website
On the command line simply type:

C:\checkpwd [oradba_user]/[password]@[tns entry] [your_password _file _location.txt]
On oracle 8i it’s easy but now with Oracle 10g, to connect as sysdba we have to specify for example in SQLPLUS:

SQL>conn sys/[password] as sysdba

This is apparently not taken into account yet and it fails to recognize the command with the extra words “as sysdba”.

The recovery manager – RMAN

Here is one tool that DBA’s will be happy to use.

On oracle 9i it was still having some problem, on Oracle 10.2 it’s working just fine.

Using RMAN, it’s now possible to create some backups strategy with simplicity.

Let’s take the most simple example:

RMAN> backup database; Frankly, if you didn’t know that you might think it’s too easy to be true.

RMAN>backup archivelog all;

Personally I would have allowed RMAN to understand backup all archive log which is more human.

Oops! I’ve just lost a tablespace Oo’

RMAN>restore tablespace SYSTEM01;

Oops! I’m not too sure what’s wrong finally

RMAN>restore database;

RMAN>recover database;

The Recovery Manager also features a direct tape channel provided you can configure it with your provider driver. It’s a bit tricky.

It will check a retention time what you would have set up. I use 15 days.

However, you must make sure you have enough disk space to allow 15 backups living together on the same disk.

That was just a introduction to RMAN. I will come back we advanced commands later when I have a little time.

I will also show how RMAN looks like in the OEM and how it is possible to give the task to someone less knowledgeable in Oracle thanks to the cool graphical interface.

I mean, it does the backups but it still need to be checked on.

To the developer turned DBA – Plan a little bit

So many companies have a project involving a big database. For some reasons they turn to Oracle but they didn’t want to have a DBA helping them…

Because “We already have IT people to do the job”. When a boss is not in the IT business, you can be sure he doesn’t make a big difference between a developer and a DBA.

And there you are! Standing alone in front of this cold server waiting to have Oracle installed on it… And you don’t have a clue!

Or you think it’s easy which is juste the same.

This post is intended to the Developers, system administrators and anybody else who needs to install Oracle on a server.

First of all: Dig a little bit of information.

I’m not going to discuss about the server configuration, it has been told here already.

But anyway, here it is. Use RAID 1 + 0 when you are in doubt.

Usually what will happen is:

Because the IT guy doesn’t have a clue how to install oracle, he will use the wizard.

He will end up with 5 tablespaces and he will quicky return to developing his application.

All the tables will be stored in the same tablespace usr and possibly on the same set of hard disks.

Indulge yourself with a bit of standard.

1) Think about the different module in your application and group your tables accordingly. Put them in different tablespaces.

2) Create a tablespace for your data and a tablespace for your indexes. Do that for each group of tables

3) Separate the indexes and the data on different hard disks

4) Separate the data modules evenly on the different disks.

Let’s consider the following:

I have an application which will have a Financial module, a provider module and a Customer module.

I will create the Tablespaces:

TD_FINANCIAL with a datafile named TD_FIN_[servicename]_01.dbf

TI_FINANCIAL with a datafile named TI_FIN_[servicename]_01.dbf

TD_PROVIDER with a datafile named TD_PROV_[servicename]_01.dbf

TI_PROVIDER with a datafile named TI_PROV_[servicename]_01.dbf

TD_CUSTOMER with a datafile named TD_CUST_[servicename]_01.dbf
TI_CUSTOMER with a datafile named TI_CUST_[servicename]_01.dbf

If you have 3 sets of disks D E and F

Then I would put the datafiles as follow:

D:\oradata\[service_name]\dat\

TD_FIN_[servicename]_01.dbf

E:\oradata\[service_name]\dat\

TI_FIN_[servicename]_01.dbf

E:\oradata\[service_name]\dat\

TD_PROV_[servicename]_01.dbf

F:\oradata\[service_name]\dat\

TI_PROV_[servicename]_01.dbf

F:\oradata\[service_name]\dat\

TD_CUST_[servicename]_01.dbf

D:\oradata\[service_name]\dat\

TI_CUST_[servicename]_01.dbf

Indexes and data are separated on different disks so that the access to the data and the indexes can be done simultaneously thus making better performance.

Also, the different modules are separated on different disks so that 2 persons can work together on different modules without reducing the performances for each of them.

Of course with a more complexe system it gets tricky to have such a perfect model. This is really school demonstration but the idea is to mix up so that the chances of accessing data simultaneously are best.
Finally, notice how the file names are standardized. The idea is to know where your tables are. With this system, it is even possible to shutdown a part of the database, say the customer module, without affecting the others (if it is not too much related, we are still in a perfect world here…)

It is still ok to create the database with the wizard, but discard all the tablespaces, except the system tablespace (that would destroy your DB) and create your own following the standards. It’s very easy to manage that with Toad or DBA Studio.

Now on Oracle 10g, it’s so simple to manage the tablespaces and the datafile, it would be a crime not doing so.