Restart required when editing oracle tnsnames.ora?
I am currently trying to export a dump file from oracle 10G which will be imported into an oracle 9i instance on another server. There appears to be a bug in SQLDeveloper which chops the last column off each table when an SQL insert or SQL*loader file is generated.
The post at http://forums1.itrc.hp.com/service/f...hreadId=957611 says that this is possible by editing the tnsnames.ora file on the 9i server to refer to the 10g server and then use the exp on the 9i server like so: exp user/passwd@10g_connect_string
Presumably this is a valid approach to performing this data transfer, if not could someone please offer details as to why it's not?
The reason for this post is because the oracle (9i) instance into which i want to import the data has a number of other database instances and some other members of the development team are performing ongoing operations which i absolutely cannot interrupt or affect. My question, therefore, is: Is it safe to edit tnsnames.ora as outlined above, without having to restart oracle/interrupt operations on the other database instances/have any of the other instances know about the change? To put it another way, what are the consequences and required steps to perform after a tnsnames.ora edit in order to get the dbms to recognise it? Will saving my edits result in the exp command detailed above working correctly?
Any help much appreciated,
9i exp on a 10g oracle instance
thanks for the advice. Network bandwidth is likely to cause some difficulty but currently if i can get it working i'd be happy.
Regarding performing the export on the 10G server, presumably you mean using the 9i version of exp (i've found a few forums on the Web where people say you must use the exp from the earlier version when going backwards, e.g. from 10g to 9i)?
If so, can you give me any pointers on the steps required to get the 9i exp command working on the 10g server?
I tried just copying over the 9i bin and lib directories and setting the LD_LIBRARY_PATH variable to point to the copied lib directory (because i got a 'shared library libclntsh.so.9.0 not found' error when running the exp command originally.
I realise the above is probably missing several steps or is naive in its approach, so could you tell me if there's additional directories i need to copy over or generally if its possible to port 9i's exp over to the 10g server without having to install an entire 9i instance?
Many thanks for your help so far,
10G to 9i exp/imp success
FYI, i followed your advice and everything worked smoothly. On the 9i server, I edited tnsnames.ora to add a connection descriptor for the database on the 10G server, which caused no problems with the other tablespaces as i was fearing.
Then, as suggested and still on the 9i server i issued the command 'exp username/password@TNSENTRY ' , where are the basic exp options such as file=, etc. and TNSENTRY is the name given to the connection descriptor in tnsnames.ora, i.e. TNSENTRY = ( DESCRIPTION = (ADDRESS_LIST= .. ) (CONNECT_DATA= ... ) ).
That led to the 10G data being exported to a dump file on the 9i server, which could be imported as normal using the 9i version of the imp utility.
Thanks very much for all your help, it's very much appreciated.