Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
Minoo28
New Member

Error - Oracle: ORA-12154: TNS

Am unable to connect to the Oracle Database.  The error is - "Oracle: ORA-12154: TNS:could not resolve the connect identifier specified".  Can anybody offer a solution.  Thanks

19 REPLIES 19
test001
New Member

=========linux useful=======

nmon

====================================================spool log==============

column tm new_value tm
column db new_value db
set feed on
set echo on
set time on
set timing on
set lines 1000 pages 1000
select to_char(sysdate, 'DD_MM_YYYY') tm from dual ;
select name db from v$database;
SPOOL OAS_&no._&tm._&db..log;

===========BLACKOUT==============

./emctl start blackout DR_DRIL_ACTIVITY -nodelevel -d 48:00
./emctl stop blackout DR_DRIL_ACTIVITY
./emctl status blackout

ps -ef|grep agent

cd /oracle/axoraadm/AGENT13C/agent_13.5.0.0.0/bin
./emctl status agent
./emctl start blackout bk_downtime -nodelevel -d 03:00
./emctl status blackout

========================grep command==============

grep -i "GENERAL_ACCT_MAST_TABLE" *

grep -i "GENERAL_ACCT_MAST_TABLE" * | grep -i 'stat'

cat table_stats.log | grep -i 'GATHER'

===============linux commands===========

 

===================stale tables===========

set line 200
select owner,table_name,stale_stats,last_analyzed from dba_tab_statistics where owner=upper('&owner') and stale_stats='YES' and TABLE_NAME not like '%TMP';

===================active session=========

set line 300 pages 400
set feedback on;
col SPID for a15
col USERNAME for a15
col LOGONDATE for a25
col OSUSER for a15
col SQL_ID for a18
col MODULE for a25
select a.sid,b.spid,a.username,
to_char(a.logon_time,'DD-MON-YYYY HH24:MI:SS') LogonDate,a.osuser,a.sql_id , a.status, a.module,a.last_call_et
from gv$session a, gv$process b where a.paddr = b.addr
and a.status = 'ACTIVE' and a.username is not null
order by 9 desc;

====================INDEX==========


SELECT name, height,lf_rows,lf_blks,del_lf_rows FROM INDEX_STATS;

select count(*) from INDEX_STATS where height ='2';


select index_name,status,owner from dba_indexes where status!='VALID';

select index_name,status,owner from dba_indexes where status!='VALID';

select count(*) from dba_indexes where owner='MX_FIN29' and status='VALID';


select OBJECT_NAME,OWNER,CREATED from dba_objects where OBJECT_TYPE='INDEX' and OWNER='MX_REP'order by created FETCH FIRST 50 ROWS ONLY ;


create index MX_REP.IDX_231660001 on MX_REP.AXE_TO_FX_01_MK_REP("M_V_EVT_CLS","M_TP_INT","M_REF_DATA");

 

select degree,count(1) from dba_indexes where owner='MLC' group by degree;

select degree,INDEX_NAME from dba_indexes where owner='MX_FIN' and DEGREE='0';


select degree,INDEX_NAME from dba_indexes where owner='MX_FIN' and DEGREE='0';


alter



SELECT
'alter index '||OWNER||'.'||INDEX_NAME||' noparallel;'
FROM
DBA_INDEXES
WHERE
degree <> 'DEFAULT'
AND to_number(DEGREE) not like '%1%' and INDEX_TYPE not like '%LOB%' and owner='MX_REP'
-- put any exclusions here
--AND INDEX_NAME NOT IN ('BINARY_SEARCH_INDEX','TEXT_SEARCH_INDEX','SIMPLE_SEARCH_INDEX')
ORDER BY
INDEX_NAME
/

 


SELECT
'alter index '||OWNER||'.'||INDEX_NAME||' noparallel;'
FROM
DBA_INDEXES
WHERE
degree not like '%1%'
ANDINDEX_TYPE not like '%LOB%' and owner='MX_REP'
-- put any exclusions here
--AND INDEX_NAME NOT IN ('BINARY_SEARCH_INDEX','TEXT_SEARCH_INDEX','SIMPLE_SEARCH_INDEX')
ORDER BY
INDEX_NAME
/

