Step By Step Manual Database Upgrade to 12.1.0.1

       Step By Step Manual Database Upgrade to 12.1.0.1

Purpose :-

In this Tutorial, you can learn how to upgrade database from 11.2.0.3 to 12.1.0.1 version .

Database Upgrade are common but risky task for a DBA if not done properly. 

This post covers step by step upgrade of Database. 

Upgrade Steps:-

Compatibility Matrix

Backup database (Recommended)

Check the database Integrity

Run Oracle's Database Pre-Upgrade Utility

Gather the DICTIONARY statistics

Verify That Materialized View Refreshes Have Completed Before Upgrading

Ensure That No Files Need Media Recovery Before Upgrading

Ensure That No Files Are in Backup Mode Before Upgrading

Purge the Database Recycle Bin Before Upgrading

Verify SYS and SYSTEM Default tablespace

Verify the existance of the AUD$ tables

Remove or comment out obsolete and deprecated initialization parameters   

Stop the listener for the database

Edit  /etc/oratab

Upgrading Database to 12cR1

Run the Post-Upgrade

Configure listener

Gather DB statistics

Details:-

Compatibility Matrix :-

Minimum version of the database that can be directly upgraded to Oracle 12c Release 1 (12.1)  

Source Database	Target Database
10.2.0.5	12.1.x
11.1.0.7	12.1.x
11.2.0.2 or higher	12.1.x

The following database versions will require an indirect upgrade path:

Source Database		Intermediate Upgrade Path for Target Database		Target Database
11.2.0.1	---->	11.2.0.2 or higher	---->	12.1.x
11.1.0.6	---->	11.1.0.7 or 11.2.0.2 or higher	---->	12.1.x
10.2.0.4 (or earlier)	---->	10.2.0.5 or later direct upgrade version	---->	12.1.x
10.1.0.5 (or earlier)	---->	10.2.0.5 or later direct upgrade version	---->	12.1.x
9.2.0.8 (or earlier)	---->	9.2.0.8 --> 11.2.0.2 or higher	---->	12.1.x

Backup database (Recommended):-

Either take a cold or hot backup of the source database (advisable to have cold backup).

Take the RMAN cold backup of database.

Connect to RMAN:

rman "target / nocatalog"

run {

allocate channel t1 type disk;

allocate channel t2 type disk;

allocate channel t3 type disk;

backup database format '/u01/app/oracle/bkup/rman/%d_%u_%s';
backup current controlfile format '/u01/app/oracle/bkup/rman/CONTROL_%d_%u_%s';

release channel t1;

release channel t2;

release channel t3;

}

Check the database Integrity:-

Ensure that all database components/objects provided by Oracle are VALID in the source database prior to starting the upgrade.

Ensure that you do not have duplicate objects in the SYS and SYSTEM schema

Clean Up Duplicate Objects Owned by SYS and SYSTEM Schema :-

In order to clean up the duplicate objects you need to issue a SQL script to find out the names of the duplicate objects.

You can then manually drop the objects or use a 'SQL generating SQL' script to generate a list of drop commands. 

Below is a SQL*Plus script that will list all objects that have been created in both the SYS and SYSTEM schema:  

column object_name format a30

select object_name, object_type

from dba_objects

where object_name||object_type in

   (select object_name||object_type 

    from dba_objects

    where owner = 'SYS')

and owner = 'SYSTEM';

Output of duplication object query is display only replication objects, which are created in sys and system users. But not other objects. Please go through Exception rule as below

EXCEPTION TO THE RULE :-

THE REPLICATION SCRIPTS (XXX) CORRECTLY CREATES OBJECTS WITH THE SAME NAME IN THE SYS AND SYSTEM ACCOUNTS. LISTED BELOW ARE THE OBJECTS USED BY REPLICATION THAT SHOULD BE CREATED IN BOTH ACCOUNTS. DO NOT DROP THESE OBJECTS FROM THE SYSTEM ACCOUNT IF YOU ARE USING REPLICATION. DOING SO WILL CAUSE REPLICATION TO FAIL!

The following objects are duplicates that will show up (and should not be removed) when running this script in 8.1.x and higher.

Without replication installed:

INDEX           AQ$_SCHEDULES_PRIMARY

TABLE           AQ$_SCHEDULES

If replication is installed by running catrep.sql:

INDEX           AQ$_SCHEDULES_PRIMARY

PACKAGE         DBMS_REPCAT_AUTH

PACKAGE BODY    DBMS_REPCAT_AUTH

TABLE           AQ$_SCHEDULES

When database is upgraded to 11g using DBUA, following duplicate objects are also created

OBJECT_NAME                OBJECT_TYPE

------------------------------ -------------

Help                          TABLE

Help_Topic_Seq                  Index

Note: - If you have a list of duplicate objects you will simply issue the appropriate DROP command to get rid of the object that is owned by the SYSTEM user.

If the list of objects is large then you may want to use the following SQL*Plus script to automatically generate an SQL script that contains the appropriate DROP commands:

set pause off

set heading off

set pagesize 0

set feedback off

set verify off

spool /u01/app/oracle/bkup/upgrd/dropsys.sql

select 'DROP ' || object_type || ' SYSTEM.' || object_name || ';'

from dba_objects

where object_name||object_type in

   (select object_name||object_type 

    from dba_objects

    where owner = 'SYS')

and owner = 'SYSTEM';

spool off

exit

Note :- If you drop duplicate objects in system user . You may get some ORA- Error . Please ignore and re-run the find out duplication objects script again .If it is display “no rows selected “then there are no duplicate objects on database .

