• 11
• Managing the Database
• The Parameter File: INIT.ORA
• The Oracle SID
• Creating a New Database
• The First Objects Created
• Startup and Shutdown
• Startup Stages
• Shutdown Stages
• The Data Dictionary
• Core System Tables
• Data Dictionary Views
• Dynamic Performance Tables
• Other Data Dictionary Views
• Redo Logs
• Creating, Altering, and Dropping Redo Logs
• Multiplexing Redo Logs
• Control Files
• Multiplexing Control Files
• Trace and Alert Files
• Database Modes
• NOARCHIVELOG Mode
• ARCHIVELOG Mode
• Changing the Mode of the Database
• Useful Data Dictionary Views
• Summary

11

Managing the Database

In this chapter, you learn how to perform the following tasks:

• Make a new Oracle database
  
  
• Manage the instance by bringing the instance up and down
  
  
• Obtain information from the Oracle system tables
  
  
• Modify the number of control and redo log files
  
  

The Parameter File: INIT.ORA

An instance is the set of background processes and the memory structures that Oracle uses. Every instance must have a parameter file, known as the INIT.ORA file, from which it retrieves the parameters that it uses to run and the mode of the database. This parameter file is used by the Oracle SQL*DBA tool when the instance is first started. Once the instance has been started, the parameter file is not used again until the next time the instance is started. The parameter file must be on the same machine as the SQL*DBA tool.

INIT.ORA parameter files are used to specify

• Amount of storage allocated for the Oracle memory structures
  
  
• Which rollback segments to use for the instance
  
  
• National language settings
  
  
• Settings for running Oracle in parallel server mode
  
  
• Which database and control file to use
  
  
• When to issue checkpoints
  
  
• Limits for database control structures
  
  
• Which non-mandatory background processes to initialize
  
  

The file is given the generic name INIT.ORA, but the Oracle instance identifier is usually part of the filename. The name can be any filename, but it usually begins with INIT.

The parameter file is an ordinary text file, which you modify using any character-based operating system text editor. The parameters can be included in any order, and they can be specified in upper, lower, or mixed case.

Even though the parameters can be included in mixed case, the names of operating system files must be specified according to the operating system's rules for file naming. In UNIX, for example, a filename must be specified exactly as it appears on the file system.

You specify over 130 parameters. Most of them are common to all the platforms on which Oracle runs. The number of the parameters increase from one release of the Oracle RDBMS software to the next.

An example parameter file comes with the Oracle software; its default name is INIT.ORA. You can use this file could to run your instance, although you might have to adjust the parameters manually to obtain optimal performance.

It is possible to many parameter files for the same instance, although only one is used at a time for the instance startup. If you want to use another set of parameters for the run, you must shut down and restart the instance.

Many parameters have a minimum value. If they are set below the minimum value, the Oracle instance does not start up. The values of some parameters are derived from settings in other parameters. Normally, you do not need to set them. There are even some parameters that you should not set unless the Oracle support desk instructs you to set them. Appendix A of the Oracle7 Server Administrator's Guide describes all the different parameters.

Use one parameter file for the instance for normal online operations during the day. At night, use a parameter file that optimizes performance for a batch job.

In the INIT.ORA file, the only parameter that is mandatory is the CONTROL_FILES parameter, which names one or more control files to be used for the instance. This control files are used during instance startup to determine the names and locations of the database and redo log files. Table 11.1 describes additional parameters for the INIT.ORA file.

Table 11.1. Important INIT.ORA parameters.

Parameter Name

Description

Table 11.1 does not describe all the parameters in the INIT.ORA file. Refer to Appendix A of the Oracle7 Server Administrator's Guide for descriptions of the all parameters.

Use the IFILE parameter to name a file that contains a common set of parameters for all the instances running on the machine.

To see the parameters that the current instance is using, enter the following in SQL*DBA:

SQLDBA> SHOW PARAMETERS

You can also see only those parameters that contain a given string. For example,

SQLDBA>  SHOW PARAMETERS buffer

shows all the parameters that contain the string buffer.

To see a list of the parameters when the instance first comes up, include the word LIST in the first line of the INIT.ORA file.

To access the parameters from any Oracle tool, run a select operation against the v$parameter table, as in

SQLPLUS> SELECT * FROM v$parameter.

You must have access to the v$parameter table, which is owned by the Oracle user SYS.

The Oracle SID

The Oracle System Identifier (SID) identifies the Oracle instance on the machine. It is usually set up as an operating system variable— the $ORACLE_SID environment variable in UNIX or the ORA_SID symbol in VMS—that is used to name of the Oracle background processes and to identify the SGA area in memory. Whenever a user process wants to connect to a database, the SID set up in that operating system account determines to which instance and, therefore, to which database the user will connect. The same user name can exist in different databases. For example, the Oracle system username SYS exists in all Oracle databases. With the SID, you can connect to the correct instance.

Many instances might be running on the same machine, each doing the processing for a different database. By looking at the processes running on the machine, you see many occurrences of the DBWR background process. The name of the process itself usually has the SID somewhere in it.

For client/server setups, the client machine identifies the network protocol and machine address to which the client machine wants to attach and the name of the SID of the connection. Requests are then processed against the database for which the instance is processing.

Creating a New Database

When you create a database, you create files on disk that Oracle has formatted and prepared. The structure of the database does not have to remain the same as initial structure. You can add other files later.

On some platforms, you can create a new database when the Oracle software is first installed. To create a database manually after that, follow the steps in this section. It is assumed that the software has been installed correctly on your machine. The steps provided here are general steps that apply to most platforms. Creating a database involves operating system-specific steps, of course. Refer to the Oracle7 Installation and User's Guide for their platform for specific information on your platform.

The crux of creating a database lies in running the CREATE DATABASE SQL statement. You must perform other steps, however, before you can run it. The syntax of the CREATE DATABASE SQL statement is

CREATE DATABASE mydb

  LOGFILE file1, file2 SIZE nM

  MAXLOGFILES n

  MAXLOGMEMBERS n

  MAXLOGHISTORY n

  DATAFILE file1 SIZE nM

  MAXDATAFILES n

  MAXINSTANCES n

  ARCHIVELOG | NOARCHIVELOG

For example,

CREATE DATABASE mydb

     DATAFILE '/disk03/mydb/system1.dbf' SIZE 20M

     LOGFILE '/disk04/mydb/log1.log',

     'disk05/mydb/log2.log' SIZE 512K;

The datafile parameter will physically create the first database file on the disk specified and with the size specified. If the file already exists the command will fail (unless the REUSE option is specified as well in which case the file will be over-written). The same is true with the LOGFILE parameter which creates the very first two redo log files.

Dont use the REUSE option of the CREATE DATABASE command, for you could accidentally overwrite a file that is being used by another database. Instead, physically delete the file. You can use REUSE on some platforms when contiguous disk space that you want to reuse has already been allocated to a file.

The default mode of the database is NOARCHIVELOG.

To specify the maximum number of data files, redo logs, and log members that can exist on the database, use the MAXDATAFILES, MAXLOGMEMBERS, and MAXLOGFILES parameters. If you specify lower limits, fewer resources are used in the SGA. The MAXINSTANCES parameter controls the maximum number of instances that can be connected to database.

If you set these maximum limits lower than the predefined maximum as part of the CREATE DATABASE statement, you must recreate the control file manually to increase them. Instead, you should specify the limits by using the INIT.ORA parameters, which set the limits for that startup of the instance. That way, I you need to increase the limits, simply increase the INIT.ORA parameter and restart the instance.

Leave the database mode set to NOARCHIVELOG—the default—in the CREATE DATABASE command. Change the mode to ARCHIVELOG when all the application objects have been created and populated. This improves performance while the application objects and data are initially loaded.

The steps in creating new database are

1. Plan for the creation. Make sure that there is adequate disk space for the database. It is useful to do a sizing exercise to estimate the amount of storage required to hold the data for the application system. Determine the names and locations of the database files, the control file, and the redo log files.
   
   Make sure that the memory on the machine is enough for the new database, including the memory for the SGA, the PGA, and the programs on the database server machine. All the Oracle memory structures should fit into the machine's real memory.
   
   
2. The operating system user account that you will use should have the INTERNAL privilege. This privilege enables your operating system account to run the CREATE DATABASE, STARTUP, and SHUTDOWN commands from the SQL*DBA tool. A lower-level form of the INTERNAL privilege is the OSOPER and OSDBA roles. INTERNAL, OSOPER, and OSDBA are assigned using operating system security mechanisms—groups on a UNIX machine or process rights on a VMS machine. Operating system administrators generally grant your account these privileges.
   
   
3. Decide what the Oracle instance identifier is going to be, and set the appropriate operating system variable names. The Oracle SID should not have the same as an existing instance on the same machine.
   
   
4. Create the INIT.ORA file for your database. The easiest way to do this is to copy an existing INIT.ORA file. Rename this file to indicate the SID used for the instance—for example, INITMYDB.ORA. Specify the name and location of the control file in the CONTROL_FILES parameter. Specify the name of the database—typically the same as the instance identifier— in the DB_NAME parameter. The name of the database must match the name given in the CREATE DATABASE command. It is recorded in all the database files, the control file, and the redo logs for the database. If you want to change the DB_BLOCK_SIZE parameter, you must do so now. It cannot be modified later.
   
   
5. Initiate the SQL*DBA tool, and make a connection to the Oracle RDBMS software by using the INTERNAL account. Although the syntax of the CONNECT command looks similar to a normal connect, no password is specified:
   
   SQLDBA> CONNECT INTERNAL
   
   
6. Start the Oracle background processes, and allocate memory for the SGA by starting up the Oracle instance. For example,
   
   SQLDBA> STARTUP NOMOUNT PFILE=INITMYDB.ORA
   
   
7. Run the CREATE DATABASE statement with the parameters specific to your requirements. For example,
   
   SQLDBA> CREATE DATABASE mydb DATAFILE '/disk03/mydb/system1.dbf' SIZE 20M
   
   LOGFILE '/disk04/mydb/log1.log',
   'disk05/mydb/log2.log' SIZE 512K;
   
   

Once the database has been created, it is immediately available for use. No further steps are necessary. At this stage, though, you have not created any objects on the database other than the ones automatically by the CATALOG.SQL file. You can now start building the structure of your application database by defining tablespaces, usernames, tables, and so on.

You might need to run additional scripts to install the objects that enable the procedural database option or to store Oracle forms and other programs on the database.

The First Objects Created

Many objects are present on the database immediately after you create it. They are

• The Oracle users SYS and SYSTEM
  
  
• A tablespace called SYSTEM
  
  
• A rollback segment called SYSTEM
  
  
• The core data dictionary tables in the user account SYS
  
  
• The CONNECT, RESOURCE, and DBA roles, which that provide backward compatibility with granting privileges
  
  

SYS has the default password CHANGE_ON_INSTALL, and SYSTEM has the default password MANAGER. To test whether the database was created, try to make a connection using these user names and passwords. The default passwords should be changed. The SYS account is the Oracle user that owns the core data dictionary tables. There is usually little reason to use this account for day-to-day access to the database. The SYSTEM account does not own any of the core data dictionary tables. Instead, it uses a set of views created on them. These views help the DBA and developers obtain information on the structure of the database and its objects.

The first set of data files created as part of the CREATE DATABASE statement are allocated to the SYSTEM tablespace. Its initial contents are usually the data dictionary tables and other objects.

Additional rollback segments should be created on the database.

Along with the core data dictionary tables in the user account SYS, data dictionary views that make the information in the core tables easier to view and manage are created. Public synonyms are created for most of the data dictionary views to give Oracle users access to basic information on their user accounts.

Startup and Shutdown

Before you can use an Oracle database, an instance must be running. An instance is the combination of the Oracle background processes and the SGA. This section discusses how to initiate and shut down an instance.

Startup Stages

For a database to be fully open and usable, the instance must be brought through three stages of startup:

1. Starting the instance
   
   
2. Mounting the database
   
   
3. Opening the database
   
   

The first stage involves initiating the Oracle background processes and allocating memory for the SGA. During this stage, the INIT.ORA parameter file is used to determine the mode and instance initialization parameters, such as the sizes of the SGA structures in memory.

The second stage is mounting the database. It involves opening the control file—whose location is specified in the INIT.ORA parameter file used in stage 1—and determining the locations and names of the other database and redo log files.

The third stage opens the actual database files and the redo logs. Once this stage has been completed, you can access and use the database objects.

In MOUNT or NOMOUNT state, the database can be used only by the DBA.

In some cases, such as when recovery is to be initiated, the database might not have gone through all three stages. For example, it might be at stage 2, which means that that only the control file can be accessed. The actual database files are closed, so no operations on database objects are possible.

The tool used to perform the startup and shutdown is the Oracle SQL*DBA utility, which comes with the database software. This tool has a basic menu and Òfill in the blankÓ screens.

Before we can issue a startup or shutdown from the SQL*DBA tool, you must be connected as the INTERNAL user by means of a dedicated server connection. In other words, the multi-threaded server configuration cannot be used. This is a check whether you have operating system privileges and can perform these operations. Operating system-specific privileges, such as UNIX groups or VAX VMS process rights, determine who has access to perform these operations.

SQLDBA>  CONNECT INTERNAL

Once you are connected as the INTERNAL user, you can use the STARTUP command to initiate the instance startup in whatever mode you want. During the first stage of startup, the instance parameter file must be provided. In its simplest form, the startup command brings the instance through all three stages and makes the database available for all Oracle users to access:

SQLDBA> STARTUP

Note that he parameter file that the instance will use has not been specified. On most platforms, you can set an operating system environment variable. The default location for the parameter file on a UNIX platform is the /dbs directory under the home directory of the Oracle software.

The name of the database is assumed to have been specified in the INIT.ORA parameter file with the DB_NAME parameter. The name of the database specified is checked against the name of the database stored in the control file. If a mismatch occurs, the instance fails to start up and an error message is generated.

If you do not use this syntax, you must name the parameter file explicitly in the STARTUP command. In the following command, the DB_NAME parameter has not been specified in the INIT.ORA file. Therefore, you must specify it explicitly as part of the STARTUP command.

SQLDBA> STARTUP PFILE ='/disk04/initmydb.ora' db_mydbname

You can also use this syntax when you want to start up the instance with a set of parameters different from the default values. For example, you might want to use parameters that have been tuned to optimize batch jobs.

If you use the parallel server option, you can specify additional parameters as part of the STARTUP command. The PARALLEL and EXCLUSIVE parameters control whether other instances can access the database once the instance has started up. If you use the EXCLUSIVE parameter, any attempt to start up the instance on another processor fails.

In many cases, you might not want to take the instance through all three stages. For example, you might need to perform maintenance and recovery operations. If this is the case, specify additional parameters, depending what stage you want.

If you use NOMOUNT parameter, only the Oracle background processes start and the SGA area in memory is allocated. You typically use NOMOUNT only when the database is created. For example,

SQLDBA> STARTUP PFILE ='/disk04/initmydb.ora' NOMOUNT

The MOUNT option takes the instance up to the point when the control file is opened. You use MOUNT, for example, when you want to recover the database and the database files or redo logs are not present. You need to have the control file open, which is the case if the database is mounted, because it contains information that helps ensure that the files that make up the database remain in a synchronized state. For example,

SQLDBA> STARTUP PFILE ='/disk04/initmydb.ora' MOUNT mydbname

The next word after MOUNT or OPEN in the STARTUP command is assumed to be the name of the database. If the name of the database has already been specified in the INIT.ORA file, move the MOUNT or OPEN keyword to the end of the command.

Another option is to bring the database through all three stages but to limit access to the tables to the DBA. This is useful for performing a full export of the database, because the data will not change during the export. For example,

SQLDBA> STARTUP PFILE ='/disk04/initmydb.ora' RESTRICT OPEN

Only Oracle accounts that have been given the RESTRICTED SESSION system privilege can connect to the database after the instance is brought up with the RESTRICT keyword.

On some operating systems, a special facility is provided to databases to be started automatically when the machine is booted up. Refer to the user's guide for your platform to see how to implement this facility if it is available on your platform.

Shutdown Stages

A database is brought down in three stages:

1. Closing the database files and the redo log files
   
   
2. Dismounting the database
   
   
3. Shutting down the Oracle background processes and releasing the memory occupied for the SGA
   
   

In the first stage, the database files and the redo log files are closed. Before this happens, information in the SGA is flushed down to the database files for system and user data. Entries from the redo buffer cache in the SGA are also flushed down to the active redo log file, and a marker is made in the redo log to indicate that a shutdown has occurred.

In the second stage, the database is dismounted. This means that the control file is updated with synchronization information and closed.

In the third stage, the Oracle background processes are shut down, and the memory occupied for the SGA is released.

The SQL*DBA tool is used to perform the shutdown, and the user must be connected to the database as INTERNAL. For example,

SQLDBA>  CONNECT INTERNAL

The SHUTDOWN command takes the instance through all three shutdown stages:

SQLDBA>  SHUTDOWN

If the database is not fully open, informational messages indicate which shutdown stage is not necessary. The other stages are performed.

If users are connected to the database, the SHUTDOWN command without any parameters waits indefinitely for them to disconnect from the database. You can disconnect users manually by using the ALTER SYSTEM KILL SESSION command or by means of the SQL*DBA menu options.

You can shut down the instance so that connected users are immediately disconnected and work in their current transactions is rolled back. Use the IMMEDIATE keyword after the SHUTDOWN command:

SQLDBA>  SHUTDOWN IMMEDIATE

You should send a message notifying all the users currently connected to disconnect from the database. There is no Oracle facility for doing this, so you must use an operating system utility. This is a problem especially for client/server setups, because the users might be running the tools on another machine.

In extreme cases, you can shut down the instance without waiting for information to be flushed from the SGA to the database and redo log files. Use the ABORT option of the SHUTDOWN command:

SQLDBA>  SHUTDOWN ABORT

Like the IMMEDIATE option, ABORT disconnects all the users currently connected, but the rollback operation is not be performed when the instance is shut down. Instead, if a rollback is performed, it happens the next time the instance is started up. This option is quicker than the IMMEDIATE option, especially when a large update transaction has to be rolled back before the instance can be shut down. Essentially, the rollback is delayed until you next start up the instance.

A more cruder way of bringing down the database on some platforms is to kill the Oracle background processes that are currently running. If the background processes are no longer running, the instance is not up. This method is not recommended, though.

Regardless of how you bring the instance down, the integrity of the database is not in any danger. The values of any changes are recorded in the redo logs; on COMMIT, they are forced to the redo log files on disk. Changes to the database blocks in the database buffer cache in the SGA that have not been written to the database files before SHUTDOWN IMMEDIATE, SHUTDOWN ABORT, or even an instance crash, are reapplied automatically the next time the instance is brought up. This is totally invisible to the user and the DBA. The only noticeable effect after SHUTDOWN IMMEDIATE or SHUTDOWN ABORT is a increase in the time taken during the opening stage of bringing up the instance.

Suppose that you started the instance in NOMOUNT or MOUNT state and need to take it from one stage of startup to another. The easy way to do this is to shut down the instance entirely and then restart it in the mode you want. Likewise, you could use the ALTER DATABASE command, which takes the database from one stage of startup to another. Refer to the Oracle SQL Language Reference Guide for more information on this command.

The Data Dictionary

The Oracle data dictionary is a set of tables that the Oracle software uses to record information about the structure of the database. These core system tables are owned by the Oracle user present on all Oracle databases—the SYS user. SYS is rarely used, even by DBAs, for maintenance or inquiry work. Instead, another Oracle user with a high-level system privileges is used. Typically, only one other DBA account is created for the user of the DBA.

The DBA does not usually use the SYSTEM user, which is also automatically defined when the database is created. This is because product-specific tables are installed in SYSTEM, and accidental modification or deletion of these tables can interfere with the proper functioning of some of the Oracle products.

Core System Tables

The core data dictionary tables have short names, such as tab$, col$, ind$. These core system tables are rarely referenced directly, for the information is available in more readily digestible form in the data dictionary views defined when the database is created. To obtain a complete list of the data dictionary views, query the DICT view.

Data Dictionary Views

The data dictionary views are based on the X$ and V$ tables. They make information available in a readable format. These names of these views are available by selecting from the DICT data dictionary view. Selecting all the rows from this view shows a complete list of the other accessible views.

SQL*Plus provides basic column formatting, whereas the SQL*DBA utility does not. Therefore, you use SQL*Plus for running queries on these views.

If you are not sure which data dictionary view contains the information that you want, write a query on the DICT view. Suppose, for example, that you want to find all the data dictionary views that give information about SYNONYMS—aliases for other database objects. You would write the following query:

SQLPLUS> SELECT * FROM DICT WHERE TABLE_NAME LIKE '%SYNONYM%';

The list of views produced by this query is a list of other views that you can query.

Most views used for day-to-day access begin with USER, ALL, ROLE, or DBA.

The USER views show information on objects owned by the Oracle user running the query. Suppose, for example, that a table called FOOTBALL is owned by the Oracle user LINEKER and a table called BOXING is owned by the Oracle user BRUNO. If you log into the Oracle account LINEKER and query the USER_TABLES view, the only table that you will see is the FOOTBALL table. If you disconnect from that user and connect to the Oracle user BRUNO and then run the same query, the only table you will see is the BOXING table.

The data dictionary views beginning with ALL show information on objects owned by the current Oracle user as well as objects to which the user has been given access. Suppose, for example, that BRUNO gives SELECT access on the BOXING table to LINEKER. When you log into the Oracle database using the LINEKER account and run a query on the ALL_TABLES view, you will see information on both the FOOTBALL and BOXING tables. One is owned by the account, and the other is a table that the user can access. Note that BRUNO still sees only the BOXING table.

If you connect to the Oracle database using a more privileged account—such as SYS or SYSTEM—you can access the DBA data dictionary views. The DBA views are typically used only by the DBA. They show information for all the users of the database. The SELECT ANY TABLE system privilege enables other users to access these views. Querying the DBA_TABLES view shows the tables owned by all the Oracle user accounts on the database. If you log in as SYS and query the DBA_TABLES view, you will see that LINEKER owns the FOOTBALL table and that BRUNO owns the BOXING table.

Dynamic Performance Tables

Another set of tables often considered to be part of the data dictionary are the dynamic performance tables. They do not actually occupy storage on the database; they are in-memory tables that exist while the instance is running and disappear when the instance is shut down. Their names typically begin with X$. You rarely go directly to the X$ tables. Instead, you use views that begin with V$ to access the information in a more readable form.

The dynamic performance tables are divided into two groups: static and dynamic. Because they are held in memory, the overhead of keeping them updated is negligible.

The tables that hold static information on the instance and the database setup always show the same information while the instance is running regardless of whatever activity is occurring. For example, the v$parameter table shows the settings for the initialization parameters that have been defaulted or set in the INIT.ORA file.

The dynamic tables are updated continuously when activity occurs and provide data that is useful for tuning. For example, the v$sysstat table holds system-level statistics that show the number of Oracle blocks physically accessed from database files and the number of logical Oracle block accesses. These statistics are updated whenever a SQL command is executed on the database—whether it is user SQL or system-generated SQL.

Other Data Dictionary Views

Other data dictionary views provide the same information as the views discussed previously. These are either ANSI-standard views or views that are present for compatibility with previous versions of the Oracle database. These ANSI-standard views, such as CATALOG, show information in ANSI-standard format. Suppose, for example, that you used the ANSI-standard view names on another database. You can work with the same view names and see the same information on an Oracle database. The views, however, do not show information about the parts of the Oracle database that have surpassed the ANSI-standards.

Redo Logs

The Oracle system auditing is affected by the redo logs. There must be a minimum of two redo log files for every database, and in this part, I discuss how to maintain redo log files and how they can be multiplexed.

Creating, Altering, and Dropping Redo Logs

The online redo log files are used to record changes made to the database files. They are also used in rotation. That is, when one redo log file fills, a checkpoint is performed and entries are written to the other redo log file. Once that file has filled and the checkpoint on the next has completed, the next file is used. If the checkpoint on a redo log file has not completed, it cannot be used until the checkpoint has completed. This causes the instance to hang temporarily until the checkpoint has completed. For this reason, additional redo log files can be created.

As a general rule, size the redo log files so that a switch from one online redo log file to another occurs roughly every 30 minutes. This ensures that not too many checkpoints occur. (Remember: At each checkpoint, the changed database blocks must be written to disk, which causes more disk I/O.) Likewise, the time needed to recover the instance if it fails is not too great; only 30 minutes of changes must be reapplied. Use the Oracle alert file or the V$ dynamic performance views to determine how often log switches occur.

You can create more than two online redo log files when you create the database or by using the ALTER DATABASE SQL command. Because you are modifying the structure of the database, you must start the instance in a mounted state. Having the instance in a mounted state means that the control file is open—the names of the redo logs are recorded in the control file.

To start the instance up in a mounted state, go into the SQL*DBA tool and issue the STARTUP MOUNT command. This assumes that the INIT.ORA parameter file is in the default location and that the name of the database is specified in the INIT.ORA file with the DB_NAME parameter. For example,

SQLDBA> STARTUP MOUNT

To add another online redo log file, enter

ALTER DATABASE ADD LOGFILE

     '/disk03/mydb/log3.rdo' SIZE 512K;

When you restart the instance, the log writer background can write to three online redo log files, which reduces the chance of having to wait for a checkpointing process to occur.

