CHAPTER 13

Instance Management

Exam Objectives

• 062.3.1    Use Database Management Tools

• 062.3.2    Understand Initialization Parameter Files

• 062.3.3    Start Up and Shut Down an Oracle Database Instance

• 062.3.4    View the Alert Log and Access Dynamic Performance Views

After creating a database and an instance, the instance will start, and the database will be open. Assuming that the database was configured with Enterprise Manager Database Express, this will be available as a management tool. What may not be running is the database listener (which is fully described in Chapter 14). Before a user can connect to Database Express, the listener must be running too.

Database Express requires no configuration. It only has to be created, and that is done at database creation time (or it can be installed later). However, the database instance in most cases will require substantial configuration after creation. This is done by adjusting initialization parameters.

Whichever tool is used to connect to the database, it is necessary at this point to understand the two techniques for connecting to an Oracle instance. A normal user is authenticated by presenting a password whose hash is stored within the data dictionary. A privileged user is authenticated either by presenting a password whose hash is stored in the external password file or by presenting an operating system identity that Oracle accepts.

Use Database Management Tools

The tools used in this section are the SQL*Plus command-line utility and the Database Express graphical utility. Some sites will have access to Cloud Control as well, which in some ways is a superior management tool but is beyond the scope of the Oracle Certified Professional (OCP) exams.

Working with SQL*Plus

SQL*Plus is just an elementary process for issuing ad hoc SQL commands to a database. On Windows systems, either launch it from a command prompt or use the shortcut to the sqlplus.exe executable file in your Start menu that the standard installation of Oracle will have created. On Unix, it is called sqlplus. On either operating system you will find the executable program in your ORACLE_HOME/bin directory.

A variation you need to be aware of is the NOLOG switch. By default, the SQL*Plus program immediately prompts you for an Oracle username, password, and database connect string. To launch SQL*Plus without a login prompt, use the /NOLOG switch.

This will give you a SQL prompt from which you can connect with a variety of syntaxes, to be detailed in the next section.

If SQL*Plus does not launch or throws errors when it does launch, the most likely reason is that your operating system session environment is not set up correctly; the ORACLE_HOME variable or the PATH variable is wrong or missing. Figure 13-1 shows examples of this problem, and how to correct it, for Linux. The same technique is applicable on Windows.

Figure 13-1    Launching SQL*Plus from an operating system command prompt

Log On as a Normal or Privileged User

Chapter 16 goes through security in detail, but an understanding of how to connect with SYSDBA privileges is necessary at this point.

A normal user logon requires presenting a username and a password. The username is stored in the data dictionary, along with a hashed version of the password. This technique for logon requires the database to be open. If the database were not open, it would not be possible to query the data dictionary. This raises the question of how one can log on to a database that is not open—or, indeed, to an instance that has not been started. The answer is that Oracle offers two techniques for authenticating oneself that do not require the database to be open: password file authentication and operating system authentication. Both of these give you the option to connect with the SYSDBA or SYSOPER privilege. Only SYSDBA is discussed here (see Chapter 16 for SYSOPER).

Password file authentication compares a username and the hash of a presented password with values stored in the external password file. This is the file that was created with the orapwd utility before the database was created (see Chapter 2 for details). If the values match, the user is logged on as user SYS.

Operating system authentication delegates the authentication to the operating system. At install time, an OS group was nominated as the SYSDBA group (see Chapter 12). If your Unix or Windows user is a member of that group, Oracle will permit you to connect as user SYS with the SYSDBA privilege without presenting a password at all.

The choice of authentication method is made by the syntax used when connecting. Here’s how to connect as a normal user from a SQL prompt:

This is how to connect as a privileged user using password file authentication:

And this is how to connect as a privileged user using operating system authentication:

Figure 13-2 shows all three connection syntaxes. The first example connects as a normal user, over the network. The database must be open (and the database listener must be running) for this to succeed. The second example connects as the privileged user SYS over the network. The database listener must be running, but this would succeed even if the database were shut down. The third example uses operating system authentication to obtain a privileged connection. The SQL*Plus session must be running on the same machine as the database. Neither the database nor the listener need be running for the connection to succeed.

Figure 13-2    Syntax for login with SQL*Plus

Working with Database Express

Database Express is a Java servlet application. Users communicate with it from a browser over a Hypertext Transfer Protocol (HTTP) connection established by the database listener. The application generates pages of Hypertext Markup Language (HTML) that are sent to browsers; users can use these pages to retrieve information or to send updates back. Because it consists of code stored within the database, Database Express cannot be used to start up or shut down a database.

The uniform resource locator (URL) to access Database Express will have been displayed by the Database Configuration Assistant (DBCA) at the conclusion of the database creation. By default, it will be the following:

To determine the HTTP listening port if it is not the default of 5500, log on to the database with SQL*Plus and run this query:

In this example, the listening port is 5500, and the protocol is HTTP. Use the lsnrctl utility to show the status of the listener, as in Figure 13-3.

