kenken0807_DBメモ

つば九郎が好きなDBAです。Oracle Standard Editionでの運用やツールとかとかの備忘録。特に記載がない場合はoracle11gR2です。時々MySQL

MyDumperを使ってみた

MySQL

MySQLのデータインポートツールMyDumperを試したのでメモ。

  • mysqldump・・・データのエクスポート・インポートをシングルスレッドで実行
  • mydumper・・・データのエクスポート・インポートをパラレルスレッドで実行

エクスポートしたファイルごとにパラレルで実行する。デフォルトはテーブル単位でパラレル化。
巨大なテーブルがあればデータをチャンクごとに分けファイルを分散することで高速に実行してくれる。

インストール

  • yumでインストール
yum install https://twindb.com/twindb-release-latest.noarch.rpm
yum install mydumper
  • 確認
$ mydumper --version   
mydumper 0.6.2, built against MySQL 5.6.25

もし、libmysqlclient.so.18がないよ。って言われた場合はyumでMySQL5.6をインストール（5.1ではだめ）。
または、libmysqlclient.so.18がある場所にLD_LIBRARY_PATHで設定。

コマンド

  • mydumper・・・export用コマンド
  • myloader・・・import用コマンド

試す

DB容量：280G（内130Gが１つのテーブル）

  • export DB設定

    • MySQL5.6
    • stop slaveして実行する

  • import DB設定

    • MySQL5.6
    • --skip_innodb_doublewrite
    • innodb_flush_log_at_trx_commit=0
    • slow_query_log=OFF
    • log_bin=OFF
    • innodb_log_buffer_size=128M
    • innodb_log_file_size=1G
    • innodb_log_files_in_group = 3

EXPORT

  • 10スレッドでGZ圧縮してチャンクサイズ10000000行で取得。
    注意：exportはスキーマの指定はフルまたは1つのスキーマになってしまうので複数スキーマを指定することはできない。
    -Tオプションでテーブル単位で指定可能。information_schema.tablesで複数のスキーマから取得する方法を教わったのでそちらで実行。
time mydumper -u root -p 'xxxxxxx' -S /var/lib/mysql/mysql.sock -e                   
     -t 10 -c -o /tmp/dump_dir 
     -r 10000000 
     -s 5000000 
     -T $(mysql  
        -sse"set group_concat_max_len=10240; 
        select aa from  (select 1,group_concat(concat(table_schema,'.',table_name)) as aa
        from information_schema.tables where table_schema in
       ('main','sub1','sub2','sub3','sub4') group by 1) a")

real    43m41.139s
user    220m16.990s
sys     3m0.312s

約44分

  • １スレッドかつチャンク分けなしでやった場合は約4時間20分かかった。

出力ファイル確認

できたファイルはこんな感じで分かれている。

$ ls -ltr /tmp/dump_dir 
合計 45721788
・
・
-rw-rw-r-- 1 mysql mysql        679  6月  1 16:00 2016 main.table1.sql.gz
-rw-rw-r-- 1 mysql mysql        169  6月  1 16:00 2016 main.table2.sql.gz
-rw-rw-r-- 1 mysql mysql        185  6月  1 16:00 2016 main.table3.sql.gz
-rw-rw-r-- 1 mysql mysql      76932  6月  1 16:00 2016 main.table4.sql.gz
-rw-rw-r-- 1 mysql mysql     208327  6月  1 16:00 2016 main.table5.sql.gz
-rw-rw-r-- 1 mysql mysql    1505960  6月  1 16:00 2016 sub1.subtable1.sql.gz
-rw-rw-r-- 1 mysql mysql  768276450  6月  1 16:06 2016 sub1.subtable2.00000.sql.gz
-rw-rw-r-- 1 mysql mysql  750226866  6月  1 16:06 2016 sub1.subtable2.00001.sql.gz
-rw-rw-r-- 1 mysql mysql  770132442  6月  1 16:07 2016 sub1.subtable2.00002.sql.gz

通常はテーブル単位でのファイルだが、チャンク分けしていると連番でファイルも分かれている。

  • スレーブとして起動したい場合はmetadataファイルを確認するとポジション情報が記載してある
$ vim /tmp/dump_dir/metadata

  1 Started dump at: 2016-06-01 16:00:13
  2 SHOW MASTER STATUS:
  3         Log: mysql-bin.000275
  4         Pos: 120
  5
  6 SHOW SLAVE STATUS:
  7         Host: 192.168.1.2
  8         Log: mysql-bin.000438
  9         Pos: 944084446
 10
 11 Finished dump at: 2016-06-01 16:39:00

IMPORT

  • 16スレッドで実行
$ time myloader -d /tmp/dump_dir  -u root -p 'xxx' -S /var/lib/mysql/mysql.sock -t 16 

real    525m31.015s
user    299m14.828s
sys     3m8.467s

約8時間45分

  • チャンク分けなしのものを16スレッドで実行した場合。 結局全DB量の半分近くを占めているテーブルがシングル実行になるので遅い。
$ time myloader -d /tmp/dump_dir  -u root -p 'xxx' -S /var/lib/mysql/mysql.sock -t 16 

real    1541m14.572s
user    245m43.989s
sys     2m23.104s

約25時間

よって、チャンク分けをうまくすればmydumper結構使える。

注意

  • ibdata1のサイズが大きくなる。
    50%はinsert bufferが占めていた。セカンダリインデックスを後で貼るようにしてくれたらいいのにな。
 *8.1G*  6月  3 01:01 2016 ibdata1   こうなるので注意。
  • export時にテーブル指定でもimport時データベース作成してしてくれる。
    ファイル名の先頭を読んでcreate databaseする。↓のような感じ。
    main.table1.sql.gz → create database main;
    となるとデータベースごとにキャラクタセットが違うと危険かも・・・

OEM再作成のエラー対処方法

ORACLE SE

RMANで別ホストに移行した時にOEMの作成でいろいろ嵌ったのでメモ。

emca -config dbcontrol db -repos recreate実行時

パターン１

設定: ORA-01031: ???????-??????????
・
・
・
2014/10/09 9:58:40 oracle.sysman.emcp.util.GeneralUtil initSQLEngineLoacly
設定: SQLEngine created successfully and connected
2014/10/09 9:58:40 oracle.sysman.emcp.ParamsManager checkListenerStatusForDBControl
設定: ORA-01031: ???????-??????????
2014/10/09 9:58:40 oracle.sysman.emcp.EMConfig perform
情報: この操作は/opt/app/oracle/cfgtoollogs/emca/orcl/emca_2014_10_09_09_57_46.logでロギングされています。
2014/10/09 9:58:40 oracle.sysman.emcp.EMConfig perform
致命的: リスナーが起動していないか、データベース・サービスがリスナーに登録されていません。リスナーを起動し、データベース・サービスを登録し、EM Configuration Assistantを再度実行してください。
詳細は、/opt/app/oracle/cfgtoollogs/emca/orcl/emca_2014_10_09_09_57_46.logにあるログ・ファイルを参照してください。
構成を完了できませんでした。 詳細は、/opt/app/oracle/cfgtoollogs/emca/orcl/emca_2014_10_09_09_57_46.logにあるログ・ファイルを参照してください。
  • 設定: ORA-01031: ???????-??????????→権限が不足しているエラー
  • パスワードファイルがなかったのでをコピーまたは作成してあげて再実行

パターン２

・
・
2014/10/14 18:59:33 oracle.sysman.emcp.ParamsManager getLocalListener
警告: oracle-test-dbのリスナーを取得中にエラーが発生しました
2014/10/14 18:59:37 oracle.sysman.emcp.util.DBControlUtil secureDBConsole
情報: Database Controlの保護中(少し時間がかかります)...
・
・
  • listener.oraからデフォルトリスナーLISTENER(1521)の記述がなかった。

パターン３

2014/10/14 18:59:37 oracle.sysman.emcp.util.DBControlUtil secureDBConsole
情報: Database Controlの保護中(少し時間がかかります)...
2014/10/14 18:59:41 oracle.sysman.emcp.util.PlatformInterface executeCommand
警告: /opt/app/oracle/product/11.2.0.4/dbhome_1/bin/emctl secure dbconsole -host oracle-test-db -sid orcl の実行中のエラ ー
2014/10/14 18:59:41 oracle.sysman.emcp.EMDBPostConfig performConfiguration
警告: Database Controlの保護中にエラーが発生しました。
2014/10/14 18:59:41 oracle.sysman.emcp.EMDBPostConfig setWarnMsg
情報: Database Controlの保護中にエラーが発生しました。Database Controlが非セキュア・モードで起動されています。Database Controlを保護するには、次のコマンドを実行してください。

 1) 環境変数ORACLE_UNQNAMEを一意のデータベース名に設定します
 2) /opt/app/oracle/product/11.2.0.4/dbhome_1/bin/emctl stop dbconsole
 3) /opt/app/oracle/product/11.2.0.4/dbhome_1/bin/emctl config emkey -repos -sysman_pwd < SYSMANユーザーのパスワード >
 4) /opt/app/oracle/product/11.2.0.4/dbhome_1/bin/emctl secure dbconsole -sysman_pwd < SYSMANユーザーのパスワード >
 5) /opt/app/oracle/product/11.2.0.4/dbhome_1/bin/emctl start dbconsole

 EMキーを保護するには、/opt/app/oracle/product/11.2.0.4/dbhome_1/bin/emctl config emkey -remove_from_repos -sysman_pwd < SYSMANユーザーのパスワード >を実行します
