Local Listener & Sqlnet.ora And NAMES.DEFAULT_DOMAIN.

Local Listener & Sqlnet.ora And NAMES.DEFAULT_DOMAIN.


DEMO :1  !!!!!

SQLNET.ORA file without NAMES.DEFAULT_DOMAIN=world parameter 


[oracle@rac1 admin]$ cat sqlnet.ora
# sqlnet.ora Network Configuration File: /oracle/DEA/12102/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

SQLNET.USE_ZERO_COPY_IO = 0

NAMES.DIRECTORY_PATH= (TNSNAMES, HOSTNAME)

ADR_BASE = /oracle/DEA



No Local_listener value has been set.

SQL> show parameter listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
listener_networks                     string
local_listener                            string
remote_listener                        string



[oracle@rac1 admin]$ cat tnsnames.ora

LISTENER.WORLD=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1.example.com)(PORT=1521)))

[oracle@rac1 admin]$

Lets set the Local_listener Value 

SQL> show parameter listener

NAME                                 TYPE        VALUE

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

listener_networks                    string

local_listener                       string

remote_listener                      string

SQL> alter system set local_listener='LISTENER' scope=both;

alter system set local_listener='LISTENER' scope=both

*

ERROR at line 1:

ORA-02097: parameter cannot be modified because specified value is invalid

ORA-00119: invalid specification for system parameter LOCAL_LISTENER

ORA-00132: syntax error or unresolved network name 'LISTENER'

Lets Update the tnsnames.ora  and remove the WORLD from Listener. 

[oracle@rac1 admin]$ cat tnsnames.ora

LISTENER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1.example.com)(PORT=1521))

SQL> show parameter listener

NAME                                 TYPE        VALUE

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

listener_networks                    string

local_listener                       string

remote_listener                      string

SQL> alter system set local_listener='LISTENER' scope=both;

System altered.

SQL>  show parameter listener

NAME                                 TYPE        VALUE

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

listener_networks                    string

local_listener                       string      LISTENER

remote_listener                      string

Oh yeah!!!! I am able to resolve the Local listener error Bravo.!!!!

DEMO 2:!!!!!

SQLNET.ORA file with Value  NAMES.DEFAULT_DOMAIN=world parameter .


[oracle@rac1 admin]$ pwd
/oracle/DEA/121/network/admin
[oracle@rac1 admin]$ cat sqlnet.ora
# sqlnet.ora Network Configuration File: /oracle/DEA/12102/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

SQLNET.USE_ZERO_COPY_IO = 0

NAMES.DIRECTORY_PATH= (TNSNAMES, HOSTNAME)

ADR_BASE = /oracle/DEA
NAMES.DEFAULT_DOMAIN=world

[oracle@rac1 admin]$


And will use the same tnsnames.ora

[oracle@rac1 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /oracle/DEA/12102/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

LISTENER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1.example.com)(PORT=1521))).


Lets Bounce a Database.

[oracle@rac1 admin]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 1 09:36:56 2020

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options


SQL> startup force
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'LISTENER'
SQL> shut immediate
ORA-01012: not logged on
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@rac1 admin]$
[oracle@rac1 admin]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 1 09:37:23 2020

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

Connected to an idle instance.

SQL> startup
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'LISTENER'

SQL>

OH!!! Again Error Why!!!!!?? because the NAMES.DEFAULT_DOMAIN=world value set  and LISTENER is without WORLD.

[oracle@rac1 admin]$ cat sqlnet.ora
# sqlnet.ora Network Configuration File: /oracle/DEA/12102/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

SQLNET.USE_ZERO_COPY_IO = 0

NAMES.DIRECTORY_PATH= (TNSNAMES, HOSTNAME)

ADR_BASE = /oracle/DEA

NAMES.DEFAULT_DOMAIN=world

So lets resolve this issue ,

Update the

[oracle@rac1 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /oracle/DEA/12102/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

LISTENER.WORLD=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1.example.com)(PORT=1521))).

[oracle@rac1 admin]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 1 09:41:16 2020

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1644167168 bytes
Fixed Size                  2925024 bytes
Variable Size            1056968224 bytes
Database Buffers          570425344 bytes
Redo Buffers               13848576 bytes
Database mounted.
Database opened.

SQL>

[oracle@rac1 admin]$ tnsping LISTENER

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 01-JUL-2020 09:46:40

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

Used parameter files:

/oracle/DEA/12102/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1.example.com)(PORT=1521)))

OK (0 msec)

[oracle@rac1 admin]$

Oh!!! Bravo We Able to start up database.