DBA Fundamentals

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:

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

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 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
/

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

/