How to retrieve data from Oracle 8.5i database running on NT Server 4.0

I inherited an old server box running some software that is Oracle based.  I need to pull that data out of Oracle and dump it into a database or spreadsheet.  My issue is, I have no idea how to connect to the Oracle database.  My other issue is, I know absolutely nothing about Oracle.

I did a little bit of research and attempted to download some tools to make the connection but I keep getting road blocks, I've tried 2 pieces of software

The Oracle Instant Client - I receive this error
TSN:PROTOCOL ADAPTER ERROR

DbVisualizer Free - I receive this error
Io Exception: Invalid connection string format, a valid format is: "host:port:sid"

I can locate my tnsnames.ora file on the Oracle server and get the information from there.  My hang up seems to be with this driver that is needed that both of these applications want, and setting that up.

If there is an easier way to do this, I'm all ears. Any help is greatly appreciated!!
jmchristyAsked:
Who is Participating?
 
NeilChakConnect With a Mentor Commented:
Hi here you go.
2 scripts to place in your c:\sql folder
1. export_csv.sql
 export-csv.sql
2. gettable.sql
 gettable.sql

Run the export_csv.sql file using the start command in sqlplus.
Exit sqlplus
Edit the output file c:\temp\getall.sql
remove the lines for tables you do not want.
Then run the output file using the start command in sqlplus.

Then in the c:\temp directory will appear one file per table named after the table.

You may have to edit and play around with the data to get it to load properly.

0
 
khairilCommented:
Hi,

I think you having problem with listener. Most likely it's happen when you change the IP address of the box (or having DHCP enable on server). Check your IP address and compare with entry in tnsnames.ora and listener.ora

You can find those files in <oracle installation path>\<home>\NETWORK\ADMIN

You can edit those file using notepad. Just replace host value with the new IP. Make sure you make backup first before edit.

After setting that out then you can use the imp tools to dump data.
0
 
jmchristyAuthor Commented:
I'm not trying to change the IP address of the server, it's a static IP address and I'm just trying to connect to it to pull data off of it.

I did check both of those files and they have the correct HOST IP address
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
khairilCommented:
Are the service in Windows service applet for the Oracle running?
0
 
jmchristyAuthor Commented:
There are 7 services for Oracle, 2 of them are running
OracleOraHome81TNSLictener
OracleServicePLAT

The others are all OracleOraHome81Agent, ClientCache, CMAdmin, CMan, DataGatherer which are all not running
0
 
khairilCommented:
Hi,

Just like to confirm for "TSN:PROTOCOL ADAPTER ERROR " is the code is ORA-12560

You can get the error code by this method:
1. Run command prompt.
2. Type command below:
sqlplus / as sysdba

Open in new window

3. See if any error return, it will be some like ORA-XXXXX, post it here

I'm using 11g, services list are different compare to 8i.. but suppose you should have one more service named begin with "OracleService<Computer Name>".

Try run all other services, it looks like your listener already up but not your database instance.

Sometimes this can cause by missing PATH environment to indicate Oracle Home, try this tools to switch Oracle Home, http://www.dbmotive.com/products/oracle-locator-express/.


0
 
khairilCommented:
oppss.. sorry, I think you instance name is PLAT? emm..if it is then that makes instance of database already running but listener not connect to that instance.

Try set OracleServicePLAT to run automatically, so does the TNS service.

and set the Oracle Home using the tools I linked you above, and run the sqlplus command again.
0
 
jmchristyAuthor Commented:
I downloaded the instaclient from Oracle's website for version 10.1.0.5.0 - this is what a few others around the web have said to try to connect to an 8.1 database.

I run the SQLPLUS.EXE and get a username/password prompt.  I enter my credentials and get

ORA-12560:  TNS:protocol adapter error

The OracleServicePLAT is set to run automatically already.  When I try to run your command above I receive this
sqlplus-error.JPG
0
 
jmchristyAuthor Commented:
Yes the instance name is PLAT - sorry forgot to confirm that.
0
 
NeilChakCommented:
Please issue the following command then try to connect with sqlplus
in the Command prompt issue

SET ORACLE_SID=PLAT
sqlplus / as sysdba

See if this works if not post the error message.

Once connected you can set passwords to gain access though network connections.
0
 
jmchristyAuthor Commented:
Thanks - I gave that a try and here is what I received
oracle-error.JPG
0
 
NeilChakCommented:
Hi, Case matters
Try again with ORACLE_SID in upper case.
0
 
jmchristyAuthor Commented:
I tried again with uppercase as you posted - same results.  I couldn't tell if there was a space after the / as or /as so I tried that twice.
any other suggestions?
oracle-error-2.JPG
0
 
