따봉 구걸남's 블로그

이번 포스팅은 몇개의 글에 걸쳐서 싱글노드에서 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

ora-14064 : Index with Unusable partition exists on unique/primary constraint key

파티션 인덱스가 unusuable 상태로 제약조건 추가할때 오류.

DBA_IND_PARTITIONS 뷰에서 인덱스 확인하고 REBUILD 해주자.

EX) alter index INDEX_NAME rebuild partition PARTITION_NAME;

개발자로 부터 db 가 접속이 안되니 확인요청이 들어오는 일이 종종있다. 

개발자들은 보통 sqldeveloper 나 기타 무료 툴들을 쓰는데, 클라이언트에서 서버로 접속하는 환경은

일시적인 통신환경의 이상이나 작업등으로 불안정할수 있다. (실제로 서버에 문제가 생기지 않는한)

 이때는 클라이언트 프로그램을 재기동하거나 재접속하게 되면 대부분 정상적으로 접속이 되기도 한다. 

그래도 접속이 안된다고 하면, 아래의 흐름을 따라서 어느 구간이 문제인지 확인을 해보자. 

 client 의pc (개발자) 에서 cmd 터미널을 열어 tnsping "tnsnames.ora의 name" 을 쳐본다.

수초 내로 ok 가 떨어지면 통신환경에는 이상이 없는 것이다. 그렇지 않고 응답없는 상태가 된다면 방화벽을 다시 확인해본다. 

tnsping 도 되고, sqlplus 로도 접속이 된다면 사용중인 client tool 의 문제일 가능성이 크다. 

tool 에서 실제로 접속하는 경로가 맞는지 확인해본다. 

오라클DB 아카이브 모드 변경에 대해서 알아보자

1. no arch => arch

SQL>archive log list 
SQL>shutdown immediate
SQL>startup mount;

SQL>alter database archivelog;
SQL>alter database open;
SQL>show parameter log_archive_dest_1;

SQL>alter system switch logfile;
SQL>alter system switch logfile;
SQL>alter system switch logfile;

2. arch => no arch

SQL>archive log list 
SQL>shutdown immediate
SQL>startup mount;

SQL>alter database noarchivelog;
SQL>alter database open;

재기동이 필요하다.