Hands-On 15 (Maintaining and Configuring an UNDO tablespace manually)

As a DBA, you are responsible for maintaining UNDO tablespaces manually due to an users’ database transactions.  Always try to use the auto UNDO segments option, versus the manual unless you have a good reason to do so. Now, due to your organization’s backward compatibility, you should maintain a manual UNDO tablespace. Your job’s responsibilities dictate that you should at least be informed of the following basic fundamental subjects:

Maintaining an UNDO tablespace manually

Creating an UNDO tablespace manually using OMF

Using the DICTIONAY-MANAGED tablespace

Creating UNDO segments with OPTIMAL option

Setting the UNDO MANAGEMENT to the MANUAL mode

Shutting down and Starting up the database using SPFILE

OFFLINE an UNDO tablespace

ONLINE an UNDO tablespace

Setting the Rollback Segment status to ONLINE

Dropping the UNDO manual tablepace

Commands:

CREATE TABLESPACE EXTENT MANAGEMENT DICTIONARY

CREATE ROLLBACK SEGMENT TABLESPACE

   STORAGE (INITIAL NEXT MAXEXTENTS OPTIMAL)

ALTER SYSTEM SET undo_management=MANUAL SCOPE=spfile

SHUTDOWN IMMEDIATE

STARTUP

ALTER ROLLBACK SEGMENT ONLINE

DROP TABLESPACE

-- Hands-On 15 (Maintaining and Configuring an UNDO tablespace manually)
-- 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 manually.

-- Now, connect to SQL*Plus as the system/manager user.

pause

CONNECT system/manager AS SYSDBA

pause

CLEAR SCR
-- Create a tablespace named RBS to create UNDO segments 
-- manually using Oracle-Managed Files (OMF).

pause


CREATE TABLESPACE rbs 
DATAFILE size 100k
EXTENT MANAGEMENT DICTIONARY
/

-- Notice that the manual UNDO segments must be created
-- with the DICTIONAY-MANAGED tablespace.

pause

CLEAR SCR
-- Create an UNDO segment with the following storage 
-- settings: an initial size of 10k, a next extent size of 10K,
-- a maximum extent of 100, and an optimal size of 100k. 

pause


CREATE ROLLBACK SEGMENT rbs01
TABLESPACE rbs
STORAGE (INITIAL 10k 
NEXT 10k 
MAXEXTENTS 100 
OPTIMAL 100k)
/

-- Notice that you can't create an UNDO segment when 
-- the database is in the automatic UNDO mode.

pause

CLEAR SCR
-- Show the UNDO parameter values.

pause


SHOW PARAMETER undo

-- Notice that the AUTO_MANAGEMENT mode is AUTO.

pause

CLEAR SCR
-- Let's now change the AUTO mode to the MANUAL mode.

pause


ALTER SYSTEM SET undo_management=MANUAL SCOPE=spfile
/

pause


CLEAR SCR
-- Then shutdown and startup the database.

-- We assume that you have already created your Server 
-- Parameter File (SPFILE) from the pervious exercise.

-- You can also modify the UNDO_MANAGEMENT parameter
-- in the Parameter File (PFILE). But you should 
-- start the database using that specific PFILE.

pause

SHUTDOWN IMMEDIATE

CONNECT system/manager AS SYSDBA

STARTUP

pause

CLEAR SCR
-- Use the SHOW command to display the UNDO parameter 
-- values again.

pause


SHOW PARAMETER undo

-- Notice that this time, the AUTO_MANAGEMENT mode is
-- set to MANUAL.

pause

CLEAR SCR
-- Now, you should be able to create the UNDO segments.

pause

CREATE ROLLBACK SEGMENT rbs01
TABLESPACE rbs
STORAGE (INITIAL 10k 
NEXT 10k 
MAXEXTENTS 100 
OPTIMAL 100k)
/

pause

CLEAR SCR
-- Query the DBA_ROLLBACK_SEGS view to display information
-- about the rollback segment tablespaces.

pause

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

-- Notice that the new manual rollback segment status is OFFLINE.

pause

CLEAR SCR
-- Set the rollback segment status to ONLINE.

pause

ALTER ROLLBACK SEGMENT rbs01 ONLINE
/

pause

CLEAR SCR
-- Query the DBA_ROLLBACK_SEGS view again.

pause

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

-- Notice that the new manual rollback segment is 
-- ONLINE this time.

pause

CLEAR SCR
-- Always try to use automatic rollback segments.
-- It is now easier to manage. 

-- Let's change the UNDO_MANAGEMENT parameter
-- to AUTO. Then shutdown and startup the database.

-- Remember that you can also modify the UNDO_MANAGEMENT
-- parameter in the Parameter File (PFILE), and you 
-- should start the database using that specific PFILE.
pause
ALTER SYSTEM SET undo_management=AUTO SCOPE=spfile;
SHUTDOWN IMMEDIATE
CONNECT system/manager AS SYSDBA
STARTUP
pause

CLEAR SCR
-- Drop the RBS tablespace.

pause


DROP TABLESPACE rbs
INCLUDING CONTENTS 
CASCADE CONSTRAINTS
/

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