1 Introduction Copyright 2005 Oracle All rights reserved

1 Introduction Copyright © 2005, Oracle. All rights reserved.

Course Objectives After completing this course, you should be able to do the following: • Install, create, and administer Oracle Database 10 g • Configure the database for an application • Employ basic monitoring procedures • Implement a backup and recovery strategy • Move data between databases and files 1 -2 Copyright © 2005, Oracle. All rights reserved.

Suggested Schedule 1 -3 1. 2. 3. 4. Introduction Installation DB Creation Instance 13. Performance 14. Backup & Recovery Concepts 15. Backup 5. 6. 7. 8. Storage Users Schema Data & Concurrency 16. Recovery 17. Flashback 18. Moving Data 9. 10. 11. 12. Undo Security Network Proactive Maintenance Copyright © 2005, Oracle. All rights reserved.

Lesson Objectives After completing this lesson, you should be able to do the following: • Describe the course objectives • Explain the Oracle Database 10 g architecture 1 -4 Copyright © 2005, Oracle. All rights reserved.

Oracle Products and Services • • • 1 -5 Oracle databases Oracle Application Server Oracle applications Oracle Collaboration Suite Oracle Developer Suite Oracle services Copyright © 2005, Oracle. All rights reserved.

Oracle Database 10 g: “g” Stands for Grid • • Global Grid Forum (GGF) Oracle’s grid infrastructure: – Low cost – High quality of service – Easy to manage 1 -6 Storage grid Database grid Automatic Storage Management Real Application Clusters Application grid Oracle Streams Copyright © 2005, Oracle. All rights reserved. Grid control Enterprise Manager Grid Control

Oracle Database Architecture An Oracle server: • Is a database management system that provides an open, comprehensive, integrated approach to information management • Consists of an Oracle instance and an Oracle database 1 -8 Copyright © 2005, Oracle. All rights reserved.

Database Structures Memory structures Instance System Global Area (SGA) Process structures Background processes Storage structures 1 -9 Database files Copyright © 2005, Oracle. All rights reserved. DB structures - Memory - Process - Storage

Oracle Memory Structures Server process 1 PGA Server process 2 DB structures. > Memory Process Storage Background process PGA SGA 1 -10 Shared pool Streams pool Large pool Java pool Database buffer cache Redo log buffer Copyright © 2005, Oracle. All rights reserved. PGA

Process Structures Instance PGA User process • • • 1 -12 Server process DB structures Memory > Process Storage SGA Background processes User process: Is started at the time a database user requests a connection to the Oracle server Server process: Connects to the Oracle instance and is started when a user establishes a session Background processes: Are started when an Oracle instance is started Copyright © 2005, Oracle. All rights reserved.

Oracle Instance Management SGA Shared pool Streams pool Large pool Java pool Database buffer cache Redo log buffer System Monitor (SMON) Checkpoint (CKPT) 1 -13 Process Monitor (PMON) Database Writer (DBWn) Log. Writer (LGWR) Control files Data files Redo log files Copyright © 2005, Oracle. All rights reserved. Archive log files Archiver (ARCn)

Server Process and Database Buffer Cache Server process SGA Database buffer cache Buffers: • Pinned • Clean • Free or unused • Dirty DBWn Data files 1 -14 Copyright © 2005, Oracle. All rights reserved.

Physical Database Structure Control files Data files Parameter file Backup files Password file 1 -15 DB structures Memory Process > Storage . Online redo log files Archive log files Alert and trace log files Copyright © 2005, Oracle. All rights reserved.

Tablespaces and Data Files • • Tablespaces consist of one or more data files. Data files belong to only one tablespace. Data file 1 Data file 2 USERS tablespace 1 -17 Copyright © 2005, Oracle. All rights reserved.

SYSTEM and SYSAUX Tablespaces • • • 1 -18 The SYSTEM and SYSAUX tablespaces are mandatory tablespaces. They are created at the time of database creation. They must be online. The SYSTEM tablespace is used for core functionality (for example, data dictionary tables). The auxiliary SYSAUX tablespace is used for additional database components (such as the Enterprise Manager Repository). Copyright © 2005, Oracle. All rights reserved.

Segments, Extents, and Blocks • • Segments exist within a tablespace. Segments are made up of a collection of extents. Extents are a collection of data blocks. Data blocks are mapped to disk blocks. Segment 1 -19 Extents Data blocks Copyright © 2005, Oracle. All rights reserved. Disk blocks

Logical and Physical Database Structures Logical Physical Database Schema Tablespace Data file Segment Extent Oracle data block 1 -20 Copyright © 2005, Oracle. All rights reserved. OS block

Course Examples: The HR Schema REGIONS REGION_ID (PK) REGION_NAME COUNTRIES COUNTRY_ID (PK) COUNTRY_NAME REGION_ID (FK) JOB_ID (PK) JOB_TITLE MIN_SALARY MAX_SALARY LOCATIONS EMPLOYEES LOCATION_ID (PK) STREET_ADDRESS POSTAL_CODE CITY STATE_PROVINCE COUNTRY_ID (FK) DEPARTMENTS DEPARTMENT_ID (PK) DEPARTMENT_NAME MANAGER_ID LOCATION_ID (FK) 1 -22 JOBS EMPLOYEE_ID (PK) FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID (FK) SALARY COMMISION_PCT MANAGER_ID (FK) DEPARTMENT_ID (FK) Copyright © 2005, Oracle. All rights reserved. JOB_HISTORY EMPLOYEE_ID (PK) START_DATE (PK) END_DATE JOB_ID (FK) DEPARTMENT_ID (FK)

Database Architecture: Summary of Structural Components • Memory structures: – System Global Area (SGA): Database buffer cache, redo buffer, and various pools – Program Global Area (PGA) • Process structures: – User process and Server process – Background processes: SMON, PMON, DBWn, CKPT, LGWR, ARCn, and so on • Storage structures: – Logical: Database, schema, tablespace, segment, extent, and Oracle block – Physical: Files for data, parameters, redo, and OS block 1 -23 Copyright © 2005, Oracle. All rights reserved.

Summary In this lesson, you should have learned how to: • Install, create, and administer Oracle Database 10 g • Configure the database for an application • Employ basic monitoring procedures • Implement a backup and recovery strategy • Move data between databases and files 1 -24 Copyright © 2005, Oracle. All rights reserved.