Oracle compatible 19 параметр
Upgrade Oracle Database from 12.2.0.1 to 19c using DBUA
Table of Contents
___________________________________________________________________________________________________
___________________________________________________________________________________________________
1. Environment
PRE-UPGRADE TASKS
2. Backup
3. Run pre-upgrade script
4. View Preupgrade log
5. Verify tablespace sizes for upgrade
6. Update INITIALIZATION PARAMETERS
7. Gather DICTIONARY STATS
8. Purge Recyclebin
9. Refresh MVs
10. Run preupgrade_fixups.sql
11. Verify archive log dest size
12. Create Flashback Guaranteed Restore Point
13. Run DBUA
POST-UPGRADE TASKS WHEN DBUA USING
14. Verify /etc/oratab
15. Verify Timezone version
16. Verify INVALID objects
17. Verify DBA_REGISTRY
18. Run postupgrade_fixups.sql
19. Drop Restore point
20. Update COMPATIBLE parameter
Warning: If the value of COMPATIBLE parameter is changed to 19.0.0 then if for some reasons database needs to be downgraded to 12.2.0.1 the DBA would not have any option other than export/import to downgrade the database. But if this parameter is left unchanged for sometime to see how the database performs after upgrade then it is very easy and fast to downgrade the database if for some reason it is required to be downgraded.
If you change COMPATIBLE you can directly drop your restore points as they are useless. You can’t use Flashback Database to restore point back across a compatibility change of your database.
FOR YOUR INFORMATION ONLY
If local_listener set in INITIALIZATION parameter file, then please remove it before upgrade, after upgrade you can add it back manually. If you don’t remove, may get error while running Post upgrade steps DBUA. I have faced this issue while upgrade from 11g to 12c not in this test case. FYI ONLY
Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.
Before you upgrade, review compatibility between your earlier release Oracle Database and the new Oracle Database release as part of your upgrade plan.
If new features are incompatible with your earlier release, then Database compatibility can cause issues.
Oracle recommends increasing the COMPATIBLE parameter only after you have completed testing the upgraded database.
Review to understand how to set the COMPATIBLE initialization parameter for non-CDB and multitenant architecture containers.
Review to find the default, minimum, and maximum values for the COMPATIBLE initialization parameter for Oracle Database 19c.
Before upgrading to Oracle Database 19c, you must set the COMPATIBLE initialization parameter to at least 11.2.0 .
The COMPATIBLE initialization parameter enables or disables Oracle Database features based on release compatibility
Use this SQL query to find the COMPATIBLE initialization parameter value set for your database.
Leave a Reply Cancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
Subscribe via Email
The COMPATIBLE Initialization Parameter in Oracle Database
Review to understand how to set the COMPATIBLE initialization parameter for non-CDB and multitenant architecture containers.
Oracle Database enables you to control the compatibility of your database with the COMPATIBLE initialization parameter.
Understanding the COMPATIBLE Initialization Parameter
In Oracle Database 19c, when the COMPATIBLE initialization parameter is not set in your parameter file, the COMPATIBLE parameter value defaults to 19.0.0 If you do not set the COMPATIBLE initialization parameter to 19.0.0 , then you cannot use the new Oracle Database 19c features, because your upgraded database is not running in the required COMPATIBILITY setting for Oracle Database 19c features.
When the Oracle Database COMPATIBLE parameter is increased to 19.0.0 , the first Java call to the database initiates a "name translation" operation. This operation can require a few minutes to complete. You should expect this delay the first time a Java call is made to the database after you increase the compatibility parameter. This initial delay to carry out the name translation occurs only during the initial Java call.
Before upgrading to Oracle Database 19c, you must set the COMPATIBLE initialization parameter to at least 11.2.0 , which is the minimum setting for Oracle Database 19c.
The compatible parameter must be at least 3 decimal numbers, separated by periods. For example:
Oracle recommends that you only raise the COMPATIBLE parameter after you have thoroughly tested the upgraded database.
After you increase the COMPATIBLE parameter, you cannot downgrade the database.
If you are upgrading from Oracle Database release 11.2, then you must set the compatible value to at least 11.2.0. You must do this at the time of the upgrade. Do not make this change until you are ready to upgrade, because a downgrade back to an earlier compatibility level is not possible after you raise the COMPATIBLE initialization parameter value.
Oracle Database Administrator’s Guide for information about managing initialization parameters
Rules for COMPATIBLE Parameter Settings in Multitenant Architecture
The COMPATIBLE parameter of the container database (CDB) affects the COMPATIBLE parameter settings of pluggable databases (PDBs) plugged into that container database. Review the following scenarios that occur when you plug in a PDB to a CDB:
PDB COMPATIBLE equal to CDB$ROOT COMPATIBLE parameter setting.
Result: No change to the PDB COMPATIBLE parameter setting.
PDB COMPATIBLE is lower than CDB$ROOT COMPATIBLE parameter setting.
Result: The PDB COMPATIBLE parameter is increased automatically to the same COMPATIBLE parameter setting as CDB$ROOT . After you plug in the PDB, you cannot downgrade the PDB to an earlier release.
PDB COMPATIBLE is higher than CDB$ROOT COMPATIBLE parameter setting.
Result: The PDB cannot be plugged in. Only PDBs with a COMPATIBLE parameter setting equal to or lower than CDB$ROOT can be plugged in to the CDB.
COMPATIBLE is an almost mystic parameter. It has a default setting for each release. But if you try to find more information what it really does, you won’t be very happy. And in reply to my previous blog post about whether you need to change COMPATIBLE when you apply an RU, I received the following question: When and how should you change COMPATIBLE ?
When you change COMPATIBLE …
What happens when you change COMPATIBLE ? At first, you can only set it to a higher value. You can’t revert it to a lower value since Oracle 9i. The ALTER DATABASE RESET COMPATIBILITY command does not exist anymore for over a decade. Hence, once changed, you are not able to revert to the previous value.
Changing COMPATIBLE requires a restart of the database:
You can’t adjust COMPATIBLE while the database is up and running. And you can’t have different COMPATIBLE settings on different instances in a RAC environment.
When you change it, it most likely will adjust the structure of the controlfiles, the redologs and the data file headers. And in the database, you may have now access to new features such as the online move of data files.
COMPATIBLE is also used to determine how your database acts to the “outside”, i.e. how it interacts with an application. Having COMPATIBLE set to 12.1.0 in an Oracle 18c environment should allow the database to behave as it would be an 12.1 database.
But still, where is the list which features depend exactly on COMPATIBLE ?
Unfortunately there is no such list to share. It would be good to have such a list. But the Oracle database code is a complex thing. We are working on something like that for the next releases. Some features are pretty obvious and well documented. For instance, see the LONG identifiers we introduced in Oracle 12.2. When COMPATIBLE is < 12.2 you can’t create a table named “ THIS_IS_MY_TABLE_AND_IT_HAS_A_VERY_LONG_NAME ” (44 byte), but when COMPATIBLE is >= 12.2 , then you can.
When to Set the COMPATIBLE Initialization Parameter in Oracle Database
Oracle recommends increasing the COMPATIBLE parameter only after you have completed testing the upgraded database.
After the upgrade is complete, you can increase the setting of the COMPATIBLE initialization parameter to the maximum level for the new Oracle Database release. However, after you increase the COMPATIBLE parameter, you cannot subsequently downgrade the database.
When should you change COMPATIBLE?
Now the real question somebody raised after my previous blog post Should you change COMPATIBLE when you apply an RU? was: “When should you change COMPATIBLE ?”
And our usual answer is: “Change it 7-10 days after the upgrade IF you will get additional downtime.”
Especially customers who don’t (or can’t) test much, should at least test the downgrade option.
The downgrade option is simple and fast. It usually is even faster than the database upgrade as we don’t need to remove everything from an upgraded database. But your database will behave as it did before.
Hence, the 7-10 days are an estimate based on experience. And honestly, neither Roy nor I have seen many databases being downgraded in real life after an upgrade. But at least we could convince DBAs and project leads to include the downgrade option as a possible fallback scenario and test it.
Still, remember that you will need additional downtime to change COMPATIBLE afterwards as the database will need to be restarted. This is not the case in every project.
And if you ask if you could run a database for months or years with a lower COMPATIBLE setting, then my answer is: Of course, you can. But you will miss a lot of cool features. That is the downside.
Another person on Twitter raised the hand and mentioned that running the database even just for a few days with a lower COMPATIBLE setting may required an extra testing cycle, i.e. database upgrade but with lower COMPATIBLE setting versus database upgraded with higher COMPATIBLE setting. While I can see the point, I wouldn’t invest too much into testing with the lower setting (see the optimizer section below).
Upcoming Events
There are no upcoming events at this time.
Understanding Oracle Database Compatibility
If new features are incompatible with your earlier release, then Database compatibility can cause issues.
Databases from different releases of Oracle Database software are compatible if they support the same features, and if those features perform the same way. When you upgrade to a new release of Oracle Database, certain new features can make your database incompatible with your earlier release.
Your upgraded database becomes incompatible with your earlier release under the following conditions:
A new feature stores any data on disk (including data dictionary changes) that cannot be processed with your earlier release.
An existing feature behaves differently in the new environment as compared to the old environment.
Related
You may also like.
Database Migration from non-CDB to PDB – The Fallback Challenge
AutoUpgrade and the COMPATIBLE parameter
Fallback Strategy: Flashback to Guaranteed Restore Points
COMPATIBLE and Database Upgrades
During and after a database upgrade, we don’t change COMPATIBLE . And neither does the DBUA. Or the new AutoUpgrade.
There are two exceptions to this rule:
- COMPATIBLE gets adjusted when there is a forced COMPATIBLE change needed. This is the case when the release you are upgrading to does not support the COMPATIBLE setting of the source anymore. Simple example: You operate an Oracle 11.2.0.4 database with COMPATIBLE=10.2.0, then it will be pushed up. Or another example: You upgrade an Oracle 9.2 database to Oracle 11.1.0.7. Oracle 11g did not support COMPATIBLE=9.2.0 – hence, there had to be an implicit COMPATIBLE adjustment.
- Be aware when operating with Oracle Multitenant across different releases. Once you unplug from a CDB with lower COMPATIBLE setting and plug into one with a higher setting, an implicit COMPATIBLE change will happen. And it will block your option to plug back into the source again.
RSS Feed for Upgrade your Database – NOW!
Can AutoUpgrade change COMPATIBLE?
Yes it can. Or actually you can convince it to do so. And here is how.
AutoUpgrade has these powerful options to execute a shell script before or after upgrade:
before_action and after_action .
This way I can pass a script to AutoUpgrade changing COMPATIBLE after the database upgrade.
But wait one second please. Didn’t I write above that you can’t change COMPATIBLE when a GRP is in place – which is the standard behavior of the tool? Yes, I did. And I will assume that you will change COMPATIBLE only in cases where you are either 100% certain that the upgrade will succeed and you won’t fall back to a GRP. Or in cases where you have another backup/restore strategy in place already, and don’t want to rely on a GRP.
Hence, I will use the parameter:
in my command file to have no GRP created.
And even if you disagree with my statement above, there is still an option. You can advice AutoUpgrade to drop the GRP automatically once the upgrade has been completed successfully:
will drop the GRP.
Recent Posts
Upgrade your Database – NOW! © 2022. All Rights Reserved.
The technical storage or access is strictly necessary for the legitimate purpose of enabling the use of a specific service explicitly requested by the subscriber or user, or for the sole purpose of carrying out the transmission of a communication over an electronic communications network.
The technical storage or access is necessary for the legitimate purpose of storing preferences that are not requested by the subscriber or user.
The technical storage or access that is used exclusively for statistical purposes. The technical storage or access that is used exclusively for anonymous statistical purposes. Without a subpoena, voluntary compliance on the part of your Internet Service Provider, or additional records from a third party, information stored or retrieved for this purpose alone cannot usually be used to identify you.
The technical storage or access is required to create user profiles to send advertising, or to track the user on a website or across several websites for similar marketing purposes.
Several times I’ve got asked by customers if AutoUpgrade does change the COMPATIBLE parameter. So let me explain what happens to COMPATIBLE when you upgrade. And give you also some hints about AutoUpgrade and the COMPATIBLE parameter.
Photo by Ivana Cajina on Unsplash
What does COMPATIBLE do?
To find an answer to this question, I consulted the documentation at first. And I found this:
Setting COMPATIBLE ensures that new features do not write data formats or structures to disk that are not compatible with the earlier release, preventing a future downgrade. Features that require a higher value of COMPATIBLE to work correctly may be restricted or disabled to ensure downgrades are possible.The COMPATIBLE parameter specifies the Oracle version number that the database disk format must be compatible with. The database can be downgraded to the version specified in the COMPATIBLE parameter or any later version.
That is a good explanation. COMPATIBLE determines the data format and structures in disk. This for instance may apply to the internal format of the redologs or the data file headers. In addition, features seem to be dependent on it. And – this is the most important fact to take note of – if you change it, you won’t be able to downgrade anymore.
Furthermore, I queried MOS with terms such as “COMPATIBLE” or “COMPATIBLE features“. But honestly, I found a lot of things. And not what I was looking for. Indeed, I read results from the community pages where people had exactly this question: Which features depend on COMPATIBLE ?
COMPATIBLE and the Optimizer
A very common misunderstanding happens when COMPATIBLE gets mixed with the Oracle optimizer. Both are independent from each other. As far as I know, an adjustment in COMPATIBLE has no effect on the optimizer and its features. Those are derived from the value of OPTIMIZER_FEATURES_ENABLE . Please see also the SQL Tuning Guide about how to influence the Optimizer with the setting of OPTIMIZER_FEATURES_ENABLE .
A tiny example
With this config file I will upgrade my DB12 database in the Hands-On Lab:
The key parameters are marked in RED .
If you’d like to use the GRP standard option, this would be the config file then instead:
I’m executing a simple shell script after upgrade:
And just in case you are wondering (several people have asked) how I set my environment. I do set the environment with my script db19 . This is owned by root and has execute rights. It is placed in /usr/local/bin , and hence can be executed by my oracle user. This is how the environment script looks like:
Don’t forget to set:
And this shell script calls a SQL file:
After the upgrade:
Pretty nice – if you need and want this.
One important thing you should recognize:
You will find the logfile in ///postupgrade – its name is action_stdout.log .
12 Responses
WOW! Thanks heaps for this one, Mike!
No clue this would be a problem but it makes total sense!
hi mike good that there is some protection now 😉
you are (unfortunately) 100% correct – and I agree with you. The basic issue is that, as soon as you use, SCOPE=SPFILE you can basically do all sorts of nonsense. Nobody protects you from doing this (e.g. SGA_TARGET=10000000G). And this happens here as well. So yes, there’s sort of protecting against breaking your fallback. But you still have to rebuild your PFILE 🙁
Love the blog Mike.
In this case, you can change the spfile. This is because the instance started. It was the mount attempt that failed. Since the instance is started, you can issue the alter system set compatible command, using the scope=spfile parameter. The spfile will be changed. Then you just shutdown and restart.
Notice in your output, the instance started just fine – it was the attempt to mount the database that failed. I just tested this on 12.2, and it worked fine.
So, after the ORA-38880, the instance is still up. You simply issue an alter system set compatible=”11.2.0.0.0″ scope=spfile;
Shutdown the database and issue a startup command. No muss, no fuss.
Thanks Robert – good point!
Hi Mike. Nice post about FB DB. I have a doubt. In order to flashback changes on primary database protected with physical standby. Do I need to create garantee restore point on standby first? and hours later Do I need to cancel mrp on standby and flashback primary database ?
Often a GRP on the standby is not necessary as you can flashback it far back as long as the archive logs are present and not cleaned up. This means:
You delete SCOTT accidentally on PRIM, then you FLASHBACK the standby and export SCOTT and reimport it on PRIMARY. The standby will synch automatically after the flashback operation – and the SCOTT schema will be propagated again to the standby.
But you don’t need a GRP on the standby in addition when you FLASHBACK the primary. The standby is intelligent to synch again after you flashed back the primary.
Nice blog. it worked for me.
Thanks.
Luckily I found your blog, I’ve tried to change the compatible from 11.2.0.4 to 19.0.0 on EXACS, in the physical standby and primary. I dropped the restore point first in the Physical but you need to stop and mount the database, after that I’ve followed the process.
Thank you very much!
Thanks a lot, Lizzeth!
Hi Mike. First of all, Your blog is awesome and i really admire you. I want to ask a question. Is it possible to flashback database to a time (not to a GRP) in flashback time retention period but before the point of time of changing COMPATIBLE parameter?
I have encountered that issue, i need an accidentally deleted table value. Can i rewind my physical standby to time before delete operation that took place just before we changed compatible parameter?
thanks for your feedback.
When your database has FLASHBACK ON (check in v$database) then you can flashback to any point in time being still covered in your flashback logs. Since this is dependent on your disk space assigned to the FRA, you may need to check the max flashback time.
SELECT to_char(oldest_flashback_time, 'YYYY-MM-DD HH24:MI:SS') FROM v$flashback_database_log;
But if you change COMPATIBLE, there is no way back beyond this change. We prevent changing COMPATIBLE since 12.2.0.1 when you have a GRP. Since you don’t have a GRP, there is no blocking – and hence, you can go backwards exactly to the point AFTER you changed COMPATIBLE if your flashback logs allow you that span.
Upgrade @YouTube
Archives
Follow me on Twitter
Does AutoUpgrade change COMPATIBLE?
No. Clearly, AutoUpgrade does not change the COMPATIBLE parameter for several reasons. At first, we recommend to not change the COMPATIBLE parameter right after the upgrade if you can afford another downtime soon after. By keeping COMPATIBLE on the previous (before upgrade) value, you have the choice to revert back to the previous state. Either via flashing back to a Guaranteed Restore Point (GRP) right after the upgrade, or days after the upgrade with the database downgrade.
Furthermore, by default AutoUpgrade sets a GRP – and allows you to flashback to it. But having a GRP since Oracle Database 12.2.0.1 blocks the change of COMPATIBLE as long as the GRP is in place.
Actually these are enough reasons to have the AutoUpgrade not change COMPATIBLE. And still, we listen to your feedback and discussed whether AutoUpgrade may get a parameter to enforce compatibility after a successful upgrade. And with the change to the CDB-only architecture with Oracle 20c this may even play less of a role as there is no seamless simple fallback anyways anymore when you migrate to the CDB-architecture.
Categories
How many digits should you use?
That is another typical question I see from time to time. And my advice is: 3
Why 3? The default is 3 number, for instance ‘ 18.0.0 ‘. And 3 numbers are always enough.
Even a marquee feature such as Oracle In-Memory which got introduced with so called patch set 12.1.0.2 did not require to adjust COMPATIBLE in the forth digit. If you don’t believe me, please check MOS Note: 1903683.1 – Oracle Database In-Memory Option (DBIM) Basics and Interaction with Data Warehousing Features:
The COMPATIBLE parameter must be set to at least 12.1.0 to enable the feature, and the RDBMS version must be at least 12.1.0.2.0.
Hence, 3 numbers are always enough. And as I wrote here, there’s no need to change COMPATIBLE when you apply an RU or RUR.
A while ago I blogged about Fallback Strategy: Flashback Database to Guaranteed Restore Points. I included a recommendation that you must not change the COMPATIBLE setting. But I should have been a bit more clear and precise about Guaranteed Restore Points and COMPATIBLE parameter settings.
Guaranteed Restore Points and COMPATIBLE parameter
First of all, you can’t use FLASHBACK DATABASE to a Guaranteed Restore Point when you changed to COMPATIBLE parameter. This was a common pitfall in older Oracle releases.
But with Oracle Database 12.2.0.1 we add a tiny change which prevents you from accidentally changing COMPATIBLE while your fallback relies on a Guaranteed Restore Point.
In Oracle Database 12.2 you’ll get the following error when you try to advance COMPATIBLE :
ORA-38880: Cannot advance compatibility from 11.2.0.4.0 to 12.2.0.0.0 due to
guaranteed restore points
Hence you have to drop the guaranteed restore point first:
But as you will change COMPATIBLE usually in the SPFILE with:
you’ll receive the ORA-38880 only during a failed startup. Your database won’t mount anymore.
In this case use the following workaround:
Then edit your PFILE in $ORACLE_HOME/dbs – and restart the database using this PFILE.
Then drop the restore point first – and afterwards you can advance COMPATIBLE .
Читайте также: