When errors tell you absolutely nothing!

Share Button

The task was simple…

Oh yes, hello by the way. It has been a while since I’ve posted!

Anyways, like I said, the task was simple. Migrate an Oracle 10.1.0.3 32-bit database on one server to 10.2.0.3 64-bit on another server, and do it with only 1 hour of downtime. Migrations like this are daunting sometimes to company-employed DBAs since the number of migrations per database release are usually kept to a minimum. However, in the consulting world, these sort of tasks are old hat, and we’ve got lots of tricks up our sleeves to get a database from one box to another without resorting to the old, tired, and painfully slow exp/imp.

So in this case, I set myself up a task list that would accomplish the necessary goal:

  1. Back up the production database (10.1.0.3 32-bit) via RMAN to a NAS array (we’ll just call it /nas)
  2. Create an instance on the new server (64-bit 10.2.0.3) and restore the controlfile from /nas
  3. Restore the database to the new server
  4. Start the database on the new server in mount mode
  5. Copy archive logs from the old server periodically, catalog them on the new server and recover database over and over until you’re ready to migrate
  6. Shut down production, start it in restricted mode, archive log current, and shut it down again
  7. Apply the final archive log to the new server and “alter database open resetlogs upgrade”
  8. Run @?/rdbms/admin/catupgrd.sql (remember, it’s not catpatch.sql anymore)
  9. Shutdown, start up, and run utlirp.sql and utlrp.sql to change packages from 32-bit to 64-bit
  10. All finished!

10 points to anyone who can figure out the missing step there without reading any further. One missing step caused #8 (running catupgrd.sql) to fail miserably.

Everything went very smoothly until step #8. When running that step, I received the following error after about 5 minutes:

ORA-20000: Insufficient privileges to analyze an object in Dictionary Schema
ORA-06512: at "SYS.DBMS_STATS", line 13323
ORA-06512: at "SYS.DBMS_STATS", line 13649
ORA-06512: at "SYS.DBMS_STATS", line 15985
ORA-06512: at "SYS.DBMS_STATS", line 16027
ORA-06512: at line 2

That was an odd error, considering I was logged in as SYS. Just to be overly safe and perform a step I never should have had to do, I explicitly granted ANALYZE ANY and ANALYZE ANY DICTIONARY to SYS, just in case. I ran it once more, and received the same error.

Further investigation showed that the error occurred while running cmpdbmig.sql, during this command:

execute dbms_registry.gather_stats(null); 

To diagnose the error, I decided to see if dbms_stats had the same issue.

BEGIN dbms_stats.gather_table_stats('SYS', 'SOURCE$'); END;

*
ERROR at line 1:
ORA-20000: Unable to analyze TABLE "SYS"."SOURCE$", insufficient privileges or
does not exist
ORA-06512: at "SYS.DBMS_STATS", line 13046
ORA-06512: at "SYS.DBMS_STATS", line 13076
ORA-06512: at line 1

Sure enough, I couldn’t gather statistics on the SOURCE$ table even though I was logged in as SYS. This error was a little different, saying that I had insufficient privileges OR the object did not exist.

SQL> show user
USER is "SYS"

SQL> desc source$
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OBJ#                                      NOT NULL NUMBER
 LINE                                      NOT NULL NUMBER
 SOURCE                                             VARCHAR2(4000)

As expected, the object exists and I’m SYS. I tried everything I could think of, but under no circumstances could I make the DBMS_STATS package work against SYS objects. I thought that perhaps I had gotten a little too clever with my upgrade strategy.

Finally, it dawned on me that there was one step I had missed before running the catupgrd.sql script: I did not add a tempfile to the TEMP tablespace! Remember always, if you restore from backup, there WILL NOT BE a temporary tablespace anymore. It is not included in your backups!

SQL> alter tablespace temp add tempfile '/u01/app/oracle/oradata/temp01.dbf' size 1000M;

Tablespace altered.

SQL> exec dbms_stats.gather_table_stats('SYS', 'SOURCE$');

PL/SQL procedure successfully completed.

As if by magic, it suddenly works! All the “insufficient privilege” errors put to rest with the creation of a temporary tablespace file. I reran catupgrd.sql, did the final steps, and the migration went off without a hitch!

As such, I hereby submit that the error should be rewritten to:

ORA-20000: Insufficient privileges to analyze an object in Dictionary Schema or something like that, or perhaps not like that whatsoever. Please contact the internet.

Share Button

11 comments

  1. I like the ‘Please contact the internet’ bit.
    I think “ORA-20000” should set off warning bells. It’s not a built-in ORA- code with matching text, but one bodged up with a RAISE_APPLICATION_ERROR. I wonder if there’s a bit of PL/SQL in DBMS_STATS that actually trapped a useful SQL error message and substituted that one.

  2. Gary, I agree, the ORA-20000 was definitely a tip-off to think outside the text, but at the same time, it was odd that I got not one but TWO like messages regarding permissions.

    ORA-20000: Insufficient privileges to analyze an object in Dictionary Schema
    ORA-20000: Unable to analyze TABLE “SYS”.”SOURCE$”, insufficient privileges or
    does not exist

    A substitution is possible, but two different substitutions regarding the same general issue (and both being wrong) was a stretch!

    The good news is, as you’ve reminded me, I can write my own ORA-20000’s with RAISE_APPLICATION_ERROR, meaning I can write my own DBMS_STATS wrapper that catches their error and inserts my more suitable one. 😉

  3. I think , temp tablespace error / message always come in alert.log
    When , we upgrade database , we always keep one window open with tail -f alertSID.log. It keep informing, if any thing go wrong on DB level.

  4. Let me take a guess that the missing step is that you need to switch log file after the restore and upgrade operation, is this a correct guess?

    Sorry if wrong as I have jet lag from a trip the other day.

    -Ben

  5. I received the same error. However, it was during an import of a table into the database. I can’t figure out what is causing the error. The import is into an existing database that has a temp tablespace. So that can’t be it. Here is a sample:
    IMP-00017: following statement failed with ORACLE error 20000:
    “DECLARE SREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := NULL; SREC.MAXVAL :=”
    ” NULL; SREC.EAVS := 0; SREC.CHVALS := NULL; SREC.NOVALS := DBMS_STATS.NUMAR”
    “RAY(0,0); SREC.BKVALS := DBMS_STATS.NUMARRAY(0,1); SREC.EPC := 2; DBMS_STAT”
    “S.SET_COLUMN_STATS(NULL,'”BIB_HOLDINGS_INTERFACE”‘,'”EXTRA_NUMBER_FIELD2″‘,”
    ” NULL ,NULL,NULL,0,0,93895,srec,0,6); END;”
    IMP-00003: ORACLE error 20000 encountered
    ORA-20000: TABLE “TEMSLOAD”.”BIB_HOLDINGS_INTERFACE” does not exist or insufficient privileges
    ORA-06512: at “SYS.DBMS_STATS”, line 2121
    ORA-06512: at “SYS.DBMS_STATS”, line 4931
    ORA-06512: at line 1
    Import terminated successfully with warnings.

    Any ideas as to why I would get this error?

  6. how if we duplicate database from shared nfs volume. duplicate database, shutdown, and wait for green signal – when up, recover the db and open it. we could bypass temporary tablespace issue if we have ‘set newname for temp/data file n’ properly in run{ } clause – right?

  7. I got the ORA-20000 while trying to run gather_fixed_object_stats too, only I got it on a dev instance that we have had up and running for over a year, and it has a temp file.

    But when I ran gather_table_stats on sys.source$, it didn’t generate the error.

    I’m still searching for answers.

    SQL> exec dbms_stats.gather_fixed_objects_stats(‘ALL’);
    BEGIN dbms_stats.gather_fixed_objects_stats(‘ALL’); END;

    *
    ERROR at line 1:
    ORA-20000: Insufficient privileges to analyze an object in Fixed Ob
    ORA-06512: at “SYS.DBMS_STATS”, line 13323
    ORA-06512: at “SYS.DBMS_STATS”, line 13637
    ORA-06512: at “SYS.DBMS_STATS”, line 14165
    ORA-06512: at line 1

    SQL> show user
    USER is “ADPDS_DBA”
    SQL> connect / as sysdba
    Connected.
    SQL> begin dbms_stats.gather_table_stats(‘SYS’,’SOURCE$’); end;
    2 /

    PL/SQL procedure successfully completed.

    SQL> exec dbms_stats.gather_fixed_objects_stats(‘ALL’);
    BEGIN dbms_stats.gather_fixed_objects_stats(‘ALL’); END;

  8. Interesting.

    I didn’t get the ORA error after deleting the statistics, and then running the gather_fixed_objects_stats without putting any parameters, as follows:

    begin
    dbms_stats.delete_fixed_objects_stats();
    end;
    /

    begin
    dbms_stats.gather_fixed_objects_stats();
    end;
    /

    2 3 4

    PL/SQL procedure successfully completed.

    There is a very good Metalink note related to fixed object stats: Note 798257.1

  9. I have run into the same problem (11gR2), and found that it does make a difference how you specify the tabname parameter to dbms_stats.gather_table_stats :

    , tabname => ‘MY_TABLE’ — OK
    , tabname => my_table_var — Gives Oracle 20000 error.

    Easy workaround is to hard code your table name, but that does not make me happy, though.

  10. fwiw, I have an ORA-20000 with gather_dictionary_stats. Nothing in alert log. 10.2.0.1.

    Turns out, by trying to gather stats on sy.wrh$_sqltext, I see the real problem is a block corruption in my sysaux tablespace. 🙁

    Cheers, Wayne

  11. I had the same issue.. fixed by granting “ANALYZE ANY DICTIONARY”:

    SQL> exec DBMS_SCHEDULER.run_job(‘DBADMIN.GATHER_STATS_JOB’);
    BEGIN DBMS_SCHEDULER.run_job(‘DBADMIN.GATHER_STATS_JOB’); END;

    *
    ERROR at line 1:
    ORA-20000: ORA-20000: Insufficient privileges to analyze an object in Dictionary
    Schema
    ORA-06512: at “SYS.DBMS_STATS”, line 13197
    ORA-06512: at “SYS.DBMS_STATS”, line 13523
    ORA-06512: at “SYS.DBMS_STATS”, line 15859
    ORA-06512: at “SYS.DBMS_STATS”, line 15901
    ORA-06512: at “DBADMIN.GATHERSTATS”, line 39
    ORA-06512: at line 1
    ORA-06512: at “SYS.DBMS_ISCHED”, line 150
    ORA-06512: at “SYS.DBMS_SCHEDULER”, line 441
    ORA-06512: at line 1

    SQL> grant ANALYZE ANY DICTIONARY to dbadmin;

    Grant succeeded.

    SQL> exec DBMS_SCHEDULER.run_job(‘DBADMIN.GATHER_STATS_JOB’);

    PL/SQL procedure successfully completed.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.