Manual upgrading Oracle database 11gR2 to 19c
If you want to know how we upgrade an 11.2.0.4 database to 19.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, 18c to 19.0.0.0.0 manually.
Manually upgrading to 19c
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 19c. I will be using here 11.2.0.4 to upgrade to 19.1.0.0.0, on Oracle Linux (x64_86) platform.
I already install new software for Oracle 19c
Below is my environment detail
DBNAME – MANUAL19C
CURRENT ORACLE_HOME – /u01/app/oracle/product/11.0.0/dbhome_1
NEW ORACLE_HOME – /u01/app/oracle/product/19.0.0/dbhome_1
[oracle@oracle19c ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Mon Feb 25 11:00:55 2019 Copyright (c) 1982, 2013, Oracle. All rights reserved. SQL> select name,open_mode,version from v$database,v$instance; NAME OPEN_MODE VERSION --------- -------------------- ----------------- UPG19C READ WRITE 11.2.0.4.0 SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@oracle19c ~]$
PRE-UPGRADE STEPS:
1. Run the preupgrade tool
Preupgrade tool is available under new $ORACLE_HOME/rdbms/admin.
[oracle@oracle19c ~]$ ls -lrt /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar -rw-r--r--. 1 oracle oinstall 719812 Feb 5 01:03 /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar [oracle@oracle19c ~]$
SYNTAX
$OLD_ORACLE_HOME/jdk/bin/java -jar $NEW_ORACLE_HOME/rdbms/admin/preupgrade.jar -FILE DIR $OS_DIRECTORY
Where OS_DIRECTORY is any directory at OS level.
[oracle@oracle19c ~]$ /u01/app/oracle/product/11.0.0/dbhome_1/jdk/bin/java -jar /u01/app/oracle/product/19.0.0/dbhome_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@oracle19c ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Feb 25 11:09:20 2019
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: 19.0.0.0.0 Build: 1
Generated on: 2019-02-25 11:08:02
For Source Database: MANUAL19C
Source Database Version: 11.2.0.4.0
For Upgrade to Version: 19.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 YES None.
7. mv_refresh NO Informational only.
Further action is optional.
8. pre_fixed_objects YES None.
9. tablespaces_info NO Informational only.
Further action is optional.
10. exf_rul_exists NO Informational only.
Further action is optional.
11. rman_recovery_version 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> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oracle19c ~]$
To get rid of from the warnings. Steps are below
Again re-run preupgrade_fixup.sql.
[oracle@oracle19c tmp]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sun Feb 24 00:42:43 2019
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> @preupgrade_fixups.sql
Executing Oracle PRE-Upgrade Fixup Script
Auto-Generated by: Oracle Preupgrade Script
Version: 19.0.0.0.0 Build: 1
Generated on: 2019-02-23 23:35:16
For Source Database: MANUAL19
Source Database Version: 11.2.0.4.0
For Upgrade to Version: 19.0.0.0.0
Preup Preupgrade
Action Issue Is
Number Preupgrade Check Name Remedied Further DBA Action
------ ------------------------ ---------- --------------------------------
1. parameter_min_val YES None.
2. em_present YES None.
3. amd_exists YES None.
4. apex_manual_upgrade YES None.
5. dictionary_stats YES None.
6. trgowner_no_admndbtrg YES None.
7. mv_refresh NO Informational only.
Further action is optional.
8. pre_fixed_objects YES None.
9. tablespaces_info NO Informational only.
Further action is optional.
10. exf_rul_exists NO Informational only.
Further action is optional.
11. rman_recovery_version 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> 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
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.
[oracle@oracle19c tmp]$ cd /u01/app/oracle/product/11.0.0/dbhome_1/dbs [oracle@oracle19c dbs]$ [oracle@oracle19c dbs]$ cp spfileMANUAL19C.ora orapwMANUAL19C /u01/app/oracle/product/19.0.0/dbhome_1/dbs [oracle@oracle19c dbs]$ [oracle@oracle19c dbs]$ cd /u01/app/oracle/product/19.0.0/dbhome_1/dbs [oracle@oracle19c dbs]$ ls -lrt -rw-r--r--. 1 oracle oinstall 3104 Feb 18 18:56 init.ora -rw-r-----. 1 oracle oinstall 2560 Feb 25 00:35 spfileMANUAL19C.ora -rw-r-----. 1 oracle oinstall 1536 Feb 25 00:35 orapwMANUAL19C [oracle@oracle19c dbs]$
Set new ORACLE HOME of 19c location and start the database in upgrade mode.
[oracle@oracle19c dbs]$ export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1 [oracle@oracle19c dbs]$ export ORACLE_SID=MANUAL19C [oracle@oracle19c dbs]$ export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH [oracle@oracle19c dbs]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Mon Feb 25 00:42:45 2019 Version 19.2.0.0.0 Copyright (c) 1982, 2018, Oracle. All rights reserved. Connected to an idle instance. SQL> startup nomount; ORACLE instance started. Total System Global Area 2432695136 bytes Fixed Size 8899424 bytes Variable Size 654311424 bytes Database Buffers 1761607680 bytes Redo Buffers 7876608 bytes SQL> alter database mount; Database altered. SQL> SQL> alter database open upgrade; Database altered. SQL> exit Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.2.0.0.0 [oracle@oracle19c dbs]$
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). Run the below command from $ORACLE_HOME/rdbms/admin
$ORACLE_HOME/perl/bin/perl catctl.pl -n 4 catupgrd.sql
[oracle@oracle19c admin]$ $ORACLE_HOME/perl/bin/perl catctl.pl -n 4 catupgrd.sql
Argument list for [catctl.pl]
For Oracle internal use only A = 0
Run in c = 0
Do not run in C = 0
Input Directory d = 0
Echo OFF e = 1
Simulate E = 0
Forced cleanup F = 0
Log Id i = 0
Child Process I = 0
Log Dir l = 0
Priority List Name L = 0
Upgrade Mode active M = 0
SQL Process Count n = 4
SQL PDB Process Count N = 0
Open Mode Normal o = 0
Start Phase p = 0
End Phase P = 0
Reverse Order r = 0
AutoUpgrade Resume R = 0
Script s = 0
Serial Run S = 0
RO User Tablespaces T = 0
Display Phases y = 0
Debug catcon.pm z = 0
Debug catctl.pl Z = 0
catctl.pl VERSION: [19.0.0.0.0]
STATUS: [Production]
BUILD: [RDBMS_19.2.0.0.0_LINUX.X64_190204]
/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/orahome = [/u01/app/oracle/product/19.0.0/dbhome_1]
/u01/app/oracle/product/19.0.0/dbhome_1/bin/orabasehome = [/u01/app/oracle/product/19.0.0/dbhome_1]
catctlGetOraBaseLogDir = [/u01/app/oracle/product/19.0.0/dbhome_1]
Analyzing file /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/catupgrd.sql
Log file directory = [/tmp/cfgtoollogs/upgrade20190225005318]
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/tmp/cfgtoollogs/upgrade20190225005318/catupgrd_catcon_21913.lst]
catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20190225005318/catupgrd*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20190225005318/catupgrd_*.lst] files for spool files, if any
Number of Cpus = 2
Database Name = MANUAL19C
DataBase Version = 11.2.0.4.0
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/MANUAL19C/upgrade20190225005320/catupgrd_catcon_21913.lst]
catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/MANUAL19C/upgrade20190225005320/catupgrd*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/MANUAL19C/upgrade20190225005320/catupgrd_*.lst] files for spool files, if any
Log file directory = [/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/MANUAL19C/upgrade20190225005320]
Components in [MANUAL19C]
Installed [APEX APS CATALOG CATJAVA CATPROC CONTEXT JAVAVM ORDIM OWM SDO XDB XML XOQ]
Not Installed [DV EM MGW ODM OLS RAC WK]
Parallel SQL Process Count = 4
------------------------------------------------------
Phases [0-107] Start Time:[2019_02_25 00:53:22]
------------------------------------------------------
*********** Executing Change Scripts ***********
Serial Phase #:0 [MANUAL19C] Files:1 Time: 155s
---- Output Truncated----
------------------------------------------------------
Phases [0-107] End Time:[2019_02_25 01:52:30]
------------------------------------------------------
Grand Total Time: 3552s
LOG FILES: (/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/MANUAL19C/upgrade20190225005320/catupgrd*.log)
Upgrade Summary Report Located in:
/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/MANUAL19C/upgrade20190225005320/upg_summary.log
Grand Total Upgrade Time: [0d:0h:59m:12s]
[oracle@oracle19c admin]$
For the output log, click here
Start the database and run “postupgrade_fixups.sql” which was created earlier.
[oracle@oracle19c ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Feb 25 11:28:31 2019
Version 19.2.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 2432695136 bytes
Fixed Size 8899424 bytes
Variable Size 654311424 bytes
Database Buffers 1761607680 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
SQL> @/u01/sw/postupgrade_fixups.sql
Session altered.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
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.
WARNING - This script was generated for database MANUAL19C.
Executing Oracle POST-Upgrade Fixup Script
Auto-Generated by: Oracle Preupgrade Script
Version: 19.0.0.0.0 Build: 1
Generated on: 2019-02-25 11:08:08
For Source Database: MANUAL19C
Source Database Version: 11.2.0.4.0
For Upgrade to Version: 19.0.0.0.0
Preup Preupgrade
Action Issue Is
Number Preupgrade Check Name Remedied Further DBA Action
------ ------------------------ ---------- --------------------------------
12. depend_usr_tables YES None.
13. old_time_zones_exist NO Manual fixup recommended.
14. dir_symlinks YES None.
15. post_dictionary YES None.
16. post_fixed_objects NO Informational only.
Further action is optional.
17. upg_by_std_upgrd YES None.
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 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> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.2.0.0.0
[oracle@oracle19c ~]$
To fix the manual error related to timezone
The database is using timezone datafile version 14 and the target 19.1.0.0.0 database ships with timezone datafile version 34. 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@oracle19c DBMS_DST_scriptsV1.9]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Feb 25 23:41:37 2019
Version 19.2.0.0.0
Copyright (c) 1982, 2018, 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.
PL/SQL procedure successfully completed.
Package created.
No errors.
Package body created.
PL/SQL procedure successfully completed.
No errors.
1 row selected.
Package created.
No errors.
Package body created.
No errors.
Executing Oracle POST-Upgrade Fixup Script
Auto-Generated by: Oracle Preupgrade Script
Version: 19.0.0.0.0 Build: 1
Generated on: 2019-02-23 23:35:21
For Source Database: MANUAL19
Source Database Version: 11.2.0.4.0
For Upgrade to Version: 19.0.0.0.0
Preup Preupgrade
Action Issue Is
Number Preupgrade Check Name Remedied Further DBA Action
------ ------------------------ ---------- --------------------------------
10. depend_usr_tables YES None.
11. old_time_zones_exist YES None.
12. dir_symlinks YES None.
13. post_dictionary YES None.
14. post_fixed_objects YES None.
15. upg_by_std_upgrd YES None.
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 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 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 19.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>
SQL> alter system set compatible='19.0.0' scope=spfile;
System altered.
Restart the database
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 2432695136 bytes
Fixed Size 8899424 bytes
Variable Size 654311424 bytes
Database Buffers 1761607680 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
SQL>
SQL> show parameter compatible;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 19.0.0
noncdb_compatible boolean FALSE
SQL>
SQL> select name, open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
MANUAL19 READ WRITE
1 row selected.
SQL>
SQL> select version from v$instance;
VERSION
-----------------
19.0.0.0.0
1 row selected.
SQL>
Cheers!!!
Upgradation Successfully Done
Stay tuned for More articles on Oracle 19c
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
Share this:
Since 12c invoking catupgrd.sql in sqlplus returns information to use parallel upgrade utility. Is it really changed back in 19c?
We can use CATCTL.PL from 12c onwards