tnsnames.ora配置小结

listener.ora tnsnames.orasqlnet.ora3个文件是关系oracle网络配置的3个主要文件,其中listener.ora是和数据库服务器端 相关,而tnsnames.orasqlnet.ora2个文件不仅仅关系到服务器端,主要的还是和客户端关系紧密。
检查客户端oracle网络的时候可以先检查sqlnet.ora文件:

# SQLNET.ORA Network Configuration File: $ORACLE_HOME/NETWORK/ADMIN/sqlnet.ora
# Generated by Oracle configuration tools.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, HOSTNAME, ONAMES)

##NAMES.DEFAULT_DOMAIN = us.oracle.com

上面的sqlnet.ora文件说明:
SQLNET.AUTHENTICATION_SERVICES= (NTS)——
这个表示采用os认证,在数据库服务器上,可以利用sqlplus “/ as sysdba”。一般这个配置在windows上是ok的,在unix环境下可能会有问题,一般在unix下可以去掉这个配置。

NAMES.DIRECTORY_PATH= (TNSNAMES, HOSTNAME, ONAMES)——表示将首先利用tnsnames进行解析;如果tnsnames解析不到,将使用hostname解析;如果hostname解析不 到,将采用onames进行解析。

被注释掉的NAMES.DEFAULT_DOMAIN = us.oracle.com——表示采用默认的domain nameus.oracle.com,在tnsnames.ora中如果配置对应的解析,如果原来的别名oralocal,那么,当启用这个参数后,在 tnsnames中的配置要改成oralocal.us.oracle.com。在使用tnsping时或者sqlplus登录时,只需写前面的别名,系 统会自动加上后面的domain name来进行解析。

检查完毕sqlnet.ora,一般都会发现是使用tnsname来解析别名的,那么,tnsnames.ora中可以有哪些配置种类呢?

# TNSNAMES.ORA Network Configuration File: $ORACLE_HOME/NETWORK/ADMIN/tnsnames.ora
# Generated by Oracle configuration tools.


###### 一般的配置 ##################################
ORALOCAL =
  
(DESCRIPTION =
    
(ADDRESS_LIST =
      
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    
)
    
(CONNECT_DATA =
      
(SERVER = DEDICATED)
      
(SERVICE_NAME = oralocal)
    
)
  
)


###### 这样也行,用SID=oralocal ###########################
ORALOCAL_2 =
  
(DESCRIPTION =
    
(ADDRESS_LIST =
      
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    
)
    
(CONNECT_DATA =
      
(SERVER = DEDICATED)
      
(SID = oralocal)
    
)

###### RAC的配置(3节点rac) ###############################
ORALOCAL =
  
(DESCRIPTION =
  
(load_balance = yes)
  
(failover = on)
    
(ADDRESS_LIST =
      
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1521))
      
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT = 1521))
      
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.3)(PORT = 1521))   
    
)
    
(CONNECT_DATA =
        
(SERVICE_NAME = oralocal)
        
(SERVER = DEDICATED)
        
(failover_mode=(type=select)(method=basic)(retries=20)(delay=20))
    
)
  
)
  
ORALOCAL_NODE1 =
   
(DESCRIPTION =
     
(ADDRESS_LIST =
       
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1521))
       
)
       
(CONNECT_DATA =
         
(SERVICE_NAME = oralocal)
         
(INSTANCE_NAME = oralocal_node1)
       
)
   
)


ORALOCAL_NODE2=
   
(DESCRIPTION =
     
(ADDRESS_LIST =
       
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT = 1521))
       
)
       
(CONNECT_DATA =
         
(SERVICE_NAME = oralocal)
         
(INSTANCE_NAME = oralocal_node2)
       
)
   
)
   

ORALOCAL_NODE3 =
   
(DESCRIPTION =
     
(ADDRESS_LIST =
       
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.3)(PORT = 1521))
       
)
       
(CONNECT_DATA =
         
(SERVICE_NAME = oralocal)
         
(INSTANCE_NAME = oralocal_node3)
       
)
   
)


  
)


###### DATA GUARD配置(primary库和standby库都需要配置)##############
standby =
  
(DESCRIPTION =
    
(ADDRESS_LIST =
      
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.2)(PORT = 1521))
        
)
    
(CONNECT_DATA =
          
(SERVER=DEDICATED)
          
(SERVICE_NAME = oralocal)
      
)
  
)

primary =
  
(DESCRIPTION =
    
(ADDRESS_LIST =
      
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.1)(PORT = 1521))
        
)
    
(CONNECT_DATA =
          
(SERVER=DEDICATED)
          
(SERVICE_NAME = oralocal)
      
)
  
)

另外需要注意的2点情况:
1)如果tnsnames中的service_name配置错误,配置成了instance_name了,这个时候会发生tnsping能通,但是 sqlplus连接不上的奇怪情况。报错ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect descriptor。这个时候查错的时候,需要检查对应的service_name
2)如果远程数据库是rac,而且本地客户端端远程数据库处于不同的网段,通过公网链接,rac对外的ip映射只有一个,即只映射到一个节点。请注意在 客户端配置tnsnames的时候按照单机的情况来配置。呵呵,dba不仅仅要学习oracle,了解一些网络的知识,特别是自己系统的网络架构,也是需 要的。

 

阅读更多
个人分类: ORACLE
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