In this article we are going to see how to upgrade a oracle 12c container database from 12c to 19c using manual upgrade method.
Here we are going to proceed with the same steps as we followed while upgrading a non container database from oracle 12c to 19c.
In my environment I am having one container database and two pluggable database in it.
Overview Steps:
Step:1 Create a new directory called backup, Take, Take backup of listener.ora, tnsnames.ora files ,then move all the files to the newly created directory called backup.
Step:2 Create a new directories called upgrade, while upgrade, Run the preupgrade.jar file which is in the location. Move the summary of the preupgrade script to the upgrade directory.
/home/oracle/u03/app/oracle/product/12.2.0/db_1/jdk/bin/java -jar
/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar
Step:3 Three files named preupgrade.log, preupgrade_fixups.sql, postupgrade_fixups.sql files will be created after running the preupgrade script.
Step:4 We should execute the below scripts,
Before upgrade – @/u01/preupgrade/preupgrade_fixups.sql
After upgrade – @/u01/preupgrade/postupgrade_fixups.sql
Step:5 Then check the preupgrade.log whether it is completed without any errors.
Step:6 Verify the tablespace sizes for upgrade.(enable the AUTO EXTENT ON and set the maximum size for tablespaces)
Step:7 Gather the dictionary statistics query to get the statistical data of fixed objects. we
should gather dictionary statistics before and after the upgrade to maintain better database performance. Because dictionary tables are modified and created during the upgrade.
Step:8 Purge the recyclebin
Step:9 Refresh all the materialized views before upgrade.
Step:10 Run preupgrade_fixups.sql (/u01/upgrade/preupgrade_fixups.sql) before the upgrade.
Step:11 You should enable the archive log list, it is mandatory to perform an upgrade.
Step:12 Create the flashback restore point before performing upgrade, it is important suppose if dbupgrade fails you can recover the database from this restore point.
Step:13 Set the db_recovery_file_dest_size=10GB
UPGRADE STEPS:
After we have completed all our pre-upgrade steps,
Step:14 shut down the database, copy the spfile, password files from 12c home to 19c home.
Step:15 startup the database in upgrade mode from 19c home.
Step:16 Run the upgrade, $ORACLE_HOME/bin/dbupgrade file. Here starts the manual upgrade process.
POST UPGRADE STEPS:
Step:17 Run catcon.pl to start utlrp.sql to recompile the invalid objects.
Step:18 Then check for any invalid objects in the database after upgrade.
Step:19 Verify the timezone version of the upgraded database.(it should be changed to 32),for that run utlusts.sql script.
Step:20 We have to verify the DBA_REGISTRY view, it displays the information about the components loaded in the database.
Step:21 Now we can drop the restore point that we have created already before the upgrade.
Step:22 Update the compatible parameter value, it will be set in the base database version we have to update the upgraded database version in the compatible parameter value.
Step:23 Finally check the listener.ora and tnsnames.ora files it should be updated to a upgraded database version.
PRE-UPGRADE TASK:
Step: 1 Create directory backup, upgrade for taking backup of listener.ora , tnsnames.ora and running the preupgrade.jar files respectively.
Backup TNS Files listener spfile and passwordfile.
[oracle@localhost ~]$ mkdir -p /u01/backup
[oracle@localhost u01]$ cd backup/ [oracle@orcldbs backup]$ ll total 20 -rwxr-xr-x. 1 oracle oinstall 343 Mar 09 03:32 listener21013011PM3657.bak -rwxr-xr-x. 1 oracle oinstall 555 Mar 09 03:32 listener.ora -rwxr-xr-x. 1 oracle oinstall 191 Mar 09 03:32 sqlnet21013011PM3657.bak -rwxr-xr-x. 1 oracle oinstall 191 Mar 09 03:32 sqlnet.ora -rwxr-xr-x. 1 oracle oinstall 614 Mar 09 03:32 tnsnames.ora [oracle@orcldbs backup]$
Step:2 Login to the database & Run the preupgrade_fixups.sql
Step:3 Gather the dictionary statistics it should be gathered before and after performing the upgrade.
Step:4 Purge the Recyclebin, it gains some storage space and on other hand we have chosen the recovery options.
Step:5 Before upgrading Oracle Database, you must wait until all materialized views have completed refreshing.
Step:6 Verify archive log mode and enable it ,if not
Step:7 Create Flashback Guaranteed Restore Point in the name pre_upgrade.
Step:8 Copy init and password files from 12c home to 19c home.
preupgrade_CDB_ROOT.log
Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 1 on 2021-03-09T05:41:20
Upgrade-To version: 19.0.0.0.0
=======================================
Status of the database prior to upgrade
=======================================
Database Name: EUROPE
Container Name: CDB$ROOT
Container ID: 1
Version: 12.2.0.1.0
DB Patch Level: No Patch Bundle applied
Compatible: 12.2.0
Blocksize: 8192
Platform: Linux x86 64-bit
Timezone File: 26
Database log mode: NOARCHIVELOG
Readonly: FALSE
Edition: EE
preupgrade_PDB_SEED.log
Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 1 on 2021-03-09T05:41:43
Upgrade-To version: 19.0.0.0.0
=======================================
Status of the database prior to upgrade
=======================================
Database Name: EUROPE
Container Name: PDB$SEED
Container ID: 2
Version: 12.2.0.1.0
DB Patch Level: No Patch Bundle applied
Compatible: 12.2.0
Blocksize: 8192
Platform: Linux x86 64-bit
Timezone File: 26
Database log mode: NOARCHIVELOG
Readonly: TRUE
Edition: EE
preupgrade_ARGENTINA.log
Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 1 on 2021-03-09T05:41:43
Upgrade-To version: 19.0.0.0.0
=======================================
Status of the database prior to upgrade
=======================================
Database Name: EUROPE
Container Name: ARGENTINA
Container ID: 3
Version: 12.2.0.1.0
DB Patch Level: No Patch Bundle applied
Compatible: 12.2.0
Blocksize: 8192
Platform: Linux x86 64-bit
Timezone File: 26
Database log mode: NOARCHIVELOG
Readonly: FALSE
Edition: EE
preupgrade_BRAZIL.log
Report generated by Oracle Database Pre-Upgrade Information Tool Version 19.0.0.0.0 Build: 1 on 2021-03-09T05:42:09 Upgrade-To version: 19.0.0.0.0 ======================================= Status of the database prior to upgrade ======================================= Database Name: EUROPE Container Name: BRAZIL Container ID: 4 Version: 12.2.0.1.0 DB Patch Level: No Patch Bundle applied Compatible: 12.2.0 Blocksize: 8192 Platform: Linux x86 64-bit Timezone File: 26 Database log mode: NOARCHIVELOG Readonly: FALSE Edition: EE
UPGRADE TASK:
Step:9 Startup the database in Upgrade mode from 19c home, Run dbupgrade, $ORACLE_HOME/bin/dbupgrade file. Here starts the manual upgrade process.
[oracle@localhost ~]$ cd $ORACLE_HOME/rdbms/admin [oracle@localhost admin]$ pwd /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin [oracle@localhost admin]$ mkdir -p /u01/whileupgrade [oracle@localhost admin]$ nohup /u01/app/oracle/product/19.0.0/dbhome_1/perl/bin/perl catctl.pl -l /u01/whileupgrade -n 4 catupgrd.sql & [1] 24608 [oracle@orcldbs admin]$ nohup: ignoring input and appending output to ‘nohup.out’ Start of Summary Report ------------------------------------------------------ Oracle Database Release 19 Post-Upgrade Status Tool 03-09-2021 06:06:0 Database Name: EUROPE Component Current Full Elapsed Time Name Status Version HH:MM:SS Oracle Server UPGRADED 19.3.0.0.0 00:18:34 JServer JAVA Virtual Machine UPGRADED 19.3.0.0.0 00:01:34 Oracle XDK UPGRADED 19.3.0.0.0 00:00:47 Oracle Database Java Packages UPGRADED 19.3.0.0.0 00:00:13 OLAP Analytic Workspace UPGRADED 19.3.0.0.0 00:00:12 Oracle Label Security UPGRADED 19.3.0.0.0 00:00:06 Oracle Database Vault UPGRADED 19.3.0.0.0 00:00:20 Oracle Text UPGRADED 19.3.0.0.0 00:00:31 Oracle Workspace Manager UPGRADED 19.3.0.0.0 00:00:27 Oracle Real Application Clusters UPGRADED 19.3.0.0.0 00:00:00 Oracle XML Database UPGRADED 19.3.0.0.0 00:01:31 Oracle Multimedia UPGRADED 19.3.0.0.0 00:00:47 Spatial UPGRADED 19.3.0.0.0 00:05:33 Oracle OLAP API UPGRADED 19.3.0.0.0 00:00:10 Datapatch 00:03:27 Final Actions 00:03:35 Post Upgrade 00:00:17 Total Upgrade Time: 01:55:38
Database upgrade completed.
Step:10 Startup DB from 19c home.
POST-UPGRADE TASKS:
Step:-11 Run catcon.pl(it is used used to execute the command in all PDBs and CDB database) to start utlrp.sql to recompile invalid objects.
SQL> @?/rdbms/admin/utlrp.sql Session altered. TIMESTAMP -------------------------------------------------------------------------------- COMP_TIMESTAMP UTLRP_BGN 2021-03-09 06:30:35 DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid DOC> objects in the database. Recompilation time is proportional to the DOC> number of invalid objects in the database, so this command may take DOC> a long time to execute on a database with a large number of invalid DOC> objects. DOC> DOC> Use the following queries to track recompilation progress: DOC> DOC> 1. Query returning the number of invalid objects remaining. This DOC> number should decrease with time. DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6); DOC> DOC> 2. Query returning the number of objects compiled so far. This number DOC> should increase with time. DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED; DOC> DOC> This script automatically chooses serial or parallel recompilation DOC> based on the number of CPUs available (parameter cpu_count) multiplied DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu). DOC> On RAC, this number is added across all RAC nodes. DOC> DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel DOC> recompilation. Jobs are created without instance affinity so that they DOC> can migrate across RAC nodes. Use the following queries to verify DOC> whether UTL_RECOMP jobs are being created and run correctly: DOC> DOC> 1. Query showing jobs created by UTL_RECOMP DOC> SELECT job_name FROM dba_scheduler_jobs DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%'; DOC> DOC> 2. Query showing UTL_RECOMP jobs that are running DOC> SELECT job_name FROM dba_scheduler_running_jobs DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%'; DOC># PL/SQL procedure successfully completed. TIMESTAMP -------------------------------------------------------------------------------- COMP_TIMESTAMP UTLRP_END 2021-03-09 06:34:29 DOC> The following query reports the number of invalid objects. DOC> DOC> If the number is higher than expected, please examine the error DOC> messages reported with each object (using SHOW ERRORS) to see if they DOC> point to system misconfiguration or resource constraints that must be DOC> fixed before attempting to recompile these objects. DOC># OBJECTS WITH ERRORS ------------------- 0 DOC> The following query reports the number of exceptions caught during DOC> recompilation. If this number is non-zero, please query the error DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors DOC> are due to misconfiguration or resource constraints that must be DOC> fixed before objects can compile successfully. DOC> Note: Typical compilation errors (due to coding errors) are not DOC> logged into this table: they go into DBA_ERRORS instead. DOC># ERRORS DURING RECOMPILATION --------------------------- 0 Function created. PL/SQL procedure successfully completed. Function dropped. PL/SQL procedure successfully completed.
Step:12 Upgrade the time zone by running the following scripts,
First Script $ORACLE_HOME/rdbms/admin/utltz_upg_check.sql
Second Script $ORACLE_HOME/rdbms/admin/ utltz_upg_apply.sql
SQL> @?/rdbms/admin/utltz_upg_check.sql Session altered. INFO: Starting with RDBMS DST update preparation. INFO: NO actual RDBMS DST update will be done by this script. INFO: If an ERROR occurs the script will EXIT sqlplus. INFO: Doing checks for known issues ... INFO: Database version is 19.0.0.0 . INFO: This database is a Multitenant database. INFO: Current container is CDB$ROOT . INFO: Updating the RDBMS DST version of the CDB / CDB$ROOT database INFO: will NOT update the RDBMS DST version of PDB databases in this CDB. INFO: There are no open PDBs . INFO: Database RDBMS DST version is DSTv26 . INFO: No known issues detected. INFO: Now detecting new RDBMS DST version. A prepare window has been successfully started. INFO: Newest RDBMS DST version detected is DSTv32 . INFO: Next step is checking all TSTZ data. INFO: It might take a while before any further output is seen … A prepare window has been successfully ended. INFO: A newer RDBMS DST version than the one currently used is found. INFO: Note that NO DST update was yet done. INFO: Now run utltz_upg_apply.sql to do the actual RDBMS DST update. INFO: Note that the utltz_upg_apply.sql script will INFO: restart the database 2 times WITHOUT any confirmation or prompt. Session altered. SQL> @?/rdbms/admin/utltz_upg_apply.sql Session altered. INFO: If an ERROR occurs, the script will EXIT SQL*Plus. INFO: The database RDBMS DST version will be updated to DSTv32 . INFO: This database is a Multitenant database. INFO: Current container is CDB$ROOT . INFO: Updating the RDBMS DST version of the CDB / CDB$ROOT database INFO: will NOT update the RDBMS DST version of PDB databases in this CDB. INFO: There are no open PDBs . WARNING: This script will restart the database 2 times WARNING: WITHOUT asking ANY confirmation. WARNING: Hit control-c NOW if this is not intended. INFO: Restarting the database in UPGRADE mode to start the DST upgrade. Database closed. Database dismounted. ORACLE instance shut down. ORACLE instance started. Total System Global Area 2432695832 bytes Fixed Size 9137688 bytes Variable Size 654311424 bytes Database Buffers 1761607680 bytes Redo Buffers 7639040 bytes Database mounted. Database opened. INFO: Starting the RDBMS DST upgrade. INFO: Upgrading all SYS owned TSTZ data. INFO: It might take time before any further output is seen … An upgrade window has been successfully started. INFO: Restarting the database in NORMAL mode to upgrade non-SYS TSTZ data. Database closed. Database dismounted. ORACLE instance shut down. ORACLE instance started. Total System Global Area 2432695832 bytes Fixed Size 9137688 bytes Variable Size 654311424 bytes Database Buffers 1761607680 bytes Redo Buffers 7639040 bytes Database mounted. Database opened. INFO: Upgrading all non-SYS TSTZ data. INFO: It might take time before any further output is seen … INFO: Do NOT start any application yet that uses TSTZ data! INFO: Next is a list of all upgraded tables: Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S" Number of failures: 0 Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L" Number of failures: 0 Table list: "MDSYS"."SDO_DIAG_MESSAGES_TABLE" Number of failures: 0 Table list: "DVSYS"."SIMULATION_LOG$" Number of failures: 0 Table list: "DVSYS"."AUDIT_TRAIL$" Number of failures: 0 INFO: Total failures during update of TSTZ data: 0 . An upgrade window has been successfully ended. INFO: Your new Server RDBMS DST version is DSTv32 . INFO: The RDBMS DST update is successfully finished. INFO: Make sure to exit this SQL*Plus session. INFO: Do not use it for timezone related selects. Session altered.
Step:13 Run utlusts.sql ,this script reads the view dba_registry_log and displays the upgrade results for the database components.
Step:14 Drop Restore point as we have successfully completed the manual upgradation.
SQL> drop restore point PRE_UPGRADE;
Restore point dropped.
Step:15 Set COMPATIBALE parameter value to 19.0.0 from 12.2.0
Step:16 Verify DBA_REGISTRY
Step:17 Add TNS Entries in 19c TNS home
[oracle@localhost admin]$ lsnrctl start LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 09-MAR-2021 07:56:41 Copyright (c) 1991, 2019, Oracle. All rights reserved. Starting /u01/app/oracle/product/19.0.0/dbhome_1/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 19.0.0.0.0 - Production System parameter file is /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora Log messages written to /u01/app/oracle/diag/tnslsnr/orcldbs/listener/alert/log.xml Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.26)(PORT=1521))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.26)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production Start Date 09-MAR-2021 07:56:41 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/orcldbs/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.26)(PORT=1521))) Services Summary... Service "europe" has 1 instance(s). Instance "europe", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully [oracle@orcldbs admin]$ lsnrctl status LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 09-MAR-2021 07:56:50 Copyright (c) 1991, 2019, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.26)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production Start Date 09-MAR-2021 07:56:41 Uptime 0 days 0 hr. 0 min. 9 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/orcldbs/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.26)(PORT=1521))) Services Summary... Service "europe" has 1 instance(s). Instance "europe", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully Step:18 Edit oratab,Put an entry of current database in the vi /etc/oratab file.
[oracle@localhost admin]$ cat /etc/oratab | grep -i europe europe:/home/oracle/u03/app/oracle/product/12.2.0/db_1:N [oracle@localhost admin]$ cat /etc/oratab | grep -i europe europe:/u01/app/oracle/product/19.0.0/dbhome_1:Y europe:/home/oracle/u03/app/oracle/product/12.2.0/db_1:N