2014/10/14 18:59:41 oracle.sysman.emcp.util.DBControlUtil startOMS
情報: Database Controlの起動中(少し時間がかかります)...
2014/10/14 18:59:51 oracle.sysman.emcp.EMDBPostConfig performConfiguration
情報: Database Controlは正常に起動されました
2014/10/14 18:59:51 oracle.sysman.emcp.EMDBPostConfig performConfiguration
情報: >>>>>>>>>>> Database ControlのURLはhttp://oracle-test-db:1158/emです <<<<<<<<<<<


Database Controlの保護中にエラーが発生しました。Database Controlが非セキュア・モードで起動されています。Database Controlを保護するには、次のコマンドを実行してください。

 1) 環境変数ORACLE_UNQNAMEを一意のデータベース名に設定します
 2) /opt/app/oracle/product/11.2.0.4/dbhome_1/bin/emctl stop dbconsole
 3) /opt/app/oracle/product/11.2.0.4/dbhome_1/bin/emctl config emkey -repos -sysman_pwd < SYSMANユーザーのパスワード >
 4) /opt/app/oracle/product/11.2.0.4/dbhome_1/bin/emctl secure dbconsole -sysman_pwd < SYSMANユーザーのパスワード >
 5) /opt/app/oracle/product/11.2.0.4/dbhome_1/bin/emctl start dbconsole

 EMキーを保護するには、/opt/app/oracle/product/11.2.0.4/dbhome_1/bin/emctl config emkey -remove_from_repos -sysman_pwd < SYSMANユーザーのパスワード >を実行します
  • OEMの起動はできたがレポジトリが壊れていて接続できなかった。
    • emca -deconfig dbcontrol db -repos drop
    • emca -config dbcontrol db -repos createで解決

Oracle shrinkする上での注意点

ORACLE SE

Oracleを使用していると、断片化がつらい。
頻繁に更新しているインデックスの断片化が特につらい。
そして表領域を圧迫していく。。

そういう時にEnterpriseEditionであればindex rebuild onlineなんてできるけど、
StandardEditionだとできないのでshrinkで対応。

以下のように記述する。

ALTER INDEX indexname SHRINK SPACE;

このshrinkはSEでもオンラインで可能なので、いいんですが注意が必要。

1. ディスク領域

アーカイブログが大量に出力されるのでディスク領域に注意が必要。
単純なディスクサイズの問題もあるし、もし高速リカバリエリアにアーカイブログを保存している場合は使用率の確認が必要。
高速リカバリエリアのサイズを確認しながら、またはdfコマンドで確認しながらshrinkする。

>SELECT nvl(SUM(PERCENT_SPACE_USED),0)   FROM v$flash_recovery_area_usage;

NVL(SUM(PERCENT_SPACE_USED),0)
------------------------------
                         32.86

2. UNDO領域の使用率

shrink中はUNDO領域の使用も大幅に増える。
UNDO領域を食い潰すと更新できずにセッションが詰まる、自動拡張にしていても拡張のタイミングで更新が待たされセッションが詰まる。
よって、UNDO領域の使用率がいっぱいにならないように確認しながら、UNDO領域のパージを待ったりして進める必要あり。

