Oracle Database Architecture

1. Oracle Database Architecture

2. Objectives • After completing this lesson, you should be able to: • Identify the difference between the Oracle database and the Oracle instance • Define the Oracle Database System Global Area (SGA) • List the main database background processes and their functions • Log in to Enterprise Manager and view the database administration pages • Log in to the database by using SQL*Plus and issue a simple SQL statement

3. Overview • This course is designed for Linux system administrators who manage servers that host an Oracle database. • Working knowledge of Linux and some conceptual understanding of Oracle Database are assumed. • The concepts presented in the lectures are supplemented and reinforced with hands-on practices.

4. Course Objectives • In this course, you: • Diagnose problems with real and virtual memory • Explain how to maximize memory usage for an Oracle database on a 32-bit machine • Configure and use a yum repository based on the Unbreakable Linux Network • Configure Netdump • Diagnose memory consuming database sessions • Create and manage file systems • Collect and analyze TCP data • Install and use Oracle Cluster File System version 2 (OCFS2)

5. Assigned Responsibilities DBA System administrator

6. The Classroom Computer Setup • The attributes of the classroom are the following: • The operating system is Enterprise Linux 4 Update 4. • Oracle Database 10.2.0.3 is installed, with a database and sample schemas. • Oracle Automatic Storage Management (ASM) is installed, and the database data files are stored there. • The computers have at least 1 GB of RAM. • The computers are networked. • All the work you do is on the computers inthe classroom; there are no back-endservers.

7. 1: Introduction 2: Oracle Database Users and Files 3: Oracle Instance Processes 4: Oracle and Linux Memory Typical Schedule 5: Creating a ULN Repository 6: Diagnosing the System 7: Diagnosing Processes 8: Managing Database Storage 9: Implementing OCFS2 10: Configuring and Diagnosing the Network 11: Configuring Netdump 12: Course Summary

8. Oracle Software on the Database Server • The following Oracle products may sometimes be installed on the same machine as Oracle Database software: • Real Application Clusters (RAC) • OCFS2 • Clusterware • Automatic Storage Management • Oracle Collaboration Suite • Enterprise Manager

9. Optional Security Features Encrypted Communication (ASO) Object-Level Access Control (Database Vault) Row-Level Security (OLS or VPD) Column Encryption (TDE) Backup File Encryption (RMAN and OSB) Secure Audit Logs (Audit Vault)

10. Oracle Database Architecture • An Oracle database 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

11. System Global Area (SGA) Background processes Database Structures Memory structures Instance Process structures Database files Storage structures

12. Oracle Memory Structures Server process 1 Server process 2 Background process PGA PGA PGA SGA Streams pool Large pool Shared pool Database buffer cache Redo log buffer Java pool

13. SGA Process Structures • 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 Instance PGA Server process User process Background processes

14. Oracle Instance Management SGA System Monitor (SMON) Process Monitor (PMON) Database Writer (DBWn) LogWriter (LGWR) Archive log files Checkpoint (CKPT) Archiver (ARCn) Control files Data files Redo log files

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

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

17. Ways to Manage Your Database Grid Control Database Control Management Repository

18. Grid Control Versus Database Control Feature Grid Control Database Control Installation Separate DB 10g Management Repository Centralized In local DB 10g Management Agent One per host in separate home One per DB 10g Home Target Type Many Single DB 10g Number of DBs Many One EM job System Yes Yes Cluster DB (RAC) Yes Yes

19. Grid Control Versus Database Control Feature Grid Control Database Control Config. mgmt. Enterprisewide Local system Data Guard (Standby DB) Yes No

20. Starting the Enterprise Manager dbconsole $ emctl start dbconsole

21. Accessing Enterprise Manager Database Control http://host name:1158/em

22. Enterprise Manager: Database Control Login

23. Property pages Enterprise Manager: Database Home Page

24. Granting EM Administrative Privileges Select Setup > Administrators from the Database Home page. Select an existing database user to assign management privileges to.

