I had a very pleasant day yesterday at a SIG meeting of the UK Oracle User Group where I did the presentation about “drawing your SQL” that is the basis of the article I wrote for Simple Talk a little while ago.
One of the other sessions had the entertaining title: “What happens if you drop the OUTLN user ?”. You might wonder why you would ever think of dropping the outln user, of course, but the presenter was able to justify that. (If you’re wondering, the outln user is the one that first appeared in Oracle 8i as the owner of the tables that record Stored Outlines.)
The scary summary of the presentation was this: if someone drops the outln owner in 11g and you’re not using stored outlines, you may not find out about it until the next time you try to start up the database – and it won’t start up. (Side note – Robert Geier was also speaking on the necessity of auditing database activity).
Having had a little accident (fortunately on a test system when testing the upgrade procedures from 10g to 11g) the presenter spent some time with Oracle trying to recover the situation. With a little help from Oracle they managed to get the database started (and this included the less than obvious step of setting parameter replication_dependency_tracking to false) and recreated the outln user. But this still left the database throwing errors and core dumps fairly regularly.
Oracle’s final comment on the problem was a suggestion to extract all the data and import it into a new instance because the hacking around to get it going is not supported, or go to a backup. See Metalink note 855104.1 for further details. Enhancement request 8657453 has been raised to warn users who try to drop this schema.
Having a free day today – and being curous, and in the mood to avoid real work – I thought I’d have a look into the problem. My guess was that all the SQL Plan Management stuff was probably associated with the stored outline stuff and that this was causing the problem. The presenter had been using 22.214.171.124, but the metalink note says the problem is also relevant to 126.96.36.199 which is the version I happened to have to hand, so – having taken a backup – I dropped the outln schema and bounced the database. Sure enough – the startup aborted.
After poking around a little bit, I ended up setting the following parameters (which seemed a likely set to stop any 11g clever tricks from messing up my recovery strategy – they might be overkill for what I did). Note particularly the very defensive optimizer feature level:
- *.replication_dependency_tracking = false
- *.optimizer_use_sql_plan_baselines = false
This let me start the database normally, at which point I deleted everything to do with the outln schema objects that was still lurking. This included views, synonyms, privileges, rows from sys.expact$ (see $ORACLE_HOME/rdbms/admin/catol.sql for these) and global temporary tables in the SYSTEM schema (see $ORACLE_HOME/rdbms/admin/dbmsol.sql for these). Look for objects with names like ‘%OUTLINE%’ or ‘%OUTLN%’ to get the data dictionary clean.
Once I had deleted the remnants I shutdown and restarted the database.
After restarting, I extracted the code to create the outln schema from $ORACLE_HOME/rdbms/admin/doptim.sql. I had to set a couple of references “M_IDEN” and “M_CSIZ” as column lengths – which I did by comparison with other tables in the data dictionary. In my case the relevant values were 30 and 2000.
After running the code to recreate the schema, I ran: catol.sql, dbmsol.sql and prvtol.plb in that order (the metalink note doesn’t mention them), flushed the shared pool, shutdown the database, got rid of the funny parameters, and restarted the database.
It restarted, and hasn’t thrown a single errror … so far.
Update 24th March: There’s always room for error. Doing some work today on stored outlines and the use of the dbms_metadata package I discovered an oversight. While testing a couple of ideas (using my laptop) I found that dbms_metadata was crashing with Oracle error ORA-00942 (table or view does not exist).
Repeating the test script with sql_trace enabled showed me that the error came from a query against table SYS.KU$_OUTLINE_VIEW, and using the Windows “find” command on the SQL scripts in $ORACLE_HOME\rdbms\admin I found the section of catmeta.sql that I needed to run to recreate all the ku$ types and views needed to allow dbms_metadata to access the definitions of stored outlines.