| Oracle® Database Platform Guide 10g Release 1 (10.1) for Windows Part Number B10113-01 |
|
|
View PDF |
| Oracle® Database Platform Guide 10g Release 1 (10.1) for Windows Part Number B10113-01 |
|
|
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, and Oracle Database Personal Edition.
This chapter contains these topics:
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 Advanced Security includes Enterprise Security Manager Console and Oracle Wallet Manager. These are available only with Oracle Database Enterprise Edition and Oracle Database Personal Edition, and not Oracle Database Standard Edition. To learn whether they were installed or not, see your installActions.log at C:\program files\Oracle\Inventory\logs.
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.
Microsoft Management Console must be obtained from Microsoft Corporation for use with Windows NT 4.0.
Table 3-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 3-2, "Starting Database Tools from the Start Menu", 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/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 |
Table 3-1 Preferred Database Tools
| Administration Task | Preferred Tool | Other Tools |
|---|---|---|
| Create a database | Database Configuration Assistant |
SQL*Plus Worksheet |
| Delete database services | Database Configuration Assistant |
ORADIM |
| Start a database | Oracle Enterprise Manager Console | SQL*Plus or SQL*Plus Worksheet |
| Shut down a database | Oracle Enterprise Manager Console | Control Panel
SQL*Plus or SQL*Plus Worksheet |
| Change database passwords | ORAPWD | ORADIM |
| Migrate database users to a directory | User Migration Utility | None |
| Migrate a database | Oracle Database Upgrade Assistant | Migration Utility (MIG) |
| Upgrade a database | 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 | Oracle Enterprise Manager Console | SQL*Plus or SQL*Plus Worksheet
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 |
|
Note: The following refer to items in Table 4-7.
|
This section describes how to start each of the database tools in the following categories:
You will be referred back to this section for database tool startup procedures as you use this guide.
If you have multiple Oracle homes on your computer from previous releases, then see Chapter 2, " Multiple Oracle Homes and Optimal Flexible Architecture" for a description of differences between Oracle homes before and after Oracle Database 10g.
If you are using multiple Oracle homes, then the command to start a tool from any home other than the first includes a HOME_NAME, where HOME_NAME indicates the name of that Oracle home. The first Oracle home created on your computer does not have HOME_NAME appended to the group.
To start Oracle Administration Assistant for Windows from the first Oracle home, choose Start > Programs > Oracle > Configuration and Migration Tools > Administration Assistant for Windows.
To start Oracle Administration Assistant for Windows from an additional Oracle home, choose Start > Programs > Oracle - HOME_NAME > Configuration and Migration Tools > Administration Assistant for Windows.
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, choose Start > Programs > Oracle - HOME_NAME > Configuration and Migration Tools > Administration Assistant for Windows.
Table 3-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. All Start Menu paths begin with Start > Programs > Oracle - HOME_NAME >.
|
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 choose Start> Programs > Oracle - HOME_NAME >. |
Table 3-2 Starting Database Tools from the Start Menu
| Tool | Start Menu Path | More Information |
|---|---|---|
| Oracle Administration Assistant for Windows | Configuration and Migration Tools > Administration Assistant for Windows |
Chapter 9, " Authenticating Database Users with Windows" |
| Oracle Database Upgrade Assistant | Configuration and Migration Tools > Database Upgrade Assistant |
Oracle Database Upgrade Guide |
| Database Configuration Assistant | Configuration and Migration Tools > Database Configuration Assistant |
"Creating a Database on Windows Using Database Configuration Assistant" |
| Oracle Enterprise Manager Configuration Assistant | Configuration and Migration Tools > Enterprise Manager Configuration Assistant |
Oracle Enterprise Manager Grid Control Installation and Basic Configuration |
| Oracle Enterprise Manager Console | Enterprise Manager Console | Oracle 2 Day DBA |
| Oracle INTYPE File Assistant | Application Development > INTYPE File Assistant |
Oracle INTYPE File Assistant online help |
| Oracle Locale Builder | Configuration and Migration Tools > Locale Builder |
Oracle Database Globalization Support Guide |
| Microsoft ODBC Administrator | Configuration and Migration Tools > Microsoft ODBC Administration |
Microsoft ODBC Administration online help |
| Oracle Migration Workbench | Configuration and Migration Tools > Migration Workbench |
Oracle Migration Workbench Release Notes
Oracle Migration Workbench Reference Guide for Microsoft SQL Server and Sybase Adaptive Server Migrations Oracle Migration Workbench Reference Guide for Microsoft Access 2.0, 95, 97, 2000 Migrations |
| Oracle Net Configuration Assistant | Configuration and Migration Tools > Net Configuration Assistant |
Oracle Net Services Administrator's Guide |
| Oracle Net Manager | Configuration and Migration Tools > Net Manager |
Oracle Net Services Administrator's Guide |
| Oracle Directory Manager | Integrated Management Tools > Oracle Directory Manager |
Oracle Internet Directory Administrator's Guide |
| Oracle Counters for Windows Performance Monitor | Configuration and Migration Tools > Oracle Counters for Windows Performance Monitor |
"Using Oracle Counters for Windows Performance Monitor " |
| Pro*C/C++ | Application Development > Pro*C/C++ |
Pro*C/C++ Programmer's Guide |
| SQL*Plus | Application Development > SQL*Plus |
SQL*Plus User's Guide and Reference
"Starting and Shutting Down a Database with SQL*Plus" |
| SQL*Plus Worksheet | Application Development > SQL*Plus Worksheet
Integrated Management Tools |
Oracle Enterprise Manager Administrator's Guide |
| Oracle Wallet Manager | Integrated Management Tools > Wallet Manager |
Oracle Advanced Security Administrator's Guide |
Table 3-3 describes how to start Oracle Database tools from the command line, and where to go for further information on using these products.
Table 3-3 Starting Database Tools from the Command Line
| Tool | Enter at Prompt | More Information |
|---|---|---|
| Oracle Enterprise Manager Console | C:\> oemapp console |
Oracle 2 Day DBA |
| DBVERIFY | C:\> dbv
DBVERIFY starts and prompts you for a filename parameter. To obtain a list of parameters, enter:
|
Oracle Database Utilities |
| Data Pump Export | C:\> expdp username/password
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 |
| Data Pump Import | C:\> impdp username/password
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 |
| Export | C:\> exp username/password
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 |
| Import | C:\> imp username/password
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 |
| Migration Utility | C:\> mig
To obtain a list of parameters, enter:
|
Oracle Database Upgrade Guide |
| OPERFCFGver | C:\> OPERFCFGver |
"Modifying Oracle Counters for Windows Performance Monitor Parameters" |
| ORADIM | C:\> oradim options
To get a list of ORADIM options, enter either of the following:
|
"Using ORADIM to Administer an Oracle Database Instance" |
| Password Utility (ORAPWD) | C:\> orapwd
Password file is hidden. Use Windows Explorer to see it in a file list. Choose View > Options > View > Show All Files |
"Creating and Populating Password Files" |
| Recovery Manager (RMAN) | C:\> rman parameters |
Oracle Database Backup and Recovery Basics |
| SQL*Plus (SQLPLUS) | C:\> sqlplus |
SQL*Plus User's Guide and Reference |
| SQL*Loader (SQLLDR) | C:\> sqlldr
SQL*Loader displays a Help screen with available keywords and default values. |
Oracle Database Utilities |
| TKPROF | C:\> tkprof |
Oracle Database Performance Tuning Guide |
| User Migration Utility | C:\> umu parameters
To get a list of parameters, enter:
|
"Using the User Migration Utility" in Oracle 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 3-4 describes how to start each Windows tool and where to go for more information on using these products.
Table 3-4 Starting Windows Tools
| Tool | Start Procedure | More Information |
|---|---|---|
| Event Viewer | Choose Start > Programs > Administrative Tools > Event Viewer |
"Using Event Viewer to Monitor a Database"
Your Windows documentation |
| Microsoft Management Console (MMC)Foot 1 | Choose Start > Programs > Oracle - HOME_NAME > Configuration and Migration Tools > Administration Assistant for Windows |
Your Windows documentation |
| Oracle Counters for Windows Performance Monitor | Choose Start > Programs > Oracle - HOME_NAME > Configuration and Migration Tools > Oracle Counters for Windows Performance Monitor |
"Using Oracle Counters for Windows Performance Monitor "
Your Windows documentation |
| Registry Editor | At the command prompt enter:
C:\> regedt32 |
"Using Registry Editor to Modify Configuration Information"
Chapter 14, " Configuration Parameters and the Registry" Your Windows documentation |
| Task Manager | Right-click the Task Bar and choose Task Manager | "Using Task Manager to Monitor Applications and Processes"
Your Windows documentation |
| Local Users and Groups | Choose Start > Settings > 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 Windows documentation |
| User Manager | Choose Start > Programs > Administrative Tools > User Manager |
"Using User Manager to Manage Users"
Chapter 9, " Authenticating Database Users with Windows" Your Windows documentation |
This section describes Windows-specific information for using SQL*Loader (SQLLDR).
This section discusses possible values for the Operating System Dependent (OSD) file processing specifications string option, referred to in "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.
Fixed record format in which each record is exactly n bytes long. Record terminators are not required with fixed record format. If the record includes a record terminator, then the record terminator bytes are included in the number of bytes n.
Variable record format in which the length of each record in a character field is included at the beginning of each record in the datafile. Record terminators are not required with the variable record format. This format provides some added flexibility over the fixed record format and a performance advantage over the stream record format. You can specify a datafile that is to be interpreted as being in variable record format as follows:
INFILE "mydata.dat" "var n"
In this example, n specifies the number of bytes in the record length field. If n is not specified, SQL*Loader assumes a length of 5 bytes. Specifying n larger than 40 will result in an error. Lengths are always interpreted in bytes, even if character-length semantics are in effect for the file. This is necessary because the file could contain a mix of fields, some processed with character-length semantics and others processed with byte-length semantics.
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.
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.
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 3-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 3-5 Default Sizes of Native Datatypes
| Native Datatypes | Default Field Length |
|---|---|
| DOUBLE | 8 |
| FLOAT | 4 |
| INTEGERFoot 1 | 4 |
| SMALLINT | 2 |
|
See Also: Oracle Database Utilities for a complete list of options and instructions on using SQL*Loader |
You 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 to Event Viewer.
|
See Also: Chapter 7, " Monitoring a Database on Windows" for specific instructions on accessing and using Event Viewer to monitor Oracle Database events |
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
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 performance |
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 |
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.
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
User Manager enables you to manage Windows computer security and create user accounts on Windows NT. With User Manager, you can:
Grant Oracle Database roles
Use operating system authentication for user accounts (For example, you can grant DBA access to a Windows user.)
Create a Windows user account that enables you to make secure client connections to Oracle Database without a password
|
See Also: "Manually Administering External Users and Roles" for specific instructions on using User Manager to perform Oracle Database administration |
Windows 2000 Resource Kit includes several diagnostic and tuning utilities.
QuickSlice provides a quick overview of what is occurring on the system, using a graphical user interface.
Distinguishes between time spent in user mode and kernel mode
Imposes low overhead on the system (unlike Performance Monitor)
Shows a continuous display, rather than just a snapshot
Opens a window with more details when you double-click a process
Process Viewer summarizes resource usage by a process.
Process Explode 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.
|
See Also:
|
|
 Copyright © 1996, 2003 Oracle Corporation All Rights Reserved. |
|
