Home » RDBMS Server » Networking and Gateways » Set tnsnames.ora (Oracle 12c Release 2)
Set tnsnames.ora [message #673789] Mon, 10 December 2018 06:02 Go to next message
petar97
Messages: 120
Registered: October 2018
Senior Member
I create two databases MYNEWDB and RCATDB for BACKUP, I should set one insert for MINEWDB and one for RCATDB in tnsnames.ora

I don't know which host to use?

listener.ora I wrote manually.

After that, I should check whether the MYNEWDB is available to the RCATDB server 


[oracle@localhost ~]$ cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
#::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

127.0.0.1 vbgeneric vbgeneric.localdomain


tnsnames.ora look like this:

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.2/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORCL12C =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl12c)
    )
  )

LISTENER_ORCL12C =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))


ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

And listener.ora look like this:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl12c)
      (SID_NAME = orcl12c)
      (ORACLE_HOME = /u01/app/oracle/product/12.2/db_1)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
    )
  )

LISTENER_MYNEWDB =
        (DESCRIPTION =
                (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1523))
        )
    
SID_LIST_LISTENER =
  (SID_LIST =
   (SID_DESC =    
    (SID_NAME = MYNEWDB)
      (ORACLE_HOME = /u01/app/oracle/product/12.2/db_1)
   )
  )

LISTENER_RCATDB =
        (DESCRIPTION =
                (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1532))
        )

SID_LIST_LISTENER =
  (SID_LIST =
   (SID_DESC =
    (SID_NAME = RCATDB)
    (ORACLE_HOME = /u01/app/oracle/product/12.2/db_1)
   )
  )

#HOSTNAME by pluggable not working rstriction or configuration error.
DEFAULT_SERVICE_LISTENER = (orcl12c)

Sorry my english is not good
Thanks Advance.

[Updated on: Mon, 10 December 2018 06:13]

Report message to a moderator

Re: Set tnsnames.ora [message #673792 is a reply to message #673789] Mon, 10 December 2018 06:27 Go to previous messageGo to next message
John Watson
Messages: 8962
Registered: January 2010
Location: Global Village
Senior Member
Address 0.0.0.0 in a listener.ora file will mean that the listener will listen on all available addresses (including all the 127.x.x.x range) and in a tnsnames file it will (if I remember correctly) cause the clients to use the machine's hostname. So I think your SQL*Net configuration should work, if in tnsnames.ora you change ocl12c and orcl to rcatdb and mynewdb.

However, even though it should work, your listener.ora is a bit of a mess. For example, you have three SID_LIST_LISTENER sections. You should have one, with a list of both databases.

Report message to a moderator

Re: Set tnsnames.ora [message #673793 is a reply to message #673789] Mon, 10 December 2018 06:33 Go to previous messageGo to next message
petar97
Messages: 120
Registered: October 2018
Senior Member
Use Oracle pre-built ORCL12C I get from them

I set tnsnames.ora

MYNEWDB =
        (DESCRIPTION =
                (ADDRESS_LIST =
                (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.2)(PORT = 1523))
                )
                (CONNECT_DATA =
                        (SERVICE_NAME = MYNEWDB)
                )
        )
RCATDB =
        (DESCRIPTION =
                (ADDRESS_LIST =
                (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.3)(PORT = 1532))
                )
                (CONNECT_DATA =
                        (SERVICE_NAME = RCATDB)
                )
        )
And test with tnsping

[oracle@localhost ~]$ . ./.profile_MYNEWDB
[oracle@localhost ~]$ tnsping RCATDB

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 10-DEC-2018 07:31:58

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

Used parameter files:
/u01/app/oracle/product/12.2/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.3)(PORT = 1532))) (CONNECT_DATA = (SERVICE_NAME = RCATDB)))
OK (10 msec)
[oracle@localhost ~]$ . ./.profile_RCATDB
[oracle@localhost ~]$ tnsping MYNEWDB

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 10-DEC-2018 07:32:21

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

Used parameter files:
/u01/app/oracle/product/12.2/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.2)(PORT = 1523))) (CONNECT_DATA = (SERVICE_NAME = MYNEWDB)))
OK (20 msec)
Is this good?

[Updated on: Mon, 10 December 2018 06:34]

Report message to a moderator

Re: Set tnsnames.ora [message #673794 is a reply to message #673793] Mon, 10 December 2018 06:56 Go to previous messageGo to next message
John Watson
Messages: 8962
Registered: January 2010
Location: Global Village
Senior Member
Quote:
Is this good?
Well, does it work? You have changed the ports. I did not suggest that you do that. Up to you, I suppose, but why ask for advice and than not follow it?

Really, it is now a total mess. Better remove all your SQL*Net files, and start again.

Report message to a moderator

Re: Set tnsnames.ora [message #673795 is a reply to message #673794] Mon, 10 December 2018 07:14 Go to previous messageGo to next message
petar97
Messages: 120
Registered: October 2018
Senior Member
host I changed before I got the answer, I forgot to bring them back to 0.0.0.0.
Now they are all at 0.0.0.0

Port is same as in the first post

MYNEWDB =
        (DESCRIPTION =
           (ADDRESS_LIST =
              (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1523))
           )
           (CONNECT_DATA =
              (SERVICE_NAME = MYNEWDB)
           )
        )
RCATDB =
        (DESCRIPTION =
           (ADDRESS_LIST =
              (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1532))
           )
           (CONNECT_DATA =
              (SERVICE_NAME = RCATDB)
         )
       )

