Oracle Database Administration/Collection

Oracle Database Administration

Learning Guide

This learning guide supports the Wikiversity course Oracle Database Administration, available at http://en.wikiversity.org/wiki/Oracle_Database_Administration.

Overview

Oracle Database Administration introduces Oracle database administration concepts, including database architecture, environment, management, networking, storage, security, maintenance, performance, and backup and recovery.

This course comprises 14 lessons covering Oracle database administration. Each lesson includes a combination of Wikipedia and Oracle readings, YouTube videos, and hands-on learning activities. The course also assists learners in preparing for Oracle Database Administration I certification.

Preparation

This is a third-semester, college-level course. Learners should already be familiar with Oracle SQL Fundamentals.

Lessons

1. Environment
2. Instances
3. Network
4. Architecture
5. Storage
6. Undo Data
7. Data Concurrency
8. User Security
9. Database Security
10. Maintenance
11. Performance
12. Moving Data
13. Backup and Recovery
14. Support

See Also

• Oracle SQL Fundamentals
• Oracle: Database 2-Day DBA (HTML) (PDF)
• Oracle: Database 2-Day + Security Guide (HTML) (PDF)
• Oracle: Database 2-Day + Performance Tuning Guide (HTML) (PDF)

References

• Oracle: Database 11g: Administration I Exam Topics
• Watson. OCA Oracle Database 11g Administration I Exam Guide: Exam 1Z0-052. Oracle Press. ISBN 9780071591027

Lesson 1 - Environment

This lesson introduces the Oracle database environment.

Objectives and Skills

Objectives and skills for the database environment portion of Oracle Database Administration I certification include:

• Preparing the Database Environment
  • Identify the tools for Administering an Oracle Database
  • Plan an Oracle Database installation
  • Install the Oracle software by using Oracle Universal Installer (OUI)
• Creating an Oracle Database
  • Create a database by using the Database Configuration Assistant (DBCA)

Readings

1. Wikipedia: Oracle Database
2. Wikipedia: SQL*Plus
3. Wikipedia: Oracle SQL Developer
4. Wikipedia: Oracle Enterprise Manager
5. Oracle: Database 2-Day DBA Introduction
6. Oracle: Database 2-Day DBA Installing Oracle Database and Creating a Database

Multimedia

1. YouTube: Downloading Oracle Software
2. YouTube: Install Oracle on Windows
3. YouTube: Install Oracle Client Software
4. YouTube: Creating an Oracle Database
5. YouTube: Using SQL Plus
6. YouTube: Download & Install SQL Developer
7. YouTube: Using SQL Developer
8. YouTube: Deinstalling & Reinstalling Oracle
9. YouTube: Rerunning netca ntemgr dbca

Activities

1. Download Oracle Database 11g Release 2 Enterprise Edition.
   1. Download both disks for Oracle Database 11g Release 2 Enterprise Edition from Oracle: Database Software Downloads.
   2. Extract the files from both disks into a single combined folder structure for installation.
2. Install Oracle Database.
   1. Complete the tutorial Oracle: Installing the Software Using the Oracle Universal Installer (OUI).
3. Create a database.
   1. Complete the tutorial Oracle: Using the Database Configuration Assistant (DBCA) to Create a Database.
4. Use SQL*PLUS to connect to the database.
   1. At a command prompt, enter the following command to connect to the database:
      sqlplus / as sysdba
   2. In SQL*PLUS, enter the following query to verify the database name:
      SELECT NAME FROM V$DATABASE;
   3. Enter the following command to exit SQL*PLUS:
      exit
5. Use Oracle SQL Developer to connect to the database.
   1. Review Oracle: Installing Oracle SQL Developer.
   2. Download and install Java SE Development Kit.
   3. Download and install Oracle SQL Developer.
   4. Run SQL Developer.
   5. Add a connection to your server. Include username, password, hostname or ip address, and port number. Test and save the connection.
   6. Double-click on the connection to connect.
   7. In the connection worksheet, enter the following:
      SELECT NAME FROM V$DATABASE;
   8. Run the query.
   9. Close SQL Developer.
6. Configure database options.
   1. Review Oracle: Creating and Managing a Database with DBCA.
   2. Use DBCA to change the database to Shared Server Mode.
7. Delete a database.
   1. Review Oracle: Creating and Managing a Database with DBCA.
   2. Use DBCA to delete the database created above.
8. Create a database and examine creation scripts.
   1. Review Oracle: Creating and Managing a Database with DBCA.
   2. Use DBCA to create a database named orcl. Use the default settings, but under Creation Options, choose Generate Database Creation Scripts. Note the location of the scripts. While the database is being created, examine the scripts Oracle is using to create the database.

Lesson Summary

• An Oracle database is a collection of physical files on one or more disks.
• The database contains user data, metadata, and control structures.
• The Oracle instance is composed of background processes, shared memory areas, server processes, Oracle NET, and the Oracle Net listener.
• Oracle Universal Installer (OUI) is a utility that installs your Oracle software and options. It can automatically start Oracle Database Configuration Assistant to install a database.
• Oracle Database Configuration Assistant (DBCA) is a utility that creates a database from templates that are supplied by Oracle, or you can create your own.
• Database Upgrade Assistant is a tool that guides you through the upgrade of your existing database to a new Oracle Database release.
• Net Configuration Assistant is a utility that enables you to configure listeners and naming methods, which are critical components of the Oracle Database network.
• Oracle Enterprise Manager Database Control is a web-based interface and the primary product for managing your database.
• SQL*Plus is an Oracle Database utility, with a basic command-line interface, commonly used by users, administrators, and programmers.
• Oracle SQL Developer (SQLDeveloper) is an integrated development environment (IDE) for working with SQL in Oracle databases using the Java Development Kit.
• Oracle Net Manager is a graphical user interface tool primarily used to configure Oracle Net Services for an Oracle home on a local client or server host.
• Oracle Net Configuration Assistant is a wizard-based tool with a graphical user interface used to configure basic Oracle Net network components and to configure Oracle home for directory server usage.
• Oracle Data Pump technology enables very high-speed movement of data and metadata from one database to another.
• SQL*Loader loads data from external files into tables of an Oracle database.
• Oracle Recovery Manager (RMAN), a command-line and Enterprise Manager-based tool, is the preferred method for efficiently backing up and recovering an Oracle database.
• Oracle Optimal Flexible Architecture (OFA) helps organize database software and configure databases to allow multiple databases, of different versions, owned by different users to coexist on the same server.
• ORACLE_BASE is the root of the Oracle Database directory tree.
• ORACLE_HOME corresponds to the environment in which Oracle Database products run for a given instance.

See Also

• Oracle: Universal Installer Concepts Guide
• Oracle: SQL*Plus User's Guide and Reference
• Oracle: SQL Developer User's Guide

References

Oracle Database Administration

Lesson 2 - Instances

This lesson introduces Oracle database instance management.

Objectives and Skills

Objectives and skills for the instance management portion of Oracle Database Administration I certification include:

• Managing the Oracle Instance
  • Setting database initialization parameters
  • Describe the stages of database startup and shutdown
  • Using alert log and trace files
  • Using data dictionary and dynamic performance views

Readings

