Expert Insights: Learn About Oracle Net Service Name Resolution

Just another WordPress site

Expert Insights: Learn About Oracle Net Service Name Resolution

hello and welcome the patheon’s expert insights a series dedicated to providing expert knowledge on the bleeding edge of technology today we are joined by one of pythias oracle database principal consultants Simon pin to discuss Oracle net service named resolutions getting rid of the tnsnames.ora file hello everybody and welcome to this presentation on oracle net services and getting rid of the tnsnames.ora file there are better solutions my name is Simon I’m a Oracle principal consultant here at Pythian I’ve been working in the Oracle space for almost my whole career which is almost about 20 years so really i started at oracle version 6 and it worked right the way up to version 12 see obviously along the way i have lots of oracle credentials as you see here and i speak quite regularly and handsome orca lace associate currently anyway let’s dive into today’s presentation this presentation is targeted really for the Oracle DBA s now people worry and they see words like ldap on the screen of F hat dba’s ask me what is ldap that’s okay today’s presentation is designed to give you some background about these things and explain what some of these terms and definitions mean so it’s really intended for oracle DBA s it is not a presentation at saying that system administrators or network administrator specifically all right so first of all in order to make the conversation go smoothly I like to give a little refresher of some of the terms and technologies so what are we talking about here well first of all a net service name in the Oracle contacts context is really what we type behind the username and password in a connection strength okay so we might say connect Scott / tiger at ORCL ORCL is really called the net service thing sure we may call it by other names just casually or for lack of proper definition but that’s what we the proper definition is now what that translates to is most Oracle deviation know is what’s called a full connect descriptor so that’s where we have description equals address equals and all that stuff that follows that then all oracle DBA should be quite familiar with and of course it almost goes without saying most of the time we find those in the tnsnames.ora file again every DBA knows that and probably knows it quite well and that’s really what the purpose of today’s presentation is as was mentioned in the title or what are the alternatives to the tnsnames.ora file okay going back to the very basics well first of all we can put all of that connect descriptor information right in at the command prompt and we have been able to for for quite a while so in these two examples here you can see first I do a tea nest pain from a unix or actually a Linux command prompt and I can put the full connect descriptor in there so I’m not relying on a TNS names entry or net service name at all similarly in the second example you can see I’m doing exact same thing but impaired providing the credentials as well and actually connecting to the database to make sure I’m going that step further whereas at ens paying only communicates with the listener make sure that the listeners responsive here I’m going in to end up making sure I can connect to the database right so that’s pretty simple to do but people often overlook that functionality or forget or maybe don’t know that you actually don’t need any resolution at all you can provide everything at the command line as an argument if you like now that’s typically not done because it’s long and cumbersome to write in or implement but it is sometimes good for testing and troubleshooting in oracle 10g they added the new easy connect functionality which really shortened what we saw in the above this previous screens allowing us to just specify the host port if not 1521 by default and the servicing and again you can see we can easily use the easy connect syntax as long as we’ve specified easy connecting our sequel letter or a file and it works for both at TNS paying and an actual database connection one thing that DBA is often overlooked is really how many different places there may be a for the TNS name store or a file right people think of the obvious location which is the dollar or go home / network admin but often overlooked some of the other ones now you can see on the screen shot here i’m doing an s trace of a linux TNS ping command i just put in XYZ as the host or the the

descriptor that i’m trying to pain just as a dummy andrey just to see how many different places it looked so there’s a couple surprising ones in there like there’s things that you would expect to see like it first touch the sequel method or that’s an oracle home network admin but then there’s some other ones that are maybe a little bit more obscure and that DPAs may not even know about like for example a hidden dns name stood or a file starting with a period that’s in the home directory of the oracle user or one in the et Cie directory anyway they command that I’ve shown there is a good utility or a good command that dba’s can run to really understand on their system and given their environment and their environment variables where Oracle will actually search for at TNS names that were a fall when it’s trying to redo resolution okay and and it’s maybe you’re going to be a little bit surprising to them as to how many places it does actually search the important thing to understand though is to put in a dummy entry like I did here with the XYZ because that way it’ll you’ll force it to list out for you all the different places they could possibly search where as I’ve written here in reality it stops once it finds the first match now why do I not like the tnsnames.ora file well first of all it’s unstructured data and dbas are all about structured data dbas and the concept of databases is that we don’t have tons and tons of unstructured flat files or Excel spreadsheets to run a business instead we put it in a structured repository called the database so why do dbas then have this unstructured tnsnames.ora file and I’ve seen variety of sizes of T nesting store files the largest i’ve seen have a roughly 1,400 or well over a thousand approaching a thousand and a half entries so we have this huge unstructured file and that presents some risk we we don’t do the same thing with DNS DNS is we could argue is conceptually similar okay except that the server level or we are resolving a server hosting however we never have huge host files on a desktop Windows desktop for example on a database server a UNIX or Linux database server no instead we always point to a DNS server that acts as a common centralized repository for dns entries and provides a resolution to us and the final thing is that since this is a free flow file the tnsnames.ora there’s lots of opportunity to make it inconsistent between entries it may be inconsistent in structure or in syntax and actual layout so it becomes a very difficult file to work with in my experience I’ve seen many many different techniques of how dbas and administrators system administrators may be managed having the TNS file or manage it throughout their environment of course I’ve seen jobs nightly batch jobs sometimes to push out the latest and greatest copy of that file to every server desktop in the environment I’ve seen centralization by using the TNS admin environment variable or soft links where they place it on a common network share maybe an NFS share maybe it’s a windows shared drive whatever and I’ve also seen a lot of linking of the files that may be you know the entry that’s pushed out or sorry the file is pushed out to everybody’s desk top doesn’t have all the entries but rather uses the AI file to create up to four soft links to other TNS files which may be in more centralized for common look so over my years have seen a variety of techniques for managing the TNS name’s Dora file however all of them have their challenges so what are some of the challenges well I’ve already touched on some of this first of all I think the most important issue or challenge with maintaining a large tnsnames.ora file is that you run the risk that if you have one bracket in the wrong place you can corrupt the file and you really corrupt it from that point onwards because it searches the file sequentially so if you have 1400 entries and you corrupt the third entry but with an extra bracket you potentially have made it impossible for Oracle to read all the subsequent 1400 minus three entries it’s also difficult and cumbersome to work with and there’s always some challenges when you go to eating management techniques that were presented in the previous slide if it’s centralized and you introduce a problem such as a bad bracket or misaligned Brack or an unmatched bracket you potentially can

affect every user in your organization if it’s a one centralized location if it’s localized meaning it’s copied every desktop well then there’s time that it’s required to propagate changes for example I mentioned previously there might be a bad job that runs nightly that pushes out changes so potentially you made a change to a service name for example a hostname or changed or new databases added recording a new entry and it takes some overnight or potentially the longer for the new copy you’d be pushed out and finally you run the risk if you’re gonna have all these different copies in your environment people might manually change them and hence that their changes clobbered when new for master copies are pushed out and and you run this risk of things getting out of sync and they’re being mismatched so overall there’s there’s a lot of different problems with this approach of trying to maintain at ens name’s doll or a file now that all being said I do find that almost all organizations or a very very high percentage of organization still use the TNS names to horrify it’s only a small percentage that has adopted some other technique and when I ask people why are you still doing it this way the answer almost every time is that’s the way we’ve always been doing it and we try to mitigate the risks the best we can we try to make sure that we don’t have unmatched brackets or we just know that it takes a day for changes to propagate and that’s just the way it is but my argument and the point of this presentation is to challenge that thought to say just because we did it that way in the past doesn’t mean that’s the best way or the way that we should be implementing it on a go-forward basis so the whole purpose of this presentation really is to stimulate thought and to challenge the old ways of doing things and to present some different ideas of what might be a better or more efficient way okay so what are our options and how can we make things better well there are a number of different options that I’m going to discuss in the next few minutes but they’re all based around this concept of an ldap compatible directory server so what three am I going to really talk about well three the three main ones are Oracle internet directory otherwise known as OID Microsoft Active Directory which I’m sure everybody is familiar with or at least of has heard of that name or ad I think that even organizations where the majority of user desktops are Macintosh based that are highly Apple friendly environment I still believe that even those apple mac desktop environments truly do have an Active Directory infrastructure buried down lower within their organization because likely they have a number of servers that run windows at the very minimum so I I mean of course there’s going to be exceptions but I’m rarely have seen an organization that doesn’t have Microsoft Active Directory as one of their core fundamental technologies somewhere in the environment a third one which I’m also going to discuss and that have had some hands-on experience with is a free open source product called openldap and others that I’m not going to touch on today but it certainly could be used in the same capacity as the concepts that we’re going to review here are numerous apache directory server and redhat directory server or maybe some of the more common ones but also some platform specific ones like IBM tivoli directory server and Sons java system directory server are alternatives that could be used so the purposes presentation is to stimulate the thought and give some background on how you can store these TNS entries for lack of better term and they did define that the proper definitions at the beginning of the presentation but Tina’s entry in reality is the name that most DBA is used to refer to a service named net descriptor well putting it in an LDAP compatible directory store is the best way and will be discussed further other alternatives you know is to not have one at all I just rely on Easy Connect where you’re providing all of the routing in connection information at the in the connection string as we’re shown earlier or maybe using a hybrid of all the different approaches all right well it’s important first of all to understand how the data is stored in a directory server and oracle has actually published what they call the ldap schema for oracle net services and within that are structural ldap classes for work on that now for a DBA or somebody who’s database focused that kind of sounds confusing it may not make a lot of sense but really all we

need to understand is that the LDAP directory server is effectively a very use specific and customized database so it’s just like the Oracle databases that we’re used to working with in deploying and similarly they’ve published a schema just like an application might publish a schema for accounting in an Oracle database or whether they’re whatever other purpose Oracle has provided this ldap class that’s specific to oracle net so really think of it all as tables and columns within an Oracle database it’s just it’s not an Oracle database it’s an LDAP directory server instead and these entries are all specific to Oracle net routing and resolution ok so what decision criteria do we need to consider when choosing which one of the alternatives I can’t say that I’m going to give a recommendation that says use this one or top of all of the others I think that’s something that needs to be evaluated on an organization by organization basis because every end organization might have different requirements I’ve tried however to list a number of good questions that you should be asking yourself if considering this approach ok and there’s important things like the first few points about supported platforms and whether I need software hardware and licenses and stuff like that but there’s also operational considerations that are really important that are kind of in the middle there like can I bulk load all of my existing entries if you have 1400 entries like I was mentioning you sure don’t want to have to have a DBA manually entering those into a new system we bulk loading is going to be mandatory under almost all circumstances is it easy and scriptable to add additions can we automate that process or similarly modifications and removals and there’s going to be exceptions where we’re going to need tienes name store files still generated for those very few applications that are not going to be compatible so do we have the technology to easily export our entries into tnsnames.ora format lastly you know there’s some other non-functional criteria that you might want to consider such as can we put advanced entries liked a for rack entries can we use aliases and what I availability and security mechanisms are available so that’s a really good list that should be able to help you evaluate what might be the best fit so having a keyboard issue here sorry about that all right seem to be back on track here OID Oracle internet directory well that’s our first and usually that’s the most obvious option so what are some of the benefits from that well first of all that’s an Oracle product we’re using this for Oracle routing and hence we’re suggesting an Oracle product of course that’s going to have full support from Oracle secondly the actual data is stored in an Oracle database and that’s something that oracle DBA is already know how to manage and backup so it’s something that’s a little less obscure and a little bit more understandable for dbas and of course part of that management is knowing how high availability options whether they want to replicate that with Oracle data guard or whatnot and it’s got built-in options that make it very easy to export to a teen as themes or a file using the GUI and finally it’s also very easy to handle multiple contexts now hopefully you don’t have multiple contexts in your environment I really don’t like to see environments that have for example their domain com as well as no suffix as well as the dot world and really have a mixture of all three of those because I think it makes things confusing and adds complexity but if you do have multiple contexts oid i find is very easy day to work with for handling those however some of the downside to 0 ID is if you want to use the full functionality of it on the full route Phil spectrum of interfaces it uses weblogic as a front-end and most Oracle core dbas like myself may not be as familiar with weblogic server as they are with the core database and that potentially adds some difficulty to your environment it means now that we have another layer of software that’s going to require patching that’s both the weblogic server and this new database instance that we potentially spun up just a whole

