Oracle Database入門 - データベース設定編

Slide 1

Slide 1 text

Oracle Database -データベース設定編-

Slide 2

Slide 2 text

Slide 3

Slide 3 text

Slide 4

Slide 4 text

• 意外と簡単⁉データベース構築編で⾏った作業 • Oracle Database 19cのインストール • DBCAを使ったデータベースの作成 • SQL*PLUSでのデータベース起動停⽌ • システム構成 • オペレーティングシステム︓Windows Server2019 x64 – Standard Edition • RDBMS︓ Oracle Database 19c Enterprise Edition for Windows(19.3) データベース構築編で⾏った作業とシステム構成について Copyright © 2023, Oracle and/or its affiliates 4

Slide 5

Slide 5 text

Slide 6

Slide 6 text

データベース関連ドキュメント • Oracle Database データベース管理者リファレンス for Microsoft Windows • Oracle Database リリースノート 19c • Oracle Database 2⽇でデータベース管理者上記の資料は以下のURLより参照できます https://www.oracle.com/jp/database/technologies/oracle-database-documentation.html SQL Developer関連ドキュメント • SQL Developer ユーザーズ・ガイド上記の資料は以下のURLより参照できます https://www.oracle.com/jp/database/technologies/appdev/oracle-sql-developer-documentation.html ドキュメントの⽤意 Copyright © 2023, Oracle and/or its affiliates 6

Slide 7

Slide 7 text

Slide 8

Slide 8 text

データベース開発者/管理者に向けた統合開発ツール • 無償の開発/管理ツール • 簡単なインストール • ダウンロードして展開するのみ • Linux, Windows, Mac OSに対応 • 以下の機能をGUI上で⾏うことで、SQLやPL/SQLの開発⽣産性が向上 • オブジェクトの閲覧と作成 • SQLWorksheet • PL/SQLの編集及びデバッグ • Oracle Databaseのパフォーマンス、セキュリティ、ストレージ、設定などの管理以下のリンクを参考にSQL Developerをダウンロード https://docs.oracle.com/cd/F51516_01/rptig/in stalling-sql-developer.html SQL Developerとは Copyright © 2023, Oracle and/or its affiliates 8

Slide 9

Slide 9 text

Copyright © 2023, Oracle and/or its affiliates 9 SQL Developerを使⽤してデータベースに接続してみよう ①+ボタンをクリック ②以下の通り⼊⼒いたします (解説がないものはすべてデフォルト) Name:任意の名前(接続名) ユーザー名:sys パスワード︓データベース作成時に設定したものロール︓SYSDBA ホスト名︓作成時に設定したホスト名ポート︓設定したポートサービス名︓作成時に設定したサービス名 ③クリック CDB(sysユーザー)

Slide 10

Slide 10 text

Slide 11

Slide 11 text

Slide 12

Slide 12 text

【CDB】 • データベースのステータス確認 • データベース運⽤モードの確認 • PDBの管理（CDB固有） • 物理的なファイルの配置確認 • 制御ファイルの確認（CDB固有） • REDOログ・ファイルの確認（CDB固有） • 表領域およびデータファイルの確認 • 論理的な設定の確認 • 初期化パラメータの確認 • メモリー割り当ての確認 • ユーザー管理【PDB】 • データベースのステータス確認 • 物理的なファイルの配置確認 • 表領域およびデータファイルの確認 • 論理的な設定の確認 • 初期化パラメータの確認 • ユーザー管理データベース作成後の主な確認事項および設定 Copyright © 2023, Oracle and/or its affiliates 12 SQL*Developer 「DBA機能」を利⽤して確認します

Slide 13

Slide 13 text

Slide 14

Slide 14 text

Slide 15

Slide 15 text

Slide 16

Slide 16 text

Copyright © 2023, Oracle and/or its affiliates 16 • PDBの管理 • セッション状況の表⽰ • 表領域およびセグメントスペースの使⽤状況の表⽰ • 待機イベントの表⽰ • 初期化パラメータの表⽰ • リアルタイムSQLモニタリングの表⽰ Enterprise Editionのオプション・ライセンスが必要: Oracle Diagnostics Pack Oracle Tuning Pack SQL DeveloperのDBA機能でできること (⼀部オプション・ライセンスが必要)

Slide 17

Slide 17 text

【CDB】 • データベースのステータス確認 • データベース運⽤モードの確認 • PDBの管理（CDB固有） • 物理的なファイルの配置確認 • 制御ファイルの確認（CDB固有） • REDOログ・ファイルの確認（CDB固有） • 表領域およびデータファイルの確認 • 論理的な設定の確認 • 初期化パラメータの確認 • メモリー割り当ての確認 • ユーザー管理【PDB】 • データベースのステータス確認 • 物理的なファイルの配置確認 • 表領域およびデータファイルの確認 • 論理的な設定の確認 • 初期化パラメータの確認 • ユーザー管理データベース作成後の主な確認事項および設定 Copyright © 2023, Oracle and/or its affiliates 17 SQL*Developer 「DBA機能」を利⽤して確認します

Slide 18

Slide 18 text

Slide 19

Slide 19 text

Slide 20

Slide 20 text

ノー・アーカイブログモード(テスト環境のみ) アーカイブログモードデータベース運⽤モード Copyright © 2023, Oracle and/or its affiliates 20 #50 #51 #52 #53 #54 #55 循環して上書き #50 #51 #52 #53 #54 #55 循環して上書き循環して REDOログを上書き #50 #51 #52 #53 #54 #55 REDOログファイル REDOログファイルアーカイブ・ログファイル REDOログの上書き前にアーカイブする

Slide 21

Slide 21 text

Slide 22

Slide 22 text

Slide 23

Slide 23 text

Slide 24

Slide 24 text

Slide 25

Slide 25 text

【CDB】 • データベースのステータス確認 • データベース運⽤モードの確認 • PDBの管理（CDB固有） • 物理的なファイルの配置確認 • 制御ファイルの確認（CDB固有） • REDOログ・ファイルの確認（CDB固有） • 表領域およびデータファイルの確認 • 論理的な設定の確認 • 初期化パラメータの確認 • メモリー割り当ての確認 • ユーザー管理【PDB】 • データベースのステータス確認 • 物理的なファイルの配置確認 • 表領域およびデータファイルの確認 • 論理的な設定の確認 • 初期化パラメータの確認 • ユーザー管理データベース作成後の主な確認事項および設定 Copyright © 2023, Oracle and/or its affiliates 25 SQL*Developer 「DBA機能」を利⽤して確認します

Slide 26

Slide 26 text

Slide 27

Slide 27 text

Slide 28

Slide 28 text

Slide 29

Slide 29 text

Slide 30

Slide 30 text

USERS表領域表領域詳細 Copyright © 2023, Oracle and/or its affiliates 30 USERS表領域の特徴 • DATAFILE属性 • サイズ: 5MB (5,242,880 byte) • サイズに対するブロック数: 640 (5,242,880 ÷ 8192) • DATAFILEは⾃動拡張（AUTOEXTEND） • (⾃動拡張時の次の確保領域: 1,310,720 byte → 160 Block: dba_data_files の increment_by カラム) • 表領域属性 • 表領域内のエクステント管理: LOCAL • エクステント割り当てのタイプ: SYSTEM → AUTOALLOCATE • 表領域内のセグメントの空き領域と使⽤済み領域の管理⽅法: AUTO • BIGFILE表領域かどうか: NO CDB(sysユーザー)

Slide 31

Slide 31 text

参考）論理記憶域構造 Copyright © 2023, Oracle and/or its affiliates 31 データ・ブロックデータの最⼩レベルでの格納単位。⼀定のバイト数(8KB)の物理ディスク領域に対応エクステント論理的に連続する⼀連のデータ・ブロックセグメント表などの特定のデータベース・オブジェクトのために割り当てられるエクステントの集合表領域セグメントを格納するための論理記憶域コンテナ。１つ以上のデータ・ファイルまたは⼀時ファイルで構成論理記憶域と物理記憶域の ERダイアグラム表領域セグメントエクステントデータ・ブロックデータ・ファイル OSブロック論理記憶域物理記憶域エクステント（72 KB） 8 KB 8 KB 8 KB 8 KB 8 KB 8 KB 8 KB 8 KB 8 KB エクステント（120 KB） 8 KB 8 KB 8 KB 8 KB 8 KB 8 KB 8 KB 8 KB 8 KB 8 KB 8 KB 8 KB 8 KB 8 KB 8 KB データファイルデータファイルセグメント（192 KB）データ・ブロックデータ・ブロック特定のオブジェクトに割当表領域

