Oracle® Database Platform Guide 11g Release 1 (11.1) for Microsoft Windows Part Number B32010-03 |
|
|
View PDF |
Oracle Database for Windows includes various tools to perform database functions. This chapter describes preferred tools to perform common database administration tasks and explains how tools can be started.
Unless otherwise noted, features described in this guide are common to Oracle Database Enterprise Edition, Oracle Database Standard Edition, Oracle Database Standard Edition One and Oracle Database Personal Edition.
This chapter contains these topics:
See Also:
Chapter 5, "Administering a Database on Windows" for OracleServiceSID configuration tasks
Chapter 6, "Monitoring a Database on Windows" for thread management tasks
Chapter 9, "Authenticating Database Users with Windows" for authentication tasks
Chapter 15, "Configuration Parameters and the Registry" and Chapter 6, "Monitoring a Database on Windows" for Oracle Counters for Windows Performance Monitor tasks
Oracle Database Installation Guide for Microsoft Windows for components available for installation
Database tools is a collective term for tools, utilities, and assistants that you can use to perform database administration tasks. Some database tools perform similar tasks, though no one database tool performs all database administration tasks. The following sections indicate which database tools can be used on particular operating systems and preferred tools to use for common database administration tasks.
Almost all database tools are available on all supported versions of Windows. The exceptions are:
Oracle Migration Workbench is available only at Oracle Technology Network (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.
Oracle Portal is not supported on Windows x64.
Oracle Objects for OLE is not supported on Windows x64.
Oracle Open Gateways is not supported on Windows x64.
Oracle Database Gateways is not supported on Windows x64.
SQL*Plus Worksheet is not supported on Windows x64.
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.
Note:
TheVOLSIZE
parameter for Export and Import utilities is not supported on Windows. If you attempt to use the utilities with the VOLSIZE
parameter, then error LRM-00101 occurs. For example:
D:\> exp system full=y volsize=100m;
Password: password
LRM-00101: unknown parameter name 'volsize'
EXP-00019: failed to process parameters, type 'EXP HELP=Y' for help
EXP-00000: Export terminated unsuccessfully
Table 2-1 Preferred Database Tools
Administration Task | Preferred Tool | Other Tools |
---|---|---|
Create a database on Windows 32-Bit system |
Database Configuration Assistant |
SQL*Plus or SQL*Plus Worksheet |
Create a database on Windows x64 system |
Database Configuration Assistant |
SQL*Plus |
Create database services |
Database Configuration Assistant |
ORADIM |
Delete database services |
Database Configuration Assistant |
ORADIM |
Start a database on Windows 32-Bit system |
Oracle Enterprise Manager Console |
SQL*Plus or SQL*Plus Worksheet |
Start a database on Windows x64 system |
Oracle Enterprise Manager Console |
SQL*Plus |
Shut down a database on Windows 32-Bit system |
Oracle Enterprise Manager Console |
Control Panel SQL*Plus or SQL*Plus Worksheet |
Shut down a database on Windows x64 system |
Oracle Enterprise Manager Console |
Control Panel SQL*Plus |
Change passwords in the database password file |
ORAPWD |
ORADIM |
Migrate database users to a directory |
User Migration Utility |
None |
Migrate a database on Windows 32-Bit system |
Oracle Database Upgrade Assistant |
Upgrade Information Tool |
Migrate a database on Windows x64 system |
Upgrade Information Tool |
None |
Upgrade a database on Windows 32-Bit system |
Oracle Database Upgrade Assistant |
Run provided scripts in SQL*Plus |
Upgrade a database on Windows x64 system |
Oracle Database Upgrade Assistant |
Run provided scripts in SQL*Plus |
Export data |
Data Pump Export (EXPDP) |
Export (EXP) |
Import data |
Data Pump Import (IMPDP) |
Import (IMP) |
Load data |
Oracle Enterprise Manager Load Wizard |
SQL*Loader (SQLLDR) |
Back up database |
Oracle Enterprise Manager Backup Wizard |
Recovery Manager (RMAN) OCOPY |
Recover database |
Oracle Enterprise Manager Recovery Wizard |
Recovery Manager (RMAN) OCOPY |
Authenticate database administrators and users on Windows 32-Bit system |
Oracle Enterprise Manager Console |
SQL*Plus or SQL*Plus Worksheet Windows operating system Oracle Administration Assistant for Windows |
Authenticate database administrators and users on Windows x64 system |
Oracle Enterprise Manager Console |
SQL*Plus Windows operating system Oracle Administration Assistant for Windows |
Store encrypted and decrypted Oracle Wallet (Oracle Advanced Security and Oracle PKI integration) |
Oracle Wallet Manager |
None |
Grant database roles |
Oracle Enterprise Manager Console |
Local Users and Groups User Manager SQL*Plus |
Create database objects |
Oracle Enterprise Manager Console |
SQL*Plus |
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 Database 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) and 2 (10.2). Oracle Database Upgrade Assistant can also be used to apply patch sets.
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 RAC 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.
Oracle Database for 64-bit Windows supports enterprise users and roles stored in Oracle Internet Directory, but administration tools must be run from a 32-bit computer.
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.
Oracle Enterprise Manager Grid Control can manage a 64-bit Windows database from a remote Linux or Windows 32-bit computer.
Oracle Enterprise Manager Console is not supported in Oracle Database for 64-bit Windows.
This section describes how to start each of the database tools in the following categories:
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 for a description of differences between Oracle homes in different releases.
Beginning in Oracle8i release 8.1.3, each Oracle home, including the first Oracle home you create on your computer, has a unique HOME_NAME. To start Oracle Administration Assistant for Windows from any Oracle home, from the Start menu, select Programs, then select Oracle - HOME_NAME, then select Configuration and Migration Tools and then select Administration Assistant for Windows.
To ensure that only trusted applications run on your computer, Windows Vista provides User Account Control. If you have enabled this security feature, then, depending on how you have configured it, Oracle Universal Installer prompts you for either your consent or your credentials when installing Oracle Database Client. Provide either the consent or your Windows Administrator credentials as appropriate.
You must have Administrator privileges to run some configuration tools, or to run any tool or application that writes to any directory within the Oracle home. If User Account Control is enabled, and you are logged in as the local Administrator, then you can successfully run each of these commands in the usual way. However, if you are logged in as a member of the Administrator group, then you must explicitly invoke these tasks with Windows Administrator privileges.
The following tools must be run with Administrator privileges:
Oracle Administration Assistant for Windows. This tool is available as a Configuration and Migration Tool.
Oracle Net Configuration Assistant. This tool is available as a Configuration and Migration Tool.
Oracle OLAP Analytic Workspace Manager and Worksheet. This tool is available as an Integrated Management Tool.
Oracle Database Configuration Assistant. This tool is available as a Configuration and Migration Tool.
Oracle Database Wallet Manager. This tool is available as an Integrated Management Tool.
Oracle Database Upgrade Assistant. This tool is available as a Configuration and Migration Tool.
Oracle Net Manager. This tool is available as a Configuration and Migration Tool.
To create a Windows shortcut with Windows Administrator privileges:
Click the Start menu button.
Navigate to Programs, then to Oracle - HOME_NAME.
Right-click the name of the command or application you want to run, then select Run as administrator.
To start a command prompt window with Windows Administrator privileges:
On your Windows Vista Desktop, create a shortcut for the command prompt window. An icon for that shortcut appears on the Desktop.
Right-click the icon for the newly created shortcut, and specify Run as administrator.
When you open this window, the title bar reads Administrator: Command Prompt. Commands run from within this window are run with Administrator privileges.
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.
Note:
When you use an assistant, you must have read and write access to the directory where database files will be moved or created. To create an Oracle Database, you must have an administrative privilege. If you run Database Configuration Assistant from an account that is not part of the Administrators group, then the tool exits without completing the operation.Note:
All Start Menu paths begin with the Start menu where you select Programs, then select Oracle - HOME_NAME and so on.Table 2-2 Starting Database Tools from the Start Menu
Tool | Start Menu Path | More Information |
---|---|---|
From Configuration and Migration Tools, select Administration Assistant for Windows |
Chapter 9, "Authenticating Database Users with Windows" |
|
From Configuration and Migration Tools, select Database Upgrade Assistant |
|
|
From Configuration and Migration Tools, select Database Configuration Assistant |
"Creating a Database on Windows Using Database Configuration Assistant" |
|
Enterprise Manager Console |
Oracle Enterprise Manager Console is available from the Start Menu only with Oracle Database Client. |
|
From Configuration and Migration Tools, select Locale Builder |
Oracle Database Globalization Support Guide |
|
From Configuration and Migration Tools, select Microsoft ODBC Administration |
Microsoft ODBC Administration online help |
|
From Configuration and Migration Tools, select Migration Workbench |
Oracle Migration Workbench software and documentation are available at http://www.oracle.com/technology/tech/migration/workbench/index.html |
|
From Configuration and Migration Tools, select Net Configuration Assistant |
Oracle Database Net Services Administrator's Guide |
|
From Configuration and Migration Tools, select Net Manager |
Oracle Database Net Services Administrator's Guide |
|
From Integrated Management Tools, select Oracle Directory Manager |
Oracle Internet Directory Administrator's Guide |
|
From Configuration and Migration Tools, select Oracle Counters for Windows Performance Monitor |
To install Oracle Counters for Windows Performance Monitor, choose Advanced Installation and then the Custom installation type. |
|
From Application Development, select SQL*Plus |
SQL*Plus User's Guide and Reference "Starting and Shutting Down a Database with SQL*Plus" |
|
From Application Development, select SQL*Plus Worksheet From Integrated Management Tools, select SQL*Plus Worksheet |
SQL*Plus Worksheet is available from the Start Menu only with Oracle Database Client. |
|
From Integrated Management Tools, select Wallet Manager |
Oracle Database Advanced Security Administrator's Guide |
Note:
After installing Oracle Database 11g Companion Products, Oracle Wallet Manager is not available from the Start menu. See Table 2-3, "Starting Database Tools from the Command Line" for instructions on starting Oracle Wallet Manager from the command line.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.
Table 2-3 Starting Database Tools from the Command Line
Tool | Enter at Prompt | More Information |
---|---|---|
|
Oracle Enterprise Manager Console is installed only with Oracle Database Client |
|
DBVERIFY starts and prompts you for a filename parameter. To obtain a list of parameters, enter:
|
|
|
EXPDP starts and prompts you for parameters. To obtain a list of these parameters, enter:
|
Oracle Database Utilities for instructions on use of Data Pump Export Oracle Database Error Messages for information on error messages |
|
IMPDP starts and prompts you for parameters. To get a list of these parameters, enter:
|
Oracle Database Utilities for instructions on use of Data Pump Import Oracle Database Error Messages for information on error messages |
|
Database Configuration Assistant |
DBCA wizard starts in interactive mode. For silent options and other command line options enter:
|
"Using DBCA" in Oracle Database 2 Day DBA |
Database Upgrade Assistant |
DBUA wizard starts in interactive mode. For silent options and other command line options enter:
|
"Using the Database Upgrade Assistant" in Oracle Database Upgrade Guide |
EXP starts and prompts you for parameters. To obtain a list of these parameters, enter:
|
Oracle Database Utilities for instructions on use of Export Oracle Database Error Messages for information on error messages |
|
Net Services Configuration |
NETCA wizard starts in interactive mode. For silent options and other command line options enter:
|
"Configuring the Network Environment" in Oracle Database Upgrade Guide |
Import |
IMP starts and prompts you for parameters. To get a list of these parameters, enter:
|
Oracle Database Utilities for instructions on use of Import Oracle Database Error Messages for information on error messages |
|
"Modifying Oracle Counters for Windows Performance Monitor Parameters" |
|
Oracle Wallet Manager |
|
Chapter 11, "Storing Oracle Wallets in the Windows Registry" |
To get a list of ORADIM options, enter either of the following:
|
"Using ORADIM to Administer an Oracle Database Instance" |
|
Password file is hidden. Use Windows Explorer to see it in a file list. From the View menu, select Options, then select View and then select Show All Files |
"Creating and Populating Password Files" |
|
|
Oracle Database Backup and Recovery Basics |
|
|
||
SQL*Loader displays a Help screen with available keywords and default values. |
||
|
||
User Migration Utility |
To get a list of parameters, enter:
|
"Using the User Migration Utility" in Oracle Database Advanced Security Administrator's Guide |
Note:
Three special conditions apply when running Export or Import utilities on Windows. First, default values forBUFFER
and RECORDLENGTH
parameters are 4 KB and 2 KB respectively. This default RECORDLENGTH
parameter does not depend on the value of BUFSIZ
defined in the system header file. If you specify a value larger than USHRT_MAX
(64 KB), you will get a warning message. Second, the VOLSIZE
parameter is not supported. Third, to export an entire database, you must use the EXP_FULL_DATABASE
role.Table 2-4 describes how to start each Windows tool and where to go for more information on using these products.
Table 2-4 Starting Windows Tools
Tool | Start Procedure | More Information |
---|---|---|
From the Start menu, select Programs, then select Administrative Tools and then select Event Viewer |
"Using Event Viewer to Monitor a Database" Your operating system documentation |
|
Microsoft Management Console (MMC)Note |
From the Start menu, select Programs, then select Oracle - HOME_NAME, then select Configuration and Migration Tools and then select Administration Assistant for Windows |
Your operating system documentation |
From the Start menu, select Programs, then select Oracle - HOME_NAME, then select Configuration and Migration Tools and then select Oracle Counters for Windows Performance Monitor |
"Using Oracle Counters for Windows Performance Monitor" Your operating system documentation |
|
At the command prompt, enter: C:\> regedit |
"Using Registry Editor to Modify Configuration Information" Chapter 15, "Configuration Parameters and the Registry" Your operating system documentation |
|
Right-click the Task Bar and select Task Manager |
"Using Task Manager to Monitor Applications and Processes" Your operating system documentation |
|
Local Users and Groups |
From the Start menu, select Settings, then select Control Panel. Double-click Administrative Tools. Double-click Computer Management. In the console tree, click Local Users and Groups. |
"Using Local Users and Groups to Manage Users and Groups" Your operating system documentation |
From the Start menu, select Programs, then select Administrative Tools and then select User Manager |
Chapter 9, "Authenticating Database Users with Windows" Your operating system documentation |
Note
MMC is started whenever Oracle Administration Assistant for Windows is started.
This section describes Windows-specific information for using SQL*Loader (SQLLDR
).
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.
Stream record format in which each record is terminated by a record terminator. If "str terminator_string" is not specified, then the record terminator defaults to either the Windows-style record terminator (the two-character sequence carriage return, \r, followed by line feed, \n) or the UNIX-style record terminator (single-character line feed, \n). Maximum record size is 48 KB.
When processing stream format data files, SQL*Loader can usually recognize record terminators automatically, whether they are Windows-style or UNIX-style. So you usually do not need to specify which record terminator you are using.
For external table loads, however, only Windows-style record terminators are recognized automatically. If your data file contains UNIX-style record terminators, you must specify the record terminator. If you are using SQL*Loader (with external_table
option), then specify the UNIX-style record terminator by specifying "str
'\n'"
on the INFILE
line in the SQL*Loader control file. For example:
INFILE mydata.dat "str '\n'"
You can also specify the record terminator in hex, by specifying "str x'0a'"
(assuming an ASCII-based character set). For example:
INFILE mydata.dat "str x'0a'"
Note that the "0
" (zero) before the "a
" is required. If you are using SQL with an access parameter list to create the external table, then you must specify '\n'
in a RECORDS
DELIMITED
BY
clause. For example:
RECORDS DELIMITED BY '\n'
You can also use a hex string in this case. For example:
RECORDS DELIMITED BY 0x'0a'
Note that in this case, the "0" (zero) before the "x" and the "0" (zero) before the "a" are both required.
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.
Table 2-5 Default Sizes of Native Datatypes
Native Datatypes | Default Field Length |
---|---|
DOUBLE |
8 |
FLOAT |
4 |
INTEGERNote |
4 |
SMALLINT |
2 |
Note
The default listed is correct if INTEGER
is specified without a size. But INTEGER(n)
is also allowed. In that case, n
specifies the size of the INTEGER
field in bytes.
See Also:
Oracle Database Utilities for a complete list of options and instructions on using SQL*LoaderYou can use Windows tools in various ways to manage Oracle 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 in the Event Log, which is viewable with the Event Viewer.
See Also:
Chapter 6, "Monitoring a Database on Windows" for specific instructions on accessing and using Event Viewer to monitor Oracle Database eventsMicrosoft 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 OracleService
SID
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
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.
See Also:
"Using Oracle Counters for Windows Performance Monitor" for specific instructions on accessing and using Oracle Counters for Windows Performance Monitor to monitor Oracle Database performanceOracle 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 media, 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 15, "Configuration Parameters and the Registry" for definitions of Oracle Database configuration parameters and specific instructions on using the registry to modify Oracle Database configuration parametersTask 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.
Local Users and Groups enables you to manage users and groups on Windows 2000, Windows XP, and Windows Vista. Specifically, you can:
Create and modify local user accounts
Create and modify user profiles
Create, add, and delete local groups
Windows Resource Kit includes several diagnostic and tuning utilities.
Process Viewer summarizes resource usage by a process.
Process Monitor displays real-time file systems, registry and process-thread activity. It combines Filemon
and Regmon
utilities and provides reliable process information.
Process Explorer provides a detailed display of resource usage by a process.
Task List displays resource usage and other details of a process when its processor identifier or process name is given as an argument. This tool also displays a list of executables and DLLs associated with a process.