Oracle DB 12c can't start tns service

Question

Yesterday I restarted my machine and now my DB is unavailable. I had already some difficulties to run it the first time because there were two different installations of Oracle DB.

The windows services that I suppose they should run:

• OracleJobSchedulerFOOBAR
• OracleOraDB12Home3MTSRecoveryService
• OracleOraDB12Home3TNSListener : OracleOraDB12Home3TNSListener service on your local computer start and stopped. Some service stop automatically if they are not in use by other services or programs.
• OracleServiceFOOBAR
• OracleVssWriterFOOBAR

So as you can see : I can not start the OracleOraDB12Home3TNSListener service.

I changed the file tnsnames.ora :

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )


  FOOBAR=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = myurl.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = FOOBAR)
    )
  ) 

And my listener.ora is

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\OracleDB\product\12.1.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\OracleDB\product\12.1.0\dbhome_1\bin\oraclr12.dll")
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = myurl.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

I already try a lot of things like :

• sqlplus / as sysdba and startup but I got

ORA-00119: invalid specification for system parameter LOCAL_LISTENER ORA-00132: syntax error or unresolved network name 'LISTENER_FOOBAR'

• alter system set local_listener='FOOBAR'; scope=spflile; but I got

* ERROR at line 1: ORA-01034: ORACLE not available Process ID: 0 Session ID: 0 Serial number: 0

When I check I the Oracle Administration Assistance for the OracleDB12Home3 :

• ORACLE_SDI = FOOBAR
• ORACLE_HOME = C:\OracleDB\product\12.1.0\dbhome_1

Does someone has any idea on how to help me?

Answer 1

After long investigations, thanks the help of a colleague, it's working.

To sum up how we did to solve it :

• So to fix my first issue 'OracleOraDB12Home3TNSListener can not start' was due to that I had a new IP. So I changed it in my config (...\NETWORK\ADMIN ): https://community.oracle.com/thread/855326

• when I was trying to connect with sql developer (after the restart of my computer). I had the error: ORA-01034: ORACLE not available ORA-27101: shared memory realm does not exist. That was meaning that my DB was not started (http://www.dba-oracle.com/sf_ora_01034_oracle_not_available.htm).

• So I try start the db manually sqlplus / as sysdba and startup but I had always this output :

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

• To fix I tried to work on the LISTENER.ORA and TNSNAMES.ORA files :

LISTENER.ORA

---

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\OracleDB\product\12.1.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\OracleDB\product\12.1.0\dbhome_1\bin\oraclr12.dll")
    )
    (SID_DESC =
      (GLOBAL_DBNAME=FOOBAR)
      (ORACLE_HOME = C:\OracleDB\product\12.1.0\dbhome_1)
      (SID_NAME = FOOBAR)
    )
  )

LISTENER_FOOBAR =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST =  localhost)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

TNSNAMES.ORA

---

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )


FOOBAR =
 (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST =  localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = FOOBAR)
    )
  ) 

• I try to restart the right windows service several times : OracleServiceFOOBAR but nothing was changing. I had always the same issue. I remember that I did pfile configuration.

• So I decided to change the pfile configuration with something like alter system set LOCAL_LISTENER='(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))' scope=both;. Right but I can't apply any command line because the database don't start...

• so i created a pfile called init.ora with *.local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))' trick from ORA-00119: invalid specification ORA-00132: syntax error. And I started the DB wit this file in parameter SQL> startup pfile=c:\oracledb\admin\XXX\pfile\init.ora

And then it was working.