Create Data Lake Without Hadoop

 In this post, the focus is to build a modern data lake using only open source technologies. I will walk-through a step-by-step process to demonstrate how we can leverage an S3-Compatible Object Storage (MinIO) and a Distributed SQL query engine (Presto) to achieve this. For some administrative work we may use Hive as well.

Connect to Presto from Spark

If you have Presto cluster as your processing layer, you could connect to it from Spark using Scala.


1- Copy the presto driver to the spark master location eg; /opt/progs/spark-2.4.5-bin-hadoop2.7/jars

Kudu Integration with Spark

Kudu integrates with Spark through the Data Source API, I downloaded the jar files from below locaton

https://jar-download.com/artifacts/org.apache.kudu/kudu-spark2_2.11/1.10.0/source-code
you can place the jar files in $SPARK_HOME/jars (eg; /opt/progs/spark-2.4.5-bin-hadoop2.7/jars)if you dont want to use --jars option with spark shell

Working with Ignite [In-Memory Data Grid]

Introduction                                                                        

Apache Ignite is an open source In-Memory Data Grid (IMDG), distributed database, caching and high performance computing platform. It offers a bucketload of features and integrates well with other Apache frameworks such as Hadoop, Spark, and Cassandra.  We need it for its High Performance and Scalability. It keeps data in RAM for fast processing and linear scaling. If you add more workstations to the grid, it will offer higher scalability and performance gains.

Working with Apache Kudu

Introduction                                                                        

Kudu is a columnar storage manager developed for the Apache Hadoop platform. Kudu shares the common technical properties of Hadoop ecosystem applications: it runs on commodity hardware, is horizontally scalable, and supports highly available operation.

Unlike other storage for big data analytics, Kudu isn't just a file format. It's a live storage system which supports low-latency millisecond-scale access to individual rows. Kudu isn't designed to be an OLTP system, but Fast processing of OLAP workloads.

Configuring Logstash with Elasticsearch

Introduction

Logstash is an open source data collection engine with real-time pipelining capabilities. Logstash can dynamically unify data from disparate sources and normalize the data into destinations of your choice. Cleanse and democratize all your data for diverse advanced downstream analytics and visualization use cases. You can clean and transform your data during ingestion to gain near real-time insights immediately at index or output time. Logstash comes out-of-box with many aggregations and mutations along with pattern matching, geo mapping, and dynamic lookup capabilities.

Integrating Hadoop and Elasticsearch

Introduction

Hadoop shines as a batch processing system, but serving real-time results can be challenging. For truly interactive data discovery, ES-Hadoop (The Elasticsearch-Hadoop connector) lets you index Hadoop data into the Elastic Stack to take full advantage of the speedy Elasticsearch engine and beautiful Kibana visualizations.

Working with Elasticsearch

Introduction

Elasticsearch is a  distributed, scalable, real-time search and analytics engine built on top of Apache Lucene™,. Lucene ( a library) is arguably the most advanced, high-performance, and fully featured search engine library in existence today—both open source and proprietary. It enables you to search, analyze, and explore your data whether you need full-text search, real-time analytics of structured data, or a combination of the two. 

Configure Rsyslog with Any Log File

Modern linux distros ship with Rsyslog which has some nice additional functionality (imfile module) that provides the ability to convert any standard text file into a Syslog message.

HDFS Centralized Cache Management

Due to increasing memory capacity, many interesting working sets are able to fit in aggregate cluster memory. By using HDFS centralized cache management, applications can take advantage of the performance benefits of in-memory computation. Cluster cache state is aggregated and controlled by the NameNode, allowing applications schedulers to place their tasks for cache locality. 

Configuring ACLs on HDFS

ACLs extend the HDFS permission model to support more granular file access based on arbitrary combinations of users and groups. We will discuss how to use Access Control Lists (ACLs) on the Hadoop Distributed File System (HDFS).

Spooling Files to HBase using Flume

Scenario:

One of my team wants to upload the contents of file existing in a specific directory (spooling dir) to HBase for some analysis. For the purpose we will be using Flume's spooldir-source which will allow users and applications to place files in spooling dir and process each line as one event to put it in HBase. It is assumed that Hadoop cluster and HBase is running, our environment is on HDP 2.6.

Integrating Hadoop Cluster with Microsoft Azure Blob Storage

Introduction

Azure Blob storage is a service for storing large amounts of unstructured object data, such as text or binary data, that can be accessed from anywhere in the world via HTTP or HTTPS. You can use Blob storage to expose data publicly to the world, or to store application data privately. All access to Azure Storage is done through a storage account. 

Install SQL Server 2017 on Linux [RHEL]

SQL Server 2017 now runs on Linux. It’s the same SQL Server database engine, with many similar features and services regardless of your operating system.

I'm providing below the straight away installation for it.

Using Avro with Hive and Presto

Pre-requsites

Working with Apache Avro to manage Big Data Files

The AvroSerde allows users to read or write Avro data as Hive tables. The AvroSerde's bullet points:

Working with Apache Avro to manage Big Data Files

What is Avro?

Apache Avro is a language-neutral data serialization system and is a preferred tool to serialize data in Hadoop. Serialization is the process of translating data structures or objects state into binary or textual form to transport the data over network or to store on some persistent storage. Once the data is transported over network or retrieved from the persistent storage, it needs to be deserialized again.

Custom Merge Utility for Flume Generated Files

Problem:

My client is streaming tweets to HDFS location, thousands of flume files are being created on this location. Hive External Table has been created on this location, external table performance is degraded when there are too may small files.

How to merge multiple part files (ORC) in Hadoop created by PolyBase?

Problem:

One of my client is using PolyBase to query and offloading SQL Server data to Hadoop. While offloading data in Hive ORC format, multiple part files are created by the PolyBase in HDFS. For better query performance all these part files are needed to be merged.

Using Microsoft PolyBase to query Big Data

Introduction

PolyBase is a technology that accesses data outside of the database via the t-sql language. In SQL Server 2016, it allows you to run queries on external data in Hadoop or to import/export data from Azure Blob Storage. Queries are optimized to push computation to Hadoop. PolyBase does not require you to install additional software to your Hadoop environment. Querying external data uses the same syntax as querying a database table. This all happens transparently. PolyBase handles all the details behind-the-scenes, and no knowledge about Hadoop is required by the end user to query external tables.

Processing Twitter (JSON) Data in Oracle (12c External Table)

Problem:

• We have live Twitter stream data ingested by Flume to our Hadoop cluster. 
• Flume is generating too many files in HDFS, 2 files in 1 second about 172k files in a day.
• We have to process the Flume generated twitter JSON files.
• Created an Oracle external table over twitter JSON files but performance is too bad because of too many files.
• Need a remedy for the above issues.

Using Preprocessor with External Table [Over HDFS]

Oracle 11g Release 2 introduced the PREPROCESSOR clause to identify a directory object and script used to process the files before they are read by the external table. This feature was backported to 11gR1 (11.1.0.7). The PREPROCESSOR clause is especially useful for reading compressed files, since they are unzipped and piped straight into the external table process without ever having to be unzipped on the file system.

Partitioning Oracle (12c) External Table [Over HDFS]

Partitioned external tables were introduced in Oracle Database 12c Release 2 (12.2), allowing external tables to benefit from partition pruning and partition-wise joins. With the exception of hash partitioning, many partitioning and subpartitioning strategies are supported with some restrictions. In this post I've created a test to get better performance of external table over HDFS.

Optimizing NFS Performance [HDP NFS]

Introduction

You may experience poor performance when using NFS. Careful analysis of your environment, both from the client and from the server point of view, is the first step necessary for optimal NFS performance. Aside from the general network configuration - appropriate network capacity, faster NICs, full duplex settings in order to reduce collisions, agreement in network speed among the switches and hubs, etc. - one of the most important client optimization settings are the NFS data transfer buffer sizes, specified by the mount command options rsize and wsize.

Offload Oracle Data to HDFS using RO Tablespaces

Purpose:

Offloading Oracle Data to HDFS

Prerequisites:

Hortonworks NFS Gateway is running Please, Please visit the post below

Configuring NFS Gateway for HDFS [HDP]

Creating Oracle External Table (12c) on HDFS using HDP NFS Gateway

Purpose:

Offloading Oracle Data to HDFS

Prerequisites:

Hortonworks NFS Gateway is running 

Configuring NFS Gateway for HDFS [HDP]

The NFS Gateway for HDFS allows clients to mount HDFS and interact with it through NFS, as if it were part of their local file system. The gateway supports NFSv3.

After mounting HDFS, a user can:

Configure Hortonworks Hive ODBC Driver for Oracle HS

The Hortonworks Hive ODBC Driver is used for direct SQL and HiveQL access to Apache

Hadoop / Hive distributions, enabling Business Intelligence (BI), analytics, and reporting on

Hadoop / Hive-based data. The driver efficiently transforms an application’s SQL query into

the equivalent form in HiveQL.

Query Teradata Presto from Oracle using ODBC Heterogeneous Gateway [RHEL 7]

Presto is a distributed SQL query engine designed to query large data sets distributed over one or more heterogeneous data sources. In Italian, “Presto” means fast. In the tech world, it means an open-source distributed SQL query engine for Apache™ Hadoop® that runs interactive analytic queries against data sources of all sizes. Through a single query, data is accessed where it resides. Typically, this means data in a Hadoop Distributed File System (HDFS). However, unlike other SQL on Hadoop engines, Presto can query data sources such as Apache Cassandra™, relational databases or even proprietary data stores. 

Diagnostics: Hive CLI is hanging On HDP

On our HDP, some time Hive CLI shell just hangs.

Diagnostics: Fix Under replicated blocks [Ambari Dashboard]

I see below in Ambari dashboard under HSDS Summary.

Using HDP Zeppelin

Apache Zeppelin is a web-based notebook that enables interactive data analytics. With Zeppelin, you can make beautiful data-driven, interactive and collaborative documents with a rich set of pre-built language backends (or interpreters, An interpreter is a plugin that enables you to access processing engines and data sources from the Zeppelin UI.) such as Scala (with Apache Spark), Python (with Apache Spark), SparkSQL, Hive, Markdown, Angular, and Shell. 

Using Apache Phoenix on HDP

Apache Phoenix is an open source, massively parallel, relational database engine supporting OLTP for Hadoop using Apache HBase as its backing store. It is a SQL abstraction layer for interacting with HBase. Phoenix translates SQL to native HBase API calls. Phoenix provide JDBC/ODBC and Python drivers. 

Working with HBase on HDP

Introduction

Apache HBase is a No-SQL database that runs on a Hadoop cluster. It is ideal for storing unstructured or semi-structured data. It was designed to scale due to the fact that data that is accessed together is stored together which allows to build big data applications for scaling and eliminating limitations of relational databases. 

Working with Talend for Big Data (TOSBD)

Introduction                                                                               

Talend (eclipse based) provides unified development and management tools to integrate and process all of your data with an easy to use, visual designer. It helps companies become data driven by making data more accessible, improving its quality and quickly moving it where it’s needed for real-time decision making.

Talend for Big Data is built on top of Talend's data integration solution that enables users to access, transform, move and synchronize big data by leveraging the Apache Hadoop Big Data Platform and makes the Hadoop platform ever so easy to use.

Analyzing/Parsing syslogs using Hive and Presto

Scenario

My company asked me to provide the solution for syslog aggregation for all the environments so that they may be able to analyze and get insights. Logs should be captured first, then retained and finally processed by the analyst team in a way they already use to query/process with database. The requirements are not much clearer as well as volume of data can't be determined at the stage.

Working with Apache Cassandra (RHEL 7)

Introduction

Cassandra (created at Facebook for inbox search) like HBase is a NoSQL database, generally, it means you cannot manipulate the database with SQL. However, Cassandra has implemented CQL (Cassandra Query Language), the syntax of which is obviously modeled after SQL and designed to manage extremely large data sets with manipulation capabilities. It is a distributed database, clients can connect to any node in the cluster and access any data.

Hortonworks - Using HDP Spark SQL

Using SQLContext, Apache Spark SQL can read data directly from the file system. This is useful when the data you are trying to analyze does not reside in Apache Hive (for example, JSON files stored in HDFS).

Installing/Configuring Hortonworks Data Platform [HDP]

Ambari is  completely open source management platform for provisioning, managing, monitoring and securing Apache Hadoop clusters. Apache Ambari takes the guesswork out of operating Hadoop. As part of the Hortonworks Data Platform, allows enterprises to plan, install and securely configure HDP making it easier to provide ongoing cluster maintenance and management, no matter the size of the cluster.

Working with Apache Spark SQL

What is Spark?

Apache Spark is a lightning-fast cluster (in-memory cluster )computing technology, designed for fast computation. Spark does not depend upon Hadoop because it has its own cluster management, Hadoop is just one of the ways to implement Spark, it uses Hadoop for storage purpose. It extends the MapReduce model to efficiently use it for more types of computations, which includes interactive queries and stream processing.

Installing/Configuring and working with Apache Kafka

Introduction

Apache Kafka is an open source, distributed publish-subscribe messaging system,

