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

A. UPGRADING NON-CDB DATABASE

A.1. Preparing to Upgrade Oracle Database

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

A.1.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.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.1.2. Running the Pre-Upgrade Information Tool

A.1.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.1.2.b. Create Log Directory
C:\> mkdir C:\UPGRADE-19c
A.1.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.1.3. Pre-Upgrade Information Tool Warnings and Recommendations for Oracle Database

A.1.3.a. View Pre-Upgrade Log
C:\> C:\UPGRADE-19c\preupgrade.log

A.1.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

A.2. Upgrading Oracle Database

A.2.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
A.2.2. Run "dbua.bat"
C:\> cd C:\Oracle\app\Administrator\product\19.0.0\dbhome_1\bin
C:\> dbua.bat

A.3. Post-Upgrade Tasks

A.3.1. Verify Timezone version
SQL> SELECT version FROM v$timezone_file;

32
A.3.2. Verify INVALID objects
SQL> select count(1) from dba_objects where status='INVALID';

1
A.3.3. Verify DBA_REGISTRY
SQL> select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry;
A.3.4. Update COMPATIBLE parameter
SQL> ALTER SYSTEM SET COMPATIBLE = '19.0.0' SCOPE=SPFILE;
A.3.5. Delete Old Home
C:\> cd C:\Oracle\app\Administrator\product\12.2.0\dbhome_1\deinstall
C:\> deinstall.bat

B. 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.

MÜRSEL BAKAY

Oracle

12c19cCONVERTNON-CDBPDBPLUGGABLEUPGRADE

Related Posts ...