when I test with the tnsping it all works well
How should it look tnsnames.ora, not to be in mess

Report message to a moderator

Re: Set tnsnames.ora [message #673797 is a reply to message #673795] Mon, 10 December 2018 07:36 Go to previous messageGo to next message
John Watson
Messages: 8962
Registered: January 2010
Location: Global Village
Senior Member
Remove all the files, and start again.

Report message to a moderator

Re: Set tnsnames.ora [message #673807 is a reply to message #673797] Mon, 10 December 2018 09:25 Go to previous messageGo to next message
petar97
Messages: 120
Registered: October 2018
Senior Member
did you think of something like this?


CONNECTION =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521)
      (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521)
    )
    (CONNECT_DATA =
      (SERVICE_NAME = MYNEWDB)
      (SERVICE_NAME = RCATDB)
      (SERVER = DEDICATED)
    )
  )

Report message to a moderator

Re: Set tnsnames.ora [message #673808 is a reply to message #673807] Mon, 10 December 2018 10:13 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
use of 0.0.0.0 means that no remote client can access this database

https://superuser.com/questions/949428/whats-the-difference-between-127-0-0-1-and-0-0-0-0

Why do you not use an actual routable IP#; like 8.8.8.8?

Report message to a moderator

Re: Set tnsnames.ora [message #673809 is a reply to message #673807] Mon, 10 December 2018 12:27 Go to previous messageGo to next message
John Watson
Messages: 8962
Registered: January 2010
Location: Global Village
Senior Member
Holy smoke. That is not remotely syntactically correct.
What document are you following to do this?

Report message to a moderator

Re: Set tnsnames.ora [message #674039 is a reply to message #673797] Fri, 28 December 2018 15:01 Go to previous messageGo to next message
petar97
Messages: 120
Registered: October 2018
Senior Member
I apologize for the delay with the reply.

I have set the listener.ora in this way:

Ignore the database name, because it is not the same as the beginning of the post

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl12c)
      (SID_NAME = orcl12c)
      (ORACLE_HOME = /u01/app/oracle/product/12.2/db_1)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = TESTDB)
      (SID_NAME = TESTDB)
      (ORACLE_HOME = /u01/app/oracle/product/12.2/db_1)
    ) 
  )
      
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION = 
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    )
    (DESCRIPTION = 
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
  )

When I call LSNRCTL reload and then
 LSNRCTL status, I get this:

[oracle@localhost admin]$ lsnrctl status

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 28-DEC-2018 15:48:06

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                28-DEC-2018 15:44:58
Uptime                    0 days 0 hr. 3 min. 7 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           orcl12c
Listener Parameter File   /u01/app/oracle/product/12.2/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=8081))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "51c99766d7e2568de0530100007f4fae" has 1 instance(s).
  Instance "orcl12c", status READY, has 1 handler(s) for this service...
Service "TESTDB" has 2 instance(s).
  Instance "TESTDB", status UNKNOWN, has 1 handler(s) for this service...
  Instance "TESTDB", status READY, has 1 handler(s) for this service...
Service "TESTDBXDB" has 1 instance(s).
  Instance "TESTDB", status READY, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
  Instance "orcl12c", status READY, has 1 handler(s) for this service...
Service "orcl12c" has 2 instance(s).
  Instance "orcl12c", status UNKNOWN, has 1 handler(s) for this service...
  Instance "orcl12c", status READY, has 1 handler(s) for this service...
Service "orcl12cXDB" has 1 instance(s).
  Instance "orcl12c", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@localhost admin]$ vi listener.ora
[oracle@localhost admin]$ lsnrctl reload

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 28-DEC-2018 15:49:12

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521)))
The command completed successfully
[oracle@localhost admin]$ vi listener.ora
^[[A[oracle@localhost admin]$ lsnrctl status

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 28-DEC-2018 15:49:21

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                28-DEC-2018 15:44:58
Uptime                    0 days 0 hr. 4 min. 22 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           orcl12c
Listener Parameter File   /u01/app/oracle/product/12.2/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=8081))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "51c99766d7e2568de0530100007f4fae" has 1 instance(s).
  Instance "orcl12c", status READY, has 1 handler(s) for this service...
Service "TESTDB" has 2 instance(s).
  Instance "TESTDB", status UNKNOWN, has 1 handler(s) for this service...
  Instance "TESTDB", status READY, has 1 handler(s) for this service...
Service "TESTDBXDB" has 1 instance(s).
  Instance "TESTDB", status READY, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
  Instance "orcl12c", status READY, has 1 handler(s) for this service...
Service "orcl12c" has 2 instance(s).
  Instance "orcl12c", status UNKNOWN, has 1 handler(s) for this service...
  Instance "orcl12c", status READY, has 1 handler(s) for this service...
Service "orcl12cXDB" has 1 instance(s).
  Instance "orcl12c", status READY, has 1 handler(s) for this service...
The command completed successfully

Why are some places standing UNKNOWN?

[Updated on: Fri, 28 December 2018 15:02]

Report message to a moderator

Re: Set tnsnames.ora [message #674040 is a reply to message #674039] Fri, 28 December 2018 15:26 Go to previous message
Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Status UNKNOWN are for instance that are statically registered in the listener.ora with lines like "(SID_NAME = orcl12c)".
You don't need these lines, just let the instances register themselves to the listener (as they apparently do as the READY status show).

Report message to a moderator

Previous Topic: Remote Connection Problem
Next Topic: create link access database with oracle.
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Jan 02 08:49:27 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.