Oracle Text Installation 12c

How to install Oracle TEXT

What is Oracle TEXT:-

It provided indexing with work and theme searching. It also has capabilities  to view text in query applications and  in document classification applications.

Installing Oracle Text :-

Oracle TEXT is not a part of the database installation. you can use GUI and CLI methods to configure ORACLE TEXT on DB after the DB installation.

1. You need to connect using SYS user and catctx.sql to start the installation of Oracle Text.
2. Oracle recommends to set set OVERFLOW-CONTROL=*NO-CONTROL on the terminal you are going to run catctx.sql for Oracle TEXT.
3. drdefus.sql need to run using CTXSYS user that is needed for Oracle TEXT if you are using US english text which we all do.


Steps to follow during the installation of Oracle TEXT:-
1. log in to the Db server and open a terminal to set OVERFLOW-CONTROL=*NO-CONTROL to start the installation of Oracle TEXT.
2. connect to SYS user and run the below script to install ORACLE TEXT(Make sure you change the path for the script. It's $ORACLE_HOME\admin\catctx.sql)

@@D:\app\oracle\product\12.1.0\dbhome_1\ctx\admin\catctx.sql CTXSYS SYSAUX TEMPORARY NOLOCK;

where  TEMPORARY is temp tablespace, you need to check the name of the temp tablespace before running the script to install oracle TEXT.

It will take 10-15min to complete the script which includes creating some procedures and inserting data.

3. You need run drdefus.sql if you are going to work with US English texts.
connect CTXSYS/CTXSYS@TNS
and run @@D:\app\oracle\product\12.1.0\dbhome_1\ctx\admin\defaults\drdefus.sql;



You are ready to use ORACLE TEXT.

Oracle Mean company, not database :)

Oracle database 18c XE

Oracle database 18c XE

Oracle Database 18c XE is a free for use but with below limitations on H/W and memory:-

1.       Instance:-  We can only have one instance per server with the instance name XE. Please be aware that we                             cannot change the name of the instance.  This is default and it is not clear whether we are                                   allowed to change it.

2.       Threads:-   We can only have  2 user threads.

3.       CPU:-           We can only have 2 CPUs(max) in 18c XE. If your machine have more CPU’s it will not count more                        than two CPU’s.

4.       Memory:-  We can only have RAM up to 2GB(max). 

5.       Storage:-    We can only have  12GB of user data stored (max). 

Some important Key features in 18c XE :-

1.       Pluggable databases :-   Oracle Multitenant Container Database only can have Up to 3 Pluggable Databases. 

2.       Compression :-               Basic Table Compression and Index Compression.

3.       Encryption  :-                  Transparent Data Encryption(TDE) which enables you to encrypt sensitive data that                                                 you store in tables and tablespaces.

4.       partitioning :-                 Enhance performance, availability, and manageability.

We can now run multiple XE installations inside multiple VMs/Containers with one each. That means that we can run 4 instances on 4 VMs, one instance per VM.

Oracle Mean company, not database :)

ORA-04031

ORA-04031 Errors Occurring (Doc ID 2063751.1)

SYMPTOMS ORA-04031 Errors Occurring

ORA-04031 errors occurring in a RAC instance with the trace file showing high memory usage for "ges resource dynamic" and "ges enqueues" memory in the shared pool.  This can cause LMD processes to become unresponsive leading to an instance termination.

The following query can be used to determine if this fix will help:

select substr(RESOURCE_NAME,instr(RESOURCE_NAME,'[',1,3)+1,2) , MASTER_NODE, count(*)   from gv$ges_resource   where substr(RESOURCE_NAME,instr(RESOURCE_NAME,'[',1,3)+1,2) in ('DX', 'BB')   group by substr(RESOURCE_NAME,instr(RESOURCE_NAME,'[',1,3)+1,2), MASTER_NODE    order by 3 desc;

If that shows a large number of entries (much larger than the expected total number of transactions), then this fix will help.

CAUSE for ORA-04031

This is due to unpublished bug:21373473 fixed in 12.2, and occurs due to DX and BB locks being cached but not cleared out.  This fix removes the need to cache DX and BB locks and hence reduces overall shared pool memory usage in RAC instances.

