310 likes | 483 Views
Module 3: Week 6 Database MANAGEMENT. Access Database – SQL Plus. SQL Plus Login. SQL Plus Commands. OEM ( https://{hostname}:1158/em). The Data Dictionary. Looking at Data Dictionary Components Using Data Dictionary Views Useful Dynamic Performance Views
E N D
Module 3: Week 6Database MANAGEMENT ITEC 450
Access Database – SQL Plus ITEC 450
SQL Plus Login ITEC 450
SQL Plus Commands ITEC 450
OEM (https://{hostname}:1158/em) ITEC 450
The Data Dictionary • Looking at Data Dictionary Components • Using Data Dictionary Views • Useful Dynamic Performance Views • Examining Table Structure Using SQL*Plus and iSQL*Plus ITEC 450
LOOKING AT DATA DICTIONARY ITEC 450
LOOKING AT DATA DICTIONARY • Generally, USER, ALL, and DBA views are in sets • USER_TABLES, ALL_TABLES, DBA_TABLES • Each view has nearly identical columns • USER version omits OWNER column; it also sometimes omits columns to simplify the view • V$ and GV$ views are in sets • There are few views that don’t begin these prefixes • For simplicity, all views (except DBA ones) prefix public synonyms and public permission to query ITEC 450
Using Data Dictionary Views • Frequently used (static) data dictionary views: • USER_TABLES, USER_VIEWS • ALL_DEPENDENCIES • USER_ERRORS • USER_INDEXES, USER_IND_COLUMNS • DBA_SOURCE • USER_TAB_PRIVS, ALL_TAB_PRIVS_MADE • USER_TAB_PRIVS_MADE • DBA_USERS • PRODUCT_COMOPONET_VERSION ITEC 450
Useful Dynamic Performance Views • Begin with V$ and have a counterpart GV$ view • V$SYSSTAT • V$SQL • V$SESSTAT • V$SESSION_WAIT • V$FILESTAT • V$FILESTAT • Primary use: tuning the database system • Oracle provides options for gathering/viewing stats • Statistics are used to tune a database • This book does not cover the details of DB tuning ITEC 450
Module 3 Database Management ITEC 450 Section 1 Database Change Management
Drivers for Change Management 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 ITEC 450
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
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
Impact of Database Changes 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. ITEC 450
Execution of Database Changes • Maintain current database structures – source control and version control • Migration process • 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
Examples of Database Changes 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 ITEC 450
Module 3 Database Management ITEC 450 Section 2 High Availability Requirement and Architecture
Drivers of Availability Availability is the condition where a given resource can be accessed by its consumers. • Mandate for 24x7 availability • Shrinking maintenance window • Full-time availability: airline reservation systems, credit card approval • Cost of downtime ITEC 450
Availability Problems • Disasters: loss of the data center • System failures • Server infrastructure problems: network , loss of the server hardware, storage, operating system • Software failure: DBMS, application, corruption of data • Data failures • Procedure problems: security and authorization, loss of database objects • Human errors: loss of data, DBA mistakes ITEC 450
Availability Solutions • Automate DBA functions • Exploit high-availability DBMS features • Exploit clustering technology • Hardware redundancy design ITEC 450
High Availability DBMS Features • Solutions to disasters • 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 • Solutions to system failures • Oracle Real Application Clusters • SQL Server transactional replication • DB2 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 ITEC 450
Clustering Technology A cluster is a group of interconnected servers for increasing the reliability of servers. Oracle standby database: ITEC 450
ORACLE REPLICATION ITEC 450
SQL Server Database Mirroring 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). ITEC 450
Oracle Real Application Cluster (RAC) Oracle database with RAC architecture build higher levels of availability on top of the standard Oracle features. ITEC 450
Hardware Redundancy Design • 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 ITEC 450
RAID Technology RAID example with 4 disks and striping. Pages 1-4 can be read/written simultaneously ITEC 450
Wrap Up • Assignment 6-1: Research Paper: Database High Availability ITEC 450