1. Oracle: Database 2-Day DBA Getting Started with Database Administration

Multimedia

1. YouTube: Oracle Enterprise Manager OEM

Activities

1. Use Oracle Enterprise Manager.
   1. Complete the tutorial Oracle: Getting Started with Oracle Enterprise Manager.
2. Stop and start Enterprise Manager.
   1. Review Oracle: Administering a Database on Windows.
   2. Use the following commands to view status, stop, and start the Enterprise Manager console service:
      emctl status dbconsole
emctl stop dbconsole
emctl start dbconsole
   3. Use the Windows Services management console to view status, stop, and start the OracleDBConsoleorcl console service.
   4. Use the following commands to view status, stop, and start the NET command to stop and start the OracleDBConsoleorcl console service:
      net start
net stop OracleDBConsoleorcl
net start OracleDBConsoleorcl
3. Test connecting to Enterprise Manager.
   1. Use emctl, Services, or net to stop Enterprise Manager.
   2. While the service is stopped, attempt to connect to Enterprise Manager using a web browser. The connection will fail.
   3. While the service is stopped, attempt to connect to the Oracle database using SQL*PLUS. The connection should succeed.
   4. Use emctl, Services, or net to start Enterprise Manager.
   5. Use a web browser to connect to Enterprise Manager to verify the service started.
4. User Enterprise Manager Database Control to shutdown and startup the database.
   1. Review Oracle: Administering a Database on Windows.
   2. Use Enterprise Manager Database Control to shutdown the database. Under Advanced Options, choose Transactional.
   3. While the database is shutdown, attempt to connect to the database with SQL*PLUS.
   4. Use Enterprise Manager to startup the database. Under Advanced Options, view the options available but keep the default settings.
5. Use SQL*PLUS to shutdown and startup the database.
   1. Review Oracle: Administering a Database on Windows.
   2. Use the following command to shutdown the database:
      SHUTDOWN TRANSACTIONAL
   3. Use the following command to startup the database:
      STARTUP
6. Use SQL*PLUS to shutdown and startup the database in stages:
   1. Review Oracle: Starting Up a Database.
   2. Use the following command to close and dismount the database:
      SHUTDOWN IMMEDIATE
   3. Use the following command to verify instance status:
      SELECT * FROM V$INSTANCE;
   4. Use the following command to startup the database:
      STARTUP NOMOUNT
   5. Use the following command to verify instance status:
      SELECT * FROM V$INSTANCE;
   6. Use the following command to mount the database:
      ALTER DATABASE MOUNT;
   7. Use the following command to verify instance status:
      SELECT * FROM V$INSTANCE;
   8. Use the following command to open the database:
      ALTER DATABASE OPEN;
   9. Use the following command to verify instance status:
      SELECT * FROM V$INSTANCE;
7. View the alert log.
   1. Review Oracle: Viewing the Alert Log.
   2. Use Enterprise Manager to view Alert History and Alert Log Contents.
   3. Use SQL*PLUS to locate the alert log with the following query:
      SELECT * FROM V$DIAG_INFO;
   4. Use a text editor to open and view the alert log.
8. Use data dictionary and dynamic performance views.
   1. Review Oracle: Data Dictionary and Dynamic Performance Views.
   2. Use the following query to identify available DBA_ views:
      SELECT TABLE_NAME FROM DICTIONARY WHERE TABLE_NAME LIKE 'DBA_%';
   3. Use the following query to identify available ALL_ views:
      SELECT TABLE_NAME FROM DICTIONARY WHERE TABLE_NAME LIKE 'ALL_%';
   4. Use the following query to identify available USER_ views:
      SELECT TABLE_NAME FROM DICTIONARY WHERE TABLE_NAME LIKE 'USER_%';
   5. Use the following query to identify available V$ views:
      SELECT TABLE_NAME FROM DICTIONARY WHERE TABLE_NAME LIKE 'V$%';
   6. Select records from one or more DBA_, ALL_, USER_, and V$ views.

Lesson Summary

• Oracle database utilities depend on correct environment variable settings for ORACLE_HOME and ORACLE_SID.
• %ORACLE_HOME%\bin must be included in the PATH environment variable.
• Enterprise Manager Control (emctl) is used to start, stop, and display the status of the Enterprise Manager console website.
• The Enterprise Manager database home page is located at https://server:1158/em.

References

Oracle Database Administration

Lesson 3 - Network

This lesson introduces the Oracle database network environment.

Objectives and Skills

Objectives and skills for the network environment portion of Oracle Database Administration I certification include:

• Configuring the Oracle Network Environment
  • Configure and Manage the Oracle Network
  • Using the Oracle Shared Server architecture

Readings

1. Oracle: Database 2-Day DBA Configuring the Network Environment

Multimedia

1. YouTube: Understanding the tnsnames.ora and listener.ora files

Activities

Network

1. Configure the network environment.
   1. Complete the tutorial Oracle: Configuring the Network Environment.

Listener

1. Stop and start the listener.
   1. Review Oracle: Configuring and Administering Oracle Net Listener.
   2. Use Enterprise Manager to stop and start the listener.
   3. Use the following commands to view status, stop, and start the Enterprise Manager console service:
      lsnrctl status
lsnrctl stop
lsnrctl start
   4. Use the Windows Services management console to view status, stop, and start the OracleOraDb11g_<network>TNSListener service.
2. Test local and remote connections.
   1. Review Oracle: Net Services.
   2. Use SQL*PLUS or SQL Developer to connect to the database from the server and from a network client to verify functionality. Close the client application.
   3. Use Enterprise Manager, lsnrctl, Services, or the net command to stop the listener service.
   4. Use SQL*PLUS or SQL Developer to connect to the database from the server and from a network client to verify functionality. The local server connection should still work, while the network client connection will fail.
   5. Use Enterprise Manager, lsnrctl, Services, or the net command to start the listener service.
   6. Use SQL*PLUS or SQL Developer to connect to the database from a network client to verify functionality. Do not close the client application.
   7. Use Enterprise Manager, lsnrctl, Services, or the net command to stop the listener service.
   8. Use the following query to select instance information:
      SELECT * FROM V$INSTANCE;
      Stopping the listener prevents new network connections but does not interrupt existing connections.
   9. Use Enterprise Manager, lsnrctl, Services, or the net command to start the listener service.
3. View listener settings.
   1. Review Oracle: Managing Oracle Net Services.
   2. Use Enterprise Manager to view listener settings.
   3. Use a text editor to open the %ORACLE_HOME%\network\admin\listener.ora file and view listener settings.
   4. Use Net Manager to view listener settings.
   5. Use Network Configuration Assistant to view / reconfigure listener settings. Cancel the assistant without saving changes.

TNSNAMES

1. View and edit tnsnames.ora settings.
   1. Review Oracle: Local Naming Parameters (tnsnames.ora).
   2. Use a text editor to open the %ORACLE_HOME%\network\admin\tnsnames.ora file.
   3. Use the following command to connect to the local server using this tnsnames.ora file:
      sqlplus system/<password>@orcl
      The connection should work, using the orcl connection information found in tnsnames.ora.
   4. Use the following command to connect to the local server using this tnsnames.ora file:
      sqlplus system/<password>@test
      The connection should fail, because there is no test service entry in the tnsnames.ora file.
   5. In tnsnames.ora, copy the existing orcl service entry and paste it after the existing entry. Rename the copied service entry as TEST. Leave all other information as is. Save the updated tnsnames.ora file.
   6. Use the following command to connect to the local server using this tnsnames.ora file:
      sqlplus system/<password>@test
      The connection should now work, because there is a test service entry in the tnsnames.ora file.

