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:
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:
To diagnose the error, I decided to see if dbms_stats had the same issue.
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.
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!
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.