Startup, Shutdown & Initialization file

Hello everyone,

In this article you can find some useful commands regarding managing the startup and shutdown process of an oracle database along with managing initialization files.

-- Commands used to startup and shutdown the database
> startup
> startup nomount
> startup mount
> startup open
> startup restrict
> startup pfile = '/u01/myinit.ora';     -- To open DB with specific pfile
> startup open read only;

> shutdown
> shutdown normal
> shutdown immediate
> shutdown transactional
> shutdown abort
---------------------------------------------------------------------------------


-- Case: One of the control files is missing
-- 1. Find the Location of the control files
> show parameter control_files

-- 2. From OS remove one of them
--    On Linux you may just rename it to name.ctl.bkp
--    $ mv name.ctl name.ctl.bkp

-- 3. Restart the database (10g case)
> shutdown immediate
> startup
ORA-00205 error in identifying control file check alert log for more information

-- 4. Checking alert log
--    Go to alert log directory and view the alert log file
--      It will show that one of the control files is missing
--    To know where alert log is
> show parameter background_dump_dest

-- 5. Copy one of the control files & rename it to the missing one
--       use show parameter control_files to know files and locations
--       and compare between files names and actual files located on disk

-- 6. View DB mode and change it to open mode
--       You will find that the database in started mode (no mount mode)
> select status from v$instance;
> alter database mount;
> alter database open;
---------------------------------------------------------------------------------


-- Case: starting db using pfile (or using any other pfile)
-- (You can practice it using the following steps)

-- 1. To know which file is used pfile or spfile
--    This gives the full path to the spfile
--       If empty :. pfile is used
> show parameter spfile

-- 2. moving to that directory will find (11g case)
--    will find no pfile.ora exist
--      normally this will be under $ORACLE_HOME/dbs
$ ls
spfile.ora
init.ora

-- 3. We create a pfile to be used to start db from spfile (to be similar to spfile)
--    Also it will be in the same directory as spfile
> create pfile from spfile;
$ ls
spfile.ora
init.ora
init.ora    <-- br="br" created="created" just="just" new="new" pfile="pfile" the="the">
-- 4. Now we can shutdown the db and start it using the new pfile (init.ora)
--    Don't forget to record the location of the newly created pfile to be used
--       Note no semicolon after the startup command, this works just fine :)
> shutdown immediate
> startup pfile='/full/path/to/pfile/init.ora'

-- 5. Now shutdown the database and startup normally
> shutdown immediate
> startup
---------------------------------------------------------------------------------


-- Case: starting the database in restricted mode [restricted & allowed]
> shutdown immediate
> startup restrict
> conn hr/hr
ORA-01035 oracle only available to users with RESTRICTED SESSION privilege

> conn / as sysdba
> select logins from v$instance;
RESTRICTED

> alter system disable restricted session;
> select logins from v$instance;
ALLOWED

> conn hr/hr
Connected
---------------------------------------------------------------------------------


-- Case: using database in suspend mode [suspended & active] 
-- used to prevent I/O on the datafiles, to be used while backup
-- In this practice we use two sessions session1 for sys user, session2 for hr user

-- session1
> conn / as sysdba
> alter system suspend;
> select database_status from v$instance;
SUSPENDED

-- session2
> conn hr/hr
> select * from user_tables;
The prompt stopped (No output comes)

-- session1
> alter system resume;
> select database_status from v$instance;
ACTIVE

-- session2
The output will be displayed now
---------------------------------------------------------------------------------


-- Case: changing parameter dynamically
-- for this case we use background_dump_dest parameter
-- we want to see if we cn modify this on the system level


-- case1: When issys_modifiable = immediate [i.e can be changed]
-- 1. To know about tha settings of the parameter [v$parameter]
--    Note the settings NAME, VALUE & ISSYS_MODIFIABLE    which can be (immediate or deffered)
> desc v$parameter
> select value, issys_modifiable
  from v$parameter
  where name = 'background_dump_dest';
 
VALUE                                            ISSYS_MODIFIABLE
------------------                                ----------------
/u01/app/oracle/diag/rdbms/db11g/db11g/trace    IMMEDIATE

-- IMMEDIATE means we can use alter system set command to modify this
-- parameter without having to bounce the database
 
-- 2. Modifying parameter
> alter system set background_dump_dest='/u01';

-- 3. Check to see if value changed
> show parameter background_dump_dest

NAME                    TYPE    VALUE
----------                ----    -----
background_dump_dest    String    /u01
---------------------------------------------------------------- #

-- case2: When issys_modifiable = deferred [i.e can be modified but deferred until bouncing DB]
-- 1. To know about tha settings of the parameter [v$parameter]
> desc v$parameter
> select value, issys_modifiable
  from v$parameter
  where name = 'audit_file_dest';
 
