Skip to main content

Posts

Showing posts from May, 2012

Solution to : "WARNING: Detected too many memory locking problems."

Solution to : "WARNING: Detected too many memory locking problems." My alert log file was generating too many warnings that i was not aware of. After a thorough search in Google (special thanks), i found that one file permission was responsible for generating the warning. My alert log file entries: Current log# 3 seq# 4645 mem# 0: <DATAFILE_LOCATION>/redo03.log Mon May 28 11:53:39 2012 WARNING: Detected too many memory locking problems. WARNING: Performance degradation may occur. The real cause of those warnings is the file oradism located on $ORACLE_HOME/bin folder was lacking permission. As per meta link note 374367.1, issuing the following command should stop the warnings to get generated: 1- cd $ORACLE_HOME/bin 2- chmod 4550 oradism 3- chmod g+s oradism 4- chown root:dba oradism  5- Bounce the database These commands ware generating errors on my case so i just made my oracle os user, the owener of this file. and bounced the database. A

Recreating UNDO Tablespace

If your undo tablespace has grown to a large size and you need to recreate it , you can do so with the following easy steps: STEP 1: Query V$PARAMETER to see the default UNDO tablespace currently active. SELECT name,value FROM v$parameter WHERE name IN ('undo_management','undo_tablespace'); NAME VALUE _______________________ undo_management AUTO undo_tablespace UNDOTBS2 STEP 2: Create a new tablespace that will be the new default tablespace. CREATE UNDO TABLESPACE undotbs02 DATAFILE '/oradata/oracle/oradata/DB1/undotbs02.dbf' SIZE 500M REUSE AUTOEXTEND ON NEXT 4096K MAXSIZE 10240M; STEP 3: Set this newly created UNDO tablespace to default UNDO tablespace. ALTER SYSTEM SET undo_tablespace = 'UNDOTBS02'; STEP 4: Set the old tablespace to OFFLINE to be eligible to drop. ALTER TABLESPACE undotbs2 OFFLINE; STEP 5: Drop the old UNDO tablespace. DROP TABLESPACE undotbs2 INCLUDING CONTENTS AND DATAFILES; ------------------------

Shared Pool Tuning: Cursor Tuning (Tuning Open_Cursors, Session_Cached_Cursors, Cursor_Space_For_Time)

Shared Pool Tuning: Cursor Tuning The three most important parameter for shared pool tuning are OPEN_CURSORS , SESSION_CACHED_CURSORS and CURSOR_SPACE_FOR_TIME. But most of the time we see that these two parameters SESSION_CACHED_CURSORS and CURSOR_SPACE_FOR_TIME are ignored or unused . OPEN CURSORS Open cursors take up space in the shared pool, in the library cache. OPEN_CURSORS sets the maximum number of cursors each session can have open, per session. For example, if OPEN_CURSORS is set to 1000, then each session can have up to 1000 cursors open at one time. V$open_cursor shows cached cursors, not currently open cursors, by session. If you’re wondering how many cursors a session has open, don’t look in v$open_cursor. It shows the cursors in the session cursor cache for each session, not cursors that are actually open.  To monitor open cursors, query v$sesstat where name= ’opened cursors current’ . This will give the number of currently opened cursors, by session: