Chapter 6 *
In this chapter, you will understand and demonstrate knowledge in the following areas:
 | The Oracle architecture |
| Starting and stopping the Oracle instance |
| Creating an Oracle database |
A major portion of your understanding of Oracle, both to be a successful Oracle DBA and to be a successful taker of the OCP Exam 2 for Oracle database administration, is understanding the Oracle database architecture. About 22 percent of OCP exam 2 is on material in these areas. Oracle in action consists of many different items, from memory structures, to special processes that make things run faster, to recovery mechanisms that allow the DBA to restore systems after seemingly unrecoverable problems. Whatever the Oracle feature, it’s all here. You should review this chapter carefully, as the concepts presented here will serve as the foundation for material covered in the rest of the book, certification series, and your day-to-day responsibilities as an Oracle DBA.
In this section, you will cover the following topics related to the Oracle architecture:
| Oracle memory structures |
| Oracle background processes |
| Oracle disk utilization structures |
The Oracle database server consists of many different components. Some of these components are memory structures, while others are processes that execute certain tasks behind the scenes. There are also disk resources that store the data that applications use to track data for an entire organization, and special resources designed to allow for recovering data from problems ranging from incorrect entry to disk failure. All three structures of the Oracle database server running together to allow users to read and modify data are referred to as an Oracle instance. Figure 6-1 demonstrates the various disk, memory, and process components of the Oracle instance. All of these features working together allow Oracle to handle data management for applications ranging from small "data marts" with fewer than five users to enterprise-wide client/server applications designed for online transaction processing for 50,000+ users in a global environment.
Focus first on the memory components of the Oracle instance. This set of memory components represents a "living" version of Oracle that is available only when the instance is running. There are two basic memory structures on the Oracle instance. The first and most important is called the System Global Area, which is commonly referred to as the SGA. The other memory structure in the Oracle instance is called the Program Global Area, or PGA. This discussion will explain the components of the SGA and the PGA, and also cover the factors that determine the storage of information about users connected to the Oracle instance.
Figure 1: The Oracle database architecture at a glance
The Oracle SGA is the most important memory structure in Oracle. When DBAs talk about most things related to memory, they are talking about the SGA. The SGA stores several different components of memory usage that are designed to execute processes to obtain data for user queries as quickly as possible while also maximizing the number of concurrent users that can access the Oracle instance. The SGA consists of three different items, listed here.
| The buffer cache |
| The shared pool |
| The redo log buffer |
The buffer cache consists of buffers that are the size of database blocks. They are designed to store data blocks recently used by user SQL statements in order to improve performance for subsequent selects and data changes. The shared pool has two required components and one optional component. The required components of the shared pool are the shared SQL library cache and the data dictionary cache. The optional component of the shared pool includes session information for user processes connecting to the Oracle instance. The final area of the SGA is the redo log buffer, which stores online redo log entries in memory until they can be written to disk.
Explore the usage of the shared pool in the Oracle database architecture. The shared SQL library cache is designed to store parse information for SQL statements executing against the database. Parse information includes the set of database operations that the SQL execution mechanism will perform in order to obtain data requested by the user processes. This information is treated as a shared resource in the library cache. If another user process comes along wanting to run the same query that Oracle has already parsed for another user, the database will recognize the opportunity for reuse and let the user process utilize the parse information already available in the shared pool. Of course, the specific data returned by the query for each user will not reside in the shared pool, and thus not be shared, because sharing data between applications represents a data integrity/security issue.
The other mandatory component of the shared pool is the data dictionary cache, also referred to by many DBAs as the "row" cache. This memory structure is designed to store the data from the Oracle data dictionary in order to improve response time on data dictionary queries. Since all user processes and the Oracle database internal processes use the data dictionary, the database as a whole benefits in terms of performance from the presence of cached dictionary data in memory.
The redo log buffer allows user processes to write their redo log entries to a memory area in order to speed processing on the tracking of database changes. One fact that is important to remember about redo logs and user processes is that every process that makes a change to the database must write an entry to the redo log in order to allow Oracle to recover the change. When the database is set up to archive redo logs, these database changes are kept in order to rebuild database objects in the event of a disk failure. The availability of a buffer for storing redo information in memory prevents the need for user processes to spend the extra time it takes to write an entry directly to disk. By having all user processes writing those redo log records to memory, the Oracle database avoids contention for disk usage that would invariably cause database performance to slow down. Since every data change process has to write a redo log entry, it makes sense that processes be able to write that change as quickly as possible in order to boost speed and avoid problems.
The final SGA resource is the buffer cache. This area of memory allows for selective performance gains on obtaining and changing data. The buffer cache stores data blocks that contain row data that has been selected or updated recently. When the user wants to select data from a table, Oracle looks in the buffer cache to see if the data block that contains the row has already been loaded. If it has, then the buffer cache has achieved its selective performance improvement by not having to look for the data block on disk. If not, then Oracle must locate the data block that contains the row, load it into memory, and present the selected output to the user. There is one overall performance gain that the buffer cache provides that is important to note. No user process ever interfaces directly with any record on a disk. This fact is true for the redo log buffer as well.
After the user’s select statement has completed, Oracle keeps the block in memory according to a special algorithm that eliminates buffers according to how long ago they were used. The procedure is the same for a data change, except that after Oracle writes the data to the row, the block that contains the row will then be called a dirty buffer, which simply means that some row in the buffer has been changed. Another structure exists in the buffer cache, called the dirty buffer write queue, and it is designed to store those dirty buffers until the changes are written back to disk.
The PGA is an area in memory that helps user processes execute, such as bind variable information, sort areas, and other aspects of cursor handling. From the prior discussion of the shared pool, the DBA should know that the database already stores parse trees for recently executed SQL statements in a shared area called the library cache. So, why do the users need their own area to execute? The reason users need their own area in memory to execute is that, even though the parse information for SQL or PL/SQL may already be available, the values that the user wants to execute the search or update upon cannot be shared. The PGA is used to store real values in place of bind variables for executing SQL statements.
The question of location for user session information is an important one to consider. Whether user session information is stored in the PGA or the shared pool depends on whether the multithreaded server (MTS) option is used. MTS relates to how Oracle handles user processes connecting to the database. When the DBA uses MTS, all data is read into the database buffer cache by shared server processes acting on behalf of user processes. When the DBA uses the MTS configuration, session information is stored in the shared pool of the SGA. When MTS is not used, each user process has its own dedicated server process reading data blocks into the buffer cache. . In the dedicated server process configuration, , the PGA stores session information for each user running against Oracle. More information about shared vs. dedicated servers, the MTS architecture, and the purpose of the Server process appear in this discussion, Unit IV, and Unit V of this Guide.
A good deal of the discussion around users thus far speaks of processes--user processes doing this or that. In any Oracle instance, there will be user processes accessing information. . Likewise, the Oracle instance will be doing some things behind the scenes, using background processes. There are several background processes in the Oracle instance. It was mentioned in the discussion of the SGA that no user process ever interfaces directly with I/O. This setup is allowed because the Oracle instance has its own background processes that handle everything from writing changed data blocks onto disk to securing locks on remote databases for record changes in situations where the Oracle instance is set up to run in a distributed environment. The following list presents each background process and its role in the Oracle instance.
| DBWR | The database writer process. This background process handles all data block writes to disk. It works in conjunction with the Oracle database buffer cache memory structure. It prevents users from ever accessing a disk to perform a data change such as update, insert, or delete. |
| LGWR | The log writer process. This background process handles the writing of redo log entries from the redo log buffer to online redo log files on disk. This process also writes the log sequence number of the current online redo log to the datafile headers and to the control file. Finally, LGWR handles initiating the process of clearing the dirty buffer write queue. At various times, depending on database configuration, those updated blocks are written to disk by DBWR. These events are called checkpoints. LGWR handles telling DBWR to write the changes. |
| SMON | The system monitor process. The usage and function of this Oracle background process is twofold. First, in the event of an instance failure—when the memory structures and processes that comprise the Oracle instance cannot continue to run—the SMON process handles recovery from that instance failure. Second, the SMON process handles disk space management issues on the database by taking smaller fragments of space and "coalescing" them, or piecing them together. |
| PMON | The process monitor process. PMON watches the user processes on the database to make sure that they work correctly. If for any reason a user process fails during its connection to Oracle, PMON will clean up the remnants of its activities and make sure that any changes it may have made to the system are "rolled back," or backed out of the database and reverted to their original form. |
| RECO | (optional) The recoverer process. In Oracle databases using the distributed option, this background process handles the resolution of distributed transactions against the database. |
| ARCH | (optional) The archiver process. In Oracle databases that archive their online redo logs, the ARCH process handles automatically moving a copy of the online redo log to a log archive destination. |
| CKPT | (optional) The checkpoint process. In high-activity databases, CKPT can be used to handle writing log sequence numbers to the datafile headers and control file, alleviating LGWR of that responsibility. |
| LCK0.. LCK9 |
(optional) The lock processes, of which there can be as many as ten. In databases that use the Parallel Server option, this background process handles acquiring locks on remote tables for data changes. |
| S000.. S999 |
The server process. Executes data reads from disk on behalf of user processes. Access to Server processes can either be shared or dedicated, depending on whether the DBA uses MTS or not. In the MTS architecture, when users connect to the database, they must obtain access to a shared server process via a dispatcher process, described below. |
| D001.. D999 |
(optional) The dispatcher process. This process acts as part of the Oracle MTS architecture to connect user processes to shared server processes that will handle their SQL processing needs. The user process comes into the database via a SQL*Net listener, which connects the process to a dispatcher. From there, the dispatcher finds the user process a shared server that will handle interacting with the database to obtain data on behalf of the user process. |
In addition to memory, Oracle must execute disk management processing in order to create, access, store, and recover an Oracle database. There are structures that are stored on disk that the DBA must understand how to manage, and this section will identify and discuss the meaning of each structure in kind. To begin, the DBA must first understand that there are two different "lenses" through which he or she must view the way Oracle looks at data stored on disk. Through one lens, the DBA sees the disk utilization of the Oracle database consisting of logical data structures. These structures include tablespaces, segments, and extents. Through another, the DBA sees the physical database files that store these logical database structures. Figure 6-2 demonstrates the concept of a logical and physical view of storage in the Oracle instance.
Figure 2: The logical and physical views of a database
A tablespace is a logical database structure that is designed to store other logical database structures. A segment is a logical data object that stores the data of a table, index, or series of rollback entries. An extent is similar to a segment in that the extent stores information corresponding to a table. However, the difference is that an extent handles table growth. When the row data for a table exceeds the space allocated to it by the segment, the table acquires an extent to place the additional data in. The objects a DBA may place into a tablespace are things like tables, indexes, rollback segments, and any other objects that consist of segments and extents. A logical database object such as a table or index can have multiple extents, but those extents and segments can be stored in only one tablespace.
The other lens through which the DBA will view the data stored on disk is the physical lens. Underlying all the logical database objects in Oracle disk storage are the physical methods that Oracle uses to store those objects. The cornerstone of the physical method that Oracle uses to store data in the database is the Oracle data block. Data blocks store row data for segments and extents. In turn, the blocks are taken together to comprise a datafile. Datafiles correspond to the tablespace level of the logical Oracle disk storage architecture in that a tablespace may consist of one or more datafiles. The objects in a tablespace, namely segments and extents corresponding to different tables, can span across multiple datafiles, provided that all datafiles are part of the same tablespace. Just as a table, index, or rollback segments cannot span across multiple tablespaces, any datafile on the database can contain data for only one tablespace. Individual data objects, such as tables and indexes, however, can have their segments and extents span across multiple datafiles belonging to that one tablespace.
In addition to datafiles, the structures that Oracle uses to store information about the database on disk include: control files, parameter files, password files, and redo log files. Redo log files are used to store information about the changes users make to the database. These files are generally large enough to store many entries There are generally two (minimum) redo logs available to the LGWR process of the Oracle instance for the purpose of storing redo log entries. Each redo log can consist of one or more redo log files, also referred to as redo log "members." The operation of online redo logs occurs in this way: LGWR writes entries to the files of one of the two (or more) online redo logs. As the redo log entries are written to the logs, the files that comprise the current online redo log get fuller and fuller until they reach capacity. Once the capacity of all redo log members is reached, the redo log is filled and LGWR switches writing redo log entries to the other redo log, starting the entire process again. If redo log archiving has been selected, the redo logs are archived as well, either automatically by the ARCH process or manually by the DBA. After all redo logs are filled, LGWR begins reuse of the first redo log file it wrote records to.
The parameter file is another important physical database disk resource that is available to Oracle. The parameter file is commonly referred to as init.ora. Used upon database startup, this file specifies many initialization parameters that identify the database as a unique database, either on the machine or on the entire network. Some parameters that init.ora sets include the size of various objects of the SGA, the location of the control file, the database name, and others. The database cannot start without init.ora, so it is imperative that the file be present at database startup.
Another database disk resource that is used by Oracle is the password file. This resource stores passwords for users who have administrative privileges over the Oracle database. The password file is only used on Oracle databases that use database authentication. The use of operating system authentication will be covered in more detail in the next section, "Starting and Stopping the Oracle Instance." In cases where operating system authentication is used, the password file is not present on the Oracle database.
The final database disk resource that will be covered in this section is the control file. The control file to the physical structure of the Oracle database is like the data dictionary to the logical structure. The control file tells Oracle what the datafiles are called for all tablespaces and where they are located on the machine, as well as what the redo log member filenames are and where to find them. Without a control file, Oracle database server would be unable to find its physical components, and thus it is imperative to have a control file. The name of the control file is specified in the init.ora file for the database instance. To recap, the main disk resources of Oracle are listed below:
| Datafiles |
| Redo log files |
| Control files |
In this section, you will cover the following topics related to starting and stopping the instance:
| Selecting an authentication method |
| Starting an Oracle instance and opening the database |
| Shutting down the Oracle instance |
| Altering database mode and restricting login |
The first order of business for the DBA in many environments is to install the Oracle software. The act of installing the Oracle software is specific to the operating system on the machine used to host the Oracle database. Once installation is accomplished, the DBA will start working on other aspects of the Oracle server before actually creating his or her first database. The first step of configuring the Oracle server is managing the creation and startup of the Oracle instance, and the first step for managing the instance is password authentication.
In order to determine what method of authentication to use for the Oracle database, the DBA must first answer an important question. That question is "How do I plan to support this database?" The answer to that question usually boils down to whether the DBA will be working on the same site as the host machine running Oracle primarily to support database administration tasks, or whether the DBA plans to monitor the site remotely, with a monitoring server that manages the database administration for many other databases in addition to the one being set up.
Security in Oracle server consists initially of user authentication. The users of an Oracle system each have a username that they enter, along with a password, in order to gain entry into the database. Just as users supply a password, so too must the DBA. However, Oracle offers two options with respect to password authentication. The use of either depends on the answer to the question of whether the DBA will administer the database locally or remotely, and also on a matter of preference. The two methods available are to either allow Oracle to manage its own password authentication or to rely on the authentication methods provided by the operating system for Oracle access.
The decision-making flow may go something like this. If the database administrator is planning to administer the database from a remote location, then the question of whether or not a secure connection can be established with the host system running Oracle must be answered. If the DBA can in fact obtain a secure connection with the host system running Oracle, then the DBA can use either the Oracle method of authentication or the operating system authentication method. If the DBA cannot obtain a secure connection remotely, then the DBA must use the Oracle method of user authentication. If the DBA plans to administer the database locally, then the choice of whether to use operating system authentication or Oracle authentication is largely a matter of personal preference.
Operating system authentication presents some advantages and disadvantages to using Oracle’s methods for user authentication. By using operating system authentication, the DBA thereby requires Oracle users to provide a password only when logging into the operating system on the machine hosting Oracle. When the user wants to access the Oracle database, he or she can simply use the "/" character at login time and Oracle will verify with the operating system that the user’s password is correct and allow access. Similarly, if the DBA logs onto the machine hosting Oracle and wants to execute some privileged activities such as creating users or otherwise administrating the database, the DBA will be able to log onto the database simply by using the "/" character in place of a username and password. For example, in a UNIX environment, the login procedure for the DBA using operating system authentication may look something like the following:
UNIX® SYSTEM V TTYP01 (23.45.67.98)
Login: bobcat
Password:
User connected. Today is 12/17/99 14:15:34
[companyx] /home/bobcat/ --> sqlplus /
SQL*PLUS Version 3.2.3.0.0
(c) 1979,1996 Oracle Corporation(c) All rights reserved.
Connected to Oracle 7.3.4 (23.45.67.98)
With the parallel, distributed, and multithreaded server options
SQL>
Operating system authentication allows the user or DBA to access the database quickly and easily, with minimal typing and no chance that a password can be compromised at any time during the Oracle login process. However, using the operating system to perform authentication on the Oracle database leaves Oracle’s data integrity and security at the level that the operating system provides. If for some reason a user’s ID is compromised, not only is the host machine at risk to the extent that the user has access to the machine’s resources, but the Oracle database is at risk as well to the level that the user has access to Oracle resources. Therefore, it is recommended that where it does not hinder usage of the database and where it improves security, use the Oracle methods of user authentication.
To use operating system authentication first requires that a special group be created on the operating system level if that feature is supported, or to grant the DBA special prioritization for executing processes on the host system for Oracle. This step of some other operating system requirements must be fulfilled in order to connect to the database for startup and shutdown as internal. The internal privilege is a special access method to the Oracle database that allows the DBA to start and stop the database. One additional constraint for connecting to the database as internal (in addition to having group privileges and proper process execution prioritization) is that the DBA must have a dedicated server connection. In effect, the DBA cannot connect as internal when shared servers are used in conjunction with the MTS architecture, rather than dedicated servers, for handling database reads for user processes,.
The ability to connect as internal is provided for backward compatibility. In the future, the DBA can connect to the database for the purposes of administration using the connect name/pass as sysdba command. The sysdba keyword denotes a collection of privileges that are akin to those privileges granted with the internal keyword.
Once the DBA is connected as internal and the database instance is started, the DBA can then perform certain operations to create databases and objects with operating system authentication. The performance of these functions is contingent upon the DBA having certain roles granted to their userid. The roles the DBA must have granted to them in this case are called osoper and osdba. These two privileges are administrated via the operating system and cannot be revoked or administrated via Oracle, and are used in conjunction with operating system authentication. They have equivalents used with Oracle authentication—sysoper and sysdba, respectively--and from here on this book will assume the use of Oracle authentication; thus, the use of sysoper and sysdba. When the DBA connects to the database as internal, the sysdba and sysoper privileges are usually enabled as well. Therefore, it is possible for the DBA to simply connect as internal when the need arises to do any DBA-related activities. However, this is not advised in situations where the database is administered by multiple database administrators who may have limited access to accomplish various functions. In this case, it is better simply to grant sysdba or sysoper directly to the user who administers the system.
Choosing which privilege to grant to the user depends on what type of administrative functions the DBA may fulfill. The sysoper and sysdba privileges provide some breakout of responsibilities and function with the privileges they allow the grantee to perform. The sysoper privilege handles administration of the following privileges:
| Starting and stopping the Oracle instance |
| Mounting or opening the database |
| Backing up the database |
| Initiating archival of redo logs |
| Initiating database recovery operations |
| Changing database access to restricted mode |
As shown by the types of privileges granted with sysoper, sysdba is a DBA privilege that is granted to the DBA for startup and shutdown recovery and backup, and other availability functions. The sysdba privilege administers certain other privileges as well. Those privileges are listed as follows:
| The sysoper privilege |
| All system privileges granted with admin option |
| The create database privilege |
| Privileges required for time-based database recovery |
Obviously, this is the role granted to the DBA ultimately responsible for the operation of the database in question. In addition, there is another role that is created upon installation of the Oracle software. This role is called DBA, and it also has all system privileges granted to it.
Oracle’s method of user authentication for database administrators when operating system authentication is not used is called a password file. The DBA first creates a file to store the authentication passwords for all persons that will be permitted to perform administrative tasks on the Oracle database. This functionality is managed by a special utility called ORAPWD. The name of the password file can vary according to the operating system used by the machine hosting the Oracle instance. First, a filename for the password file that stores users that can log in as DBA must be specified. The location for the password file varies by database, but for the most part it can be found in the dbs subdirectory under the Oracle software home directory of the machine hosting the Oracle database. The filename for the password file is usually orapwsid, where the database name is substituted for sid.
The password for administering the password file is the next parameter that the ORAPWD utility requires. By specifying a password for this file, the DBA simultaneously assigns the password for internal and SYS. After creating the password file, if the DBA connects as internal and SYS and issues the alter user name identified by password command in order to change the password, and the user’s and the password file’s passwords are changed.
The final item to be specified to the ORAPWD utility is the number of entries that are allowed to be entered into the password file. This number will determine the number of users that will have administrator privileges allowed for their ID. Care should be taken when specifying this value. If too few values are specified and the DBA needs to add more, he or she will not be able to do so without deleting and re-creating the password file. This process is dangerous, and should be executed with care to ensure that the DBA does not log off before the process is complete. If the DBA does log off after deleting the password file, the database administrator will be unable to execute administrative operations on the database. Entries can be reused as members of the DBA team come and go. The actual execution of ORAPWD may look something like this from within Oracle:
SQL> ORAPWD FILE=/home/oracle/dbs/orapworgdb01.pwd PASSWORD=phantom ENTRIES=5
Once this password file creation is complete, several items must be completed in order to continue with using Oracle’s authentication method to allow DBA access to the database without allowing access as internal. The first step is to set a value for an initialization parameter in the init.ora file. This parameter is called REMOTE_LOGIN_PASSWORDFILE, and its permitted settings are NONE, EXCLUSIVE, and SHARED. These various settings have different meanings with respect to allowing remote database administration on the Oracle instance. The NONE setting means that the database will not allow privileged sessions to be established over nonsecured connections because no password file exists. When operating system authentication is used on the database, the REMOTE_LOGIN_PASSWORDFILE parameter may be set to NONE to disallow remote access to the database for administration purposes. The following code block shows how the DBA can set this parameter in the init.ora file:
REMOTE_LOGIN_PASSWORDFILE=none
REMOTE_LOGIN_PASSWORDFILE=exclusive
REMOTE_LOGIN_PASSWORDFILE=shared
The EXCLUSIVE setting indicates that the password file developed by the ORAPWD utility for security use on that database instance can be used for that instance and that instance only. In this configuration, the DBA will add users who will administer the Oracle instance to the password file and grant the sysoper and sysdba privileges directly to those userids, allowing the DBAs to log into the database as themselves with all administrator privileges. When using password file authentication to administer a database remotely via a nonsecure connection, the REMOTE_LOGIN_PASSWORDFILE parameter should be set to EXCLUSIVE.
The final option is SHARED, which means that the password file allows access only by SYS and the DBA connected as internal. All administrative operations on the database must happen by a DBA who logs into the instance as SYS or as internal when this option is set.
After creating the password file with the ORAPWD utility and setting the REMOTE_LOGIN_PASSWORDFILE parameter to EXCLUSIVE in order to administer a database remotely, the DBA can then connect to the database as some user with sysdba privileges. One such user that is created when Oracle is installed is SYS. The DBA can log into the database as user SYS and create the database and other usernames as necessary, or simply mount the existing database. Then, the other users that will administer the instance can log onto it as themselves and execute DBA tasks as needed.
SQL> CONNECT john/mary AS SYSDBA;
Connection succeeded;
SQL>
There are two important key points to remember with respect to password files. One is gaining information about the users listed in them, the other is related to schema ownership. To find out which users are in the database password file, the DBA can query a dynamic performance view from the Oracle data dictionary on the password file called V$PWFILE_USERS. More on the data dictionary will be presented in this chapter. The other feature is that any object created in any login as sysdba or sysoper will be owned by the SYS schema. While this fact of ownership may not be all that important, especially given the ability to create and use public synonyms to obtain data without regard to schema ownership, it is an important point to make that the DBA should not look for objects they may create using their own username as the owner. In the event that the DBAs simply log in as themselves without the as sysdba trailer, the objects they create will be owned by their schemas.
There is an important distinction that sometimes gets blurry in the minds of DBAs and especially Oracle developers who don’t work with Oracle internals often enough to notice a difference. The distinction is between an Oracle instance and an Oracle database. First of all, an Oracle instance is NOT an Oracle database. The Oracle database is a set of tables, indexes, procedures, and other data objects that store information that applications place into storage in the Oracle product. The Oracle instance is the memory structures, background processes, and disk resources, all working together to fulfill user data requests and changes. With those points made, it should also be said that the Oracle instance is very close in similarity to the Oracle database.
With that distinction made, attention should now turn to starting the Oracle instance. This step is the first that should be accomplished when creating a new database or allowing access to an existing database. To start the Oracle database instance, the DBA should do the following:
Within the Server Manager tool there are several different options for database availability at system startup. These different options correspond to the level of access to the database once the database instance is running. Each startup feature has several associated facts about the access level permitted while the database is running in that mode. The options available for starting up an Oracle database are starting the database without mounting it, with mounting but not opening, mounting and opening in restricted mode, mounting and opening for anyone’s use, performing a forced start, or starting the database and initializing recovery from failure. This set of options is listed below:
| Startup nomount |
| Startup mount |
| Startup open |
| Startup restricted |
| Startup force |
The first option available is starting up the instance without mounting the database. In Server Manager, the command used for starting any database is the startup command. For starting the instance without mounting the database, the startup command can be issued with an option called nomount. This option will start the instance for database creation, but will not open any other database that may be available to the Oracle instance. This option is used for preventing any problems with existing databases that are mounted at the time that a new database is created. Another recommended safety measure for creating new databases in the same instance that already owns a database is to back up the existing database before creating the new one. A complete discussion of backup and recovery occurs in Unit III.
In other situations, the DBA may want to start the instance and mount but not open an existing database for certain DBA maintenance activities. In this case, the DBA may need to work on some aspect of the physical database structure, such as the movement of redo log file members, data files, or to specify the usage of Oracle’s archival of redo logs feature. In situations where the DBA needs to perform a full database recovery, the DBA should mount but not open the database. The same startup command is used for starting the database in all modes, but the mode option used in this situation is the mount option. The DBA will need to specify the database name and the parameter file for this option to mount the database to the instance for the physical database object maintenance activities described above. A nonmounted database can be mounted to an instance after creation of that database using commands described in the section of this chapter titled "Altering Database Availability."
All other options for starting a database will allow access to the database in one form or another. Hence, the options considered now are called opening the database. The DBA will open the database for many reasons, first and foremost so that users and applications can access the database in order to work. In order for the DBA to start the instance and then mount and open the database, the DBA must use the startup open option. Once the DBA has opened the database, any user with a valid username and password who has been granted the create session privilege or the CONNECT role can access the Oracle database.
In some cases, the DBA may want to open the database without letting users access the database objects. This is the most common situation for a DBA to start the database in when there is DBA maintenance activity required on the logical portion of the Oracle database. In this case, the DBA will execute the startup option as before. However, in addition to starting and opening the database, the DBA will execute a special command that restricts database access to only those users on the system with a special access privilege called restricted session. From database startup, the DBA can execute the startup restrict command within Server Manager. Although any user on the database can have this privilege granted to them, typically only the database administrator will have it. In some cases, such as in the case of reorganizing large tables that involves a large-volume data load, the DBA may grant the restricted session privilege to a developer who is assisting in the database maintenance work. In these situations, the DBA may want to consider a temporary grant of restricted session to the developer, followed by a revocation of the privilege afterward to prevent possible data integrity issues in later maintenance cycles. This method is generally preferable to a permanent grant of restricted session to someone outside the DBA organization. Typically, the DBA will want to use the restrict option for logical database object maintenance such as reorganizing tablespaces, creating new indexes or fixing old ones, large-volume data loads, reorganizing or renaming objects, and other DBA maintenance activities.
There are two special cases for database startup left to consider, both of which are used for circumstances outside of normal database activity. One of those two situations is when the database has experienced a failure of some sort that requires the DBA to perform a complete database recovery of the database and the instance. In this case, the DBA may want the instance to initiate its complete recovery at the time the instance is started. To accomplish the task, the DBA can issue the startup recover command from the Server Manager tool, and Oracle will start the instance and initiate the complete recovery at instance startup. In cases where archiving is used, Oracle may require certain archived redo logs to be present for this option to complete successfully. In any event, the use of this option will be more carefully considered in the next unit, the treatment of OCP Exam 3 on database backup and recovery.
The final option for database startup is used in unusual circumstances as well. Sometimes (rarely) there is a situation where the Oracle database cannot start the instance under normal circumstances or shut down properly due to some issue with memory management or disk resource management. In these cases, the DBA may need to push things a bit. The DBA can give database startup an additional shove with the startup force command option. This option will use a method akin to a shutdown abort (see the next section on database shutdown) in order to end the current instance having difficulty before starting the new instance. It is not recommended that the DBA use this option without extreme care, as there is usually a need for instance recovery in this type of situation.
Shutting down the Oracle instance works in much the same way as starting the instance, with the requirement to cease allowing access to the database and the requirement to accomplish the task while being logged on as internal. The task must also be accomplished from the Server Manager, either graphically with the use of the Shut Down menu under the Instance menu or with the shutdown command in line mode. The options for database shutdown are listed below:
| Shutdown normal |
| Shutdown immediate |
| Shutdown abort |
There are three priorities that can be specified by the DBA for shutting down the database. The first and lowest priority is normal. It is the lowest priority because Oracle will wait for many other events to play themselves out before actually shutting down the connection. In other words, the database will make the DBA wait for all other users to finish what they are doing before the database will actually close. The following description of events illustrates specifically how the shutdown process works under normal priority:
There are three rules that can be abstracted from this situation. The first is that no new users will be permitted access to the system. The second is that the database does not force users already logged onto the system to log off in order to complete the shutdown. Third, under normal shutdown situations, there is no need for instance recovery.
Normal database shutdown may take some time. While Oracle attempts to shut down the database, the DBA’s session will not allow the DBA to access any other options or issue any other commands until the shutdown process is complete. The time the process can take depends on several factors. Some of the factors that the database shutdown will depend on are whether many users have active transactions executing at the time the shutdown command is issued, how many users are logged on to the system and on the shutdown priority issued by the DBA.
A higher-priority shutdown that the DBA can enact in certain circumstances is the shutdown immediate command. Shutting down a database with immediate priority is similar to using the normal priority in that no new users will be able to connect to the database once the shutdown command is issued. However, Oracle will not wait for a user to logoff as it did in points 2 and 4 above. Instead, Oracle terminates the user connections to the database immediately and rolls back any uncommitted transactions that may have been taking place. This option may be used in order to shut down an instance that is experiencing unusual problems, or in the situation where the database could experience a power outage in the near future. A power outage can be particularly detrimental to the database; therefore, it is recommended that the DBA shut things down with immediate priority when a power outage is looming. There are two issues associated with shutting down the database with immediate priority. The first is the issue of recovery. The database will most likely need instance recovery after an immediate shutdown. This activity should not require much effort from the DBA, as Oracle will handle the recovery of the database instance itself without much intervention. However, the other issue associated with shutting down the database immediately is that the effect of the shutdown is not always immediate! In some cases, particularly in situations involving user processes running large-volume transactions against a database, the rollback portion of the database shutdown may take some time to execute.
The final priority to be discussed with shutting down a database is the shutdown with abort priority. This is the highest priority that can be assigned a shutdown activity. In all cases that this priority is used, the database will shut down immediately, with no exceptions. Use of this priority when shutting down a database instance should be undertaken with care. The additional item that a shutdown abort uses to prevent the database from waiting for rollback to complete is not to roll back uncommitted transactions. This approach requires more instance recovery activity, which is still handled by Oracle. Only in a situation where the behavior of the database is highly unusual or when the power to the database will cut off in less than two minutes should the shutdown abort option be employed. Otherwise, it is usually best to avoid using this option entirely, and use shutdown immediate in circumstances requiring the DBA to close the database quickly.
During the course of normal operation on the database, the DBA may require changing the availability of the database in some way. For example, the DBA may have to initiate emergency maintenance on the database, which requires the database to be unavailable to the users. Perhaps there are some problems with the database that need to be resolved while the instance is still running but the database is unavailable. For this and many other reasons, the DBA can alter the availability of the database in several ways. The following discussion will highlight some of those ways.
The first way a DBA may want to alter the status and availability of the database instance is to change the mount status of a database. In some situations, the DBA may need to start a database with the nomount option, as discussed earlier in the section on starting the database. After the activities that required the database not to be mounted are complete, the DBA will want to mount the database to the instance, but have the database still be closed and therefore unavailable to the users. To change the status of a database to be mounted, the DBA can use either the graphical interface of Server Manager to mount the database or use the alter database mount statement to achieve that effect. Mounting the database allows the DBA to do several database maintenance activities without allowing users the chance to access the database and cause contention.
After database work, or in the course of a manual startup, the DBA will want to allow the users access to the database. This step can be accomplished in two ways. Like mounting the database manually, the DBA can use the graphical user interface to open the database for user access. Alternately, the DBA can issue the alter database open statement from the SQL command prompt and open the database for user access. When the database is in open mode, then a database user with the create session privilege, or the CONNECT role, can access the database. One fact that is important to remember about the Oracle database is that it can be accessed by multiple instances.
The final option to be covered corresponds to situations where the DBA has the database open for use, and needs to make some changes to the database. Some of these changes may include re-creating indexes, large-volume data loads, tablespace reorganization, and other activities that require the database to be open but access to the data to be limited. This option is called the restricted session. In cases where the DBA wants to limit access to the database without actually closing it, the DBA can enable the database’s restricted session mode. This option prevents logging into the database for any user that does not have the restricted session privilege granted to the user. Although any user on the database can have this privilege granted to them, typically only the database administrator will have it. In some cases, such as in the case of reorganizing large tables that involves a large-volume data load, the DBA may grant the restricted session privilege to a developer who is assisting in the database maintenance work. In these situations, the DBA may want to consider a temporary grant of restricted session to the developer, followed by a revocation of the privilege afterward to prevent possible data integrity issues in later maintenance cycles. This method is generally preferable to a permanent grant of restricted session to someone outside the DBA organization. This option is handled in one way, mainly. The method used to close access to the database to all users except those with the restricted session privilege is alter database enable restricted session. In order to restore access to the database to all users without the restricted session privilege is to issue the following command: alter database enable restricted session.
In this section, you will cover the following topics related to creating an Oracle database:
| Entity relationships and database objects |
| Creating a database in Oracle |
| Creating the Oracle data dictionary |
Once the DBA has set up some necessary preliminary items for running the Oracle instance, such as password authentication, the DBA can then create the database that users will soon utilize for data management. Creating a database involves three activities that will be discussed in this section. The first activity for creating a database is mapping a logical entity-relationship diagram that details a model for a process to the data model upon which the creation of database objects like indexes and tables will be based. The second activity that the DBA will perform as part of creating a database is the creation of physical data storage resources in the Oracle architecture, such as datafiles and redo log files. The final (and perhaps the most important) aspect of creating a database is creating the structures that comprise the Oracle data dictionary. A discussion of each element in the database creation process will be discussed now in detail.
The first part of creating a database is creating a model for that database. One fundamental tenet of database design is remembering that every database application is a model of reality. Most of the time, the database is used to model some sort of business reality, such as the tracking of inventory, payment of sales bonuses, employee expense vouchers, and customer accounts receivable invoices. The model for a database should be a model for the process that the database application will represent.
Now, explore the combination of those entities and their relationships. The concept of an entity maps loosely to the nouns in the reality the database application is trying to model. In the employee expenditure system mentioned above, the entities (or nouns) in the model may include employees, expense sheets, receipts, payments, a payment creator such as accounts payable, and a payer account for the company that is reimbursing the employee. The relationships, on the other hand, map loosely to the idea of a verb, or action that takes place between two nouns. Some actions that take place in this employee expenditure system may be submits expense sheet, submits receipts, deducts money from account, and pays check. These entities and relationships can translate into several different types of visual representations or models of a business reality. Figure 6-3 illustrates each entity by a small illustration, with the relationships between each entity represented by an arrow and a description. The employee fills out the expense sheets for the expenses incurred on behalf of the company.
Figure 3: An entity-relationship diagram of process flow in the system
Then, the employees send their vouchers to the accounts payable organization, which creates a check for the employee and mails the payment to the employee. The process is very simple, but it accurately models the business process within an organization to reimburse an employee for his expenses. When the developers of a database application create the employee expenditure system modeled by the entity-relationship diagram above, they will first take those entities and map out the relationship, then take the entity-relationship diagram and create a logical data model out of those entities and processes. A logical data model is a more detailed diagram than the entity-relationship diagram in that it fills in details about the process flow that the entity-relationship diagram attempts to model. Figure 6-4 shows the logical data model of the employee table and the invoice table.
Figure 4: Logical data model of employee table and invoice table
On the expense sheet, the employee will fill in various pieces of information, including the expense ID number, the employee ID number, and the expense amount. The line between the two entities is similar to a relationship; however, in the logical data model, the entities are called tables and the relationships are called foreign keys.
There is an interesting piece of information communicated above and below the line on the opposite side of each table as well. That piece of information identifies a pair of facts about the relationship. The first element of the pair identifies whether the relationship is mandatory from the perspective of the table appearing next to the pair. A one indicates that the relationship is mandatory for the pair, while a zero (0) indicates that the relationship is optional. In the example in the diagram above, the relationship between employee and expense sheet is optional for employees but mandatory for expense sheets. This means that for any given record in the EMPLOYEE table, there may or may not be expense sheets in the EXPENSES table. However, every expense sheet record in the EXPENSES table will correspond to an employee record. The second component of that pair indicates whether there is a one-to-one, one-to-many, or many-to-many correspondence between records of one table and records of another table. In the example above, records in the EMPLOYEE table have a one-to-many relationship with the records of the EXPENSE table, while the records of the EXPENSE table have a one-to-one relationship with records of the EMPLOYEE table. That is to say, each employee may have submitted one or more expense sheets, or none at all, while each expense sheet corresponds to one and only one employee. This pair of facts is referred to as the ordinality of the database tables.
The relationship between columns to tables corresponds loosely to the activity or relationship that exists between the two entities that the tables represent. In the case of the table structure in Figure 6-5, the EMPID column in EXPENSE corresponds to the primary key column of the EMPLOYEE table. In terms of the entity-relationship diagram, the empid is the tie that binds an expense sheet to the employee who created it. Therefore, the relationship of one table to another through foreign keys should correspond somewhat to the relationship that occurs between two entities in the process flow being modeled.
Figure 5: Detail of logical data model of EMPLOYEE and EXPENSE
Creating a physical database out of the logical data model requires considering several issues. The database designer may ask several questions related to the physical design of that system as follows:
| How many employees will be allowed to use the system? |
| What sort of company chargeback system will be used to take the employee expense payment from the budget of the department for which the expense was incurred on behalf of the employee? |
| How many expense sheets will be submitted per month and per year? |
The proper creation of a database in Oracle depends on answering these and many other questions regarding the physical relationship between the machine hosting Oracle and the data Oracle stores as part of the application model.
Some of these questions relate to Oracle-specific features. For example, the designer of the database should know row count estimates for each object to be created in Oracle. This estimate of row count should be something that is forecasted over a period of time, say two years. This forecast of sizing for the database will allow the DBA some "breathing room" when the database application is deployed, so that the DBA is not constantly trying to allocate more space to an application that continually runs out of it. Some objects that the designer will need to produce sizing estimates for are the tables and indexes, and the tablespaces that will contain those tables and indexes.
In a point related to indexes, the designer of the application should know what the users of the database will need regarding data access. This feature of database design is perhaps the hardest to nail down after the initial estimate of transaction activity for the database application. The reason for the difficulty is knowing what the users will want with respect to data access. The developers of the application should, where possible, try to avoid providing users with free rein to access data via ad hoc queries, as many users will not know, for example, that searching a table on an indexed column is far preferable to searching on a nonindexed column, for performance reasons. Providing the "canned" query access via graphical user interfaces or batch reporting allows the designers to tune the underlying queries that drive the screens or reports, scoring a positive response from the users while also minimizing the impact of application activity on the Oracle instance.
In addition, specifying the database’s character set is critical to the proper functioning of the database. There are several different options for specifying character sets in the Oracle database, just as there are many different languages available for use by different peoples of the world. These languages fall into distinct categories with respect to the mechanisms on a computer that will store and display the characters that comprise those languages. The distinct categories are single-byte character sets, multibyte character sets, and languages read from right to left. Examples of single-byte character sets are any of the Romance or Teutonic languages originating in Western Europe, such as English, Spanish, French, German, Dutch, or Italian. Examples of the multibyte character sets available are the languages that originated in Eastern Asia, Southeast Asia, or the Pacific Rim. These languages include Mandarin, Japanese, and Korean. Examples of a language read right to left include Hebrew and Arabic.
One final, and perhaps the most important, area of all to consider at the onset of database system creation in the Oracle environment is how the user will preserve the data in the system from any type of failure inherent in the usage of computer machinery. Such methods may include full and partial backups for the database and the archiving (or storing) of redo logs created by Oracle to track changes made in the database. Backup and recovery are handled as a whole topic unto themselves in the OCP DBA Exam series, and also in this book. See Unit III covering OCP Exam 3, "Database Backup and Recovery."
There are three main steps in creating databases in the Oracle environment. The first is creating the physical locations for data in tables and indexes to be stored in the database. These physical locations are called datafiles. The second step is to create the files that will store the redo entries that Oracle records whenever any process makes a data change to the Oracle database. These physical structures are called the redo log files, or redo log members. The final step in creating an Oracle database is to create the logical structures of the data dictionary. The data dictionary comprises an integral portion of the database system. Both the users and Oracle refer to the data dictionary in order to find information stored in tables or indexes, to find out information about the tables or indexes, or to find out information about the underlying physical structure of the database, the datafiles, and the redo log files.
Creation of the Oracle database is accomplished with the create database statement. The first thing to remember about database creation is the Oracle recommended methodology for actually creating the database. The steps are as follows:
Step 1 in the process is to back up the database. This step prevents headaches later if there is a problem with database creation damaging an existing database, which can happen if the DBA attempts database creation without changing the DB_NAME parameter in the INIT.ORA file. More details will be given shortly about the required parameters that must be unique for database creation. Steps 1 and 2 are critical in preserving the integrity of any existing databases that may already exist on the Oracle instance. Sometimes accidents do happen in database creation. The worst thing a DBA can face when creating a new database is when a datafile or log filename in a parameter file may not have been changed before creating the second database. This situation leaves the first database vulnerable to being overwritten when the second database is created, which causes the first database to be unusable. Always remember to back up any existing database that uses the same instance and host machine.
A critical resource used to start any instance is the file that contains any initialization parameter that the DBA cares to set for the Oracle database and instance being used. This file is generally referred to as the init.ora or parameter file. A parameter file is as unique as the database that uses it. Each database instance usually has at least one parameter file that corresponds to it and it only. Usually, a database instance will have more than one parameter file used exclusively for starting it, to handle various situations that the DBA may want to configure the instance to handle. For example, a DBA may have one parameter file for general use on starting the Oracle instance when users will access the system, one parameter file that is specifically configured to handle an increase in processing associated with heavy transaction periods at the end of the year, and another parameter file designed to start the instance in proper configuration for DBA maintenance weekends.
Oracle provides a generic copy of that parameter file INIT.ORA in the software distribution used to install Oracle server on the machine hosting Oracle. Generally, the DBA will take this generic parameter file and alter certain parameters according to his or her needs. There are several parameters that must be changed as part of setting up and running a new Oracle database. The following list highlights key initialization parameters that have to be changed in order to correspond to a unique database. The list describes each parameter in some detail and offers some potential values if appropriate.
| DB_NAME | The local name of the database on the machine hosting Oracle, and one component of a database’s unique name within the network. If this is not changed, permanent damage may result in the event a database is created. |
| DB_DOMAIN | Identifies the domain location of the database name within a network. It is the second component of a database’s unique name within the network. |
| CONTROL_FILES | A name or list of names for the control files of the database. The control files document the physical layout of the database for Oracle. If the name specified for this parameter do not match filenames that exist currently, then Oracle will create a new control file for the database at startup. If the file does exist, Oracle will overwrite the contents of that file with the physical layout of the database being created. |
| DB_BLOCK_SIZE | The size in bytes of data blocks within the system. Data blocks are unit components of datafiles into which Oracle places the row data from indexes and tables. This parameter cannot be changed for the life of the database. |
| DB_BLOCK_BUFFERS | The maximum number of data blocks that will be stored in the database buffer cache of the Oracle SGA. |
| PROCESSES | The number of processes that can connect to Oracle at any given time. This value includes background processes (of which there are at least five) and user processes. |
| ROLLBACK_SEGMENTS | A list of named rollback segments that the Oracle instance will have to acquire at database startup. If there are particular segments the DBA wants Oracle to acquire, he/she can name them here. |
| LICENSE_MAX_SESSIONS | Used for license management. This number determines the number of sessions that users can establish with the Oracle database at any given time. |
| LICENSE_MAX_WARNING | Used for license management. Set to less than LICENSE_MAX_SESSIONS, Oracle will issue warnings to users as they connect if the number of users connecting has exceeded LICENCE_MAX_WARNING. |
| LICENSE_MAX_USERS | Used for license management. As an alternative to licensing by concurrent sessions, the DBA can limit the number of usernames created on the database by setting a numeric value for this parameter. |
Following the creation of the appropriate initialization parameter file, the DBA will need to start the database instance while connected to the database as sysdba and while running Server Manager. The task of connecting to the database as sysdba has already been discussed. To start the instance, use the startup nomount command in order to run the instance without mounting a previously existing database. After starting the instance without mounting a database, the DBA can create the database with the create database command In order to create a database, the user must have the osdba or sysdba granted to them and enabled. The following code block contains a create database statement:
CREATE DATABASE orgdb01
CONTROLFILE REUSE
LOGFILE GROUP 1 (‘redo1a.log’, ‘redo1b.log’, ‘redo1c.log’)
SIZE 1M,
GROUP 2 (‘redo2a.log’, ‘redo2b.log’, ‘redo2c.log’) SIZE 1M
MAXLOGFILES 2
DATAFILE ‘data01.dat’ SIZE 30M AUTOEXTEND 10M MAXSIZE 100M,
‘index01.dat’ SIZE 30M AUTOEXTEND 10M MAXSIZE 100M,
‘rbs01.dat’ SIZE 20M,
‘users01.dat’ SIZE 30M,
‘temp01.dat’ SIZE 10M
MAXDATAFILES 20
ARCHIVELOG;
A new database is created by Oracle with several important features. The first item to remember about database creation is that the database will be created with two special users, which are designed to allow for performing important activities required throughout the life of a database. The names of the two users created by Oracle are SYS and SYSTEM. Both have many powerful privileges and should be used carefully for administrative tasks on the database. For now, the DBA should remember that when the users SYS and SYSTEM are created, the password for SYS is CHANGE_ON_INSTALL, and the password for SYSTEM is MANAGER.
Of the disk resources created, the one most central to database operation is the control file. As covered, the control file identifies all other disk resources to the Oracle database. If information about a datafile or redo log file is not contained in the control file for the database, the database will not know about it, and the DBA will not be able to recover it if the disk containing it is lost. The controlfile reuse clause is included in the create database statement to allow Oracle to reuse the file specified if it already exists. BEWARE OF REUSING CONTROL FILES. An existing database will need recovery if its control file is overwritten by the creation of another database. Control files are generally not very large, perhaps 250K for even a large database. This size of the control file is related to the number of datafiles and redo log files that are used on the Oracle database. Adding more datafiles and redo logs increases the size of a control file; fewer datafiles that grow larger with the autoextend option and fewer redo logs decrease the size of control files.
Other items created as part of the create database command are the datafiles and the redo log files. The datafiles are specified with the datafile clause, and they are where Oracle physically stores the data in any and all tablespaces. Redo logs are created with the logfile clause. Redo logs are entries for changes made to the database. If the create database statement specifies a datafile or a redo log file that currently exists on the system and the reuse keyword is used, then Oracle will overwrite that file with the data for the new redo log member or datafile for the database being created. This syntax is purely optional, and furthermore, if is not used and the create database statement includes files that already exist, Oracle will overwrite them. In general, care should be taken when reusing files in order to prevent "accidental reuse" in a situation where a database already exists on the machine and the DBA creates another one that overwrites key files on the first database.
Another pair of options set at database creation time are called maxdatafiles and maxlogfiles. This parameter is a keyword specified in the create database statement, and it must be followed by an integer. As its name implies, the maxdatafiles parameter indicates the maximum number of datafiles a database can have. This parameter can be a potential limitation later when the database grows to a point where it cannot accommodate another datafile because the maxdatafiles parameter would be exceeded. A workaround for this problem is to use the autoextend option when defining datafiles. When autoextend is used, the datafiles will automatically allocate more space when the datafile fills, up to a total size specified by the maxsize keyword. The final and most important option included in the create database statement is the archivelog option. When archivelog is used, Oracle archives the redo logs generated. This feature should be enabled in all but read only databases.
Not only are physical database resources and database administrative passwords created as part of the create database command, but some important logical disk resources are created as well. One of these resources is the SYSTEM tablespace. Sometimes the SYSTEM tablespace is compared to the root directory of a machine’s file system. The SYSTEM tablespace certainly is a tablespace in the Oracle system that is HIGHLY important to the operation of the Oracle database. Many important database objects are stored in the SYSTEM tablespace. Some of those objects are the Oracle data dictionary and rollback segments. There must be one rollback segment in the SYSTEM tablespace for Oracle to acquire at database startup, or else the database won’t start. In the interests of preserving the integrity of the Oracle database, the DBA should ensure that only the data dictionary and system rollback segments are placed in the SYSTEM tablespace. In particular, no data objects such as tables or indexes should be placed in the SYSTEM tablespace.
Finally, the create database command specifies the character set used throughout the database. Like DB_BLOCK_SIZE, the character set specified for the database should not be changed at any point after the database is created. After database creation, the database that was just created is mounted and opened directly by Oracle for the DBA to begin placing data objects into it, such as tables, indexes, and rollback segments. Once the database is created, the DBA should consider some preliminary work on distributing the I/O load in order to simplify the maintainability of the database in the future. Some areas that the DBA may want to address right away are placement of redo logs and datafiles and the separation of tables from their corresponding indexes. Also, the DBA will find the further allocation of rollback segments in addition to the rollback segment in the SYSTEM tablespace to be an important initial consideration on creating the database. Generally, it is recommended to place the rest of the rollback segments available to the database in a special tablespace designed only to hold rollback segments.
The data dictionary is the first database object created at the time a create database command is issued. Every object in the database is tracked in some fashion by the Oracle data dictionary. Oracle generally creates the data dictionary without any intervention from the DBA at database creation time with the use of catalog.sql and catproc.sql. The first script, catalog.sql, runs a series of other scripts in order to create all the data dictionary views, along with special public synonyms for those views. Within the catalog.sql script there are calls to several other scripts, which are listed below:
| cataudit.sql Creates the SYS.AUD$ dictionary table, which tracks all audit trail information generated by Oracle when the auditing feature of the database is used. |
| catldr.sql Creates views that are used for the SQL*Loader tool, discussed later in this unit, which is used to process large-volume data loads from one system to another. |
| catexp.sql Creates views that are used by the IMPORT/EXPORT utilities, discussed in the unit covering OCP Exam 3, "Database Backup and Recovery." |
The other script generally run by the Oracle database when the data dictionary is created is the CATPROC.SQL script. This script calls several other scripts in the process of creating several different data dictionary components used in everything procedural related to the Oracle database. The code for creating these dictionary views is not contained in catproc.sql. The code that actually creates the objects is in several scripts called by this master script. Some of the objects created by the scripts called by catproc.sql are stored procedures, packages, triggers, snapshots, and certain utilities for PL/SQL constructs like alerts, locks, mail, and pipes.
This chapter covered introductory material related to Oracle database administration. The topics covered in this chapter included an overview of the Oracle architecture, the process of starting and stopping access to a database and to an Oracle instance, and the tasks required for creating an Oracle database. The material in this chapter comprises about 22 percent of questions asked on OCP Exam 2.
The first area of discussion in this chapter was an overview of the various components of the Oracle database. Figure 6-1 gives a clear idea of the background processes, memory structures, and disk resources that comprise the Oracle instance, and also of the methods in which they act together to allow users to access information. Several memory structures exist on the Oracle database to improve performance on various areas of the database. The memory structures of an Oracle instance include the System Global Area (SGA) and the Program Global Area (PGA). The SGA, in turn, consists of a minimum of three components: the data block buffer cache, the shared pool, and the redo log buffer. Corresponding to several of these memory areas are certain disk resources. These disk resources are divided into two categories: physical resources and logical resources. The physical disk resources on the Oracle database are datafiles, redo log files, control files, password files, and parameter files. The logical resources are tablespaces, segments, and extents. Tying memory structures and disk resources together are several memory processes that move data between disk and memory, or handle activities in the background on Oracle’s behalf. The core background processes available on the Oracle instance include data block writer (DBWR), log writer (LGWR), system monitor (SMON), process monitor (PMON), checkpoint (CKPT), archiver (ARCH), recoverer (RECO), dispatcher (Dnnn), lock (LCKn), and server (Snnn).
These different processes have functions that are related to activities that happen regularly in Oracle against the memory structures, disk resources, or both. DBWR moves data blocks out of the buffer cache. LGWR writes redo log entries out of the redo log buffer and into the online redo log. SMON handles instance recovery at startup in the event of a failure, and periodically sifts through tablespaces, making large continuous free disk space out of smaller empty fragments. PMON ensures that if a user process fails, the appropriate cleanup activity and rollback occurs. CKPT handles writing new redo log number information to datafiles and control files in the database at periodic intervals during the time the database is open. ARCH handles the automatic archiving of online redo log files. RECO and LCKn handle transaction processing on distributed database configurations. Server processes read data into the buffer cache on behalf of user processes. They can either be dedicated to one user process, or shared between many processes in the MTS architecture. Dnnn are dispatchers used to route user processes to a shared server in MTS configurations.
The next area covered by the chapter was on how to start the Oracle instance. Before starting the instance, the DBA must figure out what sort of database authentication to use both for users and administrators. The options available are operating system authentication and Oracle authentication. The factors that weigh on that choice are whether the DBA wants to use remote administration via network or local administration directly on the machine running Oracle. If the DBA chooses to use Oracle authentication, then the DBA must create a password file using the ORAPWD utility. The password file itself is protected by a password, and this password is the same as the one used for authentication as user SYS and when connecting as internal. To have database administrator privileges on the database, a DBA must be granted certain privileges. They are called sysdba and sysoper in environments where Oracle authentication is used, and osdba or osoper where operating system authentication is used.
In order to start a database instance, the DBA must run Server Manager and connect to the database as internal. The command to start the instance from Server Manager is called startup. There are several different options for starting the instance. They are nomount, mount, open, restrict, recover, and force. The nomount option starts the instance without mounting a corresponding database. The mount option starts the instance and mounts but does not open the database. The open option starts the instance, mounts the database, and opens it for general user access. The restrict option starts the instance, mounts the database, and opens it for users who have been granted a special access privilege called restricted access. The recover option starts the instance, but leaves the database closed and starts the database recovery procedures associated with disk failure. The force option gives the database startup procedure some extra pressure to assist in starting an instance that either has trouble opening or trouble closing normally. There are two alter database statements that can be used to change database accessibility once the instance is started as well.
Several options exist for shutting down the database as well. The DBA must again connect to the database as internal using the Server Manager tool. The three options for shutting down the Oracle database are normal, immediate, and abort. When the DBA shuts down the database with the normal option, the database refuses new connections to the database by users and waits for existing connections to terminate. Once the last user has logged off the system, then the shutdown normal will complete. The DBA issuing a shutdown immediate causes Oracle to prevent new connections while also terminating current ones, rolling back whatever transactions were taking place in the sessions just terminated. The final option for shutting down a database is shutdown abort, which disconnects current sessions without rolling back their transactions and prevents new connections to the database as well.
The final area covered in this chapter was the creation of a database. The steps of database creation were discussed. The first area covered was the process modeling that takes place when the database designer creates the entity-relationship diagram. After developing a model of the process to be turned into a database application, the designer of the application must then give a row count forecast for the application’s tables. This row count forecast allows the DBA to size the amount of space in bytes that each table and index needs in order to store data in the database. Once this sizing is complete, the DBA can then begin the work of creating the database. First, the DBA should back up existing databases associated with the instance, if any, in order to prevent data loss or accidental deletion of a disk file resource. The next thing that should happen is the DBA should create a parameter file that is unique to the database being created. Several initialization parameters were identified as needing to be set to create a database. They are DB_NAME, DB_DOMAIN, DB_BLOCK_SIZE, DB_BLOCK_BUFFERS, PROCESSES, ROLLBACK_SEGMENTS, LICENSE_MAX_SESSIONS, LICENSE_MAX_WARNING, LICENSE_MAX_USERS. After the parameter file is created, the DBA can execute the create database command, which creates all physical disk resources for the Oracle database. The physical resources are datafiles, control files, and redo log files, the SYS and SYSTEM users, the SYSTEM tablespace, one rollback segment in the SYSTEM tablespace, and the Oracle data dictionary for that database. After creating the database, it is recommended that the DBA back up the new database in order to avoid having to re-create the database from scratch in the event of a system failure.
Of particular importance in the database creation process is the process by which the data dictionary is created. The data dictionary must be created first in a database because all other database structure changes will be recorded in the data dictionary. This creation process happens automatically by Oracle. Several scripts are run in order to create the tables and views that comprise the data dictionary. There are two "master" scripts that everything else seems to hang off of. The first is catalog.sql. This script creates all the data dictionary tables that document the various objects on the database. The second is called catproc.sql. This script runs several other scripts that create everything required in the data dictionary to allow procedural blocks of code in the Oracle database, namely packages, procedures, functions, triggers, snapshots, and certain packages for PL/SQL such as pipes and alerts.
| Three major components of the Oracle architecture are memory structures that improve database performance, disk resources that store Oracle data, and background processes that handle disk writes and other time-consuming tasks in the background. |
| Memory structures in the Oracle architecture are System Global Area (SGA) and Program Global Area (PGA). |
| The SGA consists of: the buffer cache for storing recently accessed data blocks, the redo log buffer for storing redo entries until they can be written to disk, and shared pool for storing parsed information about recently executed SQL for code sharing. |
| Disk resources in the Oracle architecture are divided into physical and logical categories. |
| Physical disk resources are control files that store the physical layout of database, redo log files that store redo entries on disk, password files to store passwords for DBAs when Oracle authentication is used, parameter files, and datafiles that store tables, indexes, rollback segments, and the data dictionary. |
| The fundamental unit of storage in a datafile is the data block. |
| Logical disk resources are tablespaces for storing tables, indexes, rollback segments, the data dictionary. The storage of these logical disk resources is handled with segments and extents, which are conglomerations of data blocks. |
| DBWR writes data blocks back and forth between disk and the buffer cache. |
| LGWR writes redo log entries between redo log buffer and online redo log on disk. It also writes redo log sequence numbers to datafiles and control files at checkpoints (also handled by CKPT when enabled) and tells DBWR to write dirty buffers to disk. |
| CKPT writes redo log sequence numbers to datafiles and control files when enabled. This task can also be handled by LGWR. |
| PMON monitors user processes and cleans up the database transactions in the event that they fail. |
| SMON automatically handles instance recovery at startup when necessary, and defragments small free spaces in tablespaces into larger ones. |
| ARCH handles archiving of online redo logs automatically when set up. |
| RECO resolves in-doubt transactions on distributed database environments. |
| LCKn obtains locks on remote databases. |
| Dnnn receives user processes from SQL*Net listener in multithreaded server (MTS) environments. |
| Snnn (server process) reads data from disk on the database on behalf of the user process. |
| Two user authentication methods exist in Oracle: operating system authentication and Oracle authentication. |
| There are two roles DBAs require to perform their function on the database. Under OS authentication, the privileges are called osdba and osoper. In Oracle authentication environments, they are called sysdba and sysoper. |
| To use Oracle authentication, the DBA must create a password file using the ORAPWD utility. |
| To start and stop a database, the DBA must connect as internal or sysdba. |
| The tool used to start and stop the database is called Server Manager. |
| There are at least six different options for starting a database: |
| Startup nomount Start instance, do not mount a database. |
| Startup mount Start instance, mount but do not open the database. |
| Startup open Start instance, mount and open database. |
| Startup restrict Start instance, mount and open database, but restrict access to those users with restricted session privilege granted to them. |
| Startup recover Start instance, leave database closed, and begin recovery for disk failure scenario. |
| Startup force Make an instance start that is having problems either starting or stopping. |
| When a database is open, any user with a username and password, with the create session privilege or the CONNECT role granted to them, may connect to the database. |
| The database availability can be changed at any time using the alter database command. |
| Options available for alter database are mount, open, and enable (disable) restricted session. |
| Closing or shutting down a database must be done by the DBA while running Server Manager and while the DBA is connected to the database as internal or sysdba. |
| There are three options for closing a database: shutdown normal—no new existing connections allowed, but existing sessions may take as long as they want to wrap up; shutdown immediate—no new connections allowed, existing sessions are terminated, and their transactions rolled back, shutdown abort—no new connections allowed, existing sessions are terminated, transactions are not rolled back. |
| Instance recovery is required after shutdown abort is used. |
| The first step in creation of a database is to model the process that will be performed by the database application. |
| The next step in creating a database is to back up any existing databases associated with the instance. |
| After that, the DBA should create a parameter file with unique values for several parameters, including the following: |
| DB_NAME The local name for the database. |
| DB_DOMAIN The network-wide location for the database. |
| DB_BLOCK_SIZE The size of each block in the database. |
| DB_BLOCK_BUFFERS The number of blocks stored in the buffer cache. |
| PROCESSES The maximum number of processes available on the database. |
| ROLLBACK_SEGMENTS Named rollback segments that the database must acquire at startup. |
| LICENSE_MAX_SESSIONS The maximum number of sessions that can connect to the database. |
| LICENSE_MAX_WARNING The sessions trying to connect above the number specified by this parameter will receive a warning message. |
| LICENSE_MAX_USERS The maximum number of users that can be created in the Oracle instance. |
| LICENSE_MAX_SESSIONS and LICENSE_MAX_WARNING are used for license tracking or LICENSE_MAX_USERS is used, bot not both, usually. |
| After creating the parameter file, the DBA executes the create database command, which creates all physical database structures in the database, along with logical database structures like SYSTEM and an initial rollback segment, as well as the SYS and SYSTEM users. On conclusion of the create database statement, the database is created and open. |
| The default password for SYS is CHANGE_ON_INSTALL. |
| The default password for SYSTEM is MANAGER. |
| The create database command also creates the Oracle data dictionary for that database. This task is done first to capture the other database objects that are created in the database. |
| The number of datafiles and redo log files created for the life of the database can be limited with the maxdatafiles and maxlogfiles options of the create database statement. |
| The size of a datafile is fixed, unless the autoextend option is used. |
| The size of a control file is directly related to the number of datafiles and redo logs for the database. |
| At least two scripts are used create the database: |
| catalog.sql Creates all data dictionary tables and views that track database objects like tables, indexes, and rollback segments. |
| catproc.sql Creates dictionary views for all procedural aspects of Oracle, like PL/SQL packages, procedures, functions, triggers, snapshots, and special utility PL/SQL packages such as those used to manage pipes, alerts, locks, etc. |