To move the redo log files, first make sure that the instance has been started in a mounted state. Use an operating system file copy command, and make a copy of the file that you want to rename or move to a new location. Then issue the following command:

ALTER DATABASE RENAME FILE

     '/disk03/mydb/log3.rdo' TO

     '/disk04/mydb/log4.rdo';

This command records the new name and location of the redo log file in the control file. Note that it is always necessary to copy the files manually to the new names or locations. Oracle does not make a copy of the files for you.

To drop an online redo log file, first make sure that the instance has been started in a mounted state. Use your operating system's file delete command to remove the file manually from disk. To notify Oracle that the control file must be updated because the redo log file can no longer to be used, issue the following statement:

ALTER DATABASE DROP LOGFILE MEMBER

     '/disk04/mydb/log4.rdo';

The redo log files should all be the same size.

Make sure that there are always two or more online redo log files. Otherwise, you will not be able to restart the instance.

Multiplexing Redo Logs

The redo log files protect the database files in case of failure. If you lose the changes in the SGA, you can reapply them from the online redo log files for the committed transactions. Likewise, if the database has been set up to run in ARCHIVELOG mode, the changes made to the database file since the last backup can be reapplied automatically to a backup of the file, thereby protecting against media failure.

If the online redo log files themselves are lost, however, you run the risk of losing committed work. For this reason, it is a good idea to mirror redo log files by using redo log groups. Essentially, multiple copies of the online redo log files are kept. If one copy is lost, there is always an exact mirror copy that the instance can use. You might think that this would slow the instance down. In fact, the opposite is often the case, because the LGWR background process needs to receive a success signal from only one of the copies. If one of the copies of the redo log files in a redo log group cannot be used, the file is marked as invalid and the instance continues to run as normal.

You can mirror the redo log files when you create the database or by using the ALTER DATABASE command. A redo log group consists of one or more redo log file members, which are the redo log files. All the members of the group have the same information recorded in them—they are mirror copies of one another. The redo log groups are numbered sequentially, starting at 1. To see which group each of the existing files belongs to, access the v$log dynamic view.

In the default setup, a database has two redo log file groups, each of which has one member. This means that there are only two redo log files; the instance writes cyclically from one to the other.

When you mirror an existing redo log file, you create another redo log file member of a redo log group. The following command adds another file to redo log group 1:

ALTER DATABASE ADD LOGFILE MEMBER

     '/disk04/mydb/log11.rdo' TO GROUP 1;

The command to drop a redo log file is similar. To drop an entire group, issue the following command:

ALTER DATABASE DROP LOGFILE

     GROUP 3;

Control Files

The control file is used in the second stage of instance startup to find out the names and locations of the database files and the redo logs. Losing this file causes a major headache in recovery. Recovery options restore the control file either from a previous backup or use the CREATE CONTROL FILE SQL command to create it directly. You can set up a further level of protection by having the Oracle instance keep more than one copy of the control file always updated. This ensures that you always have another copy if one copy is lost.

Multiplexing Control Files

To make the Oracle instance update more than one copy of the control file, follow these steps:

1. Use SHUTDOWN NORMAL to bring down the instance.
   
   
2. Use an operating system utility to copy the existing control file to a different name.
   
   
3. In the CONTROL_FILES INIT.ORA parameter, specify both control files. Separate them with a comma.
   
   
4. Bring up the instance. The instance now maintains the two control files.
   
   

If you make a copy of the control file, keep the other control file on a disk different from the original to guard against losing the entire disk drive. To provide an extra level of protection, make more than two copies. The extra overhead of keeping extra copies of the control file is negligible compared to the extra protection they provide.

To ensure that more than one copy of the control file is being maintained, you can query the v$parameter table to see the names of the control files currently being updated. This table, however, often truncates the names of the control files. Another way is to put LIST on a line by itself at the start of the INIT.ORA file. When the instance starts, this parameter lists all the parameters used by the instance. The control files parameter shows the full names and locations of the control files used.

If a failure occurs with a control file—for example, if it is accidentally deleted—remove the name of the missing control file from the CONTROL_FILES parameter of the INIT.ORA file. Then shut down and restart the instance.

Trace and Alert Files

In every Oracle instance, the background processes produce trace files and an alert file. The alert file records significant events in the life of the instance, such as instance startups and shutdowns and redo log file switches. It provides information on errors that occur on the Oracle database.

