Oracle Net Service Name
Resolution
Getting Rid of the TNSNAMES.ORA File!
Simon Pane – Oracle Database Principal Consultant March 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
ABOUT PYTHIAN
10,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 Cloudera Champion of Big Data • Broad technical experience
• Oracle, Microsoft, MySQL, Oracle EBS, Hadoop, Cassandra, MongoDB,virtualization, configuration management, monitoring, trending, and more.
TARGET AUDIENCE
• This presentation is for
– Not Sys Admins
– Not Network Admins – Not LDAP Admins
NET SERVICE NAME RESOLUTION
WHAT ARE WE TALKING ABOUT?
• 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 TNSNAMES.ORA file
– “The tnsnames.ora file 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.”
THE BASICS: THE CONNECT DESCRIPTOR
• Everything could be specified at the prompt
THE BASICS: EZCONNECT
• 10g added EZCONNECT
THE BASICS: NET SERVICE NAME SEARCH
• Net Service Name can be found in multiple locations
– TNSNAMES.ORA files, external service, directory server
STORING AS UNSTRUCTURED DATA
• “Unstructured” – not in a database
• DNS is somewhat similar yet DNS entries aren’t stored in host files
• In the TNSNAMES.ORA the “Connect
Descriptors” aren’t consistent in structure or layout
MANAGEMENT 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
PROBLEMS WITH THIS APPROACH
• One typo can corrupt the current and all subsequent 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
“BUT WE'VE ALWAYS DONE IT THAT WAY”
WHAT ARE THE OPTIONS
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
STRUCTURE IN A “DIRECTORY SERVER”
• Published “LDAP Schema for Oracle Net Services”• “Structural LDAP Classes” for Oracle Net:
orclDBServer orclNetService orclNetServiceAlias orclNetDescription orclNetDescriptionList orclNetAddress orclNetAddressList orclNetDescriptionAux1 orclNetAddressAux1
CHOOSING A DIRECTORY SERVER
• Easy to install and setup? • 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 TNSNAMES.ORA file?
• Supports advanced entries (i.e. 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 TNSNAMES.ORA file generation • Easy to handle multiple “contexts”
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)
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
ACTIVE 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:
ACTIVE DIRECTORY ISSUES
• Will need cooperation from Domain Admins to install / configure
• Extra AD permissions may be required to query • 11g Clients:
– NAMES.LDAP_AUTHENTICATE_BIND = YES
• Anonymous query may be required for UNIX clients
OPENLDAP BENEFITS
• Free (open-source) Directory Server software available on a variety of platforms
– Linux, Solaris, MacOS X, Windows, etc
• Master-slave replication options
– Including multiple slaves, platform, cross-endian
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
OPENLDAP ISSUES
• No GUI included
– Using with Oracle Net Manager is difficult
• Apache Directory Studio
COMMON FUNCTIONALITY
• All have (in some form or another)
– Bulk load ability: ldapadd –f <file>
– Command line searching: ldapsearch
– Extraction to a TNSNAMES.ORA file via tool or command
TOOLS ARE ALREADY INSTALLED!
WHAT’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
SUPPORT RISKS?
• If using an unsupported Directory Server, DBAs must know how to investigate/resolve some
problems
– Oracle Support will be limited when investigating
FAILOVER PERFORMANCE?
• Test failover times from an unresponsive master server!
• Related MOS notes:
– Slow LDAP Naming Resolution when Primary LDAP server unavailable. (Doc ID 1193853.1)
– Performance problem with Oracle*Net Failover when TCP Network down (no IP address) (Doc ID 249213.1)
– How to Setup LDAP Client Naming Resolution Failover Timeout Against OID - If OID1 is Busy, Quickly Try OID2. (Doc ID 1671486.1)
BUT REMEMBER…
• 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 TNSNAMES.ORA
• Some applications may not support it
FUNCTIONALITY RISKS?
• Extra complexity with advanced options
– TAF entries, RAC entries, global_name
– Oracle Net aliases
• Oracle7 and Oracle8.0 clients
DEBUGGING TECHNIQUES: TRACING
• Oracle Net (SQL*Net) Tracing
– HOWTO : Use sqlnet tracing to track down which tnsnames.ora file is used in the connection? (Doc ID 846822.1)
– How to Enable Oracle SQLNet Client , Server , Listener , Kerberos and External procedure Tracing from Net Manager (Doc ID 395525.1)
• Oracle whitepaper on interpreting the result
– Examining Oracle Net, Net8, SQL*Net Trace Files (Doc ID 156485.1)
• Trace Assistant
– Example of Using Trace Assistant (TRCASST) to Work an Oracle Net issue (Doc ID 1336069.1)
DEBUGGING TECHNIQUES: TRCROUTE
• Oracle Trace Route utility
– Reports on TNS entries on route to the “server” – https://docs.oracle.com/database/121/NETAG/connect.htm#NETAG383
DEBUGGING TECHNIQUES: OS TOOLS
• Linux
– Strace:
• $ strace tnsping ORCL
• Windows
– Windows Sysinternals Process Monitor:
• Run in batch file with command line switches – NtTrace:
THINGS TO WATCH OUT FOR
• NAMES.DIRECTORY_PATH
– Methods not specified are excluded – Also determines search order
– Must keep EZCONNECT for RAC cluster interconnect
• Files searched
– Remember: /etc/tnsnames.ora
– Hidden file: ~/.tnsnames.ora
• Windows
– Different search order rules (cwd vs. home dir)
VIRTUAL DEMO 1
OpenLDAP setup on OL6.5
DEMO1: OpenLDAP SETUP
DEMO1: OpenLDAP SETUP
• STEP 2: Some basic initial setup
• STEP 3: Set the LDAP admin password
DEMO1: OpenLDAP SETUP
• STEP 4: Create a default configuration file
DEMO1: OpenLDAP SETUP
• STEP 6: Edit /etc/openldap/slapd.conf
– Add new OID schema files
– Update all occurrences of “my-domain”
DEMO1: OpenLDAP SETUP
• STEP 7: Start and register slapd service
DEMO1: OpenLDAP SETUP
• STEP 9: Add the orclContext and the first entry
DEMO1: OpenLDAP SETUP
DEMO1: OpenLDAP SETUP
• Additional optional steps
– Add master and slave(s) replication (HA) – Secure with TLS and a certificate
– Configure Apache Directory Studio
– Script simplified additions using ldapadd
VIRTUAL DEMO 2
Active Directory Setup
DEMO 2: ACTIVE DIRECTORY SETUP
• STEP 1: Follow steps provided in Oracle PDF
DEMO 2: ACTIVE DIRECTORY SETUP
DEMO 2: ACTIVE DIRECTORY SETUP
• STEP 3: Add an entry
– Using the Oracle Net Manager utility on the DC – Under the “Directory” tab
DEMO 2: ACTIVE DIRECTORY SETUP
• STEP 4: Verify the entry
DEMO 2: ACTIVE DIRECTORY SETUP
• STEP 5: Verify that the entry can be modified
DEMO 2: ACTIVE DIRECTORY SETUP
• STEP 6: Test that data can be extracted
DEMO 2: ACTIVE DIRECTORY SETUP
DEMO 2: ACTIVE DIRECTORY SETUP
SUMMARY 1
• OID, Active Directory, and OpenLDAP are all just three out of many possible LDAP Directory
Servers software products
• Oracle “Connect Descriptors” can be stored and accessed from any LDAP Directory Server
• Active Directory and OpenLDAP are the easiest to setup
SUMMARY 2
• Initial data can be bulk loaded
• Data can be extracted to a TNSNAMES.ORA • Simple scripts can be used to automate:
– Creation of new entries
– Extraction into a TNSNAMES.ORA
SUMMARY 3
• Cost is typically a few days of initial setup work
– Include setup and procedural documentation!!!!
• Deployment risk is minimal
– As hybrid approach can be used
• Lower risk of issues if stored in a proper Directory Service
– Reduced propagation time for additions/changes – Lower chance of introducing a widespread error – Higher availability
