How to encrypt the data (tablespace or column's table) using a software keystore previously known as Oracle Wallet

To create a standard Oracle wallet and then add a master key to it you have to follow few basic steps:
1) Configure the sqlnet.ora file to define a file system location for the Software Keystore
2) Create the Software Keystore 
3) Opening a Software Keystore 
4) Setting the TDE Master Encryption Key in the Software Keystore 
5) Encrypt the Data 

The first four steps are already described here. Now it's time to encrypt your data.

5) Encrypt the Data 
Once you have created an Oracle Wallet and set a TDE master key in it, you can proceed to encrypt your data. Let's start creating a new encrypted tablespace first and then a column's table. My current data files:

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/app/oracle/oradata/CDB001/system01.dbf
/app/oracle/oradata/CDB001/sysaux01.dbf
/app/oracle/oradata/CDB001/undotbs01.dbf
/app/oracle/oradata/CDB001/users01.dbf

The statement to create an encrypted tablespace:

SQL> create tablespace ts_encrypted datafile '/app/oracle/oradata/CDB001/ts_encrypted01.dbf' size 10M encryption default storage(encrypt);

Tablespace created.

Information about the encrypted tablespace available in the database.

SQL> select a.name, b.TS#, b.ENCRYPTEDTS, b.ENCRYPTIONALG, b.CON_ID from V$TABLESPACE A, V$ENCRYPTED_TABLESPACES B                     
  2  where A.ts# = B.ts#;

NAME      TS# ENCRYPTEDTS ENCRYPTIONALG CON_ID
------------ --- ----------- ------------- ------
TS_ENCRYPTED 5   AES128      YES           1

How is it possible to test if the data is encrypted or not ? I'm going to create a table on the USERS (unencrypted) tablespace and another on the TS_ENCRYPTED tablespace. Because the Oracle Wallet is already open I can create on the encrypted tablespace the t1_encrypted table and insert some rows in it.

SQL> create table t1_not_encrypted (text varchar2(255)) tablespace USERS;

Table created.

SQL> create table t1_encrypted (text varchar2(255)) tablespace TS_ENCRYPTED;

Table created.

SQL> insert into t1_not_encrypted values ('my name is marcov');

1 row created.

SQL> insert into t1_encrypted values ('the secrets of marcov');

1 row created.

SQL> commit;

Commit complete.

Flush the buffer cache to be sure all data is written to the datafiles.

SQL> alter system flush buffer_cache;

System altered.

I'm able to grep and see the text on the USERS tablespace, but not that one on the TS_ENCRYPTED tablespace.

[oracle@localhost oracle]$ strings /app/oracle/oradata/CDB001/users01.dbf|grep "my name is"
my name is marcov
[oracle@localhost oracle]$ strings /app/oracle/oradata/CDB001/ts_encrypted01.dbf|grep "secrets"
[oracle@localhost oracle]$

Let's see what happens when the Oracle Wallet is closed. The following command closes an open Oracle Wallet.

SQL> administer key management set keystore close identified by "0racl30racle3";

keystore altered.

SQL> select * from v$encryption_wallet;

WRL_TYPE WRL_PARAMETER                                            STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
-------- -------------------------------------------------------- ------ ----------- --------- --------- ------
FILE     /app/oracle/product/12.1.0/dbhome_1/admin/CDB001/wallet  CLOSED UNKNOWN     SINGLE    UNDEFINED 0

When the Oracle Wallet is closed you can query every tables but those based on an encrypted tablespace.

SQL> select * from t1_not_encrypted;

TEXT
-----------------
my name is marcov

SQL> select * from t1_encrypted;
select * from t1_encrypted
              *
ERROR at line 1:
ORA-28365: wallet is not open

You have to open again the Oracle Wallet to successfully execute the query

SQL> administer key management set keystore open identified by "0racl30racle3";

keystore altered.

SQL> select * from t1_encrypted;

TEXT
---------------------
the secrets of marcov

Let's see how a closed Oracle Wallet affects an encrypted column of a table. I'm going to create a new table with two columns: one is encrypted and the other is not encrypted.

SQL> create table c##marcov.t2_column_encrypted (text varchar2(255), text_encrypted varchar2(255) encrypt) tablespace USERS;

Table created.

SQL> insert into c##marcov.t2_column_encrypted values ('this column is not encrypted', 'the secrets of marcov');

1 row created.

SQL> commit;

Commit complete.

The Oracle Wallet is closed.

SQL> administer key management set keystore close identified by "0racl30racle3";

keystore altered.

When the Oracle Wallet is closed I can able to query the non-encrypted column.

SQL> select text from c##marcov.t2_column_encrypted;

TEXT
----------------------------
this column is not encrypted

But when I try to query the encrypted column it fails:

SQL> select * from c##marcov.t2_column_encrypted;
select * from c##marcov.t2_column_encrypted
                        *
ERROR at line 1:
ORA-28365: wallet is not open

I need first to open the Oracle Wallet

SQL> administer key management set keystore open identified by "0racl30racle3";

keystore altered.

Now I can query again the encrypted column of my table.

SQL> select * from c##marcov.t2_column_encrypted;

TEXT                         TEXT_ENCRYPTED
---------------------------- ---------------------   
this column is not encrypted the secrets of marcov

That's all.

How to configure Transparent Data Encryption using a software keystore, the right way to call the old Oracle Wallet

Oracle uses an operating system file to contains authentication and signing credentials: this file, named ewallet.p12, is the so called Oracle wallet (now called more appropriately software/hardware keystore)

The transparent data encryption (TDE) feature, used when you encrypt an entire tablespace, columns of a table, safely export data or complete your backup, is strongly based on the creation of the Oracle wallet to store the master key of the database.
There are two kind of Oracle wallets: that one which you must manually open each time you restart a database instance to reenable encryption and decryption and that one automatically opened when an encryption operation is required.

The first is protected by a password specified when you create it and before accessing the keys contained you are responsible of its opening. The second is protected by a system-generated password and you don't need to open it manually.
Once an Oracle Wallet is open, it remains open until you shutdown the database or manually close it. 

As already stated here when talking about granting the minimum privilege, the Oracle Database 12c introduced the syskm administrative privilege: with this type of privilege you can perform every kind of key management operation without using the powerful sysdba privilege.
It also unified some already known commands under a set of key management statements (ADMINISTER KEY MANAGEMENT) that you can see in today's scenario.

To create a standard Oracle wallet and then add a master key to it you have to follow few basic steps:
1) Configure the sqlnet.ora file to define a file system location for the Software Keystore
2) Create the Software Keystore 
3) Opening a Software Keystore 
4) Setting the TDE Master Encryption Key in the Software Keystore 
5) Encrypt the Data (have a look at the next post) 

1) Configure the sqlnet.ora file 
Oracle should know where to find the Oracle Wallet so you have to define a directory accessible by the Oracle Software.
In the multitenant solution the Oracle Wallet location is valid for the CDB and every PDBs at the same time. Edit your sqlnet.ora file and use the following syntax to let the database know where the software keystore is located on file system.
Be sure that the directory exists to avoid the error "ORA-46633: creation of a password-based keystore failed":

[oracle@vsi08devpom admin]$ pwd
/opt/app/oracle/product/12.1.0/db_1/network/admin
[oracle@vsi08devpom admin]$ echo "ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/app/oracle/product/12.1.0/dbhome_1/admin/CDB001/wallet)))" >> sqlnet.ora 

[oracle@vsi08devpom admin]$ cat sqlnet.ora 
# sqlnet.ora Network Configuration File: /app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/app/oracle/product/12.1.0/dbhome_1/admin/CDB001/wallet)))

2) Create the Oracle Wallet 
It's possible to create the Oracle Wallet using the owm gui utility (as you can read on this post) or from sqlplus with a new set of key management statements (ADMINISTER KEY MANAGEMENT).
The steps to create an Oracle Wallet must be executed from the sqlplus command line with a user who has been granted the new SYSKM administrative privilege:
when in a multitenant environment you have to log in to the root container.

SQL> show con_name

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

To avoid the error "ORA-46633: creation of a password-based keystore failed", the directory you are going to specify in the create keystore statement must be already present.

SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/app/oracle/product/12.1.0/dbhome_1/admin/CDB001/missing_directory' IDENTIFIED BY "0racle0racle";
ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/app/oracle/product/12.1.0/dbhome_1/admin/CDB001/missing_directory' IDENTIFIED BY "0racle0racle"
*
ERROR at line 1:
ORA-46633: creation of a password-based keystore failed

To create the keystore under the path specified in the sqlnet.ora file use the following statement:

SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/app/oracle/product/12.1.0/dbhome_1/admin/CDB001/wallet' IDENTIFIED BY "0racle0racle";

keystore altered.

Querying the V$ENCRYPTION_WALLET view you can see the location, status and type of the wallet.

SQL> select * from v$encryption_wallet;

WRL_TYPE WRL_PARAMETER                                            STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
-------- -------------------------------------------------------- ------ ----------- --------- --------- ------
FILE     /app/oracle/product/12.1.0/dbhome_1/admin/CDB001/wallet  CLOSED UNKNOWN     SINGLE    UNDEFINED 0

3) Opening a Software Keystore 
To setup, configure and use encrypted tablespace or column the Oracle Wallet needs to be open.
The v$encryption_wallet view says the status of the wallet is closed so you need to open it using the following statement:

SQL> administer key management set keystore open identified by "0racle0racle";

keystore altered.

The status is now OPEN_NO_MASTER_KEY. This means that the wallet is open, but still a master key needs to be created.

SQL> select * from v$encryption_wallet;

WRL_TYPE WRL_PARAMETER                                            STATUS             WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
-------- -------------------------------------------------------- ------------------ ----------- --------- --------- ------
FILE     /app/oracle/product/12.1.0/dbhome_1/admin/CDB001/wallet  OPEN_NO_MASTER_KEY UNKNOWN     PASSWORD  UNDEFINED 0

4) Setting the TDE Master Encryption Key in the Software Keystore
You need to set a master key for the Oracle wallet used in the TDE activities on tables or tablespace.

