Hi to all
I want to upgrade our database version (220.127.116.11) to 10g (10.2.0.2)
I know if I set COMPATIBLE parameter to 10.2.0.2 at first then I can't downgrade to 18.104.22.168 if any problem occurs
I am not sure that our application can deal with 10g or not so I think it's better to leave the COMPATIBLE parameter to 9.2.0 several days for sure
and if all things go well then change COMPATIBLE to 10.2.0.2
(I need to say that I can’t test our application in a test environment )
Now do you think leaving COMPATIBLE to 9.2.0 (after upgrade) for many days can cause any problem to Database ?
In general, there should be no issue.
You should test your application in a test instance with the parameter set to 10.2.0.2 and verify there are no issues before changing it in production.
If you are going to upgrade, why not upgrade to 11gR2 (the latest version) ? 10gR2 is already de-supported.
Technically there should not be any issues. You queries will just be using 9i optimizer and plans. The right way is to test the application in test first. If you are leaving compatible parameter to 9 for a long time , than what is the benefit of upgrading the database to 10. Think about it.
+"You queries will just be using 9i optimizer and plans. "+
COMPATIBLE means you won't use any feature that would prevent a downgrade, it has an influence on how Oracle stores the data.
Using the new features of the optimizer does not prevent a downgrade hence this aspect is not covered by the COMPATIBLE parameter (it is covered by OPTIMIZER_FEATURES_ENABLE)
Explanations by Tom Kyte : http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:485421569569