Oracle 自動 UNDO 管理を使用している UNDO 表領域の変更、再作成方法

 

 

適用範囲:

Oracle Database - Enterprise Edition - バージョン 9.0.1.0 以降

この文書の内容はすべてのプラットフォームに適用されます。

目的

本文書では、自動 UNDO 管理を使用している環境で、新しい UNDO 表領域を作成

し、現行の UNDO 表領域から変更する方法について説明します。

本文書で紹介している手順は、UNDO 表領域の縮小にも使用可能です。UNDO 表領

域が大きくなってしまった場合などに、本文書で紹介している手順で新しく小さ

な UNDO 表領域を作成することで、大きくなった UNDO 表領域を削除することが

できます。

解決策

[詳細]

UNDO 表領域を変更する手順は以下の通りです。

UNDO 表領域を現行の UNDO 表領域名と同じ名前で再作成を行いたい場合は、以

下の手順を 2 回実行します。その際、1 回目は仮の UNDO 表領域名、2 回目は

現行の UNDO 表領域名を指定してください。

 

なお、CREATE 文、ALTER 文などの詳細なオプション等については、ご使用のリ

リースの SQL リファレンスをご参照ください。

 

UNDO 表領域の変更手順

========================================================================

0. 現行の UNDO 表領域の定義を確認したい場合は、以下の SQL で確認します。

 

     * この手順は必須ではありません。必要な場合のみ実施してください。

       また、リリース 9.0.1 では、DBMS_METADATA を使用して、表領域の定義

       を取得することはできません。DBA_DATA_FILES, DBA_TABLESPACES から、

       現在の設定をご確認ください。

 

  SQL> set long 3000

  SQL> select dbms_metadata.get_ddl('TABLESPACE','<現行UNDO表領域名>')

       from dual;

 

  (実行例)

  ----------------------------------------------------------------------

  SQL> set long 3000

  SQL> select dbms_metadata.get_ddl('TABLESPACE','UNDOTBS1') from dual;

 

  DBMS_METADATA.GET_DDL('TABLESPACE','UNDOTBS1')

  ------------------------------------------------------------------

    CREATE UNDO TABLESPACE "UNDOTBS1" DATAFILE

    '/app/oracle/oradata/ora102/undotbs01.dbf' SIZE 26214400

    AUTOEXTEND ON NEXT 5242880 MAXSIZE 32767M

    BLOCKSIZE 8192

    EXTENT MANAGEMENT LOCAL AUTOALLOCATE

     ALTER DATABASE DATAFILE

    '/app/oracle/oradata/ora10204/undotbs01.dbf' RESIZE 2039480320

  ----------------------------------------------------------------------

 

 

1. UNDO 表領域を新たに作成し、ONLINE にします。

 

  SQL> create undo tablespace <新規UNDO表領域名>

       datafile '<データファイル・パス>' size <データファイル・サイズ>;

  SQL> alter tablespace <新規UNDO表領域名> online;

 

  (実行例)

  ----------------------------------------------------------------------

  SQL> create undo tablespace undotbs2 datafile

    2  '/app/oracle/oradata/ora102/undotbs02.dbf' size 100m;

 

  Tablespace created.

 

  SQL> alter tablespace undotbs2 online;

 

  Tablespace altered.

  ----------------------------------------------------------------------

 

 

2. 新規の UNDO 表領域が作成され、STATUS が AVAILABLE になっていることを

   確認します。

 

  SQL> set lines 200

  SQL> set pages 200

  SQL> col file_name for a100

  SQL> select tablespace_name, status, file_name from dba_data_files;

 

  (実行例)

  ----------------------------------------------------------------------

  SQL> select tablespace_name, status, file_name from dba_data_files;

 

  TABLESPACE_NAME STATUS    FILE_NAME

  --------------- --------- --------------------------------------------

  USERS           AVAILABLE /app/oracle/oradata/ora102/users01.dbf

  SYSAUX          AVAILABLE /app/oracle/oradata/ora102/sysaux01.dbf

  SYSTEM          AVAILABLE /app/oracle/oradata/ora102/system01.dbf

  EXAMPLE         AVAILABLE /app/oracle/oradata/ora102/example01.dbf

  UNDOTBS1        AVAILABLE /app/oracle/oradata/ora102/undotbs01.dbf

  UNDOTBS2        AVAILABLE /app/oracle/oradata/ora102/undotbs02.dbf <--

  ----------------------------------------------------------------------

 

    * STATUS が AVAILABLE となっていても、3. の手順で UNDO 表領域の切り

      替えを行うまでは、新しく作成した UNDO 表領域は使用されません。

 

 

