データベース備忘録

※自分調べ

ChatGPT-3にtnsnames.oraとlistener.oraを書いて貰った

流行りのChatGPT-3にデータベースのパラメタまで作成してもらったら楽だろうな~と思ったが、さすがにパラメタ数や設計要素多すぎてそこまではやってもらえないと思い、簡単そうなOracleのtnsnames.oraとlistener.oraを作ってもらった。

 

【質問】

 

【回答】

以下は、Oracle Database 19cにおけるlistener.oraおよびtnsnames.oraファイルの例です。具体的な設定は、環境や要件に合わせて適宜変更してください。

listener.oraの内容:

```
# listener_kokyakudbの設定
SID_LIST_listener_kokyakudb =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = kokyakudb)
      (ORACLE_HOME = /path/to/oracle19c)
    )
  )

# listener_ryokindbの設定
SID_LIST_listener_ryokindb =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = ryokindb)
      (ORACLE_HOME = /path/to/oracle19c)
    )
  )

# リスナーの設定
LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = testsrv01)(PORT = 1521))
    (REGISTER = DYNAMIC)
  )

# kokyakudbのリスナーの設定
listener_kokyakudb =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = testsrv01)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = kokyakudb)
    )
  )

# ryokindbのリスナーの設定
listener_ryokindb =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = testsrv01)(PORT = 1523))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ryokindb)
    )
  )
```

tnsnames.oraの内容:

```
# kokyakudb接続の設定
kokyakudb =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = testsrv01)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = kokyakudb)
    )
  )

# ryokindb接続の設定
ryokindb =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = testsrv01)(PORT = 1523))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ryokindb)
    )
  )
```

これらのファイルを、Oracle Database 19cを稼働させるデータベースサーバの適切なディレクトリに配置し、Oracleクライアント側からこれらのファイルを参照して接続することで、kokyakudbおよびryokindbという名前でDBに接続することができます。また、リスナー名はlistener_kokyakudbおよびlistener_ryokindbとし、それぞれのリスナーのポートは1522および1523と設定されています。リスナーの静的登録用のエントリは不要としています。

 

静的登録用のエントリは不要という要望を伝えたのにSID_LIST_xxxxはあるものの、

概ね間違っていないと思う。

 

大前提として記載方法を分かっていないと書いて貰うことも出来ないとは思うが、これだけの情報だけでここまでのものが出来上がるのかと驚いた。

 

oracleのパラメタも書いて貰える時代も近そう。

DBAは設計要素だけ入力すれば最適なパラメタを作り出してくれそうだ。

とはいえ最終的には人間が判断することにはなると思うが。

 

 

ブロックチェンジトラッキングとは

Oracleデータベースにおけるブロックチェンジトラッキング(Block Change Tracking 以下、BCT)とは、RMANにおける高速増分バックアップの際に使用される機能のことである。

 

そもそもRMANの増分バックアップには「差分増分バックアップ」と「累積増分バックアップ」の2種類がある。

 

差分増分バックアップは前回バックアップから更新があった部分(ブロック)のみをバックアップ対象とする。

一方で累積増分バックアップは、初回の全体バックアップ取得以降のすべての更新箇所をバックアップ対象とする。

 

この「差分増分バックアップ」で用いることが出来る機能が「高速増分バックアップ」である。

※おそらく正式には「高速(差分)増分バックアップ

 

通常の差分増分バックアップの場合、バックアップ時に全データブロックを確認しにいき、更新のフラグが立っているブロックをバックアップ対象とする。

データサイズがそこまで大きくないDBであればこの作業は大して負荷にならないが、データサイズが大きければ大きいほど全データブロックへ更新があるか・ないか確認する作業に時間が掛かってしまう。

 

一方で高速増分バックアップの機能をオンにしておくと、更新が掛かった箇所をブロックチェンジトラッキングファイル(BCTファイル)に記録するため、バックアップ時はそのファイルを確認することでバックアップ対象のブロックを知ることが出来る。

 

つまりデータブロックを全舐めして「更新箇所はどこだろう」と探してからバックアップする必要が無くなる。

この更新ブロックを記録しておく機能がBCTである。

 

正式なメカニズムはおそらくこう

(1)DBへ更新

(2)bct_public_dba_buffer(BCTバッファ)というラージプール上の領域に更新箇所の情報が格納

(3)CTWRプロセスがBCTバッファ上の情報をBCTファイルへ書き込み※

 

※(3)のBCTファイルへの書き込み契機はおそらくチェックポイント

 

 

ちなみにBCTファイルにどれだけの世代数の更新を記録しておくか、設定できる隠しパラメタ(_bct_bitmaps_per_file)があるが、これは変更しないことが推奨。

(補足:増分更新バックアップの場合。累積増分バックアップの場合は残しておきたい世代数を設定しておくのが良いと思う。何にせよ隠しパラメタなのでOracleのサポートに方針を聞くべき。)

 

デフォルトは8(世代)だが、仮に0などに設定しておくと際限なく記録されていくのでBCTファイルのサイズが肥大化していく。

 

単純にストレージを食うのもあるが(と言っても大したサイズにはならないと思うが)、バックアップ時にBCTファイルを確認する作業にも時間が掛かってしまうのでお勧めできない。

 

 

 

 

 

OracleDBにおけるサービスとは

OracleにおけるDBサービスとは何なのか、整理してみる。

 

DBサービスとは平たく言うとデータベース名と同義である。

ただこれはデフォルトの状態であれば正しいが、ユーザでサービスを追加できることを考えると誤り。

 

 

【デフォルトのサービスとは】

デフォルト、つまりDBを構築・起動しただけの状態だと下記のデフォルトサービスが起動している状態になる。

DB_UNIQUE_NAME.DB_DOMAIN(定義されている場合)

 

DB_DOMAINが定義されていると厳密にはデータベース名とは異なるが、基本的には

デフォルトのサービス名はデータベース名(DB_UNIQUE_NAME)である。

 

参考:SERVICE_NAMESパラメタ

docs.oracle.com

 

ただ単純にDBを繋ぎたい、という場合はこのデフォルトのサービス名を使用してtnsnames.oraに記載すれば良い。

ちなみにマルチテナント構成のPDBもサービスを持っているので、PDBにつなぎたい場合はPDBのデフォルトのサービス名を使用してTNSに記載すれば接続可能。

 

 

 

【ユーザ追加のサービス】

デフォルトのサービス以外にサービスを作成することもできる。

例えば、業務用・保守用にNWセグメントを分けていて、負荷や耐障害性、セキュリティの問題それぞれに別サービスを用意しておきたい、といった場合がある

 

srvctl add service -db ora19c -service ora19c_srvAP -netnum netAP

srvctl add service -db ora19c -service ora19c_srvMNT -netnum netMNT

 

※ネットワークをあらかじめリソース登録しておく必要があるので注意

(上の例だとnetAP・netMNTのこと

 

docs.oracle.com

 

上のコマンド例はマルチテナント構成のCDBのサービス登録だが、PDBのサービス登録も可能

 

srvctl add service -db ora19c -service ora19c_srvAP -pdb 19cpdb -netnum netAP

srvctl add service -db ora19c -service ora19c_srvMNT -pdb 19cpdb -netnum netMNT

 

 

ちなみにgridを使用している前提の説明。

(今時9i以前を使っている人も少ないと思うので問題ないと思うが)

 

 

 

 

 

Oracleの静的サービス登録と動的サービス登録

リスナーがDBのサービスをリッスンするには方法が二つある

 

listner.oraへの記述方法の違いで選り分けてみる

【静的登録】

静的、つまりパラメータに記載することで明示的にリスナーを登録しなければならない

 

<記載例>

listner.ora

SID_LIST_ORCL01=
(SID_LIST =
  (SID_DESC =
    (SID_NAME = ORCL01)
      (ORACLE_HOME = /u01/app/ora12c/product/12.2.0/dbhome_1)
  )
)

 

ORCL01=
(DESCRIPTION_LIST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = hostdesu)(PORT = 1521))
  )
)

 

 

この場合、

ORCL01エントリで「1521ポート」を使用することを設定(これは静的・動的同じ)、

SID_LIST_ORCL01エントリで「/u01/app/ora12c/product/12.2.0/dbhome_1」にインストールしたoracleから起動している「ORCL01」というデータベースをリスナープロセスに

登録する。

 

 

【動的登録】

 

<記載例>

明示的にパラメータに記載せず、LREGプロセスが各パラメータファイルを定期的に読み込んで設定してくれる。

 

listner.ora

ORCL01=
(DESCRIPTION_LIST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = hostdesu)(PORT = 1521))
  )
)

 

tnsnames.ora

