Category Archives: Debugging

ORA-00845: MEMORY_TARGET not supported on this system

I have a RH5.0 Linux server that has oracle11g server running on it. I have just created a new database and when I try to start it I’m getting the following error message:-

ORA-00845: MEMORY_TARGET not supported on this system

I have increased the size of /dev/shm to 50GB and I’m still getting this error message, what can I do to resolve this issue?

In /etc/fstab i have made the following change:-

tmpfs                   /dev/shm                tmpfs   defaults,size=50G        0 0

and then did the following:-

mount -o remount /dev/shm

Or do I actually need to physically reboot the server?

karluk (MIS)
12 May 09 11:10
What is the output of a “df -k” command?  That should tell you whether /dev/shm is mounted and its current size.
nyck (TechnicalUser)
13 May 09 5:15
Hello,

The output is below:-

tmpfs                  50G   26G   25G  52% /dev/shm

karluk (MIS)
13 May 09 14:02
My only 11g databases are on SLES 10, so I don’t have a directly comparable setup to yours.  For me automatic memory management worked without a hitch.  For comparison, this is what I see on the O/S.

CODE

Filesystem           1K-blocks      Used Available Use% Mounted on
shmfs                  8388608    300560   8088048   4% /dev/shm

How much RAM do you have on your server?  50G seems like an awfully lot to allocate to shared memory, but it certainly looks as if your system is allowing it.

karluk (MIS)
13 May 09 14:42
The following is a link to an interesting article on Oracle 11g automatic memory management.  The author claims that /dev/shm has to be configured to be at least as large as MEMORY_MAX_TARGET.  If true and you set this parameter high enough, I suppose it’s possible that you simply don’t have a large enough /dev/shm.  Also, there is apparently an alert log entry when you get the ORA-00845 error.  What message is in your alert log?  Did it say how much shared memory Oracle thinks is available?

http://blog.tanelpoder.com/2007/08/21/oracle-11g-internals-part-1-automatic-memory-management/

nyck (TechnicalUser)
14 May 09 4:02
I finally got round to solving this issue. When creating the databases using dbca I did not notice that the memory being allocated to the db’s was 40% of the physical RAM ( this is 64GB ). I created four of these databases like this before I noticed what was going on. To resolve this I perfromed the following from within each database:-

Alter system set memory_target= 2000M scope=spfile;

then bounced the database and all was fixedsmile

Oradebug1

GENERAL
Background Processes List SELECT b.name, p.pid
FROM gv$bgprocess b, gv$process p
WHERE b.paddr = p.addr
ORDER BY 1;
Dispatcher Processes List SELECT d.name, p.pid
FROM gv$dispatcher d, gv$process p
WHERE d.paddr = p.addr;
Job Queue Process List SELECT s.paddr, s.sid, j.job
FROM gv$session s, dba_jobs_running j
WHERE s.sid = j.sid;

SELECT pid
FROM gv$process
WHERE addr = ’17’;

Parallel Execution Slave  Processes List SELECT pid, server_name, status
FROM gv$px_process;
Shared Server Processes List SELECT s.name, p.pid
FROM gv$shared_server s, gv$process p
WHERE s.paddr = p.addr;
SGA Variables List SELECT ksmfsnam
FROM x$ksmfsv
WHERE ksmfsnam LIKE ‘%_’ ESCAPE ”;
Switches
Switch Format Description
-G <Inst-List | def | all> Parallel oradebug command prefix
-R <Inst-List | def | all> Parallel oradebug prefix return output
CALL
Invoke function with arguments oradebug call <func> [arg1] … [argn]
SQL> oradebug call ?
CLOSE_TRACE
Close trace file oradebug close_trace
SQL> oradebug close_trace
CORE
Dump core without crashing process oradebug core
SQL> oradebug core
CURRENT_SQL

Get current SQL
oradebug current_sql
SYS UWCLASS
conn / as sysdba conn uwclass/uwclass
GRANT execute ON dbms_support
TO uwclass;
SELECT pid
FROM gv$process
WHERE addr = (
SELECT paddr
FROM gv$session
WHERE sid =
sys.dbms_support.mysid);
oradebug setorapid 26
SELECT t.tablespace_name
FROM all_tables t, all_indexes i
WHERE t.tablespace_name = i.tablespace_name;
oradebug core
DELETE
Delete a watchpoint oradebug delete <local|global|target> watchpoint <id>
SQL> oradebug delete ?
DIRECT_ACCESS
Fixed table access oradebug direct_access <set/enable/disable command | select query>
TBD
DMPCOWSGA
Dump & map SGA as COW oradebug dmpcowsga <SGA dump dir>
SQL> oradebug dmpcowsga “c: emp”
DUMP
Invoke named dump oradebug dump <dump_name> <lvl> [addr]
— dump_name values can be obtained from oradebug dumplist

SQL> oradebug setmypid
SQL> oradebug dump library_cache 4

Invoke ASH dump SQL> oradebug setmypid
SQL> oradebug dump ashdump 10
DUMPLIST

Print a list of available dumps
oradebug dumplist
SQL> oradebug dumplist

Available Dumps

ADJUST_SCN HANGDIAG_HEADER MMAN_ALLOC_MEMORY
ALRT_TEST HEAPDUMP MMAN_CREATE_DEF_REQUEST
ARCHIVE_ERROR HEAPDUMP_ADDR MMAN_CREATE_IMM_REQUEST
ASHDUMP HM_FW_TRACE MMAN_IMM_REQUEST
ATSK_TEST INSTANTIATIONSTATE MMON_TEST
AWR_FLUSH_TABLE_OFF IOERREMUL MODIFIED_PARAMETERS
AWR_FLUSH_TABLE_ON IOERREMULRNG NEXT_SCN_WRAP
AWR_TEST IR_FW_TRACE OBJECT_CACHE
BC_SANITY_CHECK JAVAINFO OCR
BG_MESSAGES KCBO_OBJ_CHECK_DUMP OLAP_DUMP
BLK0_FMTCHG KDLIDMP OPEN_FILES
BUFFER KRA_OPTIONS PGA_DETAIL_CANCEL
BUFFERS KRA_TRACE PGA_DETAIL_DUMP
CALLSTACK KRB_BSET_DAYS PGA_DETAIL_GET
CHECK_ROREUSE_SANITY KRB_CORRUPT_INTERVAL PIN_BLOCKS
CONTEXTAREA KRB_CORRUPT_REPEAT PIN_RANDOM_BLOCKS
CONTROLF KRB_CORRUPT_SIZE POKE_ADDRESS
CROSSIC KRB_CORRUPT_SPBAD_INTERVAL POKE_LENGTH
CRS KRB_CORRUPT_SPBAD_REPEAT POKE_VALUE
CSS KRB_CORRUPT_SPBITMAP_INTER POKE_VALUE0
CURSOR_STATS KRB_CORRUPT_SPBITMAP_REPEA POOL_SIMULATOR
CURSORDUMP KRB_CORRUPT_SPHEADER_INTER PROCESSSTATE
CURSORTRACE KRB_CORRUPT_SPHEADER_REPEA REALFREEDUMP
DATA_ERR_OFF KRB_FAIL_INPUT_FILENO RECORD_CALLSTACK
DATA_ERR_ON KRB_OPTIONS RECOVERY
DBSCHEDULER KRB_OVERWRITE_ACTION REDOHDR
DROP_SEGMENTS KRB_PIECE_FAIL REDOLOGS
DUMP_ADV_SNAPSHOTS KRB_SET_TIME_SWITCH REFRESH_OS_STATS
DUMP_ALL_COMP_GRANULES KRB_SIMULATE_NODE_AFFINITY ROW_CACHE
DUMP_ALL_COMP_GRANULE_ADDR KRB_TRACE RULESETDUMP
DUMP_ALL_OBJSTATS KRB_UNUSED_OPTION RULESETDUMP_ADDR
DUMP_ALL_REQS KRBMRSR_LIMIT SAVEPOINTS
DUMP_PINNED_BUFFER_HISTORY KRBMROR_LIMIT SELFTESTASM
DUMP_TEMP KRC_TRACE SET_NBLOCKS
DUMP_TRANSFER_OPS KSDTRADV_TEST SET_TSN_P1
DUMPGLOBALDATA KSFQP_LIMIT SHARED_SERVER_STATE
ENQUEUES KSKDUMPTRACE SIMULATE_EOV
ERRORSTACK KTPR_DEBUG SYSTEMSTATE 
EVENT_TSM_TEST KSTDUMPALLPROCS SYSTEMSTATE_GLOBAL
EXCEPTION_DUMP KSTDUMPALLPROCS_CLUSTER TEST_DB_ROBUSTNESS
FAILOVER KSTDUMPCURPROC TEST_GET_CALLER
FBHDR KUPPLATCHTEST TEST_SPACEBG
FBINC KXFPCLEARSTATS TEST_STACK_DUMP
FBTAIL KXFPDUMPTRACE TR_CRASH_AFTER_WRITE 
FILE_HDRS KXFPBLATCHTEST TR_CORRUPT_ONE_SIDE
FLASHBACK_GEN KXFXCURSORSTATE TR_READ_ONE_SIDE 
FLUSH_CACHE KXFXSLAVESTATE TR_SET_ALL_BLOCKS
FLUSH_JAVA_POOL LATCHES TR_SET_BLOCK 
FULL_DUMPS LDAP_KERNEL_DUMP TR_SET_SIDE
GC_ELEMENTS LDAP_USER_DUMP TRACE_BUFFER_OFF 
GES_STATE LIBRARY_CACHE TRACE_BUFFER_ON
GLOBAL_AREA LOCKS TREEDUMP 
HANGANALYZE LOGERROR TR_RESET_NORMAL
HANGANALYZE_PROC LOGHIST UPDATE_BLOCK0_FORMAT
HANGANALYZE_GLOBAL LONGF_CREATE WORKAREATAB_DUMP
DUMPSGA
Dump fixed SGA oradebug dumpsga

oradebug dumpsga <bytes>

SQL> oradebug dumpsga
c:oracleproductadminorabaseudumporabase_ora_2120.trc
DUMPTYPE
Print/dump an address with type info oradebug dumptype <address> <type> <count>
TBD
DUMPVAR
Print/dump a fixed PGA/SGA/UGA variable oradebug dumpvar <pga|sga|uga> <name> [level]
SQL> oradebug setmypid
SQL> oradebug dumpvar SGA kcbnbh
EVENT
Set trace event in process oradebug EVENT <event>
TRACE NAME CONTEXT FOREVER, LEVEL <level>
SQL> oradebug setmypid

SQL> oradebug EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12

SQL> oradebug unlimit

FFBEGIN
Flash Freeze the Instance oradebug ffbegin
SQL> oradebug ffbegin
FFDEREGISTER
FF deregister instance from cluster oradebug ffderegistger
SQL> oradebug ffderegister
FFRESUMEINST
Resume the flash frozen instance oradebug ffresumeinst
SQL> oradebug ffresumeinst
FFSTATUS
Flash freeze status of instance oradebug ffstatus
SQL> oradebug ffstatus
FFTERMINST
Call exit and terminate instance oradebug ffterminst
SQL> oradebug ffterminst
FLUSH
Flush the current contents of the trace buffer to the trace file use oradebug flush
SQL> oradebug flush
HANGANALYZE

Analyze system hang for stand-alone
oradebug hanganalzye [level] [syslevel]
SQL> oradebug setmypid;
SQL> oradebug hanganalyze;
SQL> oradebug flush;
Analyze system hang for RAC SQL> oradebug setmypid;
SQL> oradebug -g def hanganalyze 1
SQL> oradebug flush;
HELP
Describe one or all commands  oradebug help

oradebug help <command>

SQL> oradebug help
SQL> oradebug help flush
LKDEBUG
Invoke global enqueue service debugger oradebug lkdebug
SQL> oradebug lkdebug
MAPCOWSGA
Map SGA as COW oradebug mapcowsga <SGA dump dir>
SQL> oradebug mapcowsga “c: emp”
NSDBX
Invoke CGS name-service debugger oradebug nsdbx
SQL> oradebug nsdbx
PEEK
Print/Dump memory oradebug peek <addr> <len> [level]
SQL> oradebug peek 0x20005F0C 12
POKE
Modify memory. Never perform this function on a production database! oradebug poke <addr> <len> <value>
SQL> oradebug poke 0x20005F0C 4 0x46495845
PROCSTAT
Dump process statistics oradebug procstat
SQL> oradebug setmypid

SQL> oradebug procstat

RESUME
Resume execution oradebug resume
SQL> oradebug resume
SESSION_EVENT
Set trace event in session oradebug session_event <text>
SQL> oradebug session_event 10053 TRACE NAME CONTEXT FOREVER, LEVEL 1
SETINST
Set instance list oradebug setinst <instance# .. | all>
SQL> oradebug setinst “1”
SETMYPID
Sets the oradebug PID to the current process oradebug setmypid
SQL> oradebug setmypid
SETORAPID

Set PID of Oracle process to debug
oradebug setorapid <orapid> [‘force’]
SELECT pid
FROM gv$process
WHERE addr = (
SELECT paddr
FROM gv$session
WHERE sid = DBMS_SUPPORT.MYSID);

or

SELECT pid
FROM gv$process
WHERE addr = (
SELECT paddr
FROM gv$session
WHERE sid = (SELECT sid FROM gv$mystat WHERE ROWNUM = 1));

/*
or one of the processes from the background, dispatcher, job queue, parallel, or shared server process lists
*/

SQL> oradebug setorapid 19

SETOSPID
Set OS pid of process to debug

The operating system process ID is the PID on Unix systems and the thread number for Windows systems

oradebug setospid <ospid>
Do not use as it often fails. Use setorapid instead.
SETORAPNAME
Set Oracle process name to debug oradebug setorapname <orapname>
TBD
SETVAR
Modify a fixed PGA/SGA/UGA variable oradebug setvar <pga|sga|uga> <name> <value>
SQL> oradebug setvar SGA kcfdfk 200
SGATOFILE
Dump SGA to file oradebug sgatofile <SGA dump dir>
SQL> oradebug ffbegin
SQL> oradebug sgatofile “c: emp”
SQL> oradebug ffresumeinst
SHORT_STACK
Get abridged OS stack oradebug short_stack
SQL> oradebug short_stack
SHOW
Show watchpoints oradebug show <local|global|target> watchpoint <id>
SQL> oradebug show?
SKDSTTPCS
Helps translate PCs to names oradebug skdsttpcs <ifname> <ofname>
SQL> oradebug skdsttpcs?
SUSPEND
Suspends the current process oradebug suspend
SQL> oradebug suspend
TRACEFILE_NAME
Get trace file name

Will not return a value on Windows systems

oradebug tracefile_name
SQL> oradebug tracefile_name
UNLIMIT
Unlimit the size of the trace file oradebug unlimit
SQL> oradebug unlimit
WAKEUP
Wake up Oracle process oradebug wakeup <orapid>
SELECT pid
FROM gv$process
WHERE addr = (
SELECT paddr
FROM gv$bgprocess
WHERE name = ‘SMON’);

SQL> oradebug wakeup 7

WATCH
Watch a region of memory oradebug watch <address> <len> <self|exist|all|target>
SQL> oradebug watch?
Demo

Taking a heap dump during a large (sorting) query
— This example requires two sessions, session 1 logged on SYS AS SYSDBA and session 2 which
— executes the query. In session 2 identify the PID using
 

SELECT pid
FROM gv$process
WHERE addr IN (
SELECT paddr
FROM gv$session
WHERE sid = dbms_support.mysid);

In this example the PID was 12

In session 1 set the Oracle PID using

ORADEBUG SETORAPID 12

In session 2 start the query

SELECT … FROM t1 ORDER BY ….

In session 1 suspend session 2

ORADEBUG SUSPEND

The query in session 2 will be suspended

In session 1 run the heap dump

ORADEBUG DUMP HEAPDUMP 1

The heapdump will show the memory structures allocated for the sort. At this point further dumps e.g. subheap dumps can be taken.

In session 1 resume session 2

ORADEBUG RESUME

The query in session 2 will resume execution

Related Topics
Dumping Oracle
Trace & TKPROF

 

PACKAGE DBMS_SESSION

PACKAGE DBMS_SESSION

The package DBMS_SESSION is installed by running the script

    $ORACLE_HOME/rdbms/admin/dbmsutil.sql

This script is called by

    $ORACLE_HOME/rdbms/admin/catproc.sql

By default all users with CONNECT privileges can execute the procedures and functions in this package.

Procedures and functions include

 

PROCEDURE SET_SQL_TRACE

PACKAGE DBMS_SYSTEM

PACKAGE DBMS_SYSTEM

The package DBMS_SYSTEM is installed by running the script

    $ORACLE_HOME/rdbms/admin/dbmsutil.sql

This script is called by

    $ORACLE_HOME/rdbms/admin/catproc.sql

By default only the SYS user can execute procedures and functions in this package. To allow other users to execute this package, run the following commands as SYS

    GRANT EXECUTE ON dbms_system TO PUBLIC;
    CREATE PUBLIC SYNONYM dbms_system FOR dbms_system;

