Transcript Procurement Requisition Training

Oracle 10g on Solaris to Oracle RAC 11g/Oracle
Automatic Storage Management on Linux
A Client Case Study
ID#: S307842
Terry Koch
Oracle
Corporation
Lelanie Moll
Senior Manager, Database
engineering
FICO
Confidential. This presentation is provided for the recipient only and cannot
be reproduced or shared without Fair Isaac Corporation's express consent.
1
© 2009 Fair Isaac Corporation.
Alan Tai
Lead Engineer, Database
Engineering
FICO
FICO
TM
Make Every Decision Count TM
July 7, 2015
Confidential. This presentation is provided for the recipient only and cannot
be reproduced or shared without Fair Isaac Corporation's express consent.
2
© 2009 Fair Isaac Corporation.
FICO is the leader in
Decision Management —
transforming business by
making every decision count
Confidential. This presentation is provided for the recipient only and cannot
be reproduced or shared without Fair Isaac Corporation's express consent.
3
© 2009 Fair Isaac Corporation. Confidential.
Profile
Products &
Services
Clients &
Market
Offices
The leader in decision management
Founded: 1956
NYSE: FIC
Revenues: $745 million (fiscal 2008)
Analytics: scores and models
Decision management applications
Decision management tools
5,000+ clients in 80 countries
Primary Industries: Financial services, insurance, retail, healthcare
20+ offices worldwide
HQ in Minneapolis, Minnesota
Regional Hubs: London, Birmingham (UK), Madrid, Sao Paulo,
Bangalore, Beijing, Singapore
Confidential. This presentation is provided for the recipient only and cannot
be reproduced or shared without Fair Isaac Corporation's express consent.
4
© 2009 Fair Isaac Corporation.
Decision Management Portfolio – Key Products
Customer Lifecycle Solutions
Acquire
Decision
Management
Applications
Manage
Protect
Marketing
Origination
Customer
Management
Collections &
Recovery
Fraud
FICO®
Precision
Marketing
Manager
FICO
Capstone®
FICO TRIAD®
FICO® Debt
Manager
FICO Falcon®
Fraud Manager
FICO Recovery
Management
System
FICO® Insurance
Fraud Manager
FICO
LiquidCredit®
Across the Lifecycle & Embedded in Applications
Scoring &
Analytics
Scores: FICO® Score  FICO® Insurance Risk Scores  myFICO®
Decision
Management
Tools
Business Rules Management & Decision Services: FICO Blaze Advisor®
Analytics: Predictive Analytics  Optimization and Strategy Design  Portfolio Analytics
Predictive Analytics: FICO Model Builder
Optimization: FICO Xpress Optimization Suite  FICO Decision Optimizer
Services
Analytic Services
Business and Solution Consulting
Confidential. This presentation is provided for the recipient only and cannot
be reproduced or shared without Fair Isaac Corporation's express consent.
5
© 2009 Fair Isaac Corporation.
We Work with Leaders Around the World
Financial
Services
Origination
Risk Management
Marketing
Fraud
Collections & Recovery
Insurance
Underwriting
Claims Management
Fraud
Retail
Marketing & Merchandising
Credit Management
Fraud
Healthcare
Revenue Cycle Management
Fraud
Marketing
Confidential. This presentation is provided for the recipient only and cannot
be reproduced or shared without Fair Isaac Corporation's express consent.
6
© 2009 Fair Isaac Corporation. Confidential.
Target
Part 1
Setting the stage
Lelanie Moll
Senior Manager
Database engineering
FICO
July 7, 2015
Confidential. This presentation is provided for the recipient only and cannot
be reproduced or shared without Fair Isaac Corporation's express consent.
7
© 2009 Fair Isaac Corporation.
Session
Objective
Part 1
 Undertaking a change that impacts multiple layers
of the environment can be intimidating or down
right scary. Choosing to do so for the first time for a
mission critical environment with high transaction
volumes and strict performance SLAs certainly
adds to overall complexity.
 The intent of this session is to give Oracle users an
