To start and shutdown the database you need to have special privileges;

• Special OS privilege = Oracle user
• SysDBA
• SYSOPER

To connect at OS level

The following files must be present for your database to work

• spfileSID.ora
• spfile.ora
• initSID.ora

Startup Process

• Unmounted - instance started (background process + memory Structure = instance)
• mounted - Instance is associated with database
• open - Datafiles and redo logs are opened. Database is available to everyone

To Startup the instance

To know the status of your database

To mount the database after startup

To open database after mount

Alternative ways to startup the instance

This is used if you have a custom startup file somewhere that you'll like to use to

startup the database.

means that only certain privileged users can use the database

To see whether we are in restricted mode or not

To get out of the restricted session

Shutdown process

• Close the database
• Unmount the database
• Shutdown the instance

Types of shutdown

• shutdown normal
• shutdown transactional
• shutdown immediate
• shutdown abort: oracle will perform a recovery during startup

To shutdown the database

Note: this is the most used form of shutdown

Difference between spfile and initialization file

spfile means server parameter.

Your init file is a text file and it is read only once while spfile is a binary file and

it is constantly read and can be written to without shutting down the database.

To know where ur spfile is

How to dynamically change a parameter (using the alter statement)

Some of the parameters such as  background_dump_dest, log_file, control_file, audit_file_dest, user_dump_dest etc, are all dynamic parameters.

To see the structure of all dynamic parameters;

For example;

Note: It is not all parameters that you can change. E.g instance_name parameter can't be changed.

To make a copy of your spfile into pfile. 

Doing this is actually a backup task. It is also covered in the backup and recovery tutorial.

To see this file, on UNIX, you will find it in the dbs directory. Note that, by default, you don't normally have a pfile. You make a copy of it just as we did above.

To specify a different file when making a copy of spfile

To create an spfile from a pfile. To do this, make sure your database is shutdown.

Data Dictionary Views

There are about 18000 data dictionary views in oracle database. The most common ones you will be working with are;

• v$System_parameter
• v$System_paramter2
• v$parameter
• v$parameter2
• v$spparameter
• v$parameter_valid_values
• v$obsolete_parameter

The major difference between v$System_parameter and v$System_paramter2 is in how things are displayed. The bulk of these parameters are parameters that interacts with either the System or user session.

For example to alter a system parameter at the session level, we'll say;