Monday, November 12, 2018

ORA-04030: out of process memory when trying to allocate 4024 bytes (kxs-heap-b,kghsseg_1 : kokeibib

Normally users see ORA-04030 error with shortage of RAM or small PGA and kernel parameter config.

ORA-04030: out of process memory when trying to allocate 4024 bytes (kxs-heap-b,kghsseg_1 : kokeibib)
Non critical error ORA-48913 caught while writing to trace file "/u01/home/oracle/diag/rdbms/ORCL/ORCL/incident/incdir_8937/ORCL_ora_11258_i8937.trc"
Error message: ORA-48913: Writing into trace file failed, file size limit [5242880] reached
ORA-04030: out of process memory when trying to allocate 169040 bytes (pga heap,kgh stack)
ORA-04030: out of process memory when trying to allocate 4024 bytes (kxs-heap-b,kghsseg_1 : kokeibib)
Non critical error ORA-48913 caught while writing to trace file "/u01/home/oracle/diag/rdbms/ORCL/ORCL/incident/incdir_8938/ORCL_ora_11258_i8938.trc"
Error message: ORA-48913: Writing into trace file failed, file size limit [5242880] reached

Users can refer metalink Note 233869.1 titled "Diagnosing and Resolving ORA-4030 errors"

By looking the error it is clear that “out of process memory”, where oracle cannot get the required RAM. But note that you need to analyze the trace to find out the root cause

In my case I found that one of the un-optimized PL/SQL job consuming the resources and caused the issue.

Current SQL Statement for this session (sql_id=gjn38xnrxcfbj) -----
BEGIN orcl_server_bom_script.all_level(:1 , :2 , :3 , :4 , :5 , :6 , :7 , :8 , :9 , :10 , :11 , :12 , :13 , :14 , :15 , :16 , :17 , :18 , :19 , :20 , :21 ) ; END;
57% pid 67: 4089 MB used of 4092 MB allocated <= CURRENT PROC
Dump of Real-Free Memory Allocator Heap [0x7f2c640b4000]
mag=0xfefe0001 flg=0x5000003 fds=0x6 blksz=65536
blkdstbl=0x7f2c640b4010, iniblk=66560 maxblk=524288 numsegs=115
In-use num=65424 siz=4290117632, Freeable num=0 siz=0, Free num=1 siz=20512768


In our case max_map_count value is not enough and needs to investigate why the pl/sql processes requires such a large amount of memory. In this case adjusting PGA_AGGREGATE_TARGET or MEMORY_TARGET will not limit the size processes can grow and will not help.

To fix the issue increase the value of max_map_count. To find out current vale of max_map_count run below command

$cat /proc/sys/vm/max_map_count
65530

To change the value run below command
sysctl -w vm.max_map_count= <greater than 65530>

Follow below metalink note to change the value of vm.max_map_count.
Modification of "vm.max_map_count" on Oracle Linux 6 (Doc ID 2195159.1) 

Regards
Satishbabu Gunukula, Oracle ACE








Wednesday, October 17, 2018

opidcl aborting process unknown ospid (xxxx) as a result of ORA-1000


By looking the error message we can easily find out the issue

01000, 00000, "maximum open cursors exceeded"

Cause: A host language program attempted to open too many cursors. The initialization parameter OPEN_CURSORS determines the maximum number of cursors per user.

Action: Modify the program to use fewer cursors. If this error occurs often, shut down Oracle, increase the value of OPEN_CURSORS, and then restart Oracle.

First you need to work with application team and find out why the cursors are not closing.

If required you can increase the OPEN_CURSORS value by running below command.
ALTER SYSTEM SET open_cursors = 400 SCOPE=BOTH;

Note that to increase the value of OPEN_CURSORS no downtime required, but cursor storing require more memory.


Regards
Satishbabu Gunukula
htt://oracleracexpert.com

Thursday, September 6, 2018

Oracle patch failed with “CheckActiveFilesAndExecutables” failed error

We are applying patch on Oracle 12c and we got Prerequisite check “CheckActiveFilesAndExecutables” failed error. By looking the error we can see that files are active and used by some active processes.


$opatch apply
.....
[Sep 6, 2018 6:56:10 PM] [INFO] Finish fuser command /sbin/fuser /oracle/product/12.1.0.2/dbhome_1/lib/libsrvm12.so at Thu Sep 06 18:56:10 PDT 2018
[Sep 6, 2018 6:56:10 PM] [SEVERE] OUI-67073:UtilSession failed: Prerequisite check "CheckActiveFilesAndExecutables" failed.
[Sep 6, 2018 6:56:10 PM] [INFO] Finishing UtilSession at Thu Sep 06 18:56:10 PDT 2018
[Sep 6, 2018 6:56:10 PM] [INFO] Log file location: /oracle/product/12.1.0.2/dbhome_1/cfgtoollogs/opatch/opatch2018-09-06_18-55-32PM_1.log
[Sep 6, 2018 6:56:10 PM] [INFO] Stack Description: java.lang.RuntimeException: Prerequisite check "CheckActiveFilesAndExecutables" failed.
at oracle.opatch.OPatchSessionHelper.runApplyPrereqs(OPatchSessionHelper.java:6670)
at oracle.opatch.opatchutil.NApply.legacy_process(NApply.java:988)
at oracle.opatch.opatchutil.NApply.legacy_process(NApply.java:363)
at oracle.opatch.opatchutil.NApply.process(NApply.java:343)
at oracle.opatch.opatchutil.OUSession.napply(OUSession.java:1105)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at oracle.opatch.UtilSession.process(UtilSession.java:359)
at oracle.opatch.OPatchSession.process(OPatchSession.java:2670)
at oracle.opatch.OPatch.process(OPatch.java:811)
at oracle.opatch.OPatch.main(OPatch.java:861)
Caused by: java.lang.RuntimeException: Prerequisite check "CheckActiveFilesAndExecutables" failed.
... 13 more
Caused by: oracle.opatch.PrereqFailedException: Prerequisite check "CheckActiveFilesAndExecutables" failed.
... 13 more

In the log file I see that below lib is active and used by some processes

[Sep 6, 2018 6:55:49 PM] [INFO] Following executables are active :
/oracle/product/12.1.0.2/dbhome_1/lib/libclntsh.so.12.1
[Sep 6, 2018 6:55:49 PM] [INFO] Prerequisite check "CheckActiveFilesAndExecutables" failed.
The details are:
Following executables are active :
/oracle/product/12.1.0.2/dbhome_1/lib/libclntsh.so.12.1
[Sep 6, 2018 6:55:49 PM] [INFO] Start fuser command /sbin/fuser /oracle/product/12.1.0.2/dbhome_1/bin/adrci at Thu Sep 06 18:55:49 PDT 2018

To find which process using below lib run below command and you will get the process id

$ /sbin/fuser /oracle/product/12.1.0.2/dbhome_1/lib/libclntsh.so.12.1
/oracle/product/12.1.0.2/dbhome_1/lib/libclntsh.so.12.1: 8631m

Find out the process that is active using below command

$ps –ef |grep 8631     
oracle     8631      1  0 Aug23 ?        00:02:22 /oracle/product/12.1.0.2/dbhome_1/bin/tnslsnr LISTENER_ORCL-inherit

Either you stop the processes or kill the process. Once the processes is stopped you can proceed with opatch again and it should be successful.

Note that after patch is successful check for “SEVERE” errors if any in the log file.

Thanks
Satishbabu Gunukula
http://www.oracleracexpert.com


Wednesday, May 30, 2018

ORA-03206: maximum file size of (6553600) blocks in AUTOEXTEND clause is out of range

I received below error while extending the datafile. You might see below error when creating tables space too.

SQL> alter database datafile '/oradata1/ORCL/orcl101.dbf' autoextend ON maxsize 50g;
alter database datafile '/oradata1/ORCL/orcl101.dbf' autoextend ON maxsize 50g;
*
ERROR at line 1:
ORA-03206: maximum file size of (6553600) blocks in AUTOEXTEND clause is out of range


The reason is maximum file size for an autoenxtendable file has exceeded the maximum number of blocks allowed. Note that Oracle allows only up to 32GB datafile with 8k blocks. If you want to create a tablespace or datafile more than 32GB then you must use BIGFILE keyword.

To calicuate max datafile size:
max datafile size = db_block_size * maximum number of blocks

Solution: create a file less than 32 GB size or if you need to create datafile more than 32GB then use BIGFILE keyword.

SQL> alter database datafile '/oradata1/ORCL/orcl101.dbf' autoextend ON maxsize 32767M ;
Tablespace altered.

Hope this helps,

Regards
Satishbabu Gunukula, Oracle ACE
http://oracleracexpert.com

ERROR 1827 (HY000): The password hash doesn't have the expected format

When creating a user with Grant option we have received below error message

ERROR 1827 (HY000): The password hash doesn't have the expected format
That means you have to use hash password. Here is the simple solution that you can use

Enter the password that you want to set and you will get HASH password.
mysql> select password(‘enter password you want');
+-------------------------------------------+
| password(‘enter password you want') |
+-------------------------------------------+
| *B535BN128KK03E74BE2AC0EE23D07ABX6AD8165E |
+-------------------------------------------+
1 row in set (0.00 sec)

Use the has password while creating the user

mysql> GRANT ALL ON *.* TO 'admin'@'%' IDENTIFIED BY PASSWORD '*B535BN128KK03E74BE2AC0EE23D07ABX6AD8165E ' WITH GRANT OPTION;

Query OK, 0 rows affected (0.01 sec)

Thanks
Satishbabu Gunukula, Oracle ACE