3
Administering a Database

This chapter describes how to administer Oracle9i for Windows.

This chapter contains these topics:

• Managing Oracle Services
  
  
• Starting and Shutting Down a Database with SQL*Plus
  
  
• Starting and Shutting Down a Database Using Services
  
  
• Running Multiple Instances
  
  
• Creating and Populating Password Files
  
  
• Connecting Remotely to the Database as SYS
  
  
• Encrypting Database Passwords
  
  
• Creating Control, Data, and Log Files on Remote Computers
  
  
• Archiving Redo Log Files
  
  
• Using ORADEBUG Utility

Managing Oracle Services

This section provides information on the following:

• Oracle Service Naming Conventions for Multiple Oracle Homes
  
  
• Available Oracle Services
  
  
• Starting Oracle Services
  
  
• Stopping Oracle Services
  
  
• Auto-starting Oracle Services

Oracle Service Naming Conventions for Multiple Oracle Homes

Oracle9i for Windows lets you have multiple Oracle homes on a single computer. This feature, described in "Database Tools Overview" in Oracle9i Database Getting Started for Windows, affects Oracle service naming conventions. As you perform installations into Oracle home directories:

• You must accept default Oracle home name provided or specify a different name for each Oracle home directory.
  
  
• You are prompted to give a System Identifier (SID) and global database name for each database installation.

Figure 3-1 shows how the Services dialog box appears with two Oracle9i databases on a single computer. A home name appears in most service names. Line 1 points to service names for the first Oracle9i database installation (OraHome90). Line 2 points to service names for the second Oracle9i database installation (OraHome290). Line 3 points to services with SIDs of MARK and PROD.

Figure 3-1 Services Dialog Boxes

Text description of the illustration orahomes.gif

Available Oracle Services

Depending on products that you have installed, a number of Oracle services are started when you restart your Windows computer. A user with a non-system account must have local administrative rights to run services on a Windows computer. The five main Oracle services are described in this section. For information on other available services, see "Oracle9i Services on Windows" in Oracle9i Database Getting Started for Windows.

OracleServiceSID

Created for the database instance System Identifier (SID), where SID is the value you entered for the database name during Oracle9i installation. This service is mandatory. If is it not started, then the following ORA-12560 error message appears when you attempt to use any of Oracle9i Utilities, such as SQL*Plus:

ORA-12560 TNS: protocol adapter error

OracleHOME_NAMETNSListener

Listens for and accepts incoming connection requests from client applications. Automatically starts when the Windows computer restarts. If it is not started, then the following error message appears when attempting to connect with username/password@net_service_name:

ORA-12541 TNS: no listener

OracleHOME_NAMEAgent

Part of the Oracle Enterprise Manager product suite. Intelligent Agent is required for execution of jobs and performance monitoring of Oracle services or targets such as listeners, databases, Oracle HTTP Server, and Oracle Applications. Intelligent Agent also collects statistical data for Capacity Planner and Performance Manager, which are data-collecting applications available in Oracle Diagnostics Pack.

OracleHOME_NAMEHTTPServer

Starts Oracle HTTP Server. Used as the Web server with browser-based Oracle Enterprise Manager.

OracleHOME_NAMEManagementServer

Management Server is the middle tier of Oracle Enterprise Manager. It maintains centralized intelligence and distributed control between clients and managed nodes.

Starting Oracle Services

Oracle services must be started for you to use Oracle9i database and its products. You can start Oracle services from three different locations:

• Control Panel
  
  
• Command Prompt
  
  
• Oracle Administration Assistant for Windows NT

  Note: You can start Oracle9i database when you start OracleServiceSID. See "Starting and Shutting Down a Database Using Services" for information on registry parameters that enable you to do this.

Control Panel

To start Oracle services from the Control Panel:

1. Choose Start > Settings > Control Panel.

   The Control Panel window appears.

2. Double-click Services.

   The Services dialog box appears.

3. Find the service to start in the list, select it, and choose Start.

   If you cannot find OracleServiceSID in the list, then use ORADIM utility to create it.

4. Click Close to exit the Services dialog box.

   Note: If you are using Windows 2000, then you will not see Services in the Control Panel. Double-click Administrative Tools first, then double-click Services.

Command Prompt

To start Oracle services from the command prompt, enter:

C:\> NET START service

where service is a specific service name, such as OracleServiceORCL.

Oracle Administration Assistant for Windows NT

