TrivadisContent

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

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

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.

Let’s Data Pump

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

Start a Data Pump Export

Permission denied

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

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 🙂 

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

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 :

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:

 

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

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

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

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.

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.

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

Clone PDB1 into PDB2

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.

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