Transcript Document

Automating Upgrades
Presented to:
zSeries Oracle Special Interest Group Conference
Redwood Shores, California
March 28 – April 2, 2004
Michael Zechman
Database Consultant, Convertible Technology, Inc.
Defense Enterprise Computing Center (DECC)
Mechanicsburg, Pennsylvania
Agenda
• How can Automating Upgrades benefit
zSeries Oracle Customers
• Why Automate
• What is required for an Upgrade
• Upgrade Automation
• Summary
• Questions and Automation Discussion
Presented at:
zSeries Oracle SIG Conference 2004
2
Our People Make a Difference Every Day.
How can Automating Upgrades
benefit zSeries Oracle Customers
• Why should I be Listening to this on
April Fools Day! Mike’s Top 10 Reasons:
– Recommended to Jim an Automation Session Per SIG.
– Share automation techniques among Oracle SIG’s very unique
zSeries user community
– Upgrades now take less than 5 person-minutes per Database
– Your site might not require automated Upgrades, but
• Pieces of it may be useful
• Might spawn ideas for your future automation
• Information sharing on Automation (other than Upgrades) will be
discussed
– Upgrade discussion was picked for a reason – It was easy to
pull pieces, easy to create, and use.
Presented at:
zSeries Oracle SIG Conference 2004
3
Our People Make a Difference Every Day.
How can Automating Upgrades
benefit zSeries Oracle Customers
• It is the Second session in the
afternoon?
– If you are overloaded with work, consider automating
processes (or your manual procedures)
– Do your customers demand instant attention? Think about
adding dynamic automated services for those types of
requests
– Generalized Method of how to discuss and share automation
– Don’t miss putting Jim on the spot!
– Prize Time – A picture of something I bought last Year! I have
been waiting to get zSeries input. Only a zSeries Group may
be able to help me convince my wife to appreciate it. Your
should hear the initial and monthly comments from her on it! I
need help! Best comment wins a Prize!
Presented at:
zSeries Oracle SIG Conference 2004
4
Our People Make a Difference Every Day.
Why Automate
• Mainframe Computing
– 7 “Hosts or Boxes”; one split
– 45 Logical Partitions (LPARs)
– 4,713 Millions of Instructions Per Second (MIPS)
• Mid Tier
– 230 servers hosted
– Windows, Unix, LINUX Operating Systems
• Storage
– RAID DASD
• EMC, Spectris, IBM RAMAC, HDS 7700E, IBM Enterprise System Server
• 25,544 GB of DASD
– Tape storage
• 308,000 tapes in inventory, 1097 tapes processed on an average day
• 27 Tape Silos, 3 Virtual Tape System (VTS), 1 Automatic Transport Unit (ATL) for mid-tier,
108 stand-alone tape transports
• 425 Applications and 7 Different types of Databases (Software)
Presented at:
zSeries Oracle SIG Conference 2004
5
Our People Make a Difference Every Day.
Why Automate
• Our Team - Oracle and IDMS
– 160 + Database Systems to Manage
• 70 Production
• 45 Oracle zSeries databases, 15 production
– Mainframe, UNIX, and Windows platforms
– Service organization providing computing
services to our defense customers
– Protect Integrity of Database
– Staff of Nine
– Team generated Automation and Support!
Presented at:
zSeries Oracle SIG Conference 2004
6
Our People Make a Difference Every Day.
Why Automate
• More Workload and Less Staff
5000
4500
4000
3500
3000
2500
2000
1500
1000
500
0
4950
4713
4576
3085
3410
2554
2675
2554.4
2386
CPU MIPS
2200
1889
DASD GB
1951
Staffing
1390
1183
1151
853
598
546
321
312
235
189186
'93
'94
Presented at:
zSeries Oracle SIG Conference 2004
656
512
257
'95
December 2003
722
287
322
309
'96
(DASD * 10)
976
964
'97
337
322
'98
'99
323
'00
'01
319
324
338
'02
'03
315
Current Trans
Goal
7
Our People Make a Difference Every Day.
Why Automate
•
Customer test environments that are demanding - Aside from normal scheduled
backups etc., complex application testing environments require dynamic
database service that we must support:
–
–
–
–
–
–
–
–
–
–
–
–
–
•
Backup
Restore
Restore PIT (planned, but not automated yet-done manually)
Clone/Copy of a database
Cross LPAR Support For Clone/Copy
Startup
Shutdown
Recycling
Database Changes
Special Case Backups
Cross LPAR Support For Special Backup Saves
Database Software Upgrades
Of course, at 4:00 PM Friday or during evening Hours!
During the Last Fiscal Year – Not including scheduled jobs, dynamic automation
supported 1550+ Dynamic Requests From our customer application DBA’s
without our involvement! Most features not turned on until late summer 2003.
Presented at:
zSeries Oracle SIG Conference 2004
8
Our People Make a Difference Every Day.
What is Required For an Upgrade
– Unload Software or Downloaded Cumulative Oracle Patches
– Prepare Load Libraries For Use (receive and authorize etc.)
– Database Upgrade Capable – Must validate and change prior to
upgrade
– Possible Changes to Current Automated Scheduled Processes
– Backup Before and After
– Startup and Shutdown of Database
– Startup and Shutdown of Services (Instance and Listener)
– OSDI Parameter Changes
– INITORA Changes (multiple times)
Presented at:
zSeries Oracle SIG Conference 2004
9
Our People Make a Difference Every Day.
What is Required For an Upgrade
– Software Library Alias Names Changes
– Oracle .SQL Executions and Validation (CATPATCH etc.)
– Unique Changes (For example remove RBS’s add UNDO or
change all to locally managed)
– Validation of Database Entities and successful Upgrade
– Security Updates To Database Once Upgrade Complete
– Validation Database Passes Security Requirements
– Document Date, Time, Technician Performing Upgrade
– Archival of Upgrade Output
– Database Validation Upon Successful Upgrade
Presented at:
zSeries Oracle SIG Conference 2004
10
Our People Make a Difference Every Day.
What is Required For an Upgrade
Upgrade issues we had:
–
–
–
–
–
Multiple upgrades have to be done to a database per year
Time consuming
Repetitive work
Error prone especially when doing multiple upgrades at a time
Takes 2 to 4 person hours for each database upgrade after
libraries are prepared
– We had to perform more upgrades than the number of
databases we have. The overall number is based on the
number of baseline application backups/saves used for
application testing
Presented at:
zSeries Oracle SIG Conference 2004
11
Our People Make a Difference Every Day.
Upgrade Automation
• Our Requirements
– Get out of Dynamic work requests. It was killing our
productivity!
– Application DBA’s must NOT be able to perform functions
with direct access. This would be a security violation.
– Protect integrity of overall database system
– Must be bullet proof – upgrade validation was a priority
– Must be able to track history of changes and requests
– Provide capability for customers to upgrade test databases
– Provide capability for customer to upgrade various database
backups – Many different baseline testing backups/saves
– Possibly provide capability for customer DBA’s to upgrade
production
Presented at:
zSeries Oracle SIG Conference 2004
12
Our People Make a Difference Every Day.
Upgrade Automation
Upgrade Automation System
– Took 4.5 person-weeks to create
– Used pieces we had, pulled from web, and
created ourselves
– ISPF Panels for online validation and
request submission
– 15+ ISPF Edit Macros – Batch mode
– 10+ REXX Processes
– 15+ CLIST to execute ISPF EDIT Macros
Presented at:
zSeries Oracle SIG Conference 2004
13
Our People Make a Difference Every Day.
Upgrade Automation
Upgrade Automation System
– 3 Assembler Modules
– 215 Job Steps not including backups
– Scheduling Product
• Control-M Schedules
• Control-O Rules
– Messaging used to communicate between
processes
– Batch ISPF, very nice tool
– Batch SDSF, cool tool
Presented at:
zSeries Oracle SIG Conference 2004
14
Our People Make a Difference Every Day.
Upgrade Automation
First Phase - Not in our Automation
•
•
•
•
1 Step per LPAR or site
Via Oracle’s ISPF Panels, manual, or via
Oracle’s new CD installation (specifics
unknown at time of presentation creation. Note:
Being presented at SIG by Oracle)
Unload software or downloaded cumulative
Oracle patches
Prepare load libraries for use (receive and
authorize etc.)
Presented at:
zSeries Oracle SIG Conference 2004
15
Our People Make a Difference Every Day.
Upgrade Automation
Presented at:
zSeries Oracle SIG Conference 2004
16
Our People Make a Difference Every Day.
Upgrade Automation
Presented at:
zSeries Oracle SIG Conference 2004
17
Our People Make a Difference Every Day.
Upgrade Automation
Presented at:
zSeries Oracle SIG Conference 2004
18
Our People Make a Difference Every Day.
Upgrade Automation
Presented at:
zSeries Oracle SIG Conference 2004
19
Our People Make a Difference Every Day.
Upgrade Automation
Job name,
and job
number
are in
position
81-100
Presented at:
zSeries Oracle SIG Conference 2004
20
Our People Make a Difference Every Day.
Upgrade Automation
– Is Database Upgrade Capable? Validation and changes prior to
Upgrade
•
•
•
•
Oracle Upgrade Validations SQL’s Used
Minor modification for automated validation purposes
A ROWCHECK Procedure was used for Validation
Upgrade Only Continues or Changes are made based on Return Codes
– Possible Changes to Current Automated Scheduled Processes
• Batch ISPF Edit Macros Used
• Inserts IEFBR14 and // to stop automated procedures/schedules
– Backup Before and After
• Messages Sent ‘FORCE JOB …’
– Startup and Shutdown of Database
• Normal Startup and Shutdown Batch Streams.
• Parameters added/changed based on Exclusive or Migrate mode etc
Presented at:
zSeries Oracle SIG Conference 2004
21
Our People Make a Difference Every Day.
Upgrade Automation
– Startup and Shutdown of Services (Instance
and Listener)
• Dynamic Starting and Stopping of Services
• Job step was set to ‘Wait’ until service is
Stopped/Started
• Used REXXWAIT Assembler module from Web
• Created Customized REXX SERVSTAT to call
REXXWAIT
• SERVSTAT - Customized REXX to perform
Operator Commands – Loops and waits
Presented at:
zSeries Oracle SIG Conference 2004
22
Our People Make a Difference Every Day.
Upgrade Automation
REXXWAIT and SERVSTAT execution messages
Presented at:
zSeries Oracle SIG Conference 2004
23
Our People Make a Difference Every Day.
Upgrade Automation
– OSDI Parameter Changes
• If needed, ISPF macros and ALTER SERVICE
commands
– INITORA Changes (multiple times)
• Batch ISPF EDIT Macros
• IEBGENER
• JOBPARM module to pass parameters
Presented at:
zSeries Oracle SIG Conference 2004
24
Our People Make a Difference Every Day.
Upgrade Automation
• Sample ISPF EDIT macro:
VPUT in CLIST
and VGET in
Macro can be
used to pass
data to and
from EDIT
Macro
Presented at:
zSeries Oracle SIG Conference 2004
25
Our People Make a Difference Every Day.
Upgrade Automation
• ISPF Batch Sample Execution:
Presented at:
zSeries Oracle SIG Conference 2004
26
Our People Make a Difference Every Day.
Upgrade Automation
JOBPARM Sample - Passes variables to CLIST/EDIT Macro:
Presented at:
zSeries Oracle SIG Conference 2004
27
Our People Make a Difference Every Day.
Upgrade Automation
– Software Library Alias Names Changes
• Dynamic Syntax Creation for IDCAMs
• Batch ISPF Edit Macro’s
• IDCAMS
– Oracle .SQL Executions and Validation
• SQLPLUS
• ROWCHECK
• WHENEVER EXIT xxxx
– Unique Changes (For example Remove RBS’s add UNDO or Change all to
locally managed)
• Batch SQLPLUS
• Batch ISPF Edit Macros
• REXX code (if required)
– Validation of Database Entities and successful Upgrade
• SQLPLUS
• ROWCHECK
– Security Updates To Database Once Upgrade Complete
• Security Scripts Execute
Presented at:
zSeries Oracle SIG Conference 2004
28
Our People Make a Difference Every Day.
Upgrade Automation
– Validation Database Passes Security
Requirements
• Done during testing, but we have an automated
JCL Procedures to perform this task
– Document Date, Time, Technician
Performing Upgrade (Done, not implemented)
• ISPF EDIT Macro
• IEBGENER
Presented at:
zSeries Oracle SIG Conference 2004
29
Our People Make a Difference Every Day.
Upgrade Automation
– Archival of Upgrade Output
SPOOL TOOL – Database team
developed. Takes SDSF
Spooled Jobs or Step DD
Output and Writes it to an
Archive File
Useful Tool!
Presented at:
zSeries Oracle SIG Conference 2004
30
Our People Make a Difference Every Day.
Upgrade Automation
– Database Validation Upon Successful
Upgrade
• SQLPLUS
• WHENEVER EXIT xxxx
• ROWCHECK
Presented at:
zSeries Oracle SIG Conference 2004
31
Our People Make a Difference Every Day.
Upgrade Automation
• How do you permit non authorized DBA’s to perform these
functions while still protecting the integrity of the system?
•
Under USER Authority
–
–
•
Create a short lived dataset based on name of instance and function desired. User must have authority
to create it. The existence of this dataset is checked later to validate authority.
Send Message (causes message to be written to SYSLOG)
Under SYSTEM Authority
–
–
–
–
–
–
–
–
Control O Detects Message
CTOSERV Deciphers Messages and Passes Parameters to REXX EXEC
REXX Exec Validates Parameters and executes unique REXX code for each Function
Code verifies that the short lived dataset created above exists. If it doesn’t, then user is trying to
manually send a message. ‘Gotcha’
A Batch Job is then submitted under our group’s scheduling user-id which is monitored by Control-M
Messages are sent to user when various phases are complete
Summary file is updated
Since Control-M monitors the job, any failures are quickly detected and resolved.
Presented at:
zSeries Oracle SIG Conference 2004
32
Our People Make a Difference Every Day.
Upgrade Automation
• The picture I discussed earlier
– If you were a woman that did not need
computer equipment, what would you not
want your husband to bring home?
Presented at:
zSeries Oracle SIG Conference 2004
33
Our People Make a Difference Every Day.
Summary
– Secured
– Time consuming and redundant effort – Each
upgrade manually took 2 to 4 person-hours after
libraries were prepared
• Automated < 1-5 minutes technician time
• Saved time already
– Dynamic requests while being controlled and
monitored by operations via Control-M/O (standard
scheduling/monitoring facility)
– History of requests and job executions
Presented at:
zSeries Oracle SIG Conference 2004
34
Our People Make a Difference Every Day.
Summary
– Easy to add on for future releases.
– Estimated 4-6 hours to update process for
each new release.
• JCL PROC Change
• Panel Change
• ISPF EDIT Macro Change
– Messages sent to user after each phase.
– Customers happy
– Permits customer/our team flexibility
– More time to take on new database tasks
Presented at:
zSeries Oracle SIG Conference 2004
35
Our People Make a Difference Every Day.
Summary
–
–
We are happy
Not perfect upgrade system
•
•
•
Must be customized for each release
Occasional restarts required (testing phase)
Restarts not easy due to return code ‘IF’ etc. in PROC
Presented at:
zSeries Oracle SIG Conference 2004
36
Our People Make a Difference Every Day.
Summary
• Future Automation
– SPOOL TOOL integration
– Restore point in time recovery
– Clone/Copy Hot Backup to a test database
– Clone/Copy Databases Between Data
Centers?
– Use on our UNIX systems? Conversion
seems inevitable. Some tools exist.
Suggestions?
Presented at:
zSeries Oracle SIG Conference 2004
37
Our People Make a Difference Every Day.
Questions and Automation Discussion
–Questions or comments
–Automated Discussion
• Share Site Automation on SIG?
• Other Automation
–Thank you and have a great
conference
Presented at:
zSeries Oracle SIG Conference 2004
38
Our People Make a Difference Every Day.