Oracle 9i DBAIノート

目次

• はじめに
• Oracleアーキテクチャ
• Oracleインスタンスの管理
• データベースの作成
• データディクショナリ
• 制御ファイルの管理
• REDOログファイルの管理
• 表領域とデータファイルの管理
• 記憶領域構造
• UNDOデータの管理
• 表の管理
• 索引の管理
• 整合性制約の管理
• ユーザの管理
• 権限の管理
• プロファイルの管理
• ロールの管理
• グローバリゼーションサポート
• 用語
• 参考資料
• その他
• 最後に

はじめに

2003年9月24日。Oracle9i DBAIを取得するための覚書をここに綴っていこうとおもい書き始めました。Oracle9i SQL, Oracle9i入門を受けたときよりもDBAIは実践的でにOracleに特化している気がします。Oracle9i DBAIを取得する事、または取得レベルの学習をする事で満遍なく知識がつくと思います。また今後、Oracle9i DBAIを受験する人の参考になれば幸いです。

Oracleアーキテクチャ

• Oracleインスタンス
  • システムグローバル領域(SGA:System Global Area)
    • 共用プール(Shared Pool)
    • データベース・バッファ・キャッシュ
    • REDOログ・バッファ
    • ラージプール
    • Javaプール
  • バックグラウンド・プロセス
    • DBWR (DataBase WriteR)
    • LGWR (LoG WriteR)
    • CKPT (ChecK PoinT)
    • SMON (System MONitor)
    • PMON (Process MONitor)
• Oracleデータベース
  • データファイル
  • 制御ファイル
  • REDOログファイル

ユーザプロセスとサーバプロセス 単純にUnixプロセスをイメージすればよい。ユーザプロセスはsqlplusなどのクライアントをexec()すると生成するプロセスの事で、サーバプロセスとは、そのユーザプロセスがアクセスするOralcle管理?のプロセス。メモリ割り当て等はオラクルサーバが担当しているまた割り当てられた領域をPGAと呼ぶ

PGA(Program Global Area)には以下が含まれる。(*共有サーバの場合PGAはスタック領域のみ)

• ソート領域　SQLソート用のメモリ領域
• セッション領域　ユーザ情報用メモリ領域
• カーソル領域　セッションで利用されているSQL文の処理状態
• スタック領域　その他のスタック変数の領域

Oracleインスタンスの管理

情報の取得

v$instance

startup nomount インスタンスの起動のみ

startup mount インスタンスを起動して制御ファイルを結びつける

shutdown immediate

shutdown abort 強制終了

shutdown

データベースの作成

データディクショナリ

情報の取得
select * from discionary

制御ファイルの管理

Oracleを起動、停止等を行う場合は、sysdba権限が必要になる。

制御ファイルのバックアップ
方法1.制御ファイルをバイナリのままバックアップコピーする。 alter database backup controlfile to 'hoge';
方法2.再作成用のスクリプトを作る alter database backup controlfile to trace;

情報の取得

initSID.ora
v$controlfile
v$parameter
v$spparameter
show parameter(SQL*Plus)

動的ディクショナリ・ビューが分からなかったときは、initSID.oraを見ていたが、SQL*Plusを実行しているときなどは、show parameterが見やすい。 制御ファイルの追加の仕方 v$controlfile_record_sectionで MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY, MAXDATAFILES, MAXINSTANCES

REDOログファイルの管理

実践・NOARCHIVEモードからARCHIVEモードへ切り替えてみる

select archiver from v$instance 現在のログモードの確認(STOPPEDの確認)
select * from v$log グループ確認
select * from v$logfile グループ内のファイル確認
select * from v$parameter where name = 'log_achive_start'
sqlplus /nolog
connect / as sysdba
shutdown
startup mount
alter database archivelog
shutdown
startup
alter system archive log start

オンラインREDOログについて

• オンラインREDOログは、障害回復に使われる
• REDOログ・グループを持つ(REDOスレッドとも呼ばれる)

オンラインREDOログの内容

REDOレコード(=REDOエントリ):データベースに対する全ての変更の再構築に使用できるデータが記録されてる。

1. システム・グローバル領域のREDOログ・バッファにREDOレコードが入る
2. LGWR(ログ・ライタ)によってREDOログファイルに書き込まれる
3. 書き込まれる際にシステム変更番号(SCN)が割り当てられる。
4. ユーザ・プロセスはトランザクションがコミットされた事を通知される

• ２つ以上のREDOログ・ファイルから構成される。
• 循環方式で書き込む（１つのファイルがいっぱいになるまで）

オンラインREDOログ・ファイルがいっぱいになったらどうなるか?

NOARCHIVE_MODE:記録がデータファイルに書かれたら、もう一度使えるようになる。

ARCHIVE_MODE:書き込まれ、かつアーカイブされた後にLGWRで使える

