ITコーディネータのシュウです。

ときおり散歩する公園で、かもがたくさん群れをなして歩いているのを見ました。珍しいと思って急いで撮ってみた写真です。こんなにたくさん歩いているとはびっくりですね。何かあったのかな～！

＜本日の題材＞
ORA-28001:パスワードが期限切れです。

今回は、昨日までOracleを使って問題なかったのに、ログインしようとしたら急に「ORA-28001:パスワードが期限切れです。」というエラーが出てログインできなくなるという事態に遭遇したことがあったので、それについて情報としてアップします。

これは、今までOracle10gを使っていたが、Oracle11gにバージョンアップした場合などによく起きることがあります。理由としては、Oracle10gまではユーザのパスワードの有効期限はデフォルトで無期限であったものが、Oracle11gからはデフォルトで180日に設定されているため、180日たったときに起きてしまうというものです。

さすがにOracle11gがリリースされてかなりの期間が立ちますので、既に知っておられる方が多いかとは思いますが、意外とこのエラーに出くわした方が多いのではないでしょうか。

セキュリティ上はたしかにパスワードを定期的に変更したほうがよいのでしょうが、開発時やテストなどでは無期限にしてアクセスしたい場合も多いと思います。

Oracle11gの場合、「プロファイル」という、ユーザーに対するシステムリソースやパスワードの制限の設定をまとめたものをユーザに割り当てることで、一括してパスワードの制御などを行うことができるようになっており、パスワードの有効期間もそこで設定されます。
ですので、デフォルトのプロファイルが設定されているユーザであれば、このときの対処法の例としては、

１）デフォルトのプロファイルのパスワード有効期限を無期限にする
２）エラーでログインできないユーザのパスワードを再設定する
３）ロックされているアカウントのロックを解除する

というかたちで対応はできることになります。

例として、SQL Plusで行う場合を以下に記載します。
DOSプロンプトからSQL Plusを起動。

sqlplus sys/[パスワード]@接続文字列 as sysdba

・デフォルトのプロファイルのパスワード有効期限を無期限にします。

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

・エラーでログインできないユーザのパスワードを再設定します。

ALTER USER ユーザ名 IDENTIFIED BY 新パスワード;

 ・ロックされているアカウントのロックを解除します。

 以上で、再度 scott でログインできるようになります。

今日は以上まで

 

にほんブログ村

 ITコーディネータのシュウです。

きれいだった桜も散ってしまいました。なかなか忙しくて、ゆっくり花見をすることができませんでしたが、ほんの1週間ほどではありますが、力いっぱい咲いて私たちを感動させ、喜ばせてくれる自然とは、本当にすごいと、改めて感じるこの季節ですね。
ちなみに、桜の中で日本で圧倒的に多いのがソメイヨシノ（染井吉野）ですが、もとは江戸の染井村の造園師や植木職人によって育成されたものが、各地に接木されて増やされたもので、すべてのソメイヨシノは元をたどればかなり限られた数の原木につながり、それらのクローンだということです。（会社の同僚から教えてもらいました）

＜本日の題材＞
自律型トランザクション

今回は、Oracleにおける自律型トランザクション処理について、見てみます。

これは、ある一連のトランザクション処理を行う中で、それとは独立したトランザクションを宣言することができる機能で、メインのトランザクション処理はロールバックされても、そこだけCOMMITさせることが可能になります。
私も、あるデータ変換を行うメインのトランザクション処理を実行する中で、何らかのエラーが起きた場合にそのエラーがどういうものなのかをエラー用のログテーブルに書き出すという機能で使用したことがあります。当然、メインのトランザクションはエラーが発生したのでロールバックして何も更新されない状態にしますが、エラーの内容はログに書き出したいので、メインのトランザクションとは独立したトランザクションとして、例外処理の中でログに登録するというものです。
（ログの登録をメインのトランザクションの中に入れてしまうと、エラー時にトランザクションをロールバックすれば、ログへの登録もロールバックされてしまうため）
ほとんどオラクル社の提示していたサンプルをそのまま利用したかたちではありましたが。。。

例）
①まず、エラーログ用のテーブルを作成

