To start and shutdown the database you need to have special privileges;
To connect at OS level
SQL> connect / as sysdba
The following files must be present for your database to work
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;
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;
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;
SQL> select value, issys_modifiable
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'
There are about 18000 data dictionary views in oracle database. The most common ones you will be working with are;
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;