Oracle GoldenGate Overview

Oracle Corp. has acquired so many companies of late that it's getting to be a job to keep up with things. GoldenGate is one of the products you'll want to stay on top of. It's still kind of under the radar, but it's going to be big. Really big.

Oracle (the corporation) has acquired so many other companies as of late that it’s getting to be job in and of itself to keep up with things. But with GoldenGate, this is one of the products you’ll want to be familiar with. This is one of those products or features where its usage and user base (at least within Oracle) is still kind of under the radar, but it’s going to be big. Really big.

We all know about data within a database, and how to put it in via a database language such as SQL. And we’re familiar with SQL reaching out to another database. But that’s something typically done ad hoc or via a job. How do you “flow” data from one database to another, hands free? That is, an insert on Table A in Database A gets propagated to Table A on Database B? Right now, Oracle Streams (named that I’m sure to reflect the idea of flowing streams of data) fulfills this requirement.

Oracle Streams comes with about, oh, 8,000 data dictionary views and DBMS built-ins, and is certainly not lacking in terms of complexity and moving parts. Additionally, a Streams implementation can include elements of Data Guard (specifically in the Downstream Capture configuration). With respect to Streams and Data Guard, GoldenGate encompasses both and can either augment or replace either tool.

So, what does it take to use GoldenGate?

First off, it takes money, as in more of it, because of how Oracle has chosen to license it. Its cost is tied to the database server licensing metric, so for one processor (perpetual), you’re looking at $17,500 for licensing and $3,850 for the first year’s support. Streams, in comparison, is included in whichever edition you use.

Second, using Linux as the host operating system, it’s a matter of downloading and uncompressing a tar file. Most everything for running GoldenGate is based out of a “ggs” directory. Running on Windows is a bit more involved because of services.

Finally, you have to get used to a new language replete with hundreds of new keywords using a not too complex syntax. The running of GoldenGate can be 100% command line interface. There is an optional add on product (Director) which offers a GUI-based centralized management feature.  do two things for you in terms of replication. Part of replication includes having a downstream or replicated copy (think of a restored backup, however you want to get that in place) of the target. If you choose, GoldenGate can perform the initial load, although it is recommended that you use native RDBMS tools for this step.

The second (and main) task is running and managing the replication process. Replicate how? From just one database to another, or something along the lines of what Streams can do (one-way, bi-directional, N-way)? The topology diagram below should cover just about every possible way you could envision replicating data.

Another neat feature of GoldenGate is that it practically couldn’t care less which database system you’re coming from or going to. It’s almost like a Mac: everything just works. GoldenGate mines transactions from what it refers to as transaction logs (so obviously in Oracle’s case, the redo logs). The extraction process creates “trail” files GoldenGate then sends to the RMTHOST (the remote host, and congratulations if you’re new to GoldenGate: you’ve just seen your first keyword). The configuration workflow diagram shown below highlights this agnostic approach to the database systems involved.

The reference to “Data Pump” in the diagram is not the same Data Pump used in Oracle. The concept is the same though (data being moved at a relatively high speed). Similar to Streams setup, access to the remote host is much easier to configure/perform if the admin account username is the same on all systems.

Under Step 4, you see the word Replicat. That is not a typo: Replicat is the word/term/process used in and by GoldenGate. If using GoldenGate for your replication needs, Replicat will become very familiar.

This leads us to the GoldenGate Software Command Interface, otherwise known as ggsci. GGSCI is to GoldenGate as SQL*Plus is to Oracle. The table below (partial listing) gives you an idea of what GGSCI uses word-wise. Note the INFO word in the left column: plenty of online (well, at least in your session) help is available simply by typing “help ” at the command line.

One of the downsides of Streams processing, where data is typically dealt with on a row-by-row basis for a logical change record (LCR), is that bulk loads can cause performance problems. GoldenGate, and Streams to a degree, has built-in access to SQL*Loader type APIs.

Timing is everything, so how does GoldenGate keep track of transactions? Just like everyone does by using a change number (for example, Oracle’s SCN, MSSQL’s LSN). GoldenGate’s internal bookkeeping “checkpoint” is based on a CSN, or Commit Sequence Number. Knowing the CSN allows you to pick up after a stopped or interrupted process.

For learning how to use GoldenGate, you definitely need to put hands on the keyboard and bang away. Understanding the relationship of parameter and definition files – at an abstract level – is simple; the rub is getting used to GoldenGate’s language and keywords. The sample data in the demo files included with the tool is seriously lacking. For teaching this class, one of the things I incorporate is running a stream of data. I mean, you want to see this work and observe its work in progress. Simply flowing two or three records in the demo files is fine for getting things to work in the first place, but after that, let’s fire this thing up and see what it does.

