关于监听，远程连接INSATNCE_NAME SID DB_NAME

监听部分已经困扰我许久许久了：

下面所述监听均是在数据库启动情况下的动态监听

动态监听请看这个博文：https://blog.csdn.net/songyundong1993/article/details/52635598

数据库通过NETCA创建监听之后，启动了监听就可以提供远程连接服务了,端口号默认1521，可以自定义。

下图中本机IP地址DG100：192.168.56.100  

远程主机在pfile中写入了dbname=prod （IP地址：192.168.56.101） ORACLE_SID=PRODSTD

[oracle@DG100 admin]$ more listener.ora 
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = DG100)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

[oracle@DG100 admin]$ more tnsnames.ora 
tj =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.101)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PROD)
    )
  )

bj =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.100)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PROD)
    )
  )
[oracle@DG100 admin]$ 

列出两个数据库所有的信息：

DG100的DB_NAME

[oracle@DG100 admin]$ echo $ORACLE_SID
PROD

[oracle@DG100 admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun May 16 16:24:59 2021

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      PROD
SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
PROD

SQL> 

DG101的DBNAME和INSTANCE_NAME

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/11.2.0
                                                 /dbhome_1/dbs/spfilePRODSTD.or
                                                 a
SQL> create pfile from spfile
  2  ;

File created.

SQL> 
SQL> 
SQL> 
SQL> show parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      prod
SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
PRODSTD

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
[oracle@DG101 ~]$ echo $ORACLE_SID
PRODSTD

 

如何检查数据库连接是本地连接还是远程连接？

LOCAL=NO即是远程连接：

 

关于LISTENER文件和TNSNAME.ORA文件说明：

最上面的图是一个正确的格式，可以提供服务：

LISTENER.ORA文件

TNSNAMES.ORA文件  就是客户端用来连接远程数据库的关键所在：

稍有不慎就是

ORA-12514 错误

修改了文件内容 原来是PROD（db_name）现在修改成了SID/instance_name 

  

[oracle@DG100 admin]$ sqlplus sys/12345678@tj as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun May 16 16:36:52 2021

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

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor


Enter user-name: 

ORA-12154错误：

这种错误就是tnsnames文件的格式错误。

 

 

以上仅对我是对我自己的认知做提示，一定存在误解。但目前我只能这样帮助自己去理解。

提供一个连接https://blog.csdn.net/elvis_dataguru/article/details/8984961

jrojyun

2021-05-16