ORCL01_srv=
(DESCRIPTION_LIST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = hostdesu)(PORT = 1521))
  )
)

 

#リスナーエイリアス

LISTNER_ORCL01_srv=
    (ADDRESS = (PROTOCOL = TCP)(HOST = hostdesu)(PORT = 1521))

接続識別子を「ORCL01_srv」に設定

リスナーエイリアスも設定しておく(※LOCAL_LISTENER設定用に)

 

tnsnames.oraでリスナーエイリアスに設定した識別子(上の例だとLISTNER_ORCL01_srv)を初期化パラメータLOCAL_LISTENERに設定する。

※LOCAL_LISTENERにはリスナーエイリアスではなく、直接ホスト名やポート番号の記載をすることも可能。エイリアスはあくまでエイリアスで、直接内容を記載することも可能。

 

要はDB側(LREGプロセス)がリスナーを認識するために、LOCAL_LISTENERを設定する必要がある。

LREGプロセスはリスナーに接続できると自身が所属しているDBのサービス名をリスナーに提供する。

するとリスナー側はユーザプロセスからの接続要求をどこのDBに流せば良いか、認識するようになるといった流れである。

 

 

初期化パラメータ(ファイル)

LOCAL_LISTENER=LISTNER_ORCL01_srv

SERVICE_NAMES=~

 

PFILEを使用しているならファイルに記載、

SPFILEを使用しているならalter文で設定変更(のち確認)

alter system set local_listener='(ADDRESS = (PROTOCOL = TCP)(HOST= hostdesu)(PORT = 1521))';

alter system set servie_names = service_name ;

 local_listnerはリスナーエイリアスを設定(tnsnames.ora)していればそっちでも設定可能

alter system set local_listener=LISTNER_ORCL01_srv;

確認コマンド

 

show parameter local_listener

show parameter service_names

 

oracle:インデックスの断片化確認の罠

oracleにおけるインデックス(索引)の断片化確認方法について

 

 

インデックスもテーブル同様に断片化していく。

テーブルの場合は行連鎖・行移行で1レコードのブロックが複数跨ることで断片化していくが、インデックスの場合は削除などでBツリーのリーフブロックが歯抜けになっていくイメージだろうか。

 

 

 

下記のコマンドでインデックスの分析が可能、INDEX_STATS表に情報が出力されて、アクセス効率が悪いか確認できる。

analyze index indexname validate structure ;

 

 

一般的には

HEIGHT(Bツリーの高さ) が4以上で、DEL_LF_ROW/LF_ROWS(削除されたエントリーの割合)が0.2を超える場合

は効率が悪いとされる。

 

 

 

指標が「Bツリーの高さ」と「削除されたエントリーの割合」の2つがあるが、基本的には2つの指標がどちらも超えた時に効率が悪いと判断だろうか。

 

「Bツリーの高さ」はレコード数(索引のエントリー数)が多ければ必然的に高くなっていくので、高い=効率悪い、とは一概には言えないだろうし、「削除されたエントリーの割合」も考慮しながら判断していくのが良いと思う。

 

 

下記にoracleのドキュメントがあるため、詳しくはこっちを参照。

津島博士のパフォーマンス講座  第6回 パフォーマンスの基礎である索引について 2011.04.20公開

https://blogs.oracle.com/otnjp/post/tsushima-hakushi-6

 

 

 

 

 

さて、本題の「インデックスの断片化確認の罠」だが、

 

 

 

上述の断片化確認のコマンド(ANALYZE)は

表ロックを取得するため、他処理(DML)と並走できない。

 

 

 

稼働中・業務中のシステムで性能調査などは良くあることだと思うが、その調査が他処理と並走できないというのは致命的。

 

 

なんとかならんもんかと思い、ANALYZEコマンドのマニュアルを見ると、ONLINE句を付けられることを発見。

 

検証環境で実行してみたところ、コマンド自体は正常終了するがINDEX_STATS表に何もレコードが入らない。。。

INDEX_STAT表のリファレンスを確認してみると、OFFLINEで実行する必要があるとのこと。

(だったら最初からエラーにしといてくれよと思うが…)

 

注意:

統計情報を収集するには、ANALYZE INDEX ... VALIDATE STRUCTURE OFFLINE文を使用する必要があります。

 

docs.oracle.com

 

 

 

 

 

 

結論、

ANALYZEコマンドを用いたインデックス断片化の確認はオンライン業務中には不可能

