I have a challenge for you. We have 3 temporary segments (in a permanent user data tablespace) which smon cannot clean up. The errors in the alert log are; Errors in file /NEWTST_DB/oratst/tstdb/8.1.7/admin/TST2/bdump/smon_3117_tst2.trc: ORA-09999: Message 9999 not found; product=RDBMS; facility=ORA The errors in the trc file are pretty much the same.
Let me tell you how we got to this point: 1) We cloned our production database to a test instance. In creating the new control file for test (which we had to do to rename the database and rename the datafiles), we failed to include in the datafile list one of the newly added datafiles in production (we used an old create statement, forgetting that the production layout had changed). (The datafile name was 1 of 5 datafiles which make up a user tablespace). The controlfile create command succeeded and we were none the wiser. UNTIL... We started to get errors a few days later when trying to insert into a table which resides in the tablespace. We got errors to the fact of 'datafile 306 does not exist, cannot insert. At that point, oracle decided to put a stub in the datafile system tables for our missing datafile. So now we have the dreaded '/TST/oradata/MISSING00306' entry. 2) I ran a query against the dba_extents table to find the objects which have data in the missing datafile. There were three tables. I know the proper recovery method is to restore the datafile and recover archive logs. We cannot do this for two reasons. The first is the fact that this is a cloned database and was opened with resetlogs. The second is the fact that the datafile was never in the controlfile create statement. Thus it's header information still points to the production database name, while all the other datafiles now have the new test database name in their header information. Oracle will not let you add it now to the existing datafiles. Nor will it let you recreate the controlfile with the filename included. All datafile headers must contain the same database name when creating the controlfile. The next suggestion would be to reclone. We cannot do that either as we've already performed too much work in the test instance to trash it. 3) So, I decided to drop the objects which were referenced in the missing datafile and recreate them. That way the objects will be built in a good tablespace and the users can insert into them. This worked, sort of. The drop seemed to succeed, even though it produced the errors above and dropped my connection. But I was able to log back in and successfully recreate the objects. I assumed oracle was trying to update the freelists header in the datafile and bombed when it could not find the datafile. It did remove the table names from the segment information, as I was able to create new tables with the same names. 4) Ok, so now I have good tables and the users are working fine. So what's the problem? Well, I queried the extents view again to see if anything was still pointing the missing datafile. yes. It seems oracle modified my old tables (pre-rebuilt) to temporary segments! I now have the same extent usage and # extents for 3 temporary segments in the same locations. That's ok, because I can still use my new tables without problems. The problem is SMON. SMON is trying to coalesce/drop temporary segments and spewing the; ORA-09999: Message 9999 not found; product=RDBMS; facility=ORA error every 10 seconds! Is there any way to drop these temporary segments? Any way to drop the references to the missing datafiles? I have already done a 'alter datafile offline drop;' and it succeeded, but smon is still getting errors. How do I shut SMON up? How do I get rid of these temporary segments (which have data in a missing datafile)? I've bounced the instance also. I believe my only recourse now is to drop the tablespace which contained the missing datafile, which should remove all references to the datafile, correct? I don't even know if that's possible as it's over 20gb and I have no way to move all the objects out it (contains Apps tables and lobs and such). Anyone have any bright ideas or is our instance hopelessly confused? __________________________ Glenn Travis Database Administrator Business Intelligence & Support [EMAIL PROTECTED] 919-531-0434 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Glenn Travis INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
