everythingOracle.bizhat.com

 

'Everything you wanted
to know about Oracle'

Training References Syntax Tell A Friend Contact Us

 

 

DBA Fundamentals

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27
28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57
<< Previous

Chapter # 03

Next >>


 

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@yourhost 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 ORACLE user.

SQL> CONNECT oracle/learning@yourhost

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
 

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.

 

     Reviews and Templates for FrontPage
     

Copyright � everythingOracle.bizhat.com 2006 All Rights Reserved.