Hi all,

At my this post, I will upgrade an Oracle Database from “12.2.0.1.0” to release “19.3.0.0.0”. My 12c database is a Non-CDB on Windows Server and I will upgrade it to a 19c that is a PDB on the same server.

First of all, I checked that my current database (12.2.0.1.0) will be DIRECT upgrading to 19.3.0.0.0. It’s OK.

Also, I used DBUA (Database Upgrade Assistant) for a part of my processes.

Let’s start.

My Environments

StateOperating SystemDatabase VersionOracle SIDOracle HomeCDB?
Before UpgradeWindows Server 201612.2.0.1.0MBsDBC:\Oracle\app\Administrator\product\12.2.0\dbhome_1Non-CDB
After UpgradeWindows Server 201619.3.0.0.0MBsDBC:\Oracle\app\Administrator\product\19.0.0\dbhome_1PDB
My Environments

I. UPGRADING NON-CDB DATABASE

A. Preparing to Upgrade Oracle Database

A.1. Installing Oracle Software in a New Oracle Home

A.1.a. Choose a New Location for Oracle Home when Upgrading

C:\> mkdir C:\Oracle\app\Administrator\product\19.0.0\dbhome_1

A.1.b. Installing the New Oracle Database Software for Single Instance

  • Unzip “WINDOWS.X64_193000_db_home.zip” file to “C:\Oracle\app\Administrator\product\19.0.0\dbhome_1”
  • Run “setup.exe”
C:\> C:\Oracle\app\Administrator\product\19.0.0\dbhome_1\setup.exe

A.2. Running the Pre-Upgrade Information Tool

A.2.a. Setting Up Environment Variables for the Pre-Upgrade Information Tool

C:\> set ORACLE_BASE=C:\Oracle\app\Administrator
C:\> set ORACLE_HOME=C:\Oracle\app\Administrator\product\12.2.0\dbhome_1
C:\> set ORACLE_SID=MBsDB

A.2.b. Create Log Directory

C:\> mkdir C:\UPGRADE-19c

A.2.c. Run “preupgrade.jar”

C:\> C:\Oracle\app\Administrator\product\12.2.0\dbhome_1\jdk\bin\java -jar C:\Oracle\app\Administrator\product\19.0.0\dbhome_1\rdbms\admin\preupgrade.jar FILE TEXT DIR C:\UPGRADE-19c

A.3. Pre-Upgrade Information Tool Warnings and Recommendations for Oracle Database

A.3.a. View Pre-Upgrade Log

C:\> C:\UPGRADE-19c\preupgrade.log

A.3.b. Check Pre-Upgrade Log

*** Review the Pre-Upgrade Information Tool output and correct all issues noted in the output before proceeding.

For My Database:

C:\> cd C:\Oracle\app\Administrator\product\12.2.0\dbhome_1\bin
C:\> sqlplus /nolog
SQL> conn sys as sysdba

  • Recompile INVALID Objects
SQL> @C:\Oracle\app\Administrator\product\12.2.0\dbhome_1\rdbms\admin\utlrp.sql

  • Check INVALID Objects
SQL> select count(1) from dba_objects where status='INVALID';
-
     1

  • Check any SYS or SYSTEM owned INVALID objects
SQL> select object_name,object_type from dba_objects where owner in ('SYS','SYSTEM')  and status='INVALID';
-
No rows selected.

  • Remove Streams Setup
SQL> truncate table sys.apply$_source_obj;
SQL> truncate table apply$_source_schema;
SQL> begin
     dbms_streams_adm.remove_streams_configuration;
     end;
     /

  • Refresh MVs
SQL> declare
     list_failures integer(3) :=0;
     begin
     DBMS_MVIEW.REFRESH_ALL_MVIEWS(list_failures,'C','', TRUE, FALSE);
     end;
     /

  • Run “preupgrade_fixups.sql”
SQL> @C:\UPGRADE-19c\preupgrade_fixups.sql

B. Upgrading Oracle Database

B.1. Setting Up Environment Variables for the Upgrade

C:\> set ORACLE_BASE=C:\Oracle\app\Administrator
C:\> set ORACLE_HOME=C:\Oracle\app\Administrator\product\19.0.0\dbhome_1
C:\> set ORACLE_SID=MBsDB

B.2. Run “dbua.bat”

C:\> cd C:\Oracle\app\Administrator\product\19.0.0\dbhome_1\bin
C:\> dbua.bat

C. Post-Upgrade Tasks

C.1. Verify Timezone version

SQL> SELECT version FROM v$timezone_file;
-
     32

C.2. Verify INVALID objects

SQL> select count(1) from dba_objects where status='INVALID';
-
     1

C.3. Verify DBA_REGISTRY

SQL> select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry;

C.4. Update COMPATIBLE parameter

SQL> ALTER SYSTEM SET COMPATIBLE = '19.0.0' SCOPE=SPFILE;

C.5. Delete Old Home

C:\> cd C:\Oracle\app\Administrator\product\12.2.0\dbhome_1\deinstall
C:\> deinstall.bat

II. CONVERTING NON-CDB to PDB

  • Create the CDB if it does not exist.
C:\> dbca

*** Create a CDB (SID=MBsCDB) with NO PDB

  • Execute “DBMS_PDB.DESCRIBE”
C:\> set ORACLE_SID=MBsDB
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database open read only;
SQL> exec DBMS_PDB.DESCRIBE ('C:\UPGRADE-19c\MBsDB.xml');
SQL> shutdown immediate;

  • Plug in the database to existing Oracle 19c CDB
C:\> set ORACLE_SID=MBsCDB
SQL> show pdbs
SQL> create pluggable database MBsDB using 'C:\UPGRADE-19c\MBsDB.xml' nocopy tempfile reuse;
SQL> show pdbs
SQL> alter pluggable database MBsDB open;

  • Execute “noncdb_to_pdb.sql” script
SQL> alter session set container=MBsDB;
SQL> @?/rdbms/admin/noncdb_to_pdb.sql

  • 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;

  • Verify PDB is open in READ WRITE mode
SQL> conn / as sysdba
SQL> show pdbs
-
     MBsDB	   READ WRITE		NO

That’s all,

See you soon.

Oracle

12c19cCONVERTNON-CDBPDBPLUGGABLEUPGRADE