What are Initialization parameters?

Initialize means to set to the value

Or put in the condition appropriate to the start of an operation.

For e.g.: “the counter is initialized to one”

And Initialization is the process of locating and using the defined values for variable data that is used by a computer program. For example, an operating system or application program is installed with default or user-specified values that determine certain aspects of how the system or program is to function.

Oracle uses a file to do this: The Oracle initialization parameter file

When the Oracle database is started, one of the first things it needs to do is read the database initialization parameter file. The parameter file (init.ora) is created by the DBA and defines the overall instance configuration, such as how much memory should be allocated to the instance, the file locations, and internal optimization parameters.

Initialization parameters fall into various functional groups. For example, parameters perform the following functions:

1. Set limits for the entire database
2. Set user or process limits
3. Set limits on database resources
4. Affect performance (these are called variable parameters)

 

Now, there are various types of Initialization Parameters. The Oracle database server has the following types of initialization parameters:

1. Derived Parameters
2. Operating System-Dependent Parameters
3. Variable Parameters (these can be dynamic parameters or any of the preceding ones)

Some initialization parameters are derived, meaning that their values are calculated from the values of other parameters.

For some the valid values or value ranges of some initialization parameters depend upon the host operating system.

The variable initialization parameters offer the most potential for improving system performance. Some variable parameters set capacity limits but do not affect performance. Other variable parameters affect performance but do not impose absolute limits.

 

Here is a sample init.ora file:

db_cache_size = 176000M

db_2k_cache_size = 2048M

db_16k_cache_size = 99000M

db_keep_cache_size = 600000M

db_recycle_cache_size = 64000M

shared_pool_size = 14000M

 

Bibliography sources:

http://www.dba-oracle.com/concepts/initialization_parameters_init_ora.htm

https://www.google.co.in/search?ei=y0D5WfyLCpbCjwPKgJKoBw&q=initialize&oq=initiali&gs_l=psy-ab.3.7.0i67k1l7j0l3.2980.10525.0.14268.23.16.0.0.0.0.721.4964.3-2j4j3j1.10.0….0…1.1.64.psy-ab..18.5.2122….0.94tdrcgZ-As

https://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams001.htm#i1124342

What is Oracle database and oracle server?

An Oracle database is a set of files on disk.

It exists until these files are deleted. There are no practical limits to the size and number of these files, and therefore no practical limits to the size of a database. Access to the database is through the Oracle instance.

The instance is a set of processes and memory structures: it exists on the CPU(s) and in the memory of the server node, and its existence is temporary.

An instance can be started and stopped. Users of the database establish sessions against the instance, and the instance then manages all access to the database. It is absolutely impossible in the Oracle environment for any user to have direct contact with the database. An Oracle instance with an Oracle database makes up an Oracle server.

The processing model implemented by the Oracle server is that of client-server processing often referred to as two-tier. Client-server split, usually with a local area network dividing the two tiers.

The network communications protocol used between the user process and the server process is Oracle’s proprietary protocol, Oracle Net.

The client tier consists of two components:

• Users
• User processes.

The server tier has three components:

• The server processes that execute the SQL,
• The instance,
• The database

Each user interacts with a user process. Each user process interacts with a server process, usually across a local area network. The server processes interact with the instance, and the instance with the database.

Why is normalization required?

Normalization

Database Normalization is a technique of organizing the data in the database. It is a systematic approach of decomposing tables into atomic entities to eliminate data redundancy and undesirable characteristics like Insertion, Update and Deletion Anomalies.

If a database design is not perfect, it may contain anomalies, which are like a bad dream for any database administrator. Managing a database with anomalies is difficult and next to impossible.

Normalization is a method to remove all these anomalies and bring the database to a consistent state.

It is a multi-step process that puts data into tabular form by removing duplicated data from the relation tables.

Normalization is used or serves mainly two purposes,

• Eliminating redundant (useless or repetitive) data.
• Ensuring data dependencies make sense i.e. data is logically stored.

 

Anomalies in DBMS

There are three types of anomalies that occur when the database is not normalized. These are – Insertion, update and deletion anomaly.

• Update anomalies− If data items are scattered and are not linked to each other properly, then it could lead to strange situations. For example, when we try to update one data item having its copies scattered over several places, a few instances get updated properly while a few others are left with old values. Such instances leave the database in an inconsistent state.
• Deletion anomalies− Say we tried to delete a record, but parts of it were left undeleted because of unawareness, the data is also saved somewhere else.
• Insert anomalies– Say we tried to insert data in a record that does not exist at all.

 

Let’s take an example to understand this.

Suppose a manufacturing company stores the employee details in a table named employee that has four attributes: emp_id for storing employee’s id, emp_name for storing employee’s name, emp_address for storing employee’s address and emp_dept for storing the department details in which the employee works. At some point of time the table looks like this:

 

emp_id      emp_name     emp_address     emp_dept

101               Rick                 Delhi                      D001

101               Rick                 Delhi                      D002

123               Maggie            Agra                      D890

166               Glenn              Chennai                D900

166               Glenn              Chennai                D004

The above table is not normalized. We will see the problems that we face when a table is not normalized.

Update anomaly: In the above table we have two rows for employee Rick as he belongs to two departments of the company. If we want to update the address of Rick then we have to update the same in two rows or the data will become inconsistent. If somehow, the correct address gets updated in one department but not in other then as per the database, Rick would be having two different addresses, which is not correct and would lead to inconsistent data.

Insert anomaly: Suppose a new employee joins the company, who is under training and currently not assigned to any department then we would not be able to insert the data into the table if emp_dept field doesn’t allow nulls.

Delete anomaly: Suppose, if at a point of time the company closes the department D890 then deleting the rows that are having emp_dept as D890 would also delete the information of employee Maggie since she is assigned only to this department.

To overcome these anomalies and having a peaceful life we need to normalize the data.

 

Bibliography source:

http://www.studytonight.com/dbms/database-normalization.php

https://www.tutorialspoint.com/dbms/database_normalization.htm

https://beginnersbook.com/2015/05/normalization-in-dbms/

 

Transitive functional dependency.

In a Database Management System

A transitive dependency is a functional dependency which holds by virtue of transitivity.

Transitive dependencies occur when there is an indirect relationship that causes a functional dependency.

A transitive dependency can occur only in a relation that has three or more attributes.

The symbol “->” means than.

”A -> C” is a transitive dependency when it is true only because both “A -> B” and “B -> C” are true

Suppose A, B, and C designates three distinct attributes (or distinct collections of attributes) in the relation. Suppose all three of the following conditions hold:

A → B

(If A is true than B holds true)

It is not the case that B → A

(And when B is not true than A holds true)

B → C

(And when B is true C is also true in the relation.)

 

Consider the below table:

Book                                           Author                                               Author_age

Game of Thrones             George R. R. Martin                                      66

Harry Potter                      J. K. Rowling                                                  49

Dying of the Light             George R. R. Martin                                     66

 

{Book}  -> {Author}

If we know the book, we knows the authors name

 

{Author} does not -> {Book}

If we don’t know the authors name than we can still have a book.

 

{Author} -> {Author_age}

If we know Author, we also know Authors age.

 

Therefore as per the rule of transitive dependency: {Book} -> {Author_age} should hold, that makes sense because if we know the book name we can know the author’s age from the table.

 

Bibliography Source

https://en.wikipedia.org/wiki/Transitive_dependency

https://beginnersbook.com/2015/04/transitive-dependency-in-dbms/

Basic concepts required for understanding Oracle.

This world is full of Oracle DBAs.

But there are a select few who develop a deep understanding and work their magic.

Now to be above average will take a strong foundation of the basic concepts to build upon them and then even sky is not the limit.

Three most prominent areas to work upon for a in depth understanding of Oracle and its products are:

1. Operating System concepts
2. Relational DBMS concepts
3. Networking concepts

The better the understanding of these concepts, the deeper the level of your thinking about Oracle and its functionality. The simple reason being that Oracle will always run on an Operating system and will use the networking functionality/protocols and tools available to communicate. And the best part is it will not cease with Oracle. These concepts will help you learn a lot more.

We will dive into these in detail with the coming posts.

Please feel free to add your queries, feedbacks and recommendations to the comments section below.

 

GoldenGate

What is GoldenGate?

Oracle GoldenGate 12c enables the continuous, real-time capture, routing, transformation, and delivery of transactional data across heterogeneous environments. As new or updated data is committed at the source system, it is continuously captured and applied to one or more target systems with low latency
Goldengate:
Oracle GoldenGate enables the exchange and manipulation of data at the transaction level among multiple, heterogeneous platforms across the enterprise. It moves committed transactions with transaction integrity and minimal overhead on your existing infrastructure. Its modular architecture gives you the flexibility to extract and replicate selected data records, transactional changes, and changes to DDL (data definition language) across a variety of topologies. With this flexibility, and the filtering, transformation, and custom processing features of Oracle GoldenGate, you can support numerous business requirements:
■ Business continuance and high availability.
■ Initial load and database migration.
■ Data integration.
■ Decision support and data warehousing.

Oracle GoldenGate can be configured for the following purposes:
■ A static extraction of data records from one database and the loading of those records to another database.
■ Continuous extraction and replication of transactional Data Manipulation Language (DML) operations and data definition language (DDL) changes (for supported databases) to keep source and target data consistent.
■ Extraction from a database and replication to a file outside the database. Oracle GoldenGate is composed of the following components:
■ Extract
■ Data pump
■ Replicat
■ Trails or extract files
■ Checkpoints
■ Manager
■ Collector