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 Password Files

• Deleting 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 the 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, active Oracle home directories on a single computer.

The Database Tools Overview chapter of Oracle9i Database Getting Started for Windows describes this feature. Multiple Oracle homes affect the naming conventions for Oracle services. As you perform installations into Oracle home directories:

• You must accept the default Oracle home name provided or specify a different name for each Oracle home directory during installation, which is added to most service names

• 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:

Figure 3-1 Services Dialog Boxes

Text description of the illustration ch2b.gif
.
Text description of the illustration ch22.gif

Available Oracle Services

Depending on the products that you have installed, a number of Oracle services are started when you restart your Windows NT computer. A user with a non-system account must have local administrative rights to run services on a Windows NT computer. The two main Oracle services are described in Table 3-1.

Table 3-1 Oracle Services

Service Name	Description
OracleServiceSID	Created for the database instance SID. An Oracle instance is a logical term that refers to: An Oracle service called OracleServiceSID A database Each Oracle instance must have a (SID). A SID is a unique name for an Oracle database instance that can be up to 64 alphanumeric characters in length. For example, if the SID for the Oracle9i database is orcl, it is appended to the service OracleService. The instance name is the same as the value of the ORACLE_SID registry configuration parameter.
Oraclehome_nameTNSListener	Listens for and accepts incoming connection requests from client applications. Automatically starts when the Windows NT computer restarts. The home_name that displays in this service name is the value you entered in the Name field on the File Locations dialog box of Oracle Universal Installer.

Table 3-2 lists additional Oracle services related to specific products or database features that are also available:

Table 3-2 Additional Oracle Services

Service Name	Description
Oraclehome_nameAgent	Listens for and responds to job and event requests sent from the Oracle Enterprise Manager console.

Services for network products that are available are described in Table 3-3:

Table 3-3 Oracle Services for Network Products

Service Name	Description
Oraclehome_nameTNSListener or, for example: Oraclehome_nameTNSlistenerlsnr	The service name, if you are using the default network listener name listener. lsnr is the nondefault network listener name. It is only created if the following command has been run: lsnrctl START lsnr
Oraclehome_nameClientCache	Used for the Client Cache Service.
Oraclehome_nameCMAdmin Oraclehome_nameCMan	Used for the product Oracle Connection Manager.

See Also: Oracle9i Net Services Administrator's Guidefor general information on Oracle network services

Starting Oracle Services

Oracle services must be started for you to use the Oracle9i database and its products.

Start Oracle services in either of three ways:

• From the Control Panel

• From the MS-DOS command prompt

• From the Oracle Administration Assistant for Windows NT

  Note: You can start the 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.

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 you want to start in the list, select it, and choose Start.

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

4. Choose Close to exit the Services dialog box.

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

To start Oracle services from the MS-DOS command prompt:

Enter the following command to start an Oracle service at the MS-DOS command prompt:

C:\> NET START service

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

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

1. Choose Start > Programs > Oracle - HOME_NAME > Configuration and Migration Tools > Oracle Administration Assistant for Windows NT.

2. Right-click the SID.

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

3. Choose Start Service

   This starts the service OracleServiceORCL.

Stopping Oracle Services

On occasion (for example, if you want to re-install the Oracle9i database), you must stop Oracle services. Stop Oracle services in either of three ways:

• From the Control Panel

• From the MS-DOS command prompt

• From the Oracle Administration Assistant for Windows

  Note: You can stop the 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.

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

   OracleServiceSID is stopped.

To stop Oracle services from the MS-DOS command prompt:

Enter the following command to stop an Oracle service at the MS-DOS command prompt:

C:\> net STOP service

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

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

1. Choose Start > Programs > Oracle - HOME_NAME > Configuration and Migration Tools > Oracle Administration Assistant for Windows NT.

2. Right-click the sid.

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

3. Choose Stop Service

   This stops the service OracleServiceORCL.

Auto-starting Oracle Services

You can start Oracle services whenever the Windows computer is restarted. Use the Service dialog box to configure when and how the Oracle9i database is started.

To automatically start the Oracle9i database each time you restart Windows:

1. Choose Start > Settings > Control Panel.

   The Control Panel window appears.

2. Double-click Services.

   The Services dialog box appears.

