2017년 12월 11일 월요일

EDB PostgreSQL Advence Server 를 설치 후 psql 로 접속시 Cannot read termcap database 메세지가 뜬다면

아래 메세지를 찾아 보니까 버그 케이스 같은데요. edb as10 에서도 발생을 하네요

ubuntu 에 설치를 하니까


1. 제품 설치 후 아래와 같은 메세지가 뜬다면

Password:
psql.bin (10.1.5)
Type "help" for help.

Cannot read termcap database;
using dumb terminal settings.

2. 환경변수에 readline 라이브러리를 미리 로드 하게 설정

export LD_PRELOAD=/usr/lib/x86_64-linux-gnu/libreadline.so


3. 해당 readline 라이브러리 추가

yum install readline-devel
apt install libreadline-dev

2017년 8월 16일 수요일

갑자기 고민을 해본 어느 SQL


/*
  Test DB : Oracle Enterprise 12.2.0.1 Single 

*/

-- 01. test table 생성

CREATE TABLE "SCOTT"."MYTEST"
(
  "ID"      VARCHAR2(50)  NOT NULL ENABLE,
  "UPDATED" DATE          NOT NULL ENABLE,
  "MYNAME"  VARCHAR2(100) NOT NULL ENABLE,
  PRIMARY KEY ("ID", "UPDATED")
    USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE
    STATISTICS
    TABLESPACE "USERS" ENABLE
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

NOCOMPRESS LOGGING
TABLESPACE "USERS"

-- 02. 통계정보 설정
BEGIN
  dbms_stats.set_column_stats ('SCOTT', 'MYTEST', 'ID', distcnt => 8 );
  dbms_stats.set_column_stats ('SCOTT', 'MYTEST','UPDATED', distcnt => 300000);
  dbms_stats.set_table_stats ('SCOTT', 'MYTEST',    NUMROWS => 2400000);
  dbms_stats.set_index_stats ('SCOTT', 'SYS_C0011249', NUMROWS => 2400000);
END;

-- 03. 원본 쿼리

SELECT *
FROM mytest T1
WHERE T1.updated = (SELECT max(updated)
                    FROM mytest
                    WHERE id = T1.id);

Plan hash value: 4215771275
 
---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |     8 |   992 |     2   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |              |     8 |   992 |     2   (0)| 00:00:01 |
|   2 |   NESTED LOOPS               |              |     8 |   992 |     2   (0)| 00:00:01 |
|   3 |    VIEW                      | VW_SQ_1      |     8 |   288 |     1   (0)| 00:00:01 |
|   4 |     HASH GROUP BY            |              |     8 |   288 |     1   (0)| 00:00:01 |
|   5 |      INDEX FULL SCAN         | SYS_C0011249 |  2400K|    82M|     1   (0)| 00:00:01 |
|*  6 |    INDEX UNIQUE SCAN         | SYS_C0011249 |     1 |       |     1   (0)| 00:00:01 |
|   7 |   TABLE ACCESS BY INDEX ROWID| MYTEST       |     1 |    88 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   6 - access("ITEM_1"="T1"."ID" AND "T1"."UPDATED"="MAX(UPDATED)")



-- 04. 쿼리 변형

select *
  from mytest aa,
    (select /*+ index_ffs(bb) */
            id, max(updated) as updated
      from mytest bb
      group by ID
      ) cc
where aa.id = cc.id
and aa.UPDATED = cc.updated
;

Plan hash value: 198263429
 
---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |     8 |   992 |    69  (96)| 00:00:01 |
|   1 |  NESTED LOOPS                |              |     8 |   992 |    69  (96)| 00:00:01 |
|   2 |   NESTED LOOPS               |              |     8 |   992 |    69  (96)| 00:00:01 |
|   3 |    VIEW                      |              |     8 |   288 |    68  (98)| 00:00:01 |
|   4 |     HASH GROUP BY            |              |     8 |   288 |    68  (98)| 00:00:01 |
|   5 |      INDEX FAST FULL SCAN    | SYS_C0011249 |  2400K|    82M|     2   (0)| 00:00:01 |
|*  6 |    INDEX UNIQUE SCAN         | SYS_C0011249 |     1 |       |     1   (0)| 00:00:01 |
|   7 |   TABLE ACCESS BY INDEX ROWID| MYTEST       |     1 |    88 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   6 - access("AA"."ID"="CC"."ID" AND "AA"."UPDATED"="CC"."UPDATED")

select
      *
  from mytest aa,
    (select /*+ parallel_index(bb, 2) index_ffs(bb) */
            id, max(updated) as updated
      from mytest bb
      group by ID
      ) cc
where aa.id = cc.id
and aa.UPDATED = cc.updated
;

Plan hash value: 1395081935
 
----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name         | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |              |     8 |   992 |    38  (93)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR                |              |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)          | :TQ10001     |     8 |   992 |    38  (93)| 00:00:01 |  Q1,01 | P->S | QC (RAND)  |
|   3 |    NESTED LOOPS                |              |     8 |   992 |    38  (93)| 00:00:01 |  Q1,01 | PCWP |            |
|   4 |     NESTED LOOPS               |              |     8 |   992 |    38  (93)| 00:00:01 |  Q1,01 | PCWP |            |
|   5 |      VIEW                      |              |     8 |   288 |    73  (98)| 00:00:01 |  Q1,01 | PCWP |            |
|   6 |       HASH GROUP BY            |              |     8 |   288 |    73  (98)| 00:00:01 |  Q1,01 | PCWP |            |
|   7 |        PX RECEIVE              |              |     8 |   288 |    73  (98)| 00:00:01 |  Q1,01 | PCWP |            |
|   8 |         PX SEND HASH           | :TQ10000     |     8 |   288 |    73  (98)| 00:00:01 |  Q1,00 | P->P | HASH       |
|   9 |          HASH GROUP BY         |              |     8 |   288 |    73  (98)| 00:00:01 |  Q1,00 | PCWP |            |
|  10 |           PX BLOCK ITERATOR    |              |  2400K|    82M|     2   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|  11 |            INDEX FAST FULL SCAN| SYS_C0011249 |  2400K|    82M|     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|* 12 |      INDEX UNIQUE SCAN         | SYS_C0011249 |     1 |       |     1   (0)| 00:00:01 |  Q1,01 | PCWP |            |
|  13 |     TABLE ACCESS BY INDEX ROWID| MYTEST       |     1 |    88 |     1   (0)| 00:00:01 |  Q1,01 | PCWP |            |
----------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
  12 - access("AA"."ID"="CC"."ID" AND "AA"."UPDATED"="CC"."UPDATED")

--
-- Table 에 MYNAME 칼럼이 없다면 
--

SELECT /*+ index_ffs(T1, SYS_C0011249) */
      *
FROM mytest T1
WHERE T1.UPDATED =  (
                     select
                         UPDATED
                     from
                       (SELECT
                          /*+ index_desc(mytest, SYS_C0011249) */
                            updated
                        FROM mytest
                        WHERE id = T1.id
                        ORDER BY updated DESC
                       )
                    where rownum <= 1
                   );


Plan hash value: 2051182498
 
