sys 用户通过新建的服务名连接，报用户名和密码错误

abao2000521

sys 用户通过新建的服务名连接，报用户名和密码错误

1.使用test这个服务名(test就是sid)，sys和system就可以登陆
test =
  (description_list =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (LOAD_BALANCE = OFF)
        (FAILOVER = ON)
        (ADDRESS = (PROTOCOL = TCP)(Host = 10.252.114.13)(Port = 1525))
        (ADDRESS = (PROTOCOL = TCP)(Host = 10.252.114.14)(Port = 1525))
      )
      (CONNECT_DATA =
        (SERVICE_NAME = test)
        (server = dedicated)
        (FAILOVER_MODE =
          (RETRIES = 30)
          (DELAY = 5)
          (TYPE = SELECT)
        )
      )
    )
  )
  
  
[oracle@SGS-ORA-03 admin]$ sqlplus sys/123456@test as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Feb 18 11:12:55 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL>


[oracle@SGS-ORA-03 admin]$ sqlplus system/123456@test

SQL*Plus: Release 11.2.0.3.0 Production on Tue Feb 18 11:12:55 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL>


2.使用testapp这个服务名(testapp是新建的服务)，system就可以登陆 ,sys用户无法登陆，报ORA-01017: invalid username/password; logon denied


testapp =
  (description_list =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (LOAD_BALANCE = OFF)
        (FAILOVER = ON)
        (ADDRESS = (PROTOCOL = TCP)(Host = 10.252.114.13)(Port = 1525))
        (ADDRESS = (PROTOCOL = TCP)(Host = 10.252.114.14)(Port = 1525))
      )
      (CONNECT_DATA =
        (SERVICE_NAME = testapp)
        (server = dedicated)
        (FAILOVER_MODE =
          (RETRIES = 30)
          (DELAY = 5)
          (TYPE = SELECT)
        )
      )
    )
  )
  
[oracle@SGS-ORA-03 admin]$ sqlplus sys/123456@testapp as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Feb 18 11:13:12 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name: ^C


[oracle@SGS-ORA-03 admin]$ sqlplus system/123456@testapp

SQL*Plus: Release 11.2.0.3.0 Production on Tue Feb 18 11:12:55 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL>

