Differences in default RMAN configuration settings between 12c and 11g

I wanted to write a post on configuring the RMAN environment using the SHOW and CONFIGURE commands in Oracle Database 12c, but after few commands I decided to investigate about the differences on default RMAN settings between the versions 10g, 11g and 12c.

Here are part of my results: the second part will be written in the next post.

Comparing the output of the SHOW ALL command while connected to a container database...

[oracle@vsi08devpom ~]$ export ORACLE_SID=CDB001
[oracle@vsi08devpom admin]$ sqlplus system/oracle@CDB001

SQL*Plus: Release 12.1.0.1.0 Production on Thu Sep 26 09:10:50 2013

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

Last Successful login time: Tue Jul 16 2013 13:43:48 +02:00

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

SQL> show con_name

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

[oracle@vsi08devpom ~]$ rman target /

Recovery Manager: Release 12.1.0.1.0 - Production on Thu Sep 26 09:12:42 2013

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

connected to target database: CDB001 (DBID=4134963396)

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name CDB001 are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/opt/app/oracle/product/12.1.0/db_1/dbs/snapcf_CDB001.f'; # default

... with that one executed on a 11g database...

[oracle@localhost orcl]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Wed Sep 25 08:12:17 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1229390655)

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/home/oracle/app/oracle/product/11.2.0/dbhome_2/dbs/snapcf_orcl.f'; # default

... I can notice there are two main differences.

CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default

On 12c the CONTROLFILE AUTOBACKUP is by default turned on, so at the end of every RMAN backup or after structural changes for databases in ARCHIVELOG mode (eventually creating a single controlfile autobackup encompassing the latest structural changes performed in a short period of time), RMAN automatically backs up the controlfile.
Since Oracle Database 10g release Oracle recommends you enable the control file autobackup feature, but in 10g and 11g the default option of the CONTROLFILE AUTOBACKUP feature is OFF.

I've so tried to execute the SHOW ALL command on a 12c Non-Container database and here is the output:

[oracle@vsi08devpom ~]$ export ORACLE_SID=ORCL

[oracle@vsi08devpom admin]$ sqlplus system/oracle@orcl

SQL*Plus: Release 12.1.0.1.0 Production on Thu Sep 26 09:00:16 2013

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

Last Successful login time: Thu Aug 08 2013 12:16:46 +02:00

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

SQL@ORCL> show con_name

CON_NAME
------------------------------
Non Consolidated

[oracle@vsi08devpom ~]$ rman target /

Recovery Manager: Release 12.1.0.1.0 - Production on Thu Sep 26 09:02:14 2013

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1350603571)

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/opt/app/oracle/product/12.1.0/db_1/dbs/snapcf_ORCL.f'; # default

I then took a look at the "Backup and Recovery Reference 12c Release 1 (12.1)" guide and it confirms that:
"By default, control file autobackups are turned on for CDBs and turned off for non-CDBs."

The second difference is the new RMAN OUTPUT TO KEEP FOR integer DAYS command.

From the same documentation guide "...When you configure output logging to integer days, any logging entry that is older than integer days is deleted from both the RC_RMAN_OUTPUT and V$RMAN_OUTPUT views."

According to what I have observed today I can conclude:
on page 88 of the "Backup and Recovery User's Guide 12c Release 1 (12.1) E17630-13", the example 5-1 SHOW ALL Command is the same of the "Backup and Recovery User's Guide 11g Release 2 (11.2) E10642-06" on page 82 and that output is related to a Oracle Database 11g version (the 10g doesn't print the first line "RMAN configuration parameters for database with db_unique_name PROD1 are:").

I'm going to write a post on the related Oracle forum.

That's all.

How to create a PDB from a Non-CDB (12c) using Oracle Data Pump

Beside the DBMS_PDB package you can use Oracle Data Pump to create a pluggable database from a non-container database: it consists to export the non-CDB and import into an empty and already created pluggable database.

In my case the non-container database I want to transform into a pluggable database is named ORCL and both the source and the target database are Oracle Database 12c databases.

Before proceeding you have to create first a new pluggable database.
I used DBCA ("Manage Pluggable Databases" --> "Create a Pluggable Database" and so on...) and selected CDBTEST as container database to host the new pluggable database, named ORCL2 (ORCL was created in this post using DBMS_PDB package).

Let me create into the non-container database some tablespaces, users and a table with few rows per user with the following anonymous block.

SQL@ORCL> begin 
  2  for i in 1 .. 10 loop
  3    execute immediate 'create tablespace marcov_' || ltrim(to_char(i, '09')) || ' datafile ''/opt/app/oracle/oradata/ORCL/marcov_' || ltrim(to_char(i, '09')) || '.dbf'' size 20M';
  4    execute immediate  'create user marcov_' || ltrim(to_char(i, '09')) || ' identified by oracle default tablespace marcov_' || ltrim(to_char(i, '09')) || ' quota unlimited on marcov_' || ltrim(to_char(i, '09')) ;
  5    execute immediate 'create table marcov_' || ltrim(to_char(i, '09')) || '.T1 (A NUMBER)';
  6    execute immediate 'insert into marcov_' || ltrim(to_char(i, '09')) || '.T1 select level from dual connect by level <= 10';
  7    commit;
  8  end loop;
  9  end;
 10  /

PL/SQL procedure successfully completed.

It's time to start with the export. In the non-container database you need to put all the interested tablespaces in READ ONLY mode.

SQL@ORCL> begin
  2  for i in 1 .. 10 loop
  3    execute immediate 'alter tablespace marcov_' || ltrim(to_char(i, '09')) || ' read only';
  4  end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

I cannot of course modify any data on those tablespaces.

SQL@ORCL> insert into marcov_01.t1 values (1);
insert into marcov_01.t1 values (1)
                      *
ERROR at line 1:
ORA-00372: file 5 cannot be modified at this time
ORA-01110: data file 5: '/opt/app/oracle/oradata/ORCL/marcov_01.dbf'

Here is the list of the available tablespaces. I forgot to set in READ ONLY mode the USERS tablespace.

SQL@ORCL> select TABLESPACE_NAME, STATUS from dba_tablespaces;

TABLESPACE_NAME         STATUS
------------------------------ ---------
SYSTEM          ONLINE
SYSAUX          ONLINE
UNDOTBS1         ONLINE
TEMP          ONLINE
USERS          ONLINE
MARCOV_01         READ ONLY
MARCOV_02         READ ONLY
MARCOV_03         READ ONLY
MARCOV_04         READ ONLY
MARCOV_05         READ ONLY
MARCOV_06         READ ONLY
MARCOV_07         READ ONLY
MARCOV_08         READ ONLY
MARCOV_09         READ ONLY
MARCOV_10         READ ONLY

All tablespace in the transportable set must be set to READ ONLY mode, including the USERS tablespace. SYS and SYSAUX tablespace are not transportable.

SQL@ORCL> alter tablespace users read only;

Tablespace altered.

Now I can export the non-container database ORCL using the option TRANSPORTABLE=ALWAYS and FULL=Y.

[oracle@vsi08devpom ~]$ expdp system/oracle TRANSPORTABLE=ALWAYS FULL=Y dumpfile=full_orcl.dmp logfile=full_orcl.log

Export: Release 12.1.0.1.0 - Production on Thu Aug 8 12:16:46 2013

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_FULL_01":  system/******** TRANSPORTABLE=ALWAYS FULL=Y dumpfile=full_orcl.dmp logfile=full_orcl.log 
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/PLUGTS_FULL/FULL/PLUGTS_TABLESPACE
Processing object type DATABASE_EXPORT/PLUGTS_FULL/PLUGTS_BLK
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.140 MB
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/RADM_FPTM
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/SCHEMA/ON_USER_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
Processing object type DATABASE_EXPORT/RESOURCE_COST
Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/EARLY_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOU/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type DATABASE_EXPORT/STATISTICS/MARKER
Processing object type DATABASE_EXPORT/END_PLUGTS_BLK
Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/AUDIT_UNIFIED/AUDIT_POLICY_ENABLE
Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER
. . exported "SYS"."KU$_USER_MAPPING_VIEW"               5.906 KB      28 rows
. . exported "SYS"."DAM_CONFIG_PARAM$"                   6.507 KB      14 rows
. . exported "SYS"."TSDP_PARAMETER$"                     5.929 KB       1 rows
. . exported "SYS"."TSDP_POLICY$"                        5.898 KB       1 rows
. . exported "SYS"."TSDP_SUBPOL$"                        6.304 KB       1 rows
. . exported "SYSTEM"."REDO_DB"                          23.42 KB       1 rows
. . exported "WMSYS"."WM$ENV_VARS$"                      6.062 KB       5 rows
. . exported "WMSYS"."WM$EVENTS_INFO$"                   5.796 KB      12 rows
. . exported "WMSYS"."WM$HINT_TABLE$"                    9.437 KB      75 rows
. . exported "WMSYS"."WM$NEXTVER_TABLE$"                 6.359 KB       1 rows
. . exported "WMSYS"."WM$VERSION_HIERARCHY_TABLE$"       5.960 KB       1 rows
. . exported "WMSYS"."WM$WORKSPACES_TABLE$"              12.08 KB       1 rows
. . exported "WMSYS"."WM$WORKSPACE_PRIV_TABLE$"          6.546 KB       8 rows
. . exported "SYS"."AUD$"                                    0 KB       0 rows
. . exported "SYS"."DAM_CLEANUP_EVENTS$"                     0 KB       0 rows
. . exported "SYS"."DAM_CLEANUP_JOBS$"                       0 KB       0 rows
. . exported "SYS"."TSDP_ASSOCIATION$"                       0 KB       0 rows
. . exported "SYS"."TSDP_CONDITION$"                         0 KB       0 rows
. . exported "SYS"."TSDP_FEATURE_POLICY$"                    0 KB       0 rows
. . exported "SYS"."TSDP_PROTECTION$"                        0 KB       0 rows
. . exported "SYS"."TSDP_SENSITIVE_DATA$"                    0 KB       0 rows
. . exported "SYS"."TSDP_SENSITIVE_TYPE$"                    0 KB       0 rows
. . exported "SYS"."TSDP_SOURCE$"                            0 KB       0 rows
. . exported "SYSTEM"."REDO_LOG"                             0 KB       0 rows
. . exported "WMSYS"."WM$BATCH_COMPRESSIBLE_TABLES$"         0 KB       0 rows
. . exported "WMSYS"."WM$CONSTRAINTS_TABLE$"                 0 KB       0 rows
. . exported "WMSYS"."WM$CONS_COLUMNS$"                      0 KB       0 rows
. . exported "WMSYS"."WM$LOCKROWS_INFO$"                     0 KB       0 rows
. . exported "WMSYS"."WM$MODIFIED_TABLES$"                   0 KB       0 rows
. . exported "WMSYS"."WM$MP_GRAPH_WORKSPACES_TABLE$"         0 KB       0 rows
. . exported "WMSYS"."WM$MP_PARENT_WORKSPACES_TABLE$"        0 KB       0 rows
. . exported "WMSYS"."WM$NESTED_COLUMNS_TABLE$"              0 KB       0 rows
. . exported "WMSYS"."WM$REMOVED_WORKSPACES_TABLE$"          0 KB       0 rows
. . exported "WMSYS"."WM$RESOLVE_WORKSPACES_TABLE$"          0 KB       0 rows
. . exported "WMSYS"."WM$RIC_LOCKING_TABLE$"                 0 KB       0 rows
. . exported "WMSYS"."WM$RIC_TABLE$"                         0 KB       0 rows
. . exported "WMSYS"."WM$RIC_TRIGGERS_TABLE$"                0 KB       0 rows
. . exported "WMSYS"."WM$UDTRIG_DISPATCH_PROCS$"             0 KB       0 rows
. . exported "WMSYS"."WM$UDTRIG_INFO$"                       0 KB       0 rows
. . exported "WMSYS"."WM$VERSION_TABLE$"                     0 KB       0 rows
. . exported "WMSYS"."WM$VT_ERRORS_TABLE$"                   0 KB       0 rows
. . exported "WMSYS"."WM$WORKSPACE_SAVEPOINTS_TABLE$"        0 KB       0 rows
. . exported "SYSTEM"."SCHEDULER_PROGRAM_ARGS"           9.484 KB      12 rows
. . exported "SYS"."AUDTAB$TBS$FOR_EXPORT"               5.937 KB       2 rows
. . exported "SYS"."NACL$_ACE_EXP"                       9.914 KB       1 rows
. . exported "SYS"."NACL$_HOST_EXP"                      6.898 KB       1 rows
. . exported "WMSYS"."WM$EXP_MAP"                        7.703 KB       3 rows
. . exported "SYS"."DBA_SENSITIVE_DATA"                      0 KB       0 rows
. . exported "SYS"."DBA_TSDP_POLICY_PROTECTION"              0 KB       0 rows
. . exported "SYS"."FGA_LOG$FOR_EXPORT"                      0 KB       0 rows
. . exported "SYS"."NACL$_WALLET_EXP"                        0 KB       0 rows
. . exported "SYSTEM"."SCHEDULER_JOB_ARGS"                   0 KB       0 rows
Master table "SYSTEM"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:
  /opt/app/oracle/admin/ORCL/dpdump/full_orcl.dmp
******************************************************************************
Datafiles required for transportable tablespace MARCOV_01:
  /opt/app/oracle/oradata/ORCL/marcov_01.dbf
Datafiles required for transportable tablespace MARCOV_02:
  /opt/app/oracle/oradata/ORCL/marcov_02.dbf
Datafiles required for transportable tablespace MARCOV_03:
  /opt/app/oracle/oradata/ORCL/marcov_03.dbf
Datafiles required for transportable tablespace MARCOV_04:
  /opt/app/oracle/oradata/ORCL/marcov_04.dbf
Datafiles required for transportable tablespace MARCOV_05:
  /opt/app/oracle/oradata/ORCL/marcov_05.dbf
Datafiles required for transportable tablespace MARCOV_06:
  /opt/app/oracle/oradata/ORCL/marcov_06.dbf
Datafiles required for transportable tablespace MARCOV_07:
  /opt/app/oracle/oradata/ORCL/marcov_07.dbf
Datafiles required for transportable tablespace MARCOV_08:
  /opt/app/oracle/oradata/ORCL/marcov_08.dbf
Datafiles required for transportable tablespace MARCOV_09:
  /opt/app/oracle/oradata/ORCL/marcov_09.dbf
Datafiles required for transportable tablespace MARCOV_10:
  /opt/app/oracle/oradata/ORCL/marcov_10.dbf
Datafiles required for transportable tablespace USERS:
  /opt/app/oracle/oradata/ORCL/users01.dbf
Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at Thu Aug 8 12:19:16 2013 elapsed 0 00:02:26

At the end of the log file you can see the datafiles I have to transport to the source pluggable database.
In the new pluggable database the DATA_PUMP_DIR directory object is not created automatically, so before proceeding I have to create a new directory object and granting all the necessary privileges.

[oracle@vsi08devpom ~]$ export ORACLE_SID=CDBTEST
[oracle@vsi08devpom ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Thu Aug 8 12:36:46 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, OLAP, Advanced Analytics and Real Application Testing options

SQL@CDBTEST> alter session set container=ORCL2;

Session altered.

SQL@ORCL2> create directory ORCL2_DATA_PUMP_DIR as '/opt/app/oracle/admin/CDBTEST/dpdump/';

Directory created.

SQL@ORCL2> grant read,write on directory ORCL2_DATA_PUMP_DIR to system;

Grant succeeded.

SQL@ORCL2> SELECT * FROM DBA_DIRECTORIES WHERE DIRECTORY_NAME = 'ORCL2_DATA_PUMP_DIR';

OWNER DIRECTORY_NAME    DIRECTORY_PATH       ORIGIN_CON_ID
----- -------------------- ---------------------------------------- -------------
SYS   ORCL2_DATA_PUMP_DIR  /opt/app/oracle/admin/CDBTEST/dpdump/    8

I need to copy the dump file to the directory pointed to by the ORCL2_DATA_PUMP_DIR directory object.
In my case the non-container database and the new pluggable database are on the same machine so I can use a simple cp command.

[oracle@vsi08devpom ~]$ cp /opt/app/oracle/admin/ORCL/dpdump/full_orcl.dmp /opt/app/oracle/admin/CDBTEST/dpdump/

I need also to copy the datafiles of the transportable tablespaces from the source non-container database to a place accessible to the pluggable database.
The default directory currently contains the following datafiles:

[oracle@vsi08devpom ORCL2]$ pwd
/opt/app/oracle/oradata/CDBTEST/ORCL2
[oracle@vsi08devpom ORCL2]$ ll
total 922608
-rw-r----- 1 oracle oinstall   5251072 Aug  8 12:37 ORCL2_users01.dbf
-rw-r----- 1 oracle oinstall 665853952 Aug  8 14:45 sysaux01.dbf
-rw-r----- 1 oracle oinstall 272637952 Aug  8 14:45 system01.dbf
-rw-r----- 1 oracle oinstall  20979712 Aug  8 13:22 temp01.dbf

My transportable tablespaces coming from the non-container database ORCL are now copied into a directory accessible by the pluggable database ORCL2.

[oracle@vsi08devpom ~]$ cp /opt/app/oracle/oradata/ORCL/users01.dbf /opt/app/oracle/oradata/ORCL/marcov_* /opt/app/oracle/oradata/CDBTEST/ORCL2/
[oracle@vsi08devpom ORCL2]$ ll
total 1132868
-rw-r----- 1 oracle oinstall  20979712 Aug  8 14:49 marcov_01.dbf
-rw-r----- 1 oracle oinstall  20979712 Aug  8 14:49 marcov_02.dbf
-rw-r----- 1 oracle oinstall  20979712 Aug  8 14:49 marcov_03.dbf
-rw-r----- 1 oracle oinstall  20979712 Aug  8 14:49 marcov_04.dbf
-rw-r----- 1 oracle oinstall  20979712 Aug  8 14:49 marcov_05.dbf
-rw-r----- 1 oracle oinstall  20979712 Aug  8 14:49 marcov_06.dbf
-rw-r----- 1 oracle oinstall  20979712 Aug  8 14:49 marcov_07.dbf
-rw-r----- 1 oracle oinstall  20979712 Aug  8 14:49 marcov_08.dbf
-rw-r----- 1 oracle oinstall  20979712 Aug  8 14:49 marcov_09.dbf
-rw-r----- 1 oracle oinstall  20979712 Aug  8 14:49 marcov_10.dbf
-rw-r----- 1 oracle oinstall   5251072 Aug  8 12:37 ORCL2_users01.dbf
-rw-r----- 1 oracle oinstall 665853952 Aug  8 14:45 sysaux01.dbf
-rw-r----- 1 oracle oinstall 272637952 Aug  8 14:45 system01.dbf
-rw-r----- 1 oracle oinstall  20979712 Aug  8 13:22 temp01.dbf
-rw-r----- 1 oracle oinstall   5251072 Aug  8 14:49 users01.dbf

Add an entry to the tnsnames.ora file referencing to the service of the pluggable database.

[oracle@vsi08devpom ~]$ vi /opt/app/oracle/product/12.1.0/db_1/network/admin/tnsnames.ora 

ORCL2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vsi08devpom.mydomain.it)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL2)
    )
  )