-----------------------------------------------------------------------------------------------
| Id  | Operation                      | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |              |     1 |    36 |     3   (0)| 00:00:01 |
|*  1 |  FILTER                        |              |       |       |            |          |
|   2 |   INDEX FAST FULL SCAN         | SYS_C0011249 |     1 |    36 |     2   (0)| 00:00:01 |
|*  3 |   COUNT STOPKEY                |              |       |       |            |          |
|   4 |    VIEW                        |              |     1 |     9 |     1   (0)| 00:00:01 |
|*  5 |     INDEX RANGE SCAN DESCENDING| SYS_C0011249 |     1 |    36 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("T1"."UPDATED"= (SELECT "UPDATED" FROM  (SELECT /*+ INDEX_DESC ("MYTEST" 
              "SYS_C0011249") */ "UPDATED" "UPDATED" FROM "MYTEST" "MYTEST" WHERE "ID"=:B1 ORDER BY 
              "UPDATED" DESC) "from$_subquery$_002" WHERE ROWNUM<=1))
   3 - filter(ROWNUM<=1)
   5 - access("ID"=:B1)


select t1.*
FROM
  (
    SELECT
      aa.id,
      aa.updated
    FROM
      (
        SELECT
          t1.id,
          t1.updated,
          rank() over (partition BY id ORDER BY updated DESC) AS rnum
        FROM mytest t1
      ) aa
    WHERE rnum <= 1
  ) bb, mytest t1
where t1.id = bb.id
and t1.updated = bb.updated
;

2017년 5월 11일 목요일

생각의 상념


내가 생각하는 것들이 무엇인지도 모르겠다.
좀 쉬어야 하는데 그 동안 너무 달려 온거 같은데 지금의 현실에서 내가 쉰다는건 어렵다.
한달에 나가야 하는 비용을 무시 할수 없고 또 가족의 생계 라는 문제도 무시 할수 없다.
그런데 그래도 좀 쉬고 싶다.

2017년 4월 19일 수요일

Postgres 9.6 성능 이야기






도서] PostgreSQL 9.6 성능 이야기
저자 김시연,최두원 | 출판사 시연아카데미


기술적으로 탄탄한 책이다. 저자가 전직 엑셈 출신이라. 그 스타일이 묻어 난다.
Postgres 디비는 현재 OpenSource DB 의 선두 주자며 모든 디비의 할아버지 정도 되는 아주 역사가 깊은 디비다. 

이런 디비를 Source Code 레벨까지 분석을 할수 있다는 건 대단한 능력이라고 할수 있다. 그런데 다른 한편으로는 그런 지식이 필요 할 까 ? 하는 생각도 든다. 

느낌으로 이런 IT 쪽의 책은 잘 안팔리거나 팔려도 극소수가 볼것 같다. 어쨋거나 나도 이런 책 좀 써봤음 좋겠다. 

2017년 4월 18일 화요일

MySQL Python Connect Driver

-. MySQL Python Connect Driver Db Connect

#!env python3

import mysql.connector
from mysql.connector import errorcode

config = {
   'user': 'dbmgr',
   'password': 'audtlr2',
   'host': 'localhost',
   'database': 'spp',
   'raise_on_warnings': True,
   'use_pure': True,
}

try:
  cnx = mysql.connector.connect(**config)

except mysql.connector.Error as err:
  if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
    print("Something is wrong with your user name or password")
  elif err.errno == errorcode.ER_BAD_DB_ERROR:
    print("Database dose not exists")
  else:
   print(err)

else:
  cnx.close()

2017년 4월 4일 화요일

MySQL 쿼리 튜닝 하다가 생각 한번 해봐야 하는 경우

MySQL 쿼리 튜닝 하다가 생각 한번 해봐야 하는 경우


--
--
--

SELECT DISTINCT
  placement_id
  , zone_id
FROM ox_placement_zone_assoc assoc
WHERE EXISTS(SELECT 1
             FROM ox_campaigns m INNER JOIN ox_clients c
                 ON m.clientid = c.clientid AND c.block_status = 'pass'
             WHERE
               m.status = 0 AND m.block_status = 'pass' AND m.deliverable <= 1 AND assoc.placement_id = m.campaignid)
;


+------+-------------+-------+------+--------------------------------------+--------------------------------------+---------+-----------------------------+------+------------------------------+
| id   | select_type | table | type | possible_keys                        | key                                  | key_len | ref                         | rows | Extra                        |
+------+-------------+-------+------+--------------------------------------+--------------------------------------+---------+-----------------------------+------+------------------------------+
|    1 | PRIMARY     | c     | ALL  | PRIMARY                              | NULL                                 | NULL    | NULL                        | 6728 | Using where; Using temporary |
|    1 | PRIMARY     | m     | ref  | PRIMARY,ox_campaigns_clientid        | ox_campaigns_clientid                | 3       | OPENX_V1_ADMIN.c.clientid   |    3 | Using where                  |
|    1 | PRIMARY     | assoc | ref  | ox_placement_zone_assoc_placement_id | ox_placement_zone_assoc_placement_id | 4       | OPENX_V1_ADMIN.m.campaignid |   78 | Using index                  |
+------+-------------+-------+------+--------------------------------------+--------------------------------------+---------+-----------------------------+------+------------------------------+

+------------------------------+------------+
| Status                       | Duration   |
+------------------------------+------------+
| starting                     |   0.000070 |
| checking permissions         |   0.000014 |
| checking permissions         |   0.000012 |
| checking permissions         |   0.000012 |
| Opening tables               |   0.000034 |
| After opening tables         |   0.000016 |
| System lock                  |   0.000014 |
| Table lock                   |   0.000017 |
| init                         |   0.000037 |
| optimizing                   |   0.000043 |
| statistics                   |   0.000040 |
| preparing                    |   0.000032 |
| executing                    |   0.000012 |
| Creating tmp table           |   0.000037 |
| Copying to tmp table         |   1.089634 |
| converting HEAP to Aria      |   0.121867 |
| Creating index               |   0.000091 |
| Repair by sorting            |   0.912552 |
| Saving state                 |   0.000058 |
| Creating index               |   0.000014 |
| converting HEAP to Aria      |   0.006953 |
| Copying to tmp table on disk | 145.988661 | -- 메모리의 임시 테이블을 디스크로 내리면서 걸리는 시간 이 대부분
| Sending data                 |   1.860387 |
| end                          |   0.000047 |
| removing tmp table           |   0.062840 |
| end                          |   0.000021 |
| query end                    |   0.000021 |
| closing tables               |   0.000015 |
| Unlocking tables             |   0.000031 |
| freeing items                |   0.000028 |
| updating status              |   0.000026 |
| cleaning up                  |   0.000041 |
+------------------------------+------------+

-- 현재 인스턴스의 메모리 사용 부분 
# CACHES AND LIMITS #
tmp_table_size                 = 64M
max_heap_table_size            = 64M

# 수정 
tmp_table_size                 = 512M
max_heap_table_size            = 512M



####################
개발

+------+-------------+-------+------+--------------------------------------+--------------------------------------+---------+-----------------------------+------+------------------------------+
| id   | select_type | table | type | possible_keys                        | key                                  | key_len | ref                         | rows | Extra                        |
+------+-------------+-------+------+--------------------------------------+--------------------------------------+---------+-----------------------------+------+------------------------------+
|    1 | PRIMARY     | c     | ALL  | PRIMARY                              | NULL                                 | NULL    | NULL                        | 4668 | Using where; Using temporary |
|    1 | PRIMARY     | m     | ref  | PRIMARY,ox_campaigns_clientid        | ox_campaigns_clientid                | 3       | OPENX_V1_ADMIN.c.clientid   |    3 | Using where                  |
|    1 | PRIMARY     | assoc | ref  | ox_placement_zone_assoc_placement_id | ox_placement_zone_assoc_placement_id | 4       | OPENX_V1_ADMIN.m.campaignid |   76 | Using index                  |
+------+-------------+-------+------+--------------------------------------+--------------------------------------+---------+-----------------------------+------+------------------------------+


