About Upgrading a Database

4 About Upgrading a Database

This chapter describes how to upgrade your existing database to Oracle Database 12c Release 2 on Fujitsu BS2000. It gives you an overview about the steps required for the upgrade of a database and informs about actions, which are specific to the Fujitsu BS2000 platform.

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 2 as explained in “Oracle Database Installation and Deinstallation.”This chapter describes upgrading a database from Oracle Database 12c Release 1 (12.1) to Oracle Database 12c Release 2 (12.2). 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

4.1 Performing Preupgrade Procedures

You must analyze your database before upgrading it to the new release. To analyze, run the preupgrade.jar 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 preupgrade.jar tool in the environment of the source database. For example in the Oracle Database 12c Release 1 environment. The database must be up and running.

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
Before you run the Pre-upgrade Information Tool, preupgrade.jar in the POSIX shell, set the Oracle environment variables ORACLE_HOME and ORACLE_BASE by executing the .profile.oracle file:
$ . oracle_home_path_12c_R1/.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 preupgrade.jar Pre-Upgrade Information Tool:

$ $ORACLE_HOME/jdk/bin/java -jar oracle_home_path_12c_R2/rdbms/admin/preupgrade.jar

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.

You can find regular updates of the Pre-Upgrade Information tool on the My Oracle Support website. Refer to the My Oracle Support Note 884522.1 if you want to download and install a new release of the Pre-Upgrade information tool. The steps described in this note are valid for Fujitsu BS2000, too. The only difference is the procedure for unzipping the zip file.

Transfer the zip file (with binary mode) to the POSIX file system of your BS2000 machine. Then unzip the zip file into the $ORACLE_HOME/rdbms/admin directory of Oracle Database 12c Release 2 (12.2) as follows:
```
$ cd $ORACLE_HOME/rdbms/admin
$ unzip -aa preupgrade_12201_cbuild_nn_lf.zip -x preupgrade.jar
$ unzip preupgrade_12201_cbuild_nn_lf.zip preupgrade.jar
```

4.2 Performing Upgrade Procedures

Use the upgrade utility, dbupgrade to upgrade Oracle Database. As shell scripts and Perl scripts are supported only in the POSIX environment, you must change to the POSIX shell for performing some of the procedure.

The shell script dbupgrade is new in Oracle Database 12c Release 2 (12.2). It is a wrapper for the parallel upgrade utility catctl.pl, which is based on Perl.

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

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 2 INIT.ORA file and an Oracle Database 12c Release 2 ORAENV file. The original files will be saved by the $ORACINST.INSTALL.P.DBA procedure with 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 12.1.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_R2/.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 shell script for upgrade as follows:
```
$ $ORACLE_HOME/bin/dbupgrade -l $HOME/logs
```
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 upgrade utility, dbupgrade.

The upgraded database is shut down after running dbupgrade.
Restart the instance to reinitialize the system parameters for normal operation:
```
$ sqlplus /nolog
SQL> connect / as sysdba
SQL> startup
```
Run the catcon.pl script to start utlrp.sql, and to recompile any remaining stored PL/SQL and Java code. For example:
$ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlrp -l $HOME/logs -d '''.''' utlrp.sql

When you run the command using -b utlrp, the log file utlrp0.log is generated as the script is run. The log file provides the recompilation results.
Run the postupgrade_fixups.sql script. For example:
SQL> @postupgrade_fixups.sql
Run utlu122s.sql, the Post-Upgrade Status Tool to display a summary of the upgrade results:

SQL> @utlu122s.sql

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

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 2.

4.3 Performing Postupgrade Procedures

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

Rebuilding Database Application Programs

All database application programs with Embedded SQL must be precompiled using the new precompilers of Oracle Database 12c Release 2. 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.

Gathering Statistics

Oracle recommends to gather dictionary statistics after upgrading the database. Execute the following SQL statement:

SQL> execute dbms_stats.gather_dictionary_stats;

After an upgrade Oracle strongly recommends that you regather fixed object statistics after you have run representative workloads. Execute the following SQL statement:

SQL> execute dbms_stats.gather_fixed_objects_stats;

Migrating to Unified Auditing

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