EXPDP backup failed with error ORA-04031

In one of my production environment I found my EXPDP backup failed with below error. It is not always. Same I re-created in one of my test instance. Here are the reported errors.

Error while running EXPDP backup: 
============================== 
UDE-31623: operation generated ORACLE error 31623
ORA-31623: a job is not attached to this session via the specified handle
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3326
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4551
ORA-06512: at line 1


Error in alert log at that time: 
======================== 
Sun Oct 23 00:02:48 2016
Errors in file /u01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_ora_787060.trc (incident=132844):
ORA-04031: unable to allocate 376 bytes of shared memory ("streams pool","unknown object","streams pool","kwqbsinfy:cco")
Incident details in: /u01/app/oracle/diag/rdbms/prod/PROD/incident/incdir_132844/PROD_ora_787060_i132844.trc
Sun Oct 23 00:02:53 2016
Dumping diagnostic data in directory=[cdmp_20161023000253], requested by (instance=1, osid=787060), summary=[incident=132844].


Solution / Fix :
============

By setting minimum value to streams_pool_size of minimum 200M we can avoid the error.

OR

In Oracle 11g, it is highly recommended to use AMM i.e memory_target where SGA and PGA are automatically managed and set minimum values to SGA_TARGET, PGA and other pools.

This configuration will give better performance and reduces fragmentation and avoids ORA-04031 errors.

AND 

set the parameter _shared_pool_reserved_pct instead of the parameter shared_pool_reserved_size. 

Set the shared pool reserved percent with: 

alter system set "_shared_pool_reserved_pct"=10 scope=spfile; 

NOTE: Restart the database and listener once the changes are done. 

The above settings will not only fix ORA-04031 but also helps for optimal memory utilization. 

Thanks .

Error: Failed to open the wallet - Fix

During cloning activity in one of our EBS R12, the whole cloning process completed successfully without reporting any issues. But when we are trying to start application services all “opmn” services are started except HTTP_Server.

e.g.,

[applprod@app scripts]$ ./adopmnctl.sh status

You are running adopmnctl.sh version 120.6.12010000.5

Checking status of OPMN managed processes...

Processes in Instance: PRODHCM_app.app.testdomain.com
---------------------------------+--------------------+---------+---------
ias-component                    | process-type       |     pid | status
---------------------------------+--------------------+---------+---------
OC4JGroup:default_group          | OC4J:oafm          |    2418 | Alive  
OC4JGroup:default_group          | OC4J:forms         |    2219 | Alive  
OC4JGroup:default_group          | OC4J:forms         |    2218 | Alive  
OC4JGroup:default_group          | OC4J:forms         |    2216 | Alive  
OC4JGroup:default_group          | OC4J:forms         |    2215 | Alive  
OC4JGroup:default_group          | OC4J:oacore        |    1889 | Alive  
OC4JGroup:default_group          | OC4J:oacore        |    1888 | Alive  
OC4JGroup:default_group          | OC4J:oacore        |    1887 | Alive  
OC4JGroup:default_group          | OC4J:oacore        |    1886 | Alive  
HTTP_Server                      | HTTP_Server        |     N/A | Down  


adopmnctl.sh: exiting with status 0

adopmnctl.sh: check the logfile /u01/applprod/inst/apps/PRODHCM_app/logs/appl/admin/log/adopmnctl.txt for more information ...

[applprod@app scripts]$

In the opmn logfile below error messages are reported:

$ cat /u01/applprod/inst/apps/PRODHCM_app/logs/ora/10.1.3/opmn/HTTP_Server~1.log

--------
16/10/17 10:34:10 Start process
--------
/u01/applprod/inst/apps/PRODHCM_app/ora/10.1.3/Apache/Apache/bin/apachectl startssl: execing httpd
Error: Failed to open the wallet [Hint: incorrect password, bad wallet file, ...] (Server app.apollohospitals.com:8030, wallet file:/u01/applprod/inst/apps/PRODHCM_app/certs/Apache)

Then I tried to start the "apache"server only but issue not resolved. Restart of application serer also not resolved the issue. From the above error it seems there is a issue with Apache server certificate. So, Finally below workaround worked fine:

So, connected to App server and stopped the all application services.

$ cd $ADMIN_SCRIPT_HOME
$ ./adstpall.sh

Then, go to Apache sertificate directory.

$ cd /u01/applprod/inst/apps/PRODHCM_app/certs/Apache

Just rename exiting file in the folder:

$ mv cwallet.sso cwallet.sso_BAK

And, copy the same file from source ( prod) to this cloned environment and start the application. It worked for me.

Restore Pluggable database using RMAN in Oracle 12c

Here you can see how to take a pluggable database backup with compress mode and some restore and recover scenarios.

-- To Take pluggable database backup:

$ rman target /

RMAN>

RMAN> backup as compressed backupset pluggable database salespdb format '/u10/EXAM/fast_recovery_area/salespdb_full_12c_db_%U';

Starting backup at 08-OCT-2016 17:10:27

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=212 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=00012 name=/u10/EXAM/ORADATA/CDB/salespdb/sysaux01.dbf

input datafile file number=00011 name=/u10/EXAM/ORADATA/CDB/salespdb/system01.dbf

input datafile file number=00013 name=/u10/EXAM/ORADATA/CDB/salespdb/sales01.dbf

channel ORA_DISK_1: starting piece 1 at 08-OCT-2016 17:10:29

channel ORA_DISK_1: finished piece 1 at 08-OCT-2016 17:11:44

piece handle=/u10/EXAM/fast_recovery_area/salespdb_full_12c_db_0krhs1k5_1_1 tag=TAG20161008T171028 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:01:15

Finished backup at 08-OCT-2016 17:11:44

Starting Control File and SPFILE Autobackup at 08-OCT-2016 17:11:44

piece handle=/u10/EXAM/fast_recovery_area/CDB/autobackup/2016_10_08/o1_mf_s_924714704_czkpvw5x_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 08-OCT-2016 17:11:51

RMAN>

Verify schemas:

RMAN> report schema;

using target database control file instead of recovery catalog

Report of database schema for database with db_unique_name CDB

List of Permanent Datafiles

===========================

File Size(MB) Tablespace           RB segs Datafile Name

---- -------- -------------------- ------- ------------------------

1    800      SYSTEM               YES     /u10/EXAM/ORADATA/CDB/system01.dbf

