Solved

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

Posted on 2011-09-08
60
1,012 Views
Last Modified: 2012-05-12
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!!
0
Comment
Question by:jmchristy
  • 27
  • 22
  • 8
  • +1
60 Comments
 
LVL 13

Expert Comment

by:khairil
ID: 36504987
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
 

Author Comment

by:jmchristy
ID: 36505119
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
 
LVL 13

Expert Comment

by:khairil
ID: 36505175
Are the service in Windows service applet for the Oracle running?
0
 

Author Comment

by:jmchristy
ID: 36505213
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
 
LVL 13

Expert Comment

by:khairil
ID: 36508671
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
 
LVL 13

Expert Comment

by:khairil
ID: 36508688
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
 

Author Comment

by:jmchristy
ID: 36509759
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
 

Author Comment

by:jmchristy
ID: 36509762
Yes the instance name is PLAT - sorry forgot to confirm that.
0
 
LVL 5

Expert Comment

by:NeilChak
ID: 36511761
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
 

Author Comment

by:jmchristy
ID: 36511780
Thanks - I gave that a try and here is what I received
oracle-error.JPG
0
 
LVL 5

Expert Comment

by:NeilChak
ID: 36511789
Hi, Case matters
Try again with ORACLE_SID in upper case.
0
 

Author Comment

by:jmchristy
ID: 36511807
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
 
LVL 5

Expert Comment

by:NeilChak
ID: 36511830
This type of connection can only be done on the server.  Is this being executed on a remote pc?
0
 

Author Comment

by:jmchristy
ID: 36511836
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
 
LVL 5

Expert Comment

by:NeilChak
ID: 36511863
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
 

Author Comment

by:jmchristy
ID: 36511880
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
 
LVL 5

Expert Comment

by:NeilChak
ID: 36511908
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
 

Author Comment

by:jmchristy
ID: 36512509
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
 
LVL 13

Expert Comment

by:khairil
ID: 36515337
you can use enterprise manager using the account you have created.
0
 
LVL 13

Expert Comment

by:khairil
ID: 36515340
or you can use toad for oracle for quest. it is a nice tool but not free.
0
 

Author Comment

by:jmchristy
ID: 36522081
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
 
LVL 47

Expert Comment

by:schwertner
ID: 36522875
sqlplus "/ as sysdba"

pay attention to the double quotes
0
 
LVL 47

Expert Comment

by:schwertner
ID: 36522893
try also

CONNECT INTERNAL
0
 
LVL 13

Expert Comment

by:khairil
ID: 36523361
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
 
LVL 13

Expert Comment

by:khairil
ID: 36523411
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
 

Author Comment

by:jmchristy
ID: 36523927
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
 
LVL 47

Expert Comment

by:schwertner
ID: 36524054
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
 

Author Comment

by:jmchristy
ID: 36524425
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
 
LVL 5

Expert Comment

by:NeilChak
ID: 36529486
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
 

Author Comment

by:jmchristy
ID: 36529570
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 5

Expert Comment

by:NeilChak
ID: 36529611
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
 

Author Comment

by:jmchristy
ID: 36529693
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
 
LVL 5

Expert Comment

by:NeilChak
ID: 36529867
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
 

Author Comment

by:jmchristy
ID: 36529919
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
 
LVL 5

Expert Comment

by:NeilChak
ID: 36530282
Hmnn what are the last 100 or so lines in the alert_plat.log file?
0
 

Author Comment

by:jmchristy
ID: 36532047
I see a platALRT.log

See attached
platALRT.LOG
0
 
LVL 5

Expert Comment

by:NeilChak
ID: 36532079
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
 
LVL 5

Expert Comment

by:NeilChak
ID: 36532123
Actually it more looks like a crash and restart...
Beginning crash recovery of 1 threads
0
 

Author Comment

by:jmchristy
ID: 36532217
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
 
LVL 5

Expert Comment

by:NeilChak
ID: 36532243
Try again with with the host string of PLAT.COMPANY.COM
0
 
LVL 5

Expert Comment

by:NeilChak
ID: 36532269
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
 

Author Comment

by:jmchristy
ID: 36532292
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
 
LVL 5

Assisted Solution

by:NeilChak
NeilChak earned 500 total points
ID: 36532470
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
 
LVL 5

Expert Comment

by:NeilChak
ID: 36532488
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
 
LVL 5

Expert Comment

by:NeilChak
ID: 36532498
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
 

Author Comment

by:jmchristy
ID: 36532499
Thanks slot, I will give this a try in the morning!
0
 

Author Comment

by:jmchristy
ID: 36535552
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
 
LVL 5

Assisted Solution

by:NeilChak
NeilChak earned 500 total points
ID: 36535947
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
 

Author Comment

by:jmchristy
ID: 36536248
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
 
LVL 5

Expert Comment

by:NeilChak
ID: 36536255
Working on such a script.  Will upload in a bit.
0
 
LVL 5

Accepted Solution

by:
NeilChak earned 500 total points
ID: 36536549
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
 

Author Comment

by:jmchristy
ID: 36537772
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
 

Author Comment

by:jmchristy
ID: 36537776
Thanks for all your help Neil, I think this will get it done!
0
 

Author Comment

by:jmchristy
ID: 36537863
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
 
LVL 5

Assisted Solution

by:NeilChak
NeilChak earned 500 total points
ID: 36537904
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
 

Author Comment

by:jmchristy
ID: 36537980
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
 
LVL 5

Expert Comment

by:NeilChak
ID: 36538083
rename might be a bit easier but you should be able to open them as a csv file regardless.
0
 
LVL 5

Expert Comment

by:NeilChak
ID: 36538088
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
 

Author Comment

by:jmchristy
ID: 36538089
Nice, thanks Neil

You da man!!!!!  I could have never of done this without your help.
0
 

Author Closing Comment

by:jmchristy
ID: 36538095
NeilChak rocks!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many‚Ķ
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now