In Oracle 9.2 procedures and functions include

 

PROCEDURE KSDDDT
PROCEDURE KSDFLS
PROCEDURE KSDIND
PROCEDURE KSDWRT
PROCEDURE SET_SQL_TRACE_IN_SESSION
PROCEDURE SET_BOOL_PARAM_IN_SESSION
PROCEDURE SET_INT_PARAM_IN_SESSION
PROCEDURE SET_EV
PROCEDURE READ_EV

PACKAGE DBMS_SUPPORT

PACKAGE DBMS_SUPPORT

The DBMS_SUPPORT package is available in Oracle 7.2 and above. It is not installed by default.

The package is supplied with Oracle 9.0.1 and 9.2.0. It was omitted from Oracle 8.1.7. If it is missing, it can be obtained from Oracle support. The following files are required

    dbmssupp.sql
    prvtsupp.plb

The package is installed by running the script

    $ORACLE_HOME/rdbms/admin/dbmssupp.sql

By default only the SYS user can execute procedures and functions in this package. To allow other users to execute this package, run the following commands as SYS

    GRANT EXECUTE ON dbms_support TO PUBLIC;
    CREATE PUBLIC SYNONYM dbms_support FOR dbms_support;

See Metalink Note 62294.1 for further information

In Oracle 9.2 procedures and functions include

 

FUNCTION MYSID
PROCEDURE START_TRACE
PROCEDURE STOP_TRACE
PROCEDURE START_TRACE_IN_SESSION
PROCEDURE STOP_TRACE_IN_SESSION

PACKAGE DBMS_MONITOR

PACKAGE DBMS_MONITOR

The DBMS_MONITOR package is installed by default in Oracle 10.1 and above.

Subroutines include:

Session Level Subroutines
PROCEDURE SESSION_TRACE_ENABLE
PROCEDURE SESSION_TRACE_DISABLE
Database Level Subroutines
PROCEDURE DATABASE_TRACE_ENABLE
PROCEDURE DATABASE_TRACE_DISABLE
Client ID Subroutines
PROCEDURE CLIENT_ID_TRACE_ENABLE
PROCEDURE CLIENT_ID_TRACE_DISABLE
PROCEDURE CLIENT_ID_STAT_ENABLE
PROCEDURE CLIENT_ID_STAT_DISABLE
Service / Module / Action subroutines
PROCEDURE SERV_MOD_ACT_TRACE_ENABLE
PROCEDURE SERV_MOD_ACT_TRACE_DISABLE
PROCEDURE SERV_MOD_ACT_STAT_ENABLE
PROCEDURE SERV_MOD_ACT_STAT_DISABLE

The DATABASE_TRACE_ENABLE and DATABASE_TRACE_DISABLE procedures were introduced in Oracle 10.2.

Circular Trace Buffers

Trace

Circular Trace Buffers

The following events have been tested successfully in 7.3.4 and were still working in Oracle 9.2.

In Oracle 8.1.6 and above, the default value for the MAX_DUMP_FILE_SIZE is UNLIMITED. In Oracle 8.1.5 and below the default value is 512 database blocks. In either case it can be altered dynamically; however high volume trace may still rapidly fill up a disk.

If only the last few lines of the trace file are required, then a circular trace buffer can be used

Enabling the Trace Buffer

To enable a circular trace buffer use

    ALTER SESSION SET EVENTS
    'immediate trace name trace_buffer_on level level';

where level is the size of the buffer in bytes e.g.

    ALTER SESSION SET EVENTS
    'immediate trace name trace_buffer_on level 65536';

creates a 65536 byte circular trace buffer

Note that the following ORADEBUG command is equivalent

    ORADEBUG DUMP TRACE_BUFFER_ON level

Dumping the Trace Buffer

The buffer is dumped to the trace file when the ksedmp function is called which is the case when an ORA-600 is signalled

The buffer can be dumped on demand using the command

    ALTER SESSION SET EVENTS
    'immediate trace name trace_buffer_off';

or alternatively

    ORADEBUG DUMP TRACE_BUFFER_OFF 0

Note that a message is included in the trace file if trace has been discared from the trace buffer. For example

    Trace output buffer of 65536 bytes wrapped 8 times

Also the following message is written to the end of the trace file when it becomes full

    *** Trace file full ***