Showing posts from March, 2007

Hidden initialization parameters

It seemd that the number of hidden parameters is increasing between versions.
a small check reveals that:
version contains 300 paramters.
version contains 613 paramters.
version contains 1179 paramters.
Here is the query I used to locate these parameters:
a.ksppinm "Parameter",
a.ksppdesc "Description",
b.ksppstvl "Session Value",
c.ksppstvl "Instance Value"
x$ksppi a,
x$ksppcv b,
x$ksppsv c
a.indx = b.indx
a.indx = c.indx
a.ksppinm LIKE '/_%' escape '/';

spfile is created in folder DB_UNKNOWN

Using Linux RH4 64Bit with Oracle with OMF on ASM
when we tried to "create spfile from pfile;" the spfile was located in a wrong location:

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';

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.

Tracing Commands

Trace Specific ORA-XXXXX - Current Session
====================================Activate:SQL> alter session set events '3113trace 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 '3113trace name errorstack level 3'; Stop:
SQL> alter system set events '3113 trace name context off'Or at init.oraevent="3113trace 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 '10046 trace name context forever, level 12';

Trace Analyzer TRCANLZR

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 :)

Udump trace file name & location

SELECT p1.value'/'p2.value'_ora_'p.spid'.trc' filename
v$process p,
v$session s,
v$parameter p1,
v$parameter p2
WHERE = 'user_dump_dest'
AND = 'db_name'
AND p.addr = s.paddr

The orakill utility

orakill The orakillutility 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 –9command 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 psUNIX 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 therefore, is not exposed …