Sunday, April 8, 2018

Plugging non-CDB as a PDB into a CDB in the Same Host

This post shows the steps of plugging a non-CDB into a CDB when both DBs reside on the same host. Both DBs run out of the same Oracle home and has the same database options (non-CDB must have either same or subset of the CDBs options).
1. Non-CDB is as follows
SQL> select cdb from v$database;

CDB
---
NO

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
beast

SQL> select name from v$datafile;

NAME
-------------------------------------------------------------------
/opt/app/oracle/oradata/BEAST/datafile/o1_mf_system_f902ycxw_.dbf
/opt/app/oracle/oradata/BEAST/datafile/o1_mf_sysaux_f902ypt8_.dbf
/opt/app/oracle/oradata/BEAST/datafile/o1_mf_undotbs1_f902yxqx_.dbf
/opt/app/oracle/oradata/BEAST/datafile/o1_mf_users_f902zgb3_.dbf
2. Shutdown the non-CDB and open it in read only mode. Once open in read only mode connect to the non-CDB and create a non-cdb descriptor file. Once the descriptor file is created shutdown the non-CDB.
shutdown imemdiate;
startup mount;
alter database open read only;

exec dbms_pdb.describe(pdb_descr_file=>'/home/oracle/beast_non_cdb.xml');

shutdown immediate;
3. The CDB details are as follows.
SQL> select instance_name from v$instance;

INSTANCE_NAME
-------------
parakum

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         5 PDBAPP1                        READ WRITE NO
4. Connect to the CDB and run CHECK_PLUG_COMPATIBILITY to test the compatibility of the non-CDB. Following PL/SQL code could be used for this.
SQL> show con_name

CON_NAME
---------
CDB$ROOT

set serveroutput on
declare
    compa boolean;
    begin
    compa := dbms_pdb.CHECK_PLUG_COMPATIBILITY(pdb_descr_file=>'/home/oracle/beast_non_cdb.xml');
 if compa = true then
      dbms_output.put_line('compatible');
 else
    dbms_output.put_line('not compatible');
 end if;
end;
/

compatible
5. As the non-CDB is compatible, it could be safely plugged into the CDB. In preparation of this, shutdown the non-CDB.
SQL> show con_name

CON_NAME
---------
beast

SQL> shutdown immediate;


6. PDB could be created either with copy command or move. With copy command the files will be copied from non-CDB OMF locations to CDB OMF locations. At the end of the copy the non-CDB could be opened as before and copy of it will be available inside the CDB as a PDB. If the host server doesn't have enough space for a full copy of the non-CDB then move options could be used where files will be moved from non-CDB to CDB. At the end of the move, non-CDB will not be available for use since files are moved out. Following shows the use of copy command
Since non-CDB uses OMF, the file_name_convert cannot be used during the PDB creation. Using of file_name_convert will result in error (refer MOS 1912436.1 as well)
SQL> CREATE PLUGGABLE DATABASE beast USING '/home/oracle/beast_non_cdb.xml'
COPY
FILE_NAME_CONVERT = ('/opt/app/oracle/oradata/BEAST/datafile/', '/opt/app/oracle/oradata/PARAKUM/');  2    3
CREATE PLUGGABLE DATABASE beast USING '/home/oracle/beast_non_cdb.xml'
*
ERROR at line 1:
ORA-01276: Cannot add file
/opt/app/oracle/oradata/PARAKUM/o1_mf_system_f902ycxw_.dbf.  File has an Oracle
Managed Files file name.
One of the solutions is to omit file name conversion and let OMF to take care of it.
SQL>  CREATE PLUGGABLE DATABASE beast USING '/home/oracle/beast_non_cdb.xml' copy;
Alternatively source_file_name_convert could be used by specifying current location of the datafiles into the two string mappings.
CREATE PLUGGABLE DATABASE beast USING '/home/oracle/beast_non_cdb.xml'
COPY
source_file_name_convert = ('/opt/app/oracle/oradata/BEAST/datafile/', '/opt/app/oracle/oradata/BEAST/datafile/');
The first parameter in the source_file_name_convert refers to the location of the datafiles listed in the descriptor file while second location refers to the actual location of the files. Either way the result is the same.
Following command shows the use of move option and using a different name other than the non-CDB name during the PDB creation processes.
CREATE PLUGGABLE DATABASE pdbdev USING '/home/oracle/beast_non_cdb.xml' move;
At the end of the PDB creation it will be in mount mode. Do not open it until noncdb-to-pdb script is run.
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 BEAST                          MOUNTED
         5 PDBAPP1                        READ WRITE NO
7. Once the PDB is created switch to it and verify all relevant datafiles are copied. (Following outputs from PDB created with copy above).
SQL> alter session set container=beast;
Session altered.

SQL> show con_name

CON_NAME
------------------------------
BEAST

SQL> select name from v$datafile;

NAME
------------------------------------------------------------------------------------------------------
/opt/app/oracle/oradata/PARAKUM/65E1789F663C5A9DE0534D00A8C0A811/datafile/o1_mf_system_f90fmmrn_.dbf
/opt/app/oracle/oradata/PARAKUM/65E1789F663C5A9DE0534D00A8C0A811/datafile/o1_mf_sysaux_f90fmmt2_.dbf
/opt/app/oracle/oradata/PARAKUM/65E1789F663C5A9DE0534D00A8C0A811/datafile/o1_mf_undotbs1_f90fmmt3_.dbf
/opt/app/oracle/oradata/PARAKUM/65E1789F663C5A9DE0534D00A8C0A811/datafile/o1_mf_users_f90fmmt5_.dbf
8. Before opening run the noncdb-to-pdb script from within the newly created PDB
@?/rdbms/admin/noncdb_to_pdb.sql
9. Once the script finishes, open the PDB
SQL> alter pluggable database open;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         6 BEAST                          READ WRITE NO
10. Check alert log and PDB_PLUG_IN_VIOLATIONS view for any PDB violation during the opening. Finally backup the CDB with the newly created PDB.

Related Posts
Plugging a Non-CDB (pre-12c) into CDB Using Transportable DB (TDB) / Tablespaces (TTS)
Plugging a SE2 non-CDB as an EE PDB Using File Copying and Remote Link