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:
- Back up the production database (10.1.0.3 32-bit) via RMAN to a NAS array (we’ll just call it /nas)
- Create an instance on the new server (64-bit 10.2.0.3) and restore the controlfile from /nas
- Restore the database to the new server
- Start the database on the new server in mount mode
- 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
- Shut down production, start it in restricted mode, archive log current, and shut it down again
- Apply the final archive log to the new server and “alter database open resetlogs upgrade”
- Run @?/rdbms/admin/catupgrd.sql (remember, it’s not catpatch.sql anymore)
- Shutdown, start up, and run utlirp.sql and utlrp.sql to change packages from 32-bit to 64-bit
- 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 2That 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 1Sure 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.
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.
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. 😉
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.
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
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?
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?
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;
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
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.
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
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.