Slide 32

Slide 32 text

現在はローカル管理表領域、⾃動セグメント領域管理⾃動セグメント領域管理 Automatic Segment Space Management (ASSM) • 8i以前のフリーリストによる管理に⽐べ、管理性、領域使⽤効率性、同時実⾏処理が向上 • ASSMでなければ以下の機能は利⽤できない • bigfile表領域 • オンライン・セグメント縮⼩参考）表領域における空き領域管理 Copyright © 2023, Oracle and/or its affiliates 32 表領域におけるセグメントに対するExtent割り当て【ローカル管理表領域】【Dictionary管理表領域】【Uniform Size】【AUTOALLOCATION】表領域空き領域 File Header Extent管理情報セグメント01 セグメント02 セグメント03 表領域空き領域 File Header Extent管理情報セグメント01 セグメント02 セグメント03 Extent System Dictionary 表領域空き領域 File Header セグメント01 セグメント02 セグメント03 Extent管理情報 • Extent管理はDictionary • 表領域内のExtentサイズは詳細指定可能 • 断⽚化率︓ ⼤ • Extent管理は表領域内のファイルヘッダー内のビットマップで管理 • Extentサイズは指定したサイズで全て均⼀ • 断⽚化率︓ ⼩ • Extent管理は表領域内のファイルヘッダー内のビットマップで管理 • Extentサイズはセグメントのサイズに従い、 Oracle内部アルゴリズムで決定 • 断⽚化率︓ 中セグメント内の空き領域管理 Oracle 8.1.5 よりも前セグメント03 空き領域

Slide 33

Slide 33 text

参考）表領域における空き領域管理 Copyright © 2023, Oracle and/or its affiliates 33 表領域のエクステント管理⽅法 8iまでデフォルト 9iからデフォルト ※ System表領域の LOCAL指定はR9.2から CREATE TABLESPACE XXX …… EXTENT MANAGEMENT LOCAL PCTFREE 表領域作成時の指定⽅法 PCTFREE PCTUSED FREELISTS FREELISTS GROUP ディクショナリ管理ローカル管理⼿動データブロック管理（MANUAL）⾃動セグメント領域管理（AUTO）エクステントサイズ均⼀サイズ指定エクステントサイズ System⾃動管理 9i以降利⽤可 SEGMENT SPACE MANAGEMENT AUTOALLOCATION UNIFORM SIZE <サイズ> AUTO

Slide 34

Slide 34 text

smallfile表領域（デフォルト） • 複数のデータファイルを束ねて利⽤可能（最⼤1023 個まで） bigfile表領域（10g〜） • 単⼀の⾮常に⼤きなデータファイルのみで構成 • ファイル管理が簡素化できる • ローカル管理表領域かつ⾃動セグメント領域管理であること • Oracle Automatic Storage Management (ASM) 上での利⽤を推奨参考）bigfile表領域 Copyright © 2023, Oracle and/or its affiliates 34 CREATE BIGFILE TABLESPACE XXX …… 【参考】物理データベースの制限 Oracle Database のデータファイルの制限: • 1つのデータファイルあたり 2^22 -1 = 4,194,303 block → smallfile表領域では、ブロック・サイズが 8k の場合、1つのデータファイルに 32 GB のデータを格納できるそれ以上の容量の表領域を作成したい場合は複数のデータファイルを束ねる必要がある → bigfile表領域では、ブロックサイズが 8k の場合、32TB までデータを格納できる ※ Oracle Base Database Service のUSERS表領域は smallfile表領域のため、⼤量データの格納するには独⾃の bigfile表領域を⽤意すべき db_block_size データファイルの最⼤サイズ 2k byte 約 8 G byte ( 8,589,932,544 byte) 4k byte 約 16 G byte ( 17,179,865,088 byte) 8k byte 約 32 G byte ( 34,359,730,176 byte) 16k byte 約 64 G byte ( 68,719,460,352 byte)

Slide 35

Slide 35 text

