2 Database Tools on Windows

2.1.1 Database Tools and Operating System Compatibility

Almost all database tools are available on all supported versions of Windows. The exceptions are:

Comment: In the following list, an item about Oracle Advanced Security was deleted, because it concerned installation versions rather than operating system compatibility. (4/21/04)

• Oracle Migration Workbench is available only at OTN. See http://www.oracle.com/technology/tech/migration/workbench/index.html

• Oracle Enterprise Manager and its optional management packs have additional integrated tools to assist in managing databases. See Oracle Enterprise Manager Concepts for more information.

2.1.2 Preferred Database Tools

Table 2-1 lists various database tools you can use to perform common database administration tasks. Oracle recommends you use tools listed in the "Preferred Database Tool" column of the table. After choosing a tool to perform a task, go to Table 2-2, for instructions on how to start the tool.

D:\> exp system/manager full=y volsize=100m;
LRM-00101: unknown parameter name 'volsize'
EXP-00019: failed to process parameters, type 'EXP HELP=Y' for help
EXP-00000: Export terminated unsuccessfully

Comment: Legato Single Server Version added to Other Tools for "Back up database" and "Recover database". See Oracle Database ReadMe section 10.0, "Media Management Software". (3/25/04)

Comment: Legato references removed, as it is no longer supported. (11/17/05)

Comment: In the following list, the item concerning Oracle Wallet Manager is based on Oracle Database ReadMe section 7.2, "Issues Related to Using Oracle Database Wallets". (3/24/04)

Comment: In the following list, the item concerning Oracle Enterprise Manager Console is based on e-mail from Janelle Simmons dated 4/19/04. (4/19/04)

Comment: In the following list, the item concerning Oracle Enterprise Manager Grid Control is based on e-mail from Janelle Simmons dated 4/20/04. (4/20/04)

Comment: In the following list, in the item about Oracle Database Upgrade Assistant, all databases before Oracle8i release 8.1.7 were deleted, based on information in the Oracle Database Upgrade Guide 10gR2 review draft. (5/11/05)

The following points refer to tools listed in Table 2-1, "Preferred Database Tools":

• SQL*Plus Worksheet is part of Oracle Enterprise Manager.

• ORADIM can only set a password when none was previously set. If a password has been previously set, then ORADIM can change it only by deleting and re-creating Oracle Database services.

• User Migration Utility can migrate local or external users to enterprise users. For more information, see "Using the User Migration Utility" in Oracle Advanced Security Administrator's Guide.

• Oracle Database Upgrade Assistant can upgrade the following databases to the current release: Oracle8i release 8.1.7, Oracle9i releases 1 (9.0.1) and 2 (9.2), and Oracle Database 10g Release 1 (10.1).

• Data Pump Export and Data Pump Import are preferred for Oracle Database 10g Release 1 (10.1) and later data; Export and Import are preferred for earlier data.

• When upgrading a database, the provided scripts in SQL*Plus are required when upgrading Oracle Real Application Clusters systems.

• If you back up files while you are shutting down the database, then your backup will be invalid. You cannot use an invalid backup to restore files at a later date.

• You cannot use earlier versions of Oracle Wallet Manager to manage Oracle Database 10g Release 1 (10.1) and later wallets that contain password-based credentials for authentication to Oracle Internet Directory. These credentials are placed in the wallet when an Oracle Database server is registered in Oracle Internet Directory.

  The database wallet that Database Configuration Assistant automatically generates during database registration can only be used with an Oracle Database 10g Release 1 (10.1) or later server. You cannot use this database wallet for earlier versions of the database, nor can you use it for Oracle Internet Directory Release 9.0.4 or earlier.

• For guidelines on creating database objects, see Oracle Database Administrator's Guide.

2.2.1 Starting Database Tools in Multiple Oracle Homes

If you have multiple Oracle homes on your computer from previous releases, then see Appendix B, "Optimal Flexible Architecture", in Oracle Database Installation Guide for Microsoft Windows (32-Bit) for a description of differences between Oracle homes in different releases.

2.2.2 Starting Database Tools from the Start Menu

Table 2-2 describes how to start assistants and other tools from the Start Menu. It also tells where to go for further information on using these products.

Comment: Oracle INTYPE File Assistant removed from this table, because it is desupported in 10gR1. See Windows Release Notes section 9, "Desupported Components". (3/25/04)

Comment: Oracle Enterprise Manager Configuration Assistant removed from this table, because it no longer appear on the Start Menu. (4/14/04)

Comment: New first paragraphs under "More Information" for Oracle Enterprise Manager Console and SQL*Plus Worksheet. They appear on Start Menu only with Oracle Database Client. (4/15/04)

Comment: The entry for Oracle Migration Workbench under "More Information" is new. The first paragraph for Oracle Counters for Windows Performance Monitor under "More Information" is new. (4/14/04)

Comment: The Pro*C/C++ graphical interface was removed from this table, because is no longer available from the Start Menu. (4/15/04)

http://www.oracle.com/technology/tech/migration/workbench/index.html

Comment: The following Note is new. It is based on Windows Release Notes section 10.19, "Oracle Wallet Manager Missing from the Start Menu". (3/25/04)

2.2.3 Starting Database Tools from the Command Line

Comment: The entry for Oracle Wallet Manager in this table is new. It is based on Windows Release Notes section 10.19, "Oracle Wallet Manager Missing from the Start Menu". (3/25/04)

Comment: The entry for Migration Utility in this table was deleted, because Migration Utility is apparently no longer supported. (4/13/04)

