Download Dababase basic intro for Database administrator and more Lecture notes Introduction to Database Management Systems in PDF only on Docsity!
Database
Administration & Management
Lecture 01
Data & MetadataPolicy Analysis Design Data Administrator ITInfrastructure DBA (ifno SA) System Administrator Development Testing Implementation (Databases, applications) Maintenance andTuning Database Administrator Database Administrator (ifno DA)
- System Design
- Database Design
- Application Dev elopment
- Unit Testing
- Integration Testing - Application Shakeout - Testing With RelatedSystems - VolumeTesting - Operational Status Production Quality Test Assurance DBMSEnvironments
- At least 2 separate environmentsmust be created for a quality database implementation.
- Thetest environment neednot be exactly the same
- May not havesameresourcesbut mayaccesssameapplication software.
- May only contain asubset of data.
- DBMSsoftware maybe of laterversion. Choosing the DBMSArchitecture
- Enterprise
- Departmental
- Personal
- Mobile
- Cloud
PersonalDBMS
- Single user
- Lowto medium powerPC
- Examples
- Microsoft Access
- OracleDatabase Personal Edition
- DB2Personal Edition
- Not useful for large/multi-user applicationsor sharedwork
- Lowcost Mobile DBMS
- Runson asmart phone or pervasive device
- Specializedversion of adepartmental or enterprise DBMS
- Remoteusers,not usually connectedto the network
- Datacanbe synchronized
Cloud Computing
- Acloud databasesystem delivers DBMSservicesover the Internet.
- Agood example of a Cloud Computing service is offered by Salesforce.com,which delivers accessto aCRMapplication over the web.
- Another aspect prevalent with Cloud computing offerings is that users can rent computing power with no commitment. Insteadof buying a server,you canrent the useof one and payjust for what youuse. - Thisusedto be referredto asutility computing becauseit mimics how peoplepayfor utilities, suchaswater or electricity. - It is a“pay asyou go” service to minimize maintenancecost,efforts andadministration.,
- Pervasive devices suchassmart phonesoften are usedto interact with data in the cloud.
- Utilizing cloud database systems can enable organizations, especially smaller ones without the resourcesto invest in an enterprise computing infrastructure, to focuson their businessinstead of their computing environment. DBMSClustering
- Wheremultiple independent systemswork together asasingle, highly available system in a distributed environment.
- The main advantage of shared-nothing clustering is scalability. In theory, a shared-nothing multiprocessor canscaleup to thousandsof processorsbecausethey do not interfere with one another—nothing isshared.
- Eachprocessor still hasits own private memory, but all the processorscandirectly addressall the disks. Typically, shared-disk clustering does not scale as well for smaller machines, as shared-nothing clustering. Shared-disk clustering is better suited to large-enterprise processingin a mainframe environment.
- Failover support.
Choosing the DBMS
- Tier- 1 vendors
- IBM, Oracle, Microsoft (only for Windowsplatforms). Mainframe WindowsServer Unix SunSolaris Linux others? DesktopOS Windows XP/Vista / 7 Linux Mac OS DBMSDecision Factors
- Operating System support for existingsystem.
- OrganizationType (Government, bank, insurance, health,universities)
- Benchmarks
- Scalability
- Availability of Tools (support of required users and size) (may include query and analysis tools, data warehousing support recovery tools, tools, database performance administrationmonitoring tools, tools, backup capacity and planning tools, database utilities, and support for various programminglanguages.)
- Availability of Technicians(DBAs,Programmers, SAs,etc.)
- Cost of Ownership (license, any supportive software, professionalsand administrators, hardware etc.)
- ReleaseSchedule (Versions, Releases)
- ReferenceCustomers (DBMSvendorsupplycurrent userreferences?)
DBMSPrerequisites
ADBMSis a complex piece of software that requires up-frontplanning for
installation to besuccessful.
- Hardware Requirements
- CPU(version/speed), firmware, memory,etc.
- StorageRequirements
- DBMS(catalog, logs, work files,etc.)
- System, Applications
- Memory Requirements
- Data buffers and cache(see next slide for example)
- Program cache
- Etc.
- Software Requirements
- Allied Agents (middleware)
- Languages and Compilers
- Configuration
- …ofthe DBMS
- …ofconnectingsoftware Program 1 Buffer Pool DBMS Database (1) Programrequestsa row of data (2) DBMSfindsthe requesteddata (3) And movesit to the bufferpool (4) And tothe program ( 5 ) A subsequent request is made for the same row of data Program 2 (6) DBMS finds the datain thebufferpool (7) And moves itto the programwithout readingit fromdisk ADBMSrequires a significant amount of memory to cache data in memory structures in order to avoid I/O. Reading data from a disk storage device is always more expensiveand slower than moving the data around inmemory. In general, the larger the buffer pool, the longer thedata canremain in memory and the better overall databaseprocessingwill perform.
Deciding When toUpgrade RewardsofUpgrade Drawbacks ofUpgrading New features and functionality are only available in the newversion/release An upgrade to the DBMSusually involves some level of disruption to businessoperations. Purchased applications may requirespecific version functionality Other disruptions canoccur,suchashaving to convert database structures New DBMSreleasesusually deliver enhanced performance and availability Thecost of an upgrade canbe a significant barrier to DBMSreleasemigration DBMSvendors will often provide better support and respond to problems faster fora new release of theirsoftware Anew DBMSrelease will generate SQLaccess paths that perform worse than before Production migration to a new DBMSrelease will align the test and production database environments, thereby providing a consistent environment for development and implementation Supporting software products may lack immediate support for a new DBMSrelease Cost savings. The DBMSvendor may charge extra if running two versions of the DBMS(new in test; old inprod) New DBMSreleasesmaycausefeatures and syntax to bedeprecated Installation Verification
- Testthat the DBMShasbeenproperlyinstalled
- Usevendor-suppliedprogramsand installation verificationprocedures
- Build your own
- Sample SQL:SELECT,INSERT,UPDATE,and DELETEstatements issued against sample databases
- Testall requiredconnections
- Transaction processors,drivers, etc.
Oracle Database Oracle Databasearchitecture
- Blocks:smallest unit of storage in a tablespace of database. Block is of a sizeof few operating system’s block size; specific number of bytes.
- Extents: contains one or more databaseblocks.
- Segments:a group of extents to form adatabaseobject called units, suchastable ofindex.
- Instance:consists of memory andprocessstructure; asa meanto accessanOracle database; always opens one and only onedatabase.
- More than one instancewill usethe samedatabasein Oracle RealApplication Cluster (RAC).
- Tablespace;datafiles in oracle are grouped together into one or more tablespaces. Oracle 11g installation creates minimum 2 table spacesi.e. SYSTEM& SYSAUX.Defaultinstallation creates six tablespaces.
- Oracle allows to create a special BIGFILEtablespace;aslarge as 128TB.
- Eachtablespace contains tables and indexes.
- Tablespaceconsists of one or moredatafiles.
- Adatafile canbe part of only onetablespace.
Installation Software Requirements
- Protocol:
- TCP/IP,TCP/IPwith SSL,NamedPipes
- Supported web browser:
- Internet explorer, Netscape,chrome, Firefox, safari.
- User privilege: Administratorgroup. Oracle Installation options
- Enterprise Edition
- Standard Edition (up to 4CPUs)
- Standard Edition One(single server with max 2 CPUs)
- PersonalEdition (Either standard or enterprise, cannot be usedin production environment)
- ExpressEdition (entry-level, one CPUor one dual-core CPU,1GBram,4GBdisk)
Oracle DBAToolsfor AdministratingDatabase
- OracleUniversal Installer(OUI)
- A utility that installs your Oraclesoftware and options. It canautomatically start
OracleDatabaseConfigurationAssistant to install a database.
- OracleDatabaseConfigurationAssistant (DBCA)
- A utility that creates a database from templates,or you cancreateyour own.
- Database UpgradeAssistant
- Guides you through the upgrade of your existing database to a new OracleDatabase
release.
- Net Configuration Assistant(NETCA)
- A utility that enables you to configurelistenersand namingmethods,which are
critical components ofthe OracleDatabasenetwork.
SQL*Plus
- A3-tier interactive and batch query tool with acommand-line user interface, aWindows GUI and /SQL*Plus web-baseduser interface; installed with every oracle database.
- SQL*Plus Instant Client is astand-alone command-lineinterface does not require its own Oracle databaseinstallation.
- Enablesto enter andexecute following commands:
- Generatereports; output to text, screenor HTMLfile.
- Examine tables andobjects
- Develop and run batchscripts
- Perform databaseadministration http://host.domain:5560/isqlplus/
Oracle Installation
Install the Oracle DatabaseServer
1.Logon asamember of the Administrators group for the computer on which you want to install Oracle components. If you are installing on a Primary Domain Controller (PDC)or aBackupDomain Controller (BDC),log on asamember of the Domain Administrators group. 2.Insert Oracle Databaseinstallation media and navigateto the database\directory. Alternatively, navigate to the directory where you downloaded or copied theinstallation files. 3.Double-click setup.exe to start Oracle UniversalInstaller.
- Follow these guidelines to complete the installation:
Install the Oracle DatabaseServer
- When prompted for a password,follow theseguidelines. Theinstallation does not continueif the following requirements are notmet: - Password cannotexceed 30 characters. - Password cannotcontain invalid characters:! @ %^ & * ( ) + = \ | ` ~ [ { ] } ; : ' " , < >? - A user namecannot be apassword. - An empty password cannot beaccepted. - The SYSaccount passwordcannot be change_on_install. (case-insensitive) - The SYSTEMaccountpasswordcannot be manager. (case-insensitive) - The SYSMANaccount passwordcannot be sysman.(case-insensitive) - The DBSNMPaccountpassword cannot be dbsnmp.(case-insensitive)