Figure 13-3    The status of the database listener

In the figure, the lsnrctl utility is run with the status switch. In the STATUS section of the output, you can see that the listener has been running for an hour and a half and that it is listening on two endpoints: port 1521 and port 5500, both on the address db121a.example.com. Port 1521 is the default port for database connections over Oracle Net. Note that port 5500 is listening for HTTP; therefore, this is the port for Database Express, and it is now possible to deduce what the URL for Database Express is.

If the lsnrctl status command returns any sort of error, it is probable that the listener has not been started. In this case, start it with this:

The Database Express login window prompts for a username and password, with the option to specify that the connection should be made AS SYSDBA. If you want to connect as a privileged user, give the username SYS and the password and then check the box for AS SYSDBA. Otherwise, give any username and password that are valid for the database.

Database Express can be installed in a database at database creation time. The DBCA tool prompts for this. It can also be installed subsequently, either by running scripts or (much easier) by using DBCA and selecting the option Configure Database Options. The prerequisites for using Database Express are as follows: First, an HTTP or HTTPS listening port must have been created. To confirm whether this has been done (and to correct the situation if it has not), use the DBMS_XDB_CONFIG package.

Also, if you prefer to use HTTPS for Database Express, use the procedure DBMS_XDB_CONFIG.SETHTTPSPORT rather than DBM_XDB_CONFIG.SETHTTPPORT.

Finally, all access to Database Express is through the shared server mechanism, described in Chapter 14. This requires the existence of a DISPATCHER process; one will be running by default.

Exercise 13-1: Use Database Management Tools    In this exercise, you become familiar with the techniques for connecting as a privileged user, with both SQL*Plus and Database Express. Explore the Database Express user interface by following these steps:

1.  From an operating system prompt, confirm that your account is in the DBA group. On Linux, use the id command.

This output shows that the user is logged on as user oracle and is a member of the groups oinstall and dba. These are the default names for the groups that own the Oracle software and have SYSDBA privilege within the database.

On Windows, use the whoami command.

This output shows that the user is a member of the group ora_dba, which is the name of the DBA group on Windows.

2.  Set the necessary environment variables. This is a Linux example:

And this is a Windows example:

Substitute whatever values are appropriate for your installation.

3.  Launch SQL*Plus and then connect as a privileged user using operating system authentication.

This must show that you are connected as user SYS.

4.  Launch a browser and then issue this URL:

This will present you with a logon screen. Enter the username sys and password oracle (or whatever password you specified when creating the database) and select the “as sysdba” check box.

5.  Explore the Database Express user interface. Select any tabs or links that look interesting. You will need to become familiar with all of them.

Understand Initialization Parameter Files

An instance is defined by the parameters used to build it in memory. It can be changed after startup by adjusting these parameters—if the parameters are ones that can be changed. Some are fixed at startup time and can be changed only by shutting down the instance and starting it again.

Static and Dynamic Parameter Files

Parameter files come in two flavors: the static parameter file (also known as a pfile or an init file) and the dynamic server parameter file (also known as the spfile). Either way, the initialization parameter file stores values for parameters used to build the instance in memory and to start the background processes. There are three default filenames. On Unix they are as follows:

$ORACLE_HOME/dbs/spfile<SID>.ora
$ORACLE_HOME/dbs/spfile.ora
$ORACLE_HOME/dbs/init<SID>.ora

On Windows they are as follows:

%ORACLE_HOME%databaseSPFILE<SID>.ORA
%ORACLE_HOME%databaseSPFILE.ORA
%ORACLE_HOME%databaseINIT<SID>.ORA

In all cases, <SID> refers to the name of the instance that the parameter file will start. The preceding order is important! Unless a pfile is specified in the startup command, Oracle will work its way down the list, using the first file it finds and ignoring the rest. If none of them exists (and a nondefault pfile is not specified), the instance will not start.

The spfile is a server-side file, and it cannot be renamed or relocated. The only exception to this is when using Grid Infrastructure (GI), in which case a nondefault and location name can be registered in the GI registry. It is read by the System Monitor (SMON) background process when the instance is started. The spfile is a binary file, and it cannot be edited by hand. Any attempt to edit will usually corrupt it and make it unusable. The pfile is a client-side file. It exists by default in the ORACLE_HOME directory, but it is in fact read by the user process that issues the command to start the instance. You can rename or move the pfile as you want, but if you do this, it will not be found by default, and you must specify its name and location on your STARTUP command. The pfile is an ASCII text file; edit it with any text editor you like (perhaps with Windows notepad.exe or the Unix vi editor). The spfile is a binary file and cannot be edited manually. To change any values in it, use the ALTER SYSTEM SET… commands from SQL*Plus or the parameter-editing facilities of Database Express.

To create a pfile, just type in the parameter=value pairs, one per line, and save the file with a name that conforms to the standard. To create an spfile, use this SQL*Plus command:

This command will read the nominated text pfile and then use its contents to generate the binary spfile. By default, the files read and written will be those with the standard names in the standard directories. To convert an spfile into a text file that can be edited, use the reverse command.

The CREATE PFILE and CREATE SPFILE commands can be run from SQL*Plus at any time, even before the instance has been started.

Static and Dynamic Parameters and the Initialization Parameter File

To view the parameters and their current values, a query such as this will do:

This query may give slightly different results.

The difference is the view from which the parameter names and values are taken. V$PARAMETER shows the parameter values currently in effect in the running instance. V$SPPARAMETER shows the values in spfile on disk. Usually, these will be the same but not always. Some parameters can be changed while the instance is running; others, known as static parameters, are fixed at instance startup time. A change made to the changeable parameters will have an immediate effect and can optionally be written to the spfile. If this is done, the change will be permanent. The next time the instance is stopped and started, the new value will be read from the spfile. If the change is not saved to the spfile, the change will persist only until the instance is stopped. To change a static parameter, the change must be written to the spfile, and then it will come into effect at the next startup. If the output of the two preceding queries differs, this will typically be because the DBA has done some tuning work that he has not yet made permanent, or he has found it necessary to adjust a static parameter and hasn’t yet restarted the instance.

If the instance is started with a pfile rather than an spfile, the V$SPPARAMETER view will show a NULL as the value for every parameter. Any attempt to change a parameter in the spfile will return an error. Here’s an example:

You can also see the view through Database Express. From the home page, select the Configuration tab and click the Initialization Parameters link. In the subsequent window, shown in Figure 13-4, are two subtabs. Current shows the values currently in effect in the running instance, and SPFile shows those recorded in the spfile.

Figure 13-4    Initialization parameters, as shown through Database Express

You can adjust the changeable parameters through the same window. The values for the first parameter shown (DB_BLOCK_SIZE) cannot be changed; it is not a dynamic parameter. But the next three parameters can be changed. To change the static parameters, it is necessary to select the SPFile subtab and make the changes there.

The Basic Parameters

The instance parameters considered to be “basic” are those that should be considered for every database. To view the basic parameters and their current values, a query such as this will do:

The following query may give slightly different results:

The difference will be because some parameter changes may have been applied to the instance but not the spfile (or vice versa). The join is necessary because there is no column on V$SPPARAMETER to show whether a parameter is basic or advanced. Table 13-1 summarizes the basic parameters.

Table 13-1    Basic Parameters

All these basic parameters, as well as some of the advanced parameters, are discussed in the appropriate chapters.

Changing Parameters

To change parameters with SQL*Plus, use the ALTER SYSTEM command. Figure 13-5 shows examples.

Figure 13-5    Changing and querying parameters with SQL*Plus

The first query in Figure 13-5 shows that the value for the parameter DB_FILE_MULTIBLOCK_READ_COUNT is on by default. It does not exist in the spfile on disk. The next two commands adjust the parameter in both memory and the spfile to different values, using the SCOPE keyword to determine where the change is made. The results are seen in the second query. The final command uses RESET to remove the stored value from the spfile; it will remain in effect within the instance at its current value until the instance is restarted, at which time it will return to the default. Here is the syntax:

Note that the default for the scope clause is BOTH, meaning that if you do not specify a SCOPE, the update will be applied to the running instance and written to the spfile. Therefore, it will become a permanent change.

An example of a static parameter is LOG_BUFFER. If you want to resize the log buffer to 10MB and issue the following command, it will fail with the message “ORA-02095: specified initialization parameter cannot be modified”:

It must be changed with the SCOPE=SPFILE clause, and the instance must be restarted to take effect.

An example of a parameter that applies to the whole instance but can be adjusted for individual sessions is OPTIMIZER_MODE. This influences the way in which Oracle will execute statements. A common choice is between the values ALL_ROWS and FIRST_ROWS. The value ALL_ROWS instructs the optimizer to generate execution plans that will run statements to completion as quickly as possible, whereas FIRST_ROWS instructs it to generate plans that will get something back to the user as soon as possible, even if the whole statement takes longer to complete. Therefore, if your database is generally used for long DSS-type queries but some users use it for interactive work, you might issue the command

and let the individual users issue

if they want.

Exercise 13-2: Query and Set Initialization Parameters    In this exercise, use either SQL*Plus or Database Express to manage initialization parameters. The examples use SQL*Plus, but only because it is clearer to give exact commands than to provide navigation paths through a graphical user interface (GUI). Here are the steps to follow:

1.  Connect to the database (which must be open!) as user SYS, with the SYSDBA privilege. Use either operating system authentication or password file authentication.

2.  Display all the basic parameters, checking whether they have all been set or are still at their default, and note the values for PROCESSES and SESSIONS.

Any basic parameters that are at their default should be investigated to see whether the default is appropriate. In fact, all the basic parameters should be considered. Read up on all of them in the Oracle documentation now. The volume you need is titled “Oracle Database Reference.” Chapter 1 in Part 1 has a paragraph describing every initialization parameter.

3.  Change the PROCESSES parameter to 200. This is a static parameter. It is therefore necessary to specify a SCOPE value and then to bounce the database.

Figure 13-6 shows the sequence of commands. The STARTUP FORCE command is explained in the next section.

Figure 13-6    How to change a static parameter

4.  Rerun the query from step 2. Note the new value for PROCESSES and also for SESSIONS. PROCESSES limits the number of operating system processes allowed to connect to the instance, and SESSIONS limits the number of sessions. These figures are related because each session will require a process. The default value for SESSIONS is derived from PROCESSES, so if SESSIONS was on default, it will now have a new value.

5.  Change the value for the NLS_LANGUAGE parameter for your session. Choose whatever mainstream language you want (Oracle supports 67 languages at the time of writing), but the language must be specified in English (for example, you would use German, not Deutsch):

6.  Confirm that the change has worked by querying the system date.

You may want to change your session language back to what it was before (such as English) with another ALTER SESSION command. If you don’t, be prepared for error messages to be in the language your session is now using.

7.  Change the OPTIMIZER_MODE parameter, but restrict the scope to the running instance only; do not update the parameter file. This exercise enables the deprecated rule-based optimizer, which might be needed while testing some old code, but you would not want the change to be permanent.

8.  Confirm that the change has been effected but not written to the parameter file.

9.  Return the OPTIMIZER_MODE to its standard value in both the running instance and the parameter file.

Note that the scope clause is not actually needed because BOTH is the default.

Start Up and Shut Down an Oracle Database Instance

Oracle Corporation’s recommended sequence for starting a database is to start the database listener and then the database. Starting the database is itself a staged process.

Starting the Database Listener

The database listener is a process that monitors a port for database connection requests. These requests (and all subsequent traffic once a session is established) use Oracle Net, Oracle’s proprietary communications protocol. Oracle Net is a layered protocol running over whatever underlying network protocol is in use (probably TCP/IP). Managing the listener is fully described in Chapter 14, but it is necessary to know how to start it now.

There are two ways to start the database listener:

•  With the lsnrctl utility

•  As a Windows service (Windows only, of course)

The lsnrctl utility is in the ORACLE_HOME/bin directory. The key commands are as follows, where <listener> is the name of the listener:

This will have defaulted to LISTENER, which is correct in most cases. You will know if you have created a listener with another name. Figure 13-3, from earlier in the chapter, shows the output of the lsnrctl status command when the listener (which is indeed named LISTENER) is running.

Note that the first DESCRIPTION line of the output in the figure shows the host address and port on which the listener is listening, and the third line from the bottom states that the listener will accept connections for the service orcl, which is offered by an instance called orcl. These are the critical bits of information needed to connect to the database. Following a successful database creation with DBCA, it can be assumed that they are correct. If the listener is not running, the output of lsnrctl status will make this clear.

Under Windows, the listener runs as a Windows service. It is therefore possible to control it through the services interface. To identify the name of the listener service, use the Services management console. Control it either through the Services console or from a command prompt.

Database Startup and Shutdown

If you are being precise (always a good idea if you want to pass the OCP examinations), you do not start or stop a database. An instance may be started and stopped; a database is mounted and opened and then dismounted and closed. This can be done with SQL*Plus using the STARTUP and SHUTDOWN commands. On a Windows system, it can also be done by controlling the Windows service under which the instance runs.

Connecting with an Appropriate Privilege

Ordinary users cannot start up or shut down a database. You must therefore connect with some form of external authentication. You must be authenticated either by the operating system, as being a member of the group that owns the Oracle software, or by giving a username/password combination that exists in an external password file. You tell Oracle that you want to use external authentication by using the appropriate syntax in the CONNECT command you give in your user process.

If you are using SQL*Plus, the syntax of the CONNECT command tells Oracle what type of authentication you want to use: the default of data dictionary authentication, password file authentication, or operating system authentication. Here are the possibilities:

•  connect user/pass[@connect_alias]

•  connect user/pass[@connect_alias] as sysdba

•  connect user/pass[@connect_alias] as sysoper

•  connect / as sysdba

•  connect / as sysoper

In these examples, user is the username, and pass is the password. The connect_alias will be resolved to a connect string, as described in Chapter 14. Either SYSDBA or SYSOPER is needed to perform a STARTUP or SHUTDOWN. Figure 13-7 shows examples of connecting with these privileges.

Figure 13-7    Use of operating system and password file authentication

Use of the SYSDBA privilege logs you on to the instance as user SYS, the most powerful user in the database and the owner of the data dictionary. Use of the SYSOPER privilege connects you as user PUBLIC. PUBLIC is not a user in any normal sense; they are a notional user with administration privileges, but with no privileges that let them see or manipulate data.

Startup: NOMOUNT, MOUNT, and OPEN

Remember that the instance and the database are separate entities; they can exist independently of each other. The startup process is therefore staged.

1.  You build the instance in memory.

2.  You enable a connection to the database by mounting the controlfile.

3.  You open the database for use.

At any moment, a database will be in one of four states:

•  SHUTDOWN

•  NOMOUNT

•  MOUNT

•  OPEN

When the database is in SHUTDOWN mode, all files are closed, and the instance does not exist. In NOMOUNT mode, the instance has been built in memory (the SGA has been created and the background processes started according to whatever is specified in its parameter file), but no connection has been made to a database. It is indeed possible that the database does not yet exist. In MOUNT mode, the instance locates and reads the database controlfile. In OPEN mode, all database files are located and opened, and the database is made available for use by end users. The startup process is staged. Whenever you issue a startup command, it will go through these stages. It is possible to stop the startup partway. For example, if your controlfile is damaged or a multiplexed copy is missing, you will not be able to mount the database, but by stopping in NOMOUNT mode, you may be able to repair the damage.

At any stage, how does the instance find the files it needs, and exactly what happens? Start with NOMOUNT. When you issue a startup command, Oracle will attempt to locate a parameter file, following the naming convention given earlier.

If no parameter file exists, the instance will not start. The only file used in NOMOUNT mode is the parameter file. The parameters in the parameter file are used to build the SGA in memory and to start the background processes.

Where is the alert log? It’s in the location calculated from the DIAGNOSTIC_DEST parameter. This will have defaulted to the ORACLE_BASE (remember Optimal Flexible Architecture [OFA] from Chapter 2) directory. Within the DIAGNOSTIC_DEST is a standard directory structure. The alert log will be located and named as follows:

For a database and instance named orcl, the values (Windows and Linux) would typically be the following:

If the log already exists, it will be appended to. Otherwise, it will be created. If any problems occur during this stage, trace files can also be generated in the same location.

Once the instance is successfully started in NOMOUNT mode, it may be transitioned to MOUNT mode by reading the controlfile. It locates the controlfile by using the CONTROL_FILES parameter, which it knows from having read the parameter file used when starting in NOMOUNT mode. If the controlfile (or any multiplexed copy of it) is damaged or missing, the database will not mount, and you will have to take appropriate action before proceeding. All copies of the controlfile must be available and identical if the mount is to be successful.

As part of the mount, the names and locations of all the datafiles and online redo logs are read from the controlfile, but Oracle does not yet attempt to find them. This will happen during the transition to OPEN mode. If any files are missing or damaged, the database will remain in MOUNT mode and cannot be opened until you take appropriate action. Furthermore, even if all the files are present, they must be synchronized before the database opens. If the last shutdown was orderly, with all database buffers in the database buffer cache being flushed to disk by DBWn, then everything will be synchronized. Oracle will know that all committed transactions are safely stored in the datafiles and that no uncommitted transactions are hanging around waiting to be rolled back. However, if the last shutdown was disorderly (such as from a loss of power or from the server being rebooted), Oracle must repair the damage, and the database is considered to be in an inconsistent state. The mechanism for this process (known as instance recovery) is described in Chapter 22. The process that mounts and opens the database (and carries out repairs, if the previous shutdown was disorderly) is the SMON process. Only once the database has been successfully opened will Oracle permit user sessions to be established with normal data dictionary authentication.

Shutdown should be the reverse of startup. During an orderly shutdown, the database is first closed and then dismounted, and finally the instance is stopped. During the close phase, all sessions are terminated; active transactions are rolled back, completed transactions are flushed to disk by DBWn, and the datafiles and redo log files are closed. During the dismount, the controlfile is closed. Then the instance is stopped by deallocating the SGA and terminating the background processes.

Shutdown: NORMAL, IMMEDIATE, TRANSACTIONAL, and ABORT

Here are the options that can be used on the SHUTDOWN command, all of which require either a SYSDBA or a SYSOPER connection:

•  Normal    This is the default. No new user connections will be permitted, but all current connections are allowed to continue. Only once all users have (voluntarily!) logged off will the database actually shut down.

•  Transactional    No new user connections are permitted. Existing sessions that are not in a transaction will be terminated; sessions currently in a transaction are allowed to complete the transaction and will then be terminated. Once all sessions are terminated, the database will shut down.

•  Immediate    No new sessions are permitted, and all currently connected sessions are terminated. Any active transactions are rolled back, and the database will then shut down.

•  Abort    As far as Oracle is concerned, this is the equivalent of a power cut. The instance terminates immediately. Nothing is written to disk, and there is no attempt to terminate transactions in progress in any orderly fashion.

The normal, immediate, and transactional shutdown modes are usually referred to as clean or consistent shutdowns. After all sessions are terminated, PMON will roll back any incomplete transactions. A checkpoint is then issued that forces the DBWn process to write all updated data from the database buffer cache down to the datafiles. LGWR also flushes any change vectors still in memory to the log files. Then the file headers are updated and the file handles closed. This means that the database is in a “consistent” state: All committed transactions are in the datafiles, there are no uncommitted transactions hanging about that need to be rolled back, and all datafiles and log files are synchronized.

