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

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

****************I am authoring a Book on Oracle Database Cloud Services.......More details to follow.***************

Title : Oracle Database Cloud Revealed
Publisher : Apress
Release Date : Jan-2019

**********************************************************************************


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]


My Cloud Certifications:

AWS Certified Solutions Architect Associate

Oracle Cloud Infrastructure 2018 Certified Architect Associate.

Oracle Cloud Infrastructure Classic 2018 Certified Architect Associate.

Oracle Database Cloud Administrator Certified Professional.

Oracle Database Cloud Service Operations Certified Associate.

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: