Tutorial HK: Configuring Oracle on Linux For Peak Performance

1
Tutorial HKConfiguring Oracle on Linux For Peak
Performance

• Roger Schrag
• Database Specialists, Inc.
• www.dbspecialists.com
• LinuxWorld Conference Expo
• August 10, 1999

2
Todays Presentation

• Introduction to Oracle technology
• Installing Oracle
• Managing your database
• Optimizing your database
• Where to get more information

3
About The Author

• 10 years experience with Oracle technology
• Lots of Oracle DBA experience on Unix platforms
• Mostly e-business and ERP systems
• Presenter at Oracle user groups and conferences
• Author of various white papers
• Linux believer

4
Todays Goals

• Get a basic understanding of How Oracle Works
• Learn the proper way to install Oracle
• Gain access to accurate information

5
Oracle Quick Start

• Learn basic Oracle concepts
• Install Oracle software
• Create a default database
• Make the default database usable
• Create application schema
• Develop application
• Big picture issues (backups, capacity planning)

6
An Introduction to Oracle Technology

• Platform independence
• Database terminology
• Important files
• Oracle process architecture
• Environment variables
• Optimal flexible architecture
• Interacting with a database

7
Platform Independence

• Abstraction of platform-specific issues
• Generic and platform-specific documentation
• Oracle skills transfer well from one platform to
  another

8
Database Terminology

• Key fundamentals
• Learn them correctly
• Many Oracle-isms

Terminology
9
DBA

• Stands for database administrator
• Involved in logical design of a database
• Performs the physical design and implementation
• Assists developers with difficult database issues
• Responsible for ensuring good performance, proper
  security, as well as backup and recovery
  strategies

Terminology
10
Database

• A collection of operating system files that make
  up one physical data store or node
• Many different types of data, each with its own
  attributes, can be stored in one database

Terminology
11
Database Name

• Every Oracle database has a name
• Eight characters or less
• Default global domain name is .WORLD
• Best to give each database a unique name and use
  one common domain

Terminology
12
Instance

• A set of operating system processes and shared
  memory structures which allow an application to
  access data in a database

Terminology
13
Instance Name

• Also known as SID (System Identifier)
• Applications identify which instance they wish to
  access by name
• The convention is to give an instance the same
  name as the database it accesses

Terminology
14
SGA

• Stands for System Global Area
• A collection of shared memory structures created
  by an Oracle instance
• Each process of the instance attaches itself to
  the SGA upon startup

Terminology
15
Starting the Database

• Opening a database so that applications may
  access it
• Done by starting up an instance and mounting it
  to the database

Terminology
16
Net8 or SQLNET

• Networking infrastructure that allows
  applications to access databases on remote
  servers
• Was called SQLNet before Oracle8
• Renamed to Net8

Terminology
17
Client/Server

• Oracle internally uses a client/server
  architecture
• The application accessing Oracle is seen as the
  client, the Oracle server process is a server

Terminology
18
Username

• Each user in an Oracle database has a unique name
  up to 30 characters in length
• Separate usernames in the database do not need to
  map to separate real-life users

Terminology
19
Schema

• A separate namespace for database objects such as
  tables, indexes and stored procedures
• Every database object resides in one schema
• Each database user has one schema with the same
  name as their username
• Username schema

Terminology
20
Data Dictionary

• Repository of information indicating all of the
  users defined in the database and all of the
  objects they own
• A set of database tables with names like user
  and tab

Terminology
21
SYS and SYSTEM

• SYS user owns all of the internal structures that
  make up the data dictionary for the database
• SYS user has all privileges on the database
• SYSTEM user has DBA privileges also

Terminology
22
Control Files, Data Files, Redo Logs and
Parameter Files

• The physical files that make up an Oracle database

Terminology
23
Tablespace

• A named collection of one or more physical files
  used for storing database objects
• Mask certain implementation details from
  application developers

Terminology
24
Extent

• One contiguous chunk of physical storage within a
  tablespace
• Can range in size from 2 Kb to 2 Gb, but must be
  contiguous

Terminology
25
Segment

• A collection of extents that belong to one object
• Physical storage used to hold the data for an
  object
• Most hold the contents of a table or index, but
  there are other types of segments as well

Terminology
26
Rollback Segment

• Stores undo information for a transaction that
  is still in progress
• Allows Oracle to back out the transaction if the
  application requests a rollback
• Enables one session to update data while another
  is querying the same data

Terminology
27
Temporary Segment

• Holds a partial result set when too much data is
  involved to complete the operation in memory
• Commonly used when sorting data for ordering,
  grouping, or building an index

Terminology
28
Identifier

• Most are limited to 30 bytes in length and are
  case-insensitive
• Unless quoted, identifiers must start with a
  letter and may include letters, digits and
  certain special characters like _, , and

Terminology
29
The Files That Control an Oracle Instance

• Parameter file
• Password file

30
The Files That Make Up a Database

• Control files
• Data files
• Online redo logs
• Archived redo logs

31
The Oracle Process Architecture

• Database
• Instance
• Net8
• Applications

32
The Oracle Instance

• Shared global area (SGA)
• Daemon processes
• Server processes

33
Oracle Daemons
34
Oracle Process Architecture
35
Making an Oracle Database Available

• 1. Start an instance
• 2. Mount the database
• 3. Open the database

36
Accessing a Local Database

• 1. Application forks an Oracle server process
• 2. Environment variable indicates which local
  database to access
• 3. setuid bit allows server process to attach to
  SGA, read data files
• 4. Application communicates with server process
  via IPC

37
Accessing a Remote Database

• 1. Net8 client resolves alias to hostname, port,
  and instance name (in the case of TCP/IP
  protocol other protocols supported)
• 2. Net8 client sends a request to the Net8
  listener on the database server
• 3. Net8 listener on database server forks an
  Oracle server process
• 4. Application communicates with server process
  using any supported protocol

38
Environment Variables

• ORACLE_HOME
• LD_LIBRARY_PATH
• PATH
• ORACLE_SID
• TWO_TASK
• NLS_LANG
• ORA_NLS33
• ORACLE_BASE
• DBA

39
ORACLE_HOME

• Location of Oracle software installation
• For example /u01/app/oracle/product/8.0.5
• Always required

40
LD_LIBRARY_PATH

• Location of Oracle shared libraries
• Must include ORACLE_HOME/lib
• For example /u01/app/oracle/product/8.0.5/lib
• Always required

41
PATH

• Location of Oracle executables
• Must include ORACLE_HOME/bin
• For example /u01/app/oracle/product/8.0.5/bin
• Always required

42
ORACLE_SID

• Name of local Oracle instance to access
• For example MYDB
• Overridden if application specifies Net8 alias or
  if TWO_TASK is set
• Required if application does not specify Net8
  alias and TWO_TASK is not set

43
TWO_TASK

• Net8 alias for local or remote Oracle instance to
  access
• For example MYDB
• Overridden if application specifies Net8 alias
  explicitly
• Optional

44
NLS_LANG

• Language and character set of client application
• Format is language_territory.CHARSETNAME
• For example american_america.US7ASCII
• See platform-specific installation guide for a
  list of supported settings
• Required when default character set is not being
  used

45
ORA_NLS33

• Location of Oracle client NLS character set data
• Typically ORACLE_HOME/ocommon/nls/admin/data
• For example /u01/app/oracle/product/8.0.5/ocommon
  /nls/admin/data
• Required when default character set is not being
  used

46
ORACLE_BASE

• Location of OFA directory structure
• For example /u01/app/oracle
• An optional convenience

47
DBA

• Location of instance-related administrative files
• For example /u01/app/oracle/admin
• An optional convenience

48
Optimal Flexible Architecture

• A set of best practices for installing Oracle
• Developed by Oracle Consulting and refined over
  the years
• Observed reasonably well by the Oracle Installer
• See platform-specific administrators reference
  for complete OFA specification

49
OFA Guidelines

• Assigning and naming mount points
• Directory hierarchies
• Naming conventions for all files
• Naming conventions for tablespaces
• Policies for assigning segments to tablespaces

50
OFA Benefits

• A standardized methodology
• A good naming convention
• Files organized in an easy-to-find manner
• Support for multiple databases or Oracle releases
  on one server
• Separation of Oracle software and database files
• Ability to distribute I/O across many physical
  devices

51
Key Directories in anOFA-Compliant Installation

• ORACLE_BASE
• ORACLE_BASE/product/ltversiongt
• ORACLE_BASE/admin/ORACLE_SID
• pfile
• bdump
• cdump
• udump
• create
• ltmount pointgt/oradata/ltdbnamegt

52
Accessing an Oracle Database(as a DBA)

• SQLPlus
• Server Manager
• Enterprise Manager

53
Installing Oracle

• Prepare the server
• Install the Oracle software
• Create a database
• Complete the server configuration

54
Our Installation Environment

• Oracle 8.0.5 Standard Edition
• Red Hat Linux 5.2

You are welcome to use other versions of Oracle
or other distributions of Linux, but you may need
to deviate from these steps
55
Oracle 8i

• Just became available in late July
• Installs very differently than Oracle 8.0.5
• Released too late to include in this presentation

56
Prepare the Server

• 1. Meet the software requirements
• 2. Meet the hardware requirements
• 3. Meet the kernel parameter requirements
• 4. Create a Linux group for DBAs
• 5. Create a software owner Linux account
• 6. Create mount points
• 7. Choose a local bin directory

57
Prepare the Server (continued)

• 8. Mount the CD ROM
• 9. Create the /etc/oratab file

58
Install the Oracle Software

• 1. Edit software owners login script
• 2. Log out and back in to set environment
• 3. Verify that TCP/IP is working
• 4. Ensure there is enough storage space available

59
Install the Oracle Software (continued)

• 5. Run the Oracle Installer to install software
• a. Choose a custom install
• b. Choose to install software without creating a
  database
• c. Choose a language for database messages
• d. Choose the products to install
• e. Choose the DBA and osoper groups

60
Install the Oracle Software (continued)

• 6. Add enhancements to the oraenv script
• 7. Add enhancements to the dbshut script
• 8. Perform certain steps as the root user
• 9. Double check file permissions

61
Create a Database

• 1. Choose a database block size
• 2. Tweak an Installer script to get the desired
  block size

62
Create a Database (continued)

• 3. Run the Oracle Installer to create a database
• a. Choose a custom install
• b. Choose to create database objects
• c. Choose relevant products for database
  creation
• d. Choose to create a filesystem-based database
• e. Specify the mount point for the database
• f. Choose the character sets for the database
• g. Choose database passwords
• h. Choose whether to load SQLPlus help and
  demo

63
Create a Database (continued)

• 4. Configure the Net8 listener
• 5. Prepare a tnsnames.ora file
• 6. Improve the parameter file
• 7. Relocate data files and redo logs to balance
  I/O
• 8. Configure the temporary tablespace
• 9. Configure rollback segments
• 10.Create application tablespaces
• 11.Create application schemas and roles

64
Complete the Server Configuration

• 1. Update the /etc/oratab file
• 2. Remove hardcoding from software owners login
  script
• 3. Create separate Linux accounts for database
  users
• 4. Configure automatic database start on server
  reboot

65
Managing Your Database

• Starting and stopping
• Connectivity and troubleshooting
• Schemas and users
• Tablespaces and segments
• Fault tolerance and backups

66
Opening and Closing a Database

• Invoking Server Manager
• Authentication
• STARTUP
• SHUTDOWN

67
Starting and Stopping theNet8 Listener

• Invoking the listener control tool
• START
• STOP
• RELOAD

68
Logs and Trace Files