mainly designed to persistent messaging, high throughput, support multiple clients and providing real time message visibility to consumers.

Kafka is a solution to the real-time problems of any software solution, that is, to deal with real-time volumes of information and route it to multiple consumers quickly. Kafka provides seamless integration between information of producers and consumers without blocking the producers of the information, and without letting producers know who the final consumers are. It supports parallel data loading in the Hadoop systems.

Forward syslog to Flume with rsyslog

Introduction

Syslog  

In computing, syslog is a standard for message logging. It allows separation of the software that generates messages, the system that stores them, and the software that reports and analyzes them. Each message is labeled with a facility code, indicating the software type generating the message, and assigned a severity label.

Computer system designers may use syslog for system management and security auditing as well as general informational, analysis, and debugging messages. A wide variety of devices, such as printers, routers, and message receivers across many platforms use the syslog standard. This permits the consolidation of logging data from different types of systems in a central repository. Implementations of syslog exist for many operating systems.

Streaming Twitter Data by Flume using Cloudera Twitter Source

In my previous post Streaming Twitter Data using Apache Flume which fetches tweets using Flume and twitter streaming for data analysis.Twitter streaming converts tweets to Avro format and send Avro events to downsteam HDFS sinks, when Hive table backed by Avro load the data, I got the error message said "Avro block size is invalid or too large". In order to overcome this issue, I used Cloudera TwitterSource rather than apache TwitterSource.

Streaming Twitter Data using Apache Flume

Introduction                                                                           

Flume is a distributed service for efficiently collecting, aggregating, and moving large amounts of streaming event data. It is a highly reliable, distributed, and configurable tool. It is principally designed to copy streaming data (event/log data) from various web servers and services like Facebook and Twitter to HDFS.

Building Teradata Presto Cluster

Prerequisites:
Before working on this post you should review below posts.

Installing/Configuring PrestoDB

Working with PrestoDB Connectors

In this post , I'll be covering below

1- Installing and configuring Presto Admin
2- Installing Presto Cluster on a single node
3- Using Presto ODBC Driver
4- Installing and configuring Presto Cluster with one coordinator and three workers

Working with PrestoDB Connectors

Prerequisite:
Complete my previous post Installing/Configuring PrestoDB

Presto enables you to connect to other databases using some connector, in order to perform queries and joins over several sources providing metadata and data for queries. In this post we will work with some connectors. A coordinator (a master daemon) uses connectors to get metadata (such as table schema) that is needed to build a query plan. Workers use connectors to get actual data that will be processed by them.

Installing and Configuring PrestoDB

Introduction

Presto (invented at Facebook) is an open source distributed SQL query engine for running interactive analytic queries against data sources of all sizes ranging from gigabytes to petabytes. It allows querying data where it lives, including Hive, Cassandra, relational databases or even proprietary data stores. Unlike Hive, Presto doesn’t use the map reduce framework for its execution. Instead, Presto directly accesses the data through a specialized distributed query engine that is very similar to those found in commercial parallel RDBMSs. A single Presto query can combine data (through pluggable connectors) from multiple sources, allowing for analytics across your entire organization. It is targeted at analysts who expect response times ranging from sub-second to minutes.

Managing HDFS Quotas

The Hadoop Distributed File System (HDFS) allows the administrator to set quotas for the number of names used and the amount of space used for individual directories. Name quotas and space quotas operate independently, but the administration and implementation of the two types of quotas are closely parallel.

Hadoop DFSAdmin Commands

The dfsadmin tools are a specific set of tools designed to help you root out information about your Hadoop Distributed File system (HDFS). As an added bonus, you can use them to perform some administration operations on HDFS as well.

Recover the deleted file/folder in HDFS

By default Hadoop deletes the files/directory permanently but sometimes they are deleted accidentally and you want to get them back. You have to enable Trash feature for this purpose. There are two properties (fs.trash.interval & fs.trash.checkpoint.interval) to be set in core-site.xml to move the deleted files and directories in .Trash folder which is located in HDFS /user/$USER/.Trash.

Hive Streaming

Streaming offers an alternative way to transform data. During a streaming job, the Hadoop 

Streaming API opens an I/O pipe to an external process. Data is then passed to 

the process, which operates on the data it reads from the standard input and writes the 

results out through the standard output, and back to the Streaming API job.

Installing/Configuring and Working on Apache Sqoop

Introduction

Apache Sqoop is a hadoop ecosystem's tool (hadoop client) designed to Efficiently transfers bulk data between Apache Hadoop and structured datastores like Oracle. It helps offload certain tasks (such as ETL processing) from the EDW to Hadoop for efficient execution at a much lower cost. It can also be used to extract data from Hadoop and export it into external structured datastores.

Apache PIG - a Short Tutorial

Introduction

Apache Pig is an abstraction over MapReduce developed as a research project at Yahoo in 2006 and was open sourced via Apache incubator in 2007. In 2008, the first release of Apache Pig came out. In 2010, Apache Pig graduated as an Apache top-level project. It is a tool/platform which is used to analyze larger sets of data representing them as data flows. To write data analysis programs, Pig provides a high-level language known as Pig Latin. Scripts written in Pig Latin are internally converted to Map and Reduce tasks. Apache Pig has a component known as Pig Engine that accepts the Pig Latin scripts as input and converts those scripts into MapReduce jobs. 

Creating External Table for HDFS using Oracle Connector for Hadoop (OSCH)

Introduction

Oracle Big Data Connectors facilitate data access to data stored in an Apache Hadoop cluster. It can be licensed for use on either Oracle Big Data Appliance or a Hadoop cluster running on commodity hardware. There are three connectors available from which we are going to work on Oracle SQL Connector for Hadoop Distributed File System for the purpose of this post.

Connect Oracle SQL Developer to Hive

As Oracle SQL Developer is one of the most common SQL client tool used by Developers, Data Analyst and Data Architects to interact with Oracle and other relational systems. So extending the functionality of SQL developer to connect to hive is very useful for Oracle users. You can use the SQL Worksheet to query, create and alter Hive tables dynamically accessing data sources defined in the Hive metastore.

Using Hadoop Compression

Hadoop Compression

Hive can read data from a variety of sources, such as text files, sequence files, or even custom formats using Hadoop’s InputFormat APIs as well as can write data to various formats using OutputFormat API. You can take the leverage from Hadoop to store data as compressed to save significant disk storage. Compression also can increase throughput and performance. Compressing and decompressing data incurs extra CPU overhead, however, the I/O savings resulting from moving fewer bytes into memory can result in a net performance gain.

Hive for Oracle Developers and DBAs - Part III

Today we will discuss some more topic in Hive like Hive Queries, Distributed clauses, Sampling Data, Views,  Indexes and schema design. You can review the related posts below.

Hive for Oracle Developers and DBAs - Part  I

Hive for Oracle Developers and DBAs - Part II

Hive for Oracle Developers and DBAs - Part II

In the first Hive post  we have discussed the basic usage and functionality of Hive , today we move forward and discuss some advance functionality. I'll cover Collection, Tables and Partitions in this post.

Hive Installation and Configuration

What is Hive?

Apache Hive (originated in Facebook) is a Data warehouse system which is built to work on Hadoop to manage large datasets residing in HDFS. Hive is designed to enable easy data summarization, ad-hoc querying and analysis of large volumes of data using SQL At the same time, Hive's SQL gives users multiple places to integrate their own functionality to do custom analysis, such as User Defined Functions (UDFs). It is not designed for online transaction processing and best used for traditional data warehousing tasks.

Hive for Oracle Developers and DBAs - Part I

The Hadoop ecosystem emerged as a cost-effective way of working with large data sets. It imposes a particular programming model, called MapReduce, for breaking up computation tasks into units that can be distributed around a cluster of commodity, server class hardware, thereby providing cost-effective, horizontal scalability.

Hadoop Administration: Accessing HDFS (File system & Shell Commands)

You can access HDFS in many different ways. HDFS provides a native Java application programming interface (API) and a native C-language wrapper for the Java API. In addition, you can use a web browser to browse HDFS files. I'll be using CLI only in this post.

Hadoop Ecosystem - Quick Introduction

This is data age, data data everywhere. Although we cannot measure total volume of data stored electronically but it is estimated that 4.4 zettabytes in 2013 and is forecasting a tenfold growth by 2020 to 44 zettabytes. Clearly we can say this is Zettabyte Era. A zettabyte is equal to one thousand exabytes, one million petabytes, or one billion terabytes.

Big Data - The Bigger Picture

I’ve put the title with "The Bigger Picture" instead of "The Big Picture" because even big picture comes with much more details. The aim of this post is to provide a broad understanding of the topic without indulging into deeper details.  

Using Secure External Password Store - 11gR2

As all DBAs use some sort of shell scripts and have connections to the database which can be a major security issue if these scripts contain the database connection/passowrd details.