カレント・オンラインREDOログ・ファイル:LGWRが今使っているファイルの事 INACTIVE・オンラインREDOログ・ファイル:インスタンス・リカバリに必要でない ARCHIVE_MODEの時は、ARCnによってアーカイブされるまで、アクティブなオンライン・REDOログ・ファイルを利用できない。 NOARCHIVE_MODEの時は、最初のアクティブファイルが上書きされる。

ログスイッチ

オンラインREDOログ・ファイルの書き込みを終了して、次のファイルに書き込みをはじめるポイント。手動でログスイッチを実行する事も可能

ログ順序番号

ログスイッチ時に各オンラインREDOログ・ファイルに割り当てる。オンラインREDOログ・ファイルとアーカイブREDOログ・ファイルはこの番号でユニークになる。

オンラインREDOログを扱うには?

オンラインREDOログ・ファイルの多重化: LGWRが書き込む際、１つのファイルだけでなく複数にする事で障害を防ぐ。 オンラインREDOログの障害への対処 書き込めない時INVALIDをマーキングして、オラクルにメッセージを投げる。 インスタンスのオンラインREDOログに関する唯一の用件は、最低２つのグループを持つ事 グループのメンバは同じファイルサイズにする事 適切なオンラインREDOログ・ファイル数の選択 これを調べるには、LGWRトレース・ファイルおよびデータベースのアラートログの内容を調べるのが良い。 MAXLOGFILES オンラインREDOログ・ファイルのグループ数 MAXLOGMEMBERS オンラインREDOログ・ファイルのグループ毎のメンバの数 アーカイブ・タイムラグの制御 ・時間ベースでアーカイブログを切り替える事が出来る。

・ARCHIVE_LAG_TARGET初期化パラメータにより許容できるタイムラグを調節できる。
・デフォルトでは、無効になっている ex) ARCHIVE_LAG_TARGET = 1800 (30 min) ex) alter system set archive_lag_target = 1800 ログスイッチの強制 デフォルトでは、現行のオンラインREDOログ・ファイルグループがいっっぱいになると ログ・スイッチが自動的に発生します。 ex) alter system switch logfile; // alter system権限でいつでもok

アーカイブREDOログの管理

Oracleでは、いっぱいになったオンラインREDOログ・ファイルのグループをひとつの オフラインアーカイブに保存できる。これをアーカイブREDOログ(=アーカイブ・ログ) とよぶ。

・データベースがARCHIVELOGモードで動作している時に可能

・自動アーカイブ ・手動アーカイブ ARCHIVELOGモードで稼動している時は、オンラインREDOログ・グループがアーカイブされないかぎり LGWRはREDOログ・グループを再利用できません。 NOARCHIVELOGモードによるデータベースの実行 ・オンラインREDOログはアーカイブされない。

・データベースはインスタンス障害からのみ保護され、メディア障害からは保護されません。 アーカイブ制御 ・初期データベース・アーカイブ・モードの設定

・データベース・アーカイブ・モードの変更

・自動アーカイブの使用可能

・自動アーカイブの使用禁止

・手動アーカイブの実行

情報の取得

v$database
v$archived_log
v$archive_dest
v$archive_processes
v$backup_redolog
v$log
v$log_history
archive log list

表領域とデータファイルの管理

論理データベース構造 表領域＞セグメント＞エクステント＞データブロック 表領域の管理にはローカル管理とディクショナリ管理がある。 データファイル 物理的なファイルで表領域にマップするもの

情報の取得
select tablespace_name, initial_extent, next_extent, max_extents, pct_increase, max_extlen from dba_tablespaces;

select tablespace_name, extent_management from dba_tablespaces; これでローカル管理かディクショナリ管理か分かる。

データファイルの情報はdba_data_filesでおこなう。

Q.autoextentsとは？

A.自動でエクステント管理をおこなうこと？ その他 dba_temp_files; v$tablespace v$datafile 表領域を作ってみる。 SYSTEM表領域のextent managementがlocalである場合、その他の表領域にdirectoryを作る事ができない。 temporary表領域をつくるには create temporary tablespace文とcreate tablespaceのpermanent/temporaryを切り替えることで作る

読み取り表領域について
読み取り表領域は１度バックアップしておけばよい。またalter tablespace test_ts read onlyコマンドで読取専用にする。
この際、チェックポイントが発生するまで読み取り表領域にはならない。
注意したいのは、オブジェクトの削除も可能である事。実際にはそのデータをさくじょするのではなく、データ・ディクショナリを変更する。

記憶領域構造☆☆☆☆☆

• データセグメント
• 索引セグメント
• UNDO(ロールバック)セグメント
• 一時セグメント

セグメントにエクステントが割り当てられるのは、テーブルを作ったときなど つまりCREATE TABLE文などによりセグメントが生成される。 セグメントを作成する文はない。