3. Select the service OracleServiceSID and choose the Startup button.

   The Service dialog box appears.

4. Choose Automatic from the Startup Type field.

5. Choose OK.

6. Choose Close to exit the Services dialog box.

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

1. Choose Start > Programs > Oracle - HOME_NAME > Configuration and Migration Tools > Oracle 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.

   
   
   Text description of the illustration ssconfig.gif

6. Choose Apply.

7. Choose OK.

Starting and Shutting Down a Database with SQL*Plus

These instructions assume that a database instance has been created.

Note: The directory path examples in this chapter follow Optimal Flexible Architecture (OFA) guidelines (for example, ORACLE_BASE\ORACLE_HOME\rdbms\admin). If you specified non-OFA compliant directories during installation, your directory paths will differ. See Oracle9i Database Getting Started for Windows for more information.

To start or shut down an Oracle9i database:

1. Go to your Oracle9i database server.

2. Start SQL*Plus at the MS-DOS command prompt:

   C:\> SQLPLUS / NOLOG
   

3. Connect to the Oracle9i database with the SYSDBA username:

   SQL> CONNECT / AS SYSDBA 
   

4. Follow the instructions below:

   If You Want to...	Then Enter...
   Start a database with the default parameter file	SQL> STARTUP This command uses the default init.ora file located in the ORACLE_BASE\ADMIN\db_name\pfile directory.
   Start a database with a file other than the default parameter file	SQL> STARTUP PFILE=path\filename This command uses the init.ora file specified in path\filename. This example starts the database using a file named init2.ora in C:\ora90\admin\orcl\pfile:
   	SQL> STARTUP PFILE=C:\ora90\admin\orcl\pfile\init2.ora
   Stop the database
   	where mode is one of the following:
   	NORMAL
   	The database waits for all currently-connected users to disconnect and disallows any new connections before shutting down. This is the default mode.
   	IMMEDIATE
   	The database terminates and rolls back active transactions, disconnects clients, and shuts down.
   	ABORT
   	The 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 the 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 the Oracle9i database by starting or stopping the service OracleServiceSID in the Control Panel. This automated procedure is equivalent to using the STARTUP or SHUTDOWN commands or manually entering the following:

If You...	These Commands are Performed...
Start OracleServiceSID	C:\> oradim -STARTUP -SID SID -USERPWD password -STARTTYPE SRV,INST
Stop OracleServiceSID	C:\> oradim -SHUTDOWN -SID SID -USERPWD password -SHUTTYPE srv,inst -SHUTMODE -i

To start the database by starting OracleServiceSID:

1. Review or set the following registry parameters. Note that the ORADIM utility, when used to create or edit instances, automatically sets these values in the registry.

   Parameter	When Set to true, this Parameter...
   ORA_SID_AUTOSTART	The default value, starts the database when OracleServiceSID is started.
   ORA_SID_PFILE	Sets the full path to the init.ora parameter file. The default path is ORACLE_BASE\ADMIN\db_name\pfile\init.ora.

   See Oracle9i Database Getting Started for Windows for instructions on adding and editing registry parameters.

   The exact location in which to set these parameters is determined by the number of Oracle home directories on your computer:

   If You Have...	Then Add These Parameters in...
   One home directory	HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0
   Additional directories	HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEID where ID is incremented for each additional Oracle home directory on your computer.

2. Choose Start > Settings > Control Panel.

   The Control Panel window appears.

3. Double-click Services.

   The Services dialog box appears.

4. Select OracleServiceSID and choose Start.

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

To shut down the database by stopping OracleServiceSID:

1. Set either of the following parameters to true in the registry:

   Parameter	When Set to true, this Parameter...
   ORA_SHUTDOWN	Enables the selected Oracle9i database to be shut down. This includes any database in the current Oracle home.
   ORA_SID_SHUTDOWN	Shuts down the Oracle9i database identified by the SID value.

   If either is set to false (the default setting), it is possible to shut down the database by stopping OracleServiceSID, but this is not recommended because it results in an abnormal shutdown of the database.

   The exact location in which to set these parameters is determined by the number of Oracle home directories on your computer.

   If You Have...	These Parameters are Located in...
   One home directory	HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0
   Two or more home directories	HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEID where ID is incremented for each additional Oracle home directory on your computer.

   See Also: See Oracle9i Database Getting Started for Windows for instructions on adding and editing registry parameters

