Transparent Data Encryption (TDE) In Oracle 12c

Today we are going to talk about Transparent Data Encryption (TDE) In Oracle 12c. First, have look at the introduction of TDE.  After the introduction, we have steps to configure TDE.

Transparent Data Encryption (TDE) was introduced in Oracle Database 10g Release 2 as an OUT-OF place mechanism to encrypt data at the storage (media) level. TDE enables the encryption of data at the storage level to prevent data tempering from outside of (more...)

Disable TDE In Oracle 12c

This post is about Disable TDE In Oracle 12c . Let’s hav look on the steps.

Find the encrypted table columns and modify them:

SQL> select owner,table_name,column_name,encryption_alg from dba_encrypted_columns;SQL> select owner,table_name,column_name,encryption_alg from dba_encrypted_columns; OWNER TABLE_NAME   COLUMN_NAME ENCRYPTION_ALG—– ———— ———— —————————–RAJ   EMPLOYEE    SALARY AES 192 bits key  SQL> alter table raj.employee modify( salary decrypt); Table altered. SQL> select owner,table_name,column_name,encryption_alg from dba_encrypted_columns; No rows selected

 

Now find the table under the encrypted table and move to normal tablespace:

(more...)

I’ll be presenting at UKOUG Conference with …


I will be presenting at the UKOUG Conference this year as an Oracle Ace and Snr Consultant from a award winning Partner of the Year. I’ll be speaking about:

  • ICS (now part of Oracle Integration Cloud)
  • Microservices and WebLogic
  • Oracle Messaging Cloud Service

I also have colleagues from Capgemini covering IaaS and SaaS among other things. I hope that we see you in Birmingham. Full details of my sessions :

Integration Cloud Service (ICS) Customer (more...)

Run Oracle Database in Docker using prebaked image from Oracle Container Registry–a two minute guide

imageThis article will show how to run an Oracle Database on a Docker host using the prebaked images on Oracle Continer Registry. It is my expectation that it takes me very little manual effort to run the full 12.2.0.1 Oracle Enterprise Database – just pull and run the Docker image. Once it is running, I get the usual Docker benefits such as clean environment management, linking from other containers, quick stop and (more...)

Garbled display while running FMW installer on Linux

A colleague faced this while running FMW installer on a Linux machine. The display appeared like this

 

 

 

 

 

 

 

This thread gave a clue that it could have something to do with fonts. So I checked what all fonts related stuff was installed.

[root@someserver ~]# rpm -aq |grep -i font
stix-fonts-1.1.0-5.el7.noarch
xorg-x11-font-utils-7.5-20.el7.x86_64
xorg-x11-fonts-cyrillic-7.5-9.el7.noarch
xorg-x11-fonts-ISO8859-1-75dpi-7.5-9.el7.noarch
xorg-x11-fonts-ISO8859-9-100dpi-7.5-9.el7. (more...)

root.sh fails with CRS-2101:The OLR was formatted using version 3

Got this while trying to install 11.2.0.4 RAC on Redhat Linux 7.2. root.sh fails with a message like

ohasd failed to start
Failed to start the Clusterware. Last 20 lines of the alert log follow:
2017-11-09 15:43:37.883:
[client(37246)]CRS-2101:The OLR was formatted using version 3.

This is bug 18370031. Need to apply the patch before running root.sh.

Oracle SOA Suite 12c: database configuration

After the running the rcu i was keen what was created inside the database. The installation showed some additional tablespaces:
select tablespace_name from dba_tablespaces; 
TABLESPACE_NAME              
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
DEV_SOAINFRA
DEV_IAS_TEMP
DEV_STB
DEV_IAS_UMS
DEV_WLS
DEV_MDS
DEV_IAS_OPSS
DEV_IAU

13 Zeilen gewählt.
Ok. Not really exciting.
But the list of the created tables was quite impressing:
select OWNER,TABLE_NAME from all_tables where owner like 'DEV%';

OWNER                TABLE_NAME                             
-------------------- ----------------------------------------
DEV_MDS              MDS_PURGE_PATHS                        
DEV_SOAINFRA         AQ$_IP_QTAB_G                          
DEV_SOAINFRA         AQ$_EDN_EVENT_QUEUE_TABLE_G            
DEV_SOAINFRA         (more...)

How To Move AUD$ Table To Another Tablespace Using DBMS_AUDIT_MGMT

If your AUD$ table is in SYSTEM and SYSTEM tablespace, Then it is advised to move the AUD$ to a dedicated tablespace.

 

Use steps to move AUD$.

select owner,segment_name,segment_type,tablespace_name,bytes/1024/1024 from dba_segments where segment_name=’AUD$’

OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME BYTES/1024/1024
——- ————- —————— —————————— —————
SYS AUD$ TABLE SYSTEM 16

Use the dbms_audit_mgmt to move the tablespace.

BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
audit_trail_location_value => ‘AUDIT_DATA’);
END;
/

PL/SQL procedure successfully completed.

check whether tablespace has (more...)

ENABLE_DDL_LOGGING In Oracle 12c

This  ENABLE_DDL_LOGGING parameter has been introduced in Oracle 12c.

If this ENABLE_DDL_LOGGING is enabled, then DDL records are written to the ADR.
All DDL operations like alter/create/drop/truncate objects. Only drop user will be logged, But create user will not be.

Enable the parameter:

SQL> show parameter enable_ddl_logging
NAME TYPE VALUE
———————————— ———– ——————————
enable_ddl_logging boolean FALSE

SQL> alter system set enable_ddl_logging=true;
System altered.

SQL> show parameter enable_ddl_logging
NAME TYPE VALUE
———————————— ———– ——————————
enable_ddl_logging boolean (more...)

Purge AUD$ Table Using DBMS_AUDIT_MGMT

  Introduction:-

Oracle 11gR2 introduced DBMS_AUDIT_MGMT for managing audit trails.  The growth of AUD$ can impact the performance of the database. So purging it regularly is the best practice followed by DBA’s and DBMS_AUDIT_MGMT makes it easier.

Follow below steps for purging aud$ table.

1. Make sure AUD$ table is not in SYSTEM table tablespace.

  If AUD$ table is present in SYSTEM tablespace, then move it to a newpuring  dedicated tablespace using below script.

select (more...)

Unified Audit Trail In Oracle 12c

OVERVIEW:

With oracle 12c, unified auditing has been introduced. It consolidates all audit trails into a single audit trail table.

It will capture audit records from below sources.

SYS audit records ( which was written to os trail in traditional method, will now be written to db table)
Unified audit policies for different action/privilege/statement/role etc.
EXPDP/IMPDP events
RMAN events
Sql loader
TRADITIONAL VS UNIFIED

TRADITIONAL AUDITING

  • Depends on DB init parameter like audit_trail,audit_sys_log.
  • writes audit (more...)

ADF Performance on Docker – Lighting Fast

ADF performance depends on server processing power. Sometimes ADF is blamed for poor performance, but in most of the cases real issue is related to poor server hardware, bad programming style or slow response from DB. Goal of this post is to show how fast ADF request could execute and give away couple of suggestions how to minimize ADF request time. This would apply to ADF application running on any environment, not only Docker. I'm (more...)

Great Britain and Northern Ireland February 2018 Dates: “Oracle Indexing Internals and Best Practices” Seminar (Battle For Britain)

Attention Oracle Professionals in the United Kingdom !! I have now finalised all the dates and venues for a series of my popular and critically acclaimed “Oracle Indexing Internals and Best Practices” seminar I’ll be running in the UK in February 2018. I’m extremely excited as this will be the first time I’ve delivered this […]

Quick introduction to Oracle Container Registry–running one of Oracle’s prebaked images

Oracle has been active with Docker for quite some time now. From the first hesitant steps from some enthusiastic employees with Docker build files that helped people to get going with Oracle software in their experimental containers to a GitHub repo with a broad set of extensive Docker build files to create Docker containers with various Oracle product that are supported (https://github.com/oracle/docker-images). And of course the Oracle Container Cloud – launched in (more...)

Docker-CE on Ubuntu 17.10 (Artful Aardvark)

Today docker is only added to the repositories up to ubuntu version 17.04:

If you want to run docker on 17.10 you have to perform the following steps:
After that 
# docker run hello-world
Unable to find image 'hello-world:latest' locally
latest: Pulling from library/hello-world
9a0669468bf7: Pull complete
Digest: sha256:cf2f6d004a59f7c18ec89df311cf0f6a1c714ec924eebcbfdd759a669b90e711
Status: (more...)

Big Data Marathon

This week there is a Big Data event in London, gathering Big Data clients, geeks and vendors from all over to speak on the latest trends, projects, platforms and products which helps everyone to stay on the same page and align the steering wheel as well as get a feeling of where the fast-pacing technology world is going. The event is massive but I am glad I could make it even only for one hour (more...)

Redundant Grandparent Foreign Keys and Cardinality Estimate Errors

This post is about how a slightly de-normalized database design involving redundant foreign keys to other tables can end up producing sub-optimal execution plans for queries that use those extra joins as additional filter conditions.

By default the Oracle Optimizer assumes that different columns of data in a table are independent of each other, and that their data values are not correlated with each other in any way. When a query has filter conditions on (more...)

Backup stalled due to ASM rebalance stuck

I hit an issue where a full backup took much longer than normal.
In this case there was no alarm yet as no threshold was reached. But I worked on the DB for some other reason and out of a habit I most often start a ASH viewer whenever I work on a system - even if I only check data, it's worth to have an eye on the system.
In this case I saw (more...)

Easy(lazy) way to check which programs have properly configured FetchSize

select 
   s.module
  ,ceil(max(s.rows_processed/s.fetches)) rows_per_fetch
from v$sql s
where 
    s.rows_processed>100
and s.executions    >1
and s.fetches       >1
and s.module is not null
and s.command_type  = 3    -- SELECTs only
and s.program_id    = 0    -- do not account recursive queries from stored procs
and s.parsing_schema_id!=0 -- <> SYS
group by s.module
order by rows_per_fetch desc nulls last
/

Considerations When Using SQL Server 2016 Dynamic Data Masking

SQL Server 2016 introduced a new security feature called Dynamic Data Masking.  With the General Data Protection Regulations, (GDPR) breathing heavy down most IT in America’s neck, its good timing.

Data Masking” is a term that has a strict definition, but different results when we actually discuss the technology behind it.  The main goal is to obfuscate critical data in a non-reversible process and I deem it separate from encryption (more...)