To start Oracle services from Oracle Administration Assistant for Windows NT:

1. Choose Start > Programs > Oracle - HOME_NAME > Configuration and Migration Tools > Administration Assistant for Windows NT.
   
   
2. Right-click the SID.

   where SID is a specific instance name, such as orcl.

3. Click Start Service

   This starts service OracleServiceORCL.

Stopping Oracle Services

On occasion (for example, when re-installing Oracle9i database), you must stop Oracle services. You can stop Oracle services from three different locations:

• Control Panel
  
  
• Command Prompt
  
  
• Oracle Administration Assistant for Windows NT

  Note: You can stop Oracle9i database in normal, immediate, or abort mode when you stop OracleServiceSID. See "Starting and Shutting Down a Database Using Services" for information on registry parameters that enable you to do this.

Control Panel

To stop Oracle services from the Control Panel:

1. Choose Start > Settings > Control Panel.

   The Control Panel window appears.

2. Double-click Services.

   The Services dialog box appears.

3. Select OracleHOME_NAMETNSListener and choose Stop.

   OracleHOME_NAMETNSListener is stopped.

4. Select OracleServiceSID and choose Stop.
   
   
5. Click OK.

   OracleServiceSID is stopped.

Command Prompt

To stop Oracle services from the command prompt, enter:

C:\> net STOP service

where service is a specific service name, such as OracleServiceORCL.

Oracle Administration Assistant for Windows NT

To stop Oracle services from Oracle Administration Assistant for Windows NT:

1. Choose Start > Programs > Oracle - HOME_NAME > Configuration and Migration Tools > Administration Assistant for Windows NT.
   
   
2. Right-click the sid.

   where sid is a specific instance name, such as orcl.

3. Click Stop Service

   This stops service OracleServiceORCL.

Auto-starting Oracle Services

Oracle services can be set to start automatically whenever the Windows computer is restarted. You can turn auto-start on or off from two different locations:

• Control Panel
  
  
• Oracle Administration Assistant for Windows NT

Control Panel

To use the Control Panel to configure when and how Oracle9i database is started:

1. Choose Start > Settings > Control Panel.

   The Control Panel window appears.

2. Double-click Services.

   The Services dialog box appears.

   Note: If you are using Windows 2000, then you will not see Services in the Control Panel. Double-click Administrative Tools first, then double-click Services.

3. Select service OracleServiceSID and click Startup.

   The Service dialog box appears.

4. Choose Automatic from the Startup Type field.
   
   
5. Click OK.
   
   
6. Click Close to exit the Services dialog box.

Oracle Administration Assistant for Windows NT

To automatically start Oracle services from Oracle Administration Assistant for Windows NT:

1. Choose Start > Programs > Oracle - HOME_NAME > Configuration and Migration Tools > Administration Assistant for Windows NT.
   
   
2. Right-click the sid.

   where sid is a specific instance name, such as orcl.

3. Choose Startup/Shutdown Options.
   
   
4. Choose the Oracle NT Service tab.
   
   
5. Choose Automatic in the Oracle NT Service Startup Type box.
   
   
6. Click Apply.
   
   
7. Click OK.

   

   Text description of the illustration ss_cnfg1.gif

Starting and Shutting Down a Database with SQL*Plus

These instructions assume that a database instance has been created.

To start or shut down an Oracle9i database:

1. Go to your Oracle9i database server.
   
   
2. Start SQL*Plus at the command prompt:
   
   

   C:\> sqlplus / NOLOG
   
   

3. Connect to Oracle9i database with username SYSDBA:
   
   

   SQL> CONNECT / AS SYSDBA 
   
   

4. To start a database, enter:
   
   

   SQL> STARTUP [PFILE=path\filename]
   
   

   This command uses the initialization parameter file specified in path\filename. To start a database using a file named

   init2.ora in C:\ora92\admin\orcl\pfile 
   
   

   for example, you would enter:

   SQL> STARTUP PFILE=C:\ora92\admin\orcl\pfile\init2.ora
   
   

   If no PFILE is specified, then the command uses the default initialization parameter file located in directory ORACLE_BASE\ADMIN\db_name\pfile.

