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

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))

    • Snippet:ORA-12537: TNS:connection closed

      Jupyter Code

  • 参考