List all your datafiles in the TRANSPORT_DATAFILES option of the Data Dump Import utility (or use a parameter file if you have many datafiles to specify).

[oracle@vsi08devpom ~]$ impdp system/oracle@orcl2 TRANSPORT_DATAFILES='/opt/app/oracle/oradata/CDBTEST/ORCL2/marcov_01.dbf','/opt/app/oracle/oradata/CDBTEST/ORCL2/marcov_02.dbf','/opt/app/oracle/oradata/CDBTEST/ORCL2/marcov_03.dbf','/opt/app/oracle/oradata/CDBTEST/ORCL2/marcov_04.dbf','/opt/app/oracle/oradata/CDBTEST/ORCL2/marcov_05.dbf','/opt/app/oracle/oradata/CDBTEST/ORCL2/marcov_06.dbf','/opt/app/oracle/oradata/CDBTEST/ORCL2/marcov_07.dbf','/opt/app/oracle/oradata/CDBTEST/ORCL2/marcov_08.dbf','/opt/app/oracle/oradata/CDBTEST/ORCL2/marcov_09.dbf','/opt/app/oracle/oradata/CDBTEST/ORCL2/marcov_10.dbf','/opt/app/oracle/oradata/CDBTEST/ORCL2/users01.dbf' FULL=Y DIRECTORY=ORCL2_DATA_PUMP_DIR dumpfile=full_orcl.dmp 

