JJON's StorY

mysql에 테이블 생성하는데 에러가 발생 했다.

16:36:12 mysql_13306.sock > create table push_test

-> (col1 bigint,

-> col2 varchar(256),

-> PRIMARY KEY (col1, col2));

ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

PK제약인가?

다시 생성

16:36:13 mysql_13306.sock > create table push_test

-> (col1 bigint,

-> col2 varchar(256),

-> KEY push_test_idx01 (col1, col2));

ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

여전히 안되는군.

16:45:32 mysql_13306.sock > show global variables like '%char%';

+--------------------------+-------------------------------------------------------+

| Variable_name | Value |

+--------------------------+-------------------------------------------------------+

| character_set_client | utf8mb4 |

| character_set_connection | utf8mb4 |

| character_set_database | utf8mb4 |

| character_set_filesystem | binary |

| character_set_results | utf8mb4 |

| character_set_server | utf8mb4 |

| character_set_system | utf8 |

| character_sets_dir | /data1/DBSpace_mysql/MySQL_5628/13306/share/charsets/ |

+--------------------------+-------------------------------------------------------+

8 rows in set (0.00 sec)

인덱스 만들때 key 사이즈가 767바이트를 넘으면 안되는데,

utf8mb4는 1글자당 4바이트.

그럼 191글자 까지 가능.

16:51:33 mysql_13306.sock > create table push_test

-> (col1 bigint,

-> col2 varchar(256),

-> KEY push_test_idx01 (col1, col2(192)));

ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

16:51:45 mysql_13306.sock > create table push_test

-> (col1 bigint,

-> col2 varchar(256),

-> KEY push_test_idx01 (col1, col2(191)));

Query OK, 0 rows affected (0.05 sec)

인덱스 한번 만들기 힘드네.

mysql을 사용하면 안되는 이유...

저작자표시 비영리 변경금지 (새창열림)

'MySQL' 카테고리의 다른 글

MySQL Hash Join Optimization (0)	2019.10.02
MySQL 시퀀스(AUTO_INCREMENT) 조회 및 초기화 (0)	2018.09.18
MySQL 설치 (5.6 or 5.7) (0)	2017.03.09
MySQL load data (0)	2016.10.27
MySQL 사용자 계정 추가, 변경, 자원 제한 (0)	2016.10.20

ASMDISK (size : 7TB) 추가 작업 진행 하였습니다.

ASMDISK는 추가 시 새로운 DISK와 기존 DISK의 rebalance 작업이 이루어 집니다.

rebalance power(11.2.0.2 이상)는 1~1024 까지 설정 가능하며, 1024로 설정 시 disk I/O는 rebalance 작업 우선으로 서비스가 되지 않습니다.

참고로 낮은 값으로 설정 시 구글링 해보면 몇 일이 지나도 끝나지 않는다는 글들이 종종 보입니다.

저희는 최초 100으로 설정 후 영향이 없어 300으로 재 설정 하여 진행하였습니다.

시간은 15:55 ~ 20:22(약 4:20) 까지 소요 되었습니다.

asmdisk추가.sql

-- 가용량 확인

mpods01:/home/oracle>asmcmd

ASMCMD> lsdg

State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name

MOUNTED EXTERN N 512 4096 4194304 19922792 282260 0 282260 0 N DATA/

-- lvs 확인

[root@mpods01 ~]# lvs

LV VG Attr LSize Pool Origin Data% Meta% Move Log Cpy%Sync Convert

lvol_pre01 prevg01 -wi-ao---- 512.00g

lvol_pre01 prevg02 -wi-ao---- 512.00g

lvol_pre01 prevg03 -wi-ao---- 512.00g

lvol_pre01 prevg04 -wi-ao---- 512.00g

lvol_pre01 prevg05 -wi-ao---- 512.00g

lvol_pre01 prevg06 -wi-ao---- 512.00g

lvol_pre01 prevg07 -wi-ao---- 512.00g

lvol_pre01 prevg08 -wi-ao---- 512.00g

lvol_pre01 prevg09 -wi-ao---- 512.00g

lvol_pre01 prevg10 -wi-ao---- 512.00g

lvol_pre01 prevg11 -wi-ao---- 512.00g

lvol_pre01 prevg12 -wi-ao---- 512.00g

lvol_pre01 prevg13 -wi-ao---- 512.00g

lvol_pre01 prevg14 -wi-ao---- 512.00g

lvol_pre01 prevg15 -wi-ao---- 512.00g

lvol_pre01 prevg16 -wi-ao---- 512.00g

lvol_pre01 prevg17 -wi-ao---- 512.00g

lvol_pre01 prevg18 -wi-ao---- 512.00g

lvol_pre01 prevg19 -wi-ao---- 512.00g

lvol_pre01 prevg20 -wi-ao---- 512.00g

lvol_pre01 prevg21 -wi-ao---- 512.00g

lvol_pre01 prevg22 -wi-ao---- 512.00g

lvol_pre01 prevg23 -wi-ao---- 512.00g

lvol_pre01 prevg24 -wi-ao---- 512.00g

lvol_pre01 prevg25 -wi-ao---- 512.00g

lvol_pre01 prevg26 -wi-ao---- 512.00g

lvol_pre01 prevg27 -wi-ao---- 512.00g

lvol_pre01 prevg28 -wi-ao---- 512.00g

lvol_pre01 prevg29 -wi-ao---- 512.00g

lvol_pre01 prevg30 -wi-ao---- 512.00g

lvol_pre01 prevg31 -wi-ao---- 512.00g

lvol_pre01 prevg32 -wi-ao---- 512.00g

lvol_pre01 prevg33 -wi-ao---- 512.00g

lvol_pre01 prevg34 -wi-ao---- 512.00g

lvol_pre01 prevg35 -wi-ao---- 512.00g

lvol_pre01 prevg36 -wi-ao---- 512.00g

lvol_pre01 prevg37 -wi-ao---- 512.00g

lvol_pre01 prevg38 -wi-ao---- 512.00g

lvol_home vg01 -wi-ao---- 387.73g

lvol_splex vg01 -wi-ao---- 110.00g

lvol_backup vg02 -wi-ao---- 1.82t

-- 추가 후 lvs 확인

[root@mpods01 ~]# lvs

LV VG Attr LSize Pool Origin Data% Meta% Move Log Cpy%Sync Convert

lvol_pre01 prevg01 -wi-ao---- 512.00g

lvol_pre01 prevg02 -wi-ao---- 512.00g

lvol_pre01 prevg03 -wi-ao---- 512.00g

lvol_pre01 prevg04 -wi-ao---- 512.00g

lvol_pre01 prevg05 -wi-ao---- 512.00g

lvol_pre01 prevg06 -wi-ao---- 512.00g

lvol_pre01 prevg07 -wi-ao---- 512.00g

lvol_pre01 prevg08 -wi-ao---- 512.00g

lvol_pre01 prevg09 -wi-ao---- 512.00g

lvol_pre01 prevg10 -wi-ao---- 512.00g

lvol_pre01 prevg11 -wi-ao---- 512.00g

lvol_pre01 prevg12 -wi-ao---- 512.00g

lvol_pre01 prevg13 -wi-ao---- 512.00g

lvol_pre01 prevg14 -wi-ao---- 512.00g

lvol_pre01 prevg15 -wi-ao---- 512.00g

lvol_pre01 prevg16 -wi-ao---- 512.00g

lvol_pre01 prevg17 -wi-ao---- 512.00g

lvol_pre01 prevg18 -wi-ao---- 512.00g

lvol_pre01 prevg19 -wi-ao---- 512.00g

lvol_pre01 prevg20 -wi-ao---- 512.00g

lvol_pre01 prevg21 -wi-ao---- 512.00g

lvol_pre01 prevg22 -wi-ao---- 512.00g

lvol_pre01 prevg23 -wi-ao---- 512.00g

lvol_pre01 prevg24 -wi-ao---- 512.00g

lvol_pre01 prevg25 -wi-ao---- 512.00g

lvol_pre01 prevg26 -wi-ao---- 512.00g

lvol_pre01 prevg27 -wi-ao---- 512.00g

lvol_pre01 prevg28 -wi-ao---- 512.00g

lvol_pre01 prevg29 -wi-ao---- 512.00g

lvol_pre01 prevg30 -wi-ao---- 512.00g

lvol_pre01 prevg31 -wi-ao---- 512.00g

lvol_pre01 prevg32 -wi-ao---- 512.00g

lvol_pre01 prevg33 -wi-ao---- 512.00g

lvol_pre01 prevg34 -wi-ao---- 512.00g

lvol_pre01 prevg35 -wi-ao---- 512.00g

lvol_pre01 prevg36 -wi-ao---- 512.00g

lvol_pre01 prevg37 -wi-ao---- 512.00g

lvol_pre01 prevg38 -wi-ao---- 512.00g

lvol_pre01 prevg39 -wi-a----- 512.00g

lvol_pre01 prevg40 -wi-a----- 512.00g

lvol_pre01 prevg41 -wi-a----- 512.00g

lvol_pre01 prevg42 -wi-a----- 512.00g

lvol_pre01 prevg43 -wi-a----- 512.00g

lvol_pre01 prevg44 -wi-a----- 512.00g

lvol_pre01 prevg45 -wi-a----- 512.00g

lvol_pre01 prevg46 -wi-a----- 512.00g

lvol_pre01 prevg47 -wi-a----- 512.00g

lvol_pre01 prevg48 -wi-a----- 512.00g

lvol_pre01 prevg49 -wi-a----- 512.00g

lvol_pre01 prevg50 -wi-a----- 512.00g

lvol_pre01 prevg51 -wi-a----- 512.00g

lvol_home vg01 -wi-ao---- 387.73g

lvol_splex vg01 -wi-ao---- 110.00g

lvol_backup vg02 -wi-ao---- 1.82t

[root@mpods01 mapper]# oracleasm listdisks

VOL01

VOL02

VOL03

VOL04

VOL05

VOL06

VOL07

VOL08

VOL09

VOL10

VOL11

VOL12

VOL13

VOL14

VOL15

VOL16

VOL17

VOL18

VOL19

VOL20

VOL21

VOL22

VOL23

VOL24

VOL25

VOL26

VOL27

VOL28

VOL29

VOL30

VOL31

VOL32

VOL33

VOL34

VOL35

VOL36

VOL37

VOL38

-- asm disk group & path 확인

set linesize 150

set pagesize 100

col path for a40

select group_number,mount_status,path,total_mb from v$asm_disk order by group_number, path;

GROUP_NUMBER MOUNT_STATUS PATH TOTAL_MB

------------ -------------- ---------------------------------------- ----------

1 CACHED /dev/oracleasm/disks/VOL01 524284

1 CACHED /dev/oracleasm/disks/VOL02 524284

1 CACHED /dev/oracleasm/disks/VOL03 524284

1 CACHED /dev/oracleasm/disks/VOL04 524284

1 CACHED /dev/oracleasm/disks/VOL05 524284

1 CACHED /dev/oracleasm/disks/VOL06 524284

1 CACHED /dev/oracleasm/disks/VOL07 524284

1 CACHED /dev/oracleasm/disks/VOL08 524284

1 CACHED /dev/oracleasm/disks/VOL09 524284

1 CACHED /dev/oracleasm/disks/VOL10 524284

1 CACHED /dev/oracleasm/disks/VOL11 524284

1 CACHED /dev/oracleasm/disks/VOL12 524284

1 CACHED /dev/oracleasm/disks/VOL13 524284

1 CACHED /dev/oracleasm/disks/VOL14 524284

1 CACHED /dev/oracleasm/disks/VOL15 524284

1 CACHED /dev/oracleasm/disks/VOL16 524284

1 CACHED /dev/oracleasm/disks/VOL17 524284

1 CACHED /dev/oracleasm/disks/VOL18 524284

1 CACHED /dev/oracleasm/disks/VOL19 524284

1 CACHED /dev/oracleasm/disks/VOL20 524284

1 CACHED /dev/oracleasm/disks/VOL21 524284

1 CACHED /dev/oracleasm/disks/VOL22 524284

1 CACHED /dev/oracleasm/disks/VOL23 524284

1 CACHED /dev/oracleasm/disks/VOL24 524284

1 CACHED /dev/oracleasm/disks/VOL25 524284

1 CACHED /dev/oracleasm/disks/VOL26 524284

1 CACHED /dev/oracleasm/disks/VOL27 524284

1 CACHED /dev/oracleasm/disks/VOL28 524284

1 CACHED /dev/oracleasm/disks/VOL29 524284

1 CACHED /dev/oracleasm/disks/VOL30 524284

1 CACHED /dev/oracleasm/disks/VOL31 524284

1 CACHED /dev/oracleasm/disks/VOL32 524284

1 CACHED /dev/oracleasm/disks/VOL33 524284

1 CACHED /dev/oracleasm/disks/VOL34 524284

1 CACHED /dev/oracleasm/disks/VOL35 524284

1 CACHED /dev/oracleasm/disks/VOL36 524284

1 CACHED /dev/oracleasm/disks/VOL37 524284

