ORA-00845: MEMORY_TARGET not supported on this system and ORA-00093: _shared_pool_reserved_min_alloc must be between 4000 and 0

ORA-00845: MEMORY_TARGET not supported on this system and ORA-00093: _shared_pool_reserved_min_alloc must be between 4000 and 0


Database not coming up due to below errors (ORA-00845: MEMORY_TARGET not supported on this system and ORA-00093: _shared_pool_reserved_min_alloc must be between 4000 and 0)

Scene 1)
##########################################
SQL> startup nomount;
ORA-00845: MEMORY_TARGET not supported on this system
SQL> shut abort
ORACLE instance shut down.
SQL> exitDisconnected


Scene 2)
#########################################
SQL> startup nomount;
ORA-01078: failure in processing system parameters
ORA-00093: _shared_pool_reserved_min_alloc must be between 4000 and 0
SQL> shut abort;
ORACLE instance shut down.
SQL> exit
Disconnected


Scene 3)
#########################################
SQL> startup nomount
ORA-01078: failure in processing system parameters
ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least 3072M
SQL>
SQL> exit
Disconnected
#########################################


These are all due to memory Memory related parameter which is not specified as it should be.


Solution:
Scene 1) This error always come if /dev/shm FS size is less then memory targetparameter.
To overcome from this situation either increase the /dev/shm to greater than memory target or keep less value of memory target from /dev/shm.
Other way is to use SGA_MAX_SIZE instead of MEMORY_TARGET.


Scene 2) This error only came if SGA_MAX_SIZE is set but aggregate value of individual memory parameter (db_cache_size, shared_pool_size, java_pool_size, stream_pool_size etc..) value is more than SGA_MAX_SIZE
To overcome from this situation either increase the SGA_MAX_SIZE or decrease aggregate value of individual memory parameter.


Scene 3) in case by mistaken SGA_MAX_SIZE and MEMORY_TARGET both has been define, and SGA_MAX_SIZE is greater than MEMORY_TARGET.
To overcome from this situation remove or keep size 0 either SGA_MAX_SIZE or MEMORY_TARGET parameter value


[oracle@abcdbhost01 dbs]$ sqlplus '"as sysdba"


SQL*Plus: Release 11.2.0.3.0 Production on Wed Sep 10 20:48:05 2014


Copyright (c) 1982, 2011, Oracle. All rights reserved.


Connected to an idle instance.


SQL> startup nomount
ORACLE instance started.


Total System Global Area 3340451840 bytes
Fixed Size 2232960 bytes
Variable Size 771755392 bytes
Database Buffers 2550136832 bytes
Redo Buffers 16326656 bytes
SQL>

ERROR :: ORA-00119: invalid specification for system parameter LOCAL_LISTENER

ERROR :: ORA-00119: invalid specification for system parameter LOCAL_LISTENER


Issue: ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'LISTENER_DATABASE NAME'


Couse: 1) You have not made the entry of local listener in TNSNAMES.ora file and parameter is added in spfile or pfile . In order to avoid the above error we have two options as per my understanding (if you have plz comment and share your solution.)


Technique 1:


Step 1: Make an entry in the tnsnames.ora as mentioned below
LISTENER_<NAME/DATABASE NAME> =
(ADDRESS = (PROTOCOL = TCP)(HOST = my.host.com)(PORT = 1521))


Where LISTENER_<NAME/DATABASE NAME> is the parameter value for LOCAL_LISTENER which is mentioned in pfile/spfile
PORT is the listener port you are using.
Also there is no need to modify the pfile/spfile.


Technique 2 :
Step 2:


a) If you are using a spfile for starting up your database, create a pfile from it and remove the LOCAL_LISTNER parameter and Create spfile from the pfile .
b) Strat the database .
sqlplus / as sysdba
startup ;

c) start the listener lsnrctl start

ERROR :: ORA-04030: out of process memory when trying to allocate 16328 bytes

ERROR :: ORA-04030: out of process memory when trying to allocate 16328 bytes