SQLNET

1. View sqlnet.ora settings.
   1. Review Wikipedia: Sqlnet.ora and Oracle: Profile Parameters (sqlnet.ora).
   2. Use a text editor to open the %ORACLE_HOME%\network\admin\sqlnet.ora file.
   3. Examine each entry in the file to determine the result of that setting.

Shared Server

1. Review Oracle: Configuring Oracle Database for Shared Server.
2. View the current server environment.
   1. Open two command prompts, one for viewing listener status and one for SQL*PLUS queries. Connect to Oracle using:
      SQLPLUS / AS SYSDBA
   2. Use the following query to display current processes:
      SELECT PROGRAM FROM V$PROCESS ORDER BY PROGRAM;
   3. Use the following command to view listener status:
      lsnrctl status
3. Configure the shared server environment.
   1. Use SQL*PLUS to enable shared server processes with the following query:
      ALTER SYSTEM SET SHARED_SERVERS = 20;
   2. Configure dispatcher processes with the following query:
      ALTER SYSTEM SET DISPATCHERS='(PROT=tcp)(DISP=2)';
   3. Register the configuration with the following query:
      ALTER SYSTEM REGISTER;
   4. Use the following query to display current processes:
      SELECT PROGRAM FROM V$PROCESS ORDER BY PROGRAM;
      Note the addition of the D0?? and S0?? processes.
   5. Use the following query to display shared server information:
      SELECT * FROM V$SHARED_SERVER;
   6. Use the following query to display dispatcher information:
      SELECT * FROM V$DISPATCHER;
   7. Use the following command to view the new listener status:
      lsnrctl status
      Note the additional dispatcher listener configuration.
4. Reset the shared server environment.
   1. Use the following query sequence to reset the shared server environment:
      ALTER SYSTEM SET DISPATCHERS='';
ALTER SYSTEM SET SHARED_SERVERS=0;
ALTER SYSTEM REGISTER;
   2. Query V$PROCESS, V$SHARED_SERVER, and V$DISPATCHER and view listener status to confirm the configuration.

References

Oracle Database Administration

Lesson 4 - Architecture

This lesson introduces Oracle database architecture.

Objectives and Skills

Objectives and skills for the database architecture portion of Oracle Database Administration I certification include:

• Exploring the Oracle Database Architecture
  • Explain the Memory Structures
  • Describe the Process Structures
  • Overview of Storage Structures

Readings

1. Oracle: Database 2-Day DBA Managing the Oracle Instance

Multimedia

1. YouTube: General Oracle Architecture
2. YouTube: Processes & Memory
3. YouTube: Datafiles & Tablespaces pt 1

Activities

1. Complete the tutorial Oracle: Managing the Oracle Instance.
2. Identify instance configuration.
   1. Review Oracle: Introduction to Oracle Database Architecture.
   2. Using either SQL*PLUS or SQL Developer, connect to Oracle Database as either SYSTEM or SYS AS SYSDBA.
   3. Use the following query to identify clustered (YES) or stand-alone mode:
      SELECT PARALLEL FROM V$INSTANCE;
   4. Use the following query to identify data protection level:
      SELECT PROTECTION_LEVEL FROM V$DATABASE;
   5. Use the following query to identify Streams configuration:
      SELECT * FROM DBA_STREAMS_ADMINISTRATOR;
3. Identify memory structures.
   1. Review Oracle: Memory Architecture.
   2. Use the following query to identify System Global Area (SGA) memory structures and usage:
      SELECT COMPONENT, CURRENT_SIZE, MIN_SIZE, MAX_SIZE FROM V$SGA_DYNAMIC_COMPONENTS;
   3. Use the following query to identify Program Global Area (PGA) memory usage:
      SELECT * FROM V$PGASTAT;
4. Identify process structures.
   1. Review Oracle: Process Architecture.
   2. Use the following query to identify current sessions:
      SELECT PROGRAM FROM V$SESSION;
   3. Use the following query to identify current processes:
      SELECT PROGRAM FROM V$PROCESS;
5. Identify storage structures.
   1. Review Oracle: Tablespaces, Datafiles, and Control Files.
   2. Use the following query to identify user extents:
      SELECT * FROM DBA_EXTENTS WHERE TABLESPACE_NAME = 'USERS';
   3. Use the following query to identify data files:
      SELECT * FROM V$DATAFILE;
   4. Use the following query to identify table spaces:
      SELECT * FROM DBA_TABLESPACES;
   5. Use the following query to identify log files:
      SELECT * FROM V$LOGFILE
   6. Use the following query to identify the control file:
      SELECT * FROM V$CONTROLFILE;
   7. Use the following query to identify log information:
      SELECT * FROM V$LOG

See Also

• Oracle: Database Architecture
• Oracle: Memory Architecture
• Oracle: Process Architecture
• Oracle: V$INSTANCE
• Oracle: V$DATABASE
• Oracle: DBA_STREAMS_ADMINISTRATOR
• Oracle: V$SGA_DYNAMIC_COMPONENTS
• Oracle: V$PGASTAT
• Oracle: V$SESSION
• Oracle: V$PROCESS
• Oracle: DBA_EXTENTS
• Oracle: V$DATAFILE
• Oracle: DBA_TABLESPACES
• Oracle: V$LOGFILE
• Oracle: V$CONTROLFILE
• Oracle: V$LOG

References

Oracle Database Administration

Lesson 5 - Storage

This lesson introduces Oracle database storage structures.

Objectives and Skills

Objectives and skills for the storage structures portion of Oracle Database Administration I certification include:

• Managing Database Storage Structures
  • Overview of tablespace and datafiles
  • Create and manage tablespaces
  • Space management in tablespaces

Readings

1. Oracle: Database 2-Day DBA Managing Database Storage Structures

Multimedia

1. YouTube: Datafiles & Tablespaces pt 2

Activities

Tutorial

1. Complete the tutorial Oracle: Managing Database Storage Structures.

Control Files

1. Identify control file copies.
   1. Use Enterprise Manager Database Control / Server / Storage to identify current control file copies.
   2. Use the following query to identify current control file copies:
      SELECT * FROM V$CONTROLFILE;

Tablespaces and Datafiles

1. Identify existing tablespaces and datafiles.
   1. Use Enterprise Manager Database Control / Server / Storage to identify current tablespaces and usage.
   2. Use the following query to identify current tablespaces and usage:
      SELECT * FROM V$TABLESPACE;
   3. Use the following query to identify current datafiles:
      SELECT NAME FROM V$DATAFILE;
2. Create new tablespaces.
   1. Use Enterprise Manager to create a new tablespace named TEST. Add a corresponding datafile with 1 MB file size and autoextend storage in 1 MB increments. Use Show SQL to display the generated SQL before creating the tablespace and datafile.
   2. Use the following query to identify current datafiles:
      SELECT NAME FROM V$DATAFILE;
   3. Use the following query to create another tablespace and datafile:
      CREATE TABLESPACE TEST2