3    830      SYSAUX               NO      /u10/EXAM/ORADATA/CDB/sysaux01.dbf

4    225      UNDOTBS1             YES     /u10/EXAM/ORADATA/CDB/undotbs01.dbf

5    250      PDB$SEED:SYSTEM      NO      /u10/EXAM/ORADATA/CDB/pdbseed/system01.dbf

6    5        USERS                NO      /u10/EXAM/ORADATA/CDB/users01.dbf

7    570      PDB$SEED:SYSAUX      NO      /u10/EXAM/ORADATA/CDB/pdbseed/sysaux01.dbf

8    260      PDB1:SYSTEM          NO      /u10/EXAM/ORADATA/CDB/pdb1/system01.dbf

9    590      PDB1:SYSAUX          NO      /u10/EXAM/ORADATA/CDB/pdb1/sysaux01.dbf

10   5        PDB1:USERS           NO      /u10/EXAM/ORADATA/CDB/pdb1/pdb1_users01.dbf

11   260      SALESPDB:SYSTEM      NO      /u10/EXAM/ORADATA/CDB/salespdb/system01.dbf

12   580      SALESPDB:SYSAUX      NO      /u10/EXAM/ORADATA/CDB/salespdb/sysaux01.dbf

13   250      SALESPDB:SALES       NO      /u10/EXAM/ORADATA/CDB/salespdb/sales01.dbf

14   260      PDB2:SYSTEM          NO      /u10/EXAM/ORADATA/CDB/pdb2/system01.dbf

15   580      PDB2:SYSAUX          NO      /u10/EXAM/ORADATA/CDB/pdb2/sysaux01.dbf

16   5        PDB2:USERS           NO      /u10/EXAM/ORADATA/CDB/pdb2/pdb1_users01.dbf

17   260      HRPDB:SYSTEM         NO      /u10/EXAM/ORADATA/CDB/hrpdb/system01.dbf

18   580      HRPDB:SYSAUX         NO      /u10/EXAM/ORADATA/CDB/hrpdb/sysaux01.dbf

19   250      HRPDB:SALES          NO      /u10/EXAM/ORADATA/CDB/hrpdb/hr01.dbf

List of Temporary Files

=======================

File Size(MB) Tablespace           Maxsize(MB) Tempfile Name

---- -------- -------------------- ----------- --------------------

1    197      TEMP                 32767       /u10/EXAM/ORADATA/CDB/temp01.dbf

2    100      PDB$SEED:TEMP        32767       /u10/EXAM/ORADATA/CDB/pdbseed/pdbseed_temp012016-10-04_03-18-32-PM.dbf

3    20       PDB1:TEMP            32767       /u10/EXAM/ORADATA/CDB/pdb1/temp012016-10-04_03-18-32-PM.dbf

4    20       SALESPDB:TEMP        32767       /u10/EXAM/ORADATA/CDB/salespdb/pdbseed_temp012016-10-04_03-18-32-PM.dbf

5    20       PDB2:TEMP            32767       /u10/EXAM/ORADATA/CDB/pdb2/temp012016-10-04_03-18-32-PM.dbf

6    20       HRPDB:TEMP           32767       /u10/EXAM/ORADATA/CDB/hrpdb/pdbseed_temp012016-10-04_03-18-32-PM.dbf

RMAN>

Note: You can take multiple pluggable databases using below format script also:

rman>

run { 
    setlimit channel ch1 maxopenfiles 8; 
    backup 
    incremental level = 0
    filesperset = 32
    pluggable database pdb1, ..pdbn;
} 
exit;

rman>

Now do following activity for test:

SQL> alter pluggable database SALESPDB close immediate;

Pluggable database altered.

SQL>

$ cd /u10/EXAM/ORADATA/CDB/salespdb

]$ pwd

/u10/EXAM/ORADATA/CDB/salespdb

$ mv sales01.dbf sales01.dbf_bkp

SQL> alter pluggable database salespdb open;

alter pluggable database salespdb open

*

ERROR at line 1:

ORA-01157: cannot identify/lock data file 13 - see DBWR trace file

ORA-01110: data file 13: '/u10/EXAM/ORADATA/CDB/salespdb/sales01.dbf'

SQL>

Here I am trying to open my pdb. It is clearly showing “sales01.dbf” datafile is missing. So you  need to restore and recover from last valid backup.

Now start restore and Recover:

RMAN> restore pluggable database salespdb;

Starting restore at 10-OCT-2016 15:25:51

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00011 to /u10/EXAM/ORADATA/CDB/salespdb/system01.dbf

channel ORA_DISK_1: restoring datafile 00012 to /u10/EXAM/ORADATA/CDB/salespdb/sysaux01.dbf

channel ORA_DISK_1: restoring datafile 00013 to /u10/EXAM/ORADATA/CDB/salespdb/sales01.dbf

channel ORA_DISK_1: reading from backup piece /u10/EXAM/fast_recovery_area/salespdb_full_12c_db_0krhs1k5_1_1

channel ORA_DISK_1: piece handle=/u10/EXAM/fast_recovery_area/salespdb_full_12c_db_0krhs1k5_1_1 tag=TAG20161008T171028

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:55

Finished restore at 10-OCT-2016 15:26:46

Now Recover the pluggable database:

RMAN> recover pluggable database salespdb;

Starting recover at 10-OCT-2016 15:26:58

using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 54 is already on disk as file /u10/EXAM/fast_recovery_area/CDB/archivelog/2016_10_08/o1_mf_1_54_czkvhl14_.arc

archived log for thread 1 with sequence 55 is already on disk as file /u10/EXAM/fast_recovery_area/CDB/archivelog/2016_10_08/o1_mf_1_55_czl60bfv_.arc

archived log for thread 1 with sequence 56 is already on disk as file /u10/EXAM/fast_recovery_area/CDB/archivelog/2016_10_08/o1_mf_1_56_czlf1tko_.arc

archived log for thread 1 with sequence 57 is already on disk as file /u10/EXAM/fast_recovery_area/CDB/archivelog/2016_10_09/o1_mf_1_57_czm3ywck_.arc

archived log for thread 1 with sequence 58 is already on disk as file /u10/EXAM/fast_recovery_area/CDB/archivelog/2016_10_09/o1_mf_1_58_czm95ftg_.arc

archived log for thread 1 with sequence 59 is already on disk as file /u10/EXAM/fast_recovery_area/CDB/archivelog/2016_10_09/o1_mf_1_59_czmlktv5_.arc

archived log for thread 1 with sequence 60 is already on disk as file /u10/EXAM/fast_recovery_area/CDB/archivelog/2016_10_09/o1_mf_1_60_czn1s62o_.arc

archived log for thread 1 with sequence 61 is already on disk as file /u10/EXAM/fast_recovery_area/CDB/archivelog/2016_10_09/o1_mf_1_61_cznh65n2_.arc

archived log for thread 1 with sequence 62 is already on disk as file /u10/EXAM/fast_recovery_area/CDB/archivelog/2016_10_09/o1_mf_1_62_cznpw5wk_.arc

archived log for thread 1 with sequence 63 is already on disk as file /u10/EXAM/fast_recovery_area/CDB/archivelog/2016_10_09/o1_mf_1_63_czny6jb4_.arc

archived log for thread 1 with sequence 64 is already on disk as file /u10/EXAM/fast_recovery_area/CDB/archivelog/2016_10_10/o1_mf_1_64_czom11oo_.arc

archived log for thread 1 with sequence 65 is already on disk as file /u10/EXAM/fast_recovery_area/CDB/archivelog/2016_10_10/o1_mf_1_65_czpkzhmk_.arc

archived log file name=/u10/EXAM/fast_recovery_area/CDB/archivelog/2016_10_08/o1_mf_1_54_czkvhl14_.arc thread=1 sequence=54

archived log file name=/u10/EXAM/fast_recovery_area/CDB/archivelog/2016_10_08/o1_mf_1_55_czl60bfv_.arc thread=1 sequence=55

archived log file name=/u10/EXAM/fast_recovery_area/CDB/archivelog/2016_10_08/o1_mf_1_56_czlf1tko_.arc thread=1 sequence=56

archived log file name=/u10/EXAM/fast_recovery_area/CDB/archivelog/2016_10_09/o1_mf_1_57_czm3ywck_.arc thread=1 sequence=57

archived log file name=/u10/EXAM/fast_recovery_area/CDB/archivelog/2016_10_09/o1_mf_1_58_czm95ftg_.arc thread=1 sequence=58

archived log file name=/u10/EXAM/fast_recovery_area/CDB/archivelog/2016_10_09/o1_mf_1_59_czmlktv5_.arc thread=1 sequence=59

archived log file name=/u10/EXAM/fast_recovery_area/CDB/archivelog/2016_10_09/o1_mf_1_60_czn1s62o_.arc thread=1 sequence=60

archived log file name=/u10/EXAM/fast_recovery_area/CDB/archivelog/2016_10_09/o1_mf_1_61_cznh65n2_.arc thread=1 sequence=61

archived log file name=/u10/EXAM/fast_recovery_area/CDB/archivelog/2016_10_09/o1_mf_1_62_cznpw5wk_.arc thread=1 sequence=62

archived log file name=/u10/EXAM/fast_recovery_area/CDB/archivelog/2016_10_09/o1_mf_1_63_czny6jb4_.arc thread=1 sequence=63

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

Finished recover at 10-OCT-2016 15:27:06

RMAN> quit

Recovery Manager complete.

Now verify your pluggbale database datafiles. Sure it is recovered.

[oracle@eh-uatdb1 salespdb]$ ls

pdbseed_temp012016-10-04_03-18-32-PM.dbf  sales01.dbf  sales01.dbf_bkp  sysaux01.dbf  system01.dbf

$ ls sales01.dbf

sales01.dbf

$

Now You can open the "salespdb" pluggable database.

SQL> alter pluggable database salespdb open;

Pluggable database altered.

SQL> 
SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 SALESPDB                       READ WRITE NO
         5 PDB2                           READ WRITE NO
         6 HRPDB                          READ WRITE NO

SQL> 

Fix: ORA-65086: cannot open/close the pluggable database

During cloning activity I unplugged a pdb and then try to open it but I faced "ORA-65086: cannot open/close the pluggable database  " error. I view Oracle document and found below concept to do the following steps:

-- Unplug a pluggable database

SQL> ALTER PLUGGABLE DATABASE hrpdb close immediate;

Pluggable database altered.

SQL> ALTER PLUGGABLE DATABASE hrpdb UNPLUG INTO '/u10/EXAM/bkp_pdb/hrpdb.xml';

Pluggable database altered.

Now you can see the status of pdbs.

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 PDB1                           READ WRITE NO

         4 SALESPDB                       READ WRITE NO

         5 PDB2                           READ WRITE NO

         6 HRPDB                          MOUNTED

SQL>

Here, I am trying to open the same database. But it is not opening due to unplugged.

SQL> alter pluggable database hrpdb open;

alter pluggable database hrpdb open

*

ERROR at line 1:

ORA-65086: cannot open/close the pluggable database

SQL>

It is not possible to open again the pluggable database… The official documentation claims:

After a PDB is unplugged, it remains in the CDB with an open mode of MOUNTED and a status of UNPLUGGED. The only operation you can perform on an unplugged PDB is DROP PLUGGABLE DATABASE, which will remove it from the CDB. You must drop the PDB before you can plug it into the same CDB or another CDB.

So, following official documentation recommendations:

SQL> drop pluggable database hrpdb;

Pluggable database dropped.

SQL> CREATE pluggable DATABASE hrpdb USING '/u10/EXAM/bkp_pdb/hrpdb.xml' NOCOPY;

Pluggable database created.

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 PDB1                           READ WRITE NO

         4 SALESPDB                       READ WRITE NO

         5 PDB2                           READ WRITE NO

         6 HRPDB                          MOUNTED

SQL> alter pluggable database hrpdb open;

Pluggable database altered.

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 PDB1                           READ WRITE NO

         4 SALESPDB                       READ WRITE NO

         5 PDB2                           READ WRITE NO

         6 HRPDB                          READ WRITE NO

SQL> 

Hope, It may helped you. 

Clone a PDB in different Container Database - Oracle 12c

Prerequisites:

The prerequisites for cloning a remote PDB or non-CDB are very similar, so I will deal with them together.

Source:

Create one pdb:
$ export ORACLE_SID=CDB
$ sqlplus / as sysdba

