iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Training

    Home      .Services     Login       Start Learning     Certification      .                 .Share your BELIEF(s)...

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       Email2aFriend    |

 

Online Oracle Training for beginners and advanced - The most comprehensive Oracle tutorial

The authors do not guarantee or take any responsibility for the accuracy, or completeness of the information.

DBAs - Fundamentals

 

Lesson 01 | Lesson 02 | Lesson 03 | Lesson 04 | Lesson 05 | Lesson 06 | Lesson 07 | Lesson 08 | Lesson 09 | Lesson 10 | Lesson 11 | Lesson 12 | Lesson 13 | Lesson 14 | Lesson 15 | Lesson 16 | Lesson 17 | Lesson 18 | Lesson 19 | Lesson 20 | Lesson 21 | Lesson 22 | Lesson 23 | Lesson 24 | Lesson 25 | Lesson 26 |

 

Lesson 03

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