1 / 252

Overview of Oracle Database Administration

Overview of Oracle Database Administration. Objectives. After completing this lesson, you should be able to do the following: Describe the structure of a relational database and the use of SQL Identify the components of an Oracle instance and database

alatham
Download Presentation

Overview of Oracle Database Administration

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. Overview of Oracle Database Administration

  2. Objectives • After completing this lesson, you should be able to do the following: • Describe the structure of a relational database and the use of SQL • Identify the components of an Oracle instance and database • Describe the basic tasks that an Oracle DBA performs • Define the tools that are used to administer an Oracle database

  3. Relational Databases: Introduction • A relational database is a collection of relations or two-dimensional tables. Oracle Database server Table name: DEPARTMENTS Table name: EMPLOYEES … …

  4. Relational Database Terminology 2. Primary key 3. Column 4. Foreign key 5. Field 6. Null value 1. Row

  5. Relating Multiple Tables • Each row of data in a table is uniquely identified by a primary key (PK). • You can logically relate data from multiple tables by using a foreign key (FK). Table Name: DEPARTMENTS Table Name: EMPLOYEES … Primary key Foreign key Primary key

  6. What Is SQL? • Structured Query Language (SQL) is a set of statements that are used to access data in the Oracle database. • SQL provides statements for a variety of tasks, including: • Querying data • Inserting, updating, and deleting rows in a table • Creating, replacing, altering, and dropping objects • Controlling access to the database and its objects • Guaranteeing database consistency and integrity

  7. SELECT INSERT UPDATE DELETE MERGE CREATE ALTER DROP RENAME TRUNCATE COMMIT ROLLBACK SAVEPOINT GRANT REVOKE SQL Statements Data retrieval Data manipulation language (DML) Data definition language (DDL) Transaction control Data control language (DCL)

  8. Oracle Instance and Database: Overview SGA Shared pool Large pool Database buffer cache Java pool Redo log buffer System Monitor SMON Process Monitor PMON Database Writer DBW0 Log Writer LGWR Check point CKPT Archiver ARC0

  9. Using SQL to Access the Database SQL statement is entered. SELECT department_name FROM departments; The statement is sent to the Oracle Database server. Oracle database Data is retrieved and returned to the user.

  10. Common Tasks of an Oracle Database Administrator • Installing and updating Oracle Database software • Creating databases • Performing upgrades of the database and software • Starting up and shutting down the instance • Managing the database’s storage structures • Managing users and security • Managing schema objects • Backing up and effecting recovery when necessary • Proactively monitoring the database and taking preventive or corrective action as required • Monitoring and tuning performance • Diagnosing and reporting errors to Oracle Support Services

  11. Tools Used to Administer an Oracle Database • Oracle Universal Installer (OUI) • Database Configuration Assistant (DBCA) • Database Upgrade Assistant (DBUA) • Oracle Net Manager • Oracle Enterprise Manager • SQL*Plus • Recovery Manager (RMAN) • Data Pump • SQL*Loader

  12. Summary • In this lesson, you should have learned how to: • Describe the structure of a relational database and the use of SQL • Identify the components of an Oracle instance and database • Describe the basic tasks that an Oracle DBA performs • Define the tools that are used to administer an Oracle database

  13. Installing Oracle Database andCreating the Database

  14. Objectives • After completing this lesson, you should be able to do the following: • Install the Oracle Database software • Create an Oracle database

  15. Using Oracle Universal Installer • Oracle Universal Installer (OUI) enables you to: • View the Oracle software that is installed on your machine • Install new Oracle software • Remove the Oracle software that you no longer intend to use • OUI provides online help.

  16. Installation Prerequisites • Before installation, OUI checks for the following: • Availability of a minimum amount of physical memory: • 512 MB on Windows or Linux • 1 GB on UNIX and other operating systems • Availability of sufficient paging space (swap space) • Appropriate operating system service packs or patches • Appropriate file system format

  17. Setting Environment Variables • ORACLE_BASE: Specifies the base of the Oracle directory structure for Optimal Flexible Architecture (OFA) • ORACLE_HOME: Specifies the directory containing the Oracle software • ORACLE_SID: Is the initial instance name (orcl by default) • LD_LIBRARY_PATH: Specifies the location of shared object libraries (UNIX)

  18. Installing the Oracle Software

  19. Database Configuration Options

  20. Configuration Assistants

  21. Database Creation

  22. Completing Your Installation

  23. Using the Database Configuration Assistant (DBCA)

  24. Using the DBCA to Create a Database

  25. Performing Other Actions with the DBCA

  26. Using the DBCA to Manage Templates

  27. Summary • In this lesson, you should have learned how to: • Use Oracle Universal Installer to install the Oracle software • Use the Database Configuration Assistant to create an Oracle database

  28. Practice 2: Overview • This practice covers the following topics: • Installing the Oracle software by using OUI • Creating a database

  29. Using Oracle Enterprise Manager Database Control and SQL*Plus

  30. Objectives • After completing this lesson, you should be able to do the following: • Start the Enterprise Manager dbconsole process • Access Enterprise Manager Database Control • Navigate in Enterprise Manager Database Control • Grant Enterprise Manager administrative privileges • Use SQL*Plus to access your database

  31. Administering the Database by Using Enterprise Manager: Overview • Oracle Enterprise Manager Database Control provides a wide variety of management capabilities, including: • Database monitoring • Administration • Maintenance features

  32. Management Framework Grid Control Management server Management repository Managed targets Agent Agent Agent Database Control Database OS/third-party application Application server Application Server Control

  33. Starting the Enterprise Manager Console Process $ emctl start dbconsole

  34. Accessing Enterprise Manager Database Control https://host name:1158/em

  35. Enterprise Manager:Database Control Login

  36. Property pages Enterprise Manager: Database Home Page

  37. Enterprise Manager Navigation • Property pages: Organize management tasks • Drill-down links: Provide increasing levels of detail • Suggested related links: Provide more information

  38. Granting Enterprise ManagerAdministrative Privileges Select Setup > Administrators on the Database home page. Select an existing database user to whom to assign management privileges.

  39. Configuring Enterprise ManagerSettings and Preferences • You can configure the following settings and preferences in Enterprise Manager to manage the database: • Notifications • Blackouts • Preferred credentials

  40. Defining Blackout Time Periods • Blackouts enable you to suspend monitoring on one or more targets to perform maintenance operations. • Define blackout time periods to prevent alert notifications when shutting down your database for maintenance.

  41. Setting Preferred Credentials • Configure host and database login information so that it is automatically supplied for jobs.

  42. Using SQL*Plus to Access Your Database • SQL*Plus provides a command-line interface to your database, enabling you to: • Perform database management operations • Execute SQL commands to query, insert, update, and delete data in your database • SQL*Plus can be used interactively or in batch mode.

  43. Using SQL*Plus SQL> connect / as sysdba Connected. SQL> SELECT * FROM hr.regions; REGION_ID REGION_NAME ---------- ------------------------- 1 Europe 2 Americas 3 Asia 4 Middle East and Africa SQL>

  44. Summary • In this lesson, you should have learned how to: • Start the Enterprise Manager dbconsole process • Access Enterprise Manager Database Control • Navigate in Enterprise Manager Database Control • Grant Enterprise Manager administrative privileges • Use SQL*Plus and iSQL*Plus to access your database

  45. Practice 3: Overview • This practice covers the following topics: • Using the emctl utility to check the status of the dbconsole process • Navigating in Enterprise Manager Database Control

  46. Configuring the Oracle Network Environment

  47. Objectives • After completing this lesson, you should be able to do the following: • Describe the Oracle Network configuration • Use the Enterprise Manager Net Services Administration page for Oracle Network configuration • Use the Listener Control utility • Configure a client to access an Oracle database

  48. Network Configuration: Overview Client/server application connection Application Oracle Net RDBMS Oracle Net TCP/IP network Client Database server

  49. Network Configuration: Overview Web client connections through an application Web server TCP/IP network Application Oracle Net RDBMS Oracle Net HTTP protocol TCP/IP network Application Web server Client with Web browser Database server

  50. Tools for Configuring and Managing the Oracle Network • Oracle Enterprise Manager Net Services Administration page • Oracle Net Manager • Oracle Net Configuration Assistant (launched by Oracle Universal Installer)

More Related