ORA-04031 errors occurring with high memory utilization for "ges enqueues" & "ges resource dynamic" memory allocations.

SOLUTION

Download and apply the one-off patch number 21373473 (Patch:21373473) for your platform and version combination. Please note that if using 12.1.0.2, then you should also apply the related patch number 21260431 (Patch:21260431) which also impacts the shared pool memory allocations identified here.

This issue can also be worked around by setting 

_GES_DIRECT_FREE_RES_TYPE="CTARAHDXBB" in the instances.



Note:- We also found ORA-01041: internal error. hostdef extension doesn't exist in alert logs that depend on ORA-04031. also suggest opening a Service request with oracle if not sure with the above method.

ORA-02051 Another Session Or Branch In Same Transaction Failed

ORA-02051 Another Session Or Branch In Same Transaction Failed (Doc ID 2253226.1)         

SYMPTOMS for ORA-02051 Another Session Or Branch In Same Transaction Failed.
Database performance is slow and caused   the transactions ORA-02051 another session or branch in same transaction failed or finalized

CAUSE for ORA-02051 Another Session Or Branch In Same Transaction Failed.
Session transactions branches caused the issue Excessive Waits On The Event "Global transaction acquire instance locks"

SOLUTION
Please use below sql and identified underscore parameter values for ORA-02051 Another Session Or Branch In Same Transaction Failed :
SQL>
select a.ksppinm "Parameter", b.ksppstvl "Session Value",c.ksppstvl "Instance Value"  FROM x$ksppi a,x$ksppcv b, x$ksppsv c  WHERE a.indx = b.indx AND a.indx = c.indx AND a.ksppinm LIKE '/_%' escape '/'  AND (a.ksppinm like '%clusterwide_global%' or a.ksppinm like '%disable_autotune_gtx%') ;

output:-

Parameter                                                          Session Value         Instance Value
----------------------------------------             -------------------- --------------------
_clusterwide_global_transactions             TRUE                              TRUE
_disable_autotune_gtx                                  FALSE                             FALSE


Set following to solver ORA-02051 Another Session Or Branch In Same Transaction Failed :-
alter system set "_clusterwide_global_transactions"=false scope=spfile;
alter system set "_disable_autotune_gtx"=TRUE scope = spfile;  

Note:-  bounce the instances for the changes to take effect

Oracle Mean company, not database :)

ORA-3136 WARNING: inbound connection timed out (ORA-3136)

WARNING: inbound connection timed out (ORA-3136) 

The "WARNING: inbound connection timed out (ORA-3136)" in the alert log indicates that the client was not able to complete it's authentication within the period of time specified by parameter SQLNET.INBOUND_CONNECT_TIMEOUT.

You may also witness ORA-12170 without timeout error on the database server sqlnet.log file. This entry would also have the client address which failed to get authenticated. Some applications or JDBC thin driver applications may not have these details.

From 10.2 onwards the default value of this parameter is 60 seconds, hence if the client is not able authenticate within 60 secs, the warning would appear in the alert log and the client connection will be terminated.

This timeout restriction was introduced to combat Denial of Service (DoS) attack whereby malicious clients attempt to flood database servers with connect requests that consumes resources.

Cause:

There can be three main reasons for this error

1.             Server gets a connection request from a malicious client which is not supposed to connect to the database, in which case the error thrown is the correct behavior. You can get the client address for which the error was thrown via sqlnet log file.

2.             The server receives a valid client connection request but the client takes a long time to authenticate more than the default 60 seconds.

3.             The DB server is heavily loaded due to which it cannot finish the client logon within the timeout specified.

Diagnosis: 

The default value of 60 seconds is good enough in most conditions for the database server to authenticate a client connection. If it’s taking longer period, then it’s worth checking all the below points before going for the workaround:

1. Check whether local connection on the database server is successful & quick.

2. If local connections are quick, then check for underlying network delay with the help of your network administrator.

3. Check whether your Database performance has degraded by anyway.

4. Check alert log for any critical errors for e.g., ORA-600 or ORA-7445 and get them resolved first.

Resolution:
 
These critical errors might have triggered the slowness of the database server.

