sql trace instruction

SQL trace is a kind of tools to trace the SQL statement, which is very powerful diagnostic tool.
Generally speaking ,there three steps
1 set up the range of trace for example trace the whole database or one session , and then use the relevent command to enable trace.
2 after a specified period of time , stop the trace
3 find out the trace file , and then format it, at last read and analyze it

The detail process
1 enable "SQL Trace"

A: enable the sql trace in the range of the whole database
Method : configure out parameter file , set sql_trace=true
Result: it results in tracing all the processes, including all the background processes and user processes
Note: This somehow leads some serious performance problem, so be carefully

B: enable the sql trace at the current session level
Method: sql> alter session set sql_trace=true;
stop tracing : alter session set sql_trace=false;
Normally ,we use the sql trace at the session level. after setting sql_trace=true, the system gonna start to trace the sql statement

C: trace another session
Method: through v$session , we need to know another session's sid and serial#
sql>select sid, serial#,username from v$session where username='****';
start tracing
sql>exec dbms_system.set_SQL_TRACE_in_session(9,437,true) ( 9 sid,437serial#)
stop tracing
sql>exec dbms_system.set_SQL_TRACE_in_session(9,437,false)

2 find , format, and read trace file
A: find
the trace file is located at position which the parameter "user_dump_desk" point to

1. if you wanna try to find the current session trace file , run this sql statement

SELECT d.value'/'lower(rtrim(i.instance, chr(0)))'_ora_'p.spid'.trc' trace_file_name
from
( select p.spid from v$mystat m,v$session s, v$process p
where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,
( select t.instance from v$thread t,v$parameter v
where v.name = 'thread' and (v.value = 0 or t.thread# = to_number(v.value))) i,
( select value from v$parameter where name = 'user_dump_dest') d

2. if you wanna find another session trace file , try to find out the session' s sid and serial#, and then run

SELECT d.value'/'lower(rtrim(i.instance, chr(0)))'_ora_'p.spid'.trc' trace_file_name
from
( select p.spid from v$session s, v$process p
where s.sid=144 and s. SERIAL#=27 and p.addr = s.paddr) p,
( select t.instance from v$thread t,v$parameter v
where v.name = 'thread' and (v.value = 0 or t.thread# = to_number(v.value))) i,

B format

oringinal file is not readable , you have to use the "tkprof" tool to translate

SQL>$tkprof D:\ORACLE\PRODUCT\10.2.0\ADMIN\MYORACLE\UDUMP\hsjf_ora_1026.trc D:\ORACLE\PRODUCT\10.2.0\ADMIN\MYORACLE\UDUMP\hsjf_ora_1026.txt

ora-04043 dba_data_file is not exit

I found a bug of Oracle

Under the mount stage , there is error reported, when you desc dba_.....

SYS@ning>select status from v$instance;

STATUS
------------
MOUNTED

SYS@ning>desc dba_data_files
ERROR:ORA-04043: object dba_data_files does not exist

And then alter database open, but it is same problem

SYS@ning>alter database open;

Database altered.

SYS@ning>desc dba_data_files
ERROR:ORA-04043: object dba_data_files does not exist
SYS@ning>desc dba_temp_files;
Name Null? Type
----------------------------------------- -------- ----------------------------
FILE_NAME VARCHAR2(513)
FILE_ID NUMBER
TABLESPACE_NAME NOT NULL VARCHAR2(30)
BYTES NUMBER
BLOCKS NUMBER
STATUS CHAR(9)
RELATIVE_FNO NUMBER
AUTOEXTENSIBLE VARCHAR2(3)
MAXBYTES NUMBER
MAXBLOCKS NUMBER
INCREMENT_BY NUMBER
USER_BYTES NUMBER
USER_BLOCKS NUMBER

That is means if only you describe some dba view under mount stage , Oracle can not identify it any more, whatever is under mount stage or open. the only way to solve this problem is restart up the database

Oracle release that this bug gonna impact on Oracle Server - Enterprise Edition - Version: <8.1.7.4 to 10.1.0.3

but actually , on 10.2.0.3 there is still same problem

Let me give you a ridiculous solution
1) Don't describe the dba_* views at mount stage.
2) If you issue DESC of any DBA_*views at mount stage, then shutdown and restart the instance.

Rman-03009 error

P 执行 rman target sys/tt@tt nocatalog >backup database format='e:\backup\%d_%s.bak'
RMAN-03009: backup 命令 (ORA_DISK_1 通道上, 在 09/08/2008 14:48:58 上) 失败 ORA-19504: 无法创建文件"E:\BACKUP\XJLI_71.BAK"
ORA-27038: 所创建的文件已存在
OSD-04010: 指定了 选项, 但文件已经存

solution 1 check out the privilege. do you have privilege to create a file in the specified folder execute command: backup database format='e:\backup\%d_%s.bak' again. it was also failure

2 run: backup database result: it can work checkout the address, it was e:\backup\
So we can get rig of suspecting pravilige

3 becauce on the first time it was failed to run backup database format='e:\backup\%d_%s.bak' and on the second time it can work the only difference between two times is the format

4 change the format backup database format='e:\backup\%d_%s_%p.bak' it can work. the only thing i did is adding a %p

Analysis is below
When we set RMAN there are two parameter need to configure out
configure channal device type disk maxpiecesize 200m
configure maxsetsize to 1g those two parameters result that one backup set contains two backup piece.

So under this situation that one backup set contains more than one backup piece, we have to set a format with %p in order to distinguish backup pieces when we execute backup database format='e:\backup\%d_%s.bak' it was failed

The reason is that this command result in an creation of file "XJLI_71.BAK" however my database is 800m, the maxsetsize is 1g, so in the thoery there are two backup piece files, each 500m. in fact there are two backup piece file, one is 500m, one is 300m So the system gonna run like this firstly this is no any problem when the first backup piece file XJLI_71.BAK was created but when the second backup piece come out , becauce of same backup set, the second backup piece is also named XJLI_71.BAK So the system gonna report an error

Consequently when we backup some objects, we need to pay attention to format . There are some principle about format %c,&u,%p,%s,%d,%n,%t

How to solve the LRM00109 and ORA01078

ORA-01078: failure in processing system parameters LRM-00109: could not open parameter file '/opt/oracle/products/10.1.0.3/db...

when you use the pfile to startup the database, probably ora-01078 and lrm-00109 gonna catch you,how does this troubleshooting be well done

first all we need to figure out the location of the pfile folder, not pfile file, this folder probably was located at orcle_home\admin\orcl\pfile\,
there is pfile file named init.ora in this folder.when we create a pfile from spfile, this pfile will be located at orcle_home\db_1\database\, not in the pfile folder. so when we startup database firstly by using this pfile , it is failed

Solution ; we can copy the original pfile from pfile folder located at orcle_home\admin\orcl\pfile\, and paste this copy at orcle_home\db_1\database\, at last rename this copy same as the pfile that was name when you create a pfile from spfile.Right now you can set the parameter in pfile at orcle_home\db_1\database\ and startup by pfile The job is well done