このブロック領域管理パラメータはパフォーマンスチューニングの際に重要になると思われる。たとえば頻繁に新規登録、更新が行われるとすると、そのセグメントに対してPCTUSED以下になるまで新規登録が出来なくなる。テンポラリデータのように絶えずテーブルに新規登録、更新が行われるのであればPCTUSEDを高めるべきであろう。

INITRANS

トランザクションスロットの初期数を指定

MAXTRANS

トランザクションスロットの最大数を指定

PCTFREE

空き領域の割合。デフォルトは10%

PCTUSED

空き領域がPCTFREEの設定値を下回り、挿入が出来なくなった後、削除などによって 空き領域がどこまでなれば、再び挿入が出来るかを示すパラメータ。デフォルトは40%

Oracle9iからは自動セグメント領域管理が可能

情報の取得

dba_segments
dba_extents
dba_free_space 空き容量を調べる

UNDOデータの管理

情報の取得

desc dba_rollback_segs
v$rollname
v$rollstat
v$session
v$transaction
v$undostat

オフラインのロールバック・セグメントを確認できるのは、v$がつかないdba_rollback_segsのみ

表の管理

表作成のガイドライン

• 種類の異なるセグメントは別の表領域に配置する。これにより表領域の断片化を防ぐ
• 断片化を減らすためなるべく少ないエクステント数を割り当てる。

索引の管理

整合性制約の管理

主キーの制約確認は、DBA_CONSTRAINTS, DBA_INDEXESを利用する。これは、主キーがnot null制約、unique制約、索引のため。

ユーザの管理

登録
create user xxx identified by xxx defalut tablespace users temporary tablespace temp quota unlimited on users quota 50m on temp
password expire account unlock profile default;

情報の取得
dba_users
dba_ts_quotas

alter user test_user quota 0 on users;

上記のようにquota句を0にすると、オブジェクト作成時に、新規に領域を割り当てることが出来なくなる。
しかし、既存データはなくならない。

権限の管理

システム権限の付加

grant create session to test with admin option

システム権限の取り消し

revoke create session from test

ADMIN OPTION句付きで付加されたシステム権限をrevokeしても、
そのユーザが作成したオブジェクトや、そのユーザが他人に与えた
権限は削除されない。（システム権限の取り消しはカスケードされない。）

情報の取得
dba_sys_privs
session_privs

オブジェクト権限
オブジェクト権限は、他のユーザの所有するオブジェクトに対して
変更、実行、データのCRUDなどを行う権限

オブジェクト権限の取り消しは権限を付加したユーザしか出来ない。
WITH GRANT OPTION句付きで付与さらたオブジェクト権限はカスケードする。

情報の取得
dba_tab_privs
dba_col_privs

SYSOPER権限
STARTUP
SHUTDOWN
ALTER DATABASE {OPEN | MOUNT}
ALTER DATABASE BACKUP CONTROLFILE
ALTER TABLESPACE {BEGIN | END} BACKUP
RECOVER DATABASE
ALTER DATABASE ARCHIVELOG
RESTRICTED SESSION

SYSDBA権限
ADMIN OPTION付きSYSOPER権限
CREATE DATABASE
RECOVER DATABASE UNTIL

Administratorの方がOperatorより強い！

07_DICTIONARY_ACCESSIBILITY初期化パラメータ
TRUEだとSELECT ANY TABLEオブジェクト権限を持っているユーザはSYSスキーマを閲覧可能。

監査の種類と内容

データベースのログをとる事を監査という。監査にには、オペレーティングシステム監査、データベース監査、値ベース監査が存在する。

監査一覧

監査証跡（ログ）はSYS.AUD$表に記録されていく。

監査するための設定

初期化パラメータAUDIT_TRAILを設定する。NONE,DB,OSのどれかを値にする。この値は監査ログの出力先になる。

alter system set audit_trail=DB scope=spfile;

下位互換のためにTRUE/FALSEも設定できる。TRUE≒DB, FALSE≒NONE

文監査追加

audit select table, insert table, update table, delete table;

文監査停止

noaudit select table, insert table, update table, delete table;

権限監査

audit システム権限 [, システム権限 ...] [by username] [by (session | access)] [whenever [not] successful];

audit select any table by xxx by access;

権限監査停止

noaudit select any table by xxx by access;

オブジェクト監査

audit select on schema.employee whenever successful;

オブジェクト監査停止

noaudit select on schema.employee whenever successful;

　

監査結果の表示

監査設定の確認

オペレーティング・システム監査はデフォルトで施行される。
データベース監査は、初期化パラメータを有効にする必要がある。
値ベース監査は、データベース・トリガーやファイン・グレイン監査にて施行する。

プロファイルの管理

