Transcript Document

Real Edition Based Redefinition
Roads and Wild West
Eter Pani
Senior Database Administrator
TSYS International
© 2012 Total System Services, Inc.® All rights reserved worldwide.
About Me
1983
> Senior Oracle DBA at TSYS International
> Oracle Developer since 1993
> Oracle DBA since 2001
> OCA, OCP, OCE
> And just a regular guy
> [email protected]
> http://uk.linkedin.com/in/epani
2
© 2012 Total System Services, Inc.® All rights reserved worldwide.
Agenda
• Who are TSYS?
• Why EBR?
• What is EBR?
– Introduction
– EBR Features
– EBR Architecture
• EBR at TSYS
– EBR Implementation
– Deployment Process
– Database Links
• Known Issues
• Summary
3
© 2012 Total System Services, Inc.® All rights reserved worldwide.
Who Are TSYS ?
1983
> TSYS is incorporated in a spin-off from CB&T, then issues shares in an initial public offering.
1989
> TSYS begins processing in Canada; shares begin trading on the New York Stock Exchange.
1999
> TSYS Europe opens for business.
> TSYS completes the largest portfolio conversion in payments history to date.
2000
> TSYS opens its first data centre in the U.K., then converts one of Europe’s largest card portfolios.
> TSYS enters Asia-Pacific with a 51% stake in GP Network Corp., a payments gateway for more than 120,000 merchants in Japan.
2005
> TSYS acquires Visa’s 50% stake in Vital to form a wholly owned subsidiary renamed TSYS Acquiring Solutions.
> TSYS acquires an equity stake in China UnionPay Data Services Co., Ltd. (CUP Data), a subsidiary of China’s only
state-sanctioned payments network.
2006
2007
2008
2009
2010
2011
4
> TSYS expands its global footprint with the acquisition of TSYS Card Tech, marking the single greatest geographic expansion
event in the company’s history.
> TSYS sets a new record for the largest single-phase conversion of Visa or MasterCard accounts.
> TSYS becomes the largest processor of contactless credit cards in the world.
> TSYS becomes the first third-party processor in Japan to support multi-currency debit cards.
> TSYS becomes the largest processor of transaction-enabled healthcare cards.
> TSYS establishes London as its hub for global payment services.
> TSYS is spun-off from its parent company, Synovus, to become a fully independent company.
> TSYS acquires a Silicon Valley technology firm to simplify the acceptance of many new forms of payment.
> TSYS adds new data centre in Okinawa, Japan garnering more than 600,000 accounts and three new clients.
> TSYS Launches Credit Card Program for Regional and Community Financial Institutions.
> TSYS acquires a 51-percent ownership of First National Merchant Solutions (FNMS), which provides electronic payment
acceptance services to merchants.
> TSYS acquires full ownership of the FNMS joint venture and Atlanta-based TermNet Merchant Services — both rebranded as
TSYS Merchant Solutions.
© 2012 Total System Services, Inc.® All rights reserved worldwide.
Where are TSYS ?
5
~ 400
>80
>14 Billion
> 8,000
Clients Around
the World
Countries Where
TSYS Does Business
Enterprise-wide
Transactions
Team Members
5 © 2012 Total System Services, Inc.® All rights reserved worldwide.
What do TSYS do ?
CARDHOLDER
ACCOUNT
ISSUERS
NETWORK/
SWITCHES
REQUEST
BANK
REQUEST
APPROVAL
APPROVAL
ITEMS
ITEMS
FUNDS
FUNDS
PROCESSOR
BANK
© 2012 Total System Services, Inc.® All rights reserved worldwide.
MERCHANT
AUTH.REQUEST
BANK
APPROVAL
SALES TICKET
FUNDS
PROCESSOR
SETTLEMENT
BANK
6
ACQUIRERS
Who are our Clients ?
Updated: July 10, 2012`
7
©2012 Total System Services, Inc.® Proprietary. All rights reserved worldwide.
Agenda
• Who are TSYS?
• Why EBR?
• What is EBR?
– Introduction
– EBR Features
– EBR Architecture
• EBR at TSYS
– EBR Implementation
– Deployment Process
– Database Links
• Known Issues
• Summary
8
© 2012 Total System Services, Inc.® All rights reserved worldwide.
Drivers for EBR in TSYS
• 99.99% Availability SLAs with our clients.
• Daily/Weekly Application Patching & Quarterly
Releases all require planned downtime.
• We need “silver bullet” to fit all application changes
into a 10min planned downtime window.
• Compilation time after application changes can take
up to 20 minutes.
– 2 Databases (PRIME & ONLINE) , 4 DB Links, 3 Schemas,
1300 Packages, 750 Procedures, 500 Triggers, 1100 other
schema objects.
• EBR is part of Enterprise Edition license.
9
© 2012 Total System Services, Inc.® All rights reserved worldwide.
Agenda
• Who are TSYS?
• Why EBR?
• What is EBR?
– Introduction
– EBR Features
– EBR Architecture
• EBR at TSYS
– EBR Implementation
– Deployment Process
– Database Links
• Known Issues
• Summary
10
© 2012 Total System Services, Inc.® All rights reserved worldwide.
Introduction
• Provides high availability during patches & upgrades
of application code.
• Code changes are deployed in the privacy of an
edition.
• EBR Provides “Online Application Upgrade”
– An application does not need to be taken offline when
upgrades & patches are applied.
– Users of the existing system continue uninterrupted.
– Users of the upgraded system use the new code.
11
© 2012 Total System Services, Inc.® All rights reserved worldwide.
Introduction
• Introduced at Oracle 11G R2.
• Oracle’s enhanced free “no-downtime” feature.
• Introduces the dimension of an edition to the object
tablespace in the database.
• Provides a route for providing High Availability for
PL/SQL Object maintenance in applications.
12
© 2012 Total System Services, Inc.® All rights reserved worldwide.
Introduction
• Edition
– New non-schema object type.
– Editions are related through parent-child relationships.
– All objects prior to a deployment are part of the parent edition.
– Every edition can have one child and one parent edition
(except the ORA$BASE).
– Patch objects are part of the child edition.
– Sufficient when the patch is changing PL/SQL Objects.
• Editioning View
– Exposes different projections of a table into each edition.
• Cross-edition Trigger
– Propagates data changes made between editions.
13
© 2012 Total System Services, Inc.® All rights reserved worldwide.
Introduction
• Oracle Users
– New property “editions_enabled”.
– Users in the dba_registry catalog cannot be edition enabled.
• Editioned Object
– Uniquely identified by owner, namespace and name of
edition.
Note! One Way only!
You could not make user
editions_disabled!
14
© 2012 Total System Services, Inc.® All rights reserved worldwide.
EBR Features
• Every database from 11.2.0.1 onwards, whether brand
new or the result of an upgrade from an earlier version,
has at least one edition.
• Every foreground database session, at every moment
of its lifetime, uses a single edition.
• Once the feature is enabled you cannot switch it off.
15
© 2012 Total System Services, Inc.® All rights reserved worldwide.
EBR Features
• Editionable Objects
1. PL/SQL Objects
1. Packages
2. Procedures/Functions
3. Types
4. Triggers
2. Synonyms
3. Views
• Non-Editionable Objects
1. Tables
2. Materialized Views
3. Indexes
4. Constraints
5. Clusters
6. Database Links
7. Jobs
8. Types used in table definitions
16
© 2012 Total System Services, Inc.® All rights reserved worldwide.
EBR Features
V1
ORA$BASE
V2
ChildEdition
PL/SQL
OBJECTS
PL/SQL
OBJECTS
Triggers
Triggers
TABLES
17
© 2012 Total System Services, Inc.® All rights reserved worldwide.
EBR Features
1000 referring objects
Pre-upgrade
application
ORA$BASE
990 referring objects
+
10 changed objects
Post-upgrade
application
V2
When a new edition is used by a schema, all editionable objects are
inherited by the new edition from the previous edition.
Inheritance is realised by creation of an oracle internal stub
structure for each inherited object. This feature can affect
compilation by making the name resolution path longer.
18
© 2012 Total System Services, Inc.® All rights reserved worldwide.
EBR Architecture – Overview
Object
ID
Object
Owner
Object
Name
Object
Name
Object
Edition
Object
ID
Object
Owner
Object
Name
Object
Code
Object
Edition
Compiled code uses object_IDs not object_names for resolution.
Internal representation.
19
OBJ$
DBA_OBJECTS_AE
STUBs
USER$
DBA_USERS
Editions
© 2012 Total System Services, Inc.® All rights reserved worldwide.
EBR Architecture – UGA and Editions
Package variables
Context variables
Edition 2
Package variables
UGA
Edition 1
SHARED POOL
Package variables
Context variables
Package variables
Session store separate set of package variables for each used edition.
Shared pool usage stay un touched.
20
© 2012 Total System Services, Inc.® All rights reserved worldwide.
EBR Architecture – Data Pump
• expdp
– New parameter called “source_edition”.
– Dump file does not store the object edition.
• impdp
– New parameter called “target_edition”.
– Inherited or existing object are not overwriting
(exception for TYPES where you can set TRANSFORM=oid:n).
– Types & Packages actualised.
– Libraries, Synonyms, Views not actualised.
21
© 2012 Total System Services, Inc.® All rights reserved worldwide.
EBR Architecture – Editioning Views
• Editioning views are different from conventional views !!!
• Provide an alias for a subset of columns in a table.
• Columns cannot be modified.
• Editioning views rely on Fine Grained Dependency
Tracking
– stay compiled if a new column is added.
– Invalidate if used column dropped.
– Invalidate dropping a unique index on a base table.
22
© 2012 Total System Services, Inc.® All rights reserved worldwide.
EBR Architecture – Editioning Views
Limitations
– Only one Editioning view in one edition for single table.
– Column could be specified only once.
– Wildcard symbol * is supported.
– Editioning view can be built on one table only.
– Index hints should use logical names of the columns.
– ON DELETE CASCADE does not fire triggers on Editioning
Views.
23
© 2012 Total System Services, Inc.® All rights reserved worldwide.
EBR Architecture – Cross-Edition Triggers
• Cross-Edition trigger is DML trigger visible only in actual
edition but fires in descendant (for forward) or ancestor
(for reverse triggers).
– propagates data changes made by the old edition into the new
edition’s columns, or (in hot-rollover) vice-versa.
– defines the transformation algorithm between old and new table
structure.
Note! Do not use on big tables !
You can code things more optimally for
big volumes of data!
24
© 2012 Total System Services, Inc.® All rights reserved worldwide.
EBR Architecture – Cross-Edition Triggers
Parent Edition
Name
Child Edition
Surname
Full Name
Forward CrossEdition Trigger
Reverse CrossEdition Trigger
INSERT INTO .. (NAME,SURNAME) ..
Jo
Brown
Jo Brown
INSERT INTO .. (Full Name) ..
Lee
25
Heart
© 2012 Total System Services, Inc.® All rights reserved worldwide.
Lee Heart
EBR Architecture – Cross-Edition Triggers
Limitations
– Must be defined on a table, not a view
– Must be a DML trigger
– Must be Deterministic
– Invalidated after column modification or dropping
• if it depends on entire row
• a column specified in its definition
• specified column to the right of the dropped column
– Cross-Edition trigger would not affect statements that were
compiled before trigger was enabled.
26
© 2012 Total System Services, Inc.® All rights reserved worldwide.
EBR Architecture – Session Edition
Yes
OCI, JDBC, SQL* PLUS
Connection?
No
Is edition specified in
connect definition?
No
Is edition specified on
the database service?
Current
Edition
Yes
Use specified Edition
Session Edition
Database Edition
No
Use Database Default
Edition
27
© 2012 Total System Services, Inc.® All rights reserved worldwide.
Agenda
• Who are TSYS?
• Why EBR?
• What is EBR?
– Introduction
– EBR Features
– EBR Architecture
• EBR at TSYS
– EBR Implementation
– Deployment Process
– Database Links
• Known Issues
• Summary
28
© 2012 Total System Services, Inc.® All rights reserved worldwide.
EBR Implementation
• Enable editions in the application schema in the
PRIME & ONLINE databases.
• Create a new schema.
– Migrate types that have non-editioned objects (queues, tables)
dependencies.
• Rebuild referenced objects.
29
© 2012 Total System Services, Inc.® All rights reserved worldwide.
EBR Implementation
TCTDBSEBR
(editions not enabled)
TCTDBS
(editions enabled)
PL/SQL
OBJECTS
Synonyms
Triggers
QUEUES
Types
Types
TABLES
30
© 2012 Total System Services, Inc.® All rights reserved worldwide.
Deployment Process – Overview
Edition1
Edition2
Edition3
Edition4
Edition5
Dropped objects do not disappear from the DBA_OBJECTS view
but the object type is changed to NON-EXISTENT!
31
© 2012 Total System Services, Inc.® All rights reserved worldwide.
Deployment Process – Overview
• All objects in database valid in Edition1.
• Switch deployment session to Edition2.
• Deploy new code to Edition2. Get number of invalid
objects in Edition2.
• Run UTL_RECOMP and bring forward (to the views)
the dependant objects.
• All objects in all editions are now valid.
• Set default edition to Edition2 and switch all sessions.
• Confirm successful deployment.
• Drop covered objects from Edition1.
• Remove privilege to use Edition1.
32
© 2012 Total System Services, Inc.® All rights reserved worldwide.
Deployment Process – Cleanup Phase
• Cleanup is a process that removes unused objects
and editions.
– The number of rows in the OBJ$ table decrease performance
of resolution process.
– Inherited objects create stub rows in OBJ$ table.
– Old and retired editions still exist in the database and can be
used by mistake.
33
© 2012 Total System Services, Inc.® All rights reserved worldwide.
Deployment Process – Cleanup (Option 1)
Edition1
Edition2
Edition3
Edition4
Edition5
34
© 2012 Total System Services, Inc.® All rights reserved worldwide.
Deployment Process – Cleanup (Option 2)
Edition1
Edition2
Edition3
ERASED
Edition4
Edition5
35
© 2012 Total System Services, Inc.® All rights reserved worldwide.
Database Links
•
DB Links in TSYS
– Bi-Directional.
– Used for intensive data exchange.
– Used by replication between two RAC systems.
– Metric of system availability.
36
© 2012 Total System Services, Inc.® All rights reserved worldwide.
Database Links – Architecture
37
© 2012 Total System Services, Inc.® All rights reserved worldwide.
Database Links – Architecture (Option 1)
Services
Edition NEW
Edition NEW
11.2.0.2
11.2.0.2
Edition OLD
Edition OLD
srvctl add service -d * -s * -t NEW
DBMS_SERVICE.CREATE_SERVICE(...edition=>'NEW')
38
© 2012 Total System Services, Inc.® All rights reserved worldwide.
Database Links – Architecture (Option 1)
• Option 1
– Create EditionT2 on Target Database.
– Create EditionS2 on Source Database.
– Create/modify ServiceT2 using EditionT2 on Target Database.
– Create/modify ServiceS2 using EditionS2 on Source Database.
– Create DB_LINK_S2T2 from Source Database to Target
Database edition EditionT2.
– Create DB_LINK_T2S2 from Target Database to Source
Database edition EditionS2.
– Recreate synonyms on remote objects using new DB_LINKS.
39
a.
Complicated
b.
Not Automated
c.
Changes required in CRS registry
d.
Driving site for all queries is source one
© 2012 Total System Services, Inc.® All rights reserved worldwide.
Database Links – Architecture (Option 2)
Services
Edition NEW
Edition NEW
Edition OLD
Edition OLD
On Logon trigger
Configuration table
40
© 2012 Total System Services, Inc.® All rights reserved worldwide.
ServiceSA
ServiceSB
EditionS2
EditionS3
Database Links – Architecture (Option 2)
• Option 2
– Create EditionT2 on Target Database.
– Create EditionS2 on Source Database.
– Re-Associate ServiceT2 from EditionT0 to EditionT2 in
configuration table on Target Database.
– Re-Associate ServiceS2 from EditionS0 to EditionS2 in
configuration table on Source Database.
– Recreate synonyms on remote objects using spare
DB_LINKS.
41
a.
Easy
b.
Automatic
c.
No changes in CRS registry
d.
Driving site for all queries is source one
© 2012 Total System Services, Inc.® All rights reserved worldwide.
Agenda
• Who are TSYS?
• Why EBR?
• What is EBR?
– Introduction
– EBR Features
– EBR Architecture
• EBR at TSYS
– EBR Implementation
– Deployment Process
– Database Links
• Known Issues
• Summary
42
© 2012 Total System Services, Inc.® All rights reserved worldwide.
Known Issues – SR 3-5590623201
SR 3-5590623201: DROP EDITION <XXX> CASCADE
•
Failed command leaves Edition in UNUSABLE state.
•
Objects in UNUSABLE edition are unavailable.
•
Edition could not be marked VALID back.
• The only way to sort the issue is to recover database
from backup.
ORA-38803: edition is unusable
43
© 2012 Total System Services, Inc.® All rights reserved worldwide.
Known Issues – BUG 13855931
BUG 13855931: Growing Library cache requirements.
• Growing number of compiled and used code during
EBR exercise increased competition for Library cache
resources.
• Keep fingers crossed and wait for version 12c.
44
© 2012 Total System Services, Inc.® All rights reserved worldwide.
Known Issues – BUG 13809923
BUG 13809923: All oracle automated recompilers
compile object in edition where they exist.
• Recompilations in production edition can cause
blocking issues and unplanned event trigger firing.
• Do not use standard oracle recompilers until oracle fix
the bug.
45
© 2012 Total System Services, Inc.® All rights reserved worldwide.
Known Issues – BUG 13835040
BUG 13835040: Implicit types inheritance.
• The types declared in package specification were not
actualised in the same edition as package
specification.
• Fixed by Oracle.
46
© 2012 Total System Services, Inc.® All rights reserved worldwide.
Known Issues – BUG 13502183
BUG 13502183: Valid Package Body In Child Edition
Marked As Invalid When Base Edition Is Invalid.
• If the parent package is invalid and could not be
compiled the package in child edition became invalid
during the normal process.
• Fixed by Oracle.
47
© 2012 Total System Services, Inc.® All rights reserved worldwide.
Known Issues – Growing PGA
Issue: Growing PGA memory requirements.
• Package state is stored separately for each edition.
• We decide that each session would use only one
edition.
48
© 2012 Total System Services, Inc.® All rights reserved worldwide.
Known Issues – VPD Complexity
Issue: Complexity of VPD in multiple edition environment.
• CONTEXT variables are visible transparently from all
editions but policy functions are editionable.
• Take policy functions from editioned schema.
49
© 2012 Total System Services, Inc.® All rights reserved worldwide.
Agenda
• Who are TSYS?
• Why EBR?
• What is EBR?
– Introduction
– EBR Features
– EBR Architecture
• EBR at TSYS
– EBR Implementation
– Deployment Process
– Database Links
• Known Issues
• Summary
50
© 2012 Total System Services, Inc.® All rights reserved worldwide.
Summary
• Applications and processes can be builds around EBR.
• It is very difficult to configure EBR around existing
complex application.
• Deployment success with EBR is no guarantee of
success on an environment without EBR and vice versa.
• You still need minimal period of downtime if you could
not accept when part of the sessions working in old
edition and part in new edition.
51
© 2012 Total System Services, Inc.® All rights reserved worldwide.
Summary
• The application support budget increases.
• The development budget increases.
• Out of working hours deployment still required for
downtime period.
• The overall costs for deployments increases because
of a more complex deployment process.
• During the EBR project the deployment process at
TSYS was redesigned and optimised, thus downtime
was decreased without EBR.
• The project is currently on hold in QA stage.
52
© 2012 Total System Services, Inc.® All rights reserved worldwide.
Any Questions
53
© 2012 Total System Services, Inc.® All rights reserved worldwide.
Thank You
© 2012 Total System Services, Inc.® All rights reserved worldwide.
Deployment Architecture – DB_Links Option2
Pre-Upgrade stage
55
Parameter
Source Database
Target Database
Default edition
EditionS1
EditionT1
DB_LINK in Default edition
DB_LINK_S_A
DB_LINK_T_A
DB_LINK in Non-Default edition
DB_LINK_S_B
DB_LINK_T_B
Service for DB_LINK_*_A
service_s_a
service_t_a
Service for DB_LINK_*_b
service_s_b
service_t_b
Service
Edition
service_s_a
EditionS1
service_s_b
EditionS0
service_t_a
EditionT1
service_t_b
EditionT0
© 2012 Total System Services, Inc.® All rights reserved worldwide.
Deployment Architecture – DB_Links Option2
Upgrade stage
Parameter
Source Database
Target Database
Default edition
EditionS1
EditionT1
New Edition
EditionS2
EditionT2
DB_LINK in Default edition
DB_LINK_S_A
DB_LINK_T_A
DB_LINK in New edition
DB_LINK_S_B
DB_LINK_T_B
Service for DB_LINK_*_A
service_s_a
service_t_a
Service for DB_LINK_*_b
service_s_b
service_t_b
56
Service
Edition
service_s_a
EditionS1
service_s_b
EditionS2
service_t_a
EditionT1
service_t_b
EditionT2
© 2012 Total System Services, Inc.® All rights reserved worldwide.
Deployment Architecture – DB_Links Option2
Post-Upgrade stage
Parameter
Source Database
Target Database
Default edition
EditionS2
EditionT2
Non-Default Edition
EditionS1
EditionT1
DB_LINK in Default edition
DB_LINK_S_B
DB_LINK_T_B
DB_LINK in Non-Default edition
DB_LINK_S_A
DB_LINK_T_A
Service for DB_LINK_*_A
service_s_a
service_t_a
Service for DB_LINK_*_b
service_s_b
service_t_b
57
Service
Edition
service_s_a
EditionS1
service_s_b
EditionS2
service_t_a
EditionT1
service_t_b
EditionT2
© 2012 Total System Services, Inc.® All rights reserved worldwide.