Oracle Database, Fusion Middleware, Linux

Around The World Flight 23, Mombassa to Nairobi

Enjoying the wildlife as our journey progresses from Mombassa across the African plain to Nairobi in Kenya.


Another flight over Africa takes us over Kenyan National Park to Nairobi, which is the capital city and largest city of Kenya. It is famous for having the Nairobi National Park, the world’s only game reserve found within a major city. An international airport in Nairobi is Jomo Kenyatta International Airport (IATA: NBO, ICAO: HKJK). The airport is named after Jomo Kenyatta, Kenya’s first president and prime minister (wikipedia).

Continue reading…

, , , , , ,

Data Redaction in Oracle Database 12c

Data Redaction provides a way to define masking policies for an application. Oracle Data Redaction provides functionality to mask (redact) data that is returned from user SELECT queries. The masking takes place in real time. The difference between Oracle Data Masking and Data Redaction is that Data Redaction doesn’t alter underlying data in the database; it redacts the data only when it is being displayed. Data Redaction can be applied conditionally, based on different factors such as user, application identifiers, or client IP addresses. Data Redaction is available in Oracle Database 12c and now also in 11g Release 2, patch set Data Redaction is licensed as part of Oracle Advanced Security.

data _redaction_concept

Data redaction doesn’t prevent application logic, operations like inserting, updating or deleting data  are perfectly consistent with original data. If the application user creates a view on redacted table, the view will also contain the redacted data.

The following methods are available in data redaction:

data _redaction_methods

Oracle Data Redaction in Oracle Enterprise Manager 12c:

In our demo we will be using sample table created in “APEX_ZION” schema running in Oracle Database 12c pluggable database. Customer data resides in CUSTOMERS table in APEX_ZION schema. We’ve got ~50k of records in the table. We generated our sample data at fakenamegenerator.com and imported it using Oracle SQL Developer.

Here is an sql script to create a demo table with several rows and test user account:

