oracle 12c ilm_customer_experience

2013 © Trivadis

BASEL BERN BRUGG LAUSANNE ZUERICH DUESSELDORF FRANKFURT A.M. FREIBURG I.BR. HAMBURG MUNICH STUTTGART VIENNA

2013 © Trivadis

Implementation of Oracle 12c ILM

Jacques Kostic Senior Consultant LS-IMS

Date 2014.09.12


1

2013 © Trivadis

AGENDA

1. Customer Overview

Environment

Requirements

Phases of the project

2. Trivadis Activities

Analysis of the Tools on the market

The implemented solution

3. Live Demo

4. Q & A

Date 2014.09.12


2

2013 © Trivadis

Date 2014.09.12


Customer Overview

3

2013 © Trivadis

Customer Overview

The name will not be disclosed but the most relevant

characteristics to the project are reported below.

Medium size customer of the Public sector.

Several databases involved on the data archiving project with more than

15 years of online data.

Complex business rules which define the data archiving and purging.

Expired information should be deleted from the database (law obligation).

Date 2014.09.12


4

Customer

Environment

2013 © Trivadis

Customer Overview

Description of the customer requirements to implement on

the database with no application change.

Data should be organized within the following categories:

Date 2014.09.12


5

Customer

Requirements

1 of 2

Active data younger than 3 fiscal years.

Archived data older than 3 fiscal years but younger than the limit defined by

the associated business rule (different retentions between 10, 15 or 25 years).

To purge expired data should be consistently deleted from the database.

2013 © Trivadis

Customer Overview

Storage tiering should be possible between Active and Archived data.

The automated database refresh procedure which clone Production

databases to Test environment, should be capable to replicate all data or

the Active data only.

The initial database version is 11gR2 but the decision to upgrade to 12c

was taken before starting this project.

Date 2014.09.12


6

Customer

Requirements

2 of 2

2013 © Trivadis

Customer Overview

Due to the complexity and the risks associated to the

purge of the data, the project has been divided in three

distinct phases:

Identification of the most appropriate data lifecycle method.

Validation of the technical solution by a PoC.

Implementation of the data lifecycle on all databases.

Date 2014.09.12


7

Phases of the

Project

2013 © Trivadis

Date 2014.09.12


Trivadis Activities

8

2013 © Trivadis

Trivadis Activities

With the aim of being customer oriented and not

technology oriented Trivadis has used the S.W.O.T.

analysis to identify the most suitable data lifecycle

technology.

The following options have been retained for the S.W.O.T. analysis:

Home made archiving/purging solution.

Pre-12c features (Partitioning, Compression, Total Recall).

Oracle 12c ILM.

Date 2014.09.12


9

Analysis of

the Tools on

the market

The S.W.O.T. analysis has shown that none of the retained options

was able to satisfy all requirements. But the adoption of Oracle 12c

Information Lifecycle Managements could guarantee solid foundation

to the project.

2013 © Trivadis

Trivadis Activities

Verified that ILM alone was not enough we have

combined the following functionalities to engineer and

automate a robust Lifecycle solution.

Oracle 12c ILM the cornerstone of the project. Designed to optimize

storage tiering and data compression, guaranteeing the maximum

performance and cost saving. This technology relies on two main

components:

Date 2014.09.12


10

The

Implemented

Solution

1 of 4

Heat Map provides a detailed view of how the data is being accessed and

modified. The fine-grained statistics generated at row and segment level are

differentiated by access (e.g. full table scan and index lookup). Other

administrative tasks like Stats Gathering, DDLs or Table Redefinitions are

automatically excluded.

2013 © Trivadis

Trivadis Activities

Automatic Data Optimization (ADO) allows to create policies for storage

tiering, data compression and data movement. ADO uses Heat Map

information to determine when enforcing a rule at row or segment level.

Complex business rules can be translated in ADO actions using PL/SQL

functions.

Date 2014.09.12


11

The

Implemented

Solution

2 of 4

-- ILM Partition compression

ALTER TABLE Sales modify partition p2009 ILM ADD POLICY ROW STORE COMPRESS

ADVANCED SEGMENT AFTER 30 DAYS OF NO MODIFICATION;

-- ILM Partition tiering

ALTER TABLE Sales modify partition p2007 ILM ADD POLICY TIER TO TS_ARCHIVE;

2013 © Trivadis

Trivadis Activities

To cover the missing functionalities the following options

have been integrated on the project.

In-Database Row Archiving permits to archive rows within a table by

marking them as invisible. Added value for the customer:

Date 2014.09.12


12

The

Implemented

Solution

3 of 4

Safely hide expired rows to the users with no need of application changes.

Development of a module for validating the rows marked as expired before

definitive deletion.

2013 © Trivadis

Trivadis Activities

Ad hoc PL/SQL procedures in support of the validation and purging of the

expired data.

Date 2014.09.12


13

The

Implemented

Solution

4 of 4

Automated RMAN replication of the test databases with the possibility to

replicate all data or the Active data only.

2013 © Trivadis

Questions and answers ...

2013 © Trivadis

BASEL BERN BRUGG LAUSANNE ZUERICH DUESSELDORF FRANKFURT A.M. FREIBURG I.BR. HAMBURG MUNICH STUTTGART VIENNA

Jacques Kostic Senior Consultant LS-IMS

Date 2014.09.12


oracle 12c ilm_customer_experience

Technology