따봉 구걸남's 블로그

앞선 포스팅에서 single node ASM 기반 database 를 설치하였다. 

터미널로 붙어서 sqlplus 로 접속할수도 있지만 불편하다. 

local 환경에서 client tool 을 설치하고 VM 의 oracle 에 접속하자. 

1. DBeaver 설치 

client tool 은 DBeaver 를 설치해보자. 무료이기도 하고 많이들 사용하고 있다. 

아래 링크에서 설치파일을 다운받자. 

https://dbeaver.io/download/

개인의 로컬환경에 맞는 버전을 선택하자. 나는 윈도 환경이다. 

설치는 별거 없고 다운받은 .exe 파일을 실행만 하면 된다.

2. VM Port Forwarding

우리는 VM 에 있는 oracle 을 사용하기에 VM 에서 한가지더 해줘야 할게 있다. 

앞에서도 ssh 접속을 위해 추가한 포트포워딩 룰 이 있다. 

여기에 1521 포트도 똑같이 추가하자. 

- port, 방화벽 확인 

 터미널로 접속해서 oracle 포트가 열려있는지 확인한다. 

1521 port 가 잘 listen 하고 있다. 

방화벽이 동작하고 있다면 아래 명령으로 방화벽도 내려놓자.

systemctl stop firewalld

3. DBeaver Connection 설정 

다시 dbeaver 로 돌아와서 커넥션 정보를 추가하자. 

아래 빨간 박스 안의 내용을 확인하고 하단의 Test connection 클릭.

접속 잘된다.

이상 끗

도움 되었으면 따봉 눌러주기!

앞선 포스팅에서 single server 에서 asm 을 사용하기 위한 grid 설치를 알아보았다. 

19c grid 설치 Standalone server (Oracle Restart)

이제 FILESYSTEM 이 아닌 ASM diskgroup 에 데이터베이스를 생성해보자. 

oracle 19c database 설치파일은 아래링크에서 다운받을수 있다. 

https://www.oracle.com/kr/database/technologies/oracle-database-software-downloads.html

oracle 계정의 .bash_profile 은 위의 글을 참조하자.

본격적으로 설치 시작 

db_env => 

=================================================
mkdir -p $ORACLE_HOME
mv LINUX.X64_193000_db_home.zip $ORACLE_HOME
cd $ORACLE_HOME
unzip LINUX.X64_193000_db_home.zip 

./runInstaller

엔진설치, DB생성을 모두 하자. 

설치파일이 있는 디렉토리와 oracle base 디렉토리가 자동으로 잡힌다. 

 container 설치는 목적이 아니니까 체크해제 한다.

ASM 을 선택하자 

 recovery 영역도 ASM 을 선택하자  

데이터베이스는 앞서 생성한 DATA 디스크그룹에 생성한다. 

 귀찮으니까 같은 패스워드로

설치중 root 로 진행하는 부분을 자동으로 설정 

설치가 완료되면 sqlplus 로 DB에 접속해보자. 

grid 환경으로 바꾸고(grid_env) crsctl stat res -t 명령어를 치면 database 가 online 으로 표시 되면 끝

이전 포스팅에서 virtual box 에  오라클리눅스 설치를 완료했다. 

[virtualbox] 오라클리눅스 설치 oracle-linux 8.4

ASM 은 RAC 에서는 필수이지만 single node DB 에서는 파일시스템에 설치할수도 있다. 

싱글노드에서 ASM 을 설치하려면 grid 를 먼저 설치해야한다. 

오라클에서 제공하는 설치관련 가이드 문서는 아래 링크에서 확인가능하다. 

https://docs.oracle.com/en/database/oracle/oracle-database/19/cwlin/index.html

 1-1) hostname 변경 

 @root 
hostname
hostnamectl set-hostname asmtest

vi /etc/hosts
10.0.2.15   asmtest

 1-2) SELINUX 

vi /etc/selinux/config
SELINUX=disabled
( 중간의 SELINUX=enforcing 를 변경해준다) 
 - 방화벽
systemctl stop firewalld


reboot

 1-3)설치준비 

  - YUM, RPM
 @root 
  
 yum install -y oracle-database-preinstall-19c
 cd /home/oracle

** 아래 파일들은 미리 준비가 필요하다. 
 rpm -ivh oracleasm-support-2.1.12-1.el8.x86_64.rpm
 rpm -ivh oracleasmlib-2.0.17-1.el8.x86_64.rpm

 - OS USER

groupadd asmadmin
groupadd asmdba
groupadd asmoper
usermod -g oinstall -G dba,oper,backupdba,dgdba,kmdba,racdba,asmdba,asmoper oracle


 - DIRECTORY

mkdir -p /app/grid 
 
chown -R oracle:oinstall /app
chmod -R 775 /app

@oracle

   mv LINUX.X64_193000_grid_home.zip   /app/grid

cd /app/grid 
unzip LINUX.X64_193000_grid_home.zip 
 

 - PROFILE 

root, oracle 계정의 .bash_profile  

편의상 grid 계정 분리하지 않고 oracle 한 계정으로 간다.  

alias 부분은 각자 취향에 맞게 편집하자

===== oracle bash profile =====
export TMP=/tmp
export TMPDIR=$TMP
 
export ORACLE_HOSTNAME=asmtest
export ORACLE_UNQNAME=orcl
export ORACLE_BASE=/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/19.0.0/db_1
export GRID_HOME=/app/grid
export ORA_INVENTORY=/oraInventory
export ORACLE_SID=orcl
export PATH=/usr/sbin:/usr/local/bin:$PATH
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
export CV_ASSUME_DISTID=RHEL7.6 
 
export LANG=C
export LC_ALL=C

alias grid_env='export ORACLE_HOME=$GRID_HOME;export ORACLE_SID=+ASM;export PATH=$GRID_HOME/bin:$PATH'
alias db_env='export ORACLE_HOME=$ORACLE_BASE/product/19.0.0/db_1;export ORACLE_SID=orcl'
alias ss='sqlplus / as sysdba'
alias oh='cd $ORACLE_HOME'
alias gh='cd $GRID_HOME'
===== oracle bash profile =====

===== root bash profile =====

export ORACLE_BASE=/app/oracle
export GRID_HOME=/app/grid
export ORACLE_HOME=$ORACLE_BASE/product/19c/db_1
export PATH=$PATH:$GRID_HOME/bin

===== root bash profile =====

 
 - ASM CONFIG
 
[root@asmtest oracle]# oracleasm configure -i
Configuring the Oracle ASM library driver.

This will configure the on-boot properties of the Oracle ASM library
driver.  The following questions will determine whether the driver is
loaded on boot and what permissions it will have.  The current values
will be shown in brackets ('[]').  Hitting <ENTER> without typing an
answer will keep that current value.  Ctrl-C will abort.

Default user to own the driver interface []: oracle
Default group to own the driver interface []: asmadmin
Start Oracle ASM library driver on boot (y/n) [n]: y
Scan for Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration: done
[root@asmtest oracle]#  

[root@asmtest oracle]# oracleasm init
Creating /dev/oracleasm mount point: /dev/oracleasm
Loading module "oracleasm": oracleasm
Configuring "oracleasm" to use device physical block size
Mounting ASMlib driver filesystem: /dev/oracleasm
[root@asmtest oracle]#  oracleasm status
Checking if ASM is loaded: yes
Checking if /dev/oracleasm is mounted: yes
[root@asmtest oracle]#  oracleasm configure
ORACLEASM_ENABLED=true
ORACLEASM_UID=oracle
ORACLEASM_GID=asmadmin
ORACLEASM_SCANBOOT=true
ORACLEASM_SCANORDER=""
ORACLEASM_SCANEXCLUDE=""
ORACLEASM_SCAN_DIRECTORIES=""
ORACLEASM_USE_LOGICAL_BLOCK_SIZE="false"
[root@asmtest oracle]#
 
 
 
 - FDISK 

디스크 추가 전

[root@asmtest dev]# ls  /dev/sd*
/dev/sda  /dev/sda1  /dev/sda2

ASM 에서 사용할 디스크그룹을 아래와 같이 추가하자

 - crs1,2,3 : asm 파일용, 대충 각 1G
 - data : database 용, 최소 20G
 - reco : recovery 영역, 대충 5G
  

VM관리자에서 설정 클릭

* 디스크는 online 상에선 추가가 안되고 vm 이 down 된 상태에서 가능하다. 처음부터 준비하면 좋다.

  fdisk /dev/sdb
  n(new) -> p(primary) -> 1 -> Enter(default) -> Enter(default) -> w(write)
  fdisk /dev/sdc
  fdisk /dev/sdd
  fdisk /dev/sde
  fdisk /dev/sdf
  

디스크 5개를 추가했으니 5개 반복해준다. 

다했으면 추가된 디스크로 asmdisk 를 만들자.
 
oracleasm createdisk CRS1 /dev/sdb1
oracleasm createdisk CRS2 /dev/sdc1
oracleasm createdisk CRS3 /dev/sdd1
oracleasm createdisk DATA /dev/sde1
oracleasm createdisk RECO /dev/sdf1
 
oracleasm scandisks
oracleasm listdisks
  
  
 - tmpfs

 vi /etc/fstab  => 아래 한줄 추가 
 tmpfs                   /dev/shm                tmpfs   size=8g         0 0

df -h
mount -o remount /dev/shm
df -h

cd /app/grid/cv/rpm/
rpm -ivh cvuqdisk-1.0.10-1.rpm

 2) grid설치

grid_env => grid 환경으로 변경 alias (path, oracle_sid)

cd /app/grid
./gridSetup.sh 

oracle restart 선택 

change discovery path 클릭, asm disk 가 있는 경로를 써준다. 

설치 도중 root 권한으로 진행이 필요한 부분을 자동으로 실행하기 위해 auto 선택

 설치가 원활하려면 최소 메모리 8G 를 되야 한다. 아무리 작아도 4G 보다는 커야 하고 사전체크에서 걸리면 ignore

- DATA, RECO diskgroup 추가 

 CRS 디스크그룹만 생성하고 grid 설치가 끝났다. 추가로 DATA, RECO 디스크 그룹을 만들자.

[oracle@oraasm grid]$ asmca

하단의 create..  클릭

디스크그룹 이름을 DATA 로 하고 disk 선택  하고 하단의 Ok.

 동일하게 RECO 를 생성하자. 

생성이 끝나고 아래 명령어로 확인 

crsctl stat res -t 

 디스크그룹이 잘 생성이 되었다. 

 이제 여기에 데이터베이스를 생성이 가능한데, 그것은 다음글에서 알아보자. 

  [ASM] 19c database 설치

이번 포스팅은 몇개의 글에 걸쳐서 싱글노드에서 ASM 위에 DB 를 생성하는 것을 알아보겠다. 

1.  virtual box 에 오라클리눅스  설치

[virtualbox] 오라클리눅스 설치 oracle-linux 8.4

2. grid 설치

19c grid 설치 Standalone server (Oracle Restart)

3. db설치
 
[ASM] 19c database 설치

성질 급한 분들은 위에 링크로 바로 넘어가시고.. 

- ASM 에 대한 잡썰.. 

os 가 제공해주는 Filesystem 은 일반적 목적으로는 불편함이 없지만 조금의 성능 차이도 의미가 있는 DB 의 입장에서는 

아쉬움이 있다. 그래서 보통 크리티컬한 목적의 서버들은 raw device 위에 DB를 설치를 해왔다. 

raw device 의 'raw' 는 날 것 이란 뜻인데 말 그대로 OS 에서 아무런 처리도 하지 않은 device 여서 DB 에서

입맛에 맞게 가공해서 쓸수 있기에, 기존에 F/S 을 DB 에서 사용해서 발생하는 redundancy 가 해소되는 이점이 있다. 

한국의 기업들은 오랜동안 이 환경에 길들여지고 익숙해졌는데 오라클이 ASM 이란 신기술을 10G 부터 적용하였다. 

오라클은 매년 라이센스 비용을 인상하는데 잘생각해보면 결국 오라클의 R&D 비용이 고객에게 그대로 전가되고 있는 것을 짐작할수가 있다. 근데 만약 그렇게 돈 써가며 개발한 신기술을 고객이 외면하면 어떻게 될까?