3. UNDO 表領域を切替えます。

 

  - サーバ・パラメータ・ファイル(SPFILE) を使用している場合

 

    SPFILE をご使用の場合は、ALTER SYSTEM 文で変更します。

 

    SQL> alter system set undo_tablespace=<新規UNDO表領域名> scope=both;

 

    (実行例)

    --------------------------------------------------------------------

    SQL> alter system set undo_tablespace='undotbs2' scope=both;

 

    System altered.

    --------------------------------------------------------------------

 

  - 初期化パラメータ・ファイル(PFILE) を使用している場合

 

    PFILE をご使用の場合は、ALTER SYSTEM 文での変更に加え、初期化パラ

    メータファイルの UNDO_TABLESPACE の設定値を変更します。

 

    SQL> alter system set undo_tablespace='<新規UNDO表領域名>';

 

    初期化パラメータファイル

 

      UNDO_TABLESPACE=<新規UNDO表領域名>

 

    (実行例)

    --------------------------------------------------------------------

    SQL> alter system set undo_tablespace='undotbs2';

 

    System altered.

 

    初期化パラメータファイル 変更前： UNDO_TABLESPACE=UNDOTBS1

                             変更後： UNDO_TABLESPACE=UNDOTBS2

    --------------------------------------------------------------------

 

  *1 各ファイルのデフォルトの位置は以下の通りです。

      UNIX    : $ORACLE_HOME/dbs

      Windows : %ORACLE_HOME%/database

 

  *2 該当環境で SPFILE が使用されているかや、ファイル位置の確認方法は、

     Document 1721854.1(KROWN:63897) を参照してください。

 

 

4. 今まで使用していた UNDO 表領域を以下の SQL で監視します。

 

  SQL> select dr.tablespace_name, dr.segment_name, vr.status

       from dba_rollback_segs dr, v$rollstat vr

       where dr.segment_id=vr.usn;

 

  STATUS が "PENDING OFFLINE" のセグメントが消えるまで監視してください。

  完全に OFFLINE となると、現行 UNDO 表領域名のレコードはなくなります。

 

  (実行例)

  ----------------------------------------------------------------------

  SQL> select dr.tablespace_name, dr.segment_name, vr.status

    2  from dba_rollback_segs dr, v$rollstat vr

    3  where dr.segment_id=vr.usn;

 

    TABLESPACE_NAME SEGMENT_NAME STATUS

    --------------- ------------ ---------------

    SYSTEM          SYSTEM       ONLINE

    UNDOTBS1        _SYSSMU1$    PENDING OFFLINE <--

    UNDOTBS1        _SYSSMU3$    PENDING OFFLINE <--

    UNDOTBS2        _SYSSMU11$   ONLINE

    UNDOTBS2        _SYSSMU12$   ONLINE

    UNDOTBS2        _SYSSMU13$   ONLINE

    UNDOTBS2        _SYSSMU14$   ONLINE

    UNDOTBS2        _SYSSMU15$   ONLINE

    UNDOTBS2        _SYSSMU16$   ONLINE

    UNDOTBS2        _SYSSMU17$   ONLINE

    UNDOTBS2        _SYSSMU18$   ONLINE

    UNDOTBS2        _SYSSMU19$   ONLINE

    UNDOTBS2        _SYSSMU20$   ONLINE

  ----------------------------------------------------------------------

 

   *1 STATUS の "PENDING OFFLINE" は、該当の UNDO 表領域が使用中のため、

      OFFLINE への切り替えを待機している状況を示します。

 

   *2 SYSTEM 表領域の UNDO セグメントはデフォルトで作成されるものです。

      この作業では無視してください。

 

   *3 リリース 11.1 以降では SEGMENT_NAME のフォーマットが _SYSSMUn から

      _SYSSMUn_nnnnnnnnnn$ に変更なっていますが、手順は同様です。

 

  もし、"PENDING OFFLINE" の STATUS が変わらない場合や、実行中の処理より

  UNDO 表領域の変更を優先する場合は、"shutdown immediate" でインスタンス

  の再起動を行ってください。

 

  [注意事項]

    1) インスタンスの停止に "shutdown abort" は使用しないで下さい。

    2) インスタンスを停止した場合も、5. の作業が完了するまで、UNDO 表領

       域のデータファイルを OS 上で削除しないでください。

 

 

