Shutting Down Database

You may need to shut down a database for a number of reasons, for example, for some types of backup, for upgrades of software, and so on. You have several options for shutting down a running database. The option you choose has several implications for the time it takes to shut down the database and any potential need of database instance recovery upon a consequent starting up of the database. The following sections cover the four available shutdown command options for the Oracle9i database.

Shutdown Normal

When you issue the shutdown normal command to shut the database down, Oracle will wait for all users to disconnect from the database before shutting the database down. That is, if a user goes on vacation for a week after logging into a database and you subsequently issue a shutdown normal command, the database will have to keep running until the user returns. The normal mode is Oracle's default mode for shutting down the database. The command is issued as follows:

Sql> shutdown normal
OR
Sql> shutdown

The shutdown normal command involves the following:

• No new user connections can be made to the database.

• Oracle waits for all users to exit their sessions.

• No instance recovery is needed when you restart the database because Oracle will write all redo log buffers and data block buffers to disk before shutting down. Thus, the database will be consistent when it's shut down in this way.

• Oracle closes the data files and terminates the background processes. Oracle's SGA is deallocated.

Shutdown Transactional

If you don't want to wait for a long time for a user to log off, you can use the shutdown transactional command. Oracle will wait for all active transactions to complete before disconnecting all users from the database, and then it will shut down the database.

Sql> shutdown transactional

The shutdown transactional command involves the following:

• No new user connections are permitted.

• Existing users can't start a new transaction and will be disconnected.

• If a user has a transaction in progress, Oracle will wait until the transaction is completed before disconnecting the user.

• After all existing transactions are completed, Oracle shuts down the instance and deallocates memory. Oracle writes all redo log buffers and data block buffers to disk.

• No instance recovery is needed because the database is consistent.

Shutdown Immediate

Sometimes, a user may be running a very long transaction when you decide to shut down the database. Both of the previously discussed shutdown modes are worthless to you under such circumstances. Under the shutdown immediate mode, Oracle will neither wait indefinitely for users to log off nor wait for any transaction to complete. It simply rolls back all active transactions, disconnects all connected users, and shuts the database down. Here is the command:

Sql> shutdown immediate

The shutdown immediate operation involves the following:

• No new user connections are allowed.

• Oracle immediately disconnects all users.

• Oracle terminates all currently executing transactions.

• For all transactions terminated midway, Oracle will perform a rollback so the database ends up consistent. This rollback process is why the shutdown immediate operation is not always immediate. This is because Oracle is busy rolling back the transactions it just terminated. However, if there are no active transactions, the shutdown immediate command will shut down the database very quickly. Oracle terminates the background processes and deallocates memory.

• No instance recovery is needed upon starting up the database because it is consistent when shut down.

Shutdown Abort

The shutdown abort command is a very abrupt shutting down of the database. Currently running transactions are neither allowed to complete nor rolled back. The user connections are just disconnected.

Sql> shutdown abort

The shutdown abort command involves the following:

• No new connections are permitted.

• Existing sessions are terminated, regardless of whether they have an active transaction or not.

• Oracle doesn't roll back the terminated transactions.

• Oracle doesn't write the redo log buffers and data buffers to disk.

• Oracle terminates the background processes, deallocates memory immediately, and shuts down.

• Upon a restart, Oracle will perform an automatic instance recovery, because the database isn't guaranteed to be consistent when shut down.

When you shut down the database using the shutdown abort command, upon recovery the database has to perform instance recovery to make the database transactionally consistent because there may be uncommitted transactions that need to be rolled back. The critical thing to remember about the shutdown abort command is this: The database may be shut down in an inconsistent mode. That's the reason Oracle recommends that you always shut down the database in a consistent mode by using the shutdown or shutdown immediate command and not the shutdown abort command before backing it up. In most cases, you aren't required to explicitly use a recover command, because the database will perform the instance recovery on its own.

Startup Database

You can start up and shut down your Oracle database through different interfaces.

Starting the Database

When you issue the startup command, Oracle will look for the initialization parameters in the default location, $ORACLE_HOME/dbs (UNIX). There, Oracle will look for the relevant files in the following order:

SPFILE$ORACLE_SID.ora

SPFILE.ora

Init$ORACLE_SID.ora

You can start the database in several modes. Let's take a quick look at the different options you have while starting up a database.

The Startup Nomount Command

You can start up the database with just the instance running by using the startup nomount command. The control files aren't read and the data files aren't opened when you open a database under this mode. The Oracle background processes are started up and the SGA is allocated to Oracle by the operating system. In fact, the instance is running by itself, rather like the engine of a tractor trailer being started with no trailer attached to the cab (you can't do much with either

SQL> connect / as sysdba Connected to an idle instance. SQL> startup nomount ORACLE instance started. Total System Global Area 156147688 bytes Fixed Size 438248 bytes Variable Size 146800640 bytes Database Buffers 8388608 bytes Redo Buffers 520192 bytes

What good does it do to have the database instance running without opening the data files for access? Well, sometimes during certain maintenance operations and during recovery times, you can't have the database open for public access. That's when this "partial open" of the database is necessary. During database creation and when you have to re-create control files, you use the nomount start-up option.

The Startup Mount Command

The next step in the database start-up process, after the instance is started, is the mounting of the database. This step reads the control file and mounts (i.e., connects) the data files to the database instance. You can do this in two ways. You can either use the alter database command to mount an already started instance, or you can use the startup mount command in the beginning, as shown in .

Sql> alter database mount;

Database altered.

Sql>

OR,

SQL> startup mount

ORACLE instance started.

Total System Global Area 156147688 bytes

Fixed Size 438248 bytes

Variable Size 146800640 bytes

Database Buffers 8388608 bytes

Redo Buffers 520192 bytes

Database mounted.

SQL>

During the mount stage, Oracle will associate the instance with the database. Oracle will open and read the control files, and get the names and locations of the data files and the redo log files. You usually need to start up a database in the mount mode when you're doing activities such as performing a full database recovery, changing the archive logging mode of the database, or renaming data files. Note that all three operations mentioned here require Oracle to access the data files, but can't accommodate any user operations in these files. Hence the opening of the database in the mount mode, with access to the general users still cut off.

The Startup Open Command

The last stage of the start-up process is the database open stage. The database is open for all users, not just the DBA. Prior to this stage, the general users can't connect to the database at all. You can bring the database into the open mode by issuing the alter database command as follows:

Sql> alter database open;

Database altered.

When the database is started in the open mode, all valid users can connect to the database and perform database operations. To open the database, the Oracle server will first open all the data files and the online redo log files and verify that the database is consistent. If the database isn't consistent—for example, if the SCNs in the control files don't match some of the SCNs in the data file headers—the background process will automatically perform an instance recovery before opening the database. If media recovery rather than instance recovery is needed, Oracle will signal that a database recovery is called for and won't open the database until you perform the recovery.

SQL> startup

ORACLE instance started.

Total System Global Area 156147688 bytes

Fixed Size 438248 bytes

Variable Size 146800640 bytes

Database Buffers 8388608 bytes

Redo Buffers 520192 bytes

Database mounted.

Database opened.

SQL>

Create the Database

You can create a new database either manually (using scripts) or by using the Oracle Database Configuration Assistant (DBCA). DBCA is configured to appear immediately after the installation of the Oracle9i software to assist you in creating a database. You can also invoke DBCA later on to help you create a database.

DBCA has several benefits, including the provision of templates for creating DSS, OLTP, or hybrid databases. You can run the tool in an interactive or "silent" mode. The biggest benefit to using DBCA is that for DBAs with little experience, it lets Oracle set all the configuration parameters and start up a new database quickly without errors. Finally, DBCA also automatically creates all its file systems based on the highly utilitarian Oracle Flexible Architecture (OFA) standard.

DBCA is an excellent tool that will help you create a new database quickly without your having to type in any database creation commands or use any scripts. The tool helps you create both small and large databases very easily, and it even allows you to register a new database automatically with Oracle Internet Directory (OID). However, I recommend strongly that you use the manual approach initially, so you can get a good idea of what initialization parameters to pick and how the database is created step by step. Once you gain sufficient confidence, of course, DBCA is without a doubt the best choice for creating an Oracle database of any size and complexity.

Whether you create a database manually or let Oracle create one for you at software installation, a configuration file called the init.ora file or its newer equivalent, the SPFILE, holds all the database configuration details. After the initial creation of the database, you can always change the behavior of the database by changing the init.ora file parameters. You can also change the behavior of the database for brief periods or during some sessions by using the alter system and alter session commands to temporarily modify some parameter values.

You need to perform certain steps before you can create a database. Among other things, you need to make sure you have the necessary software and the memory and storage resources to successfully create the database. The next few sections run down the brief list of preliminary steps.

Installing the Software

Before you can create a database, you must first install the Oracle9i software. If you currently have other Oracle9i databases running on your system, then of course you are already set and can proceed to the creation of the database itself.

Creating the File System for the Database

Planning your file systems is an important task you need to complete before you get down to creating the database. The location of the various files such as the redo log files and archive log files has to be carefully thought out beforehand. Similarly, the placement of the table and index data has serious implications for performance down the road. Two issues you need to focus on with regard to your file system are its size and location. Let's look at both of these issues in some detail.

Sizing the File System

It is a good idea to systematically figure out how big your database is going to be in terms of the total space required. Your overall space estimate should include estimates for the following:

• Space for the tables: Table data is the biggest component of the physical database. You need to first estimate the size of all the tables by getting information regarding the columns included in the tables. You also need row estimates for all the major tables. You don't need accurate numbers here; roughly accurate figures should suffice.

• Space for the indexes: There are formulas you can use to figure out the space required by the indexes in your database. First, though, you must know the indexes needed by your application. You also need to know the type of indexes you're going to create, as this has a major bearing on the physical size of the indexes.

• Space for the undo tablespace: The space that needs to be allocated to the undo tablespace depends on the size of your database and the nature of your transactions. If you anticipate a lot of large transactions or you need to plan for large batch jobs, you will require a fairly large undo tablespace.

• Space for the temporary tablespace: The temporary tablespace size also depends on the nature of your application and the transaction pattern. If the queries involve a lot of sorting operations, you're better off with a larger temporary tablespace in general. Note that you'll be creating the temporary tablespace with the create temporary tablespace command. This temporary tablespace will be designated during the creation of the database as the default temporary tablespace for the users in the database.

Choosing the Location for the Files

If you've been following the OFA guidelines you learned about in , you'll place the various files of the database such as the system, redo log, and archive log files so you can benefit from the OFA guidelines. The following list summarizes the benefits of using the OFA guidelines for file placement in your database. OFA-based files will

• Make it easy for you to locate and identify the various files such as the database files, control files, and redo log files

• Make it easy to administer multiple Oracle databases and multiple Oracle software versions

• Improve database performance by minimizing contention between competing types of files

Tip

In addition to laying out the files in the OFA format, you need to put the data and index files on different drives for performance reasons. If you're going to have several data files, it's a good idea to stripe them across several spindles. This will improve the I/O performance in your database. which discusses instance tuning, explains striping and other disk-related issues in detail.

Sizing the Redo Log Files

Redo log files are critical for the functioning of a database, and they're key components when you're trying to recover the database without any loss in committed data. Here are some other points about redo log files:

• Oracle recommends a minimum of two redo log groups (each group can have one or more members). Redo log files need to be multiplexed—that is, you should have more than a single redo log file in each group, because they're a critical part of the database and they're a single point of failure in the database.

• The size of the redo log file will depend on how fast your database is writing to the log. If you have a lot of DML operations in your database and the redo logs seem to be filling up very fast, you may want to increase the size of the log file. You can't increase the size of an existing redo log file, though—what I mean here is that you can create larger files and drop the smaller redo log files. The redo log files are written in a circular fashion, and your goal should be to size the log files such that no more than two to three redo log files are filled up every hour. The fundamental conflict here is between performance and recovery time. A very large redo log file will be efficient because there won't be many log switches and associated checkpoints, all of which impose a performance overhead on the database. However, when you need to perform recovery, larger redo logs take more time to recover from because you have more data to recover due to infrequent checkpointing.

If you have followed the OFA guidelines while installing your software, you should be in good shape regarding the way your files are physically laid out.

Ensuring Enough Memory Is Allocated

If you don't have enough memory on the system to satisfy the requirements of your database, your database instance will fail to start. Even if it does start, there will be a severe penalty to be paid by the system in the form of memory paging and swapping, which will slow your database down. Memory cost is such a small part of enterprise computing costs these days that you're better off getting a large amount of memory for the server on which you plan to install the Oracle database.

Getting Necessary Authorizations

You will need authorizations to be granted by the UNIX/Linux or Windows system administrator for you to be able to create file systems on the server. Your Oracle username should be included in the DBA group by the system administrator if you are working on a UNIX or a Linux server. If you are working on a Windows server, the system administrator should give you the appropriate administrative privileges as specified in the Oracle installation manual for Windows.

Setting the Operating System Environment Variables

Before you proceed to create the database, you must set all the necessary operating system environment variables. In Windows systems there is less need to set any specific variables, but in UNIX and Linux environments, you must set the following environment variables:

• ORACLE_SID: This is your database's name. For this chapter's purposes, you should set this variable to remorse.

• ORACLE_BASE: This is the directory at the top of the Oracle software. For this chapter's purposes, this is :/u01/app/oracle.

• ORACLE_HOME: This is the directory in which you installed the Oracle software. Oracle recommends you use the following format for this variable: $ORACLE_BASE/product/release. For this chapter's purposes, this is /u01/app/oracle/product/9.2.0.1.0.

• PATH: This is the directory in which Oracle's executable files are located. Oracle's executables are always located in the $ORACLE_HOME/bin directory. You can add the Oracle's executable files location to the existing PATH value in the following way:

  Export PATH=$PATH:$ORACLE_HOME/bin
  
  

• LD_LIBRARY_PATH: this variable points out where the Oracle libraries are located. The usual location is the $ORACLE_HOME/lib directory.

Creating the Initialization File

Every Oracle instance needs resources such as memory for the various components of the SGA. In addition, you must sometimes specify or limit how much of the system resources the instance can use. Oracle uses database parameter files, which list the names of the parameters and the values for each. An initialization parameter file, known as the initdb_name.ora, was traditionally the only type of file in which you could store these initialization parameter values. By default, this file is located in the $ORACLE_HOME/dbs directory, and again it's up to you to store it in a place that's helpful to you. When you store the configuration file in any location other than the default location, you must specify the complete location when you start the instance. If the initialization filename and the location follow the default conventions, you don't have to provide the name or location of the configuration file at start-up time.

Note

The initialization files are used not only to create the database itself initially, but also to tune its performance later on by modifying parameter values. You can change some of these parameters dynamically while the database is running, but to change the others you'll have to restart your database.

The initialization file includes parameters that will help tune the instance. It also contains parameters that set limits on certain database resources and parameters that specify the name and location of some important files. The variables that affect performance are called variable parameters by Oracle, and these are the variables DBAs are mostly interested in. Once the initialization file is ready, you can start the instance by invoking the file. However, you can dynamically modify several important configuration parameters while the instance is running. These modifications won't be permanent; as soon as you shut down the database, the changes are gone and you're back to the values hard-coded in the init.ora file. If you want to make the dynamic changes permanent so the database will come up with these new values upon a restart, you should use a server parameter file, also known as the SPFILE. The SPFILE is also an initialization file, but you can't make changes to it directly because it's a binary file, not a text file. Using the SPFILE to manage your instance provides several benefits, as you'll see in the section "The Server Parameter File (SPFILE)" later in the chapter.

In the sections that follow, I group the initialization parameters into sets of related parameters to make it easier to understand the configuration of a new database. My parameter groupings are purely arbitrary and are mainly for exposition purposes. Oracle provides a template to make it easy for you to create your own customized file. This file is located in the $ORACLE_HOME/dbs directory in UNIX systems and in the $ORACLE_HOME/database directory in Windows-based systems. You can copy this init.ora template and name it initdb_name.ora, and you can then edit it per your own site's requirements. Don't be nervous about trying to make "correct" estimates for the various configuration parameters. Most of the configuration parameters are easily modifiable throughout the life of the database. Just make sure you're careful about the handful of parameters that you can't change without redoing the entire database from scratch.

The interesting thing about the init.ora file is that it contains the configuration parameters for memory and some I/O parameters, but not the database filenames or the tablespaces the data files belong to. The control file holds all that information. The initialization file, though, has the locations of files such as the control files, the redo log files, and the dump directories for error messages. The initialization file also specifies the mode chosen for the undo management, the optimizer mode, and the archiving mode for the redo logs.

Note

All the parameters in the initialization file are optional. That is, if you don't have any parameters configured in your init.ora file, Oracle will apply default values for all the parameters and your database will be successfully started. For example, I can start a brand-new instance called "remorse" very quickly by using this short init.ora file: db_name = REMORSE control_files = (/u01/app/oracle/control1,/u01/app/oracle/control2)

As you can imagine, this means you won't have any control over the behavior of the configurable parameters. You should leave parameters out of the init.ora file only after you ascertain that their default values are OK for your database. In general, it's a good idea to use approximate sizes for the important configuration parameters you know well and use a trial-and-error method to decide whether to use newer or never-before-used parameters.

Oracle9i is famous for being a highly configurable database, but that benefit also carries with it the need for DBAs to expend the necessary energy to learn how these large numbers of parameters work. Most important, you should learn how the parameters may interact with one another at times, thereby producing a result that is at variance with your initial plans. To give you an elementary example, an increase in the SGA size may increase database performance up to a point. After that, any increase in SGA might actually slow the database down, because the operating system may be induced to swap the higher SGA in and out of real memory. Beware of configuration changes, and always think through the implications of "slight" changes in the parameter file.

Changing the Initialization Parameter Values

You can change the value of any initialization parameter by simply editing the init.ora file. However, for the changes to actually take effect, you have to bounce the database, or stop and start it again. As you can imagine, this is not always possible, especially if you are managing a production database. However, you can change several of the parameters "on the fly," and these are called dynamic parameters for that reason. The parameters you can change only by restarting the database after changing the init.ora file are called static parameters.

You have three ways to change the value of dynamic parameters. You can use the alter session, alter system, or alter system … deferred command option to change the parameter values.

Using the Alter Session Command

The alter session command enables you to change the dynamic parameter values for the duration of the session that issues the command. Obviously, you are going to use the alter session command only to change a parameter's value temporarily. Here is the general syntax for the command:

Alter session set parameter_name=value;

Using the Alter System Command

The alter system command changes the parameter's value for all sessions. However, these changes will be in force only for the duration of the instance; when the database is restarted, these changes will go away unless you modify the init.ora file accordingly or you use the SPFILE. Here is the syntax for this command:

Alter system set parameter_name=value;

Using the Alter System … Deferred Command

The alter system … deferred command will make the new values for a parameter effective for all sessions, but not immediately. Only new sessions started after the command is issued are affected. All currently open sessions will continue to use the old parameter values.

Alter system set parameter_name deferred;

The alter system … deferred command works only for the following parameters: backup_tape_io_slaves, transaction_auditing, sort_area_retained_size, object_cache_optimal_size, sort_area_size, and object_cache_max_size_percent. Because of the very small number of parameters to whom the "deferred" status applies, you can, for all practical purposes, consider alter system a command that applies immediately to all sessions.

Important Oracle9i Initialization Parameters

The following sections present some of the important Oracle initialization parameters you need to be familiar with. For the sake of clarity, I've assigned the parameters to various groups.

Although this list looks long and formidable, it isn't really a complete list of initialization parameters that you can configure for the Oracle9i database—it's a list of only the most commonly used parameters. Oracle9i has over 250 initialization parameters that DBAs can configure. Don't be disheartened, though. The basic list of parameters that you need to start your new database could be fairly small and easy to understand. Later on, as you study various topics such as backup and recovery, performance tuning, networking, and so on, you'll have a chance to really understand how to use the more esoteric initialization parameters.

Database Name and Other General Parameters

Most important among the name parameters, of course, is the parameter that sets the name of the database. Let's look at this set of parameters in detail.

Db_Name

The db_name parameter sets the name of the database. This parameter can't be changed after the database is created. You can have a db_name parameter of up to eight characters.

For the purposes of this chapter, you'll name your database remorse, and this will be the db_name parameter's value. Note that this parameter is optional; Oracle can get the name of the database from the create database statement if this parameter is omitted.

Default: false
Type: Static.

Db_Domain

The db_domain parameter gives a fully qualified name for the database. You'll use the default .world name, so your db_domain will be remorse.world.

Default: false
Type: Static.

Instance_Name

The instance_name parameter will have the same value as the db_name parameter, which is remorse for your database.

Default: false
Type: Static.

Service_Name

The service_name parameter provides a name for the database service, and it can be anything you want it to be. Usually, it is a combination of the database name and your database domain.

Default: DB_NAME.DB_DOMAIN
Type: Dynamic, can be changed with the 'alter system' command.

Compatible

Suppose you upgrade to the Oracle9i version, but your application developers haven't made any changes to the Oracle8i application. You need to set your compatibility parameter equal to 8i, so the untested features of the new version you're using won't hurt your application. Later on, after the application has been suitably upgraded, you can reset the compatible initialization parameter to Oracle9i.

Default: false
Type: Static.

Dispatchers

The dispatchers parameter configures the dispatcher process if you choose to run your database in the shared server mode.

Default: None
Type: Dynamic. 'Alter system' command can be used to reconfigure the dispatchers.

Nls_Date_Format

The nls_date_format parameter specifies the default date format Oracle will use. Oracle uses this date format when using the to-char or to-date function in SQL. There is a default value, which is derived from the nls_territory parameter. For example, if the nls_territory format is America, the nls_date_format parameter is automatically set to the DD-MON-YY format.

Default: Depends on the nls_territory variable and the operating system.
Type: Dynamic. Can be altered by using the 'alter session' command.
 File-Related Parameters

You can specify several file-related parameters in your init.ora file. Oracle requires you to specify several destination locations for trace files and error messages. The bdump, udump, and cdump files are used by the database to store the alert logs, background trace files, and core dump files. In addition, you need to specify the utl_file_directory parameter for using the UTL_FILE package. The following sections cover the key file-related parameters.

Control_Files

Control files are key files that hold information regarding the data file names and locations, and a lot of other important information. The database needs only one control file, but because this is such an important file, you always save multiple copies of it. The way to multiplex the control file is to simply specify multiple locations (two or three, although you can go up to the maximum Oracle allows) for the control_files parameter. The minimum number of control files is one. Oracle recommends at least two control files per instance, but three seems to be the number most commonly used by DBAs.

Default: false
Type: Static.

Db_Files

The db_files parameter simply specifies the maximum number of files allowed to be created in the database. This is just a number, and you don't list all the data files for your database here. In fact, the specification of the files and the tablespaces comes during the creation of the database itself. The larger the size of the database, the larger this number should be. For a large warehouse, you can set the value of the db_files parameter to 1000 or greater.

Default: 200
Type: Static

Core_Dump_Dest

The core_dump_dest parameter specifies the location where you want the core (error) messages dumped to.

Default: Depends on the operating system. You can use any valid directory.
Type: Dynamic, can be changed with the 'alter system' command.

User_Dump_Dest

This is the directory where you want Oracle to save error messages from various processes such as PMON and the database writer.

Default: Depends on the operating system. You can use any valid directory.
Type: Dynamic, can be changed with the 'alter system' command.

Background_Dump_Dest

This parameter specifies the Oracle alert log location and the locations of some other trace file for the instance.

Default:  Depends on the operating system. You can use any valid directory.
Type: Dynamic, can be changed with the 'alter system' command.

Utl_File_Directory

You can use the utl_file_directory parameter to specify the directory (or directories) Oracle will use to process I/O when you use the Oracle UTL_FILE package to read from or write to the operating system files.

Default: None. You can't use the utl_file package
to do any I/O under this scenario.
Type: Static. You can set the utl_file_dir to any OS
directory you want. If you just specify *, instead of any
specific directory name, the utl_file package will read and
write to and from all the OS directories, and Oracle
recommends against this practice.

Oracle Managed Files Parameters

The test database that you're going to create doesn't use the Oracle Managed Files (OMF) feature, so the parameter will remain blank. If you were to use the OMF feature, however, this is the parameter that you'll need to include to enable your database to use the OMF files. You'll usually need to use two parameters, both of which specify the format of the OMF files when you decide to use the feature.

Db_Create_File_Dest

The db_create_file_dest parameter denotes the directory where Oracle will create data files and temporary files when you don't specify an explicit location for them. The directory must exist already with the right read/write permissions for Oracle.

Default: false
Type: Dynamic, can be changed using either the 'alter system' or
the 'alter session' command.

Db_Create_Online_Log_Dest_n

This parameter specifies where you want the OMF online redo log files to be created by default. To multiplex the online redo log files, specify more than one value for the parameter. You can have a maximum of five separate directory locations.

Default: false
Type: Dynamic, can be changed using either the 'alter system'
or the 'alter session' command.

Process and Session Parameters

Several initialization parameters relate to the number of processes and the number of sessions that your database can handle. The following sections explore the important process and session parameters.

Processes

The value of the processes parameter will set the upper limit for the number of operating system processes that can connect to your database concurrently. Both the sessions and transactions parameters derive their default values from this parameter.

Default: 6 (may vary depending on the operating system)
Type: Static.

Db_Writer_Processes

The db_writer_processes parameter specifies the initial number of database writer processes for your instance. Instances with very heavy data modification may opt for more than the default single process. You can have up to 20 processes per instance.

Default: 1
Type: Static.

Sessions

The sessions parameter sets the maximum number of sessions that can connect to the database simultaneously. Actually, this parameter is redundant, because the processes parameter will by default determine the maximum number of sessions also.

Default: (1.1 * Processes) + 5
Type: Static.

Open_Cursors

The open_cursors parameter sets the limit on the number of cursors a single session can have.

Default: 50
Type: Static

Memory Configuration Parameters

The memory configuration parameters determine the memory allocated to key components of the SGA. There are no hard-and-fast rules regarding the right size for these parameters. You allocate an approximate amount to start with, and based on the performance statistics, you fine-tune the allocations after the database starts operating.

Note

Oracle's guidelines regarding the ideal settings for the various components of memory, such as the db_cache_size and shared pool, are often vague and not really helpful to a beginner. For example, Oracle states that the db_cache_size should be 20 percent to 80 percent of the available memory for a data warehouse database. The shared pool recommendation for the same database is 5 percent to 10 percent. Well, the wide ranges make the db_cache_size recommendations useless. If your total memory is 2GB, you're supposed to allocate 100MB to 200MB of memory for the shared pool. If your total memory allocation is 32GB, your allocation for the shared pool would be between 1.6GB and 3.2GB, according to the "standard" recommendations. The best thing to do is use a trial-and-error method to see if the various memory settings are appropriate for your database.

The buffer cache and the shared pool are the two main components of Oracle's instance memory, with the other important components being the PGA and the large pool. The buffer cache is the area of Oracle's memory where it keeps the data blocks read in from the disks. The data blocks may be modified here before being written back to disk again. A big enough buffer cache will improve performance by avoiding too many disk accesses, which are much slower than accessing data in memory.

You can set up the buffer cache for your database in units of the standard block size you chose for the database (using the db_block_size parameter), or you can use nonstandard block sized buffer caches. If you want to base your buffer cache on the standard block size, you use the db_cache_size parameter to size your standard block-based cache. You have to make an educated guess as to the right size of the buffer cache parameter. For larger databases, allocate larger buffer caches. Let's say you want to allocate about 500MB of memory on your system to the buffer cache parameter. The following sections cover the standard block size buffer cache-related initialization parameters.

Db_Cache_Size

This parameter sets the size of the default standard block-sized cache. For example, you can use a number like 1024MB.

Default: 48 MB
Type: Dynamic, can be modified with the 'alter system' command.

The normal behavior of the buffer pool is to treat all the objects placed in it equally. That is, any object will remain there as long as free memory is available in the buffer cache. Objects are removed or "aged out" only when there is no free space. When this happens, the least recently used (LRU) algorithm is used to remove the oldest unused objects sitting in memory to make space for new objects. The use of two specialized buffer tools, the keep pool and the recycle pool, allows you to specify at object creation time how you want the buffer pool to treat certain objects.

For example, if you know that certain objects don't really need to be kept in memory for long, you can have them assigned to a recycle pool, which removes the objects that aren't needed anymore as soon as they're used. Similarly, the keep pool always retains an object in memory if it's created with the keep option. The following sections cover the two relevant parameters for configuring multiple buffer pools.

Db_Keep_Cache_Size

The db_keep_cache_size parameter specifies the size of the keep pool. If you store objects in the keep pool of the buffer cache, Oracle will ensure that they will never age out of the pool.

Example: db_keep_cache_size = 5
Default: 0 Megabytes. By default, this is not configured.
Type: Dynamic. Can be changed by using the 'alter system' command.

Db_Recycle_Cache_Size

The db_recycle_cache_size parameter specifies the size of the recycle pool in the buffer cache. Oracle removes objects from this pool as soon as the objects are used.

Example: db_cycle
Default: 0 Megabytes. By default, this is not configured.
Type: Dynamic. Can be changed by using the 'alter system' command.

Db_nK_Cache_Size

If you prefer to use nonstandard-sized buffer caches, for each of the nonstandard-sized buffer cache you need to specify the db_nk_cache_size parameter, as in the following example: db_4k_cache_size=2048MB or db_8k_cache_size=4096MB. The range of values for this parameter is 2K, 4K, 8K, 16K, and 32K.

Default: 0 Megabytes.
Type: Dynamic. You can change this parameter's value with the
'alter system' command.

Shared_Pool_Size

The shared pool is a critical part of Oracle's memory, and the shared_pool_size parameter sets the total size of the SGA that is devoted to the shared pool. The shared pool consists of the data dictionary cache and the library cache. The data dictionary cache stores the recently used data dictionary information, so you don't have to constantly hit the disk to access the data dictionary.

Remember that the data dictionary is one of the most frequently consulted parts of any Oracle database. Before any query can execute, the data dictionary is consulted to verify the objects involved, user privileges, and a bunch of other important things. There is no way to separately manipulate the sizes of the two components of the shared pool. If you want to increase the size of either component of the shared pool or both of them at once, you do it through increasing the value of the shared_pool_size parameter. Oracle recommends 5 percent to 10 percent of the total memory for the shared pool for a data warehouse and a larger proportion for OLTP databases.

Default: 16 Megabytes for non-64 Bit Operating Systems, 64 Megabytes for 64 Bit.
Type: Dynamic. The 'alter system' command can be used to
change it to OS-dependent maximum size.

Shared_Pool_Reserved_Size

This parameter sets the amount of space to be reserved in the shared pool for holding large queries or packages.

Default: Five percent of shared_pool_size.
Type: Static. You can increase this to half of the total shared_pool size.

Pga_Aggregate_Target

Users need areas in memory to perform certain memory-intensive operations, such as sorting, hash joining, bitmap merging, and so on. The pga_aggregate_target parameter is the total amount of memory allocated to the instance so it can be assigned to users as "work areas" to perform the previously mentioned memory-heavy jobs. contains a detailed discussion of the PGA and how to size it. By setting the pga_aggregate_target parameter, you let Oracle manage the runtime memory management for SQL execution. The sum of the total PGA memory allocated to all sessions in this instance cannot exceed the value of this parameter.

Default: 0
Type: Dynamic. The 'alter system' command can be used to modify this parameter.

Tip

You can adjust the pga_aggregate_target parameter dynamically using the alter system command. The target value should range between 10MB and 4096GB. Oracle recommends that the pga_aggregate_target parameter should be between 20 percent and 80 percent of the available memory.

Log_Buffer

The log_buffer parameter indicates the size of the redo log buffer. As you recall, the redo log buffer holds the redo records, which are used to recover a database. The log writer writes the contents of this buffer to the redo log files on disk. The log buffer's size is usually set to a small amount, under about a megabyte or so. The more changes the redo buffers have to process using redo records, the more active the redo logs will be. Instead of adjusting the log_buffer parameter to a very large size, you may want to use the nologging option to reduce redo operations.

Default: Maximum of 512 Kilobytes or 128 Kilobytes * Number of CPUS,
whichever is greater.
Type: Static.

Large_Pool

The shared pool can normally take care of the memory needs of shared servers as well as Oracle backup and restore operations and a few other operations. But sometimes this may place a heavy burden on the shared pool, causing a lot of fragmentation in it and also the premature aging-out recycling of important objects from the shared pool due to lack of space.

To avoid these problems, Oracle enables you to use a parameter called large_pool, which is used for the previously mentioned specialized operations, thus freeing up the shared pool mostly for caching SQL queries and the data dictionary. If the parallel_automatic_tuning parameter is set, the large pool is also used for parallel-execution message buffers. The amount of memory for the large pool in this case depends on the number of parallel threads per CPU and the number of CPUs.

Default: Zero if the pool is not required for parallel
execution and DBWR_IO_SLAVES is not set.
Type: Static.
Range: 600K to 2 Gigabytes

Java_Pool_Size

Use this parameter only if your database is using Java stored procedures. Other-wise, you can leave it out of the init.ora file.

Default: 20000 Bytes
Type: Static.
Range: 1Megabyte to 1Gigabyte

Sga_Maximum_Size

You can also set a maximum limit for the memory that can be used by all the components of the SGA with the sga_maximum_size parameter. This is an optional parameter, because omitting it just means that the SGA's maximum size will default to the sum of the memory parameters in the SGA.

Default: false
Type: Static.

Lock_Sga

Setting the value of the lock_sga parameter to true will lock your entire SGA into the host physical memory. This works only on some operating systems, and you should set this parameter to true only after verification. As mentioned in Chapter 5, Oracle doesn't recommend using this parameter under most circumstances.

Default: Depends on the values of the component variables.
Type: Static.

Db_Cache_Advice

Once you start the instance with an approximate memory sizes, you can have Oracle itself advise you on the best levels for the buffer size based on the cache miss rates for various hypothetical cache sizes. The database will simulate the use of a wide range of buffer cache sizes and store the information. To enable Oracle to do the analysis regarding the ideal value for the database buffer cache size, you must set the db_cache_advice parameter to true in the init.ora file.

Default: Off
Type: Dynamic. 'Alter system' command can be used to switch to off/on.

Archive Log Parameters

Oracle gives you the option of archiving your filled redo logs. When you configure your database to archive its redo logs, the database is said to be in an archivelog mode. You should always archivelog your production databases unless there are exceptional reasons for not doing so. If you decide to archive the redo logs, you have to specify that in the initialization file by specifying the three parameters described in the following sections.

Log_Archive_Dest_n

This parameter enables you to specify the location (multiple) of the archived logs. You should set this parameter only if you are running the database in archivelog mode. You can do this when you create the database in the next section by specifying the archivelog keyword in your create database statement. But when you first create the database, there is no need for archiving to be turned on; you will thus not have a need to specify this parameter.

Default: None
Type: Dynamic. You can use the 'alter session' or the 'alter
system' command to make changes.

Log_Archive_Start

This parameter enables the automatic archiving of the logs. The alternative is to manually archive them, which may not be practical in a busy production system, as you'll see in Chapter 15.

Default: false
Type: Static.

Log_Archive_Format

This parameter specifies the default filename format for the archived redo log files.

Default: Operating system dependent.
Type: Static.

Undo Space Parameters

The main parameters to be configured here are the undo_management mode and the undo_tablespace parameter. The undo management mode will be set to auto in your case, because the remorse database will be configured to use the Automatic Undo Management (AUM) option. The undo_tablespace will be set to UNDOTBSP_01.

Undo_Tablespace

This parameter determines the default tablespace for undo records. If you don't specify one, the database will use the system rollback segment, and this should be avoided. If you don't specify a value for this parameter when you create the database, and you have chosen AUM, Oracle will create a default undo tablespace with the name UNDOTBS. This default tablespace will have a single 10MB data file that will be automatically extended without any maximum limit.

Default: The first undo_tablespace available
Type: Dynamic. You can use the 'alter system' command to
change the default undo tablespace.

Undo_Management

If the mode is set to auto, then the undo tablespace is used for storing the undo records and Oracle will automatically manage the undo segments.

Default: Manual (You need to use rollback segments)
Type: Static. You can use the AUTO value if you want the undo
space management to be automated using the undo tablespace.

Undo_Retention

This parameter specifies the amount of redo information to be saved in the undo tablespace before it can be overwritten. The value for this parameter depends on the size of the undo tablespace and the nature of the queries in your database. If the queries aren't huge, they don't need to have large snapshots of data, and you could get by with a low undo_retention interval. Similarly, if there is plenty of free space available in the undo tablespace, transactions won't be overwritten, which will cause the failure of queries (the "snapshot too old" problem). If you plan on using the Flashback Query feature extensively, you have to figure out how far back in time your Flashback Queries will go and specify the undo_retention parameter accordingly.

Default: 900 (seconds)
Type: Dynamic. You can use the 'alter system' command to
increase the value to a practically unlimited time period.

Rollback Segment Parameters

These parameters need to be set if you're choosing manual management of undo space. It will then list all the rollback segments that have been configured for the database.

You can switch from an AUM mode to the traditional undo management mode by using the alter session statement, as shown here:

Alter session set undo_management_mode=manual;

This parameter sets the standard database block size (for example, 4096, a 4KB block size). You can pick anywhere from 2KB to 32KB (2, 4, 8, 16, and 32) as your db_block_size value. You always should make the db_block_size parameter a multiple of your operating system block size, which you can ascertain from your UNIX or Windows system administrator.

You have to carefully evaluate your application's needs before you pick the correct database block size. Whenever you need to read data from or write data to an Oracle database object, you do so in terms of data blocks.

If you're supporting data warehouse applications, it makes sense to have a very large db_block_size—say, something between 8KB and 32KB. This will improve database performance when it's reading in huge chunks of data from disk. However, if you're dealing with a typical OLTP application where most of your reads and writes consist of relatively short transactions, a large db_block _size would be overkill and could actually lead to inefficiency in input and output operations. Most OLTP transactions read or write a very small number of rows per transaction and conduct numerous transactions with random access I/O (index scans), so you need to have a smaller block size, somewhere between 2KB and 8KB. A large block size for most OLTP applications is going to hurt performance, as the database has to read large amounts of data into memory even when it really needs very small bits of information. A small db_block_size for an OLTP database would reduce slowdowns due to buffer_busy_waits, of which you'll learn a lot more in . Large data warehouses perform more fill table scans and thus perform more sequential data access than random access I/Os.

Default: 2048, range is 2048-32768.
Type: Static.

Db_File_Multiblock_Read_Count

This parameter specifies the maximum number of blocks Oracle will read during a full table scan. The larger the value, the more efficient your full table scans will be, because Oracle will retrieve multiple blocks of data in a single read. The general principle is that data warehouse operations need high multiblock read counts because of the heavy amount of data processing involved. If you are using a 16KB block size for your database and the multiblock read count parameter is set to 16 also, Oracle will read 256KB in a single I/O. Depending on the platform, Oracle supports I/Os up to 1MB. Note that when you stripe your disks, the stripe size should be a multiple of the I/O size for optimum performance. If you are using an OLTP application, a multiblock read count such as 8 or 16 would be ideal. Large data warehouses could go much higher than this.

Default: 8
Type: Dynamic - modifiable with either an 'alter system' or
an 'alter session' command.

Types of Databases

In many organizations, as a DBA you can face on a daily basis the possibility of working with different types of databases and, as a result, different types of data and management requirements. You may find yourself working on simple SQL queries with users and simultaneously wrestling with decision-support systems for management. Databases perform a variety of functions, but you can group all of those functions into two broad categories: online transaction processing (OLTP) and decision support system (DSS) (which is sometimes also called online analytical processing [OLAP]). Let's take a quick look at some of the basic classifications of Oracle databases.

Online Transaction Processing and Decision Support System Databases

The online transaction processing (OLTP) category of databases represents the bread and butter of most consumer- and supplier-oriented databases. This category includes order entry, billing, customer, supplier, and supply-chain databases. These databases are characterized by rapid transactions and a need to be online continuously, which today (given the use of the Internet to access such systems) means 24/7/365 availability, short maintenance intervals, and low tolerance for breakdowns in the system.

Decision support systems (DSSs) range from small databases to large data warehouses. These are typically not 24/7 operations and they can easily manage with regularly scheduled downtime and maintenance windows. The extremely large size of some of these data warehouses necessitates the use of special techniques both to load and to use the data.

There isn't a whole lot of difference between the administration of a DSS-oriented data warehouse and a transaction-oriented OLTP system from the DBA's perspective. The backup and recovery methodology is essentially the same, and database security and other related issues are also very similar. The big difference between the two types of databases occurs at the design and implementation stages. DSS systems usually involve a different optimization strategy for queries and different physical storage strategies. Oracle9i provides you a choice between using an OLTP database and a DSS database using the same database server software.

Performance design considerations that may work well with one type of database may be entirely inappropriate for another type of database. For example, a large number of indexes can help you query a typical data warehouse efficiently while you are getting some reports out of that database. If you have the same number of indexes on a live OLTP system with a large number of concurrent users, you may see a substantial slowing down of the database, because the many updates, inserts, and deletes on the OLTP system require more work on the part of the database. So, know what you are trying to achieve with your new system and design accordingly in terms of choosing indexes, index types, and space parameters for the various objects in your database.

Development, Test, and Production Databases

Oracle databases can generally be grouped into three categories: development, test, and production. Applications are developed, tested, and put into production. A firm usually has all three database versions in use at any given time, although for smaller companies, the test and development versions of the database may be integrated in one database.

Development databases are usually owned by the development team, which has full privileges to access and modify data and objects in those databases. The test databases are designed to simulate actual production databases and are used to test the functionality of code after it comes out of the development databases. No new code is usually implemented in the production or the "real" databases of the company unless it has been successfully tested in the test databases.

When a new application is developed, tested, and put into actual business use (production), the development and production cycle does not end. Application software is always being modified, for two reasons: to fix bugs and to improve the functionality of the application. Although most applications go through several layers of testing before they move into production, coding errors and the pressure to meet deadlines contribute to actual errors in software, which are sometimes caught later when the application is already in use. In addition, users continually request (or, more appropriately, demand) modifications in the software to improve the functionality of the application. Consequently, application code does not remain static; rather, developers and testers are always working on it.

Orkut Secrits

Show a cool STAR in your scraps: Just write “& # 9 7 3 3″ without spaces and quotes in your orkut scarp writing text for testing directly in your friend’s scrapbook. And now submit your scrap. You will see a cool star icon showing up in your scrap. It is one of the simplest of orkut tricks. Isn’t it?.

Unlimited pictures in orkut album : By default an orkut user can upload only 12 pictures in orkut album. Sometimes, we have a lot of pictures to show in our orkut album but due to this restriction on number of picture uploads we could not show all we want to show in our orkut album. But now you can show unlimited number of pictures and photos using the new rss feed feature in orkut.

Sending a blank or invisible scrap

Open your friend’s scrapbook and the write anyone you wanted from the list given below:

[i], [u], [b], [i][u], [i][b], [u][i], [u][b], [u][i][b], [i][u][b], [i][u][b][i]

Now press ‘postscrap’ tab.

Accessing orkut from the place where it has been banned.

You can use proxies like http://www.proxut.com and http://www.orkutlive.info/ to access orkut.

Adding images to orkut scraps

You can send images to your friend through scrap. But the condition is that urls of those images should have .jpg, .gif, .png or .bmp in their ending.

for example, Copy and paste “http://www.desiclub.com/bollywood/photo_album/photoalbums/aamir_khan/aamir3.jpg” in scrapbook and then press ‘postscrap’. Now you will see the images as a scrap in your scrapbook.

Hiding your visits on the other’s profiles.

1) Go to ‘Setting’.
2) Click on ‘privacy’ tab and select hide profile visit.

See large display pic of your friend in your scrapbook

Copy and paste this code “javascript:document.body.innerHTML=document.body.innerHTML.replace(/small/g,”medium”);void(0)” in the address bar.

Enjoy these cool orkut tips and tricks, will be back with many more.