Oracle® Database
Platform Guide
11g Release 2 (11.2) for Microsoft Windows
E10845-08
February 2012
Oracle Database Platform Guide, 11g Release 2 (11.2) for Microsoft Windows
E10845-08
Copyright © 1996, 2012, Oracle and/or its affiliates. All rights reserved.
Primary Author: Ashmita Bose
Contributing Author: Namrata Bhakthavatsalam, Reema Khosla, Lance Ashdown
Contributors: Beldalker Anand, Adam Bentley, Ricky Chen, David Collelo, David Friedman, Janelle Simmons, Sue K. Lee, Rich Long, Satish Panchumarthy, Ravi Thammaiah, Michael Verheij
This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is prohibited.
The information contained herein is subject to change without notice and is not warranted to be error-free. If you find any errors, please report them to us in writing.
If this is software or related documentation that is delivered to the U.S. Government or anyone licensing it on behalf of the U.S. Government, the following notice is applicable:
U.S. GOVERNMENT RIGHTS Programs, software, databases, and related documentation and technical data delivered to U.S. Government customers are "commercial computer software" or "commercial technical data" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, the use, duplication, disclosure, modification, and adaptation shall be subject to the restrictions and license terms set forth in the applicable Government contract, and, to the extent applicable by the terms of the Government contract, the additional rights set forth in FAR 52.227-19, Commercial Computer Software License (December 2007). Oracle America, Inc., 500 Oracle Parkway, Redwood City, CA 94065.
This software or hardware is developed for general use in a variety of information management applications. It is not developed or intended for use in any inherently dangerous applications, including applications that may create a risk of personal injury. If you use this software or hardware in dangerous applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure its safe use. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software or hardware in dangerous applications.
Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
Intel and Intel Xeon are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks are used under license and are trademarks or registered trademarks of SPARC International, Inc. AMD, Opteron, the AMD logo, and the AMD Opteron logo are trademarks or registered trademarks of Advanced Micro Devices. UNIX is a registered trademark of The Open Group.
This software or hardware and documentation may provide access to or information on content, products, and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to third-party content, products, and services. Oracle Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of third-party content, products, or services.
PK̓DC > PK 6A OEBPS/monitor.htm
6 Monitoring a Database on Windows
This chapter describes how to monitor Oracle Database for Windows.
This chapter contains these topics:
Database Monitoring Overview
Table 6-1 describes tools that enable you to monitor Oracle Database.
Using Oracle Counters for Windows Performance Monitor
Oracle Counters for Windows Performance Monitor is a graphical tool for measuring the performance of Oracle Database for Windows objects on a local server or other servers on a network. This tool is the same in appearance and operation as Windows Performance Monitor, except it has been loaded with Oracle Database performance elements.
On each computer, you can view behavior of objects, such as buffer cache, data dictionary cache, datafiles, threads, and processes. An object is a graphical representation of an element in your system. Every element, resource, and device in your system can be represented as an object.
A set of counters is associated with each object. A counter is a unit of measurement used by Performance Monitor to display activity. The type of activity the counter measures depends on the type of object.
Certain types of objects and their respective counters are present on all systems. Other counters, such as application-specific counters, appear only if the computer is the associated software.
Each of these objects has an associated set of counters that provide information about device usage, queue lengths, delays, and information used to measure throughput and internal congestion.
Registry Information
When you install Oracle Counters for Windows Performance Monitor, values are automatically set in the registry as described in Chapter 15, "Configuration Parameters and the Registry".
Oracle Counters for Windows Performance Monitor lets you monitor only one database instance at a time. For this reason, the registry contains the following values:
-
Hostname
-
Username
-
Password
Use operfcfg to change these values. Oracle recommends setting the security level on each of these registry values.
To use Oracle Counters for Windows Performance Monitor for another database instance on the same computer or a UNIX computer, change the values appropriately in the registry. You can also monitor non-Windows installations of Oracle Database by changing Hostname registry value so it points to another computer specified in file tnsnames.ora.
Accessing Oracle Counters for Windows Performance Monitor
To access Oracle Counters for Windows Performance Monitor:
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.
Oracle Counters for Windows Performance Monitor has four views you can choose from the View menu:
-
Chart view displays database activity as it occurs.
-
Alert view lets you know when certain minimum performance criteria are not being met, or maximum criteria are being exceeded.
-
Log view maintains continuous records on performance.
-
Report view saves information about specific criteria.
|
See Also:
Your operating system documentation for complete information about the four views |
Modifying Views
For each view you can decide objects to monitor, what counters to use for each object, and graphical attributes of the view. When an object is chosen, it is assigned a counter, a color, and added to the status bar at the bottom of Oracle Counters for Windows Performance Monitor.
To add objects to a view:
-
Choose Add To (Chart, Alert, Log, Report) from the Edit menu. (This example uses Add to Chart, but other dialogs are similar.)
The Add to Chart dialog appears.
-
In the Computer list, select the computer to monitor.
-
In the Object list, select the object or objects to monitor, then click Add.
If no data or Oracle Database objects appear, then either Oracle Database is not or an invalid host string or password has been entered. If the database is not started, then exit Oracle Counters for Windows Performance Monitor, start the database, and restart Oracle Counters for Windows Performance Monitor.
-
In the Counter list, select one or more counters for each object you have selected. Note that the contents of the Counter list change depending upon your selection in the Object list. For details on how a counter works, highlight the counter and click Explain.
-
In the Instance list, select an instance for this counter.
-
In the Color list, choose a color for the display of the selected counter.
-
In the Scale list, choose the scale at which to display the counter.
-
In the Width list, specify the width of the line on the graph.
-
In the Style list, choose a style for your graph line.
-
Click Done when you are finished.
The selections you have chosen to monitor are displayed.
Understanding Oracle Database Performance Objects
All Oracle Database system resources that can be monitored through Oracle Counters for Windows Performance Monitor begin with 'Oracle Database'. This section lists Oracle Database objects and describes their associated counters. These measures are defined in:
ORACLE_HOME\dbs\PERFver.ora
|
Note:
You can monitor only one instance at a time using Oracle Counters for Windows Performance Monitor on a given computer. |
Oracle Database Buffer Cache
The counter is phyrds/gets %. The percentage of phyrds/gets is calculated as a Miss ratio. The lower the Miss counter, the better. To improve performance, increase the number of buffers in the buffer cache, if memory is available on the computer. This value is not time-derived.
Oracle Database Redo Log Buffer
The counter is redo log space requests. The value of this counter must be near zero. If this value increments consistently, then processes have had to wait for space in the redo log buffer. In this case, it may be necessary to increase the size of the redo log buffer.
Oracle Database Data Dictionary Cache
The counter is getmisses/gets %. The value of this counter must be less than 10 or 15% for frequently accessed data dictionary caches. If the ratio continues to increase over this threshold while your application is, then increase the amount of memory available to the data dictionary cache.
To increase the memory available to the cache, increase the value of initialization parameter SHARED_POOL_SIZE. This value is not time-derived.
Oracle Database Library Cache
The counter is reloads/pins %. This is the percentage of SQL statements, PL/SQL blocks, and object definitions that required reparsing. Total Reloads must be near zero. If the ratio of Reloads to Pins is greater than 1%, then reduce the library cache misses. This value is not time-derived.
Oracle Database Datafiles
The counters are phyrds/sec and phywrts/sec. These values are time-derived. Disk contention occurs when multiple processes try to access the same disk simultaneously. Depending on results from monitoring disk activity, corrective actions could include:
Oracle Database DBWR stats1
The two counters available, buffers scanned/sec and LRU scans/sec, are helpful in tuning Buffer Cache. Buffers scanned/sec is the number of buffers DBWR scanned in each second. The buffers scanned are on the LRU (Least Recently Used) list. LRU scans/sec is the number of times DBWR scanned the (Least Recently Used) buffer list in each second.
Oracle Database DBWR stats2
The two counters available, timeouts/sec and checkpoints/sec, are helpful in determining how much work DBWR has been requested to perform. Timeouts/sec is the number of times DBWR timed-out in each second. DBWR is on a three second timeout interval. If DBWR has not been posted within a three second interval, then it times out.
Checkpoints/sec is the number of checkpoint messages processed by database writer in each second. Whenever a checkpoint occurs, DBWR must be messaged (posted) to "write dirty buffers to disk".
Oracle Database Dynamic Space Management
The counter is recursive calls/sec. Dynamic extension causes Oracle Database to execute SQL statements in addition to those SQL statements issued by user processes. These SQL statements are called recursive calls.
If Oracle Database makes excessive recursive calls while an application is, then it may be necessary to determine the cause. Examine the recursive calls statistic through dynamic performance table V$SYSSTAT.
Oracle Database Free List
The counter is free list waits/requests %. Contention for free lists is reflected by contention for free data blocks in buffer cache. You can determine if contention for free lists is reducing performance by querying V$WAITSTAT.
If the number of free list waits for free blocks is greater than 1% of the total number of requests, then consider adding more free lists to reduce contention.
Oracle Database Sorts
The available counters are sorts in memory/sec and sorts on disk/sec. The default sort area size is adequate to hold all data for most sorts. However, if your application often performs large sorts on data that does not fit into the sort area, then you may increase sort area size.
Oracle Counters for Windows Performance Monitor Troubleshooting Information
If no data or Oracle Database objects appear in the Objects list of the Add to (Chart, Alert, Log, Report) dialog, then:
-
Ensure that Oracle Database is. If it is not, then exit Oracle Counters for Windows Performance Monitor, and start the database.
-
If the database is but no data or Oracle Database objects appear, then check the error file for Oracle Counters for Windows Performance Monitor:
ORACLE_HOME\dbs\OPERFver.log
If the log file indicates an invalid host string or password, then check the registry for correct values for Hostname, Password, and Username. See "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services" for further information about these values.
-
Restart Oracle Counters for Windows Performance Monitor.
Using Event Viewer
Oracle Database for Windows problems and other significant occurrences are recorded as events in an application event log. View and manage these recorded events in Event Viewer.
To access Event Viewer:
-
From the Start menu, select Programs, then select Administrative Tools and then select Event Viewer.
The Event Viewer window appears.
-
Choose Application from the Log menu.
Figure 6-1 displays the Application view window, Table 6-2 shows what is recorded in each column, and Table 6-3 interprets icons that appear at the left edge of the viewer.
Reading Event Viewer
Oracle Database for Windows events display with a source of Oracle.SID.
Event number 34 specifies an audit trail event. These events are recorded if parameter AUDIT_TRAIL is set to db (true) or os in the initialization parameter file. Option os enables system wide auditing and causes audited records to be written to Event Viewer. Option db enables system wide auditing and causes audited records to be written to the database audit trail (table SYS.AUD$). Some records, however, are written to Event Viewer.
Event numbers other than 34 specify general database activities, such as an instance being started or stopped.
When you double-click an icon in Event Viewer, the Event Detail dialog appears with more information about the selected event. Figure 6-2, for example, shows details about EventID 1011. In the Description field you will find a text description of the event. In the Data field you can choose Bytes to see the information in hexadecimal format or Words to see DWORDS for the same data.
|
See Also:
Microsoft operating system documentation for more information on using Event Viewer |
Managing Event Viewer
Setting AUDIT_TRAIL to db or os causes more records to be written to Event Viewer. This can fill up the Event Viewer log file. Follow these procedures to increase log file size:
-
Choose Log Settings from the Log menu.
The Event Log Settings dialog appears.
-
Adjust the setting in the Maximum Log Size field to an appropriate level.
-
Click OK.
You are returned to Event Viewer.
|
Caution:
Audit information cannot be spooled to a file. Parameter AUDIT_FILE_DEST is not supported in Windows and should not be added to the initialization parameter file. |
Trace Files
Oracle Database for Windows background threads use trace files to record occurrences and exceptions of database operations, and errors. Background thread trace files are created and stored in the ADR directory specified by parameter DIAGNOSTIC_DEST in the initialization parameter file.
Oracle Database creates a different trace file for each foreground and background thread. The name of the trace file contains the name of the thread, followed by the extension ".trc". The following are examples of foreground trace file names:
-
ops_ora_5804.trc
-
ops_ora_4160.trc
The following are examples of background trace file names:
-
ops_pmon_1556.trc
-
ops_mmon_3768.trc
-
ops_lgwr_2356.trc
-
ops_dbw0_132.trc
Trace files arem also created for user threads and stored in the ADR directory specified by parameter DIAGNOSTIC_DEST in the initialization parameter file. Trace files for user threads have the form oraxxxxx.trc, where xxxxx is a 5-digit number indicating the Windows thread ID.
Alert Logs
Alert logs contain important information about error messages and exceptions that occur during database operations. Each Oracle Database for Windows instance has one alert log; information is appended to the file each time you start the instance. All threads can write to the alert log.
For example, when automatic archiving of redo logs is halted because no disk space is available, a message is placed in the alert log. The alert log is the first place to check if something goes wrong with the database and the cause is not immediately obvious.
The alert log is named alert_SID.log and is found in the ADR directory specified by parameter DIAGNOSTIC_DEST in the initialization parameter file. Alert logs should be deleted or archived periodically.
Viewing Threads
To view information on Oracle Database threads using Oracle Administration Assistant for Windows, you must either enable Windows native authentication for the database or run utility ocfgutil.exe with arguments username and password. The utility stores the user name and password in the following registry location:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\OracleOraConfig
When Windows native authentication is not enabled, Oracle Remote Configuration Agent retrieves the user name and password from this registry key to log in to the database.
To view information on Oracle Database threads using Oracle Administration Assistant for Windows:
-
From the Start menu, select Programs, then select Oracle - HOME_NAME, then select Configuration and Migration Tools and then select Administration Assistant for Windows.
-
Right-click the SID, where SID is a specific instance name, such as orcl.
-
Choose Process Information.
The Process Information dialog appears, listing name, type, user, thread ID, and CPU usage for each Oracle Database thread.
-
To terminate a thread, select it and click Kill Thread.
Description of the illustration procinfo.gif
PKtCv PK 6A OEBPS/create.htm
3 Postinstallation Database Creation on Windows
This chapter describes how to create a database after installing Oracle Database, using either Database Configuration Assistant or command-line tools.
This chapter contains these topics:
Naming Conventions for Oracle Database
All mounted Oracle Database servers in a network must have unique database names. When a database is created, a name is associated with it and stored in its control files. If you provide the database keyword, either in the CREATE DATABASE statement or when prompted by Database Configuration Assistant, then that value becomes the name for that database.
If you attempt to mount two Oracle Database servers with the same database name, then you receive the following error during mounting of the second server:
ORA-01102: cannot mount database in EXCLUSIVE mode
If there are two or more Oracle Database servers on the same computer, but located in different Oracle homes, then the following rules apply:
To change the name of an existing database, you must use the CREATE CONTROLFILE statement to re-create your control files and specify a new database name.
Creating a Database on Windows Using Database Configuration Assistant
Oracle recommends you use Database Configuration Assistant to create a database, because it is easier. It offers the same interface and operates the same way on all supported platforms, so no step-by-step procedures or screen shots are included here.
Database Configuration Assistant enables you to:
An initialization parameter file is an ASCII text file containing parameters. Use this file to create and modify a database using command-line tools. When you create a database using Database Configuration Assistant, a server parameter file (SPFILE) is created from the initialization parameter file, and the initialization parameter file is renamed. Oracle does not recognize the renamed file as an initialization parameter file, and it is not used after the instance is started.
If you want to modify an instance created with Database Configuration Assistant after it starts, you must use ALTER SYSTEM statements. You cannot change the Server Parameter File itself, because it is a binary file that cannot be browsed or edited using a text editor. The location of the newly-created Server Parameter File is ORACLE_HOME\database. The Server Parameter File file name is spfileSID.ora.
Creating a Database on Windows Using Command-Line Tools
This section describes how to create a new database manually. As part of its database software files, Oracle Database provides a sample initialization parameter file, which can be edited to suit your needs. You can choose to create database creation scripts using DBCA.
Database creations are of three types:
-
Copy an existing database and delete the old database.
-
Copy an existing database and keep the old database.
-
Create a new database when no database exists on your system.
Table 3-1 summarizes tasks involved in creating a new database for each of these database creation categories. Each step is explained in detail in the following subsections.
Note 1
Yes if you copy data from the existing database to the new database; no otherwise.
Note 2
Yes if you import tables and other objects exported from the existing database; not otherwise.
We use an example in the following sections to demonstrate how to create a database. In this example, the existing database is the starter database with a SID of orcl located in directory C:\app\username\product\11.2.0\oradata\orcl. You will copy orcl to a new database with a database name and SID of prod located in directory C:\app\username\product\11.2.0\oradata\prod. You will then delete starter database orcl.
Exporting an Existing Database
You are required to export an existing database only if you intend to copy its contents to a new database. If you are working with data from an earlier Oracle release, then you can use Export for this task. If you are using Oracle Database 10g Release 1 (10.1) or later data, then Oracle recommends that you use Data Pump Export because it supports new Oracle Database 10g Release 1 (10.1) or later features, such as floating points.
Although you can start Data Pump Export or Export in either parameter mode or interactive mode, Oracle recommends parameter mode. Interactive mode provides less functionality than parameter mode and exists for backward compatibility only.
The syntax for Data Pump Export parameter mode is:
C:\> expdp SYSTEM DUMPFILE=myexp.dmp FULL=y LOGFILE=myexp.log
Password: password
The syntax for Data Pump Export interactive mode is:
C:\> expdp SYSTEM
Password: password
Enter only the command expdp SYSTEM to begin an interactive session and let Data Pump Export prompt you for information it needs.
|
Note:
If you use parameter mode, then Data Pump Export considers file names and directory names to be invalid if they contain one or more blank spaces. The workaround is to enclose the full path in the DUMPFILE= parameter in triple quotation marks. For example:
DUMPFILE="""C:\program files\export.dmp"""
If Data Pump Export is used in interactive mode, then the file name or directory name can contain a space without quotation marks.
|
The syntax for Export parameter mode is:
C:\> exp SYSTEM FILE=myexp.dmp FULL=y LOG=myexp.log
Password: password
The syntax for Export interactive mode is:
C:\> exp SYSTEM
Password: password
Enter only the command exp SYSTEM to begin an interactive session and let Export prompt you for information it needs.
|
Note:
If you use parameter mode, then Export considers file names and directory names to be invalid if they contain one or more blank spaces. The workaround is to enclose the full path in the FILE= parameter in triple quotation marks. For example:
FILE="""C:\program files\export.dmp"""
If Export is used in interactive mode, then the file name or directory name can contain a space without quotation marks.
|
To export all data from an existing database to a new database:
-
Set ORACLE_SID to the database service of the database whose contents you intend to export. For example, if the database you intend to export is starter database orcl, then enter the following at the command prompt. Note that there are no spaces around the equal sign (=) character.
C:\> set ORACLE_SID=orcl
-
If the existing database is Oracle Database 10g Release 1 (10.1) or later, then start Data Pump Export from the command prompt:
C:\> expdp SYSTEM DUMPFILE=myexp.dmp FULL=y LOG=myexp.log
Password: password
You now have a full database export of starter database orcl in file myexp.dmp. All messages from Data Pump Export are logged in file myexp.log.
-
If the existing database is before Oracle Database 10g Release 1 (10.1), then start Export from the command prompt:
C:\> exp SYSTEM FILE=myexp.dmp FULL=y LOG=myexp.log
Password: password
You now have a full database export of starter database orcl in file myexp.dmp. All messages from Export are logged in file myexp.log.
Deleting Database Files
Deleting database files is required only when you copy an existing database to a new database to replace the old database. In the following example, you delete the database files of starter database orcl.
To delete database files:
-
Shut down starter database orcl at the command prompt:
C:\> oradim -SHUTDOWN -SID orcl -SHUTTYPE inst -SHUTMODE immediate
-
Delete the following files from directory C:\app\username\product\11.2.0\oradata\orcl:
Modifying the Initialization Parameter File
To use starter database orcl as the basis for your new database, first copy ORACLE_BASE\admin\orcl\pfile\init.ora. Second, put the copy in ORACLE_BASE\admin\prod\pfile\init.ora. Third, modify the file as described in this section.
|
Note:
Beginning with Oracle9i Release 2 (9.2), nesting of quotation marks using the backslash (\) escape character is no longer supported. This may affect how Oracle Database interprets parameter values in your initialization parameter file. For example, if you specified CONTROL_FILES = "ctlfile\'1.ora" in releases before release 9.2, the file name would be interpreted as ctlfile'1.ora. Starting with release 9.2, the file name would be interpreted as ctlfile\'1.ora.
Oracle highly recommends modifying your parameter files to remove such references. See Oracle Database Reference for other methods of nesting quotation marks in initialization parameter values.
|
If you do not have an existing database on your system, then you cannot copy an existing initialization parameter file to use as the basis for your new initialization parameter file. However, you can use the sample initialization parameter file initsmpl.ora provided in
ORACLE_HOME\admin\sample\pfile
as the basis for the initialization parameter file for database prod.
If you use initsmpl.ora as the basis for the initialization parameter file, then you must set the following parameters to the indicated values, or you will not be able to start database prod:
-
DB_NAME=prod.domain
Parameter DB_NAME indicates the database name and must match the name used in the CREATE DATABASE statement in "Putting the CREATE DATABASE Statement in a Script". You give a unique database name to each database. You can use up to eight characters for a database name. The name is not required to match the SID of the database service.
-
INSTANCE_NAME=prod.domain
-
SERVICE_NAMES=prod.domain
-
CONTROL_FILES = ( "C:\app\username\product\11.2.0\oradata\prod\control01.ctl", "C:\app\username\product\11.2.0\oradata\prod\control02.ctl", "C:\app\username\product\11.2.0\oradata\prod\control03.ctl")
Parameter CONTROL_FILES lists database control files. You do not have control files on your file system at this point, because control files are created when you run the CREATE DATABASE statement. Ensure that you specify the complete path and file name, including drive letter.
-
DB_FILES=100
Modifying initialization parameter DB_FILES is not required, but it is recommended to optimize performance. Set this parameter to the same number as the value of the MAXDATAFILES option of the CREATE DATABASE statement. The value of 100 is used for this example.
The DIAGNOSTIC_DEST initialization parameter sets the location of the Automatic Diagnostic Repository (ADR), which is a directory structure stored outside of the database. The ADR is used in problem diagnostics.
DIAGNOSTIC_DEST = ORACLE_HOME\log if the environment variable ORACLE_BASE is not set.
DIAGNOSTIC_DEST = ORACLE_BASE variable if the environment variable ORACLE_BASE is set.
Creating and Starting an Oracle Database Service
You are required to create and start an Oracle Database service only if you do one of the following:
Before you create the database, first create a Windows service to run the database. This service is the Oracle Database process, oracle.exe, installed in the form of a Windows service.
Use ORADIM to create the service. After it has been created, the service starts automatically. See "Using ORADIM to Administer an Oracle Database Instance" for information on how to use ORADIM.
To create and start an Oracle Database service:
-
Run ORADIM from the command prompt:
C:\> oradim -NEW -SID prod -STARTMODE manual
-PFILE "C:\app\oracle\product\11.2.0\admin\prod\pfile\init.ora"
Note that the previously created initialization parameter file is specified, with complete path, including drive name. You can check if the service is started in the Services window of the Control Panel.
-
Set ORACLE_SID to equal prod. Note that there are no spaces around the equal sign (=) character:
C:\> set ORACLE_SID=prod
Starting an Oracle Database Instance
Start an instance without mounting a database.
SQL> STARTUP NOMOUNT
You are not required to specify the PFILE clause in this example, because the initialization parameter file is stored in the default location. At this point, there is no database. Only the SGA is created and background processes are started in preparation for the creation of a new database.
Putting the CREATE DATABASE Statement in a Script
The CREATE DATABASE statement is a SQL statement that creates the database. A script containing this statement can be used anytime you create a database.
The CREATE DATABASE statement may have the following parameters:
-
MAXDATAFILES - default value: 32, maximum value: 65534
-
MAXLOGFILES - default value: 32, maximum value: 255
When you run a CREATE DATABASE statement, Oracle Database performs several operations depending upon clauses that you specified in the CREATE DATABASE statement or initialization parameters that you have set.
|
Note:
Oracle Managed Files is a feature that works with the CREATE DATABASE statement to simplify adm inistration of Oracle Database. Oracle Managed Files eliminates the requirement to directly manage operating system files comprising an Oracle Database server, because you specify operations in terms of database objects rather than file names. For more information on using Oracle Managed Files see Oracle Database Administrator's Guide. |
To create database prod, copy and save the following statement in a file named script_name.sql:
CREATE DATABASE prod
USER SYS IDENTIFIED BY sys_password
USER SYSTEM IDENTIFIED BY system_password
MAXLOGFILES 5
MAXDATAFILES 100
DATAFILE 'C:\app\oracle\product\11.2.0\oradata\prod\system01.dbf' SIZE 325M REUSE
AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
UNDO TABLESPACE "UNDOTBS" DATAFILE 'app\oracle\product\11.2.0\oradata\prod\undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
CHARACTER SET WE8MSWIN1252
logfile 'C:\app\username\product\11.2.0\oradata\prod\redo01.log' size 100M reuse,
'C:\app\username\product\11.2.0\oradata\prod\redo02.log' size 100M reuse,
'C:\app\username\product\11.2.0\oradata\prod\redo03.log' size 100M reuse
EXTENT MANAGEMENT LOCAL;
the CREATE DATABASE Script
To use the SQL script to create a database:
-
Verify that the service is started in the Control Panel. In this example, the service name is OracleServicePROD, and its status column must display Started. If not, then select the service name and choose Start.
You can also check the status of the service by entering the following at the command prompt:
C:\> net START
A list of all Windows services currently on the system appears. If OracleServicePROD is missing from the list, then enter:
C:\> net START OracleServicePROD
-
Make PROD the current SID:
C:\> set ORACLE_SID=PROD
-
Add ORACLE_HOME\bin to your PATH environment variable:
set PATH=ORACLE_BASE\ORACLE_HOME\bin;%PATH%
-
Start SQL*Plus from the command prompt, and connect to the database as SYSDBA:
C:\> sqlplus /NOLOG
SQL> CONNECT / AS SYSDBA
The message connected appears.
-
Turn on spooling to save messages:
SQL> SPOOL script_name.log
-
Run script script_name.sql that you created in "Putting the CREATE DATABASE Statement in a Script":
SQL> C:\app\oracle\product\11.2.0\dbhome_1\rdbms\admin\script_name.sql;
If the database is successfully created, then the instance is started and the following message appears numerous times: Statement processed
Importing a Database
You can use Data Pump Import (for Oracle Database 10g Release 1 (10.1) or later data) or Import (for earlier data) to import the full export created in "Exporting an Existing Database" into the new database. Although you can start Data Pump Import or Import using either parameter mode or interactive mode, Oracle recommends parameter mode because it provides more functionality. Interactive mode exists solely for backward compatibility.
The syntax for Data Pump Import parameter mode is:
C:\> impdp SYSTEM DUMPFILE=myexp.dmp FULL=y LOG=myexp.log
Password: password
The syntax for Data Pump Import interactive mode is:
C:\> impdp SYSTEM
Password: password
Enter only impdp SYSTEM to begin an interactive session and let Data Pump Import prompt you for information it needs.
|
Note:
-
If you use parameter mode, then Data Pump Import considers file names and directory names to be invalid if they contain one or more blank spaces. The workaround is to enclose the full path in the DUMPFILE= parameter in triple quotation marks. For example:
DUMPFILE="""C:\program files\export.dmp"""
If you use Data Pump Import in interactive mode, then the file name or directory name can contain a space without quotation marks.
-
If the original database from which the export file was generated contains a tablespace that is not in the new database, then Import tries to create that tablespace with associated datafiles.
The easy solution is to ensure that both databases contain the same tablespaces. Datafiles are not required to be identical. Only tablespace names are important.
|
Updating ORACLE_SID in the Registry
If this is the first database on the system or if you intend to make the new database the default database, then you must make a change in the registry.
-
Start Registry Editor at the command prompt:
C:\> regedit
The Registry Editor window appears.
-
Choose subkey \HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0 for the first Oracle home on your computer. For subsequent installations to different Oracle homes on the same computer, the path is \HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEID where ID is the unique number identifying the Oracle home.
-
Locate parameter ORACLE_SID on the right side of the Registry Editor window.
-
Double-click the parameter name and change the data to the new SID, which is prod in this example.
If you do not yet have parameter ORACLE_SID, because this is the first database on your system, then you must create it.
To create parameter ORACLE_SID:
-
Choose Add Value from the Edit menu.
The Add Value dialog appears:
Description of the illustration addvalue.gif
-
Enter ORACLE_SID in the Value Name field.
-
Select REG_EXPAND_SZ (for an expandable string) in the Data Type list.
-
Click OK.
A string editor dialog appropriate for the data type appears:
Description of the illustration stringre.gif
-
Enter prod in the String field.
-
Click OK.
Registry Editor adds parameter ORACLE_SID.
-
Choose Exit from the Registry menu.
Registry Editor exits.
Backing Up the New Database
|
Caution:
If anything goes wrong while operating the new database without a backup, then you must repeat the database creation procedure. Back up your database now to prevent loss of data. |
To back up the new database:
-
Shut down the database instance and stop the service:
C:\> oradim -SHUTDOWN -SID prod -SHUTTYPE srvc,inst -SHUTMODE immediate
|
Caution:
Although ORADIM returns the prompt immediately, you must wait for the database and the service to stop completely before continuing to Step 2. Wait until the Control Panel indicates service OracleServicePROD has stopped. If you do not do this, then the backup may be useless because it was taken while data was being written to datafiles. |
-
Using the tool of your choice, back up database files.
Database files consist of the initialization parameter file, control files, online redo log files, and datafiles.
When the backup is complete, you can start the database again, create users and objects, if necessary, make any other changes, and use the database.
Be sure to back up the database after making any significant changes, such as switching archiving mode or adding a tablespace or datafile.
|
See Also:
For more information on archiving, backup, and recovery
|
|
Caution:
Do not store database files on a compressed drive. This can result in write errors and decreased performance. |
Using ORADIM to Administer an Oracle Database Instance
ORADIM is a command-line tool that is available with Oracle Database. You are required to use ORADIM only if you are manually creating, deleting, or modifying databases. Database Configuration Assistant is an easier tool to use for this purpose.
The following sections describe ORADIM commands and parameters. Note that each command is preceded by a dash (-). To get a list of ORADIM parameters, enter:
oradim -? | -h | -help
|
Note:
Specifying oradim without any options also returns a list of ORADIM parameters and descriptions. |
When you use ORADIM, a log file called oradim.log opens in ORACLE_HOME\database, or in the directory specified by registry parameter ORA_CWD. All operations, whether successful or failed, are logged in this file. You must check this file to verify success of an operation.
If you have installed an Oracle Database service on Windows, then when logging in as SYSTEM user (LocalSystem), with startup mode set to Automatic, it is possible that the Oracle Database service starts but the database does not start automatically. The following error message is written to file ORADIM.LOG in directory ORACLE_HOME\database:
ORA-12640: Authentication adapter initialization failed
Oracle Enterprise Management Agent, Oracle Enterprise Manager Management Server and Oracle Internet Directory may also fail, because they cannot connect to the database for the same reason. The workarounds are:
-
Modify SQLNET.ORA
You can modify SQLNET.ORA, either by removing the line
sqlnet.authentication_services=(NTS)
or by changing it to
sqlnet.authentication_services=(NONE)
-
Start the database after the service starts
You can start the database manually after the Oracle Database service has started, using SQL*Plus and connecting as SYSDBA.
-
Start the service as a specific user
|
See Also:
Your operating system documentation for instructions on starting services |
Creating an Instance
To use ORADIM to create an instance, enter:
oradim [-NEW -SID SID] | -SRVC service_name | -ASMSID SID | -ASMSRVC service_name
[-SYSPWD password] [-STARTMODE auto | manual] [-SRVCSTART system | demand]
[-PFILE filename | -SPFILE] [-SHUTMODE normal | immediate | abort] [-TIMEOUT
secs] [-RUNAS osusr/ospass]
where
-
-NEW indicates that you are creating a new instance. This is a mandatory parameter.
-
-SID SID is the name of the instance to create.
-
-SRVC service_name is the name of the service to create (OracleServiceSID).
-
-ASMSID SID is the name of the Automatic Storage Management instance to create.
-
-ASMSRVC service_name is the name of the Automatic Storage Management service to create.
-
-SYSPWD password is the system password.
-
-STARTMODE auto | manual indicates whether to start the instance when the Oracle Database service is started. Default is manual.
-
-SRVCSTART system | demand indicates whether to start the Oracle Database service on computer restart. Default is demand. Here, system specifies that the service be configured to automatically start when the system boots or reboots. Demand specifies that the user has to explicitly start the service.
-
-PFILE filename is the initialization parameter file to be used with this instance. Ensure that you specify the complete path name of this file, including drive letter.
-
-SPFILE indicates that a server parameter file (SPFILE) be used during startup instead of a PFILE.
-
-SHUTMODE normal|immediate|abort specifies how to stop an instance. This is an optional parameter. If you do not specify how to stop an instance, then normal is the default mode.
-SHUTMODE requires an argument and the default is immediate. If SHUTMODE is omitted, then there is no attempt made to shutdown the instance when the service is shutdown.
-
-TIMEOUT secs sets the maximum time to wait (in seconds) before the service for a particular SID stops. The default is 90 seconds. It cannot be used without the SHUTDOWN argument.
-
-RUNAS osusr/ospass ("run as") sets the operating system user with which the Oracle service logs on to the system. You supply an operating system user name and password, and the service logs on and runs with the privileges of that user. If omitted, the service logs on to the system using the Local system account, which is a privileged user.
|
Note:
For simplicity in demonstrating this feature, this example does not perform the password management techniques that a deployed system normally uses. In a production environment, follow the Oracle Database password management guidelines, and disable any sample accounts. See Oracle Database Security Guide for password management guidelines and other security recommendations. |
To create an instance called PROD, for example, you can enter:
C:\> oradim -NEW -SID prod -STARTMODE auto -PFILE C:\app\oracle\product\11.2.0\admin\prod\pfile\init.ora
Starting an Instance and Services
To use ORADIM to start an instance and services, enter:
oradim -STARTUP -SID SID | -ASMSID SID [-SYSPWD password] [-STARTTYPE srvc |
inst | srvc,inst] [-PFILE filename | -SPFILE]
where
-
-STARTUP indicates that you are starting an instance that already exists. This is a mandatory parameter.
-
-SID SID is the name of the instance to start.
-
-ASMSID SID is the name of the Automatic Storage Management instance to start.
-
-STARTTYPE srvc, inst indicates whether to start the service or the instance. One or both values can be specified. If it is not specified, then the registry is checked for the current setting.
-STARTTYPE srvc is equivalent to net start oracleservice<sid> from the command line.
-STARTTYPE inst is equivalent of startup within SQL*Plus.
-
-PFILE filename is the initialization parameter file to be used with this instance. Ensure that you specify the complete path name of this file, including drive letter.
-
-SPFILE indicates that a server parameter file (SPFILE) be used during startup instead of a PFILE.
To start an instance called puma, for example, you can enter:
C:\> oradim -STARTUP -SID puma -STARTTYPE inst -PFILE C:\app\oracle\product\11.2.0\admin\prod\pfile\init.ora
Stopping an Instance and Services
The Enterprise Database Control service (OracleDBConsoleSID) is dependent on the Oracle Database service (OracleServiceSID). You must stop the dependent Enterprise Database Control service (if installed) before ORADIM to stop the database instance service.
To use ORADIM to stop an instance, enter:
oradim -SHUTDOWN -SID SID | -ASMSID SID [-SYSPWD password]
[-SHUTTYPE srvc | inst | srvc,inst] [-SHUTMODE normal | immediate | abort]
where
-
-SHUTDOWN indicates that you are stopping an instance. This is a mandatory parameter.
-
-SID SID specifies the name of the instance to stop.
-
-ASMSID SID is the name of the Automatic Storage Management instance to stop.
-
-SHUTTYPE srvc, inst indicates whether to stop the service or the instance. One or both values can be specified. If it is not specified, then the registry is checked for the current setting.
-
-SHUTMODE specifies how to stop an instance. This is an optional parameter. If you do not specify how to stop an instance, then normal is the default mode.
To stop an instance called puma, for example, you can enter:
C:\> oradim -SHUTDOWN -SID puma -SHUTTYPE srvc,inst
Editing an Instance
You can edit an existing instance to change such values as instance name, startup mode, shutdown mode, and shutdown type. To use ORADIM to modify an instance, enter:
oradim -EDIT -SID SID | -ASMSID SID [-SYSPWD password] [-STARTMODE auto |
manual] [-SRVCSTART system | demand] [-PFILE filename | -SPFILE][SHUTMODE normal
| immediate | abort] [SHUTTYPE srvc | inst | srvc,inst]
where
-
-EDIT indicates that you are modifying an instance. This is a mandatory parameter.
-
-SID SID specifies the name of the instance to modify. This is a mandatory parameter.
-
-ASMSID SID is the name of the Automatic Storage Management instance to modify.
-
-STARTMODE indicates whether to start the instance when the Oracle Database service is started. Default is manual.
-
-SRVCSTART system | demand indicates whether to start the Oracle Database service on computer restart. Default is demand.
-
-PFILE filename specifies the initialization parameter file to be used with this instance. Ensure that you specify the complete path name of this file, including drive letter.
-
-SPFILE indicates that a server parameter file (SPFILE) be used during startup instead of a PFILE.
-
-SHUTMODE specifies how to stop an instance. This is an optional parameter. If you do not specify how to stop an instance, then normal is the default mode.
-
-SHUTTYPE indicates whether to stop the service or the instance. One or both values can be specified. If it is not specified, then the registry is checked for the current setting.
To specify a new initialization parameter file for the instance prod, for example, you can enter:
C:\> oradim -EDIT -SID prod -PFILE C:\app\oracle\product\11.2.0\admin\lynx\pfile\init.ora
Deleting an Instance
The Enterprise Database Control service (OracleDBConsoleSID) is dependent on the Oracle Database service (OracleServiceSID). You must stop the dependent Enterprise Database Control service (if installed) before ORADIM to delete the database instance.
To use ORADIM to delete an instance, enter:
oradim -DELETE -SID SID | -ASMSID SID | -SRVC service_name | -ASMSRVC service_name
where
-
-DELETE indicates that you are deleting an instance or service. This is a mandatory parameter.
-
-SID SID specifies the name of the SID to delete.
-
-SRVC service_name specifies the name of the service to delete (OracleServiceSID). User should specify either SID or SRVC.
-
-ASMSID SID is the name of the Automatic Storage Management instance to delete.
-
-ASMSRVC service_name is the name of the Automatic Storage Management service to delete.
To delete an instance called prod, for example, you can enter:
C:\> oradim -DELETE -SID prod
Database Migration from a 32-bit Windows Computer
This section contains these topics:
|
See Also:
Oracle Database Upgrade Guide for information about upgrading an earlier release of Oracle Database to Oracle Database 11g Release 2 (11.2) |
Backing Up a 32-Bit Oracle Database
To back up a 32-bit Oracle home database:
-
Start SQL*Plus:
C:\> sqlplus /NOLOG
-
Connect to the database instance as SYSDBA:
SQL> CONNECT / AS SYSDBA;
-
Create a.trc file to use as a template to re-create the control files on the 64-bit computer:
SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
-
Shut down the database:
SQL> SHUTDOWN IMMEDIATE;
-
Perform a full offline backup of the database.
Migrating an Oracle Database 11g Release 2 (11.2)
To migrate an Oracle Database 11g Release 2 (11.2) for 32-bit Windows to an Oracle Database 11g Release 2 (11.2) for 64-bit Windows:
-
Install Oracle Database 11g Release 2 (11.2) for 64-bit Windows.
-
Create the new Oracle Database 11g Release 2 (11.2) service at the command prompt:
C:\> ORADIM -NEW -SID SID [-INTPWD PASSWORD ]-MAXUSERS USERS
-STARTMODE AUTO -PFILE ORACLE_HOME\DATABASE\INITSID.ORA
The following table provides more information on the values you must supply.
-
Copy the 32-bit datafiles to the new 64-bit Oracle home.
-
Copy the 32-bit configuration files to the 64-bit Oracle home.
-
If your 32-bit initialization parameter file has an IFILE (include file) entry, then copy the file specified by the IFILE entry to the 64-bit Oracle home and edit the IFILE entry in the initialization parameter file to point to its new location.
-
If you have a password file that resides in the 32-bit Oracle home, then copy the password file to the 64-bit Oracle home. The default 32-bit password file is located in ORACLE_HOME\database\pwdSID.ora., where SID is your Oracle instance ID.
-
In the 64-bit Oracle home, add the _SYSTEM_TRIG_ENABLED = false parameter to the ORACLE_HOME\database\ORACLE_SID \init.ora file before changing the word size.
-
Remove this parameter from the initialization file after the word size change is complete.
-
Go to the 64-bit ORACLE_HOME\rdbms\admin directory from the command prompt.
-
Start SQL*Plus:
C:\> sqlplus /NOLOG
-
Connect to the database instance as SYSDBA:
SQL> CONNECT / AS SYSDBA;
-
Re-create the 64-bit control files using the CREATE CONTROLFILE command. Edit the trace file created in "Backing Up a 32-Bit Oracle Database" to change the paths to the datafiles, log files and control files to point to the Oracle home on the 64-bit computer. This creates the new control file in ORACLE_HOME\database.
Here is an example of a database named orcl32 on a 32-bit computer migrating to orcl64 on a 64-bit computer:
CREATE CONTROLFILE REUSE DATABASE "T1" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXDATAFILES 32
MAXINSTANCES 16
MAXLOGHISTORY 1815
LOGFILE
GROUP 1 'C:\app\oracle\product\11.2.0\oradata\orcl64\REDO03.LOG' SIZE 1M,
# was 'C:\app\oracle\product\11.2.0\oradata\orcl32\...LOG'
# on the 32-bit computer
GROUP 2 'C:\app\oracle\product\11.2.0\oradata\orcl64\REDO02.LOG' SIZE 1M,
GROUP 3 'C:\app\oracle\product\11.2.0\oradata\orcl64\REDO01.LOG' SIZE 1M
DATAFILE
'C:\app\oracle\product\11.2.0\oradata\orcl64\SYSTEM01.DBF',
# was 'C:\app\oracle\product\11.2.0\oradata\orcl32\...DBF'
# on the 32-bit computer
'C:\app\oracle\product\11.2.0\oradata\orcl64\RBS01.DBF',
'C:\app\oracle\product\11.2.0\oradata\orcl64\USERS01.DBF',
'C:\app\oracle\product\11.2.0\oradata\orcl64\TEMP01.DBF',
'C:\app\oracle\product\11.2.0\oradata\orcl64\TOOLS01.DBF',
'C:\app\oracle\product\11.2.0\oradata\orcl64\INDX01.DBF',
'C:\app\oracle\product\11.2.0\oradata\orcl64\DR01.DBF'
CHARACTER SET WE8ISO8859P1;
-
Alter the init file from the 32-bit computer to include the new control file generated in the preceding step.
-
Shut down the database:
SQL> SHUTDOWN IMMEDIATE;
-
Start the database in UPGRADE mode to run utlirp.sql:
SQL> STARTUP UPGRADE;
You must use the PFILE option to specify the location of your initialization parameter file.
-
Set the system to spool results to a log file for later verification of success. For example:
SQL> SPOOL mig32-64.log;
-
Enter the following command to view the output of the script on-screen:
SQL> SET ECHO ON;
-
Recompile existing PL/SQL modules in the format required by the 64-bit Oracle Database:
SQL> @utlirp.sql;
-
Turn off the spooling of script results to the log file:
SQL> SPOOL OFF;
-
Check the spool file and verify that the packages and procedures compiled successfully. Correct any problems you find in this file.
-
Shut down the database:
SQL> SHUTDOWN IMMEDIATE;
-
Start the database:
SQL> STARTUP;
-
If the JServer JAVA Virtual Machine component is installed, perform the following steps after connecting as SYS:
begin
update obj$ set status=5 where obj#=(select obj# from obj$,javasnm$
where owner#=0 and type#=29 and short(+)=name and
nvl(longdbcs,name)='oracle/aurora/rdbms/Compiler');
commit;
declare
cursor C1 is select
'DROP JAVA DATA "' || u.name || '"."' || o.name || '"'
from obj$ o,user$ u where o.type#=56 and u.user#=o.owner#;
ddl_statement varchar2(200);
iterations number;
previous_iterations number;
loop_count number;
my_err number;
begin
previous_iterations := 10000000;
loop
-- To make sure we eventually stop, pick a max number of iterations
select count(*) into iterations from obj$ where type#=56;
exit when iterations=0 or iterations >= previous_iterations;
previous_iterations := iterations;
loop_count := 0;
open C1;
loop
begin
fetch C1 into ddl_statement;
exit when C1%NOTFOUND or loop_count > iterations;
exception when others then
my_err := sqlcode;
if my_err = -1555 then -- snapshot too old, re-execute fetch query
exit;
else
raise;
end if;
end;
initjvmaux.exec(ddl_statement);
loop_count := loop_count + 1;
end loop;
close C1;
end loop;
end;
commit;
initjvmaux.drp('delete from java$policy$shared$table');
update obj$ set status=1 where obj#=(select obj# from obj$,javasnm$
where owner#=0 and type#=29 and short(+)=name and
nvl(longdbcs,name)='oracle/aurora/rdbms/Compiler');
commit;
end;
/
create or replace java system;
/
-
Recompile existing PL/SQL modules in the format required by the 64-bit Oracle Database:
SQL> @utlrp.sql;
Migrating an Oracle Database 10g Release 2 (10.2) or Earlier Database
To migrate an Oracle Database 10g Release 2 (10.2) to an Oracle Database 11g Release 2 (11.2) for 64-bit Windows:
-
Perform steps 1 - 11 in "Migrating an Oracle Database 11g Release 2 (11.2)".
-
Shut down the database on the 64-bit computer:
SQL> SHUTDOWN IMMEDIATE;
-
Start the database migration:
SQL> STARTUP MIGRATE;
-
Migrate the database as described in Chapter 3, "Upgrading to the New Release" in Oracle Database Upgrade Guide.
-
Shut down the database:
SQL> SHUTDOWN IMMEDIATE;
-
Restart the database:
SQL> STARTUP OPEN;
PK:RL1 =1 PK 6A OEBPS/ap_unix.htm2_͠
E Oracle Database Windows/UNIX Differences
This chapter lists major differences between Oracle Database on Windows and UNIX. For Oracle Database developers and database administrators moving from a UNIX platform to Windows, this information can be helpful in understanding Windows features that are relevant to Oracle Database.
This chapter contains these topics:
Automatic Startup and Shutdown
On UNIX, several files and scripts in different directories are used to start an instance automatically. Other scripts are run on computer shutdown, allowing applications such as Oracle Database to shut down cleanly.
For automatic startup on Windows, set registry parameter ORA_SID_AUTOSTART to true using an Oracle Database tool such as ORADIM. Enter the following with parameters at the command prompt:
C:\> oradim options
To start the listener automatically, set services startup type to automatic.
For automatic shutdown on Windows, set registry parameters ORA_SHUTDOWN and ORA_SID_SHUTDOWN to stop the relevant OracleServiceSID and shut down. Set registry parameter ORA_SID_SHUTDOWNTYPE to control shutdown mode (default is i, or immediate).
Background Processing and Batch Jobs
UNIX provides sophisticated control mechanisms for background processing and batch jobs.
For similar functionality on Windows, use the AT command or a GUI version in the Microsoft Resource Kit.
Diagnostic and Tuning Utilities
On UNIX, utilities such as sar and vmstat are used to monitor Oracle Database background and shadow processes. These utilities are not integrated with Oracle Database.
Performance utilities available on Windows include Oracle Counters for Windows Performance Monitor, Task Manager, Control Panel, Event Viewer, and Microsoft Management Console.
Oracle Database is integrated with several of these tools. For example:
-
Oracle Counters for Windows Performance Monitor displays key Oracle Database information. This tool is the same in appearance and operation as Windows Performance Monitor, except it has been loaded with Oracle Database performance elements.
-
Event Viewer displays system alert messages, including Oracle Database startup/shutdown messages and audit trail.
-
Task Manager on Windows displays currently processes and their resource usage, similar to the UNIX ps -ef command or HP OpenVMS SHOW SYSTEM. But Task Manager is easier to interpret and the columns can be customized.
Direct Writes to Disk
On both UNIX and Windows platforms, bypassing the file system buffer cache ensures data is written to disk.
On UNIX, Oracle Database uses the O_SYNC flag to bypass the file system buffer cache. The flag name depends on the UNIX port.
On Windows, Oracle Database bypasses the file system buffer cache completely.
Dynamic Link Libraries (DLLs)
Shared libraries on UNIX are similar to shared DLLs on Windows. Object files and archive libraries are linked to generate Oracle Database executables. Relinking is necessary after certain operations, such as installation of a patch.
On Windows, Oracle Database DLLs form part of the executable at run time and are therefore smaller. DLLs can be shared between multiple executables. Relinking by the user is not supported, but executable images can be modified using ORASTACK.
Modifying executable images on Windows reduces the chances of out of virtual memory when using a large SGA or when supporting thousands of connections. However, Oracle recommends doing this only under the guidance of Oracle Support Services.
Hot Backups
A (manual) hot backup is equivalent to backing up a tablespace that is in offline backup mode.
Backup strategy on UNIX is as follows: put the tablespace into backup mode, copy the files to the backup location, and bring the tablespace out of backup mode.
Windows supports the same backup strategy, but you cannot copy files in use with normal Windows utilities. Use OCOPY to copy open database files to another disk location. Then use a utility to copy the files to tape.
Initialization Parameters: Multiple Database Writers
On UNIX, you can specify many database writer process with initialization parameter DB_WRITERS. Multiple database writers can help, for example, when a UNIX port does not support asynchronous I/O.
DB_WRITERS is supported but typically unnecessary on Windows, which has its own asynchronous I/O capabilities.
Install Accounts and Groups
UNIX uses the concept of a DBA group. The root account cannot be used to install Oracle Database. A separate Oracle Database account must be created manually.
On Windows, Oracle Database must be installed by a Windows username in the Administrators group. The user name is automatically added to the Windows local group ORA_DBA, which receives the SYSDBA privilege. This allows the user to log in to the database using CONNECT / AS SYSDBA and not be prompted for a password.
You can also create an ORA_OPER group to grant SYSOPER privileges to the other Windows users.
Password files are located in the ORACLE_HOME\database directory and are named pwdSID.ora, where SID identifies the Oracle Database instance.
Installation
The following manual setup tasks, all required on UNIX, are not required on Windows:
-
Set environment variables
-
Create a DBA group for database administrators
-
Create a group for users Oracle Universal Installer
-
Create an account dedicated to installing and upgrading Oracle Database components
Memory Resources
The resources provided by the UNIX default kernels are often inadequate for a medium or large instance of Oracle Database. The maximum size of a shared memory segment (SHMMAX) and maximum number of semaphores available (SEMMNS) may be too low for Oracle Database recommendations.
On Windows, fewer resources are needed for interprocess communication (IPC), because the Oracle Database relational database management system is thread-based and not process-based. These resources, including shared memory and semaphores, are not adjustable by the user.
Microsoft Transaction Server
UNIX does not support Microsoft Transaction Server.
Windows supports Microsoft Transaction Server beginning with Oracle8. Using Oracle Services for Microsoft Transaction Server, you can develop and deploy applications based on COM. Microsoft Transaction Server coordinates application transactions for Oracle Database.
Multiple Oracle Homes and OFA
The goal of OFA is to place all Oracle Database software under one ORACLE_HOME directory and to spread database files across different physical drives as databases increase in size. OFA is implemented on Windows and UNIX in the same way, and main subdirectory and file names are the same on both operating systems. Windows and UNIX differ, however, in their OFA directory tree top-level names and in the way variables are set.
On UNIX, ORACLE_BASE is associated with a user's environment. ORACLE_HOME and ORACLE_SID must be set in system or user login scripts. Symbolic links are supported. Although everything seems to be in one directory on the same hard drive, files may be on different hard drives if they are symbolically linked or have that directory as a mount point.
On Windows, ORACLE_BASE is defined in the registry (for example, in HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0). ORACLE_HOME and ORACLE_SID are variables defined in the registry. Symbolic links like those on UNIX are not supported, although Microsoft has announced the intention to support them.
Processes and Threads
On UNIX, Oracle Database uses a process to implement each of such background tasks as database writer (DBW0), log writer (LGWR), shared server process dispatchers, and shared servers. Each dedicated connection made to the database causes another operating system process to be spawned on behalf of that session.
On Windows, each background process is implemented as a thread inside a single, large process. For each Oracle Database instance or system identifier, there is one corresponding process for Oracle Database. For example, 100 Oracle Database processes for a database instance on UNIX are handled by 100 threads inside one process on Windows.
All Oracle Database background, dedicated server, and client processes are threads of the master Oracle Database Windows process, and all threads of the Oracle Database process share resources. This multithreaded architecture is highly efficient, allowing fast context switches with low overhead.
To view processes or end individual threads, use Oracle Administration Assistant for Windows. From the Start menu, select Programs, then select Oracle - HOME_NAME, then select Configuration and Migration Tools and then select Administration Assistant for Windows. Right-click the SID and choose Process Information.
|
Note:
Microsoft Management Console (MMC) is started when Oracle Administration Assistant for Windows is started. Oracle Database has integrated several database administration snap-ins into Microsoft Management Console. |
Raw Partitions
Datafiles for tablespaces may be stored on a file system or on raw partitions. A raw partition is a portion of a physical disk that is accessed at the lowest possible level.
UNIX supports raw partitions (logical drives). There is no limitation on the number of disk drives.
Windows is limited to using drive letters A-Z, but creating raw partitions lets you bypass the disk drive limitation and divide disks into smaller sections.
Use Windows disk management tools to create an extended partition on a physical drive. An extended partition points to raw space on the disk that can be assigned multiple logical partitions for database files.
An extended partition avoids the four-partition limit on Windows by allowing you to define large numbers of logical partitions to accommodate applications using Oracle Database. Logical partitions can then be given symbolic link names to free up drive letters.
On Windows Vista, Windows Server 2008, and Windows Server 2008 R2, create primary partitions and logical drives in extended partitions by selecting the New Simple Volume option. To create a raw device, select Do not assign a drive letter or drive path. To mount the raw device, assign and remove a drive letter. Do not use spanned volumes or striped volumes. These options will convert the volume to a dynamic disk. Automatic Storage Management does not support dynamic disks.
|
Note:
Oracle RAC is not supported on Windows XP, Windows Vista, Winodws 7, and any 32-bit Windows operating system. |
Services
Windows services are similar to UNIX daemons.
Oracle Database registers a database instance as a service (OracleServiceSID). Services start background processes.
To connect to and use an Oracle Database instance, an Oracle Database service is created during database creation and associated with Oracle Database. Once a service is created with Oracle Database, the service can run even while no user is logged on.
By default, services run under the SYSTEM account. From the Start menu, select Settings, then select Control Panel and then select Services to access the Services dialog.
PKWQ7_ 2_ PK 6A OEBPS/glossary.htm
Glossary
alert log
A file that contains important information and error messages that are generated during database operations.
authenticate
To verify the identity of a user, device, or other entity in a computer system, often as a prerequisite for allowing access to resources in a system.
authorization
Permission given to a user, program, or process to access an object or set of objects. In Oracle Database, authorization is done through the role mechanism. A single person or a group of people can be granted a role or a group of roles. A role, in turn, can be granted other roles.
backup
A representative copy of data. This copy includes important parts of your database such as control files, redo log files, and datafiles.
A backup is a safeguard against unexpected data loss; if you lose your original data, then you can use the backup to make the data available again. A backup is also a safeguard against an application error; if an application makes incorrect changes, then you can restore the backup.
certificate authority
A certificate authority (CA) is a trusted third party that certifies the identity of other entities such as users, databases, administrators, clients, and servers. The certificate authority verifies the user's identity and grants a certificate, signing it with one of the certificate authority's private keys.
COM
Microsoft's Component Object Model is an object-oriented programming architecture and a set of operating system services. These services notify application components of significant events and ensure that they are authorized to run. COM is intended to make it relatively easy to create business applications that work well with Microsoft Transaction Server.
component-based shadow copies
VSS snapshots of Oracle database components. Examples of components include tablespaces or archived redo logs.
connect descriptor
A specially formatted description of the destination for a network connection. A connect descriptor contains destination services and network route information. The destination service is indicated by using its service name for Oracle9i or Oracle8i databases or its Oracle system identifier for Oracle8 Release 8.0 databases. The network route provides, at a minimum, the location of the listener through use of a network address.
connect identifier
A net service name or service name, that maps to a connect descriptor. Users initiate a connect request by passing a username and password along with a connect identifier in a connect string for the services to which they want to connect, for example:
CONNECT username/password@connect_identifier
connect string
See net service name.
control files
Files that record the physical structure of a database and contain the database name, the names and locations of associated databases and online redo log files, the time stamp of the database creation, the current log sequence number, and checkpoint information.
credentials
A username, password, or certificate used to gain access to the database.
data dictionary
A set of read-only tables that provide information about a database.
database alias
See net service name.
decryption
Process of converting contents of a message that has gone through encryption (ciphertext) back into its original readable format (plaintext).
digital certificates
ITU X.509 v3 standard data structures that securely bind an identity to a public key. A certificate is created when an entity's public key is signed by a trusted identity, a certificate authority. The certificate ensures that the entity's information is correct and that the public key actually belongs to that entity.
digital signature
Digital signatures are created when a public key algorithm is used to sign messages with senders' private keys. A digital signature assures that a document is authentic, has not been forged by another entity, has not been altered, and cannot be repudiated by the sender.
downgrade
To convert the data in Oracle Database to an earlier Oracle release. See upgrade and migrate.
dynamic link library
An executable file that a Windows application can load when needed.
encryption
Process of disguising a message, rendering it unreadable to any but the intended recipient.
enterprise domains
Directory constructs consisting of Oracle Database and enterprise users and roles.
enterprise role
A directory structure which contains global roles on multiple databases, and which can be granted to an enterprise user.
enterprise user
A user that has a unique identity across an enterprise. An enterprise user connects to individual databases through a schema and is assigned an enterprise role that determines the user's access privileges on databases.
external procedures
Functions written in a third-generation language (C, for example) and callable from within PL/SQL or SQL as if they were PL/SQL functions or procedures.
external role
Roles created and managed by Windows operating systems. Once an external role is created, you can grant or revoke that role to a database user. You must set init.ora parameter OS_ROLES to true and restart Oracle Database before you can create an external role. You cannot use both Windows operating systems and Oracle Database to grant roles concurrently.
external routine
A function written in a third-generation language (3GL), such as C, and callable from within PL/SQL or SQL as if it were a PL/SQL function or procedure.
external user
A user authenticated by the Windows operating system who can access Oracle Database without being prompted for a password. External users are typically regular database users (non-database administrators) to which you assign standard database roles (such as DBA), but do not want to assign SYSDBA (database administrator) or SYSOPER (database operator) privilege.
external user
The Windows operating system can authenticate a user, who can then access Oracle Database without being prompted for a password. External users are typically regular database users (non-database administrators) to whom you assign standard database roles (such as DBA), but do not want to assign the SYSDBA (database administrator) or SYSOPER (database operator) privilege.
global role
A role whose privileges are contained within a single database, but which is managed in a directory.
Globalization Support
The Oracle Database architecture that ensures that database utilities, error messages, sort order, date, time, monetary, numeric, and calendar conventions automatically adapt to the native language and locale.
HOME_NAME
Represents the name of an Oracle home. In Oracle Database 11g Release 2 (11.2), all Oracle homes have a unique HOME_NAME.
initialization parameter file
An ASCII text file that contains information needed to initialize a database and instance.
instance
Every Oracle Database is associated with an Oracle Database or Automatic Storage Management instance. When a database is started on a database server (regardless of the type of computer), Oracle Database allocates a memory area called the System Global Area and starts one or more Oracle Database processes. This combination of the System Global Area and Oracle Database processes is called an instance. The memory and processes of an instance manage the associated database's data efficiently and serve the users of the database.
instantiate
Produce a more defined version of some object by replacing variables with values (or other variables).
latch
A simple, low-level serialization mechanism to protect shared data structures in the System Global Area.
Lightweight Directory Access Protocol (LDAP)
A standard, extensible directory access protocol. It is a common language that LDAP clients and servers use to communicate. LDAP is a framework of design conventions supporting industry-standard directory products, such as Oracle Internet Directory.
listener
The Oracle Database server process that listens for and accepts incoming connection requests from client applications. The listener process starts Oracle Database processes to handle subsequent communications with the client; then it goes back to listening for new connection requests.
listener.ora
A configuration file that describes one or more Transparent Network Substrate (TNS) listeners on a server.
local roles
Roles created and managed by the database. Once a local role is created, you can grant or revoke that role to a database user. You cannot use Windows (for external role management) and Oracle Database (for local role management) concurrently.
Microsoft Management Console
An application that serves as a host for administrative tools called snap-ins. By itself, Microsoft Management Console does not provide any functionality.
Microsoft Transaction Server
A transaction processing system based on COM that runs on an Internet or network server.
migrate
To upgrade or downgrade an Oracle Database or convert the data in a non-Oracle database into an Oracle Database.
mount
To associate a database with an instance that has been started.
multiple Oracle homes
The capability of having multiple Oracle home on a computer.
net service name
The name used by clients to identify an Oracle Net server and the specific system identifier or database for the Oracle Net connection. A net service name is mapped to a port number and protocol. Also known as a connect string, database alias, host string, or service name.
This also identifies the specific system identifier or database to which the connection is attaching, not just the Oracle Net server.
network listener
A listener on a server that listens for connection requests for one or more databases on one or more protocols. See listener.
network service
In an Oracle application network, a service performs tasks for its service consumers. For example, a Names Server provides name resolution services for clients.
obfuscated
Protected by a process often used by companies for intellectual property written in the form of Java programs. The obfuscation process mixes up Java symbols found in the code. It leaves the original program structure intact, allowing the program to run correctly, while changing the names of the classes, methods, and variables to hide the intended behavior. Although it is possible to decompile and read non-obfuscated Java code, obfuscated Java code is sufficiently difficult to decompile to satisfy U.S. government export controls.
Optimal Flexible Architecture
A set of file naming and placement guidelines for Oracle Database software and databases.
ORACLE_BASE
ORACLE_BASE is the root of the Oracle Database directory tree. If you install an OFA-compliant database using Oracle Universal Installer defaults, then ORACLE_BASE is X:\app\oracle\product\11.2.0 where X is any hard drive.
Oracle Call Interface
An application programming interface that enables you to manipulate data and schema in an Oracle Database. You compile and link an Oracle Call Interface application in the same way that you compile and link a non-database application. There is no need for a separate preprocessing or precompilation step.
ORACLE_HOME
Corresponds to the environment in which Oracle Database products run. This environment includes the location of installed product files, the PATH variable pointing to the binary files of installed products, registry entries, net service names, and program groups.
If you install an OFA-compliant database, using Oracle Universal Installer defaults, then Oracle home (known as ORACLE_HOME in this guide) is located beneath ORACLE_BASE. It contains subdirectories for Oracle Database software executables and network files.
Oracle Internet Directory
An Oracle Database-based LDAP V3 directory server, used for centralizing database user, Oracle Net network connector, and database listener parameters. Oracle Internet Directory ships only with Oracle Application Server, not the Oracle Database 11g product set.
Oracle Net
A component of Oracle Net Services that enables a network session from a client application to an Oracle Database server. Once a network session is established, Oracle Net acts as a data courier for the client application and the database server. It is responsible for establishing and maintaining the connection between the client application and database server, and exchanging messages between them. Oracle Net can perform these jobs because it is located on each computer in the network.
Oracle Net Services
A suite of networking components that provide enterprise-wide connectivity solutions in distributed, heterogeneous computing environments. Oracle Net Services are comprised of Oracle Net, listener, Oracle Connection Manager, Oracle Net Configuration Assistant, and Oracle Net Manager.
Oracle PKI
Oracle Advanced Security includes Oracle PKI (public key infrastructure) integration for authentication and single sign-on. Oracle-based applications are integrated with the PKI authentication and encryption framework, using Oracle Wallet Manager.
Oracle Protocol Support
A product that maps the functions of a given network protocol into Oracle Transparent Network Substrate (TNS) architecture. This process translates TNS function calls into requests to the underlying network protocol. This allows TNS to act as an interface among all protocols. Oracle Net requires Oracle Protocol Support.
Oracle services
Windows services that are associated with particular Oracle Database components.
Oracle VSS writer
A service on Windows systems that acts as coordinator between an Oracle database instance and other VSS components, enabling data providers to create a shadow copy of files managed by the Oracle instance. For example, the Oracle VSS writer can place datafiles in hot backup mode to provide a recoverable copy of these datafiles in a shadow copy set.
PL/SQL
Procedural language extension to SQL that is part of Oracle Database.
PL/SQL enables you to mix SQL statements with procedural constructs. You can define and run PL/SQL program units such as procedures, functions, and packages.
precompiler
A programming tool that enables you to embed SQL statements in a high-level source program.
private keys
In public key cryptography, these are the secret keys. They are used primarily for decryption but also for encryption with a digital signature.
privilege
A right to run a particular type of SQL statement or to access another user's object.
process
A mechanism in an operating system that can run an executable. (Some operating systems use the terms job or task.) A process normally has its own private memory area in which it runs. On Windows a process is created when an application runs (such as Oracle Database or Microsoft Word). In addition to an executable program, all processes consist of at least one thread. The Oracle Database master process contains hundreds of threads.
provider
Software or hardware that creates shadow copies on demand. Typically, a provider is a disk storage system. In response to a request from a requester, a provider responds to VSS COM messages to create and maintain shadow copies.
public key
In public key cryptography, this key is made public to all. It is primarily used for encryption but can also be used for verifying signatures.
public key cryptography
Public key cryptography involves information encryption and decryption using a shared public key paired with private keys. Provides for secure, private communications within a public network.
quota
A limit on a resource, such as a limit on the amount of database storage used by a database user. A database administrator can set tablespace quotas for each Oracle Database username.
raw partitions
Portions of a physical disk that are accessed at the lowest possible disk (block) level.
recovery
To restore a physical backup is to reconstruct it and make it available to the Oracle Database server. To recover a restored backup is to update it using redo records (that is, records of changes made to the database after the backup was taken). Recovering a backup involves two distinct operations: rolling forward the backup to a more current time by applying redo data, and rolling back all changes made in uncommitted transactions to their original state.
redo log buffer
A circular buffer in the System Global Area that contains information about changes made to the database.
redo log files
Files that contain a record of all changes made to data in the database buffer cache. If an instance failure occurs, then the redo log files are used to recover the modified data that was in memory.
registry
A Windows repository that stores configuration information for a computer.
remote computer
A computer on a network other than the local computer.
remote database
A database on a computer other than the local database.
requester
An application that uses the VSS API to create shadow copies. Requester applications communicate with VSS writers to gather information on the system and to signal writers to prepare data for backup. The requester maintain control over VSS backup and restore operations by generating COM events through calls in the VSS API.
replication
The process of copying and maintaining database objects in multiple databases that make up a distributed database system.
role
A named groups of related privileges. You can grant a role to users or to another role.
schema
A named collection of objects, such as tables, views, clusters, procedures, and packages, associated with one or more particular users.
services
Executable processes installed in the Windows registry and administered by Windows. Once services are created and started, they can run even when no user is logged on to the computer.
service name
See net service name.
shadow copy
A consistent snapshot of a component or volume.
shadow copy set
A collection of shadow copies that are all taken at the same time.
Shared Server Process
A server configuration which allows many user processes to share very few server processes. The user processes connect to a dispatcher background process, which routes client requests to the next available shared server process.
snap-ins
Administrative tools that run within Microsoft Management Console.
snapshot
(1) Information stored in rollback segments provide transaction recovery and read consistency. Use Rollback segment information to re-create a snapshot of a row before an update.
(2) A point-in-time copy of a master table located on a remote site. Read-only snapshots can be queried, but not updated. Updateable snapshots can be queried and updated. They are periodically refreshed to reflect changes made to the master table, and at the snapshot site.
starter database
A preconfigured, ready-to-use database that requires minimal user input to create.
SYSDBA
A special database administration role that contains all system privileges with the ADMIN OPTION, and the SYSOPER system privilege. SYSDBA also permits CREATE DATABASE actions and time-based recovery.
SYSOPER
A special database administration role that permits a database administrator to perform STARTUP, SHUTDOWN, ALTER DATABASE OPEN/MOUNT, ALTER DATABASE BACKUP, ARCHIVE LOG, and RECOVER, and includes the RESTRICTED SESSION privilege.
System Global Area
A group of shared memory structures that contain data and control information for an Oracle Database instance.
system identifier
A unique name for an Oracle Database instance. To switch between instances of Oracle Database, users must specify the desired system identifier. The system identifier is included in the CONNECT DATA parts of the connect descriptors in a tnsnames.ora file, and in the definition of the network listener in a tnsnames.ora file.
SYSTEM
One of two standard database administrator usernames automatically created with each database. (The other user name is SYS.). The SYSTEM user name is the preferred user name for database administrators to use for database maintenance.
tablespace
A database is divided into one or more logical storage units called tablespaces. Tablespaces are divided into logical units of storage called segments, which are further divided into extents.
thread
An individual path of execution within a process. Threads are objects within a process that run program instructions. Threads allow concurrent operations within a process so that a process can run different parts of its program simultaneously on different processors. A thread is the most fundamental component that can be scheduled on Windows.
tnsnames.ora
A file that contains connect descriptors mapped to net service names. The file can be maintained centrally or locally, for use by all or individual clients.
trace file
Each server and background process can write to an associated trace file. When a process detects an internal error, it dumps information about the error to its trace file. Some information written to a trace file is intended for the database administrator, while other information is intended for Oracle Support Services. Trace file information is also used to tune applications and instances.
trust points
Trust points or trusted certificates are third party identities that are qualified with a level of trust. A trusted certificate is used when an identity is being validated as the entity it claims to be. Certificate authorities you trust are called trusted certificates. If there are several levels of trusted certificates, a trusted certificate at a lower level in the certificate chain does not need to have all its higher level certificates reverified.
universal groups
Universal groups are available in Windows. They can contain other groups, including other universal groups, local groups, and global groups.
upgrade
To convert the data in an Oracle Database into a later release. See downgrade and migrate.
username
A name that can connect to and access objects in a database.
view
A selective presentation of the structure and data of one or more tables. Views can also be based on other views.
volume-based shadow copies
VSS snapshots of complete drives or volumes.
Volume Shadow Copy Service (VSS)
An infrastructure on Windows server platforms that enables requesters, writers and providers to participate in creation of consistent snapshots called Shadow Copies. The VSS service uses well-defined COM interfaces.
Windows global groups
Groups that can be granted permissions and rights in their own domain, member servers and workstations of their domain, and in trusted domains. They can also become members of Windows local groups in all these places. But global groups can contain user accounts only from their own domains.
Windows local groups
Groups that can be granted permissions and rights only for its own computer or, if part of a domain, to the domain controllers of that domain. Local groups can, however, contain user accounts and Windows global groups from both their own domain and from trusted domains
writer
An application or service that stores persistent information on disk and cooperates with providers and requesters within the VSS infrastructure.
PK2,
PK 6A OEBPS/admin.htm
5 Administering a Database on Windows
This chapter describes how to administer Oracle Database for Windows.
This chapter contains these topics:
Managing Oracle Database Services
This section tells you how to manage the services that Oracle Database installs on your computer.
This section provides information on the following:
Oracle Database Service Naming Conventions for Multiple Oracle Homes
Oracle Database for Windows lets you have multiple Oracle homes on a single computer. This feature, described in Appendix B, "Optimal Flexible Architecture", in Oracle Database Installation Guide for Microsoft Windows, affects Oracle services naming conventions. As you perform installations into Oracle home directories:
-
You must accept default Oracle home name provided or specify a different name for each Oracle home directory.
-
You are prompted to give a system identifier and global database name for each database installation.
Starting Oracle Database Services
Oracle Database services must be started for you to use Oracle Database and its products. You can start Oracle Database services from three different locations:
Control Panel
To start Oracle Database services from the Control Panel:
-
Access your Windows Services dialog.
|
See Also:
Your operating system documentation for instructions |
-
Find the service to start in the list, select it, and click Start.
If you cannot find OracleServiceSID in the list, then use ORADIM to create it.
-
Click Close to exit the Services dialog.
Command Prompt
To start Oracle Database services from the command prompt, enter:
C:\> NET START service
where service is a specific service name, such as OracleServiceORCL.
Oracle Administration Assistant for Windows
To start Oracle Database services from Oracle Administration Assistant for Windows:
-
From the Start menu, select Programs, then select Oracle - HOME_NAME, then select Configuration and Migration Tools and then select Administration Assistant for Windows.
-
Right-click the SID.
where SID is a specific instance name, such as orcl.
-
Click Start Service.
This starts service OracleServiceORCL.
Stopping Oracle Database Services
On occasion (for example, when re-installing Oracle Database), you must stop Oracle Database services. You can stop Oracle Database services from three different locations:
Control Panel
To stop Oracle Database services from the Control Panel:
-
Access your Windows Services dialog.
|
See Also:
Your operating system documentation for instructions |
-
Select OracleHOME_NAMETNSListener and click Stop.
OracleHOME_NAMETNSListener is stopped.
-
Select OracleServiceSID and click Stop.
-
Click OK.
OracleServiceSID is stopped.
Command Prompt
To stop Oracle Database services from the command prompt, enter:
C:\> net STOP service
where service is a specific service name, such as OracleServiceORCL.
Oracle Administration Assistant for Windows
To stop Oracle Database services from Oracle Administration Assistant for Windows:
-
From the Start menu, select Programs, then select Oracle - HOME_NAME, then select Configuration and Migration Tools and then select Administration Assistant for Windows.
-
Right-click the SID.
where SID is a specific instance name, such as orcl.
-
Click Stop Service.
This stops service OracleServiceORCL.
Auto-starting Oracle Database Services
Oracle Database services can be set to start automatically whenever the Windows computer is restarted. You can turn auto-start on or off from two different locations:
Control Panel
To use the Control Panel to configure when and how Oracle Database is started:
-
Access your Windows Services dialog.
|
See Also:
Your operating system documentation for instructions |
-
Select service OracleServiceSID and click Startup.
-
Choose Automatic from the Startup Type field.
-
Click OK.
-
Click Close to exit the Services dialog.
Oracle Administration Assistant for Windows
To automatically start Oracle Database services from Oracle Administration Assistant for Windows:
-
From the Start menu, select Programs, then select Oracle - HOME_NAME, then select Configuration and Migration Tools and then select Administration Assistant for Windows.
-
Right-click the SID.
where SID is a specific instance name, such as orcl.
-
Choose Startup/Shutdown Options.
-
Choose the Oracle Service tab.
-
Choose Automatic in Oracle Service Startup Type.
-
Click Apply.
-
Click OK.
Description of the illustration ss_cnfg1.gif
Starting and Shutting Down a Database with SQL*Plus
These instructions assume that a database instance has been created.
To start or shut down Oracle Database:
-
Go to your Oracle Database server.
-
Start SQL*Plus at the command prompt:
C:\> sqlplus /NOLOG
-
Connect to Oracle Database with username SYSDBA:
SQL> CONNECT / AS SYSDBA
-
To start a database, enter:
SQL> STARTUP [PFILE=path\filename]
This command uses the initialization parameter file specified in path\filename. To start a database using a file named init2.ora located in
C:\app\oracle\product\11.2.0\admin\orcl\pfile
you would enter:
SQL> STARTUP PFILE=C:\app\oracle\product\11.2.0\admin\orcl\pfile\init2.ora
If no PFILE is specified, then the command looks for an SPFILE in ORACLE_HOME\database. If the command finds one, then the command uses it to start the database. If it does not find an SPFILE, then it uses the default initialization parameter file located in ORACLE_BASE\ADMIN\db_name\pfile.
-
To stop a database, enter:
SQL> SHUTDOWN [mode]
where mode is normal, immediate, or abort.
In a normal shutdown, Oracle Database waits for all currently-connected users to disconnect and disallows any new connections before shutting down. This is the default mode.
In an immediate shutdown, Oracle Database terminates and rolls back active transactions, disconnects clients, and shuts down.
In an abort shutdown, Oracle 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:
Chapter 2, "Database Tools on Windows" for a list of other tools that can start Oracle 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 Oracle Database by starting or stopping service OracleServiceSID in the Control Panel. Starting OracleServiceSID is equivalent to using the STARTUP command or manually entering:
C:\> oradim -STARTUP -SID SID [-STARTTYPE srvc | inst | srvc,inst] [-PFILE
filename | -SPFILE]
Stopping OracleServiceSID is equivalent to using the SHUTDOWN command or manually entering:
C:\> oradim -SHUTDOWN -SID SID [-SHUTTYPE srvc | inst | srvc,inst] [-SHUTMODE
normal | immediate | abort]
You can enable starting and stopping Oracle Database through OracleServiceSID two different ways:
Oracle Administration Assistant for Windows
To start or stop a database using Oracle Database services from Oracle Administration Assistant for Windows:
-
From the Start menu, select Programs, then select Oracle - HOME_NAME, then select Configuration and Migration Tools and then select Administration Assistant for Windows.
-
Right-click the SID.
where SID is a specific instance name, such as ORCL.
-
Choose Startup/Shutdown Options.
-
Choose the Oracle Instance tab.
-
Select Start up instance when service is started, Shut down instance when service is stopped, or both.
Description of the illustration ss_cnfg2.gif
Setting Registry Parameters
To start or stop Oracle Database through Oracle Database Services, set the following registry parameters to the indicated values:
-
ORA_SID_AUTOSTART
When set to true, the default value, this parameter causes Oracle Database to start when OracleServiceSID is started.
-
ORA_SID_PFILE
This parameter sets the full path to the initialization parameter file. If this entry is not present, then oradim will try to start the database with an SPFILE or PFILE from ORACLE_HOME\database.
-
ORA_SHUTDOWN
When set to true, this parameter enables the selected instance of Oracle Database to be shut down when OracleServiceSID is stopped. This includes any database in the current Oracle home. Default value is false.
-
ORA_SID_SHUTDOWN
When set to true, the default value, this parameter causes the instance of Oracle Database identified by the SID value to shut down when OracleServiceSID is stopped manually—using either the Control Panel or Net stop command.
|
Caution:
If ORA_SHUTDOWN or ORA_SID_SHUTDOWN is set to false, then manually shutting down OracleServiceSID will still shut down Oracle Database. But it will be an abnormal shutdown, and Oracle does not recommend it. |
The following two registry parameters are optional:
-
ORA_SID_SHUTDOWNTYPE
This parameter controls database shutdown mode. Set it to a (abort), i (immediate), or n (normal). Default mode is i (immediate) if you do not set this parameter.
-
ORA_SID_SHUTDOWN_TIMEOUT
This parameter sets maximum time to wait before the service for a particular SID stops.
The registry location of these required and optional parameters is determined by the number of Oracle home directories on your computer. If you have only one Oracle home directory, then these parameters belong in:
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0
If you have multiple Oracle home directories, then these parameters belong in:
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEID
where ID is incremented for each additional Oracle home directory on your computer.
|
Note:
If you use ORADIM to create or edit instances, then it automatically sets the relevant registry parameters to their appropriate values. |
Starting or Stopping OracleServiceSID from the Control Panel
-
To start the database, start OracleServiceSID.
This automatically starts ORADIM and issues the -STARTUP command using the initialization parameter file identified by ORA_SID_PFILE.
-
To stop the database, stop OracleServiceSID.
This automatically starts ORADIM, which issues the -SHUTDOWN command in the mode indicated by ORA_SID_SHUTDOWNTYPE, and shuts down Oracle Database.
|
See Also:
Your operating system documentation for instructions on starting and stopping services. |
Starting Multiple Instances
-
Start the service for each instance using ORADIM or the Services dialog of the Control Panel.
-
At the command prompt set the ORACLE_SID configuration parameter to the SID for the first instance to run:
C:\> SET ORACLE_SID=SID
where SID is the name of the Oracle Database instance.
-
Start SQL*Plus:
C:\> sqlplus /NOLOG
-
Connect AS SYSDBA:
SQL> CONNECT / AS SYSDBA
-
Start up the first instance:
SQL> STARTUP PFILE=ORACLE_BASE\admin\db_name\pfile\init.ora
where ORACLE_BASE is c:\app\oracle\product\11.2.0 (unless you changed it during installation) and db_name is the name of the instance.
-
Repeat Steps 2-5 for the other instances to run.
Creating and Populating Password Files
Use Password Utility to create password files. Password Utility is automatically installed with Oracle Database utilities. Password files are located in directory ORACLE_HOME\database and are named PWDsid.ora, where SID identifies the Oracle Database instance. Password files can be used for local or remote connections to Oracle Database.
To create and populate a password file:
-
Create a password file with the Password Utility:
C:\> orapwd FILE=PWDsid.ora ENTRIES=max_users
where
-
FILE specifies the password file name.
-
SID identifies the database instance.
-
ENTRIES sets maximum number of entries in password file. This corresponds to maximum number of distinct users allowed to connect to the database simultaneously with either the SYSDBA or the SYSOPER DBA privilege.
-
Set initialization parameter file parameter REMOTE_LOGIN_PASSWORDFILE to exclusive, shared, or none.
The value 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, Oracle Database looks in the registry for the value of parameter ORA_SID_PWFILE. If no value is specified, then it looks in the registry for the value of parameter ORA_PWFILE, which points to a file containing usernames, passwords, and privileges. If that is not set, then it uses the default:
ORACLE_HOME\DATABASE\PWDsid.ORA.
The default value is shared. It specifies that multiple instances (for example, an Oracle RAC environment) can use the password file. However, the only user recognized by the password file is SYS. Other users cannot log in with SYSOPER or SYSDBA privileges even if those privileges are granted in the password file. The shared value of this parameter affords backward compatibility with earlier Oracle releases. Oracle Database looks for the same files as it does when the value is exclusive.
The value none specifies that Oracle Database ignores the password file and that authentication of privileged users is handled by the Windows operating system.
-
Start SQL*Plus:
C:\> sqlplus /NOLOG
-
Connect AS SYSDBA:
SQL> CONNECT / AS SYSDBA
For an ASM instance, connect AS SYSASM:
SQL> CONNECT / AS SYSASM
-
Start Oracle Database:
SQL> STARTUP
-
Grant appropriate privileges to each user. Users who must perform database administration, for example, would be granted privilege SYSDBA:
SQL> GRANT SYSDBA TO db_administrator;
For an ASM instance:
SQL> GRANT SYSASM TO SYS;
If the grant is successful, then the following message displays:
Statement Processed.
This adds scott to the password file and enables scott to connect to the database with SYSDBA privileges. Use SQL*Plus to add or delete usernames, user passwords, and user privileges in password files.
|
Caution:
Copying or manually moving password files may result in ORADIM being unable to find a password to start an instance. |
Viewing and Hiding the Password File
The password file is not automatically hidden. It can be made invisible and visible again from two different locations:
Command Prompt
-
To see the password file, enter:
ORACLE_BASE\ORACLE_HOME\database> attrib
The password file is displayed as PWDsid.ora:
A ORACLE_HOME\database\oradba.exe
A ORACLE_HOME\database\oradim.log
A ORACLE_HOME\database\PWDsid.ora
A ORACLE_HOME\database\SPFILEsid.ora
-
To make the password file invisible, enter:
ORACLE_HOME\database> attrib +H PWDsid.ora
-
To see the effect of the change, enter:
ORACLE_HOME\database> attrib
The password file is now hidden:
A ORACLE_HOME\database\oradba.exe
A ORACLE_HOME\database\oradim.log
A H ORACLE_HOME\database\PWDsid.ora
A ORACLE_HOME\database\SPFILEsid.ora
-
To make the password file visible again, enter:
ORACLE_HOME\database> attrib -H PWDsid.ora
Windows Explorer
To make the password file invisible or visible again:
-
Navigate to directory ORACLE_HOME\database.
-
Right-click PWDsid.ora.
-
Choose Properties.
The PWDsid.ora Properties dialog opens.
-
In Attributes, check or clear the checkbox next to Hidden.
-
Click OK.
To view or hide an invisible password file:
-
Navigate to directory ORACLE_BASE\ORACLE_HOME\database.
-
Choose Folder Options from the View main menu.
-
Choose the View tab.
-
To view an invisible password file, choose Show hidden files and folders.
-
To hide a visible password file, choose Do not show hidden files and folders.
-
Click OK.
Connecting Remotely to the Database as SYS
When connecting to the starter database from a remote computer as SYS, you must use a different password from the one described in Oracle Database Installation Guide for Microsoft 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.
Automatically Encrypted Database Passwords
With Oracle Database, the password used to verify a remote database connection is automatically encrypted. Whenever a user attempts a remote login, Oracle Database encrypts the password before sending it to the remote database. If the connection fails, then the failure is noted in the operating system audit log.
Accessing Database Files on Remote Computers
Oracle Database can access database files on a remote computer using Universal Naming Convention (UNC), but it may degrade database performance and network reliability. UNC is a PC format for specifying locations of resources on a local area network. UNC uses the following format:
\\server-name\shared-resource-path-name
For example, UNC specification for file system01.dbf in directory C:\app\oracle\product\11.2.0\oradata\orcl on shared server argon would be:
\\argon\app\oracle\product\11.2.0\oradata\orcl\system01.dbf
Locations of archive log files cannot be specified using Universal Naming Convention (UNC). Always set initialization parameter LOG_ARCHIVE_DEST_n to a local drive. If you set it to a UNC specification, then Oracle Database does not start and you receive the following errors:
ORA-00256: error occurred in translating archive text string '\meldell\rmdrive'
ORA-09291: sksachk: invalid device specified for archive destination
OSD-04018: Unable to access the specified directory or device
O/S-Error: (OS 2) The system cannot find the file specified
An ORA-00256 error also occurs if you enter \\\meldell\rmdrive or \\\meldell\\rmdrive. Control files required the additional backslashes for Oracle8 release 8.0.4, but redo log files and datafiles did not.
|
Note:
When you use Universal Naming Convention path with external tables, you must ensure that Universal Naming Convention share names do not match with the local share names on which the database server runs. If the Universal Naming Convention share names and the local share names match, then it will result in an error. |
Archiving Redo Log Files
If you installed Oracle Database through the Typical installation, then it is created in NOARCHIVELOG mode. If you created your database through the Custom option of Database Configuration Assistant, then you had the choice of either ARCHIVELOG or NOARCHIVELOG.
In NOARCHIVELOG mode, redo logs are not archived. Setting your archive mode to ARCHIVELOG and enabling automatic archiving causes redo log files to be archived. This protects Oracle Database from both instance and disk failure.
PKbGS PK 6A OEBPS/preface.htmw
