'Database'에 해당하는 글 67건

mysql 일별 통계

Database/Mysql 2021. 10. 7. 22:50

특정 기간의 통계를 화면에 출력해야 할 경우가 있다. 일련의 날짜들이 DB 테이블에 들어 있다면 별 문제가 없겠지만, 굳이 별도의 통계 테이블이 필요하지 않다면 쿼리로 일련의 날짜들에 관련된 임시 테이블을 생성해야 한다. 우선 필요한 날짜 기간을 파악하고, 날짜 테이블을 만들어 본다.

 

curdate() - interval 1 day
curdate() - interval 2 day
curdate() - interval 3 day

 

이런 식이라면 현재부터 원하는 이전날짜까지의 결과셋을 만들 수 있다.

 

아래는 0000 부터 9999까지 각 자리마다 컬럼을 구성하는 쿼리이다.

select *
from 
(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 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
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 d


0 | 0 | 0 | 0 부터
1 | 0 | 0 | 0
...
9 | 9 | 9 | 8
9 | 9 | 9 | 9 까지가 출력된다.

 

그리고 아래와 같이 select 문을 변경하면 원하는 일자 테이블을 출력할 수 있다.

 

select curdate() - interval (a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a) ) day as date

 

2021-10-07
2021-10-06
2021-10-05
...

 

그리고 여기에 기간도 설정하고 다른 테이블과 조인하여 통계에도 사용할 수 있다.

 

select a.date as daily, sum(b.cost) as dailycost
from (
    select curdate() - interval (a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a) ) day as date
    from 
        (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 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
        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 d
    ) a left outer join `sales` b on a.date = Date(b.createDatetime)
where 1=1
and a.gDate between '2021-09-01' and '2021-09-31' 
group by daily
order by daily asc;

 

위 쿼리로 2021-09-01 부터 2021-09-31 까지의 일별 매출 통계를 출력할 수 있다.

 

약간 무식해 보이긴 하지만 필요하다면 ^^

 


WRITTEN BY
손가락귀신
정신 못차리면, 벌 받는다.

트랙백  0 , 댓글  0개가 달렸습니다.
secret

systemctl 로 자동실행을 설정할 수도 있지만 기존의 init.d 를 사용할 수도 있다.

 

 

1. oracle 서비스 스크립트 작성

 

# vi /etc/rc.d/init.d/oracle

#!/bin/bash
# oracle: Start/Stop Oracle Database 11g R2
#
# chkconfig: 345 90 10
# description: The Oracle Database is an Object-Relational Database Management System.
#
# processname: oracle
 
. /etc/rc.d/init.d/functions
 
LOCKFILE=/var/lock/subsys/oracle
ORACLE_HOME=/app/oracle/product/11.2.0/dbhome_1
ORACLE_USER=oracle
 
case "$1" in
'start')
    if [ -f $LOCKFILE ]; then
        echo $0 already running.
        exit 1
    fi
    echo -n $"Starting Oracle Database:"
    su - $ORACLE_USER -c "$ORACLE_HOME/bin/lsnrctl start"
    su - $ORACLE_USER -c "$ORACLE_HOME/bin/dbstart $ORACLE_HOME"
    su - $ORACLE_USER -c "$ORACLE_HOME/bin/emctl start dbconsole"
    touch $LOCKFILE
    ;;
'stop')
    if [ ! -f $LOCKFILE ]; then
        echo $0 already stopping.
        exit 1
    fi
    echo -n $"Stopping Oracle Database:"
    su - $ORACLE_USER -c "$ORACLE_HOME/bin/lsnrctl stop"
    su - $ORACLE_USER -c "$ORACLE_HOME/bin/dbshut"
    su - $ORACLE_USER -c "$ORACLE_HOME/bin/emctl stop dbconsole"
    rm -f $LOCKFILE
    ;;
'restart')
    $0 stop
    $0 start
    ;;
'status')
    if [ -f $LOCKFILE ]; then
        echo $0 started.
    else
        echo $0 stopped.
    fi
    ;;
*)
    echo "Usage: $0 [start|stop|status]"
    exit 1
esac
 
exit 0
# End of file

 

 

2. /etc/oratab 수정

 

orcl:/app/oracle/product/11.2.0/dbhome_1:Y

 

부팅시 dbstart 유틸리티를 사용하도록 설정(Y)

 

 

3. chkconfig 등록

 

