# 06ex1. Oracle ________________________________________ Oracle Databaseオンライン・ドキュメント 12c リリース1 (12.1) https://docs.oracle.com/cd/E57425_01/121/index.htm 10 Windows環境におけるデータベース・ユーザーの認証 https://docs.oracle.com/cd/F19136_01/ntqrf/authenticating-database-users-with-windows.html ________________________________________ ## 1. Install and Settings ________________________________________ ### 1.0. WindowsとLinuxの初期構成の違い 設定 |Windows |Linux --------------------------|------------------------|----------------------------- hosts |自分のホスト名はない想定|自分のホスト名がある想定 OSユーザのoracle |ログイン不可 |ログイン可能 想定される起動操作 |サービスで管理 |oracleユーザから起動操作 ORACLE_HOMEなど |レジストリに記載 |oracleユーザの.bash_profile DB |自動起動 |未起動 Listener(※1) |自動起動 |未起動 listener.ora |存在する(localhost) |存在しない 別マシンからのsqlplusなど |不可能(※2) |可能(※1)(※2) sqlnet.ora |存在する |存在しない 使用Namingメソッド |未構成 |未構成 AUTHENTICATION_SERVICES |nts(※3) |未構成(all)(※3) ※1 接続関連は「06ex1.1. ログイン、リスナ、TNS、CDB・PDB」も参照の事 ※2 Windowsはlistener.oraがlocalhostリスンで初期構成、linuxはlistener.oraが存在しないため ※3 言い換えると、どちらもローカル接続時のOS認証接続が有効 ________________________________________ ### 1.1.A. Install for Windows（Listenerを手動構成） ```text 1. 公式サイトにログインしてダウンロード 2. setup.exe 1. セキュリティ：不要 2. Gridインスト：データーベース・ソフトウェアのみ 3. インストール：単一インスタンス 4. 製品言語 ：日本語 5. エディション：Enterprise 6. OSユーザ ：oracle/oracle 7. インストール：C:\app\oracle\product\12.1.0\dbhome_1 3. Database Configuration Assistant 1. データベースの作成 2. デフォルトの構成でデータベースを作成します 選択 1. DB名 ：dev(単一インスタンスの場合、サービス名、SIDもこれ) 2. 記憶域 ：FS(ファイルシステム) 3. 場所 ：oradata 4. リカバリ ：fast_recovery_area 5. 文字コード：AL32UTF8 4. パスワード：dev 5. (oracle ：OSユーザ作成時に指定したパスワード(上記ならoracle)) 6. コンテナ ：off 4. コントロールパネル > 管理ツール > サービスを確認 1. 以下二つのサービスが登録されており、起動中か確認 - OracleService○○ - OracleTNSListener 2. TNSListenerがない場合、設定する - Net Configuration Assistant - リスナー構成 - 追加 - LISTENER, パスワード入力 - TCP - 標準ポート番号の1521を使用 - 以下が構成されlocalhostからのアクセスのみ可能となる - リスナー：LISTENER - サービス・ネーミング：oraclr_connection_data(IPC) - 再度サービスを確認し、OracleTNSListenerが実行中で追加されたか確認 3. LANからのアクセスの許可 - listener.oraのtcpのHOSTがlocalhostや127.0.0.1になっていたら、ローカルIPかコンピュータ名に変える ``` Windows対応 ```text Windows 10 Creators UpdateでSQL*Plusを使うと日本語が文字化けする件について https://blog.shibata.tech/entry/2017/05/26/220900 Windows(Version 1809)上でコンソール出力が正しく表示されない問題による製品への影響について http://www.hitachi-support.com/alert/us/HWS19-003/index.htm 1. NLS_LANG=JAPANESE_JAPAN.JA16SJISTILDE 2. 上記設定してもWindows10でsqlplusで文字化けする場合 - cmdのレガシコンソールを使用する有効化 - 根本解決する場合：ver. 1703 → KB4020102 - 根本解決する場合：ver. 1809 → KB4490481 ``` Directory (Windowsはレジストリに情報がある) Win directory |Path ----------------|--------------------------------------------- Oracle Inventory|c:\Program Files\Oracle\Inventory ORACLE_BASE |c:\app\oracle ORACLE_HOME |c:\app\oracle\product\\dbhome_1 - ________________________________________ ### 1.1.B. Install for linux（Oracleユーザでリスナを起動） Databaseインストレーション・ガイドfor Linux https://docs.oracle.com/cd/F19136_01/ladbi/index.html Oracle Linux: A better alternative to CentOS https://linux.oracle.com/switch/centos/ Note ```text 1. OUI上での設定は、Windowsと同じでOK 2. sqlplusは履歴機能がないのでrlwrap併用で対応。.bash_profileにエイリアス 3. リスナーは起動していない。oracleもOS再起動後は起動していない 4. lsnrctlをstart後にOracle Databaseそのものを再起動しないと、listenerが機能するまで約60秒待つ必要がある 5. Linux版はoracleもlistenerも自動起動が用意されていない 6. ハードウェアや事前要件は以下の通り 20GB storage 2GB memory 3GB swap x window Oracle Linux互換 ``` X window settings ```bash # e.g. vmhost=windows & vm=linux, tera term & vcxsrv # install vcxsrv to windows # start vcxsrv by XLaunch # tera term menu > setup > ssh forwarding > check x forwarding # tera term menu > setup > save setup... # restart tera term sudo yum -y install xorg-x11-xauth.x86_64 xorg-x11-server-utils.x86_64 sudo yum -y install xeyes # re-login xeyes ``` rlwrap ```bash sudo yum -y install wget # wget http://download.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm wget https://ftp.riken.jp/Linux/fedora/epel/epel-release-latest-7.noarch.rpm sudo rpm -ivh epel-release-latest-7.noarch.rpm sudo yum -y install epel-release sudo yum -y install readline-devel sudo yum -y install rlwrap ``` Centos to oracle linux ```bash # sudo curl -O https://linux.oracle.com/switch/centos2ol.sh curl -O https://raw.githubusercontent.com/oracle/centos2ol/main/centos2ol.sh # 確認 cat centos2ol.sh sudo bash centos2ol.sh sudo yum -y distro-sync ``` Pre install ```bash # sudo yum -y install oracle-database-preinstall-18c sudo yum -y install oracle-database-preinstall-19c sudo yum -y update ``` Install by runInstaller ```bash sudo passwd oracle # oracle # Login oracle official page, and download LINUX.X64_180000_db_home.zip # cp /home/vagrant/vagrant_shared/LINUX.X64_180000_db_home.zip /tmp # sudo mkdir -p /u01/app/oracle/product/18.0.0/dbhome_1 cp /home/vagrant/vagrant_shared/LINUX.X64_193000_db_home.zip /tmp sudo mkdir -p /u01/app/oracle/product/19.3.0/dbhome_1 sudo chown -R oracle:oinstall /u01 su - oracle # oracle user # cd /u01/app/oracle/product/18.0.0/dbhome_1 cd /u01/app/oracle/product/19.3.0/dbhome_1 # unzip -q /tmp/LINUX.X64_180000_db_home.zip unzip -q /tmp/LINUX.X64_193000_db_home.zip ./runInstaller # OUI ## Set Up Software Only ## Single instance database installation ## Enterprise Edition ## /u01/app/oracle ## /u01/app/oraInventory, oinstall ## dba, oper, backupdba, dgdba, kmdba, racdba ## none ## (Swap Size warning) Ignore all > Next > Yes ## Install ## If show "Execute Configuration Script@..." ## open another terminal ### sudo /u01/app/oraInventory/orainstRoot.sh #### sudo /u01/app/oracle/product/18.0.0/dbhome_1/root.sh ### sudo /u01/app/oracle/product/19.3.0/dbhome_1/root.sh ### Enter the full pathname of the local bin directory: [/usr/local/bin]: #### Do you want to setup Oracle Trace File Analyzer (TFA) now ? yes|[no] : ### exit ## ok ## Close ``` vi ~/.bash_profile ```text # User specific environment and startup programs ORACLE_BASE=/u01/app/oracle # ORACLE_HOME=/u01/app/oracle/product/18.0.0/dbhome_1 ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1 ORACLE_SID=dev NLS_LANG=JAPANESE_JAPAN.AL32UTF8 PATH=<:>$ORACLE_HOME/bin export ORACLE_BASE export ORACLE_HOME export ORACLE_SID export NLS_LANG export PATH alias sqlplus='rlwrap -pRed sqlplus' ``` Oracle Linux化した後、Guest AdditionのKernelソースが見つからない場合の対応 ```bash # 通常と異なり、package名がkernel-uekのように、-uekと付くので注意 # https://yum.oracle.com/index.html # https://yum.oracle.com/repo/OracleLinux/OL7/UEKR6/x86_64/ sudo yum install kernel-uek kernel-uek-devel -y ``` Create database and run listener ```text # oracle user exit # vagrant user exit # re-login su - oracle dbca # create a database ## Global database name > dev ## Storage type > File System ## Database file location > {ORACLE_BASE}/ora_data/{DB_UNIQUE_NAME} ## Fast Recovery Area > {ORACLE_BASE}/fast_recovery_area/{DB_UNIQUE_NAME} ## Database character set > AL32UTF8 ## Administrative password > dev ## Create as Container database > no check # Next # DBT-06208 ... > Yes # Finish # Close lsnrctl # start # quit ``` Restart Oracle ```text # re-login su - oracle lsnrctl # start # quit sqlplus system/manager as sysdba # SHUTDOWN IMMEDIATE # STARTUP ``` Directory Linux directory |Path ----------------|--------------------------------------------- Oracle Inventory|/u01/app/oraInventory ORACLE_BASE |/u01/app/oracle ORACLE_HOME |/u01/app/oracle/product/19.3.0/dbhome_1 - ________________________________________ ### 1.2. ORACLE_BASE、ORACLE_HOME ORACLE_BASE |OS |通常の想定 |--------|--------------- |Windows |C:\app\oracle |Linux |/u01/app/oracle - ディレクトリ詳細 パス |内容 -------------------------------------------|--------------------------------------------- [ORACLE_BASE]/admin |普段は考慮不要 [ORACLE_BASE]/audit |普段は考慮不要 [ORACLE_BASE]/cfgtoollogs |普段は考慮不要。各種設定ツール使用時のログ [ORACLE_BASE]/checkpoints |普段は考慮不要 [ORACLE_BASE]/diag |普段は考慮不要。不具合発生時のログが出力される場所 [ORACLE_BASE]/fast_recovery_area |普段は考慮不要。リカバリ用のトランザクションログが保存される場所(REDOログ、アーカイブログなどのデフォルト保存先) [ORACLE_BASE]/oradata |datafileなど [ORACLE_BASE]/product/バージョン/dbhome_1/ |ORACLE_HOME。実行ファイルや各種設定の一式 - ________________________________________ ### 2. ユーザ、ロール、ユーザ権限 ________________________________________ ### 2.1. Add Userサンプル 1. sqlplus system/manager as sysdba 0. show con_name - alter session set container = orclpdb 1. create tablespace foo datafile 'foo.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M; 2. create user foo identified by bar default tablespace foo; 3. grant connect, resource, create view, unlimited tablespace to foo; 4. quit 2. sqlplus foo/bar@localhost:1521/dev 1. create table accounts(id number(18,0), name varchar2(255), primary key(id)); 2. select table_name from user_tables; 3. insert into accounts values(1, 'アリス'); 4. insert into accounts values(2, 'ボブ'); 5. select * from accounts; 3. quit その他サンプル（bar用） ```text create table fruits(id number(18,0), name varchar2(255), primary key(id)); insert into fruits values(1, 'アップル'); insert into fruits values(2, 'バナナ'); select * from fruits; ``` 7.3.1 SYSおよびSYSTEMユーザー https://docs.oracle.com/cd/E57425_01/121/ADMQS/GUID-CF1CD853-AF15-41EC-BC80-61918C73FDB5.htm 7.3.2 SYSDBAおよびSYSOPERシステム権限 https://docs.oracle.com/cd/E57425_01/121/ADMQS/GUID-2033E766-8FE6-4FBA-97E0-2607B083FA2C.htm 管理権限 https://docs.oracle.com/cd/E16338_01/server.112/b56301/dba.htm#i1006554 sysdba、DBAロール - as sysdbaは、（通常は）ローカルでのみ利用可能なOS認証（またはパスワード・ファイル認証）による（通常は）SYSユーザでログインする手段である - DBがオープンしていない状態でも可能な認証=DB内のユーザ・パス情報に依存しない=OS認証またはパスワード・ファイル認証、である - OS認証の条件を満たしている場合、パスワード・ファイル認証されずにOS認証され、管理ユーザ=SYSユーザでログインする扱いになる - 典型的な状況では「ローカルでas sysdba認証」=「OS認証」 - SYSユーザは最上位権限かつdbmsの操作用に存在しているユーザである - このユーザの権限やスキーマを手動変更してはいけない - DBAロールは、定義済みロールの1つである - 自他全てのユーザ・ロール・スキーマ全般のDDLやDML操作は全て可能 - dbms操作的な操作（起動や停止等）は含まれていない（ロール機能自体がDBの中に存在しているため、ある意味当たり前である） sys以外にsysdbaを付与した場合の挙動 - そもそもとしてOS認証された場合、そのユーザでログインされない - sys以外にsysdbaを付与する＝パスワード・ファイルへ追記する - そのユーザでログインすると、権限はそのユーザの権限になる - ただし、作業スキーマはsysになる（ALTER sessionしない限り、自分のスキーマにならない） ________________________________________ ### 2.2. Drop Userサンプル ```sql DROP USER mwrep CASCADE; ``` ________________________________________ ### 2.3. デフォルトロール ```text dba unlimited tablespace connect resource create view create synonym debug any procedure debug connect session ``` ________________________________________ ## 3. サーバパラメータ ________________________________________ ### 3.1. 初期化パラメータ 初期化パラメータ － ＠IT https://www.atmarkit.co.jp/fdb/ref/ref_oracle/param.html サーバパラメータファイル（spfile） 項目 |典型的な例 --------|------------------------------------ Windows |%ORACLE_HOME%\database\SPFILE.ora Linux |$ORACLE_HOME/dbs/spfile.ora ※1 旧形式として、pfileがある。非推奨 各種確認 項目 |典型的な例 -----------|------------------------------------ spfileか |SELECT value FROM v$parameter WHERE name = 'spfile'; spfile内容 |SELECT * FROM v$spparameter WHERE value IS NOT NULL; spfile変更 |ALTER SYSTEM SET = '...' SCOPE=SPFILE; ※ pfileの場合、ALTER SYSTEMではファイルは書き変わらない ________________________________________ ## 4. データディクショナリ ________________________________________ dba_users ```sql SELECT username , lock_date , expiry_date , default_tablespace , created , default_collation FROM dba_users WHERE oracle_maintained <> 'Y' AND username <> 'EMULATION' ORDER BY username; ``` all_tables ```sql SELECT owner , table_name , tablespace_name , num_rows , last_analyzed , default_collation FROM all_tables WHERE owner = 'FOO' ORDER BY owner , table_name; ``` dba_data_files ```sql SELECT file_name , tablespace_name , round(bytes / 1073741824, 1) AS bytes_gb , autoextensible , round(maxbytes / 1073741824, 1) AS maxbyte_gb FROM dba_data_files ORDER BY file_name; ``` dba_temp_files ```sql SELECT * FROM dba_temp_files; ``` dba_sys_privs, dba_role_privs ```sql SELECT * FROM dba_sys_privs WHERE grantee = 'DBA'; SELECT * FROM dba_sys_privs WHERE grantee IN ('CONNECT', 'RESOURCE') ORDER BY grantee; SELECT privs.* FROM ( SELECT grantee, privilege, NULL AS granted_role, admin_option FROM dba_sys_privs UNION ALL SELECT grantee, NULL, granted_role, admin_option FROM dba_role_privs ) privs WHERE privs.grantee = 'MWREP'; ``` プロシージャの最終更新日の取得 ```sql SELECT p.object_name , p.object_type , p.procedure_name , o.last_ddl_time , o.status FROM user_procedures p , user_objects o WHERE p.object_name = o.object_name AND o.last_ddl_time >= '2019/12/10 20:00:00' ORDER BY last_ddl_time ``` ________________________________________ ## 5. 表領域とDATAFILE ________________________________________ DATAFILEの仕様 ```text 表領域のDATAFILEは、指定しなければOracle Managed Filesになり、表領域削除時に勝手に消える。 ``` UNDO/TEMPORARY表領域の再作成サンプル ```sql -- 表領域(ダミー)の作成 CREATE TEMPORARY TABLESPACE tempdummy; CREATE UNDO TABLESPACE undotbs2; -- 表領域(ダミー)へ切り替え ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tempdummy; ALTER SYSTEM SET undo_tablespace = 'UNDOTBS2'; -- 表領域の削除 -- システム管理表領域&データファイルは INCLUDING CONTENTS AND DATAFILES; が無くてもよい。 DROP TABLESPACE temp; DROP TABLESPACE undotbs1; -- 表領域の再作成 CREATE TEMPORARY TABLESPACE temp; CREATE UNDO TABLESPACE undotbs1; -- 表領域の切り替え ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp; ALTER SYSTEM SET undo_tablespace = 'UNDOTBS1'; -- UNDO表領域(ダミー)の削除 DROP TABLESPACE tempdummy; DROP TABLESPACE undotbs2; ``` DATAFILE ```sql -- リサイズ ALTER DATABASE DATAFILE 'C:\APP\ORACLE\ORADATA\DEV\DATAFILE\O1_MF_USERS_GQGOF8BV_.DBF' RESIZE 50M; -- 削除 ALTER TABLESPACE USERS drop DATAFILE 'D:\oracledatafile\USERS2.DBF'; -- 切り替え ALTER TABLESPACE USERS OFFLINE; -- エクスプローラでファイル移動 ALTER TABLESPACE USERS RENAME DATAFILE 'C:\APP\ORACLE\ORADATA\DEV\DATAFILE\O1_MF_USERS_GQGOF8BV_.DBF' TO 'D:\oracledatafile\O1_MF_USERS_GQGOF8BV_.DBF' ALTER TABLESPACE USERS ONLINE; -- 1DATAFILE最大4194303(2^22 - 1)問題対応 ALTER TABLESPACE USERS ADD DATAFILE 'D:\oracledatafile\USERS2.DBF' SIZE 30G AUTOEXTEND ON NEXT 100M; ``` ________________________________________ ## 6. 統計情報・マテビュー ________________________________________ 統計情報の再計算(sqlplusから実行) ```sql BEGIN DBMS_STATS.GATHER_SCHEMA_STATS ( OWNNAME => 'FOO' ,OPTIONS => 'GATHER' ); END; / ``` MaterializedViewのリフレッシュ ```sql -- SQLPlusから下記実行 execute dbms_mview.refresh(''); ``` ________________________________________ ## 7. Create Database link & synonim ________________________________________ ```sql # from system/dev grant create database link, create synonym, drop synonym to foo; # from foo/bar CREATE DATABASE LINK foo2bar CONNECT TO bar IDENTIFIED BY baz USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.3.251)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=dev)))' # from foo/bar CREATE SYNONYM fruits FOR fruits@foo2bar; ```