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: 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 entries Cumbersome to work with/edit with a large number of entries If centralized, problems affect all users If localized.

3 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? Supported platforms? Additional software required? Additional hardware required?

4 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?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.

5 = 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! LDAP tools in every Database and Client homeWHAT S THE DOWNSIDE?

6 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. (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?

7 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? 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: TO WATCH OUT FOR Methods not specified are excluded Also determines search order Must keep EZCONNECT for RAC cluster interconnect Files searched Remember: / Hidden file: ~/.

8 Windows Different search order rules (cwdvs. home dir) Different search orders if %ORACLE_HOME% is setVIRTUAL DEMO 1 OpenLDAP setup on 10 simple steps!DEMO1: OpenLDAPSETUP STEP 1: Install the required RPMsDEMO1: OpenLDAPSETUP STEP 2: Some basic initial setup STEP 3: Set the LDAP admin password Record the hash for use laterDEMO1: OpenLDAPSETUP STEP 4: Create a default configuration file STEP 5: Create the OID schema filesDEMO1: OpenLDAPSETUP STEP 6: Edit /etc/ Add new OID schema files Update all occurrences of my-domain Add rootpwhash value (could use plain text as well)DEMO1: OpenLDAPSETUP STEP 7: Start and register slapdservice STEP 8: Manually add the OU to the rootDEMO1: OpenLDAPSETUP STEP 9: Add the orclContextand the first entryDEMO1: OpenLDAPSETUP STEP 10: Adjust : OpenLDAPSETUP Additional optionalsteps Add master and slave(s) replication (HA) Secure with TLS and a certificate Configure Apache Directory Studio Script simplified additions using ldapadd Script generation using ldapsearchVIRTUAL DEMO 2 Active Directory SetupIn < 10 simple steps!

9 DEMO 2: ACTIVE DIRECTORY SETUP STEP 1: Follow steps provided in Oracle PDF Configuring Microsoft Active Directory for Net Naming (Doc ID )DEMO 2: ACTIVE DIRECTORY SETUP STEP 2: Adjust 2: ACTIVE DIRECTORY SETUP STEP 3: Add an entry Using the Oracle Net Manager utility on the DC Under the Directory tabDEMO 2: ACTIVE DIRECTORY SETUP STEP 4: Verify the entry Using Active Directory Users and Computers DEMO 2: ACTIVE DIRECTORY SETUP STEP 5: Verify that the entry can be modified Using Active Directory Explorer (Sysinternals)DEMO 2: ACTIVE DIRECTORY SETUP STEP 6: Test that data can be extracted Using ldapsearch DEMO 2: ACTIVE DIRECTORY SETUP STEP 7: Test Resolution from WindowsDEMO 2: ACTIVE DIRECTORY SETUP STEP 8: Test Resolution from LinuxWRAP UP!SUMMARY 1 OID, Active Directory, and OpenLDAPare 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 OpenLDAPare the easiest to setupSUMMARY 2 Initial data can be bulk loaded Data can be extracted to a Simple scripts can be used to automate: Creation of new entries Extraction into a LDAP utilities are already in every $OHSUMMARY 3 Cost is typically a few days of initial setup work Include setup and procedural documentation!

10 !!! 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 availabilityTHANKS AND