# chmod 755 /etc/rc.d/init.d/oracle
# chkconfig --add oracle
# chkconfig oracle on

 

 

4. 재부팅 후 프로세스 확인

 

# /etc/init.d/oracle status
oracle started.
# ps ax | grep ora

 


WRITTEN BY
손가락귀신
정신 못차리면, 벌 받는다.

트랙백  0 , 댓글  0개가 달렸습니다.
secret

systemctl 는 RHEL 7 에 도입된 systemd 를 관리/제어하는 명령어이다. /usr/lib/systemd/system/ 디렉토리에 .service 파일을 만들어 서비스를 추가할 수 있다. Oracle 구동에 필요한 lsnrctl 과 dbstart 역시 직관적으로 사용할 수도 있지만 부팅시 자동실행을 위해 서비스에 등록해 본다.

 

  • /usr/lib/systemd/system/oracle_listener.service
  • /usr/lib/systemd/system/oracle_instance.service

 

 

1. listener 서비스 작성

 

# vi /usr/lib/systemd/system/oracle_listener.service

[Unit]
Description=oracle listener
After=network.target

[Service]
Type=forking
User=oracle
Group=dba
Environment=ORACLE_BASE=/app/oracle
Environment=ORACLE_HOME=/app/oracle/product/11.2.0/dbhome_1
Environment=ORACLE_SID=orcl
ExecStart=/app/oracle/product/11.2.0/dbhome_1/bin/lsnrctl start
ExecStop=/app/oracle/product/11.2.0/dbhome_1/bin/lsnrctl stop

[Install]
WantedBy=multi-user.target

 

 

2. instance 서비스 작성

 

# vi /usr/lib/systemd/system/oracle_instance.service

[Unit]
Description=oracle instance
After=network.target syslog.target

[Service]
Type=forking
User=oracle
Group=dba
Environment=ORACLE_BASE=/app/oracle
Environment=ORACLE_HOME=/app/oracle/product/11.2.0/dbhome_1
Environment=ORACLE_SID=orcl
ExecStart=/app/oracle/product/11.2.0/dbhome_1/bin/dbstart ORACLE_HOME
ExecStop=/app/oracle/product/11.2.0/dbhome_1/bin/dbshut ORACLE_HOME

[Install]
WantedBy=multi-user.target

 

 

3. systemd 에 등록 후 자동실행 설정

 

# systemctl daemon-reload
# systemctl enable oracle_listener.service
# systemctl enable oracle_instance.service

 

 

4. /etc/oratab 수정

 

orcl:/app/oracle/product/11.2.0/dbhome_1:Y

 

부팅시 dbstart 유틸리티를 사용하도록 설정(Y)

 

 

5. 재부팅 후 프로세스 확인

 

 

 


WRITTEN BY
손가락귀신
정신 못차리면, 벌 받는다.

트랙백  0 , 댓글  0개가 달렸습니다.
secret

CentOS 7 에서 Oracle 11g 에 설치하기.

 

 

1. 다운로드

 

Oracle 11g 는 다운로드 하는 것 부터가 불편하다. 구글에서 대충 검색하면 현재 보편화된 19c 다운로드 페이지로 이동되고, 모든 버전을 확인하고 다운받으려면 Oracle Software Delivery Cloud 사이트(https://edelivery.oracle.com) 로 이동해야 한다. 마찬가지로 oracle 계정이 필요하다.

 

 

2. X window 준비

 

Oracle 11g 는 X window 환경을 필요로 하기 때문에 X window 환경을 준비한다.

 

  • 방법1. GUI 용 서버 설치
    # yum groupinstall 'Server with GUI'
  • 방법2. GNOME Desktop 설치
    # yum groupinstall 'GNOME Desktop'
  • 방법3. 최소한의 X11 패키지 설치 및 Xming(원격) 사용하기
    link: Xming 설치 링크

 

CentOS GUI 환경에서는 설치 중 에러 메시지 등이 깨져 보일 수 있는데 이럴 때는 설치를 중단하고 윈도우즈 환경(Xming) 에서 원격 접속하면 설치 중 메시지들을 정상적으로 확인할 수 있다.

 

 

3. 오라클 계정/그룹 생성

 

# groupadd dba
# useradd -g dba oracle
# passwd oracle

 

 

4. 설치 디렉토리 및 권한 설정

 

원하는 ORACLE_BASE 디렉토리를 생성한다.

 

# mkdir -p /app/oracle
# chown -R oracle:dba /app

 

 

5. 설치 전 준비사항

 

오라클 설치 전에는 메모리도 확보해야 하고, 파일 디스크립터, OS 커널 파라미터 설정도 해야 하고, 의존 라이브러리도 설치해야 한다. 아무 설정 없이 오라클 설치를 진행하면 다음의 화면을 만날 수 있다.

 

 

ignore all 을 체크하고 설치를 진행해도 오라클 설치는 마칠 수 있지만, 정상적으로 설치가 완료됐다고 볼 수도 없고 굳이 그렇게 설치할 필요도 없다. 또 ignore 체크를 해도 진행되지 않는 경우도 있다. 나는 Swap Memory 와 pdksh(Public domain Korn shell) 때문에 진행이 되지 않은 적이 있다.

 

 

5.1 OS 커널 파라미터 값 설정 (ignore 로 fix 가능)

 

# vi /etc/sysctl.conf

# Controls the maximum shared segment size, in bytes
kernel.shmmax = 68719476736

# Controls the maximum number of shared memory segments, in pages
kernel.shmall = 10523004
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128

fs.aio-max-nr = 1048576
fs.file-max = 6815744

net.ipv4.ip_local_port_range = 9000 65500

net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048586

 

# /sbin/sysctl -p

 

 

5.2 유저 자원 사용 제한값 설정 (ignore 로 fix 가능)

 

# vi /etc/security/limits.conf
...
oracle soft nproc 2048
oracle hard nproc 65536
oracle soft nofile 1024
oracle hard nofile 65536

 

 

5.3 의존 라이브러리 설치

 

# yum -y install compat-libstdc++-33.x86_64 binutils elfutils-libelf elfutils-libelf-devel
# yum -y install glibc glibc-common glibc-devel glibc-headers gcc gcc-c++ libaio libaio-devel
# yum -y install libgcc libstdc++ libstdc++-devel make sysstat unixODBC unixODBC-devel

# yum -y install unzip wget

 

 

5.4 pdfsh 설치

 

다운로드 사이트

http://rpm.pbone.net/info_idpl_2398776_distro_redhat7.x_com_pdksh-5.2.14-8.i386.rpm.html

 

# wget ftp://ftp.pbone.net/mirror/archive.download.redhat.com/pub/redhat/linux/7.0/en/os/i386/RedHat/RPMS/pdksh-5.2.14-8.i386.rpm
# rpm -Uvh --nodeps pdksh-5.2.14-8.i386.rpm --force

 

 

5.5. oracle 계정 환경변수 설정

 

# vi /home/oracle/.bash_profile
export ORACLE_BASE=/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export ORACLE_SID=orcl
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/usr/local/lib
export PATH=$PATH:$ORACLE_HOME/bin

 

ORACLE_BASE, ORACLE_HOME 이나 SID 등은 필요에 맞게 수정한다.

 

 

5.6 Swap 메모리 설정

 

이 부분은 조금 이해가 안된다. Oracle 11g R2 설치시 최소 메모리가 1GB 인데, 한번은 16GB 시스템에서 Actual Value 가 8GB, Expected Value 가 16GB 로 되어 진행되지 않은 적이 있다. 메모리가 부족하지도 않은데 시스템 메모리 만큼 영끌하라는게 말이됨? 아무튼 이런 이유로 진행이 되지 않는다면 Expected Value 까지 swap 메모리를 확보해야 한다.

 

link: Swap memory 생성 참고

 

 

6. oracle 11g 설치파일 준비

 

oracle 11g 설치 파일을 서버에서 직접 다운받은게 아니라면 ftp 등을 이용하여 oracle 11g 파일을 서버에 업로드한다. oracle 11g 설치 파일을 oracle 계정이 실행할 수 있도록 설정하고 installer 를 실행한다.

 

# mv V17530-01_1of2.zip /home/oracle
# mv V17530-01_2of2.zip /home/oracle
# chown oracle:dba /home/oracle/V17530*

 

 

7. installer 실행

 

installer 를 실행하는데 있어 가장 중요한 것이 oracle 계정으로 로그인 하는 것이다. su / sudo 등을 이용하지 않고 새로운 세션에서 oracle 계정으로 로그인 해야 한다.

 

$ unzip V17530-01_1of2.zip
$ unzip V17530-01_2of2.zip
$ ./database/runInstaller

 

만약 한글판일 경우 한글이 깨지면,

 

$ export LANG=C
$ export LC_ALL=C
$ ./database/runInstaller

 

 

8. 설치 과정

 

  • Configure Security Updates : pass
  • Installation Option : Create and configure a database
  • System Class : Server Class
  • Grid Options : Single instance database installation
  • Install Type : Typical install
  • Database Edition : Enterprise Edition
  • Installation Location : default (/app/oracle)
  • Create Inventory : default
  • Prerequisite Checks : Ignore All

 

 

9. 설치 중 오류 수정

 

$ vi /app/oracle/product/11.2.0/dbhome_1/ctx/lib/ins_ctx.mk
ctxhx: $(CTXHXOBJ) 
    -static $(LINK_CTXHX) $(CTXHXOBJ) $(INSO_LINK)

 

$ vi /usr/oracle/app/product/11.2.0/dbhome_1/sysman/lib/ins_emagent.mk
$(SYSMANBIN) emdctl:
    $(MK_EMAGENT_NMECTL) -lnnz11

 

 

10. 구성 스크립트 실행(Root)

 

# /app/oraInventory/orainstRoot.sh
# /app/oracle/product/11.2.0/dbhome_1/root.sh

 

 

11. 설치 완료 후 계정 생성

 

$ sqlplus / as sysdba
SQL> CREATE USER username identified by password;
SQL> GRANT resource, connect, dba to username;
SQL> SELECT * FROM ALL_USERS

USERNAME                          USER_ID CREATED
------------------------------ ---------- ------------------
OOPS4U                                 91 10-JUL-21

 

username 과 password 에 원하는 계정정보 입력.

 

 

12. 방화벽 port 열기

 

# firewall-cmd --zone=public --add-port=1521/tcp --permanent
# firewall-cmd --reload

 

 

13. 클라이언트 접속 테스트

 

SQL Developer 설치

https://www.oracle.com/tools/downloads/sqldev-downloads.html

 


WRITTEN BY
손가락귀신
정신 못차리면, 벌 받는다.

트랙백  0 , 댓글  0개가 달렸습니다.
secret

Xming + putty 로 oracle 11g 설치하기.
설치는 알아서들 하시고... 실제 제목은 설치 화면 띄우기?

리눅스에 오라클을 설치할 때 꼭 필요한 것이 GUI 환경이다. Xming 이나 X window manager 를 이용하면 원격으로 GUI 환경의 창을 띄울 수 있다. (무료인 Xming 추천) 서버가 로컬에 있다면 X window 깔고 GUI 화면에서 작업하면 되겠지만, 원격에서 X window 가 깔리지 않았을 때 GUI 창을 띄우는데 어떤 것들이 필요한지 테스트 해 보았다.

* 윈도우환경
  - OS : Windows 10
  - Xming 최신버전 (기본값으로 설치)
  - putty

* 서버환경
  - OS : CentOS 7 minimal (OS 설치시 네트워크 on)

 

 

1. putty 설정 후 접속
  - Connection - SSH - X11 - Enable X11 forwarding 체크


  - /etc/ssh/sshd_config 파일에도 X11Forwarding 옵션이 있지만 기본적으로 활성화 상태이다.
  - 이 두 옵션이 모두 활성화 된 상태여야 한다.

2. yum 업데이트 및 최소 X window 설치

# su -
# yum update
# yum install -y unzip
# yum install -y xorg-x11-apps.x86_64
# yum install -y xorg-x11-xauth.x86_64
# yum install -y xorg-x11-server-Xorg.x86_64


3. putty 새 세션에서 xclock 실행하고 Xming 구동 확인

$ xclock


4. oracle 계정 생성

# groupadd dba
# useradd -g dba oracle
# passwd oracle


5. ftp 로 알아서 Oracle 11g 업로드 후 압축 해제

# su - oracle
$ unzip V17530-01_1of2.zip
$ unzip V17530-01_2of2.zip

 

6. 새 세션에서 oracle 계정으로 로그인 후 oracle 설치 실행

$ ./database/runInstaller

 


물론 아무 설정없이 이 따위로 진행했다가는 에러 폭격을 맞겠지만, X window 창 띄우기가 그닥 어렵지 않다는 점...


WRITTEN BY
손가락귀신
정신 못차리면, 벌 받는다.

트랙백  0 , 댓글  0개가 달렸습니다.
secret