For the money it costs, especially if you are considering replacing Streams, you’re going to want to see a couple of advantages or benefits over what you’re doing now: a better/easier setup and management, and data being replicated at near real time. No product will ever be exactly real time, but we can get pretty close.

Filed Under: DBA, Oracle 11g, oracle 12c on الخميس، 29 مارس 2018

Oracle NoSQL Database (Interview)

For the last few years, the world of NoSQL databases has been filled with exciting new projects, ambitious claims, and plenty of chest beating. The hypesters said the new NoSQL software packages offered tremendous performance gains by tossing away all of the structure and paranoid triple-checking that database creators had lovingly added over the years. Reliability? It's overrated, said the new programmers who didn't run serious business applications for Wall Street banks but trafficked in trivial, forgettable data about people's lives. Tabular structure? It's too hidebound and limiting. If we ignore these things, our databases will be free and insanely fast.

Alas, just as the summer of love ended and reality set in, the boundary-free experimentation with NoSQL databases is slowly being brought down to earth. Oracle, the developer of top-notch, bulletproof SQL databases, has arrived at the hippie fest with a solid, practical, and very Oracle-like NoSQL server. While the crazy dreamers can continue to craft NoSQL data stores, serious people will want to take a look at Oracle's version. It offers many of the features that make NoSQL fun but also the solid performance promises that tend to come from big, serious teams of engineers. NoSQL pioneers will want to tell themselves that imitation is the sincerest form of flattery.

The arrival of this product might be a surprise to NoSQL fans who have listened to old-school DBAs talk with pride about Oracle databases, but Oracle has been slowly moving down this path for some time. Five years ago, the company bought Sleepycat Software, the creators of the open source Berkeley DB, a tool with a long and rich tradition of flexible, key-value storage for C and lately Java programmers. This same Berkeley DB technology is said to be at the core of Oracle NoSQL Database, although it seems to be a complete rewrite.

Oracle NoSQL: Practically ACID

The fun part of Oracle NoSQL is the key-value structure. You don't need to define a schema or lock yourself into a big tabular architecture. You just create keys and attach a bag of bits to them. You might link your key to a string or an image file or anything. The database accepts the bytes and doesn't think much about the contents.

Oracle breaks up the key into major and minor parts. You can think of the major part as the object pointer and the minor part as the fields in the record. So you might put a name and Social Security number into the major parts of the key and other strings like the street address and ZIP code into the minor parts. It's comparable to the way that some other NoSQL tools let you think of the value in the pair as being an object with multiple fields. Oracle just uses the term "minor key" for the names of the fields.

The serious part of Oracle NoSQL is a practical approximation of ACID compliance, the standard that SQL databases like to offer. ACID means "Atomic, Consistent, Isolated, Durable transactions," and there's a robust debate about just what this translates to in excruciating detail. Most NoSQL systems promise a different acronym, BASE, which stands for "Basically Available, Soft State, and Eventually Consistent." In other words, you'll probably get the right answer except when you don't.

There will be plenty of debate about whether Oracle NoSQL offers real ACID compliance. The promises aren't as all-encompassing as they are with SQL databases. You only get an ACID promise when you write data attached to the same major part of the key. For example, you could change the address and ZIP code of the same person and get an ACID guarantee because both parts are stored under the same major key. But you get no guarantee that changes to two separate people will remain consistent. In other words, a bank could use Oracle NoSQL to store personnel records, but not to safely transfer cash between accounts because there's no ACID guarantee that the money won't get lost along the way.

Oracle NoSQL is able to make this promise because it guarantees that one master machine will hold all of the minor keys associated with a major key. Attach any collection of fields to a major key defining a person, and all of this data will end up in the same node in the cluster. But the data from different major keys could end up on different machines, and Oracle NoSQL doesn't have a mechanism to ensure that the data will be written to both simultaneously.

You can also add replication and sharding, which Oracle calls "partitioning." In essence, you arrange the nodes in a rectangle where the sharding occurs along one axis and the replication occurs across the other. If you want more reliability and faster reads, you add more machines along the replication axis. If you want less contention, you add more machines along the partitioning axis. Oracle NoSQL handles most of this configuration for you.

Again, this structure stores data with Oracle-grade seriousness. If you don't want the slacker-grade promise of eventual consistency offered by so many other NoSQL stores, Oracle NoSQL will deliver absolute consistency across all of the machines replicating a node. You'll pay for this in write performance, of course, but it's your choice.

This is more than a binary decision, by the way. You can tell Oracle NoSQL to sign off on the write after one, all, or a simple majority of the nodes are finished sending the data to disk. The documentation calls this feature a durability policy.

Some of this flexibility is available to you, the programmer, if you have the time to worry about it. All of the key-value pairs come with a version number, which you can watch yourself if you want to play your own games with replication. This can be helpful if you're trying to goose performance when modifying records.