DATAFILE '<data file path>\TEST2.DBF'
SIZE 1M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED
LOGGING EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;
   4. Use the following queries to verify tablespace and datafile creation:
      SELECT * FROM V$TABLESPACE;
SELECT NAME FROM V$DATAFILE;
3. Manage tablespaces.
   1. Use Enterprise Manager to take the TEST tablespace offline using Normal mode.
   2. Use Enterprise Manager to display tablespace status. Observe the change in status for the TEST tablespace.
   3. Use Enterprise Manager to place the TEST tablespace online.
   4. Use Enterprise Manager to make the TEST tablespace readonly.
   5. Use Enterprise Manager to display tablespace status. Observe the change in status for the TEST tablespace.
   6. Use Enterprise Manager to make the TEST tablespace writable.
   7. Use Enterprise Manager to display tablespace status. Observe the change in status for the TEST tablespace.
   8. Use the following query to take the TEST2 tablespace offline:
      ALTER TABLESPACE TEST2 OFFLINE NORMAL;
   9. Use the following query to display tablespace status:
      SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACES;
   10. Use the following query to place the TEST2 tablespace online:
       ALTER TABLESPACE TEST2 ONLINE;
   11. Use the following query to make the TEST2 tablespace readonly:
       ALTER TABLESPACE TEST2 READ ONLY;
   12. Use the following query to display tablespace status:
       SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACES;
   13. Use the following query to make the TEST2 tablespace writable:
       ALTER TABLESPACE TEST2 READ WRITE;
   14. Use the following query to display tablespace status:
       SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACES;
4. Delete tablespaces.
   1. Use Enterprise Manager to delete the TEST tablespace and associated data files.
   2. Use the following query to delete the TEST2 tablespace and associated data files:
      DROP TABLESPACE TEST2 INCLUDING CONTENTS AND DATAFILES;
5. Use Oracle-managed files.
   1. Review Oracle: Using Oracle-Managed Files.
   2. Enable Oracle-managed files using the following query:
      ALTER SYSTEM SET DB_CREATE_FILE_DEST = '%ORACLE_HOME%\ORADATA\ORCL';
   3. Create a new tablespace using the following query:
      CREATE TABLESPACE TEST3;
   4. View datafile information using the following query:
      SELECT NAME FROM V$DATAFILE;
   5. Remove the new tablespace using the following query:
      DROP TABLESPACE TEST3 INCLUDING CONTENTS AND DATAFILES;

Segments, Extents, and Data Blocks

1. Review Oracle: Data Blocks, Extents, and Segments.
2. Review Oracle: Displaying Information About Space Usage for Schema Objects.
3. Describe the DBA_SEGMENTS view with the following query:
   DESCRIBE DBA_SEGMENTS;
4. Describe the DBA_EXTENTS view with the following query:
   DESCRIBE DBA_EXTENTS;
5. Select tablespace, segment, extent, block and storage allocation for the HR schema using the following query:
   SELECT TABLESPACE_NAME, SEGMENT_TYPE, SEGMENT_NAME, EXTENTS, BLOCKS, BYTES
FROM DBA_SEGMENTS
WHERE OWNER = 'HR'
ORDER BY SEGMENT_TYPE DESC, SEGMENT_NAME;
6. Select tablespace, segment, extent ID, block and storage allocation for the HR schema using the following query:
   SELECT TABLESPACE_NAME, SEGMENT_TYPE, SEGMENT_NAME, EXTENT_ID, BLOCKS, BYTES
FROM DBA_EXTENTS
WHERE OWNER = 'HR'
ORDER BY SEGMENT_TYPE DESC, SEGMENT_NAME, EXTENT_ID;

See Also

• Oracle: Managing Tablespaces
• Oracle: Managing Data Files and Temp Files

References

Oracle Database Administration

Lesson 6 - Undo Data

This lesson introduces Oracle database undo data.

Objectives and Skills

Objectives and skills for the undo data portion of Oracle Database Administration I certification include:

• Managing Undo Data
  • Overview of Undo
  • Transactions and undo data
  • Managing undo

Readings

1. Oracle: Database 2-Day DBA Managing Undo Data
2. Oracle: Database Administrator's Guide Managing Undo

Multimedia

1. YouTube: Managing Undo

Activities

1. Complete the tutorial Oracle: Managing Database Storage Structures.
2. View Undo storage.
   1. Use the following queries to view undo tablespaces and rollback segments:
      SELECT * FROM DBA_TABLESPACES WHERE CONTENTS = 'UNDO';
SELECT * FROM V$PARAMETER WHERE NAME = 'undo_tablespace';
SELECT * FROM DBA_ROLLBACK_SEGS;
   2. Use the following queries to view undo statistics:
      SELECT * FROM V$ROLLSTAT;
SELECT * FROM V$UNDOSTAT;
3. View transactions.
   1. Use the following query to begin a transaction:
      INSERT INTO HR.REGIONS VALUES (5, 'Antarctica');
   2. Use the following query to view transaction information:
      SELECT * FROM V$TRANSACTION;
   3. Open a second connection to the database and use the following query to select HR REGIONS:
      SELECT * FROM HR.REGIONS;
   4. Use the following query in the first session to roll back the transaction:
      ROLLBACK;
   5. Use the following queries to verify the transaction was rolled back:
      SELECT * FROM V$TRANSACTION;
SELECT * FROM HR.REGIONS;
4. Use Flashback.
   1. Use the following query to view HR REGIONS as of an hour ago:
      SELECT * FROM HR.REGIONS AS OF TIMESTAMP (SYSTIMESTAMP - 1 / 24);
   2. Use Enterprise Manager Database Control / Server / Automatic Undo Management / Edit Undo Retention to set the undo retention to 3600 seconds (1 hour).
   3. Use the following query to configure undo retention to 86400 seconds (1 day):
      ALTER SYSTEM SET UNDO_RETENTION = 86400;
5. Create undo tablespaces.
   1. Use Enterprise Manager Database Control / Server / Tablespaces to create an undo tablespace named UNDOTBS2 with a datafile named UNDOTBS02.
   2. Use the following query to identify the location of existing datafiles:
      SELECT NAME FROM V$DATAFILE;
   3. Use the following query to create an undo tablespace:
      CREATE UNDO TABLESPACE UNDOTBS3
DATAFILE '<data file path>\UNDOTBS03.DBF'
SIZE 1M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;
6. Manage undo tablespaces.
   1. Use Enterprise Manager Database Control / Server / Tablespaces to edit UNDOTBS2 and enable Undo Retention Guarantee.
   2. Use Enterprise Manager Database Control / Server / Automatic Undo Management to change the undo tablespace to UNDOTBS2.
   3. Use the following query to enable undo retention guarantee on UNDOTBS3:
      ALTER TABLESPACE UNDOTBS3 RETENTION GUARANTEE;
   4. Use the following query to change the undo tablespace to UNDOTBS3:
      ALTER SYSTEM SET UNDO_TABLESPACE = 'UNDOTBS3';