If the dbupgdiag.sql script reports any invalid objects, run $ORACLE_HOME/rdbms/admin/utlrp.sql (multiple times) to validate the invalid objects in the database until there is no change in the number of invalid objects.

Suppose if you have found any invalid objects in database. You need to run utlrp.sql script.

Run Oracle's Database Pre-Upgrade Utility:-

* Log into the system as the owner of the Oracle Database 12c Release 1 (12.1) Oracle Home directory.

* Copy the Pre-Upgrade Information Tool script preupgrd.sql and utluppkg.sql from the Oracle Database 12c Release 1 (12.1) $ORACLE_HOME/rdbms/admin directory to  the $ORACLE_HOME/rdbms/admin directory of the source Oracle Home.

The utility will give the output in the form of recommendations to be implemented before starting the upgrade. Unless these requirements are met, the upgrade will fail.

Increased Processes value from 150 to 300 as per oracle recommendation .

Verify That Materialized View Refreshes Have Completed Before Upgrading :-

Before upgrading Oracle Database, you must wait until all materialized views have completed refreshing.

Run the following query to determine if there are any materialized view refreshes still in progress:

select s.obj#,o.obj#,s.containerobj#,lastrefreshdate,pflags,xpflags,o.name,o.owner#, bitand(s.mflags, 8) from obj$ o, sum$ s

where o.obj# = s.obj# and o.type# = 42 AND bitand(s.mflags, 8) = 8;

Ensure That No Files Need Media Recovery Before Upgrading :-

Make  ensure that there are no files requiring media recovery by executing the following query .

SELECT * FROM v$recover_file;

Ensure That No Files Are in Backup Mode Before Upgrading :-

Execute the following query to verify that No Files in backup mode when upgrading Oracle Database.

Purge the Database Recycle Bin Before Upgrading :

To empty the database recycle bin, issue the following command:

PURGE DBA_RECYCLEBIN

Verify SYS and SYSTEM Default tablespace:-

Ensure the users SYS and SYSTEM have 'SYSTEM' as their default tablespace.

You must have sufficient space in the 'SYSTEM' tablespace or set extents to unlimited.

 SELECT username, default_tablespace FROM dba_users WHERE username in ('SYS','SYSTEM');

If DEFAULT_TABLESPACE is anything other than SYSTEM tablespace, modify the default tablespace for user SYS and SYSTEM to SYSTEM by using the command below:

ALTER user SYS default tablespace SYSTEM;

ALTER user SYSTEM default tablespace SYSTEM;

Verify the existance of the AUD$ tables :-

Ensure that if the aud$ table exists that it is in the SYS schema and in the SYSTEM tablespace.

SELECT owner,tablespace_name FROM dba_tables  WHERE table_name='AUD$';

If the AUD$ table is not in SYSTEM tablespace and not owned by the SYS user then before doing the upgrade put it back to the SYSTEM tablespace and it should be owned by SYS .

Note: If the AUD$ table exists and is in use, upgrade performance can be effected depending on the number of records in the table.

Remove or comment out obsolete and deprecated initialization parameters :-

Comment out obsoleted parameters and change all deprecated parameters:-

           The SEC_CASE_SENSITIVE_LOGON parameter is deprecated in 12.1

The DIAGNOSTIC_DEST initialization parameter replaces the USER_DUMP_DEST, BACKGROUND_DUMP_DEST

If you are upgrading from 10.2.0.5 then make sure that the COMPATIBLE initialization parameter is set to at least 11.0.0 or the minimum value indicated by the Pre-Upgrade Information Tool prior to upgrading the database.

This value must remain throughout the upgrade and can be changed to the higher value after the upgrade has been completed successfully.

Stop the listener for the database:-

Edit  /etc/oratab :-

Upgrading Database to 12cR1:-

At the operating system prompt, change to the $ORACLE_HOME/rdbms/admin directory of 12cR1 Oracle Home.

$ cd $ORACLE_HOME/rdbms/admin

$ sqlplus "/ as sysdba"

SQL> startup UPGRADE

SQL> exit

Note :- Run the catctl.pl script from the new Oracle home.

In this release, the new Upgrade Utility, catctl.pl, replaces catupgrd.sql.

To run catctl.pl on Linux:

Example: Where parallelism is 6 ( n=6)

 cd $ORACLE_HOME/rdbms/admin

 $ORACLE_HOME/perl/bin/perl catctl.pl -n  6 -l $ORACLE_HOME/diagnostics catupgrd.sql

Upgrade Logfile location is :-

Run the Post-Upgrade:-

Run the Post-Upgrade Status Tool $ORACLE_HOME/rdbms/admin/utlu121s.sql which provides a summary of the upgrade at the end of the spool log.

It displays the status of the database components in the upgraded database and the time required to complete each component upgrade.

Any errors that occur during the upgrade are listed with each component and must be addressed.

Run catuppst.sql, located in the $ORACLE_HOME/rdbms/admin directory, to perform upgrade actions that do not require the database to be in UPGRADE mode.

After ran utlrp.sql,again execute post upgrade script whether any components in the upgraded database or not.

Initialization parameter file :-

Password File :-

If the REMOTE_LOGIN_PASSWORDFILE initialization parameter is set to either exclusive or shared, create a password file with ORAPWD.

Change COMPATIBLE parameter :-

Summary:-

In this tutorial, you learned how to upgrade database from 11.2.0.3 to 12.1.0.1 version.

Hope it helps. Please stay tuned, add me to your G+ Circles!! Your feedback always helps me and motivates me to blog more, please leave your valuable comment that is always a pleasure to read.

Many Thanks,

Regards

Senthilkumar Devaraj