As a workaround to avoid only these warning messages, you can set the parametersSQLNET.INBOUND_CONNECT_TIMEOUT and INBOUND_CONNECT_TIMEOUT_listenername
to the value more than 60. 

In server side sqlnet.ora file add SQLNET.INBOUND_CONNECT_TIMEOUT

SQLNET.INBOUND_CONNECT_TIMEOUT = 120

In listener.ora file INBOUND_CONNECT_TIMEOUT_listenername

INBOUND_CONNECT_TIMEOUT_LISTENER = 110

From Oracle version 10.2.0.3 onwards the default value of INBOUND_CONNECT_TIMEOUT_ is 60 seconds. For previous releases it is zero by default.

Oracle Mean company, not database :)

What is Deprecation of Non-CDB Architecture means

Please check the below link for point (8.1.1 : Deprecation of Non-CDB Architecture).

https://docs.oracle.com/database/121/UPGRD/deprecated.htm#BABDBCJI

"Deprecation" means "at some stage in future, Oracle *might* stop doing enhancements on this features, and at some stage after that, Oracle *might* no longer support it".

In 12.1.0.1.0 non-CDB (old architecture) is not deprecated whereas from 12.1.0.2.0 it got deprecated. Even if you have single database, it is better to have it in multitenant architecture as 1CDB and 1PDB.

As per Oracle document:- https://docs.oracle.com/en/database/oracle/oracle-database/12.2/upgrd/deprecated-features-oracle-database-12c-r2.html#GUID-5D181F03-F74D-4888-B7B2-7176CF6FA8F8

Deprecation of Non-CDB Architecture

The non-CDB architecture was deprecated in Oracle Database 12c. It can be desupported and unavailable in a release after Oracle Database 19c .

Oracle recommends use of the CDB architecture.

Oracle Database 19c ?

Release 12.2: New releases will be annual and the version will be the last two digits of the release year. The release originally planned as 12.2.0.2 will now be release 18c, and the release originally planned as 12.2.0.3 will be release 19c. Releases 18c and 19c will be treated as under the umbrella of 12.2 for Lifetime Support purposes. The current plan is for Oracle Database 19c to be the last release for 12.2. This may change in the future to Oracle 20 as the last release for 12.2

COUNT STOPKEY ROWNUM optimization

No COUNT STOPKEY in explain plan:- ROWNUM optimization

Product:- Oracle Server (RDBMS)

Range of versions believed to be affected:-  12.1

confirmed affected:- 1. 11.1.0.7
                                    2. 10.2.0.4

Platforms affected:-         all

So I was doing explain plan for below SQL on database version 11.2.0.1.0:-

select * from test where rownum = 0;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 1829668517

-------------------------------------------------------------------------------------
| Id  | Operation           | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |               |  3219K|   733M| 35580   (2)| 00:07:07 |
|   1 |  COUNT              |               |       |       |            |          |
|*  2 |   FILTER            |               |       |       |            |          |
|   3 |    TABLE ACCESS FULL| TEST          |  3219K|   733M| 35580   (2)| 00:07:07 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(ROWNUM=0)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     129363  consistent gets
     129358  physical reads
          0  redo size
       6210  bytes sent via SQL*Net to client
        513  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

On Database version 11.2.0.4.0 it was:-

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 2416982823

------------------------------------------------------------------------------------
| Id  | Operation          | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |               |     1 |   240 |     2   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY     |               |       |       |            |          |
|   2 |   TABLE ACCESS FULL| TEST            |     1 |   240 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM=0)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
       6210  bytes sent via SQL*Net to client
        513  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

After studying on oracle support I found this as a bug on 11.2.0.1.0 explained under Oracle
Please check your version for the bug because you can easily see the difference and performance matrix between both explain plans.

Beware! This use of rownum< can cause performance problems. Using rownum may change the all_rows optimizer mode for a query to first_rows, causing unexpected sub-optimal execution plans. One solution is to always include an all_rows hint when using rownum to perform a top-n query.

Keep sharing :)

SQL*Loader

SQL*Loader

What is this?

SQL*Loader loads data to an oracle database.With the help of SQL*Loader you can load data from external files to Oracle database. With the powerful data parsing engine that puts little limitation on the format of the data in the datafile.

