Upgrade to 12c and Plugin – one fast way to move into the world of Oracle Multitenant

What is the best way to move into Oracle Multitenant (which includes Single Tenant with just one PDB) as well?

This question came up on the blog, it’s part of the Multitenant chapter in our big slide deck but let me elaborate this a bit more in detail. Of course there are several ways to move a database into a Single/Multitenant environment such as Data Pump but one fact is for sure: You can’t convert a stand-alone database to become a container database (CDB$ROOT). A container database has to be built up from scratch as first step. Please use the DBCA to create it as this is the easiest path.

But how can your stand-alone database be moved now?

1. Upgrade your stand-alone database to Oracle Database 12c, ideally to Oracle 12.1.0.2 with the newest PSU included already. Upgrade it to exactly the same version including the same PSU as your container database (CDB) got created from.
   —
2. Then start your stand-alone database in read-only mode – of course at this step you’ll encounter downtime:

   startup open read only;--

3.  Generate the XML description file – this file
   will contain the information describing the database structure. To create it
   the database has to be in read only mode:
   

   exec DBMS_PDB.DESCRIBE('/tmp/pdb1.xml');--

4. hutdown the database
   

   shutdown immediate-

5. Change into your new CDB and do a compatibility check:

   SET SERVEROUTPUT ON
   DECLARE
   compatible CONSTANT VARCHAR2(3) := CASE
   DBMS_PDB.CHECK_PLUG_COMPATIBILITY(pdb_descr_file => '/tmp/pdb1.xml',pdb_name => 'PDB1') WHEN TRUE
   THEN 'YES' ELSE 'NO'
   END;
   BEGIN
   DBMS_OUTPUT.PUT_LINE(compatible);
   END;
   /

6. Plugin your stand-alone database (in my case with a new name PDB1):

   create pluggable database PDB1 using '/tmp/pdb1.xml' nocopy tempfile reuse;

   Be aware that using the NOCOPY option will require that you have a valid backup of your database. If you’ll use the COPY option instead you will need additional disk space. When using NOCOPY files will remain at their location. When using COPY you’ll combine it with the FILE_NAME_CONVERT option, converting source to destination paths.
   It’s always a recommendation to keep the TEMP tablespaces. In case you’d like to remove then you’ll have to drop the tablespace and the tempfile before. Otherwise there will be a reference in the XML manifest file making it impossible to plug in your new PDB.—

7. Connect to this new PDB1 and perform sanity operations:

   alter session set container=PDB1;
   @?/rdbms/admin/noncdb_to_pdb.sql

   
   This script will do required sanity operations to connect the PDB with the CDB. It will do things such as change object id’s, mark common objects etc. Its runtime can vary between 5 minutes and several hours depending on the complexity of the former stand-alone’s database data dictionary. It will do recompilations of many invalid objects as well. Please test it carefully. If this step gets skipped the PDB can be open in read-only mode only. It is not rerunable in Oracle Database 12.1.0.1/2. 

8. Please make sure you backup your PDB directly afterwards the plugin operation as neither your previous backups nor your archive logs are usable.

—-

1. Now the database is plugged in – but not open yet. It will need to be started.
   

   startup
   show pdbs

2. To connect to the consolidated PDB1 from the command prompt the following command syntax needs to be used:
   

   sqlplus "sys/oracle@pdb1 as sysdba"

   As alternative you could use the EZconnect syntax:

   sqlplus "sys/oracle@//localhost:1521/pdb1 as sysdba"

–Mike