Managing Database Instances (part 1)
Starting Up an Oracle Instance
Two special connection account authorizations are available for startup and shutdown:
- SYSDBA authorization is an all-empowering authorization that allows you to perform any database task.
- SYSOPER authorization is a less powerful authorization that allows startup and shutdown abilities but restricts other administrative tasks, such as access to nonadministrative schema objects.
- These authorizations are managed either through a passwords file or via operating-system control.
- When a database is initially installed, only the SYS schema can connect to the database with the SYSDBA authorization.
- You can grant this authorization and the SYSOPER authorization to give others the ability to perform these tasks without connecting as the SYS user.
A-Starting Up Instance :
- STARTUP NOMOUNT :This starts the instance without mounting the database.
When a database is started in this mode,
- the parameter file is read,
- the background processes and memory structures are initiated(started, allocated.)
- they are not attached nor do they communicate with the disk structures of the database.
When the instance is in this state, the database is not available for use.
Most likely cause :
- For example, this can occur if Oracle has a problem accessing the control file structures, which contain important information needed to continue with the startup process.
- If these structures are damaged or not available, the database startup process cannot continue until the problem is resolved.
- If STARTUP NOMOUNT fails, the most likely cause is that the parameter file cannot be read or is not in the default location. Other causes include OS resource limits that prevent memory or process allocation.
2. STARTUP MOUNT : This performs all the work of the STARTUP NOMOUNT option and reads the control file.
- At this point, Oracle obtains information from the control files that it uses to locate and attach to the physical database structures. The control file contains the name of the database, all the data filenames, and the redo log files associated with the database.
- Certain administrative tasks can be performed while the database is in this mode, including renaming data files, enabling or disabling archive logging, renaming and adding redo log files, and recovering the database.
3. STARTUP OPEN : This is the default startup mode if no mode is specified on the STARTUP command line.
- STARTUP OPEN performs all the steps of the STARTUP NOMOUNT and STARTUP MOUNT options.
- This option makes the database available to all users.
When opening the database, you can use a couple of options:
- STARTUP OPEN READ ONLY : opens the database in read-only mode.
- STARTUP OPEN RECOVER : opens the database and performs a database recovery.
a few other startup options are available that you can use in certain situations:
- STARTUP FORCE :
You can use the STARTUP FORCE startup option if you are experiencing difficulty starting the database in a normal fashion.
For example, when a database server loses power and the database stops abruptly, the database can be left in a state in which a STARTUP FORCE startup is necessary.This type of startup should not normally be required but can be used if a normal startup does not work. What is also different about STARTUP FORCE is that it can be issued no matter what mode the database is in. STARTUP FORCE does a shutdown abort and then restarts the database; therefore, it can be used to cycle a database that is in open state.
2. STARTUP RESTRICT :
The STARTUP RESTRICT option starts up the database and places it in OPEN mode but gives access only to users who have the RESTRICTED SESSION privilege.
You might want to open a database using the RESTRICTED option :
- when you want to perform maintenance on the database while it is open but want to ensure that users cannot connect and perform work on the database.
- You might also want to open the database using the RESTRICTED option to perform database exports or imports and guarantee that no users access the system during these activities.
After you are done with your work, you can disable the restricted session,
- ALTER SYSTEM DISABLE RESTRICTED SESSION, so everyone can connect to the database.
3. STARTUP UPGRADE / DOWNGRADE :
The STARTUP UPGRADE option starts up the database in UPGRADE mode and sets system initialization parameters to specific values required to enable database upgrade scripts to be run. Similarly, the STARTUP DOWNGRADE option sets system initialization parameters to specific values required to enable database downgrade scripts to be run.
UPGRADE should be used only when a database is first started with a new version of the Oracle Database Server.
Starting Oracle Using SQL*Plus :
You can use the command-line facility SQL*Plus to start the Oracle database.
You will need to connect to SQL*Plus as a user with SYSOPER or SYSDBA privileges.
Here is the syntax of the startup options available:
STARTUP [NOMOUNT|MOUNT|OPEN] [PFILE=] [RESTRICT] [FORCE] [QUIET]
SQL*Plus. The QUIET option suppresses the SGA information during startup.
Example :
- STARTUP NOMOUNT pfile=/u01/oracle/init.ora : Starts Oracle in NOMOUNT mode using a nondefault parameter file.
- STARTUP MOUNT : Starts Oracle in MOUNT mode using a default spfile or pfile.
- STARTUP OPEN : Starts Oracle in OPEN mode using a default spfile or pfile.
- STARTUP RESTRICT :Starts Oracle in OPEN mode and allows only users with restricted session privileges to connect to the database.
- STARTUP FORCE : Forces database startup using the default pfile or spfile. The running instance is shut down using the ABORT method.
- STARTUP OPEN PFILE=/u01/sp01.ora : Starts Oracle in OPEN mode using a nondefault parameter file.
- startup force pfile='/home/oracle/mypfile1.ora' :command with a nondefault parameter file to start an Oracle database using SQL*Plus
Changing Database Startup States Using SQL :
When the database is in the NOMOUNT or MOUNT state, you can go to the next state by using the ALTER DATABASE statement instead of shutting down the database and starting with the
appropriate state option. The following SQL statements show how to perform database availability state changes.
- To mount a database in NOMOUNT state, use ALTER DATABASE MOUNT;.
- To open a database from NOMOUNT or MOUNT state, use ALTER DATABASE OPEN;.
- To open a database in read-only mode, use ALTER DATABASE OPEN READ ONLY;.
- To enable restricted mode, use ALTER SYSTEM ENABLE RESTRICTED SESSION;.
- To disable restricted mode, use ALTER SYSTEM DISABLE RESTRICTED SESSION;.
If the database is already open, you cannot return to the MOUNT or NOMOUNT state. You have to shut down the database and start with the appropriate state.
