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).
Most of your database troubleshooting techniques are available at theOracle Database XE home page, where you can browse and monitor thefollowing:
Frequently running SQL
In addition, you can run reports against database tables to see whichof those tables may be performing poorly because they either donít haveenough indexes, have too many indexes, or donít even have a primarykey.
One useful tool in the administratorís toolbox is not available usingthe Web interface: the databaseís alert log. The alert log fileis a text file containing messages about the state of the database. Itcontains entries about significant database events, such as databasestartup and shutdown information, nondefault initialization parameters,and other error conditions ranging from warning messages to fatal errorsthat cause a database failure. In addition, allALTER SYSTEMcommands appear in the alert log.
The alert log is a good place to look in situations where yourdatabase does not start or suddenly fails. Other log files known as tracefiles in the same directory as the alert log may also helpdetermine the issue when the database is having problems.
The location and name of the alert log differ on the Windows andLinux platforms. On Windows the alert log file is stored in%ORACLE_BASE%\admin\XE\bdump\alert_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 filed:\oraclexe\app\oracle\admin\xe\bdump\alert_xe.log Wed Jul 12 20:37:342006 ORACLE V10.2.0.1.0 - Production vsnsta=0 vsnsql=14 vsnxtr=3 Windows XP Version V5.1 Service Pack 2 CPU : 2 - type 586 Process Affinity : 0x00000000 Memory (Avail/Total): Ph:3144M/3583M, Ph+PgF:6753M/7002M, VA:1945M/2047M Wed Jul 12 20:37:34 2006 Starting ORACLE instance (normal) LICENSE_MAX_SESSION = 0LICENSE_SESSIONS_WARNING = 0 Picked latch-free SCN scheme 2 Using LOG_ARCHIVE_DEST_10 parameter default value asUSE_DB_RECOVERY_FILE_DEST Autotune of undo retention is turned on. IMODE=BR ILAT =10 LICENSE_MAX_USERS = 0 SYS auditing is disabled ksdpec: called for event 13740 prior to event group initialization Starting up ORACLE RDBMS Version: 10.2.0.1.0.
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 makeit easy to browse previous alert log entries. After you rename the alertlog file, Oracle automatically creates a new alert log file the nexttime it needs to record an event or error message.
Oracle Database XE, with its intuitive Web interface, makes it eveneasier to be a developer. The same can be said for those times when youmust wear your DBA hat; most, if not all, functions you need to performas a DBA are available within the Web interface.
In this chapter, we gave you a high-level look at the Oracle databasearchitecture, from logical to physical, as well as how to stop andstart the database while your server is still running. Next, we showedyou how to run SQL commands from both the Web interface and the commandline. Many of the tasks you will perform as a DBA involve typingslightly unintuitive commands when something doesnít seem to be workingright in your PHP development environment.
In the next chapter, we shift gears a bit and present some of theother ways to interact with Oracle Database XE from a developerís and auserís point of view. In addition, weíll show you how to download andinstall the Oracle Database XE client software and connect to an OracleDatabase XE instance from another server.