all of this TNS names or net service descriptor information and in my experience whenever somebody is implementing OID in the environment it’s usually a project and part of that project is provisioning of new hardware and new software dedicated for this purpose so all in all is it a good solution yes it functionally will work very well but it seems to me like there’s an awful lot of hardware and software that may be cumbersome to work with if it’s just for this purpose if you’re using oid for other purposes well then maybe you’re just adding on function i’ll leave you to it and that makes more sense but if you’re just using it for net service name lookup it may be a lot of overkill the second option is active directory again I don’t know of any organizations that don’t already have active directory somewhere in there or somewhere within that organization and there’s some really great benefits not only is it already there but now the DBA is no longer responsible for high availability for patching it for updating it similarly as we already know since active directory and d and other similar services such as dns are usually so critical to an infrastructure there’s already replication and high availability options it’s already a critical part of the network infrastructure so it’s actually quite surprising um how easy it is to set up and move all of your oracle net entries into active directory but there are some downsides the downsides however I find our more process and political than they are technological right technically it’s pretty easy to do and Oracle provides a really great step-by-step guide for doing so and I have a virtual demo with some screenshots that I will show at the end this presentation but the downside really is that in order to do this the Oracle DBA and will require administrative access to the domain controllers and my experience is whenever windows-based system administrators hear that oh the Oracle guys one administrative access to this critical part of their infrastructure that’s so sensitive it’s usually a political issue and they say no we don’t want to let these guys touch this thing okay so that’s really probably the number one downside the next issue is that extra ad permissions may be required to make queries right you may have to allow anonymous queries if coming from unix platforms where the client can’t automatically authenticate against active directory and things like that again these are things that are more process oriented where active directory or Windows domain system administrators are likely to say we don’t really want to give that to the Oracle guys or why are we extending our active directory for this unknown Oracle product which they typically know very little about consider it a black box and maybe it quite possibly doesn’t Oracle doesn’t have the best reputation within the organization for whatever reasons so a third option is openldap and I’ve deployed not by my choosing but but I would also still recommend this open ldap at a number of different sites now there’s a few real strategic advantages of open out that first of all it’s a free open-source software product that runs on a variety of operating systems I’ve worked at environments were deployed on linux and solaris and including hybrid environments where it’s partially on linux and parsing on solaris with replication between the two it’s been very successful it gives us a lot of functional options such as master slave replication we can have multiple slaves and that as I mentioned in my previous example can even cross the platform in Indian boundaries such was the case when I was going from linux to solaris and eventually reversing direction of which was the master which was the slate it really is the client did a replat forming from one hardware and the software infrastructure to another in one of the things that I need to migrate was openldap which was used for or on that service name resolution and of course it’s easy to update for example if it’s just a yum command within the next deployments now to install it on Linux is very simple it’s just an RPM that gets installed and what runs in the background is what’s called the