The abort mode leaves the database in an inconsistent state; it is quite possible that committed transactions have been lost because they existed only in memory and DBWn had not yet written them to the datafiles. Equally, there may be uncommitted transactions in the datafiles that have not yet been rolled back. This is a definition of a corrupted database. It may be missing committed transactions or storing uncommitted transactions. These corruptions must be repaired by instance recovery (described in Chapter 22). It is exactly as though the database server had been switched off, or perhaps rebooted, while the database was running.

An orderly shutdown is a staged process, and it is theoretically possible to control the stages. The SQL*Plus commands are as follows:

These commands will exactly reverse the startup sequence. In practice, however, there is no value to them; a SHUTDOWN is all any DBA will ever use.

Exercise 13-3: Start Up and Shut Down an Oracle Database Instance    Use SQL*Plus to start an instance and open a database. If the database is already open, do this in the opposite order. Note that if you are working in Windows, the Windows service for the database must be running. It will have a name of the form OracleServiceSID, where SID is the name of the instance.

1.  Log on to the computer as a member of the operating system group that owns the ORACLE_HOME and then set the environment variables appropriately for ORACLE_HOME and PATH and ORACLE_SID, as described previously.

2.  Connect as SYS with operating system authentication.

3.  Start the instance only.

4.  Mount the database.

5.  Open the database.

6.  Confirm that the database is open.

This will return READ WRITE if the database is open.

7.  Shut down the database.

Figure 13-8 shows the entire sequence of steps 2 through 7.

Figure 13-8    Database startup and shutdown

8.  Restart the database.

Observe that the default startup mode is OPEN.

View the Alert Log and Access Dynamic Performance Views

The alert log is a vital source of information regarding important events in the life of the database instance. It is a continuous historical record of events. The dynamic performance views give real-time information, in other words, what is happening right now or in the recent past. In addition to the alert log, there is an optionally enabled Data Definition Language (DDL) log and a debug log generated automatically in the event of certain errors.

The Alert Log

The alert log is a continuous record of critical operations applied to the instance and the database. Its location is derived from the instance parameter DIAGNOSTIC_DEST, and its name is alert_<SID>.log, where <SID> is the name of the instance. The DIAGNOSTIC_DEST defaults to the ORACLE_BASE, and the alert log will be beneath that.

A copy of the alert log in Extensible Markup Language (XML) format is also maintained in a different directory.

The critical operations recorded in the alert include the following:

•  All startup and shutdown commands, including intermediate commands such as ALTER DATABASE MOUNT

•  All errors internal to the instance (the ORA-600 errors, about which the DBA can do nothing other than investigate them using My Oracle Support and report them to Oracle Support if they are new issues)

•  Any detected datafile block corruptions

•  Any row-locking deadlocks that may have occurred

•  All operations that affect the physical structure of the database, such as creating or renaming datafiles and online redo logs

•  All ALTER SYSTEM commands that adjust the values of initialization parameters

•  All log switches and log archives

The alert log entry for a startup shows all the initialization parameters specified in the parameter file. This information, together with the subsequent record of changes to the instance with ALTER SYSTEM and to the database physical structures with ALTER DATABASE, means that it is always possible to reconstruct the history of changes to the database and the instance. This can be invaluable when you are trying to backtrack in order to find the source of a problem.

Trace files are generated by the various background processes, usually when they hit an error. These files will be located in the trace directory, along with the alert log. If a background process has failed because of an error, the trace file generated will be invaluable in diagnosing the problem.

The DDL Log

Should the DBA choose to enable this, it is possible to record DDL commands in a log file. The statement used (without any supporting information, such as who issued it) is recorded in a text file named ddl_<instancename> in the following directory, with the same information in an XML file named log.xml:

To enable DDL logging, the instance parameter ENABLE_DDL_LOGGING must be set to TRUE (the default is FALSE).

The Dynamic Performance Views

There are more than 600 dynamic performance views. You will often hear them referred to as the vee dollar views because their names are prefixed with V$. In fact, the vee dollar views are not views at all—they are synonyms to views that are prefixed with V_$, as shown in Figure 13-9.

Figure 13-9    A V$ view (or rather, a view and its V$ synonym)

The figure shows V$INSTANCE, which has one row with some summary information about the instance. Most views are populated with information from the instance; the remainder are populated from the controlfile. All of them give real-time information. Dynamic performance views that are populated from the instance, such as V$INSTANCE or V$SYSSTAT, are available at all times, even when the instance is in NOMOUNT mode. Dynamic performance views that are populated from the controlfile, such as V$DATABASE and V$DATAFILE, cannot be queried unless the database has been mounted, which is when the controlfile is read. By contrast, the data dictionary views (prefixed DBA_, ALL_, or USER_) can be queried only after the database—including the data dictionary—has been opened.

