iSelfSchooling - DBA 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

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.
SQL> CONNECT system/manager AS SYSDBA

Create a manually undo tablespace
Create a tablespace named RBS to create UNDO segments manually using Oracle-Managed Files (OMF).
SQL> CREATE TABLESPACE rbs
DATAFILE size 100k
EXTENT MANAGEMENT DICTIONARY
/
Notice that the manual UNDO segments must be created with the DICTIONAY-MANAGED tablespace. The difference between the DICTIONALY-MANAGED and LOCALLY MANAGED tablespace is: In the locally managed tablespace all information about the datafiles such as the last performed checkpoint, etc are stored locally in the datafiles of the tablespace but in the DICTIONAY-MANAGED tablespace all such information would be stored in the Oracle repository in the SYSTEM tablespace.

Create manually undo segments

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.
SQL> 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.

Show the UNDO parameter values.
SQL> SHOW PARAMETER undo
Notice that the AUTO_MANAGEMENT mode is AUTO.

Change undo_management to manual
Let's now change the AUTO mode to the MANUAL mode.
SQL> ALTER SYSTEM

SET undo_management=MANUAL SCOPE=spfile
/

Shutdown and start a database
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.
SQL> SHUTDOWN IMMEDIATE
SQL> CONNECT system/manager AS SYSDBA
SQL> STARTUP

Check undo parameters
Use the SHOW command to display the UNDO parameter values again.
SQL> SHOW PARAMETER undo
Notice that this time, the AUTO_MANAGEMENT mode is set to MANUAL.

Create undo segment manually
Now, you should be able to create the UNDO segments.
SQL> CREATE ROLLBACK SEGMENT rbs01
TABLESPACE rbs
STORAGE (INITIAL 10k
NEXT 10k
MAXEXTENTS 100
OPTIMAL 100k)
/

Query the DBA_ROLLBACK_SEGS view to display information about the rollback segment tablespaces.
SQL> SELECT segment_name, tablespace_name, status
FROM dba_rollback_segs
WHERE tablespace_name = 'RBS'
/
Notice that the new manual rollback segment status is OFFLINE.

Set undo segment online

Set the rollback segment status to ONLINE.
SQL> ALTER ROLLBACK SEGMENT rbs01 ONLINE
/

Query the DBA_ROLLBACK_SEGS view again.
SQL> 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. Always try to use automatic rollback segments. It is now easier to manage.

Change undo management parameter to original parameter
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.
SQL> ALTER SYSTEM SET undo_management=AUTO SCOPE=spfile;
SQL> SHUTDOWN IMMEDIATE
SQL> CONNECT system/manager AS SYSDBA
SQL> STARTUP

Drop the RBS tablespace.
SQL> DROP TABLESPACE rbs
INCLUDING CONTENTS
CASCADE CONSTRAINTS
/

“Next in importance to freedom and justice is popular education, without which neither freedom nor justice can be permanently maintained.” James A. Garfield (1831 - 1881), July 12, 1880

Questions:

Q: How do you create an UNDO tablespace manually?

Q: What are the differences between an UNDO tablespace manually and automatically?

Q: What is the DICTIONARY-MANAGED tablespace?

Q: What are the differences between a DICTIONARY-MANAGED and LOCALLY managed tablespace?

Q: Describe the OPTIMAL option.

Q: How do you offline an UNDO tablespace?

Q: How do you change an automatically UNDO tablespace to a manually UNDO tablespace?

Q: What do the following SQL statements do?

SQL> CREATE TABLESPACE rbs
DATAFILE size 100k
EXTENT MANAGEMENT DICTIONARY
/

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

SQL> ALTER SYSTEM

SET undo_management=MANUAL SCOPE=spfile
/

SQL> ALTER ROLLBACK SEGMENT rbs01 ONLINE
/

SQL> DROP TABLESPACE rbs
INCLUDING CONTENTS
CASCADE CONSTRAINTS