Create PDB from Non-CDB in 18c

Non-CDB - PXP

Multitenant - CDB

Non-CDB : Shutdown, mount exclusive, read only mode:

>set ORACLE_SID=PXP
 

>sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Fri Feb 12 12:00:30 2021
Version 18.8.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.


Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.8.0.0.0

 

SQL> select name,open_mode,cdb from v$database;

NAME      OPEN_MODE            CDB
--------- -------------------- ---
PXP       READ WRITE           NO 

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
 

SQL> startup mount exclusive;
ORACLE instance started.

Total System Global Area 1.0268E+10 bytes
Fixed Size                 12339080 bytes
Variable Size            1979711488 bytes
Database Buffers         8254390272 bytes
Redo Buffers               21213184 bytes
Database mounted.
SQL> alter database open read only;

Database altered.

        

Create PDB manifest file from non-CDB and Shutdown:

SQL> exec dbms_pdb.describe(pdb_descr_file=>'C:\Apps\ORADATA\PXP\noncdb18c_manifestfile.xml');

 

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit

Connect CDB and Create PDB from Non-CDB:

SQL> select name,open_mode from v$pdbs;

NAME           OPEN_MODE
----------         --------------------
PDB$SEED   READ ONLY
PDB1             READ WRITE

Check compatibility:

SQL> SET SERVEROUTPUT ON;
SQL>  DECLARE
  2   Compatible CONSTANT VARCHAR2(3) :=CASE  DBMS_PDB.CHECK_PLUG_COMPATIBILITY
  3   (pdb_descr_file => 'C:\Apps\ORADATA\PXP\noncdb18c_manifestfile.xml')
  4   WHEN TRUE THEN 'YES'
  5   ELSE 'NO'
  6   END;
  7   BEGIN
  8      DBMS_OUTPUT.PUT_LINE(compatible);
  9   END;
 10   /
YES

PL/SQL procedure successfully completed.

SQL> SELECT name, cause, type, message, status FROM PDB_PLUG_IN_VIOLATIONS WHERE name='PXP';

NAME
--------------------------------------------------------------------------------
CAUSE                                                            TYPE
---------------------------------------------------------------- ---------
MESSAGE
--------------------------------------------------------------------------------
STATUS
---------
PXP
Non-CDB to PDB                                                   WARNING
PDB plugged in is a non-CDB, requires noncdb_to_pdb.sql be run.
PENDING

 No violations found. The warning noncdb_to_pdb.sql should be executed after PDB creation.

Create pluggable database using manifest file created earlier:

SQL> CREATE PLUGGABLE DATABASE PXP USING 'C:\Apps\ORADATA\PXP\noncdb18c_manifestfile.xml'
  2  COPY
  3  FILE_NAME_CONVERT = ('C:\Apps\ORADATA','C:\Apps\ORADATA\CDB\PXP\');
CREATE PLUGGABLE DATABASE PXP USING 'C:\Apps\ORADATA\PXP\noncdb18c_manifestfile.xml'
*
ERROR at line 1:
ORA-01276: Cannot add file
C:\APPS\ORADATA\CDB\PXP\\PXP\DATAFILE\O1_MF_SYSTEM_J2CY9QDX_.DBF.  File has an
Oracle Managed Files file name.

 Trying with SOURCE_FILE_NAME_CONVERT also got error and failed to create.

SQL> CREATE PLUGGABLE DATABASE PXP USING 'C:\Apps\ORADATA\PXP\noncdb18c_manifestfile.xml'
  2  COPY
  3  SOURCE_FILE_NAME_CONVERT = ('C:\Apps\ORADATA','C:\Apps\ORADATA\CDB\PXP\');
CREATE PLUGGABLE DATABASE PXP USING 'C:\Apps\ORADATA\PXP\noncdb18c_manifestfile.xml'
*
ERROR at line 1:
ORA-19505: failed to identify file
"C:\APPS\ORADATA\CDB\PXP\\PXP\DATAFILE\O1_MF_SYSTEM_J2CY9QDX_.DBF"
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 3) The system cannot find the path specified.

The solution is to omit file name conversion and let OMF to take care of creating datafiles.

SQL> CREATE PLUGGABLE DATABASE PXP USING 'C:\Apps\ORADATA\PXP\noncdb18c_manifestfile.xml' COPY;

Pluggable database created.

SQL> alter session set container=PXP;

Session altered.

SQL> select name from v$datafile;

NAME
------------------------------------------------------------------------------------------
C:\APPS\ORADATA\CDB\307726814800481493A138B1E881B9BF\DATAFILE\O1_MF_SYSTEM_J2D7FF2O_.DBF
C:\APPS\ORADATA\CDB\307726814800481493A138B1E881B9BF\DATAFILE\O1_MF_SYSAUX_J2D7FF3L_.DBF
C:\APPS\ORADATA\CDB\307726814800481493A138B1E881B9BF\DATAFILE\O1_MF_UNDOTBS1_J2D7FF4D_.DBF
C:\APPS\ORADATA\CDB\307726814800481493A138B1E881B9BF\DATAFILE\O1_MF_USERS_J2D7FF54_.DBF

 Execute noncdb_to_pdb.sql script:

@?/rdbms/admin/noncdb_to_pdb.sql

...

...

PL/SQL procedure successfully completed.

 

Open database and verify the status:

 

SQL> alter pluggable database open;

Pluggable database altered.

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         5 PXP                            READ WRITE NO