:

The Oracle STARTUP command

If you are only interested in learning how to restart Oracle please read the section lower down on “STARTUP FORCE”. This is a quick way to restart your Oracle database but it’s not the recommended or cleanest approach!

Before I explain a little about how to start the Oracle database let’s clarify the different parts of the database and what they do at a very high level.

The Oracle Server – Background Info

The Oracle server, more commonly just called the Oracle database, has an instance and a set of files. The file are physically on disk, such as the files containing the actual data, configuration files (pfile and spfiles), REDO files, TEMP files, UNDO files, etc. The instance is made up of memory and processes. The memory is used to store the data from the files to make acess faster and allow sharing, sort data and hold session information among many other important tasks.

Oracle Database STARTUP Stages

So, what happens when you issue the start up command in Oracle? This is the rough breakdown of what happens:

1. Parameter file is read (could be pfile or spfile)

    Default location checked first for spfile If not found, or no pointer to where it is, the PFILE is read from the default location

2. Parameters are checked for correctness

3. Instance is started

Memory for SGA, PGA, etc

4. Processes are also started

SMON, PMON, MMON, LGWR, DBWn, etc

5. Control files are read

Location of data files, temp files, REDO log files found in control file are located on disk

Internal checking of highest SCN value in control file checked with all data file headers

6. Database is open in read/write mode and users are allowed to connect

UNDO tablespace is assigned Online REDO logs are opened for use.

If there is a problem at any stage of the startup command there will be an error written to the screen and to the alert log, so check there before issuing another start up command.

The startup command in Oracle can be broken down into smaller stages for various reasons. You might want to start up the instance but not read the control file, for example. Or you might want to startup the instance, read the control file but not open the database. The reasons for this vary, but they are usually for administrative operations such as renaming data file locations and recovery operations.

How to Start an Oracle Database

First of all, you have to have the SYSDBA or SYSOPER privileges to start up or shutdown an Oracle database. Grant these privileges with care, and always adhere to the least privilege rule

There are many different ways you can start up an Oracle database to put it into the state required to perform different tasks as I mentioned above. Let’s run through them:

STARTUP NOMOUNT

This comprises stages 1-3 above. This state is useful when recovering the control file from a backup because it starts all the processes but doesn’t read the control file, which enabled you to re-create it.

  1. Parameter file read
  2. Instance started
  3. Processes started

To move from the NOMOUNT state to the MOUNT state, issue the ALTER DATABASE MOUNT command.

STARTUP MOUNT

The mount state is one step further than the NOMOUNT state. It now reads the control file. This state is the most commonly used for database administration work. In the MOUNT state no one can connect to the database unless they have SYSDBA or SYSOPER level access, which should be reserved for DBAs. When you want to rename Oracle database files this is the state that you will put the database in.

  1. Stages 1-3
  2. Control file read

To move from the MOUNT state to the OPEN state, issue the ALTER DATABASE OPEN command.

All of the steps as listed above and will open the database in read write mode if possible.

STARTUP RESTRICT

The STARTUP RESTRICT option can be used with any of the other start up options. For example, STARTUP MOUNT RESTRICT. The RESTRICT mode prevents users connecting who don’t have the RESTRICTED SESSION privilege. By default, that is only anyone with the DBA role, SYSDBA and SYSOPER.

Once open, you can disable the restricted session by issuing the command ALTER SYSTEM DISABLE RESTRICTED SESSION;

If you want to put an open database into restricted mode you can do it using this command:

ALTER SYSTEM ENABLE RESTRICTED SESSION;

STARTUP FORCE

This is a really quick and dirty way to restart an Oracle database. Under the covers this command issues a shutdown abort (discussed later) and then a startup command. In essence, shutdown abort pulls the rug out from underneath the instance and it shuts down abnormally just like when media failure occurs.

This method should only be used when you cannot shutdown the database normally, using shutdown immediate. There are other times when I use it, though, but never on the production DB. Here are the times when you can use it to restart oracle:

1. To Improve Rollback Performance in Oracle

If someone has run a large update but it has failed and it’s rolling back you can use the option to speed up recovery. It works because rolling back will normally be single threaded from one session but if you restart the Oracle database it will recover in parallel (providing the settings are correct, fast_start_parallel_rollback and recovery_parallelism).
2. When you are refreshing it

We refresh our DEV DBs from our PROD data quite regularly. When we do this we’re not worried about the data in the DB so we quite often issue a startup force before dropping all of the users and running an import. We actually issue the STARTUP FORCE RESTRICT command. The reason for this is because if there is a user connected as the user that you are trying to drop it will not allow you to drop that user. using STARTUP FORCE for this works great because it quickly restarts the DB and it doesn’t get stuck like a SHUTDOWN IMMEDIATE and STARTUP RESTRICT might.

Hopefully this will help you to get a more in-depth understanding of how the Oracle database startup command works and the different ways in which you can use it to your advantage.

Like it, share it...

Category: Articles


Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *