iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

Online Oracle Training for beginners and advanced - The most comprehensive Oracle tutorial

The authors do not guarantee or take any responsibility for the accuracy, or completeness of the information.

DBAs - Fundamentals

 

Lesson 01 | Lesson 02 | Lesson 03 | Lesson 04 | Lesson 05 | Lesson 06 | Lesson 07 | Lesson 08 | Lesson 09 | Lesson 10 | Lesson 11 | Lesson 12 | Lesson 13 | Lesson 14 | Lesson 15 | Lesson 16 | Lesson 17 | Lesson 18 | Lesson 19 | Lesson 20 | Lesson 21 | Lesson 22 | Lesson 23 | Lesson 24 | Lesson 25 | Lesson 26 |

 

Lesson 16

“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

/