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 - grid2. Connect to root, with sys as sysdba
Password:
[grid@ora12cn1 ~]$ asmcmd
ASMCMD> cd +DATA
ASMCMD> mkdir pdb3
ASMCMD> cd pdb3
ASMCMD> pwd
+DATA/pdb3
ASMCMD>
[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