as APEX_ZION user
create table customers (
	customer_id		number,
	cust_first_name		varchar2(20),
	cust_last_name		varchar2(20),
	cust_phone_nr		varchar2(25),
	CUST_STREET_ADDRESS	varchar2(60),
	cust_postal_code	varchar2(10),
	cust_city		varchar2(30),
	cust_email		varchar2(60),
	cust_url		varchar2(100),
	cust_national_id	varchar2(20)
Insert into CUSTOMERS values ('124','George','Haynes','478-226-7323','4145 Oakridge Lane','30901','Augusta','GeorgeAHaynes@dayrep.com','closedloopcolor.com','670-09-4251');
Insert into CUSTOMERS values ('125','William','Bill','205-968-8067','3553 Wright Court','35243','Cahaba Heights','WilliamCBill@gustr.com','jswimshop.com','419-92-3203');
Insert into CUSTOMERS values ('126','Rhonda','Finley','217-615-9990','1405 University Hill Road','62701','Springfield','RhondaNFinley@cuvox.de','istanbulchekup.com','345-40-5460');
Insert into CUSTOMERS values ('127','Rosana','Solis','630-912-3504','747 Lewis Street','60606','Chicago','RosanaASolis@teleworm.us','germanfacials.com','342-30-5422');
Insert into CUSTOMERS values ('128','Craig','Montz','407-296-6024','1326 Barnes Street','32808','Orlando','CraigAMontz@dayrep.com','moocroot.com','768-32-5412');
Insert into CUSTOMERS values ('129','Todd','Barrios','325-282-5551','2445 Felosa Drive','79506','Blackwell','ToddJBarrios@teleworm.us','rekzai.com','636-84-3118');
Insert into CUSTOMERS values ('130','Ida','Cochran','334-566-0879','3410 Turkey Pen Lane','36081','Troy','IdaJCochran@gustr.com','onwebinsurance.com','417-04-4580');
Insert into CUSTOMERS values ('131','Lewis','Anderson','812-442-2562','709 Lucy Lane','47834','Brazil','LewisBAnderson@armyspy.com','oreauction.com','309-98-5662');
Insert into CUSTOMERS values ('132','Cheryl','Winters','559-501-6054','2155 Chicago Avenue','93721','Fresno','CherylGWinters@jourrapide.com','mcdube.com','573-54-1811');
Insert into CUSTOMERS values ('133','Leann','Sullivan','773-396-7992','1060 Oakmound Drive','60605','Chicago','LeannDSullivan@cuvox.de','localvim.com','356-78-9654');


connect sys / as sysdba
create user test_user identified by *******;
grant insert, select, delete, update on APEX_ZION.customers to test_user;
grant create session to test_user;

Our goal is to redact the following columns for all other users than apex_zion:

Column Name Data Redaction method
cust_phone_nr Partial
cust_postal_code Random
cust_email RegExp
cust_national_id Partial

Login to the Oracle Enterprise Manager 12c, next login to a target database and create data redaction policy:

New policies are effective immediately, let’s test it in Oracle SQL Developer by selecting the same data from two different user accounts:

Data Redaction policies can also be managed in Oracle SQL Developer.

First we have to grant the following privileges to user APEX_ZION

--as sysdba user:
grant select on sys.redaction_policies to apex_zion;
grant select on sys.redaction_columns to apex_zion;
grant execute on dbms_redact to apex_zion;

Data Redaction in SQL Developer:

That’s all folks. For more information please check DBMS_REDACT at Oracle Docs.

Please leave your comments down below.

, , ,

Configure SSL in WebLogic Server Domain

Quick guide on how to implement SSL in WebLogic Server domain. Custom Identity and Custom Trust with self-signed certificate.

First, let’s create custom directory to store self-signed certificate, custom keystore and custom trust store files:

mkdir -p /u01/app/oracle/config/domains/wls12c_domain/security/SSL

Modify input variables according to your requirements and run below script on WebLogic Server host. This script will automate entire procedure and does the following:

  • create keystore
  • create self-signed certificate
  • export the server certificate
  • create Trust Store
#Script: Generate Keystore
cd ${DOMAIN_HOME}/security/SSL

DNAME="CN=example.local.net, OU=ZION Support, O=ZION Inc, L=Gotham, ST=StateOfMind, C=EU"

echo "Creating keystore"
${JDK_HOME}/keytool -genkey -alias ${ALIAS} -keyalg RSA -keysize 2048 -sigalg SHA256withRSA -dname "${DNAME}" -keypass ${KEYPASS} -keystore ${IDENTITY_JKS} -storepass ${STOREPASS}

#Self Signing the Certificate
echo "Self Signing the Certificate"
${JDK_HOME}/keytool -selfcert -alias ${ALIAS} -dname "${DNAME}" -keypass ${KEYPASS} -keystore ${IDENTITY_JKS} -storepass ${STOREPASS}

#Exporting the Server certificate
echo "Exporting the Server certificate"
${JDK_HOME}/keytool -export -alias ${ALIAS} -file ${CERT_CER} -keystore ${IDENTITY_JKS} -storepass ${STOREPASS}

#Creating Trust Store
echo "Creating Trust Store"
${JDK_HOME}/keytool -import -alias ${ALIAS} -file ${CERT_CER} -keystore ${TRUST_JKS} -sigalg SHA256withRSA -storepass ${STOREPASS} -noprompt

echo ""
echo "Done"
echo ""

To check the content of the keystore

keytool -v -list -keystore identity.jks

Next, Login to Weblogic Server Admin Console, go to Servers and select the managed server you want to update.

  1. Go to Configuration -> Keystore tab
  2. Click the Change button under Keystore Configuration and select Custom Identity and Custom Trust
  3. For the Custom Identity Keystore, enter the path to identity.jks file, next type in Keystore type: JKS
  4. Enter your Custom Identity Keystore Passphrase as the password
  5. For the Custom Trust Keystore: enter the path to trust.jks file
  6. Keystore Type: JKS. Click Save button
  7. Go to Configuration -> Keystore -> SSL tab
  8. Enter the server certificate key alias (in our example: zion), and the keystore password
  9. Click Save to apply the changes.

Finally go to Configuration -> General tab and enable managed server to be listening on SSL port. Save changes.

You need to reboot WebLogic managed servers for the changes to take effect.

Extra tip: You may like the following WLST script which will automate the WebLogic Server configuration part – WLS_configure_SSL


, , , , ,

Oracle Live SQL is live. Code SQL in a Web Browser

Oracle Live SQL is a free online tool to learn and code SQL & PL/SQL in Oracle Database. Learn and share SQL, for free.

source: oracle.com

Get instant access to the Oracle Database and learn from a collection of community scripts and structured tutorials. Save and share SQL sessions with others!

Oracle Live SQL

Oracle Live SQL exists to provide the Oracle database community with an easy online way to test and share SQL and PL/SQL application development concepts.

  • Browser based SQL worksheet access to an Oracle database schema
  • Ability to save and share SQL script
  • Schema browser to view and extend database objects
  • Interactive educational tutorials
  • Customized data access examples for PL/SQL, Java, PHP, C
  • Learn more by using Live SQL yourself. Login to OTN and get started.

Live SQL Content

SQL Queries, PL/SQL Procedures, Functions, Packages and more!

Browse the collection of SQL scripts and tutorials: View ALL

source: oracle.com

, , , , ,

How to enable Oracle Enterprise Manager Express 12c

Oracle Enterprise Manager Express is a Web-based interface for managing an Oracle database 12c. It enables users to perform basic administrative tasks such as managing users, managing database initialization parameters, memory or storage. You can also view performance and SQL Tuning Advisor information, check status information about your database and pluggable databases.

In Oracle Database 12c Release 1, the concept of multi-tenant environment has been introduced. The multi-tenant architecture enables an Oracle database to function as a multi-tenant container database (CDB) that includes zero, one, or many customer-created pluggable databases (PDBs).

A CDB includes the following components:

Root named CDB$ROOT, stores Oracle-supplied metadata and common users. An example of metadata is the source code for Oracle-supplied PL/SQL packages. A common user is a database user known in every container.

A PDB appears to users and applications as if it were a non-CDB. For example, a PDB can contain the data and code required to support a specific application (e.g., APEX).

Each of these components is called a container. Therefore, the root is a container, the seed is a container, and each PDB is a container.

In this tutorial we will show two different types of configurations of Enterprise Manager Express one for CDB and the second for PDBs only. Imagine yourself as a dba who has full access to non-CDB/CDB/PDB, OEM Express 12c will allow you to manage CDB and all PDB containers from one central console. On the other hand you would like to allow regular users to login to OEM Express 12c as well, but grant them access to their PDBs only.


Continue reading…

, , , , , ,

Installing Oracle Linux 7 for Oracle software deployments

This demo shows installation process of Oracle Linux 7 in virtual machine (VirtualBox) and basic OS configuration for further Oracle software deployments e.g., Oracle Databases, WebLogic Servers.


1. Download Oracle Linux ISO image from Oracle Software Delivery Cloud

Continue reading…

, ,

Shell script: Copy files from source to target directory

Simple shell script to search and copy files from one folder to another.

Pretty useful if there is a need to copy/move files from one folder to another (e.g., Dropbox) in case that running program cannot be configured to write output files to multiple locations. The existing files in the destination folder are skipped. However bear in mind that this script doesn’t cover currently opened files, or it will not transfer the files again if the size of the source file has changed – for such scenario it would be useful to run rsync utility.

#This script will search for files defined by SUFFIX_ARG in SPATH
#directory and copy them to DPATH directory
#The files that already exist in destination will be skipped

#Script Variables
ACTION_TIME=`date +"[%d-%m-%y %T]:"`
COPY_LOG=COPY-`date +"%d-%m-%y_%T"`.log

echo "${ACTION_TIME} Starting copy task" >> ${COPY_LOG}
find ${SPATH} -type f -name "${SUFFIX_ARG}" -print | while read path
   FN="${path##*/}" #extract the file name from the path
   ACTION_TIME=`date +"[%d-%m-%y %T]:"`
   if [ -e "${DPATH}/${FN}" ]   #if the destination file exists, skip it
      echo "${ACTION_TIME} Skipped: File ${DPATH}/${FN} already exist" >> ${COPY_LOG}
       echo "${ACTION_TIME} Trying to copy ${FN}" >> ${COPY_LOG}
	   cp "$path" "${DPATH}/${FN}" >> ${COPY_LOG}
	   ACTION_TIME=`date +"[%d-%m-%y %T]:"`
	   echo "${ACTION_TIME} Copy ${FN} to ${DPATH} Complete" >> ${COPY_LOG}

As an alternative we could use rsync utility in order to synchronize multiple folders in the system. This is useful when we run rsync and there are still opened files. Once the particular file has been written, next rsync run will also synchronize this file, regardless whether it exists in the target folder or not.

rsync -q -axr --delete --exclude "DIR1" --exclude "DIR2" --include "*/" --include="*.jpeg" --exclude "*" /home/user/source /home/user/target

What we do in rsync is the following:

-q, --quiet		suppress non-error messages
-a, --archive		archive mode
-x, --one-file-system	don't cross filesystem boundaries
-r, --recursive		recurse into directories
--delete		delete extraneous files from destination
--exclude=PATTERN	exclude files matching PATTERN
--include=PATTERN	don't exclude files matching PATTERN



Oracle SOA Suite 11g: a workaround for long running SQL queries

Oracle SOA Suite 11g ( environment is connected to a database which is its dehydration store database, used to store process status data, especially for asynchronous BPEL processes. Production environments tend to produce millions of records in tables of that database. This, when not well maintained, can become a true performance bottleneck in the database and SOA Suite engine itself. Some of the performance issues can be caused by long running queries being queued in a database. Here is the example. Three SQL queries were executed in a database in parallel, multiple times per minute but it required 1-4 minutes for database in order to resolve each of them. Next to that there were more than 150M of records in CUBE_INSTANCE table, because historical composites’ instances were not being purged properly. The entire environment was monitored by Oracle Enterprise Manager 12c.


Here are the 2 of 3 SQL queries (3rd query has different solution):

 ci.STATE ,

Continue reading…

, ,

Fix for ORA-03113: end-of-file on communication channel

Oracle Database Server 12c Here is how to fix ORA-03113: end-of-file on communication channel

Cause: I issued shutdown immediate command in a running database instance. The database was busy and it took ages to shut it down. I was impatient and I did kill a virtual machine server. After virtual machine restart, the database did not start any more, throwing ORA-03113 error.

[oracle@host ~]$ sqlplus / as sysdba
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size 2926472 bytes
Variable Size 1224738936 bytes
Database Buffers 905969664 bytes
Redo Buffers 13848576 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 4903
Session ID: 237 Serial number: 26032


SQL> exit
Disconnected from Oracle Database 12c 
Enterprise Edition Release - 64bit Production

[oracle@zeus ~]$ sqlplus / as sysdba
Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size 2926472 bytes
Variable Size 1224738936 bytes
Database Buffers 905969664 bytes
Redo Buffers 13848576 bytes
SQL> alter database mount;

Database altered.

SQL> alter database clear unarchived logfile group 1;
Database altered.

SQL> alter database clear unarchived logfile group 2;
Database altered.

SQL> alter database clear unarchived logfile group 3;
Database altered.

SQL> shutdown immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size 2926472 bytes
Variable Size 1224738936 bytes
Database Buffers 905969664 bytes
Redo Buffers 13848576 bytes
Database mounted.
Database opened.

Uff… Database opened!


, ,

Oracle Database 12c In-Memory How To Get Started

Oracle Database In-Memory feature has been released by Oracle in recent patch set Here is how to upgrade Oracle database to version Upgrade Oracle Database 12c with ASM to

With the introduction of Oracle Database In-Memory, a single database can now efficiently support mixed workloads, delivering optimal performance for transactions while simultaneously supporting real-time analytics and reporting. This is possible due to a unique “dual-format” architecture that enables data to be maintained in both the existing Oracle row format, for OLTP operations, and a new purely in-memory column format, optimized for analytical processing. It is not a free feature though, see at the bottom for prices.


Continue reading…

, , ,

Previous Posts

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.