Reputation: 175
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:
My listener Windows service is running:
What is suspicious is that the lsnrctl utility does NOT listen my service name, it just lists the EXTPROC1521:
Whenever I try to connect to the database I get:
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
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
Reputation: 175
Finally solved my problem by following this instruction: https://www.programmersought.com/article/48144147987/
The summary:
Upvotes: 0