プロファイルの管理
ユーザ情報をグループ単位で管理するための機能
リソース制限とユーザアカウント制限の２つに分かれる

リソース制限をかけるには初期化パラメータRESOURCE_LIMITをTRUEにしなければならない。
alter system set RESOURCE_LIMIT=TRUE

session_per_user セッション数
cpu_per_session セッション中のCPU時間(1/100s)
cpu_per_call 1処理中のCPU時間(1/100s)
connect_time
idle_time
logical_reads_per_session 読めるブロック数
logical_reads_per_call 読めるブロック数
private_sga SGA内のプライベート領域の規模

create profile test_profile limit
session_per_user 100
cpu_per_session unlimited
cpu_per_call unlimited
connect_time 60
idle_time 30
logical_reads_per_session default
logical_reads_per_call default
private_sga unlimited;

create profile pass_profile limit
failed_login_attempts 3
password_life_time 1
password_reuse_time 1
password_reuse_max 5
password_lock_time 1/2
password_grace_time 1
password_verify_function default;

情報の取得
dba_profiles
dba_users

ロールの管理

システム権限とオブジェクト権限をグループ化したものをロール（役割）という。

事前定義済みロール

ロールの作成

create role test_role not identified

ロールの割り当て

grant test_role to test_user with admin option

ロールの使用禁止

set role all except role_test

ロールの取り消し

revoke test_role from test_user

ロールの削除

drop role test_role

create role test_role identified GLOBALLY;
エンターブライズ・ディレクトリ・サービスを利用するときはGLOBALLYを指定する。

drop role testrole
ロールを削除するには、WITH ADMIN OPTION付きでロールが付加されているか、DROP ANY ROLEシステム権限が必要。

データ・ディクショナリを参照するには、DELETE_CATALOG_ROLE, EXECUTE_CATALOG_ROLE, SELECT_CATALOG_ROLEが必要。

情報の取得

グローバリゼーションサポート

グローバリゼーションサポート

キャラクタセット概要
データベース作成時にCHARACTER SET,NATIONAL CHARACTER SETを指定する。
CHARACTER SETはCHAR, VARCHAR2,LONGなどの文字列で使われる。
NATIONAL CHARACTER SETはNCHAR, NVARCHAR2,NCLOBなどのNLSデータ型で使われる。
CHARACTER SETは、可変幅マルチバイトキャラクタセットを指定する。(JA16EUC,JA16SJIS)
NATIONAL CHARACTER SETのデフォルトはAL16UTF16
AL16UTF16は固定幅2バイトUnicodeキャラクタセット
UTF8は可変幅1-3バイトUnicodeキャラクタセット
US7ASCIIは7bitシングルバイトキャラクタセット

NLSパラメータ
言語依存する部分の設定パラメータ
NLS_LANGUAGE NLS_DATE_LANGUAGE, NLS_SORTを設定
NLS_TERRITORY NLS_CURRENCY NLS_ISO_CURRENCY NLS_DATE_FORMAT NLS_NUMERIC_CHARACTERSを設定
NLS_LANG環境変数 NLS_LANG=<言語>_<地域>.<キャラクタセット> export NLS_LANG=ja_JP.JA16EUC

情報の取得
NLS_DATABASE_PARAMETERS 現在の設定
NLS_INSTANCE_PARAMETERS 初期設定
NLS_SESSION_PARAMETERS V$NLS_PARAMETER セッション中の設定
V$NLS_VALID_VALUES NLSパラメータに指定できる値のリスト

Oracle9i データベース・エラー・メッセージ リリース2（9.2）

インポート

1. 型定義
2. 表定義
3. 表データ
4. 表索引
5. 整合性制約、ビュー、プロシージャ、トリガー
6. ビットマップ索引、ビュー、ファンクション索引、ドメイン索引

• catexp.sqlまたは、catalog.sqlを実行している
• IMP_FULL_DATABASEロールを持つ

SYSDBA権限でImportユーティリティを使う場合 一般ユーザとは異なるので次の場合以外は使わない ・オラクル社カスタマ・サポート・センターからの要求 ・トランスポータブル表領域セットをインポートする場合

OMF (Oracle-Managed Files)

Oracle9iの新機能でデータベースファイルを自動生成できる。 以下の初期化パラメータを設定する。

DB_CREATE_FILE_DEST データファイルのデフォルトの場所を指定する。

DB_CREATE_ONLINE_LOG_DEST_n REDOログファイルと制御ファイルのデフォルトの場所を指定する。(n<=5)

OMFでも制御ファイルを再作成することは可能だが、データファイル、REDOログファイル名を指定しなければいけないので、
alter database backup controlfile to trace;でトレース・ファイルにスクリプトを生成する事で対応する。

DB_CREATE_FILE_DEST データファイルのデフォルトの場所を指定

参考資料