VALUE                                ISSYS_MODIFIABLE
------------------                    ----------------
/u01/app/oracle/admin/db11g/adump    DEFERRED

-- DEFERRED means we can use alter system set command to modify this
-- parameter, butwe need to bounce the DB to changes take effect
 
-- 2. Modifying parameter
> alter system set audit_file_dest='/u01' scope=spfile;
-- Must use scope=spfile so the changes taking place in spfile but
-- will not take effect until we bounce the db

-- 3. Check to see if value changed
> show parameter audit_file_dest

NAME                    TYPE    VALUE
----------                ----    -----
background_dump_dest    String    /u01/app/oracle/admin/db11g/adump

-- Note that value didnt change, old value still exist

-- 4. Restart the DB so changes can take effect
> shutdown immediate
> startup
> show parameter audit_file_dest

NAME                    TYPE    VALUE
----------                ----    -----
background_dump_dest    String    /u01
------------------------------------------------- #


-- case3: When issys_modifiable = false [i.e cannot be changed]
-- 1. To know about tha settings of the parameter [v$parameter]
> select value, issys_modifiable
  from v$parameter
  where name = 'instance_name';
 
VALUE        ISSYS_MODIFIABLE
-------        -------
db11g        FALSE

-- FALSE means we cannot use alter system set command to modify this parameter
----------------------------------------------------- #


-- case4: changing a parameter in memory only [i.e current session]
-- 1. To know the current value of a parameter [here we use user_dump_dest]
> show parameter user_dump_dest
 
-- 2. Changing parameter value in memory only [scope = memory]
> alter systems set
  user_dump_dest = '/u01' scope = memory;
 
-- 3. check current value
> show parameter user_dump_dest
Will find changed

-- 4. Return to previous value after restart
> shutdown immediate
> startup

> show parameter user_dump_dest
will find the original value
---------------------------------------------------------------------------------



-- Case: To add comments to the spfile [Adding comments to parameters]
-- 1. To add comments to spfile to specific parameter
> alter system set
  user_dump_dest = '/u01' scope = memory
  comment = 'modified by most_10-12-2012';
 
-- 2. To get comments associated with a parameter [from v$spparameter]
--      update_comment is the comment
> select name, value, update_comment
  from v$spparameter
  where name = 'user_dump_dest';
modified by most_10-12-2012
---------------------------------------------------------------------------------


-- Case: Creating pfile from spfile

-- or Taking spfile & dump it to text file (pfile) [spfile > text file]

-- To create text file (pfile) from spfile
-- This file will be created in $HOME/databae in Win & $HOME/dba in Unix
-- will take the name init.ora
> create pfile from spfile;

-- To create it in a different location or with a different name
> create pfile = '/u01/myinit.ora' from spfile;
----------------------------------------------------------------------------------



-- Case: Creating spfile from pfile [ text file (pfile) > spfile] 
-- Note: The database must be down. << <<

-- To create spfile from pfile
> shutdown immediate
> create spfile = '/u01/myspfile.ora' from pfile = '/u01/myinit.ora';
---------------------------------------------------------------------------------


-- Case: using the v$_2 parameter-- gives a nicer look for the parameter

-- For the Instance Settings we use
-- v$system_parameter, v$system_parameter2
> select name, value
  from v$system_parameter
  where name='control_files';

> select name, value
  from v$system_parameter2
  where name='control_files';
 
Output looks nicer and easyer to read
----------------------------------------------------------------------------------



-- Case: changing parameter at session level

--       session parameter overwrite system parameter

-- Note: in scope = memory, will be in memory for all current sessions
-- but here is for current session only.

-- 1. To change a parameter in session level
> show parameter statistics_levle;     -- typical
> alter session set statistics_level = basic;
> show parameter statistics_levle;     -- basic

-- 2. To check its value at system level
> select value from v$system_parameter
  where nem = 'statistics_level'; -- typical

-- 3. To check its value at session level using v$parameter
> select value from v$parameter
  where nem = 'statistics_level'; -- basic

/*
Note:
To check value of parameter at session levle use show parameter or v$parameter view
To check value of parameter at system levle use v$system_parameter view
*/  
----------------------------------------------------------------------------------


-- Case: checking valid values for a parameter [v$parameter_valid_values]
> select name, value, isdefault
  from v$parameter_valid_values
  where name = 'statistics_level';
 
NAME        VALUE    ISDEFAULT
--------    ------    ----------
stats_level    BAISC    false
stats_level    TYPICAL    true
stats_level    ALL        false
----------------------------------------------------------------------------------


-- Case: checking obsolete parameters [v$obsolete_parameters]
-- All of them must not be set [isspecified MUST= false]

> select * from v$obsolete_parameters;
 
NAME        ISSPECIFIED
--------    ----------
PARAMETER1    FALSE
PARAMETER2    FALSE
PARAMETER3    FALSE
----------------------------------------------------------------------------------

Regards
Mostafa