enable pluggable database(PDB) archivelog mode

Enabling archive log mode -12c pluggable database/Container database.

Since the Redologs are created at container database level in 12c and not at pluggable database level.
(Enabling archivelog at pluggable database level is not possible). Archiving is done at CDB's.

You can check archive log mode either by querying v$database or archivelog list

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

NAME      OPEN_MODE            LOG_MODE
--------- -------------------- ------------
CDB      READ WRITE           NOARCHIVELOG

(OR)

SQL> archive log list
Database log mode        Archive Mode
Automatic archival        Disabled
Archive destination        USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     11
Next log sequence to archive   13
Current log sequence        13

***************  ***************
To enable the Archive-log mode
***************  ***************

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

SQL> startup mount;
ORACLE instance started.

Total System Global Area  788529152 bytes
Fixed Size      2929352 bytes
Variable Size    541068600 bytes
Database Buffers   239075328 bytes
Redo Buffers      5455872 bytes
Database mounted.

SQL> ALTER DATABASE ARCHIVELOG;

Database altered.

SQL> ALTER DATABASE OPEN;

Database altered.

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

NAME      OPEN_MODE            LOG_MODE
--------- -------------------- ------------
CDB      READ WRITE           ARCHIVELOG



SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     413
Next log sequence to archive   415
Current log sequence           415

SQL> show parameter DB_RECOVERY_FILE_DEST

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u03/app/oracle/fast_recovery_area
db_recovery_file_dest_size           big integer 27G

PDB Clone steps



                                                       PDB Clone steps
Clone Oracle 12c Pluggable database from one Oracle Cloud Service to another

We can have multiple options for doing this data migration. For our example we will use remote cloning of a PDB using Database Links.
Note that this post will use the unique features of Oracle Database 12c Multitenant and Pluggable Databases (PDB).
This was a new and the key feature of Oracle 12c which is not present in Oracle database version 11g.
With PDBs in 12c you have got ease of data migration.
You may have to move PDBs between test and production environments in your live environments and this process makes data migration a simple process.

Source Database:
Database Cloud Service: Production
PDB Name to be cloned: PROD01
Target Database:
Database Cloud Service: SUJEET
Activity
To clone PROD01 PDB from ‘Production’ database service to ‘SUJEET’ database service.
Below is the series of steps that will be followed to accomplish this goal.
STEP 1 : Enable communication between Source and Target by Security Rules
STEP 2 : Add Source PDB TNS Entry in Target tnsnames.ora
STEP 3 : Create DBLINK in Target to connect to Source PDB
STEP 4 : Change the Source wallet from 'Autologin' to 'Password'
STEP 5 : Export the keys for Source PDB from Source Wallet 
STEP 6 : Transfer the exported keys from Source to Target server
STEP 7 : Change the Target wallet from 'Autologin' to 'Password'
STEP 8 : Import the copied keys into Target Database Wallet 
STEP 9 : Set the source PDB to read only mode
STEP 10 : Clone the Source PDB into Target database 
STEP 11 : Revert back the source PDB to READ WRITE Mode
STEP 12 : Revert back the Source wallet from 'Password' to 'Autologin'
STEP 13 : Revert back the Target wallet from 'Password' to 'Autologin'
STEP 14 : Verify the newly cloned Target PDB

STEP 1 : Enable communication between Source and Target by Security Rules
From the Oracle Compute Cloud Service Dashboard navigate to the Network > Security Rules, and create a new rule.
http://expertoracle.com/wp-content/uploads/2017/01/img_586d9b38659e8.png
Input the below parameters as shown:
Give your security rule a name, and make sure it is enabled. This Security Rule is based on the Security Application Production/db_1/ora_listener, which opens the port 1521. The Source is brijesh/db_1/ora_db, and the Destination is Production/db_1/ora_db.
http://expertoracle.com/wp-content/uploads/2017/01/img_586d9c0c37cc2.png
You will be able to see the new security rule in the list.
http://expertoracle.com/wp-content/uploads/2017/01/img_586d9d812ccd2.png
STEP 2 : Add Source PDB TNS Entry in Target tnsnames.ora
Open Putty connections to both ‘Production’ and ‘Brijesh’ compute instances
We will add source PDB tns entry, which is PRODPDB1 in our case. In the tns entry we will put private IP of the Production servers. Private IP can be easily located at the Compute Cloud Console in the instance information page or you can also run “ifconfig eth0” command from the unix command prompt to get it.
Go to $ORACLE_HOME/netowrk/admin and added below tns entry to the tnsnames.ora
PRODPDB1_CLONE =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = 10.196.139.62)(PORT = 1521))
 (CONNECT_DATA =
 (SERVER = DEDICATED)
 (SERVICE_NAME = prodpdb1.bgogia.oraclecloud.internal)
 )
 )
