1 / 26

Week 1 Lecture 2 Overview of Database Administrator (DBA) Tools

Week 1 Lecture 2 Overview of Database Administrator (DBA) Tools. Objectives. Identify the main DBA tools in the Oracle9 i software suite Configure Oracle Net to connect to the database List the memory and background process components of the database instance

decima
Download Presentation

Week 1 Lecture 2 Overview of Database Administrator (DBA) Tools

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. Week 1 Lecture 2 Overview of Database Administrator (DBA) Tools

  2. Objectives • Identify the main DBA tools in the Oracle9i software suite • Configure Oracle Net to connect to the database • List the memory and background process components of the database instance • Start using the Enterprise Manager

  3. Overview of DBA tools Tools can be started from the console or as stand-alone components. Important tools to know: • Database Configuration Assistant • Enterprise Manager Console • Log Miner • Net Manager and Net Configuration Assistant • SQL*Plus and SQL*Plus Worksheet

  4. Overview of DBA tools Important operating system commands:

  5. Configuring Oracle Net: Architecture Oracle Net must be configured on both the client and the server when using Oracle tools on the client side.

  6. Configuring Oracle Net: Architecture Three methods of connecting: Oracle Net JDBC thin driver Bequeath protocol

  7. Configuring Oracle Net: Net Manager • Use Net Manager to configure Oracle Net • A service name defines the database’s name, location, and listening port • Net Manager contains a wizard to configure a new service name • The file tnsnames.ora stores Oracle Net configuration settings

  8. Oracle Memory and Background Processes: Connecting to a Shared Server Red lines trace the path between the client and the instance

  9. Oracle Memory and Background Processes:Connecting to a Dedicated Server Red lines trace the path between the client and the instance

  10. Oracle Memory : PGA & SGA The SGA is allocated when the instance starts up The PGA has private areas for each application

  11. Oracle Memory : SGA Components The shared pool stores parsed SQL commands for possible reuse The buffer cache stores data blocks read from the datafiles

  12. Oracle Memory: SGA Components The redo log buffer stores all changes to data before the changes are written to the datafiles The cursor pool is optional and provides extra memory for program cursors The large pool is optional and adds more memory for better response time

  13. Background Processes: PMON & SMON PMON (Process Monitor) cleans up remaining resources after the server and user processes end SMON (System Monitor) handles recovery if needed, cleans up old temporary tables, and restores space

  14. Background Processes: DBWn & CKPT DBWn (Database Writer) writes modified buffers to the datafiles CKPT (Checkpoint Process) signals the DBWn process and assigns SCN

  15. Background Processes: ARCn & LGWR ARCn (Archiver) copies redo log files to archive log files LGWR (Log Writer) writes redo log buffers to the redo log files

  16. Background Processes: RECO & LMS RECO (Recoverer Process) fixes errors if changes across distributed databases fail LMS (Lock Manager Service) handles updates that affect data in multiple clustered databases

  17. Introducing Enterprise Manager • Central clearinghouse of DBA tools • Windows-like interface with little or no SQL coding required • Centered around the Enterprise Manager console • Optionally, uses the Enterprise Management Server

  18. Enterprise Manager console Important features

  19. Enterprise Manager: Instance Manager Instance Manager handles these tasks (and more): • Setting initialization parameters • Starting and shutting down the database • Monitoring and adjusting memory usage • Locating redo log files

  20. Enterprise Manager: Schema Manager Schema Manager handles these tasks (and more): • Adding and removing columns on tables or views • Creating new tables, indexes, views, and object types • Adding constraints to tables • Viewing statistics on tables

  21. Enterprise Manager: Security Manager Security Manager handles these tasks (and more): • Creating new users and roles • Changing user passwords • Assigning and revoking user privileges • Adjusting user storage quotas

  22. Enterprise Manager: Storage Manager Storage Manager handles these tasks (and more): • Creating new tablespaces • Adjusting size of data files • Taking tablespaces offline • Monitoring storage usage

  23. Lecture Summary • Oracle’s DBA tools are available inside the Enterprise Manager console or stand-alone • Oracle Net is the most common way to communicate between client and database server • Other methods of communicating are JDBC driver and Bequeath protocol • Net Manager provides a tool for configuring Oracle Net • The tnsnames.ora file stores Oracle Net configuration information

  24. Lecture Summary • Database servers are either in shared server mode or dedicated mode • User processes and server processes handle connections and user sessions • Oracle memory is made up of the PGA and the SGA • The PGA has private areas for each application • The SGA is part of the database instance

  25. Lecture Summary • The SGA stores data blocks read from the data files and stores changed data blocks • The background processes run the database functions such as reading and writing datafiles • The Enterprise Manager console is the center of Oracle’s DBA tool set • The console can run stand-alone or as part of the Enterprise Management Server

  26. Lecture Summary • The four main tools within the Enterprise Management console are: • Instance Manager • Schema Manager • Security Manager • Storage Manager

More Related