Multitenant: Standalone to pluggable database migration

 

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

This entry was posted in Oracle and tagged . Bookmark the permalink.

One thought on “Multitenant: Standalone to pluggable database migration

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>