Verified if the connection is successful by tnsping command
http://expertoracle.com/wp-content/uploads/2017/01/img_586da154d2543.png

STEP 3 : Create DBLINK in Target to connect to Source PDB
Now log into the database  ‘Brijesh’ using SQL*Plus to create the Database Link that will be used to create a new PDB in this database.
$ sqlplus / as sysdba

SQL> set lines 132
col owner format a8
col DB_LINK format a40
col USERNAME format a10
col HOST format a25

select * from dba_db_links;

SQL> create database link db_link1 connect to system identified by <production_system_password> using 'PRODPDB1_CLONE';

SQL> select * from dba_db_links;

Verify if DB LINK is working OK
SQL> select name from v$database@DB_LINK1.BGOGIA.ORACLECLOUD.INTERNAL;

NAME
---------
PROD

SQL> exit

STEP 4 : Change the Source wallet from ‘Autologin’ to ‘Password’
The Oracle Public Cloud uses Transparent Data Encryption to secure the datafiles. When you move the pluggable databases you need to export/import the encryption keys. Before we can export the keys from the source PDB we need to first change the wallet type to ‘Password’ from ‘Autologin’
First keep the backup of /u01/app/oracle/admin/PROD/tde_wallet/cwallet.sso file
SQL> select WRL_TYPE, WRL_PARAMETER, STATUS, WALLET_TYPE from v$encryption_wallet;

WRL_TYPE     WRL_PARAMETER                          WALLET_TYPE
---------- ---------------------------------------- ----------
FILE        /u01/app/oracle/admin/PROD/tde_wallet/   AUTOLOGIN


SQL> administer key management set keystore close;
keystore altered.

SQL> administer key management set keystore open identified by "vana_05Nir" container=all;
keystore altered.

SQL> select wrl_type,wrl_parameter,wallet_type from v$encryption_wallet;

WRL_TYPE          WRL_PARAMETER                              WALLET_TYPE
-------------  -------------------------------------------  --------------------
FILE             /u01/app/oracle/admin/PROD/tde_wallet/       PASSWORD


STEP 5 : Export the keys for Source PDB from Source Wallet
In this step we will export the key from Source PDB in a temporary wallet file
Connect to Source PDB using SQLPLUS
SQL> administer key management export encryption keys with secret "<put_password>" to '/tmp/prodpdb1_2.p12'
     identified by "<put_password>" with identifier in
     (select key_id from v$encryption_keys where creator_pdbname='PRODPDB1');

keystore altered.
/tmp/prodpdb1_2.p12 will have the required key
STEP 6 : Transfer the exported keys from Source to Target server
In this step we will copy the /tmp/prodpdb1_2.p12 from Source server to Target server using WINSCP software
Connect to WINSCP and provide all the below required details to connect to your Oracle Cloud Servers
http://expertoracle.com/wp-content/uploads/2017/01/img_586ddb26c1376.png
STEP 7 : Change the Target wallet from ‘Autologin’ to ‘Password’
Now we have to open the wallet with password because the key that we brought in from Source server cannot be imported when wallet type is ‘autologin’
First keep the backup of /u01/app/oracle/admin/brijesh/tde_wallet/cwallet.sso file
Run below command by connecting to target PDB using SQLPLUS
SQL> administer key management set keystore close;
keystore altered.

SQL> administer key management set keystore open identified by "<put_password>";
keystore altered.

SQL> select wrl_type,wrl_parameter,wallet_type from v$encryption_wallet;

WRL_TYPE          WRL_PARAMETER                                     WALLET_TYPE
-------------  ---------------------------------------------------  ---------------------------------
FILE             /u01/app/oracle/admin/brijesh/tde_wallet/           PASSWORD

STEP 8 : Import the copied keys into Target Database Wallet
Run below command by connecting to target PDB using SQLPLUS
SQL> administer key management import encryption keys with secret "<put_password>" from
   '/tmp/prodpdb1_2.p12' identified by "<put_password>" WITH BACKUP USING '/tmp/tde_backup_001';
keystore altered.

STEP 9 : Set the source PDB to read only mode
Now from PuTTY we will connect to our ‘Production’ database service using SQL*Plus and set the source PRODPDB1 pluggable databsae to READ ONLY mode in the Oracle Database Cloud Service
$ sqlplus / as sysdba

SQL> show pdbs

CON_ID      CON_NAME                      OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
 2          PDB$SEED                      READ ONLY    NO
 3          PRODPDB1                      READ WRITE   NO

SQL> alter pluggable database PRODPDB1 close immediate;

Pluggable database altered.

SQL> show pdbs

CON_ID        CON_NAME                     OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
 2            PDB$SEED                    READ ONLY   NO
 3            PRODPDB1                    MOUNTED

SQL> alter pluggable database PRODPDB1 open read only;
Pluggable database altered.

SQL> show pdbs

CON_ID       CON_NAME                     OPEN MODE   RESTRICTED
---------- ------------------------------ ---------- ----------
 2          PDB$SEED                      READ ONLY    NO
 3          PRODPDB1                      READ ONLY    NO

SQL> EXIT;


STEP 10 : Clone the Source PDB into Target database
Now from PuTTY we will connect to our ‘brijesh’ database service using SQL*Plus and clone the PRODPDB1 to the ‘brijesh’ database service.
$ sqlplus / as sysdba

SQL> create pluggable database clone_pdb from PRODPDB1@DB_LINK1 keystore identified by "<put_password>";
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         CLONE_PDB                       MOUNTED

SQL> alter pluggable database CLONE_PDB 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         CLONE_PDB                      READ WRITE   NO

SQL> select name, status, message, action from pdb_plug_in_violations where name='CLONE_PDB';
no rows selected

STEP 11 : Revert back the source PDB to READ WRITE Mode
Execute on the Source database from SQLPLUS
SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
 2 PDB$SEED READ ONLY NO
 3 PRODPDB1 READ ONLY NO
SQL> alter pluggable database PRODPDB1 close immediate;
Pluggable database altered.

SQL> alter pluggable database PRODPDB1 open;

Pluggable database altered.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
 2 PDB$SEED READ ONLY NO
 3 PRODPDB1 READ WRITE NO

STEP 12 : Revert back the Source wallet from ‘Password’ to ‘Autologin’
We will now revert the container wallet from Password to Autologin type. To accomplish this, we reintroduce the cwallet.sso file and then restart the Container Database.
$ mv /u01/app/oracle/admin/PROD/tde_wallet/cwallet.sso_bkp /u01/app/oracle/admin/PROD/tde_wallet/cwallet.sso

[oracle@Production admin]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Thu Jan 5 04:47:49 2017
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

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

Total System Global Area 2667577344 bytes
Fixed Size 8796336 bytes
Variable Size 687867728 bytes
Database Buffers 1962934272 bytes
Redo Buffers 7979008 bytes
Database mounted.
Database opened.

SQL> select wrl_type,wrl_parameter,wallet_type from v$encryption_wallet;

WRL_TYPE          WRL_PARAMETER                              WALLET_TYPE
-------------  -------------------------------------------  --------------------
FILE             /u01/app/oracle/admin/PROD/tde_wallet/       AUTLOGIN

STEP 13 : Revert back the Target wallet from ‘Password’ to ‘Autologin’
Similarly to the previous step we will revert back  the Target wallet to ‘autologin’
SQL> select wrl_type,wrl_parameter,wallet_type from v$encryption_wallet;

WRL_TYPE          WRL_PARAMETER                              WALLET_TYPE
-------------  -------------------------------------------  --------------------
FILE             /u01/app/oracle/admin/brijesh/tde_wallet/       AUTLOGIN

STEP 14 : Verify the newly cloned Target PDB
Connect to newly cloned PDB in your target container database and verify
[oracle@brijesh ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Thu Jan 5 04:58:46 2017
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show pdbs

CON_ID      CON_NAME                      OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
 2         PDB$SEED                       READ ONLY    NO
 3         PDB1                           READ WRITE   YES
 4         CLONE_PDB                      READ WRITE   NO

SQL> sho con_name
CON_NAME
------------------------------
CDB$ROOT

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

SQL> sho con_name
CON_NAME
------------------------------
CLONE_PDB

SQL> create table dummy_CLONE_PDB(x number);
Table created.

SQL> insert into dummy_CLONE_PDB values(1);
1 row created.

SQL> commit;
Commit complete.

This completes the cloning of Oracle 12c Pluggable database from one Oracle Cloud Service to another
Oracle Database 12.1.0.2c: Hot Cloning of Pluggable Databases
Cloning of a Pluggable Database (PDB) in Oracle Multitenant is a great feature and is very useful. Oracle Database 12c Release 1 has introduced various enhancements to the cloning of Pluggable Databases to overcome the limitations imposed by the initial release of Oracle Database 12c (12.1.0.1). In Oracle 12.1.0.2c: PDBs can be hot cloned, i.e. you don’t need to put the source PDB

Cloning of a Pluggable Database (PDB) in Oracle Multitenant is a great feature and is very useful. Oracle Database 12c Release 1 has introduced various enhancements to the cloning of Pluggable Databases to overcome the limitations imposed by the initial release of Oracle Database 12c (12.1.0.1). In Oracle 12.1.0.2c:

PDBs can be hot cloned, i.e. you don’t need to put the source PDB in read-only for cloning so that it can be cloned to a CDB online. In Oracle 12.1.0.1c, a pluggable database can be cloned only if it is read-only.
PDBs can be remote cloned, i.e. the source PDB may be located in a remote container. This feature was listed in Oracle Database 12c Release 1 (12.1.0.1), but didn’t work.
There is no need to create the directories where the database files of clone PDB will be placed. If the directory is not already present, it will be automatically created as part of the cloning operation.
In this article, I will demonstrate hot cloning of a PDB locally as well as remotely. For the purpose of this demonstration, I have two virtual machines, each with a container database.

Current scenario:
Host: host01
Container Database 12.1.0.2c: cdb1 with pluggable database pdb1

Host: host02
Container Database 12.1.0.2c: destcdb with pluggable database pdb1

Demonstration 1: Hot cloning of PDB locally
We will clone the pluggable database pdb1 to pdb1clone in the same CDB, i.e. cdb1 on host host01

Source host: host01
Source PDB: pdb1 in container database cdb1

Destination host: host01
Destination PDB: pdb1clone in container database cdb1

Currently, there is only one PDB called pdb1 currently open in READ WRITE mode in the container database cdb1.

CDB1>select name, cdb from v$database;

CDB1>select name, cdb from v$database;
NAME      CDB
--------- ---
CDB1      YES

CDB1>select con_id, name, open_mode from v$pdbs;
    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         2 PDB$SEED                       READ ONLY
         3 PDB1                           READ WRITE

CDB1>select name from v$datafile where con_id = 3;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/cdb1/pdb1/system01.dbf
/u01/app/oracle/oradata/cdb1/pdb1/sysaux01.dbf
/u01/app/oracle/oradata/cdb1/pdb1/SAMPLE_SCHEMA_users01.dbf
/u01/app/oracle/oradata/cdb1/pdb1/example01.dbf
Verify that directory corresponding to data files of target PDB, i.e. pdb1clone is not present:

[oracle@host01 cdb1]$ ls -l /u01/app/oracle/oradata/cdb1/
total 1782620
-rw-r----- 1 oracle asmadmin  17973248 Jul 26 15:03 control01.ctl
drwxr-x--- 2 oracle oinstall      4096 Jun 29 12:06 pdb1
drwxr-x--- 2 oracle oinstall      4096 Jun 29 11:59 pdbseed
-rw-r----- 1 oracle asmadmin  52429312 Jul 25 15:18 redo01.log
-rw-r----- 1 oracle asmadmin  52429312 Jul 25 15:18 redo02.log
-rw-r----- 1 oracle asmadmin  52429312 Jul 26 15:03 redo03.log
-rw-r----- 1 oracle asmadmin 650125312 Jul 26 15:02 sysaux01.dbf
-rw-r----- 1 oracle asmadmin 817897472 Jul 26 15:02 system01.dbf
-rw-r----- 1 oracle asmadmin  62922752 Jul 26 15:02 temp01.dbf
-rw-r----- 1 oracle asmadmin 173023232 Jul 26 15:01 undotbs01.dbf
-rw-r----- 1 oracle asmadmin   5251072 Jul 25 15:18 users01.dbf

[oracle@host01 cdb1]$ ls -l /u01/app/oracle/oradata/cdb1/ | grep pdb1
drwxr-x--- 2 oracle oinstall      4096 Jun 29 12:06 pdb1
Using the CREATE PLUGGABLE DATABASE ... FROM command we will clone the existing PDB (pdb1) to create a new PDB (pdb1clone) in the same container database (cdb1). This statement copies the files associated with the source PDB to a new location and associates the files with the target PDB.

CDB1>create pluggable database pdb1clone from pdb1
     file_name_convert = ('pdb1','pdb1clone');
Pluggable database created.
We can see that the new PDB called pdb1clone is in MOUNTED state when created and is opened successfully thereafter.

CDB1>sho pdbs

CDB1>sho pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB1CLONE                      MOUNTED

CDB1>alter pluggable database pdb1clone open;
Pluggable database altered.

CDB1>sho pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB1CLONE                      READ WRITE NO
CDB1>alter session set container=pdb1clone;
Session altered.

CDB1>sho con_name
CON_NAME
------------------------------
PDB1CLONE

CDB1>select count(*) from hr.employees;
  COUNT(*)
----------
       107
Note that the directory for the data files of the clone PDB pdb1clone has been created automatically in the location specified using FILE_NAME_CONVERT.

[oracle@host01 cdb1]$ ls -l /u01/app/oracle/oradata/cdb1/pdb1clone

[oracle@host01 cdb1]$ ls -l /u01/app/oracle/oradata/cdb1/pdb1clone
total 2089832
-rw-r----- 1 oracle asmadmin 1304174592 Jul 25 15:53 example01.dbf
-rw-r----- 1 oracle asmadmin   20979712 Jul 25 15:52 pdb1clone_temp012016-06-29_12-06-27-PM.dbf
-rw-r----- 1 oracle asmadmin    5251072 Jul 25 15:53 SAMPLE_SCHEMA_users01.dbf
-rw-r----- 1 oracle asmadmin  555753472 Jul 25 15:53 sysaux01.dbf
-rw-r----- 1 oracle asmadmin  272637952 Jul 25 15:53 system01.dbf
Hence, we have been able to hot clone a PDB locally without:

Placing the source PDB in READ ONLY mode
Creating the directory for the destination PDB
Demonstration 2: Hot cloning of PDB remotely
We will clone the pluggable database pdb1 in CDB cdb1 on host host01 to pdb1new in another CDB, i.e. destcdb on host host02:

Source host: host01
Source PDB: pdb1 in container database cdb1

Destination host: host02
Destination PDB: pdb1new in container database destcdb

Currently, there is only one PDB called pdb1 open in READ WRITE mode in destination container database destcdb:

DESTCDB>sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
On the target container database destcdb, we need to create the database link to connect to source container database cdb1 which will be used in the CREATE PLUGGABLE DATABASE.

DESTCDB>create database link cdb1_link
        connect to system identified by oracle using 'host01:1521/cdb1';
Database link created.
Verify that the source pluggable database (pdb1@cdb1) that we want to clone is in READ WRITE mode.

CDB1> select con_id, name, open_mode from v$pdbs where name = 'PDB1';

    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         3 PDB1                           READ WRITE
Let’s execute the CREATE PLUGGABLE DATABASE statement using the database link (cdb1_link) as previously defined.

DESTCDB> create pluggable database pdb1new from pdb1@cdb1_link;
 create pluggable database pdb1new from pdb1@cdb1_link
                                                     *
ERROR at line 1:
ORA-65016: FILE_NAME_CONVERT must be specified
Let’s find out location of datafiles for pdb1@cdb1 on host01:

CDB1>alter session set container = pdb1;
Session altered.
CDB1>select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/cdb1/undotbs01.dbf
/u01/app/oracle/oradata/cdb1/pdb1/system01.dbf
/u01/app/oracle/oradata/cdb1/pdb1/sysaux01.dbf
/u01/app/oracle/oradata/cdb1/pdb1/SAMPLE_SCHEMA_users01.dbf
/u01/app/oracle/oradata/cdb1/pdb1/example01.dbf
Verify that directory corresponding to data files of target PDB, i.e. pdb1new, is not present on the target host host02:

[oracle@host02 destcdb]$ ls -l /u01/app/oracle/oradata/destcdb
total 1761816
-rw-r----- 1 oracle asmadmin  17973248 Jul 25 15:35 control01.ctl
drwxr-x--- 2 oracle oinstall      4096 Jul 24 16:04 PDB1
drwxr-x--- 2 oracle oinstall      4096 Jul 24 15:57 pdbseed
-rw-r----- 1 oracle asmadmin  52429312 Jul 24 16:09 redo01.log
-rw-r----- 1 oracle asmadmin  52429312 Jul 25 15:35 redo02.log
-rw-r----- 1 oracle asmadmin  52429312 Jul 24 16:08 redo03.log
-rw-r----- 1 oracle asmadmin 629153792 Jul 25 15:34 sysaux01.dbf
-rw-r----- 1 oracle asmadmin 817897472 Jul 25 15:35 system01.dbf
-rw-r----- 1 oracle asmadmin  62922752 Jul 25 15:26 temp01.dbf
-rw-r----- 1 oracle asmadmin 173023232 Jul 25 15:35 undotbs01.dbf
-rw-r----- 1 oracle asmadmin   5251072 Jul 25 15:19 users01.dbf
Let’s specify FILE_NAME_CONVERT and re-execute the CREATE PLUGGABLE DATABASE statement using the database link (cdb1_link) we previously defined:

DESTCDB>create pluggable database pdb1new from pdb1@cdb1_link
        file_name_convert = ('/u01/app/oracle/oradata/cdb1/pdb1','/u01/app/oracle/oradata/destcdb/pdb1new');
Pluggable database created.
By default the new pluggable database is created in MOUNTED state and can be opened.

DESTCDB>sho pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB1NEW                        MOUNTED

DESTCDB>alter pluggable database pdb1new open;
Pluggable database altered.

DESTCDB>sho pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB1NEW                        READ WRITE NO
DESTCDB>alter session set container=pdb1new;
Session altered.
DESTCDB>select count(*) from hr.employees;
  COUNT(*)
----------
       107
Verify that the directory for data files of pbdnew has been created automatically on host02 in the location specified using FILE_NAME_CONVERT:

[oracle@host02 pdb1new]$ ls -l /u01/app/oracle/oradata/destcdb/pdb1new
total 2089832
-rw-r----- 1 oracle asmadmin 1304174592 Jul 25 15:41 example01.dbf
-rw-r----- 1 oracle asmadmin   20979712 Jul 25 15:39 pdb1_temp012016-06-29_12-06-27-PM.dbf
-rw-r----- 1 oracle asmadmin    5251072 Jul 25 15:41 SAMPLE_SCHEMA_users01.dbf
-rw-r----- 1 oracle asmadmin  555753472 Jul 25 15:41 sysaux01.dbf
-rw-r----- 1 oracle asmadmin  272637952 Jul 25 15:41 system01.dbf
Hence, we have been able to hot clone a PDB remotely without:

Placing the source PDB in READ ONLY mode
Creating the directory for the destination PDB.

Step by step Clone PDB within CDB in Oracle Database 12c

Step by step Clone PDB within CDB in Oracle Database 12c

Oracle database 12c is providing new feature to clone database in very simple steps.
It is very easy and simple to clone database in Oracle version 12c.

Here, I am listing step by step to clone PDB (Plug-able Database ) database within it's CDB (Container Database).

Example shows, clone database from pdborcl to new database pbduporcl.

Below are the Steps,

SrNo
Description
Step 1
Setting the Source PDB to READ ONLY Mode
Step 2
Create directory for new clone PDB.
Step 3
Configure OMF to the directory of the Clone PDB
Step 4
Clone the PDB within CDB
Step 5
Set source PDB back to open mode.
Step 6
Check status of all PDBs

Step 1:- Setting the Source PDB to READ ONLY Mode


Connect with SYS user and make pdborcl database to read only mode.
-bash-4.1$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed May 9 11:28:40 2016
Copyright (c) 1982, 2014, 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> alter pluggable database pdborcl close immediate;
Pluggable database altered.

SQL> alter pluggable database pdborcl open read only;
Pluggable database altered.

SQL>
SQL> exit


Step 2:- Create directory for new clone PDB

Now Create directory structure for new pdbduporcl database.

-bash-4.1$ pwd
/mnt/devops_0/Oracle/db1212/db1212dat/orcl12c


-bash-4.1$ mkdir pdbduporcl
-bash-4.1$ ls -ltr
total 2080842
drwxr-x---+ 2 db1212 oinstall         5 May  8 14:19 pdbseed
-rw-r-----+ 1 db1212 oinstall  52429312 May  9 01:31 redo02.log
-rw-r-----+ 1 db1212 oinstall  52429312 May  9 11:30 redo03.log
-rw-r-----+ 1 db1212 oinstall 206577664 May  9 11:31 temp01.dbf
-rw-r-----+ 1 db1212 oinstall   5251072 May  9 11:35 users01.dbf
-rw-r-----+ 1 db1212 oinstall 361766912 May  9 11:45 undotbs01.dbf
-rw-r-----+ 1 db1212 oinstall 838868992 May  9 11:45 system01.dbf
-rw-r-----+ 1 db1212 oinstall 744497152 May  9 11:45 sysaux01.dbf
-rw-r-----+ 1 db1212 oinstall  52429312 May  9 11:45 redo01.log
drwxr-x---+ 2 db1212 oinstall         7 May  9 11:45 pdborcl
-rw-r-----+ 1 db1212 oinstall  17973248 May  9 11:45 control01.ctl
drwxr-xr-x+ 2 db1212 oinstall         2 May  9 11:45 pdbduporcl


Step 3:- Configure OMF to the directory of the Clone PDB (Optional)

-bash-4.1$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Wed May 9 11:48:01 2016
Copyright (c) 1982, 2014, 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> alter system set db_create_file_dest='/mnt/devops_0/Oracle/db1212/db1212dat/orcl12c/pdbduporcl';
System altered.
SQL>


Step 4:- Clone the PDB within CDB

Now below steps will clone database from pdborcl to pdbduporcl.

-bash-4.1$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Wed May 9 11:49:53 2016
Copyright (c) 1982, 2014, 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>
SQL> create pluggable database pdbduporcl from pdborcl;
Pluggable database created.


Open new PDB database

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


Add TNS Entery in tnsnames.ora file.

pdbduporcl =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = orac12c)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdbduporcl)
    )
  )

