Golden Gate Concept

1. Introduction
===========
Data Replication:-

In simplest term data replication is nothing but storing same data on multiple storage devices.
Replication is the process of copying and maintaining database objects in multiple databases that make
up a distributed database system. Changes applied at one site are captured and stored locally before
being forwarded and applied at each of the remote locations. Replication provides user with fast, local
access to shared data, and protects availability of applications because alternate data access options
exist. Even if one site becomes unavailable, users can continue to query or even update the remaining
locations.
Database replication can be used on many database management systems, usually with a master/slave
relationship between the original and the copies. The master logs the updates, which then ripple
through to the slaves. The slave outputs a message stating that it has received the update successfully,
thus allowing the sending (and potentially re-sending until successfully applied) of subsequent updates.
Multi-master replication, where updates can be submitted to any database node, and then ripple
through to other servers, is often desired, but introduces substantially increased costs and complexity
which may make it impractical in some situations. The most common challenge that exists in
multi-master replication is transactional conflict prevention or resolution. Most synchronous or eager
replication solutions do conflict prevention, while asynchronous solutions have to do conflict resolution.
For instance, if a record is changed on two nodes simultaneously, an eager replication system would
detect the conflict before confirming the commit and abort one of the transactions. A lazy replication
system would allow both transactions to commit and run a conflict resolution during resynchronization.
The resolution of such a conflict may be based on a timestamp of the transaction, on the hierarchy of
the origin nodes or on much more complex logic, which decides consistently on all nodes.
When data is replicated between database servers, so that the information remains consistent
throughout the database system and users cannot tell or even know which server in the DBMS they are using, the system is said to exhibit replication transparency.

Data Replication Techniques

Materialized Views:-

Oracle uses materialized views (also known as snapshots in prior releases) to replicate data to
non-master sites in a replication environment. A materialized view is a replica of a target master
from a single point in time. The master can be either a master table at a master site or a
master materialized view at a materialized view site. Whereas in multi-master replication tables
are continuously updated by other master sites, materialized views are updated from one or
more masters through individual batch updates, known as a refreshes.

Triggers

Oracle lets you define procedures called triggers that run implicitly when an INSERT, UPDATE, or
DELETE statement is issued against the associated table or, in some cases, against a view, or
when database system actions occur. These procedures can be written in PL/SQL or Java and
stored in the database, or they can be written as C callouts.
Triggers are similar to stored procedures. A trigger stored in the database can include SQL and
PL/SQL or Java statements to run as a unit and can invoke stored procedures. However,
procedures and triggers differ in the way that they are invoked. A procedure is explicitly run by a
user, application, or trigger. Triggers are implicitly fired by Oracle when a triggering event
occurs, no matter which user is connected or which application is being used.
Insert, update, delete statements can be used in Triggers to replicate data to another
database(Using old & New qualifiers) after initial loading, Initial loading can be done by
export/import or through data pump.

Data Guard

Oracle Data Guard ensures high availability, data protection, and disaster recovery for
enterprise data. Data Guard provides a comprehensive set of services that create, maintain,
manage, and monitor one or more standby databases to enable production Oracle databases to
survive disasters and data corruptions. Data Guard maintains these standby databases as copies
of the production database. Then, if the production database becomes unavailable because of a
planned or an unplanned outage, Data guard can switch any standby database to the
production role, minimizing the downtime associated with the outage. Data Guard can
be used with traditional backup, restoration, and cluster techniques to provide a high level of
data protection and data availability.
With Data Guard, administrators can optionally improve production database performance by
offloading resource-intensive backup and reporting operations to standby systems.

Oracle Streams

Oracle Streams enables information sharing. Using Oracle Streams, each unit of shared
information is called a message, and you can share these messages in a stream. The stream can
propagate information within a database or from one database to another. The stream routes
specified information to specified destinations. The result is a feature that provides greater
functionality and flexibility than traditional solutions for capturing and managing messages, and
sharing the messages with other databases and applications. Oracle Streams provides the
capabilities needed to build and operate distributed enterprises and applications, data
warehouses, and high availability solutions. You can use all of the capabilities of Oracle
Streams at the same time. If your needs change, then you can implement a new capability of
Oracle Streams without sacrificing existing capabilities.
Using Oracle Streams, you control what information is put into a stream, how the stream flows
or is routed from database to database, what happens to messages in the stream as they flow
into each database, and how the stream terminates. By configuring specific capabilities of
Oracle Streams, you can address specific requirements. Based on your specifications, Oracle
Streams can capture, stage, and manage messages in the database automatically, including, but
not limited to, data manipulation language (DML) changes and data definition language (DDL)
changes. You can also put user-defined messages into a stream, and Oracle Streams can
propagate the information to other databases or applications automatically. When messages
reach a destination, Oracle Streams can consume them based on your specifications

2. Oracle GoldenGate Replication

Golden gate software was initially developed by Cullinet software company which is located at
Massachusetts. In 2009 Oracle acquired Golden Gate software. Oracle Golden Gate can be used as a
replication tool,ETL and even as a DR solution.
GoldenGate enables us to extract and replicate data across a variety of topologies Business Continuity
and High Availability Data migrations and upgrades Decision Support Systems and Data Warehousing
Data integration and consolidation Oracle GoldenGate enables the exchange and manipulation of data
at the transaction level among multiple, heterogeneous platforms across the enterprise. Its modular
architecture gives you the flexibility to extract and replicate selected data records, transactional
changes, and changes to DDL (data definition language2) 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

