Re: 8i/9i cross version sqlplus connection problem

Ulrich Gayer <ugayer_at_gmx.de> schreef in berichtnieuws bg5eib$l1hme$1_at_ID-12871.news.uni-berlin.de...
| Hello,
|
| I've got Oracle 8.1.7.4 and 9.2.0.2 installed on a HP-UX 11i box.
|
| Two databases:
|
| GEPJ4 (Oracle 9i)
| GEPT4 (Oracle 8i)
|
| My problem is that I don't manage to connect to the 9i db *without
| password* using a Oracle 8 sqlplus or vice versa. I use the Unix user
| "oracle", member of groups "oinstall" (primary group) and "dba"
| (secondary group)
|
| Is this possible at all?
|
| This works fine:
|
| - connect with Oracle 9 sqlplus to Oracle 9 db:
|
| ORACLE_HOME=/db/oracle/product/9.2
| SHLIB_PATH=$ORACLE_HOME/lib
| ORACLE_BASE=/db/oracle
| export ORACLE_HOME ORACLE_BASE SHLIB_PATH ORACLE_SID
| ORACLE_SID=GEPJ4
|

PATH=/usr/ccs/bin:/usr/bin:/usr/contrib/bin:$ORACLE_HOME/bin:/etc:/usr/local /bin
| sqlplus "/ as sysdba"
|
| - connect with Oracle 8 sqlplus to Oracle 8 db:
|
| ORACLE_HOME=/db/oracle/product/8.1.7
| SHLIB_PATH=$ORACLE_HOME/lib
| ORACLE_BASE=/db/oracle
| export ORACLE_HOME ORACLE_BASE SHLIB_PATH ORACLE_SID
| ORACLE_SID=GEPT4
|

PATH=/usr/ccs/bin:/usr/bin:/usr/contrib/bin:$ORACLE_HOME/bin:/etc:/usr/local /bin
| sqlplus "/ as sysdba"
|
|
| This does *not* work:
|
| - connect with Oracle 9 sqlplus to Oracle 8 db:
|
| ORACLE_HOME=/db/oracle/product/9.2
| SHLIB_PATH=$ORACLE_HOME/lib
| ORACLE_BASE=/db/oracle
| export ORACLE_HOME ORACLE_BASE SHLIB_PATH ORACLE_SID
| ORACLE_SID=GEPT4
|

PATH=/usr/ccs/bin:/usr/bin:/usr/contrib/bin:$ORACLE_HOME/bin:/etc:/usr/local /bin
| sqlplus "/ as sysdba"
| "Connected to an idle instance."
|
| - connect with Oracle 8 sqlplus to Oracle 9 db:
|
| ORACLE_HOME=/db/oracle/product/8.1.7
| SHLIB_PATH=$ORACLE_HOME/lib
| ORACLE_BASE=/db/oracle
| export ORACLE_HOME ORACLE_BASE SHLIB_PATH ORACLE_SID
| ORACLE_SID=GEPJ4
|

PATH=/usr/ccs/bin:/usr/bin:/usr/contrib/bin:$ORACLE_HOME/bin:/etc:/usr/local /bin
| sqlplus "/ as sysdba"
| "Connected to an idle instance."
|
|
| Other symptoms:
|
| sqlplus "system/xxxx_at_GEPJ4" works using Oracle 8 sqlplus
| sqlplus "system/xxxx_at_GEPT4" works using Oracle 9 sqlplus
|
| but
|
| sqlplus "system/xxxx"
| ORA-27101: shared memory realm does not exist
| HP-UX Error: 2: No such file or directory
|
| and
|
| sqlplus "/@GEPJ4 as sysdba"
| ORA-01031: insufficient privileges
| (Probaby a nonsense syntax at all)
|
|
|
| listener.ora:
| (SID_DESC =
| (GLOBAL_DBNAME = GEPJ4.foo.bar)
| (ORACLE_HOME = /db/oracle/product/9.2)
| (SID_NAME = GEPJ4)
| )
| (SID_DESC =
| (GLOBAL_DBNAME = GEPT4.foo.bar)
| (ORACLE_HOME = /db/oracle/product/8.1.7)
| (SID_NAME = GEPT4)
| )
|
|
| tnsnames.ora:
| GEPT4 =
| (DESCRIPTION =
| (ADDRESS_LIST =
| (ADDRESS = (PROTOCOL = TCP)(HOST = foohost)(PORT = 1521))
| )
| (CONNECT_DATA =
| (SID = GEPT4)
| (SERVICE_NAME = GEPT4.foo.bar)
| (ORACLE_HOME = /db/oracle/product/8.1.7)
| )
| )
|
| GEPJ4 =
| (DESCRIPTION =
| (ADDRESS_LIST =
| (ADDRESS = (PROTOCOL = TCP)(HOST = foohost)(PORT = 1521))
| )
| (CONNECT_DATA =
| (SERVER = DEDICATED)
| (SID = GEPJ4)
| (SERVICE_NAME = GEPJ4.foo.bar)
| (ORACLE_HOME = /db/oracle/product/9.2)
| )
| )
|
| Any idea?
|
| Ulrich
|

All your observations are perfectly correct. When ORACLE_SID is set you can only connect to a local database. A local database must run under the $ORACLE_HOME that is set. You can connect with

     sqlplus '/ as sysdba' (both 8i and 9i) or sqlplus internal (8i only, obsolete in 9i) It is the OS that authenticates you, that is you must be a member of the osdba or osoper group you set when you installed the software, usually "dba".

When the database runs under a different ORACLE_HOME than the one you set you connect to a remote database, regardless it runs on the same host or another. Connect with syntax

   sqlplus un/pw_at_servicename
where the servicename must be found in the Net8 (Net9) configuration. On Unix you can set a variable

   export TWO_TASK=servicename
as a default so you can omit the "@servicename" part. When TWO_TASK is set it precedes ORACLE_SID

When you want to connect as SYS to a remote database it depends on init.ora parameters.When:

       o7_dictionary_accessibility=TRUE (default TRUE in 8i, default FALSE in 9i)
and remote_login_password_file=NONE
you can connect with

      sqlplus sys/pw_at_servicename
When o7_dictionary_accessibility=FALSE you can only make a remote connection as sys when remote_login_password_file={SHARED, EXCLUSIVE}. You must make a password file with the orapwd utility.
You can connect as SYS in 2 ways:

       sqlplus un/pw_at_servicename as sysdba with the regular password of the user. You must be granted then by SYS with:

     grant sysdba to un;
This is only possible with an exclusive password file. View V$PWFILE_USERS shows you who is granted this priv.
Or you can connnect as SYS with:

      sqlplus sys/pw_at_servicename as sysdba with the password of the password file.

Note there is a bug in sqlplus V8.1.x for Windows platforms. The box with the 3 fields username, password, hostname works only if you type "as sysdba" after the password.
When you use TOAD 7.5 I advise you to set o7_dictionary_accessibility=FALSE because it shows passwords of database links when "select any table" is granted to the user (although intelligent users already knew where to find it in the SYS schema but nobody bothered to search for it. But TOAD makes if very easy now). We are in the middle of this exercise now with all problems that is causes. Access to all SYS objects gets very restricted. Granting select_catalog_role is not always sufficient. Received on Tue Jul 29 2003 - 13:07:49 CDT