I want to upgrade our database version (126.96.36.199) 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 188.8.131.52 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 ?
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