いろいろと話題になっている [中堅企業向け オラクル都市伝説－ シーズン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
GO
DBCC REBUILD_LOG(‘Northwind’,’C:Program Files (x86)Microsoft SQL ServerMSSQL$SQL2000DataNorthwind.LDF’)
GO
ALTER DATABASE [Northwind] SET MULTI_USER
GO
sp_configure ‘allow updates’,0
GO
RECONFIGURE WITH OVERRIDE
GO
|
|
設定オプション ‘allow updates’ が 0 から 1 に変更されました。RECONFIGURE ステートメントを実行して、インストールしてください。
(1 行処理されました)
警告 : データベース ‘Northwind’ のログが再構築されました。トランザクションの一貫性は失われます。DBCC CHECKDB を実行して物理的な一貫性を調べる必要があります。データベース オプションを再設定し、余分なログ ファイルを削除する必要があります。
DBCC の実行が完了しました。DBCC がエラー メッセージを出力した場合は、システム管理者に相談してください。
設定オプション ‘allow updates’ が 1 から 0 に変更されました。RECONFIGURE ステートメントを実行して、インストールしてください。
|
[SQL Server 2005 以降]
SQL Server 2005 以降では ALTER DATABASE で EMERGENCY モードに設定することができます。
また、2005 以降では DBCC CHECKDB を REPAIR_ALLOW_DATA_LOSS で実行することとで
トランザクションログの再作成ができます。
# REBUILD_LOG は使えなくなっています。
|
AL
TER DATABASE [Northwind] SET EMERGENCY
GO
ALTER DATABASE [Northwind] SET SINGLE_USER
GO
DBCC CHECKDB (‘Northwind’, REPAIR_ALLOW_DATA_LOSS)
GO
ALTER DATABASE [Northwind] SET MULTI_USER
GO
|
?
|
ファイル アクティブ化エラー。物理ファイル名 "C:Program FilesMicrosoft SQL ServerMSSQL10.SQL2008MSSQLDATAnorthwnd.ldf" が正しくない可能性があります。
データベースのシャットダウン時に開いているトランザクション/ユーザーがあったか、データベースにチェックポイントが発生していないか、またはデータベースが読み取り専用のため、ログを再構築できません。このエラーは、トランザクション ログ ファイルが手動で削除されたか、ハードウェアまたは環境の障害により失われた場合に発生する可能性があります。
警告: データベース ‘Northwind’ のログが再構築されました。トランザクションの一貫性は失われました。RESTORE チェーンが壊れ、サーバーが以前のログ ファイルのコンテキストを保持しなくなったので、以前のログ ファイルについて把握しておく必要があります。DBCC CHECKDB を実行して物理的な一貫性を検証してください。データベースは dbo 専用モードに設定されました。データベースが使用可能な状態になったら、データベース オプションを再設定し、余分なログ ファイルを削除してください。
‘Northwind’ の DBCC 結果。
Service Broker メッセージ 9675、状態 1: 分析されるメッセージ型: 14。
Service Broker メッセージ 9676、状態 1: 分析されるサービス コントラクト: 6。
Service Broker メッセージ 9667、状態 1: 分析されるサービス: 3。
Service Broker メッセージ 9668、状態 1: 分析されるサービス キュー: 3。
Service Broker メッセージ 9669、状態 1: 分析されたメッセージ交換のエンドポイント: 0。
Service Broker メッセージ 9674、状態 1: 分析されたメッセージ交換グループ: 0。
Service Broker メッセージ 9670、状態 1: 分析されるリモート サービス バインド: 0。
Service Broker メッセージ 9605、状態 1: 分析されたメッセージ交換の優先度: 0。
‘sys.sysrscols’ の DBCC 結果。
オブジェクト "sys.sysrscols" の 10 ページには 785 行あります。
～ 省略 ～
‘Suppliers’ の DBCC 結果。
オブジェクト "Suppliers" の 1 ページには 29 行あります。
CHECKDB により、データベース ‘Northwind’ に 0 個のアロケーション エラーと 0 個の一貫性エラーが見つかりました。
DBCC の実行が完了しました。DBCC がエラー メッセージを出力した場合は、システム管理者に相談してください。
メッセージ 824、レベル 24、状態 2、行 1
SQL Server で、一貫性に基づいた論理 I/O エラーが検出されました: 無効な保護オプション。このエラーは、ファイル ‘C:Program FilesMicrosoft SQL ServerMSSQL10.SQL2008MSSQLDATAnorthwnd.ldf’ のオフセット 0000000000000000 にあるデータベース ID が 7 のページ (2:0) の 読み取り 中に発生しました。SQL Server エラー ログまたはシステム イベント ログ内の別のメッセージで詳細情報が報告されることもあります。このエラー状態は深刻で、データベースの整合性を損なう可能性があるので、すぐに解決する必要があります。完全なデータベース一貫性確認 (DBCC CHECKDB) を実行してください。このエラーには多くの要因があります。詳細については、SQL Server オンライン ブックを参照してください。
|
このような形でログファイルが破損していても、データファイルのデータは復旧することが可能です。
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 でログを再構築した場合はログチェーンも切れてしまうと思いますので、トランザクションログの
? バックアップ運用をしていても直前までは復元できないと思います。
よい機会だったので、復元のメモ書きとして投稿しておきます。