The BACKGROUND_DUMP_DEST INIT.ORA parameter specifies the location of the trace files and the alert file. You can safely delete the trace and alert files, for they are recreated when the background processes need them.

In addition to the trace produced by the background processes, user processes produce trace files, which are usually used for debugging and performance optimization. These trace files are produced in different ways in the Oracle tools. For example, alter session set SQL_TRACE true is used to start tracing in a SQL*Plus or an Oracle reports module. Likewise, setting the statistics option in Oracle Forms shows the number of cursors used during the runform session and produces a trace file.

The name of the trace file is platform-dependent, and the location is given by the USER_DUMP_DEST INIT.ORA parameter. The raw trace files produced are not easy to deal with. Use the TKPROF Oracle utility to format the trace files so that they are easier to read. You can see information such as the number of logical and physical block reads, parses, executions, and fetches for the different SQL and PL/SQL statements that occurred while the trace was running. To sort the trace output, use the command line parameters of TKPROF. Show the slowest statements at the top of the trace output.

Information about the CPU and elapsed time taken by the statements appears only if the TIMED_STATISTICS INIT.ORA parameter is set to TRUE.

You can also set tracing on for the whole Oracle instance. To do this, set the SQL_TRACE INIT.ORA parameter to TRUE. The usual effect, however, is drastically reduced instance performance because all the user-generated and system-generated statements must be recorded in the trace files.

Database Modes

In this part, I discuss two modes in which the database can operate: the default, which means that redo logs are not archived when filled, and ARCHIVELOG mode, which essentially means that copies of the redo log files are made when they fill.

NOARCHIVELOG Mode

The default mode of a database is NOARCHIVELOG. When one of the online redo log files fills, a checkpoint is made and further old and new values are written to the next online redo log file. The redo log files are not archived to the archive destination. Recovery is limited to instance recovery. That is, you can protect against the instance crashing. However, if you lose the database or other files, you must restore the files to the latest backup, and all changes made to the database since the last backup cannot be reapplied. This provides the simplest setup. This setup could be applicable for a development database where losing the database is not such a big deal and where the resources to manage archiving are not available. It is also simpler to maintain because the problem of not having enough space to archive the redo logs does not occur.

ARCHIVELOG Mode

If the database runs in ARCHIVELOG mode, the redo logs are copied over to the archive destination whenever one of the online redo log files fills. The background process, ARCH, should be set up to copy the online redo log file to the archive destination automatically. Otherwise, the DBA must manually copy the files when they fill. If the online redo log files cannot be archived—either automatically by the ARCH background process or manually by the DBA—the whole database waits until the archiving is done. Setting the database in ARCHIVELOG mode obviously involves more work for the DBA, but it provides an extra level of protection. If the database, control, or redo log files is lost, they can be restored from the most recent backup, and all the changes made since the last backup can be automatically reapplied from a combination of the archived and online redo logs.

Changing the Mode of the Database

To change the mode of the database from the default mode, NOARCHIVELOG, to ARCHIVELOG so that redo log files are archived, perform the following steps in the SQL*DBA tool:

1. Shut down and restart the instance in STARTUP MOUNT mode.
   
   
2. Run the ALTER DATABASE ARCHIVELOG statement to start archiving.
   
   
3. Ensure that the ARCHIVELOG_START INIT.ORA parameter is set to TRUE to enable automatic archiving and that ARCHIVELOG_DEST is set to the destination where you want the offline redo log files copied.
   
   
4. Shut down and restart the instance normally. Check that the offline redo log files are being produced by looking at which files are produced in the archive destination or by checking the alert file.
   
   

Useful Data Dictionary Views

Table 11.2 describes the data dictionary objects. The objects are classified by purpose:

If you are not familiar with the dictionary tables and views, ignore the AUDIT, DUP, and PREV objects. Oracle auditing is rarely used. DUP views show information that is available elsewhere. PREV views are provided for compatibility with previous versions of Oracle.

Table 11.2. Data dictionary objects.

Object Purpose

Prefix

Name of Object

Description

Summary

In this chapter, you learned how to create a database, how to set the mode of the database to ARCHIVELOG, and how to bring an instance up and down. You also learned about the initialization parameters for an instance and the information available in the data dictionary. The fundamentals covered in this chapter are the same for whatever platform your Oracle server runs on. Study your installation and user's guide to see whether any specific differences exist for your platform.