The Oracle Fundamentals II Case Study consists of 22 of the most common DBA tasks to do its backup, recovery and network jobs. In your organization, you as a DBA, are expected to know at least how to:: allocate and re-size the memory space on the fly; understand the Oracle background processes; multiplex the Control Files; configure the Database Archive mode; Maintain Redo Log files; Duplex Archived Online Redo Log files; User-Managed COLD or OFFLINE Backups; User-Managed HOT or ONLINE backups; User-Managed Tablespaces and Datafiles backups; User-Managed Tablespace Restoration; Backup and Recovery using the EXPORT and IMPORT utilities; Create the RMAN repository; Use of the RMAN commands; Configure of the RMAN tool; Use the RMAN backup utility; Perform the recovery of a tablespace using the RMAN tool; Performing a Complete Backup using the RMAN tool; Performing incomplete database recoveries; Using the LIST and REPORT commands; Configuring a Listener using the “Net Manager” tool; and Configuring a Service Name using the “Net Manager” tool.

Hands-On Oracle Fundamentals II-CD#01

(User-Managed Backup and Recovery)

Hands-On 01 (Memory Space Allocation on the Fly) - Manuscript

As a DBA, you are responsible to monitor and change a memory space allocation of your organization’s database in case of performance problems. Your job’s responsibilities dictate that you should at least be informed of the following basic fundamental subjects:

Oracle Memory Structures

Using the V$SGA dictionary view

Fixed Size

Variable Size

Oracle Database Files

Database Size

Datafiles

Redo Buffers

Useful parameters and views to resize memory

SHARED_POOL_SIZE

LARGE_POOL_SIZE

JAVA_POOL_SIZE

DB_BLOCK_BUFFERS

BLOCK_SIZE.

DB_CACHE_SIZE

SHARED_POOL_SIZE

LARGE_POOL_SIZE

DB_CACHE_SIZE

SHO PARAMETER

ALTER SYSTEM SET

BUFFER POOL

KEEP

RECYCLE

DEFAULT

DB_KEEP_CACHE_SIZE

DB_RECYCLE_CACHE_SIZE

HOST ERASE

DB_8K_CACHE_SIZE

Views

V$SGA

V$BUFFER_POOL

Commands:

SHO PARAMETER

ALTER SYSTEM SET

CREATE TABLESPACE

DROP TABLESPACE

Hands-On 02 (Oracle Background Processes) - Manuscript

As a DBA, you are responsible to monitor and understand the functions of the Oracle background processes. Your job’s responsibilities dictate that you should at least be informed on the following basic fundamental subjects:

Oracle Background Processes

V$BGPROCESS

PMON

DBWn

ARC0

CKPT

SMON

RECO

Archive log configurations

ARCHIVE LOG LIST

DATABASE LOG MODE

The NOARCHIVELOG mode

The ARCHIVELOG mode

Commands:

ARCHIVE LOG LIST

Hands-On 03 (Multiplexing the Control File) - Manuscript

You, as a DBA, are responsible to multiplex controlfiles to protect your organization from a possible and unexpected 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:

Control Files

Using the V$CONTROLFILE view

Using the V$CONTROLFILE_RECORD_SECTION view

Multiplexing the Control File

Using the INIT.ORA file

Editing the INIT.ORA

Commands:

STARTUP OPEN PFILE=%ORACLE_BASE%\admin\school\pfile\init.ora

HOST MKDIR

ALTER DATABASE BACKUP CONTROLFILE

ALTER DATABASE BACKUP CONTROLFILE TO TRACE

SHUTDOWN IMMEDIATE

Hands-On 04 (Configuring the Database Archive mode) - Manuscript

You, as a DBA, are responsible to recover any failures to a point of failure and also to perform your backup while in an online status. Your shop is 24x7 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 be at least informed of the following basic fundamental subjects:

Configuring The Database Archive Mode

Setting the database to the ARCHIVELOG mode

Querying archive log information

Making a directory.

Enabling the automatic archival process

Setting an archive destination.

Using The Server Parameter File (SPFILE)

Commands:

ARCHIVE LOG LIST

ALTER DATABASE CLOSE

ALTER SYSTEM SET log_archive_start=true SCOPE=spfile

ALTER SYSTEM SET log_archive_dest='c:\archivelogs'

ALTER SYSTEM SET log_archive_format='arc%S.%T' SCOPE=spfile

The %S entry

The %T entry

Shutdown and Startup Database

SHUTDOWN IMMEDIATE

STARTUP

Hands-On 05 (Maintaining Redo Log files) - Manuscript

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

Redo Log Files

Using the V$LOG view

Archiving a current Online Redo log file

Checking a Online Redo log file’s Status

The INACTIVE status

The ACTIVE status

The CURRENT status

Dropping a Log file Group

Resizing a Redo Log file

Adding a Redo Log file

Managing an Online Redo log file with (OMF)

Using the DB_CREATE_ONLINE_LOG_DEST parameter

Using the V$LOGFILE dictionary view

Checking the SEQUENCE# column

Using the V$ARCHIVED_LOG view

Commands:

ALTER SYSTEM SET

ALTER SYSTEM ARCHIVE LOG CURRENT

ALTER DATABASE DROP LOGFILE GROUP

ALTER DATABASE ADD LOGFILE GROUP

