Differences

This shows you the differences between two versions of the page.

--- tns [2018/12/08 12:49] – created 0.0.0.0
+++ tns [2022/03/30 10:38] (current) – external edit 127.0.0.1
@@ Line 1: / Line 1: @@
-====== TNS ======
+<code>
+tnsnames.ora is used by the client to communicate with the listener
+listener.ora is used by the listener to communicate with the database
+</code>
+  *  [[http://martincarstenbach.wordpress.com/2012/06/20/little-things-worth-knowing-static-and-dynamic-listener-registration/|static and dynamic listener registration - artincarstenbach.wordpress.com]]
+  *  [[http://edstevensdba.wordpress.com/2011/07/30/exploring-the-local_listener-parameter/|Exploring the LOCAL_LISTENER parameter - edstevensdba.wordpress.com]]
+  *  [[http://edstevensdba.wordpress.com/2011/02/09/sqlnet_overview/|diagnosing connection issues - edstevensdba.wordpress.com]]
+  *  [[http://edstevensdba.wordpress.com/2011/03/19/ora-12514/|tracking down solutions to tns error messages - edstevensdba.wordpress.com]]
+  *  [[https://laurentschneider.com/wordpress/2016/06/what-is-sid-in-oracle.html]]
+==== Some basic reminders ====
+Original at [[https://edstevensdba.wordpress.com/2018/09/17/a-few-points-about-dynamic-registration/|edstevensdba.wordpress.com]]\\
-  * [[http://martincarstenbach.wordpress.com/2012/06/20/little-things-worth-knowing-static-and-dynamic-listener-registration/|static and dynamic listener registration - artincarstenbach.wordpress.com]]
+  *  The listener itself doesn’t give a flying fig about what is in tnsnames.ora. That file (tnsnames.ora) is used ONLY by client processes. In the case of dynamic registration, the database IS the client process.
-  * [[http://edstevensdba.wordpress.com/2011/07/30/exploring-the-local_listener-parameter/|Exploring the LOCAL_LISTENER parameter - edstevensdba.wordpress.com]]
+  *  The listener is quite capable of starting with no listener.ora file at all. In this case it will start with all default values, including the default name of LISTENER and default port of 1521. For most people, most of the time, this is sufficient.
-  * [[http://edstevensdba.wordpress.com/2011/02/09/sqlnet_overview/|diagnosing connection issues - edstevensdba.wordpress.com]]
+  *  If the local_listener parameter is not set (null) the database will send the registration request to port 1521.
-  * [[http://edstevensdba.wordpress.com/2011/03/19/ora-12514/|tracking down solutions to tns error messages - edstevensdba.wordpress.com]]
+  *  If you choose to set local_listener, you can either use a full connect string, eg.  <nowiki>((ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1421)))</nowiki> or simply reference an entry in tnsnames.ora, to resolve to the address (server name and port) of the listener.
-=====Some basic reminders=====
+  *  The SID_LIST section of listener.ora has nothing to do with dynamic registration. Quite the opposite. The SID_LIST section is used to implement static registration.
-Original at [[https://edstevensdba.wordpress.com/2018/09/17/a-few-points-about-dynamic-registration/|edstevensdba.wordpress.com]]<br />
-<br />
-  * The listener itself doesn’t give a flying fig about what is in tnsnames.ora. That file (tnsnames.ora) is used ONLY by client processes. In the case of dynamic registration, the database IS the client process.
-  * The listener is quite capable of starting with no listener.ora file at all. In this case it will start with all default values, including the default name of LISTENER and default port of 1521. For most people, most of the time, this is sufficient.
-  * If the local_listener parameter is not set (null) the database will send the registration request to port 1521. Notice that 1521 is also the default port of of the listener.
-  * If you choose to set local_listener, you can either use a full connect string ((ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1421))) or simply reference an entry in tnsnames.ora, to resolve to the address (server name and port) of the listener.
-  * The SID_LIST section of listener.ora has nothing to do with dynamic registration. Quite the opposite. The SID_LIST section is how you implement static registration.
-=====Connections fail 10% of the time. How to fix?=====
+==== Count the number of entries in the tnsnames.ora file ====
-Turn on tracing<br />
+<code>
+grep -E -v '^ ' tnsnames.ora|grep -v '^$'|grep -v LISTENER|grep -v '^#'|sed -e 's/=.*$//'|sort -u|wc
+</code>
+==== How to parse the listener.log file ====
+  *  [[https://blog.toadworld.com/2018/06/14/snorkelling-in-the-oracle-listener-logs|Snorkelling in the Oracle Listener Logs - Norman Dunbar]]
+  *  Other Useful stuff by Norman Dunbar (DBA at Toad)
+  [[http://qdosmsq.dunbar-it.co.uk/blog/|Norman's general IT blog - useful Oracle stuff also]]
+==== Connections fail 10% of the time. How to fix? ====
+Turn on tracing\\
 There are some settings that can help...
-====sqlnet.ora====
+=== sqlnet.ora ===
 Setting a value greater than 0 ensures that connections are not left open indefinitely, due to, for example, an abnormal client termination.
-<code>0@@</code>
+<code>
-====tnsnames.ora====
+NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
+ADR_BASE = /oracle/product
+DIAG_ADR_ENABLED=OFF
+sqlnet.expire_time=10
+DEFAULT_SDU_SIZE=11280
+</code>
+=== Clean up the tnsnames.ora ===
+The contents of this file can get pretty nasty and outdated. Keeping it clean and up-to-date is a very good idea but what should be in there?
+  - a reference to the service names used by any applications connecting to the databases (generally the database names)
+  - a reference to any useful external services (database link hosts, synonym links, ...)
+% of the file can be cleaned up with this. It addresses the first point above by spitting out only the stanzas in use by instances on this host.
+<code perl>
+#!/usr/bin/env perl
+# ==============================================================================
+# Name         : clean_tnsnames.ora.pl
+# Description  : extracts the relevant stanzas from tnsnames.ora
+#
+# Parameters   : 1 - hostname
+#
+# Notes        : Must be run from the $TNS_ADMIN directory
+#                hostname should be supplied in case clustername is different
+#
+# Example      : ./tns.pl hn481
+#
+# Modification History
+# ====================
+# When      Who               What
+# ========= ================= ==================================================
+# 24-MAY-20 Stuart Barkley    Created
+# ==============================================================================
+use strict;
+use warnings;
+die "ERROR: Usage: tns.pl <host>\n\n" unless @ARGV == 1;
+my ($host) = @ARGV;
+# --------------------------------------------------
+# build an array of instances running on this server
+# --------------------------------------------------
+my @running_sids = qx[ps -eo args|grep pmon|sed -e 's/ora_pmon_//'|grep -vE "grep|sed"];
+chomp @running_sids;
+#print "running_sid array:\n", join "\n",@running_sids, "\n";
+# ----------------------------------------------------------
+# build an array of tnsnames stanzas that match current host
+# ----------------------------------------------------------
+local $/ = '';
+my @stanzas;
+open (DATA, "<tnsnames.ora") or die "Could not open tnsnames.ora file, $!";
+while (<DATA>) {
+    chomp;
+    if (m/$host/i) {push @stanzas, $_};
+}
+close (DATA);
+#print "stanza array:\n", join "\n\n", @stanzas, "\n";
+$/ = "\n";
+# ---------------------------------------------------------
+# print only the tns stanzas matching the running instances
+# ---------------------------------------------------------
+my (@matches, @sorted, @unique);
+foreach my $running_sid (@running_sids) {
+    push @matches, grep { /$running_sid\b/i } @stanzas;
+    @sorted = sort @matches;
+    @unique = do { my %seen; grep { !$seen{$_}++ } @sorted };
+}
+print join ("\n\n", @unique),"\n\n";
+# next bit needs more thinking about
+# ----------------------------------------------------
+#print "add these stanzas needed for database links\n";
+# ----------------------------------------------------
+#my @db_link_hosts = qx[printf "%s\n%s" "set head off" "select distinct upper(host) from dba_db_links;"|sqlplus -s / as sysdba|grep -v 'selected'|grep -v '^$'];
+#chomp @db_link_hosts;
+#foreach my $db_link_host (@db_link_hosts) {
+#    open CMD,'-|','tnsping '||$db_link_host or die $@;
+#    while (defined(my $line=<CMD>)) {
+#        print "$line\n");
+#    }
+#    close CMD;
+#    print ("tnsping ",$db_link_host,"\n");
+#}
+</code>
+==== Check to see how many connections there are in the tnsnames.ora file ====
+<code perl>
+perl -ne 'print "$1\n" if m/^(\w+)?[=| ]/' tnsnames.ora|grep -vE "^$"|sort|wc -l
+</code>
+=== Layout of a typical tnsnames.ora stanza ===
 The clause for LISTENER_FEUK11UD is a reference for the local_listener parameter.
-<code>1@@</code>
+<code>
+LISTENER_FEUK11UD =
+  (ADDRESS = (PROTOCOL = TCP)(HOST = hn481)(PORT = 1521))
+FEUK11UD  =
+  (DESCRIPTION =
+    (ADDRESS = (PROTOCOL = TCP)(HOST = hn481)(PORT = 1521))
+    (CONNECT_DATA =
+      (SERVER = DEDICATED)
+      (SERVICE_NAME = FEUK11UD)
+    )
+  )
+</code>
 Setting the local_listener in the database to the connection string instead of the connect identifier can help the debugging process...
-<code>2@@</code>
+<code>
+alter system set local_listener="(ADDRESS = (PROTOCOL = TCP)(HOST = hn481)(PORT = 1521))" scope=both;
+</code>
-====listener.ora====
+=== listener.ora ===
 Setting STARTUP_WAIT_TIME_LISTENER
 Setting CONNECT_TIMEOUT_LISTENER
-<code>3@@</code>
+<code>
+STARTUP_WAIT_TIME_LISTENER_FEUK11UD = 0
+CONNECT_TIMEOUT_LISTENER_FEUK11UD = 30
+LISTENER_FEUK11UD =
+  (DESCRIPTION_LIST =
+    (DESCRIPTION =
+      (ADDRESS = (PROTOCOL = TCP)(HOST = HFE2PrePrdOra)(PORT = 1521))
+      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
+    )
+  )
+ADR_BASE_LISTENER_FEUK11UD = /oracle/product
+</code>
+==== Static registration ====
+Check what the global_name parameter is in the database
+<code>
+SQL*Plus: Release 18.0.0.0.0 - Production on Thu Mar 21 14:00:48 2019
+Version 18.4.0.0.0
+Copyright (c) 1982, 2018, Oracle.  All rights reserved.
+Connected to:
+Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
+Version 18.4.0.0.0
+SQL> select * from global_name;
+GLOBAL_NAME
+-------------------------
+UPG.WORLD
+</code>
+listener.ora specifying global_dbname without the domain
+<code>
+LSNR481_184 =
+  (DESCRIPTION_LIST =
+    (DESCRIPTION =
+      (ADDRESS = (PROTOCOL = TCP)(HOST = hn481)(PORT = 3531))
+    )
+  )
+SID_LIST_LSNR481_184 =
+(SID_LIST =
+  (SID_DESC =
+     (GLOBAL_DBNAME = upg)
+     (SID_NAME = upg)
+     (ORACLE_HOME = /cln/tst/ora_bin1/app/oracle/product/18.4/dbhome_1)
+   )
+)
+</code>
+What services are recognised?
+<code>
+$ORACLE_HOME/bin/lsnrctl services lsnr481_184
+LSNRCTL for IBM/AIX RISC System/6000: Version 18.0.0.0.0 - Production on 21-MAR-2019 21:28:24
+Copyright (c) 1991, 2018, Oracle.  All rights reserved.
+Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hn481.cln.be)(PORT=3531)))
+Services Summary...
+Service "upg" has 1 instance(s).
+  Instance "upg", status UNKNOWN, has 1 handler(s) for this service...
+    Handler(s):
+      "DEDICATED" established:5 refused:0
+         LOCAL SERVER
+The command completed successfully
+</code>
+listener.ora specifying global_dbname with domain
+<code>
+LSNR481_184 =
+  (DESCRIPTION_LIST =
+    (DESCRIPTION =
+      (ADDRESS = (PROTOCOL = TCP)(HOST = hn481.cln.be)(PORT = 3531))
+      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC3531))
+    )
+  )
+SID_LIST_LSNR481_184 =
+(SID_LIST =
+  (SID_DESC =
+     (GLOBAL_DBNAME = upg.world)
+     (SID_NAME = upg)
+     (ORACLE_HOME = /cln/tst/ora_bin1/app/oracle/product/18.4/dbhome_1)
+   )
+)
+</code>
+What services are recognised now?
+<code>
+$ORACLE_HOME/bin/lsnrctl services lsnr481_184
+LSNRCTL for IBM/AIX RISC System/6000: Version 18.0.0.0.0 - Production on 21-MAR-2019 21:28:24
+Copyright (c) 1991, 2018, Oracle.  All rights reserved.
+Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hn481.cln.be)(PORT=3531)))
+Services Summary...
+Service "upg.world" has 1 instance(s).
+  Instance "upg", status UNKNOWN, has 1 handler(s) for this service...
+    Handler(s):
+      "DEDICATED" established:5 refused:0
+         LOCAL SERVER
+The command completed successfully
+</code>