This course covers Oracle database system architecture, administration techniques, memory optimization, and network configuration for Linux system administrators. Diagnostic tools and hands-on practices enhance learning.
Une image / un lien ci-dessous est fourni (tel quel) pour télécharger la présentation
Politique de téléchargement : le contenu du site vous est fourni TEL QUEL pour votre information et votre usage personnel ; il ne peut être vendu, concédé sous licence ni partagé sur d’autres sites sans l’accord de son auteur.Le contenu vous est fourni TEL QUEL, pour votre information et votre usage personnel uniquement.Téléchargez la présentation en cliquant sur ce lien.Lors du téléchargement, si vous ne parvenez pas à télécharger une présentation, l’éditeur a peut-être supprimé le fichier de son serveur.Pendant le téléchargement, si vous ne parvenez pas à obtenir une présentation, le fichier a peut-être été supprimé par l’éditeur.
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
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.
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)
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.
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
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
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)
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
System Global Area (SGA) Background processes Database Structures Memory structures Instance Process structures Database files Storage structures
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
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
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
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
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
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
Grid Control Versus Database Control Feature Grid Control Database Control Config. mgmt. Enterprisewide Local system Data Guard (Standby DB) Yes No
Granting EM Administrative Privileges Select Setup > Administrators from the Database Home page. Select an existing database user to assign management privileges to.
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>
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]] ;
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';
Data Dictionary: Overview Tables Indexes Views Users Schemas Procedures and so on SELECT * FROM dictionary;
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
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
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
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
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
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
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
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. #
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
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
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.
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.
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
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
