いろいろと話題になっている [中堅企業向け オラクル都市伝説- シーズン2] ですが、記事の中に以下の記載があります。
ログファイルが壊れるとデータの復旧ができない。 |
?
ログファイルが壊れ、データの復旧ができなくなったため、バックアップまで戻したとあります。
トランザクションの整合性が保たれた状態に戻すということを考えるとこの対応は正しいと思いますが、
障害発生時では、それまでのデータを何とかして確認する方法がないかということも重要だと思います。
SQL Server はトランザクションログファイルが壊れてもデータファイルが生きていれば状況によってはある程度は復旧できます。
といことで実験してみました。
[トランザクションログを壊す]
まずは、トランザクションログを壊さないと話が始まりません。
とりあえず、バイナリエディタを使用してファイルの内容を 0 クリアしてみました。
データベースは SQL Server 2000 の Northwind を使用しています。
?
[データベースは障害状態になる?]
この状態のデータベースを SQL Server 2000 で開くとどうなるか確認してみました。
データベースが開けちゃうんですね・・・。
DBCC で確認してもエラーにはなりませんでした。
[トランザクションログのファイル自体を削除する]
続いては ldf を削除してデータファイルだけの状態にしてみました。
これもまた起動してきちゃうんですね。
SQL Server が起動時に新しいトランザクションログを作っていました。
[トランザクションログファイルを壊れた状態にするには?]
壊さないことには検証が始まらなさそうですので、ひとまず復旧間隔を 20 分に設定し、10,000 件のデータを挿入して、
サーバーを強制シャットダウンして、トランザクションログを FF でクリアしてみました。
どうやらトランザクションログを壊すことに成功したようです。
# SQL Server 2000 / 2008 でこの方法でログを壊すことができました。
?
さて、これで都市伝説と同じ状態に持っていくことができました。
ここから、データを復旧したいと思います。
[ログの再構築でデータを復旧]
SQL Server 2000 と 2005 以降ではログの復旧方法が異なります。
[SQL Server 2000]
SQL Server 2000 で復旧する場合は、システムテーブルを直接更新して、EMERGENCY モードに設定する必要があります。
SQL Server 2000 の DBCC にはログを再構築するコマンドがあります。
REBUILD_LOG コマンドで新しいログファイルが作成できます。このコマンドは Undocumented な DBCC なんですよね。
2000 では REPAIR_ALLOW_DATA_LOSS でログファイルを再構築することができないので、REBUILD_LOG コマンドを
使用してログファイルの再構築をする必要があります。
sp_configure ‘allow updates’,1 GO RECONFIGURE WITH OVERRIDE GO UPDATE sysdatabases SET status = 32768 WHERE name = N’Northwind’ GO ALTER DATABASE [Northwind] SET SINGLE_USER |
設定オプション ‘allow updates’ が 0 から 1 に変更されました。RECONFIGURE ステートメントを実行して、インストールしてください。 (1 行処理されました) |
[SQL Server 2005 以降]
SQL Server 2005 以降では ALTER DATABASE で EMERGENCY モードに設定することができます。
また、2005 以降では DBCC CHECKDB を REPAIR_ALLOW_DATA_LOSS で実行することとで
トランザクションログの再作成ができます。
# REBUILD_LOG は使えなくなっています。
AL |
?
ファイル アクティブ化エラー。物理ファイル名 "C:Program FilesMicrosoft SQL ServerMSSQL10.SQL2008MSSQLDATAnorthwnd.ldf" が正しくない可能性があります。 ~ 省略 ~ ‘Suppliers’ の DBCC 結果。 |
このような形でログファイルが破損していても、データファイルのデータは復旧することが可能です。
SQL Server 2000 / 2005 / 2008 ではログの再構築により、トランザクションログが破損していても
データファイル上のデータを復旧することができます。
ただし、ダーティーページに関してはデータファイル上に書き込みがされていませんので、このようなデータは
トランザクションログだけに書き込まれており、データファイル上には存在しません。
この辺はチェックポイントの発生タイミングに依存しますので、復旧間隔の設定によってどの程度の
データがデータファイルに書き込まれているかが変わっていきます。
[データファイルだけをアタッチ]
SQL Server 2000 以降では sp_attach_singile_file_db というデータファイルだけをアタッチする
ストアドプロシージャがあります。
ログファイルが破損した状態のデータファイルだけをアタッチしようとしたところこの方法は
うまくいきませんでした。
CREATE DATABASE でアタッチする方法も同様でエラーとなってしまいました。
SQL Server 2005 以降では復旧中になっているデータベースはデタッチできないようになっているので、
間違ってエントリをはずすことはないかとは思いますが。
SQL Server 2000 では sp_attach_single_file_db による、単一のデータファイルによるアタッチしか、
データファイルをアタッチする方法がありませんが、状況によってはデータファイルのアタッチによる
復旧ができる可能性もあるかと思います。
# 今回、私は失敗しましたが・・・。
ログファイルが二重化できないのである程度の復旧となってしまいますので、トランザクションログの構成については
Oracle に優位性があるのは確かだと思います。
ただし、SQL Server ではバックアップ取得時点でなくても途中までは復元できる可能性は残っています。
# DBCC でログを再構築した場合はログチェーンも切れてしまうと思いますので、トランザクションログの
? バックアップ運用をしていても直前までは復元できないと思います。
よい機会だったので、復元のメモ書きとして投稿しておきます。
私も同様な方法(ログ削除)などでトラブル対応を行った実績があります。最近良く感じるのが、トラブル時のデータ復元についてどの時点まで戻す必要があるか検討していないことが多いということです。前日のバックアップまで戻し、処理を再実行すれば問題なしといったパターンが多い割に、データベースタイプを「完全」にしている、その上しっかり管理出来ずログがあふれる。こういったところが多いのが実際だと思います。いろいろ検討する必要はあると思いますが、管理出来ないようならタイプを「単純」にするという選択肢もありかと思います。※そのあたりがわかってればしっかりログコンデンスするのでしょうけど….
正樹
8 9月 09 at 01:14
>最近良く感じるのが、トラブル時のデータ復元について>どの時点まで戻す必要があるか検討していないことが多いということです。>前日のバックアップまで戻し、処理を再実行すれば問題なしといったパターンが多い割に、>データベースタイプを「完全」にしている、その上しっかり管理出来ずログがあふれる。>こういったところが多いのが実際だと思います。私も実際の現場では、どの時点まで戻せばよいか検討されていないことは意外と多いのではと思っています。障害発生直前まで時間指定で戻す必要がないのであれば「単純」にしての運用が一番運用コストがかからずに楽ですよね。データベースを扱う立場からすると週次で完全バックアップ、日次で差分バックアップ、日の中で時 / 分単位でトランザクションログのバックアップがデフォルトではありますが。復旧モデルを「完全」にしている場合は、Tail-log Backup や、point-in-time recovery の手順も考える必要がありますので、運用方法を検討するにも単純な運用ではなくデータベース寄りのスキルが必須になり、現場の運用担当だけでバックアップ計画を立案するのは敷居がすこし高いと思います。実際は、障害が発生したら有識者を連れてきて対応するというパターンが多いのでしょうね。サーバー運用をされている方が自分で対応できるのがベストだとは思いますが難しいのが現実かと。トランザクションログの肥大化とインデックスの断片化は現場で多い事例ですよね。SQL Server のバックアップは一度情報を整理したいなと思って、やることリストにはタスクとして登録してはいるのですが手つかずです…。
真之
8 9月 09 at 22:49