【CDB】 • データベースのステータス確認 • データベース運⽤モードの確認 • PDBの管理（CDB固有） • 物理的なファイルの配置確認 • 制御ファイルの確認（CDB固有） • REDOログ・ファイルの確認（CDB固有） • 表領域およびデータファイルの確認 • 論理的な設定の確認 • 初期化パラメータの確認 • メモリー割り当ての確認 • ユーザー管理【PDB】 • データベースのステータス確認 • 物理的なファイルの配置確認 • 表領域およびデータファイルの確認 • 論理的な設定の確認 • 初期化パラメータの確認 • ユーザー管理データベース作成後の主な確認事項および設定 Copyright © 2023, Oracle and/or its affiliates 35 SQL*Developer 「DBA機能」を利⽤して確認します

Slide 36

Slide 36 text

Slide 37

Slide 37 text

データベース・インスタンスに、メモリー割り当て、制限、デフォルト値、必要なファイルやディレクトリなどを指定データベースは、インスタンスを起動するために、インスタンス構成パラメータ（初期化パラメータ）を以下のいずれかのファイルより読み込む • デフォルトでは $ORACLE_HOME/dba (Windowsの場合は $ORACLE_HOME¥database) ディレクトリに存在 • 初期化パラメータ SPFILE に指定していれば、SPFILE はサーバー上のどこでも配置できる PFILEとSPFILEの相互変換 SPFILE利⽤時のパラメータの動的な変更初期化パラメータ Copyright © 2023, Oracle and/or its affiliates 37 初期化パラメータ・ファイル（PFILE）: テキスト形式（テキスト・エディターで変更可能）パラメータ変更にはインスタンスの再起動が必要サーバー・パラメータ・ファイル（SPFILE）: バイナリ形式（テキスト・エディターで変更不可）動的にパラメータ値を変更可能 Database Configuration Assistant (DBCA)はPFILEからSPFILEを作成 CREATE SPFILE='/u01/oracle/dbs/test_spfile.ora' FROM PFILE='/u01/oracle/dbs/test_init.ora'; CREATE PFILE='/u01/oracle/dbs/test_init.ora' FROM SPFILE ='/u01/oracle/dbs/test_spfile.ora '; ALTER SYSTEM SET SEC_MAX_FAILED_LOGIN_ATTEMPTS=3 COMMENT='Reduce from 10 for tighter security.' SCOPE=SPFILE; SCOPE=SPFILE SPFILEにのみ変更が適⽤。次回インスタンス起動時に有効 SCOPE=MEMORY 現⾏インスタンスに即時適⽤。次回インスタンス起動時には変更が無視 SCOPE=BOTH SPFILEと現⾏インスタンスに即時適⽤。次回インスタンス起動時も有効

Slide 38

Slide 38 text

PDBで変更可能な初期化パラメータ • 上記の動的パフォーマンス・ビューには「ISPDB_MODIFIABLE」というカラムがあり、この値が「TRUE」のものが PDBで変更可能なもの • ALTER SYSTEM ⽂でPDBを動的に変更可能。 • 構⽂も同じだが、SCOPE=BOTH 、 SCOPE = SPFILE の初期化パラメータに対する値はPDBのXMLメタデータに保存される。（SPFILEではない）参考）初期化パラメータ関連の動的パフォーマンス・ビューとPDBでの変更可否 Copyright © 2023, Oracle and/or its affiliates 38 V$PARAMETER セッションに現在有効になっている初期化パラメータの情報を⽰す V$SYSTEM_PARAMETER インスタンスに現在有効になっている初期化パラメータの情報を⽰す SELECT NAME FROM V$SYSTEM_PARAMETER WHERE ISPDB_MODIFIABLE = 'TRUE' ORDER BY NAME;

Slide 39

Slide 39 text

Slide 40

Slide 40 text

【CDB】 • データベースのステータス確認 • データベース運⽤モードの確認 • PDBの管理（CDB固有） • 物理的なファイルの配置確認 • 制御ファイルの確認（CDB固有） • REDOログ・ファイルの確認（CDB固有） • 表領域およびデータファイルの確認 • 論理的な設定の確認 • 初期化パラメータの確認 • メモリー割り当ての確認 • ユーザー管理【PDB】 • データベースのステータス確認 • 物理的なファイルの配置確認 • 表領域およびデータファイルの確認 • 論理的な設定の確認 • 初期化パラメータの確認 • ユーザー管理データベース作成後の主な確認事項および設定 Copyright © 2023, Oracle and/or its affiliates 40 SQL*Developer 「DBA機能」を利⽤して確認します

