The problem of Shutdown

there will be time when an application is coded with a detached process running. The detached process makes a connection to your database and maintains that connection indefinitely. There are many valid reasons to have an application process do this.

However, a problem arises when you need to shut down your database. You won't be able to perform a normal shutdown because the application process is running and the database will wait until all processes have disconnected before the shutdown is completed.

There are two ways to solve this problem. You can make it a policy always to do a shutdown immediate, or you can have your developers use the v$instance view to determine whether your database is waiting to shut down by checking the shutdown_pending column. If this column value changes from NO to YES, the application can be coded to automatically disconnect from the database. You will, of course, have to give the application user the privilege to select from this view

ORA-01476 ORA-06512: Statspack.Snap Fails

Applies to:
Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 10.2.0.2This problem can occur on any platform.

Symptoms
-- Problem Statement:While trying to use statspack procedure for taking snapshot , execution fails with ORA - 01476
exec statspack.snap(i_snap_level => 7);
ERROR at line 1:ORA-01476: divisor is equal to zero
ORA-06512: at "PERFSTAT.STATSPACK", line 4956
ORA-06512: at "PERFSTAT.STATSPACK", line 104
ORA-06512: at line 1

Error: ORA 1476Text: divisor is equal to zero------------------------------------------------------------Cause:
An expression attempted to divide by zero.Action: Correct the expression, then retry the operation

You can also observe similar problem if you observe trace files with the following contents are being created in the bdump directory by the m000 background process as discussed inNote 359950.1
*** SQLSTR: total-len=273, dump-len=240,STR={insert into wrh$_sga_target_advice (snap_id, dbid, instance_number,SGA_SIZE, SGA_SIZE_FACTOR, ESTD_DB_TIME, ESTD_PHYSICAL_READS) select:snap_id, :dbid, :instance_number,SGA_SIZE, SGA_SIZE_FACTOR, ESTD_DB_TIME, ESTD_PHYSICAL_R}

Cause
After enabling the 1476 errorstack, we found the following statement has failed
ksedmp: internal or fatal errorORA-01476: divisor is equal to zeroCurrent SQL statement for this session:INSERT INTO STATS$SGA_TARGET_ADVICE ( SNAP_ID , DBID , INSTANCE_NUMBER , SGA_SIZE , SGA_SIZE_FACTOR , ESTD_DB_TIME , ESTD_DB_TIME_FACTOR , ESTD_PHYSICAL_READS ) SELECT :B3 , :B2 , :B1 , SGA_SIZE , SGA_SIZE_FACTOR , ESTD_DB_TIME , ESTD_DB_TIME_FACTOR , ESTD_PHYSICAL_READS FROM V$SGA_TARGET_ADVICE

This behavior. is attributed toBug 5008142.To verify the same try to query V$SGA_TARGET_ADVICE

SQL> select * from v$sga_target_advice;select * from v$sga_target_advice*
ERROR at line 1:ORA-01476: divisor is equal to zero
Basically here the db_cache_advice (=ready/off ) advisory is turned off so value for base_estd_phy_reads is zero which causes the failure.

The fix for the bug is to put a decode statement while selecting from the X$ table and use that in view definition for v$sga_target_advice
Solution
-- To implement the solution, please execute the following steps::1)Download thePatch 5008142from metalink and apply it.

OR

Set db_cache_advice on
SQL> alter system set db_cache_advice=on scope=both;
You would need to wait for some time for statistics to be captured.

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