standalone LDAP directory server or slap thee as I sometimes say whether correctly or incorrectly now the nice thing about this is that it’s really quite lightweight and simple so if you have a highly available unix based server that’s already provisioned and running some other background services is quite easy to add openldap and the standalone LDAP directory server on top of that environment now you do need some root permissions to install because obviously we’re installing new software and it requires some basic linux skills which most dba’s already of course have now one of the biggest downsides of openldap is that it doesn’t come with a GUI and for a new technology that dba’s don’t really know the commands for and haven’t really worked with a lot that can be a bit of a challenge fortunately however I’ve had a lot of success with apache directory studio the biggest problem with apache directory studio is is quite a fairly large file to download as in a couple hundred megabytes and you think it’d be something lighter and simpler but really that’s about it you download a couple hundred megabytes of a free product here a patchy directory studio and you set it up and you just connect to your openldap sources and it gives you a graphical interface as you can see in the screenshot example so upon the right in the screenshot example you can start to see that we got some ldap kind of nomenclature as in CN or canonical name and here I’ve got a PeopleSoft example so called it p soft and you can see at the bottom right the oracle net options string in there is the familiar description equals address equals protocol equals the DVD a should be very familiar with already ok there’s other alternatives to there’s web-based interfaces and things like that so it does not come with a GUI included however there are a number of no cost alternatives that are available now it’s important to really understand that all three of these things are really just different versions of the same thing okay Active Directory sure sounds like it’s a different product than open LDAP or OID but really at the heart of them there ldap compliant applications Active Directory feels like a black box to almost everybody in an organization technologists included unless they are windows system administrators and it kind of usually comes as a surprise to most people to understand that really it’s still held app compatible and compliant hence we can use common ldap utilities and commands to complete some of the functionality that’s necessary if we’re going to implement something like this for example ldap add an LDAP search or two very common commands for adding new entries or for extracting existing entries the example here I’m showing in the screenshot is where i am using ldap search as a tool to search my directory store in this case it’s openldap and to build myself a TNS names the raw file so ldap search extracts all of the command I’m sorry all of the entries that are related to Oracle net and then I’m really just using some grep and said commands to reformat some syntax remove things like CN or canal canonical name equals and to format it into Oracle Tina’s named or a file compatible syntax but really that’s it you see this is a one-time effort where I can write a little script that’s only four lines long and I can extract from my directory store and create a TNS name’s Aurora file now as mentioned earlier OID already has a button that can do this for you however if you’re not using oid if you’re using Active Directory or using openldap then maybe you need to make a script and the exact syntax of the grep and sacraments are going to vary ever so slightly depending on the LDAP directory services you have that has been chosen now the best part about this is where are all these tools they’re already installed into every Oracle software installation that you have so whether it’s a database home or it’s a client home go and try the commands as I’ve shown here list what’s in the Orca home in ldap directory and you’ll see that all of these tools and utilities are already present again most dba’s don’t know that they already have the commands and utilities rather that they need now this is set up with the or this is included with the Oracle software for purpose of OID but again these are generic utilities that can be used against any directory store so there’s no need to install separate software or download or try to have your

system administrator add these tools to your unix or linux box if you if you’re an Oracle DBA you’ll already have oracle software installed there and hence those tools are there so what’s the downside to all this well with OID as was mentioned the entire software stack is supported right so if there are any issues you’re going to be able to open an Oracle SR and get oracle to help you investigate them it may be surprising again to the Oracle DBA so know that active directory is also fully supportive so again Oracle is going to help you and you perfectly within your rights to open an Oracle SR if you if you have a problem that you can’t overcome and you need to however if you choose one of the other vendors you’re not going to be able to necessarily open an Oracle SR if they think that the problem is due to the directory store namely openldap or one of those other ldap servers that were listed earlier however I challenge everybody to think is that really an issue well probably not is what I think I think if the most common issue that you’re going to counter is the common 3505 failed to resolve name error this is when the dbas are already familiar with and already have good terrible shooting techniques however I’ll go into some more detail on what things we can do if we do experience those that specific error other risks or concerns are are we going to have performance delays resolving the entry so that’s quite possible and searching an Oracle there’s a number of my Oracle support notes already published primarily for older versions of the software and in very specific use cases where there’s been a performance issue getting the results back from the ldap compatible directory server mostly OID so if you are considering this and you are doing a proof-of-concept definitely measuring performance making sure you’re getting quick speedy response times from your directory server is of paramount importance but it’s important to remember that this is an initial look up only right when you try to establish a connection you’re doing a quick data dip on to the directory server getting the information that you need and it’s sending it back to the client and the clients then creating a new socket for the listener it’s not used again anywhere else in the connection process so and first connection is persistent from that point onwards and isn’t using it on on a consistent basis it’s only used for making new connections it’s also important to remember that this isn’t used for the rack interconnect and similarly you don’t have to use for data guard or DB links it may be easier to take one link out of the chain for data guard and DB links and just use the easy connect format or even potentially a local tea and that’s names file so we mentioned performance but getting no response from the directory server and how long it takes to timeout is another thing that we may need to test and a proof of concept before deploying and as I touched upon previously some applications just may not support it so there may be the need for someone tnsnames.ora files and hence it may be important to have a mechanism whether scripted through the ldap search command or whether automatically through the oid graphical user interface regardless and maybe a need to be able to generate a Tina same store a file are their functional risks typically not in in most cases all of them are going to support more complex entries like the one I’ve shown here which is a TAF entry and I say it’s more complex for a number of different reasons first of all you can see I’m using aliases on the second line there’s a char there’s a chart world HR example com or domain com and then there’s the actual database name ORCL so there there’s one two three four different aliases for the same entry i also have a line in there from load balance equals off and failover equals on and obviously i have two hosts it may look look like the host names are quite similar there but really the first one is win dash 59 and the second one is win dash 60 so I’m listing two different hosts in the address list so that’s why I would say this is a more complex entry than the typical entry however it’s not overly complex and all three of the products that I mentioned so far really do support it now if you have some really old clients that are still running oracle 7 and oracle eight hopefully you don’t in your organization but if you do there’s going to be some extra steps required for them or maybe you need to do a one-off tnsnames.ora file or the best solution is get rid of those old oracle and oracle 80 clients