7. Delete undo tablespaces.
   1. Use Enterprise Manager Database Control / Server Automatic Undo Management and Tablespaces or the following queries to change the undo tablespace to UNDOTBS1 and delete the tablespaces UNDOTBS2 and UNDOTBS3:
      ALTER SYSTEM SET UNDO_TABLESPACE = 'UNDOTBS1';
DROP TABLESPACE UNDOTBS2;
DROP TABLESPACE UNDOTBS3;

References

Oracle Database Administration

Lesson 7 - Data Concurrency

This lesson introduces Oracle database data concurrency.

Objectives and Skills

Objectives and skills for the data concurrency portion of Oracle Database Administration I certification include:

• Managing Data and Concurrency
  • Monitor and resolve locking conflicts

Readings

1. Oracle: Database Concepts Data Concurrency and Consistency

Multimedia

1. YouTube: How to kill a user session connected to an Oracle database

Activities

1. Resolve locking conflicts using Enterprise Manager Database Control.
   1. Connect to the database and identify the current session using the following query:
      SELECT DISTINCT SID FROM V$MYSTAT;
   2. Connect to the database in a second session and identify the session using the following query:
      SELECT DISTINCT SID FROM V$MYSTAT;
   3. In the first session, begin a transaction using the following query:
      SELECT * FROM HR.REGIONS FOR UPDATE;
   4. In the second session, begin a transaction using the following query:
      SELECT * FROM HR.REGIONS FOR UPDATE;
   5. Use Enterprise Manager Database Control / Performance / Blocking Sessions to view blocking sessions. Note the Session ID and Serial Number of the idle session.
   6. Select Kill Session and then Show SQL.
   7. Kill the idle session and then refresh Blocking Sessions.
   8. Return to the two sessions and observe the results.
   9. Identify the current session using the following query:
      SELECT DISTINCT SID FROM V$MYSTAT;
   10. In the session that is still active, roll back the transaction using the following query:
       ROLLBACK;
2. Resolve locking conflicts using SQL.
   1. Connect to the database and identify the current session using the following query:
      SELECT DISTINCT SID FROM V$MYSTAT;
   2. Connect to the database in a second session and identify the session using the following query:
      SELECT DISTINCT SID FROM V$MYSTAT;
   3. In the first session, begin a transaction using the following query:
      SELECT * FROM HR.REGIONS FOR UPDATE;
   4. In the second session, begin a transaction using the following query:
      SELECT * FROM HR.REGIONS FOR UPDATE;
   5. Connect to the database in a third session and identify the blocking and blocked sessions using the following queries:
      SELECT * FROM V$LOCK WHERE BLOCK != 0;
SELECT SID, BLOCKING_SESSION FROM V$SESSION WHERE BLOCKING_SESSION IS NOT NULL;
   6. Identify the SID and Serial# of the blocking session using the following query:
      SELECT SID, SERIAL#, WAIT_CLASS FROM V$SESSION WHERE SID IN
(SELECT BLOCKING_SESSION FROM V$SESSION WHERE BLOCKING_SESSION != 0);
   7. Kill the blocking session by filling in the SID and Serial# in the following query:
      ALTER SYSTEM KILL SESSION '<SID>,<SERIAL#>' IMMEDIATE;
   8. Return to the two sessions and observe the results.
   9. Identify the current session using the following query:
      SELECT DISTINCT SID FROM V$MYSTAT;
   10. In the session that is still active, roll back the transaction using the following query:
       ROLLBACK;
3. Create a deadlock.
   1. Connect to the database and identify the current session using the following query:
      SELECT DISTINCT SID FROM V$MYSTAT;
   2. Connect to the database in a second session and identify the session using the following query:
      SELECT DISTINCT SID FROM V$MYSTAT;
   3. In the first session, begin a transaction using the following query:
      SELECT * FROM HR.REGIONS FOR UPDATE;
   4. In the second session, begin a transaction using the following query:
      SELECT * FROM HR.COUNTRIES FOR UPDATE;
   5. In the first session, extend the transaction using the following query:
      SELECT * FROM HR.COUNTRIES FOR UPDATE;
   6. In the second session, extend the transaction using the following query:
      SELECT * FROM HR.REGIONS FOR UPDATE;
   7. In the session that detects a deadlock, roll back the remaining transaction using the following query:
      ROLLBACK;
   8. In the session that is still active, roll back the transaction using the following query:
      ROLLBACK;

See Also

• Oracle: V$LOCK
• Oracle: V$SESSION

References

Oracle Database Administration

Lesson 8 - User Security

This lesson introduces Oracle database user security.

Objectives and Skills

Objectives and skills for the user security portion of Oracle Database Administration I certification include:

• Administering User Security
  • Create and manage database user accounts
  • Grant and revoke privileges
  • Create and manage roles
  • Create and manage profiles

Readings

1. Oracle: Database 2-Day DBA Administering User Accounts and Security

Multimedia

1. YouTube: User, Role, Privileges, Password Policy Administration
2. Oracle Enterprise User Security

Activities

Tutorial

1. Complete the tutorial Oracle: Administering Users and Security.

Database Authentication

1. Review Oracle: Administering Authentication.
2. Display existing users.
   1. Use Enterprise Manager Database Control / Server / Users to display existing users.
   2. Use the following query to describe the DBA_USERS table:
      DESCRIBE DBA_USERS;
   3. Use the following query to display existing users:
      SELECT * FROM DBA_USERS;
3. Add users.
   1. Use Enterprise Manager Database Control / Server / Users to add a new user named USER1 with a password of password.
   2. Use the following command to connect to the server as USER1:
      sqlplus user1/password
   3. Use the following query as SYS or SYSTEM to add a new user named USER2 with a password of password:
      CREATE USER USER2 IDENTIFIED BY password;
   4. Use the following command to attempt to connect to the server as USER2:
      sqlplus user2/password
   5. Use the following query as SYS or SYSTEM to grant a connection to USER2:
      GRANT CREATE SESSION TO USER2;
   6. Use the following command to connect to the server as USER2:
      sqlplus user2/password
4. Grant access to resources.
   1. Review Oracle: Configuring Privilege and Role Authorization.
   2. As USER1 or USER2, attempt to select data from the HR schema using the following query:
      SELECT * FROM REGIONS;
   3. As SYS or SYSTEM, use the following query to grant SELECT access to HR.REGIONS:
      GRANT SELECT ON HR.REGIONS TO USER2;
   4. Use the following query to confirm permissions:
      SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE LIKE 'USER%';
   5. As USER2, select data from the HR schema using the following query:
      SELECT * FROM REGIONS;
5. Modify users.
   1. Use Enterprise Manager Database Control / Server / Users to change the password for USER1 to newpass.
   2. Use the following query to change the password for USER2 to newpass:
      ALTER USER USER2 IDENTIFIED BY newpass;
   3. Use Enterprise Manager Database Control / Server / Users to lock the account for USER1.
   4. Use the following query to lock the account for USER2:
      ALTER USER USER2 ACCOUNT LOCK;
   5. Use the following query to unlock the accounts for USER1 and USER2:
      ALTER USER USER1 ACCOUNT UNLOCK;
ALTER USER USER2 ACCOUNT UNLOCK;

