eMarcel.com

Oracle Database, Fusion Middleware, Linux

Oracle 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 11.2.0.4. Data Redaction is licensed as part of Oracle Advanced Security.

EDIT: Be aware of Oracle’s data redaction “constraints”. Read David Litchfield’s white paper “Oracle Data Redaction is Broken” here (PDF). I checked all three described methods in my labs in September 2016 and using “RETURNING INTO” and “XMLQUERY()” methods appears to be fixed. However one gap still persist in a Database 12c version 12.1.0.2 – “an iterative inference attack”. It is still possible to be executed disclosing redacted data even to a regular test_user schema!

Below TEST 3 result based on David’s white paper example:

select cc from APEX_ZION.REDACTIONTEST;
CC 
-------
XXXXXXXXXXXXXXXX

--an iterative inference attack
exec p_undoredaction;
PL/SQL procedure successfully completed.
CC: 4111222233334444

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.

Continue reading…

, , ,

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

Continue reading…

, , , , ,

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.

multitenant_db12c_01

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.

oracle_linux-7

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.

#!/bin/sh
#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
SPATH="/home/user/source"
DPATH="/home/user/target"
SUFFIX_ARG="*.jpeg"
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
do
   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
   then
      echo "${ACTION_TIME} Skipped: File ${DPATH}/${FN} already exist" >> ${COPY_LOG}
   else
       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}
   fi
done

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

Cheers!!

,

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

Oracle SOA Suite 11g (11.1.1.7) 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.

oem12c_active_sessions_1

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

SELECT ci.DOMAIN_NAME ,
 ci.COMPOSITE_NAME ,
 ci.COMPOSITE_REVISION,
 ci.COMPONENT_NAME ,
 ci.STATE ,
 COUNT(*)
FROM CUBE_INSTANCE ci
WHERE ci.COMPONENTTYPE = :1
GROUP BY ci.DOMAIN_NAME ,
 ci.COMPOSITE_NAME ,
 ci.COMPOSITE_REVISION,
 ci.COMPONENT_NAME ,
 ci.STATE
ORDER BY ci.DOMAIN_NAME ,
 ci.COMPOSITE_NAME ,
 ci.COMPOSITE_REVISION,
 ci.COMPONENT_NAME ,
 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.

[[email protected] ~]$ 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

Solution:

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

[[email protected] ~]$ 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.
SQL>

Uff… Database opened!

Cheers!

, ,

Oracle Database 12c In-Memory How To Get Started

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

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.

2015-05-25_000455

Continue reading…

, , ,

Upgrade Oracle Database 12c with ASM 12.1.0.1 to 12.1.0.2

db12c_logoIn previous tutorial of this topic, we covered installation of Oracle Database 12c R1 on Linux 6 with ASM version 12.1.0.1.0. Now it’s the time to upgrade our database to version  12.1.0.2.0 which introduces long awaited “In-Memory Aggregation” and “In-Memory Column store” features. There is obviously much more – full description of Oracle Database 12c R1 (12.1.0.2) New Features can be found here. Readme Information for Oracle Database 12c Release 1 (12.1.0.2) available here.

Upgrade plan:

  1. Download software packages from Oracle eDelivery.
  2. Upgrade Oracle Grid Infrastructure 12c R1 to version 12.1.0.2.0
  3. Install software and upgrade Oracle Database 12c R1 to version 12.1.0.2.0
  4. Configure database listener – netca
  5. Post-Upgrade steps

Continue reading…

, , , ,

Previous Posts Next 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.

Close