SQL> ADMINISTER KEY MANAGEMENT SET KEY USING TAG 'tde_mek' IDENTIFIED BY "0racle0racle" WITH BACKUP USING 'tde_mek_backup';

keystore altered.

If you need to change the password of your Oracle Wallet because of your company's security guidelines, you must use the WITH BACKUP option: in this way you are forced to take a backup of your "old" wallet.

SQL> ADMINISTER KEY MANAGEMENT ALTER KEYSTORE PASSWORD IDENTIFIED BY "0racle0racle" set "0racl30racle3";
ADMINISTER KEY MANAGEMENT ALTER KEYSTORE PASSWORD IDENTIFIED BY "0racle0racle" set "0racl30racle3"
*
ERROR at line 1:
ORA-46631: keystore needs to be backed up

A change of the password doesn't prevent the normal use of every TDE operations: they continue to work as usual with the new password without interruptions. You need to provide the old password and the new password.

SQL> ADMINISTER KEY MANAGEMENT ALTER KEYSTORE PASSWORD IDENTIFIED BY "0racle0racle" set "0racl30racle3" WITH BACKUP USING 'tde_mek_backup_001';

keystore altered.

To see now how to encrypt you data, read this post.

That's all.

Steps to create, open and close an Oracle Wallet using Oracle Wallet Manager

It's possible to create the Oracle Wallet using the owm gui utility or from sqlplus with a new set of key management statements (ADMINISTER KEY MANAGEMENT).

Let's see first in this post how to proceed using the owm gui utility.
Using the Oracle Wallet Manager it's possible to create standard wallets (PKCS #12, Public-Key Cryptography Standards) on file system or
wallets (PKCS #11) used in conjunction of hardware security modules (HSM), tokens or smart cards.

From the command line type:

[oracle@vsi08devpom ~]$ owm &

The following screenshot appears:



















To create a new wallet from the Wallet menu select New. If the Oracle software detects you don't have a default directory for the wallet it will ask you to create one: you can always choose a custom directory. The default path of the wallet directory is: $ORACLE_HOME/owm/wallets/user_name (/app/oracle/product/12.1.0/dbhome_1/owm/wallets/oracle in my case).
 

In the next dialog box you are prompted to set a password, having a minimum of eight characters, containing alphabetic characters and numbers or special characters.


As you can see on the following alert a new empty wallet has been created. If you need to add also a certificate request select Yes, otherwise to simply return to the Oracle Wallet Manager main window select No as I have done.


From the Oracle Wallet Manager main window you can see the new wallet in the left pane. It's time to save the wallet: from the Wallet menu select "Save In System Default"

If the wallet is successfully saved the following message is displayed at the bottom of the window:

Only when you save the new wallet you can find it under the specified directory.

[oracle@localhost oracle]$ pwd
/app/oracle/product/12.1.0/dbhome_1/owm/wallets/oracle
[oracle@localhost oracle]$ ls
cwallet.sso  cwallet.sso.lck  ewallet.p12  ewallet.p12.lck

If you want to close the wallet from the graphical user interface of the Oracle Wallet Manager, start again it and from Wallet menu of the main window select Close.

If you want to open again the wallet, from Wallet menu of the main window select Open and then enter the path of the wallet. When prompted enter the right password.

That's all.

How to use the new SYSBACKUP privilege to perform backup and recovery tasks without accessing to table data

The SYSBACKUP is a new privilege introduced in the Oracle Database 12c release that allows a user to perform any backup or recovery operations.

It is heartily suggested to create a new user with the SYSBACKUP privilege granted and perform daily backup activities with that new user, instead of using directly the SYSBACKUP user created by default during the database installation: the SYSBACKUP user should even remain locked.

Before proceeding I will need to create a new user at the operating system level, but not belonging to the dba OS group: in this way, using the password file, I will able to assign the sysbackup privilege to a normal database user and forcing him to log in using a password.

Indeed if I try to log into the database when I'm already authorized at the OS level because I'm the oracle OS user, I am allowed to connect with the SYSBACKUP privilege (and of course as SYSDBA, SYSOPER, SYSDG and SYSKM) even with a wrong user and/or password.
Let's see few examples considering that when you are connecting AS SYSDBA the current schema is SYS (independently from the username you specify in the sqlplus connection) and the session USER is SYS; when you are connecting AS SYSBACKUP the current schema is again SYS, but the session user is identified as SYSBACKUP.

[oracle@localhost ~]$ id
uid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),54322(dba) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023
[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Wed May 7 06:12:19 2014

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> select sys_context('USERENV', 'CURRENT_SCHEMA') current_schema from dual;

CURRENT_SCHEMA
------------------
SYS

SQL> select sys_context('USERENV', 'SESSION_USER') session_user from dual;

SESSION_USER
------------------
SYS

SQL> exit

In the previous example I was able to connect as SYSDBA because the user was "already" verified by the operating system; when I try to connect using a normal database user, in this case specifying marcov and its right password, it happens the same thing that is I'm not connected as the specified user, but as SYS user

[oracle@localhost ~]$ sqlplus marcov/marcov as sysdba
SQL> select sys_context('USERENV', 'CURRENT_SCHEMA') current_schema, sys_context('USERENV', 'SESSION_USER') session_user from dual;

CURRENT_SCHEMA         SESSION_USER
-------------------- --------------------
SYS             SYS

What does it happens when I try to connect AS SYSDBA using a normal database user and a wrong password ?
Again I'm able to connect to the database as the SYS user.

[oracle@localhost ~]$ sqlplus marcov/m as sysdba
SQL> select sys_context('USERENV', 'CURRENT_SCHEMA') current_schema, sys_context('USERENV', 'SESSION_USER') session_user from dual;

CURRENT_SCHEMA         SESSION_USER
-------------------- --------------------
SYS             SYS

What does it happens when I try to connect AS SYSDBA using a non existing database user and a password ?
Again I'm able to connect to the database as the SYS user.
All this happens because I'm using the oracle user at the operating system level which belongs to the dba operating system group and so it's allowed
to estabilish a connection to the local database. There is a correspondence between the OS groups and the database system privileges: the dba OS group maps to the SYSDBA database system privilege (as previously seen), the oper OS group (when created and specified during the installation process) maps to the SYSOPER database system privilege, the oinstall OS group doesn't map to any database system privilege.

[oracle@localhost ~]$ sqlplus m/m as sysdba

SQL> select sys_context('USERENV', 'CURRENT_SCHEMA') current_schema, sys_context('USERENV', 'SESSION_USER') session_user from dual;

CURRENT_SCHEMA         SESSION_USER
-------------------- --------------------
SYS             SYS

So the first step to test a connection AS SYSBACKUP database system privilege is to avoid a connection using the oracle operating system user: I need to create another user at the operating system level without special group. I'm going to create marcov OS user:

[root@localhost home]# useradd marcov -p marcov
[root@localhost home]# id marcov
uid=54322(marcov) gid=54323(marcov) groups=54323(marcov)

Now you have to configure this account to let it connect to the database: I simply copied the TNS file located at
/app/oracle/product/12.1.0/dbhome_1/network/admin/tnsnames.ora, not accessible by the new user, under the default directory of marcov OS user (/home/marcov) and modified the /home/marcov/.bash_profile to add few environment variables to marcov's profile:

[root@localhost home]# cp /app/oracle/product/12.1.0/dbhome_1/network/admin/tnsnames.ora /home/marcov
[root@localhost home]# chown marcov.marcov /home/marcov/tnsnames.ora
[root@localhost home]# cat /home/marcov/.bash_profile
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs
TNS_ADMIN=/home/marcov
ORACLE_HOME=/app/oracle/product/12.1.0/dbhome_1
ORACLE_SID=CDB001
export TNS_ADMIN
export ORACLE_HOME
export ORACLE_SID

PATH=$PATH:$ORACLE_HOME/bin:$HOME/bin

export PATH

I've already a database schema named MARCOV. From the marcov OS user I'm now able to create a connection with the local database using that schema name and password specifying the tns entry of a pluggable database.

root@localhost ~]# su - marcov
[marcov@localhost ~]$ sqlplus marcov/marcov@PDB001
SQL> show user
USER is "MARCOV"

I'm not of course able to connect using the system privilege AS SYSBACKUP. If I try the error "ORA-01017: invalid username/password; logon denied" is raised.

[marcov@localhost ~]$ sqlplus marcov/marcov@PDB001 as sysbackup

SQL*Plus: Release 12.1.0.1.0 Production on Wed May 7 06:47:42 2014

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

ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name: 

I need to explicity grant the SYSBACKUP system privilege to the MARCOV database user:

[oracle@localhost ~]$ sqlplus / as sysdba
SQL> alter session set container=PDB001;

Session altered.

SQL> grant sysbackup to marcov;

Grant succeeded.

Once the GRANT command is assigned to MARCOV database user, you can see a new entry in the V$PWFILE_USERS view: the view says that the username MARCOV is into the orapwd file and can connect to the local database using the SYSBACKUP system privilege.

SQL> select * from v$pwfile_users;

USERNAME               SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS                   TRUE  TRUE  FALSE FALSE FALSE FALSE        0
MARCOV                   FALSE FALSE FALSE TRUE  FALSE FALSE        3

After having received the system privilege, MARCOV database user is now able to connect AS SYSBACKUP. Have a look at the CURRENT_SCHEMA and at the SESSION_USER values.

[marcov@localhost ~]$ sqlplus marcov/marcov@PDB001 as sysbackup
SQL> col current_schema format a10
SQL> col session_user format a20
SQL> select sys_context('USERENV', 'CURRENT_SCHEMA') current_schema, sys_context('USERENV', 'SESSION_USER') session_user from dual;

CURRENT_SC SESSION_USER
---------- --------------------
SYS       SYSBACKUP

The main goal of the system privilege SYSBACKUP is to implement the so called separation of duties: you can grant SYSBACKUP privilege to a user allowed only to perform backup or recovery operation using the RMAN command line, but without possibility to see or access the data of the database.
Let's first see what are the default system privileges and roles associated with the SYSBACKUP privilege.

