This appendix describes Oracle Net Services configuration for Windows. For more generic information about Oracle Net Services configuration, see Oracle Database Net Services Administrator’s Guide.
This appendix contains these topics:
Configuring Oracle Database to Communicate with ASM
Oracle Database for Windows installations that use Automatic Storage Management (ASM) must use Windows native authentication, which is enabled by default. To ensure that it is, check that the sqlnet.ora file, by default located in ORACLE_BASE \ ORACLE_HOME \network\admin , has NTS enabled. For example:
Understanding Oracle Net Services Registry Parameters and Subkeys
The registry contains entries for Oracle Net Services parameters and subkeys. To successfully add or modify Oracle Net Services configuration parameters, you must understand where they are located and the rules that apply to them.
Oracle Net Service Subkeys
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services contains subkeys that correspond to services. Depending on what is installed, your Oracle Net Services consist of all or a subset of the following:
Oracle HOME_NAME ClientCache
Oracle HOME_NAME CMAdmin
Oracle HOME_NAME CMan
Oracle HOME_NAME TNSListener
Each service subkey contains the parameters shown in Table C-1.
Table C-1 Service Subkey Parameters
Parameter
Description
Specifies service name.
Specifies fully qualified path name of executable invoked by service and any command line arguments passed to executable at runtime.
Specifies logon user account and computer to which service should log on.
Listener Requirements
In Oracle Database 10 g Release 1 (10.1) or later, the listener is set to start automatically at system restart. If you intend to use only the listener for all of your databases, ensure that only the Windows service for the listener, as listed in the Control Panel, is set to start automatically.
Oracle usually recommends that you only have a single net listener service running on a Windows computer at any one time. This single listener can support multiple databases. If you must have two different net listener services running on a Windows computer at the same time, ensure that they are configured to listen on different TCP/IP port numbers.
If the same IP address and port are used for different listeners, you might expect that the second and subsequent listeners would fail to bind. Instead, Windows allows them all to listen on the same IP address and port, resulting in unexpected behavior of the listeners. This is a suspected Windows operating system problem with TCP/IP and has been reported to Microsoft.
Understanding Optional Configuration Parameters
You can use the following parameters on Windows:
Oracle Net Service first checks for the parameters as environment variables, and uses the values defined. If environment variables are not defined, it searches for these parameters in the registry.
LOCAL
You can use parameter LOCAL to connect to Oracle Database without specifying a connect identifier in the connect string. The value of parameter LOCAL is any connect identifier, such as a net service name. For example, if parameter LOCAL is specified as finance , you can connect to a database from SQL*Plus with:
Oracle Net checks if LOCAL is defined as an environment variable or as a parameter in the registry, and uses finance as the service name. If it exists, Oracle Net connects.
TNS_ADMIN
You can add parameter TNS_ADMIN to change the directory path of Oracle Net Services configuration files from the default location of ORACLE_HOME \network\admin . For example, if you set TNS_ADMIN to ORACLE_BASE \ ORACLE_HOME \test\admin , the configuration files are used from ORACLE_BASE \ ORACLE_HOME \test\admin .
USE_SHARED_SOCKET
The use of shared sockets is enabled by default ( USE_SHARED_SOCKET=true ) allowing the network listener to pass the socket descriptor for client connections to the database thread. As a result, the client does not need to establish a new connection to the database thread and database connection time improves. Also, all database connections share the port number used by the network listener, which can be useful if you are setting up third-party proxy servers. Setting USE_SHARED_SOCKET to false disables the use of shared sockets.
This parameter only works in dedicated server mode in a TCP/IP environment. To spawn a dedicated server for an instance of Oracle Database not associated with the same Oracle home as the listener and with shared socket enabled, you must also set USE_SHARED_SOCKET parameter for both the Oracle homes.
Advanced Network Configuration
The following sections describe advanced configuration procedures specifically for Oracle Net Services on Windows operating systems.
Configuring Authentication Method
Oracle Net Services provides authentication methods for Windows operating systems using Windows Native Authentication.
Configuring Security for Named Pipes Protocol
The network listener service may be unable to open the Named Pipe created by Oracle Names unless service Oracle HOME_NAME TNSListener has a valid user ID and password associated with it.
Your operating system documentation for instructions on setting up network listener permissions
Modifying Configuration of External Procedures for Higher Security
This section supplements generic information provided in Oracle Database Net Services Administrator’s Guide to configure a listener on Windows operating systems to exclusively handle external procedures. For a higher level of security, you are instructed to start the listener for external procedures from a user account with lower privileges than the oracle user. For Windows operating systems, this requires that you change the user account from LocalSystem to a local, unprivileged user for the OracleHOME_NAMETNSListener extproc_listener_name service .
The following instructions assume that you have performed steps 1 through 5 in the section «Modifying Configuration of External Procedures for Higher Security» in Oracle Database Net Services Administrator’s Guide .
To change the listener account:
Create a new user account and grant it Log on as a Service privilege.
Ensure that this user account does not have general access to files owned by oracle . Specifically, this user should not have permission to read or write to database files or to the Oracle Database server address space. In addition, this user should have read access to the listener.ora file, but must not have write access to it.
Stop service OracleHOME_NAMETNSListener extproc_listener_name .
Your operating system documentation for instructions on accessing the Services dialog and stopping services
If the OracleHOME_NAMETNSListener extproc_listener_name service does not exist, issue the following command from the command prompt:
This creates the OracleHOME_NAMETNSListener extproc_listener_name service. When you return to the list of services, stop this service before proceeding to the next step of this procedure.
Select OracleHOME_NAMETNSListener extproc_listener_name service in the Services dialog and then display the properties of the service.
Select This Account and enter the username and password.
Start the listener by clicking Start . You must start the listener in this way because you cannot use the Listener Control utility to start the listener running as an unprivileged local user.
C Running Windows Services
Oracle Database 12 c Release 1 (12.1) supports Windows services to run under low-privileged, non-administrative accounts such as the LocalService, or an authenticated Windows User Account instead of the high-privileged Local System Account (LSA) for better security.
About Windows Services for Oracle Database
Starting with Oracle Database 12 c Release 1 (12.1), ORADIM creates Oracle Database service, Oracle VSS Writer service, and Oracle Scheduler service to run under the Oracle Home User account. Oracle Home User is the standard Windows User Account (not an Administrator), specified during installation, that runs most of the Windows services required by Oracle for Oracle home.
If this Oracle Home User is a Windows Local User Account or Windows Domain User Account, then ORADIM prompts for password for that account and accepts the same through stdin .
All Oracle administration tools that create Windows services have been modified to prompt for the password of Oracle Home User when the Oracle Home User is a Windows Local User Account or a Windows Domain User Account, and the password for Oracle Home User is not stored in the Oracle Wallet.
This section discusses the following topics in detail:
Running Windows Services in Oracle Home
Depending on the type of database installation and user account used as the Oracle Home User, Windows services run under a low-privileged, non-administrative accounts such as LocalService, or an authenticated Windows User Account, or as a high-privileged Local System Account (LSA) in Oracle home.
Table C-1 Running Windows Services
Type of Installation
Oracle Home User
Windows Service User for the Services
Oracle Database Server
Windows User Account
Windows User Account
Oracle Database Server
Local System Account
Oracle Database Client
Windows User Account
Windows User Account
Oracle Database Client
Oracle Grid Infrastructure (with the Grid Infrastructure Management Repository)
Windows User Account
Grid Listeners using LocalService
Database services using Windows User Account
Foot 1 Clusterware services using Local System Account
Oracle Grid Infrastructure (without the Grid Infrastructure Management Repository)
Grid Listeners using LocalService
Clusterware services using Local System Account
Footnote 1 Clusterware requires administrative privileges so it always uses Local System Account to run Windows services.
Additional Privileges Required by Oracle Database Services
Certain functions performed by the Oracle Database service require additional privileges. Oracle Universal Installer and other Oracle tools automatically grant the following privileges to the Windows service SID s of the respective services during the creation of these services:
SeIncreaseBasePriorityPrivilege : A process requires this privilege to change the priority of its threads. This privilege is granted to Windows service SID s of Oracle Automatic Storage Management (Oracle ASM) or Oracle Database services.
SeBackupPrivilege : This privilege is required to perform backup operations. It is granted to the Windows service SID s of Oracle VSS Writer service.
SeBatchLogonRight : This privilege is required for an account to log on using the batch logon type. It is granted to the Windows service SID s of Oracle Scheduler service.
To enable Oracle Database to use Large Pages or working set features, the following additional operating system privileges must be manually granted by the operating system administrator to either the Oracle Home User or to the Windows service SID s of the specified Oracle Database service during the creation of these services.
Oracle recommends granting privileges to the Windows service SID of Oracle Database service instead of the Oracle Home User. The Windows service SID of the database service will be in the following syntax NT AUTHORITY\OracleService SID .
SeLockMemoryPrivilege : This privilege is required to lock pages in memory. Oracle Database requires this privilege to use Large Pages. See «Overview of Large Page Support» for more information.
SeIncreaseQuotaPrivilege : This privilege is required to change the memory quota for a process. This is needed while setting the max and min working set sizes for the database.
Granting Additional Operating System Privileges Manually
To grant an operating system privilege to a specific user, perform the following steps:
From the Start menu, select Control Panel .
Double-click Administrative Tools .
Double-click Local Security Policy .
In the left pane of the Local Security Policy window, expand Local Policies and select User Rights Assignment .
In the right pane of the Local Security Policy window, double-click the relevant user privilege. For example, select Adjust memory quotas for a process to change the memory quota for a process or select Lock pages in memory to use Large Pages.
Click Add User or Group .
Enter the Oracle Home User name in Enter the object names to select field and click Check Names .
Click OK to close the Select Users, Computers, Service Accounts, or Groups dialog box.
Click OK to close the Properties window for the privilege.
6 Administering a Database on Windows
This chapter describes how to administer Oracle Database for Windows.
This chapter contains these topics:
About Ways to Manage 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:
Overview of 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 the 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.
Using the Control Panel
To start Oracle Database services from the Control Panel:
Access your Windows Services dialog box.
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 box.
Using the Command Prompt
To start Oracle Database services from the command prompt, enter:
The variable service is a specific service name, such as OracleServiceORCL.
Using Oracle Administration Assistant for Windows
To start Oracle Database services from Oracle Administration Assistant for Windows:
From the Start menu, select All Programs , then select Oracle — HOMENAME , then select Configuration and Migration Tools , and then select Administration Assistant for Windows .
Right-click the SID .
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 reinstalling 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.
Using the Control Panel
To stop Oracle Database services from the Control Panel:
Access your Windows Services dialog box.
Your operating system documentation for instructions
Select Oracle HOMENAME TNSListener and click Stop .
Oracle HOMENAME TNSListener is stopped.
Select OracleService SID and click Stop .
OracleService SID is stopped.
Using the Command Prompt
To stop Oracle Database services from the command prompt, enter:
The variable service is a specific service name, such as OracleServiceORCL .
Using Oracle Administration Assistant for Windows
To stop Oracle Database services from Oracle Administration Assistant for Windows:
From the Start menu, select All Programs , then select Oracle — HOMENAME , then select Configuration and Migration Tools, and then select Administration Assistant for Windows .
Right-click the SID .
The variable 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:
Using the Control Panel
To use the Control Panel to configure when and how Oracle Database is started:
Access your Windows Services dialog box.
Your operating system documentation for instructions
Select the service OracleServiceSID and click Startup .
Select Automatic from the Startup Type field.
Click Close to exit the Services dialog box.
Using Oracle Administration Assistant for Windows
To automatically start Oracle Database services from Oracle Administration Assistant for Windows:
From the Start menu, select All Programs , then select Oracle — HOMENAME , then select Configuration and Migration Tools , and then select Administration Assistant for Windows .
Right-click the SID .
The variable SID is a specific instance name, such as orcl .
Select Startup/Shutdown Options .
Select the Oracle NT Service tab.
Select Automatic in Oracle NT 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 directories during installation that do not comply with OFA guidelines, then your directory paths differ. See Appendix B, «Optimal Flexible Architecture», in Oracle Database Installation Guide for Microsoft Windows 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 C:\app\username\product\11.2.0\admin\orcl\pfile, 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:
The 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 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 the 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 in two different ways:
U sing 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 All Programs , then select Oracle — HOMENAME , then select Configuration and Migration Tools and then select Administration Assistant for Windows .
Right-click the SID .
The variable SID is a specific instance name, such as ORCL .
Select Startup/Shutdown Options .
Select 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 tries 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. The 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 still shuts down Oracle Database. But it is 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 ). The default mode is i ( immediate ) if you do not set this parameter.
ORA_ SID _SHUTDOWN_TIMEOUT
This parameter sets the 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:
The variable 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 16, «Configuration Parameters and the Registry» for instructions on adding and editing registry parameters
Starting or Stopping OracleServiceSID from the Control Panel
To start the database, start OracleService SID .
This automatically starts ORADIM and enters 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 enters 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
Perform the following steps to start service for multiple Oracle Database instance:
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:
The variable SID is the name of the Oracle Database instance.
Connect AS SYSDBA :
Start up the first instance:
The variable ORACLE_BASE is c:\app\ username (unless you changed it during installation) and db_name is the name of the instance.
Repeat Step 2 through Step 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 the directory 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 Password Utility:
FILE specifies the password file name.
SID identifies the database instance.
ENTRIES sets maximum number of entries in the 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 the 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 the parameter ORA_SID_PWFILE . If no value is specified, then Oracle Database looks in the registry for the value of the parameter ORA_PWFILE , which points to a file containing user names, passwords, and privileges. If that is not set, then Oracle Database uses the default:
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.
Connect AS SYSDBA :
For an Oracle ASM instance, connect AS SYSASM :
Start Oracle Database:
Grant appropriate privileges to each user. Users who must perform database administration, for example, would be granted the SYSDBA privilege:
For an Oracle ASM instance:
If the grant is successful, then the following message is displayed:
This adds smith to the password file and enables smith to connect to the database with SYSDBA privileges. Use SQL*Plus to add or delete user names, user passwords, and user privileges in password files.
Copying or manually moving password files might 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.
Using Command Prompt
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:
Using Windows Explorer
To make the password file invisible or visible again:
Go to the directory ORACLE_HOME \database .
Right-click PWD sid .ora .
The PWD sid .ora Properties dialog box opens.
In Attributes , check or clear the check box next to Hidden .
To view or hide an invisible password file:
Go to the directory ORACLE_HOME \database .
Select Folder Options from the Tools main menu.
In the Folder Options window, select the View tab.
To view an invisible password file, select Show hidden files and folders .
To hide a visible password file, select Do not show hidden files and folders .
Connecting Remotely to the Database
You can connect to Oracle Database remotely. There are many steps you must remember while connecting to the database remotely. They are as follows:
Connecting to a Database Using SYSDBA Privileges
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.
Verifying a Remote Database Using Encrypted 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.
The configuration parameter ORA_ENCRYPT_LOGIN is retained for backward compatibility and is set to true by default. See Chapter 16, «Configuration Parameters and the Registry» for instructions on adding and setting configuration parameters in the registry.
About 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 Oracle 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.
