INTRODUCTION OF WEEK 6 Assignment Discussion Graded 3

INTRODUCTION OF WEEK 6 Assignment Discussion § Graded 3 -1 -1 (DBMS installation): § § hardware requirements, open. ISO file Installation steps Lessons Learned: error, documentation, instruction manual, need more info ITEC 450 § § 2011 Fall § Turned in: 3 -1 -2 (Creation of Database) Due this week: 5 -1 (Connectivity Paper) Review of previous week and module § Database Connectivity: ODBC, JDBC § Oracle Connectivity and Network: listener, naming method § Module 2: Database Installation and Configuration § Overview of this week § Database access and metadata review § Database Change Management § Availability Requirement and Architecture 1

ACCESS DATABASE – SQL PLUS 2011 Fall ITEC 450 2

SQL PLUS LOGIN 2011 Fall ITEC 450 3

SQL PLUS COMMANDS 2011 Fall ITEC 450 4

OEM (HTTPS: //{HOSTNAME}: 1158/EM) 2011 Fall ITEC 450 5

2011 Fall ITEC 450 MODULE 3 DATABASE MANAGEMENT 6 Section 1 Database Change Management

DRIVERS FOR CHANGE MANAGEMENT 2011 Fall ITEC 450 Change is inevitable but necessary for business survival and success. Missed a requirement – an existing system is missing a feature Identified a defect Scalability demand, marketplace changes Policy and politics – process, procedure or methodology; legislation changes 7

CHANGE MANAGEMENT REQUIREMENTS Proactively change – the earlier to make change, the lower cost to achieve it Planning analysis – intelligently examining the change whether it is necessary, and planning to do it right the first time Impact analysis – comprehensive impact and risk analysis Execution – standardization of procedure, availability consideration, quick and efficient delivery ITEC 450 2011 Fall 8

TYPES OF DATABASE CHANGES DBMS software – versions and releases for new features, functions, bug fixes, support models Hardware configuration – memory, CPU, storage device Logical and physical design for an application Physical database structures ITEC 450 2011 Fall 9

IMPACT OF DATABASE CHANGES 2011 Fall ITEC 450 Data Definition Language is mainly used for database changes. Create, alter, and drop schema objects require exclusive access to the specified object The change is implicitly committed The change may cause dependent objects become invalid, which may need to recompile or reauthorize schema objects Recovery from a database change is challenging, and has to be well-planned. 10

EXECUTION OF DATABASE CHANGES Request database change � Standardized change requests � Managing database changes from one database environment to another � Condense a series of changes by comparing database structures, and migrate the differences � ITEC 450 Maintain current database structures – source control and version control Migration process 2011 Fall 11

EXAMPLES OF DATABASE CHANGES 2011 Fall ITEC 450 Change Management: planning (necessary? ) impacts, and execution (recovery? ) Add a new column to the end of a table Resize a column from char (8) to char (12) Re-create a new package, procedure, or function Add a new column to the middle of a table 12

2011 Fall ITEC 450 MODULE 3 DATABASE MANAGEMENT 13 Section 2 High Availability Requirement and Architecture

DRIVERS OF AVAILABILITY 2011 Fall ITEC 450 Availability is the condition where a given resource can be accessed by its consumers. Mandate for 24 x 7 availability Shrinking maintenance window Full-time availability: airline reservation systems, credit card approval Cost of downtime 14

AVAILABILITY PROBLEMS � Server infrastructure problems: network , loss of the server hardware, storage, operating system � Software failure: DBMS, application, corruption of data Data ITEC 450 loss of the data center System failures 2011 Fall Disasters: failures � Procedure problems: security and authorization, loss of database objects � Human errors: loss of data, DBA mistakes 15

AVAILABILITY SOLUTIONS 2011 Fall ITEC 450 Automate DBA functions Exploit high-availability DBMS features Exploit clustering technology Hardware redundancy design 16

HIGH AVAILABILITY DBMS FEATURES Solutions to disasters Solutions to system failures � � � ITEC 450 Oracle data guard – a single primary database and one or more standby database � SQL Server database mirroring – a single copy of the mirrored database that must reside on a different server instance, usually on a separate physical server in a different location � Hardware replication: EMC SRDF � 2011 Fall Oracle Real Application Clusters SQL Server transactional replication DB 2 Data Sharing Solutions to data failures � � � Backup and recovery capability Flashback database: view data at a point-in-time in the past Partition: decompose large tables and indexes into smaller and more manageable pieces 17

CLUSTERING TECHNOLOGY ITEC 450 Oracle standby database: 2011 Fall A cluster is a group of interconnected servers for increasing the reliability of servers. 18

SQL SERVER DATABASE MIRRORING 2011 Fall ITEC 450 Database mirroring maintains an exact copy of the database on the mirror. It works at the level of the physical log record (by sending the actual log records to the mirror server). 19

2011 Fall ITEC 450 SQL SERVER TRANSACTIONAL REPLICATION ARCHITECTURE 20

ORACLE REAL APPLICATION CLUSTER (RAC) 2011 Fall ITEC 450 Oracle database with RAC architecture build higher levels of availability on top of the standard Oracle features. 21

HARDWARE REDUNDANCY DESIGN 2011 Fall ITEC 450 No single point of failures on CPU, Memory, I/O controller, Network, etc. Clustered software to failover to another server in seconds RAID technology (Redundant Arrays of Inexpensive Disks) – one large logical storage unit with a set of physical disk drives 22

RAID TECHNOLOGY 2011 Fall ITEC 450 RAID example with 4 disks and striping. Pages 1 -4 can be read/written simultaneously 23

WRAP UP Assignment 6 -1: Research Paper: Database High Availability 2011 Fall ITEC 450 24