Slides © 2004-2007 SkillBuilders.

Instructor's Notes

(If applicable)

  • Lesson 6: Starting and Stopping an Instance
  • In this lesson you will learn how to startup and shutdown an Oracle database. (Technically, we should say “startup and shutdown an Oracle instance”. However, though “instance” is more accurate, in day-to-day conversation we tend to say things like “I’m going to shutdown the database”.)
  • You will learn all about the STARTUP and SHUTDOWN commands, including the phases of startup, privileges required and the different types of shutdown. Hands-on workshops will give you the chance to practice.
  • You will need to have accomplished a few things to be successful completing this lesson:
    • Oracle basics. You need a solid understanding of SQL, SQL*Plus, Oracle objects (tables, views, etc.)
    • Architecture. You need an understanding of architecture concepts such as “instance”, SGA and database files.
    • Administrative user. You will not only need access to a test Oracle database (one you can afford to lose), but you need a user with SYSDBA privileges. You should have already completed the lesson Creating Administrative Users in SkillBuilders' Oracle9i Database Administration course.

Other Text:
(Examples or comments displayed on slide, if any).

Slides © 2004-2007 SkillBuilders.

Instructor's Notes

(If applicable)

  • The first step in starting the database is to decide which tool you will use. Two popular choices – because both tools come with the Oracle database – are SQL*Plus and Oracle Enterprise Manager (OEM). SQL*Plus is a widely available line mode tool that is installed by default with all server and client installations. It is the tool we will use in this course. OEM is a graphical tool that can be used to administer multiple databases on multiple remote sites.
  • One you have started the administrative tool, you will need to connect with a user that has SYSOPER or SYSDBA privileges.
  • Once connected, enter the STARTUP command in the line mode tools, or select the startup option from OEM.
  • Let’s look at the steps in more detail…
  • Supplemental Notes
    • As of Oracle9i, Server Manager (SVRMGRL) is no longer supported. Server Manager was a line mode tool used for administrative functions. It has been replaced with SQL*Plus.
    • Note that prior to version 8, SQL*Plus did not support STARTUP/SHUTDOWN.

Other Text:
(Examples or comments displayed on slide, if any).

This lesson uses SQL*Plus

Slides © 2004-2007 SkillBuilders.

Instructor's Notes

(If applicable)

  • By default, SQL*Plus attempts to connect to an already started database. To prevent this, add the NOLOG option to the SQL*Plus executable. There are two executables – SQLPLUS.EXE and SQLPLUSW.EXE. Both can be found in the $ORACLE_HOME\BIN directory.
  • To create a SQL*Plus icon on the Windows desktop, open Windows Explorer and drag the file to the desktop.
  • SQLPLUSW.EXE is the Windows version; the other is a DOS version. Either version will get the job done. The nice thing about the DOS version is that the commands you have entered can be retrieved (with the UP arrow key), edited and rerun. The benefit of the windows version is the Page Up / Page Down scroll keys work and there is a Windows menu bar available that eliminates the need for some line mode commands. Since I rarely use the menu options, I prefer the DOS version. Of course, the choice is yours.

Other Text:
(Examples or comments displayed on slide, if any).

SQL> connect system@angela as sysdba
Enter password: ****
Connected to an idle instance.

Slides © 2004-2007 SkillBuilders.

Instructor's Notes