Advantages Of Golden Gate:-

Oracle GoldenGate 11gR2 provides the following features and benefits that enable you to achieve
real-time data integration and continuous availability for mission-critical systems:
Real-time data.

1.  Immediately captures, routes, transforms, and delivers transactional data to other

systems with sub-second latency.

      2. Improves organizational decision-making through enterprise-wide visibility into

accurate, up-to-date information. Heterogeneous support.
      3. Supports heterogeneous databases and platforms to increase IT flexibility.

Heterogeneous support.

1. Supports heterogeneous databases and platforms to increase IT flexibility.
2. Extracts data from existing IT investments and lowers your total cost of ownership while
   unifying data from all enterprise systems.

Reliability

•  Delivers all committed records to the target, even in the event of network outages.

•  Moves data without requiring system interruption or outage windows.

High performance with low impact

•  Moves thousands of transactions per second with negligible impact on source and

target systems. Enables to access critical information in real time without bogging down
production systems.

Transaction integrity

•  Maintains transaction commit boundaries and atomicity, consistency, isolation, and

durability (ACID) properties as transactions are moved between source and target
systems. Ensures data consistency and referential integrity across multiple masters,
back-up systems, and reporting databases.
Integration

•  Integrates with Oracle Data Integrator Enterprise Edition and complements other ETL

solutions. Via Oracle Golden Gate Application Adapters, it allows to capture from, or
deliver to, Java Message Services based messaging solutions such as Oracle WebLogic.

Flexible topology support.

•  Moves data in one-source-to-one-target, one-to-many, many-to-one, many-to-many,cascading, and bidirectional configurations.
  

  Conflict detection and resolution

  •  Enables conflict detection and resolution in multi-master configurations where two
  systems can modify separate instances of the same table.
  Event based infrastructure
  
  •  Triggers immediate actions based on specific database operations captured and stored
  in Trail Files.
  

  Routing and compression

  • Utilizes TCP/IP to send data and eliminate geographical distance constraints. Applies
  additional compression to the data as it is routed.
  

  Data encryption

  •  Securely transmits data for domestic and international applications with variable key
  length encryption.
  

  Deferred apply

  • Applies data immediately or at a deferred time chosen by the user, without losing
  transaction integrity.

Different Kinds OF AWR Reports

 AWR Reports

===========

It’s a good idea to know how to produce the text format – which is what I do most of the time (especially since I often end up with a simple telnet or putty session into a client server). Take a look at $ORACLEHOME/rdbms/admin for all the scripts starting with “awr” – there’s quite a lot of them, and the number keeps growing. Apart from finding a script that will give you the standard AWR report in a good old-fashioned text format, you may that newer versions of Oracle include a few useful variations on the theme.

Here’s a list from the 11.2.0.2 home ($ORACLE_HOME/rdbms/admin) with (mostly) the one-line description from the start of file. I’ve put the most useful ones in the first list:
awrrpt.sql      -- basic AWR report
awrsqrpt.sql    -- Standard SQL statement Report

awrddrpt.sql    -- Period diff on current instance

awrrpti.sql     -- Workload Repository Report Instance (RAC)
awrgrpt.sql     -- AWR Global Report (RAC)
awrgdrpt.sql    -- AWR Global Diff Report (RAC)

awrinfo.sql     -- Script to output general AWR information
For most people the awrrpt.sql and awrsqrpt.sql are likely to be sufficient, but the “difference between two periods” can be very useful – especially if you do things like regularly forcing an extra snapshot at the start and end of the overnight batch so that you can (when necessary) find the most significant differences in behaviour between the batch runs on two different nights.

If you get into the ‘RAC difference report’ you’ll need a very wide page – and very good eyesight !

There are also a lot of “infrastructure and support” bits – some of the “input” files give you some nice ideas about how you can write your own code to do little jobs like: “run the most recent AWR report automatically”:
awrblmig.sql    -- AWR Baseline Migrate
awrload.sql     -- AWR LOAD: load awr from dump file
awrextr.sql     -- AWR Extract

awrddinp.sql    -- Get inputs for diff report
awrddrpi.sql    -- Workload Repository Compare Periods Report

awrgdinp.sql    -- Get inputs for global diff reports
awrgdrpi.sql    -- Workload Repository Global Compare Periods Report

awrginp.sql     -- AWR Global Input
awrgrpti.sql    -- Workload Repository RAC (Global) Report

awrinpnm.sql    -- AWR INput NaMe
awrinput.sql    -- Get inputs for AWR report

awrsqrpi.sql    -- Workload Repository SQL Report Instance
I usually carry copies of the scripts with me when I’m troubleshooting in case I need them at client sites – sometimes I’m not allowed the privileges I really need to do rapid troubleshooting, but if I can persuade the DBA to give me execute privileges on package dbms_workload_repository and select privileges on a couple of the necessary tables and views then I can run the reports from an otherwise “minimal” account.

There are also a couple of deceptively named files that you might miss in 11.2:
spawrrac.sql  -- Server Performance AWR RAC report
spawrio.sql  -- AWR IO Intensity Report
spadvrpt.sql -- Streams Performance ADVisor RePorT