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 11.1.0.7, but the metalink note says the problem is also relevant to 11.1.0.6 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:

*._system_trig_enabled=false

*.replication_dependency_tracking = false

*.control_management_pack_access=none

*._optimizer_adaptive_cursor_sharing=false

*.optimizer_use_sql_plan_baselines = false

optimizer_features_enable=’9.2.0′

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.

Related

This was pretty quick. I was thinking about if using transportable tablespaces instead of export/import would work faster and you already sorted the problem. As you mentioned it is all about seeing the patterns.

By the way as a feedback to your presentation, It was absolutely fantastic. If I remember correctly this was the 4th time I listened your presentations (2 in 2008 UKOUG conference and 1 last year in UKOUG event for statspack) and this was the best of them. I normally loose concentration at the last presentation of the day but this time I wished it did not end. Thank you very much for not going to hotsos and bringing hotsos to us :)

Thanks for the comment on my presentation. I have to say that I thought it went very well (even though I skipped the second example). It’s a presentation that I will be repeating at various venues around the world over the next nine months.

Warning: Although my fix worked for me, I can’t guarantee it would work for everyone else. It’s just possible that unexpected things like “operation X happened between the drop and the recreate” may make my method fail.

Why would you drop this user anyway?
– In Error
• We dropped this by mistake when cloning a production database.
• Could easily be done using a front end tool – or an application.
• A script could do this in error.
– For Sabotage
• Would only be an issue if stored outlines are being used?
– As Directed
• We have recently been asked to drop (and re-create) the user by
Oracle support due to an issue we had with Data Pump – not really
sure of the connection here.

I think Coskan may have given you some of the possible arguments. In the case of the presenter it related a mechanical procedure for upgrading their product at client sites. (A procedure that had worked perfectly well for quite a long time)

I agree – more significantly there ought to be some clear and timely documentation about which bits of functionality are massively dependent. Watch out, for example, for the XMLDB owner – if I recall correctly you can drop this one without problems, but expdp and impdp (datapump) will stop working.

I think in this case you can even go through the DBCA install process and cross XML off the selected install list – and then find that datapump doesn’t work.

But what I do is a lot easier than what they often have to do. I started by seeing a carefully prepared 45 minute presentation about what went wrong and how to limit the damaged; and then I had some free time to play with the problem and no pressure to get a result or respond to other calls.

If you want to see something worse, see MetaLink Note#463957.1
Although the “Applies To” section says “Oracle OLAP”, there is nothing OLAP-specific. Particularly, when you read the “Goal” section.
The script is very dangerous. The shocker is the last line beginning with “For Example :” in the “Word of advice” box at the end.

I have, probably about two weeks ago, provided feedback that this was a very dangerous note. It hasn’t been modified yet.

I guess no one ever thought that actual relevant objects may become invalid and stay that way until they are referenced. I notice on one test db it would drop apex and some app views that are granted to public.

I think Terry Pratchett once supplied the appropriate response to this type of advice:

“ARGHHHHH NO NO NO NO NO!”

Even thinking about writing SQL to write SQL to drop anything that is invalid is a bad idea. (And the supplied code has an error anyway which means it can drop a public synonym because an identically named private synonym has become invalid.)

I’ve added a couple of comments on the feedback suggesting that the note should be removed, or significantly modified.

I’ve just been drawn to this post by the need to moderate the pingback from Charles Hooper’s blog – so I thought I’d point out that the offending MOS note has disappeared. Remember, it is possible to supply feedback about MOS notes, Oracle people do listen.