ASM 이라는 신기술이 나왔지만, 지금 환경에 익숙한데 새로운걸 배워서 적용해야 하는 DBA, 고객 입장에서는 한동안 외면받아왔다. 공식적으로 특정버전의 RAC 에서는 raw device 를 더이상 지원하지 않고 ASM 이 필수라고 못을 박았지만

여전히 몇몇 사이트는 ASM 을 불신하고 기존대로 raw device 위에 운영을 하는 곳도 있다. 

마치 최신 노트북을 사서 윈도우xp 가 편하다고 구글링해가며 설치 하는 격인데..

내가 IT 바닥을 경험해보니.. IT 환경은 계속해서 손을보고, 조금씩 변화해야하는 것이 운명인것 같다. 

변하지 않으려고 애쓰면 나중에 값 비싼 청구서가 갑자기 날아오게 된다. 

아무튼.. 

ASM 은 결국 OS의 F/S 과 같은 oracle 의 자체 F/S 이고 RAC 를 설치할 경우 필수이다. 

오라클의 기조는 ASM 을 쭉 밀고 갈 것으로 보이니 이제 시작하시는 분들은 열심히 공부하세요!  

오라클 엔진을 설치하고 나서 dbca 로 간단하게 데이터베이스를 생성할수도 있지만
CREATE DATABASE 구문으로 DB 를 생성할수도 있다. 

1. CREATE DB

export ORACLE_SID=ORCL;
export DB_NAME=ORCL;

--shutdown immediate;
sqlplus / as sysdba
startup mount exclusive restrict
drop database;

-- pfile 은 사전에 준비되어있어야 한다.
startup nomount pfile='initORCL.ora'

CREATE DATABASE ORCL
user sys identified by oracle
user system identified by oracle
CONTROLFILE REUSE
LOGFILE
GROUP 1 ('+DATA') SIZE 10M REUSE,
GROUP 2 ('+DATA') SIZE 10M REUSE
DATAFILE '+DATA' SIZE 10G AUTOEXTEND ON MAXSIZE 30G
DEFAULT TABLESPACE USERS DATAFILE '+DATA' SIZE 10G AUTOEXTEND ON MAXSIZE 30G
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '+DATA' SIZE 10G AUTOEXTEND ON MAXSIZE 30G
UNDO TABLESPACE UNDOTBS DATAFILE '+DATA' SIZE 10G AUTOEXTEND ON MAXSIZE 30G
CHARACTER SET AL32UTF8
;

alter database mount;
alter database open;

2. DICTIONARY, 후속작업 

수동으로 디비를 생성하고 open 했지만 깡통 디비이기에 할수 있는게 없다.
후속으로 아래 스크립트를 실행하자.

conn sys/oracle as sysdba
SQL>@?/rdbms/admin/catalog.sql
-- Creates the data dictionary and public synonyms for many of its views, and grants PUBLIC access to the synonyms; also calls the scripts CATAUDIT.SQL, CATESP.SQL, and CATLDR.SQL

SQL>@?/rdbms/admin/catproc.sql
-- Runs all scripts required for or used with PL/SQL: CATPRC.SQL, CATRSNAP.SQL, CATRPC.SQL, STANDARD.SQL, DBMSSTDX.SQL, PIPDL.SQL, PIDIAN.SQL, DIUTIL.SQL, PISTUB.SQL, DBMSSNAP.SQL, DBMSLOCK.SQL, DBMSPIPE.SQL, DBMSALRT.SQL, SBMSOTPT.SQL, DBMSDESC.SQL

SQL>@?/sqlplus/admin/pupbld.sql
-- re-create the PRODUCT_USER_PROFILE table
SQL> @?/rdbms/admin/utlrp.sql
-- recompiles invalid objects


3. CRS등록

srvctl add database -db orcl
-oraclehome "오라클홈경로"
-spfile  "SPFILE경로"
-diskgroup "

srvctl config database -db orcl

.control_files='',"",""
create spfile from pfile

4. LISTENER, TNS 추가

오라클 diskgroup 이름 을 변경하는 것에 대해 알아보자 

1. dg umount
2. renamedg
3. dg mount
4. srv config
5. file rename