SQL> show user;
USER is "SYSBACKUP"
SQL> set pages 999
SQL> col grantee format a25
SQL> col granted_role format a25
SQL> select * from dba_sys_privs where grantee = 'SYSBACKUP';

GRANTEE           PRIVILEGE                   ADMIN_OPTION COMMON
------------------------- ---------------------------------------- ------------ ------
SYSBACKUP          ALTER SYSTEM                   NO           YES
SYSBACKUP          AUDIT ANY                   NO           YES
SYSBACKUP          SELECT ANY TRANSACTION           NO           YES
SYSBACKUP          SELECT ANY DICTIONARY            NO           YES
SYSBACKUP          RESUMABLE                   NO           YES
SYSBACKUP          CREATE ANY DIRECTORY               NO           YES
SYSBACKUP          UNLIMITED TABLESPACE               NO           YES
SYSBACKUP          ALTER TABLESPACE               NO           YES
SYSBACKUP          ALTER SESSION                NO           YES
SYSBACKUP          ALTER DATABASE               NO           YES
SYSBACKUP          CREATE ANY TABLE               NO           YES
SYSBACKUP          DROP TABLESPACE               NO           YES
SYSBACKUP          CREATE ANY CLUSTER               NO           YES

13 rows selected.

SQL> select * from dba_role_privs where grantee = 'SYSBACKUP';

GRANTEE           GRANTED_ROLE            ADMIN_OPTION DEFAULT_ROLE COMMON
------------------------- ------------------------- ------------ ------------ ------
SYSBACKUP          SELECT_CATALOG_ROLE        NO             YES          YES

