So here i am: stoforlong ( meaning ‘Stade Toulousain For Long’ a famous French Rugby team that I’m fan of) from blog.easyteam to arkzyod, a new enthusiastic challenge. This is my first post directly in English as the previous one was a translation of episode 1, and i hope i will be clear and understandable !
The series is about migrating to Database 12c , and the idea is to fly to it step by step , story by story , beginning by the setup we’ve seen during episode 1 . We have now one OEL6 server with ora112 database in release 11.2.0.3 ready to go and 12.1.0.1 binaries in place. Today will see the prepare to upgrade stage, and first we’re going to a snapshot of the performance of our application. The only way to answer to the where you come from question. As we don’t have a real application, we’ll use our old friend Swingbench from Dominic Giles. The Order Entries bench based on the OE schema that ships with the database is the one I choice for my reference.
1. Reference
For the complete setup of the test you could refer , to an old post i made several weeks ago here but it’s still in french and i will resume it here :
- Download last release from there. Unzip it to a dedicated folder. The release of the product is now 2.4 dated march 2010, its quite a long time, but it’s still stable and accurate for the tests I’ve done with Windows 7 client and java 1.6.0.4.
- Dowload and install JDK 1.6 from oracle
- Run oewizard.bat from winbin sub directory
- Run swingbench from winbin sub directory
Parameters of the Order Entry bench :
– Number of Users :15
– Minimum Delay beetween transaction (MinimumThinkTime): 100ms
– Maximum Delay beetween transaction (MaximumThinkTime): 200ms
– Logon Delay : 0
– Logon Group : 1
– Benchmark run time : 15 mn
– Checkbox ‘Collect Database Statistics’ checked
See table 1 for the results
Table 1:
| TPS | Max TPM | Customer Registration | Browse Products | Order Products | Process Orders | Browse Orders | Remarks |
| 14.25 | 917 | 20 | 17 | 70 | 30 | 36 | July 3th 11:04 |
- TPS : Transaction per second
- Max TPM : Maximal Transaction per minute
- Average for each items (ms)
2. Migration Paths
Our choice there is simple, the source database is in the latest patchset so we can used dbua from Oracle to do the trick. But that’ll certainly not be the case at your site, and you need to choose the right path, for some help about that, look at this white paper where you can read table : ‘Direct Upgrade to Oracle Database 12c’ and some more interesting thing :
| Source release | Source Patch Set | Direct Upgrade Supported |
| Oracle Database 11g Release 2 | 11.2.0.2 and later | Yes |
|
— |
11.2.0.1 | No. Use another method |
| Oracle Database 11g Release 1 | 11.1.0.7 | Yes |
|
— |
11.1.0.6 | No. Use another method |
| Oracle Database 10g | 10.2.0.5 | Yes |
|
— |
10.2.0.4 and earlier | No. Use another method |
| Oracle Database 9i and earlier | All | No. Use another method |
For other migration paths, next episodes will follow, particularly the one i loved regarding full transportable export/import.
Remind that this season we will not cover Plugable databases and containers but be patient other spots from Arkzyod will talk about it.
3. Pre upgrade check changes
Several changes in release 12c for this part :
utlu121i.sql doesn’t exist anymore and is replaced by preupgrd.sql, the file is still there but if you look at it, you will see a place holder for the new one :
Rem Rem $Header: rdbms/admin/utlu121i.sql /main/24 2012/08/22 07:32:07 bmccarth Exp $ Rem Rem utlu121i.sql Rem Rem Copyright (c) 2010, 2012, Oracle and/or its affiliates. Rem All rights reserved. Rem Rem NAME Rem utlu121i.sql - UTiLity Upgrade Information Rem Rem DESCRIPTION Rem Provide information to users of location of new pre-upgrade Rem information tool. Rem Rem NOTES Rem Functionality replaced by preupgrd.sql script. Rem Rem MODIFIED (MM/DD/YY) Rem bmccarth 07/10/12 - Replace with DOC statements explaining how to Rem use the new utility. Rem DOC ############################################################################ ############################################################################ Note that the new preupgrd.sql script replaces the utlu121i.sql script and earlier versions of the Pre-Upgrade Information Tool. The Pre-Upgrade Information Tool, which now consists of preupgrd.sql and utluppkg.sql. 1. Make sure preupgrd.sql and utluppkg.sql (located in the admin directory of the new installation of Oracle Database 12.1) are accessible while connected to your source database, which is the database to be upgraded 2. Connect to your source database using an account with DBA privileges and execute the preupgrd.sql script. For more information on the Pre-Upgrade Information Tool, see the Oracle Database Upgrade Guide. ############################################################################ ############################################################################ #
You need both files preupgrd.sql and utluppkg.sql located on your path when the script is executed. Both files are not wrapped and are readable if your little curious, be warn of the 100000 lines of code !
The new features are the preupgrade_fixups.sql and postupgrade_fixups.sql which are generated dynamically during the run depending of your database configuration. They will help a lot in what to do exactly before and after the upgrade, making your life easier (always what we need).
4. Running pre upgrade check
Once connected in source database environment ora112 (11.2.0.3 binaries) , changed to upgrade scripts localization and check files presence :
[oracle@easydirtech ~]$ cd /u01/app/oracle/product/12.1.0.1/rdbms/admin [oracle@easydirtech admin]$ ls pre* preupgrd.sql [oracle@easydirtech admin]$ ls utluppkg* utluppkg.sql
Database opened and ready for use, run the upgrade script with SQL*Plus as SYS user :
[oracle@easydirtech admin]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 25 06:35:36 2013 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> @preupgrd.sql Loading Pre-Upgrade Package... Executing Pre-Upgrade Checks... Pre-Upgrade Checks Complete. ************************************************************ Results of the checks are located at: /u01/app/oracle/cfgtoollogs/ora112/preupgrade/preupgrade.log Pre-Upgrade Fixup Script (run in source database environment): /u01/app/oracle/cfgtoollogs/ora112/preupgrade/preupgrade_fixups.sql Post-Upgrade Fixup Script (run shortly after upgrade): /u01/app/oracle/cfgtoollogs/ora112/preupgrade/postupgrade_fixups.sql ************************************************************ Fixup scripts must be reviewed prior to being executed. ************************************************************ ************************************************************ ====>> USER ACTION REQUIRED <<==== ************************************************************ The following are *** ERROR LEVEL CONDITIONS *** that must be addressed prior to attempting your upgrade. Failure to do so will result in a failed upgrade. You MUST resolve the above errors prior to upgrade ************************************************************
Files created during script execution :
a. Log file
[oracle@easydirtech admin]$ cat /u01/app/oracle/cfgtoollogs/ora112/preupgrade/preupgrade.log Oracle Database Pre-Upgrade Information Tool 07-25-2013 06:36:26 Script Version: 12.1.0.1.0 Build: 006 ********************************************************************** Database Name: ORA112 Version: 11.2.0.3.0 Compatible: 11.2.0.0.0 Blocksize: 8192 Platform: Linux x86 64-bit Timezone file: V14 ********************************************************************** [Renamed Parameters] [No Renamed Parameters in use] **********************************************************************
First part dedicated to parameter’s change. The full list could be find upgrade documentation chapter 8 or you can generate it after the upgrade with ‘SELECT NAME,VALUE FROM V$PARAMETER WHERE ISDEPREECATED=’TRUE’.
The only real new one is SEC_CASE_SENSITIVE_LOGON (The others seen were already deprecated in release 11.2) .Deprecated but still usable for descendant compatibility, this parameter and particularly the security needs to be review with that release as several add on and changes were introduced in 12c , see Oracle® Database Security Guide 12c Release 1 (12.1) for details.
********************************************************************** [Obsolete/Deprecated Parameters] --> max_enabled_roles 10.1 DESUPPORTED --> cursor_space_for_time 11.1 DESUPPORTED --> cursor_space_for_time 11.2 DESUPPORTED --> sec_case_sensitive_logon 12.1 DESUPPORTED [Changes required in Oracle Database init.ora or spfile] **********************************************************************
Remind that if you used deprecated parameter, you will see this in the alert log file of your database during startup :
Deprecated system parameters with specified values: max_enabled_roles cursor_space_for_time End of deprecated system parameter listing
As in my case I setup
max_enabled_roles = 120
cursor_space_for_time =true
sec_case_sensitive_logon = true
Following the output , you had all installed components, actual state (hopefully VALID) and the need for upgrade :
[Component List] ********************************************************************** --> Oracle Catalog Views [upgrade] VALID --> Oracle Packages and Types [upgrade] VALID --> JServer JAVA Virtual Machine [upgrade] VALID --> Oracle XDK for Java [upgrade] VALID --> Oracle Workspace Manager [upgrade] VALID --> OLAP Analytic Workspace [upgrade] VALID --> Oracle Enterprise Manager Repository [upgrade] VALID --> Oracle Text [upgrade] VALID --> Oracle XML Database [upgrade] VALID --> Oracle Java Packages [upgrade] VALID --> Oracle Multimedia [upgrade] VALID --> Oracle Spatial [upgrade] VALID --> Expression Filter [upgrade] VALID --> Rule Manager [upgrade] VALID --> Oracle Application Express [upgrade] VALID --> Oracle OLAP API [upgrade] VALID **********************************************************************
Next part show space requirements and recommendations regarding tablespaces :
[Tablespaces] ********************************************************************** --> SYSTEM tablespace is adequate for the upgrade. minimum required size: 1228 MB --> SYSAUX tablespace is adequate for the upgrade. minimum required size: 1491 MB --> UNDOTBS1 tablespace is adequate for the upgrade. minimum required size: 400 MB --> TEMP tablespace is adequate for the upgrade. minimum required size: 60 MB --> EXAMPLE tablespace is adequate for the upgrade. minimum required size: 310 MB [No adjustments recommended] **********************************************************************
follow up by tests results and corrective actions if needed :
********************************************************************** [Pre-Upgrade Checks] ********************************************************************** WARNING: --> Enterprise Manager Database Control repository found in the database In Oracle Database 12c, Database Control is removed during the upgrade. To save time during the Upgrade, this action can be done prior to upgrading using the following steps after copying rdbms/admin/emremove.sql from the new Oracle home - Stop EM Database Control: $> emctl stop dbconsole - Connect to the Database using the SYS account AS SYSDBA: SET ECHO ON; SET SERVEROUTPUT ON; @emremove.sql Without the set echo and serveroutput commands you will not be able to follow the progress of the script. WARNING: --> Existing DBMS_LDAP dependent objects Database contains schemas with objects dependent on DBMS_LDAP package. Refer to the Upgrade Guide for instructions to configure Network ACLs. USER APEX_030200 has dependent objects. INFORMATION: --> OLAP Catalog(AMD) exists in database Starting with Oracle Database 12c, OLAP is desupported. If you are not using the OLAP Catalog component and want to remove it, then execute the ORACLE_HOME/oraolap/admin/catnoamd.sql script before or after the upgrade. INFORMATION: --> Older Timezone in use Database is using a time zone file older than version 18. After the upgrade, it is recommended that DBMS_DST package be used to upgrade the 11.2.0.3.0 database time zone version to the latest version which comes with the new release. Please refer to My Oracle Support note number 977512.1 for details. Then Pre and Post upgrade recommendations(the one Oracle made, really need to follow if you want to stay supported) ********************************************************************** [Pre-Upgrade Recommendations] ********************************************************************** ***************************************** ********* Dictionary Statistics ********* ***************************************** Please gather dictionary statistics 24 hours prior to upgrading the database. To gather dictionary statistics execute the following command while connected as SYSDBA: EXECUTE dbms_stats.gather_dictionary_stats; ^^^ MANUAL ACTION SUGGESTED ^^^ ********************************************************************** [Post-Upgrade Recommendations] ******************************************************************** ***************************************** ******** Fixed Object Statistics ******** ***************************************** Please create stats on fixed objects two weeks after the upgrade using the command: EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS; ^^^ MANUAL ACTION SUGGESTED ^^^ **********************************************************************
And for the last part , summary and last recommendations :
************ Summary ************ 0 ERRORS exist in your database. 2 WARNINGS that Oracle suggests are addressed to improve database performance. 2 INFORMATIONAL messages that should be reviewed prior to your upgrade. After your database is upgraded and open in normal mode you must run rdbms/admin/catuppst.sql which executes several required tasks and completes the upgrade process. You should follow that with the execution of rdbms/admin/utlrp.sql, and a comparison of invalid objects before and after the upgrade using rdbms/admin/utluiobj.sql If needed you may want to upgrade your timezone data using the process described in My Oracle Support note 977512.1 ***********************************
b. Fixup file , to be executed before the upgrade :
This file should contains all the actions needed before the upgrade, changes that couldn’t be made by the script are marked
-- Action: ^^^ MANUAL ACTION REQUIRED ^^^
And what ! in my case all the actions needed to be done are manually , I’m a little bit disappointed, the script is a little bit useless, I’ won’t run it and just made the required actions :
[oracle@easydirtech admin]$ cat /u01/app/oracle/cfgtoollogs/ora112/preupgrade/preupgrade_fixups.sql
SET ECHO OFF SERVEROUTPUT ON FORMAT WRAPPED TAB OFF LINESIZE 750;
BEGIN
dbms_output.put_line ('Pre-Upgrade Fixup Script Generated on 2013-07-31 04:52:03 Version: 12.1.0.1 Build: 006');
dbms_output.put_line ('Beginning Pre-Upgrade Fixups...');
END;
/
BEGIN
dbms_preup.clear_run_flag(TRUE);
END;
/
BEGIN
-- ***************** Fixup Details ***********************************
-- Name: DEFAULT_PROCESS_COUNT
-- Description: Verify min process count is not too low
-- Severity: Warning
-- Action: ^^^ MANUAL ACTION REQUIRED ^^^
-- Fix Summary:
-- Review and increase if needed, your PROCESSES value.
dbms_preup.run_fixup_and_report('DEFAULT_PROCESS_COUNT');
END;
/
BEGIN
-- ***************** Fixup Details ***********************************
-- Name: DBMS_LDAP_DEPENDENCIES_EXIST
-- Description: Check for dependency on DBMS_LDAP package
-- Severity: Warning
-- Action: ^^^ MANUAL ACTION REQUIRED ^^^
-- Fix Summary:
-- Network Objects must be reviewed manually.
dbms_preup.run_fixup_and_report('DBMS_LDAP_DEPENDENCIES_EXIST');
END;
/
BEGIN
-- ***************** Fixup Details ***********************************
-- Name: AMD_EXISTS
-- Description: Check to see if AMD is present in the database
-- Severity: Informational
-- Action: ^^^ MANUAL ACTION REQUIRED ^^^
-- Fix Summary:
-- Manually execute ORACLE_HOME/oraolap/admin/catnoamd.sql script to remove OLAP.
dbms_preup.run_fixup_and_report('AMD_EXISTS');
END;
/
BEGIN
dbms_output.put_line ('');
dbms_output.put_line ('**********************************************************************');
dbms_output.put_line (' [Pre-Upgrade Recommendations]');
dbms_output.put_line ('**********************************************************************');
dbms_output.put_line ('');
END;
/
BEGIN
dbms_output.put_line (' *****************************************');
dbms_output.put_line (' ********* Dictionary Statistics *********');
dbms_output.put_line (' *****************************************');
dbms_output.put_line ('');
dbms_output.put_line ('Please gather dictionary statistics 24 hours prior to');
dbms_output.put_line ('upgrading the database.');
dbms_output.put_line ('To gather dictionary statistics execute the following command');
dbms_output.put_line ('while connected as SYSDBA:');
dbms_output.put_line (' EXECUTE dbms_stats.gather_dictionary_stats;');
dbms_output.put_line ('');
dbms_output.put_line ('^^^ MANUAL ACTION SUGGESTED ^^^');
dbms_output.put_line ('');
END;
/
BEGIN dbms_preup.fixup_summary(TRUE); END;
/
BEGIN
dbms_output.put_line ('**************** Pre-Upgrade Fixup Script Complete *********************');
END;
/
c. Post migration file
Once again nothing to do with the script, all the recommendations and suggestions need to be done manually :
[oracle@easydirtech admin]$ cat /u01/app/oracle/cfgtoollogs/ora112/preupgrade/preupgrade_fixups.sql
SET ECHO OFF SERVEROUTPUT ON FORMAT WRAPPED TAB OFF LINESIZE 750;
BEGIN
dbms_output.put_line ('Pre-Upgrade Fixup Script Generated on 2013-07-31 04:52:03 Version: 12.1.0.1 Build: 006');
dbms_output.put_line ('Beginning Pre-Upgrade Fixups...');
END;
/
BEGIN
dbms_preup.clear_run_flag(TRUE);
END;
/
BEGIN
-- ***************** Fixup Details ***********************************
-- Name: DEFAULT_PROCESS_COUNT
-- Description: Verify min process count is not too low
-- Severity: Warning
-- Action: ^^^ MANUAL ACTION REQUIRED ^^^
-- Fix Summary:
-- Review and increase if needed, your PROCESSES value.
dbms_preup.run_fixup_and_report('DEFAULT_PROCESS_COUNT');
END;
/
BEGIN
-- ***************** Fixup Details ***********************************
-- Name: DBMS_LDAP_DEPENDENCIES_EXIST
-- Description: Check for dependency on DBMS_LDAP package
-- Severity: Warning
-- Action: ^^^ MANUAL ACTION REQUIRED ^^^
-- Fix Summary:
-- Network Objects must be reviewed manually.
dbms_preup.run_fixup_and_report('DBMS_LDAP_DEPENDENCIES_EXIST');
END;
/
BEGIN
-- ***************** Fixup Details ***********************************
-- Name: AMD_EXISTS
-- Description: Check to see if AMD is present in the database
-- Severity: Informational
-- Action: ^^^ MANUAL ACTION REQUIRED ^^^
-- Fix Summary:
-- Manually execute ORACLE_HOME/oraolap/admin/catnoamd.sql script to remove OLAP.
dbms_preup.run_fixup_and_report('AMD_EXISTS');
END;
/
BEGIN
dbms_output.put_line ('');
dbms_output.put_line ('**********************************************************************');
dbms_output.put_line (' [Pre-Upgrade Recommendations]');
dbms_output.put_line ('**********************************************************************');
dbms_output.put_line ('');
END;
/
BEGIN
dbms_output.put_line (' *****************************************');
dbms_output.put_line (' ********* Dictionary Statistics *********');
dbms_output.put_line (' *****************************************');
dbms_output.put_line ('');
dbms_output.put_line ('Please gather dictionary statistics 24 hours prior to');
dbms_output.put_line ('upgrading the database.');
dbms_output.put_line ('To gather dictionary statistics execute the following command');
dbms_output.put_line ('while connected as SYSDBA:');
dbms_output.put_line (' EXECUTE dbms_stats.gather_dictionary_stats;');
dbms_output.put_line ('');
dbms_output.put_line ('^^^ MANUAL ACTION SUGGESTED ^^^');
dbms_output.put_line ('');
END;
/
BEGIN dbms_preup.fixup_summary(TRUE); END;
/
BEGIN
dbms_output.put_line ('**************** Pre-Upgrade Fixup Script Complete *********************');
END;
/
4. Manual actions required and suggested
So for my database to be clean and able to be ugraded without problems i need to :
- made some parameters change
- remove Dbconsole schema and stuff
- Check ACL dependencies for Network package
- Remove OLAP MDA
Lets go then.
a. Parameters
I removed deprecated parameter definition:
SQL> alter system reset max_enabled_roles ; System altered. SQL> alter system reset cursor_space_for_time ; System altered. SQL> alter system reset sec_case_sensitive_logon;
Remember the reset command leave the parameter with his default value, if possible dynamically, if not storing the value in the spfile needed a database bounce.
And change setting for the process number :
SQL> alter system set processes=300 scope=spfile ; System altered.
That done, I bounced my database ora121 to be ready for the next step.
b Dbconsole removed
The Dbconsole application is no more used in 12c , instead you’ll have ‘Database Application Express’ an APEX application. It’s then better to remove all the Dbconsole parts from your database before upgrading, less time and resources used during the process. Think that all your customs stuff like scheduled tasks will be lost, I didn’t see any procedure or trick to retrieve them.
To remove it, you could :
Follow the steps we described there in french except the last one!
Use emremove.sql from 12c ORACLE_HOME/rdbms/admin
I used it. It’s straight forward and quick :
$emctl stop dbconsole [oracle@easydirtech ~]$ cd /u01/app/oracle/product/11.2.0.3/rdbms/admin [oracle@easydirtech admin]$ sqlplus / as sysdba SQL> set serveroutput on SQL> set echo on SQL> @emremove.sql SQL> Rem SQL> Rem $Header: rdbms/admin/emremove.sql /main/2 2012/07/27 01:19:53 spramani Exp $ SQL> Rem SQL> Rem emremove.sql SQL> Rem SQL> Rem Copyright (c) 2012, Oracle and/or its affiliates. All rights reserved. SQL> Rem SQL> Rem NAME SQL> Rem emremove.sql - This script removes EM Schema from RDBMS …. Finished phase 5 Starting phase 6 : Dropping Oracle Enterprise Manager related other roles ... Finished phase 6 The Oracle Enterprise Manager related schemas and objects are dropped. Do the manual steps to shutdown the DB Control if not done before running this script and then delete the DB Control configuration files PL/SQL procedure successfully completed.
I personally leave the configuration files under my old ORACLE_HOME as i will completely removed it at the full end of the process.
c. ACL dependencies for network package
Since release 11.2 you needs ACLs, defined in an XML file, to be able to use network packages like DBMS_LDAP, which was used by APEX in our source database. To do that you need to :
- Create ACLs using DBMS_NETWORK_ACL_ADMIN.CREATE_ACL()
- Assign targets to ACLs using DBMS_NETWORK_ACL_ADMIN_ASSIGN_ACL()
We don’t really use APEX in our case and will leave this for a later time. If you need an example go to this post.
d. Remove OLAP metadata
The metadata for CWM (Common Warehouse Metamodel) is no more supported with database 12c , nor OLAP is still there , but if you were using it , it’s time to switch to another solution. If not use the catnoamd.sql script to remove it from the dictionary before the upgrade.
This script is there in 11.2 and in 12.1 you can use the one you prefer , the older could be the better, I choose the later one :
[oracle@easydirtech admin]$ . oraenv ORACLE_SID = [ora112] ? ora112 The Oracle base remains unchanged with value /u01/app/oracle [oracle@easydirtech admin]$ cd /u01/app/oracle/product/12.1.0.1/olap/admin [oracle@easydirtech admin]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Wed Jul 31 10:23:31 2013 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> @catnoamd Synonym dropped. … PL/SQL procedure successfully completed. 1 row deleted. SQL> select * from dba_registry where COMP_ID='AMD' ; no rows selected
e. Dictionary stats
To speed up the upgrade, run the statistics on the dictionary , this is simple and quick but could help a lot, don’t miss it :
[oracle@easydirtech bin]$ time sqlplus /nolog <<! > connect / as sysdba > EXECUTE dbms_stats.gather_dictionary_stats; > ! SQL*Plus: Release 11.2.0.3.0 Production on Wed Jul 31 10:45:32 2013 Copyright (c) 1982, 2011, Oracle. All rights reserved. SQL> Connected. SQL> PL/SQL procedure successfully completed. SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options real 0m19.655s user 0m0.011s sys 0m0.024s
6. Backup
Don’t forget this step, it’s not because we’re flying to the sky that we are not rigorous and serious. Here, i need a backup to be able to replay the upgrade using several configurations and tools and i used a simple cp of the datafiles for that. But at your site use your standard procedure.
Everything is ready ! Our first move, coming next, will be with dbua in silent mode. Stay tuned…
