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.