25. Using SQL*Plus • SQL*Plus provides a command-line interface to your database. • SQL*Plus can be used interactively or in batch mode. SQL> connect hr/hr Connected. SQL> SELECT * FROM regions; REGION_ID REGION_NAME ---------- ------------------------- 1 Europe 2 Americas 3 Asia 4 Middle East and Africa SQL>

26. Writing SQL Statements • SQL statements are not case sensitive. • SQL statements can be on one or more lines. • Keywords cannot be abbreviated or splitacross lines. • Clauses are usually placed on separate lines. SELECT *|{[DISTINCT] column|expression [alias],...} FROM table [WHERE condition(s)] [ORDER BY {column, expr, alias} [ASC|DESC]] ;

27. SELECT Statements SELECT employee_id, last_name, job_id, department_id FROM employees WHERE department_id = 90; SELECT e.employee_id, e.last_name, e.department_id, d.location_id FROM employees e, departments d WHERE e.department_id = d.department_id ORDER BY e.last_name; SELECT terminal, program FROM v$process WHERE UPPER(username) = 'HRAPP';

28. Data Dictionary: Overview Tables Indexes Views Users Schemas Procedures and so on SELECT * FROM dictionary;

29. Data Dictionary Views

30. Data Dictionary: Usage Examples SELECT table_name, tablespace_name FROM user_tables; a SELECT sequence_name, min_value, max_value, increment_by FROM all_sequences WHERE sequence_owner IN ('MDSYS','XDB'); b SELECT USERNAME, ACCOUNT_STATUS FROM dba_users WHERE ACCOUNT_STATUS = 'OPEN'; c DESCRIBE dba_indexes; d

31. Summary • In this lesson, you should have learned how to: • Identify the difference between the Oracle database and the Oracle instance • Define the Oracle Database System Global Area (SGA) • List the main database background processes and their functions • Log in to Enterprise Manager and view the database administration pages • Log in to the database by using SQL*Plus and issue a simple SQL statement

32. Practice 1: Overview • This practice covers the following topics: • Starting the Oracle listener • Starting the ASM and ORCL database instances • Starting Enterprise Manager Database Control • Navigating in Enterprise Manager Database Control

33. Oracle Database Users and Files

34. Objectives • After completing this lesson, you should be able to: • List the required users and groups for an Oracle database • List the major files and locations of an Oracle database • Navigate the directory structure where Oracle database is installed • Locate and view the database alert log • Locate and view the trace and log files

35. Identifying Necessary Groups • Group • Description • Common Name • Oracle Inventory • Identifies the owner of the Oracle software • oinstall • OSDBA • Identifies OS accounts that have database administration privileges (SYSDBA) • dba • OSOPER • Identifies OS accounts that have limited database administration privileges (SYSOPER) • oper

36. OS User Accounts • The Oracle software installation requires: • An installation owner (typically named oracle) • A low-privileged user (nobody) • For database operation: • Operators are members of the OSOPER group • DBAs are members of the OSDBA group • Ordinary database users: • May have OS accounts • May be authenticated by OS • In an N-tier environment, do not need OS accounts

37. Creating the Oracle Software Owner • The user that owns the Oracle software: • Must be created • Has the Oracle Inventory group as its primary group • Must be a member of an OSDBA group • Is commonly named oracle

38. Creating the Oracle Software Owner 1 # useradd oracle 2 # usermod -g oinstall -G dba,oper,oracle oracle 3 # passwd oracle Changing password for user oracle. New UNIX password: Retype new UNIX password: passwd: all authentication tokens updated successfully. #

39. Physical Database Structure • Data files • Online redo log files Control files • Parameter file • Backup files • Archive log files • Alert and trace log files • Password file • Audit log files

40. Initialization Parameter Files spfileorcl.ora

41. Initialization Parameters Advanced Basic CONTROL_FILES DB_BLOCK_SIZE PROCESSES UNDO_MANAGEMENT … DB_CACHE_SIZE SHARED_POOL_SIZE LARGE_POOL_SIZE LOG_BUFFER …