SQL> CREATE PLUGGABLE DATABASE hrpdb 
ADMIN USER hradmin IDENTIFIED BY hradmin
DEFAULT TABLESPACE hr 
DATAFILE '/u10/EXAM/ORADATA/CDB/hrpdb/hr01.dbf' SIZE 250M AUTOEXTEND ON
PATH_PREFIX = '/u10/EXAM/ORADATA/CDB/hrpdb/'
FILE_NAME_CONVERT = ('/u10/EXAM/ORADATA/CDB/pdbseed/', '/u10/EXAM/ORADATA/CDB/hrpdb/');

Pluggable database created.

Unplug the PDB in source:

SQL> ALTER PLUGGABLE DATABASE hrpdb close immediate;

SQL> ALTER PLUGGABLE DATABASE hrpdb UNPLUG INTO '/u10/EXAM/bkp_pdb/hrpdb.xml';

Pluggable database altered.

-- Copy the XML file along with datafiles to target host with creating paths:

$ cd /u10/EXAM/ORADATA/CDB/hrpdb
[oracle@eh-uatdb1 hrpdb]$ ls
hr01.dbf  pdbseed_temp012016-10-04_03-18-32-PM.dbf  sysaux01.dbf  system01.dbf

[oracle@eh-uatdb1 hrpdb]$ scp *.dbf oracle@172.18.1.18:/u10/EXAM/ORADATA/CDB/hrpdb

-- In target host create the folder structure for datafiles

$ cd /u10/EXAM/ORADATA/CDB/
[oracle@eh-uatdb2 CDB]$ mkdir hrpdb
[oracle@eh-uatdb2 CDB]$ cd hrpdb/
[oracle@eh-uatdb2 hrpdb]$ pwd
/u10/EXAM/ORADATA/CDB/hrpdb

Note : Give full permissions to .xml file in target host.

SQL> CREATE PLUGGABLE DATABASE hrpdb USING '/u10/EXAM/pdb_bkps/hrpdb.xml' NOCOPY TEMPFILE REUSE;

Pluggable database created.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 SALESPDB                       READ WRITE NO
         5 PDB2                           READ WRITE NO
         6 HRPDB                          MOUNTED
SQL> alter pluggable database HRPDB open;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 SALESPDB                       READ WRITE NO
         5 PDB2                           READ WRITE NO
         6 HRPDB                          READ WRITE NO
SQL>

Note :
1) The files are in the correct location. Therefore, NOCOPY is included
2) A file with the same name as the temp file specified in the XML file exists in the target location. Therefore, the TEMPFILE REUSE clause is required.

Here are other options to clone PDB in different hosts:

--  In case datafile location is changed in Target, the use below format:

CREATE PLUGGABLE DATABASE salespdb USING '/disk1/usr/salespdb.xml' 
  SOURCE_FILE_NAME_CONVERT = ('/disk1/oracle/oradata/sales/', '/disk2/oracle/oradata/sales/')
  NOCOPY TEMPFILE REUSE;

-- Plugging In an Unplugged PDB Using the AS CLONE and NOCOPY Clauses

CREATE PLUGGABLE DATABASE salespdb AS CLONE USING '/disk1/usr/salespdb.xml' 
  NOCOPY TEMPFILE REUSE;

-- Plugging In an Unplugged PDB With the COPY, PATH_PREFIX, and FILE_NAME_CONVERT Clauses

CREATE PLUGGABLE DATABASE salespdb USING '/disk1/usr/salespdb.xml' 
  COPY
  PATH_PREFIX = '/disk2/oracle/oradata/sales/'
  FILE_NAME_CONVERT = ('/disk1/oracle/oradata/sales/', '/disk2/oracle/oradata/sales/');

Note:

1) The XML file does not accurately describe the current locations of the files. Therefore, the SOURCE_FILE_NAME_CONVERT clause orSOURCE_FILE_DIRECTORY clause is required. In this example, the XML file indicates that the files are in /disk1/oracle/oradata/sales, but the files are in /disk2/oracle/oradata/sales, and the SOURCE_FILE_NAME_CONVERT clause is used.
2) The new PDB is based on the same unplugged PDB that was used to create an existing PDB in the CDB. Therefore, the AS CLONE clause is required. The AS CLONE clause ensures that the new PDB has unique identifiers.
3) The PDB's relative directory object paths must be treated as relative to a specific directory. Therefore, the PATH_PREFIX clause is required. In this example, the PDB's relative directory object paths must be treated as relative to the /disk2/oracle/oradata/sales directory and its subdirectories.

Restore and Recover entire CDB in different host using Oracle 12c

We will discuss:
1) Complete CDB restore in different host
and..
2) Create new PDBs
3) Change all PDB passwords for container in one go
4) 12c Networking, Connecting PDBs via oracle client

About CDB and PDB:

Recovery of CDB, ROOT and PDB Database in 12c can be performed at the CDB container level (with ALL PDBs), ROOT or as at the individual PDB level. For the recovery at the CDB level, obviously the backups for ALL the PDBs must be present. Conversely it does not matter whether the backups, were taken at the CDB level (with all PDBs) or backups performed for individual PDBs. RMAN will automatically choose the appropriate backup sets to recover some or all the databases, as instructed. Let’s look at how a recovery of a CDB and PDB can be performed in Oracle 12C, multi-tenant environment.

1) Complete CDB restore in different host

Source : 192.168.1.17 – hostname : uatdb1
Target : 192.168.1.18 – hostname : uatdb2 ( software installed only)

$ export ORACLE_HOME=/u01/oracle/app/product/12.1.0.2/dbhome_1
$ export PATH=$ORACLE_HOME/bin:$PATH
$ export ORACLE_SID=CDB

List the Pluggable databases- Source

$export ORACLE_SID=CDB
$ sqlplus / as sysdba
SQL> select DBID,NAME,OPEN_MODE from v$pdbs;

      DBID NAME                           OPEN_MODE
---------- ------------------------------ ----------
1401480341 PDB$SEED                       READ ONLY
 202699746 PDB1                           READ WRITE
  24383494 SALESPDB                       READ WRITE
2563786999 PDB2                           READ WRITE

OR
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 SALESPDB                       READ WRITE NO
         5 PDB2                           READ WRITE NO
SQL>

Take backup of Container Database (CDB):

[oracle@uatdb1 ~]$ rman target /

RMAN> backup database plus archivelog;