5. 今まで使用していた UNDO 表領域のセグメントが完全に OFFLINE になったら

   UNDO 表領域を削除します。

 

  SQL> drop tablespace <現行UNDO表領域名> including contents and

       datafiles;

 

  (実行例)

  ----------------------------------------------------------------------

  SQL> drop tablespace undotbs1 including contents and datafiles;

 

  Tablespace dropped.

  ----------------------------------------------------------------------

 

 

6. 今まで使用していた UNDO 表領域が削除されていることを確認します。

 

  SQL> set lines 200

  SQL> set pages 200

  SQL> col file_name for a100

  SQL> select tablespace_name, status, file_name from dba_data_files;

 

  (実行例)

  ----------------------------------------------------------------------

  SQL> select tablespace_name, status, file_name from dba_data_files;

 

  TABLESPACE_NAME STATUS    FILE_NAME

  --------------- --------- --------------------------------------------

  USERS           AVAILABLE /app/oracle/oradata/ora102/users01.dbf

  SYSAUX          AVAILABLE /app/oracle/oradata/ora102/sysaux01.dbf

  SYSTEM          AVAILABLE /app/oracle/oradata/ora102/system01.dbf

  EXAMPLE         AVAILABLE /app/oracle/oradata/ora102/example01.dbf

  UNDOTBS2        AVAILABLE /app/oracle/oradata/ora102/undotbs02.dbf

 

  --> UNDOTBS1 が削除されていることが確認できます。

  ----------------------------------------------------------------------

 

========================================================================

 

 

[FAQ]

Q1. 4. の手順で "PENDING OFFLINE" が解消されないため、再起動も検討してい

    ますが、どの処理が UNDO を使用しているか不明なため、再起動を行って良

    いかの判断ができません。

    UNDO を使用している処理を特定する方法はありますか。

 

A1. 以下のような SQL で UNDO を使用しているセッションの情報を確認するこ

    とができます。

    -----------------------------------------------------------------

    select r.segment_name, t.xidusn, s.username, s.osuser, s.process,

           s.machine, s.terminal, s.program

      from dba_rollback_segs r,v$transaction t, v$session s

      where r.segment_id = t.xidusn

      and t.addr= s.taddr;

    -----------------------------------------------------------------

 

Q2. 4. の手順で "PENDING OFFLINE" が解消されないため再起動を行います。再

    起動を早く行うため "shutdown abort" で行っても良いでしょうか。

 

A2. "shutdown abort" では、起動時にトランザクションのリカバリ処理を行う

    ため、再起動後に切り替え前の UNDO が必要となります。UNDO 表領域の切

    り替えを行う場合は、UNDO を開放するため、"shutdown immediate" でイン

    スタンスを停止してください。

 

Q3. 4. の手順でインスタンスを停止しました。切り替え前の UNDO 表領域の

    データファイルを OS 上から削除しても良いでしょうか。

 

A3. データファイルの削除は、5. の "drop tablespace" コマンドで行ってくだ

    さい。データベースの内部情報と整合性を取るため、"drop tablespace" コ

    マンドを実行する前に、rm コマンドなどで OS 上からデータファイルを削

    除しないでください。

 

 

[参照情報]

マニュアル『Oracle Database SQL リファレンス 11g リリース1（11.1）』

マニュアル『Oracle Database SQL リファレンス 10g リリース2（10.2）』

マニュアル『Oracle Database SQL リファレンス 10g リリース1（10.1）』

マニュアル『Oracle9i SQL リファレンス リリース2（9.2）』

マニュアル『Oracle9i SQL リファレンス リリース1（9.0.1）』