1 CACHED /dev/oracleasm/disks/VOL38 524284

38 rows selected.

-- oracleasm disk 추가

oracleasm createdisk VOL39 /dev/mapper/prevg39-lvol_pre01

oracleasm createdisk VOL40 /dev/mapper/prevg40-lvol_pre01

oracleasm createdisk VOL41 /dev/mapper/prevg41-lvol_pre01

oracleasm createdisk VOL42 /dev/mapper/prevg42-lvol_pre01

oracleasm createdisk VOL43 /dev/mapper/prevg43-lvol_pre01

oracleasm createdisk VOL44 /dev/mapper/prevg44-lvol_pre01

oracleasm createdisk VOL45 /dev/mapper/prevg45-lvol_pre01

oracleasm createdisk VOL46 /dev/mapper/prevg46-lvol_pre01

oracleasm createdisk VOL47 /dev/mapper/prevg47-lvol_pre01

oracleasm createdisk VOL48 /dev/mapper/prevg48-lvol_pre01

oracleasm createdisk VOL49 /dev/mapper/prevg49-lvol_pre01

oracleasm createdisk VOL50 /dev/mapper/prevg50-lvol_pre01

oracleasm createdisk VOL51 /dev/mapper/prevg51-lvol_pre01

-- oracleasm disk 추가 후 asm disk group & path 확인

SQL> select group_number,mount_status,path,total_mb from v$asm_disk order by group_number, path;

GROUP_NUMBER MOUNT_STATUS PATH TOTAL_MB

------------ -------------- ---------------------------------------- ----------

0 CLOSED /dev/oracleasm/disks/VOL39 0

0 CLOSED /dev/oracleasm/disks/VOL40 0

0 CLOSED /dev/oracleasm/disks/VOL41 0

0 CLOSED /dev/oracleasm/disks/VOL42 0

0 CLOSED /dev/oracleasm/disks/VOL43 0

0 CLOSED /dev/oracleasm/disks/VOL44 0

0 CLOSED /dev/oracleasm/disks/VOL45 0

0 CLOSED /dev/oracleasm/disks/VOL46 0

0 CLOSED /dev/oracleasm/disks/VOL47 0

0 CLOSED /dev/oracleasm/disks/VOL48 0

0 CLOSED /dev/oracleasm/disks/VOL49 0

0 CLOSED /dev/oracleasm/disks/VOL50 0

0 CLOSED /dev/oracleasm/disks/VOL51 0

1 CACHED /dev/oracleasm/disks/VOL01 524284

1 CACHED /dev/oracleasm/disks/VOL02 524284

1 CACHED /dev/oracleasm/disks/VOL03 524284

1 CACHED /dev/oracleasm/disks/VOL04 524284

1 CACHED /dev/oracleasm/disks/VOL05 524284

1 CACHED /dev/oracleasm/disks/VOL06 524284

1 CACHED /dev/oracleasm/disks/VOL07 524284

1 CACHED /dev/oracleasm/disks/VOL08 524284

1 CACHED /dev/oracleasm/disks/VOL09 524284

1 CACHED /dev/oracleasm/disks/VOL10 524284

1 CACHED /dev/oracleasm/disks/VOL11 524284

1 CACHED /dev/oracleasm/disks/VOL12 524284

1 CACHED /dev/oracleasm/disks/VOL13 524284

1 CACHED /dev/oracleasm/disks/VOL14 524284

1 CACHED /dev/oracleasm/disks/VOL15 524284

1 CACHED /dev/oracleasm/disks/VOL16 524284

1 CACHED /dev/oracleasm/disks/VOL17 524284

1 CACHED /dev/oracleasm/disks/VOL18 524284

1 CACHED /dev/oracleasm/disks/VOL19 524284

1 CACHED /dev/oracleasm/disks/VOL20 524284

1 CACHED /dev/oracleasm/disks/VOL21 524284

1 CACHED /dev/oracleasm/disks/VOL22 524284

1 CACHED /dev/oracleasm/disks/VOL23 524284

1 CACHED /dev/oracleasm/disks/VOL24 524284

1 CACHED /dev/oracleasm/disks/VOL25 524284

1 CACHED /dev/oracleasm/disks/VOL26 524284

1 CACHED /dev/oracleasm/disks/VOL27 524284

1 CACHED /dev/oracleasm/disks/VOL28 524284

1 CACHED /dev/oracleasm/disks/VOL29 524284

1 CACHED /dev/oracleasm/disks/VOL30 524284

1 CACHED /dev/oracleasm/disks/VOL31 524284

1 CACHED /dev/oracleasm/disks/VOL32 524284

1 CACHED /dev/oracleasm/disks/VOL33 524284

1 CACHED /dev/oracleasm/disks/VOL34 524284

1 CACHED /dev/oracleasm/disks/VOL35 524284

1 CACHED /dev/oracleasm/disks/VOL36 524284

1 CACHED /dev/oracleasm/disks/VOL37 524284

1 CACHED /dev/oracleasm/disks/VOL38 524284

51 rows selected.

-- ASM Operation 확인

set linesize 150

set pagesize 100

col name for a20

select d.name, o.operation, o.state, o.power, o.est_minutes

from v$asm_disk d, v$asm_operation o

where d.group_number=o.group_number order by 1;

-- ASM 그룹 디스크 추가 (15:55:39 시작)

alter diskgroup DATA

add disk '/dev/oracleasm/disks/VOL39','/dev/oracleasm/disks/VOL40','/dev/oracleasm/disks/VOL41','/dev/oracleasm/disks/VOL42',

'/dev/oracleasm/disks/VOL43','/dev/oracleasm/disks/VOL44','/dev/oracleasm/disks/VOL45','/dev/oracleasm/disks/VOL46',

'/dev/oracleasm/disks/VOL47','/dev/oracleasm/disks/VOL48','/dev/oracleasm/disks/VOL49','/dev/oracleasm/disks/VOL50',

'/dev/oracleasm/disks/VOL51'

rebalance power 100 wait;

-- ASM Operation 확인

set linesize 150

set pagesize 100

col name for a20

select d.name, o.operation, o.state, o.power, o.est_minutes

from v$asm_disk d, v$asm_operation o

where d.group_number=o.group_number order by 1;

NAME OPERATION STATE POWER EST_MINUTES

-------------------- ---------- -------- ---------- -----------

DATA_0000 REBAL WAIT 100 0

DATA_0000 REBAL RUN 100 147

DATA_0001 REBAL WAIT 100 0

DATA_0001 REBAL RUN 100 147

DATA_0002 REBAL RUN 100 147

DATA_0002 REBAL WAIT 100 0