completely if you do have a problem the number one thing to do is to try doing an oracle net or previously called and still I guess generally called a sequel net trace okay and here I’m linking some my Oracle support documents and give instructions on how to do that and how to interpret the results another utility mentioned at the bottom there is the Oracle trace assistant again this is there on every Oracle installation yet most dbas don’t know about it and that’s a good utility which can parse and interpret an Oracle sequel net trace file similar to what k prof might do on a sequel trace file and finally the traceroute the oracle traceroute utility is another utility that most DBAs don’t know about that provides a little bit more information when trying to debug how entries are resolved and how connections are routed to the server and if none of those Oracle provided tools work for you well then you can revert back to operating system based racing at the very beginning there is an example using the estrace command in a linux environment showing which files were touched when looking for an entry in tnsnames.ora and other daughter laura files and that’s a good utility for seeing exactly what Oracle’s doing what files it’s touching what is looking for and so forth and if you’re not running Linux you’re running a Windows client and it’s a little bit more difficult but there are some up options such as the process monitor utility from sysinternals which is now owned by microsoft as well as NT trace so when you combine all of that there’s lots of utilities there’s very easy and quick workarounds and back outs should a problem be encountered there’s some necessary proof of concept testing that would be done and hence really there are risks but I believe that they’re all mitigate upon some other last things to watch out for well let’s not forget the names directory path that’s in our sequel mentor or a file if we don’t include ldap in there as a source well it’s not going to be searched it’s not going to be included the methods are specific and are searched for in a specific order also there’s those surprising locations where you might find it teenis names the raw file so if you directory path is saying we’re going to support ldap as well as t tnsnames.ora as well as easy direct it might find an entry first in one of those locations if you really want to you know mess up your DBA for a day and really give them a hard time put a bogus entry and that hidden dot tnsnames.ora file and see how long it takes them to work out why it’s not not resolving properly though we shouldn’t be mean so be doing that and finally remember Windows is a different operating system and it’s going to operate differently than linux or POSIX based operating systems including different search orders the current working directory versus home directory prioritization and even directory different directory search orders if you have the Oracle home environments variable set or not which is not necessarily mandatory in Windows environments all right so that all being said a couple quick virtual demos just to show how easy it is to get started with some of these products OID is obviously very much more complicated and cumbersome so the virtual demo starts here with openldap instead so if we want install openldap it’s really quite straightforward there’s a couple our pms that we need to install in this case here i’m using llaman an Oracle Linux environment simple enough download two packages and install them there’s a little bit of basic setup in the configuration file and they’re setting a new administrative password which for which they provide a utility which purpose which allows you to encrypt it into a hash which is then use later in some of the configuration text files so that you’re not putting plain passwords in plain text in in text files where they could obviously be discovered and used for malicious purposes we need to create a default configuration file and then this is the really interesting part we need to add Oracle as a schema to our LDAP directory store it’s not there by default so consider this like as if you have an Oracle database and you’re having a new application installed well your vet application vendor or maybe if it’s internally your data modeler czar going to provide you a sequel script that’s going to create all the necessary tables and indexes and so forth or that application well conceptually we need to do something similar in openldap so where do we get this schema deafen

