why oracle 8.0.5 client can't connect to oracle 9i db
A reader, September 25, 2002 - 8:34 pm UTC
Thank you, Tom. That is very helpful.
However, I do have another client 8.0.5.0.0, it can connect to 8.1.7 database, but not 9i, here is the error, it is not tnsnames related because I did use 8i client was able to connect to this 9i database with the same tnsnames.ora entry, plus I was able to use tnsping fine.
C:\Oracle\Ora81\BIN>tnsping dows
TNS Ping Utility for 32-bit Windows: Version 2.3.4.0.0 - Production on 25-SEP-02
19:39:45
Copyright (c) Oracle Corporation 1995. All rights reserved.
Attempting to contact (ADDRESS=(PROTOCOL=TCP)(HOST=ausdfsdpast01)(PORT=1521))
OK (230 msec)
SQL*Plus: Release 8.0.5.0.0 - Production on Wed Sep 25 19:34:31 2002
(c) Copyright 1998 Oracle Corporation. All rights reserved.
Enter user-name: vvvv/vvvv@dowp
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production
With the Partitioning option
JServer Release 8.1.7.3.0 - Production
xxxx@DOWP> connect xxxx@dows
Enter password:
ERROR:
ORA-12203: TNS:unable to connect to destination
September 25, 2002 - 9:19 pm UTC
12203, 00000, "TNS:unable to connect to destination"
// *Cause: Invalid TNS address supplied or destination is not listening.
// This error can also occur because of underlying network transport
// problems.
// *Action: Verify that the service name you entered on the command line
// was correct. Ensure that the listener is running at the remote node and
// that the ADDRESS parameters specified in TNSNAMES.ORA are correct.
// Finally, check that all Interchanges needed to make the connection are
// up and running.
tnsping doesn't prove that you can connect. your SID or SERVICE it probably wrong in the tnsnames entry.
tnsping only shows that "there be a listener on that port". It does not show that "the database you want that listener to hook you up with exists and is up"
tammy pickett, September 25, 2002 - 10:48 pm UTC
Tom, I totall understanding what you are talking about.. However the oracle 8.1.7.0.0 client is able to connect to the same 82 db: dows....
C:\>cd oracle\ora817\bin
C:\Oracle\Ora817\bin>sqlplus xxxx@dows
SQL*Plus: Release 8.1.7.0.0 - Production on Wed Sep 25 21:38:48 2002
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Enter password:
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
in both 8.0.5 and 8.1.7 client tnsnames file are as following: ifile=H:\Network\Admin\tnsnames.ora
which H drive is the shared drive I use. so it means that both TNSNAMES.ORA files are the same; also sqlnet file are as following:
LOG_DIRECTORY_CLIENT = d:\Apps\Ora81\network\log
LOG_FILE_CLIENT = sqlnet.log
LOG_DIRECTORY_SERVER = d:\Apps\Ora81\network\log
LOG_FILE_SERVER = sqlnet_server.log
TRACE_DIRECTORY_CLIENT = d:\Apps\Ora81\network\TRACE
TRACE_FILE_CLIENT = sqlnet_client.trc
TRACE_DIRECTORY_SERVER = d:\Apps\Ora81\network\TRACE
TRACE_FILE_SERVER = sqlnet_server.trc
# ---------------------------------------------------------
TNSPING.TRACE_DIRECTORY = d:\Apps\Ora81\network\TRACE
TNSPING.TRACE_LEVEL = OFF
# ---------------------------------------------------------
NAMES.DIRECTORY_PATH = (TNSNAMES)
SQLNET.AUTHENTICATION_SERVICES = (BEQ, NTS)
SQLNET.EXPIRE_TIME = 10
# ---------------------------------------------------------
# TRACE_LEVEL_SERVER = USER
# TRACE_LEVEL_SERVER = ADMIN
# TRACE_LEVEL_SERVER = SUPPORT
# ---------------------------------------------------------
# TRACE_LEVEL_CLIENT = USER
# TRACE_LEVEL_CLIENT = ADMIN
# TRACE_LEVEL_CLIENT = SUPPORT
# TRACE_UNIQUE_CLIENT = ON
AUTOMATIC_IPC = OFF
TRACE_LEVEL_CLIENT = OFF
names.directory_path = (TNSNAMES)
# names.default_domain = world
# name.default_zone = world
why client 8.0.5.0.0 can't connect while client 8.1.7.0.0 is able to connect?
C:\Oracle\Ora81\BIN\plus80.exe
SQL*Plus: Release 8.0.5.0.0 - Production on Wed Sep 25 21:48:4 2002
(c) Copyright 1998 Oracle Corporation. All rights reserved.
Enter user-name: xxxx@dows
Enter password:
ERROR:
ORA-12203: TNS:unable to connect to destination
C:\>cd oracle\ora817\bin
C:\Oracle\Ora817\bin>sqlplus xxxx@dows
SQL*Plus: Release 8.1.7.0.0 - Production on Wed Sep 25 21:38:48 2002
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Enter password:
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
September 26, 2002 - 7:24 am UTC
first -- try it without ifile, ifile is not a documented supported technique.
Second, lets see the tnsnames.ora entry, perhaps you are using some syntax that was new with 8i and not available in 8.0
Also, try it like this:
sqlplus 'scott/tiger@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=aria-dev)(PORT=1529)))(CONNECT_DATA=(ORACLE_SID=ora9i)))'
where you put the tnsnames.ora entry right in there -- and lets see the cut and paste of that from 8i and 80
still not working
tammy pickett, September 26, 2002 - 10:44 am UTC
xxxx:xxxx> connect xxxx/xxxx@(DESCRIPTION=(ADDRESS_LIST =(ADDRESS =(PROTOCOL = TCP)(HOST = ausdf
sdpast01)(PORT = 1521)))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = DOWS)));
Invalid option.
Usage: CONNECT <username> [AS SYSDBA|SYSOPER]
Tom: I am more confused when I connect use this tnsnames.ora entry through 8.0.5 client, I got above error.But this client is able to connect to other 8i database no problem.
also this 9i database is able to be connected through 8i client.
also if I connect directly by using database aliase, I got following error, I am going to send you with email since I can't paste here.
September 26, 2002 - 11:32 am UTC
sqlplus 'scott/tiger@(.......)'
one line, no spaces
A reader, September 26, 2002 - 12:03 pm UTC
tom: actually command line sqlplus exeucte plus33.exe,not plus80.exe. I can't show you the plus80.exe error if use command line.
c:\orant\bin sqlplus.exe
Enter user-name: cccc/dddd@(DESCRIPTION=(ADDRESS_LIST =(ADDRESS =(PROTOCOL = T
CP)(HOST = ausdfsdpast01)(PORT = 1521)))(CONNECT_DATA =(SERVER = DEDICATED)(SERV
ICE_NAME = DOWS)))
Error accessing package DBMS_APPLICATION_INFO
ERROR:
ORA-06550: line 1, column 40:
PLS-00553: character set name is not recognized
ORA-06550: line 0, column 0:
PL/SQL: Compilation unit analysis terminated
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
ERROR:
ORA-06550: line 1, column 29:
PLS-00553: character set name is not recognized
ORA-06550: line 0, column 0:
PL/SQL: Compilation unit analysis terminated
September 26, 2002 - 12:11 pm UTC
well, we know that plus33 won't work.
if you have the 8.0 client -- why don't you have the 8.0 character mode??????
A reader, September 26, 2002 - 2:36 pm UTC
DOWS:cccc> SELECT VALUE FROM NLS_database_parameters where parameter = 'NLS_CHARACTERSET';
VALUE
----------------------------------------------------------------------------------------------------
AL32UTF8
Tom:
I did connect through plus33 to the 9i database, found out the characterset for our 9i database. so why you say that oracle 805 client doesn't have correct character set?
September 26, 2002 - 2:48 pm UTC
plus33 is 7.x code.
That will not work reliably, it is most definitely not supported. There are tons of known issues with it.
I don't remember saying the 805 client doesn't have the correct character set -- where did I say that?
Having a similar problem with Oracle9i Release 1
K Kishore, September 27, 2002 - 12:47 pm UTC
Tom,
We had a similar issue with Oracle9i Release 1. We had two
machines with One machine having Oracle 8.0.5 client and
another one having Oracle 8.1.7 client.
When we try to connect to the Oracle9i Rel 1 database from
machine with 8.0.5 cleient, we get the same error.
But when we try to connect using other machine with 8.1.7
client, we can connect successfully.
In both cases tnsnames entry were exactly same.
Does this mean that Oracle8 client are not certified for
Oracle9i?
September 27, 2002 - 2:02 pm UTC
I no longer have an 805 instance, but using 817/806
$ setenv TNS_ADMIM /export/home/oracle9i/network/admin
$ sqlplus scott/tiger@ora9i
SQL*Plus: Release 8.1.7.0.0 - Production on Fri Sep 27 14:00:23 2002
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.0.1.2.0 - Production
With the Partitioning option
JServer Release 9.0.1.2.0 - Production
scott@ORA9I.WORLD> Disconnected from Oracle9i Enterprise Edition Release 9.0.1.2.0 - Production
With the Partitioning option
JServer Release 9.0.1.2.0 - Production
$ dev806
$ !sql
sqlplus scott/tiger@ora9i
SQL*Plus: Release 8.0.6.0.0 - Production on Fri Sep 27 14:00:36 2002
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.0.1.2.0 - Production
With the Partitioning option
JServer Release 9.0.1.2.0 - Production
scott@ORA9I.WORLD>
it works, i'm not aware of any big issue in this regards, no
Assumption
K Kishore, September 30, 2002 - 8:24 pm UTC
Based on this outcome, is it fair to say that the minimum
version of Oracle client required to connect to Oracle9i
is Oracle 8.0.6?
October 01, 2002 - 9:48 am UTC
9iR2 yes, 9iR1 works with 7.x
Connecting Oracle8i server from Oracle 9i Client
Nikhil Saxena, May 20, 2003 - 2:13 am UTC
I have a oracle8i database i am trying to connect to it using oracle 9i client, it is giving me error as TNS could not resolve service name.
The entry is there in tnsname.ora
May 20, 2003 - 9:40 am UTC
then the entry is wrong. it works fine, it works the same as 8i connecting to 8i or 9i to 9i or 8i to 9i or 9i to 8i.
segmentation fault on 8.1.7.4
A reader, July 06, 2003 - 5:22 pm UTC
We had cloned an Oracle Apps 11.5.7 , 8.1.7.4 database on the same box Sun Solaris. The original env. GOLD, and the new env. DEV2.
Checked the new Apps DEV2, and all worked except the users other than the oracle can not use sqlplus to access the database and got as issued sqlplus: SEGMENTATION FAULT (coredump). The oracle account has no the problem. The users have had no problem to access the GOLD database from the GOLD env.
The stack trace created on the core dump, which was shown the privilege problem on the sqlplus:
---------------------------------
$ gdb $ORACLE_HOME/bin/sqlplus core
GDB is free software and you are welcome to distribute copies of it
under certain conditions; type "show copying" to see the conditions.
There is absolutely no warranty for GDB; type "show warranty" for details.
GDB 4.15.1 (sparc-sun-solaris2.4), Copyright 1995 Free Software Foundation, Inc...
/oraclebase_dev2/product/8.1.7/bin/sqlplus: Permission denied.
Core was generated by `sqlplus'.
Program terminated with signal 11, Segmentation fault.
#0 0xff3d04d0 in ?? ()
(gdb)
(gdb) q
I checked:
$ ls -l /oraclebase_dev2/product/8.1.7/bin/sqlplus
-rwxr-x--x 1 oracle dba 842772 Jun 2 03:44 /oraclebase_dev2/product/8.1.7/bin/sqlplus
The privilege is the same as the GOLD's
Why do the users have the "Permission denied" on the sqlplus?
TIA
Sean
followed up "segmentation fault..."
Sean, July 06, 2003 - 8:14 pm UTC
It looked like you had answered my query, because it showed in today's asktom.com. But there is no shown yours "followed up"?
Thanks.
July 07, 2003 - 7:10 am UTC
it showed up there cause you touched the question. any followup does that.
In short, I don't have sufficient data to diagnose this issue. Looks 100% like an environment issue. I do not run apps myself, so I don't know what "cloning apps" does -- looks like it copies the oracle install based on the path names. Have you tried the "other" sqlplus over net8 to see if you can connect. suggest you open a tar with support for this one.
nothing
SD, July 07, 2003 - 9:48 am UTC
Just curious!
What it means when you say open a tar with support, is it a jargon or a key word to say call the support guys/ contact support..etc
TIA
July 07, 2003 - 10:48 am UTC
tar = technical assistance request
9i Release 2 - Win 2003
TH, September 03, 2003 - 9:20 am UTC
Dear Tom,
Today I tried to install 9i rel 2 on a Win 2003 server.
I noted down the SID while installation.
I tried to connect SYSTEM/PWD through SQL plus. It get connected. While I'm giving the SID as the host string, it is not connecting. I rechecked the Instance name through the sql statement,
SQL> select instance_name from v$instance;
Under Services, OracleService<SID> also shows the same name.
What will be my mistake? Pls help me in configuring connections.
TH
September 03, 2003 - 11:53 am UTC
you do not use a sid in a connect string.
you set up a tnsnames.ora file that tells the client how to connect to which machine using which service or sid.
locally, you just have the sid set in the environment -- it is not used on the "connect" string.
Oracle CLient for XP
mo, November 17, 2003 - 12:04 pm UTC
Tom:
I got a new PC that has P4/2.4 GHZ and XP O/s. My old one was P3/500 and Win 98.
When I unzipped the "win817client.zip" to c:\OraHome1
and tried to run setup.exe to install the client software nothing happens. I looked at the directories created and there is no /bin.
I tried to look at OTN to see if there are special client software for XP and there is none.
Do you know if there is a client for XP systems that can connect to 8i/9i databases?
Thanks,
November 19, 2003 - 6:40 am UTC
i don't know what "win817client.zip" is?
do you have the real install cd's
oracle client
mo, November 19, 2003 - 7:56 am UTC
Tom:
win817client is the 8.1.7 client software fom oracle. I do have the CD, however I install oracle 9.2.0 client software and it works fine.
1. Is there any issues with a oracle 9.2 client connecting to 8i database?
I think the original problem is not with oracle client but with the P-4 processor and a file named symcjit.dll.
Here is intel description of the problem:
</code>
http://www.intel.com/support/processors/pentium4/issues.htm <code>
Thank you,
November 21, 2003 - 11:26 am UTC
1) no
there is a patch for the p4 issue (the p4 post dates 8i software -- no one had any idea there would be an issue). you can contact support for it.
symcjit.dll
Tomas Ericson, November 20, 2003 - 3:08 am UTC
Work Around:
1. Create a temporary directory on your server.
2. Copy the contents of the Oracle REBMS Server CD to the temporary
directory created in step 1.
3. Search the directory structure created in step 1 for the existence of
the filename symcjit.dll.
4. Rename each copy of the symcjit.dll to symcjit.old.
5. Run the setup.exe from the \install\win32 directory and install
Oracle 8.1.x.
That should do it. Make sure you don't have a jre installed already and if you do I believe you also have to rename the symcjit.dll as well.
shiv, November 20, 2003 - 4:03 am UTC
Hi,
Just to update what I infer from below metalink alert is connectivity from 9.2 to 7.x is officially desupported and not the other way around.
ALERT: Connections from Oracle 9.2 to Oracle7 are Not Supported Type: Note Doc ID: 207319.1
My client has many databases on 7.x clients happily connecting to 9.2. see one below.
isd250> svrmgrl
Oracle Server Manager Release 2.3.4.0.0 - Production
Copyright (c) Oracle Corporation 1994, 1995. All rights reserved.
Oracle7 Server Release 7.3.4.0.1 - Production
With the distributed, replication, parallel query and Spatial Data options
PL/SQL Release 2.3.4.0.0 - Production
SVRMGR> quit
Server Manager complete.
isd250> sqlplus dbawk/xxxx@t_cusmarp2iam alive
SQL*Plus: Release 3.3.4.0.1 - Production on Thu Nov 20 08:39:50 2003
Copyright (c) Oracle Corporation 1979, 1996. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
SQL> quit
November 21, 2003 - 3:42 pm UTC
7.3 is implicitly not supported connecting to 92 since 7.3 is "not supported"
that it works by accident for what you are doing is great -- but there are cases where will will cease working AFTER you are connected.
random PLS-00553 when using Web-Packages (htp,..)
Rainer Uhr, December 18, 2003 - 9:32 am UTC
Hi Tom,
using htp,htf,owa for HTML reporting.
stable and fast.
Now all of a sudden randomly error PLS-00553 appears (roughly 5% of cases) .
A refresh in 99% of the cases makes problem disappear.
Any idea ?
Rainer
December 18, 2003 - 11:28 am UTC
care to share the entire error stack there with me (the text would be nice too)
Problem connecting 806 client to 9204 db
raj, February 17, 2004 - 4:40 am UTC
Hi tom, i have just recently upgraded my 9i db to 9204. And i have also applied patch 10 for my developer components. I am still not able to connect to the 9i db via my 806 client. The 806 sqlplus just closes without any error message. How do i connect to 9204 db via sqlplus 806 version ?
February 17, 2004 - 8:22 am UTC
contact support.
Connecting from 806 sqlplus to 9204 db
Raj, February 20, 2004 - 2:08 am UTC
I came to find out that certain 9204 db charactersets are not support by 806 sqlplus clients. Therefore i had to drop the existing database and recreate a db with a compatible characterset and that solved that issue.
error: ora-06550, pls-00553
garuav mahajan, April 24, 2004 - 2:45 am UTC
when i use dbms_output.put_line() method, the error occurs:
DBD::Oracle::st execute failed: ORA-06550: line 39, column 21:
PLS-00553: character set name is not recognized
ORA-06550: line 0, column 0:
PL/SQL: Compilation unit analysis terminated
Procedure successful
how to remove the error on oracle 8i enterprise edition?
April 26, 2004 - 5:02 am UTC
if you do it in sqlplus, what than?
what is your db character set?
and you NLS_LANG environment variable? (or registry on windows)
tnsping compatibility
A reader, June 01, 2004 - 1:23 pm UTC
If I use a tnsping utility to ping a database,
If the tnsping belonsgs to 817 database, will it
work for pinging 817, 9i and 10g databases.
What about reverse, i.e. if I use tnsping of
9i database will it work for 817 and 10g?
Thanx!
June 01, 2004 - 3:41 pm UTC
tnsping should pretty much work up and down the versions - yes.
client vs server
reader, September 03, 2004 - 2:57 pm UTC
If I upgrade my database to 9.2.0.5 do I have to upgrade oracle client also to 9.2.0.5? Can clients with 9.2.0.2 connect to database that is 9.2.0.5? Thanks.
September 03, 2004 - 3:25 pm UTC
they can connect, you should consider patching the client if at all possible.
tnsping
A reader, September 09, 2004 - 11:11 pm UTC
when I do a tnsping <databasename> at the command prompt,what I'm I checking for, and what is the typical result I'm expecting to see.
September 10, 2004 - 8:07 am UTC
Upgrading client software - any ROTs?
AR, November 03, 2004 - 5:30 pm UTC
Tom,
Is there a ROT when it comes to upgrading Oracle client software? For example a 9.0.1 client can be used to connect to databases from 7.x (??) thru 10g. So what's the incentive in upgrading client software? Is it just good practise to keep the client software version and database version in sync?
I've got a few applications that currently use 9.0.1 client software to connect to 9ir2 databases. Everything seems to work just fine. I'm wondering if there is any need for me to think along the lines of upgrading these clients.
Thank you for your time.
November 05, 2004 - 11:23 am UTC
well, it is becoming over time (fortunately) a moot point.
with 3 tier, the client is in the data center and there are few of them. there are not 10's of thousands of them.
Even here at Oracle -- the laptops, they do not come with client software for Oracle -- everything is web based. The data center upgrades the clients to stay in sync with the database software (after testing)
Connecting from 8.0.5 client (forms 6.0) to 9i database (9.2.0.6)
N.Balasubramanian, January 24, 2005 - 8:11 am UTC
Hi Tom,
We recently upgraded the database from 8.1.7.4 to 9.2.0.6.
With the old tnsnames.ora (on the client side), I am able to connect to 9.2.0.6 database from Forms 6.0.
But when I add a new entry in the tnsnames.ora manually for connecting to another database (9.2.0.6) it is not allowing.
TNSPING80 is working. But sqlplus and Forms are not working. Unfortunately, Net8 config Asst is not in the menu and when I try to do a custom install of that, it fails with the message "unable to start a service for Net8 client".
How to make oracle accept the manual entry in tnsnames.ora.
I have enabled trace_level_client=4 and I find that the failed entry has an SID=ORCL. I do not know how is it happening.
Thanks and regards
January 24, 2005 - 8:44 am UTC
my car wouldn't start this morning either, it always used to.
I won't tell you what lights went on on the dashboard, nor what sound the engine did or did not make.
so, whats wrong? why didn't my car start (that's how i feel when i get a "it isn't working" question with zero details :)
Error connecting to DB
VP, June 21, 2005 - 5:29 pm UTC
Tom,
I am sorry if the question is irrelevent.
All of a sudden I am not able to connect to the database. The DB version is 9.2.
The following is the error that I am getting:
H:\>sqlplus userid@db
SQL*Plus: Release 9.0.1.3.0 - Production on Tue Jun 21 15:44:42 2005
(c) Copyright 2001 Oracle Corporation. All rights reserved.
Enter password:
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
SVR4 Error: 2: No such file or directory
If I use TNSPING, the result is as follows:
TNSPING db
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = yuca)(PORT = 1526))) (CONNECT_DATA = (SID = db)))
OK (40 msec).
I am trying to connect over network.
Please help.
June 21, 2005 - 9:08 pm UTC
database is not up, start datatbase.
DB is up but am not able to connect
VP, June 22, 2005 - 2:02 pm UTC
Tom,
Thanks for your response. I contacted my DBA regarding this issue and he says that DB is up and running and he is able to connect to it. It looks lke nothing (SID/ Host/ Port) was changed.
So I am really confused as to why I was able to connect a couple of days back but not now. Please help.
Am able to connect thru TOAD!!
VP, June 23, 2005 - 12:44 pm UTC
Tom,
I accidentally tried connecting with TOAD and surprisingly was able to connect. This leads me onto to more confusion. Could there be any problem w.r.t Oracle client settings in my machine? I have both Oracle 8 and 9 clients in my machine. Please help.
June 23, 2005 - 6:56 pm UTC
(maybe database is now up and was not up before?)
can you
sqlplus "user@(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = yuca)(PORT = 1526))) (CONNECT_DATA = (SID = db)))"
Still not able to connect...
VP, June 24, 2005 - 10:11 am UTC
I tried the command as specified by you but still it is giving the same error.
DBA confirmed that DB was not down recently.
I am confused as I am able to successfully connect from TOAD. Could it make a difference?
June 24, 2005 - 6:23 pm UTC
no, toad would not make a difference.
can you a) connect from toad and simultaneously b) not be able to connect with sqlplus. they use the same code, they same api's, the same stuff.
Connection issues
VP, June 27, 2005 - 9:11 am UTC
Tom,
I was able to connect through TOAD but not through SQLPLUS. I raised it with my help desk and they changed my registry to point to some network share as ORACLE_HOME and now am able to connect through SQLPLUS. I do not understand as to how it made the difference. Anyways, I am able to connect now.
Thanks again for your support.
9.2.0.6 ora-01031
abc, July 05, 2005 - 6:37 pm UTC
I recently installed oracle 9.2.0.1 and applied 9.2.0.6 pacth and then CPU April Patch.
Now everything is ready
D:\>set oracle_sid=prd
D:\>sqlplus
SQL*Plus: Release 9.2.0.6.0 - Production on Tue Jul 5 17:34:17 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Enter user-name: sys as sysdba
Enter password:
Connected to an idle instance.
SQL> startup nomount
ORA-01031: insufficient privileges
SQL>
Why it is happening? If I reboot server then This problem goes off.
July 05, 2005 - 7:13 pm UTC
I'll have to ask you to check with support, I know very very little about windows (other than the obvious)
Perl DBD and TNS
Clark Pearson, September 23, 2005 - 2:02 pm UTC
Sorry if this should go down as a new thread, it does seem quite relevant to my issue.
Have recently upgraded my db to 10.2 from 10.1.
Am using Perl to issue my SQL/PL-SQL.
Before upgrading, I used the cpan version of Oracle's DBD; this is now only available from Oracle.
Previously, I could bypass a TNS lookup - Perl and Ora are on the same (Windows) box, there's only one db, I simply didn't (and still don't) need a TNS lookup.
However, now when I use the same perl script I get a TNS error...
DBI connect('','TC',...) failed: ORA-12560: TNS:protocol adapter error (DBD ERROR: OCIServerAttach) at testOra.pl line 5
My connect string in perl (no TNS, get above err, but didn't before upgrading) is
$dbh=DBI->connect("dbi:Oracle:" ..etc
It does connect however when I use
$dbh=DBI->connect("dbi:Oracle:host=LocalHost;sid=TC" ..etc
Can TNS be bypassed with the new DBD?
Many thanks.
September 23, 2005 - 8:40 pm UTC
i've never used perl, however -- a 10.1 client would need to use sqlnet to connect to 10.2 - are you a 10.1 client?
we can use TWO_TASK in unix (local in windows) as an enviroment variable to set the "default" connect string.
Clark Pearson, September 24, 2005 - 6:15 am UTC
There is no client - I'm right on the server, like connecting from sqlplus with no '@' (at least as far as my understanding of these things goes - I'm a developer/programmer, my non-[PL-&]SQL knowledge is rudimentary). It connected before without even having the listener installed never mind running.
Perl is a server-side tool as well so there really should be no requirement for the listener, in an environment with only one db installed on that server (as far as my understanding...)
Also, I'm in development, there's nothing to upgrade, so when I say 'upgrade' the db, I actually mean completely uninstall Ora then reinstall from the new release, so either way, the client version isn't relevant because the client will have been ditched then reinstalled to the appropriate release.
Cheers.
September 24, 2005 - 8:06 am UTC
there is always a client, unless you are the database itself.
is the PERL CLIENT 10.1 or 10.2
perl is the client, it is linked with client libraries. What version are these client libraries.
Clark, September 24, 2005 - 8:35 am UTC
Err, sorry, I don't know, and I don't think I can find out either because I've now discovered that sqlplus doesn't work. "Oracle SQL*Plus has encountered a problem and needs to close." I've rebooted, I've tried using sqlplus from DOS and sqlplusw (note 'w') and both give this fluffy Windows error message.
(Why o why did I install a product that's only been released in the past month?)
It's still ok connecting to the db via my (noddy, test) perl script, but sqlplus is, excuse my french, knacked.
If I do a 'properties' on sqlplus.exe the file version says 10.2.0.1.0. Which isn't what you want to know.
The Perl installation is version 5.8.7. Which isn't what you want to know.
The perl client version? I'm stumped, sorry. My best guess is that you want to know what version the oci-files/dlls are..? - the ones that the Perl DBD invokes. Don't know how I can find that out - but the perl DBD (DBD-Oracle in perl language), the modules that enable perl to 'talk to' Oracle, were installed yesterday (via a perl utility, ppm, which automatically gets the latest version) - so they're as up-to-date as they get.
And Oracle was installed on Thu so again, bang up to date.
Didn't expect any response on a Sat - but thanks!
Restarting database remotely
Olaf, June 18, 2006 - 10:50 am UTC
Tom,
I just tried to restart Oracle from remote client using sqlplus:
SQL> connect sys@corp as sysdba
Enter password:
Connected.
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.
ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect descript
or
SQL> select status from v$instance;
select status from v$instance
*
ERROR at line 1:
ORA-03114: not connected to ORACLE
Now I keep getting ORA-12514 as I try to connect to Oracle. What is my problem - I did not change anything in config. I was able to connect to the database as it was running, but after shutdown it is not possible anymore?
June 18, 2006 - 12:20 pm UTC
$ oerr ora 12514
12514, 00000, "TNS:listener does not currently know of service requested in connect descriptor"
// *Cause: The listener received a request to establish a connection to a
// database or other service. The connect descriptor received by the listener
// specified a service name for a service (usually a database service)
// that either has not yet dynamically registered with the listener or has
// not been statically configured for the listener. This may be a temporary
// condition such as after the listener has started, but before the database
// instance has registered with the listener.
// *Action:
// - Wait a moment and try to connect a second time.
// - Check which services are currently known by the listener by executing:
// lsnrctl services <listener name>
// - Check that the SERVICE_NAME parameter in the connect descriptor of the
// net service name used specifies a service known by the listener.
// - If an easy connect naming connect identifier was used, check that
// the service name specified is a service known by the listener.
// - Check for an event in the listener.log file.
You would need to use STATIC registration - the listener.ora file would have to "statically" know of this instance.
So, it is possible to do this remotely, but only if the DBA has set it up to be permitted explicitly.
Restarting database remotely
A reader, June 19, 2006 - 1:58 am UTC
5 Stars to Tom,
1 Star to Oracle documentation
Thank you Tom for responding on Sunday! I registered the instance statically in the listener.ora by adding:
SID_LIST_listener=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=***)
(SID_NAME=***)
(ORACLE_HOME=***)))
and it worked.
I have to say that I find Oracle documentation pretty confusing. The documentation says that since version 8i Oracle uses dynamic service registration and any static registration in the listener.ora file not required.
This works until one day (one Sunday) you decide to restart a database (an imprortant prod database) remotely...
BTW: Reading the Oracle documentation I did not understand a concept of having multiple database services. Can you please give us a example of this (and how to configure this in init.ora))
//* A database can be presented as multiple services and a service can be implemented as multiple database instances. The service name is a string that is the global database name, that is, a name comprising the database name and domain name, entered during installation or database creation. If you are not sure what the global database name is, you can obtain it from the value of the SERVICE_NAMES parameter in the initialization parameter file. *//
June 19, 2006 - 5:53 pm UTC
well, do you need help understanding why
o an instance would present itself as multiple services
o many instances would present themselves as the same instance?
or how to configure?
the init.ora is pretty simple:
</code>
http://docs.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams187.htm#sthref776 <code>
a comma separated list of services you want to be presented as (or the single name you want to present yourself as - of which more than one instance may use)
Multiple services
A reader, June 20, 2006 - 8:56 am UTC
I just wanted to know what additional functionality enables having multiple services associated with one database.
For e.g. we currently have a database server running 5 different applications. The database has one service_name and 5 different schemas. Will I have more flexibility if I create one distinct service_name for each of 5 applications.
What are these multple services for?
June 20, 2006 - 10:11 am UTC
you could, for example, in 10g set up a resource manager plan that uses the service name and says something like:
when the load gets near 100%, I would like
HR_SVC to have 20% of the cpu
FINANCIAL_SVC to have 20%
REPORTS_SVC to have 10%
ADMIN_SVC to have 10%
ORDER_ENTRY_SVC to have 40%
among other things, you can control things by "tns service used to connect"
JA, July 13, 2006 - 10:55 am UTC
This (the whole thread) helped me understand why I was having this behaviour and resolve it. Good stuff.
Trigger Compilation problem by charaterset
Asheesh Dhupper, June 11, 2008 - 2:56 am UTC
Dear Sir,
We have Oracle 8i *8.1.7 DB Server with NLS Characterset UTF8. Now when we are compiling a trigger through client(Oracle8i) it gives an error
PLS-00553: character set name is not recognized
We have checked our registry and all parameters. The charater set is UTF8.
Please specify what characterset needs to be specified for Client too.
Please help
June 11, 2008 - 8:14 am UTC
see Note 286964.1 in metalink
Asheesh Dhupper, June 12, 2008 - 4:25 am UTC
Dear Sir,
I do not have Metalink access.
Please help
June 12, 2008 - 8:06 am UTC
give me the 4th digit in your version
are you using 8.1.7.0? or 8.1.7.4?
Asheesh Dhupper, June 13, 2008 - 2:11 am UTC
Its
Oracle8i Enterprise Edition Release 8.1.7.3.0
June 13, 2008 - 7:50 am UTC
you have support then, you could not have a patch otherwise.
that referenced note is a 'metanote' with pointers to other notes (meaning the causes are varied, there are more than one). I cannot repost them all here.