Home arrow Site Administration arrow Page 3 - Oracle Database XE: Utilities and Troubleshooting

Troubleshooting in Oracle - Administration

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).

TABLE OF CONTENTS:
  1. Oracle Database XE: Utilities and Troubleshooting
  2. Using Oracle-Supplied Utilities
  3. Troubleshooting in Oracle
By: Apress Publishing
Rating: starstarstarstarstar / 1
October 21, 2010

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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

  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 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.

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                   =
        D:\ORACLEXE\APP\ORACLE\PRODUCT\10.2.0\SERVER\DBS\SPFILEXE.ORA
  sga_target               = 805306368 
  control_files            = D:\ORACLEXE\ORADATA\XE\CONTROL.DBF
  __db_cache_size          = 587202560 
  compatible               = 10.2.0.1.0
  db_recovery_file_dest    = D:\OracleXE\app\oracle\flash_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:\ORACLEXE\APP\ORACLE\ADMIN\XE\ADUMP
  background_dump_dest     = D:\ORACLEXE\APP\ORACLE\ADMIN\XE\BDUMP
  user_dump_dest           = D:\ORACLEXE\APP\ORACLE\ADMIN\XE\UDUMP
  core_dump_dest           = D:\ORACLEXE\APP\ORACLE\ADMIN\XE\CDUMP
  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 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.

Summary

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. 



 
 
>>> More Site Administration Articles          >>> More By Apress Publishing
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

SITE ADMINISTRATION ARTICLES

- Coding: Not Just for Developers
- To Support or Not Support IE?
- Administration: Networking OSX and Win 7
- DotNetNuke Gets Social
- Integrating MailChimp with Joomla: Creating ...
- Integrating MailChimp with Joomla: List Mana...
- Integrating MailChimp with Joomla: Building ...
- Integrating MailChimp with Joomla
- More Top WordPress Plugins for Social Media
- Optimizing Security: SSH Public Key Authenti...
- Patches and Rejects in Software Configuratio...
- Configuring a CVS Server
- Managing Code and Teams for Cross-Platform S...
- Software Configuration Management
- Back Up a Joomla Site with Akeeba Backup

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: