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?
- Upgrade your stand-alone database to Oracle Database 12c, ideally to Oracle 188.8.131.52 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.
- Then start your stand-alone database in read-only mode - of course at this step you'll encounter downtime:
startup open read only;
- 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:
- Shutdown the database
- Change into your new CDB and do a compatibility check:
SET SERVEROUTPUT ON
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'
- 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.
- Connect to this new PDB1 and perform sanity operations:
alter session set container=PDB1;
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.
- Now the database is plugged in – but not open yet. It will need to be started.
- 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 an alternative you could use the EZconnect syntax:
sqlplus "sys/oracle@//localhost:1521/pdb1 as sysdba"