DATA_0003 REBAL RUN 100 147

DATA_0003 REBAL WAIT 100 0

DATA_0004 REBAL RUN 100 147

DATA_0004 REBAL WAIT 100 0

DATA_0005 REBAL RUN 100 147

DATA_0005 REBAL WAIT 100 0

DATA_0006 REBAL RUN 100 147

DATA_0006 REBAL WAIT 100 0

DATA_0007 REBAL WAIT 100 0

DATA_0007 REBAL RUN 100 147

DATA_0008 REBAL RUN 100 147

DATA_0008 REBAL WAIT 100 0

DATA_0009 REBAL RUN 100 147

DATA_0009 REBAL WAIT 100 0

DATA_0010 REBAL WAIT 100 0

DATA_0010 REBAL RUN 100 147

DATA_0011 REBAL WAIT 100 0

DATA_0011 REBAL RUN 100 147

DATA_0012 REBAL WAIT 100 0

DATA_0012 REBAL RUN 100 147

DATA_0013 REBAL RUN 100 147

DATA_0013 REBAL WAIT 100 0

DATA_0014 REBAL WAIT 100 0

DATA_0014 REBAL RUN 100 147

DATA_0015 REBAL WAIT 100 0

DATA_0015 REBAL RUN 100 147

DATA_0016 REBAL WAIT 100 0

DATA_0016 REBAL RUN 100 147

DATA_0017 REBAL RUN 100 147

DATA_0017 REBAL WAIT 100 0

DATA_0018 REBAL RUN 100 147

DATA_0018 REBAL WAIT 100 0

DATA_0019 REBAL WAIT 100 0

DATA_0019 REBAL RUN 100 147

DATA_0020 REBAL RUN 100 147

DATA_0020 REBAL WAIT 100 0

DATA_0021 REBAL RUN 100 147

DATA_0021 REBAL WAIT 100 0

DATA_0022 REBAL RUN 100 147

DATA_0022 REBAL WAIT 100 0

DATA_0023 REBAL WAIT 100 0

DATA_0023 REBAL RUN 100 147

DATA_0024 REBAL RUN 100 147

DATA_0024 REBAL WAIT 100 0

DATA_0025 REBAL RUN 100 147

DATA_0025 REBAL WAIT 100 0

DATA_0026 REBAL RUN 100 147

DATA_0026 REBAL WAIT 100 0

DATA_0027 REBAL RUN 100 147

DATA_0027 REBAL WAIT 100 0

DATA_0028 REBAL RUN 100 147

DATA_0028 REBAL WAIT 100 0

DATA_0029 REBAL RUN 100 147

DATA_0029 REBAL WAIT 100 0

DATA_0030 REBAL RUN 100 147

DATA_0030 REBAL WAIT 100 0

DATA_0031 REBAL RUN 100 147

DATA_0031 REBAL WAIT 100 0

DATA_0032 REBAL RUN 100 147

DATA_0032 REBAL WAIT 100 0

DATA_0033 REBAL RUN 100 147

DATA_0033 REBAL WAIT 100 0

DATA_0034 REBAL WAIT 100 0

DATA_0034 REBAL RUN 100 147

DATA_0035 REBAL RUN 100 147

DATA_0035 REBAL WAIT 100 0

DATA_0036 REBAL RUN 100 147

DATA_0036 REBAL WAIT 100 0

DATA_0037 REBAL WAIT 100 0

DATA_0037 REBAL RUN 100 147

DATA_0038 REBAL WAIT 100 0

DATA_0038 REBAL RUN 100 147

DATA_0039 REBAL WAIT 100 0

DATA_0039 REBAL RUN 100 147

DATA_0040 REBAL WAIT 100 0

DATA_0040 REBAL RUN 100 147

DATA_0041 REBAL RUN 100 147

DATA_0041 REBAL WAIT 100 0

DATA_0042 REBAL RUN 100 147

DATA_0042 REBAL WAIT 100 0

DATA_0043 REBAL WAIT 100 0

DATA_0043 REBAL RUN 100 147

DATA_0044 REBAL RUN 100 147

DATA_0044 REBAL WAIT 100 0

DATA_0045 REBAL RUN 100 147

DATA_0045 REBAL WAIT 100 0

DATA_0046 REBAL WAIT 100 0

DATA_0046 REBAL RUN 100 147

DATA_0047 REBAL WAIT 100 0

DATA_0047 REBAL RUN 100 147

DATA_0048 REBAL RUN 100 147

NAME OPERATION STATE POWER EST_MINUTES

-------------------- ---------- -------- ---------- -----------

DATA_0048 REBAL WAIT 100 0

DATA_0049 REBAL RUN 100 147

DATA_0049 REBAL WAIT 100 0

DATA_0050 REBAL WAIT 100 0

DATA_0050 REBAL RUN 100 147

102 rows selected.

-- rebalance power 변경 (16:03:30 변경)

alter diskgroup data rebalance power 300;

-- 완료 (20:22:00)

-- asm disk group & path 확인

set linesize 150

set pagesize 100

col path for a40

select group_number,mount_status,path,total_mb from v$asm_disk order by group_number, path;

GROUP_NUMBER MOUNT_STATUS PATH TOTAL_MB

------------ -------------- ---------------------------------------- ----------

1 CACHED /dev/oracleasm/disks/VOL01 524284

1 CACHED /dev/oracleasm/disks/VOL02 524284

1 CACHED /dev/oracleasm/disks/VOL03 524284

1 CACHED /dev/oracleasm/disks/VOL04 524284

1 CACHED /dev/oracleasm/disks/VOL05 524284

1 CACHED /dev/oracleasm/disks/VOL06 524284

1 CACHED /dev/oracleasm/disks/VOL07 524284

1 CACHED /dev/oracleasm/disks/VOL08 524284

1 CACHED /dev/oracleasm/disks/VOL09 524284

1 CACHED /dev/oracleasm/disks/VOL10 524284

1 CACHED /dev/oracleasm/disks/VOL11 524284

1 CACHED /dev/oracleasm/disks/VOL12 524284

1 CACHED /dev/oracleasm/disks/VOL13 524284

1 CACHED /dev/oracleasm/disks/VOL14 524284

1 CACHED /dev/oracleasm/disks/VOL15 524284

1 CACHED /dev/oracleasm/disks/VOL16 524284

1 CACHED /dev/oracleasm/disks/VOL17 524284

1 CACHED /dev/oracleasm/disks/VOL18 524284

1 CACHED /dev/oracleasm/disks/VOL19 524284

1 CACHED /dev/oracleasm/disks/VOL20 524284

1 CACHED /dev/oracleasm/disks/VOL21 524284