Connect to new PDB database

-bash-4.1$ sqlplus /nolog
SQL*Plus: Release 12.1.0.2.0 Production on Wed May 9 12:35:18 2016
Copyright (c) 1982, 2014, Oracle.  All rights reserved.

SQL> conn sys/sysdba123@pdbduporcl as sysdba
Connected.
SQL>
SQL> show con_name
CON_NAME
------------------------------
PDBDUPORCL
SQL>


Step 5:- Set source PDB back to open mode.

Now, set Source PDB back to open mode and make it available to users.

-bash-4.1$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Wed May 9 12:37:41 2016
Copyright (c) 1982, 2014, 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>
SQL> alter pluggable database pdborcl close immediate;
Pluggable database altered.

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


Step 6:- Check status of all PDBs


Now check the status of all PDBs as below.

-bash-4.1$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Wed May 9 12:37:41 2016
Copyright (c) 1982, 2014, 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> select con_id, dbid, name, open_mode from v$pdbs;

    CON_ID  DBID          NAME                          OPEN_MODE
-------------- --------------  ---------------------------  ------------------
         2         3707469898 PDB$SEED                  READ ONLY
         3         1332221668 PDBORCL                   READ WRITE
         4         1313681298 PDBDUPORCL           READ WRITE

SQL>

Reference:-






ORA-08004: sequence IEX_DEL_BUFFERS_S.NEXTVAL exceeds MAXVALUE

 Error:- IEX: Scoring Engine Harness Error - ORA-08004: sequence IEX_DEL_BUFFERS_S.NEXTVAL exceeds MAXVALUE (Doc ID 2056754.1) SYMPTOMS:- Yo...