Operating System - HP-UX
1745919 Members
4535 Online
108723 Solutions
New Discussion юеВ

Database shutdown problem

 
SOLVED
Go to solution
Chris Fung
Frequent Advisor

Database shutdown problem

Hi all,

We are running a dataware house with Oracle 8.1.7. There are 4 instances, 3 for production (1 x core db, 1 x application repository db )and 2 for development (1 x core db, 1 x application repository db) The application is "informatica". Today, I tried to bring down the production core database for maintenance, however I wait for more than 30 minutes and the svrmgrl session seemed hanged (The oracle processes still exist). I have no choice but shutdown abort it. After that I brought it up again sucessfully and then I tried to shutdown it again....This time I still fail, the svrmgrl session still hanged for quit long. This time I have to stop it again by "shutdown abort".

When I brought down the production core database with shutdown abort option, then I stop the production repository database (and it stopped normally). Since I doubted there will be relationship between the production core db and the production repository db, I then try to stop the production core db again and this time it stopped normally. After a series of testing, I finally concluded that there is dependence between these two databases. However, I don't have other evidence to support my hypothesis !! Just wondering if anyone can tell me this problem really happen in the real life ?? Appreciated it if you can direct me to some resources related to this matter.

Many thanks,

Chris,
12 REPLIES 12
harry d brown jr
Honored Contributor

Re: Database shutdown problem

Chris,

Is the prod-db updating the repos-db? Check with your dba's and developers to see what the relationship is. Or check with informatica.

live free or die
harry
Live Free or Die
Andreas D. Skjervold
Honored Contributor

Re: Database shutdown problem

Hi

Are you using "shutdown" or "shutdown immediate" ?

"Shutdown [normal]" will wait for users to end their transactions and log out before shutting down the database. And in an production enviroment this might be a long wait.

"Shutdown immediate" ends transactions and rolls back all uncommitted data and closes sessions before shuting down the database.

Anyway the "shutdown abort" should be save to use as well. The only difference from "shutdown immediate" is that the rollback of data is done prior to database open.

Andreas
Only by ignoring what everyone think is important, can you be aware of what everyone ignores!
Tom Geudens
Honored Contributor

Re: Database shutdown problem

Hi Chris,
A simple database link between the two databases being used in a session would do the trick ... yes, this happens in real life :-)
B.t.w. when you say shutdown, do you actually mean "shutdown" or do you mean "shutdown immediate" ? There is a major difference. In our environment the basic shutdown is a "shutdown immediate".

As Harry suggested, I would start grilling the DBA's. Oracle topsessions (part of Oracle Enterprise Manager which most - if not all - DBA's have) can reveal such sessions.

Regards,
Tom
A life ? Cool ! Where can I download one of those from ?
Aashish Raj
Valued Contributor

Re: Database shutdown problem

Hi,

If you give shutdown immediate, and get message
"waiting for active processes to complete" in the alert.log, then oracle is clean up the shadow processes .
U can speed up this shutdown process by manually killing all oracle shadow processes oracle for users .pls do not kill the process which is shutting down the database :)

AR
Chris Fung
Frequent Advisor

Re: Database shutdown problem

Hi all,

I use "Shutdown immediate" option through out the process described above. I think Tom's suggestion is quite correct, there probably have DB link between the repository DB and production DB.

Cheers,

Chris,

Re: Database shutdown problem

We had a similar problem with an oracle database. In our case there was a lot of "fragments" within the tablespaces and the pmon process was trying to cleanup the mess during the shutdown.

If this is the case you will see that the pmon process consumes 100% of cpu (can be less if your server is heavily loaded) during the shutdown.

The oracle suggestion was to wait the pmon processs clean all the fragment. It took almost 24hs to the shutdown to succed.

Bill Thorsteinson
Honored Contributor

Re: Database shutdown problem

We run short transactions
so I have switched to
SHUTDOWN TRANSACTIONAL
so as to not rollback any
in progress transactions.

Cleanup can take quite a while.
Brian Crabtree
Honored Contributor

Re: Database shutdown problem

One thing that could be happening (most likely) is that the database is cleaning up temporary segments from the temp tablespace. You can verify this by doing the following:

> shutdown abort;
> startup restrict;
> shutdown immediate;

This should hang. On the UNIX level, you can use 'top' to see if the "smon" process is taking up 100% of a CPU. Unfortunantly, there isn't anything that you can do other than let it clean up that information before you can bring the database down. One way is to let the shutdown proceed. The other is to perform an "alter tablespace temp coalesce;" with "temp" substituted for your temporary tablespace name.

To prevent this problem in the future, you will want to change the initial and next extent sizes of your tablespace up from whatever it is now (most likely, 24k default). Normally, 500k - 1m and higher should be used. Otherwise, moving to LM Temporary tablespaces would be the next best option.

Brian
Chris Fung
Frequent Advisor

Re: Database shutdown problem

I am quite agree with Brain. I noticed that before I shutdown the database, the free space for the Temp tablespace was around 4MB. However, after I successfully shutdown the database and restart it again, the Temp tablespace was around 4000MB. I think Oracle was performing clean up and reclaiming space for the Temp tablespace.

If that is the case, should I drop the Temp tablespace and recreate it again with large extents so as to reduce the fragmentation? I doubt that some Oracle objects are stored in the Temp tablespace !! How can I recreate the Temp tablespace in safe ??

Many Thanks,

Chris,