ORA-00119: invalid specification for system parameter LOCAL_LISTENER

 

My database was working fine, and suddenly one day I got this error:

[oracle@amey ~]$ sp
SQL*Plus: Release 12.1.0.2.0 Production on Wed Jun 23 19:49:48 2021
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_CHAMP'
SQL> 
SQL> select name, open_mode from v$database;
select name, open_mode from v$database
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0

In tnsname.ora file add following code containing port number and hostname.

LISTENER_CHAMP =

  (ADDRESS = (PROTOCOL = TCP)(HOST = amey.lad.com)(PORT = 1521))

[oracle@amey ~]$ cat /u01/app/oracle/product/12.1.0.2/db_1/network/admin/tnsnames.ora 

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

CHAMP =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = amey.lad.com)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = champ)

    )

  )

LISTENER_CHAMP =

  (ADDRESS = (PROTOCOL = TCP)(HOST = amey.lad.com)(PORT = 1521))

[oracle@amey ~]$ 

[oracle@amey ~]$ sp

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jun 23 19:58:02 2021

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

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 4630511616 bytes

Fixed Size     2933592 bytes

Variable Size 1509952680 bytes

Database Buffers 3103784960 bytes

Redo Buffers    13840384 bytes

Database mounted.

Database opened.

SQL> select name, open_mode from v$database;

NAME   OPEN_MODE

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

CHAMP   READ WRITE

SQL> exit

Ref: https://dbaclass.com/article/ora-00119-invalid-specification-for-system-parameter-local_listener/