DBA Fundamentals

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:

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.

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

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.