Hi,
Hope someone can help with this. I have an issue with a customers 9i database (9.2.0.5 running on Windows 2003 SP2), it's a legacy application that is due to be migrated elsewhere next year so it's a "keep the lights on" scenario. The database that I'm having issues with is actually a dev box, the production is the same version though.
Basiaclly the issue is at the moment that shutdown immediate hangs with 'Waiting for smon to disable tx recovery'. I've left this run for around 15 hours but there's no further entries in the alert log. I've read the MOS note on the issue and several other articles online which would suggest that SMON is trying to clear up temporary segments. this would make sense, there was an issue with a missing tempfile last week. I had since created a new temp tablespace, made it the default temp tablespace and dropped the old one. The MOS note suggested running some of the following:
select ktuxeusn, to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') "Time", ktuxesiz, ktuxesta
from x$ktuxe
where ktuxecfl = 'DEAD';
SELECT usn, state, undoblockstotal "Total", undoblocksdone "Done",
undoblockstotal-undoblocksdone "ToDo",
DECODE(cputime,0,'unknown',SYSDATE+(((undoblockstotal-undoblocksdone) / (undoblocksdone / cputime)) / 86400))
"Finish at"
FROM v$fast_start_transactions;
select count(block#) from fet$;
select count(block#) from uet$;
All of the above actually return nothing at all, which would suggest that there's nothing going on that SMON is actually doing. TheMOS notes would suggest that I should see some items being recovered and the count from the fet$ and uet$ views reducing. Then what is causing the Waiting for smon to disable tx recovery error? I was able to shutdown abort and restart in restricted mode, there's no spid or sid running other than my own. There's nothing running in the task manager either. I've requested the local sysadmin team install Procexplorer to give a more comprehensive view of what's going on also.
Further to this, I had been attempting to refresh a schema with production data. I dropped the original schema and recreated it and began the import from the dmp file but this gets to one specific table and hangs. I was able to do an import of the schema with rows=none, this ran fine, which led me to believe that there may be corruption in the prod data, however I ran an RMAN VALIDATE (physical & logical) which suggests that all is well on prod. I'm hoping that the procexplorer might identify a hung OS process for IMP.exe that might be causing the issue.
Setting the following: event="10513 trace name context forever, level 2" has been suggested as a way of stopping SMON from performing the recovery, however that seems like a workaround and delaying the inevitable rather than an actual solution to me.
Has anyone any other suggestions? Apologies about the length of the post but I've tried to provide as much information as possible.
Thanks,
David