“The shifts of
Fortune test the reliability of friends.” Cicero (106 BC - 43
BC), De Amicitia |
Read
first then play the video:
DBA-VIDEO -Startup
and Shutdown the database
Startup and Shutdown the
database
Introduction
As a DBA, you are
responsible for starting-up and shutting-down the database of your
organization. Your job’s responsibilities dictate that you should at
least be informed of the following basic fundamental subjects:
Starting up the instance
with the NOMOUNT option
Starting the instance with
the MOUNT option
Starting up the instance
with the FORCE option
Starting the instance with
the OPEN option
Shutting down the database
with the TRANSACTIONAL option
Shutting down the database
with the IMMEDIATE option
Shutting down the database
with the NORMAL option
Shutting down the database
with the ABORT option
Using the IMMEDIATE option
Using the NOMOUNT option
Using the NORMAL option
Using the MOUNT option
Using the TRANSACTIONAL
option
Using the OPEN option
Using the FORCE option
Using the READ ONLY mode
Using the ABORT option
Using the
BACKGROUND_DUMP_DEST parameter
Commands:
SHUTDOWN IMMEDIATE
SHUTDOWN NORMAL
SHUTDOWN TRANSACTIONAL
SHUTDOWN ABORT
STARTUP OPEN READ ONLY
FILE=init.ora
STARTUP NOMOUNT PFILE=init.ora
STARTUP MOUNT PFILE=init.ora
STARTUP OPEN PFILE=init.ora
STARTUP FORCE PFILE=init.ora
ALTER DATABASE MOUNT;
ALTER DATABASE OPEN;
SHUTDOWN IMMEDIATE
Hands-on
In this exercise you will learn how to START and SHUTDOWN the
ORACLE database.
Connect to a database
Connect to SQL*Plus as the system/manager user.
SQL> CONNECT system/manager@school AS SYSDBA
Shutdown and startup a
database
Shutdown the database with the IMMEDIATE option and then start the
instance with the NOMOUNT option.
IMMEDIATE option
The IMMEDIATE option means
not to wait for a user to log off and roll back uncommitted
transactions, then shut down the instance and close the database.
NOMOUNT option
The NOMOUNT option starts the instance without mounting the
database. It means that all of the memory structure and background
processes are in place, but no database is attached to the instance.
SQL> SHUTDOWN IMMEDIATE
SQL> -- to start…
SQL> CONNECT system/manager@school AS
SYSDBA
SQL> STARTUP NOMOUNT \par PFILE=%ORACLE_BASE%.ora
Mount a database
The MOUNT option tells the
instance the database’s name and its components such as its
datafiles location, online redo log files, maximum number of
datafiles, maximum number of instances, etc. Now, mount the database
and then open it.
Don't forget to check your
alert file. The alert log file stores information that is extremely
useful in order to know the health of the database. It records the
starting and stopping of the databases, creation of new redo log
files, datafiles, tablespaces, and most importantly, the Oracle system
error messages. It is located in the BACKGROUND_DUMP_DEST parameter.
SQL> ALTER DATABASE MOUNT;
When the database is
mounted, it does mean that it is open. You should open the database in
order to access to its objects (tables, procedures, functions, views,
etc). A DBA will start a database with the MOUNT option to perform
maintenance on it. You will be able to perform the ALTER DATABASE sql
statement such as ALTER DATABASE NOARCHIVELOG.
SQL> ALTER DATABASE
OPEN;
Now, shutdown the database
with the NORMAL option and then start the instance with the MOUNT
option. The following shows how this works.
NORMAL option
The NORMAL option will wait for users to log out and then, it will
close the database and shutdown the instance.
MOUNT option
The MOUNT option, starts the instance, reads the control file, and
attaches the database, but does not open it.
SQL> SHUTDOWN NORMAL
SQL> -- start
SQL> CONNECT
system/manager@school AS SYSDBA
SQL> STARTUP MOUNT \par PFILE=%ORACLE_BASE%\b0
init.ora
Open a database
Now, open the database. We
must open the database since in the STARTUP command, we used the MOUNT
option.
Notice that the MOUNT option starts the instance, reads the control
file, and attaches the database, but does not open it.
SQL> ALTER DATABASE OPEN;
Shutdown the database with the TRANSACTIONAL option and then start the
instance and open the database. The following shows how this works.
TRANSACTIONAL option
The TRANSACTIONAL option
tells oracle not to wait for a user to log off, but wait for the
client to end the transaction that is in progress, then shut down the
instance and close the database.
OPEN option
The OPEN option starts the
instance, reads the control file, attaches the database, and then
opens it. Notice that the OPEN option is a default option. You do not
need to use the OPEN option, since it is the default option.
SQL> -- Shutdown
SQL> SHUTDOWN TRANSACTIONAL;
SQL> -- start the database…
SQL> CONNECT
system/manager@school AS SYSDBA
SQL> STARTUP OPEN \par PFILE=%ORACLE_BASE%.ora
FORCE option
Use the FORCE option to
shutdown and then startup the database. This should be your last
resort when you cannot shutdown your database. The following shows how
this works.
Make sure that you have already patiently waited for the database to
be shutdown.
SQL> -- Shutdown and Startup
SQL> STARTUP FORCE
\par PFILE=%ORACLE_BASE%.ora
READ ONLY mode
You can also open your database on the READ ONLY mode. In the READ
ONLY mode, you cannot insert, update, or delete any records. Nor are
you allowed to create, alter, or drop any tables. Also, you can't
change the structure of the database by adding tablespaces or
datafiles.
SQL> SHUTDOWN IMMEDIATE
SQL> -- start database…
SQL> CONNECT
system/manager@school AS SYSDBA
SQL> STARTUP OPEN READ ONLY
FILE=%ORACLE_BASE%\b0
init.ora
Now, let's connect to SQL*Plus as the ISELF user.
SQL> CONNECT iself/schooling@school
Query the department table.
SQL> SELECT * FROM dept
/
Insert a record into the department table.
SQL> INSERT INTO dept
VALUES (50, 'EDUCATION','VIRGINIA')
/
Notice that you are not able to insert any record.
ABORT option
Shutdown the database with
the ABORT option. The ABORT option tells Oracle not to wait for a user
and do not roll back for any transaction and shutdown the instance.
The following shows how this works.
SQL> CONNECT system/manager@school AS
sysdba
SQL> -- Shutdown the database
SQL> SHUTDOWN ABORT
“Life is a long
lesson in humility.” James M. Barrie (1860 - 1937) |
Questions:
Q: Describe the NOMOUNT
option in the STATUP command’
Q: How do you start up an
instance with the NOMOUNT option?
Q: Describe the MOUNT
option in the STARTUP command’
Q: How do you start up an
instance with the MOUNT option?
Q: Describe the FORCE
option in the STARTUP command.
Q: How do you start up an
instance using the FORCE option?
Q: Describe the OPEN option
in the STARTUP command.
Q: How do you start up an
instance using the OPEN option?
Q: Describe the
TRANSACTIONAL option in the SHUTDOWN command.
Q: How do you shutdown an
instance using the TRANSACTIONAL option?
Q: Describe the IMMEDIATE
option in the SHUTDOWN command.
Q: How do you shutdown an
instance using the IMMEDIATE option?
Q: Describe the NORMAL
option in the SHUTDOWN command.
Q: How do you shutdown an
instance using the NORMAL option?
Q: Describe the ABORT
option in the SHUTDOWN command.
Q: How do you shutdown an
instance using the ABORT option?
Q: Describe the READ ONLY
mode option.
Q: How do you start up an
instance with the READ ONLY mode option?
Q: Describe the
BACKGROUND_DUMP_DEST parameter.
Q: Describe the PFILE
parameter in the STARTUP command.
|