Below key points taken from Oracle documention:-

1. Load data across a network. This means that you can run the SQL*Loader client on a different system from the one that is running the SQL*Loader server.

2. Load data from multiple datafiles during the same load session.

3. Load data into multiple tables during the same load session.

4. Specify the character set of the data.

5. Selectively load data (you can load records based on the records' values).

6. Manipulate the data before loading it, using SQL functions.

7. Generate unique sequential key values in specified columns.

8. Use the operating system's file system to access the datafiles.

9. Load data from disk, tape, or named pipe.

10. Generate sophisticated error reports, which greatly aid troubleshooting.

11. Load arbitrarily complex object-relational data.

12. Use secondary datafiles for loading LOBs and collections.


Example to load data in fixed format:- 

load data
infile 'test.dat'  "fix 12"
into table test
fields terminated by ',' optionally enclosed by '"'
(col_1, col_2)

example.dat:
007,   cd, 0008,fghi,
00009,lmn,
1, "pqrs",
0010,uvwx,

total size of oracle database

How to check the total size of oracle database

We know  oracle database consists of data files, redo log files, control files, temporary files and temporary files.

The size of the database actually means the total size of all these files.


col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
, round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
from (select bytes
from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used
, (select sum(bytes) as p
from dba_free_space) free
group by free.p
/

I found this on a blog and very use full. It will show you the total size and the used size. Total size includes size of  all files.

output will be like :-



Database Size        Used space           Free space
-------------------- -------------------- --------------------
78 GB                59 GB                19 GB

Keep sharing :)

ORA-65016: FILE_NAME_CONVERT must be specified

ORA-65016: FILE_NAME_CONVERT must be specified

I just started working on 12C version of oracle database.It's new to me and facing many problems.


Error code: ORA-65016: FILE_NAME_CONVERT must be specified


Description:"ORA-65016: FILE_NAME_CONVERT must be specified" normally occurs when you create a PDB.I will explain later what is a PDB.


Cause and solution :

 ORA-65016: FILE_NAME_CONVERT must be specified caused when Data files, and possibly other files, needed to be copied as a part of creating a pluggable database.Enable OMF or define PDB_FILE_NAME_CONVERT system parameter before issuing CREATE PLUGGABLE DATABASE statement, or specify FILE_NAME_CONVERT clause as a part of the statement and make sure the path you are giving to convert the file exists.

I think if you are creating the PDB's using GUI then you will not face this error "ORA-65016: FILE_NAME_CONVERT must be specified". If you creating ODB using script and you have gave a wrong path then may you face "ORA-65016: FILE_NAME_CONVERT must be specified".


Syntax to create PDB:-

create pluggable database TEST admin user TEST identified by TEST  file_name_convert = ('lcoation/', '/lcoation/');


Please write in comment box if you think there is better explanation on this error "ORA-65016: FILE_NAME_CONVERT must be specified".

ORA-01578: ORACLE data block corrupted (file # XX, block # XXXXX)

ORA-01578: ORACLE data block corrupted (file # XX, block # XXXXX)

As this error (ORA-01578: ORACLE data block corrupted) message shows that you have a corrupt block. So for data corruption you need to check what is going wrong.Check alert logs for more detail.

You can also use below sql:-

select * from v$database_block_corruption;

When i run the SQL. I found that one file # in my case 14 and some block 15353 is corrupted. As we know Corruption can occur in a table or index. So i have checked for this. In my case it was an index. So for a solution i just drop the index and created again and rebuild it. Just remember you cannot rebuild the index if it is having error ORA-01578: ORACLE data block corrupted.

So drop and create index works for me and now i can rebuild. Dropping a index will not harm you much because it's already on corrupt block.

Also i think when we create index again then it will take a different block. I am not sure about this because i have checked with sql after dropping and creating the index:-

select relative_fno, owner,segment_name,segment_type from dba_extents where file_id = 14 and 15353 between block_id and block_id + blocks - 1;

And found the same file and block information. It is strange as when i run the SQL : -select * from v$database_block_corruption;
It also shows me the same information that showed me that i still have the corroupt block for the same index and same file. But the thing is my DB is working now.  For now It's working fine.


Please if someone has faced it before let me know in comment section. How to get rid of the error ORA-01578: ORACLE data block corrupted.

For a table label corruption you can use RMAN. I am just putting the link below.

click here

Hope this will save your day.

Can anyone can tell, what are the reasons for block corruption?

 “ORA-01578: ORACLE data block corrupted” This is not a common error message means we don’t receive/see this very often.  Data block corruption is a serious issue, and it is crucial to address it promptly. Always perform regular backups and ensure the integrity of your database to minimize the risk of data corruption.

"ORA-01578: ORACLE data block corrupted" on  INDEX

If it occurs on INDEX that means you are lucky :) to fix this simply dropping/recreating the index will solve the issue.

Let's say it's not INDEX, Which is scary but then you have mainly two options

1. Restore the backup to avoid losing data.
2. You don't have a backup or the restore is not working or you just want to extract as much data as possible. 

 As mentioned "ORA-01578: ORACLE data block corrupted" is not common but very serious issue which mostly relates to your storage as well. Here are the possible 8 solutions for "ORA-01578: ORACLE data block corrupted"

1. Restore from Backup
2. Use Data Recovery Advisor
3. Run DBVERIFY Utility
4. Use RMAN Utility
5. Check Disk and Storage
6. Check for Software Bugs
7. Perform Block-Level Recovery
8. Engage Oracle Support

Restore from Backup

   If you have a valid and recent backup of the database, you can restore the corrupted data block from the backup. This requires performing a point-in-time recovery or a complete database restore.

Use Data Recovery Advisor

   Oracle provides the Data Recovery Advisor (DRA) tool to diagnose and repair data block corruption issues. You can use the DRA to analyze the corruption and generate repair recommendations. Follow the instructions provided by the DRA to repair the corrupted data block.

Run DBVERIFY Utility

   The DBVERIFY utility is a built-in Oracle tool used to check the logical and physical integrity of data blocks. Run DBVERIFY against the affected data files to identify the corrupt blocks. If possible, restore the corrupted blocks from a backup or recreate them using the RECOVER command.

 Use RMAN Utility

   If you are using Oracle Recovery Manager (RMAN), you can use its block media recovery feature to repair the corrupted data blocks. RMAN can restore the corrupted blocks from a backup or perform block recovery using online redo logs.

Check Disk and Storage

   Verify that the disk or storage system where the corrupted data block resides is functioning correctly. Check for any hardware failures, disk errors, or storage issues that might have caused the corruption. Correct any underlying problems before attempting to repair the data block.

Check for Software Bugs

   Consult the Oracle Support website, bug database, or community forums to check if the corruption issue is a known bug. There might be specific patches or workarounds available to address the problem.

Perform Block-Level Recovery

   In some cases, it might be necessary to perform a block-level recovery. This involves using RMAN to restore and recover individual data blocks from a backup. Exercise caution when performing block-level recovery as it can be complex and may require expert guidance.

Engage Oracle Support

   If none of the above solutions resolve the issue, it is recommended to contact Oracle Support for further assistance. Provide them with the error details, associated trace files, and any relevant diagnostic information for analysis.

 

Note that data block corruption is a serious issue, and it is crucial to address it promptly. Always perform regular backups and ensure the integrity of your database to minimize the risk of data corruption. If you want to add more please comment. 

Here are a few solutions and studies by oracle support.

• Bug 7381632 - ORA-1578 Free corrupt blocks may not be reformatted when Flashback is enabled (Doc ID 7381632.8)
• Content Reset fails with "java.sql.SQLException: ORA-01578: ORACLE data block corrupted " (Doc ID 2119387.1)
• Data Collections failing with ORA-01578: ORACLE data block corrupted error (Doc ID 2752761.1)
• ORA-1578 / ORA-26040 Corrupt blocks by NOLOGGING - Error explanation and solution (Doc ID 794505.1)
• Use RMAN to format corrupt data block which is not part of any object (Doc ID 1459778.1)
• ORA-1578 Methods to Skip Block Corruption (Doc ID 2199133.1)

ORA-12541: TNS:no listener

ORA-1254: TNS: could not resolve the connect identifier specified

This error is because the connect identifier given, wrongalias, cannot be resolved
into database connection details by the TNS (Transparent Network Substrate—not an
acronym particularly worth remembering) layer of Oracle Net. The name resolution
method to be used and its configuration is a matter for the database administrator. In
this case, the error is obvious: the user entered the wrong connect identifier.
The second connect attempt gives the correct identifier, orcl.

This fails with

ORA-12541: TNS:no listener

This indicates that the connect identifier has resolved correctly into the address
of a database listener, but that the listener is not actually running. Note that another
possibility would be that the address resolution is faulty and is sending SQL*Plus
to the wrong address. Following this error, the user should contact the database
administrator and ask him or her to start the listener. Then try again.
The third connect request fails with

ORA-12514: TNS:listener does not currently know of service

requested in connect descriptor
This error is generated by the database listener. SQL*Plus has found the listener
with no problems, but the listener cannot make the onward connection to the database
service. The most likely reason for this is that the database instance has not been
started, so the user should ask the database administrator to start it and then try again.

Cómo solucionar ORA-12505, Listener no conoce actualmente de SID

Cómo solucionar ORA-12505, Listener no conoce actualmente de SID



Este es un error muy común y realmente se chupar la sangre si no estás camino correcto. Intentaré explicarle sobre este error.

Como se puede ver este error se muestra que el oyente está en marcha y en funcionamiento, pero no sirve el SID que está destinado a. Y sí primero usted necesita comprobar donde usted está haciendo frente a este error. Es en el lado del cliente o en el lado del servidor.

Entonces, ¿cuál es el SID que está buscando este oyente?

SID es el nombre de su base de datos o puede decir identificador de servicio, que es ayudar a identificar el servicio como utilizamos PROD para la producción de UAT para el servidor de pruebas. SID de la base de datos debe ser idéntico para evitar la confusión. Su longitud es de 8 caracteres sólo significa que no se puede extender más de 8 caracteres.

¿Cómo funciona el oyente?

Como todos sabemos que es como un coche, que nos caen desde el aeropuerto al hotel, hahah lo siento, no soy bueno en los ejemplos. Así que permítanme explicar técnicamente, cada vez que intenta hacer la conexión de una máquina cliente con el servidor DB en lugar de conectar directamente oracle tiene esta utilidad llamada oyente. Como usted puede entender por su nombre, sí, sí sí que va a escuchar su reqst y luego pasar que a DB. Wow gran derecho.?


Ahora, ¿qué pasa si usted está tomando un taxi a algún hotel y ese hotel no existe? Lo mismo ocurre con este error. El conductor del taxi no sabe adonde usted va tan él a través de usted el error: -

"ORA-12505, TNS: Listener no sabe actualmente de SID dado en el descriptor de conexión"


¿Cómo solucionar esto "ORA-12505, TNS: el oyente no está actualmente"?



Es muy fácil. Dile al taxista nombre del hotel que realmente existe. La tarifa suficiente ¿verdad?

OK Sugerencias para solucionar problemas.

Todos sabemos que listner.ora y tnsnames.ora son dos archivos que están involucrados en este caso. Y la ubicación de estos archivos son $ ORACLE_HOME \ network \ admin

Ahora compruebe su tnsnames.ora que SID se menciona es correcto o no. Al igual que para mi prueba de DB es debe ser como a continuación: -
TEST_TNS = (DESCRIPCION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = *******) (PORT = 0000))) (CONNECT_DATA = (SERVICE_NAME = TEST) (INSTANCE_NAME = TEST)


Usted puede cambiar el formato también pero me gusta esta manera. Y sí PORT no puede ser 0000.;)

Ahora sabe que el nombre del servicio SID TEST está escuchando en el número de puerto 0000.

Si he establecido entradas exactas de las que no debe enfrentar ningún problema. Por favor, compruebe estas entradas. También verifique el archivo listner.ora también. Si hay desajuste en las entradas. Que vas a enfrentar el mismo problema

Eso resolverá su problema.
También compruebe este enlace: - ora-12154-error-en-oracle-11g-and-12c

Sigue compartiendo. Mantén la sonrisa