Hatena::ブログ(Diary)

ablog このページをアンテナに追加 RSSフィード

2017-09-23

AWRレポートを CSV に変換する手順

AWRレポートを CSV に変換する手順。CSVEXCEL のピボットグラフなどを使って分析すると便利です。


$ git clone https://github.com/yasushiyy/awr2csv.git
$ cd awrrep
  • AWRレポートを CSV に変換する。
$ python ../awrhtml2csv.py *.html
  • 変換後のファイルを確認する。
$ ls -1 *.csv
events_background.csv
events_foreground.csv
events_topn.csv
inst_efficiency.csv
load_profile.csv
parameters.csv
seg_logical.csv
seg_phys_reads.csv
seg_phys_writes.csv
seg_unoptimized.csv
sql_cpu.csv
sql_elapsed.csv
sql_executions.csv
sql_gets.csv
sql_parses.csv
sql_reads.csv
sql_unoptimized.csv
sql_user_io.csv
sql_version.csv
time_model.csv

前提

  • NLS_LANG は American_America.<CHARACTERSET> で AWR レポートを出力する。

Tips

NLS_LANG が American_America... で出力されておらずAWRレポートの日付が "31-9月 -15" のようになっていて以下のエラーになる場合は、

$ python ../../awrhtml2csv.py *.html 
Processing awrrpt_1_41050_41051.html...
Traceback (most recent call last):
  File "../../awrhtml2csv.py", line 135, in <module>
    output = parse(filelist)
  File "../../awrhtml2csv.py", line 97, in parse
    st = datetime.strptime(snap.text, '%d-%b-%y %H:%M:%S')
UnicodeEncodeError: 'ascii' codec can't encode character u'\u6708' in position 4: ordinal not in range(128)

月を英字に変換する。

$ perl -i.org -pe 's/8 /Aug/g;s/9 /Sep/g' *.html

参考


追記(2017/09/29):

$ python ../../awrhtml2csv.py *.html

xml.etree.ElementTree.ParseError: not well-formed (invalid token): line 9192, column 13

とエラーになったので、調べたら、AWRレポートの末尾に「9317行が選択されました。」と入ってるのが原因だった。

End of Report
</body></html>

9317行が選択されました。

Perl ワンライナーで除去したらエラーが出なくなった。

$ perl -i.org -pe 's/.*行が選択されました。//g' *.html

2017-09-22

Oracle Database から Aurora MySQL互換 に DMS でデータをロードする

Oracle Database から Aurora with MySQL Compatibility にテーブルのデータを AWS Database Migration Service (DMS) でフルロードしてみたメモ。


ロード後のソースDBとターゲットDBのデータ比較

SQL> select * from test_timestamp;

COL_TS                          COL_TS6                         COL_TS9
------------------------------- ------------------------------- -------------------------------
20-SEP-17 01.23.39.000000000 AM 20-SEP-17 01.23.39.000000000 AM 20-SEP-17 01.23.39.000000000 AM
20-SEP-17 01.23.54.502295000 AM 20-SEP-17 01.23.54.502295000 AM 20-SEP-17 01.23.54.502295000 AM
  • ターゲットDB(Aurora with MySQL Compatibility)
mysql> select * from TEST_TIMESTAMP;
+----------------------------+----------------------------+-------------------------------+
| COL_TS                     | COL_TS6                    | COL_TS9                       |
+----------------------------+----------------------------+-------------------------------+
| 2017-09-20 01:23:39.000000 | 2017-09-20 01:23:39.000000 | 2017-09-20 01:23:39.000000000 |
| 2017-09-20 01:23:54.502295 | 2017-09-20 01:23:54.502295 | 2017-09-20 01:23:54.502295000 |
+----------------------------+----------------------------+-------------------------------+
2 rows in set (0.01 sec)

テーブル定義の比較

SQL> info test_timestampTABLE: TEST_TIMESTAMP
	 LAST ANALYZED:2017-09-20 02:00:03.0
	 ROWS         :2
	 SAMPLE SIZE  :2
	 INMEMORY     :DISABLED
	 COMMENTS     :

