More than 3 years have passed since last update.

Oracle に関するメモ

Last updated at 2021-12-05Posted at 2021-11-22

内容をZennに移動しました. 以後、こちらで随時更新していきます.
https://zenn.dev/kaaki/books/4ac444a0c4a80c

Oracle DBに関して調べたことのメモ

セッションの確立

専用サーバプロセスの場合

DBに接続を行いたいクライアント側で立ち上がるユーザプロセスが、DBサーバへ接続要求を行う.

DBサーバのリスナーが接続要求を受け取り、DBサーバ内に専用のサーバプロセスを立ち上げ、接続の確立を行う (以後のSQL処理の実行命令や実行結果の返却はサーバプロセスを介して行われる).

ユーザが接続介してログイン処理を行い、成功後にセッションが確立される.
---

共有サーバプロセスの場合

DBに接続を行いたいクライアント側で立ち上がるユーザプロセスが、DBサーバへ接続要求を行う.

DBサーバのリスナーが接続要求を受け取り、DBサーバ内の負荷の最も低いディスパッチャに接続を中継する (以後のSQL処理の実行命令や実行結果の返却はディスパッチャを経由し、SGA内のキューに溜め込まれ、共有サーバプロセスで処理される).

ユーザが接続を介してログイン処理を行い、成功後にセッションが確立される.

リスナーに関して

リスナーは、ネットワーク越しにクライアント要求を適切なデータベースに転送する役割をもつ独立したプロセスである. よって、リスナーが起動されていない場合はインスタンスが起動されていたとしてもユーザはセッションを作成することは出来ない. リスナーの設定はテキスト形式のファイルである listener.ora に記載が可能である. １つのサーバに複数のリスナーを起動することはできるが、推奨はされていない(参考). また、その際は受付情報の重複を防ぐために各リスナーが待ち受けるポート番号は一意でなければいけない.

ORACLE_HOME/network/admin/listener.ora

<リスナー名>=
  (DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=<プロトコル>)(HOST=<ホスト名>)(PORT=<ポート番号>))
      (ADDRESS=(PROTOCOL=ipc)(KEY=<インスタンス名>))))

デフォルトリスナーの設定は以下の通り.

リスナー名 : LISTENER
ホスト名 : 稼働サーバのホスト名
ポート番号 : 1521
プロトコル : TCP/IP

リスナーの起動

リスナーの起動には以下のコマンドを用いる.

lsnrctl start <リスナー名>

デフォルトでリスナー名はデフォルトリスナーのLISTENERが指定され、任意で設定した別リスナーの起動の際に指定する.
尚、lsnrctlコマンドのみを入力した場合、対話モードに入り、startなどのコマンドを入力することができる.

リスナーの状態確認

リスナーの稼働時間や登録されているデータベースサービスを確認する際は、以下のコマンドを用いる.

lsnrctl status <リスナー名> 
//リスナーの情報

lsnrctl services <リスナー名> 
//接続先のデータベースの情報

リスナーへのサービス登録

リスナーがクライアント要求を適切なデータベースへ転送するには、クライアント要求に含まれるデータベースサービス名を適切なインスタンスにマッピングできる必要性がある. よって、サービスの登録にはリスナーにデータベースサービス名と対応したインスタンスのSIDを登録する.

サービス登録には以下の方法がある.

動的サービス登録 : インスタンス起動時/終了時にインスタンスのLREGプロセスにより自動にリスナーへのサービス登録/削除が行われる. LREGプロセスは初期化パラメータのLOCAL_LISTENER設定値を参照し、登録先のリスナーアドレスを取得する. 初期値としては、デフォルトリスナーの設定値が記載されており、アドレスは同ホストのポート1521番、プロトコルとしてはTCP/IPが指定されている. よって、デフォルトリスナー以外のリスナーを起動している場合は、LOCAL_LISTENERを明示的に設定する必要がある. インスタンスが動的サービス登録された後に、該当のリスナーのステータスを確認した場合は、インスタンスのステータスとしてはREADYと出力される.
静的サービス登録 : listener.ora のSID_LIST_<リスナー名> にインスタンス SID を直接指定する. よって、インスタンスの起動/終了に関係なく、インスタンスがリスナーにサービス登録された状態が維持される. 同時に、リスナーはインスタンスの起動状態を把握できなくなるため、ステータスはUNKNOWNと出力される.

クライアントからリスナーへの接続要求

前述の通り、クライアントがデータベースに接続する際、リスナーに接続要求を送信する必要があり、接続要求に以下の情報を含む必要がある.

アドレス : 対象のリスナーが稼働しているサーバのホスト名もしくはIPアドレス.

ポート番号 : リスナーが受け付けているポート番号

データベースサービス名 : 接続先データベースサービス名

実際にはユーザが接続先を指定する際、以下のような接続識別子を利用する.

<ユーザ名>/<パスワード>@<接続識別子>

接続識別子から上記の接続先情報を解決する方法をネーミングメソッド (参考)と呼ぶ.

1. 簡易接続ネーミング・メソッド

簡易接続ネーミングは接続識別子に接続記述子を直接指定する方法である.

<ユーザ名>/<パスワード>@<ホスト名>:<ポート番号>/<データベースサービス名>
//ポート番号に関しては、省略した際1521が指定される.
2. ローカル・ネーミング・メソッド

クライアントのテキスト形式設定ファイルtnsnames.oraに上記接続記述子を記載しておく手法をローカルネーミングメソッドと呼び、接続識別子解決の為にtnsnames.oraでサービス名を検索する必要がある.
ORACLE_HOME/network/admin/tnsnames.ora
<接続記述子名>=
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = <プロトコル>)(HOST = <ホスト名>)(PORT = <ポート番号>))
   )
   (CONNECT_DATA =
     (SERVICE_NAME = <データべースサービス名>)
   )
 )
また、以下のように接続先情報を複数記載 (FAILOVERオプションは任意) しておくことで、接続時フェイルオーバーを実現したり、LOAD_BALANCEオプションでクライアント・ロード・バランシングを構成できる(参考).
この場合、仮に最初に<ホスト名1>:<ポート番号1>に対する接続を行い、結果的に失敗した場合は<ホスト名2>:<ポート番号2>への接続に自動で切り替える. なお、ロードバランシングを有効化している場合は、接続先はリストの中からランダムで選択される.

ORACLE_HOME/network/admin/tnsnames.ora
<接続記述子名>=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = <プロトコル1>)(HOST = <ホスト名1>)(PORT = <ポート番号1>))
      (ADDRESS = (PROTOCOL = <プロトコル2>)(HOST = <ホスト名2>)(PORT = <ポート番号2>))
　　　 (LOAD_BALANCE = yes)
　　　 (FAILOVER=on)
    )
    (CONNECT_DATA =
      (SERVICE_NAME = <データべースサービス名>)
    )
  )
3.外部ネーミング・メソッド

サード・パーティのネーミング・サービスに上記の接続記述子をtnsnamesマップに格納し、名前解決を行う際に参照しにいく手法を外部ネーミングメソッドと呼ぶ.
格納されるtnsnamesマップはtnsnames.oraとtns2nisコマンドを用いて生成できる.

4.ディレクトリ・ネーミング・メソッド

LDAPサーバ上に接続記述子を格納し、名前解決時に参照する手法をディレクトリ・ネーミング・メソッドと呼ぶ.

リスナーの終了

リスナーの起動には以下のコマンドを用いる.

lsnrctl stop <リスナー名>

リスナーの終了後、新規のセッションは確立できなくなるが、すでに作成されたセッションに関しては影響しない.

インスタンスに関して

インスタンス起動

インスタンスの起動には以下のコマンドを用いる.

STARTUP [ NOMOUNT | MOUNT | OPEN (デフォルト) ]

インスタンスの状態の説明は以下の通り.

SHUTDOWN : インスタンスおよびデータベースが稼働していない状態.
NOMOUNT : インスタンスのみ起動が行われた状態. インスタンス起動に必要な初期化パラメータなどは読み込まれ、SGAが割り当てられ、同時にバックグラウンドプロセスが起動する. インスタンスが起動しているため、CREATE DATABASEなどでデータベースの作成が行える.
MOUNT : 制御ファイルのみ読み書き可能な状態. 制御ファイルは初期化パラメータにて指定される.
OPEN : データベース (データファイルやREDOログファイルなど) に読み書き可能な状態. 読み込まれるファイルの設定は制御ファイルにて指定される.

インスタンス終了

インスタンスの終了には以下のコマンドを用いる.

SHUTDOWN [ NORMAL (デフォルト) | TRANSACTIONAL | IMMEDIATE | ABORT ]

インスタンス終了時のオプションは以下の通り.

NORMAL : 全てのユーザ接続が終了した際にインスタンスを終了する.
TRANSACTIONAL : 実行中のトランザクションが全て終了した際にインスタンスを終了する. ユーザは実行中のトランザクションが終了次第、切断される.
IMMEDEIATE : 実行中のトランザクションをロールバックし、インスタンスを終了する. ユーザは即座に切断される.
ABORT : インスタンスを強制終了する. ユーザは即座に切断される.

初期化パラメータ

初期化パラメータファイルに記載されたインスタンスに関するパラメータ設定. 指定されていない場合はデフォルト値が使用される.
初期パラメータファイルには以下のフォーマットが使用できる.

spfile : バイナリ形式のファイル (デフォルトで使用され、優先的に読み込まれる). 以下のコマンドで値を変更する.

ALTER SYSTEM SET <パラメータ名> = <設定値> [SCOPE = {SPFILE | MEMORY | BOTH}]

SCOPEの選択技は以下の通り.

SPFILE : インスタンス再起動後に設定が有効となる. インスタンス起動中に変更が不可能なパラメータを静的パラメータと呼ぶ.
MEMORY : 起動中のインスタンス内のみで有効となり、再起動後に変更は失われる. pfileでは、MEMORYのみが使用可能. インスタンス起動中に変更が可能なパラメータを変更可能パラメータ(動的パラメータ)と呼ぶ.
BOTH (デフォルト) : 上記２つの両方を有効とする.

pfile : テキスト形式のファイル. エディタなどで直接値を変更する. ALTER SYSTEM SET コマンドで設定値を変更した場合、変更内容はpfileには適応されないため、再起動後に変更は失われる.

設定されている初期化パラメータを確認するには以下の方法が存在する.

SQL Plus の SHOW PARAMETERS コマンド.
動的パフォーマンスビューのV＄PARAMETER, V＄SYSTEM_PARAMETER

また、日付フォーマット等の一部のパラメータは以下の通りにセッション単位で変更が可能である.
ALTER SESSION SET <パラメータ名> = <設定値>

システムグローバル領域 (SGA)

インスタンス起動時に割り当てられるプロセス間共有メモリ領域 - 反対にプロセス間で共有しないプロセス専用の領域が**プログラムグローバル領域(PGA)**と呼ぶ.
SGAのコンポーネントは以下の通り:

1. データベースバッファキャッシュ領域 :
  データベースから読み込んだデータを一時的にキャッシュしておく.
  また、書き込みの際にも、OracleはDBへの即時書き込みは行わず、一時的に内容をキャッシュし、データベースライター(DBWn)により遅れてDBへの書き込み処理が行われる (遅延書き込み).
  
  遅延書き込みのメリットは以下の通り:
a. 書き込み処理が一時的に高頻度になっても、処理負荷の平準化が行われる.
b. 同じデータへの書き込み処理が複数回行われた場合であっても、実際のDBへの書き込み回数が１度に固定化される.
1. REDO ログバッファ領域 :
  REDOログファイルに書き込む前の変更履歴を一時的に保管する.
1. Javaプール領域 :
  Java言語で記述されたストアドプログラムの実行時に使用される領域.
1. ラージプール領域 :
  バックアップや並列処理などの作業領域として使用される.
1. 共有プール領域 :
  解析済みSQLやシーケンス、データディクショナリなどをキャッシュする.
  SQL文が発行された際の処理と、共有プールに保管されるまで(参考)
  1.解析
  
  a. 構文チェック : SQL文の構文上の妥当性 (FROMなどの予約文字のスペルミスを含む) を検査する.
  
  b. セマンティクスチェック : SQL文内で参照されているオブジェクトや列の存在有無などを検査し、文の有効性を確認する.
  c. 共有プール・チェック : リソース集中型の手順を省略できるかどうかを検査する. 共有プール内に過去に解析されたSQL文が、実行されるSQL文と等価である場合、情報を再利用することで後続処理であるを省くことで、SQL解析のオーバーヘッドを回避する.
  
  存在する場合 : (キャッシュ情報を利用し、処理の実行を行う) 一連の解析をソフト解析と呼ぶ
  
  存在しない場合 :(最適化と行ソース生成および処理の実行を行う) 一連の解析をハード解析と呼ぶ
  2.最適化
  
  3.行ソース生成
  
  4.処理の実行
  バックグラウンドプロセス
  
  インスタンス起動時に起動される全体処理を担うプロセス群.
  
  1. データベースライター(DBWn)
  
  データベースキャッシュ内にバッファされている変更分を実際のDB(データファイル)に書き込む. キャッシュに更新情報をが新規に追加されるたびにダーティー情報として管理され、書き込みが終わったものに関しては、クリーン/フリー情報として管理される. 書き込みが実行されるタイミングは以下の通り (参考).
  - a. チェックポイントにシグナルされた場合
  - b. ダーティー情報のリスト長が一定の閾値を超えた場合
  - c. フリー情報や、再利用可能な情報が一つも存在しない場合
  2. ログライター(LGWR)
  
  REDOログバッファ内の情報をREDOログファイルに書き出す (参考).
  
  書き込みが実行されるタイミングは以下の通り.
  - 1. COMMITが発行された場合
  - 1. REDOログバッファの空き領域が不足した場合
  書き込みが行われるREDOログファイルは、原則２つ以上必要であり、LGWRは循環方式で書き込み先を指定しする. また、書き込み先が変更されるタイミングをログ・スイッチと呼ぶ.
  
  ログ・スイッチが行われるタイミングは以下の通り.
  - 1. 以下のコマンドで手動で実行した時
```
ALTER SYSTEM SWITCH LOGFILE; 
```
  - 1. 書き込み先のREDOログファイルが一杯になった時
  - 1. 定期的に自動でログ・スイッチするようにしている時
  3. アーカイバ(ARCn)
  
  ログスイッチが発生した際に、REDOログファイルをアーカイブログファイルとしてコピーする.
  
  4. チェックポイント(CKPT)
  
  DBWnに書き込み処理を実行するシグナルを出す. また、制御ファイルにチェックポイント情報を書き込む.
  
  5. システムモニター(SMON)
  
  インスタンス異常終了を検知し、次回起動時にデータベースファイルの生合成を復旧する. また、未使用の一時セグメントを解放する.
  
  6. プロセスモニター(PMON)
  
  プロセス異常終了を検知し、データやリソースのクリーンアップや強制終了を行う.
  
  7. 管理モニター(MMON)
  
  AWRスナップショットなど、性能分析に用いられる統計情報の定期収集を行う.
  
  8. リスナー登録プロセス(LREG)
  
  インスタンスに関する情報をリスナーに登録する.
  
  データベースリンク
  
  あるデータベースから別のデータベースのテーブル、ビューおよびストアド・プログラムを参照できるようにする機能をデータベースリンクと呼ぶ (参照1, 参照2, 参照3).
  この場合、接続元のデータベースがクライアントとして動作し、接続先のデータベースに問い合わせを行う.
  また、リンク先のデータベースはOracle DBに限ったものをではない (その場合、Oracle異機種間サービスを使用する必要性がある).
  
  データベースリンクの作成は以下のコマンドで行う.
```
CREATE [PUBLIC] DATABASE LINK <データベースサービス名>
 CONNECT TO <ユーザ名>
 IDENTIFIED BY <パスワード>
 USING <接続識別子>;

//ここでの接続先情報は、接続先のデータベースの*tsnnames.ora*に記載済みであるとする.
```
  作成されたデータベースリンクは、PUBLICオプションを指定した場合は、全てのユーザにより使用可能となり (パブリック・データベース・リンク)、指定されていない場合は作成を行ったユーザにのみ使用可能となる (固定ユーザー・データベース・リンク).
  
  データベースリンクを用いてレコードをセレクトする例は以下の通り.
