Oracle Database XE: Utilities and Troubleshooting

In this conclusion to a five-part series on Oracle Database XE administration, you’ll learn how to start and stop the application, troubleshoot it, and use its utilities. This article is excerpted from chapter 28 of the book Beginning PHP and Oracle: From Novice to Professional, written by W. Jason Gilmore and Bob Bryla (Apress; ISBN: 1590597702).

Starting and Stopping Oracle Database XE

When you install Oracle Database XE, one of your options is to start the database automatically when the operating system starts. When you shut down your server, the shutdown process runs scripts to automatically shut down the database as well. Whether you start the database automatically using this method, or start it manually using the menu commands or SQL*Plus, there are situations where you need to stop the database manually without shutting down your server. For example, you may want to move some of your tablespaces from one disk drive to another, or you may want to change some system parameters that can only be changed after you shut down the database.

Note  Under Linux, Oracle starts up automatically when the parameter ORACLE_DBENABLED is set to TRUE in the file /etc/sysconfig/oracle-xe . Under Windows operating systems, the installer sets the autostart option by setting the registry key HKEY_LOCAL_MACHINESOFTWAREORACLEKEY_XE

Starting Oracle Database XE

The manual startup and shutdown procedures are identical on Linux and Windows. You can use SQL*Plus from the command line, the SQL Command GUI equivalent from the Windows or Linux start menu, or the Start Database and Stop Database menu items in the Windows or Linux start menu. The following shows how to start up SQL*Plus from the command line if you did not configure Oracle to start up automatically on your Linux server:

[oracle@phpxe ~]$ sqlplus system as sysdba

SQL*Plus: Release – Production on Fri Jul 14 00:17:53 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Enter password:
Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area   146800640 bytes Fixed Size                   1257668 bytes Variable Size               79695676 bytes Database Buffers            62914560 bytes Redo Buffers                 2932736 bytes Database mounted.
Database opened.

Notice the keywords AS SYSDBA in the SQLPLUS command. Because the database is down, you cannot authenticate your user account using the database. AS SYSDBA authenticates your user account with a password file stored in the Oracle directory structure. The password you supply is the same password you use to connect when the database is up. Oracle automatically keeps the passwords stored in the database in sync with the passwords stored in the password file for user accounts that you grant the SYSDBA privilege. We discuss user privileges, security, and roles in greater detail in Chapter 30.

Note  The password file is located in %ORACLE_HOME%serverdatabasePWDXE.ora on Windows and $ORACLE_HOME/dbs/orapwXE on Linux.

If you are logged into the server using the user account you used to install the Oracle software, you can start up the database by authenticating with the operating system. You need not specify a user account or a password. To use operating system authentication to start up the database, you use the/ keyword as follows:

[oracle@phpxe ~]$ sqlplus / as sysdba

SQL*Plus: Release – Production on Fri Jul 14 00:18:20 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area   146800640 bytes Fixed Size                   1257668 bytes Variable Size             79695676 bytes Database Buffers            62914560 bytes Redo Buffers                 2932736 bytes Database mounted.
Database opened.


Notice that you specify neither a username nor a password, since the authentication takes place when you log into the operating system account that owns the Oracle software.

If you are using Windows XP as your host operating system, you don’t need to see the command line. Click Start ? All Programs ? Oracle Database 10g Express Edition ? Start Database to, as you might expect, start the database. A DOS command window will appear to confirm that you started the database successfully.

Stopping Oracle Database XE

Ideally, you want all users logged off before you shut down the database. If you cannot contact the users that are still logged in and do not have time to disconnect each session manually using the Monitor Sessions function under the Administration icon on the Oracle Database XE home page, you can still shut down the database quickly with the SHUTDOWN IMMEDIATE command. This command performs the following operations:

  1. Prevents any new connections
  2. Prevents any new transactions from starting 
  3. Rolls back any uncommitted transactions 
  4. Immediately disconnects all users and applications

To shut down the database, connect to the database using the command SQLPLUS / AS SYSDBA , and use the SHUTDOWN IMMEDIATE command:

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit

If the SHUTDOWN command does not respond after several minutes, you can force a shutdown. The database may not be responding for a number of reasons, including a background process that is no longer responding, a corrupted datafile, or a network failure. Use the SHUTDOWN ABORT command to force a shutdown:

SQL> shutdown abort
ORACLE instance shut down.

Since the database is in an inconsistent state, Oracle recommends that you start up the database to perform a recovery process, and then shut down the database gracefully using the SHUTDOWN IMMEDIATE command:

SQL> startup
ORACLE instance started.

Total System Global Area   146800640 bytes Fixed Size                   1257668 bytes Variable Size             79695676 bytes Database Buffers            62914560 bytes Redo Buffers                 2932736 bytes Database mounted.
Database opened.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

If you are using Windows XP as your host operating system, you don’t need to see the command line in this case either. Click Start ? All Programs ? Oracle Database 10g Express Edition ? Stop Database to stop the database. A DOS command window will appear to confirm that you stopped the database successfully.

{mospagebreak title=Using Oracle-Supplied Utilities}

You can navigate to all Oracle-supplied tools from the home page. Figure 28-7 shows the first- and second-level navigation hierarchy for the tools. In the following sections, we give you a brief overview of the tools and where we will cover these tools in more depth later in this book.

Figure 28-7.  Oracle Database XE home page tool menu hierarchy


The following are the tools available when you click the Administration icon:

Storage: Monitor the amount of disk storage your applications use. You can have up to 4GB of user data, and up to 1GB of system data, for a total of 5GB. The system data does not include space allocated for temporary and rollback storage.

Memory: Monitor main memory, similar to the Storage tool. The sum of all memory structures allocated for Oracle cannot exceed 1GB.

Database Users: Search, create, modify, and drop user accounts. Grant and revoke privileges and roles for user accounts.

Monitor: Monitor user and system connections as well as disconnect users, system statistics, most frequently executed SQL statements, and SQL statements that have been running for more than six seconds (wall clock time).

About Database: View version information, system settings, language settings, Common Gateway Interface (CGI) environment settings, and system parameters. We provide an overview of system parameters earlier in this chapter in the section “Initialization Parameters.”

Object Browser

The Object Browser icon, as the name implies, makes it easy for you to view the characteristics of all types of database objects: tables, views, indexes, packages, procedures, functions, and so forth. In addition, you can create these objects on the same page.


From the SQL icon, you can run ad hoc queries, manage SQL scripts (more than one SQL command), and build queries with multiple tables using a graphical representation of the tables and their columns.


The Utilities icon accounts for more than half of the tools available in the Web interface. When you click this icon, you see four new icons: Data Load/Unload, Generate DDL, Object Reports, and Recycle Bin.

Data Load/Unload

It’s unlikely that you have a homogeneous environment where all of your data is stored in an Oracle database (and no other brand of database) and no one uses spreadsheets or text files. As a result, you need the capability to import and export data in a variety of formats. On the Data Load/Unload page, you can load data into the database from text files, spreadsheets, or XML documents. To transfer the data in your database to another site that, for example, can only accept text or XML files for import, you can export one or more of your database tables into a number of text formats and XML. We cover importing and exporting Oracle data in more depth in Chapter 39.

Generate DDL

You may have a need to create your tables in another database that is not directly accessible from your database. You can use the Generate DDL icon to export the data-definition language (DDL) commands for one or more object types in a selected schema. You can selectively include or exclude generating the commands to create tables, indexes, functions, views, synonyms, and so forth.

Object Reports

The Object Reports icon facilitates reporting on all types of database objects: tables, PL/SQL components, security objects, and data dictionary views. We dive into views, both user views and data dictionary views, in Chapter 34. The reporting tools available don’t merely list the columns and datatypes of tables; they give you an easy way to identify objects that may need your attention. For example, you can retrieve a list of tables without primary keys and indexes. Tables without primary keys or indexes will most likely cause some kind of performance problem in your database, especially if the table is large or your users access only a small subset of the rows in a table in their queries.

Recycle Bin

The database recycle bin operates much like the Recycle Bin on a Windows or Linux desktop: the object is logically deleted but still resides somewhere on disk if there is enough disk space available to maintain some of the deleted objects. The Recycle Bin icon provides you with the capability to browse and restore dropped database objects or to empty the recycle bin.

Application Builder

As the name implies, the Application Builder makes it easy for your developers to create Web-enabled applications that use the database for the application’s data. Clicking the down arrow next to the Application Builder icon gives you access to three sample applications that cover most of the key features available with Application Builder. The entire Oracle Database XE Web application environment is an Application Builder application.

Note The Application Builder icon shows up for all users except for SYS and SYSTEM , even if the user has been granted system privileges.



{mospagebreak title=Troubleshooting in Oracle}

Most of your database troubleshooting techniques are available at the Oracle Database XE home page, where you can browse and monitor the following:

  1. Database connections
  2. System statistics 
  3. Frequently running SQL 
  4. Long-running SQL

In addition, you can run reports against database tables to see which of those tables may be performing poorly because they either don’t have enough indexes, have too many indexes, or don’t even have a primary key.

One useful tool in the administrator’s toolbox is not available using the Web interface: the database’s alert log. The alert log file is a text file containing messages about the state of the database. It contains entries about significant database events, such as database startup and shutdown information, nondefault initialization parameters, and other error conditions ranging from warning messages to fatal errors that cause a database failure. In addition, all ALTER SYSTEM commands appear in the alert log.

The alert log is a good place to look in situations where your database does not start or suddenly fails. Other log files known as trace files in the same directory as the alert log may also help determine the issue when the database is having problems.

The location and name of the alert log differ on the Windows and Linux platforms. On Windows the alert log file is stored in %ORACLE_BASE%adminXEbdumpalert_xe.log ; on Linux it is in $ORACLE_BASE/admin/XE/bdump/alert_XE.log .

Here is a sample of the alert log in a Windows environment:

Dump file d:oraclexeapporacleadminxebdumpalert_xe.log Wed Jul 12 20:37:34 2006
ORACLE V10. – Production vsnsta=0 vsnsql=14 vsnxtr=3
Windows XP Version V5.1 Service Pack 2
CPU                 : 2 – type 586
Process Affinity    : 0×00000000
Memory (Avail/Total): Ph:3144M/3583M, Ph+PgF:6753M/7002M, VA:1945M/2047M
Wed Jul 12 20:37:34 2006
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on. IMODE=BR
ILAT =10
SYS auditing is disabled
ksdpec: called for event 13740 prior to event group initialization
Starting up ORACLE RDBMS Version:

System parameters with non-default values:
  sessions                 = 49 
  __shared_pool_size       = 201326592
  __large_pool_size        = 8388608
  __java_pool_size         = 4194304
  __streams_pool_size      = 0
  spfile                   =
  sga_target               = 805306368 
  control_files            = D:ORACLEXEORADATAXECONTROL.DBF
  __db_cache_size          = 587202560 
  compatible               =
  db_recovery_file_dest    = D:OracleXEapporacleflash_recovery_area
  db_recovery_file_dest_size= 10737418240
  undo_management          = AUTO
  undo_tablespace          = UNDO
  remote_login_passwordfile= EXCLUSIVE
  dispatchers              = (PROTOCOL=TCP) (SERVICE=XEXDB)
  shared_servers           = 4
  job_queue_processes      = 4
  audit_file_dest          = D:ORACLEXEAPPORACLEADMINXEADUMP
  background_dump_dest     = D:ORACLEXEAPPORACLEADMINXEBDUMP
  user_dump_dest           = D:ORACLEXEAPPORACLEADMINXEUDUMP
  core_dump_dest           = D:ORACLEXEAPPORACLEADMINXECDUMP
  db_name                  = XE
  open_cursors             = 300 
  os_authent_prefix        = 
  pga_aggregate_target     = 268435456

Even though the alert log file grows slowly, it does grow without limit, so you should rename the log file on a weekly or monthly basis to make it easy to browse previous alert log entries. After you rename the alert log file, Oracle automatically creates a new alert log file the next time it needs to record an event or error message.


Oracle Database XE, with its intuitive Web interface, makes it even easier to be a developer. The same can be said for those times when you must wear your DBA hat; most, if not all, functions you need to perform as a DBA are available within the Web interface.

In this chapter, we gave you a high-level look at the Oracle database architecture, from logical to physical, as well as how to stop and start the database while your server is still running. Next, we showed you how to run SQL commands from both the Web interface and the command line. Many of the tasks you will perform as a DBA involve typing slightly unintuitive commands when something doesn’t seem to be working right in your PHP development environment.

In the next chapter, we shift gears a bit and present some of the other ways to interact with Oracle Database XE from a developer’s and a user’s point of view. In addition, we’ll show you how to download and install the Oracle Database XE client software and connect to an Oracle Database XE instance from another server. 

Google+ Comments

Google+ Comments