Oracle Database Control
When Oracle starts it goes through 3 stages which are
Startup (nomount) | Oracle first reads the parameter file (pfile or spfile) and obtains all memory parameters in order to configure the SGA, memory allocatations take place and the necessary background processes are started. Remember the Memory allocation with the background processes is called the INSTANCE. |
Mount | At this stage Oracle opens and reads the controlfile and obtains the location of the datafiles but it does not open them. |
Open | The last stage is to open the datafiles (also called the DATABASE) for reading and writing, Oracle makes sure that the datafiles are consistent. |
Startup
Nomount Mode | startup nomount; Note: The INSTANCE is born, memory allocated and background processes started but the controlfile has not been read and the datafiles are not open for reading or writing |
Mount Mode | startup mount; Note: The controlfile has been read at this stage but the datafiles are not open for reading or writing. |
Read Only Mode | startup open read only Note: the database files are open for reading only |
Open Mode | startup startup open Note: database files will be open for reading and writing, this is the normal mode of operation. |
Force mode | startup force; Note: this performs a 'shutdown abort' followed by a 'startup' |
Restricted Mode | startup restrict; # To change into restricted mode |
Alter operation mode | # You must be in mount mode to alter the database, if not then shutdown the database first |
Display operation mode | # Display instance state in nomount mode v$database is unavailable. # Obtain the database operation mode, the database must be in mounted or open mode to access v$database # Is restricted session enabled |
Parameter File
As stated in the Oracle structure web page, Oracle can access two types of parameter file pfile (text based) or spfile (binary based), you can specify which parameter you want to start the database with
pfile | startup pfile='c:\oracle\pfiles\initD10.ora' |
spfile | # There is no option to specify a spfile but you can use the pfile option and in the pfile # point to the spfile |
Display the parameter file in use | select name, value from v$parameter where name = 'spfile'; show parameter spfile; |
For more information on Oracle's parameter file see Oracle Structure.
Shutdown
Oracle has a number of options to shutdown the database, there are four to choose from
Normal | No new connections Note: you could be waiting a long time, not normally used |
Transactional | No new connections or transactions Note: again you could be waiting a long time |
Immediate | No new connections Uncommitted transactins will be rolled back then disconnect the client Note: normal shutdown command used |
Abort | No new connections Note: last resort shutdown command, a automatic recovery will take place when you start the database |
Shutdown Commands |
|
Shutdown commands | shutdown normal; Note: 'startup force' will run a 'shutdown abort' immediately followed by a 'startup' |
Display if shutdown is in progress | select shutdown_pending from v$instance; |
Restricted and Quiesce Modes
When you have problems on an Oracle database sometimes you would like the database fully running but allow only privilege users access (normally DBA's), Oracle offers two modes
Restricted | In this mode any users without the restricted privilege will not be allowed to connect to the database, however any users that are still connected will be allowed to use any DML/DDL. Note: make sure all users are disconnected (need to kill their sessions) before changing the mode into restricted. |
Quiesce | In this mode all users transactions are allowed to complete, once completed the user is unable to perform any DML/DDL commands (their sessions appear to freeze) but remain connected to the database. Users will be allowed to login but the session will again appear to freeze. DBA's can perform any DML/DDL as normal, their sessions are unaffected, this also applies to users with the restricted session privilege. |
Useful Commands |
|
Restricted | alter system enable restricted session; |
Grant restricted privilege | grant restricted session to <user>; |
Quiesce | alter system quiesce restricted; alter system unquiesce; select active_state from v$instance; |
Basic Database Information
To obtain general database information the following can be used
Display Parameter file | select name, value from v$parameter where name = 'spfile'; show parameter spfile; |
Parameter settings | select name, value, isdefault, isses_modifiable, issys_modifiable from v$parameter; |
Instance state | select status from v$instance; |
Database operation mode | select open_mode from v$database; |
Shutdown pending | select shutdown_pending from v$instance; |
Restricted Session Mode | select logins from v$instance; |
Quiesce Mode | select active_state from v$instance; |
Oracle version | select * from v$version; select * from product_component_version; |
Log Mode | select log_mode from v$database; |
License info | select * from v$license; |
Database options | select * from v$option; |
Memory Info | show sga select * from v$sga; select * from v$sgainfo; select * from v$ssgastat; |
Display controlfiles | select * from v$controlfile; select * from v$controlfile_record_section; |
Display Oracle processes | select * from v$bgprocess; |
Log file locations | # display background (alert.log destination), core and user destinations show parameter dump_dest; |