DronaBlog

Showing posts with label Oracle. Show all posts
Showing posts with label Oracle. Show all posts

Tuesday, January 31, 2023

What is ORA-12154: TNS could not resolve service name error in Oracle database

 Would you be interested in knowing what causes ORA-12154 error and how to resolve it? Are you also interested in knowing important tips in order to resolve ORA-12154 error? If so, then you reached the right place. In this article, we will understand how to fix this error. Let's start.






What is ORA-12154 error in Oracle database?

ORA-12154: TNS could not resolve service name is a common error encountered while connecting to an Oracle database. This error occurs when the TNS (Transparent Network Substrate) service is unable to find the service name specified in the connection string.


Here are some tips to resolve the ORA-12154 error:


  • Check the TNSNAMES.ORA file: This file is used by TNS to resolve the service name to an actual database connection. Check the file for any spelling or syntax errors in the service name.

  • Verify the service name: Make sure that the service name specified in the connection string matches the service name defined in the TNSNAMES.ORA file.

  • Update the TNS_ADMIN environment variable: If you are using a different TNSNAMES.ORA file, make sure that the TNS_ADMIN environment variable points to the correct location.

  • Check the listener status: Ensure that the listener is running and able to accept incoming connections. You can check the listener status by using the “lsnrctl status” command.

  • Restart the listener: If the listener is not running, restart it using the “lsnrctl start” command.

  • Check the network connectivity: Verify that the server hosting the database is reachable and there are no network issues preventing the connection.

  • Reinstall the Oracle client: If all other steps fail, reinstalling the Oracle client may resolve the ORA-12154 error.

  • Verify the Oracle Home environment variable: Make sure that the Oracle Home environment variable is set correctly to the location of the Oracle client software.

  • Check the SQLNET.ORA file: This file is used to configure the Oracle Net Services that provide the communication between the client and the server. Verify that the correct settings are configured in the SQLNET.ORA file.

  • Use the TNS Ping utility: The TNS Ping utility is used to test the connectivity to the database by checking the availability of the listener. You can use the “tnsping” command to run this utility.

  • Check the firewall settings: If the server hosting the database is located behind a firewall, verify that the firewall is configured to allow incoming connections on the specified port.

  • Disable the Windows Firewall: If the Windows firewall is enabled, it may be blocking the connection to the database. Try disabling the Windows firewall temporarily to see if it resolves the ORA-12154 error.

  • Check the port number: Make sure that the port number specified in the connection string matches the port number used by the listener.

  • Try a different connection method: If the error persists, try connecting to the database using a different method such as SQL*Plus or SQL Developer.





  • Check for multiple TNSNAMES.ORA files: If you have multiple Oracle client installations on the same machine, there may be multiple TNSNAMES.ORA files. Make sure you are using the correct TNSNAMES.ORA file for your current Oracle client installation.

  • Check the service name format: The service name can be specified in different formats such as a simple string, an easy connect string, or a connect descriptor. Make sure that you are using the correct format for your particular scenario.

  • Upgrade the Oracle client software: If you are using an outdated version of the Oracle client software, upgrading to the latest version may resolve the ORA-12154 error.

  • Check for incorrect hostname or IP address: Verify that the hostname or IP address specified in the connection string is correct and matches the actual hostname or IP address of the database server.

  • Verify the SERVICE_NAME parameter in the database: If you are connecting to a database that uses the SERVICE_NAME parameter instead of the SID, make sure that the service name specified in the connection string matches the actual service name in the database.





  • Check the network configuration: If you are using a complex network configuration such as a VPN, make sure that the network is configured correctly and that the database server is accessible from the client machine.

  • Verify that LDAP is listed as one of the values of the names.directory_path parameter in the sqlnet.ora Oracle Net profile.
  • Verify that the LDAP directory server is up and that it is accessible.
  •  

  • Verify that the net service name or database name used as the connect identifier is configured in the directory.
  •  

  • Verify that the default context being used is correct by specifying a fully qualified net service name or a full LDAP DN as the connect identifier

  • By following these tips, you should be able to resolve the ORA-12154 error and successfully connect to your Oracle database. If the error persists, it is important to seek the help of a qualified Oracle database administrator or support specialist.


    Learn more about Oracle here







    Tuesday, January 25, 2022

    What is RANK function in Oracle database ?

                   Are you looking for an article on the Rank function in the Oracle database? Are you also interested in knowing how to use the RANK function in SQL? If so, then you reached the right place. In this article, we will learn the RANK function in detail Let's start.


    A) What is the RANK function?

                  The RANK is an analytic function in oracle we use the RANK function to get the rank of a value in a group of values. The RANK  function can be used both as an aggregate and as an analytic function.






    B) Using RANK function as an aggregate function 

                 We need to use the following syntax in order to use the RANK function as an aggregate function 

                 RANK (Expression_1 , Expression _2 .......Expression_n)

                               WITHIN GROUP

                              (ORDER BY Expression_1 , Expression_2......Expression_n)

                 In the above syntax, Expression _1 , Expression_2 are used to determine the unique row in the group.

     





    C) Using RANK function as an Analytic function 

                  We need to use the following syntax in order to use RANK function as Analytic function -

                  RANK ( ) OVER ( [ query - partition _clause ]

                                                 ORDER BY clause )

                 In above syntax -

                Order By clause - is an optional parameter and is used to order the data within each group.

                query-partition_clause - it is an optional parameter and is used to partition results set into groups.


    D) DENSE_RANK in oracle 

                   The DENSE_RANK in oracle determines the rank of a row in an ordered group of rows and returns rank as Number Rank values are not skipped in the event of ties. If values are the same then the same rank number is given.


                      Learn more about oracle here - 



    Tuesday, October 5, 2021

    How to monitor Errors in the Alert logs in Oracle Database?

                    Are you looking for details about monitoring Errors in the Alert log?  Would you also like to know about ORA  errors such as ORA-7445 , ORA-1653 ,ORA-1650 etc? If so, then you reached the right place. In this article, we will understand monitoring Errors in the Alert logs.


    A) What are Alert logs?

                    The important information about error messages and exceptions which occurs during various operation database is captured in the log file called Alert logs.

                     Each Oracle database for windows instance has one alert log.






    B) What is the location of Alert logs? 

                     We set the path for DIAGNOSTIC_DEST initialization parameter. At this path location, the alert log file is created. Normally, the alert file name is alert _SID.log


    C) Database crash errors 

                      These errors are associated with an error that can be severe enough to crash an oracle instance. To analyze the oracle instance crash issue we need to capture a trace file or a core dump file and sent it to oracle technical support.


    D) ORA - 600 Errors 

                      The ORA-600 will not crash the oracle database. However, it may produce a core dump or trace file - 

               Example of trace file -

                       Errors in file /ora/home/dba/oracle/product/rdbms/log ora_ 123.trc

                       ORA-00600= internal error code , arguments : [12700],[12345],[61],[ ],[ ]....






    E) ORA-1578

                     If a data block is read that appears corrupt in such case ORA-1578 is returned. This error message provides details of the file and block number.

                      e.g. 

                    ORA-D1578 ORACLE data block corrupted (file#xyz, block#01)


    F) ORA-1650 

                    It is an error message related to the rollback segment. The error message 'ORA-1650 cannot extend rollback segment ' is produced when the rollback segment has become full. The oracle instance will not crash but the task will be terminated.

                    e.g.  ORA-1650 is unable to extend rollback segment PQR by 64000 in tablespace ROLLBACK.

                 Based on the above critical error messages we can build the monitoring system. 



    Learn more about oracle here -







    Sunday, August 29, 2021

    What is difference between TRUNCATE and DELETE

             Are you working on databases or learning the database concepts, and want to know about the basic commands related to table like DELETE, TRUNCATE, then this is the right place. In this article, we are going to learn what is the meaning of these commands and the purpose of using these. TRUNCATE and DELETE command does the same job but slightly in a different manner. In this article, we will see what is the difference between the DELETE and TRUNCATE commands.






    TRUNCATE command

    - TRUNCATE command is DDL (Data Definition Language) command.

    - If you use truncate command, then it will delete the data in a table and not a table itself.

    - TRUNCATE locks the table but does not locks the rows, as it removes all the data from the table.

    - It removes all the rows of the table, but the structure of the table remains as it is. It does not delete the   

       table structure, columns, indexes and constrains.

    - This command does not require where clause.

    - After truncate operation rollback process is not possible because this command does not maintain any log file from which we can rollback the data.

                                      Syntax –

                                    TRUNCATE TABLE table_name;

                                     Example –

                                     TRUNCATE TABLE Customer;

    DELETE command

    -                           DELETE command is DML(Data Manipulation Language) command.

    -                          DELETE command deletes all the records from a table.

    -                          DELETE locks the rows, for deletion each row in the table is locked.

    -                         If we want to delete a specific row/record from a table then we can use the WHERE clause.

    -                       The table structure, indexes, attributes will not be deleted after the DELETE operation.

    -                        Rollback operation can be possible after DELETE operation but we have to rollback the data before the COMMIT statement. After the COMMIT statement we can not rollback the data.

     

    ·                                                                     Syntax –

                             DELETE FROM table_name;

                                         Example -

                             DELETE FROM Customer;

     

                                        Syntax (Using WHERE clause)-

                            DELETE FROM table_name WHERE condition;

                                        Example-

                                        DELETE FROM Customer WHERE id=1;

     The above delete operation will delete all the records from the ‘Customer’ table without deleting the ‘Customer’ table itself.





    Difference between TRUNCATE and DELETE command

    -                   DELETE command deletes the specific commands based on conditions defined by the WHERE clause,        but it does not free up the space.

    -                 TRUNCATE command does not require a WHERE clause. After executing the TRUNCATE statement it releases all the memory along with the removal of data.

    -                DELETE command maintains the log but the TRUNCATE command does not maintain the logs, hence the TRUNCATE command is faster than the DELETE command.

    -               TRUNCATE command uses less Transaction space.





     

     

    Sunday, July 11, 2021

    Howto fix - "ORA-00245: control file backup failed" issue

     Are you looking for an article about fixing "ORA-00245: control file backup" error? If so, then you reached the right place. In this article, we will see what is root cause of "ORA-00245: control file backup" error message at the database side and how to fix it?






    What is "ORA-00245: control file backup" error ?

    This error message occurs when Archive logs backup fails. This is Oracle database level functionality which takes backup of archive logs so that you can restore your device quickly and seamlessly in the event of data loss.


    Whar the error messages are associated with "ORA-00245: control file backup"?

    Here is the list of error messages associated with "ORA-00245: control file backup" -

    • RMAN-03009: Failure of full resync command on default channel
    • RMAN-03002: Failure of configuring command
    • RMAN-03014: implicit resync of recovery catalog failed
    • ORA-00245: control file backup failed; in Oracle RAC, target might not be on shared storage

     

    What is the root cause of "ORA-00245: control file backup" error message?

    The root cause can be the failure of   SNAPSHOT CONTROLFILE due to local file system configuration e.g. /abc CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/<oracle home>/snapcf_pqr1.f'


    However, you need to find out all possible other root causes.

     

    How to fix  "ORA-00245: control file backup" error?

    In order to fix this error message change SNAPSHOT CONTROLFILE NAME  RAMN parameter to shared storage e.g. +RECO_PROD



    Learn more about Oracle here -






     


    Wednesday, July 8, 2020

    What are the Types of Data Model?


    Are you looking for an article which explains different types of Data Model? Are you also interested in knowing what data model to choose for business? If so, then you reached the right place. In this article, we will understand the different types of data models that are used in Enterprise applications.



    What is the Data Model?

    The process in which the data is analyzed and defined based on the requirements in order to support the business processes is called Data Modeling and the output of data modeling process is a data model.

    What are the Types of Data Model?

    There are three types of data models. The list of data models is:

    1. Conceptual Data Models: It is a high-level data model and used for static business structures and concepts
    2. Logical Data Models:  This data model elaborates entity types, data attributes, and relationships between entities
    3. Physical Data Models: This type of data model provides detailed information such as data type, length, relationships, referential integrity etch.


    Selection of data model-

    Each data model has its own purpose. Depending on the purpose we need to decide which model is suitable for business use cases.

    For example, if we are planning to explain to business users how business attributes are structured in the application then the Logical Data Model will be a better choice.

    If we are planning to use integrate multiple systems and would like to explain how systems work as a whole then Conceptual Data Model will be helpful as it gives a high-level view of the underlying system.

    If the use case is to develop a database based on data model then we need detailed information about each and every component of the data model. In this case, the Physical Data Model will be the best suited. Normally, developers use the Physical Data Model to configure database objects.




    Detailed information is explained in the video below:


    Tuesday, June 16, 2020

    What are the differences between RAC and GRID?

    Are you looking for the differences in RAC and GRID system in Oracle? Are you also would like to know how RAC and GRID systems related to each other? If yes, then you reached the right place. In this article, we will see what are the differences between RAC and Grid systems.


    What is RAC system?

    The database system comprises the configuration of multiple servers which are combined together with clustering software or program and accessing the shared disk storage structures is known as Oracle Real Application Clusters (RAC) system.




    What is Grid System

    The Grid system represents the pool of database hosts or servers, along with a pool of storage, and networks in an inter-related resource platform. Grid is used for effective workload management within the grid database.

    What is the relationship between RAC and Grid systems?

    The RAC is an integral part of Grid computing and RAC helps Grid computing for high availability information sharing.

    What are differences between RAC and Grid?

    Here is a list of the differences between RAC and Grid systems

    Cluster
    Grid
    User management in Cluster is centralized
    User management in the Grid is decentralized
    In Cluster, the inter-operability is VIA and it is proprietary
    No standard is developed for inter-operability
    Ownership is singe in case of Cluster
    The multiple ownership exits in Grid
    Throughput is medium
    Throughput is high
    The guaranteed capacity
    Capacity varies for grid implementation
    Centralized resource management
    Decentralized resource management
    Can be used with commodity computer
    Can be used with commodity and high end computers
    The scheduling is centralized
    The scheduling is decentralized
    Single system image is possible
    Single system image is not possible



    Tuesday, July 2, 2019

    Important stats related SQL queries with details of determining Tablespace



    This article on Oracle database provides details about how can we monitor tablespace during sql execution. In this article we will also see how to what are the sqls are currently executing. During this process we will understand, how to get SQL ID associated with each sql statement.

    Tablespaces in the Oracle database

    The tablespace is one or more logical storage units in the Oracle database. Each database table belongs to some tablespace in the oracle database. Normally, Oracle DBA has alerts setup to monitor tablespace, so that SQL or jobs which are based on SQL statements will not fail due to tablespace issue. However, as a developer, we can also monitor tablespace using the SQL statement below -

    SELECT B.TABLESPACE_NAME, TBS_SIZE SIZE_MB, A.FREE_SPACE FREE_MB FROM (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES)/1024/1024 ,2) AS FREE_SPACE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) A, (SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024 AS TBS_SIZE FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME UNION SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024 TBS_SIZE FROM DBA_TEMP_FILES GROUP BY TABLESPACE_NAME ) B WHERE A.TABLESPACE_NAME(+)=B.TABLESPACE_NAME;

    To determine used and free tablespace, use the sql below

    select tablespace_name, SUM(bytes_used/1024/1024/1024) "Temp_Used", sum(bytes_free/1024/1024/1024) "Temp_Free"

    from v$temp_space_header where tablespace_name like '%TEMP%' group by tablespace_name;


    Determine currently running Active DB Sessions

    As Oracle developer or application developer, we can use the query below to determine the active DB sessions in the Oracle database -
    SELECT * FROM GV$SESSION WHERE STATUS = 'ACTIVE' AND TYPE <> 'BACKGROUND' AND USERNAME = 'TEST_SCHEMA_ID';

    This query will return important details e.g. USERNAME, STATUS, database MACHINE, SERVICE_NAME, SQL_ID etc.



    Determine which SQL statement is running in Database

    Before determining SQL statement, we need to determine what are the active session associated with SQL statement which can be determined with help of above section - 'Determine currently running Active DB Sessions'.

    By using the result of above query, get the SQL_ID associated with active session. Once we have SQL_ID then execute the SQL statement below to determine the query which is running in the database -

    SELECT * FROM GV$SQL WHERE SQL_ID = '61f38skxkw8hc'

    e.g. Here '61f38skxkw8hc' is value for SQL_ID


    Get Explain Plan
    In order to get explain plan execute the statement below -

    select * from TABLE(dbms_xplan.display_awr('61f38skxkw8hc'));


    Get Start and End time of SQL Query

    select sql_id, first_load_time, last_load_time, elapsed_time, cpu_time from   v$sql where  sql_text like 'with /* slow */ rws as (%';

    select sql_id, elapsed_time_delta/executions_delta avg_elapsed
    from   sys.dba_hist_sqlstat
    where  snap_id = :snap;




    Getting SQL Event details

    SELECT SNAP_ID, SQL_TIME, SESSION_ID, USER_ID, SQL_ID, SQL_OPNAME,
    TO_CHAR(A.SQL_EXEC_START, 'MM/DD/YYYY HH24:MI:SS'), SESSION_STATE,
    EVENT, TIME_WAITED, PROGRAM, A.MACHINE FROM DBA_HIST_ACTIVE_SESS_HISTORY A
    WHERE
    A.SQL_TIME BETWEEN TO_DATE('03/10/2018 09:00:00', 'MM/DD/YYYY HH24:MI:SS')
    AND TO_DATE('03/11/2018 6:00:00', 'MM/DD/YYYY HH24:MI:SS')
    AND SQL_ID = 'XXX'
    AND EVENT IS NOT NULL
    AND EVENT <> 'CELL SINGLE BLOCK PHYSICAL READ'
    ORDER BY SQL_TIME ASC

    Monday, December 10, 2018

    Issues noticed during Materialized View Creation - ORA-23413: table "CMX_ORS"."C_B_CUST" does not have a materialized view log



    Did you encounter with ORA-23413 the Oracle database error while creating a Materialized view? Are you also noticing ORA-01031: insufficient privileges error? If yes, then refer below article about explanation and solution to these database errors?

    SQL Error: ORA-23413: table "CMX_ORS"."C_B_CUST" does not have a materialized view log

    Explanation: This type of error occurs if you try to create a Materialized view on table C_BO_CUST without creating the Materialized View Log. It is mandatory to create log definition first before creating actual Materialized View.

    Error Message:
    SQL Error: ORA-23413: table "CMX_ORS"."C_B_CUST" does not have a materialized view log
    23413. 00000 -  "table \"%s\".\"%s\" does not have a materialized view log"
    *Cause:    The fast refresh cannot be performed because the master table does not contain a materialized view log.

    Solution: Use the sample below to create the Materialized View Log -
    CREATE MATERIALIZED VIEW LOG ON C_B_CUST
       PCTFREE 5
       TABLESPACE CMX_TEMP
       STORAGE (INITIAL 5K NEXT 5K);



    SQL Error: ORA-01031: insufficient privileges

    Explanation: ORA-01031 - insufficient privileges is the generic message which normally occurs if required privileges are not available for given operation. In the case of Materialized view, if user does not have "CREATE MATERIALIZED VIEW" privileges then 'ORA-01031: insufficient privileges'  error message will be reported.

    Error Message:
    SQL Error: ORA-01031: insufficient privileges 01031. 00000 -  "insufficient privileges"
    *Cause:    An attempt was made to change the current username or password 
               without the appropriate privilege. This error also occurs if
               attempting to install a database without the necessary operating
               system privileges.
               When Trusted Oracle is configure in DBMS MAC, this error may occur
               if the user was granted the necessary privilege at a higher label
               than the current login.

    Solution: In order to get required privileges we need to reach out Oracle DBA. In the current case, we need to ask DBA to provide "CREATE MATERIALIZED VIEW" privileges to given user. Assume that you are using TEST_USER to create Materialized view then ask DBA to provide privileges below -
    GRANT CREATE MATERIALIZED VIEW TO TEST_USER

    The video below provides a detailed explanation and prerequisites for Material views : 




    What are the features of Informatica Intelligent Data Management Cloud (IDMC)?

     Are you looking for the details Informatica Intelligent Data Management Cloud (IDMC)? Earlier it is called Informatica Intelligent Cloud Se...