The column COMMON (as states in the Oracle documentation: http://docs.oracle.com/cd/E16655_01/server.121/e17615/refrn23274.htm#REFRN23274)
"indicates how the grant was made. Possible values: YES if the role was granted commonly (CONTAINER=ALL was used); NO if the role was granted locally (CONTAINER=ALL was not used)"

When you are connected using MARCOV database user which has the SYSBACKUP system privilege you are able to use the DESCRIBE
command on every database objects, but you are not allowed to have a look at the data contained by the object.
Here is an example of what you receive (ORA-01031: insufficient privileges) when you try to see the data within the object.

SQL> show user;
USER is "SYSBACKUP"
SQL> select table_name from dba_tables where owner = 'MARCOV';

TABLE_NAME
------------------------
T2
T1

SQL> select * from MARCOV.T1;
select * from MARCOV.T1
                     *
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> desc MARCOV.T1;
 Name                       Null?    Type
 ----------------------------------------- -------- ----------------------------
 A                            NUMBER

If your database user with SYSBACKUP privilege needs to access some data, it should directly receive SELECT grant on specific tables.

Once you have a database user with SYSBACKUP privilege you can create a RMAN connection to manage your backups.

[marcov@localhost ~]$ rman

Recovery Manager: Release 12.1.0.1.0 - Production on Wed May 7 13:46:50 2014

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

RMAN> connect target 'marcov/marcov@PDB001 as sysbackup'

connected to target database: CDB001 (DBID=4134986109)

RMAN> select sys_context('USERENV', 'CURRENT_SCHEMA') current_schema, sys_context('USERENV', 'SESSION_USER') session_user from dual;

using target database control file instead of recovery catalog


CURRENT_SCHEMA     SESSION_USER
------------------ ------------------
SYS                SYSBACKUP

From the RMAN session you can for example take a backup of the current controlfile or have a list of available backups.

RMAN> list backup;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
51      Full    209.94M    DISK        00:00:54     14-APR-14      
        BP Key: 51   Status: AVAILABLE  Compressed: YES  Tag: TAG20140414T151315
        Piece Name: /app/oracle/fast_recovery_area/CDB001/E1F26215682E1142E045000000000001/backupset/2014_04_14/o1_mf_nnndf_TAG20140414T151315_9nqqw3np_.bkp
  List of Datafiles in backup set 51
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  7       Full 3389939    14-APR-14 /app/oracle/oradata/CDB001/PDB001/system01.dbf
  8       Full 3389939    14-APR-14 /app/oracle/oradata/CDB001/PDB001/sysaux01.dbf
  9       Full 3389939    14-APR-14 /app/oracle/oradata/CDB001/PDB001/PDB001_users01.dbf

RMAN> backup current controlfile;

Starting backup at 07-MAY-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=72 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 07-MAY-14
channel ORA_DISK_1: finished piece 1 at 07-MAY-14
piece handle=/app/oracle/fast_recovery_area/CDB001/E1F26215682E1142E045000000000001/backupset/2014_05_07/o1_mf_ncnnf_TAG20140507T135023_9pn7j3y4_.bkp tag=TAG20140507T135023 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04
Finished backup at 07-MAY-14

Starting Control File and SPFILE Autobackup at 07-MAY-14
piece handle=/app/oracle/fast_recovery_area/CDB001/autobackup/2014_05_07/o1_mf_s_846942632_9pn7j9dy_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 07-MAY-14

RMAN> list backup;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
51      Full    209.94M    DISK        00:00:54     14-APR-14      
        BP Key: 51   Status: AVAILABLE  Compressed: YES  Tag: TAG20140414T151315
        Piece Name: /app/oracle/fast_recovery_area/CDB001/E1F26215682E1142E045000000000001/backupset/2014_04_14/o1_mf_nnndf_TAG20140414T151315_9nqqw3np_.bkp
  List of Datafiles in backup set 51
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  7       Full 3389939    14-APR-14 /app/oracle/oradata/CDB001/PDB001/system01.dbf
  8       Full 3389939    14-APR-14 /app/oracle/oradata/CDB001/PDB001/sysaux01.dbf
  9       Full 3389939    14-APR-14 /app/oracle/oradata/CDB001/PDB001/PDB001_users01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
56      Full    1.13M      DISK        00:00:05     07-MAY-14      
        BP Key: 56   Status: AVAILABLE  Compressed: YES  Tag: TAG20140507T135023
        Piece Name: /app/oracle/fast_recovery_area/CDB001/E1F26215682E1142E045000000000001/backupset/2014_05_07/o1_mf_ncnnf_TAG20140507T135023_9pn7j3y4_.bkp
  Control File Included: Ckp SCN: 3418486      Ckp time: 07-MAY-14

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
57      Full    17.20M     DISK        00:00:02     07-MAY-14      
        BP Key: 57   Status: AVAILABLE  Compressed: NO  Tag: TAG20140507T135032
        Piece Name: /app/oracle/fast_recovery_area/CDB001/autobackup/2014_05_07/o1_mf_s_846942632_9pn7j9dy_.bkp
  SPFILE Included: Modification time: 07-MAY-14
  SPFILE db_unique_name: CDB001
  Control File Included: Ckp SCN: 3418494      Ckp time: 07-MAY-14

When you want to connect to a specific pluggable database using the AS SYSBACKUP syntax directly from the Linux command line you should quote your command with single and double quotes.

[marcov@localhost ~]$ rman target '"marcov/marcov@PDB001 as sysbackup"'

Recovery Manager: Release 12.1.0.1.0 - Production on Wed May 7 13:52:19 2014

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

connected to target database: CDB001 (DBID=4134986109)

RMAN>

As latest consideration the SYSBACKUP user accounts cannot be dropped.

[oracle@localhost ~]$ sqlplus / as sysdba
SQL> drop user SYSBACKUP;
drop user SYSBACKUP
*
ERROR at line 1:
ORA-28050: specified user or role cannot be dropped

You have no reason now to implement the right separation of duties that best fit for your requirements.

That's all.

How to rename everything on Oracle Database (redolog members, tablespaces, schema objects, PDBs, partitions, constraints)

Few days ago I was wondering how many RENAME clauses are available on Oracle Database. I have summarized them here:

1. Renaming redolog members;
2. Renaming tablespaces;
3. Renaming datafiles of a single offline tablespace;
4. Renaming constraints;
5. Renaming schema objects (tables, views, sequences, private synonyms, indexes, triggers);
6. Renaming table columns;
7. Renaming table and index partitions (subpartitions);
8. Restoring and Renaming table from the Recycle Bin;
9. Changing the domain in a global database name for a CDB;
10. Renaming a PDB;

Let's review them with few examples.

• Renaming redolog members:

To complete this requirement you have to shutdown the database, move the redo log files to the new destination, startup the database in mount mode, rename the log members and then open the database.

[oracle@localhost admin]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Wed Mar 12 16:16:04 2014

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> set lines 180                        
SQL> col member format a50
SQL> select GROUP#, MEMBER, CON_ID from V$LOGFILE;

    GROUP# MEMBER        CON_ID
---------- -------------------------------------------------- ----------
  1 /app/oracle/oradata/CDB001/redo01.log         0
  2 /app/oracle/oradata/CDB001/redo02.log         0
  3 /app/oracle/oradata/CDB001/redo03.log         0

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> host mv /app/oracle/oradata/CDB001/redo01.log /app/oracle/oradata/CDB001/redo01_renamed.log

SQL> host mv /app/oracle/oradata/CDB001/redo02.log /app/oracle/oradata/CDB001/redo02_renamed.log

SQL> host mv /app/oracle/oradata/CDB001/redo03.log /app/oracle/oradata/CDB001/redo03_renamed.log

SQL> host ls -l /app/oracle/oradata/CDB001/*log
-rw-r-----. 1 oracle oinstall 52429312 Feb  7 15:59 /app/oracle/oradata/CDB001/redo01_renamed.log
-rw-r-----. 1 oracle oinstall 52429312 Mar  3 13:00 /app/oracle/oradata/CDB001/redo02_renamed.log
-rw-r-----. 1 oracle oinstall 52429312 Mar 12 16:17 /app/oracle/oradata/CDB001/redo03_renamed.log

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.
SQL> alter database rename file '/app/oracle/oradata/CDB001/redo01.log','/app/oracle/oradata/CDB001/redo02.log','/app/oracle/oradata/CDB001/redo03.log'
  2  to '/app/oracle/oradata/CDB001/redo01_renamed.log','/app/oracle/oradata/CDB001/redo02_renamed.log','/app/oracle/oradata/CDB001/redo03_renamed.log';

Database altered.

SQL> alter database open;

Database altered.

SQL> select GROUP#, MEMBER, CON_ID from V$LOGFILE;

    GROUP# MEMBER        CON_ID
---------- -------------------------------------------------- ----------
  1 /app/oracle/oradata/CDB001/redo01_renamed.log        0
  2 /app/oracle/oradata/CDB001/redo02_renamed.log        0
  3 /app/oracle/oradata/CDB001/redo03_renamed.log        0

• Renaming tablespaces:

You can rename a permanent or temporary tablespace using the ALTER TABLESPACE RENAME statement. Just remember that you cannot rename SYSTEM or SYSAUX tablespace:

SQL> select a.con_id, a.name, b.name from v$containers a, v$tablespace b where a.con_id = b.con_id order by 1,3;

    CON_ID NAME      NAME
---------- ------------------------------ ------------------------------
  1 CDB$ROOT     SYSAUX
  1 CDB$ROOT     SYSTEM
  1 CDB$ROOT     TEMP
  1 CDB$ROOT     UNDOTBS1
  1 CDB$ROOT     USERS
  2 PDB$SEED     SYSAUX
  2 PDB$SEED     SYSTEM
  2 PDB$SEED     TEMP
  3 PDB001     SYSAUX
  3 PDB001     SYSTEM
  3 PDB001     TEMP
  3 PDB001     USERS
  4 PDB002     SYSAUX
  4 PDB002     SYSTEM
  4 PDB002     TEMP
  4 PDB002     USERS
  5 PDB003     SYSAUX
  5 PDB003     SYSTEM
  5 PDB003     TEMP
  5 PDB003     USERS

20 rows selected.

SQL> show con_name    

CON_NAME
------------------------------
CDB$ROOT
SQL> alter tablespace USERS rename to USERS_CDBROOT;

Tablespace altered.

SQL> select a.con_id, a.name, b.name from v$containers a, v$tablespace b where a.con_id = b.con_id order by 1,3;

    CON_ID NAME      NAME
---------- ------------------------------ ------------------------------
  1 CDB$ROOT     SYSAUX
  1 CDB$ROOT     SYSTEM
  1 CDB$ROOT     TEMP
  1 CDB$ROOT     UNDOTBS1
  1 CDB$ROOT     USERS_CDBROOT
  2 PDB$SEED     SYSAUX
  2 PDB$SEED     SYSTEM
  2 PDB$SEED     TEMP
  3 PDB001     SYSAUX
  3 PDB001     SYSTEM
  3 PDB001     TEMP
  3 PDB001     USERS
  4 PDB002     SYSAUX
  4 PDB002     SYSTEM
  4 PDB002     TEMP
  4 PDB002     USERS
  5 PDB003     SYSAUX
  5 PDB003     SYSTEM
  5 PDB003     TEMP
  5 PDB003     USERS

20 rows selected.

SQL> alter session set container=PDB001;

Session altered.

SQL> alter pluggable database PDB001 open;

Pluggable database altered.

SQL> alter tablespace USERS rename to USERS_PDB001;

Tablespace altered.

SQL> select a.con_id, a.name, b.name from v$containers a, v$tablespace b where a.con_id = b.con_id order by 1,3;

    CON_ID NAME      NAME
---------- ------------------------------ ------------------------------
  3 PDB001     SYSAUX
  3 PDB001     SYSTEM
  3 PDB001     TEMP
  3 PDB001     USERS_PDB001

• Renaming datafiles of a single offline tablespace:

While the database is open, put the tablespace offline, rename the datafile at the operating system level, rename the datafile at the database level and finally take the tablespace online again.

SQL> col file_name format a50                                    
SQL> select file_name from dba_data_files where tablespace_name = 'USERS';

FILE_NAME
--------------------------------------------------
/app/oracle/oradata/CDB001/users01.dbf

SQL> alter tablespace USERS offline;

Tablespace altered.

SQL> host mv /app/oracle/oradata/CDB001/users01.dbf /app/oracle/oradata/CDB001/users01_renamed.dbf

SQL> host ls -l /app/oracle/oradata/CDB001/users01*   
-rw-r-----. 1 oracle oinstall 5251072 Mar 12 16:36 /app/oracle/oradata/CDB001/users01_renamed.dbf

SQL> alter tablespace USERS rename datafile '/app/oracle/oradata/CDB001/users01.dbf'
  2  to '/app/oracle/oradata/CDB001/users01_renamed.dbf';

Tablespace altered.

SQL> alter tablespace USERS online;

Tablespace altered.

SQL> select file_name from dba_data_files where tablespace_name = 'USERS';

FILE_NAME
--------------------------------------------------
/app/oracle/oradata/CDB001/users01_renamed.dbf

To rename datafiles included in multiple tablespaces follow the redo log file renaming procedure described above (alter database rename file ...).

• Renaming constraints:

You can rename any constraint defined on a table

SQL> show user;
USER is "MARCOV"

SQL> select dbms_metadata.get_ddl('TABLE', 'T1') from dual;

DBMS_METADATA.GET_DDL('TABLE','T1')
---------------------------------------------------------

  CREATE TABLE "MARCOV"."T1"
   ( "A" NUMBER
   ) SEGMENT CREATION IMMEDIATE


SQL> alter table T1 add constraint t1_mypk primary key (a);

Table altered.

SQL> select dbms_metadata.get_ddl('TABLE', 'T1') from dual;

DBMS_METADATA.GET_DDL('TABLE','T1')
---------------------------------------------------------

  CREATE TABLE "MARCOV"."T1"
   ( "A" NUMBER,
  CONSTRAINT "T1_MYPK" PRIMARY KEY ("A")


SQL> alter table T1 rename constraint T1_MYPK to T1_PK; 

Table altered.

SQL> select dbms_metadata.get_ddl('TABLE', 'T1') from dual;

DBMS_METADATA.GET_DDL('TABLE','T1')
---------------------------------------------------------

  CREATE TABLE "MARCOV"."T1"
   ( "A" NUMBER,
  CONSTRAINT "T1_PK" PRIMARY KEY ("A")

• Renaming schema objects (tables, views, sequences, private synonyms, indexes, triggers):

You can rename tables, views, sequences and private synonym using the rename statement.

SQL> show user;
USER is "MARCOV"

SQL> create sequence T1_MYSEQ;

Sequence created.

SQL> rename T1_MYSEQ to T1_S001;

Table renamed.

SQL> create table mysecondtable (a number);

Table created.

SQL> rename mysecondtable to T2;

Table renamed.

SQL> select dbms_metadata.get_ddl('TABLE', 'T2') from dual;

DBMS_METADATA.GET_DDL('TABLE','T2')
---------------------------------------------------------

  CREATE TABLE "MARCOV"."T2"
   ( "A" NUMBER
   ) SEGMENT CREATION DEFERRED

SQL> create or replace view T1_MYVIEW as select * from T1 where a <= 10;

View created.

SQL> rename T1_MYVIEW to T1_VIEW;

Table renamed.

SQL> select dbms_metadata.get_ddl('VIEW', 'T1_VIEW') from dual;

DBMS_METADATA.GET_DDL('VIEW','T1_VIEW')
---------------------------------------------------------

  CREATE OR REPLACE FORCE EDITIONABLE VIEW "MARCOV"."T1_VIEW" ("A") AS
  select "A" from T1 where a <= 10

SQL> create public synonym pub_t1 for t1;

Synonym created.

SQL> create synonym priv_t1 for t1;

Synonym created.

As you can see it is not possible to rename public synonymns, just the privates.

SQL> rename pub_t1 to public_t1;
rename pub_t1 to public_t1
*
ERROR at line 1:
ORA-04043: object PUB_T1 does not exist


SQL> rename priv_t1 to private_t1;

Table renamed.

Synonym of a renamed object returns instead an error when used:

SQL> select count(*) from private_t1;

  COUNT(*)
----------
  1

SQL> rename t1 to t1_renamed;

Table renamed.

SQL> select count(*) from private_t1;
select count(*) from private_t1
                     *
ERROR at line 1:
ORA-00980: synonym translation is no longer valid


SQL> rename t1_renamed to t1;

Table renamed.

SQL> select count(*) from private_t1;

  COUNT(*)
----------
  1

To rename schema objects such as indexes and triggers you can use the ALTER ... RENAME statement

SQL> show con_name;

CON_NAME
------------------------------
PDB001
SQL> show user
USER is "SYS"
SQL> select index_name from dba_indexes where table_name = 'T1';

INDEX_NAME
----------------------------------------
T1_MYPK

SQL> alter index MARCOV.T1_MYPK rename to T1_INDEX_PK;

Index altered.

SQL> create or replace trigger marcov.t1_mytrigger 
  2  before insert
  3  on marcov.t1
  4  for each row
  5  declare
  6  i number;
  7  begin 
  8  i := 0;
  9  end;
 10  /

Trigger created.

SQL> alter trigger marcov.t1_mytrigger rename to t1_trigger;

Trigger altered.

SQL> select owner, trigger_name from dba_triggers where trigger_name = 'T1_TRIGGER';

OWNER       TRIGGER_NAME
-------------------- ----------------------------------------
MARCOV       T1_TRIGGER

• Renaming table columns:

It's possible to rename existing columns of a table using the ALTER TABLE ... RENAME COLUMN statement

SQL> alter table t1 rename column a to b;

Table altered.

• Renaming table and index partitions (subpartitions):

The same RENAME TO statement could be applied to table or index partitions as in the following examples:

SQL> alter table t1 add (a number);

Table altered.

SQL> create index T1_index_partitioned on T1 (a)
  2  global partition by range (a)
  3  (partition p1 values less than (10),
  4  partition p2 values less than (100),
  5  partition p3 values less than (maxvalue));

Index created.

SQL> alter index T1_index_partitioned rename partition p3 to pmax;

Index altered.


SQL> drop table t2 purge;

Table dropped.

SQL> create table T2 (a number, quarter date) partition by range (quarter) 
  2  (partition Q1_2012 values less than (to_date('01/04/2012','DD/MM/YYYY')),
  3  partition Q2_2012 values less than (to_date('01/07/2012','DD/MM/YYYY')),
  4  partition Q3_2012 values less than (to_date('01/10/2012','DD/MM/YYYY')),
  5  partition Q4_2012 values less than (to_date('01/01/2013','DD/MM/YYYY')),
  6  partition Q1_2013 values less than (to_date('01/04/2013','DD/MM/YYYY')),
  7  partition Q2_2013 values less than (to_date('01/07/2013','DD/MM/YYYY')),
  8  partition Q3_2013 values less than (to_date('01/10/2013','DD/MM/YYYY')),
  9  partition Q4_2013 values less than (to_date('01/01/2014','DD/MM/YYYY')),
 10  partition Q1_2014 values less than (to_date('01/04/2014','DD/MM/YYYY')),
 11  partition Q2_2014 values less than (to_date('01/07/2014','DD/MM/YYYY')),
 12  partition Q3_2014 values less than (to_date('01/10/2014','DD/MM/YYYY')),
 13  partition Q4_2014 values less than (maxvalue));

Table created.

SQL> alter table t2 rename partition Q4_2014 to Q_MAX;

Table altered.

• Restoring and Renaming table from the Recycle Bin:

You have a dropped table, it is still available in the recycle bin and you want to recover it using the FLASHBACK TABLE ... TO BEFORE DROP statement. With the clause RENAME TO you can rename the original table name and assign a new one during the recovery process.

SQL> show user
USER is "MARCOV"
SQL> select * from tab;

no rows selected

SQL> create table T1 (a number);

Table created.

SQL> drop table t1;

Table dropped.

SQL> create table T1 (a number);

Table created.

SQL> select * from tab;

TNAME      TABTYPE  CLUSTERID
---------------------------------------- ------- ----------
BIN$9IBy6OCMQ0zgRQAAAAAAAQ==$0   TABLE
T1      TABLE

SQL> show recyclebin
ORIGINAL NAME  RECYCLEBIN NAME  OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
T1   BIN$9IBy6OCMQ0zgRQAAAAAAAQ==$0 TABLE      2014-03-13:17:32:48
SQL> flashback table "BIN$9IBy6OCMQ0zgRQAAAAAAAQ==$0" to before drop rename to T2;

Flashback complete.

SQL> select * from tab;

TNAME      TABTYPE  CLUSTERID
---------------------------------------- ------- ----------
T1      TABLE
T2      TABLE

SQL> show recyclebin
SQL>

An equivalent statement to recover and rename the same table could be: flashback table T1 to before drop rename to T2; 
Don't forget that double quotes are required when dealing with system generated names such as BIN$9IBy6OCMQ0zgRQAAAAAAAQ==$0.
Dependent objects of a restored table from the recycle bin such as indexes mantains the system generated names, but you can rename them using the ALTER INDEX ... RENAME TO statement described above in the "Renaming Schema Objects" section.

• Changing the domain in a global database name for a CDB:

It's possible to modify the domain of a global database name using the ALTER DATABASE RENAME GLOBAL_NAME TO database_name.network_domain_name statement

SQL> select * from global_name;

GLOBAL_NAME
------------------------------------------------------
CDB001.MARCOV.COM

SQL> alter database rename global_name to CDB001.IT.MARCOV.COM;

Database altered.

SQL> select * from global_name;

GLOBAL_NAME
------------------------------------------------------
CDB001.IT.MARCOV.COM

Also the domain of each PDBs is affected when the previous statement is applied to the domain name of a CDB.

• Renaming a PDB:

For a pluggable database you cannot modify the domain name directly. When you only want to change the name of a specific PDB you can use the ALTER PLUGGABLE DATABASE RENAME GLOBAL_NAME TO statement. The pluggable database must be open in restricted mode. 

SQL> alter session set container=PDB001;

Session altered.

SQL> select * from global_name;
select * from global_name
              *
ERROR at line 1:
ORA-01219: database or pluggable database not open: queries allowed on fixed
tables or views only

SQL> alter pluggable database PDB001 open;

Pluggable database altered.

SQL> select * from global_name;

GLOBAL_NAME
------------------------------------------------------
PDB001.IT.MARCOV.COM

SQL> alter pluggable database rename global_name to PDB001.ROME.IT.MARCOV.COM;
alter pluggable database rename global_name to PDB001.ROME.IT.MARCOV.COM
                                               *
ERROR at line 1:
ORA-65045: pluggable database not in a restricted mode


SQL> alter pluggable database PDB001 close;

Pluggable database altered.

SQL> alter pluggable database PDB001 open restricted;

Pluggable database altered.

SQL> select * from global_name;

GLOBAL_NAME
------------------------------------------------------
PDB001.IT.MARCOV.COM

SQL> alter pluggable database rename global_name to PDB001.ROME.IT.MARCOV.COM;
alter pluggable database rename global_name to PDB001.ROME.IT.MARCOV.COM
                                               *
ERROR at line 1:
ORA-65042: name is already used by an existing container


SQL> alter pluggable database rename global_name to PDB001_ROME.IT.MARCOV.COM;

Pluggable database altered.

SQL> select name, open_mode from V$PDBS;

NAME          OPEN_MODE
------------------------------ ----------
PDB001_ROME         READ WRITE

SQL> select * from global_name;

GLOBAL_NAME
------------------------------------------------------
PDB001_ROME.IT.MARCOV.COM

That's all.

RMAN and SQL statement enhancements in Oracle Database 12c

One of the most useful enhancement Oracle has introduced within the release 12c in RMAN is the possibility to execute more SQL statements.

Of course RMAN is primarily used for backup and recovery operations, but sometimes I need to run SQL commands: so in 11g or lower I have to open another terminal/tab or quit the current RMAN session and start a new one with sqlplus.

In Oracle Database 12c instead most of the sql statements could be executed while still connected to the RMAN session.

Let's start with few examples.

In the first example I need to put offline a specific datafile because it should be recovered.
On Oracle Database 11g I have the following information.

[oracle@localhost ~]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Wed Mar 5 06:44:03 2014

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

connected to target database: ORCL (DBID=1229390655)

RMAN> report schema;

Report of database schema for database with db_unique_name ORCL

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    911      SYSTEM               ***     /home/oracle/app/oracle/oradata/orcl/system01.dbf
2    1105     SYSAUX               ***     /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
3    475      UNDOTBS1             ***     /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
4    225      USERS                ***     /home/oracle/app/oracle/oradata/orcl/users01.dbf
5    82       EXAMPLE              ***     /home/oracle/app/oracle/oradata/orcl/example01.dbf
6    7        APEX                 ***     /home/oracle/app/oracle/oradata/orcl/APEX.dbf
7    1        READ_ONLY            ***     /home/oracle/app/oracle/oradata/orcl/read_only01.dbf
8    1        ZZZ                  ***     /home/oracle/app/oracle/oradata/orcl/ZZZ01.dbf
9    1        EXAMPLE              ***     /home/oracle/app/oracle/oradata/orcl/example02.dbf
10   1        APEX                 ***     /home/oracle/app/oracle/oradata/orcl/APEX02.dbf
11   6        MARCOV               ***     /home/oracle/app/oracle/oradata/orcl/marcov01.dbf
12   1        TEST                 ***     /home/oracle/app/oracle/oradata/orcl/test01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767       /home/oracle/app/oracle/oradata/orcl/temp01.dbf
2    20       TEMP                 50          /home/oracle/app/oracle/oradata/orcl/temp02.dbf

To put my datafile offline on RMAN 11g I need to execute the "alter database" SQL command.
When I try to write it resting upon my memories I just start typing: sql 'alter database datafile ...'
Then I remember I have to enclose the entire SQL statement in double quotes because I need to specify single quotes for the filename. So as soon as I'm ready to press the return key I have to delete every character already written with the backspace key.
After I have completely rewritten the previous sql 'alter database datafile ...' statement, I can execute the new correct (?) one.

RMAN> sql "alter database datafile '/home/oracle/app/oracle/oradata/orcl/marcov01.dbf' offline";

sql statement: alter database datafile '/home/oracle/app/oracle/oradata/orcl/marcov01.dbf' offline
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of sql command on default channel at 03/05/2014 06:43:38
RMAN-10015: error compiling PL/SQL program

Another typing error ! When dealing with single quotes I usually forget to duplicate them. The right syntax is finally available.
Just too many double and single quotes for me.

RMAN> sql "alter database datafile ''/home/oracle/app/oracle/oradata/orcl/marcov01.dbf'' offline";

sql statement: alter database datafile ''/home/oracle/app/oracle/oradata/orcl/marcov01.dbf'' offline

RMAN> recover datafile 11;

Starting recover at 05-03-2014
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=48 device type=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 05-03-2014

The same syntax needs to be used when putting the datafile online again.

RMAN> sql "alter database datafile ''/home/oracle/app/oracle/oradata/orcl/marcov01.dbf'' online";

sql statement: alter database datafile ''/home/oracle/app/oracle/oradata/orcl/marcov01.dbf'' online

Let's reproduce the same steps on Oracle Database 12c using the available RMAN enhancements.

[oracle@vsi08devpom ~]$ rman target /

Recovery Manager: Release 12.1.0.1.0 - Production on Wed Mar 5 15:51:18 2014

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

connected to target database: CDB001 (DBID=4134963396)

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name CDB001

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    790      SYSTEM               ***     /opt/app/oracle/oradata/CDB001/system01.dbf
3    2300     SYSAUX               ***     /opt/app/oracle/oradata/CDB001/sysaux01.dbf
4    150      UNDOTBS1             ***     /opt/app/oracle/oradata/CDB001/undotbs01.dbf
5    250      PDB$SEED:SYSTEM      ***     /opt/app/oracle/oradata/CDB001/pdbseed/system01.dbf
6    5        USERS                ***     /opt/app/oracle/oradata/CDB001/users01.dbf
7    590      PDB$SEED:SYSAUX      ***     /opt/app/oracle/oradata/CDB001/pdbseed/sysaux01.dbf
8    270      PDB0101:SYSTEM       ***     /opt/app/oracle/oradata/CDB001/PDB0101/system01.dbf
9    650      PDB0101:SYSAUX       ***     /opt/app/oracle/oradata/CDB001/PDB0101/sysaux01.dbf
10   5        PDB0101:USERS        ***     /opt/app/oracle/oradata/CDB001/PDB0101/PDB0101_users01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    177      TEMP                 32767       /opt/app/oracle/oradata/CDB001/temp01.dbf
2    20       PDB$SEED:TEMP        32767       /opt/app/oracle/oradata/CDB001/pdbseed/pdbseed_temp01.dbf
3    20       PDB0101:TEMP         32767       /opt/app/oracle/oradata/CDB001/PDB0101/temp01.dbf

When I need to put offline a datafile in RMAN 12c I can use the common SQL syntax without starting the command with the keyword sql or the necessity to include double or single quotes. It's the same SQL command I use on a sqlplus session.

RMAN> alter database datafile '/opt/app/oracle/oradata/CDB001/users01.dbf' offline;

Statement processed

RMAN> recover datafile 6;

Starting recover at 05-03-2014
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=278 device type=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 05-03-2014

The same consideration is valid when the datafile needs to be put online again. I don't need to prefix the "alter database datafile ..." command with the keyword sql and enclose the command with quotes.

RMAN> alter database datafile '/opt/app/oracle/oradata/CDB001/users01.dbf' online;

Statement processed

What about select statement executed from the 11g RMAN client ? SQL commands that return data such as select statement are not useful in 11g because no data is simply returned.

RMAN> sql 'select name, open_mode from v$database';

sql statement: select name, open_mode from v$database

On Oracle Database 12c I can execute, while connected to a RMAN session, the same select statement using the usual SQL syntax (again without prefixing the sql keyword and single quotes) and even obtain a result set.

RMAN> select name, open_mode from v$database;

NAME      OPEN_MODE           
--------- --------------------
CDB001    READ WRITE

What does it happen instead if I execute sql commands such as describe ? On Oracle Database 11g and lower I can not use it at all, because RMAN throws a parse error.

RMAN> sql 'desc v$database';

sql statement: desc dual
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of sql command on default channel at 03/05/2014 07:30:04
RMAN-11003: failure during parse/execution of SQL statement: desc dual
ORA-00900: invalid SQL statement

On RMAN 12c it is instead possible to execute that kind of command.

RMAN> desc dual;

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DUMMY                                              VARCHAR2(1)

There are many other SQL commands that can be executed from the 12c RMAN client.
Here is an example of different SQL commands successfully executed within a RMAN session:

RMAN> create table test (a number);

Statement processed

RMAN> select count(*) from test;

  COUNT(*)
----------
         0

RMAN> insert into test values (1);

Statement processed

RMAN> select count(*) from test;

  COUNT(*)
----------
         1

RMAN> rollback;

Statement processed

RMAN> select count(*) from test;

  COUNT(*)
----------
         0

RMAN> insert into test values (1);

Statement processed

RMAN> commit;

Statement processed

So now I have a committed row and I want to delete it.
Can I use the delete command in RMAN ? Is Oracle able to distinguish the SQL "delete from ..." command from the RMAN "delete backup ..." command ?
Lets' see if RMAN throws some errors.

RMAN> delete from test;

Statement processed

RMAN> select count(*) from test;

  COUNT(*)
----------
         0

As you can see Oracle recognizes the right meaning of the delete command using the from clause in the case of SQL "delete from ..." command.
When backup is specified Oracle instead looks for the cancellation of a backup.

RMAN> list backup summary;

List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
38      B  F  A DISK        20-FEB-14       1       1       YES        TAG20140220T163840
39      B  F  A DISK        20-FEB-14       1       1       YES        TAG20140220T163840
40      B  F  A DISK        20-FEB-14       1       1       YES        TAG20140220T163840
41      B  F  A DISK        20-FEB-14       1       1       YES        TAG20140220T163840
42      B  F  A DISK        20-FEB-14       1       1       YES        TAG20140220T163840
43      B  F  A DISK        20-FEB-14       1       1       NO         TAG20140220T164653
44      B  F  A DISK        03-MAR-14       1       1       YES        PDB001_FULL_20140303
45      B  F  A DISK        03-MAR-14       1       1       NO         TAG20140303T153904

RMAN> delete backup tag 'TAG20140220T163840';

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=85 device type=DISK

List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
38      38      1   1   AVAILABLE   DISK        /app/oracle/fast_recovery_area/CDB001/backupset/2014_02_20/o1_mf_nnndf_TAG20140220T163840_9jd8d3v9_.bkp
39      39      1   1   AVAILABLE   DISK        /app/oracle/fast_recovery_area/CDB001/E1F26215682E1142E045000000000001/backupset/2014_02_20/o1_mf_nnndf_TAG20140220T163840_9jd8l78o_.bkp
40      40      1   1   AVAILABLE   DISK        /app/oracle/fast_recovery_area/CDB001/E1F329ECE0F411E6E045000000000001/backupset/2014_02_20/o1_mf_nnndf_TAG20140220T163840_9jd8n97w_.bkp
41      41      1   1   AVAILABLE   DISK        /app/oracle/fast_recovery_area/CDB001/E2B9BE56B8B936CEE045000000000001/backupset/2014_02_20/o1_mf_nnndf_TAG20140220T163840_9jd8q9jq_.bkp
42      42      1   1   AVAILABLE   DISK        /app/oracle/fast_recovery_area/CDB001/E19363E52C005C9AE045000000000001/backupset/2014_02_20/o1_mf_nnndf_TAG20140220T163840_9jd8so9h_.bkp

Do you really want to delete the above objects (enter YES or NO)? yes
deleted backup piece
backup piece handle=/app/oracle/fast_recovery_area/CDB001/backupset/2014_02_20/o1_mf_nnndf_TAG20140220T163840_9jd8d3v9_.bkp RECID=38 STAMP=840040723
deleted backup piece
backup piece handle=/app/oracle/fast_recovery_area/CDB001/E1F26215682E1142E045000000000001/backupset/2014_02_20/o1_mf_nnndf_TAG20140220T163840_9jd8l78o_.bkp RECID=39 STAMP=840040919
deleted backup piece
backup piece handle=/app/oracle/fast_recovery_area/CDB001/E1F329ECE0F411E6E045000000000001/backupset/2014_02_20/o1_mf_nnndf_TAG20140220T163840_9jd8n97w_.bkp RECID=40 STAMP=840040985
deleted backup piece
backup piece handle=/app/oracle/fast_recovery_area/CDB001/E2B9BE56B8B936CEE045000000000001/backupset/2014_02_20/o1_mf_nnndf_TAG20140220T163840_9jd8q9jq_.bkp RECID=41 STAMP=840041081
deleted backup piece
backup piece handle=/app/oracle/fast_recovery_area/CDB001/E19363E52C005C9AE045000000000001/backupset/2014_02_20/o1_mf_nnndf_TAG20140220T163840_9jd8so9h_.bkp RECID=42 STAMP=840041157
Deleted 5 objects

On RMAN 12c it is also possible to drop a table from the RMAN session.

RMAN> drop table test purge;

Statement processed

RMAN> select count(*) from test;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 03/05/2014 21:03:59
ORA-00942: table or view does not exist

Are there SQL commands that can not be executed from the 12c RMAN client ?
I was wondering if it is possible to change a container for example and the answer is: you can not change the container in a RMAN session using the SQL syntax.

RMAN> alter session set container=PDB001;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 03/05/2014 21:24:44
RMAN-06815: cannot change the container in RMAN session.

And finally have a look at the following example where the releases 11g and 12c have instead the same behaviour.

I'm connected using the RMAN 11g client, but I have forgot to set the NLS_DATE_FORMAT environment variable.
As a result when I execute commands such as list backup I'm not able to see time details, because RMAN by default shows only the date.

[oracle@localhost ~]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Tue Mar 5 21:38:10 2014

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

connected to target database: ORCL (DBID=1229390655)

RMAN> list backup summary;

List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
257     B  A  A DISK        22-04-2013      1       1       YES        TAG20130422T213335
258     B  F  A DISK        22-04-2013      1       1       YES        TAG20130422T213339
259     B  A  A DISK        22-04-2013      1       1       YES        TAG20130422T214537
260     B  F  A DISK        22-04-2013      1       1       NO         TAG20130422T214539
261     B  F  A DISK        23-04-2013      1       1       NO         TAG20130423T050224
262     B  F  A DISK        30-04-2013      1       1       NO         TAG20130430T221302

To set the NLS_DATE_FORMAT variable on RMAN 11g I need to execute the SQL "alter session" command and it follows the previous logic applied to the first example (sql "alter database datafile ...") with double and single quotes because I need to specify the date and time format.

RMAN> sql "alter session set nls_date_format=''dd-mm-yyyy hh24:mi:ss''";

sql statement: alter session set nls_date_format=''dd-mm-yyyy hh24:mi:ss''

RMAN> list backup summary;

List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
257     B  A  A DISK        22-04-2013      1       1       YES        TAG20130422T213335
258     B  F  A DISK        22-04-2013      1       1       YES        TAG20130422T213339
259     B  A  A DISK        22-04-2013      1       1       YES        TAG20130422T214537
260     B  F  A DISK        22-04-2013      1       1       NO         TAG20130422T214539
261     B  F  A DISK        23-04-2013      1       1       NO         TAG20130423T050224
262     B  F  A DISK        30-04-2013      1       1       NO         TAG20130430T221302

The column Completion is still formatted using the default date settings. so RMAN ignores that new setting.
Let's see the same example using a 12c RMAN client.

[oracle@localhost admin]$ rman target /

Recovery Manager: Release 12.1.0.1.0 - Production on Wed Mar 5 21:43:45 2014

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

connected to target database: CDB001 (DBID=4134986109)

RMAN> list backup summary;

List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
43      B  F  A DISK        20-FEB-14       1       1       NO         TAG20140220T164653
44      B  F  A DISK        03-MAR-14       1       1       YES        PDB001_FULL_20140303
45      B  F  A DISK        03-MAR-14       1       1       NO         TAG20140303T153904

RMAN> select sysdate from dual;

SYSDATE  
---------
05-MAR-14

Even if I modify the NLS_DATE_FORMAT using the SQL statement, also RMAN 12c is not able to use it.

RMAN> alter session set nls_date_format='dd-mm-yyyy hh24:mi:ss';

Statement processed

RMAN> list backup summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
43      B  F  A DISK        20-FEB-14       1       1       NO         TAG20140220T164653
44      B  F  A DISK        03-MAR-14       1       1       YES        PDB001_FULL_20140303
45      B  F  A DISK        03-MAR-14       1       1       NO         TAG20140303T153904

Running a simple select it's possible to see the SQL session is instead able to use the new NLS_DATE_FORMAT setting.

RMAN> select sysdate from dual;

SYSDATE            
-------------------
05-03-2014 21:45:25

That's all.

How to identify which pdbs a backup set belong to

I want to see what useful information are returned by few RMAN commands such as report schema or list backup when using the default RMAN configuration and getting some backups.

When connected to the ROOT container issuing the RMAN report schema command is quite simple identify how many pluggable databases are plugged into the container database and, above all, the relationship with their datafiles and locations.

In my case the container database, named CDB001, contains three pluggable databases named PDB001, PDB002 and PDB003: it is also possible to easily identify the seed container used as template when you want to create new PDBs.

[oracle@localhost ~]$ rman target /

Recovery Manager: Release 12.1.0.1.0 - Production on Thu Feb 20 16:37:50 2014

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

connected to target database: CDB001 (DBID=4134986109)

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name CDB001

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    780      SYSTEM               ***     /app/oracle/oradata/CDB001/system01.dbf
2    260      PDB$SEED:SYSTEM      ***     /app/oracle/oradata/CDB001/pdbseed/system01.dbf
3    800      SYSAUX               ***     /app/oracle/oradata/CDB001/sysaux01.dbf
4    625      PDB$SEED:SYSAUX      ***     /app/oracle/oradata/CDB001/pdbseed/sysaux01.dbf
5    310      UNDOTBS1             ***     /app/oracle/oradata/CDB001/undotbs01.dbf
6    5        USERS                ***     /app/oracle/oradata/CDB001/users01.dbf
7    260      PDB001:SYSTEM        ***     /app/oracle/oradata/CDB001/PDB001/system01.dbf
8    635      PDB001:SYSAUX        ***     /app/oracle/oradata/CDB001/PDB001/sysaux01.dbf
9    5        PDB001:USERS         ***     /app/oracle/oradata/CDB001/PDB001/PDB001_users01.dbf
10   260      PDB002:SYSTEM        ***     /app/oracle/oradata/CDB001/PDB002/system01.dbf
11   635      PDB002:SYSAUX        ***     /app/oracle/oradata/CDB001/PDB002/sysaux01.dbf
12   5        PDB002:USERS         ***     /app/oracle/oradata/CDB001/PDB002/PDB002_users01.dbf
19   260      PDB003:SYSTEM        ***     /app/oracle/oradata/CDB001/PDB003/system01.dbf
20   635      PDB003:SYSAUX        ***     /app/oracle/oradata/CDB001/PDB003/sysaux01.dbf
21   5        PDB003:USERS         ***     /app/oracle/oradata/CDB001/PDB003/PDB001_users01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    73       TEMP                 32767       /app/oracle/oradata/CDB001/temp01.dbf
2    61       PDB$SEED:TEMP        32767       /app/oracle/oradata/CDB001/pdbseed/temp01.dbf
3    20       PDB001:TEMP          32767       /app/oracle/oradata/CDB001/PDB001/temp01.dbf
4    20       PDB002:TEMP          32767       /app/oracle/oradata/CDB001/PDB002/temp01.dbf
5    20       PDB003:TEMP          32767       /app/oracle/oradata/CDB001/PDB003/temp01.dbf

After you successfully connect to the container database with RMAN it is not sufficient to have a look at the third line to determine if you are using the container database or a pluggable one.
That line simply specifies you are connected to a target database and when connecting to a pluggable database it could be misleading.

Have a look at the following attempts to connect first to the root container and then directly to the PDB001 pluggable database.
Where and what are the differences if you exclude the time settings ?

[oracle@localhost admin]$ rman target /

Recovery Manager: Release 12.1.0.1.0 - Production on Thu Feb 17 17:18:05 2014

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

connected to target database: CDB001 (DBID=4134986109)

 

[oracle@localhost admin]$ rman target sys/oracle@PDB001

Recovery Manager: Release 12.1.0.1.0 - Production on Thu Feb 17 17:19:24 2014

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

connected to target database: CDB001 (DBID=4134986109)

When connected to a pluggable database using the report schema command is possible to deduce the PDB name looking at its datafiles and locations: indeed in this case only the datafiles associated with the connected PDB are listed in the output of the report schema command.

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name CDB001

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
7    260      SYSTEM               ***     /app/oracle/oradata/CDB001/PDB001/system01.dbf
8    635      SYSAUX               ***     /app/oracle/oradata/CDB001/PDB001/sysaux01.dbf
9    5        USERS                ***     /app/oracle/oradata/CDB001/PDB001/PDB001_users01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
3    20       TEMP                 32767       /app/oracle/oradata/CDB001/PDB001/temp01.dbf

Quit the previous conection to the pluggable database and connect again to the ROOT container and have a look at the list backup command.
List backup command is not so useful when you still don't have any backups available.

RMAN> list backup;

specification does not match any backup in the repository

So let's get first a full database backup.
When you are connected to the ROOT container any commands you send using RMAN are related to the ROOT container; of course you can, while connected to the ROOT container, execute command on the pluggable database but you have to specify the pluggable database name and additional syntax.

RMAN> backup database;

Starting backup at 20-FEB-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=81 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00003 name=/app/oracle/oradata/CDB001/sysaux01.dbf
input datafile file number=00001 name=/app/oracle/oradata/CDB001/system01.dbf
input datafile file number=00005 name=/app/oracle/oradata/CDB001/undotbs01.dbf
input datafile file number=00006 name=/app/oracle/oradata/CDB001/users01.dbf
channel ORA_DISK_1: starting piece 1 at 20-FEB-14
channel ORA_DISK_1: finished piece 1 at 20-FEB-14
piece handle=/app/oracle/fast_recovery_area/CDB001/backupset/2014_02_20/o1_mf_nnndf_TAG20140220T163840_9jd8d3v9_.bkp tag=TAG20140220T163840 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:03:16
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00008 name=/app/oracle/oradata/CDB001/PDB001/sysaux01.dbf
input datafile file number=00007 name=/app/oracle/oradata/CDB001/PDB001/system01.dbf
input datafile file number=00009 name=/app/oracle/oradata/CDB001/PDB001/PDB001_users01.dbf
channel ORA_DISK_1: starting piece 1 at 20-FEB-14
channel ORA_DISK_1: finished piece 1 at 20-FEB-14
piece handle=/app/oracle/fast_recovery_area/CDB001/E1F26215682E1142E045000000000001/backupset/2014_02_20/o1_mf_nnndf_TAG20140220T163840_9jd8l78o_.bkp tag=TAG20140220T163840 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:06
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00011 name=/app/oracle/oradata/CDB001/PDB002/sysaux01.dbf
input datafile file number=00010 name=/app/oracle/oradata/CDB001/PDB002/system01.dbf
input datafile file number=00012 name=/app/oracle/oradata/CDB001/PDB002/PDB002_users01.dbf
channel ORA_DISK_1: starting piece 1 at 20-FEB-14
channel ORA_DISK_1: finished piece 1 at 20-FEB-14
piece handle=/app/oracle/fast_recovery_area/CDB001/E1F329ECE0F411E6E045000000000001/backupset/2014_02_20/o1_mf_nnndf_TAG20140220T163840_9jd8n97w_.bkp tag=TAG20140220T163840 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:36
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00020 name=/app/oracle/oradata/CDB001/PDB003/sysaux01.dbf
input datafile file number=00019 name=/app/oracle/oradata/CDB001/PDB003/system01.dbf
input datafile file number=00021 name=/app/oracle/oradata/CDB001/PDB003/PDB001_users01.dbf
channel ORA_DISK_1: starting piece 1 at 20-FEB-14
channel ORA_DISK_1: finished piece 1 at 20-FEB-14
piece handle=/app/oracle/fast_recovery_area/CDB001/E2B9BE56B8B936CEE045000000000001/backupset/2014_02_20/o1_mf_nnndf_TAG20140220T163840_9jd8q9jq_.bkp tag=TAG20140220T163840 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:15
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00004 name=/app/oracle/oradata/CDB001/pdbseed/sysaux01.dbf
input datafile file number=00002 name=/app/oracle/oradata/CDB001/pdbseed/system01.dbf
channel ORA_DISK_1: starting piece 1 at 20-FEB-14
channel ORA_DISK_1: finished piece 1 at 20-FEB-14
piece handle=/app/oracle/fast_recovery_area/CDB001/E19363E52C005C9AE045000000000001/backupset/2014_02_20/o1_mf_nnndf_TAG20140220T163840_9jd8so9h_.bkp tag=TAG20140220T163840 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55
Finished backup at 20-FEB-14

Starting Control File and SPFILE Autobackup at 20-FEB-14
piece handle=/app/oracle/fast_recovery_area/CDB001/autobackup/2014_02_20/o1_mf_s_840041213_9jd8vj0m_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 20-FEB-14

With the list command you can display information about the available backups. Looking at that information you can be able to know that the o1_mf_nnndf_TAG20140220T163840_9jd8l78o_.bkp backup is related to a full backup of the pluggable database named PDB001, the o1_mf_nnndf_TAG20140220T163840_9jd8n97w_.bkp backup is related to PDB002 and o1_mf_nnndf_TAG20140220T163840_9jd8q9jq_.bkp to PDB003.

RMAN> list backup;

using target database control file instead of recovery catalog

List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
38      Full    386.84M    DISK        00:03:08     20-FEB-14      
        BP Key: 38   Status: AVAILABLE  Compressed: YES  Tag: TAG20140220T163840
        Piece Name: /app/oracle/fast_recovery_area/CDB001/backupset/2014_02_20/o1_mf_nnndf_TAG20140220T163840_9jd8d3v9_.bkp
  List of Datafiles in backup set 38
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 3146659    20-FEB-14 /app/oracle/oradata/CDB001/system01.dbf
  3       Full 3146659    20-FEB-14 /app/oracle/oradata/CDB001/sysaux01.dbf
  5       Full 3146659    20-FEB-14 /app/oracle/oradata/CDB001/undotbs01.dbf
  6       Full 3146659    20-FEB-14 /app/oracle/oradata/CDB001/users01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
39      Full    210.43M    DISK        00:01:01     20-FEB-14      
        BP Key: 39   Status: AVAILABLE  Compressed: YES  Tag: TAG20140220T163840
        Piece Name: /app/oracle/fast_recovery_area/CDB001/E1F26215682E1142E045000000000001/backupset/2014_02_20/o1_mf_nnndf_TAG20140220T163840_9jd8l78o_.bkp
  List of Datafiles in backup set 39
  Container ID: 3, PDB Name: PDB001
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  7       Full 3147302    20-FEB-14 /app/oracle/oradata/CDB001/PDB001/system01.dbf
  8       Full 3147302    20-FEB-14 /app/oracle/oradata/CDB001/PDB001/sysaux01.dbf
  9       Full 3147302    20-FEB-14 /app/oracle/oradata/CDB001/PDB001/PDB001_users01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
40      Full    210.28M    DISK        00:01:31     20-FEB-14      
        BP Key: 40   Status: AVAILABLE  Compressed: YES  Tag: TAG20140220T163840
        Piece Name: /app/oracle/fast_recovery_area/CDB001/E1F329ECE0F411E6E045000000000001/backupset/2014_02_20/o1_mf_nnndf_TAG20140220T163840_9jd8n97w_.bkp
  List of Datafiles in backup set 40
  Container ID: 4, PDB Name: PDB002
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  10      Full 3120954    14-AUG-13 /app/oracle/oradata/CDB001/PDB002/system01.dbf
  11      Full 3120954    14-AUG-13 /app/oracle/oradata/CDB001/PDB002/sysaux01.dbf
  12      Full 3120954    14-AUG-13 /app/oracle/oradata/CDB001/PDB002/PDB002_users01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
41      Full    210.24M    DISK        00:01:14     20-FEB-14      
        BP Key: 41   Status: AVAILABLE  Compressed: YES  Tag: TAG20140220T163840
        Piece Name: /app/oracle/fast_recovery_area/CDB001/E2B9BE56B8B936CEE045000000000001/backupset/2014_02_20/o1_mf_nnndf_TAG20140220T163840_9jd8q9jq_.bkp
  List of Datafiles in backup set 41
  Container ID: 5, PDB Name: PDB003
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  19      Full 3121273    14-AUG-13 /app/oracle/oradata/CDB001/PDB003/system01.dbf
  20      Full 3121273    14-AUG-13 /app/oracle/oradata/CDB001/PDB003/sysaux01.dbf
  21      Full 3121273    14-AUG-13 /app/oracle/oradata/CDB001/PDB003/PDB001_users01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
42      Full    207.56M    DISK        00:00:53     20-FEB-14      
        BP Key: 42   Status: AVAILABLE  Compressed: YES  Tag: TAG20140220T163840
        Piece Name: /app/oracle/fast_recovery_area/CDB001/E19363E52C005C9AE045000000000001/backupset/2014_02_20/o1_mf_nnndf_TAG20140220T163840_9jd8so9h_.bkp
  List of Datafiles in backup set 42
  Container ID: 2, PDB Name: PDB$SEED
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  2       Full 1786693    16-JUL-13 /app/oracle/oradata/CDB001/pdbseed/system01.dbf
  4       Full 1786693    16-JUL-13 /app/oracle/oradata/CDB001/pdbseed/sysaux01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
43      Full    17.20M     DISK        00:00:03     20-FEB-14      
        BP Key: 43   Status: AVAILABLE  Compressed: NO  Tag: TAG20140220T164653
        Piece Name: /app/oracle/fast_recovery_area/CDB001/autobackup/2014_02_20/o1_mf_s_840041213_9jd8vj0m_.bkp
  SPFILE Included: Modification time: 20-FEB-14
  SPFILE db_unique_name: CDB001
  Control File Included: Ckp SCN: 3148638      Ckp time: 20-FEB-14

Looking only at the name of the backup set how you can deduce which PDB that backup belongs to ?
If your database is still available you can query the V$BACKUP_FILES view and match the container id with the V$CONTAINERS view for example.

SQL> select pkey, name, guid, fname from v$containers a, v$backup_files b where a.con_id = b.con_id and file_type = 'PIECE' order by pkey;

      PKEY NAME     GUID        FNAME
---------- -------- -------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------
 38 CDB$ROOT E19363E52C015C9AE045000000000001 /app/oracle/fast_recovery_area/CDB001/backupset/2014_02_20/o1_mf_nnndf_TAG20140220T163840_9jd8d3v9_.bkp
 39 PDB001   E1F26215682E1142E045000000000001 /app/oracle/fast_recovery_area/CDB001/E1F26215682E1142E045000000000001/backupset/2014_02_20/o1_mf_nnndf_TAG20140220T163840_9jd8l78o_.bkp
 40 PDB002   E1F329ECE0F411E6E045000000000001 /app/oracle/fast_recovery_area/CDB001/E1F329ECE0F411E6E045000000000001/backupset/2014_02_20/o1_mf_nnndf_TAG20140220T163840_9jd8n97w_.bkp
 41 PDB003   E2B9BE56B8B936CEE045000000000001 /app/oracle/fast_recovery_area/CDB001/E2B9BE56B8B936CEE045000000000001/backupset/2014_02_20/o1_mf_nnndf_TAG20140220T163840_9jd8q9jq_.bkp
 42 PDB$SEED E19363E52C005C9AE045000000000001 /app/oracle/fast_recovery_area/CDB001/E19363E52C005C9AE045000000000001/backupset/2014_02_20/o1_mf_nnndf_TAG20140220T163840_9jd8so9h_.bkp
 43 CDB$ROOT E19363E52C015C9AE045000000000001 /app/oracle/fast_recovery_area/CDB001/autobackup/2014_02_20/o1_mf_s_840041213_9jd8vj0m_.bkp

6 rows selected.

But how can you identify which pluggable database the backup belongs to looking only at the location of the backup set ?
From the file location you can only use the Globally Unique ID (associated with every pluggable databases by the Oracle software) and deduce the name of the pluggable database: if you don't have access to the database it is quite difficult to remember that association.
For this reason when dealing with container and pluggable databases it is heartily recommended to tag your backups using specific names and easily refer to them when required.

RMAN> backup pluggable database PDB001 tag 'PDB001_FULL_20140303';

Starting backup at 03-MAR-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=38 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00008 name=/app/oracle/oradata/CDB001/PDB001/sysaux01.dbf
input datafile file number=00007 name=/app/oracle/oradata/CDB001/PDB001/system01.dbf
input datafile file number=00009 name=/app/oracle/oradata/CDB001/PDB001/PDB001_users01.dbf
channel ORA_DISK_1: starting piece 1 at 03-MAR-14
channel ORA_DISK_1: finished piece 1 at 03-MAR-14
piece handle=/app/oracle/fast_recovery_area/CDB001/E1F26215682E1142E045000000000001/backupset/2014_03_03/o1_mf_nnndf_PDB001_FULL_20140303_9k94y5t2_.bkp tag=PDB001_FULL_20140303 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:06
Finished backup at 03-MAR-14

Starting Control File and SPFILE Autobackup at 03-MAR-14
piece handle=/app/oracle/fast_recovery_area/CDB001/autobackup/2014_03_03/o1_mf_s_841246744_9k950bv6_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 03-MAR-14

RMAN> list backup of pluggable database PDB001;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
39      Full    210.43M    DISK        00:01:01     20-FEB-14      
        BP Key: 39   Status: AVAILABLE  Compressed: YES  Tag: TAG20140220T163840
        Piece Name: /app/oracle/fast_recovery_area/CDB001/E1F26215682E1142E045000000000001/backupset/2014_02_20/o1_mf_nnndf_TAG20140220T163840_9jd8l78o_.bkp
  List of Datafiles in backup set 39
  Container ID: 3, PDB Name: PDB001
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  7       Full 3147302    20-FEB-14 /app/oracle/oradata/CDB001/PDB001/system01.dbf
  8       Full 3147302    20-FEB-14 /app/oracle/oradata/CDB001/PDB001/sysaux01.dbf
  9       Full 3147302    20-FEB-14 /app/oracle/oradata/CDB001/PDB001/PDB001_users01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
44      Full    210.45M    DISK        00:01:02     03-MAR-14      
        BP Key: 44   Status: AVAILABLE  Compressed: YES  Tag: PDB001_FULL_20140303
        Piece Name: /app/oracle/fast_recovery_area/CDB001/E1F26215682E1142E045000000000001/backupset/2014_03_03/o1_mf_nnndf_PDB001_FULL_20140303_9k94y5t2_.bkp
  List of Datafiles in backup set 44
  Container ID: 3, PDB Name: PDB001
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  7       Full 3170400    03-MAR-14 /app/oracle/oradata/CDB001/PDB001/system01.dbf
  8       Full 3170400    03-MAR-14 /app/oracle/oradata/CDB001/PDB001/sysaux01.dbf
  9       Full 3170400    03-MAR-14 /app/oracle/oradata/CDB001/PDB001/PDB001_users01.dbf

Looking at the location name you can now be able to determine that the o1_mf_nnndf_PDB001_FULL_20140303_9k94y5t2_.bkp backup set is related to PDB001 pluggable database.

That's all.