Upgrading a 12c PDB into an 18c CDB

Before You Begin

This 15-minute tutorial shows you how to upgrade a 12c pluggable database (PDB) into an 18c container database (CDB).

Background

The process and utilities to upgrade a 12.1 PDB into a 12.2 CDB are the same as an upgrade of a 12c PDB into an 18c CDB.

What Do You Need?

Oracle Database 18c installed
Oracle Database 12c Release 2 (12.2) installed
One Oracle Database 18c CDB: ORCL
One Oracle Database 12c Release 2 (12.2) CDB CDB12 with one PDB opened, PDB12
The SQL script, create_test_user_bigtab.sql downloaded to the labs directory created on your server /home/oracle/labs with the user password updated

Back up 12c PDB Before Upgrade

In this section, you'll back up PDB12 before you upgrade the 12c PDB to an 18c PDB into ORCL. Before you back up the PDB, use the create_test_user_bigtab.sql SQL script to create a test user and a table with rows.

Log in to PDB12 to create the test.bigtab table loaded with 10000 rows. This is Session 12c.
```
sqlplus sys@PDB12 AS SYSDBA
Enter password: password
```
Execute the SQL script create_test_user_bigtab.sql that creates the TEST user and the test.bigtab table that is loaded with 10000 rows.
```
@/home/oracle/labs/create_test_user_bigtab.sql
```
Launch rman to back up PDB12.
```
rman target /
Enter password: password
```

Back up PDB12.

BACKUP PLUGGABLE DATABASE pdb12 PLUS ARCHIVELOG;

Quit the session.
```
EXIT;
```

Prepare the 12c PDB for Upgrade

In this section, you'll prepare PDB12 to be upgraded to an 18c PDB into ORCL.

In Session 12c, prepare PDB12 to be unplugged from cdb12 and upgraded into ORCL. First execute the Pre-Upgrade Information Tool on the 12c PDB12 by executing the preupgrade.jar file.
```
cd /u01/app/oracle/product/18.1.0/dbhome_1/rdbms/admin
```
```
$ORACLE_HOME/jdk/bin/java -jar preupgrade.jar -c 'PDB12'
```
Read the Preupgrade Summary report from preupgrade_summary.

Before the upgrade operation, the report suggests that you execute the preupgrade fixups on the entire CDB

 CDB12.

$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/cdb12/preupgrade / -b preup_cdb12 /u01/app/oracle/cfgtoollogs/cdb12/preupgrade/preupgrade_fixups.sql

Unplug PDB12. First connect to the CDB root to close the PDB.
```
sqlplus / AS SYSDBA
```
Close the PDB.
```
ALTER PLUGGABLE DATABASE pdb12 CLOSE;
```

Unplug PDB12.

ALTER PLUGGABLE DATABASE pdb12 UNPLUG INTO '/tmp/pdb12.xml';

Quit the session.
```
EXIT
```

Upgrade the 12c PDB into the 18c CDB

In this section, you'll upgrade PDB12 into ORCL as PDB12_18.

Open another terminal window and set ORACLE_HOME to Oracle Database 18c. This session is called Session 18c.
```
. oraenv
ORACLE_SID = [oracle] ? ORCL
```
```
echo $ORACLE_HOME
/u01/app/oracle/product/18.1.0/dbhome_1
```
Log in to the ORCL CDB root.
```
sqlplus / AS SYSDBA
```
Before you perform the plugging operation, you can optionally check whether the unplugged PDB12 is compatible with ORCL. In Session 18c, execute the DBMS_PDB.CHECK_PLUG_COMPATIBILITY function by using code1 after you set the following SET command.
```
SET SERVEROUTPUT ON
```
After you execute the anonymous PL/SQL block that is defined in the compatibility text file, read the result of the function execution from the compatibility text file.
If the value returned is YES, you can immediately proceed with step 5. If the value returned is NO, examine the PDB_PLUG_IN_VIOLATIONS view to see why it is not compatible.
```
SELECT message, action FROM pdb_plug_in_violations WHERE name = 'PDB12';
```
Read the result of the query from code2. The result informs you that the PDB's version does not match the CDB's version. The PDB's version is 12.2.0.1.0 whereas the CDB's version is 18.0.0.0.0. It suggests that you upgrade the PDB. It also informs you that the CDB parameter compatible value of the incoming PDB was '12.2.0' whereas the current CDB parameter compatible value of the target CDB is '18.0.0'. These two pieces of information are informative and not error messages. You can therefore proceed with step 5.
Plug PDB12 into ORCL as a regular PDB by copying the original files into the /u02/app/oracle/oradata/ORCL/pdb12_18 directory. First create the directory.
```
host mkdir /u02/app/oracle/oradata/ORCL/pdb12_18 
```

Plug the PDB.

ALTER SESSION SET DB_CREATE_FILE_DEST='/u02/app/oracle/oradata/ORCL/pdb12_18';

CREATE PLUGGABLE DATABASE pdb12_18 USING '/tmp/pdb12.xml' 
       CREATE_FILE_DEST='/u02/app/oracle/oradata/ORCL/pdb12_18'
       COPY;

Open the PDB in UPGRADE mode.

ALTER PLUGGABLE DATABASE pdb12_18 OPEN UPGRADE;

Show the status of the PDB in ORCL.

SHOW PDBS

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB12_18                       MIGRATE    YES

Quit the session.
```
EXIT
```
Upgrade PDB12_18 in ORCL. Use the -c parameter to list the containers for which recommendations were provided before the upgrade operation.
```
cd $ORACLE_HOME/rdbms/admin
```
```
$ORACLE_HOME/perl/bin/perl catctl.pl -c 'PDB12_18' catupgrd.sql
```
Read the result of the upgrade operation from the upgrade report.

Execute the Post-Upgrade Steps

In this section, you'll finally execute the post-upgrade fixup script that was suggested in the preupgrade_summary report.

Log in to ORCL to open PDB12_18.
```
sqlplus / AS SYSDBA
```
Open PDB12_18.
```
ALTER PLUGGABLE DATABASE pdb12_18 OPEN;
```
Quit the session.
```
EXIT
```

Execute the post-upgrade fixup script.

$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/cdb12/preupgrade/ -b postup_cdb12 /u01/app/oracle/cfgtoollogs/cdb12/preupgrade/postupgrade_fixups.sql
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/u01/app/oracle/cfgtoollogs/cdb12/preupgrade/postup_cdb12_catcon_32106.lst]

catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/cfgtoollogs/cdb12/preupgrade/postup_cdb12*.log] files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/cfgtoollogs/cdb12/preupgrade/postup_cdb12_*.lst] files for spool files, if any

catcon.pl: completed successfully

Add the new net service name in the tnsnames.ora file.

PDB12_18 =
  (DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST= hostname)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PDB12_18)
    )
  )

Reload the listener.
```
lsnrctl reload
```

sqlplus test@PDB12_18
Enter password:  password

SELECT count(*) FROM bigtab;

  COUNT(*)
----------
     10000

Quit the session.
```
EXIT
```
In Session 12c, log in to CDB12 to drop PDB12.
```
sqlplus / AS SYSDBA
```

Drop PDB12.

DROP PLUGGABLE DATABASE pdb12 INCLUDING DATAFILES;

Quit the session.
```
EXIT
```