GLOBAL_DBNAME&SID_NAME&SERVICE_NAME&ORA-12537: TNS:connection closed

最新推荐文章于 2024-06-26 23:53:45 发布

redelego@cloud

最新推荐文章于 2024-06-26 23:53:45 发布

阅读量2.6k

点赞数 1

CC 4.0 BY-SA版权

分类专栏： docker&k8s oracle os 文章标签： oracle 数据库

本文链接：https://blog.csdn.net/u011078141/article/details/107410573

oracle 同时被 3 个专栏收录

62 篇文章

订阅专栏

23 篇文章

订阅专栏

docker&k8s

15 篇文章

订阅专栏

本文深入探讨了Oracle数据库中的GLOBAL_DBNAME、SID_NAME、SERVICE_NAME等概念及其区别，详细解释了这些参数的含义、作用及配置方法，并通过实例展示了如何在CDB和PDB环境下验证这些参数的设置。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

User:Administrator

Last Update:2020-7-17 12:01

### Code Reference

URL:https://docs.oracle.com/database/121/NETAG/listenercfg.htm#NETAG302
URL:https://blog.csdn.net/weixin_43885834/article/details/104435592
DESC:GLOBAL_DBNAME&SID_NAME&SERVICE_NAME&ORA-12537: TNS:connection closed
Last Update:2020-7-17 12:02
Time：2020-7-17 12:02 Tittle：GLOBAL_DBNAME&SID_NAME&SERVICE_NAME&ORA-12537: TNS:connection closed

Version:002

GLOBAL_DBNAME&SID_NAME&SERVICE_NAME的概念和区别
- glocal_name
  
  对一个数据库的唯一标识，在创建数据库的时候决定，缺省值为db_name.db_domain。在之后对参数文件中db_name和db_domain参数的任何修改都不影响global_name的值，如果要修改glocal_name,只能alter database rename global_name to <db_name,db_domain>来进行修改，然后修改相应的参数
- service_name
  
  在oracle的并行环境中，一个数据库对应多个实例，就需要多个网络服务名，设置比较繁琐。service_names参数就是为了解决这个问题，该参数对应一个数据库，而不是一个实例，缺省值为db_name.db_domain，即等于global_name。一个数据库可以对应多个service_name.
- oracle_sid
  
  oracle_sid这个参数是操作系统中用到的，他是描述默认连接的数据库实例.instance_name是数据库参数。而oracle_sid是操作系统的环境变量，oracle_sid必须与instance_name的值一致。

验证

CDB&PDB instance name
```
  -- 查看CDB实例名称(数据库参数)
  show parameter instance_name;
  
  -- 查看PDB实例名称(和CDB一致)
  alter session set container=PDB2;
  show parameter instance_name;
```
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-JgBASiP2-1594972759934)(http://192.168.100.132/uploads/big/6b01443f3ee9e71ec9a59bc4007b20ac.png)]

CDB&PDB glocal_name

  -- 查看CDB global_name名称(数据库参数ORCDB.EXAMPLE.COM)
  select * from global_name;
  
  -- 查看PDB global_name名称(和CDB不一致PDB2.EXAMPLE.COM)
  alter session set container=PDB2;
  select * from global_name;

listener.ora&tnsnames.ora

listener.ora

  LISTENER =
    (DESCRIPTION_LIST =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 9cfde0470b8a)(PORT = 1521))
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      )
    )
  
  SID_LIST_LISTENER =
    (SID_LIST =
      
      (SID_DESC =
        (GLOBAL_DBNAME = orcdb.example.com)
        (ORACLE_HOME = /u01/app/oracle/product/12.1.0.2/dbhome_1)
        (SID_NAME = cdb2)
      )
      
      (SID_DESC =
        (GLOBAL_DBNAME = pdb2.example.com)
        (SID_NAME = cdb2)
      )
      
      (SID_DESC =
        (GLOBAL_DBNAME = pdb3.example.com)
        (SID_NAME = cdb2)
      )
    )
  ADR_BASE_LISTENER=/u01/app/oracle

tnsnames.ora

  CDB2 =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 9cfde0470b8a)(PORT = 1521))
      (CONNECT_DATA =
        (SERVER = DEDICATED)
        (SERVICE_NAME = orcdb.example.com)
      )
    )
    
  PDB2 =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 9cfde0470b8a)(PORT = 1521))
      (CONNECT_DATA =
        (SERVER = DEDICATED)
        (SERVICE_NAME = pdb2.example.com)
      )
    )
  
  PDB3 =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 9cfde0470b8a)(PORT = 1521))
      (CONNECT_DATA =
        (SERVER = DEDICATED)
        (SERVICE_NAME = pdb3.example.com)
      )
    )
    
  # instance name    
  LISTENER_cdb2 =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 9cfde0470b8a)(PORT = 1521))