+------+-------------+-------+--------+---------------------------------------------------+--------------------------------------+---------+-----------------------------+------+-----------------------------------------------------+
| id   | select_type | table | type   | possible_keys                                     | key                                  | key_len | ref                         | rows | Extra                                               |
+------+-------------+-------+--------+---------------------------------------------------+--------------------------------------+---------+-----------------------------+------+-----------------------------------------------------+
|    1 | PRIMARY     | m     | ref    | PRIMARY,ox_campaigns_clientid,ox_campaigns_status | ox_campaigns_status                  | 6       | const,const                 | 2728 | Using index condition; Using where; Using temporary |
|    1 | PRIMARY     | c     | eq_ref | PRIMARY                                           | PRIMARY                              | 3       | OPENX_V1_ADMIN.m.clientid   |    1 | Using where                                         |
|    1 | PRIMARY     | assoc | ref    | ox_placement_zone_assoc_placement_id              | ox_placement_zone_assoc_placement_id | 4       | OPENX_V1_ADMIN.m.campaignid |   76 | Using index                                         |
+------+-------------+-------+--------+---------------------------------------------------+--------------------------------------+---------+-----------------------------+------+-----------------------------------------------------+
    

##################


delivery2@localhost:OPENX_V1_ADMIN 12:21:04>select status, block_status, count(*)
    -> from ox_campaigns
    -> group by 1, 2
    -> 
    -> ;
+--------+--------------+----------+
| status | block_status | count(*) |
+--------+--------------+----------+
|      0 |              |      166 |
|      0 | pass         |     5400 | -- 정의역 
|      0 | block        |    12060 |
|      2 |              |        1 |
|      2 | pass         |       36 |
|      2 | block        |       20 |
|      3 |              |      112 |
|      3 | pass         |    20078 |
|      3 | block        |    18450 |
|      4 | pass         |        6 |
+--------+--------------+----------+

-- ox_campaigns index 구성

+--------------+------------+--------------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table        | Non_unique | Key_name                       | Seq_in_index | Column_name       | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+--------------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| ox_campaigns |          0 | PRIMARY                        |            1 | campaignid        | A         |       50058 |     NULL | NULL   |      | BTREE      |         |               |
| ox_campaigns |          1 | ox_campaigns_clientid          |            1 | clientid          | A         |       16686 |     NULL | NULL   |      | BTREE      |         |               |
| ox_campaigns |          1 | ox_campaigns_campaign_group_id |            1 | campaign_group_id | A         |          32 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------------+------------+--------------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+


-- 인덱스 생성
-- 그러나 status 값이 변동이 빈번 할 수 있음
-- (2차)

alter table ox_campaigns
    add key if not exists ox_campaigns_status (status, block_status) using BTREE ;

--
-- 개발 플랜
--
+------+-------------+-------+--------+---------------------------------------------------+--------------------------------------+---------+-----------------------------+------+-----------------------------------------------------+
| id   | select_type | table | type   | possible_keys                                     | key                                  | key_len | ref                         | rows | Extra                                               |
+------+-------------+-------+--------+---------------------------------------------------+--------------------------------------+---------+-----------------------------+------+-----------------------------------------------------+
|    1 | PRIMARY     | m     | ref    | PRIMARY,ox_campaigns_clientid,ox_campaigns_status | ox_campaigns_status                  | 6       | const,const                 | 2728 | Using index condition; Using where; Using temporary |
|    1 | PRIMARY     | c     | eq_ref | PRIMARY                                           | PRIMARY                              | 3       | OPENX_V1_ADMIN.m.clientid   |    1 | Using where                                         |
|    1 | PRIMARY     | assoc | ref    | ox_placement_zone_assoc_placement_id              | ox_placement_zone_assoc_placement_id | 4       | OPENX_V1_ADMIN.m.campaignid |   76 | Using index                                         |
+------+-------------+-------+--------+---------------------------------------------------+--------------------------------------+---------+-----------------------------+------+-----------------------------------------------------+


--
-- 최종안
--

set global tmp_table_size = 512 * 1024 * 1024;
set global max_heap_table_size = 512 * 1024 * 1024;


Copying to tmp table on disk 사라짐

+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000079 |
| checking permissions | 0.000015 |
| checking permissions | 0.000014 |
| checking permissions | 0.000015 |
| Opening tables       | 0.000036 |
| After opening tables | 0.000016 |
| System lock          | 0.000016 |
| Table lock           | 0.000018 |
| init                 | 0.000040 |
| optimizing           | 0.000045 |
| statistics           | 0.000043 |
| preparing            | 0.000036 |
| executing            | 0.000015 |
| Creating tmp table   | 0.000038 |
| Copying to tmp table | 5.617840 |
| Sending data         | 1.745527 |
| end                  | 0.000019 |
| removing tmp table   | 0.024801 |
| end                  | 0.000015 |
| query end            | 0.000015 |
| closing tables       | 0.000013 |
| Unlocking tables     | 0.000018 |
| freeing items        | 0.000020 |
| updating status      | 0.000020 |
| cleaning up          | 0.000019 |
+----------------------+----------+

2017년 3월 14일 화요일

Oracle Slient Install

-. 간단한 스크립트


./runInstaller -silent -ignoreSysPrereqs -responseFile /tmp/oui12102.rsp

####################################################################
## Copyright(c) Oracle Corporation 1998,2014. All rights reserved.##
##                                                                ##
## Specify values for the variables listed below to customize     ##
## your installation.                                             ##
##                                                                ##
## Each variable is associated with a comment. The comment        ##
## can help to populate the variables with the appropriate        ##
## values.                                                        ##
##                                                                ##
## IMPORTANT NOTE: This file contains plain text passwords and    ##
## should be secured to have read permission only by oracle user  ##
## or db administrator who owns this installation.                ##
##                                                                ##
####################################################################


#-------------------------------------------------------------------------------
# Do not change the following system generated value. 
#-------------------------------------------------------------------------------
oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v12.1.0

#-------------------------------------------------------------------------------
# Specify the installation option.
# It can be one of the following:
#   - INSTALL_DB_SWONLY
#   - INSTALL_DB_AND_CONFIG
#   - UPGRADE_DB
#-------------------------------------------------------------------------------
oracle.install.option=INSTALL_DB_SWONLY

#-------------------------------------------------------------------------------
# Specify the hostname of the system as set during the install. It can be used
# to force the installation to use an alternative hostname rather than using the
# first hostname found on the system. (e.g., for systems with multiple hostnames 
# and network interfaces)
#-------------------------------------------------------------------------------
ORACLE_HOSTNAME=obtest1

#-------------------------------------------------------------------------------
# Specify the Unix group to be set for the inventory directory.  
#-------------------------------------------------------------------------------
UNIX_GROUP_NAME=oinstall

#-------------------------------------------------------------------------------
# Specify the location which holds the inventory files.
# This is an optional parameter if installing on
# Windows based Operating System.
#-------------------------------------------------------------------------------
INVENTORY_LOCATION=/u01/app/oraInventory
#-------------------------------------------------------------------------------
# Specify the languages in which the components will be installed.             
# 
# en   : English                  ja   : Japanese                  
# fr   : French                   ko   : Korean                    
# ar   : Arabic                   es   : Latin American Spanish    
# bn   : Bengali                  lv   : Latvian                   
# pt_BR: Brazilian Portuguese     lt   : Lithuanian                
# bg   : Bulgarian                ms   : Malay                     
# fr_CA: Canadian French          es_MX: Mexican Spanish           
# ca   : Catalan                  no   : Norwegian                 
# hr   : Croatian                 pl   : Polish                    
# cs   : Czech                    pt   : Portuguese                
# da   : Danish                   ro   : Romanian                  
# nl   : Dutch                    ru   : Russian                   
# ar_EG: Egyptian                 zh_CN: Simplified Chinese        
# en_GB: English (Great Britain)  sk   : Slovak                    
# et   : Estonian                 sl   : Slovenian                 
# fi   : Finnish                  es_ES: Spanish                   
# de   : German                   sv   : Swedish                   
# el   : Greek                    th   : Thai                      
# iw   : Hebrew                   zh_TW: Traditional Chinese       
# hu   : Hungarian                tr   : Turkish                   
# is   : Icelandic                uk   : Ukrainian                 
# in   : Indonesian               vi   : Vietnamese                
# it   : Italian                                                   
#
# all_langs   : All languages
#
# Specify value as the following to select any of the languages.
# Example : SELECTED_LANGUAGES=en,fr,ja
#
# Specify value as the following to select all the languages.
# Example : SELECTED_LANGUAGES=all_langs  
#-------------------------------------------------------------------------------
SELECTED_LANGUAGES=en,en_GB

#-------------------------------------------------------------------------------
# Specify the complete path of the Oracle Home. 
#-------------------------------------------------------------------------------
ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/db_1

#-------------------------------------------------------------------------------
# Specify the complete path of the Oracle Base. 
#-------------------------------------------------------------------------------
ORACLE_BASE=/u01/app/oracle

#-------------------------------------------------------------------------------
# Specify the installation edition of the component.                     
#                                                             
# The value should contain only one of these choices.  
      
#   - EE     : Enterprise Edition 

#-------------------------------------------------------------------------------
oracle.install.db.InstallEdition=EE

###############################################################################
#                                                                             #
# PRIVILEGED OPERATING SYSTEM GROUPS                                          #
# ------------------------------------------                                  #
# Provide values for the OS groups to which OSDBA and OSOPER privileges       #
# needs to be granted. If the install is being performed as a member of the   #
# group "dba", then that will be used unless specified otherwise below.       #
#                                                                             #
# The value to be specified for OSDBA and OSOPER group is only for UNIX based #
# Operating System.                                                           #
#                                                                             #
###############################################################################

#------------------------------------------------------------------------------
# The DBA_GROUP is the OS group which is to be granted OSDBA privileges.
#-------------------------------------------------------------------------------
oracle.install.db.DBA_GROUP=dba

#------------------------------------------------------------------------------
# The OPER_GROUP is the OS group which is to be granted OSOPER privileges.
# The value to be specified for OSOPER group is optional.
#------------------------------------------------------------------------------
oracle.install.db.OPER_GROUP=

#------------------------------------------------------------------------------
# The BACKUPDBA_GROUP is the OS group which is to be granted OSBACKUPDBA privileges.
#------------------------------------------------------------------------------
oracle.install.db.BACKUPDBA_GROUP=dba

#------------------------------------------------------------------------------
# The DGDBA_GROUP is the OS group which is to be granted OSDGDBA privileges.
#------------------------------------------------------------------------------
oracle.install.db.DGDBA_GROUP=dba

#------------------------------------------------------------------------------
# The KMDBA_GROUP is the OS group which is to be granted OSKMDBA privileges.
#------------------------------------------------------------------------------
oracle.install.db.KMDBA_GROUP=dba

###############################################################################
#                                                                             #
#                               Grid Options                                  #
#                                                                             #
###############################################################################
#------------------------------------------------------------------------------
# Specify the type of Real Application Cluster Database
# 
#   - ADMIN_MANAGED: Admin-Managed
#   - POLICY_MANAGED: Policy-Managed
# 
# If left unspecified, default will be ADMIN_MANAGED 
#------------------------------------------------------------------------------
oracle.install.db.rac.configurationType=

#------------------------------------------------------------------------------
# Value is required only if RAC database type is ADMIN_MANAGED
# 
# Specify the cluster node names selected during the installation.
# Leaving it blank will result in install on local server only (Single Instance)
# 
# Example : oracle.install.db.CLUSTER_NODES=node1,node2
#------------------------------------------------------------------------------
oracle.install.db.CLUSTER_NODES=

#------------------------------------------------------------------------------
# This variable is used to enable or disable RAC One Node install.
#
#   - true  : Value of RAC One Node service name is used.
#   - false : Value of RAC One Node service name is not used.
#
# If left blank, it will be assumed to be false.
#------------------------------------------------------------------------------
oracle.install.db.isRACOneInstall=false

#------------------------------------------------------------------------------
# Value is required only if oracle.install.db.isRACOneInstall is true.
# 
# Specify the name for RAC One Node Service
#------------------------------------------------------------------------------
oracle.install.db.racOneServiceName=

#------------------------------------------------------------------------------
# Value is required only if RAC database type is POLICY_MANAGED
# 
# Specify a name for the new Server pool that will be configured
# Example : oracle.install.db.rac.serverpoolName=pool1
#------------------------------------------------------------------------------
oracle.install.db.rac.serverpoolName=

#------------------------------------------------------------------------------
# Value is required only if RAC database type is POLICY_MANAGED
# 
# Specify a number as cardinality for the new Server pool that will be configured
# Example : oracle.install.db.rac.serverpoolCardinality=2
#------------------------------------------------------------------------------
oracle.install.db.rac.serverpoolCardinality=0

###############################################################################
#                                                                             #
#                        Database Configuration Options                       #
#                                                                             #
###############################################################################

#-------------------------------------------------------------------------------
# Specify the type of database to create.
# It can be one of the following:
#   - GENERAL_PURPOSE                       
#   - DATA_WAREHOUSE 
# GENERAL_PURPOSE: A starter database designed for general purpose use or transaction-heavy applications.
# DATA_WAREHOUSE : A starter database optimized for data warehousing applications.
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.type=GENERAL_PURPOSE

#-------------------------------------------------------------------------------
# Specify the Starter Database Global Database Name. 
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.globalDBName=

#-------------------------------------------------------------------------------
# Specify the Starter Database SID.
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.SID=

#-------------------------------------------------------------------------------
# Specify whether the database should be configured as a Container database.
# The value can be either "true" or "false". If left blank it will be assumed
# to be "false".
#-------------------------------------------------------------------------------
oracle.install.db.ConfigureAsContainerDB=false

#-------------------------------------------------------------------------------
# Specify the  Pluggable Database name for the pluggable database in Container Database.
#-------------------------------------------------------------------------------
oracle.install.db.config.PDBName=

#-------------------------------------------------------------------------------
# Specify the Starter Database character set.
#                                               
#  One of the following
#  AL32UTF8, WE8ISO8859P15, WE8MSWIN1252, EE8ISO8859P2,
#  EE8MSWIN1250, NE8ISO8859P10, NEE8ISO8859P4, BLT8MSWIN1257,
#  BLT8ISO8859P13, CL8ISO8859P5, CL8MSWIN1251, AR8ISO8859P6,
#  AR8MSWIN1256, EL8ISO8859P7, EL8MSWIN1253, IW8ISO8859P8,
#  IW8MSWIN1255, JA16EUC, JA16EUCTILDE, JA16SJIS, JA16SJISTILDE,
#  KO16MSWIN949, ZHS16GBK, TH8TISASCII, ZHT32EUC, ZHT16MSWIN950,
#  ZHT16HKSCS, WE8ISO8859P9, TR8MSWIN1254, VN8MSWIN1258
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.characterSet=

