Home » Infrastructure » Unix » Problems with sysdba and env params (SQL*Plus: Release 9.2.0.1.0 // Solaris 8 2/02 )
Problems with sysdba and env params [message #509396] Fri, 27 May 2011 08:39 Go to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Hi all,

I'm not great at unix, so I assume this is an easy fix.


I've stored the connect descriptor for my playpen database as a unix env parameter/variable


export mydb="(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxx.xxx.xxx.xxx)(PORT=1521))(CONNECT_DATA=(SID=ORA11GMK)))"


Saves me typing it in any time I want to jump in from unix rather than the windows laptop its running on, suffice to say I can't muck about with the tns names on the unix box to get to a "private" database. Anyway I digress.

This works fine:

usr@dun01: ~] $  sqlplus scott/tiger@$mydb

SQL*Plus: Release 9.2.0.1.0 - Production on Fri May 27 14:31:08 2011

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Personal Oracle Database 11g Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

scott@ORA11GMK>


It won't work when I try as sysdba however:

scott@ORA11GMK> exit
Disconnected from Personal Oracle Database 11g Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


usr@dun01: ~] $  sqlplus sys/@$mydb as sysdba
Usage: SQLPLUS [ [<option>] [<logon>] [<start>] ]
where <option> ::= -H | -V | [ [-L] [-M <o>] [-R <n>] [-S] ]
      <logon>  ::= <username>[/<password>][@<connect_string>] | / | /NOLOG
      <start>  ::= @<URI>|<filename>[.<ext>] [<parameter> ...]
        "-H" displays the SQL*Plus version banner and usage syntax
        "-V" displays the SQL*Plus version banner
        "-L" attempts log on just once
        "-M <o>" uses HTML markup options <o>
        "-R <n>" uses restricted mode <n>
        "-S" uses silent mode



I imagine it's a syntax thing or similar, but as I say - I'm a proper greenhorn where unix is concerned and I wondered if you folks had seen anything like this before?

Posting here since it's a SQLplus related issue rather than a firm unix one.

I can do it the longhanded way to use sysdba but was hoping to avoid it.

Thanks folks Smile

[Updated on: Fri, 27 May 2011 09:14] by Moderator

Report message to a moderator

Re: Problems with sysdba and env params [message #509403 is a reply to message #509396] Fri, 27 May 2011 08:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Remove / before @

Regards
Michel
Re: Problems with sysdba and env params [message #509407 is a reply to message #509403] Fri, 27 May 2011 09:03 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Arf, typo making example and getting rid of the "scott/tiger" entry.

It does the same written (what I believe to be) properly thus:

usr@dun01: ~] $  sqlplus sys@$mydb as sysdba
Usage: SQLPLUS [ [<option>] [<logon>] [<start>] ]
where <option> ::= -H | -V | [ [-L] [-M <o>] [-R <n>] [-S] ]
      <logon>  ::= <username>[/<password>][@<connect_string>] | / | /NOLOG
      <start>  ::= @<URI>|<filename>[.<ext>] [<parameter> ...]
        "-H" displays the SQL*Plus version banner and usage syntax
        "-V" displays the SQL*Plus version banner
        "-L" attempts log on just once
        "-M <o>" uses HTML markup options <o>
        "-R <n>" uses restricted mode <n>
        "-S" uses silent mode




Edit:

It is only adding "as sysdba" that causes problems

Fail for using sys without "as sysxxxx" is expected here, but it gets passed the above sqlplus issue

 sqlplus sys@$MYDB

SQL*Plus: Release 9.2.0.1.0 - Production on Fri May 27 15:05:15 2011

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Enter password:
ERROR:
ORA-28009: connection as SYS should be as SYSDBA or SYSOPER


[Updated on: Fri, 27 May 2011 09:15] by Moderator

Report message to a moderator

Re: Problems with sysdba and env params [message #509408 is a reply to message #509407] Fri, 27 May 2011 09:07 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Try adding quotes around?
In my case it is expected to get ORA-01031

Kaapi:ora magvivek$ sqlplus "sys@$mydb as sysdba"

SQL*Plus: Release 10.2.0.4.0 - Production on Fri May 27 10:05:44 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Enter password: 
ERROR:
ORA-01031: insufficient privileges
Re: Problems with sysdba and env params [message #509409 is a reply to message #509408] Fri, 27 May 2011 09:09 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Quotes did the trick. Much appreciated.

usr@dun01: ~] $  sqlplus "sys@$MYDB as sysdba"

SQL*Plus: Release 9.2.0.1.0 - Production on Fri May 27 15:08:44 2011

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Enter password:

Connected to:
Personal Oracle Database 11g Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

sys@ORA11GMK>


(I realise the case in the var name changed in my final edit - intentional)
Re: Problems with sysdba and env params [message #509445 is a reply to message #509409] Fri, 27 May 2011 15:24 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Hi, man - are you familiar with what they call the EZCONNECT (makes sense of you pronounce your Zs the American way) syntax?
sqlplus sys/oracle@127.0.0.1:1521/orcl as sysdba

It's a bit easier than all the TNS syntax.

edit - code tags

[Updated on: Fri, 27 May 2011 15:26]

Report message to a moderator

Re: Problems with sysdba and env params [message #509588 is a reply to message #509445] Mon, 30 May 2011 03:28 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
That's basically all the $MYDB resolves to - it's a playpen on my local laptop, but sometimes if I want to test something quickly I'll jump on from the prod unix box. I stored it as a variable to save me typing it all the time or having multiple scripts to change between scott/sys. I can't really modify the tnsnames on a prod box to my sandpit, so this is basically just a placeholder for the connect string to save my lazy fingers Smile

Hopefully made sense Smile



Edit: ezconnect won't work with a 9.2 client will it?

[Updated on: Mon, 30 May 2011 03:37]

Report message to a moderator

Re: Problems with sysdba and env params [message #509589 is a reply to message #509588] Mon, 30 May 2011 03:45 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
ezconnect won't work with a 9.2 client will it?

No.

Regards
Michel
Previous Topic: very simple Oracle monitoring shell script (not replacement for Grid Control)
Next Topic: Auto Listener Start/Stop Fails
Goto Forum:
  


Current Time: Thu Mar 28 09:08:20 CDT 2024