The purpose of this chapter is to familiarize you with the basic, mid-level, and many advanced functions of SQL*DBA. It should also help you to put that knowledge to use in your current and future environments. Although this chapter's coverage is not
exhaustive, it should certainly give you all the requirements you need to forge ahead in understanding the more advanced concepts of database management with SQL*DBA.
To assist in the setup, administration, and day-to-day operations of your Oracle databases, Oracle Corporation provided a tool called SQL*DBA. Although SQL*DBA has many functions, its most obvious use is to start up and shut down local databases. If you
have SQL*Net installed, SQL*DBA uses its features to start up and shut down remote databases as well. This versatility gives the database administrator great flexibility in database management.
Secondary functions of SQL*DBA include altering database and system statistics, modifying the characteristics of a database, administering users and security, restoring a database, and manipulating data files belonging to the databases. With the added
capability to monitor various aspects of the database as it is running, SQL*DBA is an important and multifaceted tool.
SQL*DBA has three modes of operation:
Line mode is a non-graphical interface that enables the user to interactively enter commands. The output is scrolled across the user's screen. This mode is very useful for managing portions of the database that do not require the ease of screen mode.
Command mode is identical to line mode with the exception of how it is used. Although line mode is interactive by nature, command mode is intended to run in batch mode. Generally, command mode is used to run a script, or collection of commands, created
by the user.
Menu mode, also referred to as screen mode, is a graphical interface that you can use on supported ASCII terminals, including X terminals. It provides the user with a menu-driven interface from which they can issue most SQL*DBA commands.
Although the menu interface is quite powerful, a handful of commands can only be executed from the command line. Table 7.1 contains a list of those commands. Screen mode supports all the commands that line mode supports in addition to a feature called
monitor, which I discuss later in this chapter.
Command |
Purpose |
DESCRIBE |
Describes tables and views |
EXECUTE |
Executes PL/SQL blocks |
|
Prints the value of a variable defined with the VARIABLE command |
REMARK |
Denotes a comment or remark and prevents the interpreter from executing the line |
SET |
Sets or modifies the characteristics of the current SQL*DBA session |
SHOW |
Shows characteristics of the current SQL*DBA session |
VARIABLE |
Defines a variable to be used within the current SQL*DBA session |
Each of the modes is covered in greater detail in later sections. First, it is important to cover a few topics before proceeding to the commands.
SQL*DBA accepts all standard SQL, PL/SQL, and SQL*DBA commands with few exceptions. Exceptions to this rule include SQL*Plus formatting commands such as set heading or set linesize. You can find a complete list of SQL*DBA specific commands in the
command reference at the end of this chapter.
There are several requirements to meet before you can run SQL*DBA. First, the user must either own the executable or be a member of the group associated with it. You can find the SQL*DBA executable in the bin directory of your ORACLE_HOME environment
variable; it's usually named sqldba (DOS-based systems might include an EXE or COM extension). If you are running Oracle on a DOS machine, the executable is located in the directory pointed to by your XBIN environment variable.
Once you can execute SQL*DBA, you must also have privileges to execute the specific commands you want to use. For instance, if you want to add a data file to a tablespace, you must have the ALTER TABLESPACE privilege.
Next, it is important to know how to connect to a database. Nearly all the commands supported by SQL*DBA require a connection to a database (especially startup and shutdown options).
All SQL*DBA modes support the connect commands. With a username/password specification, you can connect to your default database. SQL*Net supports special connect strings to connect to remote databases on your network. You can use this option to
eliminate the need for logging into every system where a database is running.
connect internal is the most typical connection using SQL*DBA. Internal, a special username viable only through SQL*DBA, is actually a alias for logging into the SYS account. Intended strictly for use with special operations such as startup and
shutdown, the internal username is limited to users with the correct access to SQL*DBA (ownership and group access to the executable).
SQL*DBA, like SQL*Plus, enables you to execute multiple-line commands. By default, you must terminate all commands with a semicolon (;) or forward slash (/), regardless of which mode you are using. These characters tell SQL*DBA to execute the command.
Some commands might not require the terminator. If you press return while entering a command where SQL*DBA expects further input, SQL*DBA provides a continuation line where you can enter the next part of the command line. Entering the termination character
ends the command and begins execution.
I mentioned earlier the three modes of operation for SQL*DBA; only one major difference distinguishes the three modes. Screen mode is the only mode capable of running monitor programs that help you monitor various aspects of your instance. Apart from
this difference, all three modes function identically. Commands that you can issue in line mode work identically when issued in either screen or command modes.
Line mode places the user into a line-driven, interactive interface. This mode does not support menus or additional input devices other than the keyboard. You usually use line mode for quick access to SQL*DBA commands or for automating different aspects
of its functionality.
Because line and screen mode are identical in nature except for monitor commands, I limit the scope of this discussion on line mode to starting SQL*DBA in line mode, starting up and shutting down the database, and automating the startup and shutdown
processes. The following section on screen mode goes into much greater depth about the commands available in SQL*DBA.
To start SQL*DBA in line mode, you can enter one of the following commands: sqldba mode=line or sqldba lmode=y. After starting SQL*DBA, you should see a prompt that resembles SQLDBA>.
At this point, you can connect to the database using connect internal or connect username/password. Once you are finished with your SQL*DBA session, you can issue the exit command to leave.
As stated before, the primary use for SQL*DBA is the startup and shutdown of your database. You can do this from line or screen mode, but it seems the most common way is from line mode.
To start up a database, use the following set of commands.
You can substitute any one of the startup options for the STARTUP command listed in step 3, such as STARTUP MOUNT, or STARTUP FORCE. Later on, I discuss the startup and shutdown options in greater depth.
To shut down the database from line mode, use the following steps:
You can substitute other shutdown options for the SHUTDOWN option listed in step 3, depending on your site's needs. An example is using the SHUTDOWN IMMEDIATE command if you want to log out all processes connected to the database.
Command mode enables you to place a group of commands in the same file to be executed together. Various uses of command mode include starting up or shutting down an instance and collecting data for a custom report.
The format and execution of a command file for use in SQL*DBA is identical to the format of command files used in SQL*Plus. The following is a list of guidelines you can use for formatting a command file.
To call a command script while executing SQL*DBA, you can use the following command format:
sqldba command="@filename.sql"
SQL*DBA requires the quotes and the @ to execute the script properly. The following is a sample startup script:
REM REM startup.sql to be used to automate the startup REM of a database through SQL*DBA command mode REM connect internal; startup;
With the preceding script, you can start up your database with the following command:
sqldba command="@startup.sql"
The following is a sample shutdown script:
REM REM shutdown.sql to be used to automate the shutdown REM of a database through SQL*DBA command mode connect internal; shutdown;
You can then execute this script in same way you executed startup.sql:
sqldba command="@shutdown.sql"
With a certain amount of operating system expertise, you can automate the startup and shutdown of your Oracle instance. This can be helpful if you have an operations staff that routinely must start and stop your database instances for basic maintenance
(system reboot, offline backup, and so on).
The previous section discussed one possible way to automate startup or shutdown using command scripts. Another, more typical way is to embed these commands in a script that a system operator could run without any knowledge of Oracle commands or SQL*DBA
modes. The following is a sample UNIX script for starting an Oracle instance.
#! /bin/sh ########## # filename: oracle_start.sh ##### # As Oracle recommends, the first line is to force the script # to run in the Bourne Shell. ##### ##### # This script should be run from the Oracle DBA account. # It assumes that ORACLE_HOME has been defined. If it has not, # this script will exit with the appropriate error message. # # Other assumptions include that your ORACLE_SID has been set before # running this script. # ##### # If ORACLE_HOME = nothing then exit with a status of 1 if [ "${ORACLE_HOME}" = "" ] then echo "ORACLE_HOME is undefined. It must be defined before" echo "continuing." exit 1 fi # If ORACLE_SID is undefined, exit with a status of 2 if [ "${ORACLE_SID}" = "" ] then echo "ORACLE_SID is undefined. It must be defined before" echo "continuing." exit 2 fi # Check to see if the database is up # if the sgadef(instance).dbf file is there, the instance is # running and the startup should NOT proceed if [ -f "${ORACLE_HOME}/dbs/${ORACLE_SID}.dbf" ] then echo "The ${ORACLE_SID} instance of Oracle is running" echo "You must shutdown before starting up." exit 3 fi # The database is not running, so let's start it in normal mode # using the script we defined in the preceding sections. # If we cannot find sqldba, then we will exit with an error if [ -f "${ORACLE_HOME}/bin/sqldba" ] then sqldba command="@startup.sql" else echo "Could not locate the sqldba executable. Startup cannot" echo "proceed." exit 4 fi # Now let's check to see if it came up. if [ -f "${ORACLE_HOME}/dbs/${ORACLE_SID}.dbf" ] then echo "The instance has now started." else echo "The instance is NOT started. Please check for errors " echo "before attempting to restart the database." exit 5 fi exit 0 ##################################
The following code is a sample shutdown script. This program is almost identical to the startup script except that is uses shutdown.sql and reverses the logic in checking for the sgadef(instance).dbf file.
#! /bin/sh ########## # filename: oracle_stop.sh ##### # As Oracle recommends, the first line is to force the script # to run in the Bourne Shell. ##### ##### # This script should be run from the Oracle DBA account. # It assumes that ORACLE_HOME has been defined. If it has not, # this script will exit with the appropriate error message. # # Other assumptions include that your ORACLE_SID has been set # before running this script. # ##### # If ORACLE_HOME = nothing then exit with a status of 1 if [ "${ORACLE_HOME}" = "" ] then echo "ORACLE_HOME is undefined. It must be defined before" echo "continuing." exit 1 fi # If ORACLE_SID is undefined, exit with a status of 2 if [ "${ORACLE_SID}" = "" ] then echo "ORACLE_SID is undefined. It must be defined before " echo "continuing." exit 2 fi # Check to see if the database is down # if the sgadef(instance).dbf file is not there, the instance is # not running and the shutdown should exit if [ ! -f "${ORACLE_HOME}/dbs/${ORACLE_SID}.dbf" ] then echo "The ${ORACLE_SID} instance of Oracle is not currently echo "running. You must startup before you can shutdown." exit 3 fi # The database is running, so let's do a normal shutdown # using the script we defined in the preceding sections. # If we cannot find sqldba, then we will exit with an error if [ -f "${ORACLE_HOME}/bin/sqldba" ] then sqldba command="@shutdown.sql" else echo "Cannot locate sqldba. Cannot continue the shutdown." exit 4 fi # Now let's check to see if it was shut down if [ -f "${ORACLE_HOME}/dbs/${ORACLE_SID}.dbf" ] then echo "The instance is still running, or an error" echo "occurred while trying to shutdown the instance." echo "Please refer to the alert log for any errors that" echo "might have occurred." exit 5 else echo "The instance was shut down." fi exit 0 ##########
Although there are many arguments about whether a menu interface is faster or more efficient as opposed to an interactive line interface, there is no doubt that a menu interface is much easier to use. Otherwise, you'd have to face memorizing the myriad
of commands that are available to SQL*DBA. First, I discuss the various components of the menu, and then, I discuss exactly what the menus do.
There are four important parts of the display that are pointed out in Figure 7.1.
Figure 7.1. SQL*DBA menu items.
On the menu bar across the top of your screen should appear ten choices: File, Edit, Session, Instance, Storage, Log, Backup, Security, Monitor, and Help. Each of these choices represents a menu or set of menus to assist in executing various SQL*DBA
commands.
Just below the menu bar is the output window. This window displays the output to each command you type and scrolls down as it fills.
The bottom of the screen is devoted to the input window, which is where you enter all input. The input window must be the current window to accept or display your keystrokes.
To the right of the output and input windows are two scroll bars (one for each window). These bars tell you where you are in the current windows and how much more information is left to display.
Each menu could request more information, ask you to select specific options, or provide you with a list of items to choose from. In some versions of SQL*DBA and for some selections, you'll notice a keystroke command on the right of the menu option
(Esc,P, for example). You can use that shortcut key as another way to reach the menu. The following lists the various types of objects associated with the different menus.
If you don't know the navigational keystrokes, using SQL*DBA can be a difficult to nearly impossible task. All keys are defined through the Oracle*Terminal package and can be redefined by the user. The examples listed throughout this section use the
default keystrokes for a VT-220 terminal.
Knowing how to get a complete listing of all keystrokes will put you far ahead in the game. Once in SQL*DBA, press Esc,K or Ctrl+K. This places you in the Key Help for Text Editor window. There are two columns of data: Function and Keys. Function
describes what the Keys (or keystroke) option does, and the Keys option shows the keystroke used to issue the command.
Table 7.2 contains a partial listing of the more critical commands required for navigating the menus. You can find a complete listing of Oracle's predefined functions by using the show keys command or by running Oracle*Terminal and examining the keys
defined for your terminal type.
Function Keys Description
Cancel |
PF4 |
Cancel the current operation (includes closing windows) |
Menu |
K0 |
Move to the menu bar (0 on your numeric keypad) |
Next Group |
Tab |
Set focus on output or input windows (cycles through them) |
Show Keys |
Ctrl+K or Esc,K |
Display complete table of keys and their functions |
|
|
|
You use a typical navigation sequence when you start up your database. One way to accomplish this task is to issue the Menu command, use your arrow keys to move over to Instance, press Return, move your cursor over the Start Up command, press Return,
and then follow the different prompts (navigating with the spacebar and tab keys), pressing Return when you reach OK.
Another quicker way is to issue the Menu command, press I (the underlined letter in Instance), press U (the underlined character in Start Up), and then navigate through the prompts until you get to OK.
Finally, you could issue the shortcut command Esc,P, navigate through the menu to choose your startup options, and select OK when finished.
As you can see, you can use many different options to navigate through the menus. The ways you choose to navigate depend on your familiarity with them and what kind of terminal you have. There is no right or wrong way, only preferred ways.
If you have a terminal that supports mouse input, one final way to use the menus is to move your cursor to the menu selection and click the mouse button.
The menus in SQL*DBA provide almost the entire range of the command set. You must enter many different menus and options before a command is accepted and executed. Being familiar with these menus can greatly assist you in identifying and solving
problems of all kinds. The following section explains the menus of SQL*DBA and how to use them. I also provide the actual line mode commands along with some tips on their uses.
The File option, shown in Figure 7.2, has four separate options: SQL Script, Spool On, Spool Off, and Quit.
Figure 7.2. SQL*DBA File menu.
You use SQL Script to execute a SQL script file while still in SQL*DBA. After selecting this option, you are provided with a dialog box containing two options. The first is whether to Use Default File Path Name and File Extension. If you select this
option, SQL*DBA searches through your path environment variable for the file. The second option is the SQL Script to Execute (Mandatory). You choose the name of the file to run. Specify the command on the line provided and select OK.
After you select OK, the dialog box disappears, and the output scrolls down in the output window of your screen. Use the Next Group key to navigate to the output window to scroll through the output generated by your script.
Spool On requests a filename (mandatory) to send output to. This file is a log of all input and output generated during your SQL*DBA session with the exception of monitor output. Because the monitor output is changed every cycle, it cannot be saved to a
spool file. You are limited to having only one spool file open at any time. If you attempt to spool to a second file, the first file closes before a second one opens.
Command type: SQL*Plus
Line mode command: SPOOL filename
Required privileges: Write permissions for the current directory.
Spool Off closes the previously opened spool file (if any). SQL*DBA automatically keeps track of filenames and closes the currently open file. If you have no previously opened spool file, SQL*DBA generates an appropriate error message.
Command type: SQL*Plus
Line mode command: SPOOL OFF
Required privileges: None
Quit, which had a shortcut of Esc,Q, brings up a caution window asking whether it is really OK to quit your session or if you want to cancel the quit session command. Selecting OK exits you to your operating system prompt.
Command type: SQL*Plus, SQL*DBA
Line mode command: EXIT
QUIT
Required privileges: None
The Edit menu in Figure 7.3 provides you with Cut and Paste commands, as well as Previous Command and Next Command options. Please note that there are no associated SQL*DBA commands for these options.
Choosing the Cut command cuts the currently selected text. Copy copies the previously selected text from the buffer to your current location in the menu. Paste copies the currently selected text into the edit buffer. Clear erases the currently selected
text. It has no effect on the clipboard buffer.
Previous Command and Next Command both scroll through the last ten commands executed from the input window. Choosing these commands can be quicker than trying to retype one of your previous commands. Once you have selected the command you want to
reissue, press Return to execute it.
Figure 7.4 shows the Session menu. The Connect command requests a username (mandatory) and a password in order to connect to the database. Specifying internal at the username data entry field connects you as a privileged user.
Command type: SQL*DBA
Line mode command: CONNECT
Required privileges: CREATE SESSION
Disconnect does the opposite of connect; that is, it disconnects you from the database. In this mode, you can issue only commands not requiring access to database tables.
Command type: SQL*DBA
Line mode command: DISCONNECT
Required privileges: None
Enter System Command (mandatory) enables you to execute operating system commands (or programs) from within SQL*DBA. Once this field is entered and accepted, you see an operating system window where the output from your command or program is displayed.
Once the command finishes, you get a prompt to Hit Any Key to Continue. After pressing any key, you return to the SQL*DBA window.
Command type: SQL*DBA
Line mode command: HOST
Required privileges: None
Go to System takes you to an operating system window where you can enter multiple commands (as if you had exited SQL*DBA completely). It does not, however, make a complete copy of your environment, so some aliases or environment variables might not work
or display correctly. You can figure this out by trial and error.
Command type: SQL*DBA
Line mode command: HOST
Required privileges: None
Set Server Output On enables debugging output from stored procedures that use the DBMS_OUTPUT PUT() and PUT_LINE() commands. The size option enables you to specify, in bytes, the length of the message buffer that can be accumulated at one time. If the
buffer fills before calls to get message can make more room for additional data, an error is returned to the program or procedure that is sending the message. The minimum for this buffer is 2,000 bytes.
Command type: SQL*DBA
Line mode command: SET SERVEROUT
Required privileges: None
Set Server Output Off disables those messages. These two command are very useful in coding debug information into your procedures.
Command type: SQL*DBA
Line mode command: SET SERVEROUT
Required privileges: None
The Instance menu options are shown in Figure 7.5. The menu enables you to start, stop, and configure various characteristics of how the instance will run. It has the following options: Start Up, Shut Down, Mount Database, Open Database, Force
Checkpoint, Force Log Switch, Configure Dispatcher, Configure Shared Server, Prevent Connection, Allow Connection, and Kill Session.
You use the Start Up option to mount and open the database for maintenance or use. You specify in the dialog box exactly how to bring this instance online.
Command type: SQL*DBA
Line mode command: STARTUP
Required privileges: Capability to connect as INTERNAL
Shut Down enables you to specify one of three ways to bring your database down: Normal, Immediate, or Abort. These options are explained further in the command reference.
Command type: SQL*DBA
Line mode command: SHUTDOWN
Required privileges: Capability to connect as INTERNAL
Mount Database enables you to mount the database in either Exclusive mode (only a single instance can access the data files) or Parallel mode (multiple instances have access to the data). You must be running the Oracle Parallel Server option to make
full use of the Parallel option.
Command type: SQL*Plus
Line mode command: ALTER DATABASE MOUNT
Required privileges: Capability to connect as INTERNAL
The Open Database option enables you to bring the database from a mounted state to the open state. Even though the database might be mounted, the data is not accessible until it is in the open state.
Command type: SQL*Plus
Line mode command: ALTER DATABASE OPEN
Required privileges: Capability to connect as INTERNAL
You use Force Checkpoint to checkpoint either the local instance or all of the instances in your environment. If you are not running the Parallel Server option, both options work the same.
Command type: SQL*Plus
Line mode command: ALTER SYSTEM CHECKPOINT
Required privileges: ALTER SYSTEM
The Force Log Switch option forces the current instance to switch to the next available log file in the thread. Once the database has made the switch, the previous log group becomes available for archiving or maintenance.
Command type: SQL*Plus
Line mode command: ALTER SYSTEM SWITCH LOGFILE
Required privileges: ALTER SYSTEM
The next two commands are for Oracle's Multi-Threaded Server configuration (MTS).
Configure Dispatcher enables you to configure the number and type of running dispatchers. You can either add or remove dispatchers of various protocols to your system. This command effectively modifies the mts_servers parameter for the duration of the
instance. It does not reset the MTS parameters in the INIT.ORA file, nor can it go beyond the value of mts_max_servers specified at startup (in the INIT.ORA file).
Command type: SQL*Plus
Line mode command: ALTER SYSTEM SET MTS_DISPATCHERS
Required privileges: ALTER SYSTEM
To configure the number of shared servers currently running, you can use the Configure Shared Server option. The dialog box that appears asks you how many shared server processes to run at one time, which must be in the range of mts_servers to
mts_max_servers (found in your INIT.ORA file). You cannot go below or above these values.
Command type: SQL*Plus
Line mode command: ALTER SYSTEM SET MTS_SERVERS
Required privileges: ALTER SYSTEM
Prevent Connection enables you to limit connections to the database to only those users that have been granted the RESTRICTED SESSION privilege. Users without this privilege receive an error message stating that only specific users are permitted access.
Command type: SQL*Plus
Line mode command: ALTER SYSTEM ENABLE RESTRICTED SESSION
Required privileges: ALTER SYSTEM
Allow Connection enables all authorized users to gain access to the system again. These two commands are extremely helpful when you're doing database maintenance that requires the database to be online but unused.
Command type: SQL*Plus
Line mode command: ALTER SYSTEM DISABLE RESTRICTED SESSION
Required privileges: ALTER SYSTEM
When you choose the Kill Session option, a dialog box appears listing all the current users, their session number, and their serial number. The combination of these two numbers provides a unique identification based upon a single user session, which you
can use to monitor or kill the session.
Command type: SQL*Plus
Line mode command: ALTER SYSTEM KILL SESSION
Required privileges: ALTER SYSTEM
The Storage menu enables you to manipulate all aspects of a tablespace or rollback segment, including creating and dropping them and choosing default storage options. This could be one of the most useful menus for database management.
Figure 7.6 displays the Tablespace drop-down menu under the Storage menu. The Tablespace option enables you to Create, Drop, Set Online, Set Offline, Add a Data File, Rename a Data File, and Modify Default Storage of database tablespaces.
Figure 7.6. Tablespace drop-down menu.
Tablespace Create creates a tablespace with the name you specify.
Command type: SQL*Plus
Line mode command: CREATE TABLESPACE
Required privileges: CREATE TABLESPACE
Tablespace Drop drops the tablespace. If the tablespace is not empty (it contains tables or objects), you have to use the INCLUDING CONTENTS option.
Command type: SQL*Plus
Line mode command: DROP TABLESPACE
Required privileges: DROP TABLESPACE
Set Online sets a tablespace online. This is the default, but if the instance starts up with errors or a tablespace requires recovery, the tablespace is offline.
Command type: SQL*Plus
Line mode command: ALTER TABLESPACE tablespace ONLINE
Required privileges: ALTER TABLESPACE
Set Offline takes a tablespace offline. You use this primarily for database maintenance. There are three options in taking a tablespace offline:
Add Data File is useful for adding additional data to a tablespace that has grown beyond its first data file. Although it is better to have a single data file at the size you need, multiple data files are a short-term solution.
Command type: SQL*Plus
Line mode command: ALTER TABLESPACE tablespace ADD DATAFILE
Required privileges: ALTER TABLESPACE
You use Rename Data File for relocating data files from one disk to another. This is extremely useful in tuning your database. For example, when you have several tablespaces that are active and reside on the same disk, you should move them apart to
alleviate disk contention. It is important to note that this command does not rename or move the file. It only replaces the old name with the new name you specify. You must first take the tablespace offline and then use your operating system commands to
relocate and rename the data file. Once you have done that, you can issue the database commands to rename the data file.
Command type: SQL*Plus
Line mode command: ALTER TABLESPACE tablespace RENAME FILE
Required privileges: ALTER TABLESPACE and operating system privileges
Alter Default Storage modifies the storage options for the tablespace. These options include Percent Free, Percent Used, Initial Extents, and so on. If you create a table within a tablespace but do not give it any storage options, the new table gets the
default storage options given to the tablespace.
Command type: SQL*Plus
Line mode command: ALTER TABLESPACE tablespace STORAGE
Required privileges: ALTER TABLESPACE
The Rollback Segment drop-down menu, shown in Figure 7.7, enables you to manage the creation, deletion, and basic utilization of your rollback segments. The drop-down menu has the following options: Create, Drop, Set Online, Set Offline, and Alter
Storage.
Figure 7.7. Rollback drop-down menu.
Create enables you to specify a rollback segment name, tablespace name, and storage options. You can create rollback segments as Public and Private. Public rollback segments are available to all instances sharing the database (Parallel Server option),
and a Private rollback segment is used strictly for one instance.
Command type: SQL*Plus
Line mode command: CREATE ROLLBACK SEGMENT
Required privileges: CREATE ROLLBACK SEGMENT
The Drop command deletes an offline rollback segment. If the rollback segment is still online, the operation fails with the following error message:
ORA-1545: rollback segment 'rbs01' specified not available
Command type: SQL*Plus
Line mode command: DROP ROLLBACK SEGMENT
Required privileges: DROP ROLLBACK SEGMENT
Set Online takes an offline rollback segment and makes it available for use in the database. This operation takes effect immediately if the tablespace that the rollback segment resides in is currently usable.
Command type: SQL*Plus
Line mode command: ALTER ROLLBACK SEGMENT segment_name ONLINE
Required privileges: ALTER ROLLBACK SEGMENT
Set Offline enables the DBA to take a rollback segment offline. This is very handy when you must drop and recreate a rollback segment that has reached maximum extents or when you relocate a rollback segment. If there are active transactions in the
rollback segment, the database prevents new transactions from accessing the segment and waits until the current transactions are committed or rolled back before taking it offline.
Command type: SQL*Plus
Line mode command: ALTER ROLLBACK SEGMENT segment_name OFFLINE
Required privileges: ALTER ROLLBACK SEGMENT
Alter Storage alters the default storage options of the rollback segment. You use this for next extents because you cannot change the initial extent once the rollback segment is created. In order to change the initial extent, you must drop the rollback
segment and recreate it with the correct value.
Command type: SQL*Plus
Line mode command: ALTER ROLLBACK SEGMENT segment_name STORAGE
Required privileges: ALTER ROLLBACK SEGMENT
Figure 7.8 shows the Log menu. These log options control all aspects of the redo log files. Log files are a critical part of the online recovery method and are extremely important if you are running in ARCHIVELOG mode. In all databases, they can be a
source of disk contention if your database is very active. If a redo log file is corrupted or lost and you are running in ARCHIVELOG mode, you cannot completely restore your database!
A group is a set of mirrored redo log files that consists of one or more actual files. If you are using some form of disk mirroring or RAID technology, it is unnecessary to have more than one file per group because your system automatically employs a
recovery method if you lose a disk where the redo logs reside.
The Add and Drop Group options on the Log menu enable you to add or remove groups from your currently running database. This is extremely helpful if you find that you are filling log files faster than they are written to disk.
The Add Group command enables you to add a group to your currently running database.
Command type: SQL*Plus
Line mode command: ALTER DATABASE ADD LOGFILE GROUP
Required privileges: ALTER DATABASE
The Drop Group command enables you to drop an entire log file group from the database. This does not remove the files, however. The files can be reused (specifying REUSE instead of SIZE 50K in a creation statement) or deleted through your operating
system commands if the files should be deleted. The Drop Group option is useful in helping to eliminate disk contention as well as increasing the number of redo log files available for a database.
Command type: SQL*Plus
Line mode command: ALTER DATABASE DROP LOGFILE GROUP
Required privileges: ALTER DATABASE
Each redo log file of a group is considered a member, and for consistency within each group, there should be an equal number of members. This is not a requirement but a suggestion. If you are running a mirrored or RAID system, it is unnecessary to have
more than one member per group because the system is handling the mirroring. Adding or removing members is helpful in defining a high availability system.
To use the Add Member option, you need to decide on the name of the file and which group you want to add it to.
Command type: SQL*Plus
Line mode command: ALTER DATABASE ADD LOGFILE MEMBER
Required privileges: ALTER DATABASE
The RENAME MEMBER option is really just the RENAME DATA FILE command in disguise. As with the RENAME DATA FILE command, RENAME MEMBER only associates the name of the redo log member with a new file name inside the database. The member must be offline,
and before you issue the RENAME MEMBER command, you must use your operating system commands to relocate or rename the data files in question.
Command type: SQL*Plus
Line mode command: ALTER DATABASE RENAME FILE
Required privileges: ALTER DATABASE
Using the Drop Member option is helpful if you are reconfiguring your system to use mirrored disks and want to reclaim disk space or if you are resizing your group files. The group the member is in must be offline before you can drop it.
Command type: SQL*Plus
Line mode command: ALTER DATABASE DROP LOGFILE MEMBER
Required privileges: ALTER DATABASE
A thread is a collection of two or more redo log groups. Together, these groups form a sequence of log files that the database rotates through. When one log file or group is filled, the database switches to the next group. When it reaches the last group
in the thread, it cycles back to the first.
A single database that is not running in Parallel Server mode has only one thread. If a database is running in parallel, there should be one thread for each instance accessing the database. Public and Private denote the type of access for a given
thread. Private means the thread is reserved for a specific instance, and Public means the thread is available for any instance to reserve at startup, if the instance does not specifically request a certain thread.
Using the Enable Thread option makes a thread available for the next instance that requests it.
Command type: SQL*Plus
Line mode command: ALTER DATABASE ENABLE THREAD
Required privileges: ALTER DATABASE
You use the Disable Thread option to take a thread offline for maintenance or to make it available for another instance. If an instance has the thread in question mounted, the command fails.
Command type: SQL*Plus
Line mode command: ALTER DATABASE DISABLE THREAD
Required privileges: ALTER DATABASE
The Start, Stop, Begin, and List Archive options refer directly to archiving redo logs to disk. Automatic archiving enables the database to write the contents of a filled (or manually switched) log file to disk and free up the redo log for use.
Start Auto Archive tells the system to automatically write data from the redo logs to disk when it's finished with the redo log. You must specify a location and the beginning part of the filename. You can do this either in the INIT file for the database
or on the command line when you start Auto Archive mode.
Command type: SQL*Plus
Line mode command: ALTER SYSTEM ARCHIVE LOG START
Required privileges: ALTER SYSTEM
Stop Auto Archive disables the system from writing filled log files to disk. If you are extremely low on disk space or you're monitoring certain aspects of the database, this option can be helpful while you relocate files.
Command type: SQL*Plus
Line mode command: ALTER SYSTEM ARCHIVE LOG STOP
Required privileges: ALTER SYSTEM
Use Begin Manual Archive if you are not running in automatic archive log mode and you want to start writing redo logs to disk. The system can write redo logs according to age, change number, group and thread numbers, or all of these designations.
Command type: SQL*Plus
Line mode command: ALTER SYSTEM ARCHIVE LOG
Required privileges: ALTER SYSTEM
The List Archive Status command lists the status of archiving for the current instance. Use this command to verify the status of the archive mode as well as find out current sequence numbers and the location that the archived redo logs are written to.
Command type: SQL*Plus
Line mode command: ARCHIVE LOG LIST
Required privileges: Capability to connect as INTERNAL, OSOPER, or OSDBA
Figure 7.9 shows the Backup menu for SQL*DBA. The options on this menu include Begin Online Tablespace Backup, End Online Tablespace Backup, Recover Database, Recover Tablespace, and Recover Data File.
You use the backup options when you are running in ARCHIVELOG mode and you want to begin a "hot backup" of your data files.
To begin a tablespace backup, you must know the tablespace name. Once you issue the Begin Backup command, you can safely use your operating system commands to back up the data files that make up the tablespace.
Command type: SQL*Plus
Line mode command: ALTER TABLESPACE tablespace BEGIN BACKUP
Required privileges: ALTER TABLESPACE
Use the End Online Tablespace Backup option when you are finished with the backup of the tablespace.
Command type: SQL*Plus
Line mode command: ALTER TABLESPACE tablespace END BACKUP
Required privileges: ALTER TABLESPACE
You use the Recover options to bring the database back to a usable state after some sort of database or system failure. Each option deals with a specific type of recovery. If you're running in ARCHIVELOG mode and all the archived redo log files needed
for recovery are available, you can recover the database to its final state before the crash. Other recovery options provide recovery to the point where it is canceled by the operator, to a certain date and time specified by the operator, or to a specific
change number. These options apply only to database and tablespace recovery. When you attempt to start an instance and recovery is required, the instance notifies you about which type of recovery is necessary.
You use Recover Database to recover the entire database. For example, you can restore all the data files and archived redo logs from tape after a serious disk failure. Once the data files and redo logs are recovered from media, you must use the recover
commands to bring the database into a usable state.
Command type: SQL*DBA
Line mode command: RECOVER DATABASE
Required privileges: ALTER DATABASE
You can use Recover Tablespace if you have a single disk failure and must restore all a tablespace's data files from backup. Once the files are restored to their original locations, you can issue the recover command to have the database apply redo log
information to bring the tablespace online. This recovery can take place while the database is online, but all tables in the given tablespaces are unavailable for use until recovery is complete.
Command type: SQL*DBA
Line mode command: RECOVER TABLESPACE
Required privileges: ALTER DATABASE
You must use Recover Data File if you have accidentally overwritten or erased a data file or if you've had a form of media failure that does not affect all the data files in a particular tablespace. Once the data file is restored, you can issue the
RESTORE DATAFILE command and then bring the tablespace back online.
Command type: SQL*DBA
Line mode command: RECOVER DATAFILE
Required privileges: ALTER DATABASE
You use the Security menu to define how and to whom you grant access to the database and its data. Through your work with user accounts, you can audit and limit data access and resource utilization. Profiles limit the amount of resources that are
available to certain individuals. Roles enable you to manage table and object access by creating groups (roles), granting access to those groups, and then giving access to the group to specific users. Figure 7.10 shows the Security menu.
You can use the Create, Alter, and Drop User options to speed the creation or deletion of a user in the database. From these menus, you have the capability to manipulate all aspects of a user's definition.
Create User creates a database user and specifies all the parameters needed to begin working in that account. Specific information includes the user name, type of authentication, default and temporary tablespaces, quota limit, and profile (if any).
Command type: SQL*Plus
Line mode command: CREATE USER
Required privileges: CREATE USER
Alter User is nearly identical to Create User except for the Roles option. From here, you can define a default role for the user you are modifying. Use this option to change a user's characteristics.
Command type: SQL*Plus
Line mode command: ALTER USER
Required privileges: ALTER USER
You use Drop User to remove a user and possibly all tables owned by that user from the database. In order to remove a user, you must drop all the objects owned by that user either manually or with the CASCADE option.
Command type: SQL*Plus
Line mode command: DROP USER
Required privileges: DROP USER
You use the Create, Alter, and Drop Role options to define a role and how it is authenticated. You must use the grant options from the Security menu to grant table access to role names or to grant roles to users.
Command type: SQL*Plus
Line mode command: CREATE ROLE
Required privileges: CREATE ROLE
You use Alter Role to change the type of authentication for a given role. You can create roles to require passwords before a user is granted the role's access definitions.
Command type: SQL*Plus
Line mode command: ALTER ROLE
Required privileges: ALTER ANY ROLE
Using Drop Role removes the role name and all references to that role from the database. Any users who had this role granted to them will effectively have that role revoked.
Command type: SQL*Plus
Line mode command: DROP ROLE
Required privileges: DROP ANY ROLE
Profiles enable you to limit the use of specific system and database resources. There are nine limitations you can set, and a user can belong to only one profile. If a user is not defined with a profile, there are no limitations to the amount of
resources available to that user. This is the default.
CREATE PROFILE creates a profile with the name and limits you specify. For resource limits not specified in the creation of the profile, the database uses its own default value, which is usually unlimited unless you change resource costs. You can modify
resource costs from the Security menu by using the option Alter Resource Cost.
Command type: SQL*Plus
Line mode command: CREATE PROFILE
Required privileges: CREATE PROFILE
Alter Profile works in the same way as Create Profile, but you are given a list of the values for existing profiles. You must first select an existing profile before you can modify it.
Command type: SQL*Plus
Line mode command: ALTER PROFILE
Required privileges: ALTER PROFILE
You use Drop Profile to remove all traces of the profile from the database. Unlike with dropping roles, you must specify the CASCADE option if a user has been assigned the profile you want to drop.
Command type: SQL*Plus
Line mode command: DROP PROFILE
Required privileges: DROP PROFILE
For each resource listed in a profile, there is a resource cost associated with it. Oracle uses a formula to calculate each resource's cost in order to limit usage as defined in the profile. There are four resources that the DBA can modify: CPU
Time/Session, Connect Time, Logical Reads/Session, and Private SGA/Session. (Private SGA is for instances running in Multi-Threaded Server mode.) The default value for each resource is 0. These costs are actually weights. If a site decides that CPU should
be weighted higher in resources than disk I/O, you can start the menu or enter the command from the command line and enter a higher value (5 for instance), which would weight CPU usage higher than disk I/O in resource cost. This is helpful for systems with
slower CPUs or possibly slower disk access. By limiting resource usage, you can gain greater control over how the system is used.
Command type: SQL*Plus
Line mode command: ALTER RESOURCE COST
Required privileges: ALTER RESOURCE COST
You use Grant and Revoke to limit access to data within the database. You can grant a role to users and grant access to objects to roles.
Command type: SQL*Plus
Line mode command: GRANT
Required privileges: GRANT ANY ROLE or ADMIN access to the role being granted.
Revoke removes access to the specified role or object.
Command type: SQL*Plus
Line mode command: REVOKE
Required privileges: GRANT ANY ROLE or ADMIN access to the role being revoked.
The Monitor menu, which is shown in Figure 7.11, enables the developer or DBA (based on database privileges) to monitor various aspects of the database, such as aspects of the Multi-Threaded Server, sessions connected to the database, table access, SQL
statements cached in the SGA, locks, file I/O, and so on. The Monitor option is not available in any other package supplied by Oracle (with the exception of Server Manager, Oracle's X-Windows replacement for SQL*DBA).
The Multi-Threaded option monitors shared servers, dispatchers, circuits, and queues. The options help you correctly configure the number of shared servers and dispatchers required for your environment. Too many shared servers and you are using excess
memory; too few and users begin to see unnecessary wait times.
Monitoring the shared servers enables you to filter the monitor output based on server name or the status. Keep a close eye on status, total idle time, and load. If load is high and idle time is high, you might experience system bottleneck (CPU or
memory).
Command type: SQL*DBA
Line mode command: MONITOR SHARED
Required privileges: Access to V$SHARED_SERVER
When monitoring dispatchers, you get up-to-date information on how each dispatcher is running. Keep an eye out for load and status. If the load is too high, the dispatcher might be having trouble keeping up with the requests it has to send out to the
servers. Adding more dispatchers might alleviate the problem.
Command type: SQL*DBA
Line mode command: MONITOR DISPATCHER
Required privileges: Access to V$DISPATCHER
Monitoring circuits shows the virtual circuits that are related to the shared servers. These circuits are the means by which users are connected to the database. This screen links a user back to a shared server and dispatcher.
Command type: SQL*DBA
Line mode command: MONITOR CIRCUIT
Required privileges: Access to V$CIRCUIT, V$DISPATCHER, V$SESSION, V$SHARED_SERVER
Monitoring queues gives you a look at the queue of requests that are handled by the Multi-Threaded Server. If the average wait time is high, you might need to add more dispatchers.
Command type: SQL*DBA
Line mode command: MONITOR QUEUE
Required privileges: Access to V$QUEUE
Choosing the Process option gives you valuable information on the user and instance processes currently running. One of the key portions of this screen is Latch Waited. If a user is waiting on a latch, you could be having resource problems.
Command type: SQL*DBA
Line mode command: MONITOR PROCESS
Required privileges: Access to V$PROCESS
The Session monitor gives you information on each session connected to the database. Key points of the session output are session ID and process ID (used for kill session commands) and lock waited. If a user is waiting on a lock, his session appears to
hang.
Command type: SQL*DBA
Line mode command: MONITOR SESSION
Required privileges: Access to V$PROCESS, V$SESSION
If you choose to monitor a Table, you can find out the level of I/O that each table incurs. This is extremely important in alleviating disk bottlenecks. If possible, you should move I/O intensive tables to separate disks from each other.
Command type: SQL*DBA
Line mode command: MONITOR TABLE
Required privileges: Access to V$ACCESS
Monitoring the SQL Area helps you tune your custom applications. Because SQL is case sensitive, two identical SQL statements that differ only by a single uppercase or lowercase character are parsed and cached separately.
Command type: SQL*DBA
Line mode command: MONITOR SQLAREA
Required privileges: Access to V$SQLAREA
Library Cache monitoring is important because it can tell you how well your SGA is sized. It is important to keep an eye on the name space and the hit ratios associated with them. If the hit ratios are low, the database is forced to reparse and recache
SQL statements back into the SGA.
Command type: SQL*DBA
Line mode command: MONITOR LCACHE
Required privileges: Access to V$LIBRARYCACHE
Latches are similar to locks, but their life spans are very short. The Latch option enables you to monitor the types of latches and their statistics.
Command type: SQL*DBA
Line mode command: MONITOR LATCH
Required privileges: Access to V$LATCH, V$LATCHHOLDER, V$LATCHNAME
Locking problems can be extremely difficult to resolve, especially in custom applications. Using the Lock monitor shows you who has locks and who is requesting them. If a process has Mode Requested listed on the right-hand side for a long period of
time, you might be experiencing locking problems. It means that the user is requesting some sort of lock on the object, but the object is locked by another user. It is not uncommon for users to wait on locks, but depending on the wait time, it might
warrant further investigation.
Command type: SQL*DBA
Line mode command: MONITOR LOCK
Required privileges: Access to V$LOCK, V$PROCESS, V$SESSION
File I/O monitoring can be helpful in finding data files that have high I/O rates associated with them. Knowing which tablespaces and data files have the highest access rates is very helpful in eliminating disk contention.
Command type: SQL*DBA
Line mode command: MONITOR FILEIO
Required privileges: Access to V$DBFILE, V$FILESTAT
If the database is running abnormally slow, it is likely that a user is running a large, I/O-intensive query. Monitoring System I/O is extremely useful in identifying users or processes with high I/O rates.
Command type: SQL*DBA
Line mode command: MONITOR SYSTEMIO
Required privileges: Access to V$PROCESS, V$SESSION, V$SESSTAT
You should monitor Rollback segments to help reduce rollback segment contention. It is also important to notice the number of active transactions in each rollback segment. There is a database parameter that defines how many active transactions are
allowed per rollback segment. If this limit is reached, an error can result. It is also important to see how the rollback segment is extending and shrinking and how many extents each segment has.
Command type: SQL*DBA
Line mode command: MONITOR ROLLBACK
Required privileges: Access to V$ROLLNAME, V$ROLLSTAT
Session and system statistics are gathered by session ID and overall database statistics. You can do each type of monitoring on a combination of user, redo, enqueue, cache, parallel server, and SQL statistics.
Session statistics are based on each session connected to the database.
Command type: SQL*DBA
Line mode command: MONITOR SESSIONSTATISTIC
Required privileges: Access to V$SESSTAT, V$SYSSTAT
System statistics are based on the current instance.
Command type: SQL*DBA
Line mode command: MONITOR SYSTEMSTATISTIC
Required privileges: Access to V$SYSSTAT
The Help menu is designed to give the user brief help on the commands and topics listed within the menu. Intended to be a brief introduction to the topics, the menu refers you to various Oracle manuals for further assistance. Figure 7.12 shows the Help
menu.
The About SQL*DBA option gives you information on version numbers of some of the products associated with your current version of the Oracle instance. There is no command associated with this option.
Show Keys gives you a menu of keys and their functions.
Command type: SQL*DBA
Line mode command: HELP
Required privileges: None
The following list contains SQL*DBA specific commands and their syntax. For more information on SQL*Plus specific commands, refer to Chapter 6, "SQL*Plus." The following commands are listed in alphabetical order.
Description: The ARCHIVE LOG command controls the starting and stopping of the automatic archiving of used redo logs and displays the current status of archive logging.
Command syntax:
ARCHIVE LOG LIST STOP START TO filename NEXT TO filename ALL TO filename integer TO filename
Keywords:
LIST |
Displays the current status of archive logging. The output includes log mode, automatic archival, the archive destination, the oldest online log sequence, the next sequence to archive, and the current sequence number. If the oldest online log sequence and the current log sequence numbers are different, either automatic archiving has been disabled or the previous log files have not been archived yet. |
START |
Enables automatic archiving, which is controlled by the ARCH process. |
STOP |
Disables automatic archiving. |
NEXT |
Manually archives the next redo groups that have not been archived. |
ALL |
Archives all redo groups that have not been archived. |
integer |
Archives the log file with the sequence number integer stored in it. If the sequence number is invalid, an error is given. |
filename |
Refers to the instance specific destination file or device for the log files to be written to. |
Examples:
ARCHIVE LOG LIST; ARCHIVE LOG START; ARCHIVE LOG 10982 '/u01/app/oracle/admin/dev/arch/dev'
Description: You use the CONNECT command to connect to the database. Most SQL*DBA functions require that the user be connected before using them.
Command syntax:
CONNECT username/password CONNECT username/password@instance CONNECT INTERNAL
Keywords:
username |
The user ID to connect to. It must be a valid account. |
password |
The password the account is identified with. |
instance |
The instance name or connect string of the destination instance. This can be either the name of a database link, an alias created in SQL*Net Version 2, or the direct connect string specifying driver:system:instance_name. |
INTERNAL |
A privileged login used for many higher level commands such as startup and shutdown. This login is an alias for SYS. |
Example:
CONNECT INTERNAL; CONNECT SYSTEM/MANAGER; CONNECT SCOTT/TIGER;
Description: You use the DISCONNECT command to disconnect from the current instance without exiting SQL*DBA. It has no additional parameters. You can use this command in conjunction with SET INSTANCE to access multiple instances at your site without
exiting and reentering SQL*DBA.
Command syntax:
DISCONNECT
Example:
DISCONNECT;
Description: You use EXECUTE to execute a one-line PL/SQL statement. If you want to execute more than one line, you must use the BEGIN . . . END format for PL/SQL. You must also be connected to a database before executing.
Command syntax:
EXECUTE PL/SQL statement
Example:
EXECUTE total_orders;
Description: EXIT is the command you use to exit the SQL*DBA session. It automatically disconnects you from the current database if a connection has been established. This command has no parameters or keywords.
Command syntax:
EXIT
Example:
EXIT;
Description: The HOST command executes an operating system command or program while you're still in SQL*DBA. This command shells you out of SQL*DBA for the duration of the command. If issued by itself, it shells you to the operating system until you
type EXIT to return to SQL*DBA.
Command syntax:
HOST operating system command HOST
Keywords:
operating system command A valid operating system command or program.
Examples:
HOST who; HOST dir; HOST;
Description: The MONITOR command enables you to monitor various statistics and attributes of the database, processes, or users. This command is extremely helpful in analyzing and resolving database problems. You can specify any parameters that the menu
requests in order on the command line.
Command syntax:
MONITOR CIRCUIT DISPATCHER FILEIO LATCH LCACHE LOCK PROCESS QUEUE ROLLBACK SESSION SESSIONSTATISTIC SHARED SQLAREA SYSTEMIO SYSTEMSTATISTIC TABLE
Keywords:
CIRCUIT |
Displays current information on the virtual circuits owned by each shared server in a Multi-Threaded Server environment. |
DISPATCHER |
Displays current information about a shared server's dispatcher processes in a Multi-Threaded Server environment. |
FILEIO |
Displays read/write information for every database file associated with the current instance. |
LATCH |
Displays information on all current latches. |
LCACHE |
Displays current information on the library cache. |
LOCK |
Lists the current processes and the locks they are waiting on. Using ALL lists all locks being held by current processes. |
PROCESS |
Monitors summary information for every process connected to the current instance. |
QUEUE |
Lists information on each shared server's message queues. |
ROLLBACK |
Shows activity on every active rollback segment in the instance. |
SESSION |
Displays active process information. |
SESSIONSTATISTIC |
Shows user session statistics for current user processes. |
SHARED |
Monitors shared server activity. |
SQLAREA |
Gives various statistics on the shared SQL area. |
SYSTEMIO |
Summarizes the read/write statistics for each Oracle process. This information is not precise but instead is representative of relative distribution of I/O. |
SYSTEMSTATISTIC |
Shows system statistics for the current database. |
TABLE |
Displays table names of tables referenced in SQL statements that have been recently parsed and reside in the shared SQL area. |
Examples:
MONITOR PROCESS; MONITOR LATCH; MONITOR LOCK 10 20; MONITOR SYSTEMSTATISTIC;
Description: The PRINT command prints the value of a variable that you defined using the SQL*Plus command VARIABLE.
Command syntax:
PRINT variable
Keywords:
variable The name of the variable defined with the VARIABLE command.
Examples:
PRINT COUNTER; PRINT NAME;
Description: You use the RECOVER command to perform media recovery on data files, tablespaces, or entire databases as required. You must be connected as INTERNAL to use this command, and you must have a dedicated process. You cannot be connected through
Oracle's Multi-Threaded Server.
Command syntax:
RECOVER DATABASE RECOVER DATABASE UNTIL CANCEL CHANGE integer TIME date RECOVER DATABASE USING BACKUP CONTROLFILE RECOVER TABLESPACE tablespace RECOVER DATAFILE filename
Keywords:
DATABASE |
Requests the recovery of an entire database. Will apply redo log files to all tablespaces needing media recovery. |
UNTIL TIME date |
Used to specify an incomplete RECOVER to a specific time. You must specify the date in the following format: |
'YYYY-MM-DD:HH24:MI:SS' | |
YYYY is a four-digit year. | |
MM is a two-digit month. | |
HH24 is the time in 24-hour specification. | |
MI is minutes. | |
SS is seconds. | |
UNTIL CHANGE integer |
Used to recover until a specific change number. This is very useful in restoring a tablespace where a table was accidentally dropped. integer must be a valid change number, and the redo logs must be available to Oracle. |
UNTIL CANCEL |
Specifies recovery should continue applying redo logs until the operator cancels the operation. Recovery continues redo log by redo log until canceled. |
USING BACKUP CONTROLFILE |
Tells the database to use a backup version of the control file instead of the primary one. This control file must be available to Oracle, or the command will fail. |
TABLESPACE tablespace |
Recovers the specified tablespace, or tablespaces. You can recover up to 16 of them in a single statement. |
DATAFILE filename |
Specifies a particular data file belonging to a tablespace that you want to restore. There is no limit to the number of data files you can recover in a given statement. |
Examples:
RECOVER TABLESPACE tools; RECOVER DATABASE; RECOVER DATAFILE 'users_01.dbf'; RECOVER DATABASE UNTIL '1994-10-11:15:01:00'; RECOVER TABLESPACE tools, users;
Description: The SET command sets characteristics for the current SQL*DBA session. These characteristics are not saved for future sessions.
Command syntax:
SET ARRAYSIZE integer AUTORECOVERY ON/OFF CHARWIDTH integer COMPATIBILITY V6/V7 CYCLE integer DATEWIDTH integer ECHO ON/OFF FETCHROWS integer HISTORY integer INSTANCE instance-path/LOCAL LABWIDTH LINES integer LOGSOURCE pathname/DEFAULT LONGWIDTH integer MAXDATA integer NUMWIDTH integer RETRIES integer/INFINITE SERVER OUTPUT OFF/ON SIZE integer SPOOL filename/OFF STOPONERROR ON/OFF TERM PAGE/NOPAGE TERMOUT ON/OFF TIMING ON/OFF
Keywords:
ARRAYSIZE integer |
Indicates the number of rows that are fetched from the database at one time. The default is 20, and the maximum is specific to the operating system you are running. |
AUTORECOVERY |
Tells the database to automatically apply all redo logs necessary to bring the database, tablespace, or data file to a usable state. When this is ON, the database begins recovery without requesting input from the operator. Log filenames are derived from the database parameters LOG_ARCHIVE_DEST and LOG_ARCHIVE_FORMAT. If the files cannot be located, operator input is requested. |
CHARWIDTH integer |
Defines the column width displayed for columns of type CHAR. The default is 80, and if no integer is specified, the parameter is reset to 80. |
COMPATIBILITY V6/V7 |
Sets the SQL*DBA compatibility mode to either Version 6 or Version 7. This parameter affects how you specify columns of type CHAR, integrity constraint definitions, and the storage parameters for rollback segments. |
CYCLE integer |
Used for the MONITOR command, sets the time that the monitor screens cycle in gathering statistics. The default is 5 seconds. The minimum is 1 second; maximum is 3600 seconds. The smaller the number, the higher the impact on the database. |
DATEWIDTH integer |
Sets the width for DATE data to be displayed. The default is 9, and if entered with no integer, it is reset to 9. The range of values for this parameter is operating-system specific. |
ECHO ON/OFF |
Enables echoing of commands that are executed from command files. The default is OFF. In this mode, only the output is displayed. |
FETCHROWS integer |
This parameter limits the number of rows that are returned by a database query. It can be very useful in returning only the first 20 or 10 rows from a database table. The default returns all rows that match the given criteria, and as with all other parameters, entering the SET command without an integer resets the value to all. |
HISTORY integer |
Sets the number of SQL*DBA commands saved in the history buffer. Any commands that reside in this buffer can be recalled and reexecuted using the Previous and Next Command options in menu mode. The default is 10 commands. |
INSTANCE instance-path/LOCAL |
Sets the instance name to where all SQL*DBA commands are applied. The instance-path is defined by a system node name and database name separated with a hyphen. A sample connect string is my_host-my_database. Issuing the command with no database definition resets the instance back to the local default instance. |
LABWIDTH |
Used strictly with Oracle's Trusted Server package. |
LINES integer |
Limits the number of lines the output window of SQL*DBA can store and recall. After reaching the limit, the lines at the beginning of the buffer are erased. The default value is 1000. |
LOGSOURCE pathname/DEFAULT |
Tells Oracle where to find archived redo logs to be used during a recovery session. Use this to set the location to a temporary location where redo logs have been restored. |
LONGWIDTH integer |
Tells SQL*DBA how to display LONG data. By default, the display is 80 characters only. Once again, the operating system defines the range of values for this parameter. |
MAXDATA integer |
Sets the maximum number of bytes that you can fetch from the database in a single SELECT statement. The default is 20,480 bytes (20K). Your operating system defines the maximum number for this parameter. |
NUMWIDTH integer |
Defines the length in characters that data types of NUMBER are displayed in. The default is 10, and the minimum and maximum values are operating-system dependent. |
RETRIES integer/INFINITE |
Used with the STARTUP command, this specifies how many times the startup command attempts to start the database. INFINITE means it tries until it succeeds or is canceled. |
SERVER OUTPUT OFF/ON SIZE integer |
Specifies the size of the message buffer, in bytes, that can accumulate at one time. This message buffer is used by the PUT() and PUT_LINE() commands. |
SPOOL filename/OFF |
Captures commands and output from the current session to a file. Specifying OFF closes the previously opened file. |
STOPONERROR ON/OFF |
Tells SQL*DBA, when executing a command file, to stop if it encounters an error. If it finds one, the rest of the command file is not executed, and it returns control to the operating system. |
TERM PAGE/NOPAGE |
Tells SQL*DBA to display output one page at a time. The default, NOPAGE, scrolls all output to the output window. After that, you can navigate to the output window and scroll through the saved output. |
TERMOUT ON/OFF |
Controls the display of output from SQL commands to the output window. ON enables display of the output whereas OFF disables the output. This is helpful if you're spooling output to files. The output is sent to the spool file but not the terminal. |
TIMING ON/OFF |
Displays the parse, execute, and fetch times for every SQL statement executed. The default is OFF. This option is useful for establishing response times. |
Examples:
SET INSTANCE D:DEV-PROD SET HISTORY 50; SET NUMWIDTH 20;
Description: Using the SHOW command shows the values of all the parameters set by the SET command. Additionally, this command supports several other parameters listed in the Keywords section. For a definition of any parameter listed, refer to the
previous section on SET.
Command Syntax:
SHOW ARRAYSIZE AUTORECOVERY CHARWIDTH COMPATIBILITY CYCLE DATEWIDTH ECHO FETCHROWS HISTORY INSTANCE LABWIDTH LINES LOGSOURCE LONGWIDTH MAXDATA NUMWIDTH RETRIES SERVER OUTPUT SPOOL STOPONERROR TERM TERMOUT TIMING ALL PARAMETERS ERRORS
Keywords:
ALL |
Shows the values of all settings. Does not show ERRORS, PARAMETERS, or SGA, which must be displayed separately. |
ERRORS |
Shows all errors encountered during the last compilation of a function, procedure, or package. Output includes the line, column, and error message generated. |
LABEL |
This is a Trusted Oracle parameter. |
PARAMETERS |
Shows the current values for all database parameters specified in the startup files for the current instance. Used alone, it displays all parameters. Used in conjunction with a parameter name, it shows the specific parameter. If a partial parameter name is used, the output includes all parameters that are similar. |
SGA |
Shows current information on the System Global Area for the connected instance. |
Examples:
SHOW SGA; SHOW TERMOUT; SHOW ALL; SHOW PARAMETERS COUNT; SHOW ERRORS;
Description: You use SHUTDOWN to stop a currently running database. Various options include closing or dismounting the database.
Command syntax:
SHUTDOWN ABORT dbname IMMEDIATE dbname NORMAL dbname
Keywords:
ABORT |
Shuts down the database immediately, without checkpointing the database. This procedure immediately kills all active sessions without rolling back or committing transactions and then closes and dismounts the database. If you use this command, the database will require recovery. |
IMMEDIATE |
Shuts down the database by preventing new connections, terminating all existing sessions, and committing or rolling back current transactions and then checkpoints the database before dismounting and closing it. Media recovery is not required. |
NORMAL |
This is the default. It waits for currently connected users to disconnect, prevents new connections, checkpoints, and then closes and dismounts the database. No media recovery is required. |
dbname |
This is a Trusted Oracle parameter that you should not use for normal or parallel operations. |
Examples:
SHUTDOWN; SHUTDOWN IMMEDIATE dev; SHUTDOWN ABORT;
Description: The SPOOL command begins or ends the spooling of command output to a specified file.
Command syntax:
SPOOL OFF SPOOL filename
Keywords:
OFF |
Closes the currently opened file. |
filename |
The file to spool output to. |
Examples:
SPOOL ON 'kelly.spl'; SPOOL OFF;
Description: You use STARTUP to start a database. With options, this command enables you to bring the database into various stages of use for maintenance. As with the SHUTDOWN command, you must be connected as internal, and you cannot be connected via
Oracle's Multi-Threaded Server.
Command syntax:
STARTUP FORCE RESTRICT PFILE=filename MOUNT NOMOUNT OPEN RECOVER database mount options
Keywords:
FORCE |
Issues a shutdown abort of the current instance and then attempts to start the instance again. This is sometimes required if there were shutdown errors. |
|
RESTRICT |
Same as the ALTER SYSTEM ENABLE RESTRICTED SESSION command; enables the database to start up in restricted mode and will only give access to users with the RESTRICTED SESSION role. |
|
PFILE=filename |
Enables the database to start up with a specific parameter file (INIT.ORA). Very useful if the INIT.ORA file is not in the current directory or if you are starting up a new database. | |
MOUNT |
Mounts the database but does not open it for use. |
|
NOMOUNT |
Does not mount the database. You cannot use this option with the MOUNT, OPEN, PARALLEL, SHARED, or EXCLUSIVE options. |
|
OPEN |
Default option; mounts and opens the default database. |
|
RECOVER |
Similar to the RECOVER DATABASE command; starts the current instance and recovers the database, if required. The recovery works as if AUTORECOVERY were set to ON. If the recovery fails, the database remains mounted but does not open. |
|
database |
Starts this specific instance. If no instance is specified, startup runs on the default instance. |
|
mount options |
Specifies three types of options for the startup: |
|
|
EXCLUSIVE |
Mounts and opens the database for a single instance to use. |
|
PARALLEL |
Mounts and opens the database for parallel mode. |
|
SHARED |
Another name for PARALLEL. |
|
RETRIES |
Specifies the number of retries before failing. |
Examples:
STARTUP; STARTUP MOUNT; STARTUP PFILE='/home/oracle/init.ora' PARALLEL; STARTUP MOUNT RESTRICT; STARTUP FORCE;
SQL*DBA is a powerful tool that is useful for creating, managing, and tuning all your Oracle instances. The flexibility to run in command, line, or menu mode gives the user a definite advantage that many other packages do not provide. The capability to
automate many of the SQL*DBA commands enables the database administrator to turn over some of the database operations to secondary support personnelwhich can help create a higher availability system with less down time.
Overall, this tool is valuable in the day-to-day responsibilities of every database administrator and custom developer.