Slide 41

Slide 41 text

Slide 42

Slide 42 text

マルチテナント・アーキテクチャにおけるユーザー、およびユーザー設定で必要なもの共通ユーザー • 各コンテナ(ルートと各 PDB) に同じ名前で存在するユーザー • 共通ユーザーはCDB$ROOTへ接続して作成 • 初期化パラメータ common_user_prefix で接頭辞を定義可能デフォルトは C## ローカル・ユーザー • 特定の PDB のみに存在するユーザー (重複可能) • ローカル・ユーザーはそれぞれのPDBへ接続して作成 • PDB個別の管理ユーザも含まれる【ユーザー設定で必要なもの】 • ユーザー名 / パスワード • プロファイル • ロール、システム権限 • デフォルト表領域 / ⼀時表領域の指定【プロファイル】 • システム・リソースおよびパスワード制限の設定をまとめたもの • ユーザーは⼀度に1つのプロファイルのみを割り当てられる • ユーザー作成時に個別にプロファイルを指定しなければ、 DEFAULTプロファイルが適⽤【パスワード】 • パスワードの複雑度ファンクションがプロファイルに設定される • Oracle Base Database Service の場合、「ORA12C_STRONG_VERIFY_FUNCTION」が設定済み • （⼀時的に）無効にする場合は以下を実⾏データベース・ユーザー Copyright © 2023, Oracle and/or its affiliates 42 SYS/SYSTEM は特殊な共通ユーザローカル・ユーザー共通ユーザー重複可能 SCOTT SCOTT alter profile profile名 limit password_verify_function null;

Slide 43

Slide 43 text

Slide 44

Slide 44 text

Slide 45

Slide 45 text

Slide 46

Slide 46 text

Slide 47

Slide 47 text

Slide 48

Slide 48 text

Slide 49

Slide 49 text

Slide 50

Slide 50 text

Slide 51

Slide 51 text

Slide 52

Slide 52 text

Slide 53

Slide 53 text

Slide 54

Slide 54 text

Slide 55

Slide 55 text

Slide 56

Slide 56 text

Slide 57

Slide 57 text

Slide 58

Slide 58 text

Slide 59

Slide 59 text

Slide 60

Slide 60 text

• 表作成時に指定するもの • 表名 • 列名 (格納項⽬) • データ型、サイズ(VARCHAR2型、DATE型、NUMBER型) • 制約(PRIMARY KEY制約、NOT NULL制約など) 表の作成について Copyright © 2023, Oracle and/or its affiliates 60 EMP_ID LAST_NAME HIRE_DATE SALARY 1 ⽥中 03-04-01 700 2 鈴⽊ 05-06-01 600 3 佐藤 07-08-01 500 4 伊藤 07-09-01 450 EMPLOYEES 表名列 NUMBER型数字 VARCHAR2型⽂字 DATE型⽇付

Slide 61

Slide 61 text

Slide 62

Slide 62 text

Slide 63

Slide 63 text

Copyright © 2023, Oracle and/or its affiliates 63 表の作成︓設定した表定義列名データ型サイズ NOT NULL PRIMARY KEY EMPLOYEE_ID NUMBER 6 ○ ○ FIRST_NAME VARCHAR2 20 LAST_NAME VARCHAR2 25 ○ EMAIL VARCHAR2 25 ○ PHONE_NUMBER VARCHAR2 20 HIRE_DATE DATE ○ JOB_ID VARCHAR2 10 ○ SALARY NUMBER 8 COMMISSION_PCT NUMBER 2 MANAGER_ID NUMBER 6 DEPARTMENT_ID NUMBER 4 • 表名︓EMPLOYEES • スキーマ︓test1 • 表領域︓USERS

Slide 64

Slide 64 text

Slide 65

Slide 65 text

