12cR2

Oracle Database Backup Service – Encrypt your 12.2 Database Backups to the Cloud

Author: Martin Berger 27. August 2018

The Oracle RMAN backup encryption is necessary if you want to backup your database into the Oracle cloud. In Oracle 12c, you have three methods available to encrypt an Oracle RMAN backup:

with a passphrase
with a master encryption key
hybrid with a passphrase and an encryption key

On docs.oracle.com, the basic setup is described here: https://docs.oracle.com/en/cloud/paas/db-backup-cloud/csdbb/configuring-encryption-backups.html#GUID-4A1F5CF5-7EAF-4D71-9B7F-B46412F552CE

In this blog post, I show you how to configure your database environment with a master encryption key and a keystore. I use this solution to to backup and recovery to and into the Oracle cloud. And in the cloud, I don’t like to type in passwords manually for every action or write passwords in backup and restore scripts.

There are also some issues reports like in My Oracle Support Note TDE Wallet Problem in 12c: Cannot do a Set Key operation when an auto-login wallet is present (Doc ID 1944507.1).

Here are steps to create an autologin wallet.

Configure SQLNET.ora in $TNS_ADMIN to use a Keystore

ENCRYPTION_WALLET_LOCATION =
   (SOURCE =
     (METHOD = FILE)
     (METHOD_DATA =
       (DIRECTORY = /u00/app/oracle/network/wallet)
     )
    )

ENCRYPTION_WALLET_LOCATION =

(SOURCE =

(METHOD = FILE)

(METHOD_DATA =

(DIRECTORY = /u00/app/oracle/network/wallet)

)

Create Keystore as SYSDBA

SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/u00/app/oracle/tde_wallet' IDENTIFIED BY "my#wallet18";

1	SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/u00/app/oracle/tde_wallet' IDENTIFIED BY "my#wallet18";

Open Keystore

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "my#wallet18";

1	SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "my#wallet18";

The status is set to OPEN_NO_MASTER_KEY.

SQL> SELECT wrl_parameter, wallet_type, status
  2  FROM v$encryption_wallet;

WRL_PARAMETER                       WALLET_TYPE     STATUS
----------------------------------- --------------- --------------------
/u00/app/oracle/tde_wallet/     PASSWORD        OPEN_NO_MASTER_KEY

SQL> SELECT wrl_parameter, wallet_type, status

2 FROM v$encryption_wallet;

WRL_PARAMETER WALLET_TYPE STATUS

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

/u00/app/oracle/tde_wallet/ PASSWORD OPEN_NO_MASTER_KEY

Set Master Key

Now the master key has to defined. When you have already defined a wallet earlier and deleted the keys, you have to set the undocumented parameter to set the master key again. This works here too to set the key. Otherwise you get an ORA-28374: typed master key not found in wallet error. See Master Note For Transparent Data Encryption ( TDE ) (Doc ID 1228046.1) for further information.

SQL> ALTER SYSTEM SET "_db_discard_lost_masterkey"=true;
SQL> ADMINISTER KEY MANAGEMENT SET KEY FORCE KEYSTORE IDENTIFIED BY "my#wallet18" WITH BACKUP USING 'master_key_1';

1 2	SQL> ALTER SYSTEM SET "_db_discard_lost_masterkey"=true; SQL> ADMINISTER KEY MANAGEMENT SET KEY FORCE KEYSTORE IDENTIFIED BY "my#wallet18" WITH BACKUP USING 'master_key_1';

Now the status is set to OPEN.

SQL> SELECT wrl_parameter, wallet_type, status
  2  FROM v$encryption_wallet;

WRL_PARAMETER                       WALLET_TYPE     STATUS
----------------------------------- --------------- --------------------
/u00/app/oracle/tde_wallet/     PASSWORD        OPEN

SQL> SELECT wrl_parameter, wallet_type, status

2 FROM v$encryption_wallet;

WRL_PARAMETER WALLET_TYPE STATUS

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

/u00/app/oracle/tde_wallet/ PASSWORD OPEN

Activate Auto Login

SQL> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '/u00/app/oracle/tde_wallet' IDENTIFIED BY "my#wallet18";

1	SQL> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '/u00/app/oracle/tde_wallet' IDENTIFIED BY "my#wallet18";

Restart the Database

SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP

1 2	SQL> SHUTDOWN IMMEDIATE SQL> STARTUP

Verify if the keystore is available and WALLET_TYPE is AUTOLOGIN.

SQL> SELECT wrl_parameter, wallet_type, status
  2  FROM v$encryption_wallet;

WRL_PARAMETER                       WALLET_TYPE     STATUS
----------------------------------- --------------- --------------------
/u00/app/oracle/tde_wallet/     AUTOLOGIN       OPEN

SQL> SELECT wrl_parameter, wallet_type, status

2 FROM v$encryption_wallet;

WRL_PARAMETER WALLET_TYPE STATUS

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

/u00/app/oracle/tde_wallet/ AUTOLOGIN OPEN

Configure RMAN for Encryption

RMAN> CONFIGURE ENCRYPTION FOR DATABASE ON;

1	RMAN> CONFIGURE ENCRYPTION FOR DATABASE ON;

RMAN Backup Test

A simple RMAN controlfile backup into the Oracle cloud (OPC Backup Module is already configured).

RUN {  
 allocate channel t1 type 'sbt_tape' parms='SBT_LIBRARY=libopc.so, SBT_PARMS=(OPC_PFILE=/u00/app/oracle/admin/OCIDB01/opc_config/opcOCIDB01.ora)';  
 backup current controlfile;  
 release channel t1;  
}

RUN {

allocate channel t1 type 'sbt_tape' parms='SBT_LIBRARY=libopc.so, SBT_PARMS=(OPC_PFILE=/u00/app/oracle/admin/OCIDB01/opc_config/opcOCIDB01.ora)';

backup current controlfile;

release channel t1;

}

Error message if you want to backup into the Oracle cloud and the encryption is not configured correctly:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on t1 channel at 08/27/2018 18:48:27
ORA-19914: unable to encrypt backup
ORA-28365: wallet is not open

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03009: failure of backup command on t1 channel at 08/27/2018 18:48:27

ORA-19914: unable to encrypt backup

ORA-28365: wallet is not open

Backup Verification in V$BACKUP_PIECE – Column ENCRYPTED

SQL> SELECT start_time,handle,substr(media,1,30),encrypted
  2  FROM v$backup_piece;

START_TIME         HANDLE                                   SUBSTR(MEDIA,1,30)                  ENC
------------------ ---------------------------------------- ----------------------------------- ---
27-AUG-18          c-903044157-20180827-00                  eucom-north-1.stora..orage-tri      YES

SQL> SELECT start_time,handle,substr(media,1,30),encrypted

2 FROM v$backup_piece;

START_TIME HANDLE SUBSTR(MEDIA,1,30) ENC

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

27-AUG-18 c-903044157-20180827-00 eucom-north-1.stora..orage-tri YES

Links

http://www.oracle.com/technetwork/database/security/twp-transparent-data-encryption-bes-130696.pdf

http://www.oracle.com/technetwork/database/security/index-095354.html

12c, 12cR2, Oracle Cloud, Oracle RDBMS, TrivadisContent

Oracle Data Pump with the 12.2.0.1.0 Instant Client for Linux x86-64

Author: Martin Berger 22. March 2017

From the Oracle Database 12c Release 2 (12.2) New Features Guide:

Adding Oracle Data Pump and SQL*Loader Utilities to Instant Client

This feature adds SQL*Loader, expdp, impdp, exp, and imp to the tools for instant client.

Now you can run these utilities on machines that do not have a complete Oracle Database installation.

The newest release of the Oracle Instant Client for Linux x86-64 has an additional package called Tools. This package contains Data Pump, SQL*Loader and the Workload Replay Client for Real Aplication Testing. The good old import export tools is included too. This is very nice.

For example, if you want to load application log files with SQL*Loader into the database which are located on a separate server, there is no need for the client installation anymore. Or if your developers want to export data with Data Pump, all they need is the Instant Client now.

Just install – or better say unzip – the Instant Client basic package and the tools. Set some variables and go for it. The Instant Client packages are available as zip and as rpm for Unix systems. The rpm method requires root access to install.

Sizes of the Zip-Files

Instant Client Package – Basic: 66 Megabyte
Instant Client Package – Tools: 1 Megabyte

Zip Download URLs

Instant Client Package – Basic:
http://download.oracle.com/otn/linux/instantclient/122010/instantclient-basic-linux.x64-12.2.0.1.0.zip
Instant Client Package – Tools:
http://download.oracle.com/otn/linux/instantclient/122010/instantclient-tools-linux.x64-12.2.0.1.0.zip

SQL*Plus® User’s Guide and Reference

https://docs.oracle.com/database/122/SQPUG/SQL-Plus-instant-client.htm#SQPUG157

Instant Client Directory Content

Example for the Instant Client directory content on a Oracle Linux 7.2 server – Basic and Tools package installed.

oracle@kestenholz:/u00/app/oracle/product/instantclient_12_2/ [instantclient122] ll
total 221168
drwxr-xr-x. 2 oracle oinstall      4096 Mar 22 12:22 .
drwxr-xr-x. 6 oracle oinstall      4096 Mar 22 12:21 ..
-rw-r--r--. 1 oracle oinstall       363 Mar 22 12:21 BASIC_README
-rw-r--r--. 1 oracle oinstall       363 Mar 22 12:22 TOOLS_README
-rw-r--r--. 1 oracle oinstall     44220 Mar 22 12:21 adrci
-rw-r--r--. 1 oracle oinstall   1067736 Mar 22 12:22 exp
-rw-r--r--. 1 oracle oinstall    231727 Mar 22 12:22 expdp
-rw-r--r--. 1 oracle oinstall     57272 Mar 22 12:21 genezi
-rw-r--r--. 1 oracle oinstall    527125 Mar 22 12:22 imp
-rw-r--r--. 1 oracle oinstall    239564 Mar 22 12:22 impdp
-rw-r--r--. 1 oracle oinstall  71638263 Mar 22 12:21 libclntsh.so.12.1
-rw-r--r--. 1 oracle oinstall   8033199 Mar 22 12:21 libclntshcore.so.12.1
-rw-r--r--. 1 oracle oinstall   2981501 Mar 22 12:21 libipc1.so
-rw-r--r--. 1 oracle oinstall    539065 Mar 22 12:21 libmql1.so
-rw-r--r--. 1 oracle oinstall     77173 Mar 22 12:22 libnfsodm12.so
-rw-r--r--. 1 oracle oinstall   6568149 Mar 22 12:21 libnnz12.so
-rw-r--r--. 1 oracle oinstall   2218687 Mar 22 12:21 libocci.so.12.1
-rw-r--r--. 1 oracle oinstall 124771800 Mar 22 12:22 libociei.so
-rw-r--r--. 1 oracle oinstall    158543 Mar 22 12:22 libocijdbc12.so
-rw-r--r--. 1 oracle oinstall    380996 Mar 22 12:22 libons.so
-rw-r--r--. 1 oracle oinstall    116563 Mar 22 12:22 liboramysql12.so
-rw-r--r--. 1 oracle oinstall   4036257 Mar 22 12:22 ojdbc8.jar
-rw-r--r--. 1 oracle oinstall   1669142 Mar 22 12:22 sqlldr
-rw-r--r--. 1 oracle oinstall    240476 Mar 22 12:22 uidrvci
-rw-r--r--. 1 oracle oinstall    746798 Mar 22 12:22 wrc
-rw-r--r--. 1 oracle oinstall     74230 Mar 22 12:22 xstreams.jar

oracle@kestenholz:/u00/app/oracle/product/instantclient_12_2/ [instantclient122] ll

total 221168

drwxr-xr-x. 2 oracle oinstall 4096 Mar 22 12:22 .

drwxr-xr-x. 6 oracle oinstall 4096 Mar 22 12:21 ..

-rw-r--r--. 1 oracle oinstall 363 Mar 22 12:21 BASIC_README

-rw-r--r--. 1 oracle oinstall 363 Mar 22 12:22 TOOLS_README

-rw-r--r--. 1 oracle oinstall 44220 Mar 22 12:21 adrci

-rw-r--r--. 1 oracle oinstall 1067736 Mar 22 12:22 exp

-rw-r--r--. 1 oracle oinstall 231727 Mar 22 12:22 expdp

-rw-r--r--. 1 oracle oinstall 57272 Mar 22 12:21 genezi

-rw-r--r--. 1 oracle oinstall 527125 Mar 22 12:22 imp

-rw-r--r--. 1 oracle oinstall 239564 Mar 22 12:22 impdp

-rw-r--r--. 1 oracle oinstall 71638263 Mar 22 12:21 libclntsh.so.12.1

-rw-r--r--. 1 oracle oinstall 8033199 Mar 22 12:21 libclntshcore.so.12.1

-rw-r--r--. 1 oracle oinstall 2981501 Mar 22 12:21 libipc1.so

-rw-r--r--. 1 oracle oinstall 539065 Mar 22 12:21 libmql1.so

-rw-r--r--. 1 oracle oinstall 77173 Mar 22 12:22 libnfsodm12.so

-rw-r--r--. 1 oracle oinstall 6568149 Mar 22 12:21 libnnz12.so

-rw-r--r--. 1 oracle oinstall 2218687 Mar 22 12:21 libocci.so.12.1

-rw-r--r--. 1 oracle oinstall 124771800 Mar 22 12:22 libociei.so

-rw-r--r--. 1 oracle oinstall 158543 Mar 22 12:22 libocijdbc12.so

-rw-r--r--. 1 oracle oinstall 380996 Mar 22 12:22 libons.so

-rw-r--r--. 1 oracle oinstall 116563 Mar 22 12:22 liboramysql12.so

-rw-r--r--. 1 oracle oinstall 4036257 Mar 22 12:22 ojdbc8.jar

-rw-r--r--. 1 oracle oinstall 1669142 Mar 22 12:22 sqlldr

-rw-r--r--. 1 oracle oinstall 240476 Mar 22 12:22 uidrvci

-rw-r--r--. 1 oracle oinstall 746798 Mar 22 12:22 wrc

-rw-r--r--. 1 oracle oinstall 74230 Mar 22 12:22 xstreams.jar

Let’s Data Pump

Set the environment variables. TNS_ADMIN is where my tnsnames.ora / sqlnet.ora are located.

oracle@kestenholz:~/ [instantclient122] export LD_LIBRARY_PATH=/u00/app/oracle/product/instantclient_12_2:${LD_LIBRARY_PATH}
oracle@kestenholz:~/ [instantclient122] export PATH=/u00/app/oracle/product/instantclient_12_2:${PATH}
oracle@kestenholz:~/ [instantclient122] export TNS_ADMIN=/u00/app/oracle/network/admin

oracle@kestenholz:~/ [instantclient122] export LD_LIBRARY_PATH=/u00/app/oracle/product/instantclient_12_2:${LD_LIBRARY_PATH}

oracle@kestenholz:~/ [instantclient122] export PATH=/u00/app/oracle/product/instantclient_12_2:${PATH}

oracle@kestenholz:~/ [instantclient122] export TNS_ADMIN=/u00/app/oracle/network/admin

Start a Data Pump Export

oracle@kestenholz:~/ [instantclient122] expdp system/<mypassword>@GEPDB39.jurasuedfuss.com schemas=scott directory=expdp dumpfile=expdpscott.dmp logfile=expdpscott.log
Export: Release 12.2.0.1.0 - Production on Wed Mar 22 12:44:18 2017

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/********@GEPDB39.jurasuedfuss.com schemas=scott directory=expdp dumpfile=expdpscott.dmp logfile=expdpscott.log
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /u00/app/oracle/admin/GECDB32/expdp/expdpscott.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Mar 22 12:44:44 2017 elapsed 0 00:00:25

oracle@kestenholz:~/ [instantclient122] expdp system/<mypassword>@GEPDB39.jurasuedfuss.com schemas=scott directory=expdp dumpfile=expdpscott.dmp logfile=expdpscott.log

Export: Release 12.2.0.1.0 - Production on Wed Mar 22 12:44:18 2017

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/********@GEPDB39.jurasuedfuss.com schemas=scott directory=expdp dumpfile=expdpscott.dmp logfile=expdpscott.log

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:

/u00/app/oracle/admin/GECDB32/expdp/expdpscott.dmp

Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Mar 22 12:44:44 2017 elapsed 0 00:00:25

Permission denied

If you get a permission denied error, change the file properties.

oracle@kestenholz:~/ [instantclient122] expdp system/<mypassword>@GEPDB39.jurasuedfuss.com schemas=scott directory=expdp dumpfile=expdpscott.dmp logfile=expdpscott.log
-bash: /u00/app/oracle/product/instantclient_12_2/expdp: Permission denied

oracle@kestenholz:/u00/app/oracle/product/instantclient_12_2/ [instantclient122] chmod 750 expdp

oracle@kestenholz:~/ [instantclient122] expdp system/<mypassword>@GEPDB39.jurasuedfuss.com schemas=scott directory=expdp dumpfile=expdpscott.dmp logfile=expdpscott.log

-bash: /u00/app/oracle/product/instantclient_12_2/expdp: Permission denied

oracle@kestenholz:/u00/app/oracle/product/instantclient_12_2/ [instantclient122] chmod 750 expdp

Operating System Availability of the Tools Package

At the moment – 22th of March 2017 – the tools packages is only available for these operating systems:

Microsoft Windows (x64)
Linux x86-64
Solaris Operating System (SPARC 64-bit)

I hope the AIX release will coming soon 🙂

12c, 12cR2, Oracle Tools, TrivadisContent

Oracle 12.2 – how to get access to Enterprise Manager 12c Database Express

Author: Martin Berger 2. March 2017

Today I have built in my OL 7.3 VM two container databases to verify the new feature in the Enterprise Manager 12c Database Express login screen where I can go direct into a container. The benefit is that I don’t have to set a separate port for each pluggable database anymore.

The DBCA has created me a fresh container database called ZH38 and a pluggable database ZHPDB01. I have enable the checkbox to configure EM 12c Database Express for Port 5501. After the database creation was finished, I was able to see the login screen, but the login was not possible. I got an XDB login prompt. SYS, SYSTEM and other users where I tried were not accepted.

I found the solution in the Database 2 Day DBA Guide – http://docs.oracle.com/database/122/ADMQS/getting-started-with-database-administration.htm#ADMQS003 :

SQL> exec dbms_xdb_config.setglobalportenabled(TRUE);

PL/SQL procedure successfully completed.

SQL> exec dbms_xdb_config.setglobalportenabled(TRUE);

PL/SQL procedure successfully completed.

After the execution of this command in the CDB$ROOT container, logins into EM 12c Database Express of the CDB$ROOT and the pluggable database ZHPDB01 were possible. No restart of the container database was required. If once set, it works for all existing and new created pluggable databases, the PDB$SEED included.

If you want to disable this access method to a specific pluggable database, execute the command above with the FALSE flag.

How can you find out if the parameter is set? Thanks to my Trivadis collegue Philipp Salvisberg / https://www.salvis.com/blog who has provided me this PL/SQL code:

SQL> WITH
  2  FUNCTION b2vc (in_bool_expr VARCHAR2) RETURN VARCHAR2 IS
  3     l_bool  BOOLEAN;
  4     l_plsql VARCHAR2(32767);
  5     l_ret   VARCHAR2(5);
  6  BEGIN
  7     l_plsql := 'BEGIN :l_bool := ' || in_bool_expr || '; END;';
  8     EXECUTE IMMEDIATE l_plsql USING OUT l_bool;
  9     IF l_bool IS NOT NULL THEN
 10        IF l_bool THEN
 11           l_ret := 'TRUE';
 12        ELSE
 13           l_ret := 'FALSE';
 14        END IF;
 15     END IF;
 16     RETURN l_ret;
 17  END b2vc;
 18  SELECT b2vc('DBMS_XDB_CONFIG.ISGLOBALPORTENABLED') FROM dual;
 19  /

B2VC('DBMS_XDB_CONFIG.ISGLOBALPORTENABLED')
--------------------------------------------------------------------------------
TRUE

SQL> WITH

2 FUNCTION b2vc (in_bool_expr VARCHAR2) RETURN VARCHAR2 IS

3 l_bool BOOLEAN;

4 l_plsql VARCHAR2(32767);

5 l_ret VARCHAR2(5);

6 BEGIN

7 l_plsql := 'BEGIN :l_bool := ' || in_bool_expr || '; END;';

8 EXECUTE IMMEDIATE l_plsql USING OUT l_bool;

9 IF l_bool IS NOT NULL THEN

10 IF l_bool THEN

11 l_ret := 'TRUE';

12 ELSE

13 l_ret := 'FALSE';

14 END IF;

15 END IF;

16 RETURN l_ret;

17 END b2vc;

18 SELECT b2vc('DBMS_XDB_CONFIG.ISGLOBALPORTENABLED') FROM dual;

19 /

B2VC('DBMS_XDB_CONFIG.ISGLOBALPORTENABLED')

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

TRUE

Summary: Do you have any problems with the new release? First read the new documentation.

12cR2, Oracle RDBMS, TrivadisContent

Oracle 12.2 – New Features I like – Part 1: Multitenant “Hot Clones”

Author: Martin Berger 9. November 2016

I like the Oracle Multitenant architecture which was introduced in 12.1. But the concept to clone a source database to multiple copies in a small step had one big problem. In Oracle 12.1, to clone a pluggable database the source database had to be in state read-only.

In 12.2 is it not longer necessary to set the source pluggable database in state read-only, the source database has not to be modified to create a clone. Oracle calls it in the documentation “Hot Clone”.

Link to the official Oracle documentation: https://docs.oracle.com/database/122/ADMIN/creating-and-removing-pdbs-with-sql-plus.htm#ADMIN13584

Here is a clone of the pluggable database PDB1 into PDB2 in the Oracle Database Cloud Service.

Verify existing Pluggable Databases and States – PDB1 is in state READ-WRITE

SQL> SHOW pdbs

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

SQL> SHOW pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ ONLY NO

3 PDB1 READ WRITE NO

Verify if Oracle Managed Files are in use

Using Oracle Managed Files makes the file name convert much easier. You don’t have to care about files and directories. Like the feature says, Oracle is managing that for you.

SQL> show parameter db_create_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      /u02/app/oracle/oradata

SQL> show parameter db_create_file_dest

NAME TYPE VALUE

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

db_create_file_dest string /u02/app/oracle/oradata

Create TEMP directory

Is it a bug or is it a feature? The directory for the temporary datafile will not be created, we have to do it manually.

SQL> !mkdir /u04/app/oracle/oradata/BERGER6/PDB2

1	SQL> !mkdir /u04/app/oracle/oradata/BERGER6/PDB2

Error message when the directory is not created in advanced when a clone is started:

ERROR at line 1:
ORA-01119: error in creating database file
'/u04/app/oracle/oradata/BERGER6/PDB3/temp012016-10-04_11-34-07-330-AM.dbf'
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 1
ORA-06512: at line 2

ERROR at line 1:

ORA-01119: error in creating database file

'/u04/app/oracle/oradata/BERGER6/PDB3/temp012016-10-04_11-34-07-330-AM.dbf'

ORA-27040: file create error, unable to create file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 1

ORA-06512: at line 2

Clone PDB1 into PDB2

SQL> BEGIN
  2    EXECUTE IMMEDIATE 'CREATE PLUGGABLE DATABASE "PDB2" FROM "PDB1"
  3    STORAGE UNLIMITED
  4    TEMPFILE REUSE
  5    FILE_NAME_CONVERT=(''PDB1'',''PDB2'')
  6    KEYSTORE IDENTIFIED BY "<my_cloud_sys_password>"';
  7  END;
  8  /

PL/SQL procedure successfully completed.

SQL> BEGIN

2 EXECUTE IMMEDIATE 'CREATE PLUGGABLE DATABASE "PDB2" FROM "PDB1"

3 STORAGE UNLIMITED

4 TEMPFILE REUSE

5 FILE_NAME_CONVERT=(''PDB1'',''PDB2'')

6 KEYSTORE IDENTIFIED BY "<my_cloud_sys_password>"';

7 END;

8 /

PL/SQL procedure successfully completed.

Attention: Databases in the Oracle Database Cloud Service are created “Secure by Default”, that means that Transparent Data Encryption (TDE) is enabled. This is why I need the KEYSTORE INDENTIFIED BY command in line 6.

Verify PDB2 state

The cloned PDB2 is in state MOUNTED after the clone procedure.

SQL> SHOW pdbs

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

SQL> SHOW pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ ONLY NO

3 PDB1 READ WRITE NO

4 PDB2 MOUNTED

Open PDB2

Now the pluggable database PDB2 is ready to use, just open it. For more information how TDE works in a multitenant environment (export key etc.) take a look here: http://docs.oracle.com/database/122/ASOAG/using-transparent-data-encryption-with-other-oracle-features.htm#ASOAG10353

SQL> ALTER PLUGGABLE DATABASE PDB2 OPEN READ WRITE;

1	SQL> ALTER PLUGGABLE DATABASE PDB2 OPEN READ WRITE;

12cR2, Oracle Cloud, Oracle RDBMS, Oracle Tools, TrivadisContent