```
SELECT * FROM <テーブル名>@<データベースサービス名>;
```
  管理情報とその確認
  
  データディクショナリはオブジェクト定義情報、ユーザ情報、ロール情報、権限情報やデータファイルに関する情報などのOracle内部情報を格納し管理している特殊な表を指す. Oracleに読み込みやすいような形式になっており、直接閲覧したとしても人間が読みやすい形式で管理はされておらず、その際にはデータディクショナリビューを参照する. 両情報はSYSユーザにより所有されており、DBがOPEN状態でない場合と参照することは出来ない.
  
  主なデータディクショナリビューは以下の通り.
  - 表の情報/DBA_TABLES, ALL_TABLES, USER_TABLES
  - インデックスの情報/DBA_INDEXES, ALL_INDEXES, USER_INDEXES
  - ビューの情報/DBA_VIEWS, ALL_VIEWS, USER_VIEWS
  - シーケンスの情報/DBA_SEQUENCES, ALL_SEQUENCES, USER_SEQUENCES
  - シノニムの情報/DBA_SYNONYMS, ALL_SYNONYMS, USER_SYNONYMS
  - 制約の情報/DBA_CONSTRAINTS, ALL_CONSTRAINTS, USER_CONSTRAINTS
  - ユーザの情報/DBA_USERS
  - 表領域の情報/DBA_TABLESPACES
  - データファイルの情報/DBA_DATA_FILES
  - データファイルの空き領域の情報/DBA_FREE_SPACE
    DBA系はデータベース内全ての情報であり参照にはシステム権限またはSELECT_CATALOG_ROLEが必要となる.
    ALLはユーザが他のユーザが所有しているものを含んだ参照可能な全ての情報を参照でき、USERはユーザが保有している全ての情報を参照できる.
  - 動的パフォーマンスビュー : 制御ファイルに格納された内部情報やSGA内で保管された内部情報を閲覧する際に使用する. データディクショナリと異なり、格納先がデータファイルでないため、情報の種類によってはMOUNT状態、NOMOUNT状態であっても情報を取得できるが、読み取り一貫性は保障されない.
  - 初期化パラメータの設定値/V$PARAMETER
  - SGAのサマリー情報/V$SGA
  - SGAに関するサイズ情報/V$SGAINFO
  - セッション情報/V$SESSION
  自動診断レポジトリ(Automatic Diagnostic Repository)
  
  ADRとは、初期化パラメータの/logもしくはDIAGNOSTIC_DESTで指定したパス配下に製品別で管理されるログファイルの集合体.
  
  アラートログファイル : 1つのインスタンスに1つのアラートログファイルが生成される. インスタンス内で発生したイベント(CRETAE等の管理コマンドの実行、インスタンスの起動/終了など)やその設定値、致命的なエラーのログを記録する. 致命的なエラーはORA-<数字>で出力され、プロセストレースファイル (バックグラウンドプロセスやサーバプロセス等のログ) とインシデントダンプファイル (カスタマーサポート問い合わせ時に参考となるインシデント診断情報) が出力される.

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up

Oracle に関するメモ

Oracle DBに関して調べたことのメモ

セッションの確立

専用サーバプロセスの場合

共有サーバプロセスの場合

リスナーに関して

リスナーの起動

リスナーの状態確認

リスナーへのサービス登録

クライアントからリスナーへの接続要求

1. 簡易接続ネーミング・メソッド

2. ローカル・ネーミング・メソッド

3.外部ネーミング・メソッド

4.ディレクトリ・ネーミング・メソッド

リスナーの終了

インスタンスに関して

インスタンス起動

インスタンス終了

初期化パラメータ

システムグローバル領域 (SGA)

バックグラウンドプロセス

1. データベースライター(DBWn)

2. ログライター(LGWR)

3. アーカイバ(ARCn)

4. チェックポイント(CKPT)

5. システムモニター(SMON)

6. プロセスモニター(PMON)

7. 管理モニター(MMON)

8. リスナー登録プロセス(LREG)

データベースリンク

管理情報とその確認

自動診断レポジトリ(Automatic Diagnostic Repository)