strange issue 1

[maswien]

I have a windows box have oracle 9i installed. I install another oracle 10G home and create a 10G database.

I didn't touch the existing 9i database, but the application running on the same box that connecting to the 9i database stops working. Give me error like 'can't resolve service name...".

I tried sqlplus or tnsping in DOS window, all connect fine, the application doesn't use DSN to connect, and I can find no place to change this connect information, the only interface to configure the connection only allow me to change the database name, account/password, and it doens't have a test connection button. It's something hard coded to me.

any idea?

 

[KenCunningham]

What about tnsnames.ora? Have you copied the 9i version to the 10g home? The other issue might be that your PATH hits the 10g home first.

I want to be good, is that not enough?

 

[maswien]

Another clue, it actually say ' th microsoft ole db provider error, can resolve serverice name ...".

May be I need to install mdac 2.8?

 

[Thargy]

I would suggest that you de-install all of the windows drivers, and then re-install the 10g only.

That way you can remove version incompatibility issues from the problem. 10g drivers will be backward compatible with 9, but 9i drivers won't necessarily talk to a 10g database.

Regards

T

 

[maswien]

Hi thargtheslayer ,

You mean I deinstall both the 9i client and 10G client on the server?

We can't touch the 9i environment because we have a 9i production database here.

 

[Thargy]

No, I meant that on a client machine (definitely NOT the server) you uninstall all your Oracle, completely. Ideally re-image a blank PC with a known-good build.

Then install 10g2 client from either a CD or internet download. Make sure that the client installs the ole db drivers for windows. That way you have only one version of oracle - guaranteed. Then use the client to talk to the 9i and/or 10g databases on the server, relying on the backwards compatibility of 10g to 'talk' to 9i.

Regards

T

 

[maswien]

Your answer really makes sense to me. But for some reason the application for the 9i database was installed on the database server, this makes things complicated.

After I updated the MDAC which is related to microsoft ole db I guess, the clients can't connect to the database anymore if the 10G listener is running. To make it connected, I have to switch to the 9i listener and the connecting turns very slow.

 

[Thargy]

maswien,

installing the application on the server is just asking for trouble. For example, you have zero possibility of a successful disaster recovery. If the server fails, it's no good starting up your DR server, because the application was torpedoed along with the server. That is a major cock-up that needs immediate action to correct. As for mixing major versions of Oracle on the same server, well, that's just too daft for words.

You absolutely must get the application moved off the Oracle server ASAP. Your backup, recovery and DR are badly compromised at the moment.

Regards

T

 

[maswien]

Thanks a lot!

These issues was because of bad design at the beginning. We already allocate another server to migrate the application to. By that time, the connecting problem will solved as well.

This is just phase I, the phase II is to move the database from the windows platform to unix. The size of the database is about 1.5 TB, so I'm thinking of using transportable tablespace, cause the database is partitioned by date, I can move the files one by one and won't impact the production server badly.

Any suggestion on this?

 

[Thargy]

Going from windoze to unix, transportable tablespaces may be the only option available to you. Expdp and impdp aren't guaranteed across different OS's, unless you dump all the data as XML, which for 1.5 TBytes is ridiculous.

Your migration should be fully scripted and tested. A major issue will be the outage time window available. Do you have an overnight outage? If you don't, then I suggest that you make all the old tablespaces read only (if it's old data nobody will write to it again - correct?). Set up the new instance and database and bring in all the read-only tablespaces. Then when you come to do the real move, you'll only have to do the few most recent tablespaces, which should shorten your outage time.

Don't forget to practice this move, until you can do it all at press of a button. Time how long it takes. When it works successfully, restore everything from backup, and repeat, noting the time taken. Once you've had two clean runs, with known times, you can approach management with an outage request and do it for real.

How does that sound?

Regards

T

 

[maswien]

I found the following article:

http://www.databasejournal.com/features/oracle/article.php/3600931

talking about migrate database using RMAN cross platform. I need do more test like you mentioned and script and time every step.

Thanks a lot!

 

[Thargy]

Well done for taking the time to read up on the subject - many don't.

I deliberately hadn't mentioned the concept of endian-ness to avoid confusing the issue. But it must be dealt with. You could use either RMAN or transportable tablespaces. Your testing should reveal which works best for your particular circumstances.

Whatever you decide, make sure that you press one button and it all happens. Manually firing off one script after another is a recipe for disaster. It really does have to be one press and it all happens, as if by magic.

I wish you well in your endeavours and thank you for the star, they mean a lot to me.

Regards

T