Oracle Fundamentals I Case Study

Hands-On Oracle Fundamentals I–CD#01

(Database Administrator-DBA)

Hands-On 01 (The most important Dictionary Views)

As a DBA, you are responsible for obtaining the most important dictionary views and report them to the senior DBA of your organization. You need to know how to obtain these requirements by using simple SQL statements. Your job’s responsibilities dictate that you should at least be informed of the following basic fundamental dictionary views:

Creating a data dictionary

Using the CATALOG.SQL script

Using the ALL_, DBA_, and USER_ dictionary views

Using the DBA_VIEWS dictionary view

Using the DBA_TABLES dictionary view

Using the V$PWFILE_USERS view

Connecting as SYSDBA or SYSOPER

Using the V$PARAMETER

Using the V$SYSTEM_PARAMETER view

Using the SHOW PARAMETER command

Using the V$SGA view

Using the V$OPTION view

Using the V$PROCESS view

Using the V$SESSION view

Using the V$VERSION view

Using the V$INSTANCE view

Using the V$THREAD view

Using the V$PARAMETER view

Using the V$CONTROLFILE view

Using the V$DATABASE view

CONTROLFILE TYPE

CONTROLFILE CREATED

CONTROLFILE SEQUENCE NUMBER

CONTROLFILE CHANGE NUMBER

Using the V$DATAFILE view

Using the V$LOGFILE view

Command:

            START %ORACLE_HOME%\rdbms\admin\catalog.sql

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 03 (Multiplexing controlfiles)

As a DBA, you are responsible for multiplexing controlfiles in order to protect your organization in case of a possible loss of controlfiles due to media failure. Your job’s responsibilities dictate that you should at least be informed of the following basic fundamental subjects:

Multiplexing control files

Using the V$CONTROLFILE view

Editing the INIT.ORA file

Using the V$CONTROLFILE_RECORD_SECTION view

Commands:

ALTER DATABASE BACKUP CONTROLFILE

ALTER DATABASE BACKUP CONTROLFILE TO TRACE

HOST COPY

HOST MKDIR

Hands-On 04 (Reading the ALERT file)

As a DBA, you are responsible for reading the ALERT file once in a while to be informed of any unknown problems with the database such as, not enough space in the rollback segment or the maximum extent reached in a table. Your job’s responsibilities dictate that you should at least be informed of the following basic fundamental subjects:

Reading the ALERT file

            Information:

The location of the ALERT file

The date and time the database started

Oracle version

Operating System information

Number of processes

Shared pool size

Location of control files

Block size

Processes:

Process ID

DB writer process (DBWn)

LOG writer process(LGWR), etc.

Redo Log file archive information

Frequency of the checkpoint process

ERROR location

Looking at the backup controlfile with the TRACE option

Hands-On 05 (Changing the database mode)

As a DBA, you are responsible for changing the database mode for database maintenance purposes. Your job’s responsibilities dictate that you should at least be informed of the following basic fundamental subjects:

Database modes:

SUSPEND

RESUME

RESTRICTED SESSION

QUIESCE RESTRICTED

Using the RESOURCE_MANAGER_PLAN parameter

SHOW PARAMETER resource_manager_plan

Commands:

CREATE SPFILE='c:\spfileschool.ora' FROM PFILE

ALTER SYSTEM SET

SHOW PARAMETER

Hands-On 06 (Server Parameter File-SPFILE)

As a DBA, you are responsible for changing memory size allocations while the database is on. You need to use the Server Parameter File to do so. Your job’s responsibilities dictate that you should at least be informed of the following basic fundamental subjects:

Creating the Server Parameter File (SPFILE)

Using the MEMORY option

Using the SPFILE

Using the BOTH

Using the Server Parameter File (SPFILE)

Setting the RESOURCE_MANAGER_PLAN parameter

Creating the Parameter File (PFILE)

Using the Parameter File (PFILE)

Commands:

ALTER SYSTEM SUSPEND

ALTER SYSTEM RESUME

ALTER SYSTEM ENABLE RESTRICTED SESSION

ALTER SYSTEM QUIESCE RESTRICTED

ALTER SYSTEM command

Hands-On 07 (Configure the database to the archive mode)

As a DBA, you are responsible to recover any failures to a point of failure and also to perform your backup while online. Your organization is a 24x7 shop and you are not able to shutdown the database. Therefore, the database has to be in an archive mode. Your job’s responsibilities dictate that you should at least be informed of the following basic fundamental subjects:

Archiving the database

Maintaining the Online Redo Log files

Maintaining the checkpoints

Using the NOARCHIVELOG mode

Using the ARCHIVELOG mode

Using the DBA_USERS view

Using the V$LOG dictionary view

The STATUS column

Using the LOG_CHECKPOINT_INTERVAL parameter

Using the LOG_CHECKPOINT_TIMEOUT parameter

Using the LOG_CHECKPOINTS_TO_ALERT parameter

Commands:

ARCHIVE LOG LIST

SHUTDOWN IMMEDIATE

STARTUP MOUNT PFILE=init.ora

ALTER DATABASE ARCHIVELOG

ALTER DATABASE OPEN

ALTER SYSTEM SWITCH LOGFILE

ALTER SYSTEM CHECKPOINT

SHOW PARAMETER checkpoint

ALTER SYSTEM SET log_checkpoint_timeout = 7200

Hands-On 08 (Maintaining and Relocating the Redo Log files)

As a DBA, you are responsible for maintaining and relocating the Redo Log file in order to distribute data among multiple hard disks to increase I/O performance. Your job’s responsibilities dictate that you should at least be informed of the following basic fundamental subjects:

Maintaining and relocating the Redo Log files

Using the V$LOG directory view

Using the V$LOGFILE directory view

Adding an Online Redo Log Group

Relocating or Renaming the Online Redo Log file

Copying the Online Redo Log file

Dropping an Online Redo Log Group

Deleting the Online Redo Log file's physical file

Commands:

ALTER DATABASE ADD

SHUTDOWN IMMEDIATE

HOST COPY

HOST ERASE

STARTUP MOUNT

ALTER DATABASE RENAME FILE

ALTER DATABASE OPEN

ALTER DATABASE DROP LOGFILE

Hands-On 09 (Multiplexing and Maintaining the Online Redo Log files)

As a DBA, you are responsible for maintaining multiple Online Redo log files to protect your organization of a possible loss of data due to media failure. Your job’s responsibilities dictate that you should at least be informed of the following basic fundamental subjects:

Maintaining the Online Redo Log files

Multiplexing the Online Redo Log files

Using Oracle-Managed Files

Using User-Managed Files

Using the DB_CREATE_ONLINE_LOG_DEST parameter

Creating an Online Redo Log group

Creating an Online Redo Log member

Clearing an Online Redo Log group

Dropping an Online Redo Log group

Using the V$LOGFILE view

Commands:

SHOW PARAMETER

HOST MKDIR

ALTER SYSTEM SET

ALTER DATABASE ADD LOGFILE GROUP

ALTER DATABASE ADD LOGFILE MEMBER

ALTER DATABASE CLEAR LOGFILE GROUP

Hands-On 10 (Maintaining Tablespace and Datafiles)

As a DBA, you are responsible for maintaining tablespaces and datafiles due to the growth of a user’s database.  Your job’s responsibilities dictate that you should at least be informed of the following basic fundamental subjects:

Maintaining Tablespaces using Oracle-Managed Files (OMF)

Maintaining Tablespaces without using OMF

Maintaining Datafiles using OMF

Maintaining Datafiles without using OMF

Using the AUTOEXTEND ON option

Using a default storage option

Using the INITIAL parameter

Using the NEXT parameter

Using the MINEXTENTS parameter

Using the MAXEXTENTS parameter

Using the PERMANENT ONLINE option

Using the DBA_TABLESPACES view

The EXTENT_MANAGEMENT column

Managing a tablespace LOCALLY

Adding a datafile to a tablespace

Using the DBA_DATA_FILES view

Using the DBA_TABLESPACES view

The STATUS column

Using the DB_CREATE_FILE_DEST parameter

Commands:

ALTER TABLESPACE ADD

ALTER TABLESPACE OFFLINE

ALTER TABLESPACE ONLINE

DROP TABLESPACE INCLUDING …

HOST ERASE

ALTER SYSTEM SET

CREATE TABLESPACE

Hands-On 11 (Maintaining a TEMPORARY tablespace)

As a DBA, you are responsible for maintaining a TEMPORARY tablespace due to a user’s big transaction sorting.  Your job’s responsibilities dictate that you should at least be informed of the following basic fundamental subjects:

Maintaining a TEMPORARY tablespace with OMF

Maintaining a TEMPORARY tablespace without OMF

Using the DB_CREATE_FILE_DEST parameter

Creating a TEMPORARY tablespace with OMF

Using the DBA_TABLESPACES view

TABLESPACE_NAME

INITIAL_EXTENT

MAX_EXTENTS

CONTENTS

LOGGING

STATUS

Using the DBA_DATA_FILES directory view

Using the DBA_TEMP_FILES directory view

Using the V$SESSION dictionary view

Using the V$SORT_SEGMENT view

Dropping a TEMPORARY tablespace

Creating a TEMPORARY tablespace using original syntax

Advantages of using OMF

Creating a TEMPORARY tablespace using the TEMPFILE clause

Using Oracle-Managed Files

Using the UNIFORM option

Using the DBA_TEMP_FILES view

Commands:

ALTER SYSTEM SET

CREATE TEMPORARY TABLESPACE

DROP TABLESPACE

CREATE TABLESPACE TEMPORARY