Comment: Added Database Configuration Assistant and Database Upgrade Assistant to following list. E-mail from Satish Panchumarthy dated 5/27/05. (6/10/05)

Table 2-3 describes how to start Oracle Database tools from the command line, and where to go for further information on using these products.

2.2.4 Starting Windows Tools

Table 2-4 describes how to start each Windows tool and where to go for more information on using these products.

C:\> regedt32

2.3.1 Windows Processing Options

This section discusses possible values for the operating system dependent file processing specifications string option (os_file_proc_clause), referred to in "Specifying Datafile Format and Buffering" in Chapter 8, "SQL*Loader Control File Reference", in Oracle Database Utilities.

INFILE mydata.dat "str '\n'"

INFILE mydata.dat "str x'0a'"

RECORDS DELIMITED BY '\n'

RECORDS DELIMITED BY 0x'0a'

INFILE "mydata.dat" "var n"

2.3.2 Case Study Files

The distribution media for SQL*Loader contains case study files for control files, datafiles, and setup files in ulcase1,...ulcase11 in the following directory: Oracle_Home\rdbms\demo.

2.3.3 Specifying the Bad File

When SQL*Loader executes, it can create a file called a bad file or reject file in which it places records that were rejected because of formatting errors or because they caused Oracle Database errors. If you have specified that a bad file is to be created, it overwrites any existing file with the same name; ensure that you do not overwrite a file you wish to retain.

2.3.4 Control File Conventions

When preparing SQL*Loader control files (.ctl), you must follow certain syntax and notational conventions.

In full path descriptions, backslashes do not require escape characters or other special treatment. When embedding a single or double quotation mark inside a string delimited by double quotation marks, place a backslash escape character before the embedded quotation mark.

When specifying datatypes in the SQL*Loader control file, note that the default sizes of native datatypes shown in Table 2-5 are specific to Windows. These datatypes can be loaded with correct results only between systems where they have the same length in bytes. You cannot override these defaults in the control file. If the byte order is different between the systems, you can indicate the byte order of the data with the BYTEORDER parameter, or you can place a byte-order mark (BOM) in the file.

2.4.1 Using Event Viewer to Monitor a Database

Event Viewer lets you monitor events in your system. An event is an important occurrence in the system or application (such as Oracle Database) that requires user notification. While messages for major events can appear on-screen as you work at your computer, events not requiring your immediate attention are recorded by Windows in the Event Viewer log file. You can then view this information at your convenience.

Event Viewer can be used to monitor Oracle Database events, such as:

• Initialization of System Global Area for active instance

• Initialization of Program Global Area (PGA) for background processes of active instance

• Connection to Oracle Database using AS SYSDBA

In addition, the operating system audit trail is logged to Event Viewer.

2.4.2 Using Microsoft Management Console to Administer a Database

Microsoft Management Console provides a central location for network administration. Microsoft Management Console hosts applications (called snap-ins) that administrators can use to manage their networks. Oracle snap-ins enable database administrators to:

• Configure Oracle Database administrators, operators, users, and roles so the Windows operating system can authenticate them

• Configure OracleServiceSID

• Modify registry parameters for all Oracle homes on the computer

• Modify the computer hostname, username, and password for the database being monitored by Oracle Counters for Windows Performance Monitor

• View and terminate an Oracle Database thread

2.4.3 Using Oracle Counters for Windows Performance Monitor

Oracle Counters for Windows Performance Monitor is integrated into Windows Performance Monitor. This tool enables you to view performance of processors, memory, cache, threads, and processes. Performance information provided includes device usage, queue lengths, delays, throughput measurements, and internal congestion measurements. This information is provided as charts, alerts, and reports.

You can use Oracle Counters for Windows Performance Monitor to monitor key Oracle Database information, such as:

• Library cache

• Buffer cache

• Data dictionary cache

• Redo log buffer cache

• Thread activity

You can use your findings to improve database performance.

2.4.4 Using Registry Editor to Modify Configuration Information

Oracle Database stores its configuration information in a structure known as the registry. You can view and modify this configuration information through Registry Editor. The registry contains configuration information for your computer and must not be accessible for editing by inexperienced users. Only experienced administrators should view and change this information.

Registry Editor displays configuration information in a format similar to Windows Explorer. In the left-hand window is a tree-like format consisting of keys (or folders). When one of these keys is highlighted, parameters and values assigned to that key are displayed in the right-hand window.

When you install products from your CD-ROM, configuration parameters are automatically entered in the registry. These parameters are read each time your Windows computer is started and whenever an Oracle Database product is started. These parameters include settings for:

• Oracle home directory

• Language

• Company name

• Oracle home subdirectories for individual products

• Individual products such as SQL*Plus

• Services

  
  

  See Also: Chapter 14, "Configuration Parameters and the Registry" for definitions of Oracle Database configuration parameters and specific instructions on using the registry to modify Oracle Database configuration parameters

  
  

2.4.5 Using Task Manager to Monitor Applications and Processes

Task Manager has three tabs:

• Applications tab displays what applications are running. This is useful for identifying and ending unresponsive tasks. (Oracle Database does not appear as an application because it runs as a service.)

• Processes tab displays details of currently running processes and their resource usage. Columns are customizable.

• Performance tab graphically displays real-time CPU and memory usage, which is useful for spotting sudden changes.

2.4.6 Using Local Users and Groups to Manage Users and Groups

Local Users and Groups enables you to manage users and groups on Windows 2000 and Windows XP. Specifically, you can:

• Create and modify local user accounts

• Create and modify user profiles

• Create, add, and delete local groups