Startup, Shutdown & the initialization File

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

  • Special OS privilege = Oracle user
  • SysDBA
  • SYSOPER

To connect at OS level

SQL> connect / as sysdba

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

SQL> startup

To know the status of your database

SQL> select status from v$instance

To mount the database after startup

SQL> alter database mount

To open database after mount

SQL> alter database open

Alternative ways to startup the instance

SQL> startup pfile='c:\oracle\product\10.2.0\db_1\dbs\initdw.ora'

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

startup the database.

SQL> startup restrict;

means that only certain privileged users can use the database

To see whether we are in restricted mode or not

SQL> select logins from v$instance;

To get out of the restricted session

SQL> alter system disable 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

SQL> shutdown immediate;

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

SQL> show parameter spfile;

 

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;

SQL> desc v$parameter;

For example;

SQL> select value, issys_modifiable
from v$parameter
where name='background_dump_dest';
 
SQL> alter system set background_dump_dest='c:\oracle'

 

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.

SQL> create pfile from spfile;

 

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

SQL> create pfile='c:\oracle\myinit.ora'; from spfile

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

SQL> create spfile='c:\oracle\myspfile.ora' from pfile='c:\oracle\myinit.ora'
 

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;

SQL> alter session set statistics_level=basic;

Post new comment

The content of this field is kept private and will not be shown publicly.