select to_char(sysdate,'YYYY/MM/DD HH24:MI:SS') "TIME",
       round("USED_SIZE(MB)", 3) "USED(MB)",
       round("TBS_SIZE(MB)", 3)  "TBS(MB)",
       round("USED_SIZE(MB)"/"TBS_SIZE(MB)" * 100, 3) "USAGE(%)"
  from (select sum(BYTES)/1024/1024 "USED_SIZE(MB)"
          from DBA_UNDO_EXTENTS
         where STATUS in ('ACTIVE', 'UNEXPIRED')
           and TABLESPACE_NAME = 'UNDOTBS1') ,
       (select sum(BYTES)/1024/1024 "TBS_SIZE(MB)"
          from DBA_DATA_FILES
         where TABLESPACE_NAME = 'UNDOTBS1') ;

SQLの参考

第28回 UNDO表領域の管理～保存期間の自動チューニング～

3. 解放時のロック

SHRINK SPACEを実施してると断片化を解消している処理の間は行ロックなので、ほぼオンライン。
しかし、その空いたエクステントを解放するときはテーブルロックをとるので解放する領域が大きいとセッションが詰まってこれまた大変なことに。。。

SHRINK SPACE COMPACTにすることで、エクステントを解放はせずに断片化だけ解消するのでセグメントのサイズは変わらないが、
空いた領域を再利用してくれるので増加は抑えられる。
または、SHRINK SPACE COMPACTをしておいて、負荷の少ない時間帯に解放してあげるのが良い。

以上のことを踏まえて、うちではこの辺を自動化して運用してます。

RMANで同一サーバ上にデータベースを複製する

ORACLE SE

あるOracleDBを同一サーバ上に複製させるメモ。
環境は12cSE2。

現在、稼動中のインスタンス名をtestdb、複製先DBをcopydbとする。

準備

  • 初期化パラメータをコピーして、リネーム
cp -p $ORACLE_HOME/dbs/inittestdb.ora $ORACLE_HOME/dbs/initcopydb.ora
  • パスワードファイルコピー
cp -p $ORACLE_HOME/dbs/orapwtestdb $ORACLE_HOME/dbs/orapwcopydb
  • 初期化パラメータの編集 コントロールファイルの場所であったり、SGAのサイズであったり調整する。
・
*.audit_file_dest='/opt/app/oracle/admin/copydb/adump'
*.control_files='/opt/app/oracle/oradata/copydb/control01.ctl'
・
  • 初期化パラメータで編集した存在しないディレクトリがあれば作成する
$ mkdir -p {/opt/app/oracle/admin/copydb/adump,/opt/app/oracle/oradata/copydb}
  • /etc/oratabに記述して環境変数を変更しやすくしておく
$ vim /etc/oratab

testdb:/opt/app/oracle/product/12.1.0/dbhome_1:N
copydb:/opt/app/oracle/product/12.1.0/dbhome_1:N

複製する

静的リスナーを設定して起動、copydbにアクセスできるようにする

今回はLISTENER2を作成。

vim $ORACLE_HOME/network/admin/listener.ora
 
LISTENER2 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
    )
  )
SID_LIST_LISTENER2=
  (SID_LIST=
    (SID_DESC=
      (SID_NAME=copydb)
      (ORACLE_HOME=/opt/app/oracle/product/12.1.0/dbhome_1)
    )
  )

$ lsnrctl start LISTENER2

・
・
サービスのサマリー...
サービス"copydb"には、1件のインスタンスがあります。
  インスタンス"copydb"、状態UNKNOWNには、このサービスに対する1件のハンドラがあります...
・
・

copydbインスタンス起動

$ . oraenv
ORACLE_SID = [testdb] ? copydb
The Oracle base remains unchanged with value /opt/app/oracle

$ sqlplus / as sysdba
SQL> startup nomount;
ORACLEインスタンスが起動しました。

RMANから接続してインスタンス起動

testdbに接続。

$ . oraenv
ORACLE_SID = [copydb] ? testdb

$ rman target /

ターゲット・データベース: TESTDB (データベースID=2695979559)に接続されました

copydbに接続。パスワードファイルをコピーしておいたのでtestdbと同じパスワード。

RMAN> connect auxiliary sys/oracle@localhost:1522/copydb

補助データベース: COPYDBに接続されました(マウントされていません)

複製開始する。