Database Administrator Authentication

1. Review Oracle: Authentication of Database Administrators.
2. Add administrator users.
   1. Add an operating system account for USER1 and USER2. In Windows use Computer Management / Local Users and Groups / Users. In Linux, use useradd.
   2. Log off and log on as USER1 or USER2. Attempt to connect to the database using the following commands:
      sqlplus user1/newpass as sysdba
sqlplus user2/newpass as sysoper
   3. Log off and log on as Administrator. Connect to the database using the following command:
      sqlplus / as sysdba
   4. Use Enterprise Manager Database Control / System / Users to edit USER1 and edit the System Privileges list to add the SYSDBA privilege and select the Admin Option checkbox.
   5. Use the following query to grant SYSOPER privilege to USER2:
      GRANT SYSOPER TO USER2;
   6. Use the following query to confirm the changes:
      SELECT * FROM V$PWFILE_USERS;
   7. Log off and log on as USER1 or USER2. Connect to the database using the following commands:
      sqlplus user1/newpass as sysdba
sqlplus user2/newpass as sysoper
   8. Log off and log on as Administrator. Connect to the database using the following command:
      sqlplus / as sysdba

External Authentication

1. Review Oracle: Administering Authentication.
2. Configure external authentication.
   1. Use the following query to set the Oracle user authentication prefix to an empty string:
      ALTER SYSTEM SET OS_AUTHENT_PREFIX = '' SCOPE = SPFILE;
   2. Use the following query to shutdown and startup the database so that the change takes effect:
      SHUTDOWN TRANSACTIONAL
STARTUP
3. Add external users.
   1. Use the following query to identify the server host name:
      SELECT HOST_NAME from V$INSTANCE;
   2. Use the host name in following query to add a new user named USER3 authenticated by the operating system:
      CREATE USER "<HOST_NAME>\USER3" IDENTIFIED EXTERNALLY;
   3. Use the following query to grant a connection to <HOST_NAME>\USER3:
      GRANT CREATE SESSION TO USER3;
   4. Add an operating system account for USER3. In Windows use Computer Management / Local Users and Groups / Users. In Linux, use useradd.
4. Test external authentication.
   1. Log off and log onto the system as USER3. Use the following command to connect to Oracle as USER3:
      sqlplus /
   2. Log off and log onto the system as Administrator. Use the following command to connect to Oracle as SYS
      sqlplus / as sysdba
5. Delete external users.
   1. Use the following query to delete USER3:
      DROP USER USER3;
   2. Delete USER3 from the system using Computer Management or userdel.

Global Authentication

1. Review Oracle: Global Authentication and Authorization
2. Add global users.
   1. Add a global directory account for USER4. Note the distinguished directory service name for the user.
   2. Add USER4 using the distinguished name in the following query:
      CREATE USER USER4 IDENTIFIED GLOBALLY AS '<distinguished name>';
   3. Use the following query to grant a connection to USER4:
      GRANT CREATE SESSION TO USER4;
3. Test global users.
   1. Log off and log onto the system as USER4. Use the following command to connect to Oracle as USER4:
      sqlplus /
   2. Log off and log onto the system as Administrator. Use the following command to connect to Oracle as SYS:
      sqlplus / as sysdba
4. Delete global users.
   1. Use the following query to delete USER4:
      DROP USER USER4;
   2. Delete the global directory account for USER4.

Roles

1. Create roles.
   1. Review Oracle: Configuring Privilege and Role Authorization.
   2. Use the following queries to create roles for the HR schema:
      CREATE ROLE HR_VIEW;
CREATE VIEW HR_UPDATE;
2. Manage roles.
   1. Use the following queries to grant access to the HR schema roles:
      GRANT CREATE SESSION TO HR_VIEW;
GRANT SELECT ON HR.REGIONS TO HR_VIEW;
GRANT SELECT ON HR.DEPARTMENTS TO HR_VIEW;
GRANT SELECT ON HR.COUNTRIES TO HR_VIEW;
GRANT SELECT ON HR.JOB_HISTORY TO HR_VIEW;
GRANT SELECT ON HR.EMPLOYEES TO HR_VIEW;
GRANT SELECT ON HR.LOCATIONS TO HR_VIEW;
GRANT SELECT ON HR.JOBS TO HR_VIEW;
 
GRANT HR_VIEW TO HR_UPDATE;
GRANT INSERT, UPDATE, DELETE ON HR.REGIONS TO HR_VIEW;
GRANT INSERT, UPDATE, DELETE ON HR.DEPARTMENTS TO HR_VIEW;
GRANT INSERT, UPDATE, DELETE ON HR.COUNTRIES TO HR_VIEW;
GRANT INSERT, UPDATE, DELETE ON HR.JOB_HISTORY TO HR_VIEW;
GRANT INSERT, UPDATE, DELETE ON HR.EMPLOYEES TO HR_VIEW;
GRANT INSERT, UPDATE, DELETE ON HR.LOCATIONS TO HR_VIEW;
GRANT INSERT, UPDATE, DELETE ON HR.JOBS TO HR_VIEW;
 
GRANT HR_VIEW TO USER1;
GRANT HR_UPDATE TO USER2;
   2. Use the following queries to confirm roles and permissions:
      SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE LIKE 'HR_%';
SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE LIKE 'HR_%';
SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE LIKE 'USER%';
   3. Connect as USER1 and USER2 and test access for each using the following queries:
      SELECT * FROM REGIONS;
INSERT INTO REGIONS VALUES(5, 'Antarctica');
SELECT * FROM REGIONS;
ROLLBACK;
3. Delete roles.
   1. Use the following queries to remove roles:
      DROP ROLE HR_VIEW;
DROP ROLE HR_UPDATE;
   2. Use the following queries to confirm roles and permissions:
      SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE LIKE 'HR_%';
SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE LIKE 'HR_%';
SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE LIKE 'USER%';

Profiles

1. Review Oracle: CREATE PROFILE.
2. View existing profile settings.
   1. Use the following queries to view existing profile settings:
      SELECT USERNAME, PROFILE FROM DBA_USERS;
SELECT * FROM DBA_PROFILES WHERE PROFILE = 'DEFAULT';
3. Create a profile.
   1. Use the following query to create a profile:
      CREATE PROFILE "RESTRICTED" LIMIT
SESSIONS_PER_USER 1
IDLE_TIME 10
FAILED_LOGIN_ATTEMPTS 5
PASSWORD_LOCK_TIME 1/24
PASSWORD_GRACE_TIME 5;
4. Assign a profile.
   1. Use the following query to assign the profile to USER1:
      ALTER USER USER1 PROFILE "RESTRICTED";
   2. Attempt to connect as USER1 with multiple concurrent sessions to test the profile.
5. Delete a profile.
   1. Use the following query to delete the RESTRICTED profile:
      DROP PROFILE "RESTRICTED";

Cleanup

1. Delete users.
   1. Use Enterprise Manager to delete USER1.
   2. Use the following query to delete USER2:
      DROP USER USER2;
   3. Use the following query to confirm the changes:
      SELECT * FROM V$PWFILE_USERS;

References

Oracle Database Administration

Lesson 9 - Database Security

This lesson introduces Oracle database security.

