Oracle DBA Place

It seemd that the number of hidden parameters is increasing between versions. a small check reveals that: version 8.1.7.4 contains 300 paramters. version 9.2.0.8 contains 613 paramters. version 10.2.0.3 contains 1179 paramters. Here is the query I used to locate these parameters: SELECT a.ksppinm "Parameter", a.ksppdesc "Description", 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 '/';

Using Linux RH4 64Bit with Oracle 10.2.0.3 with OMF on ASM when we tried to "create spfile from pfile;" the spfile was located in a wrong location: +DATA1/ DB_UNKNOWN /PARAMETERFILE/SPFILE.185.634235343 The link was pointing to the right location. After some struggles we found the solution in Note: 393932.1 We even managed to fix it by the note instructions: 1. Create a pfile    SQL> create pfile='/tmp/pfile' from spfile'+DATA/DB_UNKNOWN/PARAMETERFILE/SPFILE.272.622486851'; 2. Shutdown and startup mount using the restored spfile    SQL> startup mount force pfile=/tmp/pfile 3. Restore the spfile again to the correct location as DB_NAME is correctly set this time.    RMAN> restore spfile; 4. Startup again using the spfile    SQL> startup force UNTIL the next time we had to use "create spfile ...;" the nightmare got back again. L Eventually we decided to give up this solution and we are using spfile in a regular file system.

==================================== Trace Specific ORA-XXXXX - Current Session ==================================== Activate: SQL> alter session set events ' 3113 trace name errorstack level 3';   Stop: SQL> alter session set events ' 3113 trace name context off'   ==================================== Trace Specific ORA-XXXXX – for all system ==================================== Activate: SQL> alter system set events ' 3113 trace name errorstack level 3';   Stop: SQL> alter system set events ' 3113 trace name context off'   Or at init.ora   event=" 3113 trace name ERRORSTACK level 3"   You'll need to bounce the database.   ============================= 10046 Trace - Current Session ============================= Open new session is SQL*Plus ALTER SESSION SET max_dump_file_size = unlimited; ALTER SESSION SET tracefile_identifier = '10046'; ALTER SESSION SET statistics_level = ALL; ALTER SESSION SET events &

I discovered that oracle has a utility to analyze EVENT 10046 trace files. It seems to arrange it in HTML and collect some more relevant information from the database. To create the trace file see tracing commands . The TRCANLZR can be found in Metalink Note: 224270.1 Enjoy it :)

SELECT p1.value'/'p2.value'_ora_'p.spid'.trc' filename FROM v$process p, v$session s, v$parameter p1, v$parameter p2 WHERE p1.name = 'user_dump_dest' AND p2.name = 'db_name' AND p.addr = s.paddr AND s.audsid = USERENV ('SESSIONID');

orakill The orakill utility is provided only with Oracle databases on Windows platforms. The executable ( orakill.exe ) is available to DBAs to kill Oracle sessions directly from the DOS command line without requiring any connection to the database. In the UNIX world, a DBA can kill a shadow process by issuing the kill –9 command from the UNIX prompt. UNIX is able to provide this capability given that the UNIX operating system is based on processes that fork other processes. All processes can be listed by using the ps UNIX command. The Oracle background processes will be listed separately from all of the Oracle sessions since they have their own process. Unlike the UNIX operating system, Windows systems are thread-based. For each instance, the background processes and sessions are all contained within the oracle.exe executable. These processes are not listed in the "Processes" tab of Windows Task Manager. Each session creates its own thread within oracle.exe and t