Oracle Database Tips by Donald BurlesonJanuary 1, 2015
Just like how ORACLE_SID works on UNIX/Linux, the ORACLE_SID identifies the name of a specific database instance and tells the Operating System which Oracle Instance to apply your commands. ORACLE_SID is stored in the registry.
However, any time a database needs to be accessed via the command prompt or with a script, then ORACLE_SID needs to be set. The command line syntax is simply:
Note that there are no spaces around the equal sign.
In Windows, the LOCAL command can also be used to define the ORACLE_SID.
Burleson is the American Team
Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals. Feel free to ask questions on our Oracle forum .
Verifyexperience!Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.
Errata? Oracle technology is changing and we strive to update our BC Oracle support information. If you find an error or have a suggestion for improving our content, we would appreciate your feedback. Just e-mail:
Oracle ® is the registered trademark of Oracle Corporation.
Windows setting oracle sid
Describes Oracle Database for Windows registry parameters for the following keys.
Other products, such as Oracle Enterprise Manager, have additional keys and parameters that are not described.
About HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_HOMENAME Each time you install Oracle products into a new Oracle home on your computer, HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_ HOMENAME is created.
About HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE This subkey contains the following parameter:
About HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet contains the following keys:
About HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_HOMENAME
Each time you install Oracle products into a new Oracle home on your computer, HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_ HOMENAME is created.
This subkey contains parameter values for most Oracle products.
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_ HOMENAME includes the following parameters for an Oracle home directory. Depending on products you install, additional parameters can also be created. See your Windows development manuals.
MSHELP_TOOLS Specifies the location of Windows help files.
NLS_LANG and Other Globalization Parameters Specifies supported language, territory, and character set.
ORA_CWD Specifies current working directory.
ORA_SID_AUTOSTART Starts Oracle Database when OracleService SID service is started.
ORA_SID_PFILE Specifies full path to initialization parameter file.
ORA_SID_SHUTDOWN When set to true , the default value, this parameter shuts down the instance of Oracle Database identified by SID when OracleService SID is stopped manually—using either the Control Panel or Net stop command.
ORA_SID_SHUTDOWN_TIMEOUT Sets maximum time (in seconds) to wait for shutdown to complete before the service for a particular SID stops.
ORA_SID_SHUTDOWNTYPE Specifies mode in which Oracle Database is shut down when you stop OracleService SID .
ORA_TZFILE Specifies the location of time zone file.
ORACLE_AFFINITY Enables the scheduling of class threads on more than one processor group for systems with more than 64 CPUs.
ORACLE_BASE Specifies the top-level Oracle directory (for example, C:\app\username\product\12.2.0 ) that contains ORACLE_HOME , admin , and oradata .
ORACLE_GROUP_NAME Specifies the name of the group containing icons of the Oracle products installed.
ORACLE_HOME Specifies Oracle home directory in which Oracle products are installed.
ORACLE_HOME_KEY The HKEY_LOCAL_MACHINE location of Oracle parameters.
ORACLE_HOME_USER A string type entry that holds the Oracle Home User value.
ORACLE_HOMENAME Specifies home name of Oracle home directory in which Oracle products are installed.
ORACLE_PRRITY Determines Windows scheduling priorities of the threads within the Oracle Database management system process.
ORACLE_SID Specifies the name of the Oracle Database instance on the host computer.
OSAUTH_PREFIX_DOMAIN Enables user authentication.
RDBMS_ARCHIVE Specifies the location of backup database files.
RDBMS_CONTROL Specifies the location of backup database control files.
SQLPATH Specifies the location of SQL scripts.
Oracle Database Installation Guide for Microsoft Windows Appendix B, «Optimal Flexible Architecture» for details on the PATH variable and registry values when you are working with multiple Oracle homes.
MSHELP_TOOLS
Specifies the location of Windows help files.
The default value is:
NLS_LANG and Other Globalization Parameters
Specifies supported language, territory, and character set.
This parameter specifies the language in which messages appear, the territory and its conventions for calculating week and day numbers, and the character set displayed. Oracle Universal Installer sets this value during installation based on the language setting of the operating system.
The default value for NLS_LANG, if not set, is AMERICAN_AMERICA.US7ASCII.
There are other globalization parameters that can be set along NLS_LANG to override some values implicitly determined by NLS_LANG. These parameters are:
The following parameters can also be set along NLS_LANG to determine globalization behavior that is independent from the value of NLS_LANG:
All globalization parameters set in the environment and Registry for a database client are ignored if NLS_LANG is not set.
Oracle Database Globalization Support Guide for more information about NLS_LANG and other globalization parameters
ORA_CWD
Specifies current working directory.
For example, if you set this parameter and then use ORADIM, a log file called oradim.log is created in this directory. This parameter must be manually set.
ORA_SID_AUTOSTART
Starts Oracle Database when OracleService SID service is started.
The default value is true.
ORA_SID_PFILE
Specifies full path to initialization parameter file.
The default value is ORACLE_BASE \admin\DB_NAME\pfile\init.ora
ORA_SID_SHUTDOWN
When set to true , the default value, this parameter shuts down the instance of Oracle Database identified by SID when OracleService SID is stopped manually—using either the Control Panel or Net stop command.
ORA_SID_SHUTDOWN_TIMEOUT
Sets maximum time (in seconds) to wait for shutdown to complete before the service for a particular SID stops.
The default value is 30 .
ORA_SID_SHUTDOWNTYPE
Specifies mode in which Oracle Database is shut down when you stop OracleService SID .
Valid values are a (abort), i (immediate), and n (normal). The default value is i .
ORA_TZFILE
Specifies the location of time zone file.
Each file contains:
Valid time zone names
Offset from UTC
Abbreviation for standard time
Abbreviation for daylight savings time
In previous releases, the default value for ORA _TZFILE was
Starting with Oracle Database 11 g Release 2 (11.2), the default value is
The timezone_ version_number .dat data files contain most commonly used time zones and are smaller for better database performance. The new default, timezlrg_ version_number .dat , includes time zones not defined in the smaller file.
Oracle Database Globalization Support Guide for additional details about time zone files
ORACLE_AFFINITY
Enables the scheduling of class threads on more than one processor group for systems with more than 64 CPUs.
This parameter must be manually added. Oracle recommends consulting Oracle Support Services before changing this parameter. The format is:
Where, processorgroup is an optional parameter designating Windows CPU group. On systems with 64+ logical CPUs, Windows divides all available CPUs into 4 groups (0,1,2,3) with each group containing no more than 64 logical CPUs. By default, a process utilizes single processor group. The processorgroup parameter enables Oracle to use more than 64 logical CPUs. Refer to the specific hardware configuration to determine the valid processor groups.
You should not use the ORACLE_AFFINITY parameter with multiple processor groups on a system with fewer than 64 logical cores. On production servers any system with fewer than 64 logical CPUs can have only one processor group.
Each name n setting must be the name of a background thread, USER for non-background (shadow) threads, or DEF for any thread type not handled specifically. Valid background thread names include DBW0 , LGWR , PMON , SMON , ARCH , RECO , CKPT , TRWR , J000 through J999 , P000 through P481 , and any other name found in the NAME column of the v$bgprocess data dictionary view.
The cpumask sets the affinity mask of the Oracle Database process. Each affinity setting must be a valid affinity mask or its numeric equivalent for the corresponding thread name. Process affinity masks are used only when Oracle Services are first started. Each thread’s affinity is set only when the individual thread is started (for example, at database startup time for the background threads).
Few examples, to use multiple processor groups in a system with 160 logical CPUs, ORACLE_AFFINITY registry key in HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_HOMENAME may be defined as follows:
The following examples show how set the ORACLE_AFFINITY registry key in HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_HOMENAME to use multiple processor groups in a system with 160 logical CPUs. In the following examples, it is assumed that: USER , DEF are thread class names; 0,1,2,3 are valid CPU groups in the system; and 4294967295 is a valid affinity mask in the corresponding CPU group.
Affinitize USER (foreground) threads to all CPUs in processorgroup1 or to all CPUs in processorgroup2 or to all CPUs in processorgroup3 while alternating between the processor groups for each new foreground thread. Also, affinitize DEF class threads to CPUs 0-31 in processorgroup0 .
Affinitize USER class threads either to CPUs 0-19 in processorgroup0 or to CPUs 16-31 in processorgroup2 . Also, affinitize DEF class threads to CPUs 0-19 in processorgroup1 .
Affinitize USER class threads to all the CPUs of all processor groups while alternating between the processor groups for each new foreground thread. Also, affinitize DEF class threads to CPUs 0-31 in all the processor groups while alternating between the processor groups for each new DEF class thread.
Affinitize USER class threads to CPUs 0-31 in processorgroup0 , CPUs 0-19 in processorgroup1 and CPUs 0-19 in processorgroup2 while alternating between the processor groups for each new foreground thread.
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 about 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 Itanium (64-Bit) , 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:
You can start Oracle Database when you start OracleService SID . See «Starting and Shutting Down a Database Using Services» for information about registry parameters that enable you to do this.
To start Oracle Database services from the Control Panel:
Access your Windows Services dialog.
Your operating system documentation for instructions
Find the service to start in the list, select it, and click Start .
If you cannot find OracleService SID in the list, then use ORADIM to create it.
Click Close to exit the Services dialog.
To start Oracle Database services from the command prompt, enter:
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:
Choose Start > Programs > Oracle — HOME_NAME > Configuration and Migration Tools > 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:
You can stop Oracle Database in normal, immediate, or abort mode when you stop OracleService SID . See «Starting and Shutting Down a Database Using Services» for information about registry parameters that enable you to do this.
To stop Oracle Database services from the Control Panel:
Access your Windows Services dialog.
Your operating system documentation for instructions
Select Oracle HOME_NAME TNSListener and click Stop .
Oracle HOME_NAME TNSListener is stopped.
Select OracleService SID and click Stop .
OracleService SID is stopped.
To stop Oracle Database services from the command prompt, enter:
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:
Choose Start > Programs > Oracle — HOME_NAME > Configuration and Migration Tools > 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:
To use the Control Panel to configure when and how Oracle Database is started:
Access your Windows Services dialog.
Your operating system documentation for instructions
Select service OracleServiceSID and click Startup .
Choose Automatic from the Startup Type field.
Click Close to exit the Services dialog.
Oracle Administration Assistant for Windows
To automatically start Oracle Database services from Oracle Administration Assistant for Windows:
Choose Start > Programs > Oracle — HOME_NAME > Configuration and Migration Tools > 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 .
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.
Directory path examples in this chapter follow Optimal Flexible Architecture (OFA) guidelines. If you specified non-OFA compliant directories during installation, then your directory paths will differ. See Appendix B, «Optimal Flexible Architecture», in Oracle Database Installation Guide for Microsoft Windows Itanium (64-Bit) for more information.
To start or shut down Oracle Database:
Go to your Oracle Database server.
Start SQL*Plus at the command prompt:
Connect to Oracle Database with username SYSDBA :
To start a database, enter:
This command uses the initialization parameter file specified in path \ filename . To start a database using a file named init2.ora located in
you would enter:
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:
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.
Chapter 2, «Database Tools on Windows» for a list of other tools that can start Oracle Database and this guide for information about 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 OracleService SID in the Control Panel. Starting OracleService SID is equivalent to using the STARTUP command or manually entering:
Stopping OracleService SID is equivalent to using the SHUTDOWN command or manually entering:
You can enable starting and stopping Oracle Database through OracleService SID two different ways:
Oracle Administration Assistant for Windows
To start or stop a database using Oracle Database services from Oracle Administration Assistant for Windows:
Choose Start > Programs > Oracle — HOME_NAME > Configuration and Migration Tools > 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 OracleService SID 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 .
When set to true , this parameter enables the selected instance of Oracle Database to be shut down when OracleService SID 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 OracleService SID is stopped manually—using either the Control Panel or Net stop command.
If ORA_SHUTDOWN or ORA_SID_SHUTDOWN is set to false , then manually shutting down OracleService SID 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:
If you have multiple Oracle home directories, then these parameters belong in:
where ID is incremented for each additional Oracle home directory on your computer.
If you use ORADIM to create or edit instances, then it automatically sets the relevant registry parameters to their appropriate values.
Chapter 14, «Configuration Parameters and the Registry» for instructions on adding and editing registry parameters
Starting or Stopping OracleService SID from the Control Panel
To start the database, start OracleService SID .
This automatically starts ORADIM and issues the — STARTUP command using the initialization parameter file identified by ORA_ SID _PFILE .
To stop the database, stop OracleService SID .
This automatically starts ORADIM, which issues the -SHUTDOWN command in the mode indicated by ORA_ SID _SHUTDOWNTYPE , and shuts down Oracle Database.
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:
where SID is the name of the Oracle Database instance.
Connect AS SYSDBA :
Start up the first instance:
where ORACLE_BASE is c:\oracle\product\10.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_BASE \ ORACLE_HOME \DATABASE and are named PWD sid .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:
FILE specifies the password filename.
SID identifies the database instance.
PASSWORD sets the password for account SYS .
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:
The default value is shared . It specifies that multiple instances (for example, a Real Application Clusters 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.
Connect AS SYSDBA :
Start Oracle Database:
Grant appropriate privileges to each user. Users who must perform database administration, for example, would be granted privilege SYSDBA :
If the grant is successful, then the following message displays:
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.
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:
The password file must be visible before it can be moved, copied, or deleted.
To see the password file, enter:
The password file is displayed as PWD sid .ora :
To make the password file invisible, enter:
To see the effect of the change, enter:
The password file is now hidden:
To make the password file visible again, enter:
To make the password file invisible or visible again:
Navigate to directory ORACLE_BASE \ ORACLE_HOME \database .
Right-click PWD sid .ora .
The PWD sid .ora Properties dialog opens.
In Attributes , check or clear the checkbox next to Hidden .
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 .
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 Itanium (64-Bit) 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.
Configuration parameter ORA_ENCRYPT_LOGIN is retained for backward compatibility and is set to true by default. See Chapter 14, «Configuration Parameters and the Registry» for instructions on adding and setting configuration parameters in the registry.
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.
This section describes how to change archive mode to ARCHIVELOG and enable automatic archiving.
For complete descriptions of ARCHIVELOG and NOARCHIVELOG modes, see «Managing Archived Redo Logs» in Oracle Database Administrator’s Guide
Changing Archive Mode to ARCHIVELOG
Start SQL*Plus at the command prompt:
Connect to Oracle Database as SYSDBA :
If the database is open, then shut it down:
Next, mount the database:
Enter the following command:
The following output indicates the database is not in archive mode:
Change archive mode to ARCHIVELOG :
Enter the following command:
The following output indicates the database is now in archive mode:
Open the database:
Enabling Automatic Archiving
Open file ORACLE_BASE \ADMIN\ db_name \pfile\init.ora .
Find the following three initialization parameters:
The LOG_ARCHIVE_DEST_n parameter can specify standby destinations that are running Oracle Standard Edition, but only when specifying local destinations with the LOCATION attribute. See «Oracle Software Requirements» in Oracle Data Guard Concepts and Administration for details.
Remove the # sign from in front of each.
Double quotation marks around LOG_ARCHIVE_FORMAT
Edit the LOG_ARCHIVE_DEST_ n value to identify an existing drive and directory in which to archive your filled redo logs.
Default size of Redo logs is approximately 100 MB.
Edit the LOG_ARCHIVE_FORMAT value to indicate the appropriate archiving format:
This format specifies the thread number. This number is padded to the left by zeroes. The default value is one with a range of up to three characters. Example: SID0001.ARC
This format specifies the log sequence number. This number is padded to the left by zeroes. The default value is one with a range of up to five characters. Example: SID0001.ARC
This format specifies the thread number. The number is not padded. The default value is one with no range limit on characters. Example: SID1.ARC
This format specifies the log sequence number. The number is not padded. The default value is one with no range limit on characters. Example: SID1.ARC
Save your changes.
Shut down the database:
Restart the database
Enter the following command:
The following output indicates that automatic archiving of redo log files is enabled and an archiving destination is specified:
and include the URL for the page. 