Import: Release 12.1.0.1.0 - Production on Thu Aug 8 15:27:18 2013

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/********@orcl2 TRANSPORT_DATAFILES=/opt/app/oracle/oradata/CDBTEST/ORCL2/marcov_01.dbf,/opt/app/oracle/oradata/CDBTEST/ORCL2/marcov_02.dbf,/opt/app/oracle/oradata/CDBTEST/ORCL2/marcov_03.dbf,/opt/app/oracle/oradata/CDBTEST/ORCL2/marcov_04.dbf,/opt/app/oracle/oradata/CDBTEST/ORCL2/marcov_05.dbf,/opt/app/oracle/oradata/CDBTEST/ORCL2/marcov_06.dbf,/opt/app/oracle/oradata/CDBTEST/ORCL2/marcov_07.dbf,/opt/app/oracle/oradata/CDBTEST/ORCL2/marcov_08.dbf,/opt/app/oracle/oradata/CDBTEST/ORCL2/marcov_09.dbf,/opt/app/oracle/oradata/CDBTEST/ORCL2/marcov_10.dbf,/opt/app/oracle/oradata/CDBTEST/ORCL2/users01.dbf FULL=Y DIRECTORY=ORCL2_DATA_PUMP_DIR dumpfile=full_orcl.dmp 
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PLUGTS_FULL/PLUGTS_BLK
ORA-39123: Data Pump transportable tablespace job aborted
ORA-29349: tablespace 'USERS' already exists

Job "SYSTEM"."SYS_IMPORT_FULL_01" stopped due to fatal error at Thu Aug 8 15:27:28 2013 elapsed 0 00:00:08

The new pluggable database ORCL2 has already a tablespace named USERS so the Data Pump Import utility aborted its job with the error "ORA-29349: tablespace 'USERS' already exists". I decided to rename the already existing tablespace to USERS_ORCL2.

SQL@ORCL2> alter tablespace USERS rename to USERS_ORCL2;

Tablespace altered.

Now I can run the import job again.

[oracle@vsi08devpom ~]$ impdp system/oracle@orcl2 TRANSPORT_DATAFILES='/opt/app/oracle/oradata/CDBTEST/ORCL2/marcov_01.dbf','/opt/app/oracle/oradata/CDBTEST/ORCL2/marcov_02.dbf','/opt/app/oracle/oradata/CDBTEST/ORCL2/marcov_03.dbf','/opt/app/oracle/oradata/CDBTEST/ORCL2/marcov_04.dbf','/opt/app/oracle/oradata/CDBTEST/ORCL2/marcov_05.dbf','/opt/app/oracle/oradata/CDBTEST/ORCL2/marcov_06.dbf','/opt/app/oracle/oradata/CDBTEST/ORCL2/marcov_07.dbf','/opt/app/oracle/oradata/CDBTEST/ORCL2/marcov_08.dbf','/opt/app/oracle/oradata/CDBTEST/ORCL2/marcov_09.dbf','/opt/app/oracle/oradata/CDBTEST/ORCL2/marcov_10.dbf','/opt/app/oracle/oradata/CDBTEST/ORCL2/users01.dbf' FULL=Y DIRECTORY=ORCL2_DATA_PUMP_DIR dumpfile=full_orcl.dmp 

Import: Release 12.1.0.1.0 - Production on Thu Aug 8 15:30:33 2013

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/********@orcl2 TRANSPORT_DATAFILES=/opt/app/oracle/oradata/CDBTEST/ORCL2/marcov_01.dbf,/opt/app/oracle/oradata/CDBTEST/ORCL2/marcov_02.dbf,/opt/app/oracle/oradata/CDBTEST/ORCL2/marcov_03.dbf,/opt/app/oracle/oradata/CDBTEST/ORCL2/marcov_04.dbf,/opt/app/oracle/oradata/CDBTEST/ORCL2/marcov_05.dbf,/opt/app/oracle/oradata/CDBTEST/ORCL2/marcov_06.dbf,/opt/app/oracle/oradata/CDBTEST/ORCL2/marcov_07.dbf,/opt/app/oracle/oradata/CDBTEST/ORCL2/marcov_08.dbf,/opt/app/oracle/oradata/CDBTEST/ORCL2/marcov_09.dbf,/opt/app/oracle/oradata/CDBTEST/ORCL2/marcov_10.dbf,/opt/app/oracle/oradata/CDBTEST/ORCL2/users01.dbf FULL=Y DIRECTORY=ORCL2_DATA_PUMP_DIR dumpfile=full_orcl.dmp 
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
ORA-39342: Internal error - failed to import internal objects tagged with LABEL_SECURITY due to ORA-00955: name is already used by an existing object.
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PLUGTS_FULL/PLUGTS_BLK
Processing object type DATABASE_EXPORT/TABLESPACE
ORA-39083: Object type TABLESPACE:"UNDOTBS1" failed to create with error:
ORA-01516: nonexistent log file, data file, or temporary file "/opt/app/oracle/oradata/ORCL/undotbs01.dbf"
Failing sql is:
 ALTER DATABASE DATAFILE '/opt/app/oracle/oradata/ORCL/undotbs01.dbf' RESIZE 241172480
ORA-31684: Object type TABLESPACE:"TEMP" already exists
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
ORA-31684: Object type USER:"OUTLN" already exists
Processing object type DATABASE_EXPORT/RADM_FPTM
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/SCHEMA/ON_USER_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
Processing object type DATABASE_EXPORT/RESOURCE_COST
Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
. . imported "SYS"."KU$_EXPORT_USER_MAP"                 5.906 KB      28 rows
Processing object type DATABASE_EXPORT/EARLY_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
. . imported "SYS"."AMGT$DP$DAM_CONFIG_PARAM$"           6.507 KB      14 rows
. . imported "SYS"."DP$TSDP_PARAMETER$"                  5.929 KB       1 rows
. . imported "SYS"."DP$TSDP_POLICY$"                     5.898 KB       1 rows
. . imported "SYS"."DP$TSDP_SUBPOL$"                     6.304 KB       1 rows
. . imported "SYSTEM"."REDO_DB_TMP"                      23.42 KB       1 rows
. . imported "WMSYS"."E$ENV_VARS$"                       6.062 KB       5 rows
. . imported "WMSYS"."E$EVENTS_INFO$"                    5.796 KB      12 rows
. . imported "WMSYS"."E$HINT_TABLE$"                     9.437 KB      75 rows
. . imported "WMSYS"."E$NEXTVER_TABLE$"                  6.359 KB       1 rows
. . imported "WMSYS"."E$VERSION_HIERARCHY_TABLE$"        5.960 KB       1 rows
. . imported "WMSYS"."E$WORKSPACES_TABLE$"               12.08 KB       1 rows
. . imported "WMSYS"."E$WORKSPACE_PRIV_TABLE$"           6.546 KB       8 rows
. . imported "SYS"."AMGT$DP$AUD$"                            0 KB       0 rows
. . imported "SYS"."AMGT$DP$DAM_CLEANUP_EVENTS$"             0 KB       0 rows
. . imported "SYS"."AMGT$DP$DAM_CLEANUP_JOBS$"               0 KB       0 rows
. . imported "SYS"."DP$TSDP_ASSOCIATION$"                    0 KB       0 rows
. . imported "SYS"."DP$TSDP_CONDITION$"                      0 KB       0 rows
. . imported "SYS"."DP$TSDP_FEATURE_POLICY$"                 0 KB       0 rows
. . imported "SYS"."DP$TSDP_PROTECTION$"                     0 KB       0 rows
. . imported "SYS"."DP$TSDP_SENSITIVE_DATA$"                 0 KB       0 rows
. . imported "SYS"."DP$TSDP_SENSITIVE_TYPE$"                 0 KB       0 rows
. . imported "SYS"."DP$TSDP_SOURCE$"                         0 KB       0 rows
. . imported "SYSTEM"."REDO_LOG_TMP"                         0 KB       0 rows
. . imported "WMSYS"."E$BATCH_COMPRESSIBLE_TABLES$"          0 KB       0 rows
. . imported "WMSYS"."E$CONSTRAINTS_TABLE$"                  0 KB       0 rows
. . imported "WMSYS"."E$CONS_COLUMNS$"                       0 KB       0 rows
. . imported "WMSYS"."E$LOCKROWS_INFO$"                      0 KB       0 rows
. . imported "WMSYS"."E$MODIFIED_TABLES$"                    0 KB       0 rows
. . imported "WMSYS"."E$MP_GRAPH_WORKSPACES_TABLE$"          0 KB       0 rows
. . imported "WMSYS"."E$MP_PARENT_WORKSPACES_TABLE$"         0 KB       0 rows
. . imported "WMSYS"."E$NESTED_COLUMNS_TABLE$"               0 KB       0 rows
. . imported "WMSYS"."E$REMOVED_WORKSPACES_TABLE$"           0 KB       0 rows
. . imported "WMSYS"."E$RESOLVE_WORKSPACES_TABLE$"           0 KB       0 rows
. . imported "WMSYS"."E$RIC_LOCKING_TABLE$"                  0 KB       0 rows
. . imported "WMSYS"."E$RIC_TABLE$"                          0 KB       0 rows
. . imported "WMSYS"."E$RIC_TRIGGERS_TABLE$"                 0 KB       0 rows
. . imported "WMSYS"."E$UDTRIG_DISPATCH_PROCS$"              0 KB       0 rows
. . imported "WMSYS"."E$UDTRIG_INFO$"                        0 KB       0 rows
. . imported "WMSYS"."E$VERSION_TABLE$"                      0 KB       0 rows
. . imported "WMSYS"."E$VT_ERRORS_TABLE$"                    0 KB       0 rows
. . imported "WMSYS"."E$WORKSPACE_SAVEPOINTS_TABLE$"         0 KB       0 rows
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
. . imported "SYSTEM"."SCHEDULER_PROGRAM_ARGS_TMP"       9.484 KB      12 rows
. . imported "SYS"."AMGT$DP$AUDTAB$TBS$FOR_EXPORT"       5.937 KB       2 rows
. . imported "SYS"."NACL$_ACE_IMP"                       9.914 KB       1 rows
. . imported "SYS"."NACL$_HOST_IMP"                      6.898 KB       1 rows
. . imported "WMSYS"."E$EXP_MAP"                         7.703 KB       3 rows
. . imported "SYS"."DP$DBA_SENSITIVE_DATA"                   0 KB       0 rows
. . imported "SYS"."DP$DBA_TSDP_POLICY_PROTECTION"           0 KB       0 rows
. . imported "SYS"."AMGT$DP$FGA_LOG$FOR_EXPORT"              0 KB       0 rows
. . imported "SYS"."NACL$_WALLET_IMP"                        0 KB       0 rows
. . imported "SYSTEM"."SCHEDULER_JOB_ARGS_TMP"               0 KB       0 rows
Processing object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOU/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type DATABASE_EXPORT/STATISTICS/MARKER
Processing object type DATABASE_EXPORT/END_PLUGTS_BLK
Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/AUDIT_UNIFIED/AUDIT_POLICY_ENABLE
Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 4 error(s) at Thu Aug 8 15:31:59 2013 elapsed 0 00:01:25

After the import job is completed (the above errors can be ignored in my case) I'm able to use the new pluggable database ORCL2:
it contains all the data previously used in the non-container database ORCL.

[oracle@vsi08devpom CDBTEST]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Thu Aug 8 15:50:21 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, OLAP, Advanced Analytics and Real Application Testing options

SQL@CDBTEST> alter session set container=ORCL2;

Session altered.

SQL@ORCL2> select count(*) from marcov_01.t1;

  COUNT(*)
----------
 10

SQL@ORCL2> select count(*) from marcov_10.t1;

  COUNT(*)
----------
 10

That's all.

The first bug on Oracle Database 12c: how to create a PDB by cloning a remote existing pluggable database

In this post I want to describe how to create a PDB by cloning a remote existing pluggable database: at the end of this process you should have a new PDB (in my case PDB103) contained in a target CDB (CDB001) cloned from a source PDB (PDB003) contained in a remote different CDB (CDBTEST).
In my case both source and target databases are located on the same machine.

When I took part at the presentation of the new Oracle Database 12c here in Rome I took some pictures, but I was not ready with my phone when an interesting slide was presented.

While testing the scenario of today I suddenly remembered about that missing pictures: luckily few days later I received from Oracle also the pdf of the event so I'm now able to show you the following screenshot:

In 2006 I tested the beta release of Oracle Database 11gR1 because at that time my company needed to test new features for its products.
As you can read from the screenshot many test cases were conducted by Oracle and by beta testers, but a bug is always behind the corner when you consider a complex software solution as the Oracle Database and today I'm not able to close my thread with the usual "That's all" comment.

Anyway let's start describing the content of my current containers. My machine has two container databases named CDB001 and CBTEST.

[oracle@vsi08devpom ~]$ ps -ef|grep smon
oracle    4081     1  0 Jul15 ?        00:01:23 ora_smon_CDB001
oracle   16455     1  0 Jul17 ?        00:01:25 ora_smon_CDBTEST
oracle   26501 26466  0 14:08 pts/1    00:00:00 grep smon

On the target database CDB001 there is only one pluggable database (PDB101)

[oracle@vsi08devpom ~]$ env|grep SID
ORACLE_SID=CDB001
[oracle@vsi08devpom ~]$ sqlplus / as sysdba

SQL@CDB001> select name, open_mode from V$PDBS;

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

On the source database CDBTEST there are four pluggable databases (PDBTEST1, PDBTEST2, PDBTEST3 and PDB003)

[oracle@vsi08devpom ~]$ env|grep SID
ORACLE_SID=CDBTEST

SQL@CDBTEST> select NAME, OPEN_MODE FROM V$PDBS;

NAME          OPEN_MODE
------------------------------ ----------
PDB$SEED         READ ONLY
PDBTEST1         READ WRITE
PDBTEST2         READ WRITE
PDBTEST3         READ WRITE
PDB003          READ WRITE

The PDB003 pluggable database contains few rows into MARCOV.T1 table:

SQL@CDBTEST> alter session set container=PDB003;

Session altered.

SQL@CDBTEST> select count(*) from marcov.T1;

  COUNT(*)
----------
       100

I'm going to create a database link into the CDB001 container database connecting to CDBTEST using the ezconnect method.

SQL@CDB001> create database link CDBTEST_AT_VSI08DEVPOM connect to SYSTEM identified by oracle using 'vsi08devpom.mydomain.it:1521/CDBTEST';

Database link created.

I'm able to successfully test the new database link.

SQL@CDB001> select * from cdb_pdbs;

    PDB_ID PDB_NAME     DBID    CON_UID GUID        STATUS    CREATION_SCN     CON_ID
---------- ---------- ---------- ---------- -------------------------------- ------------- ------------ ----------
  3 PDB0101     532253118  532253118 E18E282148FD10A5E0430100007FC94D NORMAL  1734497   1
  2 PDB$SEED   4063453634 4063453634 E18E1CE36B940C96E0430100007FE168 NORMAL  1720746   1

SQL@CDB001> select * from cdb_pdbs@CDBTEST_AT_VSI08DEVPOM;

    PDB_ID PDB_NAME     DBID    CON_UID GUID        STATUS    CREATION_SCN     CON_ID
---------- ---------- ---------- ---------- -------------------------------- ------------- ------------ ----------
  2 PDB$SEED   4063610283 4063610283 E1B2A529DB382EACE0430100007F78B8 NORMAL      217   1
  3 PDBTEST1   3064465721 3064465721 E1B436871D9E4110E0430100007F9BBC NORMAL  1547881   1
  4 PDBTEST2   2395404598 2395404598 E1B43A36FA0B41A9E0430100007F6671 NORMAL  1548944   1
  5 PDBTEST3   2434165039 2434165039 E1B43D98C0DC41F6E0430100007F7CE7 NORMAL  1550036   1
  6 PDB003     1448206714 1448206714 E2B9BE56B8B936CEE045000000000001 NORMAL  2744910   1

Under the directory of the CDB001 container there are the following files and directories:

[oracle@vsi08devpom CDB001]$ pwd
/opt/app/oracle/oradata/CDB001
[oracle@vsi08devpom CDB001]$ ll
total 2286044
-rw-r----- 1 oracle oinstall   17973248 Aug  5 14:18 control01.ctl
drwxr-x--- 2 oracle oinstall       4096 Jul 15 15:52 PDB0101
drwxr-x--- 2 oracle oinstall       4096 Jul 15 15:48 pdbseed
-rw-r----- 1 oracle oinstall   52429312 Aug  5 14:00 redo01.log
-rw-r----- 1 oracle oinstall   52429312 Aug  5 14:18 redo02.log
-rw-r----- 1 oracle oinstall   52429312 Aug  5 02:20 redo03.log
-rw-r----- 1 oracle oinstall 1226842112 Aug  5 14:15 sysaux01.dbf
-rw-r----- 1 oracle oinstall  828383232 Aug  5 14:15 system01.dbf
-rw-r----- 1 oracle oinstall   62922752 Aug  5 13:52 temp01.dbf
-rw-r----- 1 oracle oinstall   94380032 Aug  5 14:15 undotbs01.dbf
-rw-r----- 1 oracle oinstall    5251072 Aug  5 14:06 users01.dbf

As usual to clone the remote pluggable database it must be in READ ONLY mode. The current open mode of PDB003 pluggable database is READ WRITE.

SQL@CDBTEST> select pdb_name, status from CDB_PDBS;

PDB_NAME   STATUS
---------- -------------
PDB$SEED   NORMAL
PDBTEST1   NORMAL
PDBTEST2   NORMAL
PDBTEST3   NORMAL
PDB003    NORMAL

SQL@CDBTEST> select name, open_mode from V$PDBS;

NAME          OPEN_MODE
------------------------------ ----------
PDB$SEED         READ ONLY
PDBTEST1         READ WRITE
PDBTEST2         READ WRITE
PDBTEST3         READ WRITE
PDB003          READ WRITE

I need to close and open it in READ ONLY mode.

SQL@CDBTEST> alter pluggable database PDB003 close immediate;

Pluggable database altered.

SQL@CDBTEST> alter pluggable database PDB003 open read only;

Pluggable database altered.

From the target container database I can now issue the following command to clone the pluggable database PDB003 contained in the remote database container CDBTEST.

SQL@CDB001> create pluggable database PDB103 from PDB003@CDBTEST_AT_VSI08DEVPOM file_name_convert=('/opt/app/oracle/oradata/CDBTEST/PDB003','/opt/app/oracle/oradata/CDB001/PDB103');
create pluggable database PDB103 from PDB003@CDBTEST_AT_VSI08DEVPOM file_name_convert=('/opt/app/oracle/oradata/CDBTEST/PDB003','/opt/app/oracle/oradata/CDB001/PDB103');
                                             *
ERROR at line 1:
ORA-17628: Oracle error 19505 returned by remote Oracle server
ORA-19505: failed to identify file ""

On the alert log the following error is logged:

ORA-17628 signalled during: create pluggable database PDB103 from PDB003@CDBTEST_AT_VSI08DEVPOM file_name_convert=('/opt/app/oracle/oradata/CDBTEST/PDB003','/opt/app/oracle/oradata/CDB001/PDB103')...

Because the database link can connect to either the root of the remote container database or directly to the remote pluggable database, I decided, after several failed attempts, to create another database link referencing the remote pluggable database:

SQL@CDB001> create database link PDB003_AT_CDBTEST connect to SYSTEM identified by oracle using 'vsi08devpom.recupitalia.it:1521/pdb003';

Database link created.

SQL@CDB001> select * from cdb_pdbs@PDB003_AT_CDBTEST;

no rows selected

But even with the different database link the statement continues to fail:

SQL@CDB001> create pluggable database PDB103 from PDB003@PDB003_AT_CDBTEST file_name_convert=('/opt/app/oracle/oradata/CDBTEST/PDB003','/opt/app/oracle/oradata/CDB001/PDB103');
create pluggable database PDB103 from PDB003@PDB003_AT_CDBTEST file_name_convert=('/opt/app/oracle/oradata/CDBTEST/PDB003','/opt/app/oracle/oradata/CDB001/PDB103')
                                             *
ERROR at line 1:
ORA-17628: Oracle error 19505 returned by remote Oracle server
ORA-19505: failed to identify file ""

Let's try using a different method to connect the two database containers and create another database link using the following CDBTEST tns entry:

CDBTEST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vsi08devpom.mydomain.it)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = CDBTEST)
    )
  )

Creation and testing of the database link using the CDBTEST tns entry.

SQL@CDB001> create database link CDBTEST_TNS_AT_VSI08DEVPOM connect to SYSTEM identified by oracle using 'CDBTEST';

Database link created.

SQL@CDB001> select * from cdb_pdbs@CDBTEST_TNS_AT_VSI08DEVPOM;

    PDB_ID PDB_NAME     DBID    CON_UID GUID        STATUS    CREATION_SCN     CON_ID
---------- ---------- ---------- ---------- -------------------------------- ------------- ------------ ----------
  2 PDB$SEED   4063610283 4063610283 E1B2A529DB382EACE0430100007F78B8 NORMAL      217   1
  3 PDBTEST1   3064465721 3064465721 E1B436871D9E4110E0430100007F9BBC NORMAL  1547881   1
  4 PDBTEST2   2395404598 2395404598 E1B43A36FA0B41A9E0430100007F6671 NORMAL  1548944   1
  5 PDBTEST3   2434165039 2434165039 E1B43D98C0DC41F6E0430100007F7CE7 NORMAL  1550036   1
  6 PDB003     1448206714 1448206714 E2B9BE56B8B936CEE045000000000001 NORMAL  2744910   1

The statement using the CDBTEST_TNS_AT_VSI08DEVPOM database link still continues to fail:

SQL@CDB001> create pluggable database PDB103 from PDB003@CDBTEST_TNS_AT_VSI08DEVPOM file_name_convert=('/opt/app/oracle/oradata/CDBTEST/PDB003','/opt/app/oracle/oradata/CDB001/PDB103');
create pluggable database PDB103 from PDB003@CDBTEST_TNS_AT_VSI08DEVPOM file_name_convert=('/opt/app/oracle/oradata/CDBTEST/PDB003','/opt/app/oracle/oradata/CDB001/PDB103')
                                             *
ERROR at line 1:
ORA-17628: Oracle error 19505 returned by remote Oracle server
ORA-19505: failed to identify file ""

Let's create the latest database link connected to the following tns entry:

PDB003_CDBTEST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vsi08devpom.mydomain.it)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdb003)
    )
  )

Creation and testing of the database link using the PDB003_CDBTEST tns entry.

SQL@CDB001> create database link PDB003_TNS_AT_VSI08DEVPOM connect to SYSTEM identified by oracle using 'PDB003_CDBTEST';

Database link created.

SQL@CDB001> select * from cdb_pdbs@PDB003_TNS_AT_VSI08DEVPOM;

no rows selected

The statement continues to fail even using the latest available database link: PDB003_TNS_AT_VSI08DEVPOM

SQL@CDB001> create pluggable database PDB103 from PDB003@PDB003_TNS_AT_VSI08DEVPOM file_name_convert=('/opt/app/oracle/oradata/CDBTEST/PDB003','/opt/app/oracle/oradata/CDB001/PDB103');
create pluggable database PDB103 from PDB003@PDB003_TNS_AT_VSI08DEVPOM file_name_convert=('/opt/app/oracle/oradata/CDBTEST/PDB003','/opt/app/oracle/oradata/CDB001/PDB103')
                                             *
ERROR at line 1:
ORA-17628: Oracle error 19505 returned by remote Oracle server
ORA-19505: failed to identify file ""

The available database links created and used are the following:

SQL@CDB001> select DB_LINK, HOST, OWNER from dba_db_links;

DB_LINK          HOST          OWNER
------------------------------ --------------------------------------------- -----
CDBTEST_AT_VSI08DEVPOM        vsi08devpom.mydomain.it:1521/CDBTEST      SYS
PDB003_AT_CDBTEST        vsi08devpom.mydomain.it:1521/pdb003      SYS
CDBTEST_TNS_AT_VSI08DEVPOM     CDBTEST          SYS
PDB003_TNS_AT_VSI08DEVPOM      PDB003_CDBTEST         SYS

And there is still no way to create a pluggable database to a remote target destination from a local source. You can see on the "SQL Language Reference" which CREATE PLUGGABLE DATABASE syntax is allowed:

SQL@CDBTEST> create pluggable database PDB103@CDB001_AT_VSI08DEVPOM from PDB003;
create pluggable database PDB103@CDB001_AT_VSI08DEVPOM from PDB003;
                                *
ERROR at line 1:
ORA-00922: missing or invalid option

Even tracing the unix Oracle server process with the strace system util I was not able to get useful information to know how successfully complete the statement and why it's failing.

The end of the story: after I have contacted My Oracle Support the Oracle engineer confirmed the issue, tracked as bug 15931910 and it is still being worked upon by the development team.
Other platforms could be affected by this bug but I have no reason and time to test it.

So at the moment there is no way to clone a remote pluggable database using a database link, but as a workaround you can follow this post to manually create it to the new target destination

How to create a PDB from a Non-CDB using DBMS_PDB package

To test this scenario I have recently created using DBCA a non container database named ORCL. CDB001 and CDBTEST are instead two container databases.

[oracle@vsi08devpom ~]$ ps -ef|grep smon
oracle    4081     1  0 Jul15 ?        00:01:30 ora_smon_CDB001
oracle   11394     1  0 10:20 ?        00:00:00 ora_smon_ORCL
oracle   12586  9609  0 10:46 pts/0    00:00:00 grep smon
oracle   16455     1  0 Jul17 ?        00:01:34 ora_smon_CDBTEST

I want to consolidate this database plugging it into a container database.
In this scenario I'm going to use the DBMS_PDB package to create the XML file with the metadata of ORCL database.
Before using the DBMS_PDB.DESCRIBE procedure, the ORCL database needs to be in READ ONLY mode.

[oracle@vsi08devpom ~]$ export ORACLE_SID=ORCL
[oracle@vsi08devpom ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Wed Aug 7 10:47:29 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, OLAP, Advanced Analytics and Real Application Testing options

SQL@ORCL> show con_name;

CON_NAME
------------------------------
Non Consolidated
SQL@ORCL> select name, open_mode from V$DATABASE;

NAME   OPEN_MODE
--------- --------------------
ORCL   READ WRITE

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

SQL@ORCL> startup mount;
ORACLE instance started.

Total System Global Area  471830528 bytes
Fixed Size      2289688 bytes
Variable Size    264245224 bytes
Database Buffers   197132288 bytes
Redo Buffers      8163328 bytes
Database mounted.
SQL@ORCL> alter database open read only;

Database altered.

SQL@ORCL> begin
  2  dbms_pdb.describe( pdb_descr_file => '/opt/app/oracle/oradata/orcl.xml');
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL@ORCL> host ls /opt/app/oracle/oradata/orcl*
/opt/app/oracle/oradata/orcl.xml

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

Now connect to the container database. In my case I want to plug ORCL database into the CDBTEST container. It currently contains 4 pluggable databases.

[oracle@vsi08devpom ~]$ export ORACLE_SID=CDBTEST
[oracle@vsi08devpom ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Wed Aug 7 11:07:12 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, OLAP, Advanced Analytics and Real Application Testing options

SQL@CDBTEST> show con_name;

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

SQL@CDBTEST> select name, open_mode from V$PDBS;

NAME          OPEN_MODE
------------------------------ ----------
PDB$SEED         READ ONLY
PDBTEST1         READ WRITE
PDBTEST2         READ WRITE
PDBTEST3         READ WRITE
PDB003          READ ONLY

Here is the situation of the directories for the CDBTEST container database:

[oracle@vsi08devpom ~]$ cd /opt/app/oracle/oradata/CDBTEST/
[oracle@vsi08devpom CDBTEST]$ ll
total 3459784
-rw-r----- 1 oracle oinstall   17973248 Aug  7 11:13 control01.ctl
drwxr-x--- 2 oracle oinstall       4096 Aug  1 14:54 PDB003
drwxr-x--- 2 oracle oinstall       4096 Jul 17 11:25 pdbseed
drwxr-x--- 2 oracle oinstall       4096 Jul 17 13:16 PDBTEST1
drwxr-x--- 2 oracle oinstall       4096 Jul 17 13:17 PDBTEST2
drwxr-x--- 2 oracle oinstall       4096 Jul 17 13:18 PDBTEST3
-rw-r----- 1 oracle oinstall   52429312 Aug  7 11:12 redo01.log
-rw-r----- 1 oracle oinstall   52429312 Aug  7 00:16 redo02.log
-rw-r----- 1 oracle oinstall   52429312 Aug  7 06:00 redo03.log
-rw-r----- 1 oracle oinstall 1310728192 Aug  7 11:12 sysaux01.dbf
-rw-r----- 1 oracle oinstall  828383232 Aug  7 11:12 system01.dbf
-rw-r----- 1 oracle oinstall   63971328 Aug  7 11:01 temp01.dbf
-rw-r----- 1 oracle oinstall 1205870592 Aug  7 11:12 undotbs01.dbf
-rw-r----- 1 oracle oinstall    5251072 Aug  7 06:05 users01.dbf

Now you can plug the ORCL database into the CDBTEST container database using the usual syntax (have a look at this post)

SQL@CDBTEST> create pluggable database ORCL using '/opt/app/oracle/oradata/orcl.xml' copy file_name_convert=('/opt/app/oracle/oradata/ORCL','/opt/app/oracle/oradata/CDBTEST/ORCL');

Pluggable database created.

The Oracle software copies under the directory /opt/app/oracle/oradata/CDBTEST/ORCL all the datafiles coming from /opt/app/oracle/oradata/ORCL location.

[oracle@vsi08devpom CDBTEST]$ ll
total 3459788
-rw-r----- 1 oracle oinstall   17973248 Aug  7 12:10 control01.ctl
drwxr-x--- 2 oracle oinstall       4096 Aug  7 11:49 ORCL
drwxr-x--- 2 oracle oinstall       4096 Aug  1 14:54 PDB003
drwxr-x--- 2 oracle oinstall       4096 Jul 17 11:25 pdbseed
drwxr-x--- 2 oracle oinstall       4096 Jul 17 13:16 PDBTEST1
drwxr-x--- 2 oracle oinstall       4096 Jul 17 13:17 PDBTEST2
drwxr-x--- 2 oracle oinstall       4096 Jul 17 13:18 PDBTEST3
-rw-r----- 1 oracle oinstall   52429312 Aug  7 12:10 redo01.log
-rw-r----- 1 oracle oinstall   52429312 Aug  7 00:16 redo02.log
-rw-r----- 1 oracle oinstall   52429312 Aug  7 06:00 redo03.log
-rw-r----- 1 oracle oinstall 1310728192 Aug  7 12:10 sysaux01.dbf
-rw-r----- 1 oracle oinstall  828383232 Aug  7 12:07 system01.dbf
-rw-r----- 1 oracle oinstall   63971328 Aug  7 12:03 temp01.dbf
-rw-r----- 1 oracle oinstall 1205870592 Aug  7 12:10 undotbs01.dbf
-rw-r----- 1 oracle oinstall    5251072 Aug  7 06:05 users01.dbf

A new pluggable database is created with a NEW status

SQL@CDBTEST> select pdb_id, pdb_name, dbid, status, creation_scn, con_id from CDB_PDBS;

    PDB_ID PDB_NAME     DBID STATUS        CREATION_SCN CON_ID
---------- ---------- ---------- ------------- ------------ ----------
  2 PDB$SEED   4063610283 NORMAL   217      1
  3 PDBTEST1   3064465721 NORMAL      1547881      1
  4 PDBTEST2   2395404598 NORMAL      1548944      1
  5 PDBTEST3   2434165039 NORMAL      1550036      1
  6 PDB003     1448206714 NORMAL      2744910      1
  7 ORCL       1350603571 NEW      3226095      1

It's still not the time to open the new PDB.
You need first to execute, while connected to the new pluggable database, the script $ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql:

[oracle@vsi08devpom CDBTEST]$ ll /opt/app/oracle/product/12.1.0/db_1/rdbms/admin/noncdb_to_pdb.sql 
-rw-r--r-- 1 oracle oinstall 19191 Apr 15 22:27 /opt/app/oracle/product/12.1.0/db_1/rdbms/admin/noncdb_to_pdb.sql

SQL@CDBTEST> alter session set container=ORCL;

Session altered.

SQL@CDBTEST> @/opt/app/oracle/product/12.1.0/db_1/rdbms/admin/noncdb_to_pdb.sql
SQL@CDBTEST> SET SERVEROUTPUT ON
SQL@CDBTEST> SET FEEDBACK 1
SQL@CDBTEST> SET NUMWIDTH 10
...
...
...
SQL@CDBTEST> -- leave the PDB in the same state it was when we started
SQL@CDBTEST> BEGIN
  2    execute immediate '&open_sql &restricted_state';
  3  EXCEPTION
  4    WHEN OTHERS THEN
  5    BEGIN
  6      IF (sqlcode <> -900) THEN
  7        RAISE;
  8      END IF;
  9    END;
 10  END;
 11  /

PL/SQL procedure successfully completed.

SQL@CDBTEST> 
SQL@CDBTEST> WHENEVER SQLERROR CONTINUE;
SQL@CDBTEST>

Now you can open the new ORCL pluggable database.

SQL@CDBTEST> show con_name;

CON_NAME
------------------------------
ORCL
SQL@CDBTEST> alter database open;

Database altered.

The database is so available and ready to be used.

SQL@CDBTEST> select pdb_id, pdb_name, dbid, status, creation_scn, con_id from CDB_PDBS;

    PDB_ID PDB_NAME     DBID STATUS        CREATION_SCN CON_ID
---------- ---------- ---------- ------------- ------------ ----------
  2 PDB$SEED   4063610283 NORMAL   217      1
  3 PDBTEST1   3064465721 NORMAL      1547881      1
  4 PDBTEST2   2395404598 NORMAL      1548944      1
  5 PDBTEST3   2434165039 NORMAL      1550036      1
  6 PDB003     1448206714 NORMAL      2744910      1
  7 ORCL       1350603571 NORMAL      3226095      1

That's all.

How to unplug a PDB and plug it back into the same CDB

You have just unplugged a PDB and want to know how to eventually plug it back into the same container.
Let's assume I have a pluggable database named PDB003 and I am just able to unplug it using the following commands:

SQL> alter pluggable database pdb003 close immediate;

Pluggable database altered.

SQL> alter pluggable database PDB003 unplug into '/app/oracle/oradata/pdb003.xml';

Pluggable database altered.

If you query the CDB_PDBS view you can receive useful information about all PDBs contained: id, name, unique identifiers, SCN at the time of creation and the status.
The STATUS column has several values:

• NEW, the pdb has been created but never opened 
• NORMAL, pdb available to be used 
• UNPLUGGED, the pdb has been unplugged 
• NEEDS UPGRADE, the pdb must be upgraded 
• CONVERTING, a non-CDB is going to be converted into a pdb 
• UNUSABLE, the pdb cannot be used (creation in progress) 

In my case the PDB003 pluggable database is in an UNPLUGGED status as expected:

SQL> select * from CDB_PDBS;

    PDB_ID PDB_NAME     DBID    CON_UID GUID        STATUS    CREATION_SCN     CON_ID
---------- ---------- ---------- ---------- -------------------------------- ------------- ------------ ----------
  2 PDB$SEED   4063489868 4063489868 E19363E52C005C9AE045000000000001 NORMAL      233   1
  3 PDB001     1701599811 1701599811 E1F26215682E1142E045000000000001 NORMAL  1821405   1
  4 PDB002     1497027100 1497027100 E1F329ECE0F411E6E045000000000001 NORMAL  1844390   1
  5 PDB003     1448206714 1448206714 E2B9BE56B8B936CEE045000000000001 UNPLUGGED  1997215   1

To plug again the pluggable database PDB003 inside the original container (CDB001) you cannot use a syntax like: ALTER PLUGGABLE DATABASE ... PLUG (it doesn't exist in the "SQL Language Reference" manual a PLUG clause) ...

SQL> alter pluggable database PDB003 plug;
alter pluggable database PDB003 plug
                                *
ERROR at line 1:
ORA-00922: missing or invalid option

... neither you can simply use CREATE PLUGGABLE DATABASE ... USING ...

SQL> create pluggable database PDB003 using '/app/oracle/oradata/pdb003.xml' nocopy tempfile reuse;
create pluggable database PDB003 using '/app/oracle/oradata/pdb003.xml' nocopy tempfile reuse
*
ERROR at line 1:
ORA-65012: Pluggable database PDB003 already exists.

You need first to drop the pluggable database (DROP PLUGGABLE DATABASE command) and then create it again (CREATE PLUGGABLE DATABASE command).
In this way the pluggable database is plugged again into the same CDB and using the same PDB name.
Have a look at the following commands:

SQL> drop pluggable database PDB003;

Pluggable database dropped.

SQL> create pluggable database PDB003 using '/app/oracle/oradata/pdb003.xml' nocopy;               

Pluggable database created.

SQL> select PDB_NAME, STATUS from CDB_PDBS;

PDB_NAME   STATUS  
---------- ------------- 
PDB$SEED   NORMAL 
PDB001     NORMAL 
PDB002     NORMAL 
PDB003     NEW  

SQL> alter session set container=PDB003;

Session altered.

SQL> alter database open;

Database altered.

SQL> select count(*) from marcov.T1;

  COUNT(*)
----------
       100

Have a look again at the CDB_PDBS view and note the differences compared with the previous results.
All the unique identifiers of PDB003 have been recreated.

SQL> select * from CDB_PDBS;

    PDB_ID PDB_NAME     DBID    CON_UID GUID        STATUS    CREATION_SCN     CON_ID
---------- ---------- ---------- ---------- -------------------------------- ------------- ------------ ----------
  2 PDB$SEED   4063489868 4063489868 E19363E52C005C9AE045000000000001 NORMAL      233   1
  3 PDB001     1701599811 1701599811 E1F26215682E1142E045000000000001 NORMAL  1821405   1
  4 PDB002     1497027100 1497027100 E1F329ECE0F411E6E045000000000001 NORMAL  1844390   1
  5 PDB003     1448206714 1448206714 E2B9BE56B8B936CEE045000000000001 NORMAL  3110422   1

That's all.

How to create a pluggable database by unplugging an existing PDB and plugging it into another container

So I want to create a new pluggable database into the CDBTEST container located into vsi08devpom machine (@vsi08devpom in this post) and I want the exact copy of PDB003 pluggable database currently contained into CDB001 located into my local machine (@localhost in this post).

Why not simply unplug it from my local machine and plug it into the target machine ?

When you want to disconnect a pluggable database from a multitenant container you can unplug it, specifying a file containing its metadata information formatted in XML, used eventually if you want to plug it in another container like we want to do.

To successfully unplug a pdb it must be put first in MOUNTED mode otherwise the following error is thrown:

SQL@localhost> select name, open_mode from V$PDBS;

NAME          OPEN_MODE
------------------------------ ----------
PDB$SEED         READ ONLY
PDB001          READ WRITE
PDB002          READ WRITE
PDB003          READ WRITE

SQL@localhost> alter pluggable database PDB003 unplug into '/app/oracle/oradata/pdb003.xml';
alter pluggable database PDB003 unplug into '/app/oracle/oradata/pdb003.xml'
*
ERROR at line 1:
ORA-65025: Pluggable database PDB003 is not closed on all instances.

So before proceeding with the unplug operation you have to close the pluggable database

SQL@localhost> alter pluggable database pdb003 close immediate;

Pluggable database altered.

SQL@localhost> alter pluggable database PDB003 unplug into '/app/oracle/oradata/pdb003.xml';

Pluggable database altered.

Even if the previous operation is not destructive, the container knows that a pluggable database was available, currently the database administrator no longer want to use it, you of course cannot open an unplugged database.

SQL@localhost> select name, open_mode from V$PDBS;

NAME         OPEN_MODE
-------------   ----------
PDB$SEED READ ONLY
PDB001         READ WRITE
PDB002         READ WRITE
PDB003         MOUNTED

SQL@localhost> alter pluggable database pdb003 open;
alter pluggable database pdb003 open
*
ERROR at line 1:
ORA-65086: cannot open/close the pluggable database

Where you can have information about the plugged or unplugged status of your pluggable databases ?
Query the CDB_PDBS view: it displays information about the PDBs associated with the CDB, including the status of each PDB.

SQL@localhost> select PDB_NAME, STATUS from CDB_PDBS where PDB_NAME = 'PDB003';

PDB_NAME   STATUS
---------- -------------
PDB003    UNPLUGGED

To create a new pluggable database on vsi08devpom machine I have to copy the datafiles of the unplugged database to the target machine.

[oracle@localhost PDB003]$ ll
total 921688
-rw-r-----. 1 oracle oinstall   5251072 Aug  1 14:37 PDB001_users01.dbf
-rw-r-----. 1 oracle oinstall 665853952 Aug  1 14:37 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 272637952 Aug  1 14:37 system01.dbf
-rw-r-----. 1 oracle oinstall  20979712 Aug  1 14:30 temp01.dbf
[oracle@localhost PDB003]$ scp -r ../PDB003 vsi08devpom.mydomain.it:/opt/app/oracle/oradata/CDBTEST/PDB003
oracle@vsi08devpom.mydomain.it's password: 
temp01.dbf                                               100%   20MB 620.9KB/s   00:33    
system01.dbf                                             100%  260MB 543.4KB/s   08:10    
PDB001_users01.dbf                                       100% 5128KB   1.0MB/s   00:05    
sysaux01.dbf                                             100%  635MB 545.1KB/s   19:53    
[oracle@localhost PDB003]$ scp /app/oracle/oradata/pdb003.xml vsi08devpom.mydomain.it:/opt/app/oracle/oradata
oracle@vsi08devpom.mydomain.it's password: 
pdb003.xml                                               100% 4003     3.9KB/s   00:00   

Log into the target machine.
My current multitenant container CDBTEST located on vsi08devpom is formed by three pluggable databases:

[oracle@vsi08devpom CDBTEST]$ export ORACLE_SID=CDBTEST
[oracle@vsi08devpom CDBTEST]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Thu Aug 1 15:32:55 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, OLAP, Advanced Analytics and Real Application Testing options

SQL@vsi08devpom> select name, open_mode from V$PDBS;

NAME          OPEN_MODE
------------------------------ ----------
PDB$SEED         READ ONLY
PDBTEST1         READ WRITE
PDBTEST2         READ WRITE
PDBTEST3         READ WRITE

SQL@vsi08devpom> select pdb_name, status from CDB_PDBS;

PDB_NAME   STATUS
---------- -------------
PDB$SEED   NORMAL
PDBTEST1   NORMAL
PDBTEST2   NORMAL
PDBTEST3   NORMAL

Now it's time to plug into CDBTEST container on vsi08devpom machine the unplugged database coming from CDB container (running on my local machine). The source location of the datafiles contained into the XML file is different from the target destination, so I cannot use just the following command, because the error "ORA-27041: unable to open file" is thrown as you can see.

SQL@vsi08devpom> create pluggable database PDB003 using '/opt/app/oracle/oradata/pdb003.xml' nocopy tempfile reuse;
create pluggable database PDB003 using '/opt/app/oracle/oradata/pdb003.xml' nocopy tempfile reuse
*
ERROR at line 1:
ORA-19505: failed to identify file
"/app/oracle/oradata/CDB001/PDB003/system01.dbf"
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 9

I need to appropriately use the SOURCE_FILE_NAME_CONVERT clause in the command:

SQL@vsi08devpom> create pluggable database PDB003 using '/opt/app/oracle/oradata/pdb003.xml' source_file_name_convert=('/app/oracle/oradata/CDB001','/opt/app/oracle/oradata/CDBTEST') nocopy tempfile reuse;

Pluggable database created.

The new PDB003 pluggable database on vsi08devpom machine has the same DBID of that one...

SQL@vsi08devpom> select pdb_name, status, DBID, CON_UID, GUID from CDB_PDBS;

PDB_NAME   STATUS        DBID    CON_UID GUID
---------- ------------- ---------- ---------- --------------------------------
PDB$SEED   NORMAL  4063610283 4063610283 E1B2A529DB382EACE0430100007F78B8
PDBTEST1   NORMAL  3064465721 3064465721 E1B436871D9E4110E0430100007F9BBC
PDBTEST2   NORMAL  2395404598 2395404598 E1B43A36FA0B41A9E0430100007F6671
PDBTEST3   NORMAL  2434165039 2434165039 E1B43D98C0DC41F6E0430100007F7CE7
PDB003    NEW   1448206714 1448206714 E2B9BE56B8B936CEE045000000000001

... unplugged from my local machine

SQL@localhost> select pdb_name, status, DBID, CON_UID, GUID from CDB_PDBS;

PDB_NAME   STATUS        DBID    CON_UID GUID
---------- ------------- ---------- ---------- --------------------------------
PDB$SEED   NORMAL  4063489868 4063489868 E19363E52C005C9AE045000000000001
PDB001    NORMAL  1701599811 1701599811 E1F26215682E1142E045000000000001
PDB002    NORMAL  1497027100 1497027100 E1F329ECE0F411E6E045000000000001
PDB003    UNPLUGGED  1448206714 1448206714 E2B9BE56B8B936CEE045000000000001

The new plugged database is in MOUNTED mode so I need to open it first:

SQL@vsi08devpom> select name, open_mode from V$PDBS;

NAME          OPEN_MODE
------------------------------ ----------
PDB$SEED         READ ONLY
PDBTEST1         READ WRITE
PDBTEST2         READ WRITE
PDBTEST3         READ WRITE
PDB003          MOUNTED

SQL@vsi08devpom> alter pluggable database PDB003 open;

Pluggable database altered.

My data is all there as expected!!!

SQL@vsi08devpom> alter session set container=PDB003;

Session altered.

SQL@vsi08devpom> select count(*) from marcov.T1;

  COUNT(*)
----------
       100

That's all.

How to create a pluggable database by cloning an existing local PDB

Using the CREATE PLUGGABLE DATABASE ... FROM command you can clone an existing pluggable database (the source pdb) to create a new pdb (the clone pdb).
The source pdb could be in the current local container or it can be located in a remote container (in a next post): during a clone of a remote pdb you need to use a database link referencing the remote container in the FROM clause.

Let's start cloning a local pluggable database. Be sure the current container is the root

SQL> show con_name;

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

Here are my current pluggable databases and I want to clone PDB001 which contains the user MARCOV and the table T1 (with 100 rows)

SQL> select name, open_mode from V$PDBS;

NAME          OPEN_MODE
------------------------------ ----------
PDB$SEED         READ ONLY
PDB001          READ WRITE
PDB002          READ WRITE

SQL> alter session set container=PDB001;

Session altered.

SQL> create user marcov identified by marcov quota unlimited on users;

User created.

SQL> grant create session to marcov;

Grant succeeded.

SQL> grant create table to marcov;

Grant succeeded.

SQL> connect marcov/marcov@PDB001
Connected.
SQL> show user
USER is "MARCOV"
SQL> show con_name

CON_NAME
------------------------------
PDB001

SQL> create table T1 (a number);

Table created.

SQL> create table T1 (a number);

Table created.

SQL> insert into T1 select level from dual connect by level < 101;

100 rows created.

SQL> commit;

Commit complete.

SQL> connect / as sysdba
Connected.
SQL> show user
USER is "SYS"
SQL> show con_name

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

Here is the content of my tnsnames.ora file

CDB001 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.2.15)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = CDB001)
    )
  )

PDB001 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.2.15)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PDB001)
    )
  )

To clone the source pluggable database PDB001 I need to open it in READ ONLY mode

SQL> select name, open_mode from v$pdbs;

NAME          OPEN_MODE
------------------------------ ----------
PDB$SEED         READ ONLY
PDB001          READ WRITE
PDB002          READ WRITE

SQL> alter pluggable database PDB001 open read only force;

Pluggable database altered.

On my file system I have the following directories and files.

[oracle@localhost CDB001]$ pwd
/app/oracle/oradata/CDB001
[oracle@localhost CDB001]$ ll
total 2163224
-rw-r-----. 1 oracle oinstall  17973248 Jul 30 13:12 control01.ctl
drwxr-x---. 2 oracle oinstall      4096 Jul 20 15:29 PDB001
drwxr-x---. 2 oracle oinstall      4096 Jul 20 16:22 PDB002
drwxr-x---. 2 oracle oinstall      4096 Jul 15 22:07 pdbseed
-rw-r-----. 1 oracle oinstall  52429312 Jul 30 13:12 redo01.log
-rw-r-----. 1 oracle oinstall  52429312 Jul 29 22:06 redo02.log
-rw-r-----. 1 oracle oinstall  52429312 Jul 30 02:32 redo03.log
-rw-r-----. 1 oracle oinstall 817897472 Jul 30 13:11 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 817897472 Jul 30 13:06 system01.dbf
-rw-r-----. 1 oracle oinstall  76554240 Jul 30 13:11 temp01.dbf
-rw-r-----. 1 oracle oinstall 325066752 Jul 30 13:10 undotbs01.dbf
-rw-r-----. 1 oracle oinstall   5251072 Jul 30 02:37 users01.dbf

Here is the content of PDB001 directory.

[oracle@localhost CDB001]$ ll PDB001/
total 922516
-rw-r-----. 1 oracle oinstall   5251072 Jul 30 13:08 PDB001_users01.dbf
-rw-r-----. 1 oracle oinstall 665853952 Jul 30 13:08 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 272637952 Jul 30 13:08 system01.dbf
-rw-r-----. 1 oracle oinstall  20979712 Jul 21 17:51 temp01.dbf

I'm going to create a new pluggable database called PDB003, cloning PDB001. The command to clone a pluggable database locally is the following:

SQL> create pluggable database PDB003 from PDB001 file_name_convert=('/app/oracle/oradata/CDB001/PDB001','/app/oracle/oradata/CDB001/PDB003');

Pluggable database created.

New files and directories are created. Note also the same name of the datafile PDB001_users01.dbf used as default tablespace for PDB003.

[oracle@localhost CDB001]$ ll
total 2163228
-rw-r-----. 1 oracle oinstall  17973248 Jul 30 13:19 control01.ctl
drwxr-x---. 2 oracle oinstall      4096 Jul 20 15:29 PDB001
drwxr-x---. 2 oracle oinstall      4096 Jul 20 16:22 PDB002
drwxr-x---. 2 oracle oinstall      4096 Jul 30 13:17 PDB003
drwxr-x---. 2 oracle oinstall      4096 Jul 15 22:07 pdbseed
-rw-r-----. 1 oracle oinstall  52429312 Jul 30 13:18 redo01.log
-rw-r-----. 1 oracle oinstall  52429312 Jul 29 22:06 redo02.log
-rw-r-----. 1 oracle oinstall  52429312 Jul 30 02:32 redo03.log
-rw-r-----. 1 oracle oinstall 817897472 Jul 30 13:18 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 817897472 Jul 30 13:17 system01.dbf
-rw-r-----. 1 oracle oinstall  76554240 Jul 30 13:18 temp01.dbf
-rw-r-----. 1 oracle oinstall 325066752 Jul 30 13:16 undotbs01.dbf
-rw-r-----. 1 oracle oinstall   5251072 Jul 30 02:37 users01.dbf
[oracle@localhost CDB001]$ ll PDB003
total 921688
-rw-r-----. 1 oracle oinstall   5251072 Jul 30 13:18 PDB001_users01.dbf
-rw-r-----. 1 oracle oinstall 665853952 Jul 30 13:18 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 272637952 Jul 30 13:18 system01.dbf
-rw-r-----. 1 oracle oinstall  20979712 Jul 30 13:17 temp01.dbf

After the command is sucessfully completed the status and the open mode of the new pluggable database are NEW and MOUNTED.

SQL> select PDB_NAME, STATUS from CDB_PDBS;

PDB_NAME   STATUS
---------- -------------
PDB$SEED   NORMAL
PDB001    NORMAL
PDB002    NORMAL
PDB003    NEW

SQL> select name, open_mode from V$PDBS;

NAME          OPEN_MODE
------------------------------ ----------
PDB$SEED         READ ONLY
PDB001          READ ONLY
PDB002          READ WRITE
PDB003          MOUNTED

A new service is created too.

SQL> select name, pdb from V$SERVICES order by creation_date;

NAME       PDB
-------------------- ------------------------------
CDB001XDB      CDB$ROOT
SYS$BACKGROUND      CDB$ROOT
CDB001       CDB$ROOT
SYS$USERS      CDB$ROOT
pdb001       PDB001
pdb002       PDB002
pdb003       PDB003

You can now open both pluggable databases with one simply command (have a look at this post for more information about the syntax and examples)

SQL> alter pluggable database PDB001,PDB003 open READ WRITE FORCE;

Pluggable database altered.

SQL> select name, open_mode from V$PDBS;

NAME       OPEN_MODE
-------------------- ----------
PDB$SEED      READ ONLY
PDB001       READ WRITE
PDB002       READ WRITE
PDB003       READ WRITE

SQL> select PDB_NAME, STATUS from CDB_PDBS;

PDB_NAME   STATUS
---------- -------------
PDB$SEED   NORMAL
PDB001    NORMAL
PDB002    NORMAL
PDB003    NORMAL

Add the following entry on the tnsnames.ora file.

PDB003 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.2.15)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PDB003)
    )
  )

Let's see my data on PDB003 and on PDB001

SQL> connect marcov/marcov@PDB003
Connected.
SQL> show con_name

CON_NAME
------------------------------
PDB003
SQL> select count(*) from marcov.T1;

  COUNT(*)
----------
       100

SQL> connect marcov/marcov@PDB001
Connected.
SQL> show con_name

CON_NAME
------------------------------
PDB001
SQL> select count(*) from marcov.T1;

  COUNT(*)
----------
       100

It is not possible to create a new pdb cloning a local or a remote seed: to create a new pdb from the seed you have to follow this post. If you try to clone from the seed template you will receive the ORA-65000 error, described below:

SQL> create pluggable database PDB004 from PDB$SEED file_name_convert=('/app/oracle/oradata/CDB001/pdbseed','/app/oracle/oradata/CDB001/PDB004');
create pluggable database PDB004 from PDB$SEED file_name_convert=('/app/oracle/oradata/CDB001/pdbseed','/app/oracle/oradata/CDB001/PDB004')
                          *
ERROR at line 1:
ORA-65000: missing or invalid pluggable database name

[oracle@localhost pdbseed]$ oerr ora 65000
65000, 00000, "missing or invalid pluggable database name"
// *Cause:  A valid pluggable database name was not present where required 
//     by the syntax of a CREATE PLUGGABLE DATABASE, ALTER PLUGGABLE
//          DATABASE or DROP PLUGGABLE DATABASE statement.
// *Action: Reissue the statement with a valid pluggable database name.

That's all.

How to change the open mode of all pluggable databases

When you need to modify the open mode of all your PDBs at the same time (look at this post if you want to change the open mode of only a specific pluggable database) you can use ALTER PLUGGABLE DATABASE command and the ALL option.
As usual you have to ensure that the current container is the root.

SQL> SELECT SYS_CONTEXT ('USERENV', 'CON_NAME') FROM DUAL;

SYS_CONTEXT('USERENV','CON_NAME')
--------------------------------------------------------------------------------
CDB$ROOT

The following statement for example changes the open mode of all your pluggable databases at the same time.

SQL>ALTER PLUGGABLE DATABASE ALL CLOSE IMMEDIATE;

Pluggable database altered.

SQL> select NAME, OPEN_MODE, CON_ID from V$PDBS;

NAME          OPEN_MODE      CON_ID
------------------------------ ---------- ----------
PDB$SEED         READ ONLY    2
PDB001          MOUNTED     3
PDB002          MOUNTED     4

When you need to change the open mode of all pluggable database except for listed ones you can include also the EXCEPT option as in the following example:

SQL> ALTER PLUGGABLE DATABASE ALL EXCEPT PDB001 OPEN READ WRITE;

Pluggable database altered.

SQL> select NAME, OPEN_MODE, CON_ID from V$PDBS;

NAME          OPEN_MODE      CON_ID
------------------------------ ---------- ----------
PDB$SEED         READ ONLY    2
PDB001          MOUNTED     3
PDB002          READ WRITE    4

To open all my pluggable databases I can simply execute the following command. It doesn't take care that PDB002 is already open in READ WRITE mode: any error is returned because the pluggable databases are in different open mode.

SQL> ALTER PLUGGABLE DATABASE ALL OPEN READ WRITE;

Pluggable database altered.

SQL> select NAME, OPEN_MODE, CON_ID from V$PDBS;

NAME          OPEN_MODE      CON_ID
------------------------------ ---------- ----------
PDB$SEED         READ ONLY    2
PDB001          READ WRITE    3
PDB002          READ WRITE    4

However, if any of your pluggable databases are in READ ONLY mode, then the statement returns the error "ORA-65019: pluggable database PDB001 already open" as you can see:

SQL> ALTER PLUGGABLE DATABASE ALL CLOSE IMMEDIATE;

Pluggable database altered.

SQL> select NAME, OPEN_MODE, CON_ID from V$PDBS;

NAME          OPEN_MODE      CON_ID
------------------------------ ---------- ----------
PDB$SEED         READ ONLY    2
PDB001          MOUNTED     3
PDB002          MOUNTED     4

SQL> ALTER PLUGGABLE DATABASE PDB001 OPEN READ ONLY;

Pluggable database altered.

SQL> select NAME, OPEN_MODE, CON_ID from V$PDBS;

NAME          OPEN_MODE      CON_ID
------------------------------ ---------- ----------
PDB$SEED         READ ONLY    2
PDB001          READ ONLY    3
PDB002          MOUNTED     4

SQL> ALTER PLUGGABLE DATABASE ALL OPEN READ WRITE;
ALTER PLUGGABLE DATABASE ALL OPEN READ WRITE
*
ERROR at line 1:
ORA-65019: pluggable database PDB001 already open

Even if the statement fails for pluggable database PDB001 it was able to open PDB002 as requested:

SQL> select NAME, OPEN_MODE, CON_ID from V$PDBS;

NAME          OPEN_MODE      CON_ID
------------------------------ ---------- ----------
PDB$SEED         READ ONLY    2
PDB001          READ ONLY    3
PDB002          READ WRITE    4

To avoid the error ORA-65019 you can include the FORCE option in your command:

SQL> ALTER PLUGGABLE DATABASE ALL OPEN READ WRITE FORCE;

Pluggable database altered.

SQL> select NAME, OPEN_MODE, CON_ID from V$PDBS;

NAME          OPEN_MODE      CON_ID
------------------------------ ---------- ----------
PDB$SEED         READ ONLY    2
PDB001          READ WRITE    3
PDB002          READ WRITE    4

To shutdown all your pluggable databases except one you can use the following command:

SQL> alter pluggable database all except PDB002 close immediate;

Pluggable database altered.

SQL> select name, open_mode from V$PDBS;

NAME          OPEN_MODE
------------------------------ ----------
PDB$SEED         READ ONLY
PDB001          MOUNTED
PDB002          READ WRITE

To list some of your pluggable databases you can use the comma as in the following example where I need to open all my pluggable databases (PDB001 and PDB002) except those listed (just PDB001 and PDB002!!). Nothing happens of course.

SQL> alter pluggable database all except PDB002,PDB001 open;

Pluggable database altered.

SQL> select name, open_mode from V$PDBS;

NAME          OPEN_MODE
------------------------------ ----------
PDB$SEED         READ ONLY
PDB001          MOUNTED
PDB002          READ WRITE

That's all.

Pluggable database: limitations of the open mode of the CDB imposed on the open mode of PDBs

The open mode of the container database imposes limitations on the open mode of PDBs.
For example, the root must be open before any PDBs can be open. Therefore, you might need to change the open mode of the root before changing the open mode of a PDB. Let's start.

Verify you are connected to the root container.

SQL> SELECT SYS_CONTEXT ('USERENV', 'CON_NAME') FROM DUAL;

SYS_CONTEXT('USERENV','CON_NAME')
--------------------------------------------------------------------------------
CDB$ROOT

Verify the open mode from the V$DATABASE view.

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

NAME       CON_ID OPEN_MODE
--------- ---------- --------------------
CDB001     0 READ WRITE

I've two pluggable databases in the current container: I want to close the pluggable database called PDB001.

SQL> alter pluggable database PDB001 close immediate;

Pluggable database altered.

Verify the open mode from the V$PDBS view.
One of my pluggable databases (PDB002) is available in READ WRITE mode, the second (PDB001) is in MOUNTED mode. The seed (PDB$SEED) is a template that you can use to create new PDBs and is always in READ ONLY mode.

SQL> select name, con_id, open_mode from v$pdbs;

NAME       CON_ID OPEN_MODE
------------------------------ ---------- ----------
PDB$SEED    2 READ ONLY
PDB001     3 MOUNTED
PDB002     4 READ WRITE

What does it happen to pluggable databases when you shutdown the root container and open it again in MOUNT mode ?

SQL> shutdown immediate;    
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size      2291472 bytes
Variable Size    473958640 bytes
Database Buffers   146800640 bytes
Redo Buffers      3276800 bytes
Database mounted.

The root container is in MOUNTED mode as expected.

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

NAME       CON_ID OPEN_MODE
--------- ---------- --------------------
CDB001     0 MOUNTED

SQL> SELECT SYS_CONTEXT ('USERENV', 'CON_NAME') FROM DUAL;

SYS_CONTEXT('USERENV','CON_NAME')
--------------------------------------------------------------------------------
CDB$ROOT

The three pluggable databases are all in MOUNTED mode too.

SQL> select name, con_id, open_mode from v$pdbs;

NAME       CON_ID OPEN_MODE
------------------------------ ---------- ----------
PDB$SEED    2 MOUNTED
PDB001     3 MOUNTED
PDB002     4 MOUNTED

You cannot open a pluggable database if the container is not open.

SQL> alter pluggable database PDB001 open read write;
alter pluggable database PDB001 open read write
*
ERROR at line 1:
ORA-01109: database not open

Open first the CDB instance containing the root, the seed and its pluggable databases.

SQL> alter database open;

Database altered.

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

NAME       CON_ID OPEN_MODE
--------- ---------- --------------------
CDB001     0 READ WRITE

The seed database is now put in READ ONLY mode; the other two pluggable databases are still in MOUNTED mode. So they don't start automatically and you have to take care of this.

SQL> select name, con_id, open_mode from v$pdbs;    

NAME       CON_ID OPEN_MODE
------------------------------ ---------- ----------
PDB$SEED    2 READ ONLY
PDB001     3 MOUNTED
PDB002     4 MOUNTED

You can now open pluggable databases as you want. I'm going to open a pluggable database, PDB001, in READ ONLY mode and then PDB002 in READ WRITE mode.

SQL> alter pluggable database pdb001 open read only;

Pluggable database altered.

SQL> alter pluggable database pdb002 open;          

Pluggable database altered.

SQL> select name, con_id, open_mode from v$pdbs;

NAME       CON_ID OPEN_MODE
------------------------------ ---------- ----------
PDB$SEED    2 READ ONLY
PDB001     3 READ ONLY
PDB002     4 READ WRITE

As you can see I didn't specify to open the pluggable database PDB002 in READ WRITE mode: as usual when you don't specify a valid option the READ WRITE is the default mode.

To open also the PDB001 pluggable database in READ WRITE mode you can use the following command and the FORCE option.

SQL> alter pluggable database PDB001 open read write force;

Pluggable database altered.

SQL> select name, con_id, open_mode from v$pdbs;

NAME       CON_ID OPEN_MODE
------------------------------ ---------- ----------
PDB$SEED    2 READ ONLY
PDB001     3 READ WRITE
PDB002     4 READ WRITE

That's all.

How to change the open mode of listed PDBs

The clauses of the ALTER PLUGGABLE DATABASE statement that modify the mode of a PDB are:
- OPEN READ WRITE [RESTRICTED] [FORCE]: it opens the PDB in read/write mode;
- OPEN READ ONLY [RESTRICTED] [FORCE]: it opens the PDB in read-only mode;
- OPEN UPGRADE [RESTRICTED]: it opens the PDB in migrate mode;
- CLOSE [IMMEDIATE]: it places the PDB in mounted mode and it is the PDB equivalent of the SQL*Plus SHUTDOWN command;

When using the RESTRICTED option, only users with RESTRICTED SESSION privilege can log into the PDB: all other sessions connected to the PDB without RESTRICTED SESSION privilege are terminated and their transactions rolled back.
When FORCE is specified, the statement opens a PDB that is currently closed and changes the open mode as specified.

To modify the open mode of one or more PDBs you have to ensure that the current container is the root:

SQL> SELECT SYS_CONTEXT ('USERENV', 'CON_NAME') FROM DUAL;

SYS_CONTEXT('USERENV','CON_NAME')
--------------------------------------------------------------------------------
CDB$ROOT

Let's get information about current pluggable databases querying the V$PDBS view:

SQL> select NAME, OPEN_MODE, CON_ID from V$PDBS;

NAME          OPEN_MODE      CON_ID
------------------------------ ---------- ----------
PDB$SEED         READ ONLY    2
PDB001          READ WRITE    3
PDB002          READ WRITE    4

I want to "shutdown" the pluggable database identified as PDB001: all current connected sessions will be terminated and their transaction rolled back.
You need to use the alter pluggable database command:

SQL> alter pluggable database pdb001 close immediate;

Pluggable database altered.

The specified pluggable database is put in MOUNTED mode:

SQL> select NAME, OPEN_MODE, CON_ID from V$PDBS;

NAME          OPEN_MODE      CON_ID
------------------------------ ---------- ----------
PDB$SEED         READ ONLY    2
PDB001          MOUNTED     3
PDB002          READ WRITE    4

If you want to open the pluggable database PDB001 in READ ONLY mode you can use again the alter pluggable database command:

SQL> alter pluggable database pdb001 open read only;

Pluggable database altered.

It's now open in READ ONLY mode.

SQL> select NAME, OPEN_MODE, CON_ID from V$PDBS;

NAME          OPEN_MODE      CON_ID
------------------------------ ---------- ----------
PDB$SEED         READ ONLY    2
PDB001          READ ONLY    3
PDB002          READ WRITE    4

What does it happen when you try to open it in READ WRITE mode ?

SQL> alter pluggable database pdb001 open read write;
alter pluggable database pdb001 open read write
*
ERROR at line 1:
ORA-65019: pluggable database PDB001 already open

Oracle throws the following exception:

[oracle@localhost trace]$ oerr ora 65019
65019, 00000, "pluggable database %s already open"
// *Cause:  An attempt was made to open a pluggable database that was already
//          opened.
// *Action: Check V$PLUGGABLE_DATABASE.STATE.

If you want to open in READ WRITE mode your pluggable database, currently in READ ONLY mode, you have to add the FORCE option to the previous command:

SQL> alter pluggable database pdb001 open read write force;

Pluggable database altered.

The pluggable database is now open in READ WRITE mode again:

SQL> select NAME, OPEN_MODE, CON_ID from V$PDBS;

NAME          OPEN_MODE      CON_ID
------------------------------ ---------- ----------
PDB$SEED         READ ONLY    2
PDB001          READ WRITE    3
PDB002          READ WRITE    4

In the next post I will decribe the limitations of the open mode of the root imposed on the open mode of PDBs.

That's all.

How to create a pluggable database PDB in a multitenant container database CDB using the files of the seed PDB$SEED

The new architecture provided by Oracle Database 12c enables an Oracle database to function as a multitenant container database (CDB):
it can include zero, one, or many pluggable databases (PDBs), that is a portable collection of schemas, schema objects and nonschema objects.

A multitenant container database (CDB) is formed by the following components:
- the root container (and exactly one root) named CDB$ROOT that stores Oracle-supplied metadata and common users (a database user known in every container);
- the seed template (and exactly one seed) named PDB$SEED used, if you want, to create new PDBs. It's not possible to add objects to or modify objects in the seed: it works only in READ ONLY mode;
- pluggable databases (zero, one, or many PDBs) named as you prefer that are your "old idea of databases" before Oracle Database 12c. A pluggable database contains the data and code required by your software application and is fully backward compatible with Oracle Database releases before Oracle Database 12c.

The options for creating a pluggable database (the so called PDB) fall into two main categories: copying and plugging in as you will see in this post and in the next posts.

Talking about copying, you have two options to copy a pluggable database:

• create a pluggable database (PDB) in a multitenant container database (CDB) using the files of the seed (PDB$SEED);
• create it by cloning a source PDB and plugging the clone into the CDB (bear in mind that the source PDB can be in the local CDB or in a remote CDB). 

Today I'm going to describe how to copy a pluggable database using the files of the seed from the SQL*Plus command line.

If you want to see how to create a pluggable database copying the files of the seed using DBCA you can simply watch the following video (I will create a post about it using screenshot as soon as possible).

To create a new pluggable database copying the files of the seed you have to ensure that the current container is the root:

SQL> SELECT SYS_CONTEXT ('USERENV', 'CON_NAME') FROM DUAL;

SYS_CONTEXT('USERENV','CON_NAME')
--------------------------------------------------------------------------------
CDB$ROOT

and your multitenant container database CDB must be also in read/write mode. My CDB is called CDB001:

SQL> select NAME, CDB, CON_ID, OPEN_MODE from V$DATABASE;

NAME   CDB   CON_ID OPEN_MODE
--------- --- ---------- --------------------
CDB001   YES        0 READ WRITE

Your script must include the CREATE PLUGGABLE DATABASE statement and eventually some other clauses, such as the following I used in my script:
- STORAGE: specify the limit of the amount of storage the PDB can use. Omitting this clause is equivalent to specify an unlimited amount;
- DEFAULT TABLESPACE: specify a default permanent tablespace for non-SYSTEM users. When you omit this clause the SYSTEM tablespace will be used as default permanent tablespace for non-SYSTEM users and this is not recommended;
- FILE_NAME_CONVERT: specify the target locations of the data files whereas the source files are those associated with the seed. This parameter is required when Oracle Managed Files is not enabled and the PDB_FILE_NAME_CONVERT initialization parameter is not set
- other clauses you can use are: ROLES, TEMPFILE REUSE and PATH_PREFIX.

SQL> CREATE PLUGGABLE DATABASE PDB002 
  2  ADMIN USER PDB002_ADMIN IDENTIFIED BY oracle
  3  storage (maxsize 5G MAX_SHARED_TEMP_SIZE 500M)
  4  DEFAULT TABLESPACE "USERS" DATAFILE '/app/oracle/oradata/CDB001/PDB002/PDB002_users01.dbf' SIZE 5M REUSE AUTOEXTEND ON 
  5  file_name_convert=('/app/oracle/oradata/CDB001/pdbseed/system01.dbf','/app/oracle/oradata/CDB001/PDB002/system01.dbf','/app/oracle/oradata/CDB001/pdbseed/sysaux01.dbf','/app/oracle/oradata/CDB001/PDB002/sysaux01.dbf','/app/oracle/oradata/CDB001/pdbseed/temp01.dbf','/app/oracle/oradata/CDB001/PDB002/temp01.dbf');

Pluggable database created.

From the alert log (vi /app/oracle/diag/rdbms/cdb001/CDB001/trace/alert_CDB001.log):

Sat Jul 20 16:21:48 2013
CREATE PLUGGABLE DATABASE PDB002 ADMIN USER PDB002_ADMIN IDENTIFIED BY * storage (maxsize 5G MAX_SHARED_TEMP_SIZE 500M) DEFAULT TABLESPACE "USERS" DATAFILE '/app/oracle/oradata/CDB001/PDB002/PDB002_users01.dbf' SIZE 5M REUSE AUTOEXTEND ON file_name_convert=('/app/oracle/oradata/CDB001/pdbseed/system01.dbf','/app/oracle/oradata/CDB001/PDB002/system01.dbf','/app/oracle/oradata/CDB001/pdbseed/sysaux01.dbf','/app/oracle/oradata/CDB001/PDB002/sysaux01.dbf','/app/oracle/oradata/CDB001/pdbseed/temp01.dbf','/app/oracle/oradata/CDB001/PDB002/temp01.dbf')
Sat Jul 20 16:22:35 2013
****************************************************************
Pluggable Database PDB002 with pdb id - 4 is created as UNUSABLE.
If any errors are encountered before the pdb is marked as NEW,
then the pdb must be dropped
****************************************************************
Deleting old file#2 from file$ 
Deleting old file#4 from file$ 
Adding new file#10 to file$(old file#2) 
Adding new file#11 to file$(old file#4) 
Successfully created internal service pdb002 at open
CREATE TABLESPACE USERS DATAFILE  '/app/oracle/oradata/CDB001/PDB002/PDB002_users01.dbf' SIZE 5M REUSE AUTOEXTEND ON  SEGMENT SPACE MANAGEMENT AUTO
Completed: CREATE TABLESPACE USERS DATAFILE  '/app/oracle/oradata/CDB001/PDB002/PDB002_users01.dbf' SIZE 5M REUSE AUTOEXTEND ON  SEGMENT SPACE MANAGEMENT AUTO
Sat Jul 20 16:22:49 2013
ALTER SYSTEM: Flushing buffer cache inst=0 container=4 local
****************************************************************
Post plug operations are now complete.
Pluggable database PDB002 with pdb id - 4 is now marked as NEW.
****************************************************************
Completed: CREATE PLUGGABLE DATABASE PDB002 ADMIN USER PDB002_ADMIN IDENTIFIED BY * storage (maxsize 5G MAX_SHARED_TEMP_SIZE 500M) DEFAULT TABLESPACE "USERS" DATAFILE '/app/oracle/oradata/CDB001/PDB002/PDB002_users01.dbf' SIZE 5M REUSE AUTOEXTEND ON file_name_convert=('/app/oracle/oradata/CDB001/pdbseed/system01.dbf','/app/oracle/oradata/CDB001/PDB002/system01.dbf','/app/oracle/oradata/CDB001/pdbseed/sysaux01.dbf','/app/oracle/oradata/CDB001/PDB002/sysaux01.dbf','/app/oracle/oradata/CDB001/pdbseed/temp01.dbf','/app/oracle/oradata/CDB001/PDB002/temp01.dbf')

As you can see when you create a pluggable database from the SQL*Plus command line it is open in MOUNTED mode. This is a different behaviour compared with the same operation performed by DBCA: in the final step DBCA is able to open the new pluggable database in READ WRITE mode.

SQL> select NAME, OPEN_MODE, CON_ID from V$PDBS;

NAME          OPEN_MODE      CON_ID
------------------------------ ---------- ----------
PDB$SEED         READ ONLY    2
PDB001          READ WRITE    3
PDB002          MOUNTED     4

To open your new pluggable database in READ WRITE mode execute the following alter pluggable database command:

SQL> alter pluggable database pdb002 open read write;

Pluggable database altered.

Now query again the V$PDBS view: your pluggable database is now available to the application.

SQL> select NAME, OPEN_MODE, CON_ID from V$PDBS;

NAME          OPEN_MODE      CON_ID
------------------------------ ---------- ----------
PDB$SEED         READ ONLY    2
PDB001          READ WRITE    3
PDB002          READ WRITE    4

That's all.