1、應用伺服器上sqlplus可以不加@tnsname就連上資料庫是因為應用預設連線指向的DB，但是應用伺服器上不能直接sqlplus as sysdba這樣連線
2、應用的OS使用者和DB的OS使用者都有一個TNS_ADMIN的變數，都有自己的tns配置資訊
3、應用伺服器和DB伺服器的客戶端版本不一樣，比如應用伺服器上sqlplus和lsnrctl版本都是10.1.0.5.0，而DB伺服器上的sqlplus和lsnrctl都是11.2.0.4.0
4、應用伺服器和DB伺服器不在同一臺機器上時，應用伺服器上直接lsnrctl status alias時會報錯

如下案例中
ebsdev伺服器：應用和DB共用這臺伺服器
YDerp伺服器：只有應用，資料庫在EBSDB伺服器上

ebsdev伺服器的應用OS使用者顯示的資訊
[root@ebsdev ~]# su - appldev

[appldev@ebsdev ~]$ env|grep TNS
TNS_ADMIN=/u02/DEV/inst/apps/DEV_ebsdev/ora/10.1.2/network/admin

[appldev@ebsdev ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.1.0.5.0 - Production on Tue Feb 27 14:30:42 2018
Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
Enter user-name: ^C

[appldev@ebsdev ~]$ sqlplus apps/devXXtst
SQL*Plus: Release 10.1.0.5.0 - Production on Tue Feb 27 14:30:57 2018
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string DEV
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

[appldev@ebsdev ~]$ sqlplus apps/dev0217tst@DEV
SQL*Plus: Release 10.1.0.5.0 - Production on Tue Feb 27 14:31:09 2018
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string DEV
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

[appldev@ebsdev ~]$ lsnrctl status dev
LSNRCTL for Linux: Version 10.1.0.5.0 - Production on 27-FEB-2018 14:31:39
Copyright (c) 1991, 2004, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ebsdev.YUD.com)(PORT=1551))(CONNECT_DATA=(SID=DEV)))
STATUS of the LISTENER
------------------------
Alias dev
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 23-FEB-2018 09:54:53
Uptime 4 days 4 hr. 36 min. 46 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u02/DEV/db/tech_st/11.2.0/network/admin/DEV_ebsdev/listener.ora
Listener Log File /u02/DEV/db/tech_st/11.2.0/admin/DEV_ebsdev/diag/tnslsnr/ebsdev/dev/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ebsdev.YUD.com)(PORT=1551)))
Services Summary...
Service "DEV" has 1 instance(s).
Instance "DEV", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

[appldev@ebsdev ~]$ cat /u02/DEV/inst/apps/DEV_ebsdev/ora/10.1.2/network/admin/tnsnames.ora
###############################################################
#
# This file is automatically generated by AutoConfig. It will be read and
# overwritten. If you were instructed to edit this file, or if you are not
# able to use the settings created by AutoConfig, refer to Metalink Note
# 387859.1 for assistance.
#
#$Header: NetServiceHandler.java 120.19.12010000.6 2010/03/09 08:11:36 jmajumde ship $
#
###############################################################
DEV=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=ebsdev.YUD.com)(PORT=1551))
(CONNECT_DATA=
(SID=DEV)
)
)

DEV_FO=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=ebsdev.YUD.com)(PORT=1551))
(CONNECT_DATA=
(SID=DEV)
)
)
...
IFILE=/u02/DEV/inst/apps/DEV_ebsdev/ora/10.1.2/network/admin/DEV_ebsdev_ifile.ora

ebsdev伺服器的DB的OS使用者顯示的資訊
[root@ebsdev ~]# su - oradev

[oradev@ebsdev ~]$ env|grep TNS
TNS_ADMIN=/u02/DEV/db/tech_st/11.2.0/network/admin/DEV_ebsdev

[oradev@ebsdev ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Feb 27 14:34:46 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oradev@ebsdev ~]$ sqlplus apps/dev0217tst
SQL*Plus: Release 11.2.0.4.0 Production on Tue Feb 27 14:34:58 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string DEV
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oradev@ebsdev ~]$ sqlplus apps/dev0217tst@DEV
SQL*Plus: Release 11.2.0.4.0 Production on Tue Feb 27 14:35:17 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string DEV
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oradev@ebsdev ~]$ lsnrctl status dev
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 27-FEB-2018 14:35:37
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ebsdev.YUD.com)(PORT=1551)))
STATUS of the LISTENER
------------------------
Alias dev
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 23-FEB-2018 09:54:53
Uptime 4 days 4 hr. 40 min. 43 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u02/DEV/db/tech_st/11.2.0/network/admin/DEV_ebsdev/listener.ora
Listener Log File /u02/DEV/db/tech_st/11.2.0/admin/DEV_ebsdev/diag/tnslsnr/ebsdev/dev/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ebsdev.YUD.com)(PORT=1551)))
Services Summary...
Service "DEV" has 1 instance(s).
Instance "DEV", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

