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.

Setting up Data Guard 11gR2 (RAC to RAC)

Purpose: 
Setting the Data Guard for RAC primary to RAC Standby.

Assumptions:
1- Primary site has 2 Node RAC 11gR2 installation on Oracle Linux 5.6. Primary database name is PROD
2- Standby site has Oracle GI 11gR2 with RDBMS software only installation on Linux 5.6 on 2 node Linux 5.6. Standby database name will be STAN.

RMAN: Tape backups as Expired while files physically exist

Sometimes all current backups are marked as expired when performing a crosscheck backup while backupsets physically exist and these backupsets are not expired according to Media Manager like Legato or Netbackup. You should first check the Parms used in allocate channel command are correct, if they are correct you should enable the debug for RMAN for more details.
RMAN> DEBUG ON;

RMAN> ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE 'sbt_tape' SEND 'NB_ORA_CLIENT=OR-11, NB_ORA_SERV=mg-2' trace 1;

RMAN> crosscheck backup;
RMAN> DEBUG OFF;

Check sbtio.log in user_dump_dest location which will have the errors during the time of crosscheck.

Possible Reasons:

1- Some times errors are due to the SBT library, enabling debug can provide more insight.

2- CROSSCHECK was run with the wrong PARMS settings. RMAN will query the media manager and get response back that the backup piece cannot be found. It will then set the status of the piece to Expired.

RMAN doesn't have a concept of what media manager it is using. As long as you specify device type SBT, it's up to the user to make sure that the configuration and PARMS settings are correct. RMAN will just query the media manager generically, as it only knows that the backup was made to SBT.

12c: EM: OMS Server Fails - error nm home replaced

Environment: Oracle Enterprise Manager 12c, Windows 2008 R2
Error:Below error occured on one of the client environment
C:\Windows\system32>%OMS_HOME%/bin/emctl start oms
Oracle Enterprise Manager Cloud Control 12c Release 3
Copyright (c) 1996, 2013 Oracle Corporation. All rights reserved.
Windows service OracleManagementServer_EMGC_OMS1_1 failed to be started
Oracle Management Server is Down
Please check E://gc_inst/em/EMGC_OMS1\sysman\log\emctl.log for error details


Log file showed below

2013-12-22 10:30:54,300 [main] INFO wls.OMSController main.219 - Executing emctl command : start
2013-12-22 10:30:58,536 [main] INFO commands.BaseCommand getEnvProps.456 - nm home replaced : E:/gc_inst/NodeManager/emnodemanager
2013-12-22 10:30:58,581 [main] INFO commands.StartCommand startOMS.371 - net start return code is 2
2013-12-22 10:30:58,582 [main] ERROR commands.BaseCommand logAndPrint.620 - Windows service OracleManagementServer_EMGC_OMS1_1 failed to be started
2013-12-22 10:31:00,653 [main] INFO commands.BaseCommand printMessage.404 - statusOMS finished with result: 8

Solution:
1- Check Repository Database and Listener are up and running and Repository database is registered with the listener (lsnrctl services), start the Repository Database and its listener if required.

2- Check you can connect to Repository Database as 'SYSMAN' using SQLPLUS.

3- Shutdown the OMS

C:\Windows\system32>%OMS_HOME%/bin/emctl stop oms -force
Oracle Enterprise Manager Cloud Control 12c Release 3
Copyright (c) 1996, 2013 Oracle Corporation. All rights reserved.
Stopping WebTier...
WebTier Successfully Stopped
Stopping Oracle Management Server...
Node Manager Not Running
Oracle Management Server is Down

4- Start the OMS

C:\Windows\system32>%OMS_HOME%/bin/emctl start oms

Oracle Enterprise Manager Cloud Control 12c Release 3

Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.

Windows service OracleManagementServer_EMGC_OMS1_1 successfully started

Oracle Management Server is Up

Exadata: Hybrid Columnar Compression & Considerations

Brief:

Exadata Hybrid Columnar Compression (EHCC) is a type of segment compression. With HCC, data is organized for storage by columns for a set of rows, this collection of groups of rows organized and compressed together is what leads to the “hybrid” tag. 

Exadata: Migration Considerations and Strategies

Once the Exadata Machine is ready, you are ready to migrate your data from your legacy hardware to Exadata. Please note that there is no difference between data segments stored in Exadata Storage Server cells and those in conventional storage unless Exadata Hybrid Columnar Compression (EHCC) is being used.  So you can use the same tools and techniques that you normally use for migration of non-Exadata systems but with some considerations. 

RMAN: Incremental Backup

Below explains a method which can be used to restore oracle incremental level backup to restore rman backups on new server to  significantly reduce restore time.
Following steps are involved.

Exadata: Storage Indexes

Brief: 
Storage Indexes are not indexes that are stored in the database like Oracle’s traditional B-Tree or bitmapped indexes. A storage index is a memory-based (heap of cellsrv) structure and its purpose is to tell Oracle with absolute certainty that a requested extent does not exist in specific locations, and Exadata uses this information to bypass I/O requests to the physical storage locations. 

Exadata: Shuting down/Rebooting cell without affecting ASM

Sometimes it becomes necessary to power down or reboot the cell to perform maintenance while one or more databases are running. In this situation You need to verify that taking the storage server offline will not impact Oracle ASM disk group and database availability. The ability to take Oracle Exadata Storage Server offline without affecting database availability depends on the level of Oracle ASM redundancy used on the affected disk groups, and the current status of disks in other Oracle Exadata Storage Servers that have mirror copies of data as Oracle Exadata Storage Server to be taken offline.

Exadata: Calculating Reserve Space for Cell/Disk Failure Coverage

ASM disk groups in Exadata are defined as either normal or high redundancy. Normal redundancy provides for two copies of file extents while high redundancy provides for three copies of file extents. Each disk is partnered with a set of other disks in other failure groups to ensure that file extent copies are stored in separate failure groups and the disk group can tolerate the loss of one disk (or one cell) in normal redundancy or two disks (or two cells) in high redundancy disk groups.

Exadata: Tracing Oracle I/O Requests on Exadata DB Nodes

With Oracle Exadata, database IO is handled exclusively by the cellsrv process on storage servers. The database nodes bundle IO requests into iDB messages and transmit these over the infiniband network grid to storage server. To confirm where physical database IO is performed on Oracle Exadata, we can use strace to trace system calls from an Oracle instance process. strace is a tool for tracing system calls and signals . It intercepts and records the system calls made by a running process. strace can print a record of each system call, its arguments, and its return value.

Exadata: Taking System Backup using LVM Snapshots

Recently, Oracle began shipping Exadata with the Linux LVM configured for managing file system storage on the database servers. Logical volume managers provide an abstraction layer for physical diskpartitions similar to the way ASM does for its underlying physical storage devices. 

LVM snapshots eliminate many of the typical challenges we face with simple backups using the tar command or third-party backup products. For example, depending on the amount of data in the backup set, file system backups can take quite a while to complete. These backups are not consistent to a point in time, meaning that if you must restore a file system from backup, the data in your files will represent various points in time from the beginning of the backup process to its end. Applications that continue to run during the backup cycle can hold locks on files, causing them to be skipped (not backed up). And once again, open applications will inevitably

make changes to data during the backup cycle. Even if you are able to back up these open files, you have no way of knowing if they are in any usable state unless the application is shut down before the backup is taken.

A snapshot volume is a special type of volume that presents all the data that was in the volume at the time the snapshot was created. You can create a temporary LVM partition if you have enough free space in the volume group.

In my test environment , I had not space space for the volume so I had to create the new volume for the example purpose. 

[root@exadb ~]# pvdisplay
--- Physical volume ---
PV Name /dev/sda2
VG Name VolGroup00
PV Size 49.90 GB / not usable 25.37 MB
Allocatable yes (but full)
PE Size (KByte) 32768
Total PE 1596
Free PE 0
Allocated PE 1596
PV UUID 6Fm5nC-4GGM-w8Ec-pqDQ-t3tn-usjP-RgnhPo
[root@exadb ~]# vgdisplay
--- Volume group ---
VG Name VolGroup00
System ID
Format lvm2
Metadata Areas 1
Metadata Sequence No 3
VG Access read/write
VG Status resizable
MAX LV 0
Cur LV 2
Open LV 2
Max PV 0
Cur PV 1
Act PV 1
VG Size 49.88 GB
PE Size 32.00 MB
Total PE 1596
Alloc PE / Size 1596 / 49.88 GB
Free PE / Size 0 / 0
VG UUID 6o0Qaa-sWkO-Fzpc-05Nx-cY91-IID0-zb7Ous

I added a new drive (50G) to my server and created the partition (sdb1) for it using fdisk

then verified the physical volume.

[root@exadb ~]# pvdisplay /dev/sdb1
"/dev/sdb1" is a new physical volume of "50.00 GB"
--- NEW Physical volume ---
PV Name /dev/sdb1
VG Name
PV Size 50.00 GB
Allocatable NO
PE Size (KByte) 0
Total PE 0
Free PE 0
Allocated PE 0
PV UUID SUDSzj-2qI1-7apF-a916-fG0y-mfeE-djrJhh

Having created the physical volumes the next step is to create the volume group. 

[root@exadb ~]# vgcreate db_sysbkp /dev/sdb1 -v 

    Wiping cache of LVM-capable devices

    Wiping cache of LVM-capable devices

    Adding physical volume '/dev/sdb1' to volume group 'db_sysbkp'

    Archiving volume group "db_sysbkp" metadata (seqno 0).

    Creating volume group backup "/etc/lvm/backup/db_sysbkp" (seqno 1).

  Volume group "db_sysbkp" successfully created

-- verify the volume group has been created

See the attributes of newly created volume group

[root@exadb ~]# vgdisplay db_sysbkp
--- Volume group ---
VG Name db_sysbkp
System ID
Format lvm2
Metadata Areas 1
Metadata Sequence No 1
VG Access read/write
VG Status resizable
MAX LV 0
Cur LV 0
Open LV 0
Max PV 0
Cur PV 1
Act PV 1
VG Size 50.00 GB
PE Size 4.00 MB
Total PE 12799
Alloc PE / Size 0 / 0
Free PE / Size 12799 / 50.00 GB
VG UUID OwxfW5-XEY2-BJuP-ylyI-E2AR-d1aG-S3DIdC

Up to this point you have just got the new volume group which may be in your case already available. Below is the process to use the LVM snapshot once you have sufficient space in any volume group.

Process to take System Backup

1- Create destination (SAN/NAS/NFS) for the final copy of backups. You can create the logical volume in a volume group for this purpose. Determine the volume group and create the logical volume. In my case I already had the volume group "VGdb" with 50GB space. I used the command lvcreate  to create logical volume in it.

[root@exadb ~]# lvcreate -L 25G -n /dev/VGdb/dbbkp
   Logical volume "dbbkp" created

-L option allows specification of the logical volume size in Megabytes, Terabytes, Petabytes or Exabytes
-n option, which is used to specify a name for the logical volume. If this is not specified, the logical volume name defaults to the next default logical volume number.

2- After crating logical volume verify the VGdb volume group allocations

[root@exadb ~]# vgdisplay VGdb
--- Volume group ---
VG Name VGdb
System ID
Format lvm2
Metadata Areas 1
Metadata Sequence No 2
VG Access read/write
VG Status resizable
MAX LV 0
Cur LV 1
Open LV 0
Max PV 0
Cur PV 1
Act PV 1
VG Size 50.00 GB
PE Size 4.00 MB
Total PE 12799
Alloc PE / Size 6400 / 25.00 GB
Free PE / Size 6399 / 25.00 GB
VG UUID OwxfW5-XEY2-BJuP-ylyI-E2AR-d1aG-S3DIdC