so here what you can see is a couple of very simple grep and set commands where I’m grabbing those schema definitions right out of our Oracle home now they’re in or go home for the purpose for OID but again since the two products are forming are compatible they’re conforming to the same ldap compliant structure and scheme is going to be the same we can use the schema files that are in our Oracle home designed for oid and we can implement them against openldap so very easy to do a couple script and set commands there to build some schema definition files lastly I need to again open another parameter or configuration file and point to these new schemas and some basic setup like replacing my domain with your actual corporate domain and adding the administrator password using preferably the hash value that was created earlier for extra security though if you’re just doing testing and proof of concept you could use a plaintext password as well for simplicity now I just start the service and register it so that it starts automatically on machine ooh and I’m up and running so the last few steps are really let’s add some entries okay so the first thing we need to do is manually add the organizational unit to the root of the ldap tree again this might seem kind of complex for dbas but it’s it’s really not this is a one-time thing and here on the screen I’m showing exactly what commands and syntax you can actually have to do here you know manager is equivalent of the cysts user what’s this user would be an an Oracle database manager is in the open LDAP directory server obviously you can see welcome 15 is my actual password there and I’m just adding an organizational unit that’s going to be used to store all of my Oracle entries underneath it so lastly I add an Oracle context in there and I’m adding my first entry so you can see in there actually in the oracle net the square there’s the stuff that we’re familiar with right and we’re using the ldap command to do that now in the future the beauty is is that we can write a very simple shell script or even a apex web interface that you know asks us for things like the hostname the port in the service name and automates this and run zeal that bad commands behind the scenes makes it very easy to use with from a DBA operational perspective it allows you to add some automation to the process the very last thing we need to do is we need to adjust our sequel net or a file to tell it that we’re now using ldap and to create an LDAP door or a file to tell it where the LDAP directory server is so you can see the first line there my sequin editora file I’ve added ldap to the list of directory paths along with DNS name isn’t easy connect I haven’t removed those that second line has commented out ldap authenticated buying that usually is not required for openldap but might be for Internet door sorry active directory and then we need to create an LDAP tharoor a file and in erie CI bay i provide some basic information such as the the context of how it’s to search the ldap tree and most importantly where is my ldap server or servers so here you can see a host name on the Pythian domain and a port of 389 which is one of the common well-known ldap ports there’s actually two common well-known ldap ports one is for encrypted connections and one is for unencrypted on SSL connections and finally let’s test it so I’m doing it Tina’s pain here for I’ve p soft which is for my PeopleSoft database you can see in the output below it use the ldap adapter to resolve this alias and air either from that point onwards it should look familiar to the dbas attempting to connect or sorry attempting to contact and it’s giving the description string that we’re familiar with and finally a successful result with the ok when the 10 millisecond response time so that’s it we’re up and running and it’s really that simple and again i would add some automation to the addition of future entries and that’s very easy to do through a simple script or other mechanisms however if you want to really take it to the next level and really make this thing will bust and and reliable there’s a couple other options we can do like we an ad master and slave replication or aj we can secure the traffic with TLS and a certificate and go through a secure port when

