Manual Upgrades
Backup the database before starting the upgrade process. In the event of a failure, you will need this backup to allow you to recreate your starting point.To run the new Pre-Upgrade Information Tool, copy the "preupgrd.sql" and "utluppkg.sql" scripts from the 12c home to a location accessible by the database that needs upgrading. In this example I used a directory under "/tmp".
Make sure the$ mkdir -p /tmp/upgrade $ cp /u01/app/oracle/product/12.1.0.1/db_1/rdbms/admin/preupgrd.sql /tmp/upgrade $ cp /u01/app/oracle/product/12.1.0.1/db_1/rdbms/admin/utluppkg.sql /tmp/upgrade
ORACLE_BASE is set and run the "preupgrd.sql" script from SQL*Plus.
$ export ORACLE_SID=orcl
$ ORAENV_ASK=NO
$ . oraenv
$ ORAENV_ASK=YES
$ cd /tmp/upgrade
$ sqlplus / as sysdba
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/orcl/preupgrade/preupgrade.log
Pre-Upgrade Fixup Script (run in source database environment):
/u01/app/oracle/cfgtoollogs/orcl/preupgrade/preupgrade_fixups.sql
Post-Upgrade Fixup Script (run shortly after upgrade):
/u01/app/oracle/cfgtoollogs/orcl/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
************************************************************
SQL>
The Pre-Upgrade Information Tool produces 3 scripts.- preupgrade.log : The results of all the checks performed. You need to check this to see if it is safe to continue with the upgrade.
- preupgrade_fixups.sql : A fixup script that should be run before the upgrade.
- postupgrade_fixups.sql : A fixup script that should be run after the upgrade.
SQL> @/u01/app/oracle/cfgtoollogs/orcl/preupgrade/preupgrade_fixups.sql
Pre-Upgrade Fixup Script Generated on 2013-07-24 15:00:32 Version: 12.1.0.1 Build: 006
Beginning Pre-Upgrade Fixups...
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
**********************************************************************
Check Tag: DEFAULT_PROCESS_COUNT
Check Summary: Verify min process count is not too low
Fix Summary: Review and increase if needed, your PROCESSES value.
**********************************************************************
Fixup Returned Information:
WARNING: --> Process Count may be too low
Database has a maximum process count of 150 which is lower than the
default value of 300 for this release.
You should update your processes value prior to the upgrade
to a value of at least 300.
For example:
ALTER SYSTEM SET PROCESSES=300 SCOPE=SPFILE
or update your init.ora file.
**********************************************************************
PL/SQL procedure successfully completed.
**********************************************************************
Check Tag: EM_PRESENT
Check Summary: Check if Enterprise Manager is present
Fix Summary: Execute emremove.sql prior to upgrade.
**********************************************************************
Fixup Returned Information:
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.
**********************************************************************
PL/SQL procedure successfully completed.
**********************************************************************
Check Tag: DBMS_LDAP_DEPENDENCIES_EXIST
Check Summary: Check for dependency on DBMS_LDAP package
Fix Summary: Network Objects must be reviewed manually.
**********************************************************************
Fixup Returned Information:
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.
**********************************************************************
PL/SQL procedure successfully completed.
**********************************************************************
Check Tag: AMD_EXISTS
Check Summary: Check to see if AMD is present in the database
Fix Summary: Manually execute ORACLE_HOME/oraolap/admin/catnoamd.sql script to remove OLAP.
**********************************************************************
Fixup Returned Information:
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.
**********************************************************************
PL/SQL procedure successfully completed.
**********************************************************************
[Pre-Upgrade Recommendations]
**********************************************************************
PL/SQL procedure successfully completed.
*****************************************
********* 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 ^^^
PL/SQL procedure successfully completed.
**************************************************
************* Fixup Summary ************
4 fixup routines generated INFORMATIONAL messages that should be reviewed.
PL/SQL procedure successfully completed.
**************** Pre-Upgrade Fixup Script Complete *********************
PL/SQL procedure successfully completed.
SQL>
As you can see, in this case, the suggested changes were as follows.Copy the parameter and password files from the old home to the 12c home.ALTER SYSTEM SET PROCESSES=300 SCOPE=SPFILE; SET ECHO ON; SET SERVEROUTPUT ON; -- emremove.sql scrip located in the 12c home. @/u01/app/oracle/product/12.1.0.1/db_1/rdbms/admin/emremove.sql -- Removing this before the upgrade will result in the errors shown below. -- These errors are not show-stoppers, but if you want a cleaner run through, -- remove this feature after the upgrade. @?/olap/admin/catnoamd.sql EXECUTE dbms_stats.gather_dictionary_stats; -- Shutdown the database. SHUTDOWN IMMEDIATE;
If the "preupgrade.log" file contains references to deprecated initialization parameters, make the suggested changes to the parameters now before continuing.$ cp /u01/app/oracle/product/11.2.0.3/db_1/dbs/spfileorcl.ora /u01/app/oracle/product/12.1.0.1/db_1/dbs $ cp /u01/app/oracle/product/11.2.0.3/db_1/dbs/orapworcl /u01/app/oracle/product/12.1.0.1/db_1/dbs
Edit the "/etc/oratab" file, setting the new
ORACLE_HOME value.Make sure the environment is set to the 12c home.orcl:/u01/app/oracle/product/12.1.0.1/db_1:Y
Start the database in upgrade mode.$ export ORACLE_SID=orcl $ ORAENV_ASK=NO $ . oraenv $ ORAENV_ASK=YES
Run the new Parallel Upgrade Utility (catctl.pl).$ sqlplus / as sysdba SQL> STARTUP UPGRADE; SQL> EXIT;
You can alter the level of parallelism using the "-n" parameter. For example, the following command runs the upgrade with a parallel level of 8.$ cd $ORACLE_HOME/rdbms/admin $ $ORACLE_HOME/perl/bin/perl catctl.pl catupgrd.sql
You can see an example of the output from this utility here.$ cd $ORACLE_HOME/rdbms/admin $ $ORACLE_HOME/perl/bin/perl catctl.pl -n 8 catupgrd.sql
Startup the upgraded database and run the "utlu121s.sql" script to check the summary of the upgrade results.
You can see the upgrade failed for the "Oracle OLAP API" section, which is a feature we removed before the upgrade, so it is OK to continue.$ sqlplus / as sysdba SQL> STARTUP; SQL> @utlu121s.sql . Oracle Database 12.1 Post-Upgrade Status Tool 07-24-2013 17:24:18 . Component Current Version Elapsed Time Name Status Number HH:MM:SS . Oracle Server . UPGRADED 12.1.0.1.0 00:16:48 JServer JAVA Virtual Machine . VALID 12.1.0.1.0 00:04:47 Oracle Workspace Manager . VALID 12.1.0.1.0 00:01:17 OLAP Analytic Workspace . VALID 12.1.0.1.0 00:00:53 Oracle OLAP API . SP2-0310: unable to open file "xoqsys.sql" . ORA-01917: user or role "OLAPSYS" does not exist . ORA-00942: table or view does not exist . ORA-00942: table or view does not exist . ORA-00942: table or view does not exist . ORA-00942: table or view does not exist . ORA-00942: table or view does not exist . VALID 12.1.0.1.0 00:00:46 Oracle XDK . VALID 12.1.0.1.0 00:00:48 Oracle Text . VALID 12.1.0.1.0 00:01:07 Oracle XML Database . VALID 12.1.0.1.0 00:04:35 Oracle Database Java Packages . VALID 12.1.0.1.0 00:00:22 Oracle Multimedia . VALID 12.1.0.1.0 00:02:42 Spatial . VALID 12.1.0.1.0 00:06:21 Oracle Application Express . VALID 4.2.0.00.27 00:25:28 Final Actions . 00:02:47 Total Upgrade Time: 01:09:24 PL/SQL procedure successfully completed. SQL>
If we had no errors, the "catuppst.sql" script would have been run as part of the upgrade. Since we did have errors, we need to run it manually.
If the "postupgrade_fixups.sql" file contained any recommendations, run it now.SQL> @catuppst.sql
The following items are not essential, but the upgrade manual suggests it is a best practice to run them.SQL> @/u01/app/oracle/cfgtoollogs/orcl/preupgrade/postupgrade_fixups.sql
The upgraded database is now available.-- The following item is probably included in your postupgrade_fixups.sql script. EXECUTE DBMS_STATS.gather_fixed_objects_stats; -- Recompile invalid objects. @utlrp.sql -- Check for newly invalid objects. @utluiobj.sql -- Run again to check the final outcome of the upgrade. @utlu121s.sql
Remember to alter any script or profile files that contain references to the$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.1.0 Production on Wed Jul 24 18:17:03 2013 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> SELECT name, open_mode FROM v$database; NAME OPEN_MODE --------- -------------------- ORCL READ WRITE SQL>
ORACLE_HOME environment variable.Export Import
As with previous releases, the export/import process can be used to transfer data between versions. Export the data using theexpdp utility from the source version and import using the 12c version of impdp.The basic export/import method allows you to move data directly to a pluggable database.
Transport Database
The option of an upgrade via a transport database is possible for 11.2.0.3 or later. The process is broken down into the following basic steps.- Create an empty 12c database.
- Export the 11.2.0.3+ database using the "
FULL=Y TRANSPORTABLE=ALWAYS VERSION=12" parameters. - Copy the datafiles into the correct location. If switching between platforms, the datafiles must be converted using the RMAN
CONVERTcommand, or theDBMS_FILE_TRANSFERpackage. - Import into the 12c database.
Create a new 12c database using the DBCA.
My source database was called "orcl", so I created a clean 12c database called "orcl12c", without the sample schemas. Make sure the character sets match.$ export ORACLE_HOME=/u01/app/oracle/product/12.1.0.1/db_1 $ export PATH=$ORACLE_HOME/bin:$PATH $ dbca
With the 12c database in place, we must prepare the 11g database for the export. I was testing this on a clean installation of 11.2.0.3, so I had to create a directory object for the export, as well as a little dummy data.
Export the database, using the "$ export ORACLE_SID=orcl $ ORAENV_ASK=NO $ . oraenv $ ORAENV_ASK=YES $ sqlplus / as sysdba -- Create a directory to export to. -- This directory should be created in the 12c database too. CREATE OR REPLACE DIRECTORY TEMP_DIR AS '/tmp/'; -- Create a new tablespace and test user with some dummy data. CREATE TABLESPACE data_ts DATAFILE '/u01/app/oracle/oradata/orcl/data01.dbf' SIZE 1M AUTOEXTEND ON NEXT 1M; CREATE user test IDENTIFIED BY test DEFAULT TABLESPACE data_ts TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON data_ts; GRANT CREATE SESSION, CREATE TABLE TO test; CREATE TABLE test.t1 AS SELECT * FROM dba_objects; -- Make the tablespaces you want to transport read-only. ALTER TABLESPACE data_ts READ ONLY; ALTER TABLESPACE example READ ONLY; EXIT
FULL=Y TRANSPORTABLE=ALWAYS VERSION=12"
parameters. I excluded the "USER" tablespace as it was already present
in the 12c database and there was nothing I wanted to transfer in that
tablespace.The export contains just metadata for the data tablespaces, but includes the normal dump contents of the SYSTEM and SYSAUX tablespaces.$ expdp system full=Y transportable=always version=12 directory=TEMP_DIR \ dumpfile=orcl.dmp logfile=expdporcl.log exclude=TABLESPACE:\"= \'USERS\'\"
Copy the datafiles to the correct location for the 12c database.
At this point it probably makes sense to switch the tablespaces back to read-write and shutdown the 11g database.$ cp /u01/app/oracle/oradata/orcl/data01.dbf /u01/app/oracle/oradata/orcl12c $ cp /u01/app/oracle/oradata/orcl/example01.dbf /u01/app/oracle/oradata/orcl12c
We can now import the dump file into the 12c database. This of course assumes you have already created the directory object in the 12c database, as mentioned previously.$ sqlplus / as sysdba ALTER TABLESPACE data_ts READ WRITE; ALTER TABLESPACE example READ WRITE; SHUTDOWN IMMEDIATE; EXIT;
$ export ORACLE_SID=orcl12c
$ ORAENV_ASK=NO
$ . oraenv
$ ORAENV_ASK=YES
$ impdp system full=Y directory=TEMP_DIR dumpfile=orcl.dmp logfile=impdporcl.log \
transport_datafiles= \
'/u01/app/oracle/oradata/orcl12c/data01.dbf', \
'/u01/app/oracle/oradata/orcl12c/example01.dbf'
The 12c database now contains all the data from the original database.
$ sqlplus test/test
SQL*Plus: Release 12.1.0.1.0 Production on Wed Jul 24 12:06:01 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Last Successful login time: Wed Jul 24 2013 12:05:38 +01:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> SELECT COUNT(*) FROM t1;
COUNT(*)
----------
75213
SQL>
There are a number of variations on this process, including changing platforms using the RMAN CONVERT command, or the DBMS_FILE_TRANSFER package. These are covered in documentation.For more information see:
