In this post we will discuss about the database upgradation from 12cr2 to 19c Manual Mode
Activity : Upgrade Oracle 12.2.0.1.0 to Oracle 19.3
High Level steps :
- Install Oracle 19c in the target server
- Apply latest patch (not covered in this article)
- Take RMAN and Export backup of 12c
- EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS; in 12c database
- PURGE DBA_RECYCLEBIN; in 12c database
- Run Pre upgrade.jar
- Wait until all materialized views have completed refreshing
- Create restore point for flashback incase upgrade fails
- Check Timezone version
- Take care of the Required and Recommended actions before upgrade
- Stop LISTENER in 12c
- Stop the 12c Database
- Copy listener, tnsnames and sqlnet.ora and password file to 19c network/admin
- Copy init file to 19c dbs location
- Start Listener in 19c location
- Start the 19c DB in upgrade mode
- Upgrade the Time Zone
- Gather dictionary statistics after the upgrade
- Execute the postupgrade fixups
- Drop restore point
Please click on the INDEX and browse for more interesting posts.
So let us proceed with the detailed steps.
Here we wont be covering installation of Oracle 19c, applying patch to 19c and how to take RMAN backup
We will straight away jump to the upgrade activtiy from executing stats gather
Detailed Steps :
Gather dictionary statistics :
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
Purge the DBA Recycle bin :
PURGE DBA_RECYCLEBIN;
Run Pre upgrade.jar :
[oracle@localhost ~]$ /u01/app/oracle/product/12.2.0/dbhome_1/jdk/bin/java -jar /u01/app/oracle/product/19c/rdbms/admin/preupgrade.jar TERMINAL TEXT Report generated by Oracle Database Pre-Upgrade Information Tool Version 19.0.0.0.0 Build: 1 on 2021-03-17T17:14:07 Upgrade-To version: 19.0.0.0.0 ======================================= Status of the database prior to upgrade ======================================= Database Name: INFRADB 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: ARCHIVELOG Readonly: FALSE Edition: EE Oracle Component Upgrade Action Current Status ---------------- -------------- -------------- Oracle Server [to be upgraded] VALID JServer JAVA Virtual Machine [to be upgraded] VALID Oracle XDK for Java [to be upgraded] VALID Real Application Clusters [to be upgraded] OPTION OFF Oracle Workspace Manager [to be upgraded] VALID OLAP Analytic Workspace [to be upgraded] VALID Oracle Label Security [to be upgraded] VALID Oracle Database Vault [to be upgraded] VALID Oracle Text [to be upgraded] VALID Oracle XML Database [to be upgraded] VALID Oracle Java Packages [to be upgraded] VALID Oracle Multimedia [to be upgraded] VALID Oracle Spatial [to be upgraded] VALID Oracle OLAP API [to be upgraded] VALID ============== BEFORE UPGRADE ============== REQUIRED ACTIONS ================ 1. Set DB_RECOVERY_FILE_DEST_SIZE initialization parameter to at least 9430 MB. Check alert log during the upgrade to ensure there is remaining free space available in the recovery area. DB_RECOVERY_FILE_DEST_SIZE is set at 8192 MB. There is currently 8174 MB of free space remaining, which may not be adequate for the upgrade. Currently: Fast recovery area : /u01/app/oracle/fast_recovery_area/infradb Limit : 8192 MB Used : 18 MB Available : 8174 MB The database has archivelog mode enabled, and the upgrade process will need free space to generate archived logs to the recovery area specified by initialization parameter DB_RECOVERY_FILE_DEST. The logs generated must not overflow the limit set by DB_RECOVERY_FILE_DEST_SIZE, as that can cause the upgrade to not proceed. RECOMMENDED ACTIONS =================== 2. (AUTOFIXUP) Gather stale data dictionary statistics prior to database upgrade in off-peak time using: EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS; Dictionary statistics do not exist or are stale (not up-to-date). Dictionary statistics help the Oracle optimizer find efficient SQL execution plans and are essential for proper upgrade timing. Oracle recommends gathering dictionary statistics in the last 24 hours before database upgrade. For information on managing optimizer statistics, refer to the 12.2.0.1 Oracle Database SQL Tuning Guide. 3. (AUTOFIXUP) Gather statistics on fixed objects prior the upgrade. None of the fixed object tables have had stats collected. Gathering statistics on fixed objects, if none have been gathered yet, is recommended prior to upgrading. For information on managing optimizer statistics, refer to the 12.2.0.1 Oracle Database SQL Tuning Guide. INFORMATION ONLY ================ 4. To help you keep track of your tablespace allocations, the following AUTOEXTEND tablespaces are expected to successfully EXTEND during the upgrade process. Min Size Tablespace Size For Upgrade ---------- ---------- ----------- SYSAUX 470 MB 500 MB SYSTEM 800 MB 912 MB TEMP 33 MB 150 MB UNDOTBS1 70 MB 439 MB Minimum tablespace sizes for upgrade are estimates. 5. No action needed. Using default parallel upgrade options, this CDB with 2 PDBs will first upgrade the CDB$ROOT, and then upgrade at most 1 PDBs at a time using 2 parallel processes per PDB. The number of PDBs upgraded in parallel and the number of parallel processes per PDB can be adjusted as described in Database Upgrade Guide. 6. Check the Oracle Backup and Recovery User's Guide for information on how to manage an RMAN recovery catalog schema. If you are using a version of the recovery catalog schema that is older than that required by the RMAN client version, then you must upgrade the catalog schema. It is good practice to have the catalog schema the same or higher version than the RMAN client version you are using. ORACLE GENERATED FIXUP SCRIPT ============================= All of the issues in database INFRADB container CDB$ROOT which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by executing the following from within the container SQL>@/u01/app/oracle/cfgtoollogs/infradb/preupgrade/preupgrade_fixups.sql ============= AFTER UPGRADE ============= REQUIRED ACTIONS ================ None RECOMMENDED ACTIONS =================== 7. Upgrade the database time zone file using the DBMS_DST package. The database is using time zone file version 26 and the target 19 release ships with time zone file version 32. Oracle recommends upgrading to the desired (latest) version of the time zone file. For more information, refer to "Upgrading the Time Zone File and Timestamp with Time Zone Data" in the 19 Oracle Database Globalization Support Guide. 8. To identify directory objects with symbolic links in the path name, run $ORACLE_HOME/rdbms/admin/utldirsymlink.sql AS SYSDBA after upgrade. Recreate any directory objects listed, using path names that contain no symbolic links. Some directory object path names may currently contain symbolic links. Starting in Release 18c, symbolic links are not allowed in directory object path names used with BFILE data types, the UTL_FILE package, or external tables. 9. (AUTOFIXUP) Gather dictionary statistics after the upgrade using the command: EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS; Oracle recommends gathering dictionary statistics after upgrade. Dictionary statistics provide essential information to the Oracle optimizer to help it find efficient SQL execution plans. After a database upgrade, statistics need to be re-gathered as there can now be tables that have significantly changed during the upgrade or new tables that do not have statistics gathered yet. 10. Gather statistics on fixed objects after the upgrade and when there is a representative workload on the system using the command: EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS; This recommendation is given for all preupgrade runs. Fixed object statistics provide essential information to the Oracle optimizer to help it find efficient SQL execution plans. Those statistics are specific to the Oracle Database release that generates them, and can be stale upon database upgrade. For information on managing optimizer statistics, refer to the 12.2.0.1 Oracle Database SQL Tuning Guide. ORACLE GENERATED FIXUP SCRIPT ============================= All of the issues in database INFRADB container CDB$ROOT which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by executing the following from within the container SQL>@/u01/app/oracle/cfgtoollogs/infradb/preupgrade/postupgrade_fixups.sql Report generated by Oracle Database Pre-Upgrade Information Tool Version 19.0.0.0.0 Build: 1 on 2021-03-17T17:14:24 Upgrade-To version: 19.0.0.0.0 ======================================= Status of the database prior to upgrade ======================================= Database Name: INFRADB 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: ARCHIVELOG Readonly: TRUE Edition: EE Oracle Component Upgrade Action Current Status ---------------- -------------- -------------- Oracle Server [to be upgraded] VALID JServer JAVA Virtual Machine [to be upgraded] VALID Oracle XDK for Java [to be upgraded] VALID Real Application Clusters [to be upgraded] OPTION OFF Oracle Workspace Manager [to be upgraded] VALID OLAP Analytic Workspace [to be upgraded] VALID Oracle Label Security [to be upgraded] VALID Oracle Database Vault [to be upgraded] VALID Oracle Text [to be upgraded] VALID Oracle XML Database [to be upgraded] VALID Oracle Java Packages [to be upgraded] VALID Oracle Multimedia [to be upgraded] VALID Oracle Spatial [to be upgraded] VALID Oracle OLAP API [to be upgraded] VALID ============== BEFORE UPGRADE ============== REQUIRED ACTIONS ================ None RECOMMENDED ACTIONS =================== 1. (AUTOFIXUP) Gather stale data dictionary statistics prior to database upgrade in off-peak time using: EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS; Dictionary statistics do not exist or are stale (not up-to-date). Dictionary statistics help the Oracle optimizer find efficient SQL execution plans and are essential for proper upgrade timing. Oracle recommends gathering dictionary statistics in the last 24 hours before database upgrade. For information on managing optimizer statistics, refer to the 12.2.0.1 Oracle Database SQL Tuning Guide. 2. (AUTOFIXUP) Gather statistics on fixed objects prior the upgrade. None of the fixed object tables have had stats collected. Gathering statistics on fixed objects, if none have been gathered yet, is recommended prior to upgrading. For information on managing optimizer statistics, refer to the 12.2.0.1 Oracle Database SQL Tuning Guide. INFORMATION ONLY ================ 3. To help you keep track of your tablespace allocations, the following AUTOEXTEND tablespaces are expected to successfully EXTEND during the upgrade process. Min Size Tablespace Size For Upgrade ---------- ---------- ----------- SYSAUX 330 MB 500 MB SYSTEM 250 MB 360 MB TEMP 64 MB 150 MB UNDOTBS1 100 MB 439 MB Minimum tablespace sizes for upgrade are estimates. ORACLE GENERATED FIXUP SCRIPT ============================= All of the issues in database INFRADB container PDB$SEED which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by executing the following from within the container SQL>@/u01/app/oracle/cfgtoollogs/infradb/preupgrade/preupgrade_fixups.sql ============= AFTER UPGRADE ============= REQUIRED ACTIONS ================ None RECOMMENDED ACTIONS =================== 4. Upgrade the database time zone file using the DBMS_DST package. The database is using time zone file version 26 and the target 19 release ships with time zone file version 32. Oracle recommends upgrading to the desired (latest) version of the time zone file. For more information, refer to "Upgrading the Time Zone File and Timestamp with Time Zone Data" in the 19 Oracle Database Globalization Support Guide. 5. To identify directory objects with symbolic links in the path name, run $ORACLE_HOME/rdbms/admin/utldirsymlink.sql AS SYSDBA after upgrade. Recreate any directory objects listed, using path names that contain no symbolic links. Some directory object path names may currently contain symbolic links. Starting in Release 18c, symbolic links are not allowed in directory object path names used with BFILE data types, the UTL_FILE package, or external tables. 6. (AUTOFIXUP) Gather dictionary statistics after the upgrade using the command: EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS; Oracle recommends gathering dictionary statistics after upgrade. Dictionary statistics provide essential information to the Oracle optimizer to help it find efficient SQL execution plans. After a database upgrade, statistics need to be re-gathered as there can now be tables that have significantly changed during the upgrade or new tables that do not have statistics gathered yet. 7. Gather statistics on fixed objects after the upgrade and when there is a representative workload on the system using the command: EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS; This recommendation is given for all preupgrade runs. Fixed object statistics provide essential information to the Oracle optimizer to help it find efficient SQL execution plans. Those statistics are specific to the Oracle Database release that generates them, and can be stale upon database upgrade. For information on managing optimizer statistics, refer to the 12.2.0.1 Oracle Database SQL Tuning Guide. ORACLE GENERATED FIXUP SCRIPT ============================= All of the issues in database INFRADB container PDB$SEED which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by executing the following from within the container SQL>@/u01/app/oracle/cfgtoollogs/infradb/preupgrade/postupgrade_fixups.sql Report generated by Oracle Database Pre-Upgrade Information Tool Version 19.0.0.0.0 Build: 1 on 2021-03-17T17:14:39 Upgrade-To version: 19.0.0.0.0 ======================================= Status of the database prior to upgrade ======================================= Database Name: INFRADB Container Name: INFRAPDB 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: ARCHIVELOG Readonly: FALSE Edition: EE Oracle Component Upgrade Action Current Status ---------------- -------------- -------------- Oracle Server [to be upgraded] VALID JServer JAVA Virtual Machine [to be upgraded] VALID Oracle XDK for Java [to be upgraded] VALID Real Application Clusters [to be upgraded] OPTION OFF Oracle Workspace Manager [to be upgraded] VALID OLAP Analytic Workspace [to be upgraded] VALID Oracle Label Security [to be upgraded] VALID Oracle Database Vault [to be upgraded] VALID Oracle Text [to be upgraded] VALID Oracle XML Database [to be upgraded] VALID Oracle Java Packages [to be upgraded] VALID Oracle Multimedia [to be upgraded] VALID Oracle Spatial [to be upgraded] VALID Oracle OLAP API [to be upgraded] VALID ============== BEFORE UPGRADE ============== REQUIRED ACTIONS ================ None RECOMMENDED ACTIONS =================== 1. (AUTOFIXUP) Gather stale data dictionary statistics prior to database upgrade in off-peak time using: EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS; Dictionary statistics do not exist or are stale (not up-to-date). Dictionary statistics help the Oracle optimizer find efficient SQL execution plans and are essential for proper upgrade timing. Oracle recommends gathering dictionary statistics in the last 24 hours before database upgrade. For information on managing optimizer statistics, refer to the 12.2.0.1 Oracle Database SQL Tuning Guide. 2. (AUTOFIXUP) Gather statistics on fixed objects prior the upgrade. None of the fixed object tables have had stats collected. Gathering statistics on fixed objects, if none have been gathered yet, is recommended prior to upgrading. For information on managing optimizer statistics, refer to the 12.2.0.1 Oracle Database SQL Tuning Guide. INFORMATION ONLY ================ 3. To help you keep track of your tablespace allocations, the following AUTOEXTEND tablespaces are expected to successfully EXTEND during the upgrade process. Min Size Tablespace Size For Upgrade ---------- ---------- ----------- SYSAUX 350 MB 500 MB SYSTEM 250 MB 360 MB TEMP 64 MB 150 MB UNDOTBS1 100 MB 439 MB Minimum tablespace sizes for upgrade are estimates. ORACLE GENERATED FIXUP SCRIPT ============================= All of the issues in database INFRADB container INFRAPDB which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by executing the following from within the container SQL>@/u01/app/oracle/cfgtoollogs/infradb/preupgrade/preupgrade_fixups.sql ============= AFTER UPGRADE ============= REQUIRED ACTIONS ================ None RECOMMENDED ACTIONS =================== 4. Upgrade the database time zone file using the DBMS_DST package. The database is using time zone file version 26 and the target 19 release ships with time zone file version 32. Oracle recommends upgrading to the desired (latest) version of the time zone file. For more information, refer to "Upgrading the Time Zone File and Timestamp with Time Zone Data" in the 19 Oracle Database Globalization Support Guide. 5. To identify directory objects with symbolic links in the path name, run $ORACLE_HOME/rdbms/admin/utldirsymlink.sql AS SYSDBA after upgrade. Recreate any directory objects listed, using path names that contain no symbolic links. Some directory object path names may currently contain symbolic links. Starting in Release 18c, symbolic links are not allowed in directory object path names used with BFILE data types, the UTL_FILE package, or external tables. 6. (AUTOFIXUP) Gather dictionary statistics after the upgrade using the command: EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS; Oracle recommends gathering dictionary statistics after upgrade. Dictionary statistics provide essential information to the Oracle optimizer to help it find efficient SQL execution plans. After a database upgrade, statistics need to be re-gathered as there can now be tables that have significantly changed during the upgrade or new tables that do not have statistics gathered yet. 7. Gather statistics on fixed objects after the upgrade and when there is a representative workload on the system using the command: EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS; This recommendation is given for all preupgrade runs. Fixed object statistics provide essential information to the Oracle optimizer to help it find efficient SQL execution plans. Those statistics are specific to the Oracle Database release that generates them, and can be stale upon database upgrade. For information on managing optimizer statistics, refer to the 12.2.0.1 Oracle Database SQL Tuning Guide. ORACLE GENERATED FIXUP SCRIPT ============================= All of the issues in database INFRADB container INFRAPDB which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by executing the following from within the container SQL>@/u01/app/oracle/cfgtoollogs/infradb/preupgrade/postupgrade_fixups.sql ================== PREUPGRADE SUMMARY ================== /u01/app/oracle/cfgtoollogs/infradb/preupgrade/preupgrade.log /u01/app/oracle/cfgtoollogs/infradb/preupgrade/preupgrade_fixups.sql /u01/app/oracle/cfgtoollogs/infradb/preupgrade/postupgrade_fixups.sql Execute fixup scripts across the entire CDB: Before upgrade: 1. Execute preupgrade fixups with the below command $ORACLE_HOME/perl/bin/perl -I$ORACLE_HOME/perl/lib -I$ORACLE_HOME/rdbms/admin $ORACLE_HOME/rdbms/admin/catcon.pl -l /u01/app/oracle/cfgtoollogs/infradb/preupgrade/ -b preup_infradb /u01/app/oracle/cfgtoollogs/infradb/preupgrade/preupgrade_fixups.sql 2. Review logs under /u01/app/oracle/cfgtoollogs/infradb/preupgrade/ After the upgrade: 1. Execute postupgrade fixups with the below command $ORACLE_HOME/perl/bin/perl -I$ORACLE_HOME/perl/lib -I$ORACLE_HOME/rdbms/admin $ORACLE_HOME/rdbms/admin/catcon.pl -l /u01/app/oracle/cfgtoollogs/infradb/preupgrade/ -b postup_infradb /u01/app/oracle/cfgtoollogs/infradb/preupgrade/postupgrade_fixups.sql 2. Review logs under /u01/app/oracle/cfgtoollogs/infradb/preupgrade/ Preupgrade complete: 2021-03-17T17:14:44
Check the Pre Upgrade logs and take necessary steps as mentioned in it
For CDB :
- As per REQUIRED ACTIONS, Set DB_RECOVERY_FILE_DEST_SIZE initialization parameter to at least 9430 MB.
- Resize the tablespace
alter database datafile '/u01/app/oracle/oradata/infradb/sysaux01.dbf' resize 500M;
alter database datafile '/u01/app/oracle/oradata/infradb/system01.dbf' resize 950M;
alter database datafile '/u01/app/oracle/oradata/infradb/undotbs01.dbf' resize 450M;
alter database tempfile '/u01/app/oracle/oradata/infradb/temp01.dbf' resize 150M;
- Run the ORACLE GENERATED FIXUP SCRIPT
All of the issues in database INFRADB container CDB$ROOT which are identified above as BEFORE UPGRADE “(AUTOFIXUP)” can be resolved by executing the following from within the container
SQL>@/u01/app/oracle/cfgtoollogs/infradb/preupgrade/preupgrade_fixups.sql
For PDB$SEED :
To work in PDB$SEED, the Template Container needs to be in READ WRITE Mode. To change the PDB$SEED to READ WRITE mode, execute the below steps :
alter pluggable database pdb$seed close immediate instances=all;
alter pluggable database pdb$seed OPEN READ WRITE;
- As per the Pre Upgrade logs, Resize the Tablespace
alter database datafile '/u01/app/oracle/oradata/infradb/pdbseed/system01.dbf' resize 360M; alter database datafile '/u01/app/oracle/oradata/infradb/pdbseed/sysaux01.dbf' resize 500M; alter database datafile '/u01/app/oracle/oradata/infradb/pdbseed/undotbs01.dbf' resize 450M; alter database tempfile '/u01/app/oracle/oradata/infradb/pdbseed/temp012021-03-16_17-42-12-595-PM.dbf' resize 150M
- Run the ORACLE GENERATED FIXUP SCRIPT
-
All of the issues in database INFRADB container PDB$SEED which are identified above as AFTER UPGRADE “(AUTOFIXUP)” can be resolved by executing the following from within the container
-
SQL>@/u01/app/oracle/cfgtoollogs/infradb/preupgrade/postupgrade_fixups.sql
Change the mode to PDB$SEED to READ ONLY Mode
alter pluggable database pdb$seed close immediate instances=all; alter pluggable database pdb$seed OPEN READ ONLY;
For Pluggable Database (INFRAPDB)
- Resize the tablespace
alter database datafile '/u01/app/oracle/oradata/infradb/infrapdb/system01.dbf' resize 360M; alter database datafile '/u01/app/oracle/oradata/infradb/infrapdb/sysaux01.dbf' resize 500M; alter database datafile '/u01/app/oracle/oradata/infradb/infrapdb/undotbs01.dbf' resize 450M; alter database tempfile '/u01/app/oracle/oradata/infradb/infrapdb/temp01.dbf' resize 150M;
- Run the ORACLE GENERATED FIXUP SCRIPT
All of the issues in database INFRADB container INFRAPDB which are identified above as BEFORE UPGRADE “(AUTOFIXUP)” can be resolved by executing the following from within the container
SQL>@/u01/app/oracle/cfgtoollogs/infradb/preupgrade/preupgrade_fixups.sql
Execute preupgrade fixups with the below command
[oracle@localhost ~]$ $ORACLE_HOME/perl/bin/perl -I$ORACLE_HOME/perl/lib -I$ORACLE_HOME/rdbms/admin $ORACLE_HOME/rdbms/admin/catcon.pl -l /u01/app/oracle/cfgtoollogs/infradb/preupgrade/ -b preup_infradb /u01/app/oracle/cfgtoollogs/infradb/preupgrade/preupgrade_fixups.sql
catcon: ALL catcon-related output will be written to [/u01/app/oracle/cfgtoollogs/infradb/preupgrade//preup_infradb_catcon_27451.lst]
catcon: See [/u01/app/oracle/cfgtoollogs/infradb/preupgrade//preup_infradb*.log] files for output generated by scripts
catcon: See [/u01/app/oracle/cfgtoollogs/infradb/preupgrade//preup_infradb_*.lst] files for spool files, if any
catcon.pl: completed successfully
Create restore point for flashback incase upgrade fails :
select flashback_on from v$database; show parameter compatible show parameter recovery select * from V$restore_point; create restore point 19UPGRADE guarantee flashback database; col name for a20 col GUARANTEE_FLASHBACK_DATABASE for a10 col TIME for a60 set lines 190 select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point; startup force
Check Timezone version from each container :
SELECT version FROM v$timezone_file;
Before upgrading Oracle Database, you must wait until all materialized views have completed refreshing.
SQL> SELECT o.name FROM sys.obj$ o, sys.user$ u, sys.sum$ s WHERE o.type# = 42 AND bitand(s.mflags, 8) =8;
no rows selected
SQL>
SQL> declare
list_failures integer(3) :=0;
begin
DBMS_MVIEW.REFRESH_ALL_MVIEWS(list_failures,’C’,”, TRUE, FALSE);
end;
/ 2 3 4 5 6
PL/SQL procedure successfully completed.
Stop the 12c Listener :
lsnrctl stop LISTENER_INFRA
Shutdown the database after creating pfile :
create pfile from spfile; shut immediate
Copy listener, tnsnames and sqlnet.ora and password file to 19c network/admin
Copy init file to 19c dbs location
Start Listener in 19c location
lsnrctl start LISTENER_INFRA
Start the 19c DB in upgrade mode
SQL> startup upgrade; SQL> set pages 9999 lines 300 SQL> set pages 9999 lines 300 SQL> col OPEN_MODE for a10 SQL> col HOST_NAME for a30 SQL> select name DB_NAME,HOST_NAME,DATABASE_ROLE,OPEN_MODE,log_mode,version DB_VERSION,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,v$instance; DB_NAME HOST_NAME DATABASE_ROLE OPEN_MODE LOG_MODE DB_VERSION LOGINS DB UP TIME --------- -------------------- ---------- ----------- ----------- ------------- ------------ ---------- INFRADB localhost.localdomain PRIMARY READ WRITE ARCHIVELOG 19.0.0.0.0 RESTRICTED 17-MAR-2021 20:18:29 SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ---------- -------- ---------- 2 PDB$SEED MIGRATE YES 3 INFRAPDB MOUNTED SQL> select name,open_mode,cdb,version,status from v$database,v$instance; NAME OPEN_MODE CDB VERSION STATUS --------- ---------- --- ----------------- ------------ INFRADB READ WRITE YES 19.0.0.0.0 OPEN MIGRATE
Open the PDB in upgrade mode :
SQL> ALTER PLUGGABLE DATABASE INFRAPDB open upgrade; SQL> ALTER PLUGGABLE DATABASE INFRAPDB open upgrade; Pluggable database altered. SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ------ --------- ---------- -------- 2 PDB$SEED MIGRATE YES 3 INFRAPDB MIGRATE YES
Run the Upgrade :
[oracle@localhost ~]$ cd $ORACLE_HOME/bin [oracle@localhost bin]$ ls -ltr dbupgrade -rwxr-x---. 1 oracle oinstall 3136 Apr 17 2019 dbupgrade [oracle@localhost bin]$ pwd /u01/app/oracle/product/19c/bin [oracle@localhost bin]$ ./dbupgrade Argument list for [/u01/app/oracle/product/19c/rdbms/admin/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 = 0 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.3.0.0.0DBRU_LINUX.X64_190417] /u01/app/oracle/product/19c/rdbms/admin/orahome = [/u01/app/oracle/product/19c] /u01/app/oracle/product/19c/bin/orabasehome = [/u01/app/oracle/product/19c] catctlGetOraBaseLogDir = [/u01/app/oracle/product/19c] Analyzing file /u01/app/oracle/product/19c/rdbms/admin/catupgrd.sql Log file directory = [/tmp/cfgtoollogs/upgrade20210315103606] catcon::set_log_file_base_path: ALL catcon-related output will be written to [/tmp/cfgtoollogs/upgrade20210315103606/catupgrd_catcon_9679.lst] catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20210315103606/catupgrd*.log] files for output generated by scripts catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20210315103606/catupgrd_*.lst] files for spool files, if any Number of Cpus = 2 Database Name = infra DataBase Version = 12.2.0.1.0 catcon::set_log_file_base_path: ALL catcon-related output will be written to [/u01/app/oracle/product/19c/cfgtoollogs/infra/upgrade20210315103623/catupgrdcdbroot_catcon_9679.lst] catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19c/cfgtoollogs/infra/upgrade20210315103623/catupgrdcdbroot*.log] files for output generated by scripts catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19c/cfgtoollogs/infra/upgrade20210315103623/catupgrdcdbroot_*.lst] files for spool files, if any Log file directory = [/u01/app/oracle/product/19c/cfgtoollogs/infra/upgrade20210315103623] PDB Parallel SQL Process Count = [2] is higher or equal to CPU Count = [2] Concurrent PDB Upgrades defaulting to CPU Count [2] Parallel SQL Process Count (PDB) = 2 Parallel SQL Process Count (CDB$ROOT) = 4 Concurrent PDB Upgrades = 2 Generated PDB Inclusion:[PDB$SEED INFRAPDB] Components in [CDB$ROOT] Installed [APS CATALOG CATJAVA CATPROC CONTEXT DV JAVAVM OLS ORDIM OWM SDO XDB XML XOQ] Not Installed [APEX EM MGW ODM RAC WK] ------------------------------------------------------ Phases [0-107] Start Time:[2021_03_15 10:36:49] Container Lists Inclusion:[CDB$ROOT] Exclusion:[NONE] ------------------------------------------------------ *********** Executing Change Scripts *********** Serial Phase #:0 [CDB$ROOT] Files:1 Time: 50s *************** Catalog Core SQL *************** Serial Phase #:1 [CDB$ROOT] Files:5 Time: 54s Restart Phase #:2 [CDB$ROOT] Files:1 Time: 3s *********** Catalog Tables and Views *********** Parallel Phase #:3 [CDB$ROOT] Files:19 Time: 28s Restart Phase #:4 [CDB$ROOT] Files:1 Time: 4s ************* Catalog Final Scripts ************ Serial Phase #:5 [CDB$ROOT] Files:7 Time: 26s ***************** Catproc Start **************** Serial Phase #:6 [CDB$ROOT] Files:1 Time: 21s ***************** Catproc Types **************** Serial Phase #:7 [CDB$ROOT] Files:2 Time: 20s Restart Phase #:8 [CDB$ROOT] Files:1 Time: 4s **************** Catproc Tables **************** Parallel Phase #:9 [CDB$ROOT] Files:67 Time: 44s Restart Phase #:10 [CDB$ROOT] Files:1 Time: 4s ************* Catproc Package Specs ************ Serial Phase #:11 [CDB$ROOT] Files:1 Time: 95s Restart Phase #:12 [CDB$ROOT] Files:1 Time: 3s ************** Catproc Procedures ************** Parallel Phase #:13 [CDB$ROOT] Files:94 Time: 21s Restart Phase #:14 [CDB$ROOT] Files:1 Time: 2s Parallel Phase #:15 [CDB$ROOT] Files:120 Time: 28s Restart Phase #:16 [CDB$ROOT] Files:1 Time: 4s Serial Phase #:17 [CDB$ROOT] Files:22 Time: 10s Restart Phase #:18 [CDB$ROOT] Files:1 Time: 2s ***************** Catproc Views **************** Parallel Phase #:19 [CDB$ROOT] Files:32 Time: 33s Restart Phase #:20 [CDB$ROOT] Files:1 Time: 3s Serial Phase #:21 [CDB$ROOT] Files:3 Time: 25s Restart Phase #:22 [CDB$ROOT] Files:1 Time: 3s Parallel Phase #:23 [CDB$ROOT] Files:25 Time: 221s Restart Phase #:24 [CDB$ROOT] Files:1 Time: 3s Parallel Phase #:25 [CDB$ROOT] Files:12 Time: 146s Restart Phase #:26 [CDB$ROOT] Files:1 Time: 4s Serial Phase #:27 [CDB$ROOT] Files:1 Time: 0s Serial Phase #:28 [CDB$ROOT] Files:3 Time: 10s Serial Phase #:29 [CDB$ROOT] Files:1 Time: 0s Restart Phase #:30 [CDB$ROOT] Files:1 Time: 3s *************** Catproc CDB Views ************** Serial Phase #:31 [CDB$ROOT] Files:1 Time: 7s Restart Phase #:32 [CDB$ROOT] Files:1 Time: 4s Serial Phase #:34 [CDB$ROOT] Files:1 Time: 0s ***************** Catproc PLBs ***************** Serial Phase #:35 [CDB$ROOT] Files:293 Time: 39s Serial Phase #:36 [CDB$ROOT] Files:1 Time: 0s Restart Phase #:37 [CDB$ROOT] Files:1 Time: 4s Serial Phase #:38 [CDB$ROOT] Files:6 Time: 9s Restart Phase #:39 [CDB$ROOT] Files:1 Time: 3s *************** Catproc DataPump *************** Serial Phase #:40 [CDB$ROOT] Files:3 Time: 78s Restart Phase #:41 [CDB$ROOT] Files:1 Time: 3s ****************** Catproc SQL ***************** Parallel Phase #:42 [CDB$ROOT] Files:13 Time: 151s Restart Phase #:43 [CDB$ROOT] Files:1 Time: 2s Parallel Phase #:44 [CDB$ROOT] Files:11 Time: 17s Restart Phase #:45 [CDB$ROOT] Files:1 Time: 3s Parallel Phase #:46 [CDB$ROOT] Files:3 Time: 9s Restart Phase #:47 [CDB$ROOT] Files:1 Time: 4s ************* Final Catproc scripts ************ Serial Phase #:48 [CDB$ROOT] Files:1 Time: 14s Restart Phase #:49 [CDB$ROOT] Files:1 Time: 3s ************** Final RDBMS scripts ************* Serial Phase #:50 [CDB$ROOT] Files:1 Time: 11s ************ Upgrade Component Start *********** Serial Phase #:51 [CDB$ROOT] Files:1 Time: 7s Restart Phase #:52 [CDB$ROOT] Files:1 Time: 3s ********** Upgrading Java and non-Java ********* Serial Phase #:53 [CDB$ROOT] Files:2 Time: 378s ***************** Upgrading XDB **************** Restart Phase #:54 [CDB$ROOT] Files:1 Time: 3s Serial Phase #:56 [CDB$ROOT] Files:3 Time: 16s Serial Phase #:57 [CDB$ROOT] Files:3 Time: 11s Parallel Phase #:58 [CDB$ROOT] Files:10 Time: 13s Parallel Phase #:59 [CDB$ROOT] Files:25 Time: 15s Serial Phase #:60 [CDB$ROOT] Files:4 Time: 18s Serial Phase #:61 [CDB$ROOT] Files:1 Time: 0s Serial Phase #:62 [CDB$ROOT] Files:32 Time: 12s Serial Phase #:63 [CDB$ROOT] Files:1 Time: 0s Parallel Phase #:64 [CDB$ROOT] Files:6 Time: 15s Serial Phase #:65 [CDB$ROOT] Files:2 Time: 31s Serial Phase #:66 [CDB$ROOT] Files:3 Time: 45s **************** Upgrading ORDIM *************** Restart Phase #:67 [CDB$ROOT] Files:1 Time: 3s Serial Phase #:69 [CDB$ROOT] Files:1 Time: 9s Parallel Phase #:70 [CDB$ROOT] Files:2 Time: 41s Restart Phase #:71 [CDB$ROOT] Files:1 Time: 3s Parallel Phase #:72 [CDB$ROOT] Files:2 Time: 10s Serial Phase #:73 [CDB$ROOT] Files:2 Time: 9s ***************** Upgrading SDO **************** Restart Phase #:74 [CDB$ROOT] Files:1 Time: 3s Serial Phase #:76 [CDB$ROOT] Files:1 Time: 60s Serial Phase #:77 [CDB$ROOT] Files:2 Time: 10s Restart Phase #:78 [CDB$ROOT] Files:1 Time: 3s Serial Phase #:79 [CDB$ROOT] Files:1 Time: 63s Restart Phase #:80 [CDB$ROOT] Files:1 Time: 4s Parallel Phase #:81 [CDB$ROOT] Files:3 Time: 73s Restart Phase #:82 [CDB$ROOT] Files:1 Time: 4s Serial Phase #:83 [CDB$ROOT] Files:1 Time: 14s Restart Phase #:84 [CDB$ROOT] Files:1 Time: 3s Serial Phase #:85 [CDB$ROOT] Files:1 Time: 18s Restart Phase #:86 [CDB$ROOT] Files:1 Time: 2s Parallel Phase #:87 [CDB$ROOT] Files:4 Time: 122s Restart Phase #:88 [CDB$ROOT] Files:1 Time: 3s Serial Phase #:89 [CDB$ROOT] Files:1 Time: 9s Restart Phase #:90 [CDB$ROOT] Files:1 Time: 3s Serial Phase #:91 [CDB$ROOT] Files:2 Time: 19s Restart Phase #:92 [CDB$ROOT] Files:1 Time: 4s Serial Phase #:93 [CDB$ROOT] Files:1 Time: 6s Restart Phase #:94 [CDB$ROOT] Files:1 Time: 3s ******* Upgrading ODM, WK, EXF, RUL, XOQ ******* Serial Phase #:95 [CDB$ROOT] Files:1 Time: 26s Restart Phase #:96 [CDB$ROOT] Files:1 Time: 4s *********** Final Component scripts *********** Serial Phase #:97 [CDB$ROOT] Files:1 Time: 10s ************* Final Upgrade scripts ************ Serial Phase #:98 [CDB$ROOT] Files:1 Time: 288s ******************* Migration ****************** Serial Phase #:99 [CDB$ROOT] Files:1 Time: 6s *** End PDB Application Upgrade Pre-Shutdown *** Serial Phase #:100 [CDB$ROOT] Files:1 Time: 39s Serial Phase #:101 [CDB$ROOT] Files:1 Time: 5s Serial Phase #:102 [CDB$ROOT] Files:1 Time: 66s ***************** Post Upgrade ***************** Serial Phase #:103 [CDB$ROOT] Files:1 Time: 42s **************** Summary report **************** Serial Phase #:104 [CDB$ROOT] Files:1 Time: 8s *** End PDB Application Upgrade Post-Shutdown ** Serial Phase #:105 [CDB$ROOT] Files:1 Time: 18s Serial Phase #:106 [CDB$ROOT] Files:1 Time: 4s Serial Phase #:107 [CDB$ROOT] Files:1 Time: 50s ------------------------------------------------------ Phases [0-107] End Time:[2021_03_15 11:24:39] Container Lists Inclusion:[CDB$ROOT] Exclusion:[NONE] ------------------------------------------------------ Start processing of PDBs (PDB$SEED) [/u01/app/oracle/product/19c/perl/bin/perl /u01/app/oracle/product/19c/rdbms/admin/catctl.pl -I -i pdb_seed -n 2 -c 'PDB$SEED' -l /u01/app/oracle/product/19c/cfgtoollogs/infra/upgrade20210315103623 /u01/app/oracle/product/19c/rdbms/admin/catupgrd.sql] Start processing of PDBs (INFRAPDB) [/u01/app/oracle/product/19c/perl/bin/perl /u01/app/oracle/product/19c/rdbms/admin/catctl.pl -I -i infrapdb -n 2 -c 'INFRAPDB' -l /u01/app/oracle/product/19c/cfgtoollogs/infra/upgrade20210315103623 /u01/app/oracle/product/19c/rdbms/admin/catupgrd.sql] Argument list for [/u01/app/oracle/product/19c/rdbms/admin/catctl.pl] For Oracle internal use only A = 0 Run in c = PDB$SEED Do not run in C = 0 Input Directory d = 0 Echo OFF e = 1 Simulate E = 0 Forced cleanup F = 0 Log Id i = pdb_seed Child Process I = 1 Log Dir l = /u01/app/oracle/product/19c/cfgtoollogs/infra/upgrade20210315103623 Priority List Name L = 0 Upgrade Mode active M = 0 SQL Process Count n = 2 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.3.0.0.0DBRU_LINUX.X64_190417] Argument list for [/u01/app/oracle/product/19c/rdbms/admin/catctl.pl] For Oracle internal use only A = 0 Run in c = INFRAPDB Do not run in C = 0 Input Directory d = 0 Echo OFF e = 1 Simulate E = 0 Forced cleanup F = 0 Log Id i = infrapdb Child Process I = 1 Log Dir l = /u01/app/oracle/product/19c/cfgtoollogs/infra/upgrade20210315103623 Priority List Name L = 0 Upgrade Mode active M = 0 SQL Process Count n = 2 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.3.0.0.0DBRU_LINUX.X64_190417] /u01/app/oracle/product/19c/rdbms/admin/orahome = [/u01/app/oracle/product/19c] /u01/app/oracle/product/19c/rdbms/admin/orahome = [/u01/app/oracle/product/19c] /u01/app/oracle/product/19c/bin/orabasehome = [/u01/app/oracle/product/19c] catctlGetOraBaseLogDir = [/u01/app/oracle/product/19c] Analyzing file /u01/app/oracle/product/19c/rdbms/admin/catupgrd.sql Log file directory = [/u01/app/oracle/product/19c/cfgtoollogs/infra/upgrade20210315103623] /u01/app/oracle/product/19c/bin/orabasehome = [/u01/app/oracle/product/19c] catctlGetOraBaseLogDir = [/u01/app/oracle/product/19c] Analyzing file /u01/app/oracle/product/19c/rdbms/admin/catupgrd.sql Log file directory = [/u01/app/oracle/product/19c/cfgtoollogs/infra/upgrade20210315103623] catcon::set_log_file_base_path: ALL catcon-related output will be written to [/u01/app/oracle/product/19c/cfgtoollogs/infra/upgrade20210315103623/catupgrdpdb_seed_catcon_16738.lst] catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19c/cfgtoollogs/infra/upgrade20210315103623/catupgrdpdb_seed*.log] files for output generated by scripts catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19c/cfgtoollogs/infra/upgrade20210315103623/catupgrdpdb_seed_*.lst] files for spool files, if any catcon::set_log_file_base_path: ALL catcon-related output will be written to [/u01/app/oracle/product/19c/cfgtoollogs/infra/upgrade20210315103623/catupgrdinfrapdb_catcon_16740.lst] catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19c/cfgtoollogs/infra/upgrade20210315103623/catupgrdinfrapdb*.log] files for output generated by scripts catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19c/cfgtoollogs/infra/upgrade20210315103623/catupgrdinfrapdb_*.lst] files for spool files, if any Number of Cpus = 2 Number of Cpus = 2 Database Name = infra Database Name = infra DataBase Version = 19.0.0.0.0 DataBase Version = 19.0.0.0.0 PDB$SEED Open Mode = [MIGRATE] INFRAPDB Open Mode = [MIGRATE] Generated PDB Inclusion:[PDB$SEED] Generated PDB Inclusion:[INFRAPDB] CDB$ROOT Open Mode = [OPEN] CDB$ROOT Open Mode = [OPEN] Components in [PDB$SEED] Installed [APS CATALOG CATJAVA CATPROC CONTEXT DV JAVAVM OLS ORDIM OWM SDO XDB XML XOQ] Not Installed [APEX EM MGW ODM RAC WK] Components in [INFRAPDB] Installed [APS CATALOG CATJAVA CATPROC CONTEXT DV JAVAVM OLS ORDIM OWM SDO XDB XML XOQ] Not Installed [APEX EM MGW ODM RAC WK] ------------------------------------------------------ Phases [0-107] Start Time:[2021_03_15 11:25:30] Container Lists Inclusion:[PDB$SEED] Exclusion:[NONE] ------------------------------------------------------ ------------------------------------------------------ Phases [0-107] Start Time:[2021_03_15 11:25:31] Container Lists Inclusion:[INFRAPDB] Exclusion:[NONE] ------------------------------------------------------ *********** Executing Change Scripts *********** Serial Phase #:0 [PDB$SEED] Files:1 *********** Executing Change Scripts *********** Serial Phase #:0 [INFRAPDB] Files:1 Time: 84s *************** Catalog Core SQL *************** Serial Phase #:1 [PDB$SEED] Files:5 Time: 84s *************** Catalog Core SQL *************** Serial Phase #:1 [INFRAPDB] Files:5 Time: 76s Restart Phase #:2 [PDB$SEED] Files:1 Time: 75s Restart Phase #:2 [INFRAPDB] Files:1 Time: 1s *********** Catalog Tables and Views *********** Parallel Phase #:3 [PDB$SEED] Files:19 Time: 2s *********** Catalog Tables and Views *********** Parallel Phase #:3 [INFRAPDB] Files:19 Time: 47s Restart Phase #:4 [PDB$SEED] Files:1 Time: 46s Restart Phase #:4 [INFRAPDB] Files:1 Time: 1s ************* Catalog Final Scripts ************ Serial Phase #:5 [PDB$SEED] Files:7 Time: 2s ************* Catalog Final Scripts ************ Serial Phase #:5 [INFRAPDB] Files:7 Time: 34s ***************** Catproc Start **************** Serial Phase #:6 [INFRAPDB] Files:1 Time: 35s ***************** Catproc Start **************** Serial Phase #:6 [PDB$SEED] Files:1 Time: 22s ***************** Catproc Types **************** Serial Phase #:7 [PDB$SEED] Files:2 Time: 22s ***************** Catproc Types **************** Serial Phase #:7 [INFRAPDB] Files:2 Time: 20s Restart Phase #:8 [PDB$SEED] Files:1 Time: 20s Restart Phase #:8 [INFRAPDB] Files:1 Time: 2s **************** Catproc Tables **************** Parallel Phase #:9 [INFRAPDB] Files:67 Time: 3s **************** Catproc Tables **************** Parallel Phase #:9 [PDB$SEED] Files:67 Time: 77s Restart Phase #:10 [INFRAPDB] Files:1 Time: 76s Restart Phase #:10 [PDB$SEED] Files:1 Time: 2s ************* Catproc Package Specs ************ Serial Phase #:11 [INFRAPDB] Files:1 Time: 2s ************* Catproc Package Specs ************ Serial Phase #:11 [PDB$SEED] Files:1 Time: 100s Restart Phase #:12 [INFRAPDB] Files:1 Time: 100s Restart Phase #:12 [PDB$SEED] Files:1 Time: 1s ************** Catproc Procedures ************** Parallel Phase #:13 [PDB$SEED] Files:94 Time: 2s ************** Catproc Procedures ************** Parallel Phase #:13 [INFRAPDB] Files:94 Time: 23s Restart Phase #:14 [INFRAPDB] Files:1 Time: 24s Restart Phase #:14 [PDB$SEED] Files:1 Time: 1s Parallel Phase #:15 [PDB$SEED] Files:120 Time: 2s Parallel Phase #:15 [INFRAPDB] Files:120 Time: 29s Restart Phase #:16 [INFRAPDB] Files:1 Time: 30s Restart Phase #:16 [PDB$SEED] Files:1 Time: 0s Serial Phase #:17 [PDB$SEED] Files:22 Time: 1s Serial Phase #:17 [INFRAPDB] Files:22 Time: 10s Restart Phase #:18 [PDB$SEED] Files:1 Time: 9s Restart Phase #:18 [INFRAPDB] Files:1 Time: 1s ***************** Catproc Views **************** Parallel Phase #:19 [PDB$SEED] Files:32 Time: 2s ***************** Catproc Views **************** Parallel Phase #:19 [INFRAPDB] Files:32 Time: 50s Restart Phase #:20 [INFRAPDB] Files:1 Time: 51s Restart Phase #:20 [PDB$SEED] Files:1 Time: 1s Serial Phase #:21 [INFRAPDB] Files:3 Time: 2s Serial Phase #:21 [PDB$SEED] Files:3 Time: 23s Restart Phase #:22 [INFRAPDB] Files:1 Time: 22s Restart Phase #:22 [PDB$SEED] Files:1 Time: 2s Parallel Phase #:23 [INFRAPDB] Files:25 Time: 2s Parallel Phase #:23 [PDB$SEED] Files:25 Time: 585s Restart Phase #:24 [INFRAPDB] Files:1 Time: 585s Restart Phase #:24 [PDB$SEED] Files:1 Time: 1s Parallel Phase #:25 [PDB$SEED] Files:12 Time: 2s Parallel Phase #:25 [INFRAPDB] Files:12 Time: 219s Restart Phase #:26 [INFRAPDB] Files:1 Time: 220s Restart Phase #:26 [PDB$SEED] Files:1 Time: 1s Serial Phase #:27 [INFRAPDB] Files:1 Time: 0s Serial Phase #:28 [INFRAPDB] Files:3 Time: 2s Serial Phase #:27 [PDB$SEED] Files:1 Time: 0s Serial Phase #:28 [PDB$SEED] Files:3 Time: 29s Serial Phase #:29 [PDB$SEED] Files:1 Time: 0s Restart Phase #:30 [PDB$SEED] Files:1 Time: 30s Serial Phase #:29 [INFRAPDB] Files:1 Time: 0s Restart Phase #:30 [INFRAPDB] Files:1 Time: 2s *************** Catproc CDB Views ************** Serial Phase #:31 [INFRAPDB] Files:1 Time: 2s *************** Catproc CDB Views ************** Serial Phase #:31 [PDB$SEED] Files:1 Time: 6s Restart Phase #:32 [INFRAPDB] Files:1 Time: 6s Restart Phase #:32 [PDB$SEED] Files:1 Time: 1s Serial Phase #:34 [PDB$SEED] Files:1 Time: 0s ***************** Catproc PLBs ***************** Serial Phase #:35 [PDB$SEED] Files:293 Time: 2s Serial Phase #:34 [INFRAPDB] Files:1 Time: 0s ***************** Catproc PLBs ***************** Serial Phase #:35 [INFRAPDB] Files:293 Time: 155s Time: 156s Serial Phase #:36 [INFRAPDB] Files:1 Time: 0s Restart Phase #:37 [INFRAPDB] Files:1 Serial Phase #:36 [PDB$SEED] Files:1 Time: 0s Restart Phase #:37 [PDB$SEED] Files:1 Time: 0s Serial Phase #:38 [INFRAPDB] Files:6 Time: 1s Serial Phase #:38 [PDB$SEED] Files:6 Time: 13s Restart Phase #:39 [PDB$SEED] Files:1 Time: 14s Restart Phase #:39 [INFRAPDB] Files:1 Time: 1s *************** Catproc DataPump *************** Serial Phase #:40 [INFRAPDB] Files:3 Time: 1s *************** Catproc DataPump *************** Serial Phase #:40 [PDB$SEED] Files:3 Time: 79s Restart Phase #:41 [INFRAPDB] Files:1 Time: 79s Restart Phase #:41 [PDB$SEED] Files:1 Time: 1s ****************** Catproc SQL ***************** Parallel Phase #:42 [PDB$SEED] Files:13 Time: 2s ****************** Catproc SQL ***************** Parallel Phase #:42 [INFRAPDB] Files:13 Time: 235s Restart Phase #:43 [INFRAPDB] Files:1 Time: 236s Restart Phase #:43 [PDB$SEED] Files:1 Time: 2s Parallel Phase #:44 [INFRAPDB] Files:11 Time: 2s Parallel Phase #:44 [PDB$SEED] Files:11 Time: 19s Restart Phase #:45 [INFRAPDB] Files:1 Time: 19s Restart Phase #:45 [PDB$SEED] Files:1 Time: 1s Parallel Phase #:46 [INFRAPDB] Files:3 Time: 1s Parallel Phase #:46 [PDB$SEED] Files:3 Time: 9s Restart Phase #:47 [PDB$SEED] Files:1 Time: 9s Restart Phase #:47 [INFRAPDB] Files:1 Time: 2s ************* Final Catproc scripts ************ Serial Phase #:48 [INFRAPDB] Files:1 Time: 3s ************* Final Catproc scripts ************ Serial Phase #:48 [PDB$SEED] Files:1 Time: 20s Restart Phase #:49 [INFRAPDB] Files:1 Time: 19s Restart Phase #:49 [PDB$SEED] Files:1 Time: 1s ************** Final RDBMS scripts ************* Serial Phase #:50 [INFRAPDB] Files:1 Time: 2s ************** Final RDBMS scripts ************* Serial Phase #:50 [PDB$SEED] Files:1 Time: 11s ************ Upgrade Component Start *********** Serial Phase #:51 [INFRAPDB] Files:1 Time: 10s ************ Upgrade Component Start *********** Serial Phase #:51 [PDB$SEED] Files:1 Time: 7s Restart Phase #:52 [PDB$SEED] Files:1 Time: 7s Restart Phase #:52 [INFRAPDB] Files:1 Time: 1s ********** Upgrading Java and non-Java ********* Serial Phase #:53 [INFRAPDB] Files:2 Time: 2s ********** Upgrading Java and non-Java ********* Serial Phase #:53 [PDB$SEED] Files:2 Time: 349s ***************** Upgrading XDB **************** Restart Phase #:54 [INFRAPDB] Files:1 Time: 348s ***************** Upgrading XDB **************** Restart Phase #:54 [PDB$SEED] Files:1 Time: 2s Serial Phase #:56 [PDB$SEED] Files:3 Time: 2s Serial Phase #:56 [INFRAPDB] Files:3 Time: 19s Time: 19s Serial Phase #:57 [INFRAPDB] Files:3 Serial Phase #:57 [PDB$SEED] Files:3 Time: 10s Parallel Phase #:58 [INFRAPDB] Files:10 Time: 10s Parallel Phase #:58 [PDB$SEED] Files:10 Time: 13s Parallel Phase #:59 [PDB$SEED] Files:25 Time: 13s Parallel Phase #:59 [INFRAPDB] Files:25 Time: 15s Serial Phase #:60 [INFRAPDB] Files:4 Time: 15s Serial Phase #:60 [PDB$SEED] Files:4 Time: 20s Serial Phase #:61 [INFRAPDB] Files:1 Time: 0s Serial Phase #:62 [INFRAPDB] Files:32 Time: 20s Serial Phase #:61 [PDB$SEED] Files:1 Time: 0s Serial Phase #:62 [PDB$SEED] Files:32 Time: 21s Serial Phase #:63 [PDB$SEED] Files:1 Time: 0s Parallel Phase #:64 [PDB$SEED] Files:6 Time: 21s Serial Phase #:63 [INFRAPDB] Files:1 Time: 0s Parallel Phase #:64 [INFRAPDB] Files:6 Time: 16s Serial Phase #:65 [PDB$SEED] Files:2 Time: 16s Serial Phase #:65 [INFRAPDB] Files:2 Time: 35s Serial Phase #:66 [INFRAPDB] Files:3 Time: 35s Serial Phase #:66 [PDB$SEED] Files:3 Time: 48s **************** Upgrading ORDIM *************** Restart Phase #:67 [INFRAPDB] Files:1 Time: 48s **************** Upgrading ORDIM *************** Restart Phase #:67 [PDB$SEED] Files:1 Time: 2s Serial Phase #:69 [INFRAPDB] Files:1 Time: 2s Serial Phase #:69 [PDB$SEED] Files:1 Time: 9s Parallel Phase #:70 [INFRAPDB] Files:2 Time: 9s Parallel Phase #:70 [PDB$SEED] Files:2 Time: 28s Restart Phase #:71 [PDB$SEED] Files:1 Time: 28s Restart Phase #:71 [INFRAPDB] Files:1 Time: 0s Parallel Phase #:72 [INFRAPDB] Files:2 Time: 1s Parallel Phase #:72 [PDB$SEED] Files:2 Time: 10s Serial Phase #:73 [INFRAPDB] Files:2 Time: 10s Serial Phase #:73 [PDB$SEED] Files:2 Time: 9s ***************** Upgrading SDO **************** Restart Phase #:74 [INFRAPDB] Files:1 Time: 8s ***************** Upgrading SDO **************** Restart Phase #:74 [PDB$SEED] Files:1 Time: 1s Serial Phase #:76 [INFRAPDB] Files:1 Time: 2s Serial Phase #:76 [PDB$SEED] Files:1 Time: 87s Serial Phase #:77 [INFRAPDB] Files:2 Time: 86s Serial Phase #:77 [PDB$SEED] Files:2 Time: 10s Restart Phase #:78 [PDB$SEED] Files:1 Time: 10s Restart Phase #:78 [INFRAPDB] Files:1 Time: 1s Serial Phase #:79 [PDB$SEED] Files:1 Time: 2s Serial Phase #:79 [INFRAPDB] Files:1 Time: 13s Restart Phase #:80 [INFRAPDB] Files:1 Time: 14s Restart Phase #:80 [PDB$SEED] Files:1 Time: 1s Parallel Phase #:81 [PDB$SEED] Files:3 Time: 2s Parallel Phase #:81 [INFRAPDB] Files:3 Time: 105s Restart Phase #:82 [INFRAPDB] Files:1 Time: 106s Restart Phase #:82 [PDB$SEED] Files:1 Time: 2s Serial Phase #:83 [PDB$SEED] Files:1 Time: 2s Serial Phase #:83 [INFRAPDB] Files:1 Time: 17s Restart Phase #:84 [INFRAPDB] Files:1 Time: 17s Restart Phase #:84 [PDB$SEED] Files:1 Time: 1s Serial Phase #:85 [PDB$SEED] Files:1 Time: 1s Serial Phase #:85 [INFRAPDB] Files:1 Time: 19s Restart Phase #:86 [PDB$SEED] Files:1 Time: 19s Restart Phase #:86 [INFRAPDB] Files:1 Time: 2s Parallel Phase #:87 [INFRAPDB] Files:4 Time: 2s Parallel Phase #:87 [PDB$SEED] Files:4 Time: 180s Restart Phase #:88 [PDB$SEED] Files:1 Time: 180s Restart Phase #:88 [INFRAPDB] Files:1 Time: 2s Serial Phase #:89 [PDB$SEED] Files:1 Time: 2s Serial Phase #:89 [INFRAPDB] Files:1 Time: 8s Restart Phase #:90 [PDB$SEED] Files:1 Time: 8s Restart Phase #:90 [INFRAPDB] Files:1 Time: 2s Serial Phase #:91 [PDB$SEED] Files:2 Time: 2s Serial Phase #:91 [INFRAPDB] Files:2 Time: 33s Restart Phase #:92 [PDB$SEED] Files:1 Time: 33s Restart Phase #:92 [INFRAPDB] Files:1 Time: 1s Serial Phase #:93 [PDB$SEED] Files:1 Time: 1s Serial Phase #:93 [INFRAPDB] Files:1 Time: 6s Restart Phase #:94 [PDB$SEED] Files:1 Time: 7s Restart Phase #:94 [INFRAPDB] Files:1 Time: 2s ******* Upgrading ODM, WK, EXF, RUL, XOQ ******* Serial Phase #:95 [PDB$SEED] Files:1 Time: 1s ******* Upgrading ODM, WK, EXF, RUL, XOQ ******* Serial Phase #:95 [INFRAPDB] Files:1 Time: 29s Restart Phase #:96 [PDB$SEED] Files:1 Time: 29s Restart Phase #:96 [INFRAPDB] Files:1 Time: 0s *********** Final Component scripts *********** Serial Phase #:97 [INFRAPDB] Files:1 Time: 1s *********** Final Component scripts *********** Serial Phase #:97 [PDB$SEED] Files:1 Time: 9s ************* Final Upgrade scripts ************ Serial Phase #:98 [INFRAPDB] Files:1 Time: 9s ************* Final Upgrade scripts ************ Serial Phase #:98 [PDB$SEED] Files:1 Time: 198s ******************* Migration ****************** Serial Phase #:99 [INFRAPDB] Files:1 Time: 197s ******************* Migration ****************** Serial Phase #:99 [PDB$SEED] Files:1 Time: 6s *** End PDB Application Upgrade Pre-Shutdown *** Serial Phase #:100 [INFRAPDB] Files:1 Time: 7s *** End PDB Application Upgrade Pre-Shutdown *** Serial Phase #:100 [PDB$SEED] Files:1 Time: 7s Serial Phase #:101 [INFRAPDB] Files:1 Time: 7s Serial Phase #:101 [PDB$SEED] Files:1 Time: 12s Serial Phase #:102 [PDB$SEED] Files:1 Time: 13s Serial Phase #:102 [INFRAPDB] Files:1 Time: 14s ***************** Post Upgrade ***************** Serial Phase #:103 [PDB$SEED] Files:1 Time: 14s ***************** Post Upgrade ***************** Serial Phase #:103 [INFRAPDB] Files:1 Time: 47s **************** Summary report **************** Serial Phase #:104 [INFRAPDB] Files:1 Time: 8s *** End PDB Application Upgrade Post-Shutdown ** Serial Phase #:105 [INFRAPDB] Files:1 Time: 6s Serial Phase #:106 [INFRAPDB] Files:1 Time: 5s Serial Phase #:107 [INFRAPDB] Files:1 Time: 0s ------------------------------------------------------ Phases [0-107] End Time:[2021_03_15 12:23:12] Container Lists Inclusion:[INFRAPDB] Exclusion:[NONE] ------------------------------------------------------ Grand Total Time: 3464s [INFRAPDB] LOG FILES: (/u01/app/oracle/product/19c/cfgtoollogs/infra/upgrade20210315103623/catupgrdinfrapdb*.log) Upgrade Summary Report Located in: /u01/app/oracle/product/19c/cfgtoollogs/infra/upgrade20210315103623/upg_summary.log Time: 601s **************** Summary report **************** Serial Phase #:104 [PDB$SEED] Files:1 Time: 7s *** End PDB Application Upgrade Post-Shutdown ** Serial Phase #:105 [PDB$SEED] Files:1 Time: 6s Serial Phase #:106 [PDB$SEED] Files:1 Time: 5s Serial Phase #:107 [PDB$SEED] Files:1 Time: 0s ------------------------------------------------------ Phases [0-107] End Time:[2021_03_15 12:32:25] Container Lists Inclusion:[PDB$SEED] Exclusion:[NONE] ------------------------------------------------------ Grand Total Time: 4024s [PDB$SEED] LOG FILES: (/u01/app/oracle/product/19c/cfgtoollogs/infra/upgrade20210315103623/catupgrdpdb_seed*.log) Upgrade Summary Report Located in: /u01/app/oracle/product/19c/cfgtoollogs/infra/upgrade20210315103623/upg_summary.log Time: 2869s For CDB$ROOT Time: 4083s For PDB(s) Grand Total Time: 6952s LOG FILES: (/u01/app/oracle/product/19c/cfgtoollogs/infra/upgrade20210315103623/catupgrdcdbroot*.log) Upgrade Summary Report Located in: /u01/app/oracle/product/19c/cfgtoollogs/infra/upgrade20210315103623/upg_summary.log Grand Total Upgrade Time: [0d:1h:55m:52s]
We are now done with the upgrade.
We should run After Upgrade script now to proceed further :
Check the Pre Upgrade logs and take necessary steps as mentioned in it for After Upgrade steps :
For Whole Container :
Upgrade the Time Zone version of your database :
In a multitenant environment, the time zone file upgrade must be performed in all containers.
If you follow the example of the non-CDB instance, it will only be upgraded in the root container. What’s more, any new PDBs created from the seed will also use the old time zone file. To solve this we run the upgrade in the root container and all PDBs using the “catcon.pl” Perl script.
Create a script called “/tmp/upgrade_tzf.sql” with the following contents.
SHUTDOWN IMMEDIATE;
STARTUP UPGRADE;
SET SERVEROUTPUT ON
DECLARE
l_tz_version PLS_INTEGER;
BEGIN
SELECT DBMS_DST.get_latest_timezone_version
INTO l_tz_version
FROM dual;
DBMS_OUTPUT.put_line('l_tz_version=' || l_tz_version);
DBMS_DST.begin_upgrade(l_tz_version);
END;
/
SHUTDOWN IMMEDIATE;
STARTUP;
SET SERVEROUTPUT ON
DECLARE
l_failures PLS_INTEGER;
BEGIN
DBMS_DST.upgrade_database(l_failures);
DBMS_OUTPUT.put_line('DBMS_DST.upgrade_database : l_failures=' || l_failures);
DBMS_DST.end_upgrade(l_failures);
DBMS_OUTPUT.put_line('DBMS_DST.end_upgrade : l_failures=' || l_failures);
END;
/
Now we run the script in all containers using the “catcon.pl” Perl script. The “-n 1” flag is important. The script does a shutdown and startup. If the script is run in parallel, some of the PDBs will be processed at the same time as the root container, so their upgrade will be interrupted by the root container shutdown/startup. Running with a single worker keeps things sequential.
$ORACLE_HOME/perl/bin/perl \\ -I$ORACLE_HOME/perl/lib \\ -I$ORACLE_HOME/rdbms/admin \\ $ORACLE_HOME/rdbms/admin/catcon.pl \\ -n 1 \\ -l /tmp/ \\ -b upgrade_tzf \\ /tmp/upgrade_tzf.sql
An alternative is to do the root container on its own, then do all the other PDBs in parallel by excluding the root container.
# Root only. $ORACLE_HOME/perl/bin/perl \\ -I$ORACLE_HOME/perl/lib \\ -I$ORACLE_HOME/rdbms/admin \\ $ORACLE_HOME/rdbms/admin/catcon.pl \\ -l /tmp/ \\ -b upgrade_tzf \\ -c 'CDB$ROOT' \\ /tmp/upgrade_tzf.sql # Root excluded. $ORACLE_HOME/perl/bin/perl \\ -I$ORACLE_HOME/perl/lib \\ -I$ORACLE_HOME/rdbms/admin \\ $ORACLE_HOME/rdbms/admin/catcon.pl \\ -l /tmp/ \\ -b upgrade_tzf \\ -C 'CDB$ROOT' \\ /tmp/upgrade_tzf.sql
Check the “upgrade_tzf*.log” files for the result of the upgrade in each container.
Run the Symbolic Link script as SYSDBA as mentioned in the Pre Upgrade logs from all containers one by one :
SQL> $ORACLE_HOME/rdbms/admin/utldirsymlink.sql
If there are any symbolic links present, those wont be valid, as starting from Release 18c, symbolic links are not allowed in directory object path names used with BFILE data types, the UTL_FILE package, or
external tables
Gather statistics on fixed objects after the upgrade from all containers and when there is a representative workload on the system using the command:
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
Run the post upgrade fixup script from each container :
SQL>@/u01/app/oracle/cfgtoollogs/infradb/preupgrade/postupgrade_fixups.sql
Run utlrp.sql script from each container to compile the Invalid Objects and change the status of DBA Registry Components as VALID :
@ORACLE_HOME/rdbms/admin/utlrp.sql
Execute postupgrade fixups with the below command
$ORACLE_HOME/perl/bin/perl -I$ORACLE_HOME/perl/lib -I$ORACLE_HOME/rdbms/admin $ORACLE_HOME/rdbms/admin/catcon.pl -l /u01/app/oracle/cfgtoollogs/infradb/preupgrade/ -b postup_infradb /u01/app/oracle/cfgtoollogs/infradb/preupgrade/postupgrade_fixups.sql
Check the Database (CDB and PDBs) before handing over to Application
SELECT version FROM v$timezone_file; col COMP_ID for a10 col COMP_NAME for a40 col VERSION for a15 set lines 180 set pages 999 select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry; select name,open_mode,cdb,version,status from v$database,v$instance; set pages 9999 lines 300 col OPEN_MODE for a10 col HOST_NAME for a30 select name DB_NAME,HOST_NAME,DATABASE_ROLE,OPEN_MODE,log_mode,version DB_VERSION,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,v$instance;
Drop restore point
SQL> col name for a20
col GUARANTEE_FLASHBACK_DATABASE for a10
col TIME for a60
set lines 190
select NAME,GUARANTEE_FLASHBACK_DATABASE from V$restore_point;
NAME GUARANTEE_
-------------------- ----------
19UPGRADE YES
SQL> drop restore point 19UPGRADE;
Restore point dropped.
SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point;
no rows selected
Warning: If the value of COMPATIBLE parameter is changed to 19.0.0 then can not be downgraded to 12.2.0.1.
we would not have any option other than export/import to downgrade the database.
If you can get downtime after a week or so , I would suggest you not to change compatible
for atleast a week to check database and performance after upgrade and by chance if you need to downgrade
that can be downgraded using the flashback.
If you change COMPATIBLE you can directly drop your restore points as they are useless.
You can’t use Flashback Database to restore point back across a compatibility change of your database.