2 Starting Up and Shutting Down

This section describes how to start up and shut down Oracle Database Express Edition. It contains the following topics:

Starting Up the Database

Oracle Database Express Edition (Oracle Database XE) starts up automatically immediately after installation and after each system restart. Thus, there is no need to start up the database unless you previously shut it down.

You can start up the database from the desktop or with the SQL Command Line (SQL*Plus). Each of these methods is described in the following sections:

Starting Up the Data base from the Desktop

This section explains how to start up the database from the desktop in Windows and in the following two Linux windowing managers: KDE and Gnome. If your Linux computer is not running a windowing manager, or is running a windowing manager other than KDE or Gnome, you must start the database with the SQL Command Line. See «Starting Up the Database Using the SQL Command Line» for instructions.

To start up the database using the desktop:

Do one of the following:

On Windows: Log in to the Oracle Database XE host computer as the user that installed Oracle Database XE or as a Windows administrator—that is, as a user who is a member of the Administrator group.

On Linux: Log in to the Oracle Database XE host computer as the user that installed Oracle Database XE or as a user who is a member of the dba user group. This is typically the user oracle . See «Operating System Authentication» for more information.

Do one of the following:

On Windows: Click Start , point to Programs (or All Programs) , point to Oracle Database 11g Express Edition , and then select Start Database .

On Linux with Gnome: In the Applications menu, point to Oracle Database 11g Express Edition , and then select Start Database .

On Linux with KDE: Click the icon for the K Menu, point to Oracle Database 11g Express Edition , and then select Start Database .

Starting Up the Databas e Using the SQL Command Line

When you start up the database with the SQL Command Line, you must run the SQL Command Line on the same computer where you installed Oracle Database XE (the «Oracle Database XE host computer»).

To start up the database using the SQL Command Line:

Do one of the following:

On Windows: Log in to the Oracle Database XE host computer as the user that installed Oracle Database XE or as a Windows administrator—that is, as a user who is a member of the Administrator group.

On Linux: Log in to the Oracle Database XE host computer as the user that installed Oracle Database XE or as a user who is a member of the dba user group. This is typically the user oracle . See «Operating System Authentication» for more information.

If not already opened, open a terminal session or command window.

Linux platform only: Ensure that environment variables are set properly.

At the operating system prompt, enter the following command to start the SQL Command Line and connect to the database:

The slash (/) indicates that the database should authenticate you with operating system authentication.

At the SQL Command Line prompt, enter the following command:

If the command is successful, it displays output similar to the following. (System global area sizes will vary depending on the amount of physical memory in your Oracle Database XE host computer.)

(Optional) Enter the following SQL query to verify that the database started up properly:

The query results should look similar to the following:

To exit the SQL Command Line. enter the following command:

Shutting Down the Database

Oracle Database Express Edition (Oracle Database XE) shuts down automatically when you shut down the computer that hosts it. However, you can also shut Oracle Database XE down manually whenever you want, such as to reduce the overall system processing overhead when you do not need to use the database.

Before shutting down Oracle Database XE, it is best to ensure that all users and applications have completed their work and logged out. See «Monitoring Sessions» for information on how to view current database sessions.

If users or applications are still logged in when you begin a shutdown operation, the shutdown proceeds under the following conditions:

No new connections are permitted, and no new transactions are allowed to be started.

Any uncommitted transactions are rolled back.

All users and applications are immediately disconnected.

You can shut down the database with the desktop or with the SQL Command Line (SQL*Plus). Each of these methods is described in the following sections:

Shut ting Down the Database from the Desktop

This section explains how to shut down the database from the desktop in Windows and in the following two Linux windowing managers: KDE and Gnome. If your Linux computer is not running a windowing manager, or is running a windowing manager other than KDE or Gnome, you must shut down the database with the SQL Command Line.

To shut down the database using the desktop:

Do one of the following:

On Windows: Log in to the Oracle Database XE host computer as the user that installed Oracle Database XE or as a Windows administrator—that is, as a user who is a member of the Administrator group.

On Linux: Log in to the Oracle Database XE host computer as the user that installed Oracle Database XE or as a user who is a member of the dba user group. This is typically the user oracle . See «Operating System Authentication» for more information.

Do one of the following:

On Windows: Click Start , point to Programs (or All Programs) , point to Oracle Database 11g Express Edition , and then select Stop Database .

On Linux with Gnome: In the Applications menu, point to Oracle Database 11g Express Edition , and then select Stop Database .

On Linux with KDE: Click the icon for the K Menu, point to Oracle Database 11g Express Edition , and then select Stop Database .

Shutti ng Down the Database Using the SQL Command Line

When you shut down the database with the SQL Command Line, you must run the SQL Command Line on the same computer where you installed Oracle Database XE (the «Oracle Database XE host computer»).

To shut down the database using the SQL Command Line:

Do one of the following:

On Windows: Log in to the Oracle Database XE host computer as the user that installed Oracle Database XE or as a Windows administrator—that is, as a user who is a member of the Administrator group.

On Linux: Log in to the Oracle Database XE host computer as the user that installed Oracle Database XE or as a user who is a member of the dba user group. This is typically the user oracle . See «Operating System Authentication» for more information.

If not already opened, open a terminal session or command window.

Linux platform only: Ensure that environment variables are set properly.

At the operating system prompt, enter the following command to start the SQL Command Line and connect to the database:

The slash (/) indicates that the database should authenticate you with operating system authentication.

At the SQL Command Line prompt, enter the following command:

Note that this command may take a short while to complete. If the command is successful, it displays the following output:

If the command displays no output after a number of minutes, indicating that the shutdown operation is not proceeding, you can press CTRL-C to interrupt the command, and then enter the following command:

The database must go through a recovery process when it starts up after a SHUTDOWN ABORT command. It is recommended that you enable the recovery process to take place immediately, after which you can shut down the database normally. To do this, enter the following commands when the SHUTDOWN ABORT completes:

See Oracle Database Administrator’s Guide for information on the SHUTDOWN ABORT command.

To exit the SQL Command Line. enter the following command:

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:

You can start Oracle Database when you start OracleService SID . See «Starting and Shutting Down a Database Using Services» for information on 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:

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:

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 on 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:

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:

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:

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 .

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

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 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 15, «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:\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 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 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:

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 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 privilege SYSDBA :

For an ASM instance:

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_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 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 15, «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.

«Managing Archived Redo Logs» in Oracle Database Administrator’s Guide for more information about the archiving modes and the archiving process.