Hands-On 14 (Maintaining and Configuring UNDO tablespace)

As a DBA, you are responsible for maintaining UNDO tablespaces due to an users’ database transactions, thanks to Oracle and their handy UNDO_MANAGEMENT parameter. You will indeed find this feature extremely handy. Your job’s responsibilities dictate that you should be at least informed of the following basic fundamental subjects:

Creating an UNDO tablespace automatically

Configuring an UNDO tablespace

Displaying the UNDO MANAGEMENT parameter

Setting the UNDO MANAGEMENT parameter

Using the DBA_ROLLBACK_SEGS view

Creating an UNDO tablespace using OMF

Displaying the OMF created file destination

Setting a tablespace status to ONLINE

Setting the UNDO segments to ONLINE

Changing the UNDO tablespace

Setting the UNDO retention time

Dropping the UNDO tablespace

Deactivate the UNDO tablespace

Commands:

SHOW PARAMETER

ALTER SYSTEM SET db_create_file_dest='c:\newfolder'

CREATE UNDO TABLESPACE DATAFILE

ALTER TABLESPACE ONLINE

ALTER SYSTEM SET undo_tablespace=

ALTER SYSTEM SET undo_retention=

DROP TABLESPACE

-- Hands-On 14 (Maintaining and Configuring UNDO tablespace)
-- Preparation
set echo on
connect system/manager as sysdba
SET linesize 1000 pagesize 55
SET SQLPROMPT 'SQL> '
COL name FORMAT a50
col segment_name format a40
col file_name format a50
col extent_management format a20
col username format a10
col member format a50

pause

--Start

CLEAR SCR
-- In this exercise you will learn how to create and 
-- configure an UNDO tablespace automatically.

-- Let's first connect to SQL*Plus as the system/manager user.

pause

CONNECT system/manager AS SYSDBA

pause

CLEAR SCR
-- To create and configure an UNDO tablespace automatically,
-- let's first use the SHOW command to check whether the 
-- UNDO management is set to AUTO or MANUAL.

pause

SHOW PARAMETER undo

-- Our is set to automatic.
-- If your UNDO management is not set to AUTO, then change it.
-- Open the parameter file and add the UNDO_MANAGEMENT=AUTO
-- line to it. Then shutdown and startup the database.

pause

CLEAR SCR
-- Query the UNDO tablespace information.

pause


SELECT segment_name, tablespace_name, status
FROM dba_rollback_segs
/

pause

CLEAR SCR
-- Now, let's create an UNDO tablespace using Oracle-Managed Files.
-- Before creating a tablespace, make sure that the DB_CREATE_FILE_DEST
-- parameter set to the c:\newfolder directory.

-- Then, create an UNDO tablespace using Oracle-Managed Files 
-- (OMF) with a size of 100k.


pause


ALTER SYSTEM SET db_create_file_dest='c:\newfolder'
/

CREATE UNDO TABLESPACE my_undo_tablespace
DATAFILE SIZE 100K
/

-- Remember that by default, the tablespace size using
-- Oracle-Managed Files is 100Megabytes.

pause

CLEAR SCR
-- Query the UNDO segments information.

pause


SELECT segment_name, tablespace_name, status
FROM dba_rollback_segs
WHERE tablespace_name = 'MY_UNDO_TABLESPACE'
/

-- Notice that Oracle created all of the UNDO segments 
-- for you and it will manage the UNDO segments for you
-- automatically as soon as the UNDO tablespace is created. 

-- Notice that all of the statuses are set to OFFLINE.

pause

CLEAR SCR
-- Set the created UNDO tablespace to ONLINE.

pause


ALTER TABLESPACE my_undo_tablespace ONLINE
/

pause

CLEAR SCR
-- Query the UNDO segments information only
-- where the UNDO tablespace name is my_undo_tablespace.

pause


SELECT segment_name, tablespace_name, status
FROM dba_rollback_segs
WHERE tablespace_name = 'MY_UNDO_TABLESPACE'
/
-- Notice that the segment statuses are still OFFLINE.
-- Remember, when you create an UNDO segment always 
-- their statues are OFFLINE by default.

pause

CLEAR SCR
-- Now that you created your own UNDO tablespace, let's 
-- tell the Oracle database from now on to use it.

pause

ALTER SYSTEM SET undo_tablespace=my_undo_tablespace
/

pause

CLEAR SCR
-- Use the SHOW command to see if the default undo_tablespace
-- was changed.

pause

SHOW PARAMETER undo

pause

CLEAR SCR
-- Query the UNDO segments information only
-- where the UNDO tablespace name is my_undo_tablespace.

pause


SELECT segment_name, tablespace_name, status
FROM dba_rollback_segs
WHERE tablespace_name = 'MY_UNDO_TABLESPACE'
/
-- Notice that this time, all of the segment statuses are 
-- ONLINE.

pause

CLEAR SCR
-- Specify 10 minutes where Oracle retains the committed
-- UNDO information in the database.

pause


ALTER SYSTEM SET undo_retention=600
/

pause

CLEAR SCR
-- Check the changes.

pause


SHOW PARAMETER undo

-- From now on, for 10 minutes, the Oracle database will
-- retain the prechanged, original copy of the data in an 
-- UNDO segment for Flashback Query purposes.

pause


CLEAR SCR
-- Try to drop the UNDO tablespace.

pause


DROP TABLESPACE my_undo_tablespace INCLUDING CONTENTS CASCADE CONSTRAINTS
/

-- That's right. You can't drop an UNDO tablespace 
-- that is currently in use.
pause


CLEAR SCR
-- Set the default UNDO tablespace back to UNDOTBS.

pause


ALTER SYSTEM SET undo_tablespace=undotbs
/

pause


CLEAR SCR
-- Attempt to drop the UNDO tablespace again.

pause


DROP TABLESPACE my_undo_tablespace INCLUDING CONTENTS CASCADE CONSTRAINTS
/

-- No problem this time.

pause


CLEAR SCR
-- Now, you should practice this Hands-On exercise.

-- For more information about the subject, you are encouraged
-- to read from a wide selection of available books.

-- Good luck.
--
pause
pause