1 / 30

Module 3: Week 6 Database MANAGEMENT

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

abba
Download Presentation

Module 3: Week 6 Database MANAGEMENT

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Module 3: Week 6Database MANAGEMENT ITEC 450

  2. Access Database – SQL Plus ITEC 450

  3. SQL Plus Login ITEC 450

  4. SQL Plus Commands ITEC 450

  5. OEM (https://{hostname}:1158/em) ITEC 450

  6. 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

  7. LOOKING AT DATA DICTIONARY ITEC 450

  8. 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

  9. 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

  10. 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

  11. Module 3 Database Management ITEC 450 Section 1 Database Change Management

  12. 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

  13. 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

  14. 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

  15. 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

  16. 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

  17. 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

  18. Module 3 Database Management ITEC 450 Section 2 High Availability Requirement and Architecture

  19. 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

  20. 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

  21. Availability Solutions • Automate DBA functions • Exploit high-availability DBMS features • Exploit clustering technology • Hardware redundancy design ITEC 450

  22. 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

  23. Clustering Technology A cluster is a group of interconnected servers for increasing the reliability of servers. Oracle standby database: ITEC 450

  24. ORACLE REPLICATION ITEC 450

  25. 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

  26. SQL Server Transactional Replication Architecture ITEC 450

  27. Oracle Real Application Cluster (RAC) Oracle database with RAC architecture build higher levels of availability on top of the standard Oracle features. ITEC 450

  28. 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

  29. RAID Technology RAID example with 4 disks and striping. Pages 1-4 can be read/written simultaneously ITEC 450

  30. Wrap Up • Assignment 6-1: Research Paper: Database High Availability ITEC 450

More Related