CODEWITHSUNDEEP
oracle-databaselistenertnsnames
bluefox
bluefox

Reputation: 175

Oracle 19c: Oracle Error 12514, no connection to DB possible

on my local oracle 19c installation (Windows Server 2016) everything was running fine until a Windows update from this week. Since then I get the error ORA-12514 – TNS:listener does not currently know of service requested in connect descriptor whenever I try to connect to the DB.

My listener.ora:

listener.ora

My listener Windows service is running: enter image description here

What is suspicious is that the lsnrctl utility does NOT listen my service name, it just lists the EXTPROC1521: enter image description here

Whenever I try to connect to the database I get: enter image description here

I cannot connect by any way to the Database, neither with the normal user name, than with sys or system. I tried to play with listener.ora and restarted the Windows service but all without impact :-( Can you please help me?

Upvotes: 0

Views: 3501

Answers (2)

Bjarte Brandt
Bjarte Brandt

Reputation: 4481

Is your database in open mode? NOTE! Your Database Windows Service can be running, but the database can be in any of these modes: open|mount|nomount|down. Make sure your database is in open mode. As soon as db reaches open mode, the database will register it's service(s) to the listener and you will be able to login

Bring the database into open mode:

C:\>set ORACLE_SID=<MYSID>
C:\>sqlplus / as sysdba
SQL>startup

alert log

The database alert log is your goto file in order to inspect the health of the database engine.
The database alert log will tell why the database cannot reach open mode after restart. (Probably due to undersized FRA (fast recovery area))

It is a nightmare to locate the alert log, so I created a bash function years ago.

declare -f alog
alog ()
{
    less -r ${ORACLE_BASE}/diag/rdbms/$(echo ${ORACLE_SID} | tr '[:upper:]' '[:lower:]')*/${ORACLE_SID}/trace/alert_${ORACLE_SID}.log
}

fra.sql

--------------------------------------------------------------------------------
-- name:    fra.sql
-- purpose: Display information about the Fast Recovery Area (FRA)
-- author:  Bjarte Brandt
-- date:    09.03.2010
-- remarks:
-- Change size:
-- alter system set db_recovery_file_dest_size=<size> scope=both
--------------------------------------------------------------------------------
col name format a60
col used format 99.99 heading 'Used %'

select
  name,
  floor(space_limit / 1024 / 1024) "Size MB",
  ceil(space_used  / 1024 / 1024) "Used MB",
  (space_used/space_limit) * 100 used
from
  v$recovery_file_dest
order by name;

select
  file_type,
  percent_space_used,
  percent_space_reclaimable,
  number_of_files
from
  v$flash_recovery_area_usage;

If you now are at 100%, you have to decide what to do with a database in archivelog mode. You can implement a backup strategy, disable fast recovery area, set the database in noarchivelog mode or resize the FRA - all choices depending on the use case.

Note! I know there is a lot to consider. Just remember you are now making use of the exact same engine as the worlds biggest bank. It takes time to master it.

Best of luck!

Upvotes: 1

bluefox
bluefox

Reputation: 175

Finally solved my problem by following this instruction: https://www.programmersought.com/article/48144147987/

The summary:

  • Shutdown database by command "shutdown"
  • Start the database but by"startup mount" to prevent that it is tried to open it
  • Make a "select * from v$log" to see the redo logs
  • Make a recovery by "recover database until time 'YYYY-MM-DD HH:MM:SS';" with a datetime that is inside the redo log that is marked as "CURRENT".
  • The system should report "Media recovery complete"
  • Then make a "alter database open resetlogs;". This resets all active redologs and opens the database.
  • Then shutdown database
  • Then start database by comment "startup" (w/o option)
  • The system should now report "Database open"

Upvotes: 0

Related Questions