Copyright © 2023, Oracle and/or its affiliates 65 列名データ型サイズ NOT NULL PRIMARY KEY DEPARTMENT_ID NUMBER 4 ○ ○ DEPARTMENT_NAME VARCHAR2 30 ○ MANAGER_ID NUMBER 6 LOCATION_ID NUMBER 4 表を追加︓DEPARTMENTS表の作成先ほどと同様の⽅法で、DEPARTMENTS表を作成しましょう以下の内容でDEPARTMENTS表を作成しています • 表名︓DEPARTMENTS • スキーマ︓test1 • 表領域︓USERS

Slide 66

Slide 66 text

Copyright © 2023, Oracle and/or its affiliates 66 表と表の関連付け先ほど作成したEMPLOYEES表とDEPARTMENTS表にリレーションシップ(関連付け)を⾏います EMP_ID LAST_NAME DEPT_ID 1 ⽥中 10 2 鈴⽊ 20 3 佐藤 30 EMPLOYEES表 DEPT_ID DEPT_NAME LOCATION_ID 10 営業部 1700 20 マーケティング部 1800 30 ⼈事部 2400 DEPARTMENTS表リレーションシップ

Slide 67

Slide 67 text

Slide 68

Slide 68 text

Slide 69

Slide 69 text

Slide 70

Slide 70 text

Copyright © 2023, Oracle and/or its affiliates 70 Data Pumpのエクスポート・インポートを使⽤してHRスキーマからTEST1スキーマに表の移動を⾏います Oracle Data Pumpの使⽤ローカル・ストレージダンプファイルエクスポートインポートデータベース・サーバーローカル・ストレージ上にダンプ・ファイルを配置 expdp impdp ① ③ ② 今回の作業⼿順 ①データベースに接続 ②HRスキーマから表をダンプファイルにエクスポート ③ダンプファイルからTEST1スキーマに表をインポート

Slide 71

Slide 71 text

Oracle Data Pump • Oracle Databaseに格納されているデータおよびメタデータをOS上のファイル(ダンプ・ファイル)に抽出(Export)し、他の Oracle Databaseに⾼速に読み込ませる(Import)ためのユーティリティ • 論理バックアップ Export/Importの種類 • Data Pump • Oracle Database 10g から提供 • Oracle Database サーバー側で実⾏することによるメリット • パラレル化による⾼速処理 • APIによる呼び出し可能 • ジョブ管理・状況監視が可能 • 停⽌・再開が可能 • オリジナルのExport/Import • Oracle Database 11g より原則サポートされない参考）Oracle Data Pump Copyright © 2023, Oracle and/or its affiliates 71 Oracle Database PL/SQL Package Oracle Net Oracle Net Access Driver ダンプ・ファイル expdp impdp Export Utility Import Utility ダンプ・ファイルはサーバ側に格納 (DIRECTORY オブジェクト配下) EXPDP/IMPDP の実⾏エンジンサーバークライアント

Slide 72

Slide 72 text

• 今回はexpdp/impdpの実⾏にSYSTEMユーザーを使⽤します • HRユーザーの設定と同じように、あらかじめSYSTEM ユーザーのPDB接続を済ませておきます(ロールはデフォルト) • DBA機能も使えるよう接続をします ①Data Pumpの使⽤︓systemユーザーでの接続 Copyright © 2023, Oracle and/or its affiliates 72 先ほどのHRユーザーのPDB接続の情報を使いつつ接続を作成します変更点接続名:任意の名前ユーザー名︓SYSTEM パスワード︓データベース作成時に設定したものロール︓デフォルト PDB(systemユーザー)

Slide 73

Slide 73 text

Slide 74

Slide 74 text

Slide 75

Slide 75 text

Slide 76

Slide 76 text

Slide 77

Slide 77 text

Slide 78

Slide 78 text

Slide 79

Slide 79 text

Slide 80

Slide 80 text

Slide 81

Slide 81 text

Slide 82

Slide 82 text

Slide 83

Slide 83 text

Slide 84

Slide 84 text

Slide 85

Slide 85 text

Copyright © 2023, Oracle and/or its affiliates 85 1. SQL Developerについての理解 2. データベース作成後の主な設定についての理解 • CDB/PDBでの確認事項 3. データベースユーザーについての理解 • 共通ユーザーとローカルユーザーの違い 4. 表定義とデータの確認 • HRユーザーにて定義の確認 5. 表の作成⽅法についての理解 6. Data Pumpについての理解まとめ

Slide 86

Slide 86 text

No content