Role of tnsnames. ora in oracle

1. Role of tnsnames. ora in listener

The database only reads the content in tnsnames. ora during the startup process to parse LOCAL_LISTENER. After that, tnsnames changes have nothing to do with listening.

--- Set LOCAL_LISTENER, port 1522

SQL> show parameter list

NAME TYPE VALUE

-----------------------------------------------------------------------------

Listener_networks string

Local_listener string LISTENER_1

Remote_listener string

--- Contents in listener. ora

[Oracle @ node1 admin] $ more listener. ora

# Listener. ora Network Configuration File:/oracle/app/oracle/db/network/admin/listener. ora

# Generated by Oracle configuration tools.

LISTENER_1 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP) (HOST = 1.1.1.10) (PORT = 1522 ))

)

ADR_BASE_LISTENER_1 =/oracle/app/oracle

--- Content in tnsnames. ora

LISTENER_1 =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP) (HOST = 1.1.1.10) (PORT = 1522 ))

)

)

The current database status is normal.

Delete tnsnames. Ora, start the database

SQL> ORA-00119: invalid specification for system parameter LOCAL_LISTENER

ORA-00132: syntax error or unresolved network name 'listener _ 1

The database reports an error, indicating that the database uses tnsnames. ora to parse LOCAL_LISTENER at startup.

Step 2: Modify tnsnames. The ora content gives him an incorrect IP address.

[Oracle @ node1 admin] $ vi tnsnames. ora

LISTENER_1 =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP) (HOST = 1.1.1.17) (PORT = 1522 ))

)

)

1.1.1.17 is an incorrect IP address.

SQL> startup

ORACLE instance started.

Total System Global Area 1054593024 bytes

Fixed Size 1349616 bytes

Variable Size 507512848 bytes

Database Buffers 541065216 bytes

Redo Buffers 4665344 bytes

Database mounted.

Database opened.

SQL>

The database can be started normally.

SQL>! Ps-ef | grep tns

Oracle 3219 1 0? 00:00:00/oracle/app/oracle/db/bin/tnslsnr LISTENER_1-inherit

Oracle 4125 3881 0 00:00:00 pts/1/bin/bash-c ps-ef | grep tns

Oracle 4127 4125 0 00:00:00 pts/1/bin/bash-c ps-ef | grep tns

SQL>! Lsnrctl status LISTENER_1

LSNRCTL for Linux: Version 11.2.0.3.0-Production on 30-SEP-2013 21:40:25

Copyright (c) 1991,201 1, Oracle. All rights reserved.

Connecting to (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = 1.1.1.10) (PORT = 1522 )))

STATUS of the LISTENER

------------------------

Alias LISTENER_1

Version TNSLSNR for Linux: Version 11.2.0.3.0-Production

Start Date 30-SEP-2013 21:26:50

Uptime 0 days 0 hr. 13 min. 34 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File/oracle/app/oracle/db/network/admin/listener. ora

Listener Log File/oracle/app/oracle/diag/tnslsnr/node1/listener_1/alert/log. xml

Listening Endpoints Summary...

(DESCRIPTION = (ADDRESS = (PROTOCOL = tcp) (HOST = 1.1.1.10) (PORT = 1522 )))

The listener supports no services

The command completed successfully

SQL>

No service registered to listener

Step 3: Modify tnsnames. ora to the correct IP address.

Restart listener

[Oracle @ node1 admin] $ lsnrctl stop LISTENER_1

[Oracle @ node1 admin] $ lsnrctl start LISTENER_1

SQL> alter system register;

System altered.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[Oracle @ node1 admin] $ lsnrctl status LISTENER_1

LSNRCTL for Linux: Version 11.2.0.3.0-Production on 30-SEP-2013 21:43:03

Copyright (c) 1991,201 1, Oracle. All rights reserved.

Connecting to (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = 1.1.1.10) (PORT = 1522 )))

STATUS of the LISTENER

------------------------

Alias LISTENER_1

Version TNSLSNR for Linux: Version 11.2.0.3.0-Production

Start Date 30-SEP-2013 21:42:30

Uptime 0 days 0 hr. 0 min. 32 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File/oracle/app/oracle/db/network/admin/listener. ora

Listener Log File/oracle/app/oracle/diag/tnslsnr/node1/listener_1/alert/log. xml

Listening Endpoints Summary...

(DESCRIPTION = (ADDRESS = (PROTOCOL = tcp) (HOST = 1.1.1.10) (PORT = 1522 )))

The listener supports no services

The command completed successfully

Still not good

[Oracle @ node2 admin] $ sqlplus vic @ vic2

SQL * Plus: Release 11.2.0.3.0 Production on Mon Sep 30 21:43:32 2013

Copyright (c) 1982,201 1, Oracle. All rights reserved.

Enter password:

ERROR:

ORA-12514: TNS: listener does not currently know of service requested in connect

Descriptor

The client cannot be connected either. Therefore, restarting listner will not read tnsnames. Ora content.

Step 4: restart the database

SQL> shut immediate

Startup

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

ORACLE instance started.

Total System Global Area 1054593024 bytes

Fixed Size 1349616 bytes

Variable Size 507512848 bytes

Database Buffers 541065216 bytes

Redo Buffers 4665344 bytes

Database mounted.

Alter system register;

Database opened.

SQL>

System altered.

Oracle @ node1 admin] $ lsnrctl status LISTENER_1

LSNRCTL for Linux: Version 11.2.0.3.0-Production on 30-SEP-2013 21:45:38

Copyright (c) 1991,201 1, Oracle. All rights reserved.

Connecting to (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = 1.1.1.10) (PORT = 1522 )))

STATUS of the LISTENER

------------------------

Alias LISTENER_1

Version TNSLSNR for Linux: Version 11.2.0.3.0-Production

Start Date 30-SEP-2013 21:42:30

Uptime 0 days 0 hr. 3 min. 7 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File/oracle/app/oracle/db/network/admin/listener. ora

Listener Log File/oracle/app/oracle/diag/tnslsnr/node1/listener_1/alert/log. xml

Listening Endpoints Summary...

(DESCRIPTION = (ADDRESS = (PROTOCOL = tcp) (HOST = 1.1.1.10) (PORT = 1522 )))

Services Summary...

Service "haha" has 1 instance (s ).

Instance "vicdb", status READY, has 1 handler (s) for this service...

Service "hehe" has 1 instance (s ).

Instance "vicdb", status READY, has 1 handler (s) for this service...

Service "vicdb" has 1 instance (s ).

Instance "vicdb", status READY, has 1 handler (s) for this service...

Service "vicdbXDB" has 1 instance (s ).

Instance "vicdb", status READY, has 1 handler (s) for this service...

The command completed successfully

[Oracle @ node2 admin] $ sqlplus vic @ vic2

SQL * Plus: Release 11.2.0.3.0 Production on Mon Sep 30 21:45:26 2013

Copyright (c) 1982,201 1, Oracle. All rights reserved.

Enter password:

Connected:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

Normal start and normal connection indicate that tnsnames can be read again when the database is restarted. Ora, is there any other way? You can try again. It should be implemented using lsnrctl reload xxx. There will be an activity tonight and it will be verified tomorrow.