on الأربعاء، 28 مارس 2018

SQLite Tutorial I

 SQLite Tutorial

This Post helps you understand what is SQLite, how it differs from SQL, why it is needed and the way in which it handles the applications Database.

SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is one of the fastest-growing database engines around, but that's growth in terms of popularity, not anything to do with its size. The source code for SQLite is in the public domain.

What is SQLite?

SQLite is an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. It is a database, which is zero-configured, which means like other databases you do not need to configure it in your system.

SQLite engine is not a standalone process like other databases, you can link it statically or dynamically as per your requirement with your application. SQLite accesses its storage files directly.

Why SQLite?

• SQLite does not require a separate server process or system to operate (serverless).
• SQLite comes with zero-configuration, which means no setup or administration needed.
• A complete SQLite database is stored in a single cross-platform disk file.
• SQLite is very small and light weight, less than 400KiB fully configured or less than 250KiB with optional features omitted.
• SQLite is self-contained, which means no external dependencies.
• SQLite transactions are fully ACID-compliant, allowing safe access from multiple processes or threads.
• SQLite supports most of the query language features found in SQL92 (SQL2) standard.
• SQLite is written in ANSI-C and provides simple and easy-to-use API.
• SQLite is available on UNIX (Linux, Mac OS-X, Android, iOS) and Windows (Win32, WinCE, WinRT).

SQLite A Brief History

• 2000 - D. Richard Hipp designed SQLite for the purpose of no administration required for operating a program.
• 2000 - In August, SQLite 1.0 released with GNU Database Manager.
• 2011 - Hipp announced to add UNQl interface to SQLite DB and to develop UNQLite (Document oriented database).

SQLite Limitations

There are few unsupported features of SQL92 in SQLite which are listed in the following table.

Sr.No.	Feature & Description
1	RIGHT OUTER JOIN Only LEFT OUTER JOIN is implemented.
2	FULL OUTER JOIN Only LEFT OUTER JOIN is implemented.
3	ALTER TABLE The RENAME TABLE and ADD COLUMN variants of the ALTER TABLE command are supported. The DROP COLUMN, ALTER COLUMN, ADD CONSTRAINT are not supported.
4	Trigger support FOR EACH ROW triggers are supported but not FOR EACH STATEMENT triggers.
5	VIEWs VIEWs in SQLite are read-only. You may not execute a DELETE, INSERT, or UPDATE statement on a view.
6	GRANT and REVOKE The only access permissions that can be applied are the normal file access permissions of the underlying operating system.

SQLite Commands

The standard SQLite commands to interact with relational databases are similar to SQL. They are CREATE, SELECT, INSERT, UPDATE, DELETE and DROP. These commands can be classified into groups based on their operational nature −

DDL - Data Definition Language

Sr.No.	Command & Description
1	CREATE Creates a new table, a view of a table, or other object in database.
2	ALTER Modifies an existing database object, such as a table.
3	DROP Deletes an entire table, a view of a table or other object in the database.

DML - Data Manipulation Language

Command	Description
INSERT	Creates a record
UPDATE	Modifies records
DELETE	Deletes records

DQL - Data Query Language

Sr.No.	Command & Description
1	SELECT Retrieves certain records from one or more tables

SQLite is famous for its great feature zero-configuration, which means no complex setup or administration is needed. This chapter will take you through the process of setting up SQLite on Windows, Linux and Mac OS X.

Install SQLite on Windows

• Step 1 − Go to SQLite download page, and download precompiled binaries from Windows section.
• Step 2 − Download sqlite-shell-win32-*.zip and sqlite-dll-win32-*.zip zipped files.
• Step 3 − Create a folder C:\>sqlite and unzip above two zipped files in this folder, which will give you sqlite3.def, sqlite3.dll and sqlite3.exe files.
• Step 4 − Add C:\>sqlite in your PATH environment variable and finally go to the command prompt and issue sqlite3 command, which should display the following result.

C:\>sqlite3
SQLite version 3.7.15.2 2013-01-09 11:53:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>

Install SQLite on Linux

Today, almost all the flavours of Linux OS are being shipped with SQLite. So you just issue the following command to check if you already have SQLite installed on your machine.

$sqlite3
SQLite version 3.7.15.2 2013-01-09 11:53:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>

If you do not see the above result, then it means you do not have SQLite installed on your Linux machine. Following are the following steps to install SQLite −

• Step 1 − Go to SQLite download page and download sqlite-autoconf-*.tar.gz from source code section.
• Step 2 − Run the following command −

$tar xvfz sqlite-autoconf-3071502.tar.gz
$cd sqlite-autoconf-3071502
$./configure --prefix = /usr/local
$make
$make install

The above command will end with SQLite installation on your Linux machine. Which you can verify as explained above.

Install SQLite on Mac OS X

Though the latest version of Mac OS X comes pre-installed with SQLite but if you do not have installation available then just follow these following steps −

• Step 1 − Go to SQLite download page, and download sqlite-autoconf-*.tar.gz from source code section.
• Step 2 − Run the following command −

$tar xvfz sqlite-autoconf-3071502.tar.gz
$cd sqlite-autoconf-3071502
$./configure --prefix=/usr/local
$make
$make install

The above procedure will end with SQLite installation on your Mac OS X machine. Which you can verify by issuing the following command −

$sqlite3
SQLite version 3.7.15.2 2013-01-09 11:53:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>

Finally, you have SQLite command prompt where you can issue SQLite commands for your exercises.

Filed Under: database, SQL, SQL Lite on الأربعاء، 8 نوفمبر 2017

Dapper

What’s Dapper?

Dapper is a simple object mapper for .NET and own the title of King of Micro ORM in terms of speed and is virtually as fast as using a raw ADO.NET data reader. An ORM is an Object Relational Mapper, which is responsible for mapping between database and programming language.

Dapper extend the IDbConnection by providing useful extension methods to query your database.

How Dapper Works?

It is a three step process.

• Create an IDbConnection object.
• Write a query to perform CRUD operations.
• Pass query as a parameter in Execute method.

Installation

Dapper is installed through NuGet: https://www.nuget.org/packages/Dapper

PM> Install-Package Dapper

Requirement

Dapper work with any database provider since there is no DB specific implementation.

Methods

Dapper will extend your IDbConnection interface with multiple methods:

• Execute
• Query
• QueryFirst
• QueryFirstOrDefault
• QuerySingle
• QuerySingleOrDefault
• QueryMultiple

string sqlInvoices = "SELECT * FROM Invoice;";

string sqlInvoice = "SELECT * FROM Invoice WHERE InvoiceID = @InvoiceID;";

string sp = "EXEC Invoice_Insert";




using (var connection = My.ConnectionFactory())

{

 var invoices = connection.Query<Invoice>(sqlInvoices).ToList();

 var invoice = connection.QueryFirstOrDefault(sqlInvoice, new {InvoiceID = 1});

 var affectedRows = connection.Execute(sp, new { Param1 = "Single_Insert_1" }, commandType: CommandType.StoredProcedure);

}

Parameter

Execute and queries method can use parameters from multiple different ways:

• Anonymous
• Dynamic
• List
• String

// Anonymous

var affectedRows = connection.Execute(sql,

                    new {Kind = InvoiceKind.WebInvoice, Code = "Single_Insert_1"},

                    commandType: CommandType.StoredProcedure);




// Dynamic

DynamicParameters parameter = new DynamicParameters();




parameter.Add("@Kind", InvoiceKind.WebInvoice, DbType.Int32, ParameterDirection.Input);

parameter.Add("@Code", "Many_Insert_0", DbType.String, ParameterDirection.Input);

parameter.Add("@RowCount", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);




connection.Execute(sql,

 new {Kind = InvoiceKind.WebInvoice, Code = "Single_Insert_1"},

 commandType: CommandType.StoredProcedure);




// List

connection.Query<Invoice>(sql, new {Kind = new[] {InvoiceKind.StoreInvoice, InvoiceKind.WebInvoice}}).ToList();




// String

connection.Query<Invoice>(sql, new {Code = new DbString {Value = "Invoice_1", IsFixedLength = false, Length = 9, IsAnsi = true}}).ToList();

Result

The result returned by queries method can be mapped to multiple types:

• Anonymous
• Strongly Typed
• Multi-Mapping
• Multi-Result
• Multi-Type

string sql = "SELECT * FROM Invoice;";




using (var connection = My.ConnectionFactory())

{

    connection.Open();




    var anonymousList = connection.Query(sql).ToList();

    var invoices = connection.Query<Invoice>(sql).ToList();

}

Utilities

• Async
• Buffered
• Transaction
• Stored Procedure

// Async

connection.QueryAsync<Invoice>(sql)




// Buffered

connection.Query<Invoice>(sql, buffered: false)




// Transaction

using (var transaction = connection.BeginTransaction())

{

 var affectedRows = connection.Execute(sql,

  new {Kind = InvoiceKind.WebInvoice, Code = "Single_Insert_1"},

  commandType: CommandType.StoredProcedure,

  transaction: transaction);




 transaction.Commit();

}




// Stored Procedure

var affectedRows = connection.Execute(sql,

 new {Kind = InvoiceKind.WebInvoice, Code = "Single_Insert_1"},

 commandType: CommandType.StoredProcedure);

Filed Under: .NET, c# on