The dynamic performance views are created at startup, updated during the lifetime of the instance, and dropped at shutdown. This means that they will contain values that have been accumulated since startup time; if your database has been open for six months nonstop, they will have data built up over that period. After a shutdown/startup, they will start from the beginning again.

Exercise 13-4: Use the Alert Log and Dynamic Performance Views    In this exercise, you will locate the alert log and find the entries for the parameter changes made in Exercise 13-2 and the startups and shutdowns in Exercise 13-3.

1.  Connect to your database with SQL*Plus and then display the value of some parameters.

Note that the manner in which the name of the trace directory is derived.

2.  Using whatever operating system tool you choose (such as the Windows Explorer or whatever file system browser your Linux session is using), navigate to the directory identified in step 1.

Open the alert log. It will be a file called alert_<SID>.log, where <SID> is the name of the instance. Use any editor you please (but note that on Windows, Notepad may not be a good choice because of the way carriage returns are handled).

Go to the bottom of the file. You will see the ALTER SYSTEM commands of Exercise 13-2 and the results of the startups and shutdowns.

3.  Use dynamic performance views to determine what datafile and tablespaces make up the database.

Obtain the same information from data dictionary views.

4.  Determine the location of all the controlfile copies, in two ways.

5.  Determine the location of the online redo log file members as well as their size. Because the size is an attribute of the group, not the members, you will have to join two views.

Two-Minute Drill

Use Database Management Tools

•  SQL*Plus is always available. Database Express can make administration easy, but it depends on underlying database objects; it is therefore not available until the database is open.

Understand Initialization Parameter Files

•  A database instance may be started from either a static parameter file (the init file) or a dynamic server parameter file (the spfile). If both exist, the spfile takes precedence.

•  Static parameters cannot be changed without a shutdown/startup.

•  Other parameters can be changed dynamically for the instance or a session.

•  Parameters can be seen in the dynamic performance views V$PARAMETER and V$SPPARAMETER.

Start Up and Shut Down an Oracle Database Instance

•  The stages are NOMOUNT, MOUNT, and OPEN.

•  NOMOUNT mode requires a parameter file.

•  MOUNT mode requires the controlfile.

•  OPEN mode requires the datafiles and online redo log files.

View the Alert Log and Access Dynamic Performance Views

•  The alert log is a continuous stream of messages regarding critical operations.

•  Trace files are generated by background processes, usually when they hit errors.

•  The dynamic performance views are populated from the instance or the controlfile.

•  The data dictionary views are populated from the data dictionary.

•  Dynamic performance views accumulate values through the lifetime of the instance, and they are reinitialized at startup.

•  Data dictionary views show information that persists across shutdown and startup.

•  Both the data dictionary views and the dynamic performance views are published through synonyms.

Self Test

1.  You issue the URL https://127.0.0.1:5500/em and receive an error. What could be the problem? (Choose all correct answers.)

A.  You have not started the database listener.

B.  Database Express is running on a different port.

C.  You are not logged on to the database server node.

D.  You have not started the Cloud Control agent.

E.  You have not started the database.

2.  What protocols can be used to contact Database Express? (Choose all correct answers.)

A.  HTTP

B.  HTTPS

C.  Oracle Net

D.  IPC

3.  What will be the setting of the OPTIMIZER_MODE parameter for your session after the next startup if you issue these commands:

(Choose the best answer.)

A.  all_rows

B.  rule

C.  first_rows

4.  The LOG_BUFFER parameter is a static parameter. How can you change it? (Choose the best answer.)

A.  You cannot change it because it is static.

B.  You can change it only for individual sessions; it will return to the previous value for all subsequent sessions.

C.  You can change it within the instance, but it will return to the static value at the next startup.

D.  You can change it in the parameter file, but the new value will come into effect only at the next startup.

5.  Which files must be synchronized for a database to open? (Choose the best answer.)

A.  Datafiles, online redo log files, and the controlfile.

B.  Parameter file and password file.

C.  All the multiplexed controlfile copies.

D.  None. SMON will synchronize all files by instance recovery after opening the database.

6.  During the transition from NOMOUNT to MOUNT mode, which files are required? (Choose the best answer.)

A.  Parameter file

B.  Controlfiles

C.  Online redo logs

D.  Datafiles

E.  All of the above

7.  You shut down your instance with SHUTDOWN IMMEDIATE. What will happen on the next startup? (Choose the best answer.)

A.  SMON will perform automatic instance recovery.

B.  You must perform manual instance recovery.

C.  PMON will roll back uncommitted transactions.

D.  The database will open without recovery.

8.  You issue the command SHUTDOWN, and it seems to hang. What could be the reason? (Choose the best answer.)

A.  You are not connected as SYSDBA or SYSOPER.

B.  There are other sessions logged on.

C.  You have not connected with operating system or password file authentication.

D.  There are active transactions in the database; when they complete, the SHUTDOWN will proceed.

9.  What action should you take after terminating the instance with SHUTDOWN ABORT? (Choose the best answer.)