NeilChakCommented:
This type of connection can only be done on the server.  Is this being executed on a remote pc?
0
 
jmchristyAuthor Commented:
No, I am trying this from the server thru the command prompt.  This is an old version of Oracle, version 8 on NT Server 4.0.  I'm not sure if that matters or not.
0
 
NeilChakCommented:
Ahh it's been a while here is the syntax to connect on Oracle 8i

http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:670117794561
0
 
jmchristyAuthor Commented:
Ok great - so using that - How can I dump the data in the database tables into a .CSV file, SQL table or spreadsheet?

As I said in my intro, I know absolutely nothing about logging into Oracle and gathering data.  Your help is much appreciated!
0
 
NeilChakCommented:
Once you can get to an SQL prompt you can issue comands.

SQL>  CREATE USER JMCHRISTY IDENTIFIED BY ANY_PASSWORD ;

SQL>  GRANT DBA TO JMCHRISTY ;

Then you should be able to connect using the account JMCHRISTY with password ANY_PASSWORD useing the connnection string
"host:port:sid"
where host = IP Address
  PORT = 1521 (or what ever is shown in the TNSNAMES file)
  SID = PLAT

then your tools should work.

First step though is to know the database is up and you have an account with a valid password.
Remote connection all must have a valid password that is known.

Gotta go for now but best of luck.
0
 
jmchristyAuthor Commented:
Ok i was able to logon and create an account - now I just need to find the right tool to connect to an Oracle 8 server.  Any suggestions?
0
 
khairilCommented:
you can use enterprise manager using the account you have created.
0
 
khairilCommented:
or you can use toad for oracle for quest. it is a nice tool but not free.
0
 
jmchristyAuthor Commented:
SQL Enterprise Manager?  I did try that but it's not registering the server.

I imagine I'd have to create some kind of ODBC connection to the Oracle server first right?

Thanks for staying with me on this khairil, I appreciate it!  :)
0
 
schwertnerCommented:
sqlplus "/ as sysdba"

pay attention to the double quotes
0
 
schwertnerCommented:
try also

CONNECT INTERNAL
0
 
khairilCommented:
if you need to have graphical tools, download this from Oracle, it is free, http://www.oracle.com/technetwork/developer-tools/sql-developer/overview/index.html

I use that during my training for OCA, it quite good.
0
 
khairilCommented:
you can also try this, it is open source and free, http://www.sqltools.net/

Personal experience on this - none as I have Oracle EM and Oracle Developer, but from the features it can do a lot.
0
 
jmchristyAuthor Commented:
I tried that developer tools from Oracle and it said that the database version is not supported.  I tried some older versions but a bunch of them wouldn't start, I would get an error "the application was unable to start 0xc0000018"

I did give the SQL tools a try and it wouldn't connect to the server either.

I may have hit a dead end!
0
 
schwertnerCommented:
Check if the Listener is up and running:

c:> lsnrctl status

If the listener doesnt worl go to listener.ora and try to put there the actual IP of the computer. Possibly there is the old IP.
Backup all files before changing them.

Listener and the Oracle services are the most important.
Also no firewalls and antiviruses - they will prevent login.
0
 
jmchristyAuthor Commented:
Ok, it appears that the listeners are up and running.  Says Services Summary
PLAT has 2 service handler(s)
PLSExtProc has 1 service handler(s)
ORACLE has 1 service handler(s)

There's no antivirus, or firewall client installed.  I did mention that this is Oracle 8.1 on Windows NT 4.0 right?  I may need to find an older database browser to be able to connect to it.
0
 
NeilChakCommented:
Hi,  
You will need an old oracle client to connect to 8i and old tools to use the client.
On the server is an old client allready installed so you can use it to connect as you have allready done.

Plan 1:
You can export the data into a flat file and import it into a newer version of oracle that you can install on another machine for free.  On the server you can use the exp program to get all the data into a .dmp file.  Then using that file you can load it onto an Oracle XE database you can install on another machine.  Then you can use your modern tools against the XE database. I have all the info to do the exports and imports from 8i into 10g or 11g XE databases

Plan 2:
Or you can find old tools and install them on the server and use the existing old client on the server.  

Plan 3:
Or you can install and configure an old client on a PC and use Old tools with that client.  I have an old client if you want it.

Let me know which if any of the these plans you wish to follow and I can provide the scripts and instructions as I have time.
0
 
jmchristyAuthor Commented:
Ok, I think Plan 2 might work.  I found a copy of Oracle SQL*Plus installed on the server, release 8.1.5.0.

Now when I start that up, I get a Username and Password which I'm assuming I use the ones that you had me create earlier with these commands.

SQL>  CREATE USER JMCHRISTY IDENTIFIED BY ANY_PASSWORD ;
SQL>  GRANT DBA TO JMCHRISTY ;

There is also a host string field, that I'm not sure what to put in.  I tried PLAT (receive an error ORA-03113: end of file on communication channel, and the Host name of the PC (receive ORA-12514: TNS:Listener could not resolve service_name given in connect descriptor)

I have no problems sending you the tnsnames.ora or listener files if you'd like to see them.  I could even try to load a different piece of software on that server to try to connect to the oracle database if you don't think we should be trying the SQLplus that's on there
sqlplus-errors.JPG
0
 
NeilChakCommented:
In the 8.1.5 version of SQLPLUS the host string would be the entry for the PLAT database in the TNSNAMES file. You may have more than one tnsnames.ora file on the server.
Take a look at tns names and look for the entry for the PLAT database to see what it's connection name is.
IE:  in the below example LOCALXE is the connection name.

LOCALXE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA = (SERVER = DEDICATED)  (SERVICE_NAME = XE)
    )
  )
0
 
jmchristyAuthor Commented:
I checked, I do have 2 copies of the TNSNAMES file on the system but one of them is in the samples directory.

There are 2 instances in the TNSNAMES file that have SERVICE_NAME = PLAT

PLAT.COMPANY.COM and PLS.COMPANY.COM

I have attached a copy that I changed to text to see if I got it right.  I tried the username/password I created earlier with the host string of PLAT.COMPANY.COM and I tried PLS.COMPANY.COM.  Both times I got that ORA-03113: end of file on communication channel error
tnsnames.txt
0
 
NeilChakCommented:
First verify the hostname
In DOS on the server does

ping symapps1

show a connection?

If so then
also in dos enter this command.

tnsping PLAT.COMPANY.COM

and see what it says it should show the connection files and info used to attempt a connection.

Upload the results.
0
 
jmchristyAuthor Commented:
Yes, when I ping symapps1 it responds to the correct IP address.

tnsping gives the following

TNS Ping Utility for 32-bit windows: version 8.1.5.0.0 - Production on 13-SEP-11 10:49:53

Attempting to contact (ADDRESS=(PROTOCOL=TCP)(HOST=symapps1)(PORT=1521))
OK (30 msec)
0
 
NeilChakCommented:
Hmnn what are the last 100 or so lines in the alert_plat.log file?
0
 
jmchristyAuthor Commented:
I see a platALRT.log

See attached
platALRT.LOG
0
 
NeilChakCommented:
I see the database was down and started up at  10:37am your time.
Tue Sep 13 10:37:38 2011
Completed: alter database open

So maybe your connection will work now.  From the log it looks like it was a an automated restart of the database.
0
 
NeilChakCommented:
Actually it more looks like a crash and restart...
Beginning crash recovery of 1 threads
0
 
jmchristyAuthor Commented:
I actually just rebooted at that time, thinking maybe I had to reboot the services to get some of those changes I made with my new username to apply.

I did have to do a "hard" reset by pushing the reset button on the server :/
0
 
NeilChakCommented:
Try again with with the host string of PLAT.COMPANY.COM
0
 
NeilChakCommented:
A popup blocker can cause the end of communication channel error also.  So you may wish to check for any installed popup blockers on the server.  
0
 
jmchristyAuthor Commented:
It's an NT Server 4.0, did they have those back then? :)

I did check and didn't see anything on there, there is an old version of enterprise mcafee.  It's just virus software, no firewall or malware capabilities.
0
 
NeilChakConnect With a Mentor Commented:
Ok to summarize
 You wish to pull out csv data from tables.
   sqlplus can do this see :  http://stackoverflow.com/questions/643137/how-do-i-spool-to-a-csv-formatted-file-using-sqlplus
 
However, network communication fails with end of channel error so cannot use the GUI version of SQLPLUS.

So you could simply use the dos version and connect with local connections.

For example:
 
SET ORACLE_SID=PLAT
sqlplus
SQL*Plus: Release 8.1.5.0.0 -  Production on Tue Sep 13 16:46:21 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Enter user-name: JMCHRISTY 
Enter password: ANY_PASSWORD

Connected to:
Oracle Database 8i Enterprise Edition Release 8.1.5.0.0 - 32bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

Open in new window


You can query out the owner in oracle

Select distinct owner from dba_tables;

You can query out the table names for an owner (schema in oracle lingo)

Select table_name from dba_tables where owner='WHATEVER OWNER IT IS'

Then create scripts to get the CSV data out using the web refrence above for a given table  using

Select * from owner.table_name

