Database Upgrade From 11.2.0.4 to 19.2.0.0 Using Manual Method:

1.Install new software for Oracle 19c. 

Make sure you have all the OS prerequisites in place by running the 19c preinstall package. 

yum install -y oracle-database-preinstall-19c

yum update -y

Install 19c Software

Run the root scripts when prompted.

2.Run preupgrade.jar

Put the latest “preupgrade.jar” into the 19c Oracle home.

Make sure you are using the original Oracle home and run the “preupgrade.jar”.

$ $ORACLE_BASE/product/19.0.0/dbhome_1/jdk/bin/java -jar 

$ORACLE_BASE/product/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar TERMINAL TEXT

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

>The output from the “preupgrade.jar” lists a number of pre-upgrade recommendations. Some must be manually applied. Others are incorporated into the “preupgrade_fixups.sql” script. 

>Next step is to follow the guidelines displayed by the output, i.e. changing parameters, gathering stats, running the preupgrade_fixups.sql then shutt down the source database and copying the SPFILE and creating a new password file.

3.Upgrade the Database:

Start the database in STARTUP UPGRADE mode and run the upgrade.

# Regular upgrade command.

cd $ORACLE_HOME/rdbms/admin

$ORACLE_HOME/perl/bin/perl catctl.pl catupgrd.sql

$ORACLE_HOME/perl/bin/perl catctl.pl -n 6 -l /home/oracle catupgrd.sql

# Shorthand command.

$ORACLE_HOME/bin/dbupgrade

4.Post-Upgrade:

Postupgrade_fixups.sql and time zone adjustment.

Database is now fully upgraded and ready to go.

5.Plugin the upgraded database as a PDB into a CDB

==============================

Assumptions

Prerequisities
Install 19c Software
Run preupgrade.jar
Perform Pre-Upgrade Actions
Upgrade the Database
Perform Post-Upgrade Actions
Create New Container Database (CDB)
Convert Non-CDB to PDB
Final Steps

===================

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

#) Time zone file.

sqlplus / as sysdba <<EOF

-- Check current settings.

SELECT * FROM v$timezone_file;

SHUTDOWN IMMEDIATE;

STARTUP UPGRADE;

-- Begin upgrade to the latest version.

SET SERVEROUTPUT ON

DECLARE

  l_tz_version PLS_INTEGER;

BEGIN

  l_tz_version := DBMS_DST.get_latest_timezone_version;

  DBMS_OUTPUT.put_line('l_tz_version=' || l_tz_version);

  DBMS_DST.begin_upgrade(l_tz_version);

END;

/

SHUTDOWN IMMEDIATE;

STARTUP;

-- Do the upgrade.

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;

/

-- Check new settings.

SELECT * FROM v$timezone_file;

COLUMN property_name FORMAT A30

COLUMN property_value FORMAT A20

SELECT property_name, property_value

FROM   database_properties

WHERE  property_name LIKE 'DST_%'

ORDER BY property_name;

exit;

EOF

# 12) Ignored

# 13) AUTOFIXUP

# 14) Gather fixed object stats.

sqlplus / as sysdba <<EOF

EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

exit;

EOF

# AUTOFIXUP

sqlplus / as sysdba <<EOF

@/u01/app/oracle/cfgtoollogs/db11g/preupgrade/postupgrade_fixups.sql

exit;

EOF

Convert Non-CDB to PDB:

Convert Oracle 19c Non-CDB to PDB

Execute DBMS_PDB.DESCRIBE

oracle@db21:/opt/oracle/product/19.0.0/dbhome_1/network/admin$ sqlplus sys as sysdba

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.6.0.0.0

 SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

 SQL> startup mount;

ORACLE instance started.

Total System Global Area 2147478488 bytes

Fixed Size                  8874968 bytes

Variable Size            1258291200 bytes

Database Buffers          872415232 bytes

Redo Buffers                7897088 bytes

Database mounted.

 SQL> alter database open read only;

 Database altered.

 SQL> exec DBMS_PDB.DESCRIBE ('/export/home/oracle/dgpdevl.xml');

 PL/SQL procedure successfully completed.

 SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

 Plug in the database to existing Oracle 19c CDB

 SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

 SQL> create pluggable database dgpdevl using '/export/home/oracle/dgpdevl.xml' nocopy tempfile reuse;

Pluggable database created.

SQL> show pdbs

 CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 DGPDEVL                        MOUNTED

 SQL> alter pluggable database open;

 Pluggable database altered.

 Execute noncdb_to_pdb.sql script

 SQL> alter session set container=DGPDEVL;

 Session altered.

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

….

….

 13:47:57 SQL> set tab OFF

13:47:57 SQL> set termout ON

13:47:57 SQL> set time OFF

SQL> set timing OFF

SQL> set trimout ON

SQL> set trimspool ON

SQL> set underline "-"

SQL> set verify OFF

SQL> set wrap ON

SQL> set xmloptimizationcheck OFF

 Verify PDB plug-in operation via PDB_PLUG_IN_VIOLATIONS

 SQL> select con_id, type, message, status

  from PDB_PLUG_IN_VIOLATIONS

 where status <>'RESOLVED'

 order by time; 

    CON_ID TYPE      MESSAGE                                                                                              STATUS

---------- --------- ---------------------------------------------------------------------------------------------------- ---------

         4 WARNING   Character set mismatch: PDB character set US7ASCII. CDB character set AL32UTF8.                      PENDING

         4 WARNING   Database option APS mismatch: PDB installed version NULL. CDB installed version 19.0.0.0.0.          PENDING

         4 WARNING   Database option CATJAVA mismatch: PDB installed version NULL. CDB installed version 19.0.0.0.0.      PENDING

         4 WARNING   Database option CONTEXT mismatch: PDB installed version NULL. CDB installed version 19.0.0.0.0.      PENDING

         4 WARNING   Database option DV mismatch: PDB installed version NULL. CDB installed version 19.0.0.0.0.           PENDING

         4 WARNING   Database option JAVAVM mismatch: PDB installed version NULL. CDB installed version 19.0.0.0.0.       PENDING

         4 WARNING   Database option OLS mismatch: PDB installed version NULL. CDB installed version 19.0.0.0.0.          PENDING

         4 WARNING   Database option ORDIM mismatch: PDB installed version NULL. CDB installed version 19.0.0.0.0.        PENDING

         4 WARNING   Database option OWM mismatch: PDB installed version NULL. CDB installed version 19.0.0.0.0.          PENDING

         4 WARNING   Database option SDO mismatch: PDB installed version NULL. CDB installed version 19.0.0.0.0.          PENDING

         4 WARNING   Database option XML mismatch: PDB installed version NULL. CDB installed version 19.0.0.0.0.          PENDING

         4 WARNING   Database option XOQ mismatch: PDB installed version NULL. CDB installed version 19.0.0.0.0.          PENDING

 

 Verify PDB is open in READ WRITE mode

 SQL> conn / as sysdba

Connected.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3DGPDEVL                        READ WRITE NO

https://isqlplus.com/oracle/upgrade/upgrading-oracle-database-11g-to-oracle-database-19c-non-cdb-to-cdb/

https://mikedietrichde.com/2019/07/23/database-migration-from-non-cdb-to-pdb-upgrade-plug-in-convert/

https://oracle-base.com/articles/19c/upgrading-to-19c#create-new-cdb