2. Set the following optional parameters to appropriate values in the registry:

   Parameter	Description
   ORA_SID_SHUTDOWNTYPE	Set to a (abort), i (immediate), or n (normal) to indicate the database shutdown mode. The default mode is i (immediate) if you do not set this parameter.
   ORA_SID_SHUTDOWN_TIMEOUT	Sets the maximum time to wait before the service for a particular SID stops.

3. Choose Start > Settings > Control Panel.

   The Control Panel window appears.

4. Double-click Services.

   The Services dialog box appears.

5. Select OracleServiceSID and choose Stop.

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

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

1. Choose Start > Programs > Oracle - HOME_NAME > Configuration and Migration Tools > Oracle 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 or select Shut down instance when service is stopped.

   
   
   Text description of the illustration ssconfia.gif

Running Multiple Instances

To run multiple instances, ensure that you have already created each instance and started the services for each instance using the ORADIM utility or the Services dialog box in Windows NT. You then run multiple instances by starting each of the instances using SQL*Plus.

To run multiple instances:

1. Ensure that you have already created each instance.

2. Ensure that you started the services for each instance using the ORADIM utility or the Services dialog box of the Windows NT Control Panel.

3. Set the ORACLE_SID configuration parameter at the MS-DOS command prompt to the SID for each instance you want to run:

   C:\> SET ORACLE_SID=SID
   

   where SID is the name of the Oracle9i database instance.

4. Start SQL*Plus:

   C:\> SQLPLUS / NOLOG
   

5. Connect AS SYSDBA:

   SQL> CONNECT / AS SYSDBA
   

6. Start up the database with the new instance:

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

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

Creating Password Files

Use the Password Utility to create password files. The Password Utility is automatically installed with the Oracle9i Utilities. Password files are located in the ORACLE_BASE\ORACLE_HOME\DATABASE directory 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. The following example describes how to perform a local connection.

To create a password file:

1. Create a password file with the Password utility:

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

   The essential elements of a password file are:

   Element	Description
   SID	Identifies the database instance.
   FILE	Specifies the password filename.
   PASSWORD	Sets the password for the SYS account.
   ENTRIES	Sets the maximum number of entries in the password file. This corresponds to the maximum number of distinct users allowed to connect to the database with the SYSDBA and SYSOPER DBA privileges simultaneously.

2. Set the init.ora file parameter REMOTE_LOGIN_PASSWORDFILE to exclusive or shared. Definitions for all possible values are described below:

   Element	Description
   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 the ORA_SID_PWFILE parameter. If a value is unspecified, it looks in the registry for the value of the ORA_PWFILE parameter, which points to a file containing the usernames, passwords, and privileges. If that is not set, it uses the default of ORACLE_BASE\ORACLE_HOME\DATABASE\PWDsid.ORA.
   shared	Specifies that multiple instances can use the password file (for example, a Real Application Clusters environment). 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 releases. The Oracle9i database looks for the same files as it does when the value is exclusive. This is the default value.
   none	Specifies that the Oracle9i database ignores the password file and that privileged users are authenticated by the Windows operating system. none is the default setting.

3. Start SQL*Plus:

   C:\> SQLPLUS / NOLOG
   

4. Connect AS SYSDBA:

   SQL> CONNECT / AS SYSDBA 
   

5. Start the Oracle9i database:

   SQL> STARTUP
   

6. Grant appropriate privileges to each user who must perform database administration. For example:

   SQL> GRANT SYSDBA TO scott;
   

   If successful, 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.

7. Connect to the Oracle9i database with administrative privileges for scott:

   SQL> CONNECT scott/tiger AS SYSDBA
   

   You are connected to the Oracle9i database.

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

Viewing Password Files

The password file is automatically hidden. This section describes two ways of viewing the password file:

• From the MS-DOS command prompt:

• From Windows NT Explorer:

Table 3-4 lists commands for viewing the password file from the MS-DOS command prompt:

Table 3-4 Viewing the Password File from the MS-DOS Command Prompt

To...	Enter...
See the password file	C:\oracle\ora90\database> attrib The password file is displayed as PWDsid.ora: A H C:\oracle\ora90\database\PWDsid.ora
Make the password file visible	C:\oracle\ora90\database> attrib -H PWDsid.ora Note: The password file must be visible before you can move or copy it.
Hide the password file again	C:\oracle\ora90\database> attrib +H PWDsid.ora

To see the password file from Windows NT Explorer:

1. Open Windows NT Explorer.

2. Open the folder to view.

3. Choose Folder Options from the View main menu.

4. Choose the View tab.

5. Follow the instructions below:

   To...	Choose...
   See the password file	Show all files.
   Hide the password file	Do not show hidden files.

Deleting Password Files

This section describes how to delete a password file.

To delete a password file:

1. Make the password file visible at the MS-DOS command prompt or in Windows NT Explorer by following the instructions in section "Viewing Password Files".

2. Delete the password file based on whether you made it visible at the MS-DOS command prompt or in Windows NT Explorer.

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 the 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 init.ora file on the server computer.

2. Set DBLINK_ENCRYPT_LOGIN equal to true.

3. Set the ORA_ENCRYPT_LOGIN configuration variable 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, the Oracle9i database encrypts the password before sending it to the remote database. If the connection fails, the failure is noted in the audit log. The Oracle9i database then checks if either of these parameters is set to false. If so, the Oracle9i database attempts the connection again using an unencrypted version of the password. If the connection is successful, the success is noted in the audit log, and the connection proceeds.

Note: Releases prior to release 7.1 do not support encrypted passwords. If you are connecting to an earlier version of the Oracle database, you must set the initialization parameter DBLINK_ENCRYPT_LOGIN to false for the connection to succeed.

Creating Control, Data, and Log Files on Remote Computers

Although it is possible for Oracle to access database files on remote computers using Universal Naming Convention (UNC), it is not recommended or supported because of performance and network reliability concerns.

UNC is a PC format for specifying the location of resources on a local area network. UNC uses the following format:

\\server-name\shared-resource-pathname

For example, to access the file system01.dbf in the directory C:\oracle\ora90\oradata\orcl on the shared server argon, you reference the file as:

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

Note that the location of archive log files cannot be specified using UNC. If you set the LOG_ARCHIVE_DEST_n initialization parameter to a UNC specification, the 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

Ensure that you set the LOG_ARCHIVE_DEST_n initialization parameter to a mapped drive.

Note: 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 files and datafiles did not.

Archiving Redo Log Files

Your Oracle9i database is created in NOARCHIVELOG mode if you installed your database through the Typical installation. If you created your database through the Custom option of Oracle Database Configuration Assistant, 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 the database from both instance and disk failure.

This section describes how to change the archive mode to ARCHIVELOG and enable automatic archiving. See "Managing Archived Redo Logs" of Oracle9i Database Administrator's Guide for complete descriptions of the ARCHIVELOG and NOARCHIVELOG modes.

Task 1: Change the Archive Mode to ARCHIVELOG

To change the archive mode to ARCHIVELOG:

1. Start SQL*Plus at the MS-DOS command prompt:

   C:\> SQLPLUS / NOLOG
   

2. Connect to the Oracle9i database with the SYSDBA username:

   SQL> CONNECT / AS SYSDBA 
   

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

   SQL> SHUTDOWN
   

4. 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 the 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;
   

9. Continue to "Task 2: Enable Automatic Archiving".

Task 2: Enable Automatic Archiving

To enable automatic archiving:

1. Open the ORACLE_BASE\ADMIN\db_name\pfile\init.ora file.

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: The default size of the Redo logs is approximately 100 MG.

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

   Format	Description	Example
   %%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.	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.	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.	SID1.ARC
   %%ORACLE_SID%%%sARC	Specifies the log sequence number. The number is not padded. The default value is one with no range limit on characters.	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 the ORADEBUG Utility

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

To start the ORADEBUG utility:

1. Start SQL*Plus from the MS-DOS command prompt and connect to the database as SYSDBA. For example:

   C:\> SQLPLUS / NOLOG
   SQL> CONNECT / AS SYSDBA
   
   

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

   SQL> ORADEBUG
   

   The 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
   

   The output from most debug commands is written to a trace file. Trace files are created in the directory specified by the init.ora 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. If you want to find the location of your trace file, enter the following at the SQL*Plus prompt:

   SQL> ORADEBUG TRACEFILE_NAME
   

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

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