Posts

Showing posts from March, 2007

Hidden initialization parameters

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

spfile is created in folder DB_UNKNOWN

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.

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

Trace Analyzer TRCANLZR

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

The orakill utility

Image
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 …