Oracle Scratchpad

March 11, 2010

Dropping OUTLN

Filed under: Infrastructure,Troubleshooting — Jonathan Lewis @ 5:35 pm BST Mar 11,2010

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 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.

16 Comments »

  1. 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 :)

    Comment by coskan — March 11, 2010 @ 6:23 pm BST Mar 11,2010 | Reply

    • Coskan,

      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.

      Comment by Jonathan Lewis — March 20, 2010 @ 11:57 am BST Mar 20,2010 | Reply

  2. >You might wonder why you would ever think of dropping the outln user, of course, but the presenter was able to justify that.
    And what was the reason?

    Comment by Timur Akhmadeev — March 11, 2010 @ 8:36 pm BST Mar 11,2010 | Reply

    • Here are presenters reasons (Peter Mahaffey)

      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.

      Comment by coskan — March 11, 2010 @ 9:13 pm BST Mar 11,2010 | Reply

    • Timur,

      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)

      Comment by Jonathan Lewis — March 20, 2010 @ 11:59 am BST Mar 20,2010 | Reply

  3. It would be nice if users like that would be “undroppable” by anyone.
    I must signal this article to my colleagues, periodically we are asked to drop users that we don’t need.

    Comment by lascoltodelvenerdi — March 12, 2010 @ 7:54 am BST Mar 12,2010 | Reply

    • lascoltodelvenerdi,

      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.

      (Someone may want to correct me on that – I may be wrong.)

      Comment by Jonathan Lewis — March 20, 2010 @ 12:04 pm BST Mar 20,2010 | Reply

  4. great !
    i hope you apply soon to Oracle Support

    Comment by Sokrates — March 12, 2010 @ 1:51 pm BST Mar 12,2010 | Reply

    • Sokrates,

      I’m not sure they could afford me ;)

      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.

      Comment by Jonathan Lewis — March 20, 2010 @ 12:02 pm BST Mar 20,2010 | Reply

  5. “It would be nice if users like that would be “undroppable” by anyone”

    I agree. We’ve seen queries (from novice DBAs) about dropping SYS objects.

    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.

    Hemant K hitale

    Comment by Hemant K Chitale — March 12, 2010 @ 3:52 pm BST Mar 12,2010 | Reply

    • Wow!,

      And I thought the old notes about index rebuilds were bad, you could rewrite that note as
      Step 1: Point Gun at head
      Step 2: Pull trigger

      Comment by Chris_c — March 15, 2010 @ 2:58 pm BST Mar 15,2010 | Reply

      • To me the shocker was “IMPACT:LOW SKILL-LEVEL:NOVICE”

        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.

        With moderation like that, who needs guns?

        Comment by joel garry — March 15, 2010 @ 11:43 pm BST Mar 15,2010 | Reply

    • Hemant,

      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.

      Comment by Jonathan Lewis — March 20, 2010 @ 12:15 pm BST Mar 20,2010 | Reply

      • 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.

        Comment by Jonathan Lewis — December 2, 2011 @ 6:50 pm BST Dec 2,2011 | Reply

  6. [...] 3-How to get around corrupted database after dropping OUTLN user in 11G ? Jonathan Lewis-Dropping OUTLN [...]

    Pingback by Blogroll Report 05/02/2010 – 12/03/2010 « Coskan’s Approach to Oracle — April 23, 2010 @ 1:07 am BST Apr 23,2010 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,268 other followers