Skip Headers
Oracle® Database Platform Guide
12c Release 1 (12.1) for Microsoft Windows

E10714-06
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

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:

Using the Control Panel

To start Oracle Database services from the Control Panel:

  1. Access your Windows Services dialog box.

    See Also:

    Your operating system documentation for instructions
  2. 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.

  3. Click Close to exit the Services dialog box.

Using the Command Prompt

To start Oracle Database services from the command prompt, enter:

C:\> NET START service

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:

  1. From the Start menu, select All Programs, then select Oracle - HOMENAME, then select Configuration and Migration Tools, and then select Administration Assistant for Windows.

  2. Right-click the SID.

    SID is a specific instance name, such as orcl.

  3. 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:

Using the Control Panel

To stop Oracle Database services from the Control Panel:

  1. Access your Windows Services dialog box.

    See Also:

    Your operating system documentation for instructions
  2. Select OracleHOMENAMETNSListener and click Stop.

    OracleHOMENAMETNSListener is stopped.

  3. Select OracleServiceSID and click Stop.

  4. Click OK.

    OracleServiceSID is stopped.

Using the Command Prompt

To stop Oracle Database services from the command prompt, enter:

C:\> net STOP service

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:

  1. From the Start menu, select All Programs, then select Oracle - HOMENAME, then select Configuration and Migration Tools, and then select Administration Assistant for Windows.

  2. Right-click the SID.

    The variable SID is a specific instance name, such as orcl.

  3. 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:

  1. Access your Windows Services dialog box.

    See Also:

    Your operating system documentation for instructions
  2. Select the service OracleServiceSID and click Startup.

  3. Select Automatic from the Startup Type field.

  4. Click OK.

  5. 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:

  1. From the Start menu, select All Programs, then select Oracle - HOMENAME, then select Configuration and Migration Tools, and then select Administration Assistant for Windows.

  2. Right-click the SID.

    The variable SID is a specific instance name, such as orcl.

  3. Select Startup/Shutdown Options.

  4. Select the Oracle NT Service tab.

  5. Select Automatic in Oracle NT Service Startup Type.

  6. Click Apply.

  7. Click OK.

Description of ss_cnfg1.gif follows
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.

Note:

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:

  1. Go to your Oracle Database server.

  2. Start SQL*Plus at the command prompt:

    C:\> sqlplus /NOLOG
    
  3. Connect to Oracle Database with username SYSDBA:

    SQL> CONNECT / AS SYSDBA 
    
  4. 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\username\product\11.2.0\admin\orcl\pfile, enter:

    SQL> STARTUP PFILE=C:\app\username\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.

  5. To stop a database, enter:

    SQL> SHUTDOWN [mode]
    

    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.

    See Also:

    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 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 in two different ways:

Using Oracle Administration Assistant for Windows

To start or stop a database using Oracle Database services from Oracle Administration Assistant for Windows:

  1. From the Start menu, select All Programs, then select Oracle - HOMENAME, then select Configuration and Migration Tools and then select Administration Assistant for Windows.

  2. Right-click the SID.

    The variable SID is a specific instance name, such as ORCL.

  3. Select Startup/Shutdown Options.

  4. Select the Oracle Instance tab.

  5. Select Start up instance when service is started, Shut down instance when service is stopped, or both.

Description of ss_cnfg2.gif follows
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 tries 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. 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 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 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:

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0

If you have multiple Oracle home directories, then these parameters belong in:

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEID 

The variable 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.

See Also:

Chapter 16, "Configuration Parameters and the Registry" for instructions on adding and editing registry parameters

Starting or Stopping OracleServiceSID from the Control Panel

  1. To start the database, start OracleServiceSID.

    This automatically starts ORADIM and enters the -STARTUP command using the initialization parameter file identified by ORA_SID_PFILE.

  2. To stop the database, stop OracleServiceSID.

    This automatically starts ORADIM, which enters 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

Perform the following steps to start service for multiple Oracle Database instance:

  1. Start the service for each instance using ORADIM or the Services dialog of the Control Panel.

  2. At the command prompt, set the ORACLE_SID configuration parameter to the SID for the first instance to run:

    C:\> SET ORACLE_SID=SID
    

    The variable SID is the name of the Oracle Database instance.

  3. Start SQL*Plus:

    C:\> sqlplus /NOLOG
    
  4. Connect AS SYSDBA:

    SQL> CONNECT / AS SYSDBA
    
  5. Start up the first instance:

    SQL> STARTUP PFILE=ORACLE_BASE\admin\db_name\pfile\init.ora
    

    The variable ORACLE_BASE is c:\app\username (unless you changed it during installation) and db_name is the name of the instance.

  6. 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 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:

  1. Create a password file with Password Utility:

    C:\> orapwd FILE=PWDsid.ora ENTRIES=max_users
    
    • 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.

  2. 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:

    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.

  3. Start SQL*Plus:

    C:\> sqlplus /NOLOG
    
  4. Connect AS SYSDBA:

    SQL> CONNECT / AS SYSDBA
    

    For an Oracle ASM instance, connect AS SYSASM:

    SQL> CONNECT / AS SYSASM
    
  5. Start Oracle Database:

    SQL> STARTUP
    
  6. Grant appropriate privileges to each user. Users who must perform database administration, for example, would be granted the SYSDBA privilege:

    SQL> GRANT SYSDBA TO db_administrator;
    

    For an Oracle ASM instance:

    SQL> GRANT SYSASM TO SYS;
    

    If the grant is successful, then the following message is displayed:

    Statement Processed.
    

    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.

    Caution:

    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:

Using Command Prompt

  1. To see the password file, enter:

    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
    
  2. To make the password file invisible, enter:

    ORACLE_HOME\database> attrib +H PWDsid.ora
    
  3. 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
    
  4. To make the password file visible again, enter:

    ORACLE_HOME\database> attrib -H PWDsid.ora
    

Using Windows Explorer

To make the password file invisible or visible again:

  1. Go to the directory ORACLE_HOME\database.

  2. Right-click PWDsid.ora.

  3. Select Properties.

    The PWDsid.ora Properties dialog box opens.

  4. In Attributes, check or clear the check box next to Hidden.

  5. Click OK.

To view or hide an invisible password file:

  1. Go to the directory ORACLE_HOME\database.

  2. Select Folder Options from the Tools main menu.

  3. In the Folder Options window, select the View tab.

  4. To view an invisible password file, select Show hidden files and folders.

  5. To hide a visible password file, select Do not show hidden files and folders.

  6. Click OK.

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.

Note:

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.

Using Universal Naming Convention to Access Database Files Remotely

Oracle Database can access database files on a remote computer using Universal Naming Convention (UNC), but it might 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, the UNC specification for the file system01.dbf in the directory C:\app\username\product\11.2.0\oradata\orcl on shared server argon would be:

\\argon\app\username\product\11.2.0\oradata\orcl\system01.dbf

Locations of archive log files cannot be specified using UNC. Always set the 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.

Note:

When you use the 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.

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.

See Also:

"Managing Archived Redo Logs" in Oracle Database Administrator's Guide for more information about the archiving modes and the archiving process