==================================
1. dg umount

create pfile from spfile;
@crs_user
crsctl stat res -t
srvctl stop database -d orcl

sqlplus / as sysasm
alter diskgroup OLD_DG dismount;

2. renamedg
renamedg dgname=old_dg newdgname=new_dg verbose=true check=true asm_diskstring='/dev/..'
renamedg dgname=old_dg newdgname=new_dg verbose=true asm_diskstring='/dev/..'

3. dg mount
alter diskgroup NEW_DG mount restricted;
alter diskgroup NEW_DG rename disks all;

alter diskgroup NEW_DG dismount;
alter diskgroup NEW_DG mount;

asmcmd lsdg

4. srv config

crsctl stat res -t

srvctl config database -d orcl
srvctl modify database -d orcl -a NEW_DG
srvctl modify database -d orcl -spfile +NEW_DG/spfileORCL.ora

srvctl remove diskgroup -g OLD_DG

startup nomount pfile='orahome/initORCL.ora'
alter database mount;


5. file rename
-- datafile
SELECT 'ALTER DATABASE RENAME FILE '''||NAME||''' TO '''||'+NEW_DG'||SUBSTR(NAME,9)||''';' STMT
  FROM V$DATAFILE
;

-- logfile
SELECT 'ALTER DATABASE RENAME FILE '''||NAME||''' TO '''||'+NEW_DG'||SUBSTR(NAME,9)||''';' STMT
  FROM V$LOGFILE
;

alter database open;
create spfile from pfile;
shutdown immediate;
startup

 

+DATA 디스크그룹 을 사용하는 디비 testdb 의 모든 파일을  +NEW_DG 디스크 그룹으로 옮기려면 어떻게 해야할까  

1. new diskgroup 생성 
2. datafile 이동 
  2-1)  datafile (system, users, undo ) 
  2-2)  temp 
  2-3) 기타 파일들(spfile, logfile, controlfile)

* crs 관련 

1. new diskgroup 생성 

alter dg drop disk name;

create diskgroup xxx asm_string '';

2. datafile 이동

   asmcmd mkdir 

    * system, undo 를 제외한 datafile 들은 archive mode 상태라면 rman 에서 아래 명렁으로 온라인으로 이동 가능하다. 

    1] BACKUP AS COPY DATAFILE 4 FORMAT "+NEW_DG";
    2] SQL "ALTER DATABASE DATAFILE ''+OLD_DG/orcl/datafile/users.261.689589837'' OFFLINE";
    3] SWITCH DATAFILE "+OLD_DG/orcl/datafile/users.261.689589837" TO COPY;
    4] RECOVER DATAFILE "+NEW_DG/orcl/datafile/users.256.689682663";
    5] SQL "ALTER DATABASE DATAFILE ''+NEW_DG/orcl/datafile/users.261.689589837'' ONLINE";
    6] DELETE DATAFILECOPY "+OLD_DG/orcl/datafile/users.261.689589837";

    2-1)  datafile 

    mount 상태에서 backup/ switch 로 이동시킨다. (temp 제외 모든 datafile)

   alter database mount;

    backup/ switch 

  open 이후 불필요한 datafilecopy 삭제 

 rman> delete datafilecopy all
   

  2-2)  temp 

   temp 는 새로운 경로에 생성 후 교체  
 - create temporary temp_new '+NEW_DG'
 - alter database default temp
 - drop temp
 - alter tablespace temp_new rename to temp

  2-3) 기타 파일들(spfile, logfile, controlfile)

logfile
 - alter system add logfile
 - alter system drop logfile member

  spfile
 - srvctl config 로 현재 spfile 경로 확인

  srvctl config database -d orclcdb

- srvctl modify spfile=

  srvctl modify database -d orcl -spfile +NEW_DG/spfileORCL.ora

create spfile ='+NEW_DG' from pfile;

controlfile
 - alter database nomount
 - restore to '+NEW_DG' from '+OLD_DG' 

 - vi initORCL.ora 
 - shutdown/ startup pfile
 - alter system set controlfile ='+NEW_DG' 

asmcmd rm -rf   OLD_DG 

* crs resource 등록

ERROR: failed to establish dependency between database <database name> and diskgroup resource ora.<diskgroup name>.dg

srvctl add

srvctl config

srvctl modify 

계층구조의 수직적 통합으로 순환관계 모델이 만들어 질수 있다

오라클에서는 start with 구문을 제공하여 간단하게 순환전개 쿼리를 작성할 수 있다.

1) 순환전개 SQL 

SELECT LEVEL, 부서코드, 부서명, 상위부서코드, PRIOR 부서코드
   FROM 부서
 START WITH 부서코드 ='001'
CONNECT BY 상위부서코드 = PRIOR 부서코드   
 

부서코드 '001' 을 가진 RECORD 로 부터 시작해서 해당부서코드가 상위부서코드로 되어있는 하위 레코드들을 찾아가는 쿼리이다. 

LEVEL, PRIOR 는 RECURSIVE SQL 에 제공되는 가상으로 만들어진 컬럼이다. 

LEVEL 은 해당 레코드의 depth 를, prior 는 "앞서 읽은"  이라는 의미로 이것을 단서로 계층적으로 레코드들을 찾아나간다.

2) 순환전개 결과의 정렬 

 SELECT LEVEL, 부서코드, 부서명, 상위부서코드
  FROM 부서
 START WITH 부서코드 ='001'
CONNECT BY 상위부서코드 = PRIOR 부서코드   
ORDER SIBLINGS BY 부서코드

 정렬된 결과를 얻기 위해서는 ORDER BY 중간에 SIBLINGS 를 써줘야 한다. 

같은 레벨에서의 정렬을 의미한다. 

3) 실행순서 

(1)START WITH 에서 시작해서 

(2) CONNECT BY 로 순환전개를 풀고 나서야

(3) WHERE 조건에 맞는 ROW 만 취하고 나머지는 discard 한다.

* 인덱스 생성 : 위 쿼리에서는 prior 에서 찾는 값이 상수가되어 '상위부서코드'를 찾게 되므로 

상위부서코드에 인덱스가 필요하다. but 역전개 쿼리 가능성도 존재하므로  '부서코드' 에도 인덱스를 

만드는게 좋다. 

트리거는 특정이벤트가 발생시 동작하는 object 이다.  

트랜잭션의 흐름에 대해서는 대부분 인지하고 있지만 트리거에 대해서는 따로 신경쓰지 않으면 인지하기 어려워 누락되기 쉽고 장애 또는 로직의 누수로 이어질수 있다. 

DBA 입장에서는 관리포인트가 증가하는 것도 달갑지 않다. 

비슷한 이유로 디비링크도 사용을 제한 할 것을 권고해왔는데 요즘은 서비스에서는 거의 사용 안하는 편이다.

EAI 를 통해서 조회를 하라고 권고한다. 

요즘은 프로시저나 함수도 다 걷어내고 AP에서 부하를 많이 가져가는게 베스트프랙티스로 알려져있다. 

결국 DB 는 단순하게 데이터를 담는 테이블, 조회를 도와주는 인덱스, 이 두가지만 사용하는 추세이다.  

오라클에서는 DBMS_RANDOM 패키지를 통해서 랜덤한 숫자를 추출하는 기능을 제공해주고 있다.

https://docs.oracle.com/database/121/ARPLS/d_random.htm#ARPLS040

해당패키지의 RANDOM 함수는 11G R1 부터 deprecated 되었다.
대신 VALUE 함수를 가공해서 원하는 결과가 나오게 수정하면된다.


Q) 총 100 건 데이터 중, 랜덤하게 10건 가져오기

랜덤숫자를 뽑고 그것으로 정렬한 10건을 가져오는 것으로 sql을 작성한다면..
중복 가능성이 있다.

SELECT *
FROM ( SELECT A, B, DBMS_RANDOM.VALUE(1,100) RN FROM EMP ORDER BY RN )
WHERE ROWNUM <= 10

레코드들 간 중복없는 랜덤정렬은
아래처럼 해야 한다

SELECT *
FROM ( SELECT A, B FROM EMP ORDER BY DBMS_RANDOM.VALUE )
WHERE ROWNUM <= 10