12cから増えたオンライン操作をOracle SE2で試した
Oracle 12cの新機能のオンライン操作をOracle Standard Edition2でできるか確認したのでメモ。
↓を基にいろいろ確認した。
- バージョンはOracle 12.1.0.2 SE2
オンラインでのデータファイル移動 結果: ×
これはもちろんできません。
alter database move datafile '/opt/app/oracle/oradata/soe.dbf' to '/opt/app/oracle/oradata/testdb/soe.dbf'; 行1でエラーが発生しました。: ORA-00439: 機能は有効ではありません: online move datafile
オンライン実行可能なDDLの強化
制約の削除 結果:×
SQL> ALTER TABLE aa DROP CONSTRAINT aapk ONLINE; ALTER TABLE aa DROP CONSTRAINT aapk ONLINE * 行1でエラーが発生しました。: ORA-00439: 機能は有効ではありません: Online Index Build
索引の使用禁止 結果: ×
SQL> ALTER INDEX aa_idx UNUSABLE ONLINE; ALTER INDEX aa_idx UNUSABLE ONLINE * 行1でエラーが発生しました。: ORA-00439: 機能は有効ではありません: Online Index Build
索引の削除 結果: ○
SQL> DROP INDEX idx3 ONLINE; 索引が削除されました。
列の未使用マーク付け 結果: ○
SQL> ALTER TABLE aa SET UNUSED COLUMN id2 ONLINE; 表が変更されました。
というわけで、もちろんほとんど不可でしたが例外的に索引の削除
と列の未使用マーク付け
はONLINE
句をつけても動作するみたい。
実際にオンラインになってるのかDROP INDEX
をSQL_TRACEで確認してみた。
- ONLINE句あり
LOCK TABLE FOR INDEX "IDX3" IN ROW SHARE MODE NOWAIT drop index IDX3 online ・ ・
- ONLINE句なし
LOCK TABLE FOR INDEX "IDX2" IN EXCLUSIVE MODE NOWAIT drop index IDX2 ・ ・
ONLINE
句ありではロックモードをIN ROW SHARE MODE
で取得しているため更新は可能。
ONLINE
句なしではロックモードをIN EXCLUSIVE MODE
で取得しているため更新は不可。
ちゃんとオンラインの動作になっている模様。
まとめ
OracleのStandard Editionで一切使えなかったオンライン処理が一部のみできるようになったみたい。
しかし、両処理ともわざわざオンライン処理でなくてもすぐ完了するのでよっぽどトラフィックがないと使いどころないな。。。
って、よっぽどトラフィックあればそもそもSE2じゃないな。。
Oracle database 12cマルチスレッドを試してみた
12cからの新機能のプロセスのマルチスレッド化を試してみました。
今回はバックグラウンドプロセスではなく専用サーバのスレッド化についてです。
環境はOracle database 12.1.0.2 SE2
マルチスレッドの有効化
初期化パラメータのTHREADED_EXECUTION
とUSE_DEDICATED_BROKER
をtrueにしてOracleの再起動する。
SQL> show parameter THREADED_EXECUTION NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ threaded_execution boolean FALSE SQL> show parameter USE_DEDICATED_BROKER NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ use_dedicated_broker boolean FALSE
sqlplus / as sysdba SQL> alter system set THREADED_EXECUTION=true scope=spfile; SQL> alter system set USE_DEDICATED_BROKER=true; SQL> shutdown immediate; SQL> startup
この時点でOS認証でのログインができなくなるので注意。
パスワードファイルを作成しておいて、これ以降はパスワード認証でログインする。
参考:
THREADED_EXECUTIONでOracleインスタンスをマルチスレッド構成に (コーソル DatabaseエンジニアのBlog)
USE_DEDICATED_BROKERをtrueにすることによって、リスナーは接続を専用サーバープロセスではなく専用接続ブローカに渡してそのブローカが専用サーバーを起動するようになる。
ブローカーの設定は初期化パラメーターCONNECTION_BROKERS
で設定可能。
デフォルトは専用接続ブローカが1となっている。
SQL> show parameter CONNECTION_BROKERS NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ connection_brokers string ((TYPE=DEDICATED)(BROKERS=1)), ((TYPE=EMON)(BROKERS=1))
外部接続を有効にするためにlistener.oraにDEDICATED_THROUGH_BROKER_(リスナー名)=on
追記してリスタート。
vim listener.ora DEDICATED_THROUGH_BROKER_listener=on lsnrctl stop lsnrctl start
リスナーを起動して情報を確認すると、DRCPと同様に専用接続ブローカ(N000)が起動していることがわかる。
$ lsnrctl service インスタンス"orcl"、状態READYには、このサービスに対する2件のハンドラがあります... ハンドラ: "N000" 確立:0 拒否:0 状態:ready CMON <machine: de8f124abb2c, pid: 18988_19012> (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=37710)) "DEDICATED" 確立:0 拒否:0 状態:ready
これでプロセスのスレッド化の設定は完了。
マルチスレッドになっているか確認
複数の端末からsqlplusで接続して確認してみる。
専用サーバのマルチスレッドの場合、
SPIDが同じ値になっているのでOS側で一つのプロセスで動作しているのがわかる。
そして、execution_typeがTHREADとなる。
SQL> select p.PID,p.SPID,p.STID,p.execution_type,p.PNAME,p.PROGRAM,s.program from v$process p join v$session s on s.PADDR = p.ADDR order by 1; PID SPID STID EXECUTION_ PNAME PROGRAM PROGRAM ---------- ------ ------ ---------- ----- ----------- -------------------------- 37 18988 24782 THREAD oracle@host sqlplus@host (TNS V1-V3) 38 18988 24783 THREAD oracle@host sqlplus@host (TNS V1-V3) 39 18988 24784 THREAD oracle@host sqlplus@host (TNS V1-V3)
通常の専用サーバ接続の場合、
SPIDのプロセス番号違っているのでOS側で接続毎にプロセス生成しているのがわかる。
そして、execution_typeがPROCESSとなっている。
PID SPID STID EXECUTION_ PNAME PROGRAM PROGRAM ---------- ------ ------ ---------- ----- ----------- -------------------------- 37 24798 24798 PROCESS oracle@host sqlplus@host (TNS V1-V3) 38 24800 24800 PROCESS oracle@host sqlplus@host (TNS V1-V3) 39 24802 24802 PROCESS oracle@host sqlplus@host (TNS V1-V3)
通常の専用サーバ接続、DRCPとマルチスレッドで比較
マルチスレッドにすることで新規プロセスの生成が抑えられるからログオン数とか上がるのかなと試してみた。
コネクションプール機能がある場合はたぶんマルチスレッドにメリットは少なそうだけど、新規接続切断を繰り返す場合のアプリケーションでは効果があると思う。
通常の専用サーバ接続、DRCPとマルチスレッドで比較してみた。
同時接続数5でトランザクションごとに新規接続、切断を繰り返すテストを実施。
実行時間は10分間で 5回実施した平均値を基に算出。
DRCPの設定はminsize=>30
ですべての接続がプールされているサーバで処理できる設定。
↓結果は具体的な数値はなしで相対的な数値です。
通常の専用サーバ接続を100とします。
専用サーバ接続 | マルチスレッド | DRCP | |
---|---|---|---|
ログオン回数 | 100 | 112 | 121 |
TPM | 100 | 114 | 123 |
TPS | 100 | 106 | 114 |
というように DRCP > 専用サーバマルチスレッド > 通常の専用サーバ接続 という結果になった。
専用サーバのマルチスレッドは通常の専用サーバ接続よりかは良さそうな結果だった。
Oracle Database Connect 2016(JPOUG) でDRCPについてLTしました
JPOUGで2回目のLTしました。
さすがに1回目より緊張しないかなーと思たけど、
500人以上の会場の規模にさすがにビビった。。。
Oracle常駐接続プーリングについて話させていただきました。
スライドの最後にも書いてますが、
12cからの新機能である専用サーバプロセスのマルチスレッド化が気になるので
これをDRCPと通常の専用サーバ接続とで比較したい。
最後にLTでの答えられなかった質問
質問:エチオピアの首都はどこですか?
回答:エチオピアの首都はアディスアベバ!!
↑の流れ
@yoku0825 LTにて、
— kentarokitagawa (@keny_lala) 2016年5月13日
得意技は?
世界の国の首都どこで答えられますっ!(キリッ
じゃー、エチオピアは?
わかりません。。。って流れですww
ORACLEでテーブルごとのDML回数を取得する
テーブルごとの1時間あたりのDML(INSERT,UPDATE,DELETE)の平均を取得するために考えた方法。
確認手段としてDBA_TAB_MODIFICATIONS
を使用する。
以下のように確認できる。
SELECT table_name,inserts,updates,deletes,to_char(timestamp,'YYYY-MM-DD HH24:MI:SS') as timestamp FROM dba_tab_modifications WHERE TABLE_OWNER in ('USER') and TABLE_NAME not like 'BIN$%'; TABLE_N INSERTS UPDATES DELETES TIMESTAMP ------- ---------- ---------- ---------- ------------------- TABLE00 868 2446 1974 2016-04-26 18:00:01 TABLE01 445 0 10 2016-04-26 17:15:28 TABLE02 1 1 0 2016-02-22 18:06:50 TABLE03 16635 0 16635 2016-04-26 18:00:01 TABLE04 6 0 4 2016-04-26 15:54:26 TABLE05 0 124 0 2016-04-26 18:00:01 TABLE06 335 3071 0 2016-04-26 18:00:01 TABLE07 12 0 0 2016-04-06 17:29:01 TABLE08 58694 0 40023 2016-04-26 18:00:01 TABLE09 131440 0 102863 2016-04-26 18:00:01
マニュアルによると、
前回、表の統計情報を収集した時点から変更されている
とあるので、統計情報を取得したタイミングで更新される模様。
これを定期的に更新してあげるには↓を実行する。
begin DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO; end;
そうすると実行したタイミングで更新がある場合はTIMESTAMP
が書き換えられ、更新回数が累積される。
前回との差分を比較することでDML回数がわかる。
1時間置き実行して結果をfluentdで送ったり、グラフ化することができる。
うちでは
、
1. 1時間置きにFLUSH_DATABASE_MONITORING_INFO
を実行
2. SELECT FROM dba_tab_modifications
を実行しMySQLに格納
3. Muninでグラフ化
みたいにしている。
直接グラフでもいいけど、MySQLに入れておけばSQLでいろいろ拡張やら調査が可能なので。
注意としては統計情報を取得したタイミング
で更新情報がリセットされるのでその時は差分を取得するのではなくその値を初期値にする。
MySQLで取得しているデータはこんな感じ↓
カラム名 | 説明 |
---|---|
TABLE_NAME | テーブル名 |
INSERTS | 前回との差分insert回数 |
SUM_INSERTS | 累積insert回数 |
UPDATES | 前回との差分update回数 |
SUM_UPDATES | 累積update回数 |
DELETES | 前回との差分delete回数 |
SUM_DELETES | 累積delete回数 |
TIMESTAMP | Oracle側の更新時間 |
+-----------+---------+-------------+---------+-------------+---------+-------------+---------------------+ | TABLE_NAME| INSERTS | SUM_INSERTS | UPDATES | SUM_UPDATES | DELETES | SUM_DELETES | TIMESTAMP | +-----------+---------+-------------+---------+-------------+---------+-------------+---------------------+ | TABLE00 | 14 | 88710 | 64 | 118695 | 0 | 0 | 2016-03-31 13:53:11 |↑ | TABLE00 | 5 | 88715 | 24 | 118719 | 0 | 0 | 2016-03-31 15:00:02 | | TABLE00 | 17 | 88732 | 57 | 118776 | 0 | 0 | 2016-03-31 15:57:26 | | TABLE00 | 18 | 88750 | 228 | 119004 | 0 | 0 | 2016-03-31 17:00:02 | | TABLE00 | 726 | 89476 | 325 | 119329 | 0 | 0 | 2016-03-31 18:00:01 |累積値 | TABLE00 | 483 | 89959 | 299 | 119628 | 0 | 0 | 2016-03-31 19:00:01 | | TABLE00 | 49 | 90008 | 11 | 119639 | 0 | 0 | 2016-03-31 20:00:01 | | TABLE00 | 8 | 90016 | 31 | 119670 | 0 | 0 | 2016-03-31 20:58:26 | | TABLE00 | 16 | 90032 | 22 | 119692 | 0 | 0 | 2016-03-31 22:00:01 |↓ | TABLE00 | 19 | 19 | 13 | 13 | 0 | 0 | 2016-03-31 23:00:01 |←統計情報が取得されたのでリセットされた | TABLE00 | 28 | 47 | 17 | 30 | 0 | 0 | 2016-03-31 23:59:41 |↑ | TABLE00 | 8 | 55 | 10 | 40 | 0 | 0 | 2016-04-01 01:00:01 | | TABLE00 | 7 | 62 | 5 | 45 | 0 | 0 | 2016-04-01 01:51:06 | | TABLE00 | 4 | 66 | 2 | 47 | 0 | 0 | 2016-04-01 02:42:32 | | TABLE00 | 2 | 68 | 2 | 49 | 0 | 0 | 2016-04-01 03:46:31 | | TABLE00 | 1 | 69 | 1 | 50 | 0 | 0 | 2016-04-01 04:25:24 |累積値 | TABLE00 | 4 | 73 | 4 | 54 | 0 | 0 | 2016-04-01 06:00:01 | | TABLE00 | 4 | 77 | 2 | 56 | 0 | 0 | 2016-04-01 06:36:07 | | TABLE00 | 8 | 85 | 2 | 58 | 0 | 0 | 2016-04-01 07:56:18 | | TABLE00 | 3 | 88 | 1 | 59 | 0 | 0 | 2016-04-01 08:46:48 |↓
OracleSERAC+ASMのRMANバックアップから非ASMのシングル環境へリストア
OracleSERAC+ASMのRMANバックアップから非ASM(ファイルシステム)のシングル環境へリストアしたのでメモ。
環境
- Oracle11.2.0.4
- DB_NAME:orcl
前提
- RMANバックアップは毎日0時にフルバックアップを取得(backupset plus archivelog)
delete noprompt obsolete;
で削除している。- RMANバックアップセットはファイルシステムに出力している。
- リカバリカタログは使用しない。制御ファイルで管理。
リストア手順
- 4/11の13:00までの不完全リカバリを行う。
別サーバを用意しOracleのインストールをする
↓を基にインストールまで行う。
PSUや個別パッチを適用している場合はRAC環境と同等にしておく。
初期化ファイル作成
vim $ORACLE_HOME/dbs/initorcl.ora db_name='orcl' processes = 300 audit_file_dest='/opt/app/oracle/admin/orcl/adump' audit_trail ='none' db_block_size=8192 db_domain='' db_recovery_file_dest='/opt/app/oracle/flash_recovery_area' db_recovery_file_dest_size=700G log_archive_dest_1='LOCATION=/opt/app/oracle/archivelog' diagnostic_dest='/opt/app/oracle' dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)' open_cursors=300 remote_login_passwordfile='EXCLUSIVE' undo_tablespace='UNDOTBS1' control_files = '/opt/app/oracle/control01.ctl' compatible='11.2.0.4.0' sga_target=5G pga_aggregate_target=1G
RMANから戻す
使用するもの
- 4/11 0:00に取得したフルバックアップ
- 4/11 0:00~4/11 13:00のアーカイブログ(すでにバックアップ済みであればアーカイブログのバックアップセット)
4/11 0:00に取得したバックアップを使用するので、4/11 0:00時点の制御ファイルしかない。
その制御ファイルをリストアすると4/11 0:00~4/11 13:00までのアーカイブ情報を保持していないことになる。
よって、4/11 0:00~4/11 13:00までのアーカイブをカタログ追加し、
RAC環境のlist backup
で確認した必要なアーカイブログをリストアしてあげる必要あり。
4/13 0:00 4/13 13:00 時系列--------------|--------------------------------------------------|-------------------- フルバックアップ ここに戻す 取得 ← この間のアーカイブログが必要 →
どのアーカイブログのバックアップが必要か順序番号を確認する
確認すると2016/04/12に取得したanr2qu6c_1_1
このバックアップが必要だということがわかる。
RMAN>list backup; ・ ・ BSキー サイズ デバイス・タイプ経過時間終了時間 ------- ---------- ----------- ------------ ------------------- 3407 11.79G DISK 00:01:06 2016/04/12 00:01:37 BPキー: 3407 ステータス: AVAILABLE 圧縮: NO タグ: TAG20160412T060030 ピース名: /backup/anr2qu6c_1_1 バックアップ・セット3407のアーカイブ・ログのリスト Thrd Seq Low SCN Low時間 Next SCN Next Time ---- ------- ---------- ------------------- ---------- --------- 1 46255 34194183819 2016/04/11 00:00:00 34194503257 2016/04/11 07:23:50 1 46256 34194503257 2016/04/11 07:23:50 34194522990 2016/04/11 10:24:11 1 46257 34194522990 2016/04/11 10:24:11 34194542840 2016/04/11 15:24:32 ・ ・ 2 17526 34194183858 2016/04/11 00:00:02 34194543930 2016/04/11 07:24:33 2 17527 34194543930 2016/04/11 07:24:33 34194632204 2016/04/11 12:30:12 2 17528 34194632204 2016/04/11 12:30:12 34195546508 2016/04/11 13:24:32 ・ ・ ・ ・
RAC環境に出力しているバックアップファイルを同じディレクトリを作成してにコピーし、配置する
mkdir -p /backup chown -R oracle. /backup mv (4/11のフルバックアップ) (4/12に取得したアーカイブログ[anr2qu6c_1_1]) /backup
初期化パラメータで指定したアーカイブログの出力先作成
mkdir -p /opt/app/oracle/archivelog
インスタンス起動
startup nomount;
制御ファイルリストア
RESTORE CONTROLFILE FROM '/backup/c-2096898227-20160411-00';
→4/11 0:00時点の制御ファイル
データベースマウント
ALTER DATABASE MOUNT;
制御ファイルで認識されていないアーカイブログのバックアップセットをカタログ追加
CATALOG BACKUPPIECE '/backup/anr2qu6c_1_1';
→4/11 0:00~4/11 13:00までのアーカイブログ
アーカイブログのリストア
run { set archivelog destination to '/opt/app/oracle/archivelog'; restore archivelog sequence between 46255 and 46257 thread 1; restore archivelog sequence between 17526 and 17528 thread 2; }
→4/11 0:00~4/11 13:00までのアーカイブを初期化ファイルのlog_archive_dest_1にリストアする。じゃないとリカバリできないみたい 。
リストア
- 本番でディスクグループ確認
SELECT FILE# AS "File/Grp#", NAME FROM V$DATAFILE; 1 +DATA/orcl/datafile/system.264.861799709 2 +DATA/orcl/datafile/sysaux.260.861799709 3 +DATA/orcl/datafile/undotbs1.261.861799709 4 +DATA/orcl/datafile/data.257.861799709 5 +DATA/orcl/datafile/idx.258.861799709 6 +DATA/orcl/datafile/undotbs2.262.861799709 7 +DATA/orcl/datafile/users.265.861799723 8 +DATA/orcl/datafile/perfstat.259.861799709
- ファイルシステムに置き換えてリストア
run { set newname for datafile 1 to '/opt/app/oracle/oradata/system1.dbf'; set newname for datafile 2 to '/opt/app/oracle/oradata/sysaux1.dbf'; set newname for datafile 3 to '/opt/app/oracle/oradata/undotbs1.dbf'; set newname for datafile 4 to '/opt/app/oracle/oradata/data1.dbf'; set newname for datafile 5 to '/opt/app/oracle/oradata/idx1.dbf'; set newname for datafile 6 to '/opt/app/oracle/oradata/undotbs2.dbf'; set newname for datafile 7 to '/opt/app/oracle/oradata/users1.dbf'; set newname for datafile 8 to '/opt/app/oracle/oradata/perfstat.dbf'; restore database; switch datafile all; }
リカバリ
run { SET UNTIL TIME "TO_DATE('2016-04-11 13:00:00','YYYY-MM-DD HH24:MI:SS')"; RECOVER DATABASE ; }
オンラインREDOログをリネームする
- RAC環境を確認
GROUP# THREAD# MEMBERS MEMBER BYTES SEQUENCE# STATUS ------ ------- ------- --------------------------------------------- ---------- ---------- ---------- 1 1 2 +DATA/onlinelog/group_1.257.861799869 524288000 46586 ACTIVE 1 1 2 +DATA/onlinelog/group_1.257.861799869 524288000 46586 ACTIVE 2 1 2 +DATA/onlinelog/group_2.258.861799869 524288000 46587 CURRENT 2 1 2 +DATA/onlinelog/group_2.258.861799871 524288000 46587 CURRENT 3 2 2 +DATA/onlinelog/group_3.259.861799873 524288000 17651 ACTIVE 3 2 2 +DATA/onlinelog/group_3.259.861799875 524288000 17651 ACTIVE 4 2 2 +DATA/onlinelog/group_4.260.861799875 524288000 17652 CURRENT 4 2 2 +DATA/onlinelog/group_4.260.861799875 524288000 17652 CURRENT
- オンラインREDO用のディレクトリ作成
mkdir -p /opt/app/oracle/redo
- リネームする
alter database rename file '+DATA/onlinelog/group_1.257.861799869' to '/opt/app/oracle/redo/redo01.log'; alter database rename file '+DATA/onlinelog/group_1.257.861799869' to '/opt/app/oracle/redo/redo11.log'; alter database rename file '+DATA/onlinelog/group_2.258.861799869' to '/opt/app/oracle/redo/redo02.log'; alter database rename file '+DATA/onlinelog/group_2.258.861799871' to '/opt/app/oracle/redo/redo12.log'; alter database rename file '+DATA/onlinelog/group_3.259.861799873' to '/opt/app/oracle/redo/redo03.log'; alter database rename file '+DATA/onlinelog/group_3.259.861799875' to '/opt/app/oracle/redo/redo13.log'; alter database rename file '+DATA/onlinelog/group_4.260.861799875' to '/opt/app/oracle/redo/redo04.log'; alter database rename file '+DATA/onlinelog/group_4.260.861799875' to '/opt/app/oracle/redo/redo14.log';
データベースオープン
ALTER DATABASE OPEN resetlogs;
この時点でデータベースにアクセス可能。。
一時表領域再作成
create temporary tablespace temp1 tempfile '/opt/app/oracle/oradata/temp11.dbf' size 10M; alter database default temporary tablespace temp1; drop tablespace temp; create temporary tablespace temp tempfile '/opt/app/oracle/oradata/temp02.dbf' size 5000M; alter database default temporary tablespace temp; drop tablespace temp1;
thread2のredolog消す
ALTER DATABASE DISABLE THREAD 2; alter database drop logfile group 6; alter database drop logfile group 7; alter database drop logfile group 8; alter database drop logfile group 9; alter database drop logfile group 10;
これで完了。おしまい。