1 CACHED /dev/oracleasm/disks/VOL22 524284

1 CACHED /dev/oracleasm/disks/VOL23 524284

1 CACHED /dev/oracleasm/disks/VOL24 524284

1 CACHED /dev/oracleasm/disks/VOL25 524284

1 CACHED /dev/oracleasm/disks/VOL26 524284

1 CACHED /dev/oracleasm/disks/VOL27 524284

1 CACHED /dev/oracleasm/disks/VOL28 524284

1 CACHED /dev/oracleasm/disks/VOL29 524284

1 CACHED /dev/oracleasm/disks/VOL30 524284

1 CACHED /dev/oracleasm/disks/VOL31 524284

1 CACHED /dev/oracleasm/disks/VOL32 524284

1 CACHED /dev/oracleasm/disks/VOL33 524284

1 CACHED /dev/oracleasm/disks/VOL34 524284

1 CACHED /dev/oracleasm/disks/VOL35 524284

1 CACHED /dev/oracleasm/disks/VOL36 524284

1 CACHED /dev/oracleasm/disks/VOL37 524284

1 CACHED /dev/oracleasm/disks/VOL38 524284

1 CACHED /dev/oracleasm/disks/VOL39 524284

1 CACHED /dev/oracleasm/disks/VOL40 524284

1 CACHED /dev/oracleasm/disks/VOL41 524284

1 CACHED /dev/oracleasm/disks/VOL42 524284

1 CACHED /dev/oracleasm/disks/VOL43 524284

1 CACHED /dev/oracleasm/disks/VOL44 524284

1 CACHED /dev/oracleasm/disks/VOL45 524284

1 CACHED /dev/oracleasm/disks/VOL46 524284

1 CACHED /dev/oracleasm/disks/VOL47 524284

1 CACHED /dev/oracleasm/disks/VOL48 524284

1 CACHED /dev/oracleasm/disks/VOL49 524284

1 CACHED /dev/oracleasm/disks/VOL50 524284

1 CACHED /dev/oracleasm/disks/VOL51 524284

51 rows selected.

-- 가용량 확인

ASMCMD> lsdg

State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name

MOUNTED EXTERN N 512 4096 4194304 26738484 7097796 0 7097796 0 N DATA/

저작자표시 비영리 변경금지 (새창열림)

'OraclE > ASM' 카테고리의 다른 글

ASM DISK 추가 하기 (0)	2016.03.25

자세한 설명은 생략한다.

다운로드는 요기 ↓↓↓

MySQL_install_5717.log

--=========================================================================================================

-- 001 OS 지표 확인 및 변경

--=========================================================================================================

su -

[root@mpmykpop01 ~]# df -hT

Filesystem Type Size Used Avail Use% Mounted on

/dev/sda3 ext4 9.8G 833M 8.5G 9% /

tmpfs tmpfs 32G 0 32G 0% /dev/shm

/dev/sda1 ext4 976M 150M 776M 17% /boot

/dev/mapper/vg01-lvol_home

ext4 856G 5.5G 807G 1% /home

/dev/sda5 ext4 20G 6.0G 13G 33% /usr

/dev/sda6 ext4 15G 705M 14G 5% /var

/dev/sdb1 ext4 1.8T 68M 1.7T 1% /data

[root@mpmykpop01 ~]# cat /etc/fstab

#

# /etc/fstab

# Created by anaconda on Mon Feb 27 17:31:10 2017

#

# Accessible filesystems, by reference, are maintained under '/dev/disk'

# See man pages fstab(5), findfs(8), mount(8) and/or blkid(8) for more info

#

UUID=7bcbafa0-e25c-42a1-8f15-ad0398297d40 / ext4 defaults 1 1

UUID=c6175772-9ce3-40d7-873d-555af5e9dbbc /boot ext4 defaults 1 2

/dev/mapper/vg01-lvol_home /home ext4 defaults 1 2

UUID=6a40c82c-5308-46bd-8057-587efaf7ab96 /usr ext4 defaults 1 2

UUID=4a47848b-9227-4cc6-bd84-876974e6654e /var ext4 defaults 1 2

UUID=58f9fea7-400a-401d-9b79-9a704d66269a swap swap defaults 0 0

tmpfs /dev/shm tmpfs defaults 0 0

devpts /dev/pts devpts gid=5,mode=620 0 0

sysfs /sys sysfs defaults 0 0

proc /proc proc defaults 0 0

## DATA MOUNT

LABEL=/data /data ext4 defaults 0 0

[root@mpmykpop01 ~]# free -g

total used free shared buffers cached

Mem: 62 7 55 0 0 5

-/+ buffers/cache: 1 61

Swap: 15 0 15

[root@mpmykpop01 ~]# lsblk

NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT

sda 8:0 0 931.5G 0 disk

|-sda1 8:1 0 1G 0 part /boot

|-sda2 8:2 0 16G 0 part [SWAP]

|-sda3 8:3 0 10G 0 part /

|-sda4 8:4 0 1K 0 part

|-sda5 8:5 0 20G 0 part /usr

|-sda6 8:6 0 15G 0 part /var

`-sda7 8:7 0 869.5G 0 part

`-vg01-lvol_home (dm-0) 253:0 0 869.5G 0 lvm /home

sdb 8:16 0 1.8T 0 disk