3- Create a file system, now we can create a new file system on the dbbkp partition created in step 1.
[root@exadb sbin]# mkfs.ext3 -m 0 -b 4096 /dev/VGdb/dbbkp
mke2fs 1.39 (29-May-2006)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
3276800 inodes, 6553600 blocks
0 blocks (0.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=4294967296
200 block groups
32768 blocks per group, 32768 fragments per group
16384 inodes per group
Superblock backups stored on blocks: 
        32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208, 
        4096000

Writing inode tables: done                            
Creating journal (32768 blocks): done
Writing superblocks and filesystem accounting information: done

This filesystem will be automatically checked every 35 mounts or

180 days, whichever comes first.  Use tune2fs -c or -i to override.

-b block-size: Specifies the size of blocks in bytes. Valid block size vales are 1024, 2048 and 4096 bytes per block.
-m reserved-blocks-percentage: Specifies the percentage of the file system blocks reserved for the super-user.

4- Create the target directory and mount the new file system where backup will be placed.

[root@exadb dev]# mkdir /mnt/databasebkp

[root@exadb dev]# mount /dev/VGdb/dbbkp /mnt/databasebkp

[root@exadb dev]# df -k /mnt/databasebkp

Filesystem           1K-blocks      Used Available Use% Mounted on

/dev/mapper/VGdb-dbbkp

                      25803068    176200  25626868   1% /mnt/databasebkp

[root@exadb dev]# 

[root@exadb dev]# df -h

Filesystem            Size  Used Avail Use% Mounted on

/dev/mapper/VolGroup00-LogVol00

                       44G   13G   29G  30% /

/dev/sda1              99M   23M   71M  25% /boot

tmpfs                 1.5G  966M  505M  66% /dev/shm

VOBXSHARED            368G  259G  109G  71% /media/sf_VOBXSHARED

/dev/sr0               94K   94K     0 100% /media/20131031_1914

/dev/mapper/VGdb-dbbkp

                       25G  173M   25G   1% /mnt/databasebkp

At this point , now you have the place (/mnt/databasebkp) to stage your backup images. 

5- Know your logical volumes for which, you want to take the backup. In my case I've the volume group "db_sysbkp" (50G) with logical volume "sysbkp" (25G) already . If you don't have logical volume for a volume group you could create like below.

[root@exadb ~]# lvcreate -L 25G -n /dev/db_sysbkp/sysbkp

  Logical volume "sysbkp" created

after crating logical volume you could verify the volume group "db_sysbkp" attributes

[root@exadb ~]# vgdisplay db_sysbkp

--- Volume group ---

  VG Name               db_sysbkp

  System ID             

  Format                lvm2

  Metadata Areas        1

  Metadata Sequence No  2

  VG Access             read/write

  VG Status             resizable

  MAX LV                0

  Cur LV                1

  Open LV               0

  Max PV                0

  Cur PV                1

  Act PV                1

  VG Size               50.00 GB

  PE Size               4.00 MB

  Total PE              12799

  Alloc PE / Size       6400 / 25.00 GB

  Free  PE / Size       6399 / 25.00 GB

  VG UUID               OwxfW5-XEY2-BJuP-ylyI-E2AR-d1aG-S3DIdC

6- Once you know the logical volumes to be backed up (eg; sysbkp) , you can create LVM snapshot.

[root@exadb dev]# lvcreate -L5G -s -n syssnap1 /dev/db_sysbkp/sysbkp

  Logical volume "syssnap1" created

-L parameter determines the size of the snapshot volume

When the datablocks are modified or deleted after the snapshot is created , the original copy

of the block is written to the snapshot. It is important to size the snapshot sufficiently to store 

an original copy of all chaneged blocks. If snapshot runs out of space , it will be deactivated.

From the operating system point of view snapshot will be a block device containing the information from /dev/db_sysbkp/syssnap1 from the moment the snapshot was taken.

[root@exadb dev]# ls d*

dm-0  dm-1  dsp  dvd  dvd-sr0

db_sysbkp:

sysbkp  syssnap1

disk:

by-id  by-label  by-path  by-uuid

[root@exadb dev]# 

See the LVM snapshot info

[root@exadb dev]# lvdisplay /dev/db_sysbkp/syssnap1

  --- Logical volume ---

  LV Name                /dev/db_sysbkp/syssnap1

  VG Name                db_sysbkp

  LV UUID                rOXrZh-OH6B-Cviy-QuNb-4UTJ-mPxQ-1ddC0J

  LV Write Access        read/write

  LV snapshot status     active destination for /dev/db_sysbkp/sysbkp

  LV Status              available

  # open                 0

  LV Size                25.00 GB

  Current LE             6400

  COW-table size         5.00 GB

  COW-table LE           1280

  Allocated to snapshot  0.00% 

  Snapshot chunk size    4.00 KB

  Segments               1

  Allocation             inherit

  Read ahead sectors     auto

  - currently set to     256

  Block device           253:3

7- Mount the snapshot as you would any other file system. First we will create a directory to use as mount point for our snapshot. Then we will mount it.

[root@exadb dev]# mkdir /mnt/snap

[root@exadb ~]# mount /dev/db_sysbkp/syssnap1 /mnt/snap

[root@exadb dev]# mount

/dev/mapper/VolGroup00-LogVol00 on / type ext3 (rw)

proc on /proc type proc (rw)

sysfs on /sys type sysfs (rw)

devpts on /dev/pts type devpts (rw,gid=5,mode=620)

/dev/sda1 on /boot type ext3 (rw)

tmpfs on /dev/shm type tmpfs (rw)

none on /proc/sys/fs/binfmt_misc type binfmt_misc (rw)

sunrpc on /var/lib/nfs/rpc_pipefs type rpc_pipefs (rw)

VOBXSHARED on /media/sf_VOBXSHARED type vboxsf (gid=500,rw)

/dev/sr0 on /media/20131031_1914 type iso9660 (ro,nosuid,nodev,uid=0)

/dev/mapper/db_sysbkp-sysbkp on /mnt/sysbkp type ext3 (rw)

/dev/mapper/db_sysbkp-syssnap1 on /mnt/snap type ext3 (rw)

[root@exadb dev]# 

[root@exadb /]# lvscan

  ACTIVE   Original '/dev/db_sysbkp/sysbkp' [25.00 GB] inherit

  ACTIVE   Snapshot '/dev/db_sysbkp/syssnap1' [5.00 GB] inherit

  ACTIVE            '/dev/VolGroup00/LogVol00' [45.03 GB] inherit

  ACTIVE            '/dev/VolGroup00/LogVol01' [4.84 GB] inherit

Once the snapshot is mounted , we can browse just like any other filesystem. They look and feel

just like the original file system, with one exception that it will not show the files and contents created/modified after the snapshot creation. 

8- Test and verify. I created the a text file in the volume (Original) for the test purpose before the snapshot creation with two lines like below. (You should do it just before step 6 )

[root@exadb sysbkp]# echo "First Line" >> /mnt/sysbkp/f1.txt

[root@exadb sysbkp]# echo "Second Line" >> /mnt/sysbkp/f1.txt

[root@exadb sysbkp]# cat /mnt/sysbkp/f1.txt

First Line

Second Line

After creating and mounting the LVM snapshot (Step 6,7)  I modified the contents of the file like below

[root@exadb sysbkp]# echo "Third Line - added" >> /mnt/sysbkp/f1.txt

[root@exadb sysbkp]# cat /mnt/sysbkp/f1.txt

First Line

Second Line

Third Line - added

You can see that "Third Line - added" appears in the file (Observe this file is on original volume)

Now check the same file on the LVM snapshot volume

[root@exadb mnt]# cd snap

[root@exadb snap]# ls

f1.txt  lost+found

[root@exadb snap]# cat f1.txt

First Line

Second Line

You don't see the "Third Line - added"  (Observe this the snapshot volume)

9- After your testing is done and you are comfortable with the results, finally take the backup using tar command.

[root@exadb snap]# cd /mnt/snap

[root@exadb snap]# tar -pjcvf /mnt/sysbkp/exadb_system_backup.tar.bz2 * /boot --exclude /mnt/sysbkp/exadb_system_backup.tar.bz2 >/tmp/exadata_system_backup.stdout 2>/tmp/exadata_system_backup.stderr

Note that /boot file system does not use the LVM for storage. This file system must be backed up using the tar command. That is not the problem, because /boot file system is fairly small and static so I'm not concerned with these files being modified, locked or open during the backup cycle.

10- Unmount the filesystem for snapshot and remove it.

[root@exadb ~]# umount /mnt/snap

[root@exadb ~]# lvremove /dev/db_sysbkp/syssnap1

Do you really want to remove active logical volume syssnap1? [y/n]: y

  Logical volume "syssnap1" successfully removed

Note: I've used the example to take the backup for the non-Oracle volume but you can take the Oracle related volumes int he same way. 

Avoiding Logon Storm (DoS) on Oracle Database

A Logon storm is a sudden spike in the number of client connection requests. Logon storms can occur due to a variety of factors. They could be malicious like a DoS attack. Or they could occur due to administrative actions – such as a middle tier coming online.