Instead of having these passwords in scripts, you can store password credentials for connecting to databases by using a client-side Oracle wallet. An Oracle wallet is a secure software container that stores authentication and signing credentials.

Proxy Authentication (Oracle DB)

Sometimes administrators need to connect to an application schema to perform maintenance. Sharing the application schema password among several administrators would provide no accountability. Instead, proxy authentication allows the administrators to authenticate with their own credentials first and then proxy to the application schema. In such cases, the audit records show the actual user who performed the maintenance activities. This form of proxy authentication is supported in Oracle Call Interface (OCI), JDBC, and on the SQL*PLUS command line.

Client-side Oracle wallet

Users are expected to provide the password when they connect to the database, but applications, middle-tier systems, and batch jobs cannot depend on a human to type the password. Earlier, a common way to provide passwords was to embed user names and passwords in the code or in scripts. This increased the attack surface and people had to make sure that their scripts were not exposed to anyone else. Also, if passwords were ever changed, changes to the scripts were required. Now you can store password credentials by using a client-side Oracle wallet. This reduces risks because the passwords are no longer exposed on command-line history, and password management policies are more easily enforced without changing application code whenever user names or passwords change.

12c: Zero App downtime using EBR

EBR, Edition-based redefinition allows multiple versions of PL/SQL objects, views and synonyms in a single schema, which makes it possible to upgrade the database component of an application while it is in use. An edition (nonschema object) is effectively a version label that can be assigned to all editionable objects in a schema. In Oracle 11gR2, if editioning was enabled for a user and you created a new object that was an editionable object in that user's schema, the object you created was automatically editionable. It was not possible to create non-editionable objects of one of these types in an edition enabled schema.

12c: Managing PDBs

Managing a pluggable database (PDB) involves a subset of the tasks required to administer a non-CDB. In this subset of tasks, most are the same for a PDB and a non-CDB, but there are some differences. A PDB administrator is limited to managing a single PDB and cannot affect other PDBs in the multitenant container database (CDB).

12c: ORA-65114: space usage in container is too high

ORA-65114: space usage in container is too high

Cause: Space usage in the current container exceeded the value of MAX_PDB_STORAGE for the container.

Action: Specify a higher value for MAX_PDB_STORAGE using the ALTER PLUGGABLE DATABASE statement.

12c: Network Data Encryption

Few organizations today have access to truly private and secure networks; instead, they share network infrastructure with other organizations. As a result, information traveling over these public or virtual private networks is often vulnerable to interception. The purpose of a secure cryptosystem is to convert plaintext data into unintelligible ciphertext based on a key, in such a way that it is very hard (computationally infeasible) to convert ciphertext back into its corresponding plaintext without knowledge of the correct key.

12c: Transparent Sensitive Data Protection

TSDP is the another new security feature of Oracle 12c , basically a complement to Data Redaction and Virtual Private Database. It allows to categorize sensitive columns across database and then to set a policy to each defined category of columns for example telephone number columns' policy to redact into a random number.

The packages DBMS_TSDP_MANAGE and DBMS_ TSDP_PROTECT are used to manage the categories and policies.

12c: Data Redaction

Oracle Data Redaction  is the feature which enables to mask data at run time that is returned from queries issued by applications. During the time that the data is being masked or redacted, all of the data processing is performed normally, and the back-end referential integrity constraints are preserved. It is ideal for situations in which you must redact specific characters out of the result set of queries of Personally Identifiable Information (PII) returned to certain application users. 

12c: Using Transparent Data Encryption

Oracle Database uses authentication, authorization, and auditing mechanisms to secure data in the database, but not in the operating system data files where data is stored. To protect these data files, Oracle Database provides Transparent Data Encryption (TDE). TDE encrypts sensitive data stored in data files. To prevent unauthorized decryption, TDE stores the encryption keys in a security module external to the database, called a keystore.

12c: Using In-Memory Column Store

The In-Memory Column Store (IM column store) is an optional portion of the system global area (SGA) that stores copies of tables, table partitions, and other database objects. In the IM column store, data is populated by column rather than row as it is in other parts of the SGA, and data is optimized for rapid scans, it does not replace the buffer cache, but acts as a supplement. The IM column store is included with the Oracle Database In-Memory option. To enable the IM column store, the INMEMORY_SIZE initialization parameter must be set to a non-zero value.

12c: Monitor SGA Memory on PDB

The CDB and all pluggable databases (PDBs) share a single database instance composed of the system global area (SGA) and background processes. Because of this sharing of memory resources, it would be desirable to distinguish resource usage among databases sharing the instance. You can use the below script to get it.

12c: Enterprise Manager Database Express

For Oracle Database 12c, Enterprise Manager Express enables you to perform administrative tasks such as managing user security and managing database memory and storage. You can also view performance and status information about your database. You can access Enterprise Manager Database Express using the URL that was displayed during installation. The default port used is 5011. In case you want to use a different port, you need to configure the port using the dynamic protocol registration method. After the HTTP port is configured, you use it to access Enterprise Manager Express.

12c: SYSBACKUP, SYSDG and SYSKM Administrative Users

In order to support segregation of duties requirements, Oracle 12c has introduced new administrative roles to conform to the principle of access to the least privilege. Three new user SYSBACKUP, SYSDG and SYSKM are created in support of this, when the database is created, with their account in the “EXPIRED & LOCKED” status. A equivalent administrative privilege with the same name as the user is created as well.

yum server configurations and oracle rpms installation

Configure yum server

1. copy dvd at /var/ftp/pub

2. rpm -ivh createrepo*

3. createrepo -v  /var/ftp/pub

4. service vsftpd restart

5. chkconfig vsftpd on

6. vim /etc/yum.conf

[local]

baseurl=ftp://localhost/pub

gpgcheck=0

 Install Required Linux Packages for Oracle RAC

yum install binutils elfutils-libelf glibc glibc-common libaio \

            libgcc libstdc++ make compat-libstdc++-33 elfutils-libelf-devel \

            glibc-headers glibc-devel libgomp gcc gcc-c++ libaio-devel \

            libstdc++-devel unixODBC unixODBC-devel sysstat libxp openmotif xorg-x11-libs-compat

Note: download xorg-x11-libs-compat exclusively for r12 installation, this pkg not availble in linux dvd

How to find Exadata database machine version

On any DB Node give the below command to know your Exadata version.

[root@pk3-iub-rp-od01 ~]# grep -i MACHINETYPES /opt/oracle.SupportTools/onecommand/databasemachine.xml

 X3-2 Quarter Rack HP

HP => High Performance

HC => High Capcity

Resetting unrecoverable_time in v$datafile

When you create a table or index as NOLOGGING, the database does not generate redo log records for the operation. Thus, you cannot recover objects created with NOLOGGING, even if you run in ARCHIVELOG mode. If you cannot afford to lose tables or indexes created with NOLOGGING, then make a backup after the unrecoverable table or index is created. 

Raw Device Setup for ASM

Scenario: 

Today one of the DBA requested to have the ASM instance based on raw devices. I provided the below  as a quick demo.

Using OCLUMON to analyze Cluster Health

Cluster Health Monitor & OCLUMON

The Cluster Health Monitor (CHM) stores real-time operating system metrics in the CHM repository that you can use for later triage with the help of Oracle Support should you have cluster issues.

It consists of System Monitor Service, Cluster Logger Service, CHM Repository

ASMCA tabs Volume and ASM Cluster File System are grayed out

Scenario:
DBA wanted to create the ACFS but got the Volume and Cluster File System grayed out.

Solution:

ORA-01427 While running DBMS_HM.RUN_CHECK

SQL> BEGIN
  2      DBMS_HM.RUN_CHECK('Dictionary Integrity Check', 'HC1');
  3  END;
  4  /
BEGIN
*

Configuring ACFS (Standalone Server) 11gR2

Scenerio:

Client has standalone GI installation  and wants to check the ACFS feature.

Brief:

Oracle ACFS is designed as a general-purpose, single-node and clusterwide file system that delivers support for customer file system application data maintained outside of Oracle Database. Users and applications can access and manage Oracle ACFS using native operating system file system application programming interfaces (APIs) and command-line interface (CLI) tools. Users can also manage Oracle ACFS with Oracle Enterprise Manager.

Configuring Oracle Grid Infrastructure for a standalone server (11gR2)

Scenario:

1- One of our DBA already had installed Oracle (11gR2 11.2.0.4) RDBMS on Linux 5.8

2- After some times, client requested to use the ASM as test to know its capabilities so DBA was given the task to have the GI installed and configured without disturbing the current environment.

Exadata: Defining the Threshold for Exadata Cell

In Exadata an alert is automatically triggered when a predefined hardware or software issue is detected, or when a metric exceeds a threshold. By default, there are no thresholds defined but you can define your own if you want.

