オラクル専用ページへもどる
■
■■■■ ORA-600のコールスタックで、ファイルまたはメモリに不正合が発生していると思われるときの対処方
■
■問題の発生した表と索引のDDL文を取得する
・まずDDL文を以下のSQLで取得する。（table名、index名、owner名は都度状況に合わせる）
spool ddl.txt
select table_name,index_name from dba_indexes
where index_name='PK_EMP' and TABLE_OWNER='SCOTT';
set pages 0
set long 2000000000
select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual;
select dbms_metadata.get_ddl('INDEX','PK_EMP','SCOTT') from dual;
spool off
★カレントディレクトリに出力されたログを取る。
■次に問題が発生していたテーブルのブロックに関する情報取得
・まずトレースファイルより、問題の発生していたテーブルのFILE_IDとブロックIDを確認します。
●参考：エクステントを構成するFILE_IDとBLOCK_IDを確認する方法
select file_id, block_id, blocks
from dba_extents
where owner='SCOTT' and segment_name='EMP';
FILE_ID BLOCK_ID BLOCKS
---------- ---------- ----------
1 11193 4
…
トレースではfile#=1, block#=11193で問題が発生していたことを確認したとする。
●以下のSQLでIDを指定しログを出力する。
sqlplus "/ as sysdba"
set pages 10000
spool extent.txt
SELECT * FROM dba_extents
WHERE FILE_ID = 1
AND 11193 BETWEEN BLOCK_ID AND (BLOCK_ID + BLOCKS - 1);
spool off
alter system dump datafile 1 block 11193;
exit
★カレントディレクトリに出力されたextent.txtと
user_dump_destに出力されたブロックダンプのトレースファイルを取る。
・次に、問題のファイル（file#=1）の表領域情報およびその表領域に他のデータファイルも属しているかを把握するため
●以下のSQLを実行しログを取得。
sqlplus "/ as sysdba"
set pages 10000
spool datafile.txt
select * from dba_data_files;
spool off
exit
カレントディレクトリに出力されたdatafile.txtを確認する。
・次に問題の発生した時刻の前後を含むアーカイブログファイルからREDOダンプを取得する。
●ファイルが存在している場合に、下記の手順でREDOダンプを取得。（file#, block#を指定）
sqlplus "/ as sysdba"
ALTER SYSTEM DUMP LOGFILE '<アーカイブログファイルのfull path名>'
DBA MIN 1 . 11193
DBA MAX 1 . 11193;
user_dump_destに出力されたトレースファイルを取る。
ココまで
■■■■
■一時表領域のデータファイル削除 検証
・データファイルを追加する
alter tablespace temp add tempfile 'd:\oracle\ora92\orcl\TEMP02.DBF' size 1M;
行1でエラーが発生しました。:
ORA-03214: 指定したファイル・サイズが必要最小値を下回っています。
alter tablespace temp add tempfile 'd:\oracle\ora92\orcl\TEMP02.DBF' size 4M;
1.該当の一時表領域の確認
select tablespace_name,file_name, status from dba_temp_files;
TABLESPACE_NAME FILE_NAME STATUS
------------------- -----------------------------------------------------------
TEMP D:\ORACLE\ORA92\orcl\TEMP01.DBF AVAILABLE
TEMP D:\ORACLE\ORA92\orcl\TEMP02.DBF AVAILABLE
2.追加したデータファイルを削除
下記コマンドにて削除します。
alter database tempfile '{FILE_PATH/FILE_NAME}' drop including datafiles;
例：
alter database tempfile 'D:\ORACLE\ORA92\orcl\TEMP02.DBF' drop including datafiles;
データベースが変更されました。
削除後、dba_temp_files からエントリーが消えている事を確認してください。
select tablespace_name,file_name, status from dba_temp_files;
TABLESPACE_NAME FILE_NAME STATUS
---------------- -------------------------------- ---------------------------------
TEMP D:\ORACLE\ORA92\orcl\TEMP01.DBF AVAILABLE
■■■■
■通常のデータファイル削除 検証
・追加する
alter tablespace sample add datafile 'd:\oracle\ora92\orcl\SAMPLE02.DBF' size 1M;
表領域が変更されました。
・該当の表領域の確認
select tablespace_name,file_name, status from dba_data_files where tablespace_name='SAMPLE';
TABLESPACE_NAME FILE_NAME STATUS
---------------- -------------------------------- ---------------------------------
SAMPLE D:\ORACLE\ORA92\orcl\SAMPLE01.DBF AVAILABLE
SAMPLE D:\ORACLE\ORA92\orcl\SAMPLE02.DBF AVAILABLE
・追加したデータファイルを削除
alter database datafile 'D:\ORACLE\ORA92\orcl\SAMPLE02.DBF' drop including datafiles;
行1でエラーが発生しました。:
ORA-01916: ONLINE、OFFLINE、RESIZE、AUTOEXTENDまたはEND/DROPのキーワードが入りま
す。
alter database datafile 'D:\ORACLE\ORA92\orcl\SAMPLE02.DBF' offline drop including datafiles;
※
行1でエラーが発生しました。:
ORA-00933: SQLコマンドが正しく終了されていません。
●失敗例
alter database datafile 'D:\ORACLE\ORA92\orcl\SAMPLE02.DBF' offline drop;
SQL> select name,status from v$datafile
where name like '%SAMPLE%';
NAME STATUS
------------------------------------------------ -------
D:\ORACLE\ORA92\orcl\SAMPLE01.DBF ONLINE
D:\ORACLE\ORA92\orcl\SAMPLE02.DBF RECOVER
削除したはずのデータファイルが存在し、STATUS が'RECOVER'となっています。
●[回答]
データファイルのDROPは正常に実行されています。
しかし、ディクショナリからデータファイルの情報が削除されるのは表領域を削除するタ
イミングです。（これは offline drop したデータファイルが当該表領域に属する唯一の
データファイルの場合も同様です。）
したがって対応としては表領域のDROPを行ってください。表領域が複数のデータファイル
で構成されており、ONLINEのデータファイルに残っているデータは救い出したい場合の方
法を以下にご紹介します。
[対応手順]
前提)
SQL> select file#, ts#, name, status from v$datafile
2 where name like '%SAMPLE%';
FILE# TS# NAME STATUS
----- ----- ------------------------------------------ -------
7 7 D:\ORACLE\ORA92\orcl\SAMPLE01.DBF ONLINE --->(a)
8 7 D:\ORACLE\ORA92\orcl\SAMPLE02.DBF RECOVER --->(b)
(a)と(b)は同じ表領域に属するデータファイルであるということを前提としています。
1) 作業の開始前に全てのデータファイルのバックアップを取得されることを推奨します。
2) その表領域に存在しているオブジェクトをEXPORTしてください。
このとき、OFFLINE DROPしたデータファイルに存在するEXTENTにアクセスすると
ORA-376, ORA-1110 が発生します。以下のSQLでSTATUSが'RECOVER'のデータファイル
にEXTENTが存在するオブジェクトを確認して下さい。
SQL> select owner, segment_name from dba_extents
2 where file_id={DROPしたファイルのFILE#};
●例
select owner, segment_name from dba_extents where file_id=8;
レコードが選択されませんでした。
3) 対象の表領域をDROPします。
SQL> drop tablespace {表領域名} including contents;
●例
drop tablespace SAMPLE including contents;
仮にオブジェクトが存在するのがSTATUSが'RECOVER'のデータファイルのみだったと
しても、INCLUDING CONTENTS句をつけないと ORA-1549 が発生して表領域のDROPに失
敗します。
4) 3)で実行したdrop tablespaceコマンドでは，OS上の物理ファイルは削除しませんの
でOSコマンドによりこれを削除します。
delete D:\ORACLE\ORA92\orcl\SAMPLE01.DBF ← OK！
delete D:\ORACLE\ORA92\orcl\SAMPLE02.DBF ← NG！
5) 表領域を再作成します。
例）
SQL> create tablespace SAMPLE
2 datafile 'D:\ORACLE\ORA92\orcl\SAMPLE01.DBF' size 1M;
表領域が作成されました。
●例
create tablespace SAMPLE datafile 'D:\ORACLE\ORA92\orcl\SAMPLE01.DBF' size 10M;
6) 2)にてEXPORTしたオブジェクトをIMPORTします。
7) DROPされたデータファイルのエントリが削除されていることが以下のSQL文を実行す
ることで確認可能です。
例)
SQL> select name, status, enabled from v$datafile
> where name like '%SAMPLE%';
NAME STATUS ENABLED
------------------------------------------------------- ------- ----------
D:\ORACLE\ORA92\orcl\SAMPLE01.DBF ONLINE READ WRITE
■正解
概要 複数のDATAFILEで構成されているTABLESPACEの内，一部のDATAFILEを削除する方法
[質問]
複数のDATAFILEで構成されているTABLESPACEの内，一部のDATAFILEを削除する方法
[回答]
表領域が複数のDATAFILEで構成されている場合，特定のDATAFILEのみを
offlineやdropしてはいけません．offlineした場合には，onlineに
する事もできなくなってしまいます．★失敗例参照
また，dropされたDATAFILEに他のDATAFILEにもあるTABLEのextentが存在していた場合，
その部分を読みに行くとエラーとなってしまいます．
したがって，表領域の再作成が必要となります．★
１．select owner, segment_name from dba_segments where tablespace_name
='表領域名' and segment_type='TABLE';
を実行して表領域に存在しているtableをすべてリストアップします．
２．すべてのtableをEXPORTします．
３．alter tablespace 表領域名 offline;
４．drop tablespace 表領域名 including contents;
５．drop tablespaceコマンドでは，OS上の物理ファイルは削除しないのでこれを削除します．
６．表領域を作成します．DATAFILEは必要に応じて作成してください．
７．tableをIMPORTします．
オラクル専用ページへもどる