Hands-On 02 (Startup and Shutdown the database)

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

TARTUP 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 02 (Startup and Shutdown of the database)
-- Preparation
set echo on
connect system/manager@school as sysdba
startup
SET linesize 1000 pagesize 55
COL name FORMAT a50
col parameter format a40
col username format a10

pause

--Start

CLEAR SCR
-- In this exercise you will learn how to START and SHUTDOWN
-- the ORACLE database.

-- Connect to SQL*Plus as the system/manager user.

pause

CONNECT system/manager@school AS SYSDBA

pause

CLEAR SCR
-- Shutdown the database with the IMMEDIATE option and then 
-- start the instance with the NOMOUNT 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.

-- 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.
pause
-- >>>>>>>>>>>>>>>>>>>>>> Shutdown
SHUTDOWN IMMEDIATE
CONNECT system/manager@school AS SYSDBA

-- >>>>>>>>>>>>>>>>>>>>>> Startup
STARTUP NOMOUNT PFILE=%ORACLE_BASE%\admin\school\pfile\init.ora
pause

CLEAR SCR
-- 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.

pause


ALTER DATABASE MOUNT;

ALTER DATABASE OPEN;

pause

CLEAR SCR
-- Shutdown the database with the NORMAL option and then
-- start the instance with the MOUNT option.

-- The NORMAL option will wait for users to log out and then, it
-- will close the database and shutdown the instance.

-- The MOUNT option, starts the instance, reads the control file, 
-- and attaches the database, but does not open it.
pause
-- >>>>>>>>>>>>>>>>>>>>>> Shutdown
SHUTDOWN NORMAL
CONNECT system/manager@school AS SYSDBA

-- >>>>>>>>>>>>>>>>>>>>>> Startup
STARTUP MOUNT PFILE=%ORACLE_BASE%\admin\school\pfile\init.ora
pause

CLEAR SCR
-- 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.

pause


ALTER DATABASE OPEN;

pause

CLEAR SCR
-- Shutdown the database with the TRANSACTIONAL option and then
-- start the instance and open the database.

-- 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.

-- 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.
pause
-- >>>>>>>>>>>>>>>>>>>>>> Shutdown
SHUTDOWN TRANSACTIONAL
CONNECT system/manager@school AS SYSDBA

-- >>>>>>>>>>>>>>>>>>>>>> Startup
STARTUP OPEN PFILE=%ORACLE_BASE%\admin\school\pfile\init.ora
pause

CLEAR SCR
-- Use the FORCE option to shutdown and then startup the database. 
-- This should be your last resort when you cannot shutdown 
-- your database.

-- Make sure that you have already patiently waited for the
-- database to be shutdown.

pause

-- >>>>>>>>>>>>>>>>>>>>>> Shutdown and Startup
STARTUP FORCE PFILE=%ORACLE_BASE%\admin\school\pfile\init.ora

pause

CLEAR SCR
-- 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.

pause

SHUTDOWN IMMEDIATE
CONNECT system/manager@school AS SYSDBA

-- >>>>>>>>>>>>>>>>>>>>>> Startup
STARTUP OPEN READ ONLY PFILE=%ORACLE_BASE%\admin\school\pfile\init.ora

pause

CLEAR SCR
-- Now, let's connect to SQL*Plus as the ISELF user.

pause


CONNECT iself/schooling@school

pause

CLEAR SCR
-- Query the department table

pause


SELECT * FROM dept
/

pause

CLEAR SCR
-- Insert a record into the department table.

pause


INSERT INTO dept VALUES (50, 'EDUCATION','VIRGINIA')
/

-- Notice that you are not able to insert any record.

pause

CLEAR SCR
-- 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.

pause

CONNECT system/manager@school AS sysdba

-- >>>>>>>>>>>>>>>>>>>>>> Shutdown
SHUTDOWN ABORT

pause



CLEAR SCR
-- Now, you should practice this Hands-On exercise.

-- For more information about the subject, you are encouraged
-- to read from a wide selection of available books.

-- Good luck.
--
pause
pause