#------------------------------------------------------------------------------
# This variable should be set to true if Automatic Memory Management 
# in Database is desired.
# If Automatic Memory Management is not desired, and memory allocation
# is to be done manually, then set it to false.
#------------------------------------------------------------------------------
oracle.install.db.config.starterdb.memoryOption=false

#-------------------------------------------------------------------------------
# Specify the total memory allocation for the database. Value(in MB) should be
# at least 256 MB, and should not exceed the total physical memory available 
# on the system.
# Example: oracle.install.db.config.starterdb.memoryLimit=512
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.memoryLimit=

#-------------------------------------------------------------------------------
# This variable controls whether to load Example Schemas onto
# the starter database or not.
# The value can be either "true" or "false". If left blank it will be assumed
# to be "false".
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.installExampleSchemas=false

###############################################################################
#                                                                             #
# Passwords can be supplied for the following four schemas in the       #
# starter database:                  #
#   SYS                                                                       #
#   SYSTEM                                                                    #
#   DBSNMP (used by Enterprise Manager)                                       #
#                                                                             #
# Same password can be used for all accounts (not recommended)         #
# or different passwords for each account can be provided (recommended)       #
#                                                                             #
###############################################################################

#------------------------------------------------------------------------------
# This variable holds the password that is to be used for all schemas in the
# starter database.
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.password.ALL=

#-------------------------------------------------------------------------------
# Specify the SYS password for the starter database.
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.password.SYS=

#-------------------------------------------------------------------------------
# Specify the SYSTEM password for the starter database.
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.password.SYSTEM=

#-------------------------------------------------------------------------------
# Specify the DBSNMP password for the starter database.
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.password.DBSNMP=

#-------------------------------------------------------------------------------
# Specify the PDBADMIN password required for creation of Pluggable Database in the Container Database.
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.password.PDBADMIN=

#-------------------------------------------------------------------------------
# Specify the management option to use for managing the database.
# Options are:
# 1. CLOUD_CONTROL - If you want to manage your database with Enterprise Manager Cloud Control along with Database Express.
# 2. DEFAULT   -If you want to manage your database using the default Database Express option.
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.managementOption=DEFAULT

#-------------------------------------------------------------------------------
# Specify the OMS host to connect to Cloud Control.
# Applicable only when oracle.install.db.config.starterdb.managementOption=CLOUD_CONTROL
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.omsHost=

#-------------------------------------------------------------------------------
# Specify the OMS port to connect to Cloud Control.
# Applicable only when oracle.install.db.config.starterdb.managementOption=CLOUD_CONTROL
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.omsPort=0

#-------------------------------------------------------------------------------
# Specify the EM Admin user name to use to connect to Cloud Control.
# Applicable only when oracle.install.db.config.starterdb.managementOption=CLOUD_CONTROL
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.emAdminUser=

#-------------------------------------------------------------------------------
# Specify the EM Admin password to use to connect to Cloud Control.
# Applicable only when oracle.install.db.config.starterdb.managementOption=CLOUD_CONTROL
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.emAdminPassword=

###############################################################################
#                                                                             #
# SPECIFY RECOVERY OPTIONS                                                #
# ------------------------------------                                #
# Recovery options for the database can be mentioned using the entries below  #
#                                                                             #
###############################################################################

#------------------------------------------------------------------------------
# This variable is to be set to false if database recovery is not required. Else 
# this can be set to true.
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.enableRecovery=false

#-------------------------------------------------------------------------------
# Specify the type of storage to use for the database.
# It can be one of the following:
#   - FILE_SYSTEM_STORAGE
#   - ASM_STORAGE
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.storageType=

#-------------------------------------------------------------------------------
# Specify the database file location which is a directory for datafiles, control
# files, redo logs.         
#
# Applicable only when oracle.install.db.config.starterdb.storage=FILE_SYSTEM_STORAGE 
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.fileSystemStorage.dataLocation=

#-------------------------------------------------------------------------------
# Specify the recovery location.
#
# Applicable only when oracle.install.db.config.starterdb.storage=FILE_SYSTEM_STORAGE 
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.fileSystemStorage.recoveryLocation=

#-------------------------------------------------------------------------------
# Specify the existing ASM disk groups to be used for storage.
#
# Applicable only when oracle.install.db.config.starterdb.storageType=ASM_STORAGE
#-------------------------------------------------------------------------------
oracle.install.db.config.asm.diskGroup=

#-------------------------------------------------------------------------------
# Specify the password for ASMSNMP user of the ASM instance.                 
#
# Applicable only when oracle.install.db.config.starterdb.storage=ASM_STORAGE 
#-------------------------------------------------------------------------------
oracle.install.db.config.asm.ASMSNMPPassword=

#------------------------------------------------------------------------------
# Specify the My Oracle Support Account Username.
#
#  Example   : MYORACLESUPPORT_USERNAME=abc@oracle.com
#------------------------------------------------------------------------------
MYORACLESUPPORT_USERNAME=

#------------------------------------------------------------------------------
# Specify the My Oracle Support Account Username password.
#
# Example    : MYORACLESUPPORT_PASSWORD=password
#------------------------------------------------------------------------------
MYORACLESUPPORT_PASSWORD=

#------------------------------------------------------------------------------
# Specify whether to enable the user to set the password for
# My Oracle Support credentials. The value can be either true or false.
# If left blank it will be assumed to be false.
#
# Example    : SECURITY_UPDATES_VIA_MYORACLESUPPORT=true
#------------------------------------------------------------------------------
SECURITY_UPDATES_VIA_MYORACLESUPPORT=false

#------------------------------------------------------------------------------
# Specify whether user doesn't want to configure Security Updates.
# The value for this variable should be true if you don't want to configure
# Security Updates, false otherwise.
#
# The value can be either true or false. If left blank it will be assumed
# to be false.
#
# Example    : DECLINE_SECURITY_UPDATES=false
#------------------------------------------------------------------------------
DECLINE_SECURITY_UPDATES=true

#------------------------------------------------------------------------------
# Specify the Proxy server name. Length should be greater than zero.
#
# Example    : PROXY_HOST=proxy.domain.com 
#------------------------------------------------------------------------------
PROXY_HOST=

#------------------------------------------------------------------------------
# Specify the proxy port number. Should be Numeric and at least 2 chars.
#
# Example    : PROXY_PORT=25
#------------------------------------------------------------------------------
PROXY_PORT=

#------------------------------------------------------------------------------
# Specify the proxy user name. Leave PROXY_USER and PROXY_PWD
# blank if your proxy server requires no authentication.
#
# Example    : PROXY_USER=username
#------------------------------------------------------------------------------
PROXY_USER=

#------------------------------------------------------------------------------
# Specify the proxy password. Leave PROXY_USER and PROXY_PWD  
# blank if your proxy server requires no authentication.
#
# Example    : PROXY_PWD=password
#------------------------------------------------------------------------------
PROXY_PWD=

#------------------------------------------------------------------------------
# Specify the Oracle Support Hub URL. 
# 
# Example    : COLLECTOR_SUPPORTHUB_URL=https://orasupporthub.company.com:8080/
#------------------------------------------------------------------------------
COLLECTOR_SUPPORTHUB_URL=

