4 About Upgrading a Database

The specific information about upgrading your existing Oracle database to Oracle Database 12c Release 1 on Fujitsu BS2000/OSD systems is discussed in this chapter.

Before you perform an upgrade, you must be familiar about upgrade preparation, space and backup requirements, release differences, handling TIMESTAMP WITH TIME ZONE data type, and other database upgrade concepts.

It is assumed that you have correctly installed Oracle Database 12c Release 1 as explained in “Oracle Database Installation and Deinstallation.”.This chapter describes upgrading a database from Oracle Database 11g Release 2 (11.2) to Oracle Database 12c Release 1 (12.1). The upgrade is done both in the normal BS2000 environment and in the POSIX shell. Oracle recommends that you be familiar with both these environments.

This chapter includes the following topics:

Related Topics

Oracle Database Upgrade Guide

Performing Preupgrade Procedures

You must analyze your database before upgrading it to the new release. To analyze, run the preupgrd.sql Pre-Upgrade Information Tool from the environment of the database that you want to upgrade. Running the Pre-Upgrade Information Tool provides information about any issues that needs to be fixed.

You must run the preupgrd.sql tool in the environment of the source database. For example in the Oracle Database 11g Release 2 environment. The database must be up and running. For running preupgrd.sql, you must start SQL*Plus in the POSIX shell.

The Pre-Upgrade Information Tool generates log files and fixup scripts that you can run, to resolve issues that are flagged in the source database. These log files and fixup scripts are generated in the POSIX file system. If ORACLE_BASE is defined, then the generated scripts and log files are created in the $ORACLE_BASE/cfgtoollogs directory. If ORACLE_BASE is not defined, then the generated scripts and log files are created in the $ORACLE_HOME/cfgtoollogs directory.

Perform the following steps in the environment of the source database:

Log in to the DBA user ID of the source database.
To avoid being prompted for many overflow acknowledgements on your screen, enter:

/MODIFY-TERMINAL-OPTIONS OVERFLOW-CONTROL=*NO-CONTROL
Change to the POSIX shell:

/START-POSIX-SHELL
Copy preupgrd.sql and utluppkg.sql from the rdbms/admin directory of the new Oracle home where you installed Oracle Database 12c to a directory that is accessible when you connect to your source database, which is the database to be upgraded. Use the following commands:
```
$ cp oracle_home_path_12c/rdbms/admin/preupgrd.sql preupgrd.sql
$ cp oracle_home_path_12c/rdbms/admin/utluppkg.sql utluppkg.sql
```
Before you run SQL*Plus in the POSIX shell, set the Oracle environment variables ORACLE_HOME and ORACLE_BASE by executing the .profile.oracle file:
$ . oracle_home_path_11g/.profile.oracle

You must also set the ORACLE_SID environment variable to the sid of your Oracle Database instance. Use the following commands:
```
$ ORACLE_SID=sid
$ export ORACLE_SID
```
Note:
You must remember that the variable BGJPAR might not be defined. Oracle recommends to set the variable BGJPAR according to the value you find in the corresponding ORAENV file , sid.P.ORAENV.

Run the preupgrd.sql Pre-Upgrade Information Tool:

$ sqlplus /nolog
SQL> connect / as sysdba
SQL> @preupgrd.sql 
SQL> exit

View and read through the resulting generated fixup scripts and log file, which are located in:
- $ORACLE_BASE/cfgtoollogs/db_unique_name/preupgrade directory if ORACLE_BASE is defined.
- $ORACLE_HOME/cfgtoollogs/db_unique_name/preupgrade directory if ORACLE_BASE is not defined.
After you have reviewed the scripts, Oracle recommends that you execute preupgrade_fixups.sql on the source database. The preupgrade_fixups.sql script attempts to resolve issues reported by the preupgrade process. You must manually resolve the issues that cannot be resolved automatically by a fixup script.

Shutdown the database and exit SQL*Plus. Use the following commands:

$ sqlplus /nolog
SQL> connect / as sysdba
SQL> shutdown immediate;
SQL> exit

Exit the POSIX shell:

$ exit

Note:
Oracle recommends that you back up your Oracle database after running the Pre-Upgrade Information Tool and shutting down the database.

Performing Upgrade Procedures

This topic describes the new upgrade method using the catctl.pl Perl script. As Perl is only supported on the POSIX environment, you must change to the POSIX shell for some of the following steps.

All the steps in this phase must be performed in the new Oracle Database 12c environment. For some steps you have to change to the POSIX shell.

Note:

Earlier releases of Oracle Database used the catupgrd.sql upgrade utility to process the upgrade. Oracle strongly recommends that starting with Oracle Database 12c release, you use the catctl.pl parallel upgrade utility. This replaces the catupgrd.sql utility and provides both parallel processing mode and serial mode.

Perform the following upgrade steps:

Log in to the DBA user ID of the database to be upgraded.
To avoid being prompted for many overflow acknowledgements on your screen, enter:

/MODIFY-TERMINAL-OPTIONS OVERFLOW-CONTROL=*NO-CONTROL
Enter the following command to create an Oracle Database 12c Release 1 INIT.ORA file and an Oracle Database 12c Release 1 ORAENV file. Save the original files under the suffix .OLD. Use the following commands:
```
/CALL-PROCEDURE 
$ORACINST.INSTALL.P.DBA,(sid,jobclass,UPDATE=YES)
```
Modify the newly created files according to your requirements. For example, change the values of PROCESSES or DB_CACHE_SIZE in the INIT.ORA file.
Set the parameters in the INIT.ORA file as recommended by the Pre-Upgrade Information Tool. Ensure that the COMPATIBLE initialization parameter is explicitly set to 11.2.0 or higher.
Change to the POSIX shell. Use the following command:

/START-POSIX-SHELL
Before you run SQL*Plus in the POSIX shell you must set some environment variables. For example, ORACLE_HOME and ORACLE_BASE. To set these variables, run the .profile.oracle file that is located in the ORACLE_HOME directory of the Oracle Database 12c software installation:

$ . oracle_home_path_12c/.profile.oracle
Set the ORACLE_SID environment variable to the sid of your Oracle Database instance:
```
$ ORACLE_SID=sid
$ export ORACLE_SID
```
Note:
Ensure that you define the BGJPAR variable. Oracle recommends to set the BGJPAR variable to the corresponding value in the ORAENV file, sid.P.ORAENV.

Start up the instance in upgrade mode as follows:

$ sqlplus /nolog
SQL> connect / as sysdba
SQL> startup upgrade
SQL> exit

Run the Perl script for upgrade as follows:
```
$ cd $ORACLE_HOME/rdbms/admin
$ perl catctl.pl -l $HOME/logs catupgrd.sql
```
The –l option allows you to specify the directory where you want to write the upgrade log files.
See Also:
Oracle Database Upgrade Guide for other options of the Perl script, catctl.pl.

The upgraded database is shut down after running catctl.pl.
Restart the instance to reinitialize the system parameters for normal operation:
```
$ sqlplus /nolog
SQL> connect / as sysdba
SQL> startup
```
Run utlu121s.sql, the Post-Upgrade Status Tool to display a summary of the upgrade results:

SQL> @utlu121s.sql

If the utlu121s.sql script returns errors, or shows components that do not contain the most recent release, or do not have the UPGRADED status, then refer to Oracle Database Upgrade Guide for troubleshooting.

Note:
When the upgrade is complete without any errors, the status of the database is set to UPGRADED. This behavior differs from earlier releases where the status of the database was set to VALID after upgrading. The UPGRADED status indicates that the data dictionary has been loaded without any errors.
Running the utlrp.sql script in normal mode recompiles data dictionary objects and moves the data dictionary from an UPGRADED status to a VALID status.
Run the catuppst.sql script located in the $ORACLE_HOME/rdbms/admin directory, only if you notice any errors during the database upgrade:

SQL> @catuppst.sql

Note:
Check the log file if catuppst.sql was run during the upgrade. If catuppst.sql was not run, then you must manually run the catuppst.sql script as shown in step 13. Warning messages are also displayed when running the catctl.pl script indicating that the catuppst.sql script was not run during the upgrade.
Oracle recommends to gather fixed object statistics to minimize the time needed for recompilation with utlrp.sql script. Execute the following SQL statement:

SQL> execute dbms_stats.gather_fixed_objects_stats;
Run the utlrp.sql script to recompile any remaining stored PL/SQL and Java code. Use the following command:

SQL> @utlrp.sql
Verify that all expected packages and classes are valid. (This step assumes that you previously ran the preupgrd.sql script before starting the upgrade.) Use the following command:

SQL> @utluiobj.sql
Run the utlu121s.sql script again to verify that all issues have been fixed. Refer to step 12. All components should now have the status VALID.

To query invalid objects, execute SQL queries similar to:

SQL> SELECT count(*) FROM dba_objects WHERE status='INVALID';
SQL> SELECT distinct object_name FROM dba_objects WHERE status='INVALID';

Your database is now upgraded to Oracle Database 12c Release 1.

Performing Postupgrade Procedures

You must complete the following tasks after upgrading to Oracle Database 12c Release 1:

Rebuilding Database Application Programs

All database application programs with Embedded SQL must be precompiled using the new precompilers of Oracle Database 12c Release 1. Then they must be recompiled and relinked using the new Oracle Database libraries.

All database application programs with Oracle Call Interface (OCI) must be recompiled and relinked using the new Oracle Database libraries.

All openUTM database applications must be rebuilt. Refer to the chapters on openUTM in this guide for more information.

Updating ORAENV Files

The ORAUID environment variable must refer to the correct Oracle Database installation user ID and the ORACLE_HOME environment variable must refer to the corresponding Oracle home directory in the POSIX file system. Check your ORAENV files, and if necessary, update the value of the ORAUID, ORACLE_BASE, and ORACLE_HOME environment variables. Also check other environment variables for any adjustments.

Migrating to Unified Auditing

Oracle Database 12c Release 1 supports Unified Auditing. After upgrading to Oracle Database 12c, the Unified Auditing option is not enabled.