|
"Democracy does
not guarantee equality of conditions - it only guarantees
equality of opportunity." - Irving Kristol |
Read
first then play the video:
DBA014(VIDEO)-Maintaining
and Configuring UNDO tablespace
Maintaining and Configuring
UNDO tablespace
Introduction
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:'
CREATE UNDO TABLESPACE
DATAFILE
ALTER TABLESPACE ONLINE
ALTER SYSTEM SET
undo_tablespace=
ALTER SYSTEM SET
undo_retention=
DROP TABLESPACE
Hands-on
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.
SQL> CONNECT system/manager AS SYSDBA
View undo parameters
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.
SQL> SHOW PARAMETER undo
The default 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.
Query the UNDO tablespace information.
SQL> SELECT segment_name, tablespace_name,
status
FROM dba_rollback_segs
/
Create an UNDO tablespace (OMF)
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:directory. Then, create an UNDO tablespace
using Oracle-Managed Files (OMF) with a size of 100k.
SQL> ALTER SYSTEM SET db_create_file_dest='c:'
/
SQL> CREATE UNDO TABLESPACE my_undo_tablespace
DATAFILE SIZE 100K
/
Remember that by default, the tablespace size using
Oracle-Managed Files is 100Megabytes.
Query the UNDO segments
information.
SQL> 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.
Set the created UNDO
tablespace to ONLINE.
SQL> ALTER TABLESPACE my_undo_tablespace
ONLINE
/
Query the UNDO segments
information only where the UNDO tablespace name is my_undo_tablespace.
SQL> 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.
Set to use an undo
tablespace
Now that you created your
own UNDO tablespace, let's tell the Oracle database from now on to use
it.
SQL> ALTER SYSTEM SET undo_tablespace=my_undo_tablespace
/
Use the SHOW command to see if the default undo_tablespace was
changed.
SQL> SHOW PARAMETER undo
Query the UNDO segments information only where the UNDO tablespace
name is my_undo_tablespace.
SQL> 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.
Change undo retention time
Specify 10 minutes where Oracle retains the committed UNDO
information in the database.
SQL> ALTER SYSTEM SET undo_retention=600
/
Check the changes.
SQL> 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.
Drop undo tablespace
Try to drop the UNDO
tablespace.
SQL> DROP TABLESPACE my_undo_tablespace
INCLUDING CONTENTS CASCADE
CONSTRAINTS
/
That's right. You can't drop an UNDO tablespace that is
currently in use.
Set the default UNDO tablespace back to UNDOTBS.
SQL> ALTER SYSTEM SET
undo_tablespace=undotbs
/
Attempt to drop the UNDO tablespace again.
SQL> DROP TABLESPACE my_undo_tablespace
INCLUDING CONTENTS CASCADE
CONSTRAINTS
/
No problem this time.
|
"There are only
two ways to live your life. One is as though nothing is a
miracle. The other is as though everything is a miracle." -
Albert Einstein (1879-1955) |
Questions:
Q: How do you create an
UNDO tablespace?
Q: How do you configure an
UNDO tablespace?
Q: How do you view the UNDO
MANAGEMENT parameter?
Q: How do you set the UNDO
MANAGEMENT parameter?
Q: Describe the
DBA_ROLLBACK_SEGS view?
Q: Create an UNDO
tablespace using OMF.
Q: Create an UNDO
tablespace using UMF.
Q: How do you set an UNDO
segment status from OFFLINE to ONLINE?
Q: How do you deactivate an
UNDO tablespace?
Q: How do you drop an UNDO
tablespace?
Q: How do you set an UNDO
retention time?
Q: What do the following
SQL statements do?
SQL> ALTER SYSTEM SET
db_create_file_dest='c:'
/
SQL> CREATE UNDO TABLESPACE my_undo_tablespace
DATAFILE SIZE 100K
/
SQL> SELECT segment_name, tablespace_name, status
FROM dba_rollback_segs
WHERE tablespace_name = 'MY_UNDO_TABLESPACE'
/
SQL> ALTER TABLESPACE my_undo_tablespace ONLINE
/
SQL> DROP TABLESPACE my_undo_tablespace
INCLUDING CONTENTS CASCADE
CONSTRAINTS
/
|
