Oracle破損

※※　この情報は tipではございません　※※

とあるA社さんの仕事を委託していて、開発環境もA社さんにあるため必要に応じA社さんの環境へ接続、作業をしていたりするのですが、

？？Oracle起動してないよ？
？？というか、計画外シャットダウンの画面出てるよ(Windows2008server)

となり、事が深刻だったので作業備忘録メモ


前置き長くなりました。もー何で？な心境なので文体粗いです。
追伸：Oracle 10gです

【事象】

SQL DeveloperでOracleに接続しようとしたら「ORA-01033:Oracleの初期化またはシャットダウン中です」が出て接続できない

【処置概略】

・まー、今までもあったよね。startup mount;ALTER DATABASE OPEN; でつながるよね。
・おっと、mountされてるみたい。shutdown immediateしてからだね。
・え、エラー出たし。ORA- でぐぐって対処するか。
・色々コマンド出てきて打ち込むけども、なんか治らんなぁ
・余計ひどくなった。やっぱDBAでもないのにやるもんじゃないね。
・Database作り直しちゃおう。
・Configuration Assistantで再作成。っと。
・壊れたのはどうせ使えないので削除。
・ユーザー作って、権限付与して、開発用データのdmpも入れて。と。
・listener設定してなかった。作成したdbへ変更。っと。
・よしよし。データも見れるし、まぁいいか。開発環境だし。

【処置結果】

・何もtips残りませんでした。
　ひとつ大事なことは「思いたったからと言ってすぐやるな」
　OracleはさすがOracle。あちこちにリカバリログを持っていて(いるようで）、手順さえ間違わなければログから復旧できる。(はず)ログもちゃんとバックアップしておく必要あるけどね。
　適当にログ指定してリカバリしようなんて、傷を深くしただけでした。

【余談】

ファイル壊れてんじゃんか(笑) zipファイルすら破損してるよ。ディスク障害でもあったんじゃない？ま、開発環境(hyper-v)だし、壊れてもなんとかなるからいっか。

【使ったコマンドのメモ】

※sqlplus /nologでログイン
※sys as syadba

recover database until cancel;

【恥を忍んで途中からの全log】

SQL> recover database until cancel;


ORA-00279: 変更217138326(09/30/2018 12:00:48で生成)にはスレッド1が必要です
ORA-00289:検討すべきログ・ファイル:D:\ORACLE\PRODUCT\10.2.0\DB_1\RDBMS\ARC02244_0830209207.001
ORA-00280: 変更217138326(スレッド1)は順序番号2244に存在します。
ログの指定: {=suggested | filename | AUTO | CANCEL}
D:\ORADATA\ORCL\REDO03.LOG
ORA-00283: エラーによってリカバリ・セッションは取り消されました。
ORA-10562: Error occurred while applying redo to data block (file# 3, block#
21623)ORA-10564: tablespace SYSAUX
ORA-01110: データファイル3: 'D:\ORADATA\ORCL\SYSAUX01.DBF'
ORA-10560: block type '0'
ORA-00600: 内部エラー・コード、引数: [4552],[1],[0],[],[],[],[],[]
ORA-01112: メディア・リカバリが開始されていません
SQL> recover database until cancel;
ORA-00279: 変更217138326(09/30/2018 12:00:48で生成)にはスレッド1が必要です
ORA-00289:
検討すべきログ・ファイル:D:\ORACLE\PRODUCT\10.2.0\DB_1\RDBMS\ARC02244_0830209207.001
ORA-00280: 変更217138326(スレッド1)は順序番号2244に存在します。
ログの指定: {=suggested | filename | AUTO | CANCEL}
ORA-00308:
アーカイブ・ログD:\ORACLE\PRODUCT\10.2.0\DB_1\RDBMS\ARC02244_0830209207.001をオープンできません。
ORA-27041: ファイルをオープンできません。
OSD-04002: ファイルをオープンできません
O/S-Error: (OS 2) 指定されたファイルが見つかりません。
ORA-01547: 警告: RECOVERは成功しましたがOPEN
RESETLOGSが次のエラーを受け取りました。
ORA-01194: ファイル1は一貫した状態にするためにさらにリカバリが必要です。
ORA-01110: データファイル1: 'D:\ORADATA\ORCL\SYSTEM01.DBF'
SQL> recover database until cancel;
ORA-00279: 変更217138326(09/30/2018 12:00:48で生成)にはスレッド1が必要です
ORA-00289:検討すべきログ・ファイル:D:\ORACLE\PRODUCT\10.2.0\DB_1\RDBMS\ARC02244_0830209207.001
ORA-00280: 変更217138326(スレッド1)は順序番号2244に存在します。
ログの指定: {=suggested | filename | AUTO | CANCEL}
REDO2.LOG
ORA-00308: アーカイブ・ログREDO2.LOGをオープンできません。
ORA-27041: ファイルをオープンできません。
OSD-04002: ファイルをオープンできません
O/S-Error: (OS 2) 指定されたファイルが見つかりません。
ログの指定: {=suggested | filename | AUTO | CANCEL}
D:\ORADATA\ORCL\REDO02.LOG
ORA-00310:アーカイブ・ログは順序番号2243を含んでいますが、順序番号2244が必要です。
ORA-00334: アーカイブ・ログ: 'D:\ORADATA\ORCL\REDO02.LOG'
ORA-01547: 警告: RECOVERは成功しましたがOPEN
RESETLOGSが次のエラーを受け取りました。
ORA-01194: ファイル1は一貫した状態にするためにさらにリカバリが必要です。
ORA-01110: データファイル1: 'D:\ORADATA\ORCL\SYSTEM01.DBF'
SQL> recover database automatic
ORA-00905: キーワードがありません。
SQL> recover automatic database;
ORA-00283: エラーによってリカバリ・セッションは取り消されました。
ORA-10562: Error occurred while applying redo to data block (file# 3, block#
21623)
ORA-10564: tablespace SYSAUX
ORA-01110: データファイル3: 'D:\ORADATA\ORCL\SYSAUX01.DBF'
ORA-10560: block type '0'
ORA-00600: 内部エラー・コード、引数: [4552],[1],[0],[],[],[],[],[]
SQL> recover tablespace user;
ORA-00931: 識別子がありません。
SQL> recover tablespace users;
ORA-00283: エラーによってリカバリ・セッションは取り消されました。
ORA-00368: REDOログ・ブロックでチェックサム・エラーが発生しました
ORA-00353: ブロック85960(変更217167197、時間10/01/2018
01:20:14)付近のログが破損しています
ORA-00312: オンライン・ログ3 スレッド1: 'D:\ORADATA\ORCL\REDO03.LOG'
SQL> select * from V$RECOVER_FILE;
レコードが選択されませんでした。
SQL>
SQL> recover database until cancel;
ORA-00279: 変更217138326(09/30/2018 12:00:48で生成)にはスレッド1が必要です
ORA-00289:
検討すべきログ・ファイル:D:\ORACLE\PRODUCT\10.2.0\DB_1\RDBMS\ARC02244_0830209207.001
ORA-00280: 変更217138326(スレッド1)は順序番号2244に存在します。
ログの指定: {=suggested | filename | AUTO | CANCEL}
ORA-00308:
アーカイブ・ログD:\ORACLE\PRODUCT\10.2.0\DB_1\RDBMS\ARC02244_0830209207.001をオープンできません。
ORA-27041: ファイルをオープンできません。
OSD-04002: ファイルをオープンできません
O/S-Error: (OS 2) 指定されたファイルが見つかりません。
ORA-01547: 警告: RECOVERは成功しましたがOPEN
RESETLOGSが次のエラーを受け取りました。
ORA-01194: ファイル1は一貫した状態にするためにさらにリカバリが必要です。
ORA-01110: データファイル1: 'D:\ORADATA\ORCL\SYSTEM01.DBF'
SQL>
SQL> recover database until cancel;
ORA-00279: 変更217138326(09/30/2018 12:00:48で生成)にはスレッド1が必要です
ORA-00289:検討すべきログ・ファイル:D:\ORACLE\PRODUCT\10.2.0\DB_1\RDBMS\ARC02244_0830209207.001
ORA-00280: 変更217138326(スレッド1)は順序番号2244に存在します。
ログの指定: {=suggested | filename | AUTO | CANCEL}
D:\oradata\orcl\REDO1.log
ORA-00308: アーカイブ・ログD:\oradata\orcl\REDO1.logをオープンできません。
ORA-27041: ファイルをオープンできません。
OSD-04002: ファイルをオープンできません
O/S-Error: (OS 2) 指定されたファイルが見つかりません。
ログの指定: {=suggested | filename | AUTO | CANCEL}
D:\oradata\orcl\REDO01.log
ORA-00310:
アーカイブ・ログは順序番号2242を含んでいますが、順序番号2244が必要です。
ORA-00334: アーカイブ・ログ: 'D:\ORADATA\ORCL\REDO01.LOG'
ORA-01547: 警告: RECOVERは成功しましたがOPEN
RESETLOGSが次のエラーを受け取りました。
ORA-01194: ファイル1は一貫した状態にするためにさらにリカバリが必要です。
ORA-01110: データファイル1: 'D:\ORADATA\ORCL\SYSTEM01.DBF'
SQL> recover database until cancel;
ORA-00279: 変更217138326(09/30/2018 12:00:48で生成)にはスレッド1が必要です
ORA-00289:
検討すべきログ・ファイル:D:\ORACLE\PRODUCT\10.2.0\DB_1\RDBMS\ARC02244_0830209207
.001
ORA-00280: 変更217138326(スレッド1)は順序番号2244に存在します。
ログの指定: {=suggested | filename | AUTO | CANCEL}
D:\oradata\orcl\REDO02.log
ORA-00310:
アーカイブ・ログは順序番号2243を含んでいますが、順序番号2244が必要です。
ORA-00334: アーカイブ・ログ: 'D:\ORADATA\ORCL\REDO02.LOG'
ORA-01547: 警告: RECOVERは成功しましたがOPEN
RESETLOGSが次のエラーを受け取りました。
ORA-01194: ファイル1は一貫した状態にするためにさらにリカバリが必要です。
ORA-01110: データファイル1: 'D:\ORADATA\ORCL\SYSTEM01.DBF'
SQL> recover database until cancel;
ORA-00279: 変更217138326(09/30/2018 12:00:48で生成)にはスレッド1が必要です
ORA-00289:
検討すべきログ・ファイル:D:\ORACLE\PRODUCT\10.2.0\DB_1\RDBMS\ARC02244_0830209207
.001
ORA-00280: 変更217138326(スレッド1)は順序番号2244に存在します。
ログの指定: {=suggested | filename | AUTO | CANCEL}
D:\oradata\orcl\REDO03.log
ORA-00283: エラーによってリカバリ・セッションは取り消されました。
ORA-10562: Error occurred while applying redo to data block (file# 3, block#
21623)
ORA-10564: tablespace SYSAUX
ORA-01110: データファイル3: 'D:\ORADATA\ORCL\SYSAUX01.DBF'
ORA-10560: block type '0'
ORA-00600: 内部エラー・コード、引数: [4552],[1],[0],[],[],[],[],[]
ORA-01112: メディア・リカバリが開始されていません
SQL> recover database using backup controlfile until cancel;
ORA-00279: 変更217138326(09/30/2018 12:00:48で生成)にはスレッド1が必要です
ORA-00289:
検討すべきログ・ファイル:D:\ORACLE\PRODUCT\10.2.0\DB_1\RDBMS\ARC02244_0830209207
.001
ORA-00280: 変更217138326(スレッド1)は順序番号2244に存在します。
ログの指定: {=suggested | filename | AUTO | CANCEL}
D:\oradata\orcl\REDO01.log
ORA-00310:
アーカイブ・ログは順序番号2242を含んでいますが、順序番号2244が必要です。
ORA-00334: アーカイブ・ログ: 'D:\ORADATA\ORCL\REDO01.LOG'
ORA-01547: 警告: RECOVERは成功しましたがOPEN
RESETLOGSが次のエラーを受け取りました。
ORA-01194: ファイル1は一貫した状態にするためにさらにリカバリが必要です。
ORA-01110: データファイル1: 'D:\ORADATA\ORCL\SYSTEM01.DBF'
SQL> recover database using backup controlfile until cancel;
ORA-00279: 変更217138326(09/30/2018 12:00:48で生成)にはスレッド1が必要です
ORA-00289:
検討すべきログ・ファイル:D:\ORACLE\PRODUCT\10.2.0\DB_1\RDBMS\ARC02244_0830209207
.001
ORA-00280: 変更217138326(スレッド1)は順序番号2244に存在します。
ログの指定: {=suggested | filename | AUTO | CANCEL}
D:\oradata\orcl\REDO02.log
ORA-00310:
アーカイブ・ログは順序番号2243を含んでいますが、順序番号2244が必要です。
ORA-00334: アーカイブ・ログ: 'D:\ORADATA\ORCL\REDO02.LOG'
ORA-01547: 警告: RECOVERは成功しましたがOPEN
RESETLOGSが次のエラーを受け取りました。
ORA-01194: ファイル1は一貫した状態にするためにさらにリカバリが必要です。
ORA-01110: データファイル1: 'D:\ORADATA\ORCL\SYSTEM01.DBF'
SQL> D:\oradata\orcl\REDO02.log
SP2-0734: "D:\oradata..."で開始するコマンドが不明です - 残りの行は無視されました
。
SQL> recover database using backup controlfile until cancel;
ORA-00279: 変更217138326(09/30/2018 12:00:48で生成)にはスレッド1が必要です
ORA-00289:
検討すべきログ・ファイル:D:\ORACLE\PRODUCT\10.2.0\DB_1\RDBMS\ARC02244_0830209207
.001
ORA-00280: 変更217138326(スレッド1)は順序番号2244に存在します。
ログの指定: {=suggested | filename | AUTO | CANCEL}
D:\oradata\orcl\REDO03.log
ORA-00283: エラーによってリカバリ・セッションは取り消されました。
ORA-10562: Error occurred while applying redo to data block (file# 3, block#
21623)
ORA-10564: tablespace SYSAUX
ORA-01110: データファイル3: 'D:\ORADATA\ORCL\SYSAUX01.DBF'
ORA-10560: block type '0'
ORA-00600: 内部エラー・コード、引数: [4552],[1],[0],[],[],[],[],[]
ORA-01112: メディア・リカバリが開始されていません
SQL> SELECT * FROM V$RECOVER_FILE;
レコードが選択されませんでした。
SQL> COL FILE# FORMAT 999
SQL> COL FILE_NAME FORMAT A36
SQL> COL TABLE_SPACE_NAME FORMAT A10
SQL> COL STATUS FORMAT A7
SQL> COL ERROR FORMAT A16
SQL> SELECT RF.FILE#, DF.NAME FILE_NAME, TS.NAME TABLE_SPACE_NAME, DF.STATUS, RF
.ERROR, RF.CHANGE#, RF.TIME
  2    FROM V$RECOVER_FILE RF, V$DATAFILE DF, V$TABLESPACE TS
  3    WHERE DF.FILE# = RF.FILE#
  4      AND TS.TS# = DF.TS#
  5  /
レコードが選択されませんでした。
SQL>
SQL>

Oracleで、テーブルスペースを切り替える

今使っているtablespaceを切り替える必要が出てきたので
実施内容を纏めておきます。

データファイル名、テーブルスペース名、テーブル名などは実環境に読み替えてください。

手順概略

1.新しいテーブルスペースを作成
2.データを、新しいテーブルスペースへ移動
3.インデックスを、新しいテーブルスペースへ移動
4.ユーザーのデフォルト表領域を新しいテーブルスペースへ移動

手順

1.新しいテーブルスペースの作成

CREATE TABLESPACE "tablespacename";
DATAFILE 'D:\ORADATA\ORCL\"tablespacename";_xxx.DBF' SIZE 10240M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
/

2.データを、新しいテーブルスペースへ移動

ALTER TABLE <tablename> MOVE TABLESPACE "tablespacename";

3.インデックスを新しいテーブルスペースへ移動

ALTER INDEX <indexname> REBUILD TABLESPACE "tablespacename";

4.ユーザーのデフォルトテーブルスペースを変更

alter user <username> default tablespace "tablespacename";

別アプローチとして、一旦exportして、drop user , createuserでテーブルスペースを変更 , impでテーブルスペース指定なんてこともできそうです。
→今回はデータベースの移行だったので、こちらを採用しました。

メジャーどころのRDBで利用する主要ツールまとめ。

メジャーどころのRDBで利用する主要ツール覚え書き

	CUI	Import	Export	データ操作GUI Tools (無償・公式)
Oracle	SQLPlus	imp	exp	SQL Developer
SQLServer	OSQL	Bcp	Bcp	SQLServer Management Studio
MySQL	mysql	mysqlimport	mysqldump	MySQL GUI Tools

MYSQLでフルダンプ

mysqldump -R -uusername -ppassword -hhostname databasename > exportfilename.sql

Oracleでフルダンプ

exp user/pass@connectionname file=fullpath

SQLServerで該当テーブルのデータをCSV(カンマ)で出力

bcp databasename.owner.tablename out dat.dat -U sa -P password -S .\SQLEXPRESS -r \n -c -t, -T

その他1行Tips

○Oracleでデータを一括登録
　SQLLoaderか、SQLPlusで作り込み

○OracleでCSV出力
　SQLPLUSで、パラメータを色々いじくるとできる

○MySQLでCSV出力
　SQLの構文に、LOAD FILE ～とやると取込、OUTFILE～とやると出力

Oracle 10.2.0.3 Client(32bit)でインストールしたODACのバージョン

Oracle 10.2.0.3 Client(32bit)でインストールしたODACのバージョンが、

1.102.3.0だったんです。

このサイトに検索エンジンなどでたどりついた方は、同じ状況に遭遇しているのかもしれません。


お客様先でシステムのインストール作業を行って、さあ動作検証！って時に
システムでエラーが出てしまい、調べたところ、原因がコレでした。

お客様環境ではPSRをあててるので、最終的なバージョンは1.102.5.0

使いたいODACは、2.102.5.0

ODACの2.102.5.0は、OracleClientではなく、ODACのインストーラを
使ってインストールしないといけない(注：検証していないので正しくはわかりません
らしく、今回はそんな予定はなかったので、仕方なく1.102.5.0への対応を行いました。
※.netアプリだったので、対応としてはBindingRedirectにて読み替え。

BindingRedirectの記事については過去に書いてました。

Oracle カラム一覧・テーブル一覧を取得

昔から使うのに、身につかないので記事にしてみようと思います。

当然ながら、他のRDBMSでも実現機能はあります。
アプローチが違うだけです。

以下、手抜きSQLです。

実装例

SELECT col.* , com.comments from
(
select * from user_tab_columns
where table_name in (select table_name from user_tables)
) col
,
(
SELECT * FROM USER_COL_COMMENTS
where table_name in (select table_name from user_tables)
) com
where
col.table_name = com.table_name(+)
and col.column_name = com.column_name(+)

カラム一覧を抜き出して、そこにコメントを結合しています。

テーブル名で絞っているのは、ビューなどほかのオブジェクトが出てきてしまったからです。

関連してですが、ファンクションなどの情報は、USER_OBJECTSに入ってます。

Oracleで正規表現

10gより、正規表現が使えます。

REGEXP_LIKE という関数を使います。

構文例

SELECT * FROM DATA
WHERE
REGEXP_LIKE(ATAI,'^[0-9]+[\.]*[0-9]*$')

数値：^[0-9]+[\.]*[0-9]*$

数字範囲：^[0-9]+[\.]*[0-9]*～[0-9]+[\.]*[0-9]*$

正規表現はあまり詳しくないのでお許しくださいませ。

これで、よくネットでQAが上がっている「ISDATEと同等の関数ありませんか？」とか

「ISNumericと同等の関数ありませんか？」とかが少しでも

解決するのではいかという気がします。

追伸：他のRDBMSでも類似機能がありました。

MSSQL ：　LIKEで可能

MySQL　： LIKEと似た機能で、Col RegExp 正規表現　という形で実現

Oracleで別テーブルの値を参照してデータを更新するときの簡単な方法

マスタからあるKEYでトランザクションを結合して、
マスタの値を一括更新したい！なんて時、ありますね。

そんな時、昔書いていた方法を探していたら、別のアプローチを見つけました。

今まで知らなかったのが勿体ない。
というか、何事も片手間でやってはいけないな。と反省です。

実装例

インラインビューという方式を使います。
要は副問合せ、サブクエリです。

UPDATE
(SELECT
  DS.KEYSEQ
  ,DS.HYOJI_NM
  ,M.HYOJI_NM DISP
 FROM
  DATA DS , MASTER M
 WHERE
  DS.KEYSEQ = M.KEYSEQ
)
SET HYOJI_NM = DISP

何のことはない、サブクエリでKEY結合したものを外でSETしているだけです。

Oracleでできるので、他のRDBMSでも同等の機能はありそうですね。

暫くOracleをさわっているので、それ関連の記事が続きそうです。

OracleClient(ODP.net)とBindingRedirect

Oracle Client 10.2.0.1で稼働していたシステムを、Windows7対応により、10.2.0.5へバージョンアップをします。

まず、OracleClient10.2.0.1がインストールされている環境で作成したモジュールを、
10.2.0.5環境で動かすと、例外エラーが出ます。

【エラー内容】
ファイルまたはアセンブリ 'Oracle.DataAccess, Version=10.2.0.100, Culture=neutral, PublicKeyToken=89b483f429c47342'、またはその依存関係の 1 つが読み込めませんでした。指定されたファイルが見つかりません。

これは、side-by-side実行 という.netの標準動作であり、
ビルドした時に使用したモジュールのバージョンを実行時のも保持している事が理由で
このエラーは、ビルド時のモジュールがないからと発生するものです。

詳しくは、@ITあたりで。
http://www.atmarkit.co.jp/fdotnet/technology/idnfw11_04/idnfw11_04_01.html

対処法の１つとして、bindingRedirectという仕組みがあります。
簡単に言うと、バージョンをリダイレクトし、異なるバージョンへ飛ばすものです。

これで、該当モジュールが存在しないときに、別のバージョンを参照することができます。
設定は、app.configに記載する必要があります。

【記載例】
    <runtime>
        <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
            <dependentAssembly>
                <assemblyIdentity name="Oracle.DataAccess" publicKeyToken="89B483F429C47342"/>
                <bindingRedirect oldVersion="10.2.0.000-10.2.0.999" newVersion="1.102.2.20"/>
            </dependentAssembly>
        </assemblyBinding>
    </runtime>


ここで、「ん？」と思ったかもしれませんが、
ODP.netのバージョン番号の付与方法は、ODP10.2.0.3以降より、大幅に変わっています。

簡単に言うと、10.2.0.2までは、ODP.netのバージョンとほぼ同等。
それ以降は、先頭にFrameworkのバージョン番号が付くようになります。

アセンブリはバージョンアップしているにもかかわらず、バージョン番号は下がっているようにみえる。
一瞬混乱しますが、bindingRedirectの仕組みは、バージョンダウンにも対応可能。
よって、本方法で動作します。

それじゃなければ、汎用的なbindingRedirectの記述方法で簡単に参照できるそうです。(未検証)


【追記】
別アプローチとして「発行者ポリシー」を作成する等方法があります。
これは、GACに対してBindingRedirectを設定されるようなイメージですが、
アプリを改修せずに実施できるので、選択肢として検討するのもいいかもしれません。

Oracleでパイプライン表関数を利用して表データを返却する

Oracleで、プロシージャを使ってデータを返す事が可能。

SQLが複雑になりすぎた時や、そもそもSQLで組めないときなど
適切な単位でプロシージャ分割して組むことにより、処理性能、
可読性、保守性の向上に役立ちます。

※詳しくは調べていませんが、Oracle9iで搭載された機能のようです。

概略

返却する表を定義
プロシージャの戻り値に、定義した表を指定
プロシージャ内で表のデータを生成して返却

使うキーワード

RETURN 表名 PIPELINED
PIPE ROW(レコード定義)

サンプル

※年月の開始と終了を指定すると、年月のレコードを返す

CREATE OR REPLACE PACKAGE PKG_DATE_TABLE_UTIL
IS
  -- 返却するテーブルの型を定義する
  TYPE T_TABLE_YM_ROW IS RECORD(
    YEARMONTH      NUMBER(6)
  );
  -- テーブルを定義する
  TYPE T_REC_YM_ROW  IS TABLE OF T_TABLE_YM_ROW;
  -- 関数を定義する。返却値はTABLE定義名 PIPELINED
  FUNCTION GET_YM_TABLE(IN_START_YM NUMBER , IN_END_YM  NUMBER)
  RETURN T_REC_YM_ROW PIPELINED;
END;
 /

CREATE OR REPLACE PACKAGE BODY PKG_DATE_TABLE_UTIL
IS
  FUNCTION GET_YM_TABLE(IN_START_YM NUMBER , IN_END_YM  NUMBER)
  RETURN T_REC_YM_ROW PIPELINED
  IS
    ERROR_01   EXCEPTION;
    /* 変数定義 */
    L_WORK_DATE   DATE;
    L_END_DATE    DATE;

    /* レコード格納定義 */
    W_RET_REC T_TABLE_YM_ROW;
  BEGIN
    /* パラメータチェック  */
    IF IN_START_YM IS NULL THEN
      RAISE ERROR_01;
    END IF;
    IF IN_END_YM IS NULL THEN
      L_END_DATE := TO_DATE(IN_START_YM || '01' );
    ELSE
      L_END_DATE := TO_DATE(IN_END_YM || '01');
    END IF;
    L_WORK_DATE :=TO_DATE(IN_START_YM || '01' );
    -- 初めの1月目を出力
    W_RET_REC.YEARMONTH := TO_CHAR(L_WORK_DATE,'YYYYMM');
    PIPE ROW(W_RET_REC);
    L_WORK_DATE :=  ADD_MONTHS(L_WORK_DATE,1);
    -- 以降は指定された分だけ、繰り返し
    WHILE ( L_WORK_DATE <= L_END_DATE ) LOOP
      -- 定義に対してデータを設定
      W_RET_REC.YEARMONTH := TO_CHAR(L_WORK_DATE,'YYYYMM');
      -- 1行を返却する
      PIPE ROW(W_RET_REC);
      L_WORK_DATE :=  ADD_MONTHS(L_WORK_DATE,1);
    END LOOP;

    RETURN;
  END;
END;

/

Oracleが起動しなくなったときの対処(ORA-01034,ORA-27101,ORA-03113,ORA-12640)

お客様先でUPS故障があり、サーバーの電源をコンセント直にしていたのですが、
そんな時に限って、電源が急に落ちるという事態。

結果、Oracleが起動せずにシステムが利用できなくなった。

そんなときの対処です。

Oracleのエラーコード

ORA-01034、ORA-27101

アプローチ　まずはalert.log

まずはalert.log ということで、あちこちのファイルを探しても出てこない。
Winサーバなのでbdumpのフォルダがデフォルトということなのですが、
そこに出来ているファイルは更新日が1ヶ月以上も前。

一旦追うのをあきらめて、現象から調べていくアプローチへ切替。

エラーコードからgoogle先生

まず、ORA-01034:Oracleは使用できませんで調べると、
設定ファイル系がおかしくて起動できないのだろうという線の情報が多い。
ORACLE_SID,ORACLE_HOMEの環境変数など

事例から、以下コマンドを実施

set ORACLE_SID=ORCL
sqlplus /nolog
SQL> conn / as sysdba
SQL> startup mount

すると、別の現象
ORA-03113:通信チャネルでend-of-fileが検出されました
が発生。どうやら、別物らしいです。

そもそもデータベースがマウントできない。

REDOログが怪しいということで、REDOログの初期化を試行。

これもダメ。ORA-03113です。

ファイル走査

いったん手詰まり感が出たので、コンピューター上のログを
全検索。なぜか、sqlnet.logが更新されている。

開くと、

ORA-12640: 認証アダプタの初期化に失敗しました。

ん？って感じなのですが、それでさらに調べてみるとOTNにて
有用な情報を発見。
http://www.oracle.co.jp/forum/thread.jspa?messageID=27001826


確かに、ADはきちんと構築されていないお客様なので
設定を変更。そのとたん、何も処置をしなくても繋がるようになりました。

【変更前】SQLNET.AUTHENTICATION_SERVICE=(NTS)
【変更後】SQLNET.AUTHENTICATION_SERVICE=(NONE)

念のため、サーバーを再起動して動作を確認。

各クライアントからも繋がることを確認して、作業終了となりました。

結局は

原因の特定には至りませんでした。
AD認証から来る不都合だろうという結論。

そもそもUPSなしの運用はやめようね。

Oracle 開発の時に設定する権限のサンプル

自分ローカルであればDBAにしてしまうのですが、
手を抜いてしまうと後々適切に設定する方法を知らなくなってしまうので
ひとまずやってみた的なメモ

CREATE USER ORAUSER
IDENTIFIED BY PASSWORD
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
/
GRANT CONNECT TO ORAUSER
/
GRANT RESOURCE TO ORAUSER
/
GRANT ALTER DATABASE TO ORAUSER
/
GRANT ALTER SESSION TO ORAUSER
/
GRANT CREATE DATABASE LINK TO ORAUSER
/
GRANT CREATE SEQUENCE TO ORAUSER
/
GRANT CREATE SESSION TO ORAUSER
/
GRANT CREATE SYNONYM TO ORAUSER
/
GRANT CREATE TABLE TO ORAUSER
/
GRANT CREATE TRIGGER TO ORAUSER
/
GRANT CREATE TYPE TO ORAUSER
/
GRANT CREATE VIEW TO ORAUSER
/
GRANT DEBUG CONNECT SESSION TO ORAUSER
/
GRANT UNLIMITED TABLESPACE TO ORAUSER
/

これで、大体のことはできると思います。

Oracleのシャットダウン

記憶ではSQL/Plusから実行するもんだと思っていたのですが、
改めて調べてみると、ツールからの設定でいけるということが判明

• [コンフィグレーションおよび移行ツール]→[Administration Assistant for Windows]→「Administration Assistant for Windows」を起動
• 「データベース」の下に作成されているデータベースで右クリック。「起動/停止オプション」→「起動/停止構成」画面を表示
• 「サービス停止時にインスタンスを停止」にチェックを入れて、「即時停止（Shutdown immediate）」

注：Oracle 10.2.0.5　他は未確認