Exadata: Index Elimination

Brief:

In some cases, Exadata Smart Scan may deliver better query performance than using an index. However, this may not always be the case. Even in cases where an index delivers better query performance you might choose to remove it if you determine that the un-indexed query performance is acceptable and the index is otherwise unnecessary. Removing unnecessary indexes saves space and improves DML performance by eliminating the maintenance operations associated with the index. 

Exadata: Using Hybrid Columnar Compression

Scenario:

We have a table TRANSACTION_LOG with more than 10 million rows , estimated size of table is 640MB. We want to  examine the performance of Exadata Hybrid Columnar Compression and want compare predicted and actual compression ratios using an example dataset, so that we can use HCC for our logging tables. We also want to examine how bulk data loading and query operations are affected using the different compression modes.

Exadata: Using Integrated Lights Out Manager (ILOM) CLI

What is ILOM?

Oracle ILOM is integrated service processor hardware and software that is preinstalled on Oracle servers, including the storage and database servers in Oracle Exadata. The service processor runs its own embedded operating system and has a dedicated Ethernet port to provide out-of-band server monitoring and management capabilities. Oracle ILOM can be accessed via a browser-based web interface or a command-line interface, and it also provides an SNMP interface and IPMI support.

Exadata: Configuring SSH Equivalency to use dcli

Exadata comes with many storage servers, The Cell Administration can be done on the command line as user celladmin with CellCLI,but it would be an annoying task to do all commands many times. Therefore, dcli was introduced to enable us to control multiple Cells with a single command for which you have to configure the SSH.

Exadata: Physical Disks, LUNs, and Cell Disks Mapping

As an Exadata DMA, you should know to map the physical disks in an Exadata Storage Server to Logical Units (LUNs) and map LUNs to Exadata cell disks in order to understand how Exadata’s disks are presented as usable storage entities.

Exadata: Locating Oracle Cluster Registry and Voting Disks

On Exadata, Oracle has elected to store the Oracle Cluster Registry (OCR) and voting disks on Oracle ASM disk groups, mapped to Exadata storage server grid disks. The OCR and voting disks are typically stored in the DBFS_DG ASM disk group and associated grid disks.

How to load flat files containing Arabic (any non-English) characters using SQLLDR

When you need to load non-ASCII data from a flat file into an Oracle database, the primary tool of choice is SQL*Loader, but unfortunately it does not automatically recognize the character encoding scheme of non-ASCII text (Arabic) files, you have to specify the correct encoding for the flat file in order to ensure a successful load. For that purpose, NLS_LANG needs to be set before launching SQL*Loader. If encoding type of the flat file is unknown, unfortunately there is no 100% reliable way to determine what encoding is used in that text file.

ORA-01704: String Literal Too Long. Cause: String Literal is Longer Than 4000 Characters

A text literal can have a maximum length of 4000 bytes.  In order to update a column greater than 4K, bind variables must be used and in the case of lobs if the data set is greater than 32k, use DBMS_LOB for piece wise manipulation.

Exadata: Restoring OCR and Vote Disks

Oracle allows you to restore your Oracle Cluster Registry using the ocrconfig –restore command. This command accepts a backup OCR file as its argument, which you can choose by running ocrconfig –showbackup and selecting the appropriate backup copy that resides on your compute node file system.

Getting Flash Storage info

Exadata flash storage is provided by Sun Flash Accelerator F20 PCI flash cards. Each PCI flash card has a device partitioned per FDom, yielding 16 flash devices. These flash devices are manifested as ExadataStorage Server flash disks and used for Smart Flash Cache and Smart Flash Logging.

Getting Disk Storage Details on the Exadata Storage Servers

If you want to know how storage is allocated, presented, and used in the Exadata storage cell, use below commands to get the related information.

Getting Storage Server Architecture Details

Exadata Storage Servers are self-contained storage platforms that house disk storage for an Exadata Database Machine and run Oracle’s Cell Services (cellsrv) software. A single storage server is also commonly referred to as a cell.

Restoring OCR and Vote (11gR2 Linux)

Restoring OCR from backup

Testing Scenario: 

OCR is located on +OCR_VOTE diskgroup which was created with external redundancy, 

OCRVOTE is corrupted or diskgroup where OCRVOTE is located has problem.

Fact Sheet - Hekaton - TimesTen

Upon the request of one of my friend, below is the quickly prepared fact sheet for Microsoft's Hekaton and Oracle's TimesTen, will be updated more soon.