Tech Lover

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 b...

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_...

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 ...

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: Ne...

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...

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 re...

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...

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 f...

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 dr...

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 thi...