communicating and we can manage it with a patchy directory studio and the automation commands that I’m mentioned already scripting editions using ldap ad and generating a Tina steam store a file using ldap search easy as that Active Directory is almost just as simple and Oracle already provides a great screenshot full document through this my Oracle support document IP of how to configure Active Directory for net naming I didn’t repeat any of the screenshots here because I figured anybody who’s interested can just reference that document and download the PDF from Oracle themselves it’s really great PDF it makes things makes it very easy to implement so we go through the steps that Oracle provides they’re fairly straightforward as mentioned earlier it will require you to log on to your domain controller with administrative access and that’s probably the single biggest challenge organizationally and when we’re done we’re just going to adjust our sequel naturae and ldap the raw files accordingly so just like with the UNIX admit em example or sorry the Linux example these look remarkably similar the main difference is that we have that ldap authenticate buying variable set to true in our sequel nada Dora and of course we mentioned ad as the directory server type actually I forgot to mention earlier if I just go back a few slides when setting up openldap you can see there the directory server type is actually 0 ID it’s not openldap or anything else or generic ldap it’s 0 ID so from an oracle net perspective it actually thinks it’s talking to OID the two different values we can put into that field are 0 ID or ad so obviously it’s can be 84 Active Directory oid for everything else anyway there we see directory server type is ad now we can use the oracle net manager GUI to add entries if we like as you can see here i’m making a similar PeopleSoft entry and we can use some tools like here I’m using Active Directory users and computers to explore my active directory tree normally a DBA would not do this window system administrator would do this but working with a windows system administrator and or domain admin possibly a DBA would do it the initial time just to make sure things are getting set up correctly but here you can see we have our Oracle context that was automatically created when we follow the steps in the Oracle provided document and we can see our P soft entry and some of the familiar fields underneath another tool that we can use another sysinternals tool that’s active directory explorer personally I find that this one gives more information makes it a little bit easier to browse after active directory that’s probably why they created this tool in the first place and hence it’s really the one I would recommend for browsing your Active Directory to make sure the entry is in there correctly and to your liking okay moving along last thing we want to do is test that we can extract the data again this might seem kind of surprising but we’re using ldap search common ldap utility nothing to do with active directory and not a Microsoft provided utility utility to extract my entry so similarly similar to what i did on linux of using ldap search and as you can see the stuff in yellow is coming out which is the things that we would expect to see in our tnsnames.ora file so from that point again it’s just a matter of stringing and output manipulation to actually automatically build a tnsnames.ora and the last thing we want to do is course of course is tested so again I could TNS ping we can see that it use the ldap adapter to resolve the alias and we can actually make a connection to make sure we connect it to the database let’s taking one step forward for further rather and ice test resolution from Linux ok so again this is running from the Linux host you can see that in the ldap Dora file I’ve specified the directory server which is my active directory domain controller and a specified directory server type equals ad and sure enough there I can do at ens ping and I can still resolve the alias alright so to wrap things up I think that it’s important for dbas to understand the concept that oid active directory and openldap are really just three different versions of the same thing and there’s even other versions out there as well as was mentioned

already okay people generally don’t think of oid and active directory as being similar at all but really internally the guts they fundamentally are and hence connect descriptors can really be stored in any of the above right so that’s a good concept and something that’s designed to stimulate your thought and think about well which is really the best place to store this data and which is the best repository for my organization where I can store this data in a structured manner or it’s going to be protected protected from errors protected from server failures for high availability and so forth and personally I believe that active directory and openldap are the easiest to set up due to the political issues of touching a direct a domain controller typically active directory is not used and hence openldap I think is a very viable and realistic solution even though it doesn’t have full support in the Oracle stack regardless of what we choose data can be bulk loaded fairly easy to do similarly going the other direction data can be bulk extracted into a tnsnames.ora file format and we can use simple scripts to automate both of those and lastly let’s not forget that all of the utilities and I mentioning for automation you already have when it comes to making a decision of whether we want to do something like this I think the cost is mostly up front it’s up front in developing a solution doing the proof-of-concept making sure performance and high availability meets your standards and documenting new operational procedures but once all of that upfront work is done it’s really a breeze from that point for adding additions additional entries generating kunis name store files if needed or even just for backup purposes should become trivially simple and very fast due to scripting and automation and finally the purpose of all of this is to lower the risk in our enterprise lower the risk of accidental corruption causing an outage accidental deletion of the file causing an outage propagation delays widespread spread error or even high availability has been as has been mentioned many times what happens if a server housing a common critical piece of information or infrastructure such as a Tina’s names the raw file is corrupted or lost or just overloaded and unavailable okay so the idea behind all of this was to get people thinking to understand a couple key concepts like there are alternatives that all three of the major platforms described in this presentation are really just different versions of the same thing and by deploying one of these solutions we actually reduce risk in our organization for our Oracle infrastructure and the applications that depend on that so that’s my presentation for today I’m always happy to take questions or comments via email my email address is show them there i’m also a regular longer on the Pythian blog site you can find lots of great technical articles there on a variety of different technical topics in database related you can also find a listing of my blog articles there and you can also follow Pythian on twitter and linkedin where we constantly have great updates on exciting presentations where we’re going to be and new blog articles and so forth alright so that’s the end of my presentation I hope everybody enjoyed and have a great rest of your day thank you for joining us today and watching patheon’s x x insights remember to connect the assignment using the information provided here at Penn at Pythian com and if you want to discover more about our expertise in Oracle visit the URL included here or email us at info at Pythian com thanks for watching