sys 用户通过新建的服务名连接，报用户名和密码错误
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)
)
)
)
)
$ 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>
$ 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)
)
)
)
)
$ 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
$ 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>
需要service 信息，以及发生ORA-01017: invalid username/password; logon denied 时到底连接到哪里等信息，你可以做10046 trace，或者SQL NET TRACE获得这些信息 １.生成10046文件
$ 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
刘大，提供的10046文件信息对你有帮助吗。请问还需要什么信息吗。 这个 10046文件只是对上次的session, 要对新建的session启用10046,建议配置loggon trigger,针对特定IP地址在连接的时候启用10046 trace 这个10046为什么不行。不明白 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 等 答案 。 重建密码文件解决
页:
[1]