[oradev@ebsdev ~]$ cat /u02/DEV/db/tech_st/11.2.0/network/admin/DEV_ebsdev/tnsnames.ora
###############################################################
#
# This file is automatically generated by AutoConfig. It will be read and
# overwritten. If you were instructed to edit this file, or if you are not
# able to use the settings created by AutoConfig, refer to Metalink Note
# 387859.1 for assistance.
#
#$Header: NetServiceHandler.java 120.19.12010000.6 2010/03/09 08:11:36 jmajumde ship $
#
###############################################################
DEV=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=ebsdev.YUD.com)(PORT=1551))
(CONNECT_DATA=
(SID=DEV)
)
)

DEV_FO=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=ebsdev.YUD.com)(PORT=1551))
(CONNECT_DATA=
(SID=DEV)
)
)
...
IFILE=/u02/DEV/db/tech_st/11.2.0/network/admin/DEV_ebsdev/DEV_ebsdev_ifile.ora

ebsdev伺服器顯示的程式資訊
[root@ebsdev ~]# ps -ef|grep smon
root 8569 7577 0 14:46 pts/0 00:00:00 grep smon
oradev 24237 1 0 10:33 ? 00:00:02 ora_smon_DEV
[root@ebsdev ~]# ps -ef|grep pmon
root 8571 7577 0 14:46 pts/0 00:00:00 grep pmon
oradev 24211 1 0 10:33 ? 00:00:03 ora_pmon_DEV
[root@ebsdev ~]# ps -ef|grep lsn
appldev 5090 1 0 13:50 ? 00:00:00 /u02/DEV/apps/tech_st/10.1.2/bin/tnslsnr APPS_DEV -inherit
root 8576 7577 0 14:46 pts/0 00:00:00 grep lsn
oradev 24340 1 0 Feb23 ? 00:00:01 /u02/DEV/db/tech_st/11.2.0/bin/tnslsnr dev -inherit
--以上雖然顯示兩個監聽器，但在appldev執行lsnrctl status apps_dev時會報錯，說不存在appl_dev的監聽器名稱

應用在單獨的機器上，不和DB在同一臺機器上時
[applprod@YDerp ~]$ lsnrctl status prod
LSNRCTL for Linux: Version 10.1.0.5.0 - Production on 27-FEB-2018 14:09:07
Copyright (c) 1991, 2004, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=EBSDB.YUD.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=prod)(INSTANCE_NAME=PROD)))
TNS-01189: The listener could not authenticate the user

[root@YDerp ~]# ps -ef|grep smon
root 2287 2243 0 14:57 pts/2 00:00:00 grep smon
[root@YDerp ~]# ps -ef|grep pmon
root 2291 2243 0 14:57 pts/2 00:00:00 grep pmon
[root@YDerp ~]# ps -ef|grep lsn
applprod 2209 1 0 2017 ? 00:00:00 /app/prod/apps/tech_st/10.1.2/bin/tnslsnr APPS_PROD -inherit
root 2297 2243 0 14:57 pts/2 00:00:00 grep lsn

應用指向的DB伺服器
[root@EBSDB ~]# ps -ef|grep smon
oraprod 13129 1 0 2017 ? 02:39:43 ora_smon_PROD
root 35808 35766 0 14:56 pts/0 00:00:00 grep smon
[root@EBSDB ~]# ps -ef|grep pmon
oraprod 13083 1 0 2017 ? 01:29:07 ora_pmon_PROD
root 35820 35766 0 14:56 pts/0 00:00:00 grep pmon
[root@EBSDB ~]# ps -ef|grep lsn
oraprod 13751 1 0 2017 ? 11:38:10 /db/prod/db/tech_st/11.2.0/bin/tnslsnr prod -inherit
root 35848 35766 0 14:56 pts/0 00:00:00 grep lsn

ORACLE ERP的應用沒有自己的instance例項，這點和ASM不一樣

相關文章