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
