ORACLE ERP的应用没有自己的instance实例，这点和ASM不一样

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30126024/viewspace-2151322/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30126024/viewspace-2151322/