Objectives and Skills

Objectives and skills for the database security portion of Oracle Database Administration I certification include:

• Implementing Oracle Database Security
  • Database Security and Principle of Least Privilege
  • Work with Standard Database Auditing

Readings

1. Oracle: Database 2 Day + Security Guide

Multimedia

1. YouTube: Security in the Database

Activities

Privileges

1. Review Oracle: Managing Compliance.
2. Use the following query to identify UTL packages that PUBLIC has EXECUTE privileges for:
   SELECT DISTINCT TABLE_NAME FROM DBA_TAB_PRIVS
WHERE GRANTEE = 'PUBLIC' AND TABLE_NAME LIKE 'UTL_%' AND PRIVILEGE = 'EXECUTE'
ORDER BY TABLE_NAME;
3. Consider revoking EXECUTE access from PUBLIC for all UTL packages, but at a minimum restrict the following:
   REVOKE EXECUTE ON DBMS_JOB FROM PUBLIC;
REVOKE EXECUTE ON DBMS_LOB FROM PUBLIC;
REVOKE EXECUTE ON UTL_FILE FROM PUBLIC;
REVOKE EXECUTE ON UTL_HTTP FROM PUBLIC;
REVOKE EXECUTE ON UTL_SMTP FROM PUBLIC;
REVOKE EXECUTE ON UTL_TCP FROM PUBLIC;

Auditing

1. Review Oracle: Audit Trail.
2. Enable auditing.
   1. Use the following queries to enable auditing:
      ALTER SYSTEM SET AUDIT_TRAIL = DB_EXTENDED SCOPE = SPFILE;
   2. Stop and restart the database so that the changes take effect using the following queries:
      shutdown transactional
startup
   3. Audit database connections using the following queries:
      AUDIT SESSION WHENEVER SUCCESSFUL;
AUDIT SESSION WHENEVER NOT SUCCESSFUL;
3. Examine the audit log.
   1. View the audit log using the following query:
      SELECT * FROM DBA_AUDIT_TRAIL;

See Also

• Oracle: Administering User Accounts and Security
• Oracle: Keeping Your Oracle Database Secure
• isaca.org: Oracle Database Security Checklist

References

Oracle Database Administration

Lesson 10 - Maintenance

This lesson introduces Oracle database maintenance.

Objectives and Skills

Objectives and skills for the database maintenance portion of Oracle Database Administration I certification include:

• Database Maintenance
  • Use and manage optimizer statistics
  • Use and manage Automatic Workload Repository (AWR)
  • Use advisory framework
  • Manage Alerts and Thresholds

Readings

1. Oracle: Database 2-Day DBA Monitoring and Tuning the Database
2. Oracle: Database 2-Day + Performance Tuning Guide Oracle Database Performance Method
3. Oracle: Database 2-Day + Perforamce Tuning Guide Automatic Database Performance Monitoring

Multimedia

1. YouTube: Oracle Performance Tuning - Oracle Enterprise Manager - AWR

Activities

Tutorial

1. Complete the tutorial Oracle: Monitoring and Tuning the Database.

Optimizer Statistics

1. Use the following query to verify that automatic optimizer statistics collection is enabled:
   SELECT * FROM DBA_AUTOTASK_CLIENT;
2. Use the following queries to determine when statistics were last updated for tables, indexes, and columns in the HR schema:
   SELECT OWNER, TABLE_NAME, LAST_ANALYZED FROM DBA_TABLES WHERE OWNER = 'HR';
SELECT OWNER, INDEX_NAME, LAST_ANALYZED FROM DBA_INDEXES WHERE OWNER = 'HR';
SELECT OWNER, TABLE_NAME, COLUMN_NAME, LAST_ANALYZED FROM DBA_TAB_COLUMNS WHERE OWNER = 'HR';
3. Gather statistics for the HR.REGIONS table using the following query:
   EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'REGIONS');
4. Verify that statistics were updated using the following query:
   SELECT OWNER, TABLE_NAME, LAST_ANALYZED FROM DBA_TABLES WHERE OWNER = 'HR';
5. Gather statistics for all objects in the HR schema using the following query:
   EXEC DBMS_STATS.GATHER_SCHEMA_STATS('HR');
6. Verify that statistics were updated using the following queries:
   SELECT OWNER, TABLE_NAME, LAST_ANALYZED FROM DBA_TABLES WHERE OWNER = 'HR';
SELECT OWNER, INDEX_NAME, LAST_ANALYZED FROM DBA_INDEXES WHERE OWNER = 'HR';
SELECT OWNER, TABLE_NAME, COLUMN_NAME, LAST_ANALYZED FROM DBA_TAB_COLUMNS WHERE OWNER = 'HR';
7. If you want to manually update statistics for all objects in the database, you can use the following query, but note that it may take a long time to run and will impact database performance while it runs:
   EXEC DBMS_STATS.GATHER_DATABASE_STATS();

Automatic Workload Repository (AWR)

1. Use Enterprise Manager Database Control / Server / Automatic Workload Repository / Snapshots to select a snapshot. Review the corresponding snapshot report.
2. Use Enterprise Manager Database Control / Server / Automatic Workload Repository / Edit to view options for snapshot collection and retention. If desired, modify snapshot settings.

Advisory Framework

1. Use Enterprise Manager Database Control / Advisor Central to view available advisors and tasks. View the current status for each advisor.
2. Use Enterprise Manager Database Control / Server / Automated Maintenance Tasks to view the task schedule. Configure changes if desired.

Alerts and Thresholds

1. Use Enterprise Manager Database Control / Metric and Policy Settings to view current metric thresholds and policies.
2. Use Enterprise Manager / Setup to view and configure notification methods.

See Also

• Oracle: Managing Optimizer Statistics
• Oracle: Overview of the Automatic Workload Repository
• Oracle: DBA_AUTOTASK_CLIENT

References

Oracle Database Administration

Lesson 11 - Performance

This lesson introduces Oracle database performance management.

Objectives and Skills

Objectives and skills for the performance management portion of Oracle Database Administration I certification include:

• Performance Management
  • Use Automatic Memory Management
  • Use Memory Advisors
  • Troubleshoot invalid and unusable objects

Readings

1. Oracle: Database 2-Day DBA Monitoring and Tuning the Database

Multimedia

1. YouTube: Oracle Database Performance Tuning for Admins and Architects

Activities

Automatic Memory Management

1. Use Enterprise Manager Database Control / Server / Memory Advisors to view and configure Automatic Memory Management.
2. Disable Automatic Memory Management.
3. View available SGA and PGA settings.
4. Enable Automatic Memory Management.

Memory Advisors

1. Use the following queries to view memory advisor values:
   SELECT * FROM V$PGA_TARGET_ADVICE;
SELECT * FROM V$SGA_TARGET_ADVICE;
SELECT * FROM V$MEMORY_TARGET_ADVICE;

Invalid and Unusable Objects

1. Use the following query to identify invalid objects:
   SELECT OWNER, OBJECT_TYPE, OBJECT_NAME FROM DBA_OBJECTS WHERE STATUS = 'INVALID';
2. Use the following query to identify unusable indexes:
   SELECT OWNER, INDEX_NAME FROM DBA_INDEXES WHERE STATUS = 'UNUSABLE';