Starting backup at 07-OCT-2016 13:51:51
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=17 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=28 RECID=1 STAMP=924467464
input archived log thread=1 sequence=29 RECID=2 STAMP=924473335
input archived log thread=1 sequence=30 RECID=3 STAMP=924507007
input archived log thread=1 sequence=31 RECID=4 STAMP=924518991
input archived log thread=1 sequence=32 RECID=5 STAMP=924526780
input archived log thread=1 sequence=33 RECID=6 STAMP=924526948
input archived log thread=1 sequence=34 RECID=7 STAMP=924546618
input archived log thread=1 sequence=35 RECID=8 STAMP=924559473
input archived log thread=1 sequence=36 RECID=9 STAMP=924579215
input archived log thread=1 sequence=37 RECID=10 STAMP=924604282
input archived log thread=1 sequence=38 RECID=11 STAMP=924616313
channel ORA_DISK_1: starting piece 1 at 07-OCT-2016 13:51:55
channel ORA_DISK_1: finished piece 1 at 07-OCT-2016 13:52:02
piece handle=/u10/EXAM/fast_recovery_area/CDB/backupset/2016_10_07/o1_mf_annnn_TAG20161007T135154_czgps3md_.bkp tag=TAG20161007T135154 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 07-OCT-2016 13:52:02

Starting backup at 07-OCT-2016 13:52:02
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u10/EXAM/ORADATA/CDB/system01.dbf
input datafile file number=00003 name=/u10/EXAM/ORADATA/CDB/sysaux01.dbf
input datafile file number=00004 name=/u10/EXAM/ORADATA/CDB/undotbs01.dbf
input datafile file number=00006 name=/u10/EXAM/ORADATA/CDB/users01.dbf
channel ORA_DISK_1: starting piece 1 at 07-OCT-2016 13:52:03
channel ORA_DISK_1: finished piece 1 at 07-OCT-2016 13:52:28
piece handle=/u10/EXAM/fast_recovery_area/CDB/backupset/2016_10_07/o1_mf_nnndf_TAG20161007T135202_czgpscc9_.bkp tag=TAG20161007T135202 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00012 name=/u10/EXAM/ORADATA/CDB/salespdb/sysaux01.dbf
input datafile file number=00011 name=/u10/EXAM/ORADATA/CDB/salespdb/system01.dbf
input datafile file number=00013 name=/u10/EXAM/ORADATA/CDB/salespdb/sales01.dbf
channel ORA_DISK_1: starting piece 1 at 07-OCT-2016 13:52:28
channel ORA_DISK_1: finished piece 1 at 07-OCT-2016 13:52:43
piece handle=/u10/EXAM/fast_recovery_area/CDB/3E09A4600B4CDB6BE053110112ACCC54/backupset/2016_10_07/o1_mf_nnndf_TAG20161007T135202_czgpt4nq_.bkp tag=TAG20161007T135202 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00009 name=/u10/EXAM/ORADATA/CDB/pdb1/sysaux01.dbf
input datafile file number=00008 name=/u10/EXAM/ORADATA/CDB/pdb1/system01.dbf
input datafile file number=00010 name=/u10/EXAM/ORADATA/CDB/pdb1/pdb1_users01.dbf
channel ORA_DISK_1: starting piece 1 at 07-OCT-2016 13:52:43
channel ORA_DISK_1: finished piece 1 at 07-OCT-2016 13:53:08
piece handle=/u10/EXAM/fast_recovery_area/CDB/3E08803B3049DDBBE053110112AC46EF/backupset/2016_10_07/o1_mf_nnndf_TAG20161007T135202_czgptmyp_.bkp tag=TAG20161007T135202 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00015 name=/u10/EXAM/ORADATA/CDB/pdb2/sysaux01.dbf
input datafile file number=00014 name=/u10/EXAM/ORADATA/CDB/pdb2/system01.dbf
input datafile file number=00016 name=/u10/EXAM/ORADATA/CDB/pdb2/pdb1_users01.dbf
channel ORA_DISK_1: starting piece 1 at 07-OCT-2016 13:53:09
channel ORA_DISK_1: finished piece 1 at 07-OCT-2016 13:53:24
piece handle=/u10/EXAM/fast_recovery_area/CDB/3E09A4600B4EDB6BE053110112ACCC54/backupset/2016_10_07/o1_mf_nnndf_TAG20161007T135202_czgpvfc2_.bkp tag=TAG20161007T135202 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=/u10/EXAM/ORADATA/CDB/pdbseed/sysaux01.dbf
input datafile file number=00005 name=/u10/EXAM/ORADATA/CDB/pdbseed/system01.dbf
channel ORA_DISK_1: starting piece 1 at 07-OCT-2016 13:53:24
channel ORA_DISK_1: finished piece 1 at 07-OCT-2016 13:53:39
piece handle=/u10/EXAM/fast_recovery_area/CDB/3E07F6ABA9E6D2BCE053110112ACF374/backupset/2016_10_07/o1_mf_nnndf_TAG20161007T135202_czgpvwmd_.bkp tag=TAG20161007T135202 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 07-OCT-2016 13:53:39

Starting backup at 07-OCT-2016 13:53:39
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=39 RECID=12 STAMP=924616420
channel ORA_DISK_1: starting piece 1 at 07-OCT-2016 13:53:40
channel ORA_DISK_1: finished piece 1 at 07-OCT-2016 13:53:41
piece handle=/u10/EXAM/fast_recovery_area/CDB/backupset/2016_10_07/o1_mf_annnn_TAG20161007T135340_czgpwdtw_.bkp tag=TAG20161007T135340 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 07-OCT-2016 13:53:41

Starting Control File and SPFILE Autobackup at 07-OCT-2016 13:53:42
piece handle=/u10/EXAM/fast_recovery_area/CDB/autobackup/2016_10_07/o1_mf_s_924616422_czgpwj40_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 07-OCT-2016 13:53:45
RMAN>
 
Restore to Target - software installed only:

Prepare the Target database:

Create the directory structures as shown in pfile along with datafile paths.

e.g.,
$ mkdir /u10/EXAM/ORADATA/CDB
$ mkdir /u10/EXAM/ORADATA/CDB/pdbseed
$ mkdir /u10/EXAM/ORADATA/CDB/pdb1
$ mkdir /u10/EXAM/ORADATA/CDB/salespdb
$ mkdir /u10/EXAM/ORADATA/CDB/pdb2
$ mkdir –p /u01/oracle/app/admin/CDB/adump
$ mkdir –p /u10/EXAM/ORADATA/CDB
$ mkdir –p /u10/EXAM/fast_recovery_area/CDB

-- Create pfile :

*.audit_file_dest='/u01/oracle/app/admin/CDB/adump'
*.audit_trail='db'
*.compatible='12.1.0.2.0'
*.control_files='/u10/EXAM/ORADATA/CDB/control01.ctl','/u10/EXAM/fast_recovery_area/CDB/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='CDB'
*.db_recovery_file_dest='/u10/EXAM/fast_recovery_area'
*.db_recovery_file_dest_size=10737418240
*.diagnostic_dest='/u01/oracle/app'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=CDBXDB)'
*.enable_pluggable_database=true
*.memory_target=2729m
*.open_cursors=300
*.processes=500
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

--Export Home and Path

$ export ORACLE_HOME=/u01/oracle/app/product/12.1.0.2/dbhome_1
$ export PATH=$ORACLE_HOME/bin:$PATH
$ export ORACLE_SID=CDB
$ echo $ORACLE_HOME
/u01/oracle/app/product/12.1.0.2/dbhome_1
$ ls
initCDB.ora  init.ora

-- Start the database in nomout state

$ sqlplus / as sysdba
SQL> startup nomount pfile='initCDB.ora';
ORACLE instance started.

-- Move the backup prices and archivelogs from source to target
-- Restore control file

$ pwd
/u10/12c_backup/CDB/autobackup/2016_10_07
[oracle@uatdb2 2016_10_07]$

[oracle@uatdb2 2016_10_07]$ rman target /

RMAN> restore controlfile from '/u10/12c_backup/CDB/autobackup/2016_10_07/o1_mf_s_924616422_czgpwj40_.bkp';

Starting restore at 07-OCT-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=12 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u10/EXAM/ORADATA/CDB/control01.ctl
output file name=/u10/EXAM/fast_recovery_area/CDB/control02.ctl
Finished restore at 07-OCT-16

RMAN>

-- Mount the target system

RMAN> alter database mount;

Statement processed
released channel: ORA_DISK_1

RMAN>


-- Start Restore and Recover:

]$ pwd
/u10/12c_backup/
$ chmod –R 775 CDB
$cd CDB
$pwd
RMAN> catalog start with '/u10/12c_backup/CDB';

Starting implicit crosscheck backup at 07-OCT-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=12 device type=DISK
Crosschecked 14 objects
Finished implicit crosscheck backup at 07-OCT-16

Starting implicit crosscheck copy at 07-OCT-16
using channel ORA_DISK_1
Finished implicit crosscheck copy at 07-OCT-16

searching for all files in the recovery area
cataloging files...
no files cataloged

searching for all files that match the pattern /u10/12c_backup/CDB

List of Files Unknown to the Database
=====================================
File Name: /u10/12c_backup/CDB/autobackup/2016_10_07/o1_mf_s_924616422_czgpwj40_.bkp
File Name: /u10/12c_backup/CDB/3E09A4600B4CDB6BE053110112ACCC54/backupset/2016_10_07/o1_mf_nnndf_TAG20161007T135202_czgpt4nq_.bkp
File Name: /u10/12c_backup/CDB/3E08803B3049DDBBE053110112AC46EF/backupset/2016_10_07/o1_mf_nnndf_TAG20161007T135202_czgptmyp_.bkp
File Name: /u10/12c_backup/CDB/archivelog/2016_10_07/o1_mf_1_39_czgpwd65_.arc
File Name: /u10/12c_backup/CDB/3E09A4600B4EDB6BE053110112ACCC54/backupset/2016_10_07/o1_mf_nnndf_TAG20161007T135202_czgpvfc2_.bkp
File Name: /u10/12c_backup/CDB/backupset/2016_10_07/o1_mf_nnndf_TAG20161007T135202_czgpscc9_.bkp
File Name: /u10/12c_backup/CDB/backupset/2016_10_07/o1_mf_annnn_TAG20161007T135340_czgpwdtw_.bkp
File Name: /u10/12c_backup/CDB/3E07F6ABA9E6D2BCE053110112ACF374/backupset/2016_10_07/o1_mf_nnndf_TAG20161007T135202_czgpvwmd_.bkp

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u10/12c_backup/CDB/autobackup/2016_10_07/o1_mf_s_924616422_czgpwj40_.bkp
File Name: /u10/12c_backup/CDB/3E09A4600B4CDB6BE053110112ACCC54/backupset/2016_10_07/o1_mf_nnndf_TAG20161007T135202_czgpt4nq_.bkp
File Name: /u10/12c_backup/CDB/3E08803B3049DDBBE053110112AC46EF/backupset/2016_10_07/o1_mf_nnndf_TAG20161007T135202_czgptmyp_.bkp
File Name: /u10/12c_backup/CDB/archivelog/2016_10_07/o1_mf_1_39_czgpwd65_.arc
File Name: /u10/12c_backup/CDB/3E09A4600B4EDB6BE053110112ACCC54/backupset/2016_10_07/o1_mf_nnndf_TAG20161007T135202_czgpvfc2_.bkp
File Name: /u10/12c_backup/CDB/backupset/2016_10_07/o1_mf_nnndf_TAG20161007T135202_czgpscc9_.bkp
File Name: /u10/12c_backup/CDB/backupset/2016_10_07/o1_mf_annnn_TAG20161007T135340_czgpwdtw_.bkp
File Name: /u10/12c_backup/CDB/3E07F6ABA9E6D2BCE053110112ACF374/backupset/2016_10_07/o1_mf_nnndf_TAG20161007T135202_czgpvwmd_.bkp

RMAN>

-- Now start restore the Database

RMAN> restore database;

Starting restore at 07-OCT-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=12 device type=DISK

skipping datafile 1; already restored to file /u10/EXAM/ORADATA/CDB/system01.dbf
skipping datafile 3; already restored to file /u10/EXAM/ORADATA/CDB/sysaux01.dbf
skipping datafile 4; already restored to file /u10/EXAM/ORADATA/CDB/undotbs01.dbf
skipping datafile 6; already restored to file /u10/EXAM/ORADATA/CDB/users01.dbf
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00011 to /u10/EXAM/ORADATA/CDB/salespdb/system01.dbf
channel ORA_DISK_1: restoring datafile 00012 to /u10/EXAM/ORADATA/CDB/salespdb/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00013 to /u10/EXAM/ORADATA/CDB/salespdb/sales01.dbf
channel ORA_DISK_1: reading from backup piece /u10/12c_backup/CDB/3E09A4600B4CDB6BE053110112ACCC54/backupset/2016_10_07/o1_mf_nnndf_TAG20161007T135202_czgpt4nq_.bkp
channel ORA_DISK_1: piece handle=/u10/12c_backup/CDB/3E09A4600B4CDB6BE053110112ACCC54/backupset/2016_10_07/o1_mf_nnndf_TAG20161007T135202_czgpt4nq_.bkp tag=TAG20161007T135202
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00008 to /u10/EXAM/ORADATA/CDB/pdb1/system01.dbf
channel ORA_DISK_1: restoring datafile 00009 to /u10/EXAM/ORADATA/CDB/pdb1/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00010 to /u10/EXAM/ORADATA/CDB/pdb1/pdb1_users01.dbf
channel ORA_DISK_1: reading from backup piece /u10/12c_backup/CDB/3E08803B3049DDBBE053110112AC46EF/backupset/2016_10_07/o1_mf_nnndf_TAG20161007T135202_czgptmyp_.bkp
channel ORA_DISK_1: piece handle=/u10/12c_backup/CDB/3E08803B3049DDBBE053110112AC46EF/backupset/2016_10_07/o1_mf_nnndf_TAG20161007T135202_czgptmyp_.bkp tag=TAG20161007T135202
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00014 to /u10/EXAM/ORADATA/CDB/pdb2/system01.dbf
channel ORA_DISK_1: restoring datafile 00015 to /u10/EXAM/ORADATA/CDB/pdb2/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00016 to /u10/EXAM/ORADATA/CDB/pdb2/pdb1_users01.dbf
channel ORA_DISK_1: reading from backup piece /u10/12c_backup/CDB/3E09A4600B4EDB6BE053110112ACCC54/backupset/2016_10_07/o1_mf_nnndf_TAG20161007T135202_czgpvfc2_.bkp
channel ORA_DISK_1: piece handle=/u10/12c_backup/CDB/3E09A4600B4EDB6BE053110112ACCC54/backupset/2016_10_07/o1_mf_nnndf_TAG20161007T135202_czgpvfc2_.bkp tag=TAG20161007T135202
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00005 to /u10/EXAM/ORADATA/CDB/pdbseed/system01.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u10/EXAM/ORADATA/CDB/pdbseed/sysaux01.dbf
channel ORA_DISK_1: reading from backup piece /u10/12c_backup/CDB/3E07F6ABA9E6D2BCE053110112ACF374/backupset/2016_10_07/o1_mf_nnndf_TAG20161007T135202_czgpvwmd_.bkp
channel ORA_DISK_1: piece handle=/u10/12c_backup/CDB/3E07F6ABA9E6D2BCE053110112ACF374/backupset/2016_10_07/o1_mf_nnndf_TAG20161007T135202_czgpvwmd_.bkp tag=TAG20161007T135202
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 07-OCT-16

RMAN>

-- Now recover the database

RMAN> recover database;

Starting recover at 07-OCT-16
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 39 is already on disk as file /u10/12c_backup/CDB/archivelog/2016_10_07/o1_mf_1_39_czgpwd65_.arc
archived log file name=/u10/12c_backup/CDB/archivelog/2016_10_07/o1_mf_1_39_czgpwd65_.arc thread=1 sequence=39
unable to find archived log
archived log thread=1 sequence=40
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 10/07/2016 14:37:21
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 40 and starting SCN of 2009009

-- Do more recovery

RMAN> catalog start with '/u10/12c_backup/CDB/archivelog/2016_10_07';

searching for all files that match the pattern /u10/12c_backup/CDB/archivelog/2016_10_07

List of Files Unknown to the Database
=====================================
File Name: /u10/12c_backup/CDB/archivelog/2016_10_07/o1_mf_1_43_czgs511n_.arc
File Name: /u10/12c_backup/CDB/archivelog/2016_10_07/o1_mf_1_41_czgs4xcm_.arc
File Name: /u10/12c_backup/CDB/archivelog/2016_10_07/o1_mf_1_42_czgs501k_.arc
File Name: /u10/12c_backup/CDB/archivelog/2016_10_07/o1_mf_1_40_czgs4wbm_.arc

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u10/12c_backup/CDB/archivelog/2016_10_07/o1_mf_1_43_czgs511n_.arc
File Name: /u10/12c_backup/CDB/archivelog/2016_10_07/o1_mf_1_41_czgs4xcm_.arc
File Name: /u10/12c_backup/CDB/archivelog/2016_10_07/o1_mf_1_42_czgs501k_.arc
File Name: /u10/12c_backup/CDB/archivelog/2016_10_07/o1_mf_1_40_czgs4wbm_.arc

RMAN> recover database;

Starting recover at 07-OCT-16
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 40 is already on disk as file /u10/12c_backup/CDB/archivelog/2016_10_07/o1_mf_1_40_czgs4wbm_.arc
archived log for thread 1 with sequence 41 is already on disk as file /u10/12c_backup/CDB/archivelog/2016_10_07/o1_mf_1_41_czgs4xcm_.arc
archived log for thread 1 with sequence 42 is already on disk as file /u10/12c_backup/CDB/archivelog/2016_10_07/o1_mf_1_42_czgs501k_.arc
archived log for thread 1 with sequence 43 is already on disk as file /u10/12c_backup/CDB/archivelog/2016_10_07/o1_mf_1_43_czgs511n_.arc
archived log file name=/u10/12c_backup/CDB/archivelog/2016_10_07/o1_mf_1_40_czgs4wbm_.arc thread=1 sequence=40
archived log file name=/u10/12c_backup/CDB/archivelog/2016_10_07/o1_mf_1_41_czgs4xcm_.arc thread=1 sequence=41
archived log file name=/u10/12c_backup/CDB/archivelog/2016_10_07/o1_mf_1_42_czgs501k_.arc thread=1 sequence=42
archived log file name=/u10/12c_backup/CDB/archivelog/2016_10_07/o1_mf_1_43_czgs511n_.arc thread=1 sequence=43
unable to find archived log
archived log thread=1 sequence=44
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 10/07/2016 14:40:36
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 44 and starting SCN of 2010643

