Create PDB from PDB$SEED (appropriate PDB directory)

This is an extension example from previous post of Create PDB from PDB$SEED

The objective is to convert the data files and temp files to pdb folder appropriately.

Previous example result is as follow

Check the data and temp files

SQL> alter session set container=pdb2;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
PDB2

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
+DATA/pdb2/pdb$seed_system01.dbf
+DATA/pdb2/pdb$seed_sysaux01.dbf

SQL> select file_name from dba_temp_files;

FILE_NAME
--------------------------------------------------------------------------------
+DATA/pdb2/pdbseed_temp01.dbf

If you notice that system01.dbf and sysaux01.dbf are having suffix pdb$seed but temp01.dbf is having pdbseed (without $). We therefore need to convert both of them.

Following is the command to rectify my error (I am creating PDB3 from the beginning).

1. Create the folder to place the pdb datafiles, in this example I am using ASM, the procedure is the same if you are using non RAC (ordinary file system)

[oracle@ora12cn1 ~]$ su - grid
Password:
[grid@ora12cn1 ~]$ asmcmd
ASMCMD> cd +DATA
ASMCMD> mkdir pdb3
ASMCMD> cd pdb3
ASMCMD> pwd
+DATA/pdb3
ASMCMD> 

2. Connect to root, with sys as sysdba

[oracle@ora12cn1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Mon Nov 25 15:55:04 2013
Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> 

3. Identify the location of PDB$SEED datafiles.

SQL> alter session set container=PDB$SEED;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
PDB$SEED

SQL> select file_name from dba_data_files;

FILE_NAME
-------------------------------------------
+DATA/pdb$seed_system01.dbf
+DATA/pdb$seed_sysaux01.dbf

4.  Connect to root and issue the CREATE PLUGGABLE DATABASE command with following changed
     FILE_NAME_CONVERT = ('+DATA/pdb$seed_', '+DATA/pdb3/','+DATA/pdbseed_','+DATA/pdb3/')

SQL> conn / as sysdba

Connected.

SQL> show con_name

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

SQL> CREATE PLUGGABLE DATABASE pdb3
  2  ADMIN USER pdb3_admin IDENTIFIED BY oracle ROLES=(CONNECT)
  3  FILE_NAME_CONVERT=('+DATA/pdb$seed_', '+DATA/pdb3/','+DATA/pdbseed_','+DATA/pdb3/');

Pluggable database created.

SQL> alter session set container = PDB3;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
PDB3

5. Check the result, it look better now

SQL> alter pluggable database pdb3 open;

Pluggable database altered.

SQL> alter session set container=pdb3;

Session altered.

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
+DATA/pdb3/system01.dbf
+DATA/pdb3/sysaux01.dbf

SQL> select file_name from dba_temp_files;

FILE_NAME
--------------------------------------------------------------------------------
+DATA/pdb3/temp01.dbf

7. The service is automatically added

[oracle@ora12cn2 Desktop]$ lsnrctl services
LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 01-DEC-2013 18:38:17
Copyright (c) 1991, 2013, Oracle.  All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM2", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "ora12c" has 1 instance(s).
  Instance "ora12c2", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "ora12cXDB" has 1 instance(s).
  Instance "ora12c2", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: ora12cn2, pid: 3475>
         (ADDRESS=(PROTOCOL=tcp)(HOST=ora12cn2.localdomain)(PORT=50151))
Service "pdb2" has 1 instance(s).
  Instance "ora12c2", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "pdb3" has 1 instance(s).
  Instance "ora12c2", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
The command completed successfully