CREATE TABLE ERROR_LOG (
   ERROR_CODE NUMBER
  , ERROR_MESSAGE VARCHAR2(4000)
  , BACKTRACE CLOB
  , CALLSTACK CLOB
  , CREATED_ON DATE
  , CREATED_BY VARCHAR2(30)
);

②エラー記録用のプロシージャの作成

ここで、自律型トランザクションの宣言を、PRAGMA AUTONOMOUS_TRANSACTION;で行います。

CREATE OR REPLACE PROCEDURE record_error
IS
   PRAGMA AUTONOMOUS_TRANSACTION;     -- 自律型トランザクションの宣言
   l_code  PLS_INTEGER := SQLCODE;
   l_mesg  VARCHAR2(32767) := SQLERRM;
BEGIN
   INSERT INTO error_log (error_code
                       ,  error_message
                       ,  backtrace
                       ,  callstack
                       ,  created_on
                       ,  created_by)
        VALUES (l_code
              , l_mesg
              , sys.DBMS_UTILITY.format_error_backtrace
              , sys.DBMS_UTILITY.format_call_stack
              , SYSDATE
              , USER);
 
   COMMIT;
END;
/

※DBMS_UTILITY.format_error_backtrace ファンクションはエラーが発生した行を取得し、DBMS_UTILITY.format_call_stack ファンクションは、エラー発生箇所までの実行コールスタック、どのように処理が呼び出されてきたかを示します。

③上記のエラー記録用のプロシージャを、メインのプロシージャの例外処理で呼び出します。今回は簡単な例として、部門表(DEPT)にデータを登録するという処理を作成し、エラーが発生したら処理は全てロールバックしますが、エラーログにエラーの記録を残すようにします。

BEGIN
    INSERT INTO DEPT VALUES (50, 'PLANNING', 'LOS ANGELES');
    INSERT INTO DEPT VALUES (60, 'DEVELOPMENT', 'SEATTLE');
    INSERT INTO DEPT VALUES (50, 'GENERAL AFFAIRS', 'SAN FRANCISCO');
 
COMMIT;

--例外処理
EXCEPTION
   WHEN others THEN
     record_error();
     RAISE;
END;
/

上記は、DEPT表へのデータの登録を行って、最後にCOMMIT;を実行する処理ですが、途中でDEPTNOのキー重複によりエラーが発生します。そのエラーの内容を「error_log」テーブルには記録を残すというものになります。

処理を実行すると、

DEPT表の中身は、
SELECT * FROM DEPT;

処理はロールバックされています。

ERROR_LOG テーブルの中身は、
SELECT * FROM error_log;

 エラーの内容がログとして記録されています。

また、SQL Serverにおいては、自律型トランザクションの機能はサポートしていないようです。同様な処理を行いたい場合には、TRY ～CATCHのCATCHブロックの中で、新たにトランザクションを開始してログの登録を行うなどの処理が必要です。

上記と同様の処理を行うとすると、

BEGIN TRY
  BEGIN TRAN
      INSERT INTO DEPT VALUES (50, 'PLANNING', 'LOS ANGELES');
      INSERT INTO DEPT VALUES (60, 'DEVELOPMENT', 'SEATTLE');
      INSERT INTO DEPT VALUES (50, 'GENERAL AFFAIRS', 'SAN FRANCISCO');
      COMMIT
END TRY

BEGIN CATCH
  IF @@TRANCOUNT > 0
      ROLLBACK 

  BEGIN TRAN
         SELECT ERROR_NUMBER()
                          , ERROR_SEVERITY() AS エラー重大度
                          , ERROR_MESSAGE() AS エラーメッセージ
                          , ERROR_LINE()

         INSERT INTO error_log(
                ERROR_NUMBER
              , ERROR_SEVERITY
              , ERROR_MESSAGE
              , ERROR_LINE
              )
         SELECT
                ERROR_NUMBER()
              , ERROR_SEVERITY() AS エラー重大度
              , ERROR_MESSAGE() AS エラーメッセージ
              , ERROR_LINE()
   COMMIT;
END CATCH

DEPT表の中身は、
SELECT * FROM dbo.DEPT;

ROLLBACKされて、レコードは増えていません。

ERROR_LOGテーブルの中身は、

SELECT * FROM dbo.ERROR_LOG;

エラー情報が記録されているのが確認できます。

今日は以上まで

にほんブログ村