Went Live with 126.96.36.199 in October 2011. This particular patchset fixed a huge number of bugs over the 188.8.131.52 version, it’s release was timely for our migration project (released Sept. 22, 2010). We calculated it was better to go with the latest patchset due the sheer number of bugs fixed vs. opening SR’s which more often than not would have required the installation of said patchset.
List of Open SR’s
1. Begin automatic SQL Tuning Advisor run for special tuning task “SYS_AUTO_SQL_TUNING_TASK” produces ORA-07445 errors
Mon Oct 18 22:09:45 2010
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x4] [PC:0x5259224, qsmmixComputeClusteringFactor()+386] [flags: 0x0, count: 1]
Somehow this was fixed, my understanding they are filing a bug/patch soon for this item. This information came from a reader who actually had some work done on his SR. My SR on this issue was last updated October 29, I ended up closing it Nov. 17 because the problem seemed to disappear after quite a few changes done to fix other performance issues.
2. AWR Reports Virtual Memory Paging while not evident at the OS level. Is this some sort of bug or just another marketing scheme for ASSM? We have entered an SR on this issue, they are assigning a bug number to it.
3. Leaving SQL Plan Management off for now….see the following GC graph , 3:59 is when I turned off SQL Plan Management…..
This is where I am putting the current stake in the sand in terms of the upgrade. I am entering an SR with a test case. I don’t expect the answer to be resolved very quickly. See the following initialization parameters…the one that is specific to our application is optimizer_mode. Yours may be different, we didn’t change it from our previous 10gR2 setting.
I don’t consider this project to be completely finished until the OPTIMIZER has been upgraded, that step is gradually done after the initial go-live. Stay tuned for more information as I will post our results when the optimizer is fully functional using SQL Plan Management features. YOUR MILEAGE may vary as each database environment is different, this is what I experienced at our site. Don’t assume that you will need to do the exact same steps, use this as a way of investigating some of the possible issues related to this upgrade. This won’t replace you doing the same steps in a TEST environment first. I wouldn’t hire anyone that takes these instructions verbatim and applies them to a production database. A couple of MOS documents to start with: Oracle 11gR2 Upgrade Companion [ID 785351.1] See also the Note: Complete Checklist for Manual Upgrades to 11gR2 [ID 837570.1] If you are new to Oracle upgrades start with the Manual Upgrade Guide, the DBUA doesn’t do everything the manual checklist does. Even if you plan on using the DBUA for the actual upgrade, the manual checklist might help if you encounter any issues using the DBUA.
A. What we started with?
OLTP Database ( Oracle EE 10.2.0.4) approximately 80 GB running on Linux RHEL 2.6.9-78.0.1.ELsmp SMP x86_64 using the UTF8 characterset.
DataGuard environment – two physical standbys (one local, one remote) and one logical standby for reporting. We upgraded the logical standby at the same time as the primary by following this note: Upgrading Oracle Database with a Logical Standby Database In Place [ID 437276.1] We were finally successful in upgrading a logical standby, previous migrations failed requiring us to recreate the logical. The failure most often manifested itself by some sort of hanging or SQLAPPLY stopping. The only thing that stopped the SQLAPPLY process this time was the COECBOSTATS table. One of the upgrade steps required us to upgrade that table using DBMS_STATS. We simply skipped any DML, DDL for the COESTATS schema on the logical. Thank you Oracle for improving the logical!
1. The change in database versions automatically includes a minor PL/SQL Toolkit Verison Upgrade –
select OWA_UTIL.GET_VERSION from DUAL;
SYS@11gR2 > select OWA_UTIL.GET_VERSION from DUAL;
2. select value from NLS_DATABASE_PARAMETERS where parameter = ‘NLS_NCHAR_CHARACTERSET’;
Check that National Characterset (NLS_NCHAR_CHARACTERSET) is UTF8 and AL16UTF16.
B. Game Plan
1. Install 184.108.40.206 in it’s own ORACLE_HOME, this is called an out-of-place upgrade.
I personally prefer a manual upgrade as compared to using the DBUA so that is what this case study will cover.
Since Oracle is backwards compatible, certain components can be migrated to the 220.127.116.11 version before the database. This is what I term the Step-Ordered Approach.
This method narrows the window outage because you are doing only the database upgrade, all other preliminary steps have already been finished. This means that I start migrating at least 6 months before the final database upgrade.
General list of items that can be migrated first before the database:
- Oracle Net Services – LISTENER.ORA, SQLNET.ORA
- Clients – ODBC, JDBC, SQLPLUS, Precompilers, etc.
- RMAN – Virtual/Catalog and Database
- Grid Control Repository Database
- Grid Control Intelligent Agents
- PL/SQL Toolkit
- **EXPORT/IMPORT and DATAPUMP will have issues
2. Migrate the listener by adding the environmental variable TNS_ADMIN and editing the listener.ora to reflect the ORACLE_HOME of the ORACLE_SID. Don’t forget the sqlnet.ora.
I simply shutdown the older-versioned listener, copy over the *.ora files to the 18.104.22.168 location, start any listeners needed. Verify connectivity, it is simple to return back to the earlier-versioned listener by starting them again.
3. Migrate clients, compilers and precompilers. Each environment (client types) is different but I have little to no issues using an upper-level SQLPLUS client for a down-level database. There are some issues using the reverse: using a downlevel client with an upper-level database.
4. Upgrade the RMAN catalog by running the upgrade command. RMAN can also service down-level clients (databases). Be sure and backup the RMAN catalog before doing this step. RMAN upgrade catalog bug in 22.214.171.124 with a paper fix – see the following post about that issue.
5. Make sure you have multiple fall back positions for upgrade day. We will simply turn off archivelog which stops the data transfer to the physical standbys…I can do a DATAGUARD switchover if things really go south. I also disable the standby archive destinations temporarily by the ‘alter system set log_archive_dest_state_(n) =DEFER scope=both’ command.
6. Preparation for Upgrading the Optimizer, Gathering/Saving Statistics
a. Several methods/steps for saving statistics – SQL Tuning Sets (Top N)
Pre-upgraded database – instance-wide stats at peak load using Automatic Workload Repository – 7 days
If using Statspack – Level 7 – will generate execution plans. Plan execution history is saved as of 10g see Tuning and Performance Guide and the following note for more information: DBA_HIST_SQLSTAT WHEN WAS THE EXECUTION PLAN PRODUCED ? [ID 879677.1]
b. Operating System Level Statistics using OS tools – CPU, memory and IO (such as sar, vmstat, iostat)
c. Make sure dictionary statistics have been gathered. SQL>EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
7. Day of the DATABASE Upgrade- the manual method requires some downtime.
Look into the process called “Upgrading using a Transient Logical Standby” for the least amount of downtime.
Take a backup before you begin (the manual upgrade guide has a quick and easy example using RMAN but it doesn’t take into account archivelogs, I would recommend adding a command to switch logs before the backup and after as well as including the archivelogs in the backup.)
rman “target / nocatalog”
ALLOCATE CHANNEL chan_name TYPE DISK;
BACKUP DATABASE FORMAT ‘<db_backup_directory>%U’ TAG before_upgrade;
BACKUP CURRENT CONTROLFILE TO ‘<controlfile_backup_directory>’;
a. Edit /etc/oratab to reflect the new 126.96.36.199 home, edit any other custom configuration or environment variable file as well to reflect the proper ORACLE_HOME.
b. Shutdown clients (this is our personal preference to just keep things quiet on the client side by displaying a custom message that the service is temporarily unavailable). Disable all batch jobs (AT, CRON, etc).
c. Run the preupgrade tool – 11gR2ORACLE_HOME/rdbms/admin/utlu112i.sql (required step for manual upgrades). Follow the advice for any duplicate objects owned by both sys and system, there are a few that are normal. The output from this script mentions a MOS document to determine the ones to drop. Check for the existence of invalid objects before starting the upgrade.
Don’t skip this step as you will have problems with TimeZone updates if this script isn’t run. Implement the recommendations.
There are several other scripts (tasks) that Oracle recommends from the Manual upgrade guide to run at this point:
SQL> SELECT DISTINCT(TRUNC(last_refresh))
FROM dba_snapshot_refresh_times; 2
SQL> SELECT * FROM v$recover_file;
no rows selected
SQL> SELECT * FROM v$backup WHERE status != ‘NOT ACTIVE’;
no rows selected
SQL> select * from dba_2pc_pending;
no rows selected
Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql) [ID 556610.1] Note: I also ran this script after the upgrade was finished.
d. Issue: PUBLIC DATABASE LINKS that couldn’t be modified/dropped/fixed after the upgrade. Since 11gR2 changes the global name designation, it affects database links. Opened an SR on this issue, the only resolution that worked for us was to drop them before the upgrade and recreate after. See Related Note: Cannot drop a database link after changing the global_name ORA-02024 [ID 382994.1]. This note contains a possible workaround that didn’t work for us, notice that the note references the 9i version.
e. Database shutdown. With a physical standby that the primary is no longer shipping logs to… I have an nice backup of the database at this point.
f. Exit the session, logon again cleanly, execute oraenv to the newer ORACLE_HOME.
g. SQL >startup upgrade, @?/rdbms/admin/catupgrd, startup, @?/rdbms/admin/utlu112s.sql, @?/rdbms/admin/catuppst, @?/rdbms/admin/utlrp
Here is where I hit a major snag….this one totally had me flustered. I am mentioning it here because someone else will do the same thing.
Be very careful of the initialization parameters related to the audit trail. If you put in a directory that doesn’t exist you cannot proceed. I assumed at this point I could just edit the initSID.ora I was working with to the appropriate directory. I could not change it….the database won’t mount for anything at this point, changing the initSID.ora didn’t fix the problem.
SQL> connect / as sysdba
ORA-09925: Unable to create audit trail file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 9925
ORA-09925: Unable to create audit trail file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 9925
Unable To Create Audit File When Connecting As Sysdba Into Database [ID 549651.1]
The following article clued me into what the issue was. I don’t think this is a bug but part of a security requirement.
Connect /as sysdba or Startup Fails With ORA-09925 When Instance Is Down [ID 392643.1]
“Starting with 10gR2: Note that when the database instance is down and the first connect and the startup command is run, the audit_file_dest parameter is not yet initialized, hence unknown to the shadow process, as such the audit files for these commands can only be written to the default destination. Writing these files is a mandatory requirement for NCSC C2 security evaluation criteria and therefore this behavior cannot be turned off.”
Note: The first connect and startup command is the clue. Once I set the initSID.ora parameter to the wrong entry initially entered, all was well with the world, the database finally mounted. I could continue.
Also see the following article for how to change the audit_trail_dest parameter. ALTER SYSTEM SET AUDIT_FILE_DEST results in ORA-02096 [ID 739683.1]
I immediately disable the automatic gathering of table statistics by the following SQL statement (see the later section on Post Upgrade Tasks) –
SYS@ORCL11>EXEC DBMS_AUTO_TASK_ADMIN.DISABLE(‘auto optimizer stats collection’, NULL, NULL);
This is so I can keep the current well-performing 10g version of schema statistics.
The query below verifies that the auto optimizer stats collection has been disabled:
SYS@ORCL11> select client_name,status from Dba_Autotask_Client;
h. Recreate the database links you saved earlier.
i. PASSWORD-PROTECTED DEFAULT ROLES won’t work for SQL users in the 11g world. You may have to adjust your roles to something like : alter role usr_rolename not identified;
j. Network security changes may require the creation of ACCESS CONTROL LISTS, see MOS note: How to set network ACLs in Oracle 11 [ID 958129.1]
k. We upgraded the Database Oracle Wallet by just saving it with the newer Oracle Wallet Manager binary (GUI) – start owm , open existing wallet, make sure auto login is checked, save as in the same location. I tried doing this same task with the command-line utility, it was a lot more work because it meant you had to recreate an entirely new wallet.
l. Daylight Savings Time Upgrade (The Pre-Upgrade Tool actually performs some of the initial changes required for this step) – see MOS Note: Actions For DST Updates When Upgrading To 188.8.131.52 Base Release [ID 815679.1]. We had several advanced queueing tables that were affected by the timezone version change.
m. Gather fixed object, dictionary and hardware/OS statistics (both noworkload and 2 hours with workload) – See Managing CBO Stats during an upgrade to 10g or 11g [ID 465787.1]
If you install the COEscripts into your database before the upgrade to export the statistics, you will receive this error message after the upgrade. This is considered normal, just run the command to upgrade the statistics table.
*** SKIP export before gathering: ORA-20002: Version of statistics table COECBOSTATS.COE$_STATTAB is too old. Please try
upgrading it with dbms_stats.upgrade_stat_table
ERROR at line 1:
ORA-20002: Version of statistics table COECBOSTATS.COE$_STATTAB is too old. Please try upgrading it with
ORA-06512: at “SYS.DBMS_STATS”, line 11271
ORA-06512: at “SYS.DBMS_STATS”, line 11288
ORA-06512: at “SYS.DBMS_STATS”, line 26704
ORA-06512: at line 28
SQL> EXECUTE DBMS_STATS.UPGRADE_STAT_TABLE(‘COECBOSTATS’,’COE$_STATTAB’);
n. Monitor and adjust any pinned packages – see MOS Note: How to Automate Pinning Objects in Shared Pool at Database Startup [ID 101627.1]
o. Check and adjust any DBA_DIRECTORIES…the default DATA_PUMP_DIR changed with the upgrade. See MOS Bug 9006105 DATA_PUMP_DIR is not remapped after database upgrade
Also related to DBA_DIRECTORIES…there is an issue when analyzing schemas that have external table locations if that external location/table doesn’t exist.
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
p. Be sure you have at least one aq_tm_process running:
ORA-20011/ORA-01870 reported when executing dbms_stats.gather_fixed_objects_stats [ID 1083952.1]
SYS@ORCL11> alter system set aq_tm_processes=1 scope=both;
“The Oracle RDBMS was recently upgraded from Oracle10g to Oracle11g.
The problem is caused by unpublished bug 9471411. The database initialization parameter aq_tm_processes has been set to the value of 0 and as a result the the Queue Monitor Coordinator process (QMNC) is not running.
Bug 9471411 is fixed in the future release 12 of Oracle Server.
To resolve the problem, set the database initialization parameter ‘aq_tm_processes’ to a positive integer via
alter system set aq_tm_processes=1 scope=memory;
and remove the aq_tm_processes parameter from the spfile/init.ora so that when the instance next restarts the qmon slave process will be auto-tuned.”
I am trying to figure out why the advice says to do it only in memory and then edit the spfile? I went ahead and wrote to both memory and spfile.
8. POST DATABASE Upgrade Steps
Basic methodology is to keep the database as close to the original previous version for a period of time, recommendation of at least a week before changing initialization parameters to enable the newer 11g features. Don’t turn all features on at once, this is a gradual process of making a single change at a time. Start with this document: SQL PLAN MANAGEMENT [ID 456518.1]
Basic Plan of Attack:
- PreUpgrade Step – Capture, Lock in place as well as back up existing 10g Execution Plans and Statistics
- Upgrade the database to 11g
- Start the applications and allow the end users to log on to the 11g database
- Adjust the database startup initialization parameters once the new version has been stable for at least a week
- Capture new execution plans and new statistics
- Evolve or verify new plans that execute better than the 10g versions
See NOTE: Oracle Performance Diagnostic Guide (OPDG) [ID 390374.1] this is a comprehensive guide to introduce you to the world of tuning – a problem with this document is that it currently doesn’t have very much directly related to 11g. Also look at Managing CBO Stats during an upgrade to 10g or 11g [ID 465787.1]. In particular download the scripts as part of this document (examples of how to collect statistics) in preparation for an upgrade. Also look at this whitepaper – Upgrading from Oracle Database 10g to 11g: What to Expect from the Optimizer. Be aware that I have found mistakes in the whitepaper like the following:
SQL> exec DBMS_STATS.SET_GLOBAL_PREFS(‘PENDING’,’TRUE’);
BEGIN DBMS_STATS.SET_GLOBAL_PREFS(‘PENDING’,’TRUE’); END;
ERROR at line 1:
ORA-20001: Invalid input values for pname
ORA-06512: at “SYS.DBMS_STATS”, line 29847
ORA-06512: at “SYS.DBMS_STATS”, line 30003
ORA-06512: at line 1
I assumed they are actually referring to the initialization parameter optimizer_use_pending_statistics which can be set to true or false.
SQL> execute dbms_stats.publish_pending _stats ();
BEGIN dbms_stats.publish_pending _stats (); END;
ERROR at line 1:
ORA-06550: line 1, column 34:
PLS-00103: Encountered the symbol “_” when expecting one of the following:
:= . ( @ % ;
The symbol “:= was inserted before “_” to continue.
a. Starting Point for Optimizer Upgrade – At this point you should have frozen, saved table statistics at the pre-upgrade version with newly gathered 11g system (data dictionary) and hardware statistics. Initialization Parameters – Left these all at 10.2.0.4 settings unless they were identified as oboslete during the run of the Pre-Upgrade Tool or observed as degrading performance during testing. The most important initialization parameter to leave at previous setting (note that 11g requires a minimum version level for upgrading) is COMPATIBLE. This will allow to downgrade the Oracle Version if need be. It is assumed this is a faster process than an actual database restore.
Investigate the different methods of capturing execution plans and statistics: SQL Tuning Sets for you top SQL statements, Stored Outlines, Unpack existing SQL plan baselines from a staging table as well as any statements that weren’t captured but still stored in the Cursor Cache.
b. Since you have turned off the automatic gathering statistics job (earlier step listed above), the table statistics will be from the previous version. It is assumed at this point the performance was acceptable with these statistics. You have the option of reloading saved statistics from all of the methods listed above if current statistics are not performing well.
Double check that the default setting for the initialization parameter optimizer_use_pending_statistics is false.
c. Now gather table-level statistics – See the documentation or the COEscripts mentioned earlier in note: Managing CBO Stats during an upgrade to 10g or 11g [ID 465787.1]
Something similar to the following:
SYS@ORCL11> exec dbms_stats.gather_schema_stats(ownname=> ‘”SCHEMANAME”‘ , options=> ‘GATHER AUTO’);
Note that the gathering of table statistics will be influenced by the METHOD_OPT parameter. Check out the global values by the following command. Yours will be different than mine…ours is specific to the 3rd party application for our OLTP:
SQL> select dbms_stats.get_param(‘method_opt’) from dual;
FOR ALL INDEXED COLUMNS SIZE 200
Now you can gather statistics for different schemas with different parameters but don’t forget to evaluate the global settings which come into play when gathering statistics with no parameters defined.
d. Now that all statistics have been gathered it is time to adjust more initialization parameters. Turn on the capturing of SQL Plan Baselines. Alternately you can preload SQL Plan Baselines from previously saved execution plans – STS, staging tables, stored outlines, cursor cache. HOW TO MOVE 10gR2 EXECUTION PLANS AND LOAD INTO 11g SPM [ID 801033.1]
SQL@ORCL11>alter system set optimizer_capture_sql_plan_baselines=TRUE scope=both; –time to start capturing 11g execution plans.
SQL@ORCL11>alter system set optimizer_use_sql_plan_baselines=TRUE scope=both; — This allows SQL Plan Management to use the existing 10g statistics (or pre-seeded statistics) as the first plan loaded.
It is time to re-enable the autotask job of gathering statistics, as shown below:
client_name => ‘auto optimizer stats collection’,
operation => NULL,
window_name => NULL);
Also check the initialization parameter optimizer_features_enable if you want to enable the 11g features it must be set to the appropriate level.
e. Now that SQL Plan Management is enabled all new execution plans in the history for a statement are stored as an unaccepted plan. The plan history consists of accepted and unaccepted plans. An unaccepted plan can be unverified (newly found but not verified) or rejected (verified but not demonstrably improved performance).
f. Evolve SQL Plans – Process to evaluate all unverified execution plans for a given statement in the plan history to become either accepted or rejected based on comparison to existing accepted plans.
9. If you need to Back Out the CBO Upgrade
a. Revert initialization parameters
optimizer_use_pending_statistics, optimizer_use_sql_plan_baselines, optimizer_capture_sql_plan_baselines, optimizer_features_enable
b. Restoring statistics history
There is a way to restore the statistics when performance degrades from the historical record of the statistics saved by default since Oracle Database Version 10g. If you used the COEscripts, it has another method of saving statistics.
SYS@ORCL>select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual; –default of 31 days for 11g
SYS@ORCL>select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual; –the oldest statistics that can be restored
SYS@ORCL> select TABLE_NAME, STATS_UPDATE_TIME from dba_tab_stats_history; –indicates when statistics were gathered for each table.
There are several different ways to restore a particular set of statistics:
execute DBMS_STATS.RESTORE_TABLE_STATS (‘owner’,’table’,date);
184.108.40.206 Bugs/Issues encountered
FRM-41214 and REP-501 when connecting to Database 11g using “RUN_REPORT_OBJECT” [ID 469013.1]
Required a combination of setting the initialization parameter – sec_case_sensitive_logon to FALSE and the Oracle Application Server environmental variable (set in default.env or your custom.env file)
This is where you will have a case-sensitive issues when trying to change a password using Oracle Forms (this is because the OAS 10.1.2.0 is actually using a lower-level SQLPLUS client 10.1.x). Most often the error is ORA-1017 invalid username or password. The casesensitive parameter by default is set to zero and is only available in OAS 10.1.2 +.
ORA-979 bugs on 220.127.116.11 [ID 1085766.1]
We encountered this error requiring a change to the initialization parameter – optimizer_features_enable = ‘18.104.22.168’
SQL> SQL> declare
ERROR at line 1:
ORA-00979: not a GROUP BY expression
Keep checking back for any changes to this MOS document.
There are similar bugs in 11.1.x – check out information on the following initialization parameters. We didn’t implement these because they were fixed in the 11.2.x release.