5. To stop a database, enter:
   
   

   SQL> SHUTDOWN [mode]
   
   

   where mode is normal, immediate, or abort.

   In a normal shutdown, Oracle9i database waits for all currently-connected users to disconnect and disallows any new connections before shutting down. This is the default mode.

   In an immediate shutdown, Oracle9i database terminates and rolls back active transactions, disconnects clients, and shuts down.

   In an abort shutdown, Oracle9i database terminates active transactions and disconnects users; it does not roll back transactions. The database performs automatic recovery and rollback the next time it is started. Use this mode only in emergencies.

   See Also: Oracle9i Database Getting Started for Windows for a list of other tools that can start Oracle9i database and this guide for information on options you can specify when starting your database.

Starting and Shutting Down a Database Using Services

You can start or shut down Oracle9i database by starting or stopping service OracleServiceSID in the Control Panel. Starting OracleServiceSID is equivalent to using the STARTUP command or manually entering:

C:\> oradim -STARTUP -SID SID -USERPWD password -STARTTYPE srv,inst

Stopping OracleServiceSID is equivalent to using the SHUTDOWN command or manually entering:

C:\> oradim -SHUTDOWN -SID SID -USERPWD password -SHUTTYPE srv,inst -SHUTMODE -i

You can enable starting and stopping Oracle9i database through OracleServiceSID two different ways:

• Oracle Administration Assistant for Windows NT
  
  
• Setting Registry Parameters

Oracle Administration Assistant for Windows NT

To start or stop a database using Oracle Services from Oracle Administration Assistant for Windows NT:

1. Choose Start > Programs > Oracle - HOME_NAME > Configuration and Migration Tools > Administration Assistant for Windows NT.
   
   
2. Right-click the SID.

   where SID is a specific instance name, such as ORCL.

3. Choose Startup/Shutdown Options.
   
   
4. Choose the Oracle Instance tab.
   
   
5. Select Start up instance when service is started, Shut down instance when service is stopped, or both.

   

   Text description of the illustration ss_cnfg2.gif

Setting Registry Parameters

To start or stop an Oracle9i database through Oracle Services, set the following registry parameters to the indicated values:

• ORA_SID_AUTOSTART

  When set to true, the default value, this parameter causes Oracle9i database to start when OracleServiceSID is started.

• ORA_SID_PFILE

  This parameter sets the full path to the initialization parameter file. The default path is ORACLE_BASE\ADMIN\db_name\pfile\init.ora

• ORA_SHUTDOWN

  When set to true, this parameter enables the selected Oracle9i database to be shut down when OracleServiceSID is stopped. This includes any database in the current Oracle home. Default value is false.

• ORA_SID_SHUTDOWN

  When set to true, this parameter causes the Oracle9i database identified by the SID value to shut down when OracleServiceSID is stopped. Default value is false.

  Caution: If ORA_SHUTDOWN or ORA_SID_SHUTDOWN is set to false, then shutting down OracleServiceSID will still shut down Oracle9i database. But it will be an abnormal shutdown, and Oracle Corporation does not recommend it.

The following two registry parameters are optional:

• ORA_SID_SHUTDOWNTYPE

  This parameter controls database shutdown mode. Set it to a (abort), i (immediate), or n (normal). Default mode is i (immediate) if you do not set this parameter.

• ORA_SID_SHUTDOWN_TIMEOUT

  This parameter sets maximum time to wait before the service for a particular SID stops.

The registry location of these required and optional parameters is determined by the number of Oracle home directories on your computer. If you have only one Oracle home directory, then these parameters belong in:

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0

If you have multiple Oracle home directories, then these parameters belong in:

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEID

where ID is incremented for each additional Oracle home directory on your computer.

Starting or Stopping OracleServiceSID from the Control Panel

1. Choose Start > Settings > Control Panel.

   The Control Panel window appears.

2. Double-click Services.

   The Services dialog box appears.

3. To start the database, select OracleServiceSID and choose Start.

   This automatically starts ORADIM utility and issues the -STARTUP command using the initialization parameter file identified by ORA_SID_PFILE.

4. To stop the database, select OracleServiceSID and choose Stop.

   This automatically starts ORADIM utility, which issues the -SHUTDOWN command in the mode indicated by ORA_SID_SHUTDOWNTYPE, and shuts down your Oracle9i database.

Running Multiple Instances

To run multiple instances:

1. Start the service for each instance using ORADIM utility or the Services dialog box of the Control Panel.
   
   
2. At the command prompt set the ORACLE_SID configuration parameter to the SID for the first instance to run:
   
   

   C:\> SET ORACLE_SID=SID
   
   

   where SID is the name of the Oracle9i database instance.

3. Start SQL*Plus:
   
   

   C:\> sqlplus / NOLOG
   
   

4. Connect AS SYSDBA:
   
   

   SQL> CONNECT / AS SYSDBA
   
   

5. Start up the first instance:
   
   

   SQL> STARTUP PFILE=ORACLE_BASE\admin\db_name\pfile\init.ora
   
   

   where ORACLE_BASE is c:\oracle (unless you changed it during installation) and db_name is the name of the instance.

6. Repeat Steps 2-5 for the other instances to run.

Creating and Populating Password Files

Use Password Utility to create password files. Password Utility is automatically installed with Oracle9i Utilities. Password files are located in directory ORACLE_BASE\ORACLE_HOME\DATABASE and are named PWDsid.ORA, where SID identifies the Oracle9i database instance. Password files can be used for local or remote connections to an Oracle9i database.

To create and populate a password file:

1. Create a password file with Password utility:
   
   

   C:\> orapwd FILE=PWDsid.ora PASSWORD=password ENTRIES=max_users
   
   

   where

   • FILE specifies the password filename.
     
     
   • SID identifies the database instance.
     
     
   • PASSWORD sets the password for account SYS.
     
     
   • ENTRIES sets maximum number of entries in password file. This corresponds to maximum number of distinct users allowed to connect to the database simultaneously with either the SYSDBA or the SYSOPER DBA privilege.
2. Set initialization parameter file parameter REMOTE_LOGIN_PASSWORDFILE to exclusive, shared, or none.

   exclusive specifies that only one instance can use the password file and that the password file contains names other than SYS. In search of the password file, Oracle9i looks in the registry for the value of parameter ORA_SID_PWFILE. If no value is specified, then it looks in the registry for the value of parameter ORA_PWFILE, which points to a file containing usernames, passwords, and privileges. If that is not set, then it uses the default:

   ORACLE_BASE\ORACLE_HOME\DATABASE\PWDsid.ORA.
   
   

   shared is the default value. It specifies that multiple instances (for example, a Real Application Clusters environment) can use the password file. However, the only user recognized by the password file is SYS. Other users cannot log in with SYSOPER or SYSDBA privileges even if those privileges are granted in the password file. The shared value of this parameter affords backward compatibility with earlier Oracle database software releases. Oracle9i database looks for the same files as it does when the value is exclusive.

   none specifies that Oracle9i database ignores the password file and that authentication of privileged users is handled by the Windows operating system. This is the default setting.

3. Start SQL*Plus:
   
   

   C:\> sqlplus / NOLOG
   
   

4. Connect AS SYSDBA:
   
   

   SQL> CONNECT / AS SYSDBA
   
   

5. Start Oracle9i database:
   
   

   SQL> STARTUP
   
   

6. Grant appropriate privileges to each user. Users who must perform database administration, for example, would be granted privilege SYSDBA:
   
   

   SQL> GRANT SYSDBA TO scott;
   
   

   If the grant is successful, then the following message displays:

   Statement Processed.
   
   

   This adds scott to the password file and enables scott to connect to the database with SYSDBA privileges. Use SQL*Plus to add or delete usernames, user passwords, and user privileges in password files.

   Caution: Copying or manually moving password files may result in ORADIM utility being unable to find a password to start an instance.

Viewing and Hiding the Password File

The password file is not automatically hidden. It can be made invisible and visible again from two different locations:

• Command Prompt
  
  
• Windows NT Explorer

  Note: The password file must be visible before it can be moved, copied, or deleted.

Command Prompt

1. To see the password file, enter:
   
   

   C:\oracle\ora92\database> attrib
   
   

   The password file is displayed as PWDsid.ora:

   A       C:\oracle\ora92\database\oradba.exe
   A       C:\oracle\ora92\database\OraDim.Log
   A       C:\oracle\ora92\database\PWDsid.ora
   A       C:\oracle\ora92\database\SPFILEsid.ora
   
   

2. To make the password file invisible, enter:
   
   

   C:\oracle\ora92\database> attrib +H PWDsid.ora
   
   

3. To see the effect of the change, enter:
   
   

   C:\oracle\ora92\database> attrib
   
   

   The password file is now hidden:

   A       C:\oracle\ora92\database\oradba.exe
   A       C:\oracle\ora92\database\OraDim.Log
   A   H   C:\oracle\ora92\database\PWDsid.ora
   A       C:\oracle\ora92\database\SPFILEsid.ora
   
   

4. To make the password file visible again, enter:
   
   

   C:\oracle\ora92\database> attrib -H PWDsid.ora
   

Windows NT Explorer

To make the password file invisible or visible again:

1. Navigate to directory C:\oracle\ora92\database.
   
   
2. Right-click PWDsid.ora.
   
   
3. Choose Properties.

   The PWDsid.ora Properties dialog box opens

4. In Attributes, check or clear the checkbox next to Hidden.
   
   
5. Click OK.

To view or hide an invisible password file:

1. Navigate to directory C:\oracle\ora92\database.
   
   
2. Choose Folder Options from the View main menu.
   
   
3. Choose the View tab.
   
   
4. To view an invisible password file, choose Show hidden files and folders.
   
   
5. To hide an invisible password file, choose Do not show hidden files and folders.
   
   
6. Click OK.

Connecting Remotely to the Database as SYS

When connecting to the starter database from a remote computer as SYS, you must use a different password from the one described in Oracle9i Database Installation Guide for Windows when logging on with SYSDBA privileges. This is because the password file enables database access in this situation and it requires the password oracle for this purpose.

Encrypting Database Passwords

With Oracle9i database, you can encrypt the password used to verify a remote database connection.

To enable password encryption:

1. Add DBLINK_ENCRYPT_LOGIN to the initialization parameter file on the server computer.
   
   
2. Set DBLINK_ENCRYPT_LOGIN equal to true.
   
   
3. Set configuration variable ORA_ENCRYPT_LOGIN on the client computer to true. See Oracle9i Database Getting Started for Windows for instructions on adding and setting configuration parameters in the registry.

Once these parameters are set to true, whenever a user attempts a remote login, Oracle9i database encrypts the password before sending it to the remote database. If the connection fails, then the failure is noted in the audit log. Oracle9i database then checks if either of these parameters is set to false. If so, Oracle9i database attempts the connection again using an unencrypted version of the password. If the connection is successful, then the success is noted in the audit log, and the connection proceeds.

Creating Control, Data, and Log Files on Remote Computers

Oracle can access database files on a remote computer using Universal Naming Convention (UNC), but it may degrade database performance and network reliability. UNC is a PC format for specifying locations of resources on a local area network. UNC uses the following format:

\\server-name\shared-resource-path-name

For example, UNC specification for file system01.dbf in directory C:\oracle\oradata\orcl on shared server argon would be:

\\argon\oracle\oradata\orcl\system01.dbf

Locations of archive log files cannot be specified using UNC. Always set initialization parameter LOG_ARCHIVE_DEST_n to a mapped drive. If you set it to a UNC specification, then Oracle9i database does not start and you receive the following errors:

ORA-00256: error occurred in translating archive text string '\meldell\rmdrive'
ORA-09291: sksachk: invalid device specified for archive destination 
OSD-04018: Unable to access the specified directory or device
O/S-Error: (OS 2) The system cannot find the file specified

An ORA-00256 error also occurs if you enter \\\meldell\rmdrive or \\\meldell\\rmdrive. Control files required the additional backslashes for Oracle8 release 8.0.4, but redo log files and datafiles did not.

Archiving Redo Log Files

If you installed your Oracle9i database through the Typical installation, then it is created in NOARCHIVELOG mode. If you created your database through the Custom option of Database Configuration Assistant, then you had the choice of either ARCHIVELOG or NOARCHIVELOG.

In NOARCHIVELOG mode, redo logs are not archived. Setting your archive mode to ARCHIVELOG and enabling automatic archiving causes redo log files to be archived. This protects Oracle9i database from both instance and disk failure.

This section describes how to change archive mode to ARCHIVELOG and enable automatic archiving.

Changing Archive Mode to ARCHIVELOG

To change archive mode to ARCHIVELOG:

1. Start SQL*Plus at the command prompt:
   
   

   C:\> sqlplus / NOLOG
   
   

2. Connect to the Oracle9i database as SYSDBA:
   
   

   SQL> CONNECT / AS SYSDBA
   
   

3. If the database is open, then shut it down:
   
   

   SQL> SHUTDOWN
   
   

4. Next, mount the database:
   
   

   SQL> STARTUP MOUNT
   
   

5. Enter the following command:
   
   

   SQL> ARCHIVE LOG LIST
   
   

   The following output indicates the database is not in archive mode:

   Database log mode              No Archive Mode
   Automatic archival             Disabled
   Archive destination            %RDBMS%\
   Oldest online log sequence     34
   Current log sequence           37
   
   

6. Change archive mode to ARCHIVELOG:
   
   

   SQL> ALTER DATABASE ARCHIVELOG;
   
   

7. Enter the following command:
   
   

   SQL> ARCHIVE LOG LIST
   
   

   The following output indicates the database is now in archive mode:

   Database log mode              Archive Mode
   Automatic archival             Disabled
   Archive destination            %RDBMS%\
   Oldest online log sequence     34
   Current log sequence           37
   
   

8. Open the database:
   
   

   SQL> ALTER DATABASE OPEN;
   
   

Enabling Automatic Archiving

To enable automatic archiving:

1. Open file ORACLE_BASE\ADMIN\db_name\pfile\init.ora.
   
   
2. Find the following three initialization parameters:
   
   

   # LOG_ARCHIVE_START = true
   # LOG_ARCHIVE_DEST_1 = %ORACLE_HOME%\database\archive
   # LOG_ARCHIVE_FORMAT = "%%ORACLE_SID%%T%TS%S.ARC"
   
   

3. Remove the # sign from in front of each.

   Note: Double quotation marks around LOG_ARCHIVE_FORMAT

4. Edit the LOG_ARCHIVE_DEST_n value to identify an existing drive and directory in which to archive your filled redo logs.

   Note: Default size of Redo logs is approximately 100 MG.

5. Edit the LOG_ARCHIVE_FORMAT value to indicate the appropriate archiving format:

   %%ORACLE_SID%%%T.ARC specifies the thread number. This number is padded to the left by zeroes. The default value is one with a range of up to three characters. Example: SID0001.ARC

   %%ORACLE_SID%%%S.ARC specifies the log sequence number. This number is padded to the left by zeroes. The default value is one with a range of up to five characters. Example: SID0001.ARC

   %%ORACLE_SID%%%t.ARC specifies the thread number. The number is not padded. The default value is one with no range limit on characters. Example: SID1.ARC

   %%ORACLE_SID%%%s.ARC specifies the log sequence number. The number is not padded. The default value is one with no range limit on characters. Example: SID1.ARC

6. Save your changes.
   
   
7. Exit the file.
   
   
8. Shut down the database:
   
   

   SQL> SHUTDOWN
   
   

9. Restart the database
   
   

   SQL> STARTUP
   
   

10. Enter the following command:
    
    

    SQL> ARCHIVE LOG LIST
    
    

    The following output indicates that automatic archiving of redo log files is enabled and an archiving destination is specified:

    Database log mode              Archive Mode
    Automatic archival             Enabled
    Archive destination            C:\BACKUP
    Oldest online log sequence     34
    Current log sequence           37
    

Using ORADEBUG Utility

ORADEBUG utility is a debugging tool that sends debug commands through SQL*Plus to an Oracle process. It is primarily for use by developers and Oracle Support Services personnel. Use this utility only when instructed to do so by Oracle Support Services. You must have database administrator privileges to use ORADEBUG utility.

To start ORADEBUG utility:

1. Start SQL*Plus from the command prompt:
   
   

   C:\> sqlplus / NOLOG
   
   

2. Connect to Oracle9i database as SYSDBA:
   
   

   SQL> CONNECT / AS SYSDBA
   
   

3. Enter the following at the SQL*Plus prompt:
   
   

   SQL> ORADEBUG
   
   

   ORADEBUG utility runs and prompts you for parameters. To obtain a list of these parameters, enter the following at the SQL*Plus prompt:

   SQL> ORADEBUG HELP
   
   

   Output from most debug commands is written to a trace file. Trace files are created in the directory specified by initialization parameters BACKGROUND_DUMP_DEST and USER_DUMP_DEST. By default, these parameters are set to ORACLE_BASE\ADMIN\db_name\bdump and ORACLE_BASE\ADMIN\db_name\udump, respectively. To find the location of your trace file, enter the following at the SQL*Plus prompt:

   SQL> ORADEBUG TRACEFILE_NAME
   
   

   If output from a debug command produces more than one line of output, then the result is sent to a trace file, and a message indicating that the command has completed is relayed to SQL*Plus. If output from a debug command produces only one line of output, then the output is relayed directly to SQL*Plus.

   Note: There is currently a limitation when using ORADEBUG utility. If you attempt to debug a thread that is blocking on I/O, ORADEBUG can cause SQL*Plus to hang until that I/O completes.