dbca -silent -createDatabase \
  -templateName General_Purpose.dbc \
  -gdbname cdb1 -sid cdb1 -responseFile NO_VALUE \
  -characterSet AL32UTF8 \
  -sysPassword OraPasswd1 \
  -systemPassword OraPasswd1 \
  -createAsContainerDatabase true \
  -numberOfPDBs 1 \
  -pdbName pdb1 \
  -pdbAdminPassword OraPasswd1 \
  -databaseType MULTIPURPOSE \
  -automaticMemoryManagement false \
  -storageType FS \
  -ignorePreReqs

  

2017년 3월 9일 목요일

나의 사적인 구름 연습 기계들

피씨에 뱀웨어 로 아주 지극히 개인적인 구름 연습 기계들을 만들었다.

일단 창문 8.1 , 그리고 이번에 신상인 오라꿀 12.2 버전을 팽귄에다 설치를 했다.

추가로 악마 11.0 에 돌고래 5.7 을

추가로 악마 11.0 에 코끼리 9.6 을

추가로 창문 서버 2012 에 SQL 서버 2014 를

공부 삼아서 지극히 개인적인 구름에 연습 기계들을 하나씩 하나씩 올려 봐야 겠다.

오늘은 뭘 올릴까나 ?

2017년 2월 20일 월요일

과거의 기억에서 부터


옛날에 물이 먹어서 고장이 났던 핸드폰이 있었다.
우연히 다시 찾아서 전원을 켜보니 다시 실행이 된다.
가장 마지막 메일이 2014년 10월 달  그 이 후로 부터 2년  반 만에 다시 만났다.
과거의 기억도 좀 남아 있고 또 추억의 사진도 몇장 건졌다.
다시 친해져 볼까 한다. 이 아이랑

2017년 2월 15일 수요일

MySQL 의 날짜와 로우넘버

MySQL 에서 Date 와 Rownum 을 만드는 쿼리 임

세상에는 나 보다 똑똑 하신 분들이 많네


/**
    make date calander 
*/


--
-- 현재 일자 부터 과거 로 몇일 
--
 SELECT dt.yyyymmdd
    FROM (
           SELECT
               curdate() - INTERVAL (100*a.a + (10 * b.a) + (c.a)) DAY AS yyyymmdd
           FROM (SELECT 0 AS a
                 UNION ALL SELECT 1
                 UNION ALL SELECT 2
                 UNION ALL SELECT 3
                 ) AS a
             CROSS JOIN (SELECT 0 AS a
                         UNION ALL SELECT 1
                         UNION ALL SELECT 2
                         UNION ALL SELECT 3
                         UNION ALL SELECT 4
                         UNION ALL SELECT 5
                         UNION ALL SELECT 6
                         UNION ALL SELECT 7
                         UNION ALL SELECT 8
                         UNION ALL SELECT 9) AS b
             CROSS JOIN (SELECT 0 AS a
                         UNION ALL SELECT 1
                         UNION ALL SELECT 2
                         UNION ALL SELECT 3
                         UNION ALL SELECT 4
                         UNION ALL SELECT 5
                         UNION ALL SELECT 6
                         UNION ALL SELECT 7
                         UNION ALL SELECT 8
                         UNION ALL SELECT 9) AS c
         ) dt
    where yyyymmdd BETWEEN curdate() - INTERVAL 10 day and curdate()
    order by 1
;

--
-- 년도별 칼렌더
--

SELECT
  ym
  , min(CASE dw
        WHEN 1
          THEN d END) Sun
  , min(CASE dw
        WHEN 2
          THEN d END) Mon
  , min(CASE dw
        WHEN 3
          THEN d END) Tue
  , min(CASE dw
        WHEN 4
          THEN d END) Wed
  , min(CASE dw
        WHEN 5
          THEN d END) Thu
  , min(CASE dw
        WHEN 6
          THEN d END) Fri
  , min(CASE dw
        WHEN 7
          THEN d END) Sat
FROM
  (
    SELECT
        date_format(dt, '%Y%m') ym
      , WEEK(dt)                w
      , Day(dt)                 d
      , DAYOFWEEK(dt)           dw
    FROM
      (
        SELECT concat(y, '0101') + INTERVAL a * 100 + b * 10 + c DAY AS dt
        FROM
          (
            SELECT 0 a
            UNION ALL SELECT 1
            UNION ALL SELECT 2
            UNION ALL SELECT 3
          ) aa,
          (
            SELECT 0 b
            UNION ALL SELECT 1
            UNION ALL SELECT 2
            UNION ALL SELECT 3
            UNION ALL SELECT 4
            UNION ALL SELECT 5
            UNION ALL SELECT 6
            UNION ALL SELECT 7
            UNION ALL SELECT 8
            UNION ALL SELECT 9
          ) bb,
          (
            SELECT 0 c
            UNION ALL SELECT 1
            UNION ALL SELECT 2
            UNION ALL SELECT 3
            UNION ALL SELECT 4
            UNION ALL SELECT 5
            UNION ALL SELECT 6
            UNION ALL SELECT 7
            UNION ALL SELECT 8
            UNION ALL SELECT 9
          ) cc,
          (
            SELECT '2018' y
          ) dd
        WHERE a * 100 + b * 10 + c < DayOfYear(concat(y, '1231'))
      ) a
  ) a
GROUP BY ym, w
;



/**
    MySQL Mk Rownum

*/

select
    @rnum := @rnum + 1 as Rownum
from
    ( select @rnum := -1 ) r

2017년 2월 10일 금요일

Tibero DB Create

간략하게 티베로 디비 만드는 스크립트 입니다.

# vi /etc/sysctl.conf
kernel.shmmax = 1073741824
kernel.shmall = 2097152
kernel.sem = 10000 32000 10000 10000
kernel.shmmni = 4096
fs.file-max = 6815744
net.ipv4.ip_local_port_range = 1024 65000

* /etc/security/limits.conf 를 수정
# vi /etc/security/limits.conf
tibero soft nproc 2047
tibero hard nproc 16384
tibero soft nofile 1024
tibero hard nofile 65536

seforce 0

groupadd dba
useradd -g dba tibero

export  TB_BASE=/opt/tibero
export  TB_HOME=/opt/tibero/tibero6
export  TB_SID=tibero
export  TB_VER=tibero6
export  TB_PROF_DIR=$TB_HOME/bin/prof
export  JAVA_HOME=/opt/java

export  PATH=$TB_HOME/bin:$TB_HOME/client/bin:$JAVA_HOME/bin:/bin:/usr/bin:/usr/local/bin:.
export  LD_LIBRARY_PATH=$TB_HOME/lib:$TB_HOME/client/lib:$JAVA_HOME/lib:/lib64:/usr/lib64:/usr/local/lib:.

######## TIBERO alias ########
alias tbhome='cd $TB_HOME'
alias tbbin='cd $TB_HOME/bin'
alias tblog='cd $TB_HOME/instance/$TB_SID/log'
alias tbcfg='cd $TB_HOME/config'
alias tbcfgv='vi $TB_HOME/config/$TB_SID.tip'
alias tbcli='cd ${TB_HOME}/client/config'
alias tbcliv='vi ${TB_HOME}/client/config/tbdsn.tbr'

cp license.xml $TB_HOME/license
cd $TB_HOME/bin
sh $TB_HOME/config/get_tip.sh

