Oracle Net Service Name Resolution - COUG

1 Oracle Net Service name ResolutionGetting Rid of the File!Simon Pane Oracle Database Principal ConsultantMarch 19, 2015 ABOUT ME Working with the Oracle DB since version 6 Oracle Certified Expert Oracle Certified Professional Oracle Database 8, 8i, 9i, 10g, 11g and 12c Oracle Certified Partner Specialist Oracle ACE Associate MOS Communities: Simon_DBA Level: Expert ABOUT PYTHIAN10,000 Pythian currently manages more than 10,000 systems. 385 Pythian currently employs more than 385 people in 30 countries worldwide. 1997 Pythian was founded in 1997 Global leader in data consulting and managed services . Unparalleled expertise Top 5% in databases, applications, infrastructure, Big Data, Cloud, Data Science, and DevOps Unmatched certifications 8 Oracle ACEs, 2 Oracle ACE Directors, 2 Oracle ACE Associates, 2 Oracle Certified Masters, 5 Microsoft MVPs, 1 Microsoft Certified Master 1 ClouderaChampion of Big Data Broad technical experience Oracle , Microsoft, MySQL, Oracle EBS, Hadoop, Cassandra, MongoDB,virtualization, configuration management, monitoring, trending, and AUDIENCE This presentation is for Not Sys Admins Not Network Admins Not LDAP AdminsNET Service name RESOLUTIONA Quick RefresherWHAT ARE WE TALKING ABOUT?

2 Net Service name A simple name for a Service that resolves to a connect descriptor Connect Descriptor A specially formatted description of the destination for a network connection. A connect descriptor contains destination Service and network route information. The file The is a configuration file that contains net Service names mapped to connect descriptors for the local naming method, or net Service names mapped to listener protocol addresses. Source: BASICS: THE CONNECT DESCRIPTOR Everything could be specified at the prompt Good for testing the string/troubleshootingTHE BASICS: EZCONNECT 10g added EZCONNECT shortened command line specificationTHE BASICS.

3 NET Service name SEARCH Net Service name can be found in multiple locations files, external Service , directory server Oracle Net stops searching when it finds the first oneSTORING AS UNSTRUCTURED DATA Unstructured not in a database DNS is somewhat similar yet DNS entries aren t stored in host files In the the Connect Descriptors aren t consistent in structure or layoutMANAGEMENT TECHNIQUES Scripts that run nightly to push out new files to all servers and desktops Centralized files using the TNS_ADMIN environment variable or soft links Storing on a network share or NFS mount Centralized using the IFILE parameter Can be used up to four timesPROBLEMS WITH THIS APPROACH One typo can corrupt the current and all subsequent

4 Entries Cumbersome to work with/edit with a large number of entries If centralized, problems affect all users If localized, may take time to propagate changes Multiple copies can get out of sync changes clobbered BUT WE'VE ALWAYS DONE IT THAT WAY Old way doesn't mean it's the best way WHAT ARE THE OPTIONSHow can we make things better?ALTERNATIVES Store in an LDAP compatible Directory Server Oracle Internet Directory (OID) Microsoft Active Directory (AD) OpenLDAP Others (IBM Tivoli Directory Server, Sun Java System Directory Server, Red Hat Directory Server, Apache Directory Server) EZCONNECT A hybrid approach using all methodsSTRUCTURE IN A DIRECTORY SERVER Published LDAP Schema for Oracle Net services Structural LDAP Classes for Oracle Net:orclDBServerorclNetServiceorclNetSer viceAliasorclNetDescriptionorclNetDescri ptionListorclNetAddressorclNetAddressLis torclNetDescriptionAux1orclNetAddressAux 1 CHOOSING A DIRECTORY SERVER Easy to install and setup?

5 Supported platforms? Additional software required? Additional hardware required? Additional licenses required? Bulk load existing entries? Easy additions? Easy modifications and removals? Ability to export to a file? Supports advanced entries ( TAF, RAC, other options)? Supports aliases? High availability and protection (backup options)? Security implications?OID BENEFITS Complete Oracle stack full Oracle Support Data stored in the Oracle Database DBAs know how to manage / backup High availability options Easy file generation Easy to handle multiple contexts ( .world, . )OID ISSUES Requires a WebLogic domain Cumbersome, likely difficult for most DBAs May require additional hardware For Oracle database repository and/or WLS Upgrades and patching (WLS & DB) Overkill for justNet Service name lookup?

