|
"The great aim of
education is not knowledge but action." - Herbert Spencer
(1820 - 1903) |
Read
first then play the video:
DBA-VIDEO -Maintaining
and Configuring an UNDO tablespace manually
Maintaining and Configuring
an UNDO tablespace manually
Introduction
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 dom and justice is popular education, without which
neither dom 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
/
|