by Roger Schrag
Database Specialists, Inc.
LinuxWorld Conference and Expo ’99
August 9-12, 1999
San Jose, California
HTML Conversion by Michael Edwards
Contents
- Why I Wrote This Tutorial
- My Background
An Introduction to Oracle Technology
- Platform Independence
- Database Terminology
- The Files That Control an Oracle Instance
- The Files That Make Up an Oracle Database
- The Oracle Process Architecture · Environment Variables
- Installation Best Practices: OFA
- Interacting with a Database
- Oracle Quick Start
Installing the Oracle Software and Creating a Default Database
Prepare the Server · Install the Oracle Software · Create an Oracle Database · Complete the Server Configuration · Summary
Starting and Stopping · Connectivity and Troubleshooting · Schemas, Tablespaces, and Segments · Fault Tolerance and Backups
Tuning Database Access within an Application · Sizing the SGA · Balancing Disk I/O · Managing Database Connections · Tuning Sorts · Configuring Rollback Segments
Oracle Documentation · Other Publications · Oracle Technology Network · Oracle User Groups · Free Web Resources
It’s relatively easy to install an Oracle database on your Linux server. But to realize the full scalability, availability, and performance potential that Oracle offers, you need to know some important information about how Oracle works. Most of us don’t have the time to read the dozens of books available on the subject or attend weeks of Oracle training. This tutorial will teach Linux system administrators or other technical folks new to Oracle the basics of how to install and configure Oracle on Linux in such a way that the database will offer good performance and reliability even as it grows very large.
First, we’ll have a lightning fast introduction to Oracle specifically for system administrators. We’ll look at the Oracle architecture, how it uses memory and disk resources, the various files that make up an Oracle database, and a collection of installation best practices known as the Optimal Flexible Architecture or OFA.
Next, we’ll discuss the steps required to install Oracle. We’ll talk about getting your Linux server ready for Oracle, using the Oracle Installer, database creation issues such as choosing a character set and a block size, resizing the default database so that it will meet your needs, and creating your application schema.
Then we’ll talk about administering your new database. This includes starting and stopping the database, using administration tools, and troubleshooting connectivity problems.
Next, and perhaps most interesting, we’ll talk about how to keep your database healthy as it grows. We’ll discuss tips for keeping your database manageable and highly available, as well as how to tune it for optimal performance.
Finally, we’ll look at resources available to people new to the Oracle arena in order to learn more about the care and feeding of their Oracle databases and applications.
Why I Wrote This Tutorial
There are several “how to”guides available on the internet today that will show you how to install Oracle on Linux. Most of these guides were written by Linux enthusiasts who are new to Oracle. Through persistence and sheer will (one writer explains in his guide, “It took me 16 tries to get Oracle working”) these folks have managed to get Oracle up and running, and they want to share their knowledge and save you some misery.
I want to share my knowledge also, but I approached the topic from a different angle. I’ve worked with Oracle databases for over ten years, and I can almost install Oracle on Solaris or HP-UX in my sleep. Linux was the new ingredient for me.
I wrote this tutorial to assist Linux users who are new to Oracle. But I am hoping that this tutorial does more than just explain how to get Oracle to run on your Linux box. My goal is to help you understand how it works, and to empower you to make it perform.
My Background
I have been an Oracle DBA and application architect for over ten years, and I am the founder of Database Specialists, Inc., a consulting group specializing in business solutions based on Oracle technology. We have extensive experience in e-commerce, Oracle Applications, and custom applications involving Oracle databases.
An Introduction to Oracle Technology
In this section of the tutorial we will talk about the mantra of platform independence, Oracle database terminology, the different files that make up an Oracle database, the Oracle process architecture, the environment variables Oracle cares about, a set of installation best practices, how administrators interact with the database, and a quick start punch list to getting Oracle up and running.
Platform Independence
Oracle Corporation strives toward platform independence for all of its products, and to date they have been very successful. Oracle databases and application code tend to be extremely portable, and Oracle skills garnered on one platform will be extremely transferable to other platforms. This makes it possible for people with lots of Oracle experience on other platforms to get up to speed very quickly on Oracle on Linux.
Platform independence manifests itself in the Oracle world in many ways. We’ll look at documentation and platform abstraction.
Oracle documentation comes in two varieties. There is the “generic” and the “platform specific.” The bulk of Oracle’s documentation is the former and applies to all platforms. When a generic manual needs to address a topic that is platform specific, the manual will skirt the issue and refer the reader to the manual specific to his or her platform. For each platform, Oracle publishes an installation guide and an administrator’s reference manual that covers all the platform specific details referenced in the generic documentation.
Oracle implements platform independence by abstracting issues that tend to vary from platform to platform. All application development, and as much database administration as possible, will refer to the abstraction instead of the underlying platform specific issue.
Consider data storage as an example. Earlier versions of Windows were limited to eight character case-insensitive file names, while Unix, VMS, and MVS all take radically different approaches to naming and locating data files. Oracle gets around this by storing data in “tablespaces.” A tablespace is a collection of one or more operating system files, and each tablespace in the database has a unique name not to exceed 30 characters. Applications and database administrators almost always refer to tablespaces instead of their underlying operating system files.
This technique of abstraction has the side effect of introducing lots of Oracle-specific terminology.
Database Terminology
If you invite a dozen Oracle database administrators over for dinner, you are likely to get smothered in jargon and lingo. The terminology is inevitable, so let’s go over some of the most commonly used terms. These are the terms you’ll need to understand in order to read Oracle texts or get assistance from others more familiar with Oracle:
DBA: These three letters stand for “database administrator.” Everyone has their own idea of what a DBA does, and many articles have been published on this single topic. A DBA is usually involved in the logical design of a database, performs the physical design and implementation, assists developers with difficult database issues, and is responsible for ensuring good performance, proper security, and backup and recovery strategies.
Database: In the Oracle arena, a database is a collection of operating system files that make up one physical data store or node. There can be many, many different types of data, each with its own attributes, stored in one database. It’s not uncommon for users of simple desktop database products to think of a database as one collection of records, all with the same attributes. In Oracle, we would call that a table, not a database.
Database Name: Every Oracle database has a name, typically eight characters or less. There is also a global name, which is the database name with a domain suffix. The default domain is “.WORLD” which is fine for most purposes. In small to medium sized shops, it’s a good idea to give each database a unique name and use one common domain.
Instance: An instance is a set of operating system processes and shared memory structures that allow an application to access data in a database. Database applications never access an Oracle database directly. Rather, they communicate with Oracle server processes that perform all database access on an application’s behalf. The processes that make up the instance are responsible for such tasks as writing updates to disk, coordinating distributed transactions with remote instances, and rolling back incomplete transactions when an application crashes.
Note that when a database is accessible to applications, it has an instance. In most environments, there will be a one-to-one ratio between databases and instances. But they are not the same thing and the two terms should not be used interchangibly. When Oracle Parallel Server is used, multiple Oracle instances (each running on a separate physical server) will access one common database.
Instance Name: Every Oracle instance has a name. SID (standing for “system identifier”) is another term for instance name. On Unix platforms instance names can be up to eight characters in length. The purpose of the instance name is so that applications may identify which instance they wish to access when multiple instances are running on one server. (Consider a machine with four Oracle databases and four instances.) When Oracle Parallel Server is not being used, the convention is to give the instance the same name as the database it accesses. This will help preserve your sanity, although it is technically not required.
SGA: The SGA, or system global area, is a collection of shared memory structures created by an Oracle instance. The SGA includes a cache of most recently accessed data blocks, a cache of most recently executed SQL statements, latches for implementing locking mechanisms, among many other things. The SGA is implemented on Unix platforms as a collection of shared memory segments. Each process of the instance, as well as each server process used by applications to access the database, attaches itself to the SGA upon startup.
Starting the database: You open an Oracle database to applications by starting up an instance and mounting it to the database. This is often referred to as “starting the database.”
Net8 or SQL*Net: If an application wants to access an Oracle database via an instance running on the same machine as the application itself, an Oracle server process will be started and the application will communicate with the server process through traditional interprocess communication (IPC) mechanisms. But if the application needs to access a database running on another server, then Oracle’s networking infrastructure gets involved. Historically, this infrastructure was called SQL*Net. But when Oracle introduced Oracle8, they changed the name to Net8. Think of them as the same thing, although Net8 is newer and has some additional features.
Client/Server: Oracle internally uses a client/server architecture. Even if you are not developing a fat client application using the client/server model, there still is a client/server architecture at the database level. The application wishing to access Oracle is seen as a client, and the Oracle server process is a server. The client sends requests to the server, and the server satisfies them.
Username: Oracle supports many authentication mechanisms, but the most common is a standard username and password mechanism. Each user in an Oracle database has a unique name up to 30 characters in length. Separate usernames in the database, of course, don’t have to map to separate real-life users. Twenty different application developers could sign on to a database using the same username and password. Conversely, one person could sign on to a database five times, each time specifying a different username and password.
Schema: A schema is a separate namespace for database objects such as tables, indexes, and stored procedures. Every database object resides in exactly one schema. Each database user has exactly one schema that bears the same name as their username. This makes the term username synonymous with schema.
Data Dictionary: Each Oracle database has a repository of information indicating all of the users defined in the database and all of the objects they own (tables, indexes, stored procedures, and so on). The data dictionary is actually a set of database tables with names like user$ and tab$. You should never update these tables manually, but it’s fine for you to query them if you like.
SYS and SYSTEM: Every Oracle database has two special users, SYS and SYSTEM. Think of SYS the way you think of root on a Unix system. The SYS user owns all of the internal structures that make up the data dictionary for the database, and the SYS user also has every possible privilege on the entire database. The SYSTEM user, meanwhile, has DBA privileges and is often used as a generic DBA account.
Control Files, Data Files, Redo Logs, and Parameter Files: These are the physical files that make up an Oracle database. We’ll look at each of these types of files in greater detail later in this section of the tutorial.
Tablespace: A tablespace is a named collection of one or more physical files used for storing database objects. Tablespaces mask certain implementation details from application developers. For example, a DBA can rename a data file without affecting the name of a tablespace, or an application developer can specify that certain data should be stored in a particular tablespace without needing to know if the data will be stored in one file or striped across several files.
Extent: An extent is one contiguous chunk of physical storage within a tablespace. An extent can vary in size from 2 Kb to 2 Gb, as long as the storage is contiguous.
Segment: A segment is a collection of extents that belong to one object. Essentially, a segment is the physical storage used to hold the data for an object. Most segments in a database will hold the contents of a table or an index, but there are other types of segments as well.
Rollback Segment: A rollback segment stores “undo” information for a transaction that is still in progress. This allows Oracle to back out the transaction if the application requests a rollback. Rollback segments also allow Oracle to offer incredible concurrency by enabling one session to update data while another session is querying the very same data—the querying session can use the data in the rollback segment to reconstruct what the data looked like before it was updated.
Temporary Segment: A temporary segment holds a partial result set when too much data is involved to complete the operation in memory. Temporary segments are most commonly used when sorting data for ordering, grouping, or building an index.
Identifier: Most identifiers in Oracle—whether names of users, tablespaces, tables, indexes, or even PL/SQL variables—are limited to 30 bytes in length and are case-insensitive. To make an identifier case-sensitive, or to use an Oracle reserved word as an identifier, enclose the identifier in double quotes. Unless quoted, identifiers must start with a letter and may include letters, digits, and certain (but not all) special characters like _, $, and #.
The Files That Control an Oracle Instance
An Oracle instance is controlled by parameter files and optionally a password file. Let’s look at these two types of files.
A parameter file is a text file containing a collection of name-value pairs that configure an Oracle instance. There are over 200 configurable parameters. Each has a default value, and only those that you wish to set to non-default values need to be listed in the parameter file. When you start an Oracle instance, Oracle looks for a parameter file named initSID.ora (where SID is the name of the instance) and uses the parameter file to configure the instance. arameter files are read once when an instance is started. Changes you make to a parameter file will not take effect until the instance is shut down and restarted.
The parameter file specifies such things as the location and name of the database to mount, how much memory to set aside for a buffer cache, and the maximum number of processes that will be able to access the instance. Figure 1 on the next page shows a sample parameter file.
When Oracle Parallel Server is not being used, all of the instance parameter settings should be stored in one parameter file called initSID.ora. When Oracle Parallel Server is used, some parameter settings will need to be the same for all instances mounting the same database, while others can vary from instance to instance. Therefore, when using Oracle Parallel Server, each instance should have its own parameter file called initSID.ora containing settings for that intance only, and all initSID.ora files should reference one common config.ora file that contains the settings that need to be the same for all instances.
A password file indicates to the instance the usernames and passwords of people authorized to start up and shut down the instance. Once a database is open, a table in the database’s data dictionary is used to authenticate users wishing to access the database. However, this doesn’t help much if the database has not been opened yet. This is where the password file comes in.
A password file is often not used in smaller shops running Oracle on Unix, because there is an alternative. Instead of checking a password file to see if a user is authorized to start the instance, Oracle can ask the operation system to authenticate the user. On Unix platforms this amounts to seeing if the user belongs to a special Unix group, typically called “dba”. The net result is that anybody with a Unix login that belongs to the dba group can start up and shut down all instances running on the server.
Password files are sometimes used in larger shops that have many databases. In such shops the DBAs may use an Oracle tool called Enterprise Manager which allows you to start up and shut down databases from a central location. In this arrangement, the DBA never logs on to the actual server where the instance is located, so operating system authentication cannot be used. Hence the need for a password file.
#
# initMYDB.ora
# ============
#
# Parameter file for MYDB database.
#
#
# config parameters
#
control_files = (/u02/oradata/MYDB/control01.ctl,
/u03/oradata/MYDB/control02.ctl,
/u04/oradata/MYDB/control03.ctl)
background_dump_dest = /u01/app/oracle/admin/MYDB/bdump
core_dump_dest = /u01/app/oracle/admin/MYDB/cdump
user_dump_dest = /u01/app/oracle/admin/MYDB/udump
db_block_size = 8192
db_files = 80
db_name = MYDB
compatible = 8.0.5
rollback_segments = (r01,r02,r03,r04)
utl_file_dir = /u01/app/oracle/admin/MYDB/bdump
log_archive_dest = /u01/app/oracle/admin/MYDB/bk/arch
log_archive_format = %S.log
log_archive_start = true
#
# tuning parameters
#
shared_pool_size = 16000000
sort_area_size = 4096000
db_block_buffers = 2000
db_file_multiblock_read_count = 64
processes = 100
sessions = 100
log_checkpoint_interval = 10000
log_buffer = 163840
sequence_cache_entries = 100
sequence_cache_hash_buckets = 89
job_queue_processes = 2
max_dump_file_size = 10240
Figure 1: A sample parameter file.
The Files That Make Up an Oracle Database
An Oracle database is made up of control files, data files, online redo logs, and archived redo logs. Let’s look at each of these types of files.
Every Oracle database has at least one control file. If a database has multiple control files, then they are all identical to each other. A control file is a relatively small (usually under 2 Mb) binary file that contains information about the database such as its name, character set, and the names of all data files and redo logs. Control files also contain synchronization information used during recovery from a crash or media failure.
If you lose every copy of the control file for the database, you stand to lose the entire database. For this reason, an Oracle database will typically have three control files located on separate physical devices. Since control files are relatively small and incur little I/O, there is no reason not to use multiple control files.
The data files of a database hold all of the segments of the database. This will include the actual application data, as well as database structures such as the data dictionary, rollback segments, and temporary segments. Each data file is part of exactly one tablespace. Every Oracle database has at least one data file, belonging to the SYSTEM tablespace. Data files take up the bulk of the physical storage used by the database. On some versions of Unix including Linux, physical files are limited in size to 2 Gb. This won’t put much of a damper on the size of your Oracle database, because one database can easily have over 1000 data files.
When transactions update data in the database, Oracle updates blocks in the data files. For fault tolerance purposes, Oracle also writes entries to the online redo log. These entries will contain the minimal information needed by Oracle so that it could replay or “redo” the transactions at a future time in order to bring a backup copy of the database up to date.
The online redo log consists of two or more physical files, typically the same size, that form a circular buffer. The term “redo log files” refers to the individual files that make up the online redo log. The online redo log will typically consist of three or four such files, each having a fixed size anywhere from 1 Mb to 100 Mb. The optimal size depends on the amount of transaction activity and recoverability requirements for the database.
Initially Oracle writes entries to the first redo log file. When this file fills up, Oracle switches to the next. At this time Oracle begins flushing dirty buffers from its buffer cache. After the last redo log file is filled, Oracle goes back to the first one and overwrites it. Before Oracle overwrites a redo log file, it will first make sure the flush of dirty buffers initiated when that log filled has been completed.
The online redo log allows Oracle to defer writes to data files while still being able to recover the database with no data loss if the instance is terminated abruptly without first flushing dirty buffers to disk.
In production environments and other situations where data loss cannot be tolerated, an archived redo log will be maintained for the database in addition to the online redo log. The archived redo log is a collection of physical files, each the exact size of the online redo log files, that hold all redo log entries since the database was created. Each file in the archived redo log will have a sequence number in the file name so that you can quickly tell the proper ordering of the files.
When an archived redo log is maintained for the database, the database is said to be operating “in archivelog mode” (as opposed to “noarchivelog mode”). In archivelog mode, Oracle will start copying an online redo log file to the archived redo log as soon as it fills. Oracle will not begin overwriting the online redo log until it has completed copying it to the archived redo log.
The Oracle Process Architecture
An Oracle database server will typically have an Oracle database, an instance, Net8, and one or more database applications. The applications may run on the same server as the database and instance (in which case Net8 might technically not be required) but typically the applications are distributed among networked computers.
An Oracle instance consists of several daemon processes, all attached to the shared global area or SGA. On Unix, the SGA is implemented as one or more shared memory segments. On Windows NT, the entire Oracle instance is implemented as one big process sharing one memory space which includes the SGA. (On Windows NT the daemons are implemented as threads within the one process.)
The daemons making up an Oracle instance are listed in Figure 2 below. Every Oracle instance will have at least PMON, SMON, LGWR, and one DBWn. The rest are optional and configurable. The “n” in a daemon name indicates a digit zero through nine, meaning that there can be multiple copies of that daemon running.
| Name | Function | Description |
| PMON | Process monitor | Process watchdog; cleans up after crashed processes |
| SMON | System monitor | System watchdog; performs system-wide management duties |
| LGWR | Log writer | Writes entries to the online redo log |
| DBWn | Database writer | Writes dirty buffers to the data files |
| CKPT | Checkpoint | Updates synchronization information in the control file and the headers of all data files |
| ARCH | Archiver | Copies filled online redo log files to the archived redo log |
| RECO | Recoverer | Coordinates with remote instances in order to recover failed distributed transactions |
| SNPn | Job queue | Runs queued jobs that have been scheduled to run at specified time intervals |
| Dnnn | Dispatcher | Connects an application requesting database services with a shared server that is available to perform the services (multi-threaded server configuration only) |
| Snnn | Shared server | Services database requests from applications (multi-threaded server configuration only) |
| LCKn | Lock | Coordinates cache coherency with other instances that have mounted the same database (Oracle Parallel Server only) |
Figure 2: The daemons making up an Oracle instance
A database is made accessible to applications in three phases. First an instance is started, then the database is mounted, and finally the database is opened. Figure 3 on the next page shows a diagram of the Oracle process architecture, and this will prove helpful for this discussion.
To start an instance Oracle reads the parameter files for the instance, builds the SGA, and starts daemon processes according to the parameter settings. Once the instance has been started, a number of Oracle daemons will be running and each will be attached to the SGA. The SGA will be correctly sized for when the database is opened, but at this time it will be almost completely empty.
Figure 3: The Oracle process
architecture
To mount the database, Oracle reads the contents of the control file for the database and populates data structures in the SGA. Parameters in the parameter files indicate the name of the database and the location of the control files. This is how the instance determines which database to mount. After the database has been mounted, most of the SGA will still be empty and it will not yet be possible to access data in the database or even the data dictionary for the database. However, it will be possible to access control file type information in the SGA, such as the names of the data files that make up the database.
To open the database, Oracle checks the headers of all data files against synchronization information in the control file in order to verify the state of the database. If the database was not shut down cleanly the last time, Oracle will automatically perform what is called “instance recovery” in order to make the database consistent again before applications may access it. When performing instance recovery, Oracle will use the online redo log as necessary to redo any transactions that had be committed but were never written to disk, and the contents of the rollback segments to undo any transactions that had not yet committed when the database was shut down. (These two recovery steps are called “rolling forward” and “rolling back,” respectively.)
Once Oracle determines that the database is consistent, the database becomes available for applications to access it. The database is now considered “open.” Initially the buffer cache and shared SQL areas within the SGA will be nearly empty. But they will fill, of course, as the database is accessed.
Applications access an Oracle database by connecting to the instance, preparing and submitting a request, and retrieving the results. The low-level API applications use to access Oracle databases is called the Oracle Call Interface, or OCI. There are a huge number of wrappers available to make the API look different—Pro*C, JDBC, DBI/DBD, and ODBC are just a few of them. But since these APIs ultimately boil down to OCI, they all will share similar concepts.
An application connects to an Oracle instance by specifying a username, a password, and optionally a Net8 identifier. If the desired instance is running on the same server as the application itself, the application can set the ORACLE_SID environment variable to the name of the instance. In this case, the application will fork off an Oracle server process that attaches itself to the SGA. Because the Oracle server process has its setuid bit set, the server process will be able to read all of the data files for the database and read and write to the SGA.
If the application wishes to connect to an instance on a remote server, then Net8 will be involved. The application will either specify a Net8 alias for the desired database or set the TWO_TASK environment variable to the Net8 alias. Net8 will resolve the alias to a host name, instance name, and protocol information (such as TCP/IP and a port number). The application will then send a message using the correct protocol to the correct port number on the server running the desired instance. A Net8 listener process running on the remote server will fork off an Oracle server process and the application will relay its database access requests through this server process.
Once an application is connected to an Oracle instance, it submits database access requests via its Oracle server process. Basically, the application prepares and submits SQL or PL/SQL statements to the server process for execution. The server process will read and write buffers in the SGA’s buffer cache as required to satisfy the request, also reading blocks from data files into the buffer cache when necessary and writing to the SGA’s redo log buffer as necessary. It’s interesting to note, however, that the server process will not write dirty buffers from the buffer cache back to data files. Nor will it write redo log entries from the SGA’s redo log buffer to the online redo log files. These tasks are relegated to DBWn and LGWR, respectively.
Applications interact with the Oracle server process in a client/server fashion. The application submits a request and the server process satisfies it. The Oracle server process will only respond to the application’s requests, and will not initiate activity on its own. In fact, if a DBA kills an idle database connection, the application will not learn its connection has been terminated until the next time it submits a request.
Applications execute all PL/SQL calls and SQL statements other than queries by preparing the statement and then submitting an instruction to execute the statement. Oracle will return status information indicating success, how many rows were processed, and so on. Oracle may also return values to the application.
Applications execute SQL queries a little bit differently. First they prepare the statement and then submit the instruction to execute it. The Oracle server process might not do much processing at this point, however. The next step is for the application to request a set of results. This may be one row or one hundred, depending on what the application asks for. It is this request for results that triggers the actual processing in the Oracle server process. The application may ask for results repeatedly until the entire result set has been returned to the application.
When the application is finished accessing the Oracle database, it submits an instruction to disconnect. This causes the Oracle server process to detach itself from the SGA and exit.
Environment Variables
When an application connects to an Oracle instance, or when a DBA starts an instance and opens a database, Oracle reads the settings of several environment variables. These variables help Oracle find the correct libraries and determine which database to access, among other things. Figure 4 shows the main environment variables of interest to Oracle on Unix. (Windows NT uses the registry to hold this information, instead of environment variables.)
| Variable | Purpose | Sample Value | Importance |
| ORACLE_HOME | Location of Oracle software installation | /u01/app/oracle/product/8.0.5 | Required |
| LD_LIBRARY_PATH | Location of Oracle shared libraries | Include $ORACLE_HOME/lib | Required |
| PATH | Location of Oracle executables | Include $ORACLE_HOME/bin | Required |
| ORACLE_SID | Name of local Oracle instance to access | MYDB | Optional |
| TWO_TASK | Net8 alias of Oracle instance to access | MYDB | Optional |
| NLS_LANG | Language and character set of client application | american_america.US7ASCII | Optional |
| ORA_NLS33 | Location of Oracle client NLS character set data | $ORACLE_HOME/ocommon/nls/admin/data | Optional |
| ORACLE_BASE | Location of OFA directory structure | /u01/app/oracle | Optional |
| DBA | Location of instance-related administrative files | /u01/app/oracle/admin | Optional |
Figure 4: Environment variables of interest to Oracle
ORACLE_HOME must be set and LD_LIBRARY_PATH and PATH must include the right Oracle directory in order for Oracle to locate the proper executables, libraries, and Net8 configuration files.
ORACLE_SID or TWO_TASK may be set to allow an application to access a database without specifying the desired instance explicitly. If both are set, then TWO_TASK will take precedence. Also, if the application specifies a Net8 alias when connecting, this will take precedence over both the ORACLE_SID and TWO_TASK environment variables. Note that in Figure 4 both ORACLE_SID and TWO_TASK have the same sample value. This can be misleading, as the ORACLE_SID contains the name of an Oracle instance while TWO_TASK contains a Net8 alias. It just happens to be good practice to name Net8 aliases the same as the Oracle instance they reference.
NLS_LANG and ORA_NLS33 indicate the language and character set of the client application, and help Oracle locate resource files for converting between character sets. These variables are not required if English and 7 bit ASCII are to be used. For all other languages and character sets, however, both should be set correctly. The formatting of the NLS_LANG variable is a bit odd—see the platform specific installation guide for a list of supported values. Note that early releases of Oracle for Linux may not support languages other than English.
ORACLE_BASE and DBA assist DBAs by allowing them to quickly navigate to certain important directories in the Oracle hierarchy. The exact meaning of these variables will become more clear when we look at installation best practices on the next page.
You can set all of these environment variables in your login script—such as .bashrc, .profile or .cshrc. You may choose to hardcode these variables to the appropriate values for your implementation, or you may choose to integrate your login script with scripts provided by Oracle expressly for setting up environments. While it may seem easier at first to just hardcode everything, integrating with the Oracle scripts is definitely the way to go. This will make it much easier to support multiple instances running on one server, and will minimize the complexity of future Oracle software upgrades. We will cover login scripts in greater detail when we walk through the process of installing Oracle software and building a default database.
Installation Best Practices: OFA
Over the years, the consulting group within Oracle Corporation developed a set of best practices for installing and configuring Oracle databases. They called it the Optimal Flexible Architecture, or OFA. Today the OFA is a relatively compact set of rules that help lead to an Oracle installation that is easy to support and highly scalable.
Around 1995 or so Oracle began integrating OFA concepts into the software installer on the Unix platform so that performing a “default” installation would yield a somewhat OFA compliant system. Oracle 7.3 and later install on Unix in a reasonably good way (Oracle 8.0 and later on Linux, since Oracle 7.3 was never released for Linux). It’s interesting to note that the Windows NT group at Oracle was apparently off in their own world; Windows NT Oracle installations don't approach any semblance of OFA compliance until Oracle8i.
The administrator’s reference manual in Oracle’s platform specific documentation contains a thorough explanation of the OFA. The basic benefits of the OFA are:
- A standard methodology that will be familiar to experienced Oracle DBAs
- A naming convention that is simple and makes sense
- A standard for locating files that makes things easy to find
- Support for multiple databases and/or versions of Oracle software on one server
- A separation between Oracle software and database files, making upgrades easier
- A well thought out operating system security policy
- Distributing I/O across many physical storage devices
The OFA guidelines cover the following areas:
- Assigning and naming mount points
- Directory hierarchy for Oracle software, database files, and administrative files
- Naming conventions for all files that make up a database or control an instance
- Naming tablespaces
- Assigning segments to tablespaces
Figure 5 shows the basic directories of an OFA compliant Oracle installation, and Figure 6 shows sample directory and file names for an OFA compliant Oracle database.
| Directory | Description |
| $ORACLE_BASE | Root of the OFA directory structure |
| $ORACLE_BASE/product/<version> | Oracle software installation, or ORACLE_HOME for a specific version of Oracle software |
| $ORACLE_BASE/admin/$ORACLE_SID | Administrative area for a specific instance. Subdirectories include:
- pfile |
| <mount point>/oradata/<dbname> | Data files, online redo logs, and control files for a specific database |
Figure 5: Basic directories of an OFA compliant Oracle installation
| File or Directory | Location |
| ORACLE_BASE | /u01/app/oracle |
| ORACLE_HOME | /u01/app/oracle/product/8.0.5 |
| MYDB admistrative file tree | /u01/app/oracle/admin/MYDB |
| MYDB parameter file | /u01/app/oracle/admin/MYDB/pfile/initMYDB.ora |
| MYDB alert log | /u01/app/oracle/admin/MYDB/bdump/alert_MYDB.log |
| MYDB control files | /u02/oradata/MYDB/control01.ctl /u03/oradata/MYDB/control02.ctl /u04/oradata/MYDB/control03.ctl |
| MYDB online redo logs | /u02/oradata/MYDB/redo01.log /u02/oradata/MYDB/redo03.log /u03/oradata/MYDB/redo02.log /u03/oradata/MYDB/redo04.log |
| MYDB data files | /u04/oradata/MYDB/system01.dbf /u04/oradata/MYDB/temp01.dbf /u05/oradata/MYDB/rbs01.dbf /u06/oradata/MYDB/table01.dbf /u07/oradata/MYDB/index01.dbf |
Figure 6: Sample directory and file names for an OFA compliant Oracle database
Interacting with a Database
Oracle provides two very basic tools for interacting with an Oracle database. While application developers will probably interact through a wide selection of APIs and end users will access data through applications or query tools, the DBA is often left to use Oracle’s basic tools.
SQL*Plus is a simple character mode tool that lets you connect to an Oracle instance and submit arbitrary SQL statements and PL/SQL code. The tool offers rudimentary output formatting capabilities, allowing SQL*Plus to be used as a very crude reporting tool. SQL*Plus also has scripting capabilities. Although the scripting features seem prehistoric compared to modern languages and tools, you can do a surprising amount of automation with SQL*Plus scripting.
Server Manager is an even more bare-bones character mode tool. Like SQL*Plus, it lets you connect and submit arbitrary commands. The output formatting and scripting capabilities of Server Manager are even more limited than those of SQL*Plus. However, Server Manager allows you to startup and and shut down Oracle instances—something you cannot do with SQL*Plus.
DBAs who want to get beyond basic tools have an increasing number of options available. Oracle now offers Enterprise Manager, a GUI tool for managing multiple Oracle databases. Depending on your Oracle licensing arrangement, certain modules of Enterprise Manager may be available to you free of charge. Unfortunately, the Enterprise Manager that comes with Oracle releases prior to Oracle8i is quite limited. For one thing, the earlier version only runs on the Windows platform. For another, it stores a lot of important information on the Windows client, undermining the possibilities of remote administration or having a team of Oracle DBAs share duties.
The newer version of Enterprise Manager shipping with Oracle8i is Java based, and is supposed to run from a web browser. This might make it a more appealing database administration tool.
Besides Enterprise Manager, there are a host of other DBA tools available today. Some of the more basic ones are available for free on the internet, while a whole host of companies are marketing very complex and sophisticated products for equally sophisticated prices.
Oracle Quick Start
In order to get up and running on Oracle, you’ll first need to install the Oracle software and create a default database. Next you’ll want to adjust the default database to improve its OFA compliance and make the database more usable. (The default database won’t get you very far, unless you will just be playing around with a few Kb of data.)
Once you’ve got a viable database running, you are ready to create your application schemas and establish connectivity between your applications and the database. Now you are set to do your application development.
Soon it becomes time to think about real world issues such as capacity planning, performance, security, backup and recovery, and implementing schema or application changes while you are already in production.
In the next section of this tutorial, we’ll walk through the process of installing Oracle software and creating a default database. We’ll go through this in blistering detail, because there are many issues and lots of “gotchas” that lead people new to Oracle technology to the conclusion that Oracle is overly complicated and difficult to install. Actually, it’s all very simple if you have the right information.
Installing the Oracle Software and Creating a Default Database
In this section of the tutorial we will walk through the steps of installing Oracle 8.0.5 Standard Edition in a Red Hat 5.2 Linux environment and creating a default database. Subsequent sections will look at how to manage the database and optimize performance. In this section we will drill down to a great level of detail so that even the system administrator who is new to Oracle can get up and running quickly.
Linux is extremely similar to flavors of Unix such as Solaris. About 95% of the material here is directly applicable to other Unix platforms. The main areas of divergence are the setting of the operating system kernel parameters and the locations of the oratab file and local bin directory.
There are four phases to getting Oracle up and running on your database server:
1.
Prepare
the server
2.
Install
the Oracle software
3.
Create
an Oracle database
4.
Complete
the server configuration
We will walk through these phases one at a time, detailing all the steps involved. The end result will be a very usable database that can be scaled up quite large. Of course, every implementation is unique, and you will need to evaluate each step carefully against your particular requirements. But this section of the tutorial will get you off to a very solid start.
Prepare the Server
These steps configure your machine so that it will be ready to accept the Oracle software and database. In this section, we will make sure the operating system meets Oracle’s minimum requirements, create a special user and group to “own” the software, and create some directories that will be used by the Oracle software and database. All of the steps in this section are run as the root user.
- Make sure that your version of Linux is sufficient. Oracle works with many different distributions of Linux. The key requirements are that the kernel be 2.0.33 or higher, and GNU C libraries be 2.0.6 or higher. Many users have reported that the GNU C libraries must in fact be 2.0.6 or 2.0.7–that the “or higher” is not true. You will greatly simplify your life if you work with the Red Hat 5.2 distribution. This package is reasonably priced, readily available, and known to work very well with Oracle 8.0.5 Standard Edition. Note that the Red Hat 6.0 distribution includes GNU C libraries 2.1, and many users have reported difficulties installing Oracle on Linux systems that use GNU C libraries 2.1. (Oracle has a patch available, if you really want to use the newer GNU C libraries.)
- Make sure that your hardware is sufficient. You’ll need at least 32 Mb RAM, a CD ROM drive, and about 500 Mb of disk space. This will let you create a small database for prototyping; a real implementation will likely require more RAM and more disk space.
- Make
sure that the Linux kernel has parameters set sufficiently high for Oracle. The
Oracle architecture makes heavy use of shared memory segments for sharing data between
multiple processes and semaphores for handling locking. Many operating systems,
such as Solaris, do not by default offer sufficient shared memory or semaphores
for maintaining an Oracle database. Happily, Red Hat Linux 5.2, by default,
builds a kernel that will support most Oracle implementations.
Kernel Parameter Red Hat 5.2 Default Purpose SHMMAX 0x2000000 (32 Mb) Maximum size of a single shared memory segment SHMMIN 1 Minimum size of a single shared memory segment SHMMNI 128 Maximum number of shared memory segments in entire system SHMSEG 128 Maximum number of shared memory segments one process can attach SEMMNS 4096 Maximum number of semaphores in entire system SEMMNI 128 Maximum number of semaphore sets in entire system SEMMSL 32 Maximum number of semaphores per set The first four kernel parameters configure shared memory segments. The Red Hat 5.2 defaults allow your database SGAs to get as big as you could possibly want, when you consider that Linux is limited to 2 Gb of physical memory. If a database has an SGA bigger than 32 Mb, it will be spread across multiple shared memory segments. This is not a concern.
The last three kernel parameters configure semaphores. Each Oracle instance requires one semaphore for each process. Oracle does not seem to care how many semaphores are in each set, but it appears that one instance cannot handle more than 30 sets. The Red Hat 5.2 defaults allow you to have four instances on one server, with each instance having about 960 processes. This should be sufficient for most implementations.
Recompiling a Linux kernel is somewhat complicated and definitely requires that you know what you are doing. We won’t cover it here, but you probably won’t need to recompile your kernel to install Oracle, anyway.
- Create a Linux group that will be used by the Oracle software owner and database administrators. You can call it what you like, but the standard is “dba”. If you will be installing Oracle on multiple Linux servers on your network, you might want to keep the groupid the same on all servers. I created my dba group with the command:
- Create a Linux user that will be the Oracle software owner. You can call it what you like, but the standard is “oracle”. If you will be installing Oracle on multiple Linux servers on your network, you might want to keep the userid the same on all servers. Note that this user’s home directory will not be the ORACLE_HOME or where the actual Oracle software is installed; this user’s home directory should be in the same place as other users’ home directories. This user’s initial group should be the dba group created in the previous step. I created my oracle user with the commands:
- Create mount points for the Oracle software and the Oracle database. Each mount point should correspond to a separate physical disk device. You’ll need at least one mount point. Typically you use one mount point for the Oracle software and as many as you can afford for each database. (More physical devices allow better performance.) A nice convention is to call the mount points /u01, /u02, and so on. Because mount points are typically owned by root and the Oracle installer will run as the oracle user and not root, you should create some subdirectories now to avoid permissions problems later. Create an app subdirectory below the software mount point, and oradata subdirectories below the mount points to be used for databases. (You can put software and a database on the same mount point if you wish.) Make these subdirectories owned by the oracle user and dba group, and give them 755 permissions.
- Choose a directory that the Oracle software will refer to as the “local bin” directory. A common choice is /usr/local/bin, and your installation will go more smoothly if you stick with this choice. Make sure this directory is on users’ path by default.
- If you downloaded a trial version of Oracle off of the internet, then untar the distribution. If you have the software on CD ROM, then mount the CD ROM now. I use automount to mount my CDs, but alternatively you can use a command like:
- Create the /etc/oratab file. After installation, this will be a plain text file that briefly describes the Oracle software installations and databases on the server. The file must exist before installation can begin. The easiest way to create the file is simply to create an empty file called /etc/oratab and change the ownership to oracle, the group to dba, and the permissions to 664. Alternatively, you can set the ORACLE_OWNER environment variable and execute a shell script on the CD-ROM:
groupadd -g 300 dba
useradd -c 'Oracle software owner' -d /home/oracle -g dba -m -n \
-s /bin/bash -u 300 oracle
passwd oracle
mount -t iso9660 /dev/cdrom /cdrom
ORACLE_OWNER=oracle
export ORACLE_OWNER
cd /cdrom/orainst
/oratab.sh
Install the Oracle Software
These steps install the Oracle database software onto your server so that you will then be able to create databases and use server-side tools like SQL*Plus and Server Manager. In this section, we will prepare the oracle user’s environment, run the Oracle Installer, and tidy up a few minor messes that the installer leaves behind. We will create a database later by invoking the Oracle Installer a second time. This allows us to choose our own database block size instead of being forced to use the default. All of the steps in this section, except where noted, are run as the oracle user.
- Edit the oracle user’s login file so that the environment will be configured automatically on login. If you are using Bash, then edit the .bashrc file. If you are using Bourne or Korn shell, then edit .profile. For now, we will hardcode certain things. But after the software and database are installed, we will come back and eliminate all hardcodings. Here is what I added to my .bashrc:
- Log out and log back in as the oracle user so that the environment is set correctly.
- Use ftp to transfer a small file to a remote host to prove to yourself that TCP/IP networking is installed and working properly on your server.
- Ensure that the mount point you designated for the Oracle software has sufficient free space. Allow 400 Mb as a bare minimum.
- Double check that you are logged in as oracle and not root. Then start the Oracle Installer. I recommend that with this release of Oracle, you simplify your life and use the character mode version of the installer. To do this, make sure your DISPLAY environment variable is not set, and that ORACLE_TERM is set accordingly. Start the installer with these commands:
- Choose Custom Install.
- Read two not very interesting readme files.
- Choose Install, Upgrade, or De-Install Software.
- Choose Install New Product – Do Not Create DB Objects.
- Confirm settings for ORACLE_BASE and ORACLE_HOME. These should be correct, because you set your environment variables beforehand.
- Confirm log file locations. The Oracle Installer will write four log files in the $ORACLE_HOME/orainst directory.
- Choose Install from CD-ROM.
- Choose the language you want your Oracle installation to support. This is the language that banners, prompts, and error messages will be displayed in. The languages available for your data stored in the database will be dictated by the character set you choose when creating the database, and not by how you answer this installer prompt. I chose American English.
- Confirm the root.sh location. This file will contain post-installation steps to be performed by the root user. If you run the installer multiple times, you’ll be prompted to move the previous root.sh file to another location.
- On the Software Asset Manager screen, select the products you wish to install and choose the Install button. Note that selecting products that have sub-products (like “Net8 Protocol Adapters”) doesn’t do anything—you need to select the sub-products themselves. Here’s what I installed:
- Choose the Linux group you created in the previous section as the DBA group. Any Linux user belonging to this group will have full database administration privileges over all databases on this server.
- Choose the Linux group you created in the previous section as the osoper group. Any Linux user belonging to this group will have “operator” privileges over all databases on this server. You could use a separate Linux group for this purpose, but most installations do fine with using the same Linux group for both DBA and osoper.
- Choose the JDBC drivers you wish to install (if you selected JDBC drivers on the Software Asset Manager screen). There is support for JDK 1.0 and 1.1, and there are drivers that require Net8 or drivers that run on a thin client.
- Confirm the directory for your online documentation, if you chose to install it. The default is $ORACLE_HOME/doc, and this seems like a good choice.
- Choose whether to install documentation in HTML format, PDF format, or both. Remember, this only applies to the Linux platform-specific documentation.
- At this point, the Oracle Installer installs software onto your server.
- I encountered one glitch during the software installation. You will not encounter this problem unless you chose to install Server Documentation. The installer reported a write error, which may have occurred because the installer tried to copy a file without creating the directory first. I fixed this problem easily by going to another window on the server and creating the directory specified in the error message. Then I chose the Retry option in the Oracle Installer, and things continued normally.
- When the installation is complete you’ll receive a message that the requested actions have been performed. Choose OK, and you will be returned to the Software Asset Manager screen.
- Exit the installer.
- In $ORACLE_HOME/bin you will find a shell script called oraenv. This script can be called from .bashrc or .profile to set up a user’s environment. Unfortunately, there are several variables that the script does not set—some handy, some very important. Make a backup copy of the script and then edit it, adding the following lines to the very end:
- I made a further edit to $ORACLE_HOME/bin/oraenv that you may or may not need. After editing my .bashrc to call oraenv each time a new shell is created (we’ll do this in a future step) I found that sometimes my server would run out of processes. I have not seen this behavior on other Linux servers, so I might have something peculiar in my configuration. If your server starts giving you vfork errors further down in the installation process, then come back and perform this step. Otherwise, you may wish to try and get by without it. The edit is as follows: Find the line in $ORACLE_HOME/bin/oraenv that contains a call to dbhome. Replace it with the following:
- In $ORACLE_HOME/bin you’ll find a script called dbshut. This is a utility that you can run to shut down databases on the server. Unfortunately, it shuts down databases with normal priority. This means that if any users are logged into a database, the shutdown will hang until they log out. You might want to change this script to shut down databases with immediate priority. To do this, find the two lines that contain just the word “shutdown”. Change these to read “shutdown immediate”.
- Look at the script $ORACLE_HOME/orainst/root.sh. This is a script that the installer created during software installation. It’s a collection of commands that need to be run as the root user. You should review the script carefully and decide whether to run it as is, or hand-pick the commands you wish to run as root. Running the script as is creates more problems than it fixes. Really, all you need to do as root is copy oraenv, dbhome, and coraenv from $ORACLE_HOME/bin to the local bin directory. If you do choose to run root.sh, please be advised that there is a documented serious security problem. root.sh will set the setuid bit on several files in $ORACLE_HOME/bin, and change some of them to be owned by root. Nothing in $ORACLE_HOME/bin should be owned by root, and only the oracle and dbsnmp executables should have a setuid bit turned on. Oracle Corporation has released an emergency patch called setuid_patch.sh to address this problem. The problem is also present on all Unix releases of Oracle8 and Oracle8i through and including 8.1.5.
- Whether or not you run root.sh, double-check a few file permissions. The permissions on $ORACLE_HOME/bin/oracle should be 6751. My installation came out as 4755. Also, oraenv in $ORACLE_HOME/bin and the local bin directory should have permissions 755. My installation came out as 777.
umask 022
# Substitute your Oracle software mount point in the line below.
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/8.0.5
# Substitute the name of your Oracle database below.
export ORACLE_SID=MYDB
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
# Substitute terminal type in line below. vt100 or vt220 is
# recommended.
export ORACLE_TERM=vt100
# Following two lines are not required if you’ll be using the
# default character set, 7-bit ASCII. To use another character
# set, see Appendix C of the Installation Guide and change
# NLS_LANG accordingly.
export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
export NLS_LANG=american_america.US7ASCII
# Fill in the following line as you wish, but make sure that
# $ORACLE_HOME/bin, /bin, /usr/bin, and “local bin” are all
# in the PATH.
export PATH=...
# If your /var/tmp doesn’t have at least 20 Mb free for the
# Oracle Installer to use (or is not writable by oracle), then
# complete the following line.
export TMPDIR=...
# Ensure that SRCHOME and TWO_TASK are not set.
# If you will be using Java, include the following two lines:
export CLASSPATH=$ORACLE_HOME/jdbc/lib/classes111.zip
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/jdbc/lib
cd /cdrom/orainst
./orainst
We’ll walk through the installer prompts one at a time:
· Client Software · Net8 · TCP/IP Protocol Adapter · Unix Installer · JDBC Drivers
|
· Server Documentation · Standard RDBMS · PL/SQL · Pro*C/C++ · SQL*Plus
|
I recommend installing the Unix Installer. This will make it easier to perform maintenance operations (such as relinking) in the future without having to dig out the CD ROM. Also, note that the Server Documentation selection will only install Linux platform-specific documentation; the generic Oracle product documentation is contained on a separate CD ROM.
# Begin customizations
ORACLE_BASE=`dirname $ORACLE_HOME`
ORACLE_BASE=`dirname $ORACLE_BASE`
DBA=$ORACLE_BASE/admin
ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
# Substitute character set you plan to use in following line.
NLS_LANG=american_america.US7ASCII
export ORACLE_BASE DBA ORA_NLS33 NLS_LANG
case "$LD_LIBRARY_PATH" in
*$OLDHOME/lib*) LD_LIBRARY_PATH=`echo $LD_LIBRARY_PATH | \
sed "s;$OLDHOME/lib;$ORACLE_HOME/lib;g"` ;;
*$ORACLE_HOME/lib*) ;;
*:) LD_LIBRARY_PATH=${LD_LIBRARY_PATH}$ORACLE_HOME/lib: ;;
"") LD_LIBRARY_PATH=$ORACLE_HOME/lib ;;
*) LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib ;;
esac
# Following case statement only required if using JDBC.
case "$LD_LIBRARY_PATH" in
*$OLDHOME/jdbc/lib*) LD_LIBRARY_PATH=`echo $LD_LIBRARY_PATH | \
sed "s;$OLDHOME/jdbc/lib;$ORACLE_HOME/jdbc/lib;g"` ;;
*$ORACLE_HOME/jdbc/lib*) ;;
*:) LD_LIBRARY_PATH=${LD_LIBRARY_PATH}$ORACLE_HOME/jdbc/lib: ;;
"") LD_LIBRARY_PATH=$ORACLE_HOME/jdbc/lib ;;
*) LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/jdbc/lib ;;
esac
export LD_LIBRARY_PATH
# End customizations
# Begin customizations
#ORAHOME=`dbhome "$ORACLE_SID"`
# Following two lines taken from dbhome:
ORATAB=/etc/oratab
ORAHOME=`awk -F: "/^${ORACLE_SID}:/ {print \\$2; exit}" \
ORATAB 2>/dev/null`
# End customizations
Create an Oracle Database
These steps create an Oracle database on your server. In this section we will create a default Oracle database, tweak the default configuration so that the database is actually usable, create application users and tablespaces in the database, and configure Net8. All of the steps in this section are run as the oracle user.
- Choose a block size for your Oracle database. The default is 2 Kb, and this is appropriate for very small demo or “sand box” databases. If you anticipate your database growing beyond perhaps 100 Mb in size, you should use a block size of 8 Kb or 16 Kb.
- The Oracle Installer will not ask you what block size you want, and you cannot change a database’s block size after creation. If you want your database to have a block size other than 2 Kb, then you will need to edit the file $ORACLE_HOME/rdbms/install/rdbms/cnfg.orc to set the db_block_size to 8192 or 16384. Make sure the line is not commented out.
- Double check that you are logged in as oracle and not root. Then start the Oracle Installer. Again, I recommend that with this release of Oracle you simplify your life and use the character mode version of the installer. To do this, make sure your DISPLAY environment variable is not set, and that ORACLE_TERM is set accordingly. This time we will run the Oracle Installer installed on your system; we no longer have a need for the software CD ROM. Start the installer with these commands:
- Choose Custom Install.
- Read two not very interesting readme files.
- Choose Create/Upgrade Database Objects.
- Choose Create Database Objects.
- Confirm settings for ORACLE_BASE and ORACLE_HOME. These should be correct, because you set your environment variables beforehand. Note that the installer will want the parent directory above ORACLE_BASE to be writable by the oracle user. This does not make a lot of sense, but the easiest thing to do is humor the installer and open up permissions on that directory (for example, /u01/app) temporarily. After the database is created, you can lock down the permissions again.
- Confirm log file locations. The Oracle Installer will write four log files in the $ORACLE_HOME/orainst directory.
- Confirm the setting for ORACLE_SID. This should be correct, because you set your environment variables beforehand.
- On the Software Asset Manager screen, you’ll see a list of all products that have been installed. Select every product except the Online Text Viewer and choose Install. This will cause Oracle to do whatever database setup is required for each product. Many products (like the TCP/IP protocol adapter) don't have any database objects associated with them, but some (like SQL*Plus and PL/SQL) do. It’s safest to select all products, but don't select the Online Text Viewer because the installer will give you an error message if you do.
- Choose Create Product DB Objects for Server Manager.
- Choose Filesystem-Based Database (unless you want to deal with raw devices, in which case you are on your own).
- Choose No to Distribute control files over three mount points. The default database that the installer builds for you will need tweaking whether you distribute files across three mount points or not. The mess is a little easier to clean up if it’s all in one directory instead of spread across three.
- Enter the mount point where the installer should create the database—for example, /u01. You should have already created an oradata subdirectory under the mount point and made it owned by the oracle user, or else you will run into file permission problems.
- Choose a character set for the database. US7ASCII is the default on Linux and Unix. (It is not the default on Windows NT.) If you want to use a different character set, then you should have set NLS_LANG and ORA_NLS33 before invoking the installer. You cannot change a database’s character set after creation. If you want to store non-English text in your database, such as umlauts and diacritical marks, then you should definitely not use US7ASCII as your character set. See Appendix C of the Installation Guide for a list of recognized character sets.
- Choose a national character set for the database. This is the character set to use for storing data of type NCHAR, NVARCHAR2, and NCLOB. Again, see Appendix C of the Installation Guide for a list of recognized character sets.
- Enter passwords for the SYSTEM and SYS users. Remember that Oracle passwords are not case-sensitive, must start with a letter, and certain special characters are not allowed. I’ve heard that putting a period in the SYSTEM or SYS password will cause the installer to crash, but I’ve never tried it.
- If you want to be able to start up and shut down this database without actually logging on to the server, then choose Yes when asked, “Do you want to set the passwords for the internal users (dba and operator)?” This will cause the installer to create a password file external to the database. This is necessary if you will be controlling your database remotely from Enterprise Manager.
- Enter a password for the Net8 listener.
- Choose whether or not you want the installer to configure the multi-threaded server (MTS) on your database. MTS is typically not appropriate unless there will be large numbers of OLTP users.
- Choose Yes and OK repeatedly to accept the names and sizes that the installer is proposing for your database files. Actually these defaults are pretty awful, but it’s much easier to fix later.
- You might be asked which JDK drivers you wish to install. This is not relevant to database creation, so just choose them all and humor the installer.
- You will be asked if you would like to load the SQL*Plus help facility. You should probably choose No, as the HTML and PDF documentation is infinitely more useful. But if you do like the SQL*Plus help facility, feel free to install it.
- You will be asked if you would like to load the SQL*Plus demo tables. These include the venerable emp and dept tables in the scott/tiger schema. If you are new to Oracle or would like an easy schema to start testing with, then choose Yes. In most cases, however, you’ll chose No.
- The installer now creates the database and leaves it running.
- When the database creation is complete you’ll receive a message that the requested actions have been performed. Choose OK, and you will be returned to the Software Asset Manager screen.
- Exit the installer.
- The Oracle Installer probably told you to run root.sh again, but you definitely should not.
- Configure and run the Net8 listener. Edit $ORACLE_HOME/network/admin/listener.ora to suit your needs. You’ll need to fill in the ORACLE_SID. You might need to change the host name or IP. (In my case my server is multi-homed, but I only want the database to accept connections from the internal network.) You will probably want to leave the extproc settings as they are; extproc is part of the mechanism that allows PL/SQL to call out to procedures outside the database. You may set global_name to the host name. This will allow clients to connect to the database by specifying the host name as the Net8 alias—in this way clients don’t need to have a tnsnames.ora file. My listener.ora file looks like this:
- Prepare a tnsnames.ora file in $ORACLE_HOME/network/admin on the server and distribute it to all clients. Edit the default file to suit your needs. Fill in the ORACLE_SID. Change the host name or IP if needed. My tnsnames.ora file looks like this:
- Create bdump, cdump, and udump directories in $ORACLE_BASE/admin/$ORACLE_SID. The installer on Linux violates the OFA standard in the placement of these three directories, and we will move them to where they belong.
- Shut down the database.
- The default database was created with three control files all in one directory. Move two of the control files to other directories that are on separate physical devices.
- The default parameter file that the installer created for the database is deficient in several ways. You should move the three parameter files in $ORACLE_BASE/admin/$ORACLE_SID/pfile to a backup directory and create a new parameter file from scratch. Some of the things you should correct or improve upon are:
- You might want to merge the config.ora file contents into the init.ora file. Unless you are using Oracle Parallel Server, there is not a lot of value in spreading the parameters between two files.
- Update the control_files parameter to reflect the new control file locations.
- Update the background_dump_dest, user_dump_dest, and core_dump_dest parameters to comply with the OFA standard.
- Update the shared_pool_size, db_block_buffers, and sort_area_size parameters to reasonable values based on how much physical memory your server has available.
- Update the sessions and processes parameters based on how many concurrent sessions you anticipate.
- Restart the database so that the new parameter settings take effect.
- Tweak the default install so that the database will be usable for more than trivial tasks and will be capable of offering decent performance. While later sections of this tutorial will go into this in greater detail, here is an overview of some of the things you’ll probably want to do:
- Relocate data files to spread them over multiple physical devices.
- Enlarge the online redo logs—the default log size of 500 Kb is often too small. Since you can’t resize online redo logs, you’ll need to drop and recreate them. At the same time, you can give the logs OFA compliant names, and you can relocate them as appropriate to distribute I/O.
- Resize the temporary tablespace—the default size of 1 Mb won’t be big enough unless your database will be extremely small.
- Alter the temporary tablespace to give it a content type of TEMPORARY and appropriate default storage parameters. Also, make sure all users including SYS and SYSTEM have the TEMP tablespace designated as their temporary tablespace.
- Resize the rollback segment tablespace—the default size of 15 Mb won’t be big enough unless your database is somewhat small.
- Depending on the anticipated size of your database and the expected number and type of concurrent users, you may want to adjust the number of rollback segments and their storage parameters. At the very least, you’ll probably want to set the OPTIMAL storage parameter for each rollback segment except SYSTEM.
- Adjust the sizing and default storage of the TOOLS and USERS tablespaces as needed if you will be using these tablespaces.
- Create new tablespaces for holding application segments. Create separate tablespaces with data files on separate physical devices for tables and indexes. You may want to split your application segments into several tablespaces, based on object size, permanence, volatility, I/O volume, or any of a number of other criteria.
- Choose default storage parameters carefully for each application tablespace. One strategy I highly recommend is as follows:
- Set INITIAL to either 128k, 4m, or 128m, depending on the planned sizes of the objects to be placed in the tablespace.
- Set NEXT the same as INITIAL.
- Set MINEXTENTS to 1 and MAXEXTENTS to 1024.
- Set PCTINCREASE to 0.
- Create application roles if desired. Alternatively, you can use the default roles CONNECT, RESOURCE, and DBA.
- Create your application users that will own the application schemas. Set the default tablespace to one of your application tablespaces designated to hold tables, and set the temporary tablespace to TEMP. Assign quotas on all of the application tablespaces where the user will need to be able to create schema objects. (You can use the keyword UNLIMITED.) You should not set any quota on the temporary tablespace. Do not plan to create any application objects in the SYS or SYSTEM schemas, or store any application objects in the SYSTEM or TEMP tablespaces.
- Grant roles and/or system privileges to the application users. Note that if you grant the RESOURCE role to a user, that user will also receive the UNLIMITED TABLESPACE system privilege. This will let the user create objects in any tablespace they wish, regardless of quotas. I recommend you revoke UNLIMITED TABLESPACE from all users other than SYS.
cd $ORACLE_HOME/orainst
./orainst
We’ll walk through the installer prompts one at a time:
#
# Filename: Listener.ora
#
LISTENER =
(ADDRESS_LIST =
(ADDRESS= (PROTOCOL= IPC)(KEY= MYDB))
(ADDRESS= (PROTOCOL= IPC)(KEY= PNPKEY))
(ADDRESS= (PROTOCOL= TCP)(Host= 192.168.1.1)(Port= 1521))
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME= myhost.dbspecialists.com.)
(ORACLE_HOME= /u01/app/oracle/product/8.0.5)
(SID_NAME = MYDB)
)
(SID_DESC =
(SID_NAME = extproc)
(ORACLE_HOME = /u01/app/oracle/product/8.0.5)
(PROGRAM = extproc)
)
)
STARTUP_WAIT_TIME_LISTENER = 0
CONNECT_TIMEOUT_LISTENER = 10
TRACE_LEVEL_LISTENER = OFF
#
# Filename: Tnsnames.ora
#
extproc_connection_data =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = MYDB))
(CONNECT_DATA = (SID = extproc))
)
MYDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL= TCP)(Host= 192.168.1.1)(Port= 1521))
(CONNECT_DATA = (SID = MYDB))
)
MYDB_BEQ =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = BEQ)(PROGRAM = /u01/app/oracle/product/8.0.5)
(argv0 = oracleMYDB)
(args = '(DESCRIPTION = (LOCAL=YES)(ADDRESS=(PROTOCOL=BEQ)))')
(envs = 'ORACLE_HOME=/u01/app/oracle/product/8.0.5,ORACLE_SID=MYDB')
)
)
#
# initMYDB.ora
# ============
#
# Parameter file for MYDB database.
#
# config parameters
#
control_files = (/u02/oradata/MYDB/control01.ctl,
/u03/oradata/MYDB/control02.ctl,
/u04/oradata/MYDB/control03.ctl)
background_dump_dest = /u01/app/oracle/admin/MYDB/bdump
core_dump_dest = /u01/app/oracle/admin/MYDB/cdump
user_dump_dest = /u01/app/oracle/admin/MYDB/udump
db_block_size = 8192
db_files = 80
db_name = MYDB
compatible = 8.0.5
rollback_segments = (r01,r02,r03,r04)
#
# tuning parameters
#
shared_pool_size = 16000000
sort_area_size = 4096000
db_block_buffers = 2000
db_file_multiblock_read_count = 64
processes = 100
sessions = 100
log_checkpoint_interval = 10000
log_buffer = 163840
sequence_cache_entries = 100
sequence_cache_hash_buckets = 89
job_queue_processes = 2
max_dump_file_size = 10240 # limit trace file size
# to 5 Meg each
Complete the Server Configuration
These steps complete the configuration of your server for smooth Oracle operation. These steps could have been performed earlier, but are more straightforward if performed after a database has been created. In this section we will configure the server to start the database and Net8 listener automatically whenever the server is rebooted, change the oracle user’s login script to eliminate hardcoding, and create individual operating system accounts for each database user.
- Edit the /etc/oratab file to verify that the entry for your database is correct. All of the lines in the file should be comments (starting with a pound symbol) except for one. This one line should contain the name of your Oracle instance, its ORACLE_HOME, and a Y or N. A Y indicates that the database should be started automatically on server reboot, and an N indicates that it should not. The three fields should be separated by colons. A sample /etc/oratab file looks like this:
- Edit the login file for the oracle user to eliminate hardcodings and call the oraenv script to set the environment instead. The following should work with Bash, Bourne shell, or Korn shell:
- Create separate Linux accounts for DBAs and database users who will log onto the server. You should only log in as oracle when installing or patching software. The Linux accounts for DBAs should be members of the dba group, and other users should not be members of the dba group. Give each of these accounts a login file like oracle’s so that their environment initializes correctly when they log in.
- To make the database and Net8 listener start up automatically when the server reboots, you’ll need to create a dbora file in /etc/rc.d/init.d and link it to /etc/rc.d/rc3.d. You’ll need to do this as the root user. First create a file called dbora in /etc/rc.d/init.d as follows:
#
# /etc/oratab
# ===========
#
MYDB:/u01/app/oracle/product/8.0.5:Y
# Settings for Oracle environment
ORACLE_SID=MYDB
ORAENV_ASK=NO
export ORACLE_SID ORAENV_ASK
. oraenv
#!/bin/sh
ORA_HOME=/u01/app/oracle/product/8.0.5
ORA_OWNER=oracle
if [ ! -f $ORA_HOME/bin/dbstart -o ! -d $ORA_HOME ]
then
echo "Oracle startup: cannot start"
exit
fi
case "$1" in
'start') # Start the Oracle databases and Net8 listener
su - $ORA_OWNER -c "$ORA_HOME/bin/dbstart" &
su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl start" &
;;
'stop') # Stop the Oracle databases and Net8 listener
su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl stop" &
su - $ORA_OWNER -c "$ORA_HOME/bin/dbshut" &
;;
esac
Note that the spacing around the brackets shown here is different from what appears in the Oracle documentation.
After creating the dbora file, you need to link it to /etc/rc.d/rc3.d:
ln -s /etc/rc.d/init.d/dbora /etc/rc.d/rc0.d/K10dbora
ln -s /etc/rc.d/init.d/dbora /etc/rc.d/rc3.d/K10dbora
ln -s /etc/rc.d/init.d/dbora /etc/rc.d/rc3.d/S99dbora
Summary
This section of the tutorial walked you through all of the intricate details of getting Oracle up and running on the Linux platform. It may look complicated, but that’s only because this document goes down to a nitty gritty level of detail.
Please keep in mind, though, that the requirements are different for every Oracle implementation. I am extremely confident that if you follow these steps to install Oracle 8.0.5 Standard Edition on a server running Red Hat 5.2 Linux, then the process will go very smoothly for you. However, no single document can address every specific hardware configuration and every set of business needs. Please use these steps as a starting point to get Oracle up and running in your shop. Next, you’ll want to keep your database managable and make it perform—and the next sections in this tutorial will help you with that.
In this section we’ll cover the basics of managing an Oracle database. First we’ll look at starting and stopping the database. Then we’ll look at connectivity issues and general troubleshooting tactics. Then we’ll discuss how to organize schemas, tablespaces, and segments. Finally, we’ll have a basic overview of fault tolerance and backups.
Starting and Stopping
As we discussed in an earlier section, there are three steps to making an Oracle database accessible to applications. We call the whole process “starting” or “opening” the database. You most commonly start a database using Server Manager or Enterprise Manager. Server Manager is the lowest common denominator; it exists (in one form or another) in all Oracle environments and on all platforms.
On Unix platforms you invoke Server Manager by entering svrmgrl at the shell prompt. (The “l” stands for “line mode.” There once was a hideous version of Server Manager that tried to simulate a GUI in a character mode environment. Thankfully, this was abandoned.)
You should set your Oracle environment before starting Server Manager. ORACLE_HOME and the Oracle directories included in PATH and LD_LIBRARY_PATH will allow Oracle to locate the proper binaries and shared libraries. ORACLE_SID and TWO_TASK will tell Oracle which instance you wish to access.
In Server Manager you’ll need to authenticate yourself before you can do anything useful. If your database is already open to applications, you can simply use the CONNECT command to connect to an instance. In this case you’ll need to provide a username and password, and the instance will check these against the list of users held in the data dictionary of the database.
But what do you do if the database isn’t open yet? With the database closed, there is no access to the data dictionary for authentication. There are two options: operating system authentication or a password file external to the database.
Shops that run Oracle on Unix and do not use Enterprise Manager tend to take the operating system authentication route. In this scenario, the DBA logs on to the database server using a Unix account that is a member of a privileged group, typically called “dba”. In Server Manager, the DBA gives the special CONNECT command CONNECT / AS SYSDBA. This tells Server Manager to have the operating system authenticate the user for the purposes of starting up or shutting down the database. Back in Oracle V6 the command was CONNECT INTERNAL. You’ll still hear old-time DBAs refer to CONNECT INTERNAL and it does still work, but Oracle is promising to de-support it soon. So go with CONNECT / AS SYSDBA instead.
Shops that run Oracle on less secure operating systems, or use Enterprise Manager for centralized database startup and shutdown, cannot rely on the operating system for authentication—because the DBA never logs onto the database server at all. In these situations a password file is used. As part of database creation, the orapwd utility is used to generate a file external to the database that contains passwords. In Server Manager the DBA connects with the command CONNECT username/password AS SYSDBA. Server Manager then checks this information against the contents of the password file.
Once you have performed the appropriate CONNECT command, Server Manager will respond with “Connected to an idle instance.” This basically means that you are authenticated, but there is no instance running. You may now start up the database using the STARTUP command. Typically you won’t use any of the options that come with STARTUP, but it’s good to know the most common options because they are useful in certain situations. Figure 1 shows the most common options.
| Command | Description |
| STARTUP | Takes a database all the way from being closed to being fully accessible to applications (creates an instance, mounts the database, opens the database) |
| STARTUP NOMOUNT | Creates an instance, but does not mount or open the database |
| STARTUP MOUNT | Creates an instance and mounts the database, but does not open it |
| STARTUP RESTRICT | Same as STARTUP, but only allows database users with the “restricted session” system privilege to access the database |
| STARTUP PFILE=’filename’ | Same as STARTUP, but uses the specified parameter file to configure the instance, instead of the usual initSID.ora |
Figure 1: Common Server Manager commands for opening an Oracle database
Shutting down an Oracle database is equally easy. First, authenticate yourself in Server Manager just like before. Then issue the desired shutdown command. Figure 2 shows the different options available. It’s very common to use the first two. Hopefully you won’t need the third.
| Command | Description |
| SHUTDOWN | Prevents any new logins to the database, and shuts the database down cleanly after the last existing session logs out |
| SHUTDOWN IMMEDIATE | Kills all existing sessions and shuts the database down cleanly |
| SHUTDOWN ABORT | Abruptly kills all Oracle processes and frees up all system resources used by the instance |
Figure 2: Server Manager commands for closing an Oracle database
The SHUTDOWN command without any options (also called a “normal shutdown”) could potentially hang indefinitely, because it only takes one user who fails to log out to hold things up. An immediate shutdown also may not be so immediate, because Oracle will first kill all sessions and clean up after them. An abort shutdown is always instantaneous because Oracle indiscriminantly kills everything off. You shouldn’t use SHUTDOWN ABORT unless you really need to, because it basically crashes the instance and forces Oracle to perform crash recovery the next time you open the database. (The crash recovery is automatic, but could take a while if you aborted the instance in the middle of a huge transaction.)
If you choose to use Enterprise Manager instead of Server Manager, you will need to have a password file external to the database and you’ll also need to have Net8 configured on both the Enterprise Manager client and the database server. Enterprise Manager is a GUI tool allowing you to point and click your way toward doing DBA tasks. Although the user interface is substantially different from Server Manager’s command line interface, the concepts and features available are basically the same.
If all of your database applications will run on the same physical server as the Oracle databases they access, then opening the database is the only thing you need to do in order for your applications to be able to access the database. However, it’s more typical these days to distribute processing among networked servers. If any database applications will be running on separate servers from the database server, you will need to start the Net8 listener before these applications can access the database.
The Net8 listener runs on the same server as the database. You only need to run one Net8 listener per physical server, even if you have multiple databases running on the server. You need to configure the Net8 listener before you can start it. The most common way to configure Net8 is to edit a listener.ora file manually. We did this while setting up the default database in the previous section. Alternatively, Oracle does offer GUI network configuration tools that will generate the configuration files for you. In smaller shops, it will probably be easier to just edit the listener.ora file manually.
The configuration files for Net8 tell the listener where and with which protocols to listen for connection requests from clients, and also which databases are available on the server. On Unix it is most common to have Net8 listen on port 1521 for connection requests using TCP/IP protocol. However, many different protocols are supported, and protocol interchange is even possible.
You control the Net8 listener with the lsnrctl command. You may enter lsnrctl on the command line by itself, in which case you will be prompted to enter a listener command. Alternatively, you may enter a listener command directly on the command line after the lsnrctl. Figure 3 shows the most common listener control commands.
| Command | Description |
| START | Starts the Net8 listener |
| STOP | Stops the Net8 listener |
| RELOAD | Reads the configuration files anew |
| STATUS | Shows the status of the Net8 Listener |
| SERVICES | Lists the listener services currently running, along with connection statistics for each service |
Figure 3: Common commands for controlling the Net8 listener
The Net8 listener’s primary function is to establish a connection between a database application (typically running on a remote server) and an Oracle instance (typically running on the local server). Once the Net8 listener has established a connection between the application and the instance, the two communicate directly without involving the Net8 listener. The key point to note is that stopping and restarting the Net8 listener while a database is open will not disrupt existing database sessions. While the listener is down applications will not be able to connect, but existing connections will not be affected.
In most situations, you want the Oracle databases to be open and the Net8 listeners to be running at all times. Typically, these services are started automatically when the server starts up. We saw how to do this when we set up a default database in the previous section.
Connectivity and Troubleshooting
Each Oracle instance maintains a log known as the “alert log.” This is a plain text file called alert_SID.log, where SID is the name of the instance. It resides in the directory specified by the background_dump_dest initialization parameter. Oracle writes to this log when an instance is started or stopped, when a significant database change is made (such as adding a new tablespace), and when something significantly goes wrong that affects the instance or database as a whole (such as a disk failure).
Whenever you have doubts about what’s going on in your instance, the alert log is a good place to look. It’s also a good idea to review the alert log periodically for problems you might not be aware of. You can archive or compress your alert log if it gets too big. This can be done without shutting down the instance—the instance will create a new alert log if the old one is missing.
When something bad happens in a particular Oracle daemon or server process, a trace file will be dumped and a message will be written to the alert log. Trace files for daemons are written to the background_dump_dest, while trace files for server processes are written to the user_dump_dest. Trace files are plain text, although the information sometimes is not very intelligible unless you work in Oracle’s Worldwide Technical Support division.
When an application is unable to connect to a database due to a networking or connectivity problem, the Net8 client called by the application will write a sqlnet.log file. This file will be located on the client (the machine running the application) in the working directory of the application trying to connect. The file will be plain text, containing diagnostic information you can use to troubleshoot the problem.
Some of the most common Net8 connectivity problems are as follows:
ORA-12154: TNS:could not resolve service name: This means that the Net8 client (running on the application’s server) was unable to find what server and database the Net8 alias provided by the application maps to. You should check the tnsnames.ora file on the client to make sure it includes the Net8 alias you wish to use. Also, make sure the tnsnames.ora file is in the right location—typically $ORACLE_HOME/network/admin. You should be able to connect to the database immediately after fixing the problem with tnsnames.ora.
ORA-12203: TNS:unable to connect to destination: This indicates a networking connectivity problem between the server running the database application and the server running the database. If you tried to ping the database server from the client, you would probably find 100% packet loss.
ORA-12224: TNS:no listener: The Net8 listener on the database server is not running. Start the listener and try again.
ORA-01034: ORACLE not available: This indicates that the database is not open. Start the database and try again.
ORA-12505: TNS:listener could not resolve SID given in connect descriptor: This indicates one of two possible configuration problems. Either the tnsnames.ora file on the client contains an incorrect instance name (SID) in the CONNECT_DATA clause, or the Net8 listener running on the database server is not aware of the specified instance. You’ll need to edit either the tnsnames.ora file on the client or the listener.ora file on the database server and try again. If you edit the listener.ora file, you’ll need to restart or reload the Net8 listener on the database server.
Schemas, Tablespaces, and Segments
Creating database users and schemas is an important DBA responsibility—both the organizational planning and the actual creating and dropping of the accounts.
How you divide your end user population into database users and how you separate your database objects into individual schemas will have huge impacts on the security, maintainability, and scalability of your system. You should come up with a coherent plan before creating a single database user.
For simple applications, DBAs will typically create one Oracle user as the “application owner.” All of the database objects to be used by the application—such as tables, indexes, and stored procedures—are created in this user’s schema. Complicated applications are usually divided into separate modules or components, with a separate owner user created for each.
In applications where the database will authenticate the end users directly, a separate Oracle user is created for each end user. The schemas for these users will typically be empty except for synonyms allowing the user to reference database objects in the application owner schema. Appropriate privileges on the application owner’s schema objects are granted to the end user database users, typically through database roles.
In applications where the application will authenticate the end users, one “application runtime” Oracle user is usually created. The application always accesses the database via this generic database user. A table of users in the application owner schema or some other mechanism is used to allow the application to authenticate end users. As with the individual end user schemas, the generic schema will typically be empty except for synonyms.
Client/server applications with a small to medium number of end users are typically developed using the individual database user model, as are applications that require very high security. Internet applications are typically developed using the generic application user model, due to the potentially huge user community and the expense of having Oracle create new sessions and authenticate users on every HTTP request.
Once a strategy has been devised for users and schemas, a plan should be developed for the tablespaces that will make up the physical storage of the database. Tablespace planning is more straightforward than user and schema planning.
Every Oracle database should have seven classes of tablespaces. The default database created earlier in this tutorial will have the first five. It’s up to you to create the other two, and possibly enhance some or all of the first five. Figure 4 lists these seven types of tablespaces.
| Tablespace Contents | Name in Default Database |
| Data dictionary and Oracle internals | SYSTEM |
| Rollback segments | RBS |
| Temporary segments | TEMP |
| Oracle and third party tools | TOOLS |
| Unclassified user segments | USERS |
| Application tables | n/a |
| Application indexes | n/a |
Figure 4: Seven types of tablespaces
Every Oracle database must have a SYSTEM tablespace for the data dictionary and internals. You’ll never need to create an additional tablespace in this class. Most database are fine with one tablespace each for rollback segments and temporary segments. On larger, high concurrency databases, however you may choose to add additional tablespaces for rollback and temporary segments in order to distribute I/O load effectively.
Tablespaces for tools and unclassified user segments are not always necessary, especially if you are not installing any Oracle or third party tools that create database schema objects. However, it is definitely a good idea to have a USERS tablespace to serve as a general dumping ground for those segments that will only be around for a little while or you aren’t sure where to put.
The remaining two tablespace classes are where you’ll want to focus your planning. Choosing how to distribute your application segments among tablespaces will strongly impact performance, scalability, and manageability of your database.
Rule number one is to always store tables and indexes in separate tablespaces. One of the most common ways to access data in a query is via an index scan followed by a fetch from the table by rowid. If a table is stored in the same tablespace as its indexes, then it is likely that both will be stored on the same physical device. This would lead to fierce I/O contention every time the table is accessed via its index.
From there, the rules get more vague. In general, it is a good idea to use separate tablespaces for separate applications or schemas. This will allow you to perform maintenance on one application’s data without impacting other applications using the same database. It’s also a good idea to separate highly volatile segments or segments temporary in nature (such as a temporary table you create and drop whenever a report is run) from the more static or permanent data. Further, large tables and indexes (say, over 500 Mb or 1 Gb) should be given exclusive tablespaces of their own.
Small databases for simple applications will often have just two application tablespaces—one to hold tables and one to hold indexes. It’s not uncommon, however, for large databases serving complex applications to have 50 or more tablespaces. A greater number of tablespaces may seem like more of a management overhead, but the ability to isolate outages and manage free space allocation on a more granular level become increasingly important as the size and complexity of a database grows.
Every segment in every tablespace of an Oracle database has a wealth of individual sizing parameters, collectively called “storage parameters.” Figure 5 lists the different parameters.
| Name | Purpose |
| initial | Size of the initial extent |
| next | Size of the next extent to be allocated |
| pctincrease | Factor by which to increase the size of each successive next extent |
| minextents | Initial number of extents to allocate |
| maxextents | Maximum number of extents the segment is allowed to have |
| pctfree | Space to leave empty in each new block to allow for updates that cause data to take up more space |
| pctused | How much space in a block must be free before additional data will be inserted |
Figure 5: Segment storage parameters
The degree to which you can fine tune the storage of each individual segment in the database can boggle the mind. There are a number of old wives’ tales and urban legends floating around about the best way to set storage parameters. The more complex strategies are based on limitations of older versions of Oracle which have long since been overcome. Sadly, some are based on plain old misinformation.
The segment sizing strategy I use is based on heavy-duty research performed internally at Oracle Corporation. This strategy also happens to be the simplest around. I mentioned it briefly in the steps for creating a default database. The underlying principles are as follows:
- If all extents in a tablespace are the same size, the free space will never be fragmented.
- Oracle’s extent management overhead is minimal as long as segments have fewer than 1024 extents.
- Storage parameters are easier to administer if all segments in a tablespace have the same settings.
The strategy is to set the default storage parameters for each application tablespace, and let all application segments inherit their storage parameters from the defaults for the tablespace to which they are assigned. The default storage parameters for each tablespace should be set as follows:
Set INITIAL to either 128k, 4m, or 128m, depending on the planned sizes of the objects to be placed in the tablespace.
b. Set NEXT the same as INITIAL.
c. Set MINEXTENTS to 1 and MAXEXTENTS to 1024.
d. Set PCTINCREASE to 0.
Fault Tolerance and Backups
If the data you will be storing in your database has any value to you whatsoever, you’ll want to take steps to make your database fault tolerant. You’ll also want to back up the data in your database so that you are protected against disasters—both of the hardware failure and user error variety.
To make your database fault tolerant, you should eliminate single points of failure wherever possible. The control file and online redo log are critical to an Oracle database—the entire database will fail with the loss of either. Therefore, you should always have redundant copies of your control file and online redo log as an absolute minimum.
You can use RAID to keep redundant copies of the files, or you can have Oracle perform software mirroring (where Oracle writes identical blocks to multiple files). A benefit of having Oracle mirror these files for you is that you’ll be covered if somebody accidentally deletes a file from the file system. (Unless, of course, they delete all mirrored copies!) Software mirroring of the control file incurs very little I/O or overhead. Mirroring online redo log files can cause a significant increase in I/O on update-intensive systems, but disk storage is cheap these days.
RAID is common enough these days that you should consider using it for all of the data files in your database as well. But you should not think that RAID eliminates the need for a backup and recovery strategy. It is still possible for RAID systems to fail, and RAID won’t help you in the case of user error or a catostrophic event.
Backup strategies for Oracle can be varied and complex. Heavy tomes have been published on the subject. Figure 6 gives a very high level view (say, at 40,000 feet or more) of some of the options available.
| Method | Description | How To |
| Export | Write database contents to an operating system file | Use the exp utility |
| Cold Backup | Capture a point-in-time snapshot of the entire database | Shut down the database cleanly and use operating system commands to copy the control file, online redo log, and all data files |
| Hot Backup | Capture a rolling snapshot of individual data files while the database is in use | Use operating system commands to copy data files while the database is open, informing the instance along the way so that additional logging measures can be used to ensure a viable backup |
| Recovery Manager | Oracle’s new tool to simplify hot and cold backups | Use the rman utility |
Figure 6: High level overview of Oracle database backup options
Here are a few pearls of wisdom to start you along the path toward a proper backup and recovery strategy:
- If you’ve shut the database down cleanly, you can use ordinary operating system commands to take a cold backup of the database. For the backup to be usable, you will need to capture one copy of the control file, one copy of each online redo log file, and every data file. All files need to be captured while the database is closed, and without opening the database in between copying files
- It is very easy to use the exp utility to export the contents of a database to a single operating system file. You can use an export file to reload an empty database or migrate a database between Oracle versions or operating system platforms. Understand, however, that this technique is not very scalable. It gets resource intensive and slow if your database is bigger than a few Gb in size.
- It’s easy to restore individual schema objects from an export file. Hot and cold backups, on the other hand, are typically used to restore an entire database and not individual schema objects.
- In order to take hot backups, your database needs to be running in archivelog mode. This requires that you manage the archived redo log.
- Unless your database is running in archivelog mode and you submit the correct commands to the instance before and after copying each tablespace, you cannot copy data files while the database is open and expect them to be viable.
- An export file only allows you to recover a database to the point in time of the export. (The point in time will be fuzzy unless the consistent=y export option is used.) A cold backup of a database not running in archivelog mode also only allows you to recover the database to one point in time. Only a hot or a cold backup taken from a database running in archivelog mode allows you to recover the database from a backup and “roll it forward” up to the point of failure.
- In order to roll a hot or cold backup forward to the point of failure, you’ll need all archived redo log files since the beginning of the backup of the oldest file in the backup set. If an archived redo log is missing from the sequence, you will not be able to roll forward past that point.
Please take the fifth bullet item very seriously. Because of the way Oracle optimizes writes, you can never assume that a file is quiesced. Even if there is no activity on the database, you cannot assume that all data files are in a consistent state. Oracle tracks the state of each data file in both the control file and each data file’s header.
The Oracle database is a phenomenally tunable and configurable product. The difference in performance between a vanilla install and a well tuned system can be amazing. It’s not unheard of to reduce a report’s running time from 18 hours to ten minutes by changing one line of code. In this section of the tutorial we’ll take a high level look at several areas where you can optimize your Oracle system. In each area we’ll take a peek at the most common tuning issues. To learn more about each of these tuning areas, consult the Oracle8 tuning manual.
Tuning Database Access within an Application
The highest impact tuning you can do in an Oracle system is application tuning. Optimize the queries and application logic to get the most out of Oracle. Oracle SQL and PL/SQL are very powerful, and the Oracle engine is very capable when it comes to processing and sorting large volumes of data. When SQL or PL/SQL offers a certain functionality your application needs, you should use Oracle’s native capabilities instead of engineering your own application logic to do the same thing.
The v$sqlarea view in every Oracle database allows DBAs to see the statements that are currently cached inside the SGA. Figure 1 shows a simple SQL*Plus query you can use to zero in on resource-intensive statements within your application. A “logical read” is a request for a block of data from a data file. The request may result in a cache hit (if the block is already in the buffer cache inside the SGA) or a physical read from disk. “Execs” is the number of times the statement has been executed since it entered the SGA.
SQL> COLUMN sql_text FORMAT a40 SQL> SELECT disk_reads phys_rds, buffer_gets logicl_rds, 2 executions execs, sql_text 3 FROM v$sqlarea 4* ORDER BY buffer_gets; PHYS_RDS LOGICL_RDS EXECS SQL_TEXT ---------- ---------- ---------- ---------------------------------------- 0 0 15 BEGIN dbms_sql.variable_value@REMOTE (:b1, :b2, :b3); END; ... 351 29276 7 SELECT RPAD(SUBSTR("A1"."OWNER",1,15),15 ),RPAD(SUBSTR("A1"."TABLE_NAME",1,30),30 ),RPAD(SUBSTR(NVL(TO_CHAR("A1"."LAST_ANA LYZED",'Month dd, yyyy'),'None'),1,20),2 0) FROM "SYS"."DBA_TABLES" "A1" WHERE "A 1"."OWNER"<>'SYS' AND "A1"."OWNER"<>'SYS TEM' AND ("A1"."NUM_ROWS" IS NULL OR "A1 "."LAST_ANALYZED"<SYSDATE-30) ORDER BY " A1"."OWNER","A1"."TABLE_NAME" 81 79994 3734 begin run_requests (SYSDATE); end; 614 rows selected. SQL>
Figure 1: Looking for resource-intensive statements from within SQL*Plus
When the same statement is executed multiple times, Oracle only needs to parse it once. The parsed representation, including execution plan, will be cached in the SGA’s shared SQL area. This can dramatically cut down processing time when applications execute huge numbers of simple queries.
A key to maximizing the benefit of the shared SQL area is to use bind variables in your SQL statements wherever possible. Most database APIs these days support bind variables, although the syntax varies from one to the next. JDBC, Perl DBI/DBD, and Pro*C all support bind variables.
Depending on the API involved, each SQL statement executed in an application can result in multiple network roundtrips between the application’s server and the database server. You can often reduce network traffic and boost performance by pushing logic into the database. For queries, there could be as many network roundtrips as rows fetched if you don’t use arrays for fetching multiple rows at once.
Sizing the SGA
You can boost database performance across the board by sizing the SGA optimally. If you make the SGA too small, your cache hit ratios will be low and you’ll have more physical I/O and parsing overhead than necessary. If the SGA is too big, the system may run low on physical memory and resort to swapping.
The SGA is composed of many different memory structures and areas. You can query v$sgastat to see the names and sizes of the different components that make up the SGA. Oracle database software is optimized on the assumption that the entire SGA for an instance is always in physical memory at all times. It is imperative, therefore, that you not make the SGA so big that physical memory gets exhausted.
The sizing of many of an SGA’s components are either directly or indirectly configurable via the parameter file for the instance. The two parameters that will have the greatest impact on the overall size of the SGA are db_block_buffers and shared_pool_size.
The db_block_buffers parameter specifies the size of the buffer cache in the SGA, used to hold the most recently used blocks from the data files of the database. The actual size of the buffer cache will be equal to the block size for the database times the number of buffers. Figure 2 shows a query in SQL*Plus that will determine the buffer cache hit ratio cumulative since the instance was started. Ratios vary based on the application, but hit ratios in the buffer cache are usually over 90% on well tuned systems. You can also set the db_block_lru_statistics and db_block_lru_extended_statistics parameters to cause the instance to maintain statistics about how the cache hit ratio would have differed if there were fewer or more buffers.
SQL> SELECT (S1.value + S2.value - S3.value) 2 / (S1.value + S2.value) hit_ratio 3 FROM v$sysstat S1, v$sysstat S2, v$sysstat S3 4 WHERE S1.name = 'db block gets' 5 AND S2.name = 'consistent gets' 6* AND S3.name = 'physical reads'; HIT_RATIO ---------- .986270344 SQL>
Figure 2: Determining the buffer cache hit ratio
The shared_pool_size parameter determines the size of the shared SQL area in the SGA. The shared SQL area is where the instance caches the parsed representations of the most recently used SQL statements. Also held inside the shared pool is the dictionary cache, a separate cache that holds the recently used portions of the data dictionary.
The optimal setting for shared_pool_size varies greatly from one situation to the next. The amount of PL/SQL code stored in the database, the degree to which the application uses bind variables, and the general complexity and size of the application will all influence the demands put on the shared SQL area. A simple application using bind variables and no stored procedures or database triggers may only need a shared_pool_size of 8 Mb, but a heavily used application which does not use bind variables could conceivably suffer performance degraation with a shared_pool_size under 64 Mb.
Balancing Disk I/O
Because I/O involves access to a physical storage device which is slow compared to CPU speed, many databases are often I/O bound. Once you’ve sized the SGA optimally to minimize physical I/Os, you should strive to balance those physical I/Os across multiple devices so that no one device is a bottleneck and so that contention is minimized.
You may query v$filestat and SYS.dba_data_files in order to find out how many physical reads and writes have taken place in each data file since the instance was started. If you have timed statistics enabled for the instance (either via the initialization parameter or the ALTER SYSTEM SET TIMED_STATISTICS = TRUE command) you can also see the time spent to perform these I/Os.
You can use this information to spread data files across disks in a way to distribute the I/O evenly. If one data file has more than its share of the activity, you may want to split its tablespace into multiple tablespaces and distribute the “hot” segments across multiple files on different devices.
You should also distribute data files across physical devices in a manner to avoid contention. Oracle very often needs to read from a table and its indexes in alternating fashion, so placing the table and index on the same physical device could cause severe contention. For this reason, tables should always be separated from their indexes.
Oracle also writes to the online redo log whenever a transaction commits. Placing the online redo log files on the same device as heavily used tables or indexes can cause contention. Online redo log files can even contend with each other if Oracle is archiving one redo log while the next one is being written to by current transaction activity. For this reason online redo logs should be spread across two or more physical devices in a ping pong fashion with no data files sharing these devices.
Managing Database Connections
Connecting to an Oracle instance that is running in the default architecture can be somewhat expensive. First, Oracle must fork a new process. Then the process must attach itself to the SGA and authenticate the user. The stateless nature of HTTP lends itself to an environment with a huge number of incredibly short database sessions. If each of these sessions involved a brand new connection, the overhead of connecting and disconnecting would be severe.
For this reason, many application server architectures and middleware products support connection caching and pooling. Net8 also offers new features for multiplexing and pooling database connections. Many shops using Oracle for internet-driven applications have chosen to develop their own database connection management servers in Java or Perl. Whenever using an architecture where database connections will be reused, it is important to ensure that each user of a database connection properly ends its transactions and frees up all system resources used. Also consider how security and authentication will be performed.
Tuning Sorts
Sorts are necessary when building an index or when querying data with a DISTINCT, GROUP BY, or ORDER BY clause. Oracle has a very powerful and efficient sort algorithm. All sorts begin in memory. If the amount of memory required for a sort reaches a certain threshold, then Oracle splits the sort in two, writing half to a temporary segment in a temporary tablespace while performing the other half in memory. Alfter both halves are sorted, they are merged together. This “divide and conquer” approach can be repeated infinitely many times so that the hugest sort can still be performed in a bounded amount of memory.
The sort_area_size initialization parameter determines the maximum amount of memory one database session should ever use for sorting. This establishes the threshold at which sorts will involve temporary segments and writes to disk. Unfortunately, the default setting for sort_area_size in the default database is 65536 bytes. You should definitely bump this parameter to at least 1 Mb, and probably more. How high you go will depend on the amount of physical memory on the database server and how many concurrent sessions will typically be performing sorts at once.
You can override the sort_area_size setting for an individual session with the ALTER SESSION SET sort_area_size command. This is handy for speeding up huge indexing operations.
When sorts are too big to be performed in memory, you want the disk writes to be as efficient as possible. It’s important to set each database user’s temporary tablespace designation correctly. Never use the SYSTEM tablespace as a temporary tablespace. Most databases will have one tablespace used only for temporary segments, although you may wish to have several such tablespaces if many users will be doing frequent large sorts and you want to distribute I/O widely.
All tablespaces used for temporary segments should have their content type set to TEMPORARY instead of the default of PERMANENT. This causes Oracle to use an enhanced space allocation mechanism that is optimized for the frequent creation and destruction nature of temporary segments.
In addition to making sorts as efficient as possible, you should also tune application logic to minimize the amount of sorting required. This includes examining join conditions carefully to avoid Cartesian products and avoiding overuse of the DISTINCT keyword.
Configuring Rollback Segments
Rollback segments are among the most elusive and misunderstood structures in an Oracle database. Rollback segments hold “undo” information so that a transaction may be rolled back. But they also allow a database session to query a table in a read-consistent way even while another database session is updating the very same table.
Rollback segments are used like circular buffers, except that they can expand as well. When Oracle reaches the end of a rollback segment, it sees if any of the earlier blocks in the segment can be reused. If not, the segment extends. When a transaction commits, all of the blocks in the rollback segment allocated to that one transaction are marked as no longer being needed and available for reuse.
When a transaction begins, Oracle assigns it to one rollback segment, probably the rollback segment currently hosting the fewest transactions. Optionally, a transaction may specify which rollback segment it wants to use. Multiple transactions can share one rollback segment, but if too many transactions share one rollback segment, contention for the rollback segment header can occur.
The optimal number of rollback segments for a database depends on the typical number of concurrent transactions. Note that a session performing queries only does not constitute a transaction and does not get assigned to a rollback segment. It’s reasonable to expect a few transactions to share one rollback segment, but you shouldn’t have 50 concurrent transactions share just two rollback segments.
The optimal size of rollback segments varies depending on the application. OLTP and web-based e-commerce applications tend to have many small transactions. This situation is suited to many small rollback segments in order to minimize rollback segment header contention. Batch applications are usually more suited to a smaller number of large rollback segments, because the system will have few concurrent transactions but each transaction may be large.
In reality, most systems have a mix of both large and small transactions. You should create enough rollback segments so that header contention is avoided. Rollback segments should be moderate in size, but should be able to grow when large transactions occur. It may be a good idea to set the OPTIMAL storage parameter for rollback segments so that if one segment swells enormously due to a long transaction, it will be able to shrink and release storage for use by other rollback segments in the future.
There is a lot of great information about Oracle technology available. You can find much of it on the internet for free.
Oracle Documentation
Don’t overlook the Oracle documentation CD that comes with all Oracle software. It can be overwhelming, but there is a lot of excellent reference information there. Figure 1 gives you a roadmap to help you get started.
| What You Are Looking For | Oracle Manual | Type |
| General Oracle concepts | Oracle8 Concepts | Generic |
| How to install Oracle | Oracle8 Installation Guide | Platform specific |
| Details about Oracle particular to your platform | Oracle8 Administrator’s Guide | Platform specific |
| General DBA duties | Oracle8 Administrator’s Guide | Generic |
| init.ora parameters and data dictionary views | Oracle8 Reference | Generic |
| How to use the export, import, and SQL*Loader utilities | Oracle8 Utilities Guide | Generic |
| Performance and tuning | Oracle8 Tuning Guide | Generic |
| SQL language syntax | Oracle8 SQL Reference | Generic |
| PL/SQL language syntax | Oracle8 PL/SQL User’s Guide and Reference | Generic |
| How to use SQL*Plus | SQL*Plus User’s Guide | Generic |
Figure 1: Where to look for answers in the Oracle documentation set
Other Publications
There are numerous books in print covering all facets of Oracle technology. I like the O’Reilly books the best. You can view their offerings at http://oracle.oreilly.com. Kevin Loney has written some of the Oracle Press books which are highly regarded. You can read Kevin’s plugs about his books at http://www.kevinloney.com. You can also learn more about all of the Oracle Press books at http://www.osborne.com/oracle/index.htm.
Oracle Technology Network
The Oracle Technology Network at http://technet.oracle.com has a wealth of information specifically targeted for developers. Registering to become a member gives you instant free access to nearly all online documentation, a collection of white papers and technical presentations, discussion forums, and free developer licenses for certain Oracle products.
The discussion forums are also accessible at http://www.dejanews.com.
Oracle User Groups
You can get a huge amount of information by joining an Oracle user group. Different groups meet monthly, quarterly, or annually. At these meetings you can attend technical sessions presented by other Oracle users and swap tips and techniques with other people in similar situations to you. Some of these groups also maintain a repository of tips and techniques on their websites.
The International Oracle User Group—Americas meets annually and has a website at http://www.ioug.org. The Northern California Oracle User Group meets quarterly in the San Francisco Bay Area. Their website is http://www.nocoug.org.
Free Web Resources
You can also find a lot of useful information and a helpful online service at my company’s website http://www.dbspecialists.com. You’ll find handy scripts for Oracle DBAs at http://www.dbspecialists.com/download.html. There are also white papers of a very technical “nuts and bolts” nature available at http://www.dbspecialists.com/present.html. Finally, and probably most unique, you can sign up for Database Rx at http://www.dbspecialists.com/dbrx.html. Database Rx is a free web-based service that will examine your Oracle database and give you expert tuning advice and recommendations. Database Rx can also monitor your databases on a daily basis and send you email when it discovers a potential problem you should investigate further.
The handy scripts, white papers, and use of Database Rx are all free of charge.
* * *
Roger Schrag has been an Oracle DBA and application architect for over ten years, starting out at Oracle Corporation on the Oracle Financials development team. He is the founder of Database Specialists, Inc., a consulting group specializing in business solutions based on Oracle technology. You can visit Database Specialists on the web at http://www.dbspecialists.com, and you can reach Roger by calling +1.415.344.0500 or via email at rschrag@dbspecialists.com.