Lets say you need to upgrade your 9i database to 10g (10.2.0.4 to be exact), but you actually want to sabotage the upgrade (don’t know why, just assume you do).
Granted, there are many ways to do this, but you want to do it subtle. What are your options then?
Well, one option is to create the plan_table table in your sys schema (or a synonym plan_table to a plan table in another schema if you want to make it really subtle) before the upgrade.
If you do this, you will see the following message in your upgrade log:
Warning: Package Body created with compilation errors. SQL> show errors; Errors for PACKAGE BODY DBMS_SQLPA: LINE/COL ERROR -------- ----------------------------------------------------------------- 113/5 PL/SQL: SQL Statement ignored 118/44 PL/SQL: ORA-00904: "OTHER_XML": invalid identifier
And the “oracle database server” component in the dba_registry will be marked as invalid.
Mission accomplished I would say.
What is that?
You regret your actions and you want to fix the problem?
*sigh*
Ok then, to fix it you can use the following steps:
- drop the sys.plan_table table
- drop the sys.plan_table$ table
- drop all sys synonyms and public synonyms to the plan_table or the plan_table$
- @?/rdbms/admin/catplan.sql — recreate the plan table
- @?/rdbms/admin/dbmsxpln.sql — reload dbms_xplan spec
- @?/rdbms/admin/prvtxpln.plb — reload dbms_xplan implementation
- @?/rdbms/admin/prvtspao.plb — reload dbms_sqlpa
For those seeking more information:
Metalink note 565600.1 – ERROR IN CATUPGRD: ORA-00904 IN DBMS_SQLPA
Metalink note 605317.1 – DBMS_SQLPA ORA-00904 OTHER_XML invalid identifier
According to the notes, the problem only exists with upgrades to 10.2.0.4 or to 11.1.0.7.
ps. Don’t ask me why I had a synonym called plan_table in my sys schema. I didn’t do it.
pps. This is why you should test your migration (I’m glad I did)
Irrelevant thoughts of an oracle DBA 
How do you avoid this prior to an Oracle 10.2.0.4? Oracle doesn’t really support the removal of both sys.plan_table$ and sys.plan_table prior to the upgrade. I would go with this action just to avoid bug 6893419.
What is your opinion?
Thank you.
Comment by Marie — 16 January 2009 @ 18:06 |
Marie,
Can you give more details about your situation?
If you still are on 9i, then there should be no problem in deleting the sys.plan_table table (or synonym) before you move the database to the 10g home.
If you have already moved the db to the new 10g home, then you would indeed encounter problems in dropping the table while in upgrade mode (at least, it did not work for me when I retested what happened).
In that case you would run the upgrade and then follow the steps listed in the article to fix the isssue.
greetings,
Comment by dhoogfr — 16 January 2009 @ 18:16 |
We upgraded from 9.2 to 10.2.0.3. The tables were created in 9.2. Now at 10.2.0.4 this bug surfaces where no errors existed during the 10.2.0.3 upgrade. What if the table was dropped then recreated in 10.2.0.3 prior to the upgrade? Do you think this would avoid the bug?
Thanks for all.
Comment by Marie — 16 January 2009 @ 18:30 |
According to the metalink notes the problem only exists in 10.2.0.4 and 11.1.0.7.
If you are currently on 10.2.0.3 and have a plan_table in you sys schema, dropping the table before the upgrade to 10.2.0.4 should be enough to avoid the problem.
In 10.2.0.4 you would then have the plan_table$ (as a temporary table) with a public synonym plan_table pointing to it.
Comment by dhoogfr — 16 January 2009 @ 19:09 |
Marie,
I have read metalink note 565600.1 again and according this note the problem only exists when upgrading pre 10g databases to 10.2.0.4 (or 11.1.0.7).
But, when I read further, it states that the problem occurs because the existing plan_table (which takes precedence over the public synonym to the plan_table$ table) does not contain the other_xml column, which is expected by the dbms_sqlpa package.
This other_xml column was added in 10.2.0.1, so I would think that if you have a sys.plan_table table that was created before 10.2 and you upgrade to 10.2.0.4, you will encounter the problem.
Starting with 10.1, which introduced the plan_table$ table with the public plan_table synonym, there is actually no need anymore to keep your own plan_table table.
So my advice would be to drop any plan_table tables you would have and just use the plan_table$ table (through the public plan_table synonym).
Comment by dhoogfr — 18 January 2009 @ 1:12 |
I’m wondering why you did not include run of the utlxplan.sql to recreate sys.plan_table. Wouldn’t there be a problem with plan_table$.
Thanks much.
Comment by Marie — 19 January 2009 @ 16:47 |
The sys.plan_table is not necessary either – have just tested this. All users, including the SYS user can use the temporary plan_table$. Thank you for your explanation. If my understanding is not correct, please let me know.
Comment by Marie — 19 January 2009 @ 17:22 |
Marie,
Your understanding is correct.
The plan_table$ is replacing the plan_table through the public synonym, so there is no need anymore to create a separate plan_table.
Comment by dhoogfr — 19 January 2009 @ 21:56 |
Upgrading from 9.2.0.6 to 10.2.0.4
got the error during catupgrd
shutdown immediate;
ran the following as originally suggested
startup
drop table sys.plan_table;
@?/rdbms/admin/catplan.sql
@?/rdbms/admin/dbmsxpln.sql
@?/rdbms/admin/prvtxpln.plb
@?/rdbms/admin/prvtspao.plb
shutdown immediate
startup upgrade
rerun catupgrd.sql
Database Server is now valid.
worked a treat – thanks
Comment by Rob — 21 January 2009 @ 14:08 |
Rob,
Glad to here the post helped you solve your problem.
You did not need to run the catupgrd.sql script again. The prior scripts are enough to fix the errors.
Although it could be that you need to run utlrp.sql before the status of the oracle “database server” component in the dba_registry is set to “valid”.
Comment by dhoogfr — 23 January 2009 @ 0:11 |