220.127.116.11 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
18.104.22.168 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.
22.214.171.124 File placement and naming
As shown in the example, the files used for the tablespaces may be placed
126.96.36.199 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
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
188.8.131.52 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
. 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
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.
184.108.40.206 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.
220.127.116.11 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
18.104.22.168 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
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:
22.214.171.124 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
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';
126.96.36.199 A sample control file
Whether the default directory or a directory
# 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.
188.8.131.52 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
There should always be at least two, and preferably more, copies of the control file in different directories on different disks and,
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
, 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
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.
184.108.40.206 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 220.127.116.11.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 = 18.104.22.168.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
22.214.171.124 Evolution of an initialization file
The production initialization file may start out as a copy of the development file but will probably change
# 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 (
) 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
file will usually contain an "IFILE=...CONFIG.ORA" statement meaning that the contents of the
file are to be read as a part of reading the
file. In effect, the
file is simply an extension of the
file. As with
, 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
126.96.36.199 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