See Also

• sql-bench A standalone SQL Benchmark test tools that comes with MySQL.

References

Oracle Database Administration

Lesson 12 - Moving Data

This lesson introduces moving data.

Objectives and Skills

Objectives and skills for the moving portion of Oracle Database Administration I certification include:

• Moving Data
  • Describe and use methods to move data (Directory objects, SQL*Loader, External Tables)
  • Explain the general architecture of Oracle Data Pump
  • Use Data Pump Export and Import to move data between Oracle databases

Readings

1. Oracle: Database Utilities SQL*Loader Concepts
2. Oracle: Database Utilities Overview of Oracle Data Pump
3. Oracle: Database Utilities Data Pump Export
4. Oracle: Database Utilities Data Pump Import

Multimedia

1. YouTube: Using Oracle SQL Loader
2. YouTube: Data Pump, Oracle DBA, export whole schema and import selected objects

Activities

SQL*Loader

1. Use SQL*Loader.
   1. Create a text file with the following information. Save the file as regions.txt.
      ID, REGION
101, Eastern Europe
102, Western Europe
103, North America
104, Central America
105, South America
106, Central Asia
107, Eastern Asia
108, Southern Asia
109, Southeastern Asia
110, Western Asia
111, Eastern Africa
112, Central Africa
113, Northern Africa
114, Southern Africa
115, Western Africa
   2. Create an SQL*Loader control file with the following information. Save the file as regions.ctl.
      OPTIONS (SKIP=1)
LOAD DATA
INFILE 'regions.txt'
BADFILE 'regions.bad'
DISCARDFILE 'regions.dsc'
APPEND
INTO TABLE HR.REGIONS
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(REGION_ID INTEGER EXTERNAL(3),
REGION_NAME)
   3. Use SQL*Loader to load the data using the following command:
      sqlldr userid=system/<password> control=regions.ctl
   4. Verify the import using the following query:
      SELECT * FROM HR.REGIONS
   5. Clean up using the following query:
      DELETE FROM HR.REGIONS WHERE REGION_ID >= 101;

External Tables

1. Use an external table.
   1. Create a directory object pointing to an existing operating system directory using the following query:
      CREATE DIRECTORY external_files AS '<path>';
      
   2. Put the regions.txt text file from above in the specified directory.
   3. Create an external table referencing the text file using the following query:
      CREATE TABLE HR.REGIONS_FILE
(
  REGION_ID NUMBER(3),
  REGION_NAME VARCHAR2(25)
)
ORGANIZATION EXTERNAL
(
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY external_files
  ACCESS PARAMETERS
  (
    RECORDS DELIMITED BY NEWLINE
    SKIP 1
    FIELDS TERMINATED BY ','
  )
  LOCATION ('regions.txt')
)
   4. Select data from the external table using the following query:
      SELECT * FROM HR.REGIONS_FILE
   5. Use the following query to load the data into the REGIONS table:
      INSERT INTO HR.REGIONS(REGION_ID, REGION_NAME)
(SELECT REGION_ID, REGION_NAME FROM HR.REGIONS_FILE);
   6. Verify the import using the following query:
      SELECT * FROM HR.REGIONS
   7. Clean up using the following queries:
      DELETE FROM HR.REGIONS WHERE REGION_ID >= 101;
DROP TABLE HR.REGIONS_FILE;
DROP DIRECTORY external_files;

Spool

1. Use spool to export a comma-separated-values copy of the REGIONS table.
   1. Review Charito: How to Write to a CSV File Using Oracle SQL*Plus.
   2. Use SQL*PLUS and run the following commands:
      set colsep ,
set pagesize 0
set trimspool on

spool regions.csv

SELECT * FROM HR.REGIONS;

spool off
   3. Exit SQL*PLUS and list the files in the current directory (DIR or LS). Open the regions.csv file and view the results.

SQL Developer

1. Use SQL Developer to export a comma-separated-values copy of the REGIONS table.
   1. Review Oracle: Using SQL Developer for Importing and Exporting.
   2. Follow the instructions for the Oracle: Example: Exporting Data to a Microsoft Excel File

Oracle Data Pump

1. Identify the data pump directory using the following query:
   SELECT * FROM DBA_DIRECTORIES;
2. Use Enterprise Manager Data Pump Export.
   1. Use Enterprise Manager Database Control / Data Movement / Export to Export Files to export the HR.REGIONS table.
   2. Navigate to the directory specified in the export and open the exported file using a text editor. Observe the file format and contents.
3. Use command-line Data Pump Export.
   1. Use the following command to export the HR schema:
      expdp system/<password> schemas=hr dumpfile=hr.dmp
4. Use command-line Data Pump Import.
   1. Use the following command to import HR.REGIONS into the OE schema:
      impdp system/<password> dumpfile=hr.dmp remap_schema=hr:oe tables=hr.regions
   2. Use the following query to verify the import:
      SELECT * FROM OE.REGIONS;
   3. use the following query to clean up the import:
      DROP TABLE OE.REGIONS;

See Also

• Oracle: SQL*Loader Concepts
• Oracle: SQL*Loader Command-Line Reference
• Oracle: External Table Concepts
• Oracle: Data Pump
• Oracle: Using SQL Developer for Importing and Exporting
• Charito: How to Write to a CSV File Using Oracle SQL*Plus
• Stack Exchange: Oracle - Automate Export/Unload of Data

References

Oracle Database Administration

Lesson 13 - Backup and Recovery

This lesson introduces backup and recovery.

Objectives and Skills

Objectives and skills for the backup and recovery portion of Oracle Database Administration I certification include:

• Backup and Recovery Concepts
  • Identify the types of failure that can occur in an Oracle database
  • Describe ways to tune instance recovery
  • Identify the importance of checkpoints, redo log files, and archived log files
  • Overview of flash recovery area
  • Configure ARCHIVELOG mode
  • Performing Database Backups
• Create consistent database backups
  • Back up your database without shutting it down
  • Create incremental backups
  • Automate database backups
  • Manage backups, view backup reports and monitor the flash recovery area
• Performing Database Recovery
  • Overview of Data Recovery Advisor
  • Use Data Recovery Advisor to Perform recovery (Control file, Redo log file and Data file)

Readings

1. Oracle: Database 2-Day DBA Performing Backup and Recovery

Multimedia

1. YouTube: Backup & Recovery

Activities

1. Complete the tutorial Oracle: Performing Backup and Recovery.

References

2. l Getting Started | Database Administration

3. | Oracle Database Administration

4. | Oracle Database Administration 2

Oracle Database Administration

Lesson 14 - Support

This lesson introduces Oracle database support.

Objectives and Skills

Objectives and skills for the support portion of Oracle Database Administration I certification include:

• Intelligent Infrastructure Enhancements
  • Use the Enterprise Manager Support Workbench
  • Managing Patches

Readings

1. Oracle: Database 2-Day DBA Investigating, Reporting, and Resolving Problems
2. Oracle: Database 2-Day DBA Managing Oracle Database Software

Multimedia

1. YouTube: Oracle Database Patch

Activities

1. Complete the tutorial Oracle: Managing Oracle Database Software.

References

Oracle Database Administration