Hands-On 06 (Duplexing Archived Online Redo Log files) - Manuscript

You, as a DBA, are responsible to duplex archived Online Redo log files in order to protect the organization from a loss of or a case of corrupted archived files. Your job’s responsibilities dictate that you should be at least informed of the following basic fundamental subjects:

Duplexing Archived Online Redo Log files

Setting an Oracle-Managed File destination

Setting a Redo Log Archive Duplex Destination

Querying all of the archive log destinations

Using the LOG_ARCHIVE_DUPLEX_DEST parameter

Using the V$ARCHIVE_DEST view

Using the V$ARCHIVED_LOG view

Checking the sequence archive number

Writing a procedure to generate some logs

Using the V$LOG_HISTORY view

FIRST_CHANGE#

NEXT_CHANGE#

De-activating the duplex archiving

Commands:

ALTER SYSTEM SET

SHOW PARAMETER

Hands-On 07 (User-Managed COLD or OFFLINE backup) - Manuscript

You, as a DBA, are responsible to backup the database and restore the data in case of a loss of data due to media failure. Based on your organization’s business rules, the database can be shutdown every day for 5 hours. You also know the backup won’t take more than an hour. You want to use the COLD backup process. Your job responsibilities dictate that you should be at least informed of the following basic fundamental subjects:

Performing a User-Managed COLD or OFFLINE backup

Writing a script to perform a complete, COLD, or OFFLINE backups

Using the V$DATABASE view

The ARCHIVE modes

ARCHIVELOG mode

NOARCHIVELOG mode

Using the V$LOGFILE view

Using the V$DATAFILE view

Using the V$CONTROLFILE view

Setting a Control Command

SET ECHO

SET HEADING

SET FEEDBACK

SET PAGESIZE

SET ECHO ON

The password file and the Parameter file (PFILE)

Commands:

SET ECHO

SET HEADING

SET FEEDBACK

SET PAGESIZE

ALTER DATABASE

Hands-On 08 (User-Managed HOT or ONLINE backup) - Manuscript

You, as a DBA, are responsible to backup the database and restore data to the point of failure in case of a loss of data due to media problems. Your organization is a 24x7 day shop and you are not able to shutdown the database. You have to use the HOT or ONLINE backups. Your job responsibilities dictate that you should be at least informed of the following basic fundamental subjects:

Performing a User-Managed Hot backup

Archiving the current Online Redo Log group

Backing-up a CONTROLFILE to a file or TRACE

Performing the HOT backup

Using the DBA_DATA_FILES dictionary view

Setting a tablespace into the backup mode

Commands:

ALTER TABLESPACE BEGIN BACKUP

HOST COPY ALTER TABLESPACE END BACKUP

ALTER SYSTEM

ALTER DATABASE

Hands-On 09 (User-Managed Tablespaces and Datafiles Backup) - Manuscript

You, as a DBA, are responsible to backup the tablespace or datafile and restore the data to the point of failure in case of a loss of data due to a media hard disk crash. Your organization is a 24x7 day shop and you are not able to shutdown the database. You have to use HOT or ONLINE backup. Your job responsibilities dictate that you should be at least informed of the following basic fundamental subjects:

Performing a tablespace and datafiles backup

Performing integrity checks

Using the V$DATAFILE view

Using the DBA_DATA_FILES dictionary view

Understanding the TOTAL PAGES FAILING values

Setting a tablespace while in the OFFLINE mode

The OFFLINE TEMPORARY option

The OFFLINE IMMEDIATE option

Setting a tablespace status

Commands:

ALTER TABLESPACE ONLINE

ALTER TABLESPACE OFFLINE NORMAL

dbv file= BLOCKSIZE=4096

Hands-On 10 (User-Managed Tablespace Recovery) - Manuscript

You, as a DBA, are responsible to recover the database to the point of failure due to a loss of data and media failure. Your job responsibilities dictate that you should be at least informed of the following basic fundamental subjects:

Performing a tablespace recovery

Using the V$DATABASE view

Checking if a user has created any object

Using DBA_TABLESPACE view

Writing a procedure

HOST erase C:\ORACLE\ORA90\SCHOOL\USERS01.DBF

Permanently damaging datafiles

Restoring a datafiles of a USERS tablespace

Setting a tablespace status

Recovering a tablespace

Using the USER_TABLES view

Commands:

ALTER TABLESPACE OFFLINE NORMAL

HOST COPY

ALTER TABLESPACE ONLINE

RECOVER TABLESPACE

DROP TABLE

Hands-On 11 (User-Managed Logical Backup and recovery) - Manuscript

You, as a DBA, are responsible to perform a logical backup using the EXP tool. Notice that if the loss of data since the last time of backup is not significant then a logical backup is a good option to use. Or you may use it to organized the table or relocate data from one database to another. You are also responsible to use the IMP tool to restore data to the same or a different destination. Your job responsibilities dictate that you should be at least informed of the following basic fundamental subjects:

Performing an Oracle Logical Backup and recovery

Using the EXP tool

Using the IMP tool

Dropping a table

Using the V$DATABASE view

Creating a table

Performing a logical backup

HOST EXP system/manager FILE=’xx’ TABLES=(xx)

Restoring a logical table

Commands:

DROP TABLE

HOST EXP

HOST IMP