2.3 Types of Database Files The database is comprised of several different types of files that serve different functions within the system. We describe each component in the following sections. 2.3.1 Tablespace and Tablespace Datafiles A tablespace is an internal object used to represent a physical storage area. It is the logical name for an entity used to access the operating system files called datafiles . In this book, when we speak of a tablespace we will mean the logical tablespace name as well as the files assigned to it. Oracle uses a tablespace to house the following different kinds of structures: -
Database object structureslike tables, indexes, packages, procedures, triggers, etc. -
Rollback segments -
Temporary sort segments A tablespace must have one or more datafiles assigned to it; otherwise , it cannot be created. These files may be on one or more disks. When you create a table or index, you will specify the tablespace in which the object is to be created. If this is not done, then your default tablespace will be used. The STORAGE clause used in the CREATE statement will determine how much space is allocated from the tablespace. Several examples of STORAGE clauses appear in the following sections. When this initial allocation is filled, more space will be assigned, this time according to the NEXT parameter in the STORAGE clause. The DBA monitors space utilization and, as the tablespace becomes full, will need to add more datafiles or rebuild the tablespace to combine the current datafiles and increase the allocation of space. Figure 2.2 shows the layout of a tablespace's datafiles on more than one disk. Figure 2.2. A tablespace datafiles layout  2.3.1.1 Tablespace names Tablespace names are usually chosen to represent the type of data being stored. The name is usually generic in nature. For example, in a small system there may be a DATA_01 tablespace and an INDEX_01 tablespace. Larger systems may use somewhat more specific names such as AR_01, AR_02, PER_01, and so on. While the examples shown here follow a name and number convention, there is nothing to stop you from using names such as D, I, PERSONNEL, ACCOUNTS_RECEIVABLE, and so on. The only restrictions are that tablespace names: -
Cannot usually be more than 30 characters -
Cannot contain blanks or special characters -
Cannot use certain reserved words Some of the obvious reserved words are CREATE, INSERT, and UPDATE. The list is quite lengthy, however, and we suggest that you look in the Oracle DBA manuals for a complete list. You might want to use tablespace names that represent the form of data they contain. For example, in an Financials application database, you might have tablespaces named PO_DAT or GL_IDX, for Purchase Order and General Ledger applications, respectively. If you find that another form of data needs to be stored in an application, you can always create another tablespace with an equally meaningful nameto store the new data. Another approach is to use more general names as mentioned previously (DATA_01, etc.), particularly if you will be hosting more than one type of application in the database. This removes the arbitrary restriction that only Purchase Order data can be stored in the PO_DAT tablespace. Since each of the authors feels that one method is preferable to the other, we recommend that you let your design team make the decision about the naming convention which will be used. We recommend that, whenever possible, you keep the files for data tablespaces on different disks from their index tablespaces and, preferably, on different controllers for better performance. 2.3.1.2 Creating a tablespace A tablespace is created with a CREATE TABLESPACE statement such as the following: SQL> CREATE TABLESPACE base_accounting 2 DATAFILE '/usr06/oradata/prod/data/baseacct01.dbf' SIZE 5M REUSE, 3 DATAFILE '/usr07/oradata/prod/data/baseacct02.dbf' SIZE 5M REUSE, 4 DATAFILE '/usr08/oradata/prod/data/baseacct03.dbf' SIZE 5M REUSE, 5 DATAFILE '/usr09/oradata/prod/data/baseacct04.dbf' SIZE 5M REUSE, 6 DATAFILE '/usr06/oradata/prod/data/baseacct05.dbf' SIZE 5M REUSE, 7 DATAFILE '/usr07/oradata/prod/data/baseacct06.dbf' SIZE 5M REUSE, 8 DATAFILE '/usr08/oradata/prod/data/baseacct07.dbf' SIZE 5M REUSE, 9 DATAFILE '/usr09/oradata/prod/data/baseacct08.dbf' SIZE 5M REUSE 10 ; tablespace created In this example, the "base_accounting" tablespace has been created.  | In this example, the files that constitute the tablespace are on different disksa fact that is transparent to the user . | | 2.3.1.3 Creating a table within a tablespace When you are creating a table, the tablespace can be referenced explicitly in the CREATE TABLE statements or implicitly by allowing the table to be created within the tablespace that has been defined as the user's default tablespace. For example, user mary has been defined with a default tablespace called tools. An appropriate space quota has been granted to mary on this tablespace. If mary creates a table and does not include a tablespace parameter in the CREATE statement, the table will be created in the tools tablespace. There is no harm in including the tablespace parameter for the default tablespace. In the following script, the tablespace is referenced explicitly: SQL> CREATE TABLE general_ledger 2 (entry_id NUMBER NOT NULL, 3 transaction_id NUMBER NOT NULL, 4 trans_amount NUMBER(12,4) NOT NULL, 5 document_id VARCHAR2(20), 6 <and other column definitions> 7 ) 8 TABLESPACE base_accounting 9 STORAGE (INITIAL 25M NEXT 1M PCTINCREASE 0) 10 ; table created 2.3.1.4 Creating an index for a table in a tablespace In order to improve the speed with which Oracle retrieves data, you might create an index on a table. To improve performance, create the index in a separate tablespace and, preferably, on a different disk. The CREATE statement for an index for the base_accounting table created earlier might look like this: SQL> CREATE UNIQUE INDEX gl_ui ON general_ledger (entry_id) 2 TABLESPACE base_accounting_indx 3 STORAGE (INITIAL 4M NEXT 500K PCTINCREASE 0); index created In both statements, you can see that reference is made only to the logical object (the tablespace) and not to the underlying physical objects (the datafiles). Oracle will automatically store the objects within the datafiles associated with the tablespaces mentioned in the CREATE statements. 2.3.1.5 File placement and naming As shown in the example, the files used for the tablespaces may be placed anywhere on the computer's storage disks, although there are some general rules that you should follow to avoid performance problems. The file protection should prevent any user from ever being able to modify these files directly from the operating system level. As mentioned before, modification of these files is the sole responsibility of the Oracle processes. File names are usually chosen by the DBA in cooperation with the system administrator or in compliance with the company's security policy. As was illustrated in the CREATE TABLESPACE example, the datafile names should bear some relationship to the tablespace to which they belong. 2.3.1.6 Tablespace security From a security standpoint, the only issue in maintaining tablespace security is operating system control of access to the datafiles. No user ever has to manipulate the database datafiles directly in any manner from the operating system level. Users should not have any privileges on these files. The underlying datafiles for the tablespaces must be owned by the account that was used to install the Oracle software. This account is usually named oracle . The actual datafiles are created by Oracle with the CREATE TABLESPACE or ALTER TABLESPACE commands and they are maintained automatically by Oracle. 2.3.2 Redo Log Files The redo log is used to record data that can be used to reconstruct all changes made to the database. Redo logs are created primarily to be used for recovery from a disk crashassuming that a file-level backup has been performed and that archivelog mode has been enabled. The logs can, however, be used to recover from other forms of failure. In the event that the database has become corrupted as a result of a malicious action or a break-in, a known good backup can be restored and the archive redo logs applied to bring the database back to a good state prior to the corruption. The data in the redo logs is only intended to be read by the kernel during database recovery. Like datafiles, redo log files are binary. They are created indirectly when the database is first created, and they are maintained automatically. Each database has at least two or more redo log files. Only one redo log or redo log group is ever in use at a time, and the log files are used in a circular fashion. 2.3.2.1 Log switches In a system with two redo log files, as one file is filled, the kernel marks it as closed and begins writing to the next one. This is referred to as a log file switch or thread switch . When the second log file is filled, the switch process is repeated and the first file is used again. When a log switch occurs, the contents of the first redo log file will be overwritten as the log file fills up again. If the database is a development system that is backed up often, recovery from a disaster may only need to be performed to the point when the backup was made. If, however, there will be a need to restore the system to a point in time after the last full backup, the DBA will need to perform full, file-level backups and enable archivelog mode on the database to allow Oracle to automatically save off each redo log file as the log switches occur. Redo logs and archivelog mode enabledcoupled with scheduled file-level backupsare tools that could bail you out of a disaster situation. For UNIX users working with Oracle7 databases, Oracle provides a backup utility called the Oracle7 Enterprise Backup Utility, which can be used for backup and restore operations. Chapter 12, provides more information on backup considerations and implementation. 2.3.2.2 How redo log files are created Redo log files are created when the database is created. They are specified in the CREATE DATABASE statement, as in the following example: SRVRMGR> CREATE DATABASE finprod 2> DATAFILE /usr02/oradata/data/system01.dbf SIZE 75M REUSE 3> CONTROLFILE REUSE 4> LOGFILE /usr03/oradata/logfile/finlog01.dbf SIZE 1M, 5> /usr04/oradata/logfile/finlog02.dbf SIZE 1M, 6> /usr05/oradata/logfile/finlog03.dbf SIZE 1M, 7> /usr03/oradata/logfile/finlog04.dbf SIZE 1M, 8> /usr04/oradata/logfile/finlog05.dbf SIZE 1M, 9> /usr05/oradata/logfile/finlog06.dbf SIZE 1M 10> / The redo log files, like the tablespace files, are created and owned by the Oracle system account and should never be made accessible to any other user for any purpose. In the example above, two different redo log files have been placed on each of three disks. finlog01.dbf will be written to first on disk usr03. When either the redo log file completely fills or a designated checkpoint size is reached, finlog02.dbf will be written to on disk usr04. When this log file fills or the checkpoint limit is reached, finlog03.dbf will be written to on disk usr05. The sequence will continue through the log files sequentially. Notice that if finlog03.dbf becomes damaged, there is no copy of it on the system to fall back on since there is only one unique file defined for each redo log. To avoid this, you may want to implement redo log groups as described in the next section. 2.3.2.3 Using redo log groups Another approach to creating redo logs for your system is to create redo log groups, a form of mirroring . This is done by creating the log files in sets of repeated groups of files, preferably on two or more different disks, so that if one member of a set becomes damaged the others will be available to use for quick recovery. An example of a CREATE statement for redo log groups is shown here: SRVRMGR> CREATE DATABASE finprod 2> DATAFILE /usr02/oradata/data/system01.dbf SIZE 75M reuse 3> CONTROLFILE REUSE 4> LOGFILE GROUP 1 (/usr03/oradata/logfile/finlog01a.dbf, 5> /usr04/oradata/logfile/finlog01b.dbf) SIZE 1M, 6> LOGFILE GROUP 2 (/usr03/oradata/logfile/finlog02a.dbf, 7> /usr04/oradata/logfile/finlog02b.dbf) SIZE 1M, 8> LOGFILE GROUP 3 (/usr03/oradata/logfile/finlog03a.dbf, 9> /usr04/oradata/logfile/finlog03b.dbf) SIZE 1M 10> MAXDATAFILES 300 11> / In this example, there are three sets or groups of redo logs with two members each on two separate disks. The files defined as finlog01a.dbf and finlog01b.dbf will be written on disks usr03 and usr04 respectively. At switch time, both finlog02a.dbf and finlog02b.dbf files will be opened for writing. 2.3.2.4 MAXDATAFILES parameter If you think you might need to create a large number of datafiles (more than 32), you can include the MAXDATAFILES parameter in the CREATE DATABASE statement. For example, adding MAXDATAFILES = 100 will allow you to create as many as 100 total datafiles within the database. The use of a larger number for MAXDATAFILES does not impact performance and does enable a larger number of datafiles to be created in the system if needed. If you set MAXDATAFILES to a large number, the only impact you might see on your system is an increase in the size of your control file. In earlier versions of Oracle, the only time that MAXDATAFILES could be set was at database creation time. If the maximum number of datafiles specified in this statement had to be increased later, the only way you could increase the maximum number of datafiles allowed was to recreate the database sort of a brute force approach, not to mention a time-consuming effort. As of version 7.2, there is a way (which will be discussed shortly) to edit the control file to modify this parameter. There is no performance impact and only a slight increase in the size of the control file, so setting the MAXDATAFILES value initially to a larger size is recommended. 2.3.3 Control File Each database has at least one control file. This is a small binary file (like the redo log file) that contains information on the status of the database. The information in the control file is critical to database operation and recovery. The database will not start without the control file. Like the other Oracle files, this file should never be available to any user and must be owned by the oracle install user. The control file is unreadable except by the instance background processes. The control file contains: -
The database's internal identification number, name, and the timestamp of when the database was created. -
Tablespace information and the name, location, size, and a checksum value for each tablespace's datafiles. -
The name, size, and location of each online redo log file. -
The log history and archived log information. -
Backup set, piece, datafile, and redo log information (very important for recovery). -
The current redo log sequence number and checkpoint information. -
Datafile copy information. -
Values for five of the MAX... parameters. (MAXLOGFILES, MAXLOGMEMBERS, MAXDATAFILES, MAXINSTANCE, MAXLOGHISTORY). These are always present, even if they are not specified in the database initialization file. In that case, the default values will be shown. 2.3.3.1 Modifying control files In order to view or change any of the parameters in the control file, you must convert it to a readable text file. The command to dump the control file to a format in which it can be worked on is as follows : SRVMGR> ALTER DATABASE BACKUP CONTROLFILE TO TRACE; In this example, the file is dumped with a default name to a default location. This default location is defined by the ORA_DUMP_DEST parameter in the database initialization file. The file produced by the command is a text file with a name similar to ora20117.trc . Alternatively, the control file can be written to a specific, DBA-defined location by using a command with the following syntax: SRVMGR> ALTER DATABASE BACKUP CONTROLFILE TO '$ORACLE_HOME/mdir/mfil.sql'; 2.3.3.2 A sample control file Whether the default directory or a directory path and file name is used, the output file from the above commands will have a SQL section that will look something like the following example: # The following commands will create a new control file and use it # to open the database. # No data other than log history will be lost. Additional logs may # be required for media recovery of offline datafiles. Use this # only if the current version of all online logs are available. STARTUP NOMOUNT CREATE CONTROLFILE REUSE DATABASE "DOCX" NORESETLOGS ARCHIVELOG MAXLOGFILES 32 MAXLOGMEMBERS 2 MAXDATAFILES 32 MAXINSTANCES 16 MAXLOGHISTORY 1600 LOGFILE GROUP 1 'C:\orawin95\datadocx\docxlog1.dbf' SIZE 500K, GROUP 2 'C:\orawin95\datadocx\docxlog2.dbf' SIZE 500K, GROUP 3 'C:\orawin95\datadocx\docxlog3.dbf' SIZE 500K DATAFILE 'C:\orawin95\datadocx\docxsys1.dbf', 'C:\orawin95\datadocx\newts01.dbf', 'C:\orawin95\datadocx\newts02.dbf' ; Note that all of the MAX... values are present, even if values were not specified during database initialization. This section of code is followed by several commands that will restart the database, recreate the control file, and perform a recovery. The file contains instructions on how it can be edited to change the MAX... parameter values as well as the LOGFILE and DATAFILE specifications. If you change either of the latter two parameters, be sure that the files you specify are made available or the database will not recreate the control file. | | The primary purpose of backing up and editing the control file is to change one of the MAX... parameters most commonly the MAXDATAFILES value. | | 2.3.3.3 How Oracle uses control files In Oracle8, if the Oracle Enterprise Manger is used in conjunction with the Recovery Manager (RMAN utility) to perform backups (as described in Chapter 12 and Chapter 13), the backup history will be stored both in the Recovery Catalog and in the control files for each database. It is possible that this file will become very large; you need to take this into consideration both from a disk storage perspective and from a security perspective. Control file size is normally static, but it may begin to grow if the RMAN facility is enabled. This tool does write to the control files on a regular basis, so this increase in size does not mean there has been a security breach and that someone is tampering with your system files. Oracle will maintain as many copies of the control file as you declare in the CONTROL_FILES parameter in the initialization file (described in the next section). This parameter is used to specify the name and location of all control files to be maintained. The control files are created indirectly when the database is first created and are maintained automatically. If you want to have additional copies of the control file, you should shut down the database and copy one of the existing control files to the new locations. Remember to maintain the same security protections on the new files as on the original ones, and be certain that oracle is the file owner. Modify the initialization file that will be used to start the database to include a reference to the new file and its location along with the original ones, and restart the database. The new file will be maintained automatically. There should always be at least two, and preferably more, copies of the control file in different directories on different disks and, ideally , on different controllers. The information in all of the copies is identical. These multiple copies are for protection against disk failure. Like all of the other files, these should be available only to the oracle system account. If one of the control files on a system becomes damaged and the database won't start up, when the damaged control file is determined, merely replace the damaged copy with one of the other copies of the control file from a different disk and restart the database. 2.3.4 Initialization File The initialization file is one of the two Oracle database files that are readable. (The other readable file is the configuration file known as CONFIG.ORA , which we will discuss later in this chapter.) There is no information in the initialization file that is of use to users, so it should be protected the same way as the datafiles so users cannot read it. The file must exist but does not necessarily have to contain much information since most parameters have default values. Parameters are added to change default values as part of database performance tuning. The initialization file is a text file and will be created either by the installation process or by the DBA using any text editor. As we've mentioned, depending on the operating system you are using, this file may be named using the form INIT<DATABASE SID>.ORA . If the system identifier (SID) is "payroll," for example, then the file will be named INITPAYROLL.ORA . For simplicity, in this book we refer to this file as the INIT.ORA file. 2.3.4.1 Oracle's sample initialization file Oracle provides a starting initialization file containing the most commonly modified parameters. For each of these there are three entries: one for a small, a medium, and a large database. The medium and large values are commented out with a "#" sign. The values of these parameters, however, are only suggested starting points and it is very rare that these default values are retained in a production system. There are 156 parameters that can be specified in a version 7.3.0.0.0 database. The number of parameters available will vary depending on the version of Oracle you are using. A few examples of the types of entries found in the Oracle-provided initialization file follow; in Chapter 8, you'll find a full set, along with an indication of which you'll need to pay particular attention to from a security perspective. control_files = C:\orawin95\database\ctl1orcl.ora compatible = 7.3.0.0.0 db_file_multiblock_read_count = 8 # INITIAL # db_file_multiblock_read_count = 8 # SMALL # db_file_multiblock_read_count = 16 # MEDIUM # db_file_multiblock_read_count = 32 # LARGE db_block_buffers = 200 # INITIAL # db_block_buffers = 200 # SMALL # db_block_buffers = 550 # MEDIUM # db_block_buffers = 3200 # LARGE shared_pool_size = 3500000 # INITIAL # shared_pool_size = 3500000 # SMALL # shared_pool_size = 6000000 # MEDIUM # shared_pool_size = 9000000 # LARGE All lines that start with "#" are comments. The initialization file is read when the database is created and every time the database is started. Any changes to the file will not take effect until the database is stopped and restarted. This file can have references to other files, and the default file does contain one such reference to the configuration file ( CONFIG.ORA ) in the IFILE parameter. | | Releases of Oracle up to version 7.2 had a bug that restricted the size of the INIT.ORA file. The file could be larger than the maximum size, but in many cases no error would be generated as most of the critical information usually occurs early in the file. In the earliest releases, this value was 2K and became 8K just prior to Version 7.2. As of version 7.2, this restriction no longer exists. If you are working on a version that has this restriction, you should either remove all of the comments delivered with the default initialization parameter file or move them to the bottom of the file. Make sure that all comments and remarks you want to store in this file are also placed at the bottom of the file. | | 2.3.4.2 Evolution of an initialization file The production initialization file may start out as a copy of the development file but will probably change rapidly as you tune your production system. Remember that the initialization file is modified and maintained by the DBA and should be available only to and through the oracle system account. The wise DBA will always insert an entry in the bottom of the file reflecting each change that has been made to this file. A complete record of the value prior to the change and the new value, with a date and the initials of the person who made the change, should always be logged. In this way, there will be no question of who performed a change to the file, what change was made, and when that change occurred. A change that proves to be in error can be easily backed out if the old value is retained. An audit trail of who has been making changes will help to identify who is interacting with the parameters. A sample entry audit trail entry in the INIT.ORA file might look like this: # Revision History: # 04/10/98 - mlt - increased shared_pool_size from 9000000 to 12000000 An alternative method is to make the comments in-line: shared_pool_size = 12000000 # 4/10/98 old - 9000000 - mlt If you encounter problems after increasing the SHARED_POOL_SIZE, you will know the previous value and can easily restore it or select a value between the old and new values. The revision history does not have to be complex or difficult to maintain. 2.3.5 Configuration File The configuration file ( CONFIG.ORA ) is a text file that contains information that, in earlier Oracle versions, was included in the initialization parameter file. In fact, when the installation procedure creates a configuration file, the sample INIT.ORA file will usually contain an "IFILE=...CONFIG.ORA" statement meaning that the contents of the CONFIG.ORA file are to be read as a part of reading the INIT.ORA file. In effect, the CONFIG.ORA file is simply an extension of the INIT.ORA file. As with INIT.ORA , changes made in the configuration file do not take effect until the database is restarted. The question, then, is this: if these entries in the configuration file used to be in the initialization file, why are they now in a separate file? One answer is that placing the configuration information in a separate file simplifies the maintenance of initialization parameters when the parallel server option is used. Also, if you use more than one INIT.ORA file for different processing situations, they can all call the same CONFIG.ORA file for the static part of the parameter list. For example, one INIT.ORA file may be used for build while another may be used for batch processing and still another used for normal daily use. | | In most installations where a parallel server is not being used or multiple INIT.ORA files are not used, the configuration file contents can be merged into the INIT.ORA file and the configuration file can be discarded. | | 2.3.5.1 Contents of the configuration file The parameters in the typical configuration file are those that would be shared by multiple instances all accessing the same physical database files that is, a parallel server configuration. All the instances constituting a parallel server system must use the same database name that is specified by the DB_NAME parameter. Consequently, the DB_NAME parameter is one of the lines in the configuration file. So, while each instance requires its own initialization file, the common parameters can be put into the configuration file which, as previously described, may be referenced by the INIT.ORA file. If a change is made in the configuration file, then all instances are affected and all instances will easily see the change. A sample CONFIG.ORA file is provided for somebut not allversions of Oracle. In a Windows NT Oracle version 8.0.4 system, no CONFIG.ORA file is provided but reference is made in the INIT.ORA file to placing multiple instance-specific parameters into another file and using the IFILE parameter to point to this other file. There is also no configuration file for Personal Oracle on Windows 95, but there is for most UNIX installations. The production CONFIG.ORA file is usually a copy of the development file. The file is modified and maintained by the DBA through the oracle installation account and should be available only to the oracle system account. A sample CONFIG.ORA file might look like the following: # Do generic setup. db_name = "MY_DB" ifile = ORA_SYSTEM:INIT.ORA db_block_size = 8192 control_files = (my_disk01:[oracle.my_db]ora_control1.con, my_disk02:[oracle.my_db]ora_control2.con, my_disk03:[oracle.my_db]ora_control3.con) background_dump_dest = ora_dump user_dump_dest = ora_dump # # Add specific parameters for instance MY_DB below. # # e.g. rollback_segments = (rb1,rb2,...) In this example, the parameters included are very database-specific . The database name and size, as well as the control file and dump file information, are included. However, no parameter values (like a size for DB_BLOCK_BUFFERS) are included here. | 