6 ACTIVE DIRECTORY BENEFITS Register databases via Oracle Tools (optional) DBCA or Oracle Net Manager SA handles: Replication, HA, Patches, Updates, Backups, etc Critical part of the network infrastructure Typically high performanceACTIVE DIRECTORY SETUP Very easy to setup (Demo later) Requires access to the AD on a DC Need Domain Administrator privileges Implement using Oracle Net Configuration Assistant and Oracle Net Manager Follow Oracle Implementation PDF guides Follow step-by-step guides: Configuring Microsoft Active Directory for Net Naming (Doc ID )ACTIVE DIRECTORY ISSUES Will need cooperation from Domain Admins to install / configure Extra AD permissions may be required to query 11g Clients.

7 = YES Anonymous query may be required for UNIX clientsOPENLDAP BENEFITS Free (open-source) Directory Server software available on a variety of platforms Linux, Solaris, MacOSX, Windows, etc Master-slave replication options Including multiple slaves, cross-platform, cross-endian Easy updates ( yum for Linux deployments)OPENLDAP INSTALLATION Install additional RPMs openldap-servers, openldap-clients slapd= stand-alone LDAP directory server Simple initial setup (Demo later) Customize some text files; run commands; etc Requires some basic Linux skills Will need rootaccessOPENLDAP ISSUES No GUI included Using with Oracle Net Manager is difficult Apache Directory Studio Free for Windows, Mac & LinuxCOMMON FUNCTIONALITY All have (in some form or another) Bulk load ability: ldapadd f <file> Command line searching: ldapsearch Extraction to a file via tool or commandTOOLS ARE ALREADY INSTALLED!

8 LDAP tools in every Database and Client homeWHAT S THE DOWNSIDE?Risks, Concerns, Supportability, Troubleshooting?WHAT ABOUT SUPPORT? With OID the whole stack is supported Resolution via AD also supported Net Service name Resolution from other Directory services not fully supported But is that really an issue?SUPPORT RISKS? If using an unsupported Directory Server, DBAs must know how to investigate/resolve some problems Oracle Support will be limited when investigating TNS-03505via SR when not using AD or OIDFAILOVER PERFORMANCE? Test failover times from an unresponsive master server! Related MOS notes: Slow LDAP Naming Resolution when Primary LDAP server unavailable.

9 (Doc ID ) Performance problem with Oracle *Net Failover when TCP Network down (no IP address) (Doc ID ) How to Setup LDAP Client Naming Resolution Failover Timeout Against OID -If OID1 is Busy, Quickly Try OID2. (Doc ID )BUT Used for initial connection lookup only Listener sends back a new socket Not used again for persistent connections Not used for RAC interconnect Data Guard & DB Links Optionally configure with EZCONNECT if support is a concern OTHER RISKS? Slow / no response from the Directory Servers? All options offer redundancy or high availability Worst case, switch back to Some applications may not support it Might need some one-off filesFUNCTIONALITY RISKS?

10 Extra complexity with advanced options TAF entries, RAC entries, global_name Oracle Net aliases Oracle7 and clients Still can be done but requires extra/different stepsDEBUGGING TECHNIQUES: TRACING Oracle Net (SQL*Net) Tracing HOWTO : Use sqlnettracing to track down which is used in the connection? (Doc ID ) How to Enable Oracle SQLNetClient , Server , Listener , Kerberos and External procedure Tracing from Net Manager (Doc ID ) Oracle whitepaper on interpreting the result Examining Oracle Net, Net8, SQL*Net Trace Files (Doc ID ) Trace Assistant Example of Using Trace Assistant (TRCASST) to Work an Oracle Net issue (Doc ID )DEBUGGING TECHNIQUES: TRCROUTE Oracle Trace Route utility Reports on TNS entries on route to the server #NETAG383 DEBUGGING TECHNIQUES: OS TOOLS Linux Strace: $ stracetnspingORCL Windows Windows SysinternalsProcess Monitor: Run in batch file with command line switches NtTrace.