• Instance alert log
• Trace files for daemons
• Trace files for server processes
• Net8 logs

69
Net8 Connectivity Problems

• TNS could not resolve service name
• TNS unable to connect to destination
• TNS no listener
• ORACLE not available
• TNS listener could not resolve SID given in
  connect descriptor

70
Schemas and Users

• Application owner schemas
• Contain application schema objects
• Not used by end users
• Individual Oracle users
• Database authenticates end users
• Synonyms reference application objects
• Privileges to access application objects
• Commonly used in client/server systems

71
Schemas and Users (continued)

• Generic Oracle users
• Application authenticates end users
• Synonyms reference application objects
• Privileges to access application objects
• Commonly used in web-based systems

72
Tablespace Functions
73
Assigning Application Segmentsto Tablespaces

• Use separate tablespaces for each application
• Place tables and indexes in separate tablespaces
• Separate segments that grow or shrink rapidly
  from more static ones
• Separate segments that are created and dropped
  frequently from longer lived ones
• Give large segments their own tablespace

74
Segment Storage Parameters
75
Choosing Storage Parameters For Segments to
Maximize Performance and Minimize Fragmentation

• Set the default storage clause for each
  tablespace as follows
• INITIAL 128k, 4m, or 128m
• NEXT Same as INITIAL
• MINEXTENTS 1
• MAXEXTENTS 1024
• PCTINCREASE 0

76
Choosing Storage Parameters For Segments to
Maximize Performance and Minimize Fragmentation
(continued)

• When creating segments, assign them to a
  tablespace but do note specify storage parameters

Optimizing storage parameters was much more
complicated with earlier versions of Oracle, but
starting with Oracle 7.3 this task became greatly
simplified.
77
Make Your Database Fault Tolerant

• Store multiple control files on separate devices
• Store multiple members of each online redo log
  group on separate devices
• Store data files on RAID filesystems

Eliminate single points of failure wherever
possible.
78
Backing Up an Oracle Database

• Export
• Cold Backups
• Hot Backups
• Recovery Manager

79
Optimizing Your Database

• Tuning applications
• Sizing the SGA
• Balancing I/O
• Tuning sorts
• Configuring rollback segments

80
Tuning Database Applications

• Use bind variables to reduce parsing
• Tune queries so Oracle can run them efficiently
• Use tools like EXPLAIN PLAN and TKPROF
• Look inside the SGA while application is running
• Tune application logic to make the most of SQL
  and PL/SQL

Application tuning usually gives the biggest bang
for the buck when optimizing Oracle systems.
Reducing running time from 18 hours to ten
minutes by changing one line of code is not
uncommon.
81
Sizing the SGA

• Size the buffer cache
• Set the db_block_buffers parameter
• Query vsysstat to check hit ratio
• Consider checking extended LRU statistics
• Size the shared SQL area
• Set the shared_pool_size_parameter
• Consider use of bind variables and PL/SQL when
  sizing

Maximize cache hit ratios while keeping the
entire SGA in physical memory at all times.
82
Balancing I/O

• Assign segments to tablespaces carefully
• Distribute data files across physical devices
• Query vfilestat to check I/O counts
• Adjust file placement as needed

83
Tuning Sorts

• Set sort_area_size parameter
• Assign all users a temporary tablespace
• Set content type of temporary tablespace to
  TEMPORARY
• Set storage parameters on temporary tablespace

84
Configuring Rollback Segments

• Lots of small segments for OLTP
• A few large segments for batch
• Usually, need to mix the two

85
Where To Get More Information

• Oracle documentation
• Other publications
• Oracle Technology Network
• Oracle user groups
• Free web resources

86
Slide Show and Tutorial Are Available in Soft
Copy

• Visit
• http//www.dbspecialists.com/present.html
• for this presentation

87
Contact Information

• Roger Schrag
• rschrag_at_dbspecialists.com
• Database Specialists, Inc.
• 388 Market Street, Suite 400
• San Francisco, CA 94111
• 415-344-0500