insight into how FICO approached it. Hopefully, at
the end of this session, you will realize that it can
be done, it’s not really that hard and that the
benefits are great.
8
© 2009 Fair Isaac Corporation. Confidential.
© 2009 Fair Isaac Corporation. Confidential.
Problem Statement
 Solution was first implemented in 2005
 Oracle 9i
 Sun V490 ( 4 CPUs and 8gig RAM)
 Upgraded to 10g in 2008 and moved to Sun V890 (8 SPU and 32gig
RAM)
 SLA Performance projection trending downwards as transaction volumes
trend upwards.
 We had to find a way to scale the solution which boils down to scaling the
database.
 Buying the next size up Sun server was not an option.
 The database was our single point of failure.
 Web and application tiers did have multiple servers for high availability.
 Disaster Recovery environment in different data centre but the failover process
was complicated.
 Needed to cut cost to ensure the profitability of the service offering.
 Limited time to design and implement a solution
9
© 2009 Fair Isaac Corporation. Confidential.
Requirements – internal and external
 Strategic direction - Data Center
 Move the complete solution from one Data Center to another
 Scalability
 Implement a solution that can scale – client wants to incrementally increase the
current 300TPS requirement to 1000 with a 500TPS requirement shortly after
go-live.
 Performance
 Performance SLA was increased to 97% from 95%. This is the percentage of
all transactions that needs to complete under 2 seconds.
 Global Expansion
 Accept Foreign languages like Japanese – change character set.
 Reduced downtime
 Minimize the planned and unplanned outage windows – high availability in all
tiers
 Reduce cost.
 Go Green!
10
© 2009 Fair Isaac Corporation. Confidential.
Database Tier Design Choices
Design Choice
11
Requirement met
» 3 X HP ProLiant servers with
RHEL 4 ( 8 core/32 gig RAM)
» Low cost servers
» Enhanced Performance
» Green IT- Reduced power,
cooling, space
» Oracle 11g Real Application
Clusters (RAC)
» Scalability
» High Availability Framework
» Oracle 11g Data Guard
» Disaster Recovery
» Maximum Availability Architecture
» Oracle 11g Automatic Storage
Management
» Reduce cost
» Minimize downtime
» Manageability and Performance
© 2009 Fair Isaac Corporation. Confidential.
Roadmap
From:
Infrastructure
To:
Sun V890 – 8 CPUs with 32 gig
RAM
Solaris 10
Oracle 10.2.0.3
Single Instance
VERITAS File Systems
UTF8
Data Center A
3 X HP ProLiant DL580c – 2 Quad
core with 32 gig RAM on each node
RHEL 4*
Oracle 11.1.0.7**
Clustered 3 Node RAC
ASM accessing block devices
AL32UTF8
Data Center B
Performance
SLA : 300 TPS with 95% under 2
sec
SLA : 1000 TPS with 97% under 2
sec
* Would have preferred RHEL 5 but Unix team was not ready
** Started off as 11.1.0.6 but upgraded before go-live
12
© 2009 Fair Isaac Corporation. Confidential.
Challenges
 First of it’s kind at FICO
 Oracle 11g RAC
 Linux
 Collaboration
 Building an Oracle RAC environment is not solely a DBA effort – it’s an
infrastructure effort spanning just about every group in the CIO Department
as well as Software development and the implementation teams.
 Timeline
 Due date was fixed and aggressive.
 No time for incremental approach as recommended.
 Everything was new
 Comprehensive end-to-end testing needed.
 Switches, storage array, cabling, …..
13
© 2009 Fair Isaac Corporation. Confidential.
Our Approach
 Build out all new infrastructure
 Complete all enhancement requests on “old” environment
 Limit migration to infrastructure only.
 Create performance baselines in old environment to compare
against.
 Conduct comprehensive testing on new infrastructure
 Mock migrations, Regression Testing, Load Testing, Destructive test
on Database, Rolling Patch test, DR Failover Test, DR Fail-back
Testing, DR performance test.
 Test the full data migration process to determine exact duration
 Complete the migration in a single outage window.
 Agree on a point-of-no-return. After data starts flowing,
there is no rollback.
14
© 2009 Fair Isaac Corporation. Confidential.
Environment Conceptual Design
WWW
Primary Site
Standby Site
Cluster
Cluster
DataGuard
15
© 2009 Fair Isaac Corporation. Confidential.
Node Failure/Planned outage
WWW
Primary Site
Standby Site
Cluster
Cluster
DataGuard
16
© 2009 Fair Isaac Corporation. Confidential.
Site Failure/Planned Outage
WWW
Standby Site
Primary Site
Cluster
Cluster
DataGuard
17
© 2009 Fair Isaac Corporation. Confidential.
Even if you’re on the right track, you will get run
over if you just sit there.
Will Rogers | US humorist & showman (1879 - 1935)
July 7, 2015
Confidential. This presentation is provided for the recipient only and cannot
be reproduced or shared without Fair Isaac Corporation's express consent.
18
© 2009 Fair Isaac Corporation.
Part 2
Now, Make it happen…
Alan Tai
Lead Engineer, Database Engineering
FICO
July 7, 2015
Confidential. This presentation is provided for the recipient only and cannot
be reproduced or shared without Fair Isaac Corporation's express consent.
19
© 2009 Fair Isaac Corporation.
Session
Objective
Part 2
 The technical challenge is to build a
reliable system exceeding higher SLA
requirements with less resources and
downtime. To migrate the database
across different endian platforms in 2
data centers limits the options.
 The intent of this session is to show you
how FICO built and migrated the
database. Hopefully, FICO experience
will save you time and efforts to do
similar migrations.
20
© 2009 Fair Isaac Corporation. Confidential.
© 2009 Fair Isaac Corporation. Confidential.
Agenda
 Configuration
 Server
 Hardware
 Operating System
 Network
 Storage
 Oracle
 CRS
 ASM
 RDBMS
 Migration Approaches
 Data Pump – Tips and Tricks
 Issues
21
© 2009 Fair Isaac Corporation. Confidential.
© 2009 Fair Isaac Corporation. Confidential.
Server Hardware Configuration
 Server Hardware 3 Intel based servers
 CPU’s: 2x dual core Intel Zeon
 Decision to span across as many nodes as possible for maximum
availability
 Memory
 RAM: 32GB per server
 SWAP
 Network
 6x physical 1 GbE ports
 IO
 Fibre Cards: 4x 4GB HBA’s
Optimal CPU/Memory ratio is 8 cores and 32 gig RAM per node.
22
© 2009 Fair Isaac Corporation. Confidential.
Server Operating System Configuration
 Server Operating System
 Decision for RedHat 4
 RedHat Linux vs Oracle Unbreakable Linux
 RedHat 4 vs RedHat 5
 Install with the default RPMs
 Some RPMs depend on each other and need to be installed together via
rpm –ivh option
 Kernel Configuration
kernel.shmall = physical RAM size / pagesize
kernel.shmmax = 1/2 of physical RAM
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default=4194304
net.core.rmem_max=4194304
net.core.wmem_default=262144
net.core.wmem_max=262144
 Oracle Metalink NOTE: 851598.1 Linux OS Requirements Reference
List for Database Server
23
© 2009 Fair Isaac Corporation. Confidential.
Server Network Configuration
 Redundancy
 Network Redundancy
 Bonding NICs
 Load Balance
 Public Network
 Monitor
 Backup
 Public Virtual Network
• Make sure the correct network
driver is used
• Set MTU size to 8000 instead of
9000 to avoid too large packets
 Private Network
 Use Jumbo Frame
 Drivers
 Use NIC & HBA drivers from OS
24
© 2009 Fair Isaac Corporation. Confidential.
Server Storage Configuration
 Types




Oracle Cluster Registry (OCR) disks
Voting disks
Flash Recovery disks
Data disks
 Redundancy
 RAID-5
 Multipath
 Performance
 15K RPM SCSI disks vs.
 EMC Enterprise Flash Drives
 Salability
 256 devices for each HBA limitation
 Raw Devices are obsolete
 O_DIRECT flag
25
© 2009 Fair Isaac Corporation. Confidential.
• Choose ASM disk naming
convention unique to each
environment. That will prevent
issues when storage team
reallocate asm labeled disks.
• oracleasm createdisk
volxxx_yyy /dev/emcpowerxx1
Oracle Configuration
 Oracle binaries - OFA
 Create separate ORACLE_HOMES for each component in the stack. That
will allow you to patch and upgrade them separately




Clusterware
ASM
RDBMS
Grid Control
 Cluster Readiness Verification
 Example: runcluvfy.sh stage -pre crsinst –n node1,node2,node3
 Load Balance via Oracle Net
Example:
OLTP_Service =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = node1-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = node2-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = node3-vip)(PORT = 1521))
(LOAD_BALANCE = ON)(FAILOVER = ON)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = OLTP_Service)
(FAILOVER_MODE = (TYPE=session) (METHOD=basic))
)
)
 High Availability
 Disaster Recovery via Oracle Physical Data Guard
 Redundancy via RAC
26
© 2009 Fair Isaac Corporation. Confidential.
Migration Approaches
 Oracle Streams
 Benefits
 Limit outage window
 Enables a fail-back solution at any time
 Draw backs
 Possible data loss
 Increase data management efforts
 Unpredictable outage time window
 Why it did not work
 Data Pump
 Benefits
 Can be tested, scripted and practiced many times
 Creates a perfect testing environment
 Simplified
 Draw Backs
 Longer outage
 Transport export dump to target environment
 Fail back difficult after data starts flowing
27
© 2009 Fair Isaac Corporation. Confidential.
Migration Steps
 Shut Down Web Access and bring application down
 Ensure DR Database is current
 DR was located in same data center as the new environment
 Using Data Pump – export full database to a NAS mount across DR database
and new Database Cluster.
 Using Data Pump – import data only to new RAC database using all three
nodes
 Split tables in three groups starting with largest ones
 Monitor import log file
 Start building indexes as soon as data load for large tables are complete
 Enable constraints
 Import Optimizer Starts
 Point client websites to new web servers.
 Startup Web and application in new environment
 Complete regression test
 Enable Client Access
28
© 2009 Fair Isaac Corporation. Confidential.
Data Pump – Tips and Tricks
 Performance Consideration







Fast Staging Storage – Export bump file
Full export faster than granular levels
Find the best parallelism
Create Tablespaces in advance
Preload Metadata is not faster
Pipeline Export & Import instead of a batch
Exclude
 Indexes
 Constraints
 Statistics
 Unlock statistics after import
 Bugs
 Corrupted Encrypted Data
 Decrypt – export – import - encrypt
29
© 2009 Fair Isaac Corporation. Confidential.
Issues
 Login Banners
 Disable all login banners permanently
 Nodes crashed under load
 Most issues did not show up unless you push a significant load
 MTU > 1500 is only used under load
 Interconnect waits
 Can be avoided by limiting cross node traffic
 Linux Kernel Updates
 ASM RPMs need to be updated manually, yum update or rpm –F won’t pick
them up
 Login Password is Case Sensitive on 11G
 Set sec_case_sensitive_logon = false to disable it
 ORA-600:[KDDUMMY_BLKCHK][][][18021] After ALTER TABLE ALLOCATE
EXTENT - There is a patch available patch # 6647480
30
© 2009 Fair Isaac Corporation. Confidential.
Agenda
 Cut-Over
 Post Go-Live Performance Issues
 Enhancements
 Performance Progression
 Conclusion
31
© 2009 Fair Isaac Corporation. Confidential.
© 2009 Fair Isaac Corporation. Confidential.
Cut-Over
 Preparation




Review test results - We are ready!
Determine precise outage window – 12 hours
Walk through the outage plan with the Client and technical teams
Engage Partners
 Oracle
 Engaged Oracle Advanced Customer services
 Have an Oracle Support resource on site ready to resolve any issues
immediately
 Have Oracle Support validate the RAC configuration.
 EMC
 Reviewed Storage layout and configuration
 Cut-Over Day
 Followed Migration Steps
 Absolutely no surprises.
 Post Cut-Over
32
 Watch for instability, tuning opportunities and bottlenecks
© 2009 Fair Isaac Corporation. Confidential.
Post Go-Live Performance issue
 Predominant Wait Event was User I/O – Blue
 CPU Usage (green) and Cluster overhead (gray) were not a concern
 User I/O is hampering scalability and effectiveness of the solution
 SLA Performance of 99.3% - better than old environment but not optimal
 Needed to maintain separate copy or database for extracts to limit impact on
real time SLA
33
© 2009 Fair Isaac Corporation. Confidential.
Enhancements
 Swap out traditional SAN disks with EMC Enterprise Flash Drives