Issue: ORA-04030: out of process memory when trying to allocate 16328 bytes (koh-kghu call ,pl/sql vc2)

Following ORA-04030 error is encountered every time when the PGA allocation reaches 15GB:


The incident trace shows 15G used by pl/sql:
=======================================
TOP 10 MEMORY USES FOR THIS PROCESS
---------------------------------------
100% 15 GB, 1008569 chunks: "pl/sql vc2 " PL/SQL
koh-kghu call ds=fffffc7ffc6f51f8 dsprt=c715710
0% 15 MB, 15763 chunks: "free memory "
pga heap ds=c715710 dsprt=0


This is due to bug 14119856 when real free allocator is used even though pga_aggregate_target is set more than 16GB.
Use below query to check if real free allocator is used:

SQL> col name format a30
col cur_val format a20
select i.ksppinm name , v.ksppstvl cur_val, v.ksppstdf default_val,v.ksppstvf
from x$ksppi i, x$ksppcv v where i.indx = v.indx and i.ksppinm in
('_realfree_heap_pagesize_hint', '_use_realfree_heap');SQL> SQL> 2 3

NAME CUR_VAL DEFAULT_V KSPPSTVF
------------------------------ -------------------- --------- ----------
_realfree_heap_pagesize_hint 65536 TRUE 0
_use_realfree_heap TRUE TRUE 0


Technique 1:


Step 1:
• Restart the database and server in order to fix the issue
Or
Change the upper limit at either the OS or at the database level:


• Change the page count at the OS level:

by root user,
$ more /proc/sys/vm/max_map_count
$ sysctl -w vm.max_map_count=200000 (for example)

OR at database level,
• Adjust the realfree heap pagesize within the database by setting the following parameters in the init/spfile and restart the database.

_use_realfree_heap=TRUE
_realfree_heap_pagesize_hint = 262144
- OR -
Use Workaround:

Set "_use_realfree_heap=false" and restart database instance.

Or

Apply patch <="" a="">14119856> if available for your platform and Oracle version or request for a one-off patch.

Reference MI note :: Doc ID 1506315.1 and Thanks for the giving time and reading the post .

Script to list missing and INVALID Objects in the database

Script to list missing and INVALID Objects in the database

REM Script to list missing and INVALID Objects in the database
REM
REM MISSING.SQL
REM
REM This script recompiles all objects that have become invalidated
REM
REM It should be run as SYS or SYSTEM
REM

set pagesize 0
set linesize 120
set feedback off
set trimspool on
set termout on

spool missing.txt

select A.Owner Oown,
A.Object_Name Oname,
A.Object_Type Otype,
'Miss Pkg Body' Prob
from DBA_OBJECTS A
where A.Object_Type = 'PACKAGE'
and A.Owner not in ('SYS','SYSTEM')
and not exists
(select 'x'
from DBA_OBJECTS B
where B.Object_Name = A.Object_Name
and B.Owner = A.Owner
and B.Object_Type = 'PACKAGE BODY')
union
select Owner Oown,
Object_Name Oname,
Object_Type Otype,
'Invalid Obj' Prob
from DBA_OBJECTS
where Owner not in ('SYS','SYSTEM')
and Status != 'VALID'
order by 1,4,3,2
/
spool off

Linux Files

Linux Files

Path
Description

/etc/passwd - User Settings

/etc/group - Group settings for users.

/etc/hosts -  Host name lookup information

/etc/sysctl.conf  - Kernel parameters for Linux.

/var/log/messages  - Check System and error logs and messages

/etc/oratab  - Oracle Registered instance (DBCA)

/etc/fstab  - Files to check for File System entries

/home/oracle/.bash_profile  - Oracle user profile setting file in Linux.

/proc/meminfo  - To determine To determine the RAM size

/etc/redhat- release - get the OS release information

/etc/security/limits.conf  - Specify process and open files related limits

/etc/selinux/config  - Enable or disable security feature.