実施したい場合はメンテナンスの時間を設けて、他処理が入らない状況で実施する必要がある。

 

 

ちなみにこのINDEX_STATS表、揮発するらしく、情報を確認するためにはANALYZEコマンドを打ったセッションでSELECTする必要がある。

したがって複数のインデックスに対してANALYZEコマンドを打ち、あとでまとめて確認~みたいなことが出来ない。インデックス数が少なければ良いが、多ければ多いほどオーバヘッドが多くなる。

 

システムによるだろうが、メンテナンス時間も無限に用意できないし、その中で断片化確認だけに何時間も使えないことを考えると、商用システムではインデックス断片化確認するのは現実的ではない気がする。

 

 

 

 

 

そもそもハードの性能が上がっていることを考えると、従来に比べて断片化確認の必要性は薄くなっていると思うが、じゃあやらなくても良いのか?っていうとoracleアーキテクチャ上では断片化は発生しうるし、放っておくわけにもいかない。

 

特に稼働が長いシステムでは「昔からやっていたから、リプレースによってハード性能が向上した後も継続」みたいなことが多いのではなかろうか。

 

ハードの性能が上がっても、放っておいても大丈夫とは誰も、oracle社さえも言い切れないだろうから、oralceに断片化を自動で解消するような革新的な機能が導入されるまでは何とな~く残っていく作業になっていくのではないか。

 

 

性能劣化をoracleが自動的に解消する機能が、世間一般で使われるようになった時にはDBAという職業が無くなっている気もするが…

 

 

 

 

 

 

 

 

 

 

 

※ちなみにテーブルの行連鎖を確認するコマンドは表ロックを取らないらしい、他処理と並走可能。

ANALYZE TABLE tablename LIST CHAINED ROWS ;

 

docs.oracle.com

 

 

OracleDB起動時にORA-20013が発生

マルチテナントのOracleDB起動後、正常にDBは動作しているようだがアラートログを確認したところ起動シーケンスのところでORA-20013とORA-01403が発生していた。

 

ORA-20013:DBMS_QOPATCHは、主に、インストールされていない領域で動作しました。

ORA-01403:データが見つかりません。

 

DBMS_QOPATCHとはデータベース・パッチを確認するためのインタフェースで、パッチ適用状況をSQLベースで確認できるらしい。

 

下記のブログに詳細が説明されている

rurukblog.com

 

どうやらDB起動後に自動でこの機能が動作するらしく、パッチの適用状況を正常に取得できないことでエラーが発生しているようだった。

ググってみたら下記のページに到達、どうやらディレクトリオブジェクトが設定されていないことによるものらしい。dbacatalog.blogspot.com

 

ディレクトリオブジェクトを確認してみたところ下記の状況

設定なし:OPATCH_INST_DIR
設定なし:OPATCH_LOG_DIR
設定あり:OPATCH_SCRIPT_DIR

 

☆原因(推測)

DB起動時にDBMS_QOPATCHを実行してみてもログを吐き出すところを設定していないため、エラーになってしまっていた模様

 

☆解決策

OPATCH_INST_DIR、OPATCH_LOG_DIRを設定

起動時にエラーが発生しなくなり、解決

 

 

【疑問】

特に何もしていなかったが、OPATCH_SCRIPT_DIRは設定されていた。

自動で設定されるものなのだろうか

 

 

 

 

Oracle19cのPDBクローン

以前業務で環境構築を行う際、既存PDBからクローンする機会があった。

 

マルチテナントの環境だと既存のPDBからクローン(コピー)することが可能。

※もちろん既存のCDB・PDBがあること前提

 

CREATE PLUGGABLE DATABASE pdb FROM new_pdb;

 

データファイルごとコピーされるので、レコードの内容はもちろんユーザや権限などもコピーされる。

 

ファイル名・サービス名はコピーする際に、file_name_convert句service_name_convert句を使用して変更することが可能。

 

特にサービス名は既存サービスと競合する可能性があるのでクローン時に変更しておくのが吉。

また、この場合でもサービスが、作成したPDBに紐づいているわけではないので、PDBクローン後にsrvctl add serviceでサービスの登録は必要。

 

※通常、PDBクローン作業は検証環境での構築作業で実施することが多いと思う。

既存PDBをクローンした際には環境依存の文字列が入っているオブジェクトは適宜変更するのをお忘れなく。

(DBLINKやディレクトリオブジェクト、リソースプラン名などは環境名入りがち)