(EFD).
 User I/O wait dropped by 90%.
 Overall SLA performance now over 99.5%
 Time spend on CPU was equal to or greater than User I/O Wait – A key
Scalability indicator
 Enabled elimination of separate database copy for data extraction
34
© 2009 Fair Isaac Corporation. Confidential.
Performance Progression
Average Mean response time per txn in ms
Average SLA for a week
Transactions per day in millions
233
175
98.73 99.3
5.7
7.7
77
99.97
10.0
*
At Risk period
Oracle 10g
Sun Solaris
*
3 Node Oracle 11g, RAC
Linux
*
Switch to EFD
Expand to another major market
Eliminate mirror copy
35
© 2009 Fair Isaac Corporation. Confidential.
AWR - Top 5 Timed Foreground Events
Standard HDD
Event
db file sequential read
Waits
Time(s)
1,394,308
DB CPU
gc current block 2-way
Avg wait (ms)
13,598
% DB time
10
Wait Class
73.56
2,733
User I/O
14.78
1,033,780
668
1
3.61
Cluster
log file sync
225,036
291
1
1.57
Commit
gc cr block 2-way
384,398
251
1
1.36
Cluster
EMC Enterprise Flash Drives
Event
Waits
Time(s)
DB CPU
Avg wait (ms)
% DB time
3,386
Wait Class
45.19
db file sequential read
2,175,979
1,740
1
23.23
User I/O
gc current block 2-way
1,874,874
849
0
11.34
Cluster
log file sync
218,718
310
1
4.14
Commit
gc cr block 2-way
582,344
272
0
3.63
Cluster
36
© 2009 Fair Isaac Corporation. Confidential.
Load Distribution
CPU Utilization
Disk I/O per second
37
© 2009 Fair Isaac Corporation. Confidential.
Conclusion - What made it work?
 Database Infrastructure
 Grid Control environment fully integrated and stable outside this effort.
 Database management process implemented (backup, monitoring,
maintenance)
 Get All Groups involved
 It is a collaborative effort.
 Unix, Network, Storage, Middleware, DBA, Development
 Get Partners involved
 Oracle Corporation – Local Support Engineers, Oracle product Support,
Advanced Customer Services
 RedHat, troubleshoot and validate OS configuration
 HP – Standard configuration for RAC
 EMC – Storage layout validation
 Get Executive Buy-In
 Test, Test, Test,…
 Most issues only showed themselves during load tests.
 Successful tests raise confidence levels and failed tests showed problems to
fix – win-win.
 Believe ( In the solution, in yourself, your team)
38
© 2009 Fair Isaac Corporation. Confidential.
Local Account SE
Terry Koch
Oracle
July 7, 2015
Confidential. This presentation is provided for the recipient only and cannot
be reproduced or shared without Fair Isaac Corporation's express consent.
39
© 2009 Fair Isaac Corporation.
Local Oracle team’s Involvement
 Validate Certification Matrix
 Help Educate the teams
• Lunch-n-Learn, Training, One day free seminars
 Integration with Metalink
 Partner with local Oracle RAC experts – DBConsult
 On-site Support during the migration
• Oracle Advanced Customer Services (ACS)
• Local Oracle SE
 Recommended Oracle Grid Control Packs
•
•
•
•
40
Performance Tuning
Diagnostics
Configuration Management
Provisioning
© 2009 Fair Isaac Corporation. Confidential.
References
 EMC
South Exhibition Hall Level 1 - Booth #1801
http://www.oracle.com/ocom/groups/public/documents/webcontent/01844
7.pdf
www.emc.com/oraclesolutions
 HP
South Exhibition Hall - Booth #1301
Oracle GRID on HP ProLiant 480c…white paper
http://h20195.www2.hp.com/V2/GetPDF.aspx/4AA1-1100ENW.pdf
41
© 2009 Fair Isaac Corporation. Confidential.
THANK YOU
Terry Koch
Oracle Corporation
Lelanie Moll
[email protected]
July 7, 2015
Confidential. This presentation is provided for the recipient only and cannot
be reproduced or shared without Fair Isaac Corporation's express consent.
42
© 2009 Fair Isaac Corporation.
Alan Tai
[email protected]