cancel
Showing results for 
Search instead for 
Did you mean: 

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
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,
Luis Felipe F. Rosinha
Occasional Visitor

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,
Brian Crabtree
Honored Contributor
Solution

Re: Database shutdown problem

With a normal TEMPORARY tablespace, you should set the initial and next extent to a reasonable size (for a 4000m tablespace, I would suggest 5m, but this would depend on the number of concurrent sessions accessing your system). You can do this with an alter tablespace command (ie: alter tablespace TEMP default storage (initial 5m next 5m);). I would suggest not setting pctincrease to anything other than 0 for the TEMP tablespace.

With a LM Temporary tablespace (the EXTENT_MANAGEMENT column should say "LOCAL" for this tablespace, 8i and 9i only), you will need to drop and recreate the tablespace. The easiest way is to get the list of files associated with the tablespace (select * From dba_temp_files), perform a drop tablespace, and then a create tablespace with the reuse option. I will build an example:

create temporary tablespace temp tempfile '/mnt/d0001/oradata/temp01.dbf' reuse extent management local uniform size 5m;

That should create the temporary tablespace using your current file (you will need to change the file name most likely).

Hope this answers your question,

Thanks,

Brian
Jeanine Kone
Trusted Contributor

Re: Database shutdown problem

I just wanted to note the distinction between a TEMPORARY tablespace (i.e. create temporary tablespace) and a tablespace designated for temporary sort segments (i.e. create tablespace...temporary).

I have never used the first one - but to respond to the question of it being safe to drop and recreate the tablespace, and as to whether it would contain any user data - the answer may depending upon which type you are talking about.

For the second - their will never be any user objects stored in it (nobody even needs to have quota on it) - so, yes it is safe to drop and recreate.


Brian Crabtree
Honored Contributor

Re: Database shutdown problem

Just to answer your question, neither tablespace will ever contain user objects. Both tablespaces have the "TEMPORARY" flag set, and will disallow non-sort objects from being created in them.

The true temporary tablespace (create temporary tablespace) uses temporary files, rather than datafiles, which can be recreated quickly, and do not require recovery.

Brian