42. Viewing Initialization Parameters • To show the current parameter values in SQL*Plus: • show parameters [<parameter_name>] SQL> show parameters Show all parameters Show parameters containing a string SQL> show parameters dump NAME TYPE VALUE ---------------------- -------- ------------------------------ background_core_dump string partial background_dump_dest string /u01/app/oracle/admin/orcl/bdump core_dump_dest string /u01/app/oracle/admin/orcl/cdump max_dump_file_size string UNLIMITED shadow_core_dump string partial user_dump_dest string /u01/app/oracle/admin/orcl/udump

43. Viewing the Alert Log • The alert log: • Is a text file that contains a record of events occurring in the database instance, including: • Database startup and shutdown • Errors • Security-related events • Grows without limit • Is in the directory defined by the BACKGROUND_DUMP_DEST initialization parameter

44. Database File Locations • The database files may be anywhere. Find the files by: • Enforcing a naming convention • Querying the database ASM file SQL> SELECT NAME from V$DATAFILE; NAME ------------------------------------- +DF/orcl/datafile/users.259.615341777 /u01/oradata/orcl/hrapp01.dbf /tmp/sales01.dbf /dev/sdl … OS file system Data file at risk Raw device Note: V$TEMPFILE, V$LOGFILE, and V$CONTROLFILE are also available.

45. Location of Other Database-Related Files • Database-related file default locations: • Oracle inventory directory defaults to $ORACLE_BASE/oraInventory. • /etc/oraInst.loc points to the inventory directory. • /etc/oratab defines instance properties. • /user/local/bin holds scripts for setting user environment variables.

46. Default Installation Directories • These two directories define the locations of installation files: • The directory for Oracle software installations: • The directory for a specific version of Oracle Database software: • You could have multiple ORACLE_HOME directories to support multiple versions of the Oracle software. $ echo $ORACLE_BASE /u01/app/oracle $ echo $ORACLE_HOME /u01/app/oracle/product/10.2.0/db_1

47. Default Permissions • Default installation configuration • Server accounts limited to: • DBA • System Administrator • Database access through clients or middle tier • Oracle server processes: • Run as the SETUID of the Oracle software owner • Access database files (permission 640) • Relax permissions only when: • Users have server OS accounts • Needed for backward compatibility

48. Setting Attributes for a New ORACLE_HOME $ chmod –-reference reffile file $ chown –-reference reffile file $ chgrp –-reference reffile file

49. Deciphering Oracle Executable Histories $ relink utilities ... mv -f /u01/.../bin/sqlplus /u01/…/bin/sqlplusO mv -f /u01/.../lib/sqlplus /u01/.../bin/sqlplus Original New $ cd $ORACLE_HOME/bin $ stat sqlplus* File: `sqlplus' Size: 8884 Blocks: 24 IO Block: 4096 regular file Device: 305h/773d Inode: 1936920 Links: 1 Access: (0751/-rwxr-x--x) Uid: ( 501/ oracle) Gid: ( 502/oinstall) Access: 2007-04-28 09:17:11.000000000 -0500 Modify: 2007-02-18 17:36:57.000000000 -0500 Change: 2007-02-18 17:36:58.000000000 -0500 File: `sqlplusO' Size: 8851 Blocks: 24 IO Block: 4096 regular file Device: 305h/773d Inode: 1935085 Links: 1 Access: (0751/-rwxr-x--x) Uid: ( 501/ oracle) Gid: ( 502/oinstall) Access: 2007-02-18 17:37:41.000000000 -0500 Modify: 2007-01-02 14:27:25.000000000 -0500 Change: 2007-02-18 17:36:58.000000000 -0500

50. Determining File Types $ file spfileorcl.ora spfileorcl.ora: data $ file help.txt help.txt: ASCII text $ file spfile.ora.Z spfile.ora.Z: compress'd data 16 bits $ file -L sql sql: ELF 32-bit LSB executable, Intel 80386, version 1 (SYSV), for GNU/Linux 2.2.5, dynamically linked (uses shared libs), not stripped