A.  Back up the database immediately.

B.  Open the database and perform database recovery.

C.  Open the database and perform instance recovery.

D.  None, but some transactions may be lost.

E.  None. Recovery will be automatic.

10.  Which of these actions will not be recorded in the alert log? (Choose all correct answers.)

A.  ALTER DATABASE commands

B.  ALTER SESSION commands

C.  ALTER SYSTEM commands

D.  Archiving an online redo log file

E.  Creating a tablespace

F.  Creating a user

11.  Which parameter controls the location of background process trace files? (Choose the best answer.)

A.  BACKGROUND_DUMP_DEST.

B.  BACKGROUND_TRACE_DEST.

C.  DB_CREATE_FILE_DEST.

D.  DIAGNOSTIC_DEST.

E.  No parameter. The location is platform specific and cannot be changed.

12.  Which of these views can be queried successfully in nomount mode? (Choose all correct answers.)

A.  DBA_DATA_FILES

B.  DBA_TABLESPACES

C.  V$DATABASE

D.  V$DATAFILE

E.  V$INSTANCE

F.  V$SESSION

13.  Which view will list all tables in the database? (Choose the best answer.)

A.  ALL_TABLES

B.  DBA_TABLES

C.  USER_TABLES, when connected as SYS

D.  V$FIXED_TABLE

Self Test Answers

1.      A, B, C, and E. Both the database listener and the database itself must be running to use Database Express. It is also possible the HTTP listening service may not be on the default port of 5500, and the loopback address will function only if you are running the browser on the database server machine.
    D is incorrect. The Cloud Control agent is not needed to use Database Express.

2.      A and B. Both HTTP and HTTPS can be used, provided that an appropriate listening port has been configured with XDB.
    C and D are incorrect. Oracle Net and IPC are the protocols that can be used by user processes to contact the server, not by browsers to contact Database Express.

3.      B. The default scope of ALTER SYSTEM is BOTH, meaning memory and spfile.
    A and C are incorrect. A is incorrect because this setting will have been replaced by the setting in the second command. C is incorrect because the session-level setting will have been lost during the restart of the instance.

4.      D. This is the technique for changing a static parameter.
    A, B, and C are incorrect. A is incorrect because static parameters can be changed—but only with a shutdown. B and C are incorrect because static parameters cannot be changed for a running session or instance.

5.      A. These are the files that make up a database, and all must be synchronized if the database is to open.
    B, C, and D are incorrect. B is incorrect because these files are not, strictly speaking, part of the database. C is incorrect because a problem with a controlfile copy would mean that the database could not be mounted, never mind opened. D is incorrect because SMON’s instance recovery mechanism can fix problems only in datafiles, not anything else.

6.      B. Mounting the database is the process of opening the controlfile (all copies thereof).
    A, C, D, and E are incorrect. A is incorrect because the parameter file is needed only for NOMOUNT. C, D, and E are incorrect because these file types are needed only for open mode.

7.      D. An immediate shutdown is clean, so no recovery will be required.
    A, B, and C are incorrect. No recovery or rollback will be required; all the work will have been done as part of the shutdown.

8.      B. The default shutdown mode is SHUTDOWN NORMAL, which will hang until all sessions have voluntarily disconnected.
    A, C, and D are incorrect. A and C are incorrect because these would cause an error, not a hang. D is incorrect because it describes SHUTDOWN TRANSACTIONAL, not SHUTDOWN NORMAL.

9.      E. No action is required; recovery will be automatic.
    A, B, C, and D are incorrect. A is incorrect because this is one thing you should not do after an ABORT. B is incorrect because database recovery is not necessary, only instance recovery. C is incorrect because instance recovery will occur automatically in mount mode at the next startup. D is incorrect because no transactions will ever be lost as a result of an ABORT.

10.      B and F. Neither of these affects the structure of the database or the instance; they are not important enough to generate an alert log entry.
    A, C, D, and E are incorrect. All of these are changes to physical or memory structures, and all such changes are recorded in the alert log.

11.      D. This is the parameter used to determine the location of background trace files and indeed the whole of the Automatic Diagnostic Repository.
    A, B, C, and E are incorrect. A is incorrect because although this parameter does still exist, it is deprecated. B is incorrect because there is no such parameter. C is incorrect because this is the default location for datafiles, not trace files. E is incorrect because although there is a platform-specific default, it can be overridden with a parameter.

12.      E and F. These views are populated from the instance and will therefore be available at all times.
    A, B, C, and D are incorrect. A and B are data dictionary views, which can be seen only in open mode. C and D are dynamic performance views populated from the controlfile and are therefore available only in mount mode or open mode.

13.      B. The DBA views list every appropriate object in the database.
    A, C, and D are incorrect. A is incorrect because this will list only the tables the current user has permissions on—which might be all the tables but probably isn’t. C is incorrect because it will list only the tables owned by SYS. D is incorrect because this is the view that lists all the dynamic performance views, not all the tables.