Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: remote / as sysdba

RE: remote / as sysdba

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Thu, 06 Mar 2003 12:14:13 -0800
Message-ID: <F001.00562644.20030306121413@fatcity.com>


As far as I know, it works like this:
You will need to set init parameter REMOTE_LOGIN_PASSWORD_FILE to EXCLUSIVE. You then can sign on as SYS remotely, or as another user remotely if the other user has SYSDBA. See the users that have SYSDBA or SYSOPER in v$pwfile_users.
As the view name suggests, you will also need a password file for the database, which should be created with the orapwd utility. $ orapwd
Usage: orapwd file=<fname> password=<password> entries=<users>

  where
    file - name of password file (mand),     password - password for SYS and INTERNAL (mand),     entries - maximum number of distinct DBA and OPERs (opt),   There are no spaces around the equal-to (=) character.

Once you've done all that, you can connect remotely by saying the following in SQL*Plus:
connect sys/password_at_tns_alias as sysdba

But I don't think you will ever be able to do connect / as sysdba remotely. For one thing, the syntax in SQL*Plus is: Syntaxe : CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}] ou <logon> ::= <username>[/<password>][@<connect_string>] | /

So the logon is either username/password_at_tns_alias or else "/" all by itself. How would you tell SQL*Plus which remote database you want to connect to? I tried setting TWO_TASK to the tns_alias for the database, but that didn't help. It seems to me that when you enable remote SYSDBA logins Oracle will insist on verifying a password for the SYSDBA user in the password file.
Or is there some clever trick I don't know about?

> -----Original Message-----
> From: becker.bill_at_marshfieldclinic.org
>
> env: Oracle 9.2.0.2 on Solaris 9.
>
> Does anyone know of a way to use the "/ as sysdba" logon remotely?
> (to a separate Oracle instance on a separate machine)
>
> Other remote user logons work OK.
>
> I have tried several variations from sqlplus, such as
>
> BECKERB_at_DWS>connect /@DWQ as sysdba
> ERROR:
> ORA-01031: insufficient privileges
>
> Warning: You are no longer connected to ORACLE.
>
> BECKERB_at_DWS>connect sys/sys_password_at_DWQ as sysdba
> ERROR:
> ORA-01017: invalid username/password; logon denied
>
> BECKERB_at_DWS>connect sys/exr_sys as sysdba_at_DWQ
> SP2-0306: Invalid option.
> Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
> where <logon> ::= <username>[/<password>][@<connect_string>] | /
>
> BECKERB_at_DWS>connect sys/sys_password_at_DWQ
> ERROR:
> ORA-28009: connection to sys should be as sysdba or sysoper
>
> I also find I cannot even "connect sys/syspassword" locally:
>
> BECKERB_at_DWS>connect sys/sys_password
> ERROR:
> ORA-28009: connection to sys should be as sysdba or sysoper
>
> This does work locally, but not remotely:
>
> BECKERB_at_DWS>connect sys/sys_password as sysdba
> Connected.
>
> I am a member of the dba group on both platforms.
>
> I have verified that I am using the correct sys_password for sys
> on the remote instance.
>
> Eventually, I want to do a remote transportable tablespace
> import, where
> the userid would be listed in a parfile; I have tried the
> same logons in
> a parfile, and that also fails.
>
> I found a Metalink doc that says the O7_DICTIONARY_ACCESSIBILITY (sp?)
> must be true to do this, but the same doc strongly advises
> against setting
> this to true.
>
> So, has anyone found a way to use the "/ as sysdba" logon remotely?
> (without setting the O7 parameter to true)

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jacques Kilchoer
  INET: Jacques.Kilchoer_at_quest.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Thu Mar 06 2003 - 14:14:13 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US