Maclean Liu(刘相兵

需要service 信息，以及发生ORA-01017: invalid username/password; logon denied 时到底连接到哪里等信息，你可以做10046 trace，或者SQL NET TRACE获得这些信息

abao2000521

１.生成10046文件
[oracle@SGS-ORA-03 admin]$ sqlplus sys/xxxxxxx@xxxxxx as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Feb 19 12:24:36 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> select distinct sid from v$mystat;

       SID
----------
      2983

SQL> select spid,pid from v$Process where addr=(select paddr from v$session where sid=&SID);
Enter value for sid: 2983
old   1: select spid,pid from v$Process where addr=(select paddr from v$session where sid=&SID)
new   1: select spid,pid from v$Process where addr=(select paddr from v$session where sid=2983)

SPID                                                        PID
------------------------------------------------ ----------
60296                                                         42

SQL> oradebug setorapid 42
Oracle pid: 42, Unix process pid: 60296, image: oracle@SGS-ORA-03
SQL> oradebug event 10046 trace name context forever, level 28;
Statement processed.
SQL> conn sys/xxxxxxx@xxxxx as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL> exit

2.10046文件如下：

more eipdbgd1_ora_60296.trc
Trace file /oracle/app/oracle/diag/rdbms/eipdbgd/eipdbgd1/trace/eipdbgd1_ora_602
96.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management,
OLAP,
Data Mining and Real Application Testing options
ORACLE_HOME = /oracle/app/oracle/11.2.0
System name:        Linux
Node name:        SGS-ORA-03
Release:        2.6.32-358.el6.x86_64
Version:        #1 SMP Tue Jan 29 11:47:41 EST 2013
Machine:        x86_64
Instance name: eipdbgd1
Redo thread mounted by this instance: 1
Oracle process number: 42
Unix process pid: 60296, image: oracle@SGS-ORA-03


*** 2014-02-19 12:25:40.475
*** SESSION ID:(2983.2015) 2014-02-19 12:25:40.475
*** CLIENT ID:() 2014-02-19 12:25:40.475
*** SERVICE NAME:(eipdbgd) 2014-02-19 12:25:40.475
*** MODULE NAME:(sqlplus@SGS-ORA-03 (TNS V1-V3)) 2014-02-19 12:25:40.475
*** ACTION NAME:() 2014-02-19 12:25:40.475

Processing Oradebug command 'setorapid 42'

*** 2014-02-19 12:25:40.475
Oradebug command 'setorapid 42' console output:
Oracle pid: 42, Unix process pid: 60296, image: oracle@SGS-ORA-03

*** 2014-02-19 12:25:53.748
Processing Oradebug command 'event 10046 trace name context forever, level 28'

*** 2014-02-19 12:25:53.757
Oradebug command 'event 10046 trace name context forever, level 28' console outp
ut: <none>
WAIT #0: nam='SQL*Net message to client' ela= 3 driver id=1413697536 #bytes=1 p3
=0 obj#=-1 tim=1392783953757840

*** 2014-02-19 12:26:45.230
WAIT #0: nam='SQL*Net message from client' ela= 51472205 driver id=1413697536 #b
ytes=1 p3=0 obj#=-1 tim=1392784005230078
XCTEND rlbk=0, rd_only=1, tim=1392784005230304

abao2000521

刘大，提供的10046文件信息对你有帮助吗。请问还需要什么信息吗。

生生不息

这个 10046文件只是对上次的session, 要对新建的session启用10046,建议配置loggon trigger,针对特定IP地址在连接的时候启用10046 trace

abao2000521

这个10046为什么不行。不明白

abao2000521

Trace file /oracle/app/oracle/diag/rdbms/eipdbgd/eipdbgd2/trace/eipdbgd2_ora_29901.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
ORACLE_HOME = /oracle/app/oracle/11.2.0
System name:        Linux
Node name:        SGS-ORA-04
Release:        2.6.32-358.el6.x86_64
Version:        #1 SMP Tue Jan 29 11:47:41 EST 2013
Machine:        x86_64
Instance name: eipdbgd2
Redo thread mounted by this instance: 2
Oracle process number: 66
Unix process pid: 29901, image: oracle@SGS-ORA-04 (TNS V1-V3)


*** 2014-02-20 15:27:54.416
*** SESSION ID:(144.1439) 2014-02-20 15:27:54.416
*** CLIENT ID:() 2014-02-20 15:27:54.416
*** SERVICE NAME:(SYS$USERS) 2014-02-20 15:27:54.416
*** MODULE NAME:(sqlplus@SGS-ORA-04 (TNS V1-V3)) 2014-02-20 15:27:54.416
*** ACTION NAME:() 2014-02-20 15:27:54.416

CLOSE #140070217799208:c=0,e=3,dep=2,type=0,tim=1392881274416403
=====================
PARSING IN CURSOR #140070217809800 len=244 dep=1 uid=0 oct=47 lid=0 tim=1392881274416977 hv=365880158 ad='1d4888a10' sqlid='4g8gr28awxsuy'
BEGIN
    EXECUTE IMMEDIATE 'ALTER SESSION SET timed_statistics=true';
    EXECUTE IMMEDIATE 'ALTER SESSION SET max_dump_file_size=unlimited';
    EXECUTE IMMEDIATE 'ALTER SESSION SET EVENTS ''10046 trace name context forever, level 8'' ';
END;
END OF STMT
EXEC #140070217809800:c=2000,e=1813,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=0,tim=1392881274416971
CLOSE #140070217809800:c=0,e=18,dep=1,type=0,tim=1392881274417155
WAIT #0: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1392881274417285
WAIT #0: nam='SQL*Net message from client' ela= 791 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1392881274418126
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1392881274418228
WAIT #0: nam='SQL*Net message from client' ela= 287 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1392881274418545
XCTEND rlbk=0, rd_only=1, tim=1392881274418646
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1392881274418706
WAIT #0: nam='SQL*Net message from client' ela= 97 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1392881274418828
XCTEND rlbk=0, rd_only=1, tim=1392881274418878
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1392881274418925

*** 2014-02-20 15:27:57.073
WAIT #0: nam='SQL*Net message from client' ela= 2654269 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1392881277073216
XCTEND rlbk=0, rd_only=1, tim=1392881277073381－－使用登陆loggon trigger生成的10046

psufnxk2000

等 答案 。

abao2000521

重建密码文件解决