Recently I
had an Issue in which I was supposed to bounce the database and open in
Archive log mode.
When i was
trying to bring the database down , database was hung and was not coming down
and was waiting at the following statement for hours.
Mon Jun
3 18:59:02 2013
Job queue
slave processes stopped
Mon Jun
3 18:59:05 2013
ALTER
DATABASE CLOSE NORMAL
Mon Jun
3 18:59:05 2013
SMON:
disabling tx recovery
Later on when
i went digging the problem i found out that there were temporary segments in database
. use following query to verify..
Select * from
dba_segments where segment_type='TEMPORARY';
OWNER
SEGMENT_NAME
SEGMENT_TYPE
TABLESPACE_NAME
Size in GB
------------------------------
------------------------------ ------------------
------------------------------ --------------------
USER1
792.200737
TEMPORARY TBLSPC_ DATA01
43.857872
SMON was trying
to clean up the temporary segemnts for a clean shutdown, but it was just
hanging there.
I did a
sanity check to verify the temporary segments issue using following
check list
1.)
Checked V$sessions and it doesn't show any sessions for the USER1 schema.
2.)
Checked V$session_longops and it doesn't show any entries.
3.)
Checked if SMON is disabled for cleanup of temporary segments. There is
no event set to value 10 . there is one event which is set to 4
NAME
TYPE
VALUE
------------------------------------
----------- ------------------------------
event
string 10499 trace name context forever, level 4
But nothing
came out of it.
Later i found
out a simple way to do so.
1.) Shutdown Abort
2.) Startup
3.) Shutdown
immediate
Next you
start the database in mount state and issue the Alter database Archivelog
command and that does the magic.
4.) Startup
mount
5.) Alter
database archivelog;
6.) alter
database open
Further
details :
Metalink Note 1076161.6
Description
===========
SHUTDOWN NORMAL or SHUTDOWN IMMEDIATE hangs. In the alert.log, you see only
the following:
Shutting down instance (immediate)
License high water mark = 12
Thu Dec 8 18:43:16 1994
alter database close normal
Thu Dec 8 18:43:17 1994
SMON: disabling tx recovery
SMON: disabling cache recovery
or
waiting for smon to disable tx recovery
There are no ORA errors or trace files.
Scope & Application
===================
Informational
During a SHUTDOWN IMMEDIATE and SHUTDOWN NORMAL, SMON is cleaning up extents
which are no longer needed and marking them as freed.
Either wait for SMON to clean up the free extents in the database as it
shuts down or perform a SHUTDOWN ABORT to shutdown the instance. A SHUTDOWN
ABORT will not perform a clean shutdown.
Verify that temporary segments are decreasing
---------------------------------------------
To verify that the temporary segments are decreasing have an active session
available in Server Manager or SQLPLUS during the SHUTDOWN IMMEDIATE. Issue the following
query to ensure the database is not hanging, but is actually perform extent
cleanup:
SVRMGR/SQL> select count(block#) from fet$;
COUNT(BLOC
----------
7
SVRMGR/SQL> select count(block#) from uet$;
COUNT(BLOC
----------
402
After some time has elapsed, reissue the query and see that the values for fet$
have increased while the values or uet$ have decreased:
SVRMGR/SQL> select count(block#) from fet$;
COUNT(BLOC
----------
10
SVRMGR/SQL> select count(block#) from uet$;
COUNT(BLOC
----------
399
During shutdown the SMON process is cleaning up extents and updating the data
dictionary tables with the marked free extents. As the extents are marked as
freed, they are removed from the table for used extents, UET$ and placed on the
table for free extents, FET$.
How to Avoid creating many Temporary Extents
--------------------------------------------
Once the database has shutdown cleanly, to avoid creating many temporary
extents change the initial and next extent sizes on temporary tablespaces
to a more appropriate size:
ALTER TABLESPACE DEFAULT STORAGE (INITIAL M/K NEXT M/K);
Note: If the temporary tablespace is of type TEMPORARY, then this change
will only affect temporary segments created after issuing the above
command. Any existing temporary segments already in the TEMPORARY tablespace
will not be affected till the instance is restarted. On shutdown, existing
temporary segments are dropped. If the TEMPORARY TABLESPACE is of type
PERMANENT, then cleanup is performed by SMON after completion of the process
using it.
Increasing the initial and next extent size will decrease the number of extents
that are allocated to temporary segments. Since there are fewer extents to
deallocate, the database should shutdown more speedily.
Take the following scenario:
A database was subject to large sorts with the following sort parameter in
the "init.ora" file:
- sort_area_size=1000000
The temporary tablespaces for this database were all created with initial and
next extents sized at 50k and the total database size was about 300mb.
Database sorts will utilize memory as much as possible based on the "init.ora"
parameter "sort_area_size". Once this memory-based sort area is filled, the
database will utilize the temporary table space associated with the database
user to complete the sort operation. During a shutdown normal, the database
will attempt to clean up the temporary tablespaces.
If a small extent size is used, then a large number of extents will be created
for a large sort. The cleanup of the temporary tablespace takes much longer
with a large number of extents.
Note:
=====
You have to do a shutdown abort and then bring the database
back up to run the suggested queries.
For other reasons for slow/hung shutdown see also these notes:
Note 375935.1 - What To Do and Not To Do When 'shutdown immediate' Hangs
Note 428688.1 - Bug 5057695: Shutdown Immediate Very Slow To Close Database.
References:
===========
Note 61997.1 SMON - Temporary Segment Cleanup and Free Space Coalescing
===========
SHUTDOWN NORMAL or SHUTDOWN IMMEDIATE hangs. In the alert.log, you see only
the following:
Shutting down instance (immediate)
License high water mark = 12
Thu Dec 8 18:43:16 1994
alter database close normal
Thu Dec 8 18:43:17 1994
SMON: disabling tx recovery
SMON: disabling cache recovery
or
waiting for smon to disable tx recovery
There are no ORA errors or trace files.
Scope & Application
===================
Informational
During a SHUTDOWN IMMEDIATE and SHUTDOWN NORMAL, SMON is cleaning up extents
which are no longer needed and marking them as freed.
Either wait for SMON to clean up the free extents in the database as it
shuts down or perform a SHUTDOWN ABORT to shutdown the instance. A SHUTDOWN
ABORT will not perform a clean shutdown.
Verify that temporary segments are decreasing
---------------------------------------------
To verify that the temporary segments are decreasing have an active session
available in Server Manager or SQLPLUS during the SHUTDOWN IMMEDIATE. Issue the following
query to ensure the database is not hanging, but is actually perform extent
cleanup:
SVRMGR/SQL> select count(block#) from fet$;
COUNT(BLOC
----------
7
SVRMGR/SQL> select count(block#) from uet$;
COUNT(BLOC
----------
402
After some time has elapsed, reissue the query and see that the values for fet$
have increased while the values or uet$ have decreased:
SVRMGR/SQL> select count(block#) from fet$;
COUNT(BLOC
----------
10
SVRMGR/SQL> select count(block#) from uet$;
COUNT(BLOC
----------
399
During shutdown the SMON process is cleaning up extents and updating the data
dictionary tables with the marked free extents. As the extents are marked as
freed, they are removed from the table for used extents, UET$ and placed on the
table for free extents, FET$.
How to Avoid creating many Temporary Extents
--------------------------------------------
Once the database has shutdown cleanly, to avoid creating many temporary
extents change the initial and next extent sizes on temporary tablespaces
to a more appropriate size:
ALTER TABLESPACE DEFAULT STORAGE (INITIAL M/K NEXT M/K);
Note: If the temporary tablespace is of type TEMPORARY, then this change
will only affect temporary segments created after issuing the above
command. Any existing temporary segments already in the TEMPORARY tablespace
will not be affected till the instance is restarted. On shutdown, existing
temporary segments are dropped. If the TEMPORARY TABLESPACE is of type
PERMANENT, then cleanup is performed by SMON after completion of the process
using it.
Increasing the initial and next extent size will decrease the number of extents
that are allocated to temporary segments. Since there are fewer extents to
deallocate, the database should shutdown more speedily.
Take the following scenario:
A database was subject to large sorts with the following sort parameter in
the "init.ora" file:
- sort_area_size=1000000
The temporary tablespaces for this database were all created with initial and
next extents sized at 50k and the total database size was about 300mb.
Database sorts will utilize memory as much as possible based on the "init.ora"
parameter "sort_area_size". Once this memory-based sort area is filled, the
database will utilize the temporary table space associated with the database
user to complete the sort operation. During a shutdown normal, the database
will attempt to clean up the temporary tablespaces.
If a small extent size is used, then a large number of extents will be created
for a large sort. The cleanup of the temporary tablespace takes much longer
with a large number of extents.
Note:
=====
You have to do a shutdown abort and then bring the database
back up to run the suggested queries.
For other reasons for slow/hung shutdown see also these notes:
Note 375935.1 - What To Do and Not To Do When 'shutdown immediate' Hangs
Note 428688.1 - Bug 5057695: Shutdown Immediate Very Slow To Close Database.
References:
===========
Note 61997.1 SMON - Temporary Segment Cleanup and Free Space Coalescing
No comments:
Post a Comment