Table of contents
Preamble
One of the main feature of Oracle 12cR1 is the multitenant option, not like in-memory this feature is available since initial release i.e. 12.1.0.1.0 (released in June 2013). First it is worth to mention that this enterprise option is not free. There are plenty on documentations about it, in short Oracle is now doing like MySQL/SQL Server means that one instance can hold multiple databases and that those databases know share resources (resources that can also be prioritize).
Even if on the paper the option looks great it will not be piece of cake to calculate the Return On Investment (ROI) of this option versus buying few memory DIMM more, of course saving memory is not the only aim of this option but this will be even more complex to measure… One interest that I have immediately seen is the capability to do super fast upgrade by unplugging and plugging back databases from a 12.x container to a more recent 12.x container.
A few definitions right now. The master instance is called a Container Database (CDB) and all underlining databases are called Pluggable Databases (PDB). A standalone database like in 11gR2 for example is called a non-CDB, please note that working this way is also possible in 12cR1.
This feature can have a huge interest because where I work negotiating downtime for upgrade is always a never ending story and second because having one PDB in a CDB has not cost. In other words the multitenant option is “free” if you have only one PDB.
As Oracle as finally released 12.1.0.2.0 patchset (in July 2014) it is now possible to test this upgrade from 12.1.0.1.0 to see if the promises can come true. The test system I’m using is a virtual machine running Oracle Linux Server release 6.5. I’m starting from a 11gR2 (11.2.0.4) and plan to convert it to a PDB in a 12.1.0.1 CDB, in a further blog post we will see if we can easily move it to a 12.1.0.2 container. My small test Oracle database is as well using Grid Infrastructure (GI) also often called Oracle Restart.
In below each chapter is one of the steps of whole process.
Upgrade Grid Infrastructure to 12cR1
I have chosen 12.1.0.2.0 to avoid any known bugs. The only big constraint I had was the minimum memory requirement than moved from 256MB in 11gR2 to 1GB in 12cR1. I’m wondering why so much memory for an ASM instance. Even if 1GB looks small with modern x86 server it was an issue in my small virtual machine running on my desktop. Outside of this upgrade went really smoothly with absolutely no issue.
Upgrade database to non-CDB 12c
Then I had to upgrade my 11.2.0.4 standalone database to 12cR1. As I wrote above I have chosen to upgrade to 12.1.0.1.4 to be able to later test the unplug/plug migration to 11.2.0.2. I anyway applied the latest PSU to be bug safe as much as possible.
To do this you have the choice between mainly two methods that re well explained in below My Oracle Support (MOS) notes:
- Complete Checklist for Manual Upgrades to Oracle Database 12c Release 1 (12.1) (Doc ID 1503653.1)
- Complete Checklist for Upgrading to Oracle Database 12c Release 1 using DBUA (Doc ID 1516557.1)
This time I have chosen Database Upgrade Assistant (DBUA) but both methods are valid.
I had many issues with DBUA that are sum up in:
- ORA-04063: View “SYS.V_$DIAG_VIPS_PACKAGE_MAIN_INT” Has Errors’ During Upgrade (Doc ID 1674422.1)
The recommended script to be executed was not working for me as the base tables were simply not in source database (like X$DIAG_ADR_CONTROL for example). One of the creation script has apparently not been executed while creating the database. After investigations I came to conclusion it was because database has been created using Database Configuration Assistant (DBCA) and the chosen database was the one with included datafiles. Creating a new one using the custom options (so no included datafiles) did not produce any error during 12cR1 upgrade with DBUA…
Create a CDB (cdb1) with dbca
At this stage we have now GI that is in 12.1.0.2 and a non-CDB database in 12.0.1.0.
Next and third step is to create a CDB. I think the easiest way to do it is to use Database configuration Assistant (DBCA). This step is piece of cake, don’t be shy with memory allocation as 1GB seems to be the minimum or the Java components cannot be installed successfully. I have been able to decrease the SGA to 600MB once container database has been created…
I have called my container cdb1…
Non-CDB to PDB migration
Final step is to migrate my orcl non-CDB (standalone) to a pluggable database that I’m gonna call pdb1.
First we need to create a XML file to describe the non-CDB and check if operation is possible:
SQL> EXEC dbms_pdb.describe(pdb_descr_file=>'/backup/orcl.xml'); PL/SQL PROCEDURE successfully completed. SET SERVEROUTPUT ON DECLARE compatible CONSTANT VARCHAR2(3) := CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY( pdb_descr_file => '/backup/orcl.xml', pdb_name => 'PDB1') WHEN TRUE THEN 'YES' ELSE 'NO' END; BEGIN DBMS_OUTPUT.PUT_LINE(compatible); END; / |
As I got only warnings the operation should be safe:
SET lines 200 col message FOR a100 SET pages 100 SELECT name,cause,TYPE,message FROM PDB_PLUG_IN_VIOLATIONS WHERE name='PDB1'; NAME CAUSE TYPE MESSAGE ------------------------------ ---------------------- --------- ---------------------------------------------------------------------------------------------------- PDB1 OPTION WARNING DATABASE OPTION APS mismatch: PDB installed version NULL. CDB installed version 12.1.0.1.0. PDB1 OPTION WARNING DATABASE OPTION CATJAVA mismatch: PDB installed version NULL. CDB installed version 12.1.0.1.0. PDB1 OPTION WARNING DATABASE OPTION CONTEXT mismatch: PDB installed version NULL. CDB installed version 12.1.0.1.0. PDB1 OPTION WARNING DATABASE OPTION DV mismatch: PDB installed version NULL. CDB installed version 12.1.0.1.0. PDB1 OPTION WARNING DATABASE OPTION JAVAVM mismatch: PDB installed version NULL. CDB installed version 12.1.0.1.0. PDB1 OPTION WARNING DATABASE OPTION OLS mismatch: PDB installed version NULL. CDB installed version 12.1.0.1.0. PDB1 OPTION WARNING DATABASE OPTION ORDIM mismatch: PDB installed version NULL. CDB installed version 12.1.0.1.0. PDB1 OPTION WARNING DATABASE OPTION SDO mismatch: PDB installed version NULL. CDB installed version 12.1.0.1.0. PDB1 OPTION WARNING DATABASE OPTION XML mismatch: PDB installed version NULL. CDB installed version 12.1.0.1.0. PDB1 OPTION WARNING DATABASE OPTION XOQ mismatch: PDB installed version NULL. CDB installed version 12.1.0.1.0. PDB1 Non-CDB TO PDB WARNING PDB plugged IN IS a non-CDB, requires noncdb_to_pdb.SQL be run. PDB1 UNDEFINED WARNING APEX mismatch: PDB installed version NULL CDB installed version 4.2.0.00.27 PDB1 Parameter WARNING CDB parameter sga_target mismatch: PREVIOUS 600M CURRENT 500M PDB1 Parameter WARNING CDB parameter compatible mismatch: PREVIOUS '12.1.0' CURRENT '12.1.0.0.0' PDB1 Parameter WARNING CDB parameter pga_aggregate_target mismatch: PREVIOUS 100M CURRENT 200M 15 ROWS selected. |
My initial idea was to take the opportunity to move all datafiles from +DATA/orcl to +DATA/pdb1 to respect a naming convention, so I had multiple unsuccessful test using FILE_NAME_CONVERT option (in below the AS CLONE option is clearly not mandatory as datafiles are obviously not used anywhere else):
SQL> CREATE pluggable DATABASE pdb1 USING '/backup/orcl.xml' file_name_convert=('+DATA/orcl/','+DATA/pdb1/') move; * ERROR AT line 1: ORA-65005: missing OR invalid FILE name pattern FOR FILE - +DATA/ORCL/DATAFILE/users.257.858705969 |
CREATE pluggable DATABASE pdb1 AS clone USING '/backup/orcl.xml' file_name_convert=('+DATA/orcl/datafile','+DATA/pdb1/datafile') move; * ERROR AT line 1: ORA-65005: missing OR invalid FILE name pattern FOR FILE - +DATA/orcl/tempfile/temp.256.857908919 |
As message was different I have also specify for tempfiles:
CREATE pluggable DATABASE pdb1 AS clone USING '/backup/orcl.xml' file_name_convert=('+DATA/orcl/datafile','+DATA/pdb1/datafile','+DATA/orcl/tempfile','+DATA/pdb1/tempfile') move; * ERROR AT line 1: ORA-65005: missing OR invalid FILE name pattern FOR FILE - +DATA/ORCL/DATAFILE/users.257.858705969 |
Again specifying all datafiles and tempfiles, forutnately I had not so many:
CREATE pluggable DATABASE pdb1 AS clone USING '/backup/orcl.xml' file_name_convert=('+DATA/orcl/datafile/system.258.857908857','+DATA/pdb1/datafile/system.258.857908857', '+DATA/orcl/datafile/sysaux.259.857908889','+DATA/pdb1/datafile/sysaux.259.857908889', '+DATA/orcl/tempfile/temp.256.857908919','+DATA/pdb1/tempfile/temp.256.857908919', '+DATA/ORCL/DATAFILE/users.257.858705969','+DATA/pdb1/DATAFILE/users.257.858705969') move; * ERROR AT line 1: ORA-27038: created FILE already EXISTS ORA-01119: error IN creating DATABASE FILE '+DATA/pdb1/tempfile/temp.256.857908919' |
But the ‘+DATA/pdb1/tempfile/temp.256.857908919’ file was obviously not there… So decided to add the TEMPFILE REUSE option
CREATE pluggable DATABASE pdb1 AS clone USING '/backup/orcl.xml' file_name_convert=('+DATA/orcl/datafile/system.258.857908857','+DATA/pdb1/datafile/system.258.857908857', '+DATA/orcl/datafile/sysaux.259.857908889','+DATA/pdb1/datafile/sysaux.259.857908889', '+DATA/orcl/tempfile/temp.256.857908919','+DATA/pdb1/tempfile/temp.256.857908919', '+DATA/ORCL/DATAFILE/users.257.858705969','+DATA/pdb1/DATAFILE/users.257.858705969') move tempfile reuse; * ERROR AT line 1: ORA-00604: error occurred AT recursive SQL LEVEL ORA-19504: failed TO CREATE FILE "+DATA/pdb1/datafile/system.258.857908857" |
As tests were all unsuccessful so I have decided to switch to the NOCOPY option, means I will use the original datafiles and tempfiles for my CDB:
CREATE pluggable DATABASE pdb1 AS clone USING '/backup/orcl.xml' NOCOPY; * ERROR AT line 1: ORA-19505: failed TO identify FILE "+DATA/orcl/datafile/sysaux.259.857908889" ORA-15173: entry 'sysaux.259.857908889' does NOT exist IN DIRECTORY 'datafile' |
The previous command deleted one datafile so I had to recover the datafile and so recreate the XML file (database opened in read write):
CREATE pluggable DATABASE pdb1 AS clone USING '/backup/orcl.xml' NOCOPY; Pluggable DATABASE created. SQL> SELECT name FROM v$pdbs; NAME ------------------------------ PDB$SEED PDB1 SQL> ALTER SESSION SET container=pdb1; SESSION altered. SQL> ALTER pluggable DATABASE pdb1 OPEN; Warning: PDB altered WITH errors. SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 3 PDB1 READ WRITE YES |
As the PDB was in restricted mode I tried to close and restart it:
SQL> ALTER pluggable DATABASE pdb1 CLOSE; Pluggable DATABASE altered. SQL> ALTER pluggable DATABASE pdb1 OPEN; Warning: PDB altered WITH errors. SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 3 PDB1 READ WRITE YES SQL> SELECT file_name FROM dba_data_files; FILE_NAME -------------------------------------------------------------------------------- +DATA/ORCL/DATAFILE/users.257.858705969 +DATA/ORCL/DATAFILE/sysaux.259.858955649 +DATA/orcl/datafile/SYSTEM.258.857908857 SQL> SELECT NAME,OPEN_MODE,RESTRICTED FROM v$pdbs; NAME OPEN_MODE RES ------------------------------ ---------- --- PDB1 READ WRITE YES SQL> SELECT NAME FROM PDB_PLUG_IN_VIOLATIONS; no ROWS selected |
Then realized I was missing the last step of the migration and so executed noncdb_to_pdb.sql scrip:
SQL> ALTER SESSION SET container=pdb1; SESSION altered. SQL> @?/rdbms/admin/noncdb_to_pdb . . . SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE YES SQL> ALTER pluggable DATABASE pdb1 CLOSE; Pluggable DATABASE altered. SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 MOUNTED SQL> ALTER pluggable DATABASE pdb1 OPEN; Pluggable DATABASE altered. SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE NO |
So all is working fine but still this is not what I wanted initially to achieve. So dropped pdb1 pluggable database, restored my non-cdb database and tried again with COPY option to put datafiles in correct destination directories. As you can see below I have tried countless options with no success:
CREATE pluggable DATABASE pdb1 AS clone USING '/backup/orcl.xml' copy file_name_convert=('+DATA/orcl/datafile/system.258.857908857','+DATA/pdb1/datafile/system.258.857908857', '+DATA/orcl/datafile/sysaux.259.857908889','+DATA/pdb1/datafile/sysaux.259.857908889', '+DATA/ORCL/DATAFILE/users.257.858705969','+DATA/pdb1/DATAFILE/users.257.858705969'); * ERROR AT line 1: ORA-65005: missing OR invalid FILE name pattern FOR FILE - +DATA/ORCL/DATAFILE/SYSTEM.258.858963907 CREATE pluggable DATABASE pdb1 AS clone USING '/backup/orcl.xml' copy file_name_convert=('+DATA/orcl/','+DATA/pdb1/'); * ERROR AT line 1: ORA-65005: missing OR invalid FILE name pattern FOR FILE - +DATA/ORCL/DATAFILE/SYSTEM.258.858963907 CREATE pluggable DATABASE pdb1 USING '/backup/orcl.xml' copy file_name_convert=('+DATA/ORCL/','+DATA/pdb1/'); * ERROR AT line 1: ORA-65005: missing OR invalid FILE name pattern FOR FILE - +DATA/orcl/tempfile/temp.256.857908919 CREATE pluggable DATABASE pdb1 USING '/backup/orcl.xml' copy file_name_convert=('+DATA/ORCL/','+DATA/pdb1/','+DATA/orcl/','+DATA/pdb1/') tempfile reuse; * ERROR AT line 1: ORA-65005: missing OR invalid FILE name pattern FOR FILE - +DATA/ORCL/DATAFILE/sysaux.259.858963907 |
Then I have found MOS note ORA-65005 Troubleshooting Guide (Doc ID 1910646.1) and realized that (even if not really written black on white) that when using Oracle Managed Files (OMF) you cannot specify the destination directories and have to rely on OMF as well for destination (12.1.0.2 patchset is supposed to solve few limitations around this). So finally issued:
CREATE pluggable DATABASE pdb1 USING '/backup/orcl.xml' copy; Pluggable DATABASE created. |
Once I executed noncdb_to_pdb.sql script and opened the PDB I have been able to see where the datafiles have been copied:
SQL> ALTER SESSION SET container=pdb1; SESSION altered. SQL> SELECT file_name FROM dba_data_files; FILE_NAME -------------------------------------------------------------------------------- +DATA/CDB1/02B52D2CC3547ACCE0536538A8C07CAE/DATAFILE/SYSTEM.279.859047381 +DATA/CDB1/02B52D2CC3547ACCE0536538A8C07CAE/DATAFILE/sysaux.268.859047381 +DATA/CDB1/02B52D2CC3547ACCE0536538A8C07CAE/DATAFILE/users.257.859047381 |
So everything is under the cdb1 container directory (you can use asmcmd to display directories content). The other strange directory is the seed database used to fast provision new pluggable databases.
References
- Step by Step Examples of Migrating non-CDBs and PDBs Using ASM for File Storage (Doc ID 1576755.1)
- How to migrate an existing pre12c database(nonCDB) to 12c CDB database ? (Doc ID 1564657.1)
- Multitenant : Overview of Container Databases (CDB) and Pluggable Databases (PDB)
- Multitenant : Migrate a Non-Container Database (CDB) to a Pluggable Database (PDB) in Oracle Database 12c Release 1 (12.1)
rk says:
=====Then I have found MOS note ORA-65005 Troubleshooting Guide (Doc ID 1910646.1) and realized that (even if not really written black on white) that when using Oracle Managed Files (OMF) you cannot specify the destination directories and have to rely on OMF as well for destination (12.1.0.2 patchset is supposed to solve few limitations around this). So finally issued:====
This information helped me. I was fighting with the file_name_convert on an OMF non-cdb.