RMAN>

-- Verify the status in the Target Host

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       MOUNTED
         3 PDB1                           MOUNTED
         4 SALESPDB                       MOUNTED
         5 PDB2                           MOUNTED
SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
CDB       MOUNTED

SQL>

-- Now open the container database with RESETLOG.

SQL> alter database open resetlogs;

Database altered.

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           MOUNTED
         4 SALESPDB                       MOUNTED
         5 PDB2                           MOUNTED

-- Now you can open your pdbs also.

SQL> alter pluggable database PDB1 open;

Pluggable database altered.

SQL> show pdbs; 

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 SALESPDB                       MOUNTED
         5 PDB2                           MOUNTED
SQL> alter pluggable database PDB2 open;

Pluggable database altered.

SQL> alter pluggable database SALESPDB open;

Pluggable database altered.


SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 SALESPDB                       READ WRITE NO
         5 PDB2                           READ WRITE NO
SQL>


2) Create new PDBs

Create Pluggable database via scripts with different options:

$export ORACLE_SID=CDB
SQL> select name,open_mode from v$database;
NAME      OPEN_MODE
--------- --------------------
CDB       READ WRITE

SQL> CREATE PLUGGABLE DATABASE salespdb ADMIN USER salesadmin IDENTIFIED BY salesadmin
  DEFAULT TABLESPACE sales 
    DATAFILE '/u10/EXAM/ORADATA/CDB/salespdb/sales01.dbf' SIZE 250M AUTOEXTEND ON
  PATH_PREFIX = '/u10/EXAM/ORADATA/CDB/salespdb/'
  FILE_NAME_CONVERT = ('/u10/EXAM/ORADATA/CDB/pdbseed/', '/u10/EXAM/ORADATA/CDB/salespdb/');

Pluggable database created.

SQL> select DBID,NAME,OPEN_MODE from v$pdbs;

      DBID NAME                           OPEN_MODE
---------- ------------------------------ ----------
1401480341 PDB$SEED                       READ ONLY
 202699746 PDB1                           READ WRITE
  24383494 SALESPDB                       MOUNTED

SQL>

--- Now open the sales pdb:

SQL> ALTER PLUGGABLE DATABASE salespdb OPEN;

Pluggable database altered.

SQL> select DBID,NAME,OPEN_MODE from v$pdbs;

      DBID NAME                           OPEN_MODE
---------- ------------------------------ ----------
1401480341 PDB$SEED                       READ ONLY
 202699746 PDB1                           READ WRITE
  24383494 SALESPDB                       READ WRITE

SQL>

-- Clone pdb2 from exting pdb1:

CREATE PLUGGABLE DATABASE pdb2 FROM pdb1
FILE_NAME_CONVERT=(‘/u10/EXAM/ORADATA/CDB/pdb1/’,’/u10/EXAM/ORADATA/CDB/pdb2/’);

Example:
SQL> CREATE PLUGGABLE DATABASE pdb2 FROM pdb1
FILE_NAME_CONVERT=('/u10/EXAM/ORADATA/CDB/pdb1/','/u10/EXAM/ORADATA/CDB/pdb2/');

Pluggable database created.

SQL>
SQL> select DBID,NAME,OPEN_MODE from v$pdbs;

      DBID NAME                           OPEN_MODE
---------- ------------------------------ ----------
1401480341 PDB$SEED                       READ ONLY
 202699746 PDB1                           READ WRITE
  24383494 SALESPDB                       READ WRITE
2563786999 PDB2                           MOUNTED

SQL> alter pluggable database pdb2 open;
Pluggable database altered.

SQL> select DBID,NAME,OPEN_MODE from v$pdbs;

      DBID NAME                           OPEN_MODE
---------- ------------------------------ ----------
1401480341 PDB$SEED                       READ ONLY
 202699746 PDB1                           READ WRITE
  24383494 SALESPDB                       READ WRITE
2563786999 PDB2                           READ WRITE

SQL>

3) Change all PDB passwords for container in one go:

How to change Pluggable DB's SYS password?

-- To set a common password for SYS and SYSTEM users across all PDBs

SQL> alter user SYS identified by oracle container=all;
User altered.

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
CDB       READ WRITE
SQL>

-- Switch to different container

SQL> alter session set container = PDB1;
Session altered.

SQL> show con_name  
CON_NAME
------------------------------
PDB1

SQL> alter session set container = PDB2;
Session altered.

SQL> show con_name

CON_NAME
------------------------------
PDB2
SQL>

4) 12c Networking, Connecting PDBs via oracle client

Connecting to individual PDB via Oracle Client:

-- Configure / Create listener in server

LISTENER1 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = eh-uatdb1)(PORT = 1521))

  )

SID_LIST_LISTENER1 =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = CDB)

      (ORACLE_HOME = /u01/oracle/app/product/12.1.0.2/dbhome_1)

      (SID_NAME = CDB)

    )

    (SID_DESC =

      (SID_NAME = pdb1)

      (ORACLE_HOME = /u01/oracle/app/product/12.1.0.2/dbhome_1)

    )

   (SID_DESC =

      (SID_NAME = pdb2)

      (ORACLE_HOME = /u01/oracle/app/product/12.1.0.2/dbhome_1)

    )

   (SID_DESC =

      (SID_NAME = salespdb)

      (ORACLE_HOME = /u01/oracle/app/product/12.1.0.2/dbhome_1)

    )

   (SID_DESC =

      (SID_NAME = hrpdb)

      (ORACLE_HOME = /u01/oracle/app/product/12.1.0.2/dbhome_1)

    )

  )

ADR_BASE_LISTENER1 = /u01/oracle/app

[oracle@eh-uatdb1 admin]$

-- Configure tnsnames.ora in client:

CDB =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.18.1.17)(PORT = 1522))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = CDB)

    )

  )

HRPDB =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.18.1.17)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = HRPDB)

    )

  )

pdb1 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.18.1.17)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = pdb1)

    )

  )

pdb2 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.18.1.17)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = pdb2)

    )

  )

salespdb =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.18.1.17)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = salespdb)

    )

  )

-- Sample connection:

C:\> sqlplus sys/oracle@pdb1 as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Oct 7 17:27:57 2016

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

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>