Manual upgrading Oracle database 11gR2 to 18c

If you want to know how we upgrade an 11.2.0.4 database to 18.0.0.0.0 using DBUA, click here.

This article explains how to upgrade 11.2.0.3 and above, and 12.1.0.1, 12.2.0.1 to 18.0.0.0.0 manually.

Manually upgrading to 18c

There are many DBAs who don’t want to follow the recommended way of an upgrade using DBUA (Database Upgrade Assistant). Following is the method of manually upgrading the 11gr2 database to 18c. I will be using here 11.2.0.4 to upgrade to 18.1.0.0.0, on Linux (x64_86) platform.

I already install new software for Oracle 18c

Below is my environment detail

DBNAME  – MIG18C

CURRENT ORACLE_HOME – /u01/app/oracle/product/11.2.0.4/db_1

NEW ORACLE_HOME – /u01/app/oracle/product/18.0.0.0/db_1

[oracle@18c ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Mar 11 18:42:04 2018

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name,open_mode from v$database;

NAME       OPEN_MODE
--------- --------------------
MIG18C    READ WRITE

PRE-UPGRADE STEPS:

1. Run the preupgrade tool

Preupgrade tool is available under new $ORACLE_HOME/rdbms/admin.

ls -ltr /u01/app/oracle/product/18.0.0.0/db_1/rdbms/admin/preupgrade.jar

SYNTAX

$OLD_ORACLE_HOME/jdk/bin/java -jar $NEW_ORACLE_HOME/rdbms/admin/preupgrade.jar -FILE DIR $OS_DIRECTORY

[oracle@18c ~]$ /u01/app/oracle/product/11.2.0.4/db_1/jdk/bin/java -jar /u01/app/oracle/product/18.0.0.0/db_1/rdbms/admin/preupgrade.jar FILE DIR /u01/sw
==================
PREUPGRADE SUMMARY
==================
/u01/sw/preupgrade.log
/u01/sw/preupgrade_fixups.sql
/u01/sw/postupgrade_fixups.sql

Execute fixup scripts as indicated below:

Before upgrade log into the database and execute the preupgrade fixups
@/u01/sw/preupgrade_fixups.sql

After the upgrade:

Log into the database and execute the postupgrade fixups
@/u01/sw/postupgrade_fixups.sql

Preupgrade complete: 2018-03-11T18:45:45
[oracle@18c ~]$

 Run the preupgrade_fixup.sql

[oracle@18c ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Mar 11 18:47:35 2018

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @/u01/sw/preupgrade_fixups.sql
Executing Oracle PRE-Upgrade Fixup Script

Auto-Generated by: Oracle Preupgrade Script
Version: 18.0.0.0.0 Build: 1
Generated on: 2018-03-11 18:45:42

For Source Database: MIG18C
Source Database Version: 11.2.0.4.0
For Upgrade to Version: 18.0.0.0.0

Preup   Preupgrade
Action  Issue Is
Number  Preupgrade Check Name   Remedied    Further DBA Action
------ ------------------------ ----------  --------------------------------
1.     parameter_min_val        NO          Manual fixup recommended.
2.     em_present               NO          Manual fixup recommended.
3.     amd_exists               NO          Manual fixup recommended.
4.     apex_manual_upgrade      NO          Manual fixup recommended.
5.     dictionary_stats         YES         None.
6.     trgowner_no_admndbtrg    NO          Informational only.Further action is optional.
7.     pre_fixed_objects        YES         None.
8.     tablespaces_info         NO          Informational only.Further action is optional.

The fixup scripts have been run and resolved what they can. However,
there are still issues originally identified by the preupgrade that
have not been remedied and are still present in the database.
Depending on the severity of the specific issue, and the nature of
the issue itself, that could mean that your database is not ready
for upgrade. To resolve the outstanding issues, start by reviewing
the preupgrade_fixups.sql and searching it for the name of
the failed CHECK NAME or Preupgrade Action Number listed above.
There you will find the original corresponding diagnostic message
from the preupgrade which explains in more detail what still needs
to be done.

PL/SQL procedure successfully completed.

SQL>

To get rid of from the warnings. Steps are below

Again re-run preupgrade_fixup.sql.

[oracle@18c apex]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Mar 11 19:27:29 2018

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @/u01/sw/preupgrade_fixups.sql
Executing Oracle PRE-Upgrade Fixup Script

Auto-Generated by: Oracle Preupgrade Script
Version: 18.0.0.0.0 Build: 1
Generated on: 2018-03-11 19:19:47

For Source Database: MIG18C
Source Database Version: 11.2.0.4.0
For Upgrade to Version: 18.0.0.0.0

Preup   Preupgrade
Action  Issue Is
Number  Preupgrade Check Name    Remedied    Further DBA Action
------  ------------------------ ----------  --------------------------------
1.      invalid_objects_exist    YES         None.
2.      tablespaces_info         YES         None.

PL/SQL procedure successfully completed.

SQL> exit

Shutdown the database and take full database backup. Alternatively, you can turn on the flashback database and create a Guaranteed Restore Point which can be used to rollback if upgrade process fails.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

Copy the parameter and password file from the 11g home to the 18c home.

Note: I am using Read Only Home

[oracle@18c dbs]$ cp /u01/app/oracle/product/11.2.0.4/db_1/dbs/orapwMIG18C /u01/app/oracle/dbs
[oracle@18c dbs]$ cp /u01/app/oracle/product/11.2.0.4/db_1/dbs/spfileMIG18C.ora /u01/app/oracle/dbs

Set new ORACLE HOME of 18c location

[oracle@18c dbs]$ export ORACLE_HOME=/u01/app/oracle/product/18.0.0.0/db_1
[oracle@18c dbs]$ export ORACLE_SID=MIG18C
[oracle@18c dbs]$ export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH:.

[oracle@18c dbs]$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 Production on Sun Mar 11 19:39:43 2018
Version 18.1.0.0.0

Copyright (c) 1982, 2017, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 2432693864 bytes
Fixed Size 8660584 bytes
Variable Size 654311424 bytes
Database Buffers 1761607680 bytes
Redo Buffers 8114176 bytes
SQL> alter database mount;

Database altered.

SQL> alter database open upgrade;

Database altered.

SQL>

Run catctl.pl

If used catctl.pl, the output of this upgrade process would be similar to the following. For this example, log files would be in /tmp folder as specified in upgrade command. For conventional running of catupgrd.sql from SQLPLUS, please see the spool file for output or any errors

-n is used for parallelism (new feature on Oracle 12c)

$ORACLE_HOME/perl/bin/perl catctl.pl -n 4 catupgrd.sql

For the output log, click here

Run “postupgrade_fixups.sql” which was created earlier.

[oracle@18c ~]$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 Production on Sun Mar 11 20:21:28 2018
Version 18.1.0.0.0

Copyright (c) 1982, 2017, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 2432693864 bytes
Fixed Size 8660584 bytes
Variable Size 654311424 bytes
Database Buffers 1761607680 bytes
Redo Buffers 8114176 bytes
Database mounted.
Database opened.
SQL> @/u01/sw/postupgrade_fixups.sql

Session altered.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Session altered.

Session altered.

PL/SQL procedure successfully completed.

Package created.

No errors.

Package body created.

PL/SQL procedure successfully completed.

No errors.

Package created.

No errors.

Package body created.

No errors.
Executing Oracle POST-Upgrade Fixup Script

Auto-Generated by: Oracle Preupgrade Script
Version: 18.0.0.0.0 Build: 1
Generated on: 2018-03-11 19:29:36

For Source Database: MIG18C
Source Database Version: 11.2.0.4.0
For Upgrade to Version: 18.0.0.0.0

Preup                           Preupgrade
Action                          Issue Is
Number Preupgrade Check Name    Remedied    Further DBA Action
------ ------------------------ ----------  --------------------------------
1.     depend_usr_tables         YES        None.
2.     old_time_zones_exist      NO         Manual fixup recommended.
3.     post_dictionary           YES        None.
4.     post_fixed_objects        NO         Informational only.Further action is optional.
5.     upg_by_std_upgrd          YES        None.

The fixup scripts have been run and resovled what they can. However,
there are still issues originally identified by the preupgrade that
have not been remedied and are still present in the database.
Depending on the severity of the specific issue, and the nature of
the issue itself, that could mean that your database upgrade is not
fully complete. To resolve the outstanding issues, start by reviewing
the postupgrade_fixups.sql and searching it for the name of
the failed CHECK NAME or Preupgrade Action Number listed above.
There you will find the original corresponding diagnostic message
from the preupgrade which explains in more detail what still needs
to be done.

PL/SQL procedure successfully completed.

Session altered.

SQL>

To fix the manual error related to timezone

The database is using timezone datafile version 14 and the target 18.1.0.0.0 database ships with timezone datafile version 31. Oracle recommends using the most recent timezone data.  For further information, refer to My Oracle Support Note 1585343.1

Or we can do manually from here

To get rid of from the warnings. Steps are below

[oracle@18c ~]$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 Production on Sun Mar 11 20:21:28 2018
Version 18.1.0.0.0

Copyright (c) 1982, 2017, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 2432693864 bytes
Fixed Size 8660584 bytes
Variable Size 654311424 bytes
Database Buffers 1761607680 bytes
Redo Buffers 8114176 bytes
Database mounted.
Database opened.
SQL> @/u01/sw/postupgrade_fixups.sql

Session altered.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Session altered.

Session altered.

PL/SQL procedure successfully completed.

Package created.

No errors.

Package body created.

PL/SQL procedure successfully completed.

No errors.

Package created.

No errors.

Package body created.

No errors.
Executing Oracle POST-Upgrade Fixup Script

Auto-Generated by: Oracle Preupgrade Script
Version: 18.0.0.0.0 Build: 1
Generated on: 2018-03-11 19:29:36

For Source Database: MIG18C
Source Database Version: 11.2.0.4.0
For Upgrade to Version: 18.0.0.0.0

Preup                           Preupgrade
Action                          Issue Is
Number Preupgrade Check Name    Remedied    Further DBA Action
------ ------------------------ ----------  --------------------------------
1.     depend_usr_tables         YES        None.
2.     old_time_zones_exist      YES        None
3.     post_dictionary           YES        None.
4.     post_fixed_objects        YES        None
5.     upg_by_std_upgrd          YES        None.

The fixup scripts have been run and resovled what they can. However,
there are still issues originally identified by the preupgrade that
have not been remedied and are still present in the database.
Depending on the severity of the specific issue, and the nature of
the issue itself, that could mean that your database upgrade is not
fully complete. To resolve the outstanding issues, start by reviewing
the postupgrade_fixups.sql and searching it for the name of
the failed CHECK NAME or Preupgrade Action Number listed above.
There you will find the original corresponding diagnostic message
from the preupgrade which explains in more detail what still needs
to be done.

PL/SQL procedure successfully completed.

Session altered.

SQL>

Execute utlu122s.sql

SQL> @?/rdbms/admin/utlu122s.sql

Execute catuppst.sql

SQL> @?/rdbms/admin/catuppst.sql

Lastly, execute utlrp.sql script to compile if there are any invalid objects

SQL>@?/rdbms/admin/utlrp.sql

Set COMPATIBLE parameter value to 18.0.0 and also start the listener with a new home.

SQL> show parameter COMPATIBLE

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 11.2.0.4.0
noncdb_compatible boolean FALSE
SQL> ALTER SYSTEM SET COMPATIBLE = '18.0.0' SCOPE=SPFILE;

System altered.

SQL> select count(1) from dba_objects where status='INVALID';

COUNT(1)
----------
    0

Restart the database

SQL> shut immediate

Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.

Total System Global Area 2432693864 bytes
Fixed Size 8660584 bytes
Variable Size 654311424 bytes
Database Buffers 1761607680 bytes
Redo Buffers 8114176 bytes
Database mounted.
Database opened.
SQL> select name,version,open_mode from v$database,v$instance;

NAME      VERSION           OPEN_MODE
--------- ----------------- --------------------
MIG18C    18.0.0.0.0        READ WRITE

SQL>

Cheers!!!

Upgradation Successfully Done

Stay tuned for More articles on Oracle 18c 

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

Anuradha’s LinkedIn: Anuradha’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.