/*

############################################
### Database Identification
############################################
DB_NAME=TBTAC
LISTENER_PORT=8629

############################################
### File Configuration
############################################
DB_CREATE_FILE_DEST="/tibero/tbdata"
CONTROL_FILES="/dev/mapper/sdiskvg1-lvctl_20m_01vg1","/dev/mapper/sdiskvg2-lvctl_20m_01vg2"

############################################
### Memory & Session
############################################
MAX_SESSION_COUNT=50
TOTAL_SHM_SIZE=768M
MEMORY_TARGET=1536M

DBMS_LOG_TOTAL_SIZE_LIMIT=120M
TRACE_LOG_TOTAL_SIZE_LIMIT=300M
############################################
### Undo and Rollback Segments
############################################
#UNDO_RETENTION=900
UNDO_TABLESPACE="UNDOTBS0"

############################################
### Cache and I/O
############################################
DB_BLOCK_SIZE=8192
#DB_FILE_MULTIBLOCK_READ_COUNT=64

###########################################
### Archive Log Mode
############################################
LOG_ARCHIVE_FORMAT="arcTAC%t_%s_%r.arc"
LOG_ARCHIVE_DEST="/tibero/tibarch"
#_ARCHIVE_LAG_TARGET=21600
#_ARCHIVE_CLOSED_THREAD=Y
#_ARCHIVE_CLOSED_THREAD_DIE_ON_FAIL=N

############################################
### Statistics
############################################
#AUDIT_TRAIL=NONE
#AUDIT_SYS_OPERATIONS=N
#AUDIT_FILE_DEST="/tibero/tiblog/tac/audit"

############################################
### APM Report
############################################
#AUTOMATIC_PERFORMANCE_MONITORING=Y
#APM_SNAPSHOT_SAMPLING_INTERVAL=60
#APM_SNAPSHOT_RETENTION=7
#APM_SNAPSHOT_TOP_SQL_CNT=10

############################################
### Checkpoint
############################################
#_LOG_INC_CHECKPOINT_TIMEOUT=1
#_LOG_INC_CKPT_LAG_LIMIT_PCT=25

############################################
### Log Dest
############################################
#CM_LOG_DEST="/tibero/tiblog/tac/log/cm"
#DBMS_LOG_DEST="/tibero/tiblog/tac/log/dbmslog"
#EVENT_TRACE_DEST="/tibero/tiblog/tac/log/event"
#LSNR_LOG_DEST="/tibero/tiblog/tac/log/lsnr"
#SQL_TRACE_DEST="/tibero/tiblog/tac/log/sqltrace"
#TRACE_DUMP_DEST="/tibero/tiblog/tac/dump/tracedump"
#TRACE_LOG_DEST="/tibero/tiblog/tac/log/tracelog"

############################################
### Cluster Database
############################################
CLUSTER_DATABASE=Y
THREAD=0
LOCAL_CLUSTER_ADDR=10.10.1.101
LOCAL_CLUSTER_PORT=8631
LOCAL_CLUSTER_VIP=192.167.90.111
LOCAL_CLUSTER_NIC=enp0s8
LOCAL_CLUSTER_VIP_NETMASK=255.255.255.0
LOCAL_CLUSTER_VIP_BROADCAST=192.167.90.255

############################################
### TBCM Parameter
############################################
CM_CLUSTER_MODE=ACTIVE_SHARED
CM_FILE_NAME="/dev/mapper/sdiskvg1-lvtbcm_10m_01vg1","/dev/mapper/sdiskvg2-lvtbcm_10m_01vg2"
CM_PORT=8632
CM_HEARTBEAT_EXPIRE=60
CM_WATCHDOG_EXPIRE=50
#CM_NET_EXPIRE_MARGIN=10
CM_AUTO_VIP_FAILBACK=N
CM_ENABLE_CM_DOWN_NOTIFICATION=Y
CM_ENABLE_FAST_NET_ERROR_DETECTION=Y
CM_FENCE=Y
#_CM_ENABLE_VIP_ALIAS_CHECK=N
#_CM_ENABLE_VIP_ALIAS_RETRY=N
#_CM_REBOOT_RETRY_COUNT=3

############################################
### Miscellaneous
############################################
#ACTIVE_SESSION_HISTORY=Y
USE_NET_KEEPALIVE=Y
USE_RECYCLEBIN=Y
NLS_DATE_FORMAT="YYYY/MM/DD HH24:MI:SS"
#BIND_VARIABLE_CAPTURE=Y
#JOB_HISTORY_LOG=Y

    TBTAC1=(
        (INSTANCE=(HOST=192.167.90.101)
              (PORT=8629)
              (DB_NAME=TBTAC)
        )
    )

    TBTAC=(
        (INSTANCE=(HOST=192.167.90.111)
              (PORT=8629)
              (DB_NAME=TBTAC)
        )
        (INSTANCE=(HOST=192.167.90.211)
              (PORT=8629)
              (DB_NAME=TBTAC)
        )
        (LOAD_BALANACE=Y)
        (USE_FAILOVER=Y)
    )

*/

tbboot nomount

tbsql sys/tibero

/*

CREATE DATABASE "tibero"
    USER SYS IDENTIFIED BY TIBERO
MAXDATAFILES 4096
maxinstances 8
CHARACTER SET MSWIN949
LOGFILE GROUP 0 ('redo01a.redo','redo01b.redo') SIZE 1024M,
        GROUP 1 ('redo11a.redo','redo11b.redo') SIZE 1024M,
        GROUP 2 ('redo21a.redo','redo21b.redo') SIZE 1024M,
        GROUP 3 ('redo31a.redo','redo31b.redo') SIZE 1024M,
        GROUP 4 ('redo41a.redo','redo41b.redo') SIZE 1024M
MAXLOGFILES 100
maxloggroups 255
MAXLOGMEMBERS 10
NOARCHIVELOG
DATAFILE 'system001.dtf' SIZE 1024M
AUTOEXTEND ON NEXT 16M MAXSIZE 3072M
DEFAULT TABLESPACE USR
DATAFILE 'usr001.dtf' SIZE 128M
AUTOEXTEND ON NEXT 16M MAXSIZE 3072M
DEFAULT TEMPORARY TABLESPACE TEMP
TEMPFILE 'temp001.dtf' SIZE 1024M
AUTOEXTEND ON NEXT 16M MAXSIZE 10240M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
UNDO TABLESPACE UNDO
DATAFILE 'undo001.dtf' SIZE 1024M
AUTOEXTEND ON NEXT 16M MAXSIZE 10240M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

*/

tbdown normal

tbboot 
sh $TB_HOME/scripts/system.sh -p1 tibero -p2 syscat -a1 y -a2 y -a3 y -a4 y

2017년 2월 9일 목요일

우리 회사 데이터베이스를 티베로로 변경하기







우리 회사 데이터베이스를 티베로로 변경하기

우승헌 지음 | 영진닷컴 | 2017년 01월 20일 출간 


 서평: 실제 프로젝트를 진행한 담당자가 적은 책이다.
 티베로의 기술력을 간접적으로  알수 있는 좋은 책

PostgreSQL 성능 최적화


 
[도서] PostgreSQL 성능 최적화
저자 그레고리 스미스 | 출판사 지앤선


서평:
 번역이 엉망이다. 무슨 번역기 돌린듯한 느낌 그래서 내용에 집중을 못하겠다.

2017년 1월 24일 화요일

MySQL or MariaDB FederatedX Storage Engine

MySQL 계열에서 DB Link 기능 같은걸 찾아 보다 찾은 Storage Engine 이다.
MySQL 은 Federated Engine 이 기본적으로 탑재 되어 있다. 그리고 X 는 MariaDB에서 사용하는 확장 판이다.
또한 타겟에서 끌고 올때 소스의 버퍼에 올리기 때문에 통으로나 데이터량이 많으면 소스에 스와핑이 생기거나 OOM 이 생길 수 있다.