(If applicable)

  • We discussed the requirements for connecting with startup and shutdown privileges in the lesson entitled Administrative Users. Let's take a moment to review.
  • To start the instance, you will need connect “AS SYSDBA”.
  • If you are connected locally to the server (either you are sitting in front of the server or your Telnet’ed into it), and your OS id is in the operating system ORA_DBA group, you can login with “/ AS SYSDBA”, as shown above.
  • If you need to login remotely, you will need specify the host string as shown in the second example above. The host string is defined to Oracle Net (Oracle's networking software) in $ORACLE_HOME\NETWORK\admin\tnsnames.ora. We will discuss this concept in more detail in the lesson Introduction to Oracle Networking later in this course.
  • Supplemental Notes
    • An alternate syntax for connecting allows the password to be included as part of the connect string:
    • SQL> connect sys/dave@angela as sysdba
    • Connected to an idle instance.

Other Text:
(Examples or comments displayed on slide, if any).

SQL> connect / as sysdba
Connected to an idle instance.
(Must be in OS ORA_DBA group)

SQL> connect system@angela as sysdba
Enter password: ****
Connected to an idle instance.
(Host string defined in TNSNAMES.ORA)

Slides © 2004-2007 SkillBuilders.

Instructor's Notes

(If applicable)

  • Many existing databases will startup without any parameters specified on the STARTUP command. The sample database installed by the Oracle Database Configuration Assistant is a good example. Though we will discuss many startup and shutdown parameters and scenarios, the examples above show the most basic and simplest methods of starting and stopping an Oracle instance.

Other Text:
(Examples or comments displayed on slide, if any).

SQL> startup
ORACLE instance started.

Total System Global Area 126950220 bytes
. . .
Database opened.
SQL>

SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.

Slides © 2004-2007 SkillBuilders.

Instructor's Notes

(If applicable)

  • Startup Basics Workshop
    1. Setup SQL*Plus for your environment
    • Do this step if you’re using Windows-based SQL*Plus:
      1. Copy the SQL*Plus icon and rename the new icon SQLPlus NOLOG. Add the /NOLOG parameter to the Target field of the SQLPlus NOLOG icon so that it can be used to startup an idle instance. Also, set the Start In directory to your Oracle class directory. This directory is generally c:\ORACLASS. Ask your instructor if this directory does not exist.
      2. Start SQL*Plus using this new icon. Verify that you are in your Oracle class directory by typing HOST. After verifying your directory, return to SQL*Plus by typing EXIT.
    • Do this step if you are using UNIX or Linux-based SQL*Plus:
      1. Start SQL*Plus with the /NOLOG option from your Oracle class directory. This is generally $HOME . Verify that you are in your Oracle class directory by typing HOST . After verifying your directory, return to SQL*Plus by typing EXIT.

Other Text:
(Examples or comments displayed on slide, if any).

Slides © 2004-2007 SkillBuilders.

Instructor's Notes

(If applicable)

    1. If your database instance is started, use SQL*Plus to shut it down, then exit SQL*Plus. Use the new administrative user created in the previous workshop
    2. Restart the database instance. Use all defaults on STARTUP command.

Other Text:
(Examples or comments displayed on slide, if any).

Slides © 2004-2007 SkillBuilders.

Instructor's Notes

(If applicable)

  • The starting of an Oracle instance to connect to a database includes three phases (four if you consider shutdown a phase).
  • The first stage, called NOMOUNT, involves reading the database parameter file, initiating the Oracle background processes and allocating memory for the SGA. Because the instance exists (though not attached to a database yet), the following query will work when in the NOMOUNT stage:
    • SELECT * FROM v$instance;
  • The second stage is mounting the database. This consists of opening the control file and using the information in the control file to determine the location and names of the datafiles and the redo log files.
  • The following queries will work when in the MOUNT stage:
    • SELECT * FROM v$database;
    • SELECT * FROM v$controlfile;
  • Both queries access the control file.
  • The third stage of STARTUP opens the actual database files and redo logs. Once this stage has been completed, database objects such as tables can be accessed for normal use.

Other Text:
(Examples or comments displayed on slide, if any).

Slides © 2004-2007 SkillBuilders.

Instructor's Notes

(If applicable)

  • The STARTUP command is used to start the database, i.e. start an Oracle instance and open the database files. All parameters are optional. Here are some comments on the available parameters:
    • FORCE – If the database is running, it is shutdown with ABORT, then restarted. Use in extreme situations when you cannot normally start the database.
    • RESTRICT – Only users with the RESTRICTED SESSION system privilege can connect. Any user already connected can stay connected, but cannot reconnect unless they have the RESTRICTED SESSION privilege. The ALTER SYSTEM command can be used to disable the restricted session mode.


  • Notes for this slide continue on the next page…

Other Text:
(Examples or comments displayed on slide, if any).

STARTUP [FORCE] [RESTRICT] [PFILE=filename]
[QUIET] [ MOUNT [dbname] |
[OPEN [open_options] [dbname] ] |
NOMOUNT ]

open_options
READ {ONLY | WRITE [RECOVER]} | RECOVER

Slides © 2004-2007 SkillBuilders.

Instructor's Notes

(If applicable)

  • Notes continued from previous page:
    • PFILE=filename Causes the specified database parameter file to be used while starting up the instance. Oracle will use a default parameter file if this parameter is not specified. The default location is OS-dependent:
      • Linux/UNIX default - $ORACLE_HOME/dbs/init.ora
      • Windows default - %ORACLE_HOME%\database\init.ora
      • Oracle9i first searches for a Server Parameter file. See the lesson Initialization Parameter Files later in this course for more information.
    • QUIET – Suppress display of SGA information during startup.
    • MOUNT – Mounts a database but does not open it. This mode opens the control file(s) and, from the control file, determines the location and names of the other database files and redo log files. Needed for operations such as changing this archive mode of the database and database recovery.
    • OPEN – Mounts and opens the specified database. The database is ready for normal access. The default.
      • READ ONLY – Database files cannot be updated.
      • READ WRITE – Database files can be updated. The default.
      • RECOVER – Perform full (complete) media recovery, up to the last commit.
    • dbname – The name of the database to mount or open. If no database name is specified, the database name is taken from the initialization parameter DB_NAME.
    • NOMOUNT – Start the instance in NOMOUNT mode. This stage does not open the control file - used when creating the database (with CREATE DATABASE) and not often needed after that.

  • Supplemental Notes
    • Remember that you must be connected to a database as SYSOPER, or SYSDBA. You cannot be connected via a multi-threaded server, you must be connected via a dedicated server.
    • Refer to the Oracle9i SQL*Plus User's Guide and Reference manual for more details on the STARTUP command.

Other Text:
(Examples or comments displayed on slide, if any).

Slides © 2004-2007 SkillBuilders.

Instructor's Notes

(If applicable)

  • This example uses all the default startup options:
    • OPEN – the database will be mounted and opened – ready for use.
    • PFILE – The default server parameter file will be used. See more on server parameter files in the Initialization Parameter Files lesson.
    • DBNAME – The database name will be specified in the parameter file. If the database name does not match the database name in the control file, an error occurs.

Other Text:
(Examples or comments displayed on slide, if any).

SQL> startup
ORACLE instance started.

Total System Global Area 64035360 bytes
Fixed Size 453152 bytes
Variable Size 54525952 bytes
Database Buffers 8388608 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.

Slides © 2004-2007 SkillBuilders.

Instructor's Notes

(If applicable)

  • Use the PFILE parameter if you need to specify a non-default parameter file. This method is useful when you need to start up the instance with a different set of parameters - for running a long-running batch job for example, where the parameters have been tuned to optimize the batch job. See the lesson on Initialization Parameter Files later in this course for more information on handling parameter files in Oracle9i.
  • The second example demonstrates how to start an instance in NOMOUNT mode. After database creation, this mode might be useful when altering or recovering a control file, but not for much else.
  • The third example demonstrates starting an instance in MOUNT mode. This might be helpful, for example, if you needed to rename a datafile. (Datafile names are stored in the control file; mount indicates the control file is open, thus we are able to update it with ALTER DATABASE commands.)

Other Text:
(Examples or comments displayed on slide, if any).

SQL> STARTUP PFILE=d:\oracle\initdave.ora
SQL> STARTUP NOMOUNT
SQL> STARTUP MOUNT

Slides © 2004-2007 SkillBuilders.

Instructor's Notes

(If applicable)

  • The ALTER DATABASE statement can be used to change the mode of the database, for example from NOMOUNT to MOUNT or MOUNT to OPEN.
  • Syntax:
  • ALTER DATABASE [ dbname ] MOUNT | OPEN
    • MOUNT - Mount the database: open up the control file(s) and, from the control file, determines the location and names of the other database files and redo log files.
    • OPEN – Opens the datafiles and redo logs. The database is ready for normal operations at the successful completion of this command.
  • Refer to the Oracle9i SQL Reference manual for complete details on the ALTER DATABASE statement.

Other Text:
(Examples or comments displayed on slide, if any).

SQL> alter database mount;

Database altered.
SQL> alter database open;

Database altered.

Slides © 2004-2007 SkillBuilders.

Instructor's Notes

(If applicable)

  • Startup MOUNT Workshop
  • PART 1
    1. Connect with your new administrative user with the SYSDBA privilege (you created a new user in the lesson Creating Administrative Users). Shutdown your database.
    2. Start the database in NOMOUNT state.
    3. Attempt to connect without SYSDBA privilege. What happens and why? Connect with SYSDBA privilege.
    4. Query V$INSTANCE and V$SGA.
    5. Determine the database block size by querying V$PARAMETER WHERE NAME = 'db_block_size'. Note that the string ‘db_block_size’ must be in lower case.
    6. Attempt to query V$DATABASE and V$CONTROLFILE. What happens?


  • Workshop continues on the next page…

Other Text:
(Examples or comments displayed on slide, if any).

Slides © 2004-2007 SkillBuilders.

Instructor's Notes

(If applicable)

  • PART 2
    1. Mount the database.
    2. Execute these queries:
        • SELECT name FROM v$database;
        • SELECT instance_name, status FROM v$instance;
        • SELECT * FROM v$controlfile;
        • SELECT file#, status FROM v$datafile;
      • What happens in each case? What is the database status column contain in V$INSTANCE?
    1. Use ALTER to OPEN the database for normal operation.
    2. Query V$INSTANCE. What is the database status shown in V$INSTANCE?

Other Text:
(Examples or comments displayed on slide, if any).

Slides © 2004-2007 SkillBuilders.

Instructor's Notes

(If applicable)

  • Another STARTUP option which is useful to the database administrator is RESTRICT. The RESTRICT parameter brings the database through all three phases of startup but restricts access only to the DBA or, more specifically, to those users with RESTRICTED SESSION privilege. Note that both SYSDBA and the DBA role have RESTRICTED SESSION privilege.
  • This could be used, for example, to perform a full export of the database. If a full export is being performed on the database we do not wish the data to be changing. This would leave the data in the export dump file in an inconsistent state.

Other Text:
(Examples or comments displayed on slide, if any).

SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup restrict
ORACLE instance started.
. . .
SQL> connect scott/tiger@angela
ERROR:
ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege Warning: You are no longer connected to ORACLE.

Restricted mode

Slides © 2004-2007 SkillBuilders.

Instructor's Notes

(If applicable)

  • When altering the system into restricted access mode, users are left connected, but once they disconnect they cannot reconnect unless user has RESTRICTED SESSION privilege.

  • To set the database back to normal:
    • SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION

Other Text:
(Examples or comments displayed on slide, if any).

SQL> ALTER SYSTEM ENABLE
2 RESTRICTED SESSION
3 /

System altered.

Slides © 2004-2007 SkillBuilders.

Instructor's Notes

(If applicable)

  • In the same way that a database can be brought up in 3 stages, it is also brought down in three stages. However, we cannot control the process, i.e. Oracle will attempt to completely shutdown the database when the SHUTDOWN command is issued.
    1. The first stage of shutdown is to close the database files and the redo log files. Before this can be done, information in the SGA is flushed down to the database files for system and user data. Entries from the Redo buffer cache in the SGA is also flushed down to the active redo log file and a marker is made in the redo log to indicate that a shutdown has occurred.
    2. The second stage is to dismount the database which essentially means that the control file(s) is updated with synchronization information and the file(s) are closed.
    3. The third stage is to shut down the Oracle background processes and to release the memory occupied for the SGA.

Other Text:
(Examples or comments displayed on slide, if any).

Slides © 2004-2007 SkillBuilders.

Instructor's Notes

(If applicable)

  • The SHUTDOWN command will take the instance through all the three stages of shutdown.
    • SHUTDOWN – With no parameters specified, the SHUTDOWN command will wait for all users to disconnect. New sessions cannot be created after this command is executed. This is a "clean" (the data is in a consistent state) shutdown; no instance recovery is necessary. This is the same as SHUTDOWN NORMAL.
    • SHUTDOWN IMMEDIATE - Any connected users are immediately disconnected and any work in their current transaction is rolled back. No new sessions or transactions are allowed to start. Note that, despite the name "immediate", transaction roll back can take time; shutdown may not be immediate. If you truly need to shutdown the database immediately, see SHUTDOWN ABORT. This is a "clean" shutdown; no instance recovery is necessary at next startup. Ideally, a message should be sent to all users currently connected to disconnect from the database. There is no Oracle facility to do this - an operating system way of sending messages to users will need to be used - a particular problem for client server set-ups where the users are running the tools on another machine!
    • Notes continue on the next page…

Other Text:
(Examples or comments displayed on slide, if any).

Active transactions rolled back. Rollback takes time!

SQL> connect dave/dave@angela as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

Active transactions complete

SQL> shutdown transactional
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

SQL> shutdown abort
ORACLE instance shut down.
SQL>

Slides © 2004-2007 SkillBuilders.

Instructor's Notes

(If applicable)

    • SHUTDOWN TRANSACTIONAL – Active transactions are allowed to complete. Sessions are disconnected after their transaction completes, if applicable. No new sessions or transactions are allowed to start. This is a "clean" shutdown; no instance recovery is necessary at next startup.
    • SHUTDOWN ABORT – This option causes Oracle to immediately disconnect users without rolling back current transactions. This is the quickest way to shut down the database but this is a "dirty" shutdown; instance recovery will be performed automatically upon the next startup. This, the next startup can take longer. ABORT is required if one of the background processes terminates, e.g. DBWR. Do not perform backup operations after this type of shutdown.

Other Text:
(Examples or comments displayed on slide, if any).

Slides © 2004-2007 SkillBuilders.

Instructor's Notes

(If applicable)

  • Shutdown Workshop
    1. Start two SQL*Plus sessions. Connect AS SYSDBA in one of the ses&#