`-sdb1 8:17 0 1.8T 0 part /data

[root@mpmykpop01 ~]# cat /etc/sysctl.conf

# Kernel sysctl configuration file for Red Hat Linux

#

# For binary values, 0 is disabled, 1 is enabled. See sysctl(8) and

# sysctl.conf(5) for more details.

# Controls IP packet forwarding

net.ipv4.ip_forward = 0

# Controls source route verification

net.ipv4.conf.default.rp_filter = 1

# Do not accept source routing

net.ipv4.conf.default.accept_source_route = 0

# Controls the System Request debugging functionality of the kernel

kernel.sysrq = 1

# Controls whether core dumps will append the PID to the core filename.

# Useful for debugging multi-threaded applications.

kernel.core_uses_pid = 1

# Controls the use of TCP syncookies

net.ipv4.tcp_syncookies = 1

# Disable netfilter on bridges.

net.bridge.bridge-nf-call-ip6tables = 0

net.bridge.bridge-nf-call-iptables = 0

net.bridge.bridge-nf-call-arptables = 0

# Controls the default maxmimum size of a mesage queue

kernel.msgmnb = 65536

# Controls the maximum size of a message, in bytes

kernel.msgmax = 65536

# Controls the maximum shared segment size, in bytes

kernel.shmmax = 68719476736

# Controls the maximum number of shared memory segments, in pages

kernel.shmall = 4294967296

#Melon Service

net.ipv4.tcp_max_syn_backlog = 32767

net.ipv4.tcp_fin_timeout = 30

net.ipv4.tcp_keepalive_time = 180

net.ipv4.tcp_retries2=5

net.ipv4.tcp_syn_retries=2

net.core.wmem_max=33554432

net.core.rmem_max=33554432

net.core.rmem_default = 33554432

net.core.wmem_default = 33554432

net.ipv4.tcp_rmem=4096 87380 33554432

net.ipv4.tcp_wmem=4096 87380 33554432

net.ipv4.tcp_mem=12582912 12582912 12582912

net.ipv4.conf.all.log_martians=1

#By Kwon _ Dos

net.core.netdev_max_backlog = 150000

net.core.somaxconn=32767

net.ipv4.tcp_max_orphans=262144

net.ipv4.tcp_synack_retries=2

net.core.optmem_max = 12582912

#vm.swappiness=20

vm.swappiness=0

--==============================================================================

-- 001_02 - IO Elevator 확인

--==============================================================================

[root@mpmykpop01 ~]# cat /sys/block/sdb/queue/scheduler

[noop] anticipatory deadline cfq

-- cd /sys/block/sdb/queue

-- cat scheduler

-- echo "noop" > scheduler

--==============================================================================

-- 001_03 - Disk Cache ON 및 Cacheratio 변경

--==============================================================================

-- 생략

--==============================================================================

-- 001_04 - /etc/security/limits.conf 변경 (Openfiles 수 영향)

--==============================================================================

[root@mpmykpop01 ~]# cat /etc/security/limits.conf

# /etc/security/limits.conf

#

#Each line describes a limit for a user in the form:

#

#<domain> <type> <item> <value>

#

#Where:

#<domain> can be:

# - an user name

# - a group name, with @group syntax

# - the wildcard *, for default entry

# - the wildcard %, can be also used with %group syntax,

# for maxlogin limit

#

#<type> can have the two values:

# - "soft" for enforcing the soft limits

# - "hard" for enforcing hard limits

#

#<item> can be one of the following:

# - core - limits the core file size (KB)

# - data - max data size (KB)

# - fsize - maximum filesize (KB)

# - memlock - max locked-in-memory address space (KB)

# - nofile - max number of open files

# - rss - max resident set size (KB)

# - stack - max stack size (KB)

# - cpu - max CPU time (MIN)

# - nproc - max number of processes

# - as - address space limit (KB)

# - maxlogins - max number of logins for this user

# - maxsyslogins - max number of logins on the system

# - priority - the priority to run user process with

# - locks - max number of file locks the user can hold

# - sigpending - max number of pending signals

# - msgqueue - max memory used by POSIX message queues (bytes)

# - nice - max nice priority allowed to raise to values: [-20, 19]

# - rtprio - max realtime priority

#

#<domain> <type> <item> <value>

#

#* soft core 0

#* hard rss 10000

#@student hard nproc 20

#@faculty soft nproc 20

#@faculty hard nproc 50

#ftp hard nproc 0

#@student - maxlogins 4

root soft nofile 8192

root hard nofile 8192

root soft nproc 135167

root hard nproc 135167

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

# mysql settings

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

mysql hard nofile 131072

mysql soft nofile 131072

mysql hard nproc 65535

mysql soft nproc 65535

# End of file

--==============================================================================

-- 001_05 - /etc/hosts 변경

--==============================================================================

vi /etc/hosts

127.0.0.1 mpmykpop01 localhost

--================================================================================================

-- 002 MySQL 인스턴스 설치

--================================================================================================

--==============================================================================

-- 002_01 OS계정 설정 등

--==============================================================================

cat /etc/passwd | grep mysql

cat /etc/group | grep mysql

userdel mysql

cat /etc/passwd | grep mysql

cat /etc/group | grep mysql

groupadd -g 2000 mysql

useradd -g mysql -u 2000 -s /bin/bash -d /home/mysql mysql

chmod 750 /home/mysql

chage -l mysql

chage -E -1 -I 0 -m 0 -M 99999 mysql

chage -l mysql

passwd mysql

"xxxxxxxx"

su - mysql

vi .bash_profile

# .bash_profile

# Get the aliases and functions

if [ -f ~/.bashrc ]; then

. ~/.bashrc

fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PATH

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

# User specific environment and startup programs

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

LANG=ko_KR.UTF-8

export LANG

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

# MySQL #export

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

export DB_DIR_BASE="/home/mysql/MySQL"

export DB_DIR_DATA="/home/mysql/DBSpace/data"

export DB_DIR_BINLOG="/home/mysql/DBSpace/binlog"

export DB_DIR_IBLOG="/home/mysql/DBSpace/iblog"

export DB_DIR_RELLOG="/home/mysql/DBSpace/data"

export DB_DIR_TMP="/home/mysql/DBSpace/tmp"

export DB_DIR_BACKUP="/home/mysql/DBBackup"

export MYSQL_PS1="\R:\m:\s \u@"$HOSTNAME" [\d]> "

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

# MySQL #alias

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

alias 3306=$DB_DIR_BASE"/bin/mysql -uroot -p -c -S /tmp/mysql.sock"

alias dir_dbbase="cd "$DB_DIR_BASE

alias dir_dbdata="cd "$DB_DIR_DATA

alias dir_dbbinlog="cd "$DB_DIR_BINLOG

alias dir_dbiblog="cd "$DB_DIR_IBLOG

alias dir_dbbackup="cd "$DB_DIR_BACKUP

alias tail_errlog="tail -f "$DB_DIR_DATA"/"$HOSTNAME".err"

--==================================================================================================

-- 002_02 mysql 5.7.17 설치

--==================================================================================================

su -

mkdir /data/DBSpace

mkdir /data/DBSpace/data

mkdir /data/DBSpace/iblog

mkdir /data/DBSpace/tmp

mkdir /data/DBSpace/binlog

mkdir /data/DBBackup

mkdir /home/mysql/DBSpace

ln -s /data/DBSpace/data /home/mysql/DBSpace

ln -s /data/DBSpace/iblog /home/mysql/DBSpace

ln -s /data/DBSpace/tmp /home/mysql/DBSpace

ln -s /data/DBSpace/binlog /home/mysql/DBSpace

ln -s /data/DBBackup /home/mysql/DBBackup

mkdir /home/mysql/DBA

mkdir /home/mysql/DBA/programs

chown -R mysql:mysql /data/DBBackup

chown -R mysql:mysql /data/DBSpace

chown -R mysql:mysql /home/mysql/DBSpace

chown -R mysql:mysql /home/mysql/DBBackup

chown -R mysql:mysql /home/mysql/DBA

chmod -R 750 /data/DBBackup

chmod -R 750 /data/DBSpace

su - mysql

cd /home/mysql/DBA/programs

ls -al

scp 192.168.100.123:/home/oracle/DBA/hoon/tmp/mysql-5.7.17-linux-glibc2.5-x86_64.tar ./

ls -al

tar xvzf mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz

mv mysql-5.7.17-linux-glibc2.5-x86_64 /home/mysql/MySQL_5717

ln -s /home/mysql/MySQL_5717 /home/mysql/MySQL

cd /home/mysql

ls -al

chown -R mysql:mysql /home/mysql/MySQL

chown -R mysql:mysql /home/mysql/MySQL_5717

chmod -R 750 /home/mysql/MySQL

chmod -R 750 /home/mysql/MySQL_5717

ls -al

cd /home/mysql/MySQL/bin/

ls -al | grep mysqld

su -

echo /home/mysql/MySQL/lib >> /etc/ld.so.conf && ldconfig

echo /usr/lib64 >> /etc/ld.so.conf && ldconfig

echo PATH=/home/mysql/MySQL/bin:$PATH >> /etc/profile && source /etc/profile

vi /etc/profile

..........

PATH=/home/mysql/MySQL/bin:/usr/lib64/qt-3.3/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin

cd /etc/

ls -al | grep my.cnf

rm -rf my.cnf

ls -al | grep my.cnf

cat /home/mysql/MySQL/support-files/my-default.cnf

cp /home/mysql/MySQL/support-files/my-default.cnf ./my.cnf

chmod 640 my.cnf

chown root:mysql my.cnf

ls -al | grep my.cnf

vi my.cnf

.............

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

# MySQL 5.6.28 Global Variables

# - OS Spec :

# - DB Spec :

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

[client]

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

# Client Options

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

port = 3306

socket = /tmp/mysql.sock

[mysqld]

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

# MySQLD Options

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

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

# MySQLD - Connection Info

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

port = 3306

socket = /tmp/mysql.sock

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

# MySQLD - Directory Path Info

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

basedir = /home/mysql/MySQL

datadir = /home/mysql/DBSpace/data

tmpdir = /home/mysql/DBSpace/tmp

log-bin = /home/mysql/DBSpace/binlog/mysql-bin

relay-log = /home/mysql/DBSpace/data/relay-log

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

# MySQLD - Normal Options

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

skip-character-set-client-handshake

skip-host-cache

skip-name-resolve

skip-external-locking

sysdate-is-now

init_connect = "set collation_connection=utf8mb4_general_ci"

init_connect = "set names utf8mb4"

character-set-server = utf8mb4

collation-server = utf8mb4_general_ci

transaction_isolation = REPEATABLE-READ

sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

event_scheduler = 0

log_bin_trust_function_creators = 1

ft_min_word_len = 2

default-storage-engine = innodb

lower_case_table_names = 1

query_cache_type = 0

query_cache_size = 0

query_cache_limit = 0

back_log = 50

slow_query_log = 1

long_query_time = 1

log_output = FILE

log-warnings = 2

max_connections = 3000

sort_buffer_size = 1M

read_buffer_size = 1M

join_buffer_size = 1M

read_rnd_buffer_size = 2M

max_heap_table_size = 64M

tmp_table_size = 64M

thread_cache_size = 200

wait_timeout = 28800

max_connect_errors = 99999

table_open_cache = 4096

max_allowed_packet = 1G

group_concat_max_len = 10485760

open-files-limit = 40960

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

# MySQLD - InnoDB Info

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

innodb_print_all_deadlocks

innodb_buffer_pool_dump_at_shutdown = 1

innodb_buffer_pool_load_at_startup = 1

innodb_data_home_dir = /home/mysql/DBSpace/data

innodb_log_group_home_dir = /home/mysql/DBSpace/iblog

innodb_buffer_pool_instances = 8

innodb_buffer_pool_size = 30GB

innodb_log_buffer_size = 16M

innodb_data_file_path = ibdata1:10M:autoextend

innodb_flush_method = O_DIRECT

innodb_flush_log_at_trx_commit = 1

innodb_doublewrite = 0

innodb_support_xa = 1

innodb_open_files = 20480

innodb_sort_buffer_size = 8M

innodb_log_file_size = 2048M

innodb_log_files_in_group = 3

innodb_max_dirty_pages_pct = 90

innodb_lock_wait_timeout = 60

innodb_file_per_table = 1

innodb_stats_on_metadata = 0

#innodb_read_io_threads = 12

#innodb_write_io_threads = 12

#innodb_force_recovery = 0

#innodb_thread_concurrency = 24

#innodb_fast_shutdown = 0

performance_schema

#innodb_status_file = 1

#innodb_adaptive_flushing = 1

#innodb_use_native_aio = 1

#innodb_purge_thread = 1

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

# MySQLD - Replication Info

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

server-id = 15206

binlog_format = MIXED

expire_logs_days = 8

max_binlog_size = 300M

binlog_cache_size = 1M

read_only

log_slave_updates = 1

sync_binlog = 0

relay_log_purge = 0

skip_slave_start

replicate-ignore-db = mysql

replicate-ignore-db = information_schema

replicate-ignore-db = performance_schema

replicate-ignore-db = test

replicate-ignore-db = sysbench

replicate-ignore-db = mp_member

replicate-ignore-db = mp_cms

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

# MySQLD - MyISAM Info

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

key_buffer_size = 32M

bulk_insert_buffer_size = 64M

myisam_sort_buffer_size = 128M

myisam_max_sort_file_size = 1G

myisam-recover-options

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

# ETC Options

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

[mysqldump]

quick

max_allowed_packet = 32M

[mysql]

no-auto-rehash

[myisamchk]

key_buffer_size = 256M

sort_buffer_size = 256M

read_buffer = 4M

write_buffer = 4M

[mysqlhotcopy]

interactive-timeout

[mysqld_safe]

open-files-limit = 40960

cd /etc/init.d/

ls -al | grep mysql

rm -rf mysqld

ls -al | grep mysql

cat /home/mysql/MySQL/support-files/mysql.server

cp /home/mysql/MySQL/support-files/mysql.server ./mysqld

ls -al | grep mysql

chmod 750 mysqld

chown root:mysql mysqld

ls -al | grep mysql

vi mysqld

...........

basedir=/home/mysql/MySQL

datadir=/home/mysql/DBSpace/data

...........

su - mysql

cd /home/mysql/MySQL/

-------------------------- ver. 5.6 ----------------------------------

./scripts/mysql_install_db --user=mysql --defaults-file=/etc/my.cnf

----------------------------------------------------------------------

-------------------------- ver. 5.7 --------------------------------

./bin/mysqld --defaults-file=/etc/my.cnf --verbose --help

./bin/mysqld --defaults-file=/etc/my.cnf --initialize

--------------------------------------------------------------------

################################################# 중요 #####################################################

# ./bin/mysqld --defaults-file=/etc/my.cnf --initialize 5.7 버젼은 수행시 로그 중간에 패스워드 생성됨 #

# 2017-03-09T01:21:45.975514Z 1 [Note] A temporary password is generated for root@localhost: z.#6tEKklAma #

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

cp /home/mysql/MySQL/bin/mysqld_safe /home/mysql/MySQL/bin/mysqld_safe_20170307

vi /home/mysql/MySQL/bin/mysqld_safe

........

426 MY_PWD=`dirname $MY_PWD`

427 ;;

428 esac

429 MY_PWD=`pwd` << 추가

430 # Check for the directories we would expect from a binary release install

431 if test -n "$MY_BASEDIR_VERSION" -a -d "$MY_BASEDIR_VERSION"

.........

/etc/init.d/mysqld start

cat /home/mysql/DBSpace/data/mpmykpop01.err

--------------------- ver. 5.6 -----------------------------

/home/mysql/MySQL/bin/mysql -uroot --socket=/tmp/mysql.sock

set names utf8;

use mysql;

select user, host from user order by user, host;

delete from user where user<>'root' or host<>'localhost';

select user, host from user order by user, host;

flush privileges;

set password for 'root'@'localhost'=password('XXXXXXXX');

flush privileges;

------------------------------------------------------------

------------------------ ver. 5.7 ----------------------------------

/home/mysql/MySQL/bin/mysql -uroot -p -c -S /tmp/mysql.sock

패스워드 입력 : z.#6tEKklAma

set names utf8;

ALTER USER 'root'@'localhost' IDENTIFIED BY 'XXXXXXXX';

flush privileges;

mysql> use mysql;

mysql> select user, host from user;

select user, host, authentication_string from user;

select user, host, HEX(authentication_string) as password from user;

---------------------------------------------------------------------

show databases;

drop database test;

show databases;

show global variables like '%innodb_buffer_pool_size%';

+-------------------------+-------------+

| Variable_name | Value |

+-------------------------+-------------+

| innodb_buffer_pool_size | 32212254720 |

+-------------------------+-------------+

3306

/etc/init.d/mysqld stop

cat /home/mysql/DBSpace/data/mpmykpop01.err

/etc/init.d/mysqld start

cat /home/mysql/DBSpace/data/mpmykpop01.err

저작자표시 비영리 변경금지 (새창열림)

'MySQL' 카테고리의 다른 글

MySQL Hash Join Optimization (0)	2019.10.02
MySQL 시퀀스(AUTO_INCREMENT) 조회 및 초기화 (0)	2018.09.18
Specified key was too long (부제: mysql 인덱스 생성 힘드네) (0)	2017.06.09
MySQL load data (0)	2016.10.27
MySQL 사용자 계정 추가, 변경, 자원 제한 (0)	2016.10.20

DATAPUMP 사용시 ENCRYPTION 옵션 사용을 통하여 암호화를 진행 할 수 있다.

ENCRYPTION 의 옵션은 ALL, DATA_ONLY, ENCRYPTED_COLUMNS_ONLY, METADATA_ONLY and NONE 이 있으며,

ENCRYPTION_ALGORITHM 은 AES128(기본 값), AES192 and AES256 이 있다.

아래는 test_20170119 테이블을 ENCRYPTION 옵션으로 export 하는 예제이다.

expdp \" / as sysdba\" dumpfile=MST_1st.dmp logfile=MST_1st.log directory=EXP_HOON tables=$hoon.test_20170119$ ENCRYPTION=DATA_ONLY ENCRYPTION_MODE=PASSWORD ENCRYPTION_PASSWORD=tyvld

Export: Release 11.2.0.2.0 - Production on Thu Jan 19 11:32:16 2017

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01": "/******** AS SYSDBA" dumpfile=MST_1st.dmp logfile=MST_1st.log directory=EXP_HOON tables=(hoon.test_20170119) ENCRYPTION=DATA_ONLY ENCRYPTION_MODE=PASSWORD ENCRYPTION_PASSWORD=********
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "HOON"."TEST_20170119" 44.07 KB 660 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/home/oracle/DBA/hoon/tmp/MST_1st.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 11:32:25

이제 반대로 import 해보자

impdp \" / as sysdba\" dumpfile=MST_1st.dmp directory=EXP_HOON

Import: Release 11.2.0.2.0 - Production on Thu Jan 19 11:33:10 2017

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39002: invalid operation
ORA-39174: Encryption password must be supplied.

패스워드를 지정하지 않아 import 진행이 되지 않는다.

패스워드를 지정하고 다시 import 해보자.

impdp \" / as sysdba\" dumpfile=MST_1st.dmp directory=EXP_HOON ENCRYPTION_PASSWORD=tyvld

Import: Release 11.2.0.2.0 - Production on Thu Jan 19 11:33:27 2017

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01": "/******** AS SYSDBA" dumpfile=MST_1st.dmp directory=EXP_HOON ENCRYPTION_PASSWORD=********
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "HOON"."TEST_20170119" 44.07 KB 660 rows
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at 11:33:30

정상적으로 테이블이 import 되었다.

ENCRYPTION 옵션을 잘 황용하면 데이터 백업에 대한 보안을 강화 할 수 있다.

저작자표시 비영리 변경금지 (새창열림)

'OraclE' 카테고리의 다른 글

RMAN backup set 에서 archivelog 추출하여 logminor 수행 (0)	2018.02.22
Oracle RAC 마스터 노드 찾기 (find Master Node) (0)	2017.09.14
ORACLE 12c UNIFIED AUDIT 사용 (0)	2016.07.13
RAC 원하는 노드에서 mview refresh 수행 (0)	2016.04.11
Auditing Enhancements (aud$ 테이블 관리) (0)	2016.04.11

JJON's StorY

전체 글

Specified key was too long (부제: mysql 인덱스 생성 힘드네)

'MySQL' 카테고리의 다른 글

ASM DISK 추가 하기 2

'OraclE > ASM' 카테고리의 다른 글

MySQL 설치 (5.6 or 5.7)

'MySQL' 카테고리의 다른 글

DATAPUMP 암호화

'OraclE' 카테고리의 다른 글

+ Recent posts

티스토리툴바