/**
    MySQL or MariaDB FederatedX Storage Engine 
    ( like dblink of oracle)
*/

--
-- 01. Plugin Install
--

INSTALL PLUGIN federated SONAME 'ha_federatedx.so';

# my.cnf
federated # 이부분 추가

--
-- 02. Configure
--

connection=scheme://username:password@hostname:port/database/tablename
connection=scheme://username@hostname/database/tablename
connection=scheme://username:password@hostname/database/tablename
connection=scheme://username:password@hostname/database/tablename
ex)
connection=mysql://username:password@hostname:port/database/tablename



onnection="connection_one"
connection="connection_one/table_foo"

ex)
# 사용시 table name change 안됨 
create server 'server_one' foreign data wrapper 'mysql' options
  (HOST '127.0.0.1',
  DATABASE 'db1',
  USER 'root',
  PASSWORD '',
  PORT 3306,
  SOCKET '',
  OWNER 'root');

ex)

CREATE TABLE federatedx.t1 (
  `id` int(20) NOT NULL,
  `name` varchar(64) NOT NULL default ''
  )
ENGINE="FEDERATED" DEFAULT CHARSET=latin1
CONNECTION='server_one';

# table name change 가능
CREATE TABLE test_table ENGINE=FEDERATED CONNECTION='mysql://root@127.0.0.1:9306/federatedx/test_federatedx';

--
-- 03. explain
-- ( 원격 테이블도 인덱스를 사용할수 있다.)

+------+-------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id   | select_type | table      | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+------+-------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|    1 | SIMPLE      | ox_clients | const | PRIMARY       | PRIMARY | 3       | const |    1 |   100.00 |       |
+------+-------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

--
-- 99. 작업 절차
--

INSTALL PLUGIN federated SONAME 'ha_federatedx.so';

create database if not exists dblink;

grant all on dblink.* to `root`@`localhost`;

use dblink;

create server 'server_one' foreign data wrapper 'mysql' options
(
    HOST '127.0.0.1',
    DATABASE 'db1',
    USER 'root',
    PASSWORD '',
    PORT 3306,
    SOCKET '',
    OWNER 'root'
);

create table if not exists dblink.tbl_link_01
ENGINE="FEDERATED" DEFAULT CHARSET=latin1
CONNECTION='server_one';

create table if not exists dblink.tbl_link_02
ENGINE="FEDERATED" DEFAULT CHARSET=latin1
CONNECTION='mysql://root:aaaa@127.0.0.1:9306/federatedx/test_federatedx';

CREATE TABLE dblink.tbl_link_03
(
  id     int(20) NOT NULL auto_increment,
  name   varchar(32) NOT NULL default '',
  other  int(20) NOT NULL default '0',
  PRIMARY KEY  (id),
  KEY name (name),
  KEY other_key (other)
)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION='mysql://root@127.0.0.1:9306/federatedx/test_federatedx';


2017년 1월 23일 월요일

SQL 레벨업 DB 성능 최적화를 위한 SQL 실전 가이드









SQL 레벨업 DB 성능 최적화를 위한 SQL 실전 가이드

미크 지음 | 윤인성 옮김 | 한빛미디어 | 2016년 01월 30일 출간 

서평:
  가끔은 그럴때가 있다. 어려운 문제를 어떻게 SQL 로 풀어 낼까 하고 그런 고민에 대한 해답을 주는 책이다. 이책도 일본 엔지니어가 쓴 책인데...
 

2017년 1월 18일 수요일

Netflix Billing Migration to AWS

Netflix Billing Migration 관련 Tech Blog 자료를 보다 간략하게 정리를 했음 기존 Oracle 에서 AWS 의 MySQL 로 넘어 가는 과정을 정리 했는데 느끼는 점이 많음

IDC (ORACLE) -> AWS(EC2+MySQL5.6) -> AWS Auroa

Master Replication -> DBDC

Read Slave -> Async Replication

http://techblog.netflix.com/2016/06/netflix-billing-migration-to-aws.html http://techblog.netflix.com/2016/07/netflix-billing-migration-to-aws-part-ii.html http://techblog.netflix.com/2016/08/netflix-billing-migration-to-aws-part.html


###
# Netflix MySQL Billing Database
# MySQL 5.6 + Linux 
###

--
-- 01. Bluk Insert
--

innodb_log_file_size = 2048M
innodb_lru_scan_depth = 128
innodb_adaptive_hash_index = off
innodb_flush_neighbors = off
transaction_isolation = READ-COMMITTED
query_cache_size = 0
query_cache_type = 0
innodb_doublewrite = off


--
-- 02. High Transction
-- 

innodb_log_file_size = 2048M
innodb_lru_scan_depth = 128
innodb_adaptive_hash_index = off
innodb_flush_neighbors = off
transaction_isolation = READ-COMMITTED
query_cache_size = 0
query_cache_type = 0
innodb_doublewrite = off

innodb_max_dirty_pages_pct = 80
innodb_buffer_pool_instances = 8
innodb_log_buffer_size = 64<


--
-- 03 os kernel
--

kernel.numa_balancing = 0 # instead of numactl

vm.dirty_ratio = 40
vm.dirty_background_ratio = 5
vm.swappiness = 0
vm.aio-max-nr = 65535
vm.rq_affinity = 2
vm.scheduler = cfq

2017년 1월 13일 금요일

전문가를 위한 트러블슈팅 오라클 퍼포먼스








전문가를 위한 트러블슈팅 오라클 퍼포먼스

2판
크리스티안 안토니니 지음 | (주)위즈베이스 옮김 | 제이펍 | 2016년 05월 03일 출간 

서평:
  해당 책은 국내 오라클 컨설팅 회사인 위즈베이스에서 번역을 한 오라클 관련 책이다. 
책의 내용도 심도 깊은 흥미 있는 이야기들도 씌여 있고 또한 번역도 읽기에 불편함이 없이  편하다.

2017년 1월 9일 월요일

MySQL류의 RDBMS 에서의 Slow Query Log 방법및 3th party Tools

-- logging stop

set global slow_query_log = OFF;

--
-- Log file move 이 후 
--
FLUSH LOGS;


-- 수집 조건 설정

set global long_query_time = 1; --  전체 적으로 성능 부분을 볼려면 0초로 설정 하여 모든 쿼리를 로깅함
set global log_queries_not_using_indexes = ON;

-- logging start
set global slow_query_log = ON;

--
-- 분석 Tools
-- ( Percona Toolkit )
--  https://www.github.com/percona/percona-toolkit

pt-query-digest 
Usage: pt-query-digest [OPTIONS] [FILES] [DSN]

2017년 1월 3일 화요일

오라클 레벨업 최고의 전문가 그룹이 알려주는 오라클 실전 100가지 기술



 


오라클 레벨업 최고의 전문가 그룹이 알려주는 오라클 실전 100가지 기술

스즈키 겐고 , 다마오키 다케히로, 시오바라 고타, 고바야시 오사무, 오모리 신지, 우치무라 도모아키 지음 | 윤인성, / 옮김 | 한빛미디어 | 2016년 12월 20일 출간 

서평:
 일본 엔지니어들의 기술력에 대해서 다시 한번 생각해 보게 하는 책이다. 오라클의 핵심적인 100가지의 팁을 예제와 같이 설명을 하여 이해 하기 쉽게 쓰여졌다. 나도 나중에 이런 책을 내보고 싶다.