事前にデータファイルの場所を確認しておき、SET NEWNAMEで変更する。

RMAN> RUN
{
  SET NEWNAME FOR DATAFILE 1 TO '/opt/app/oracle/oradata/copydb/system01.dbf';
  SET NEWNAME FOR DATAFILE 3 TO '/opt/app/oracle/oradata/copydb/sysaux01.dbf'; 
  SET NEWNAME FOR DATAFILE 4 TO '/opt/app/oracle/oradata/copydb/undotbs01.dbf';
  SET NEWNAME FOR DATAFILE 5 TO '/opt/app/oracle/oradata/copydb/soe.dbf';
  SET NEWNAME FOR DATAFILE 6 TO '/opt/app/oracle/oradata/copydb/users01.dbf';
  SET NEWNAME FOR TEMPFILE 1 TO '/opt/app/oracle/oradata/copydb/temp01.dbf';
duplicate target database to copydb;
}



・
・
データベースがオープンしました。
作成したサーバー・パラメータ・ファイルを削除できません
Duplicate Dbが完了しました(完了時間: 2016/07/04 22:17:17)
・
・

これで完了できた。
オンラインREDOログの場所などは事前に指定してもいいし、あとでリネームしてもよい。

基表にSELECT権限がないVIEWに対して実行計画を取得する

ORACLE SE

基表に対してSELECT権限がないVIEWに対してEXPLAIN PLAN FORを使用して実行計画を取得しようとすると

SQL> explain plan for select * from test.test1_view;
explain plan for select * from test.test1_view
                                           *
行1でエラーが発生しました。:
ORA-01039: ビューの基礎オブジェクトに対する権限が不十分です。

基表にSELECT権限があればEXPLAIN PLAN FORも問題なくできますが、
基表にアクセスさせたくないためにVIEWしたので基表にSELECT権限付与することなく実行計画を取得する方法を考えた。

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR())を使えばできることがわかった。
ただし、DBMS_XPLANパッケージを使用するにいくつかの動的ディクショナリビューにSELECT権限を付与することが必要。

DBMS_XPLAN

DBMS_XPLANパッケージは、パッケージ所有者(SYS)ではなく、コール・ユーザーの権限で実行されます。テーブル・ファンクションDISPLAY_CURSORを使用するには、V$SQL_PLAN、V$SESSIONおよびV$SQL_PLAN_STATISTICS_ALL.の各固定ビューに対するSELECT権限が必要です。
DISPLAY_CURSOR機能を使用する場合、コール・ユーザーには、固定ビューV$SQL_PLAN_STATISTICS_ALL、V$SQLおよびV$SQL_PLANに対するSELECT権限が必要です。権限がない場合は、エラー・メッセージが表示されます。

権限を付与する。

grant select on V_$SESSION to texplain;
grant select on V_$SQL_PLAN to texplain;
grant select on V_$SQL_PLAN_STATISTICS_ALL to texplain;
grant select on V_$SQL to texplain;

試す。

SQL>select count(*) from test.test1_view;

  COUNT(*)
----------
       100

SQL>SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());

PLAN_TABLE_OUTPUT   SQL_ID  0fn9jb84xq0a4, child number 0
-----------------   -------------------------------------
select count(*) from test.test1_view

Plan hash value: 2932712509

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |       |       |     3 (100)|          |
|   1 |  TABLE ACCESS FULL| TEST1 |   100 |  2600 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)

無事取得完了。でもこうすると実際にクエリが発行されちゃうのと基表名とかはわかっちゃうのか。。
あと、クエリの実行キャンセルしても実行計画を取得できるからいいね。

SQL>select count(*) from test.test1_view where id=1;
select count(*) from test.test1_view where id=1;
                   *
行1でエラーが発生しました。:
ORA-01013: ユーザーによって現行の操作の取消しがリクエストされました


SQL>SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());

PLAN_TABLE_OUTPUT
-----------------------------------------------
SQL_ID  5twf49nt4gjdw, child number 0
-------------------------------------
select count(*) from puli.test1_view where id=1

Plan hash value: 1249660229

-----------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |       |       |     1 (100)|          |
|   1 |  SORT AGGREGATE    |              |     1 |    13 |            |          |
|*  2 |   INDEX UNIQUE SCAN| SYS_C0061810 |     1 |    13 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=1)