With the spool and settings indicated in the link above.

0
 
NeilChakCommented:
By the way to start spooling (sending data to a file)

spool c:\temp\datafile.csv

to stop then issue

spool off

You can do this for each table to create separate files.
0
 
NeilChakCommented:
Here is a refrence for SQLPLUS version 8.1.5 http://www.cs.umbc.edu/portal/help/oracle8/server.815/a66735.pdf

Its not too difficult to use but may have to look a few things up.
0
 
jmchristyAuthor Commented:
Thanks slot, I will give this a try in the morning!
0
 
jmchristyAuthor Commented:
Ok - I was able to finally get connected using SQL*Plus as well as the command prompt

I was still not getting connected when I first tried, I found this article on the error I was getting and made the change to the sqlnet.ora file specified here
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:424289000563

Now I'm connected!  But those commands you provided aren't working, I don't know the table names.  I guess now that I have access thru the SQL*Plus interface, I can try one of those scripts from that link you provided showing how to pull data out into a .CSV file.

Do I just copy those scripts into a .bat or .vbs file and load them into SQL*Plus?
0
 
NeilChakConnect With a Mentor Commented:
Hi,
  Table names can be gotten and then you can use the table names to get the data for each table.  To get the list of all table names you can use this command at the sql prompt

 
select owner, table_name
from dba_tables
where owner not in ('SYS','SYSTEM','CTXSYS','DISCOVERER_DEV','DISCOVERER_PROD','MDSYS',
  'ORDPLUGINS','ORDSYS','OUTLN','PERFSTAT','WMSYS','XDB',
  'DBSNMP','EXFSYS','SYMAN','ORACLE_OCM')
order by owner, table_name;

Open in new window


The above sql will list all owners and table names for tables not included as part of the normal distribution of tables sent as part of the oracle system.
Probably the tables your are interested will be shown in the resulting list.

You can do this query to see how many rows are in a given table by substituting the owner and table_name from an entry from the results of the above query.

Select count(*) from owner.table_name

To run scripts in SQLPLUS this is the method that I use.
1. Create two directories C:\SQL and C:\TEMP
2. Use notepad or other text editor to create files in the C:\SQL directory with a .SQL ending.
3. To run a script I typically do this in the SQLPLUS prompt

SQL> spool c:\temp\results.txt
SQL> start c:\sql\script_to_run.sql
.....results
SQL> spool off

See pages 3 to 7 in the sqlplus manual for more information on the commands in sqlplus.  The entire manual is only 24 pages long.
0
 
jmchristyAuthor Commented:
Ok I ran that query you provided and I got 209 rows out of it, that shows all the table names.  That's a lot of tables!

Can I just run a script to dump all that data into a .CSV file?
0
 
NeilChakCommented:
Working on such a script.  Will upload in a bit.
0
 
jmchristyAuthor Commented:
Ok I did that and got the not spooling currently message....I obviously forgot to run that spooling command :)

It took about 3 hours to complete, I'll run that command and give it another go
0
 
jmchristyAuthor Commented:
Thanks for all your help Neil, I think this will get it done!
0
 
jmchristyAuthor Commented:
Ok, I'm now getting Illegal spool file name: c:\temp\PLATEAU-MICROSOFTDTPROPERTIES  .CSV (bad character) no rows selected

I'm doing the following

start c:\sql\export-csv.sql    -  it then says 209 rows selected
I exit SQL plus
Go back into SQL plus
start c:\temp\getall.sql

What is the gettable.sql for?
0
 
NeilChakConnect With a Mentor Commented:
gettable get's the individual tables identified.
for example in the getall sql it calls the gettable script for each table to export.

Sorry about the space before the .CSV I thought that might not happen.
the line in the gettable script

spool c:\temp\&&1-&&2 .csv

Causes this you can try without the space

spool c:\temp\&&1-&&2.csv

It may work better.
0
 
jmchristyAuthor Commented:
Cool, ok now i'm getting the files dumping into the temp directory with the table names
Looks like a bunch of file names with the dbname-tablenamecsv.lst

I can just rename those to .CSV right?  or I can work with those .LST files just the same?
0
 
NeilChakCommented:
rename might be a bit easier but you should be able to open them as a csv file regardless.
0
 
NeilChakCommented:
Oh you may need to remove some lines from the start and or end of the files as well as possibly join lines that are broken by carrage returns in the middle of the lines.

Sorry this is not the best way to do a CSV export but it does work.
0
 
jmchristyAuthor Commented:
Nice, thanks Neil

You da man!!!!!  I could have never of done this without your help.
0
 
jmchristyAuthor Commented:
NeilChak rocks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.