Columns
NAME         DATA TYPE      NULL  DEFAULT    COMMENTS
 COL_TS      TIMESTAMP(6)   Yes
 COL_TS6     TIMESTAMP(6)   Yes
 COL_TS9     TIMESTAMP(9)   Yes
  • ターゲットDB(Aurora with MySQL Compatibility)
mysql> desc TEST_TIMESTAMP;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| COL_TS  | datetime(6) | YES  |     | NULL    |       |
| COL_TS6 | datetime(6) | YES  |     | NULL    |       |
| COL_TS9 | varchar(37) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

手順

create table test_timestamp(col_ts timestamp, col_ts6 timestamp(6), col_ts9 timestamp(9));
insert into test_timestamp values(sysdate, sysdate, sysdate);
insert into test_timestamp values(systimestamp, systimestamp, systimestamp);
commit;
create table `test_timestamp` (
  `col_ts` datetime(6) default null,
  `col_ts6` datetime(6) default null,
  `col_ts9` varchar(37) default null
) engine=innodb default charset=utf8;
  • DMS のレプリケーションインスタンス、ソースDBとターゲットDBへのエンドポイントを作成する。
  • DMS のレプリケーションタスクの定義(タスクはマネジメントコンソールで作成)
$ aws dms describe-replication-tasks --filters Name=replication-task-id,Values=test-timestamp
{
    "ReplicationTasks": [
        {
            "SourceEndpointArn": "arn:aws:dms:ap-northeast-1:***************:endpoint:...",
            "ReplicationTaskIdentifier": "test-timestamp",
            "ReplicationInstanceArn": "arn:aws:dms:ap-northeast-1:***************:rep:...",
            "TableMappings": "{\"rules\":[{\"rule-type\":\"selection\",\"rule-id\":\"1\",\"rule-name\":\"1\",\"object-locator\":{\"schema-name\":\"AWSUSER\",\"table-name\":\"TEST_TIMESTAMP\"},\"rule-action\":\"include\"},{\"rule-type\":\"transformation\",\"rule-id\":\"2\",\"rule-name\":\"2\",\"rule-target\":\"schema\",\"object-locator\":{\"schema-name\":\"AWSUSER\"},\"rule-action\":\"rename\",\"value\":\"mydb\"}]}",
            "ReplicationTaskStartDate": 1506052627.915,
            "ReplicationTaskStats": {
                "TablesLoading": 0,
                "TablesQueued": 0,
                "TablesErrored": 0,
                "FullLoadProgressPercent": 100,
                "TablesLoaded": 1,
                "ElapsedTimeMillis": 1413
            },
            "Status": "stopped",
            "ReplicationTaskArn": "arn:aws:dms:ap-northeast-1:***************:task:...",
            "StopReason": "Stop Reason FULL_LOAD_ONLY_FINISHED",
            "ReplicationTaskCreationDate": 1506052582.138,
            "MigrationType": "full-load",
            "TargetEndpointArn": "arn:aws:dms:ap-northeast-1:***************:endpoint:...",
            "ReplicationTaskSettings": "{\"TargetMetadata\":{\"TargetSchema\":\"\",\"SupportLobs\":false,\"FullLobMode\":false,\"LobChunkSize\":64,\"LimitedSizeLobMode\":true,\"LobMaxSize\":32,\"LoadMaxFileSize\":0,\"ParallelLoadThreads\":0,\"ParallelLoadBufferSize\":0,\"BatchApplyEnabled\":false},\"FullLoadSettings\":{\"TargetTablePrepMode\":\"DO_NOTHING\",\"CreatePkAfterFullLoad\":false,\"StopTaskCachedChangesApplied\":false,\"StopTaskCachedChangesNotApplied\":false,\"MaxFullLoadSubTasks\":8,\"TransactionConsistencyTimeout\":600,\"CommitRate\":10000},\"Logging\":{\"EnableLogging\":true,\"LogComponents\":[{\"Id\":\"SOURCE_UNLOAD\",\"Severity\":\"LOGGER_SEVERITY_DEFAULT\"},{\"Id\":\"SOURCE_CAPTURE\",\"Severity\":\"LOGGER_SEVERITY_DEFAULT\"},{\"Id\":\"TARGET_LOAD\",\"Severity\":\"LOGGER_SEVERITY_DEFAULT\"},{\"Id\":\"TARGET_APPLY\",\"Severity\":\"LOGGER_SEVERITY_DEFAULT\"},{\"Id\":\"TASK_MANAGER\",\"Severity\":\"LOGGER_SEVERITY_DEFAULT\"}],\"CloudWatchLogGroup\":\"dms-tasks-replication-instance-1\",\"CloudWatchLogStream\":\"dms-task-R32JDUMJAF4ADCBYRWYGMN6PIE\"},\"ControlTablesSettings\":{\"historyTimeslotInMinutes\":5,\"ControlSchema\":\"\",\"HistoryTimeslotInMinutes\":5,\"HistoryTableEnabled\":false,\"SuspendedTablesTableEnabled\":false,\"StatusTableEnabled\":false},\"StreamBufferSettings\":{\"StreamBufferCount\":3,\"StreamBufferSizeInMB\":8,\"CtrlStreamBufferSizeInMB\":5},\"ChangeProcessingDdlHandlingPolicy\":{\"HandleSourceTableDropped\":true,\"HandleSourceTableTruncated\":true,\"HandleSourceTableAltered\":true},\"ErrorBehavior\":{\"DataErrorPolicy\":\"LOG_ERROR\",\"DataTruncationErrorPolicy\":\"LOG_ERROR\",\"DataErrorEscalationPolicy\":\"SUSPEND_TABLE\",\"DataErrorEscalationCount\":0,\"TableErrorPolicy\":\"SUSPEND_TABLE\",\"TableErrorEscalationPolicy\":\"STOP_TASK\",\"TableErrorEscalationCount\":0,\"RecoverableErrorCount\":-1,\"RecoverableErrorInterval\":5,\"RecoverableErrorThrottling\":true,\"RecoverableErrorThrottlingMax\":1800,\"ApplyErrorDeletePolicy\":\"IGNORE_RECORD\",\"ApplyErrorInsertPolicy\":\"LOG_ERROR\",\"ApplyErrorUpdatePolicy\":\"LOG_ERROR\",\"ApplyErrorEscalationPolicy\":\"LOG_ERROR\",\"ApplyErrorEscalationCount\":0,\"ApplyErrorFailOnTruncationDdl\":false,\"FullLoadIgnoreConflicts\":true,\"FailOnTransactionConsistencyBreached\":false,\"FailOnNoTablesCaptured\":false},\"ChangeProcessingTuning\":{\"BatchApplyPreserveTransaction\":true,\"BatchApplyTimeoutMin\":1,\"BatchApplyTimeoutMax\":30,\"BatchApplyMemoryLimit\":500,\"BatchSplitSize\":0,\"MinTransactionSize\":1000,\"CommitTimeout\":1,\"MemoryLimitTotal\":1024,\"MemoryKeepTime\":60,\"StatementCacheSize\":50}}"
        }
    ]
}
  • レプリケーションタスク実行