select 'alter index "'||owner||'"."'||index_name||'" noparallel;' from dba_indexes where DEGREE like '%20%';

theov
Advocate II
Advocate II

Hello, 

I'd make sure the ora client is installed properly, the string is correct and finally tnsnames.ora is up to date. This video might helps as well:

https://www.youtube.com/watch?v=EpeJrO5tHDc

Anonymous
Not applicable

In my case I was able to fix it just putting Server/DB

I was just putting DB in the server name part

Server/DB worked for me in the websever application, but it couldnt connect to my saved oracle instance because it was in a different format. Was not able to change the format in powerbi so i'm back to square one!

Anonymous
Not applicable

Make sure your TNSNAMES.ORA file is updated to your new database in the oracle folder and it will work fine.  I had the same issue and couldn't figure out what was happening, turns out the DB Admins changed the database hostname.  Went in changed the tnsnames.ora file name to the new database connections and power BI connected with no problem.

bjerbe
Advocate I
Advocate I

I was getting this as well, the solution for me was in the Server field to enter <server name>:<port>/<service name>.  Then in the authentication window, select Database and enter the username and pasword.

Do you still need TNSnames file? or it will work without updating/adding connection to TNS file?
I tried the same and still getting the same error (Without any TNS Name file)

I don't believe you should need the TNSNames files ans that would be redundant. In the parameter, you are alredy providing what TNSNames file provides. You can update the TNSNames file to make use is TNSping to ensure that the connection is open.

Hello

 

Should i enter in the parenthesis 

 

The <> just means that it is a variable specific to your instance, do not use the <>.

Super..This worked for me, Thx.. 

it wroks!!! thx!

This worked for me, thanks.

blopez11
Super User
Super User

Look like the connection info you specified can't be resolved

Please see the following taken from http://stackoverflow.com/questions/7957955/ora-12154-could-not-resolve-the-connect-identifier-specif...

 

  1. Is there a tnsnames.ora file
  2. Is there an entry in the file for the service
  3. Can the application find the tnsnames.ora?

Your problem may well be number 3 - does the application run as a different user than when you run the console?

Oracle looks for the tnsnames.ora file in the directory defined in the TNS_ADMIN environment variable - If you are running as different users, then maybe the TNS_ADMIN environment variable is not set, and therefore it cannot find the file?

 

Hope this helps,

In simple terms, would you mind explaining the steps to resolve # 3? Thank you. 

 

1. How do I determine what user the application runs as? This isn't immediately clear. 

2. Not sure what the TNS_ADMIN environment variable is or how to set it in this case or whether google instructions on "how to set TNS environent variable" which I followed are even relevant, or if there's something else I need to do. 

 

Please expand. 

Try like this please

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=101.21.29.218)(PORT=3090))(CONNECT_DATA=(SERVICE_NAME=TST)(INSTANCE_NAME=TST)))

Hello blopez11,

 

I checked what your link suggested 1) sqlplus user - no errors there. 2) defined the TNS_ADMIN string in the registry in the two homes I had.  I am still getting the same error.  Mine is on premise and not a web application.

Don't know what else I need to do.

 

Thanks

 

Minoo28

Did you try adding the Host and Service Name to the PowerBI. 

If your hostname is System24 and ServiceName is DEV1 then you can try System24/DEV1 

 

Minoo28
New Member

I am unable to connect to Oracle Database.  getting an error - Details: "Oracle: ORA-12154: TNS:could not resolve the connect identifier specified". 

 

I am able to connect to the same oracle database using Power Query in Excel but not able to connect with Power BI.  This tells me I have downloaded the correct Oracle Client with the correct TSN.ORA and Service names.

Can anybody help me find a solution. 

Thanks

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.