Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]

Search This Blog

Sunday, September 13, 2015

Create and manage multiple network configuration files


Below we will address:

  1. How to setup listener.ora
  2. How to setup tnsnames.ora
  3. How to setup sqlnet.ora
  4. How to change the listener port/Listener oracle home
  5. How to setup multiple listeners in listener.ora
  6. tcp_invited_nodes feature
  7. What is a connection manager and How to set it up.

  1. set up the .ora files:

[oracle@collabn1 admin]$ env | grep TNS_ADMIN;cd $TNS_ADMIN
TNS_ADMIN=/u01/app/oracle/product/11.2.0.2/SHAIKPROD/network/admin

copy the .ora files from $TNS_ADMIN/samples to the $TNS_ADMIN/

[oracle@collabn1 admin]$ cp samples/*.ora .

[oracle@collabn1 admin]$ ls -lrt
total 24
-rw-r--r-- 1 oracle oinstall  187 May  7  2007 shrept.lst
drwxr-xr-x 2 oracle oinstall 4096 Aug  1 17:51 samples
-rw-r--r-- 1 oracle oinstall  106 Sep 13 16:41 sqlnet.ora
-rw-r--r-- 1 oracle oinstall 1051 Sep 13 12:19 listener.ora
-rw-r----- 1 oracle oinstall 1970 Sep 13 12:20 tnsnames.ora

Modify the .ora file to your environment or as shown in the below example under  4. (b):

4) (a) How to change the listener port/Listener home:

Current Listener port is :1521
Current Listener Home is : Grid Home → /u01/app/oracle/product/12.1.0/grid/network/admin/listener.ora

[oracle@collabn1 lib]$ tnsping shaikdb

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 12-SEP-2015 22:40:35

Copyright (c) 1997, 2009, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/11.2.0.2/SHAIKPROD/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = collabn1.shaiksameer)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = SHAIKDB.shaikmeer)))
OK (10 msec)


[oracle@collabn1 lib]$ lsnrctl status shaikdb

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 12-SEP-2015 22:40:49

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=collabn1.shaiksameer)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=SHAIKDB.shaikmeer)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                03-SEP-2015 17:19:09
Uptime                    9 days 5 hr. 21 min. 39 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/ora_base/diag/tnslsnr/collabn1/listener/alert/log.xml
Listening Endpoints Summary...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.78.61)(PORT=1521)))
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.78.51)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
 Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "SHAIKDB.shaikmeer" has 1 instance(s).
 Instance "SHAIKDB", status READY, has 1 handler(s) for this service...
Service "SHAIKDBXDB.shaikmeer" has 1 instance(s).
 Instance "SHAIKDB", status READY, has 1 handler(s) for this service...
The command completed successfully


[oracle@collabn1 lib]$cat tnsnames.ora

SHAIKDB =
 (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = collabn1.shaiksameer)(PORT = 1521))
    (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = SHAIKDB.shaikmeer)
    )
 )

SHAIKDB>show parameter service

NAME                    TYPE     VALUE
------------------------------------ ----------- ------------------------------
service_names                string     SHAIKDB.shaikmeer
SHAIKDB>show parameter listener

NAME                    TYPE     VALUE
------------------------------------ ----------- ------------------------------
listener_networks            string
local_listener                string      (ADDRESS=(PROTOCOL=TCP)(HOST=
                       192.168.78.61)(PORT=1521))
remote_listener             string      collabn-cluster-scan.shaiksam
                       eer:1521
SHAIKDB>


Lets remove the SHAIKDB entry from the default listener


shaiks@MAC$ssh -X oracle@192.168.78.51

oracle@192.168.78.51's password:
Warning: untrusted X11 forwarding setup failed: xauth key data not generated
Warning: No xauth data; using fake authentication data for X11 forwarding.
Last login: Sat Sep 12 20:06:27 2015 from 192.168.78.2


[oracle@collabn1 ~]$ . ./SHAIKDB.env

[oracle@collabn1 ~]$ netca































Oracle Net Services Configuration:
 The net service name SHAIKDB was deleted.
Oracle Net Services configuration successful. The exit code is 0


[oracle@collabn1 ~]$ tnsping shaikdb

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 13-SEP-2015 12:06:05

Copyright (c) 1997, 2009, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/11.2.0.2/SHAIKPROD/network/admin/sqlnet.ora

TNS-03505: Failed to resolve name
[oracle@collabn1 ~]$ lsnrctl status shaikdb

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 13-SEP-2015 12:06:14

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

TNS-01101: Could not find service name shaikdb

SHAIKDB>show parameter service

NAME                    TYPE     VALUE
------------------------------------ ----------- ------------------------------
service_names                string     SHAIKDB.shaikmeer

SHAIKDB>show parameter listener

NAME                    TYPE     VALUE
------------------------------------ ----------- ------------------------------
listener_networks            string
local_listener                string      (ADDRESS=(PROTOCOL=TCP)(HOST=
                       192.168.78.61)(PORT=1521))
remote_listener             string      collabn-cluster-scan.shaiksam
                       eer:1521


4. (b)    Now lets add the SHAIKDB  listener manually:

[oracle@collabn1 ~]$ cd $TNS_ADMIN
[oracle@collabn1 admin]$ pwd
/u01/app/oracle/product/11.2.0.2/SHAIKPROD/network/admin


[oracle@collabn1 admin]$ vi listener.ora

SHAIKDB =
 (DESCRIPTION_LIST =
    (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = collabn1.shaiksameer)(PORT = 1522))
    )
 )

SID_LIST_SHAIKDB=
  (SID_LIST=
       (SID_DESC=
         (GLOBAL_DBNAME=SHAIKDB.shaiksameer)
         (SID_NAME=SHAIKDB)
         (ORACLE_HOME=/u01/app/oracle/product/11.2.0.2/SHAIKPROD)
        )
     )

[oracle@collabn1 admin]$ lsnrctl start shaikdb

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 13-SEP-2015 12:19:29

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/11.2.0.2/SHAIKPROD/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0.2/SHAIKPROD/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/collabn1/shaikdb/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=collabn1.shaiksameer)(PORT=1522)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=collabn1.shaiksameer)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     shaikdb
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                13-SEP-2015 12:19:29
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0.2/SHAIKPROD/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/collabn1/shaikdb/alert/log.xml
Listening Endpoints Summary...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=collabn1.shaiksameer)(PORT=1522)))
Services Summary...
Service "SHAIKDB.shaiksameer" has 1 instance(s).
 Instance "SHAIKDB", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully


[oracle@collabn1 admin]$ vi tnsnames.ora

SHAIKDB =
 (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = collabn1.shaiksameer)(PORT = 1522))
    (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = SHAIKDB.shaiksameer)
    )
 )

[oracle@collabn1 admin]$ tnsping shaikdb

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 13-SEP-2015 12:20:31

Copyright (c) 1997, 2009, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/11.2.0.2/SHAIKPROD/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = collabn1.shaiksameer)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = SHAIKDB.shaiksameer)))
OK (0 msec)

[oracle@collabn1 admin]$ sqlplus system@shaikdb

SQL*Plus: Release 11.2.0.1.0 Production on Sun Sep 13 12:23:29 2015

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SHAIKDB>select name from v$database;

NAME
---------
SHAIKDB


6 . How to setup multiple listeners in listener.ora


[oracle@collabn1 admin]$ env | grep TNS_ADMIN;cd $TNS_ADMIN
TNS_ADMIN=/u01/app/oracle/product/11.2.0.2/SHAIKPROD/network/admin

[oracle@collabn1 admin]$ ls -lrt
total 24
-rw-r--r-- 1 oracle oinstall  187 May  7  2007 shrept.lst
drwxr-xr-x 2 oracle oinstall 4096 Aug  1 17:51 samples
-rw-r--r-- 1 oracle oinstall  106 Aug 23 16:41 sqlnet.ora
-rw-r--r-- 1 oracle oinstall 1051 Sep 13 12:19 listener.ora
-rw-r----- 1 oracle oinstall 1970 Sep 13 12:20 tnsnames.ora

Below we will create a default listener named “LISTENER” with port 1621 and register databases “MYDB” & “ORCL”

[oracle@collabn1 admin]$ vi listener.ora

LISTENER =
 (DESCRIPTION_LIST =
    (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = collabn1.shaiksameer)(PORT = 1621))
    )
 )

SHAIKDB =
 (DESCRIPTION_LIST =
    (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = collabn1.shaiksameer)(PORT = 1522))
    )
 )

SID_LIST_LISTENER=
  (SID_LIST=
       (SID_DESC=
         (GLOBAL_DBNAME=orcl_dgmgrl.shaiksameer)
         (SID_NAME=orcl)
         (ORACLE_HOME=/u01/app/oracle/product/11.2.0.2/SHAIKPROD)
        )
       (SID_DESC=
         (GLOBAL_DBNAME=MYDB)
         (SID_NAME=MYDB)
         (ORACLE_HOME=/u01/app/oracle/product/11.2.0.2/SHAIKPROD)
        )
  )

SID_LIST_SHAIKDB=
  (SID_LIST=
       (SID_DESC=
         (GLOBAL_DBNAME=SHAIKDB.shaiksameer)
         (SID_NAME=SHAIKDB)
         (ORACLE_HOME=/u01/app/oracle/product/11.2.0.2/SHAIKPROD)
        )
    )
ADR_BASE_LISTENER = /u01/app/oracle

[oracle@collabn1 admin]$ lsnrctl start LISTENER

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 13-SEP-2015 14:17:31

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/11.2.0.2/SHAIKPROD/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0.2/SHAIKPROD/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/collabn1/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=collabn1.shaiksameer)(PORT=1621)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=collabn1.shaiksameer)(PORT=1621)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                13-SEP-2015 14:17:31
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0.2/SHAIKPROD/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/collabn1/listener/alert/log.xml
Listening Endpoints Summary...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=collabn1.shaiksameer)(PORT=1621)))
Services Summary...
Service "MYDB" has 1 instance(s).
 Instance "MYDB", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl_dgmgrl.shaiksameer" has 1 instance(s).
 Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully


[oracle@collabn1 admin]$ vi tnsnames.ora

MYDB =
 (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = collabn1.shaiksameer)(PORT = 1621))
    (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = MYDB)
    )
 )


[oracle@collabn1 admin]$ tnsping mydb

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 13-SEP-2015 14:20:14

Copyright (c) 1997, 2009, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/11.2.0.2/SHAIKPROD/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = collabn1.shaiksameer)(PORT = 1621)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = MYDB)))
OK (0 msec)

[oracle@collabn1 admin]$ tnsping shaikdb

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 13-SEP-2015 14:20:32

Copyright (c) 1997, 2009, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/11.2.0.2/SHAIKPROD/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = collabn1.shaiksameer)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = SHAIKDB.shaiksameer)))
OK (10 msec)

TCP.INVITED_NODES


To specify which clients are allowed access to the database.

[oracle@collabn1 admin]$ vi sqlnet.ora
ENCRYPTION_WALLET_LOCATION= (SOURCE=(METHOD=FILE)(METHOD_DATA= (DIRECTORY=/u01/app/oracle/admin/wallet)))
TCP.INVITED_NODES=(collabn1.shaiksameer,collabn2.shaiksameer)

[oracle@collabn1 admin]$ ping collabn1.shaiksameer
PING collabn1.shaiksameer (192.168.78.51) 56(84) bytes of data.
64 bytes from collabn1.shaiksameer (192.168.78.51): icmp_seq=1 ttl=64 time=0.010 ms
64 bytes from collabn1.shaiksameer (192.168.78.51): icmp_seq=2 ttl=64 time=0.036 ms
^C
--- collabn1.shaiksameer ping statistics ---
2 packets transmitted, 2 received, 0% packet loss, time 1077ms
rtt min/avg/max/mdev = 0.010/0.023/0.036/0.013 ms


[oracle@collabn1 admin]$ ping collabn2.shaiksameer
PING collabn2.shaiksameer (192.168.78.52) 56(84) bytes of data.
64 bytes from collabn1.shaiksameer (192.168.78.52): icmp_seq=1 ttl=64 time=0.010 ms
64 bytes from collabn1.shaiksameer (192.168.78.52): icmp_seq=2 ttl=64 time=0.036 ms




more info:

Oracle® Database Net Services Reference
11g Release 2 (11.2)
Part Number E10835-01

No comments: