화상연동/Web,APP 구축및서비스/LMS/평생교육/학점은행/기업교육/연수관리/설문구축/
2 01 2022
1.설치 – install mysql – [root@localhost /]# yum install mysql-server 2.서비스 시작 [root@localhost /]# systemctl start mysqld.service [root@localhost /]# systemctl start mysqld 3.서버 자동시작 4. 서버 재구동시 mysql 자동 서비스 구동 설정 5.패스워드 정책설정 최초설정- $ mysql_secure_installation 정책변경 – [root@localhost my.cnf.d]# cat mysql-server.cnf #Password Ploicy validate_password.policy=LOW 7.ssh 포트변경 – 1)selinux 완전 사용안함 변경 vi /etc/selinux/config SELINUX=disabled […]
16 02 2021
1)yum localinstall 명령어로 MySQL rpm 패키지 설치 [root@hae ~]# yum repolist enabled | grep “mysql.*-community.*” [root@hae ~]# sudo yum localinstall https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm Loaded plugins: fastestmirror mysql80-community-release-el7-3.noarch.rpm | 25 kB 00:00:00 Examining /var/tmp/yum-root-tl7kMk/mysql80-community-release-el7-3.noarch.rpm: mysql80-community-release-el7-3.noarch Marking /var/tmp/yum-root-tl7kMk/mysql80-community-release-el7-3.noarch.rpm to be installed Resolving Dependencies –> Running transaction check —> Package mysql80-community-release.noarch 0:el7-3 will be installed –> Finished Dependency Resolution Dependencies […]
15 02 2021
ALTER DATABASE emudb SET RECOVERY SIMPLE DBCC SHRINKFILE(emudb_log) ALTER DATABASE emudb SET RECOVERY FULL ALTER DATABASE emudb SET RECOVERY SIMPLE DBCC SHRINKFILE(emudb) ALTER DATABASE emudb SET RECOVERY FULL
10 02 2021
1) mysql 8.0 다운도르 rpm -Uvh https://repo.mysql.com/mysql80-community-release-el6-3.noarch.rpm 2)기존 받은 파일은 사용하지 않게 처리 sed -i ‘s/enabled=1/enabled=0/’ /etc/yum.repos.d/mysql-community.repo sed -i ‘s/enabled=1/enabled=0/’ /etc/yum.repos.d/mysql-community-source.repo 3)install yum –enablerepo=mysql80-community install mysql-community-server 4)Error Requires: libsasl2.so.3()(64bit) => yum whatprovides */libsasl2.so.3 Requires: libmysqlclient.so.16()(64bit) =>yum whatprovides */libmysqlclient.so.16 Requires: libstdc++.so.6(GLIBCXX_3.4.14)(64bit) =>yum whatprovides */libstdc++.so.6
10 02 2021
1.서비스 삭제 #chkconfig –list mysqld 2.등록한 서비스 파일 삭제 #cd /etc/init.d #rm -rf mysqld 3.설정정보삭제 # rm -rf /var/lib/mysql 4.yum 으로 설치 하지 않았다면 프로그램과 데이타파일을 직접 삭제한다.
10 04 2020
[숫자형 데이터 타입] 데이터 타입 의미 크기 설명 TINYINT 매우 작은 정수 1 byte -128 ~ 127 (부호없이 0 ~ 255) SMALLINT 작은 정수 2 byte -32768 ~ 32767 MEDIUMINT 중간 크기의 정수 3 byte –(-8388608) ~ -1(8388607) INT 표준 정수 4 byte – 범위 : -2147483648 에서 2147483647 (UNSIGNED 일 경우 0 에 4294967295) – 주의 […]
16 03 2020
1.DB Conneciton Pool 관련 Pool조회 select * from v$resource_limit where resource_name in (‘processes’, ‘sessions’); Sessions 접속 정보 쿼리 SELECT username, program FROM v$session; 접속 사용자 제거
|
1 2 |
--alter system kill session 'session_id,serial#'; alter system kill session '32,11234'; |
현재 Connection Pool 사용현황
|
1 2 3 |
SELECT * FROM V$resource_limit WHERE resource_name IN ('processes','sessions'); |
접속이 안되는 경우 INACTIVE 갯수가 많을 경우 삭제 하면 됨
|
1 2 3 4 |
SELECT 'alter system kill session ''' || sid || ',' || serial# || ''' immediate;' FROM v$session WHERE username = 'NURIWARE' AND status = 'INACTIVE' |
나오는 결과를 아래처럼 실행
|
1 2 3 4 5 6 7 8 9 10 |
alter system kill session '13,46763'; alter system kill session '29,30175'; alter system kill session '39,21235'; alter system kill session '43,53103'; alter system kill session '45,40359'; alter system kill session '46,50781'; alter system kill session '62,65397'; alter system kill session '71,15497'; alter system kill session '256,13051'; alter system kill session '262,14689'; |
2.DB Lock 관련 lock 조회
|
1 2 3 4 5 6 7 |
SELECT DO.OBJECT_NAME, DO.OWNER, DO.OBJECT_TYPE, DO.OWNER, VO.XIDUSN, VO.SESSION_ID, VO.LOCKED_MODE FROM V$LOCKED_OBJECT VO, DBA_OBJECTS DO WHERE VO.OBJECT_ID = DO.OBJECT_ID; |
[…]
8 01 2020
1. 1.su – oracle sqlplus ‘/as sysdba’ SQL>startup SQL> exit oracle>lsnrctl start
10 05 2019
1. indentity에 insert를 하겠다는 명령어 실행
|
1 |
SET IDENTITY_INSERT tb_sys_log_login ON |
2.명령어 실행(주의할점 칼럼을 반드시 써주어야 한다: 아마 identity의 칼럼때문인것 같다).
|
1 2 3 4 5 6 7 8 9 |
insert into tb_aaa( SN ,CONECT_IP ,CONECT_DT) select SN ,CONECT_IP ,CONECT_DT from TB_aaa_2018 |
3.Identity에 insert를 이제 안하겠다 실행
|
1 |
SET IDENTITY_INSERT tb_sys_log_login OFF |
30 10 2018
일부 테이블만 의 경우 먼저 Directory Dump를 만든다.
|
1 2 3 4 5 6 7 |
SQL> CREATE DIRECTORY KWCYBERDUMP AS '/./../kwcyber'; Directory created. SQL> GRANT read,write on directory KWCYBERDUMP to 오라클계정; Grant succeeded. |
|
1 |
oracle$]impdp 오라클계정/패스워드@SID directory=KWCYBERDUMP dumpfile=file.dmp tables=tu_progress_log table_exists_action=append; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
Import: Release 11.2.0.1.0 - Production on Tue Oct 30 17:57:25 2018 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Master table "계정"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded Starting "계정"."SYS_IMPORT_TABLE_01": 계정/********@SID directory=KWCYBERDUMP dumpfile=file.dmp tables=tu_progress_log table_exists_action=append Processing object type SCHEMA_EXPORT/TABLE/TABLE ORA-39152: Table "계정"."TU_PROGRESS_LOG" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA . . imported "계정"."TU_PROGRESS_LOG" 541.8 MB 5260803 rows Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/COMMENT Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Job "계정"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 17:59:32 |