$ aws dms start-replication-task --replication-task-arn arn:aws:dms:ap-northeast-1:***:task:*** --start-replication-task-type reload-target

参考

2017-09-18

AWS Schema Conversion Tool をインストールする

macOS SierraAWS Schema Conversion Tool (SCT) をインストールしたメモ。


ダウンロード

SCT
JDBC Driver for MySQL

インストール

SCT
JDBC Driver for MySQL
  • mysql-connector-java-5.1.44.zip を解凍して任意のフォルダに保存する。
  • 使うときは SCT で設定時にドライバのパスを指定する。

sqlplus から Oracle Database に接続すると ORA-21561 で失敗する

事象

sqlplus から Oracle Database に接続すると ORA-21561 で失敗する。

% sqlplus awsuser/******@******.******.ap-northeast-1.rds.amazonaws.com:1521/ORCL

SQL*Plus: Release 12.1.0.2.0 Production on Mon Sep 18 22:37:57 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

ERROR:
ORA-21561: OID generation failed

ORA-21561: OID生成に失敗しました

原因: 渡されたハンドルが有効でない可能性があります。

処置: env、svcハンドルの妥当性を確認してください。

ORA-19999からORA-24279

原因

  • /etc/hosts にホスト名のマッピングがない、もしくは間違っている。

Incorrect /etc/hosts mapping for the client machine or no mapping at all.

Sfaturi Oracle / Tips and tricks OracleDBA: ORA-21561 : OID generation failed

対処

  • ホスト名を確認する
% uname -n
******.***.foo.com
  • /etc/hosts に追記する
% sudo vi /etc/hosts
127.0.0.1	localhost ******.***.foo.com

環境


参考

Mac OS X に Oracle Instant Client をインストールする

ダウンロード


インストール

% unzip instantclient-basic-macos.x64-12.1.0.2.0.zip
Archive:  instantclient-basic-macos.x64-12.1.0.2.0.zip
  inflating: instantclient_12_1/BASIC_README
  inflating: instantclient_12_1/adrci
  inflating: instantclient_12_1/genezi
  inflating: instantclient_12_1/libclntsh.dylib.12.1
  inflating: instantclient_12_1/libclntshcore.dylib.12.1
  inflating: instantclient_12_1/libnnz12.dylib
  inflating: instantclient_12_1/libocci.dylib.12.1
  inflating: instantclient_12_1/libociei.dylib
  inflating: instantclient_12_1/libocijdbc12.dylib
  inflating: instantclient_12_1/libons.dylib
  inflating: instantclient_12_1/liboramysql12.dylib
  inflating: instantclient_12_1/ojdbc6.jar
  inflating: instantclient_12_1/ojdbc7.jar
  inflating: instantclient_12_1/uidrvci
  inflating: instantclient_12_1/xstreams.jar
% unzip instantclient-sqlplus-macos.x64-12.1.0.2.0.zip
Archive:  instantclient-sqlplus-macos.x64-12.1.0.2.0.zip
  inflating: instantclient_12_1/SQLPLUS_README
  inflating: instantclient_12_1/glogin.sql
  inflating: instantclient_12_1/libsqlplus.dylib
  inflating: instantclient_12_1/libsqlplusic.dylib
  inflating: instantclient_12_1/sqlplus
  • 移動する
% sudo mkdir -p /opt/oracle
% sudo mv instantclient_12_1 /opt/oracle
  • パスを通す
% vi .zshrc.local
export PATH=$PATH:/opt/oracle/instantclient_12_1

接続する

$ sqlplus awsuser/******@******.******.ap-northeast-1.rds.amazonaws.com:1521/ORCL

2017-09-16

Mac で右クリックで新規ファイル作成する by New File Creation

New File CreationMac で右クリック(2本指でタップ)で新規ファイル作成する方法をメモ。


インストール


設定

  • [システム環境設定]-[機能拡張]-[Finder]で[New File Creation Extension]にチェックを入れる。

f:id:yohei-a:20170917001747p:image:w640


使ってみる

  • 2本指でタップして、[New File Creation...] を選択する。

f:id:yohei-a:20170917002952p:image:w360

  • ファイルタイプを選んで、ファイル名を入力して [Save] で保存する。

f:id:yohei-a:20170917002946p:image


環境


参考

2017-09-15

MySQL で NOT NULL 制約のある列に複数行インサートするとその型のデフォルト値が入る

MySQLSQL モードが STRICT モードでない場合、NOT NULL 制約のある列に複数行インサートするとその型のデフォルト値(0とか空文字)が入る(1行インサートだとエラーで入らない)。


検証結果

  • Amazon Aurora with MySQL Compatibility に接続する
$ mysql -h ******.******.ap-northeast-1.rds.amazonaws.com -u awsuser -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 53
Server version: 5.6.10 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use mydb;
Database changed
mysql> create table `not_null_test` (
    ->   `id` int(10) unsigned not null,
    ->   `int_col` int(10) unsigned not null,
    ->   `char_col` char(10)  not null,
    ->   `ts_col` timestamp not null,
    ->   primary key (`id`)
    -> ) engine=innodb default charset=utf8;
Query OK, 0 rows affected (0.05 sec)
  • SQL モードは設定されていない
mysql> select @@global.sql_mode;
+-------------------+
| @@global.sql_mode |
+-------------------+
|                   |
+-------------------+
1 row in set (0.02 sec)
  • 1行インサートはエラーになる
mysql> insert into not_null_test (id, int_col, char_col, ts_col) values (1, null, null, null);
ERROR 1048 (23000): Column 'int_col' cannot be null
  • 複数行インサートは成功する
mysql> insert into not_null_test (id, int_col, char_col, ts_col) values (1, null, null, null), (2, null, null, null);
Query OK, 2 rows affected, 4 warnings (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 4
mysql> select * from not_null_test;
+----+---------+----------+---------------------+
| id | int_col | char_col | ts_col              |
+----+---------+----------+---------------------+
|  1 |       0 |          | 2017-09-15 06:54:07 |
|  2 |       0 |          | 2017-09-15 06:54:07 |
+----+---------+----------+---------------------+
2 rows in set (0.02 sec)
  • SQLモードを STRICT_ALL_TABLES にする
mysql> set sql_mode='strict_all_tables';
Query OK, 0 rows affected (0.03 sec)
  • 1行インサートは失敗する
mysql> insert into not_null_test (id, int_col, char_col, ts_col) values (1, null, null, null);
ERROR 1048 (23000): Column 'int_col' cannot be null
  • 複数行インサートも失敗する
mysql> insert into not_null_test (id, int_col, char_col, ts_col) values (1, null, null, null), (2, null, null, null);
ERROR 1048 (23000): Column 'int_col' cannot be nul

参考

単列インサートの場合はNOT NULLが指定されたカラムにNULL値が挿入されるとそのクエリはエラーとなって失敗するが、複数列インサートの場合は警告(warning)を発するものの、クエリは正常に受け付けられる。

その際、NULL値が指定された各カラムにはそれぞれのカラムのデータ型の暗黙的なデフォルト値が挿入される。(数値型なら0、文字列型なら空文字''、etc…)

MySQLにおけるNOT NULLカラムへのインサート時の挙動 - Sojiro’s Blog

NOT NULL として宣言されているカラムへの NULL の挿入。複数行の INSERT ステートメントまたは INSERT INTO ... SELECT ステートメントの場合、このカラムは、そのカラムデータ型の暗黙のデフォルト値に設定されます。これは、数値型では 0、文字列型では空の文字列 ('')、および日付と時間型では「0」の値です。サーバーは SELECT からの結果セットを検査して、それが単一行を返すかどうかを確認しないため、INSERT INTO ... SELECT ステートメントは複数行の挿入と同じ方法で処理されます。(単一行の INSERT の場合は、NULL が NOT NULL カラムに挿入されても警告は発生しません。代わりに、このステートメントがエラーで失敗します。)

MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.2.5 INSERT 構文

明示的な DEFAULT 句のない NOT NULL カラムに対するデータエントリでは、INSERT または REPLACE ステートメントにカラムの値を含まれていない場合、または UPDATE ステートメントがカラムを NULL に設定する場合、MySQL はその時点で有効な SQL モードに従ってカラムを処理します。

(中略)

セクション5.1.7「サーバー SQL モード」を参照してください。

所定のテーブルに対して、SHOW CREATE TABLE ステートメントを使用すると、どのカラムに明示的な DEFAULT 句があるかを確認できます。

暗黙的なデフォルトは次のように定義されます。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 11.6 データ型デフォルト値

厳密モードは、MySQL が INSERT や UPDATE などのデータ変更ステートメントで無効な値または欠落した値を処理する方法を制御します。値はいくつかの理由で無効になることがあります。たとえば、カラムに対して正しくないデータ型を持っていたり、範囲外であったりすることがあります。値の欠落が発生するのは、挿入される新しい行の非 NULL カラムに値が含まれておらず、そのカラムに明示的な DEFAULT 句が定義されていない場合です。(NULL カラムの場合、値が欠落しているときは NULL が挿入されます。)

厳密モードが有効でない場合、MySQL は無効または欠落した値に対して調整された値を挿入し、警告を生成します (セクション13.7.5.41「SHOW WARNINGS 構文」を参照してください)。厳密モードでは、INSERT IGNORE または UPDATE IGNORE を使用すると、この動作を実行できます。

データを変更しない SELECT などのステートメントの場合、厳密モードでは無効な値はエラーでなく警告を生成します。

厳密モードは、外部キー制約が検査されるかどうかに影響されません。foreign_key_checks を検査に使用できます。(セクション5.1.4「サーバーシステム変数」を参照してください。)

MySQL :: MySQL 5.6 リファレンスマニュアル :: 5.1.7 サーバー SQL モード