oracle tips : Problem to connect as SYSDBA(zt from asktom)

===========================================================

Problem to connect as SYSDBA(zt from asktom)

===========================================================

作者: jametong(http://jametong.itpub.net)
发表于: 2005.06.14 16:04
分类: oracle refs
出处: http://jametong.itpub.net/post/5042/32288
---------------------------------------------------------------

Hi there,

I am running Oracle 8.1.7 EE on SUN Solaris. I created two databases
SUGAR and TestDb (I need one to do some testing). Both are set 
remote_login_passwordfile = exclusive. The following shows how I had trouble to 
connect as SYSDBA. The big problem is that I cannot connect to TestDb as SYSDBA.

$sqlplus /nolog
SQL> conn sys/change_on_install@testdb
connected
SQL> select * from v$pwfile_users;
username       sysdb    sysop
------------------------------
internal       true      true
sys            true      true
SQL> conn sys/change_on_install@testdb as sysdba
ERROR
ORA-01017: invalid username/password; logon denied
Warning: You are on longer connected to ORACLE
SQL> conn sys/change_on_install@SUGAR
connected
SQL> select * from v$pwfile_users;
username       sysdb    sysop
------------------------------
internal       true      true
sys            true      true
SQL> conn sys/change_on_install@SUGAR as sysdba
ERROR
ORA-01017: invalid username/password; logon denied
Warning: You are on longer connected to ORACLE
SQL> conn sys/change_on_install as sysdba
connected
SQL> select name from v$database;
name
------
SUGAR

Questions:
1. Why "conn sys/change_on_install@testdb/sugar as sysdba" always failed, but 
"conn sys/change_on_install as sysdba" OK ?

2. Why I cannot connect to testdb as SYSDBA?

Thanks for any help.

and we said...

what was the password you used when you created the password file and have you 
tried that?

the sysdba trick is generally used to allow other NON-SYS users to connect btw, 
best to use your OWN account that has been granted sysdba!

Anyway, hopeful this will clear up the confusion and show how this works:

$ orapwd file=orapw password=foobar entries=40

I just recreated my password file with a password foobar.  My sys password is 
NOT foobar

$ svrmgrl

SVRMGR> connect internal
Connected.

SVRMGR> startup
ORACLE instance started.
Total System Global Area                        193073136 bytes
Fixed Size                                          69616 bytes
Variable Size                                   141639680 bytes
Database Buffers                                 45056000 bytes
Redo Buffers                                      6307840 bytes
Database mounted.
Database opened.

SVRMGR> connect sys/change_on_install@ora816dev
Connected.

that works OK, lets try as sysdba:

SVRMGR> connect sys/change_on_install@ora816dev as sysdba;
ORA-01017: invalid username/password; logon denied

there I got your error.  My SYS password is change_on_install but the password 
file has foobar in it.  SYS is special -- sys connecting as sysdba is like 
internal -- you have to use the password file password!  Lets try that:

SVRMGR> connect sys/foobar@ora816dev as sysdba;
Connected.

SVRMGR> grant sysdba to scott;
Statement processed.

now, we'll see this does not hold true for other users:

SVRMGR> connect scott/tiger@ora816dev as sysdba;
Connected.

That worked great, now lets just modify sys's password (not really, didn't 
change it but Oracle won't notice that

SVRMGR> alter user sys identified by change_on_install;
Statement processed.

SVRMGR> connect sys/change_on_install@ora816dev as sysdba;
Connected.
SVRMGR> 
Hey, now we can use change_on_install -- that is because altering SYS's 
password will sync up the password in the password file -- sys is a very very 
close cousin of INTERNAL.  sys's password will be burned into the password file 
now.  the password foobar is obsolete

SVRMGR> connect sys/foobar@ora816dev as sysdba;
ORA-01017: invalid username/password; logon denied
SVRMGR>