Category: Oracle Database

Oracle 데이터베이스 카테고리

listener.ora, tnsnames.ora 생성하기

오라클 데이터베이스 19c 를 Silent 설치를 하고 나면 listener.ora, tnsnames.ora 가 생성되지 않는다. 어떻게 수동으로 이것을 생성하는지에 대해서 알아보고 차이에 대해서 간단히 설명한다.

신기하게도 오라클 데이터베이스 19c를 Silent 설치하고 난 후에 이것을 생성하지 않는다고 하더라도 원격 클라이언트 접속에는 아무런 문제가 되지 않는다. 하지만 접속 서비스를 할당하고 접속을 쪼개고 싶다면 tnsnames.ora 파일이 반드시 있어야 한다.

Oracle Net Listener

Listener 는 정확하게는 ‘Oracle Net Listener’ 라고 한다. 리스너는 하나 혹은 그 이상의 지원하는 서비스에 대한 정보, 프로토콜 주소들을 리스닝하도록 설정된다. 리스너의 설정 정보는 listener.ora 파일에 저장된다. 모든 설정 파라메터는 디폴트 값을 가지기 때문에 별도의 설정을 하지 않더라도 시작하는데 문제가 없다. 디폴트 리스너는 LISTENER 라는 이름을 가지고 시작시 아무런 서비스를 지원하지 않으며 다음과 같은 TCP/IP 프로토콜 주소만 리스닝한다.

디폴트 리스너의 TCP/IP 프로토콜 주소 리스닝
1
(ADDRESS=(PROTOCOL=tcp)(HOST=host_name)(PORT=1521))

리스너는 클라이언트 요청을 지원하는 서비스로 포워드 한다. 이러한 서비스들은 listener.ora 파일에 정적으로 설정되어지거나 리스너로 동적으로 등록되어질 수 있다. 이러한 등록 기능을 서비스 등록(service registration) 이라고 부른다. 등록은 PMON process 에 의해서 실행된다.

참고: Configuring and Administering Oracle Net Listener

생성하기

오라클 데이터베이스 19c 를 Silent 설치하게되면 리스너 설정 파일이 생성되지 않는다. 생성하기 위해서 netca 명령어를 이용할 텐데, 역시나 Silent 모드로 실행을 해준다. 그러기 위해서는 response 파일이 있어야 하는데, 다음과 같은 경로에 있다.

netca 를 위한 response 파일
ZSH
1
2
$ ls $ORACLE_HOME/assistants/netca/netca.rsp
/u01/app/oracle/product/19.3.0/dbhome_1/assistants/netca/netca.rsp

파일을 열어보면 좀 복잡해 보이는데, 주석 등을 제거하면 설정된 값은 다음과 같다.

netca.rsp 설정
ZSH
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
$ cat $ORACLE_HOME/assistants/netca/netca.rsp | grep -v ^$| grep -v ^#
[GENERAL]
RESPONSEFILE_VERSION="19.0"
CREATE_TYPE="CUSTOM"
[oracle.net.ca]
INSTALLED_COMPONENTS={"server","net8","javavm"}
INSTALL_TYPE=""typical""
LISTENER_NUMBER=1
LISTENER_NAMES={"LISTENER"}
LISTENER_PROTOCOLS={"TCP;1521"}
LISTENER_START=""LISTENER""
NAMING_METHODS={"TNSNAMES","ONAMES","HOSTNAME"}
NSN_NUMBER=1
NSN_NAMES={"EXTPROC_CONNECTION_DATA"}
NSN_SERVICE={"PLSExtProc"}
NSN_PROTOCOLS={"TCP;HOSTNAME;1521"}

내용들은 그냥 디폴트 값을 가지고 있다. 오라클 데이터베이스가 listener.ora 파일이 없이 잘 작동하는 이유가 위 파일의 값을 가지고 작동하기 때문인 것인데, 그렇다면 이것을 가지고 설정 파일을 만들어도 문제가 없다는 것과 같은 의미가 된다.

listener.ora 파일 생성
ZSH
1
2
3
4
5
6
7
8
9
10
11
12
13
14
]$ netca -silent -responsefile $ORACLE_HOME/assistants/netca/netca.rsp
Parsing command line arguments:
    Parameter "silent" = true
    Parameter "responsefile" = /u01/app/oracle/product/19.3.0/dbhome_1/assistants/netca/netca.rsp
Done parsing command line arguments.
Oracle Net Services Configuration:
Profile configuration complete.
Oracle Net Listener Startup:
    Running Listener Control:
      /u01/app/oracle/product/19.3.0/dbhome_1/bin/lsnrctl start LISTENER
    Listener Control complete.
    Listener started successfully.
Listener configuration complete.
Oracle Net Services configuration successful. The exit code is 0

실행을 하게되면 listener.ora 파일이 생성되면서 리스너가 시작된다.

여기서 멀티테넌트 데이터베이스 구조일 경우에 PDB 에 따른 별도의 클라이언트 접속 요청을 처리해줄 필요가 있게된다. 리스너가 클라이언트 요청 파라메터에 PDB 의 SID 나 SERVICE 가 포함될 경우에 이를 받아서 처리해줘야 하는데, 그러기 위해서는 SID_LIST_LISTENER 엔트리를 사용해야 한다.

listener.ora 에 SID_LIST_LISTENER
ZSH
1
2
3
4
5
6
7
8
9
10
11
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = O19C)
      (SID_NAME = o19c)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = PDB1)
      (SID_NAME = pdb1)
    )
  )

위 내용을 생성한 listener.ora 에 추가해 준다.

주요한 설정 내용은 다음과 같다.

  • SID_NAME – oracle System IDentifier 초기 설정 파라메터 파일에 INSTANCE_NAME 으로부터 SID 값을 얻을 수 있다.
  • GLOBAL_DBNAME – 데이터베이스 서비스다. 클라이언트 접속 요청을 처리하는 동안, 리스너는 클라이언트 접속 디스크립터에 SERVICE_NAME 파라메터 값과 이 파라메터의 값을 매치시킬려고 한다. 만약 클라이언트 접속 디스크립터가 SID 파라메터를 사용한다면 리스너는 더 이상 매칭 시도를 하지 않는다. 이 파라메터는 전용 서버에서 다이나믹 설정을 지원하지 않는 Oracle8 데이터베이스 설정을 위한 것이였다. 이 파라메터는 Oracle8i 나 그 이후에 데이터베이스 서비스 사용을 위해 필요할 수도 있다. 이 파라메터 값은 기본적으로 초기화 파라메터 파일에 DB_NAME 과 DB_DOMAIN 파라메터의 조합으로부터 (DB_NAME.DB_DOMAIN) 얻을 수 있다.
  • ORACLE_HOME – 오라클 인스턴스의 위치. 세팅하지 않으면 오라클 홈 디렉토리으로 가정한다. 리눅스와 유닉스에서 세팅은 옵션이지만 MS Windows 에서는 세팅을 무시하면 레지스트리 값 HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEID 를 사용한다.

오라클 데이터베이스의 컨테이너별 서비스를 알기위해서는 다음의 쿼리를 실행하면 된다.

오라클 데이터베이스 서비스들
PgSQL
1
2
3
4
5
6
7
8
9
10
SQL> SELECT name, pdb
    FROM   v$services
    ORDER BY name;
 
NAME             PDB        
SYS$BACKGROUND   CDB$ROOT  
SYS$USERS        CDB$ROOT  
o19c             CDB$ROOT  
o19cXDB          CDB$ROOT  
pdb1             PDB1

Local Naming Prarameters in the tnsnames.ora 파일

tnsnames.ora 파일은 네트워크 서비스 이름을 접속 디스크립트로 매핑하거나, Net 서비스 이름을 리스너 프로토콜 주소로 맵핑을 포함한 설정 파일이다. Net 서비스 이름은 접속 디스크립터를 포함하는 데이터베이스 네트워크 주소와 연결된 별명(Alias) 이다. 접속 드스크립터는 프로토콜 주소를 통한 리스너의 위치와 연결을 위한 데이터베이스 서비스의 이름을 포함한다. 클라이언트와 서버는 애플리케이션에서 연결할대에 Net 서비스 이름을 사용한다.

오라클 멀티테넌트 데이터베이스에서 CDB, PDB 별로 접속을 하기 위해서는 tnsnames.ora 파일을 다음과 같이 생성해 준다.

tnsnames.ora 파일 내용
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
O19C =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oradb1.systemv.local)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = o19c)
    )
  )
 
PDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oradb1.systemv.local)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdb1)
    )
  )

참고: Local Naming Parameters in the tnsnames.ora File

listener.ora, tnsnames.ora 차이

접속하는 방법에 차이를 둔다. 원칙적으로 접속하는 방법은 다음과 같다.

Direct 접속방법
ZSH
1
]$ sqlplus hr/HrPassword1@oradb1.systemv.local:1521/pdb1

@ 를 기준으로 뒤에 있는 호스트네임:포트/서비스 이다. 이것은 Direct 연결 방법이라고 하는데, 이 연결은 listener.ora 에 정의된 리스너 설정파일에 의해서 접속이 이루어진다.

이것을 짧게 별명만으로 지정할 수 있는데, 그것을 가능하게 하는 것이 tnsnames.ora 이다. pdb1 컨테이너에 접속하기 위해서는 PDB1 별명을 사용하기 때문에 다음과 같이 해도 된다.

Alias 를 이용한 접속방법
ZSH
1
]$ sqlplus hr/HrPassword1@PDB1

PDB1 은 tnsnames.ora 파일에서 별명을 찾아서 접속 디스크립트에 값을 가져다 쓴다. 그리고 연결을 시도하고 이것을 오라클 리스너가 받아서 listener.ora 파일에 파라메터와 매핑해 접속이 이루어지게 된다.

멀티테넌트 데이터베이스 구조에서는 listener.ora 파일에서 SID_LIST_LISTENER 설정을 해줘야지만 PDB 에 접속이 가능해진다.

오라클 기본 정보 확인

오라클을 설치하고 나면 정보를 확인해야 한다. 오라클은 많은 테이블, 뷰, 동적쿼리등을 지원하는데 워낙 많다보니까 다 알수는 없다. 필요한 정보를 위한 간단한 쿼리들을 소개 한다.

데이터베이스 컴포넌트들의 상태 확인

오라클 데이터베이스는 다양한 컴포넌트들로 구성되는데, 이에 대한 상태를 확인할 수 있다.

데이터베이스 컴포넌트들의 상태 확인
PgSQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
SQL> col comp_name for a40
SQL> col version for a15
SQL> col status for a10
SQL> SELECT name, comp_name, version, status FROM v$database, dba_registry;
NAME   COMP_NAME                            VERSION      STATUS      
O19C   Oracle Database Catalog Views        19.0.0.0.0   VALID        
O19C   Oracle Database Packages and Types   19.0.0.0.0   VALID        
O19C   Oracle Real Application Clusters     19.0.0.0.0   OPTION OFF  
O19C   JServer JAVA Virtual Machine         19.0.0.0.0   VALID        
O19C   Oracle XDK                           19.0.0.0.0   VALID        
O19C   Oracle Database Java Packages        19.0.0.0.0   VALID        
O19C   OLAP Analytic Workspace              19.0.0.0.0   VALID        
O19C   Oracle XML Database                  19.0.0.0.0   VALID        
O19C   Oracle Workspace Manager             19.0.0.0.0   VALID        
O19C   Oracle Text                          19.0.0.0.0   VALID        
O19C   Oracle Multimedia                    19.0.0.0.0   VALID        
O19C   Spatial                              19.0.0.0.0   VALID        
O19C   Oracle OLAP API                      19.0.0.0.0   VALID        
O19C   Oracle Label Security                19.0.0.0.0   VALID        
 
NAME   COMP_NAME               VERSION      STATUS  
O19C   Oracle Database Vault   19.0.0.0.0   VALID    
 
 
15 rows selected.

만일 INVALID 컴포넌트가 있다면 다음과 같이 해준다.

데이터베이스 컴포넌트 재컴파일
PgSQL
1
SQL> @?/rdbms/admin/utlrp.sql

데이터베이스 이름 확인

데이터베이스 이름이 뭔지를 확인할 수 있다.

데이터베이스 이름 확인
PgSQL
1
2
3
SQL> SELECT dbid, name, db_unique_name, created, log_mode, open_mode, cdb, con_id FROM v$database;
        DBID NAME   DB_UNIQUE_NAME   CREATED     LOG_MODE       OPEN_MODE    CDB     CON_ID
  2857479539 O19C   o19c             30-APR-21   NOARCHIVELOG   READ WRITE   YES          0            

CDB 가 YES 면 데이터베이스가 CDB 로 생성되었다는 것을 말한다. NO 라면 non-CDB 데이터베이스다.

DB_UNIQUE_NAME 은 쉘 환경변수에도 사용되고 있어 확인해 둘 필요가 있다. 데이터베이스 상태는 READ WRITE 상태여야 정상상태로 본다.

컨테이너 확인

오라클 12c 부터 도입된 개념이 멀티테넌트 컨테이너 데이터베이스다. 어떤 컨테이너들이 있는지 확인해 볼 필요가 있다.

컨테이터 목록 확인
PgSQL
1
2
3
4
5
SQL> select con_id, name, open_mode, dbid, con_uid, guid from v$containers;
  CON_ID NAME       OPEN_MODE            DBID      CON_UID GUID                              
       1 CDB$ROOT   READ WRITE     2857479539            1 86B637B62FDF7A65E053F706E80A27CA  
       2 PDB$SEED   READ ONLY      3121618961   3121618961 C1202C7545A136AAE0531D60A8C0E59E  
       3 PDB1       READ WRITE     2824454508   2824454508 C120605562113BC8E0531D60A8C0D56F

v$containers 뷰는 CDB 안에 root 와 모든 PDB 를 포함한 모든 컨테이너에 대한 정보를 제공한다. 이것으로 PDB 이름을 확인해 볼 수 있다.

모든 CDB 는 다음의 컨테이너를 포함 한다.

  • 정확하기 한개의 root – root 는 오라클이 제공하는 메타데이터와 일반 유저들을 저장한다. 메타데이터는 오라클이 제공하는 PL/SQL 패키지드르이 소스코드 같은 것이다. 일반 유저는 모든 컨테이너가 알아야 하는 데이터베이스 사용자다. root 컨테이너의 이름은 CDB$ROOT 다.
  • 정확히 하나의 시드(seed) PDB – 시드 PDB 는 CDB 가 새로운 PDB를 생성하는데 사용할 수 있도록 하는 시스템이 제공하는 템플릿이다. 시드 PDB 이름은 PDB$SEED 다. PDB$SEED 를 추가하거나 수정할 수 없다.
  • 없거나 하나 이상의 사용자 생성 PDB – PDB는 사용자가 생성한 엔터티로 코드와 데이터를 포함한다. 예를들어, PDB는 인적자원(Human Resources) 나 판매 애플리케이션(Sales application) 과 같은 특정한 애플리케이션을 지원할 수있다. CDB 생성 시점시 PDB는 존재하지 않는다. PDB는 비지니스 요청에 따라 추가 된다.
Multitenant Container Database

위 그림은 멀티테넌트 컨테이너 데이터베이스 구조 이다. CDB 안에 PDB 가 존재하는 구조다. PDB 는 Pluggable Database 다.

root 컨테이너와 함께 오라클 데이터베이스는 자동으로 seed PDB(PDB$SEED) 를 생성 한다. 다음 그래프는 새로운 CDB 생성을 보여준다.

새로운 CDB 생성. seed PDB 도 함께 생성된다.

연결 상태 보기

CDB, PDB 구조에서 현재 내가 어느 곳에 연결되어 있는지 아는 건 중요 하다. 다음의 명령어로 확인해 볼 수 있다.

연결 정보 확인
PgSQL
1
2
3
4
5
6
7
8
SQL> show con_id;
CON_ID
------------------------------
1
SQL> show con_name;
CON_NAME
------------------------------
CDB$ROOT

혹은 다음과 같은 쿼리문으로 확인 가능하다.

연결 정보 확인
PgSQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SQL> select sys_context('USERENV','CON_NAME') CON_NAME,
                sys_context('USERENV','CON_ID') CON_ID,
                sys_context('USERENV','DB_NAME') DB_NAME from DUAL;
 
CON_NAME   CON_ID   DB_NAME  
CDB$ROOT   1        o19c      
 
 
SQL> alter session set container=pdb1;
 
Session altered.
 
SQL> select sys_context('USERENV','CON_NAME') CON_NAME,
                sys_context('USERENV','CON_ID') CON_ID,
                sys_context('USERENV','DB_NAME') DB_NAME from DUAL;
 
CON_NAME   CON_ID   DB_NAME  
PDB1       3        PDB1

컨테이너 타입 확인하기

내가 접속한 컨테이너가 CDB 인지, PDB 인지 타입을 확인할 수 있다.

컨테이너 타입 확인
PgSQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SQL> select decode(sys_context('USERENV', 'CON_NAME'),'CDB$ROOT',sys_context('USERENV', 'DB_NAME'),sys_context('USERENV', 'CON_NAME')) DB_NAME,
                decode(sys_context('USERENV','CON_ID'),1,'CDB','PDB') TYPE
           from DUAL;
 
DB_NAME   TYPE  
o19c      CDB    
 
 
SQL> alter session set container=pdb1;
 
Session altered.
 
SQL> select decode(sys_context('USERENV', 'CON_NAME'),'CDB$ROOT',sys_context('USERENV', 'DB_NAME'),sys_context('USERENV', 'CON_NAME')) DB_NAME,
                decode(sys_context('USERENV','CON_ID'),1,'CDB','PDB') TYPE
           from DUAL;
 
DB_NAME   TYPE  
PDB1      PDB

컨테이너 연결 세션 변경하기

일반 사용자(common user) 는 CDB, PDB 모두에 걸친 사용자임으로 연결 세션 변경을 통해서 PDB, CDB 를 교체할 수 있다.

컨테니어 연결 세션 변경
PgSQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
SQL> alter session set container=pdb1;
 
Session altered.
 
SQL> show con_id;
CON_ID
------------------------------
3
SQL> show con_name;
CON_NAME
------------------------------
PDB1
 
SQL> alter session set container=cdb$root;
 
Session altered.
 
SQL> show con_id;
CON_ID
------------------------------
1
SQL> show con_name;
CON_NAME
------------------------------
CDB$ROOT

CDB 와 연결된 PDB 상태 보기

CDB 와 연결된 PDB 상태는 다음과 같이 조회할 수 있다.

PDB 상태 정보 보기
PgSQL
1
2
3
4
5
SQL> COLUMN PDB_NAME FORMAT A15
SQL> SELECT PDB_ID, PDB_NAME, STATUS FROM CDB_PDBS ORDER BY PDB_ID;
  PDB_ID PDB_NAME   STATUS  
       2 PDB$SEED   NORMAL  
       3 PDB1       NORMAL

STATUS 값을 확인해 상태를 점검할 수 있다.

PDB 의 오픈 모드(Open Mode) 보기

이 쿼리를 통해서 마지막 오픈시간을 확인해 볼 수 있다. root 컨테이너에서는 모든 PDB 에 대한 정보를 보여주며, PDB 에 있다면 오직 하나의 PDB에 대한 정보만 보여준다.

PDB 오픈 상태 보기
PgSQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SQL> COLUMN NAME FORMAT A15
SQL> COLUMN RESTRICTED FORMAT A10
SQL> COLUMN OPEN_TIME FORMAT A30
SQL>  
SQL> SELECT NAME, OPEN_MODE, RESTRICTED, OPEN_TIME FROM V$PDBS;
NAME       OPEN_MODE    RESTRICTED   OPEN_TIME                                
PDB$SEED   READ ONLY    NO           01-MAY-21 04.52.38.929000000 PM +09:00  
PDB1       READ WRITE   NO           01-MAY-21 04.52.39.609000000 PM +09:00  
 
SQL> alter session set container = pdb1;
 
Session altered.
 
SQL> SELECT NAME, OPEN_MODE, RESTRICTED, OPEN_TIME FROM V$PDBS;
NAME   OPEN_MODE    RESTRICTED   OPEN_TIME                                
PDB1   READ WRITE   NO           01-MAY-21 04.52.39.609000000 PM +09:00

컨테이터 데이터 오브젝트 확인

root 에서(root CDB), 컨테이터 데이터 오브젝트는 root 나 PDB에 포함된 데이터베이스 오브젝트에(테이블 이나 사용자) 대한 정보를 볼 수 있다.

CDB_ 뷰는 컨테이너 데이터 오브젝트들인데, 여기에는 CON_ID 컬럼이 있다. 이 컬럼은 각 PDB 의 컨테이터 ID 를 나타내는 것이며 DBA_PDBS 뷰에 쿼리하면 각 컨테이너 ID 에 대한 PDB 이름을 알 수 있다. 이 두개의 뷰를 조인해서 다음과 같이 데이터 오브젝트 확인이 가능하다.

컨테이터 데이터 오브젝트 확인
PgSQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SQL> COLUMN PDB_NAME FORMAT A15
SQL> COLUMN OWNER FORMAT A15
SQL> COLUMN TABLE_NAME FORMAT A30
SQL> SELECT p.PDB_ID, p.PDB_NAME, t.OWNER, t.TABLE_NAME
  FROM DBA_PDBS p, CDB_TABLES t
  WHERE p.PDB_ID > 2 AND
        t.OWNER IN('HR','OE') AND
        p.PDB_ID = t.CON_ID
  ORDER BY p.PDB_ID;
 
  PDB_ID PDB_NAME   OWNER   TABLE_NAME    
       3 PDB1       HR      REGIONS      
       3 PDB1       HR      LOCATIONS    
       3 PDB1       HR      DEPARTMENTS  
       3 PDB1       HR      JOBS          
       3 PDB1       HR      EMPLOYEES    
       3 PDB1       HR      JOB_HISTORY  
       3 PDB1       HR      COUNTRIES    
 
 
7 rows selected.

p.PDB_ID > 2 조건을 준 이유는 root 와 seed 컨테이너를 제외하기 위함이다.

PDBS 에 있는 사용자들 보기

DBA_PDBS 와 CDB_USERS 를 조합하면 각 PDB에 사용자들을 확인해 볼 수 있다.

PDB에 있는 사용자
PgSQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
SQL> COLUMN PDB_NAME FORMAT A15
SQL> COLUMN USERNAME FORMAT A30
SQL> SELECT p.PDB_ID, p.PDB_NAME, u.USERNAME
  FROM DBA_PDBS p, CDB_USERS u
  WHERE p.PDB_ID > 2 AND
        p.PDB_ID = u.CON_ID
  ORDER BY p.PDB_ID;
 
  PDB_ID PDB_NAME   USERNAME            
       3 PDB1       SYS                
       3 PDB1       SYSTEM              
       3 PDB1       XS$NULL            
       3 PDB1       LBACSYS            
       3 PDB1       OUTLN              
       3 PDB1       DBSNMP              
       3 PDB1       APPQOSSYS          
       3 PDB1       DBSFWUSER          
       3 PDB1       GGSYS              
       3 PDB1       ANONYMOUS          
       3 PDB1       CTXSYS              
       3 PDB1       DVSYS              
       3 PDB1       DVF                
       3 PDB1       GSMADMIN_INTERNAL  
 
  PDB_ID PDB_NAME   USERNAME                
       3 PDB1       MDSYS                    
       3 PDB1       OLAPSYS                  
       3 PDB1       XDB                      
       3 PDB1       WMSYS                    
       3 PDB1       GSMCATUSER              
       3 PDB1       MDDATA                  
       3 PDB1       SYSBACKUP                
       3 PDB1       REMOTE_SCHEDULER_AGENT  
       3 PDB1       PDBADMIN                
       3 PDB1       GSMUSER                  
       3 PDB1       SYSRAC                  
       3 PDB1       HR                      
       3 PDB1       OJVMSYS                  
       3 PDB1       SI_INFORMTN_SCHEMA      
 
  PDB_ID PDB_NAME   USERNAME    
       3 PDB1       AUDSYS      
       3 PDB1       DIP          
       3 PDB1       ORDPLUGINS  
       3 PDB1       SYSKM        
       3 PDB1       ORDDATA      
       3 PDB1       ORACLE_OCM  
       3 PDB1       SYS$UMF      
       3 PDB1       SYSDG        
       3 PDB1       ORDSYS      
 
 
37 rows selected.

p.PDB_ID > 2 조건을 준 이유는 root 와 seed 컨테이너를 제외하기 위함이다.

각 PDB 의 데이터 파일 보기

DBA_PDBS 와 CDB_DATA_FILES 뷰를 조합하면 데이터 파일들을 확인할 수 있다.

PDB 의 데이터 파일 보기
PgSQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SQL> COLUMN PDB_ID FORMAT 999
SQL> COLUMN PDB_NAME FORMAT A8
SQL> COLUMN FILE_ID FORMAT 9999
SQL> COLUMN TABLESPACE_NAME FORMAT A10
SQL> COLUMN FILE_NAME FORMAT A45
SQL>
SQL> SELECT p.PDB_ID, p.PDB_NAME, d.FILE_ID, d.TABLESPACE_NAME, d.FILE_NAME
      FROM DBA_PDBS p, CDB_DATA_FILES d
     WHERE p.PDB_ID = d.CON_ID
     ORDER BY p.PDB_ID;
 
  PDB_ID PDB_NAME     FILE_ID TABLESPACE_NAME   FILE_NAME                              
       3 PDB1               9 SYSTEM            /u02/oradata/O19C/pdb1/system01.dbf    
       3 PDB1              10 SYSAUX            /u02/oradata/O19C/pdb1/sysaux01.dbf    
       3 PDB1              11 UNDOTBS1          /u02/oradata/O19C/pdb1/undotbs01.dbf  
       3 PDB1              12 USERS             /u02/oradata/O19C/pdb1/users01.dbf

혹은 다음과 같이 확인해 볼 수 있다.

컨테이너 데이터 파일 보기
PgSQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SQL> SELECT c.name DB_NAME, a.name TABLESPACE_NAME, b.name DATA_FILE_NAME, b.bytes/1024/1024 SIZE_MB, b.status
     -- c.name DB_NAME, a.con_id, a.ts#, a.name TABLESPACE_NAME, b.name DATA_FILE_NAME, b.file#, b.blocks, b.bytes/1024/1024 SIZE_MB, b.status, b.enabled, b.creation_time
     FROM v$tablespace a, v$datafile b, v$containers c
     WHERE a.con_id = b.con_id
     AND a.con_id = c.con_id
     AND a.ts# = b.ts#
     ORDER BY a.con_id, a.TS#;
 
DB_NAME    TABLESPACE_NAME   DATA_FILE_NAME                              SIZE_MB STATUS  
CDB$ROOT   SYSTEM            /u02/oradata/O19C/system01.dbf                  910 SYSTEM  
CDB$ROOT   SYSAUX            /u02/oradata/O19C/sysaux01.dbf                  580 ONLINE  
CDB$ROOT   UNDOTBS1          /u02/oradata/O19C/undotbs01.dbf                 330 ONLINE  
CDB$ROOT   USERS             /u02/oradata/O19C/users01.dbf                     5 ONLINE  
PDB$SEED   SYSTEM            /u02/oradata/O19C/pdbseed/system01.dbf          270 SYSTEM  
PDB$SEED   SYSAUX            /u02/oradata/O19C/pdbseed/sysaux01.dbf          330 ONLINE  
PDB$SEED   UNDOTBS1          /u02/oradata/O19C/pdbseed/undotbs01.dbf         100 ONLINE  
PDB1       SYSTEM            /u02/oradata/O19C/pdb1/system01.dbf             280 SYSTEM  
PDB1       SYSAUX            /u02/oradata/O19C/pdb1/sysaux01.dbf             350 ONLINE  
PDB1       UNDOTBS1          /u02/oradata/O19C/pdb1/undotbs01.dbf            100 ONLINE  
PDB1       USERS             /u02/oradata/O19C/pdb1/users01.dbf                5 ONLINE  
 
 
11 rows selected.

CDB 에 임시 파일들 보기

CDB_TEMP_FILES 뷰를 활용하면 CDB 에 각 임시 파일 이름과 위치를 확인할 수 있다.

CDB 에 임시 파일들 보기
PgSQL
1
2
3
4
5
6
7
8
9
10
11
SQL> COLUMN CON_ID FORMAT 999
SQL> COLUMN FILE_ID FORMAT 9999
SQL> COLUMN TABLESPACE_NAME FORMAT A15
SQL> COLUMN FILE_NAME FORMAT A45
SQL>
SQL> SELECT CON_ID, FILE_ID, TABLESPACE_NAME, FILE_NAME
      FROM CDB_TEMP_FILES
      ORDER BY CON_ID;
 
  CON_ID   FILE_ID TABLESPACE_NAME   FILE_NAME                          
       3         3 TEMP              /u02/oradata/O19C/pdb1/temp01.dbf

컨트롤 파일 확인

다음과 같이 컨트롤 파일들을 확인할 수 있다.

컨트롤 파일 확인
PgSQL
1
2
3
4
SQL> show parameter control_files;
NAME          TYPE   VALUE                                                            
------------- ------ ----------------------------------------------------------------
control_files string /u02/oradata/O19C/control01.ctl, /u02/oradata/O19C/control02.ctl

Redo Log 파일 확인

다음과 같이 RedoLog 파일을 확인할 수 있다.

Redo Log 파일 확인
PgSQL
1
2
3
4
5
6
7
8
SQL> col member for a50;
SQL> SELECT a.group#, a.members, a.bytes/1024/1024 SIZE_MB, a.status, b.member
  2   FROM v$log a, v$logfile b
  3   WHERE a.group# = b.group#;
  GROUP#   MEMBERS   SIZE_MB STATUS     MEMBER                        
       3         1        50 CURRENT    /u02/oradata/O19C/redo03.log  
       2         1        50 INACTIVE   /u02/oradata/O19C/redo02.log  
       1         1        50 INACTIVE   /u02/oradata/O19C/redo01.log

PDB에 연결된 서비스 보기

CDB_SERVICES 뷰를 통해서 PDB 이름, 네트워크 이름, 컨테이너 ID 등을 확인해 볼 수 있다.

PDB에 연결된 서비스 보기
PgSQL
1
2
3
4
5
6
7
8
9
10
11
SQL> COLUMN NETWORK_NAME FORMAT A30
SQL> COLUMN PDB FORMAT A15
SQL> COLUMN CON_ID FORMAT 999
SQL>
SQL> SELECT PDB, NETWORK_NAME, CON_ID FROM CDB_SERVICES
      WHERE PDB IS NOT NULL AND
            CON_ID > 2
      ORDER BY PDB;
 
PDB    NETWORK_NAME     CON_ID
PDB1   PDB1                  3

PDB 의 히스토리 보기

CDB_PDB_HISTORY 뷰는 PDB의 생성된 시간과 여러 히스토리 정보들을 보여준다.

PDB 의 히스토리 보기
PgSQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SQL> COLUMN DB_NAME FORMAT A10
SQL> COLUMN CON_ID FORMAT 999
SQL> COLUMN PDB_NAME FORMAT A15
SQL> COLUMN OPERATION FORMAT A16
SQL> COLUMN OP_TIMESTAMP FORMAT A10
SQL> COLUMN CLONED_FROM_PDB_NAME FORMAT A15
SQL>  
SQL> SELECT DB_NAME, CON_ID, PDB_NAME, OPERATION, OP_TIMESTAMP, CLONED_FROM_PDB_NAME
      FROM CDB_PDB_HISTORY
      WHERE CON_ID > 2
      ORDER BY CON_ID;
 
DB_NAME      CON_ID PDB_NAME   OPERATION   OP_TIMESTAMP   CLONED_FROM_PDB_NAME  
SEEDDATA          3 PDB$SEED   UNPLUG      17-APR-19                            
O19C              3 PDB$SEED   PLUG        30-APR-21      PDB$SEED              
O19C              3 PDB1       CREATE      30-APR-21      PDB$SEED

오라클 데이터베이스 19c 샘플 스키마 생성

오라클 데이터베이스 19c 를 Silent 설치를 할 경우에 대부분 데이터베이스를 생성하고 끝나게 된다. 하지만 오라클은 샘플 스키마를 제공하고 있는데, HR 관련 된 내용의 샘플 스키마를 제공 한다. 이것을 설치해보자.

SQLcl 활용

SQL Plus 에 기능을 더한 SQLcl 를 활용할 것이다. SQLcl 은 $ORACLE_HOME/sqldeveloper/sqldeveloper/bin 디렉토리가 있다. 여기에 sql 스크립트가 존재하는데 이것이 SQLcl 이다.

SQLcl 사용하기
ZSH
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
]$ cd $ORACLE_HOME
]$ cd sqldeveloper/sqldeveloper/bin
]$ chmod +x sql
]$ ./sql
 
SQLcl: Release 19.1 Production on Fri Apr 30 23:23:46 2021
 
Copyright (c) 1982, 2021, Oracle.  All rights reserved.
 
Username? (''?) sys as sysdba
Password? (**********?) ************
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
 
SQL> set sqlformat ansiconsole;
SQL>

“set sqlformat ansiconsole” 를 할 경우에 결과를 보다 보기 좋게 해준다.

PDB 로 세션 변경

오라클 데이터베이스 19c 의 경우에 CDB, PDB 개념이 존재한다. 실제 사용자 데이터베이스는 PDB 에 생성된다. 세션을 PDB 로 변경해준다.

PDB 이름 확인
PgSQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SQL> select name, open_mode, restricted from v$pdbs;
NAME       OPEN_MODE    RESTRICTED  
PDB$SEED   READ ONLY    NO          
PDB1       READ WRITE   NO
 
SQL> alter session set container = pdb1;
 
Session altered.
 
SQL> select dbid, con_id, name from v$pdbs;
        DBID   CON_ID NAME  
  2824454508        3 PDB1
 
SQL> show pdbs;
  CON_ID CON_NAME   OPEN MODE    RESTRICTED  
       3 PDB1       READ WRITE   NO

HR 스키마 설치

HR 스키마는 오라클 데이터베이스에 있다. 경로는 다음과 같다.

HR 스키마 경로
ZSH
1
2
]$ ls $ORACLE_HOME/demo/schema/human_resources
hr_analz.sql  hr_code.sql  hr_comnt.sql  hr_cre.sql  hr_drop_new.sql  hr_drop.sql  hr_idx.sql  hr_main_new.sql  hr_main.sql  hr_popul.sql

hr_main.sql 를 실행하면 설치가 진행되는데, 사용자들로부터 다음과 같은 값을 입력 받는다.

  • HR 스키마 사용자의 패스워드
  • HR 스키마에 기본 테이블스페이스
  • HR 를 위한 임시 테이블스페이스
  • 로그 경로

기본 테이블스페이스, 임시 테이블스페이스를 만들어도 되지만 pdb1 에 있는 것을 가져다 써도 된다. 테이블스페이스 이름을 알아야 한다.

pdb1 의 테이블스페이스 이름
ZSH
1
2
3
4
5
6
7
SQL> select tablespace_name from user_tablespaces;
TABLESPACE_NAME  
SYSTEM            
SYSAUX            
UNDOTBS1          
TEMP              
USERS

USERS 는 기본 테이블스페이스, TEMP 를 임시 테이블스페이스로 사용하면 될 듯 하다. 이제 설치를 해보자.

HR 스키마 설치
ZSH
1
2
3
4
5
6
7
8
9
10
11
12
13
SQL> @/u01/app/oracle/product/19.3.0/dbhome_1/demo/schema/human_resources/hr_main.sql
 
specify password for HR as parameter 1:
Enter value for 1: HrPassword1
 
specify default tablespeace for HR as parameter 2:
Enter value for 2: USERS
 
specify temporary tablespace for HR as parameter 3:
Enter value for 3: TEMP
 
specify log path as parameter 4:
Enter value for 4: /u01/app/oracle/product/19.3.0/dbhome_1/demo/schema/log

log 경로는 demo/schema 디렉토리에 log 디렉토리를 활용했다.

hr 계정 패스워드 설정 및 UNLOCK

계정을 생성하면 기본적으로 계정은 LOCKED 상태가 되어서 사용할 수 없다. UNLOCK 을 해줘야 한다.

HR 계정 UNLOCK
PgSQL
1
2
SQL> ALTER USER hr ACCOUNT UNLOCK;
SQL> ALTER USER hr IDENTIFIED BY HrPassword1;

Oracle Database 19c 설치

오라클 데이터베이스 19c (Oracle Database 19c) 설치에 대한 문서다. 설치는 Slient Mode 로 설치되었으면 PDB 를 기반으로 하였다. PDB, CDB 에 대한 설명은 오라클 문서를 참고.

환경

  • Oracle Linux 8.3
  • Kernel: 5.4.17-2102.200.13.el8uek.x86_64
  • Memory: 7.7Gib
  • Swap: 12Gi
  • Root Partition: 32GB

호스트 네임 변경

호스트 네임 변경
ZSH
1
2
3
]# hostnamectl set-hostname oradb1.systemv.local
]# vim /etc/hosts
192.168.96.29   oradb1.systemv.local    oradb1

Selinux 를 Permissive 로 변경

Selinux 비활성화
ZSH
1
2
]# setenforce Permissive
]# sed -i 's/^SELINUX=enforcing$/SELINUX=Permissive/' /etc/selinux/config

HugePages 비활성화.

오라클은 HugePages 를 비활성화할 것을 권장하고 있다.

Selinux 비활성화
ZSH
1
2
3
4
5
6
]# cat /sys/kernel/mm/transparent_hugepage/enabled
[always] madvise never
]# vim /etc/default/grub
- GRUB_CMDLINE_LINUX="crashkernel=auto resume=UUID=e17bf72e-de7f-49a6-9d37-4c61864a4ba3"
+ GRUB_CMDLINE_LINUX="crashkernel=auto resume=UUID=e17bf72e-de7f-49a6-9d37-4c61864a4ba3 transparent_hugepage=never"
]# systemctl reboot

PreInstallation RPM 설치

오라클은 시스템 계정, 설치 디렉토리, 시스템 리소스 설정등을 필요로 하는데 PreInstallation RPM 은 이것을 자동으로 해준다.

PreInstallation RPM 설치
ZSH
1
]# dnf install oracle-database-preinstall-19c

이것은 자동으로 필요로하는 시스템 설정을 해준다. 이것을 하고 난후에 변경된 시스템 설정들은 다음과 같다.

시스템 그룹 생성

다음과 같은 시스템 그룹이 생성되었다.

오라클 데이터베이스 19c 를 위한 시스템 그룹 생성
ZSH
1
2
3
4
5
6
7
]# groupadd -g 54321 oinstall
]# groupadd -g 54322 dba
]# groupadd -g 54323 oper
]# groupadd -g 54324 backupdba
]# groupadd -g 54325 dgdba
]# groupadd -g 54326 kmdba
]# groupadd -g 54330 racdba

oracle 계정 생성 및 소속 그룹 변경

oracle 계정이 생성되는데 기본 소속 그룹은 oinstall 이며 dba,oper,backupdba,dgdba,kmdba,racdba 그룹에도 속해야 한다.

오라클 데이터베이스 19c 를 위한 시스템 계정 생성
ZSH
1
2
3
4
]# useradd -u 54321 -g oinstall -G dba,oper,backupdba,dgdba,kmdba,racdba oracle
]# echo "oracle" | passwd oracle --stdin
Changing password for user oracle.
passwd: all authentication tokens updated successfully.

oracle 시스템 계정에 대한 시스템 자원 제한 설정.

파일, 메모리, 프로세스 등에 대한 자원 사용에 대해서 oracle 계정에 제한을 둔다. 이것은 /etc/security/limits.d 디렉토리에 파일을 생성해 설정한다.

오라클 데이터베이스 19c 에 oracle 시스템 계정 자원 제한 설정
ZSH
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
]# vim /etc/security/limits.d/oracle-database-19c.conf
# oracle database 19c setting for nofile soft limit is 1024
oracle   soft   nofile    1024
 
# oracle database 19c setting for nofile hard limit is 65536
oracle   hard   nofile    65536
 
# oracle database 19c setting for nproc soft limit is 16384
# refer orabug15971421 for more info.
oracle   soft   nproc    16384
 
# oracle database 19c setting for nproc hard limit is 16384
oracle   hard   nproc    16384
 
# oracle database 19c setting for stack soft limit is 10240KB
oracle   soft   stack    10240
 
# oracle database 19c setting for stack hard limit is 32768KB
oracle   hard   stack    32768
 
# oracle database 19c setting for memlock hard limit is maximum of 128GB on x86_64 or 3GB on x86 OR 90 % of RAM
oracle   hard   memlock    134217728
 
# oracle database 19c setting for memlock soft limit is maximum of 128GB on x86_64 or 3GB on x86 OR 90% of RAM
oracle   soft   memlock    134217728
 
# oracle database 19c setting for data soft limit is 'unlimited'
oracle   soft   data    unlimited
 
# oracle database 19c setting for data hard limit is 'unlimited'
oracle   hard   data    unlimited

memlock 부분을 조정해 줄 필요가 있다. 전체 물리 메모리가 128GB 이하라면 약 90%를 할당을 권장하고 있다.

커널 파라메터 수정.

커널 파라메터는 sysctl 명령어로 조정이 되는데, 이것은 /etc/sysctl.d 디렉토리에 파일을 생성해 적용해 준다.

오라클 데이터베이스 19c 에 커널 파라메터 수정
ZSH
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
]# vim /etc/systctl.d/97-oracle-database-19c.conf
# sysctl settings are defined through files in
# /usr/lib/sysctl.d/, /run/sysctl.d/, and /etc/sysctl.d/.
#
# Vendors settings live in /usr/lib/sysctl.d/.
# To override a whole file, create a new file with the same in
# /etc/sysctl.d/ and put new settings there. To override
# only specific settings, add a file with a lexically later
# name in /etc/sysctl.d/ and put new settings there.
#
# For more information, see sysctl.conf(5) and sysctl.d(5).
 
# oracle database 19c setting for fs.file-max is 6815744
fs.file-max = 6815744
 
# oracle database 19c setting for kernel.sem is '250 32000 100 128'
kernel.sem = 250 32000 100 128
 
# oracle database 19c setting for kernel.shmmni is 4096
kernel.shmmni = 4096
 
# oracle database 19c setting for kernel.shmall is 1073741824 on x86_64
kernel.shmall = 1073741824
 
# oracle database 19c setting for kernel.shmmax is 4398046511104 on x86_64
kernel.shmmax = 4398046511104
 
# oracle database 19c setting for kernel.panic_on_oops is 1 per Orabug 19212317
kernel.panic_on_oops = 1
 
# oracle database 19c setting for net.core.rmem_default is 262144
net.core.rmem_default = 262144
 
# oracle database 19c setting for net.core.rmem_max is 4194304
net.core.rmem_max = 4194304
 
# oracle database 19c setting for net.core.wmem_default is 262144
net.core.wmem_default = 262144
 
# oracle database 19c setting for net.core.wmem_max is 1048576
net.core.wmem_max = 1048576
 
# oracle database 19c setting for net.ipv4.conf.all.rp_filter is 2
net.ipv4.conf.all.rp_filter = 2
 
# oracle database 19c setting for net.ipv4.conf.default.rp_filter is 2
net.ipv4.conf.default.rp_filter = 2
 
# oracle database 19c setting for fs.aio-max-nr is 1048576
fs.aio-max-nr = 1048576
 
# oracle database 19c setting for net.ipv4.ip_local_port_range is 9000 65500
net.ipv4.ip_local_port_range = 9000 6550
]# sysctl --system

Firewalld 설정

Oracle SQL* NET Listener 포트인 1521/tcp 를 열어준다.

Oracle SQL* NET Listener 를 위한 firewalld 설정
ZSH
1
2
3
4
]# firewall-cmd --permanent --add-port=1521/tcp
success
]# firewall-cmd --reload
success

Oracle Universal Installer (OUI)

OUI 설치는 터미널에 명령행으로 설치를 진행할 수 있도록 해준다. 이를 위해서 필요한 작업이 있다.

디렉토리 생성

다음과 같이 디렉토리를 생성해 준다.

설치를 위한 디렉토리 생성
ZSH
1
2
3
4
5
6
7
8
]# mkdir -p /u01/app/oracle/product/19.3.0/dbhome_1
]# mkdir -p /u01/app/oraInventory
]# mkdir -p /u02/oradata
]#
]# mkdir -p /u01/software
]#
]# chown -R oracle:oinstall /u01 /u02
]# chmod -R 775 /u01 /u02

/u01/software 는 Oracle database 19c 설치파일들을 압축해제해 넣을 디렉토리다.

.bashrc 파일 설정

설치를 하기 위해서 환경변수를 설정해 준다. 이것은 oracle 계정에 .bashrc 파일에 해주면 된다.

oracle 계정에 .bashrc 파일
ZSH
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# Oracle Settings
export TMP=/tmp
export TMPDIR=$TMP
 
export ORACLE_HOSTNAME=oradb1.systemv.local
export ORACLE_UNQNAME=o19c
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/19.3.0/dbhome_1
export ORA_INVENTORY=/u01/app/oraInventory
export ORACLE_SID=o19c
export PDB_NAME=pdb1
export DATA_DIR=/u02/oradata
 
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

설치

이제 설치를 진행 해준다.

먼저, 오라클 데이터베이스 19c 에 설치 프로그램을 압축 해제해 준다.

oracle 계정으로 설치 프로그램 압축해제
ZSH
1
2
3
4
5
6
]$ export SOFTWARE_DIR=/u01/software
]$ export DB_SOFTWARE=LINUX.X64_193000_db_home.zip
]$ # Unzip the media
]$ mkdir -p ${ORACLE_HOME}
]$ cd ${ORACLE_HOME}
]$ unzip -oq ${SOFTWARE_DIR}/${DB_SOFTWARE}

이제 Silent 설치를 위한 쉘 스크립트 파일을 작성해 준다.

oracle 설치를 위한 쉘 스크립트 작성
ZSH
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
]# vim ora_19c_install.sh
#!/bin/bash
  
source $HOME/.bashrc
 
export CV_ASSUME_DISTID=RHEL8.0
 
${ORACLE_HOME}/runInstaller \
-ignorePrereq -waitforcompletion -showProgress -silent \
-responseFile ${ORACLE_HOME}/install/response/db_install.rsp \
oracle.install.option=INSTALL_DB_SWONLY \
UNIX_GROUP_NAME=oinstall \
ORACLE_HOSTNAME=${ORACLE_HOSTNAME} \
UNIX_GROUP_NAME=oinstall \
INVENTORY_LOCATION=${ORA_INVENTORY} \
SELECTED_LANGUAGES=en \
ORACLE_HOME=${ORACLE_HOME} \
ORACLE_BASE=${ORACLE_BASE} \
oracle.install.db.InstallEdition=EE \
oracle.install.db.OSDBA_GROUP=dba \
oracle.install.db.OSBACKUPDBA_GROUP=dba \
oracle.install.db.OSDGDBA_GROUP=dba \
oracle.install.db.OSKMDBA_GROUP=dba \
oracle.install.db.OSRACDBA_GROUP=dba \
SECURITY_UPDATES_VIA_MYORACLESUPPORT=false \
DECLINE_SECURITY_UPDATES=true
]# chmod +x ora_19c_install.sh

“export CV_ASSUME_DISTID=RHEL8.0” 는 설치 오류를 피하기 위한 것이다. 이제 실행해준다.

oracle 설치 쉘 스크립트 실행
ZSH
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
]# ./ora_19c_install.sh
Launching Oracle Database Setup Wizard...
 
The response file for this session can be found at:
/u01/app/oracle/product/19.3.0/dbhome_1/install/response/db_2021-04-30_00-53-34AM.rsp
 
You can find the log of this install session at:
/tmp/InstallActions2021-04-30_00-53-34AM/installActions2021-04-30_00-53-34AM.log
 
 
As a root user, execute the following script(s):
        1. /u01/app/oraInventory/orainstRoot.sh
        2. /u01/app/oracle/product/19.3.0/dbhome_1/root.sh
 
Execute /u01/app/oraInventory/orainstRoot.sh on the following nodes:
[oradb1]
Execute /u01/app/oracle/product/19.3.0/dbhome_1/root.sh on the following nodes:
[oradb1]
 
 
Successfully Setup Software.
Moved the install session logs to:
/u01/app/oraInventory/logs/InstallActions2021-04-30_00-53-34AM

출력 결과 안내대로 실행해 준다.

oracle 설치 후 쉘 스크립트 실행
ZSH
1
2
3
4
5
6
7
8
9
]# /u01/app/oraInventory/orainstRoot.sh
Changing permissions of /u01/app/oraInventory.
Adding read,write permissions for group.
Removing read,write,execute permissions for world.
 
Changing groupname of /u01/app/oraInventory to oinstall.
The execution of the script is complete.
]# /u01/app/oracle/product/19.3.0/dbhome_1/root.sh
Check /u01/app/oracle/product/19.3.0/dbhome_1/install/root_oradb1.systemv.local_2021-04-30_00-59-09-117429191.log for the output of root script

멀티테넌트 데이터베이스 생성하기

오라클 12c 로 넘어오면서 CDB, PDB 라는 개념을 도입했는데 이것이 멀티테넌트 데이터베이스 개념이라 간략히 말할 수 있다. 먼저 데이터베이스를 설치하기 위해서는 오라클 리스너(Oracle Listener) 를 실행 시켜준다.

오라클 리스너 시작
ZSH
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
$ lsnrctl start
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 30-APR-2021 01:25:23
 
Copyright (c) 1991, 2019, Oracle.  All rights reserved.
 
Starting /u01/app/oracle/product/19.3.0/dbhome_1/bin/tnslsnr: please wait...
 
TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Log messages written to /u01/app/oracle/diag/tnslsnr/oradb1/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oradb1.systemv.local)(PORT=1521)))
 
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                30-APR-2021 01:25:27
Uptime                    0 days 0 hr. 0 min. 4 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /u01/app/oracle/diag/tnslsnr/oradb1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oradb1.systemv.local)(PORT=1521)))
The listener supports no services
The command completed successfully

이제 멀티테넌트 데이터베이스를 생성해 준다.

멀티테넌트 데이터베이스 생성
ZSH
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
$ dbca -silent -createDatabase \
> -templateName General_Purpose.dbc \
> -gdbname ${ORACLE_SID} -sid  ${ORACLE_SID} \
> -responseFile NO_VALUE \
> -characterSet AL32UTF8 \
> -sysPassword SysPassword1 \
> -systemPassword SysPassword1 \
> -createAsContainerDatabase true \
> -numberOfPDBs 1 \
> -pdbName ${PDB_NAME} \
> -pdbAdminPassword PdbPassword1 \
> -databaseType MULTIPURPOSE \
> -automaticMemoryManagement false \
> -totalMemory 4000 \
> -storageType FS \
> -datafileDestination "${DATA_DIR}" \
> -redoLogFileSize 50 \
> -emConfiguration NONE \
> -ignorePreReqs
Prepare for db operation
8% complete
Copying database files
31% complete
Creating and starting Oracle instance
32% complete
36% complete
40% complete
43% complete
46% complete
Completing Database Creation
51% complete
53% complete
54% complete
Creating Pluggable Databases
58% complete
77% complete
Executing Post Configuration Actions
100% complete
Database creation complete. For details check the logfiles at:
/u01/app/oracle/cfgtoollogs/dbca/o19c.
Database Information:
Global Database Name:o19c
System Identifier(SID):o19c
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/o19c/o19c.log" for further details.

이렇게하면 하나의 CDB, PDB 가 생성된다. CDB 의 이름은 O19C, PDB 의 이름은 pdb1 이다.

멀티테넌트 데이터베이스 디렉토리
ZSH
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
]$ pwd
/u02/oradata/O19C
[oracle@oradb1 O19C]$ ls -lh
total 1.9G
-rw-r-----. 1 oracle oinstall  18M Apr 30 02:01 control01.ctl
-rw-r-----. 1 oracle oinstall  18M Apr 30 02:01 control02.ctl
drwxr-x---. 2 oracle oinstall  104 Apr 30 01:56 pdb1
drwxr-x---. 2 oracle oinstall  111 Apr 30 01:41 pdbseed
-rw-r-----. 1 oracle oinstall  51M Apr 30 02:01 redo01.log
-rw-r-----. 1 oracle oinstall  51M Apr 30 01:56 redo02.log
-rw-r-----. 1 oracle oinstall  51M Apr 30 01:56 redo03.log
-rw-r-----. 1 oracle oinstall 511M Apr 30 02:01 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 901M Apr 30 02:01 system01.dbf
-rw-r-----. 1 oracle oinstall  33M Apr 30 01:45 temp01.dbf
-rw-r-----. 1 oracle oinstall 331M Apr 30 02:01 undotbs01.dbf
-rw-r-----. 1 oracle oinstall 5.1M Apr 30 01:56 users01.dbf

필요한 데이터 파일들이 CDB 디렉토리 아래에 모두 자동으로 생성 되었다. 수동으로 생성할 수도 있는데, 이것은 추후 논의 한다.

루트(root) 사용자로 oratab 설정을 변경해 준다. 이것은 오라클 데이터베이스를 자동 시작을 활성화 해준다.

오라클 데이터베이스 자동 시작 활성화
ZSH
1
]# sed -i 's/:N$/:Y/g' /etc/oratab

이제 SQL Shell 을 실행하고 Oracle Managed File 를 활성화, CDB 시작시 PDB도 자동시작 활성화를 해준다.

SQL Shell 실행 후 명령어 실행
ZSH
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
]$ sqlplus / as sysdba
 
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Apr 30 02:08:18 2021
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
 
SQL> ALTER SYSTEM SET DB_CREATE_FILE_DEST='/u02/oradata' SCOPE=BOTH;
 
System altered.
 
SQL> ALTER PLUGGABLE DATABASE pdb1 SAVE STATE;
 
Pluggable database altered.
 
SQL>

시작/중지.

오라클 데이터베이스 시작/중지는 다음과 같다.

오라클 데이터베이스 systemd 유닛 등록
ZSH
1
2
]$ /u01/app/oracle/product/19.3.0/dbhome_1/bin/dbstart /u01/app/oracle/product/19.3.0/dbhome_1 # 시작
]$ /u01/app/oracle/product/19.3.0/dbhome_1/bin/dbshut /u01/app/oracle/product/19.3.0/dbhome_1 # 중지

Oracle JDBC Memory Management

이 문서는 Oracle JDBC Memory Management 를 번역한 것 입니다. 일부 오역과 오타가 있을 수 있습니다.

Introduction

데이터베이스 애플리케이션들은 아주 많은 양의 메모리를 사용할 수 있다. 큰 스케일의 JDBC 애플리케이션들은 그들이 사용하는 아주 많은 메모리 때문에 성능상의 문제를 발생시킬 수 있다. 오라클 데이터베이스 10i, 11g JDBC 드라이버는 의도적으로 성능 향상을 위한 많은 메모리 사용과 트레이드 오프 관계다. Oracle Database 12c 드라이버는 메모리를 좀 더 절약하지만 여전히 아주 큰 애플리케이션은 메모리 문제를 일으킬 수 있다. 이 화이트 페이퍼는(White paper) 다양한 드라이버들이 메모리를 어떻게 사용하고 최고의 성능을 위해서 그들을 어떻게 튜닝해야 하는지에 대한 약간의 식견을 제공한다.

Oracle Database 12c 에서 드라이버들의 메모리 관리는 Oracle Database 12c 에서 소개된 VARCHAR 컬럼의 32K 제한을 지원하고, 메모리 사용을 최소화하면서 최고의 성능을 내도록 디자인 되었다. 12c 메모리 관리 체계는 약간의 오버헤드를 유발하지만, 감소된 메모리 공간은 일반적으로 10i 나 11g 보다 낫거나 동일한 성능을 제공한다. 대규모 데이터 애플리케이션도 12c 드라이버를 사용한다고 하더라도 많은 메모리를 사용할 수 있다. 만약 여러분의 애플리케이션이 수용할만한 성능을 낸다면 메모리 사용에 대해 고민할 이유가 없다. 만약 여러분의 애플리케이션이 바라던 성능이 나오지 않으면서 기대 이상의 많은 메모리를 사용한다면 이 문서를 읽어보길 바란다.

Where Does It All Go?

JDBC 드라이버는 많은 곳에 메모리를 사용하지만 가장 큰 문제는 쿼리 결과를 저장하기 위해 사용하는 버퍼(buffer) 다. 각 명령문은(Statement, PreparedStatement 와 CallableStatement 포함) 메모리에 쿼리 결과를 저장한다. 12c 에서 각 명령문은 쿼리 결과를 저장하기 위해 byte[] 버퍼세트을(buffer set) 가진다. 10i, 11g 에서 각 명령문은 두개의 버퍼를 가지는데 하나는 byte[] 이고 다른 하나는 char[] 이다. char[] 는 character 타입(CHAR, VARCHAR2, NCHAR, etc)의 모든 로우 데이터를(row data) 저장한다. 나머지 데이터 타입은 byte[] 에 저장된다. 

10i 와 11g 에서, 일반적으로 명령문이 실행될때에 SQL 스트링이 파싱되는데(구문 분석) 이때 버퍼가 할당 된다. 명령문은 (접속이) 닫힐때까지 이러한 두개의 버퍼를(char[], byte[]) 들고 있게 된다. SQL이 파싱될때 버퍼가 할당되면, 그 크기는(버퍼의 크기) 쿼리에 의해서 리턴되는 실제 로우 데이터(row data) 크기와 상관 없지만, 로우 데이터의 가능한 최대 크기와 관련이 있다. SQL 이 파싱된 후에, 모든 컬럼의(column) 타입을 알고 있고, 이 정보를 이용해 JDBC 드라이버는 각 컬럼을 저장하기 위해 필요한 최대 메모리 양을 계산할 수 있다. 드라이버는 각 페치(fetch) 할때 가지고 올 행수인(the number of rows) fetchSize 를 가진다. (역, 데이터를 가지고 오는 작업을 Fetch 라고 한다. Oracle JDBC 드라이버는 한번에 한 개의 로우를(레코드) 가지고 오는게 아니라 fetchSize 만큼 여러개의 로우를 한번에 가지고 올 수 있다.) 각 컬럼의 크기와 로우 개수와 함께, 드라이버는 단일 페치(single fetch)에서 리턴되어지는 데이터의 최대 크기를 정확하게 계산할 수 있게 되는데, 이것이 버퍼의 크기가 된다.

반면에, 12c 에서 버퍼는 드라이버가 서버로부터 결과 데이터를 읽을때 요청되고 할당 된다. 이것은 10i 나 11g 와 비교해 봤을때 약간의 추가적인 오버헤드 비용이 있지만 쿼리 결과를 저장하기 위해 필요로 하는 메모리 양이 최소화 된다. LONG, LONG RAW 와 같은 타입은 버퍼에 인라인(inline) 으로 저장하기에는 매우 클 수 있고 다루기 어렵다. 기본적으로, 만약 쿼리 결과가 LONG, LONG RAW 타입을 포함한다면 fetchSize는 1로 설정되고, 이것은 향후 명확해질 대부분의 메모리 문제를 해결한다. 

10i 와 11g 에서 Character 데이터는 char[] 버퍼에 저장된다. Java에서 chars 는 한 Character 당 2 byte 이다. (10i, 11g 에서) A VARCHAR2(10) 컬럼은 최대 10개의 Character 를 가질 것이며 Java 에서 chars 는 row 하나당 20byte 를 가질 것이다. A VARCHAR2(4000) 컬럼은 8k bytes/row 를 가질 것이다. 핵심은 실제 데이터 크기가 아니라 컬럼의 크기로 정해지고 있다는데 있다. A VARCHAR2(4000) 컬럼이 모두 NULL 을 가진다 하더라도(실제 데이터가 없다 하더라도) 여전히 8K bytes/row 의 버퍼 메모리가 할당 된다. 버퍼는 쿼리 결과를 보기전에 할당되기 때문에 드라이버는 반드시 아주 큰 쿼리 결과값을 예상해 충분히 큰 메모리를 할당해야 한다. VARCHAR2(4000) 으로 정의된 컬럼은 4000 Character 로 꽉꽉 채워질 수 있다. 버퍼는 반드시 4000 chars 를 가질수 있는, 실제 결과 데이터가 그렇게 크지 않다고 하더라도, 충분한 메모리를 할당해야 한다.

BFILE, BLOB 그리고 CLOB 값들은 로케이터(locator) 처럼 저장된다. 로케이터는 BFILE, BLOB 그리고 CLOB 컬럼에 각각 4K bytes 일 수 있는데, byte[] 는 적어도 4K bytes/row 를 가져야 한다. RAW 컬럼들은 4K Bytes 이상 가질 수 있다. 다른 타입들은 대체로 이 보다 적게 갖는다. 대충 다른 모든 타입은 22 bytes/colums/row 로 가정한다.

Oracle 데이터베이스의 executeQuery 사용 예제
PgSQL
1
2
SQL CREATE TABLE TAB (ID NUMBER(10), NAME VARCHAR2(40), DOB DATE)
ResultSet r = stmt.executeQuery("SELECT * FROM TAB");

드라이버가 executeQuery 메소드를 실행하면, 데이터베이스는 SQL 을 구분 분석, 즉 파스(parse) 한다. 데이터베이스는 결과가 세 개의 컬럼을 가질거라고 보고할 것이다: a NUMBER(10), a VARCHAR2(40) 그리고 a DATE. 첫번째 컬럼은 대략 22 bytes/row 가 필요하다. 두 번째 컬럼은 40 chars/row 가 필요하다. 세번째 컬럼은 대략 22 bytes/row 가 필요하다. 따라서 하나의 로우(row) 는 22 + (40*2) + 22 = 124 bytes/row 를 필요로하게 된다. (각 Character 들은 2 bytes 라는 걸 기억해라) 기본 fetchSize 가 10 로우일 경우에 드라이버는 10 개의 char[], 10 * 40 = 400 chars (800 bytes) 그리고 10 개의 byte[], 10 * (22+22) = 440 bytes 그래서 총 1240 bytes 를 할당할 것이다. (역, 그냥 한 로우 124 bytes/row * 10 = 1240 bytes 다.) 1240 bytes 는 메모리 문제를 야기하지 않는다. 하지만 쿼리 결과가 아주 클 경우에는 문제가 될 수 있다.

최악의 경우로 쿼리 결과가 255 개의 VARCHAR2(4000) 컬럼 리턴한다고 가정해 보자. 각 컬럼은 8k bytes/row 를 갖는다. 225 개의 컬럼이니까 2040K bytes 혹은 2MB/row 다. 만약 fetchSize 가 1000 개의 로우(row) 라면 드라이버는 2GB char[] 메모리를 할당하려고 할 것이다. 이건 좋지 않다.

12c JDBC 드라이버들은 오직 쿼리 메타데이터(metadata) 와 실제 쿼리 데이터를 저장하기 위한 메모리를 할당 한다. (10i, 11g 에서는) 각 컬럼별로 최대 가능 값을 저장하기 위한 메모리를 미리 할당하는 대신에, 12c 드라이버들은 오직 실제 컬럼 값들을 저장하기에 필요한 것만큼만 메모리를 할당 한다. 이것은 추가적인 오버헤드를 요구받지만 거의 모든 상황에서 메모리 사용량이 크게 줄어든다. 

12c 드라이버는 결과 쿼리값에 15 bytes 를 할당하지만 (15 bytes/value), 실제 데이터 값을 저장하기 위해 훨씬 많은 메모리를 필요로 한다. 만약 값이 null 이면 오직 15 byte 만 할당 된다. 만약 컬럼이 VARCHAR2(32000) 이고 실제 데이터가 32,000 characters 면, 15 + 64,000 bytes (2bytes/char) 크기의 메모리를 할당 한다. 만약 컬럼에 다음 데이터가 1 character 이면 15 + 2 bytes 가 할당 된다. 

이것은 10i 와 11g 접근방법을 근본적으로 벗어난 것이다. 만약 모든 값들이 최대 크기라면 12c 는 10i 와 11g 보다 13 bytes/value 를 더 사용한다. 이것은 매우 드문 케이스다. 더 일반적으로 많은 NULL 과 크기가 가변적인 VARCHAR 값들이 있다. 아주 드문 케이스를 제외하고 12c 드라이버는 10i 이나 11g 보다 메모리를 적게 사용할 것이다. 

12c 드라이버는 오직 byte 버퍼만 사용한다. char 버퍼는 없다. 버퍼는 전부 같은 길이다.(The buffers are all the same length) 그들은 캐시되고 재사용되어진다. 버퍼 캐시는(buffer cache) Java Memory Management Team 과 협력해 메모리 소비를 최소화하면서 재사용성을 최적화하도록 디자인 되었다. 12c 버퍼 크기가 10i와 11g의 최소 버퍼 크기보다 크다는 점에 유의할 필요가 있다. 아주 작은 쿼리 결과에서는 더 많은 메모리를 사용하겠지만 12c 와 10i, 11g 비교했을때 몇 kilobytes 밖에 차이가 나지 않는다.

Managing the buffer sizes

버퍼에 의해서 사용되어지는 많은 양의 메모리를 관리하기 위해 사용자가 할 수 있는 것들이 몇가지 있다.

  • 신중하고 주의해서 테이블 정의하기
  • 신중하고 주의해서 쿼리 짜기
  • 신중하고 주의해서 fetchSize 를 지정

VARCHAR2(4000) 과 VARCHAR2(20) 와 같은 컬럼은 10i, 11g 와 아주 큰 차이를 만들어 낸다. VARCHAR2(4000) 컬럼은 8K bytes/row 를 필요로한다. 실제 컬럼이 20 character 보다 더 많이 값을 가지고 있지 않게되면 VARCHAR2(4000) 에 대한 10i, 11g 에 의해서 할당된 대부분의 버퍼는 낭비하게 된다. (역, VARCHAR2(4000) 컬럼에 실제 데이터는 20 char 가 들어가 있는데 10i 와 11g 는 컬럼의 크기를 기반으로 최대 값을 할당하기 때문에 VARCHAR2(4000) 컬럼을 위한 버퍼는 낭비하게 된다.) 12c 드라이버에서는 이런 일이 발생하지 않는다. 

오직 몇개의 컬럼만 필요한데도 SELECT * 을 실행하면 버퍼 크기뿐만 아니라 성능상에 큰 영향을 준다. 이것은 로우 컨텐츠를 가지고 오고, 바꾸고, 네트워크를 통해서 전송하고 자바 표현식으로 다시 바꾸는데 많은 시간이 든다. 오직 몇개의 컬럼만 필요한데도 많은 컬럼을 리턴하면 10i 와 11g 드라이버는 불필요한 결과를 저장하기 위해서 아주 큰 버퍼를 할당하게 된다. 12c 드라이버는 실제 컬럼값에 대해서만 메모리 할당을 강제한다. 이것은 10i 와 11g 보다 작을 수 있지만 12c 드라이버는 NULL 에 대해서도 15 bytes/value 를 할당하기 때문에 여전히 낭비다.

메모리 사용을 제어하는 기본적인 툴은 fetchSize 다. 비록 2MB 는 아주 크지만, 대부분의 자바 환경에서 그 크기를 버퍼에 할당하는 것은 전혀 문제를 발생시키지 않는다. Oracle database 11 의 최악의 케이스 결과로 255개의 VARCHAR2(4000) 컬럼 조차도 fetchSize 가 1 이라면 대부분의 애플리케이션에서 문제가 되지 않는다. Oracle database 12 의 최악의 케이스 1000 개에 VARCHAR2(32000) 은 10i와 11g 드라이버에서 64MB/row 를 필요로 한다.(3200 * 2 = 64000, 1000 개의 컬럼이 있으니까 64000 * 1000 = 64,000,000 이다.) 비록 fetchSize 가 1이라 할지라도 많은 자바 환경에서 문제를 발생시킬 수 있다. 12c 드라이버는 오직 실제 데이터를 저장하기 위해 필요로하는 메모리만 할당 한다. 

메모리 사용 이슈를 다루는 첫 단계는 SQL 을 검토하는 것이다. 각 쿼리에 대해 로우(row) 당 사용될 대략적인 크기를 계산하고 fetchSize 를 확인해라. 로우당 크기가 메우 크다면 좀 더 적은 컬럼을 페치(fetch) 하는게 가능한지(역, SELECT 에서 가지고 올 컬럼을 지정해야 한다. SELECT * 은 무조건 피해야 한다.) 혹은 데이터 크기를 좀 더 타이트하게 제약하기 위해 스크마 수정이 가능한지를 살펴보라. 마지막으로 적당한 크기에 버퍼를 유지하기 위한 fetchSize 를 지정한다. Oracle 은 비록 어떤 케이스에서 큰 크기가 알맞다라고 하더라도 fechSize 를 100 보다 크지 않기를 권한다. 아주 많은 로우를 리턴하더라도 일부 쿼리에 대해 fetchSize 100은 부적절하게 아주 클 수 있다. 

Note: 오라클의 특볗한 메소드 OracleStatement.defineColumnType 은 지나치게 큰 크기로 정의된 컬럼의 크기를 줄이기 위해 OCI 드라이버와 함께 사용되어 질 수 있다. 크기 인자와 함께 호출되면, 그 크기가 해당 컬럼에 대해 스키마에 정의된 크기보다 우선 한다. 이것은 문제를 해결하기 위해 스키마를 마음대로 수정할 수 없는 경우에 해결할 수 있게 해준다. 여러분은 주어진 명령문에서 defineColumnType 을 전혀 호출하지 않거나 그 명령문에서 모든 컬럼에 defineColumnType을 호출해야만 한다. 스키마를 고치는게 가능한 최고의 방법이다. defineColumnType 메소드는 BLOB 및 CLOB 컬럼의 LOB 프리 페치 크기를 지정하는 것을 제외하고 12C Thin 드라이버에서 지원되지 않는다.

One statement does not make a problem

VARCHAR2(32000) 타입의 1000 컬럼이나 setFetchSize(100000)과 같은 극단적인 케이스를 제외하고 단일 명령문은 메모리 사용 이슈를 발생시키지 않는다. 실제로, 문제는 수백 혹은 수천만 명령문 객체가 있는 시스템에서 나타난다. 대규모 시스템에는 동시에 수백 개의 연결이(connection) 열려 있을 수 있다. 각각의 연결은 한 두개의 명령문을 동시에 열 수 있다. 이렇게 큰 시스템은 아주 많은 물리적 메모리를 가진 머신에서 운영된다. 따라서 수백 개의 명령문을 오픈하는 대규모 시스템을 가정하면 심각한 메모리 문제는 발생하지 않을 것 같다. 물론, 드라이버도 많은 메모리를 사용할 수 있지만 메모리는 원래 그렇게 사용된다. 실제로 대규모 시스템도 메모리 문제를 피할 수 있다.

대규모 시스템은 같은 SQL 을 여러번 실행하는 경향이 있다. 성능상의 이유로 여러번 실행할때 마다 각각의 SQL에 대해서 처음부터 다시 만드는 것보다 PreparedStatement 를 재사용하는 것이 좋다. 그래서 대규모 시스템에는 각각 구별되는 SQL 에 대해서 하나 이상의 많은 PreparedStatement 를 가질 수 있다. 대부분의 대규모 시스템들은 Weblogic Server 같이 모듈러 프레임워크를 사용한다. 프레임워크안에 독립 컴포넌트들은 그들이 필요로 하는 PreparedStatement 를 생성한다. 이것은 PreparedStatement를 유지해야 할 필요성과 충돌한다. (역, 프레임워크 안에서 독립 컴포넌트들이 고유의 PreparedStatement 를 생성하기 때문에 재상용을 위해 준비된 Oracle JDBC 의 PreparedStatement 를 사용하지 않는다는 뜻이다.) 이 요구 사항을 해결하기 위해 프레임 워크는 명령문 캐싱을 제공한다. 명령문 캐싱을 가지고 있으면 각각의 연결이 메모리에 수백 혹은 그 이상의 PreparedStatement 를 가지는 것이 쉽다. 이것은 수백, 수천개의 커넥션이 생성되면 실제로 메모리 문제가 발생할 가능성이 있다. 

Oracle JDBC 드라이버는 드라이버에 내장된 명령문 캐시를, 묵시적 명령문 캐시(Implicit Statement Cache), 통해서 이 문제를 해결한다. (명시적 명령문 캐시도 있지만 여기서 다루지 않는다.) 묵시적 명령문 캐시는 투명하다. 사용자는 단지 새로운 객체를 생성하는 것처럼 PrepareStatement 를 호출한다. 만약 드라이버가 prepare 호출이 캐시에 있다면 새로운 객체를 캐시로부터 사용자에게 리턴한다. 하지만 없으면 새로운 객체를 생성한다. 문법적으로 사용자 코드는 새로운 객체와 재사용 객체를 구분할 수 없다. 성능적인 측면에서 새로운 객체를 생성하는 것보다 캐시에서 명령문을 리턴하는 것이 훨씬 빠르다. 캐시된 명령문의 첫 실행은 드라이버와 서버가 이전 실행으로부터 아주 많은 상태들을 재사용할 수 있음으로 해서 훨씬 빠르다. 

묵시적 명령문 캐시는 Oracle JDBC PreparedStatement 의 내부 구조를 알고 있다. 이것은 최적의 성능을 위한 구조로 관리를 할 수 있음을 뜻 한다. 특히, 이것은 char[] 와 byte[] 버퍼를 관리할 수 있다. Oracle 은 실제 애플리케이션에서 서로 다른 버퍼 관리 체계가 어떻게 동작해야 하는지 좀 더 잘 인식하도록 개발됨에 따라 드라이버 버전 마다 다른 방법으로 버퍼를 관리한다. 10i 와 11g JDBC 드라이버는 PreparedSatement 가 묵시적 명령문 캐시에서 리턴될때에만 char[] 와 byte[] 버퍼를 관리 할 수 있다. 만약 PreparedSatement 가 닫히지 않으면, Oracle JDBC 메모리 관리 드라어버는 명령문이 즉시 재사용되지 않을지를 알 방법이 없으며, 그래서 버퍼 사용 관리를 위해 아무것도 할 수 없다. 12c 드라이버는 좀 더 다이나믹한 버퍼 메커니즘을 가지고 있고 ResultSet 이 닫힐때 캐시에서 버퍼를 리턴한다. Oracle 은 여전히 묵시적 명령문 캐시 사용을 권장하지만, 12c 드라이버는 Weblogic Server 와 같은 다른 명령문 캐쉬를 사용할때에도 과도한 메모리 소비를 하지 않는다.

Statement Batching and Memory Use

row 데이터 버퍼는 Oracle JDBC 드라이버가 생성할 수 있는 유일하게 큰 버퍼가 아니다. Oracle JDBC 드라이버는 데이터베이스에 PreparedStatement 파라메터를 전달하기 위해 큰 버퍼를 생성할 수 있다. 애플리케이션은 일반적으로 한번에 적은 양의 데이터를 쓰고 그들의 쓰는것보다 더 많은 양의 데이터를 읽는다. 따라서 파라메터 데이터 버퍼는 row 데이터 버퍼보다 훨씬 작은 경향이 있다. 하지만 잘못된 명령문 배치를 사용하면 드라이버가 아주 큰 버퍼를 생성하게 된다.

애플리케이션이 파라메터 값을 지정하기 위해 PreparedStatement setXXX 를 호출할때, 드라이버는 값을 저장한다. 이것은 적은 메모리를 갖는다; 단지 String 과 같은 Objejct 타입, long 과 double 의 8byte, 다른 모든 것은 4 bytes 와 배열을 레퍼런스 한다. PreparedStatment 실행될때, 드라이버는 Java 타입이 아닌 SQL 타입으로 데이터베이스에 값들을 전달해야 한다. 10i, 11g 드라이버는 byte[] 와 char[] 버퍼를 생성한다. 파라메터 데이터는 버퍼에 저장할 SQL 표현으로 변환 된다. 그리고 나서 드라이버는 네트워크를 통해서 바이트를 전송한다. 드라이버는 버퍼를 할당하기 전에 실제 데이터 크기를 가지기 때문에, 최소 크기 버퍼를 생성할 수 있다. 만약 새로운 데이터 값들이 더 큰 byte[] 나 char[] 버퍼를 필요로한다면, 더 큰 버퍼를 할당 한다. 적당한 양의 메모리를 가지면, 단일 명령문 실행중에 Out of Memory 가 나지 않는다. 하지만 명령문 배칭은 다르다.

명령문 배칭은 하나의 연산으로(operation) 단일 DML 명령문을 여러번 실행한다. 이것을 실행하기 위해서 드라이버는 한번에 모든 DML 실행을 위해 모든 파라메터 값들을 전송해야만 한다. 이것은 드라이버가 모든 파라메터 데이터를 SQL 표현식으로 변환하고 버퍼에 저장해야만 한다. 배치에서 실행 수는, 배치 크기(batch size), 쿼리에 대한 fetch 크기와 유사하다. 단일 명령문 실행에 대한 매개 변수 변환이 메모리 문제를 일이키지는 않지만, 대규모 배치 크기라면 문제가 될 수 있다. 

실제로, 이것은 흔하지 않은 문제며 적합하지 않은 큰 배치 크기(수십만 개정도) 일 때만 나타난다. 매번 수백개의 정도 로우의 executeBatch 를 호출하는 것으로 문제를 해결해야 한다.

12c 드라이버는 아주 큰 배치 크기를 갖는 경우에 배치를 분리시킴으로서 이 문제를 해결한다. 이것은 배치에서 수백만개의 로우를 Out of Memory 없이 다룰 수 있게 해준다. 대신 한번에 모든 로우를 보내기 보다는 모든 로우를 보내기 위해서 여러번 왕복을 해야 할 것이다.(역, 배치를 나눴으니까..) Oracle 은 여전히 아주 큰 배치 크기일 경우에 좀 더 적은 수백개의 로우로 매번 executeBatch 를 호출하도록 권장한다.

Version Specific Memory Management

이번 섹션에서는 다양한 버전의 Oracle JDBC 드라이버들이 어떻게 버퍼 메모리를 다루며 최고의 성능을 내기위해서 사용자가 어떻게 드라이버를 튜닝할 수 있는지에 대해서 다룬다.

Note: 세부적인 메모리 관리를 논의할때에 10i와 11g 드라이버 버퍼가 구체적으로 char[] 와 byte[] 를 가진다는 것을 무시하는 것이 편리하다. 이번 섹션에서 기억해야할 것은 명령문에 대해서 char[] 와 byte[] 버퍼가 그냥 일반적으로 “버퍼” 라고 생각하는 것이다.

비록 Java 메모리 관리가 아주 훌륭하지만, 아주 큰 버퍼 할당은 비용이 많이 든다. 이것은 실제 메모리 할당 비용이 아니다. 그것은 매우 빠르다. 문제는 모든 버퍼를 Zero filled 를 요구하는 Java 언어다. 단순하게 큰 버퍼를 할당하는 것이 아닌 반드시 Zero filled 여야 한다. Zero filling 은 모든 할당된 버퍼의 바이트에 대해 터칭(touching)을 요구한다. 다중레벨 데이터 캐시를 가지는 현대의 프로세서들은 작은 버퍼를 가진다. 아주 큰 버퍼의 Zero filling 은 그 크기가 프로세서 데이터 캐시를 초과하고 Zero filling 작업은 메모리 스피드로 실행 되는데, 이는 대체로 프로세스 스피드보다 느리다. 이 문제에 대한 성능 테스트는 드라이버에서 버퍼할당이 아주 큰 성능상 하락을 반복적으로 보여줬다. 이로 인해 버퍼 할당 비용과 재사용을 위해 버퍼를 절약하는 데 필요한 메모리 공간 간의 균형을 맞추는 데 어려움을 겪는다.

Oracle Database Release 10g Oracle JDBC Drivers

초기 10g 드라이버는 메모리 관리에 대한 접근이 순진했다. 그들은 최고의 성능을 위해 메모리를 관리한다. PreparedStatement 가 처음 실행될때, 필요한 byte[] 와 char[] 버퍼가 할당 된다. 그것뿐이다. 버퍼는 PreparedStatement 가 해제 될때에만 해제 된다. 묵시적 명령문 캐시는 버퍼를 관리하기 위해 아무것도 하지 않는다. 묵시적 명령문 캐시에서 모든 PreparedStatement 는 할당된 byte[] 와 char[] 버퍼를 즉시 재사용되도록 유지한다. 따라서 이 드라이버 버전에서 메모리 관리는 setFetchSize 사용, 신중하고 주의깊은 스키마 설계, 신중하고 주의깊은 SQL 쿼리를 코딩하는 것 뿐이다. 초기 10g 드라이버는 충분히 빠르지만, OutOfMemoryExceptions 를 포함한 메모리 관리 이슈를 가질 수 있다.

Oracle Database Release 10.2.0.4 Oracle JDBC Drivers

10.2.0.4.0 드라이버는 초기 10g 드라이버에서 나타난 메모리 관리 이슈 문제를 해결하기 위해서 시작시에 커넥션 속성이 추가 됐다. 이 커넥션 속성은 모 아니면 도다. 만약 이 속성이 지정되면, PreparedStatement 를 묵시적 명령문 캐시로 반환하면 버퍼가 해제된다. 버퍼는 명령문이 캐시로부터 반환될때 재할당 된다. 이런 단순한 접근은 극적으로 메모리 사용을 줄여주지만 상당한 성능 비용을 필요로 한다. 앞에서 서술했듯이, 버퍼 할당은 비싸다.

connection 속성은 다음과 같다.

oracle.jdbc.freeMemoryOnEnterImplicitCache

이 값은 “true” 나 “false” 를 가지는 boolean 이다. 만약 “true” 라면 버퍼는 PreparedStatement 가 묵시적 명령문 캐시로 반환될때 해제 된다. 만약 “false” 라면, 기본 값이다, 버퍼는 초기 10g 드라이버처럼 유지 된다. 이 속성은 -D 를 통해 시스템 속성으로 지정하거나 getConnection 메소드에 커넥션 속성으로 지정할 수 있다. 주의할 것은 freeMemeoryOnEnterImplicitCache 세팅은 파라메터 값 버퍼 해제를 발생시키지 않으며 오직 로우 데이터(row data) 버퍼 해제만 발생시킨다.

Oracle Database Release 11.1.0.6.0 Oracle JDBC Drivers

JDBC 개발 팀은 10.2.0.4.0 에 모 아니면 도식의 접근법이 이상적이 않다는 것을 깨달았다. 11.1.0.6.0 드라이버는 메모리 관리에 좀 더 세련된 접근법을 가진다. 그건 사용하지 않는 메모리의 최소화, 버퍼 할당 비용의 최소화 라는 두가지 목표가 있다. 이 드라이버는 각 커넥션에 대해 내부 버퍼 캐시를 소개했다. PreparedStatement 가 묵시적 명령문 캐시로 반환되면 버퍼 캐시에 캐시된다. 

서론에 언급 된 바와 같이, 버퍼의 크기는 0에서 수십 또는 수백 메가 바이트까지 광범위하게 변할 수있다. 11.1.0.6.0 버퍼 캐시는 아주 단순하다. 모든 캐시된 버퍼들은 모두 같은 크기다. 버퍼는 묵시적 명령문 캐시에서 모든 PreparedStatement 에 대해서 사용되어질 수 있기 때문에 그 크기는 아주 큰 요청을 가지는 PreparedStatment 만큼의 충분히 큰 크기를 가진다. 만약 한번에 하나의 명령문만 사용중인 경우 하나의 버퍼만 있으며 해당 버퍼는 모든 PreparedStatement 에서 사용된다. 일부 또는 대부분의 명령문의 경우 버퍼가 아주 클 수 있지만, 캐시에 있는 적어도 하나의 명령문에 대해서는 적절한 크기일 수 있다. PreparedStatement 재사용 패턴이 너무 편중되지 않으면, 작은 버퍼를 유지하고 필요에 따라 큰 버퍼를 재 할당하는 것보다 큰 버퍼를 유지하고 지속적으로 재사용하는 것이 더 효과적이다. 여러 명령문이 한번에 열리거나 아주 큰 버퍼를 필요로하는 PreparedStatement 가 하나 있는 경우 잠재적으로 메모리 문제가 있다.

10MB 버퍼를 필요로하는 하나의 PreparedStatement 과 나머지는 아주 작은 버퍼를 사용하는 애플리케이션이 있다고 가정해 보자. 한번에 커넥션당 하나의 명령문만 사용하고 아주 큰 PreparedStatement 가 충분히 자주 사용되면 문제는 없다. 각 명령문이 사용할때마다 단일 10MB 버퍼를 할당하며 PreparedStatement 가 묵시적 명령문 캐시로 반환될때 버퍼 캐시로 캐시된다. 단일 10MB 버퍼는 한번 할당되며 다양한 PreparedStatement 에 의해서 지속적으로 재사용 된다. 이제 한번에 두개의 PreparedStatement 가 열리면 무슨 일이 생기는지 생각해보자. 둘다 버퍼를 가져야 한다. 모든 PreparedStatement 는 버퍼에 할당되어야 함으로 모든 버퍼는 반드시 같은 크기, 최대 크기를 똑같이 가져야만 한다. 두개의 PreparedStatement 가 한번에 열릴때 버퍼는 각각 10MB 여야 한다. 두번째 PreparedStatement 가 열리는 중에, 그것이 아주 작은 용량의 버퍼를 필요로한다고 하더라도 10MB 버퍼가 할당된다. 만약 세번째 명령문이 열리면, 세번째 10MB 버퍼가 할당된다. 수백개의 커넥션과 한번에 수백개의 PreparedStatement 가 열리는 대규모 시스템에서 모든 PreparedStatement 에 대한 아주 큰 버퍼 크기는 메모리를 넘치게할 가능성이 있다. 뒤돌아보니 (문제가) 명확하지만, 개발팀은 이것이 얼마나 큰 문제인지 판단하지 않았고 문제가 있음을 내부 테스트에서 인지하지 못했다. 이 이슈는 적절한 스키마 디자인, SQL 코딩 그리고 fetchSize 선택을 통해서 최소화 될 수 있다.

주의해야할 것은, 11.1.0.6.0 드라이버는 버퍼처럼 freeMemoryOnEnterImplicitCache 를 지원하지 않으며 그것을 캐시에 넣을때 PreparedStatement 에 의해서 항상 해제된다. 해제된 버퍼들은 내부 버퍼 캐시에 저장된다.

Oracle Database Release 11.1.0.7.0 Oracle JDBC Drivers

11.1.0.7.0 드라이버는 아주 큰 버퍼 문제를 해결하기 위해서 커넥션 속성을 소개 한다. 이 속성은 버퍼 캐시에 저장되어질 최대 버퍼 크기와 연관된다. 모든 큰 버퍼들은 PreparedStatement 가 묵시적 명령어 캐시로 집어넣을때 해제되고 PreparedStatement 가 캐시로부터 데이터를 받을때에 재할당 된다. 대부분의 PreparedStatement 가 예를들어 100KB 미만의 적당한 크기의 버퍼를 필요로하지만 일부는 훨씬 더 큰 버퍼를 필요로하는 경우, 커넥션 속성을 110KB 로 설정하면, 자주 최대 크기 버퍼 할당에 부담을주지 않고 자주 사용되는 작은 버퍼를 재사용 할 수 있다. 이 속성을 설정하면 성능 향상되고, OutOfMemoryException 을 방지할 수 있다.

이 커넥션 설정은

oracle.jdbc.maxCachedBufferSize

이 값은 “100000” 처럼 정수 문자열(int string) 이다. 기본값으로 Integer.MAX_VALUE 다. 이것은 내부 버퍼 캐시에 저장할 수 있는 버퍼에 대한 최대 크기다. 하나의 크기로 char[] 와 byte[] 버퍼를 모두 사용한다. 그 크기는 char[] 버퍼의 경우 문자로(chars) byte[] 버퍼의 경우 byte로 표시된다. 이것은 미리 정의된 크기가 아니라 최대 버퍼 크기다. 만약 maxCachedBufferSize 가 100KB 로 설정되었지만 최대 버퍼 크기가 100KB 보다 적은 50KB 라면, 버퍼 캐시의 버퍼들은 50KB 가 될 것이다. maxCachedBufferSize 값의 변경은 드라이버의 내부 버퍼 캐시에서 char[] 나 byte[] 버퍼를 포함하거나 제외할때만 성능이 달라진다. 아주 큰 변화, megabytes 조차, 차이가 없다. 마찬가지로 하나를 변경하면 PreparedStatement 의 버퍼를 포함하거나 제외하하는 일이 발생할때에 차이가 난다. 이 속성은 -D 를 통해 시스템 속성처럼 지정하거나 getConnection 을 통해서 커넥션 속성처럼 지정할 수 있다.

만약 여러분이 maxCachedBufferSize 이 설정이 필요하다면, 큰 버퍼들이 필요로하는 SQL 쿼리에 대한 버퍼 크기를 측정하는 것으로부터 시작해야 한다. 이 과정에서 이러한 쿼리에 대한 fetch 크기를 튜닝하면 여러분이 바라는 성능을 얻을 수 있다. 실행 빈도와 버퍼 크기를 고려해 대부분의 명령문이 캐시된 버퍼를 사용할 수 있도록 크기를 정해야 하지만, Java 런타임이 새로운 버퍼를 할당해야하는 빈도를 최소화하기 위해 필요한 버퍼 수를 지원할 수 있을 정도의 충분히 작은 크기여야 한다.

어떤 애플리케이션들은 쓰레드 개수에 비해 많은 idle 커넥션을 가진다. 애플리케이션은 많은 데이터베이스 중에 하나에 접속을 필요로 하지만 한번에 하나만 접속한다. 만약 각 데이터베이스에 스레드당 대충 하나의 커넥션이 있고 스레드보다 더 많은 데이터베이스가 있는 경우에 스레드보다 더 많은 idle 커넥션을 가진다. 기본적으로 버퍼 캐시는 커넥션당 가지기 때문에 idle 커넥션으로 인해서 사용하지 않는 버퍼를 버퍼에 할당한다. 이것은 필요 이상으로 큰 메모리 공간이 필요함을 의미 한다. 비교적 극단적인 상황이지만, 발생할 수 있다.

이 경우에 커넥션 속성을 지정함으로써 해결할 수 있다.

oracle.jdbc.useThreadLocalBufferCache

이 속성의 값은 Boolean 문자열, “true” 나 “false” 다. 기본값은 “false” 다. 이 속성이 “true” 로 지정되면, 버퍼 캐시는 커넥션이 아닌 ThreadLoacl 에 직접 저장된다. 만일 커넥션보다 스레드 수가 적으면 사용되는 메모리 양이 줄어들 것이다. 이 속성은 -D 를 통해서 시스템 속성이나 getConnection 을 통해 커넥션 속성으로 지정할 수 있다. 

useThreadLocalBufferCache = “true” 를 사용하는 모든 커넥션은 같은 정적 ThreadLocal 필드를 공유하며 따라서 같은 버퍼 캐시 세트를 공유한다. 만얄 useThreadLocalBufferCache = “true” 설정되어 있다면, 사용하는 모든 커넥션은 같은 maxCacheBufferSize 를 가진다. 스레드가 처음 하나의 커넥션을 사용한 다음 다른 커넥션을 사용하는 경우 두 커넥션는 사용 된 버퍼의 크기와 수에 따라 서로의 성능에 간접적인 영향을 미친다. 보통 ThreadLocal 버퍼 캐시를 사용하는 모든 커넥션은 같은 애플리케이션의 일부일 것임으로 다른 커넥션을 사용하는 경우의 서로의 성능에 간접적인 영향을 미치는 일은 없다. 만약 한 쓰레드가 명령문을 생성하고 다른 쓰레드가 명령문을 닫는다면, 버퍼는 한개의 ThreadLocal 버퍼 캐시에서 다른 ThreadLocal 버퍼 캐시로 마이그레이션할 것이고 재사용되지 않을 것이다. 이것은 사례로 권장하지 않는다. 만약 모든 명령문이 하나의 ThreadLocal 에서 생성되었고 다른 쓰레드가 닫는다면 전혀 버퍼를 재사용하지 않게 된다. 다시말해 이것은 권장하지 않는다, 만일 여러분의 애플리케이션이 이러한 경우에 속한다면 절대로 useThreadLocalBufferCache 를 true 로 세팅하지 말라. 이것은 어떤 커넥션은 ThreadLocal 버퍼 캐시를 사용하고 어떤 것은 커넥션 버퍼 캐시당 기본값을 사용할 가능성이 있다.

Oracle Database Release 11.2 Oracle JDBC Drivers

11.2 드라이버는 11.1.0.7.0 보다 더 정교한 버퍼 캐시를 가진다. 이 버퍼 캐시는 다중버킷을(multiple bucket) 가진다. 버킷에 모든 버퍼들은 모두 같은 크기이며 이 크기는 미리 정해진다. 맨 처음 PreparedStatement 가 실행되면, 드라이버는 결과를 보관할 가장 작은 크기의 버퍼를(역, 최적크기의 버퍼) 갖는 버킷으로부터 버퍼를 얻는다. 만약 버킷에 버퍼가 없다면, 버킷에 미리 정해진 알맞은 크기의 새로운 버퍼를 할당한다. PreparedStatement 가 닫히면, 버퍼는 알맞은 버킷으로 반환된다. 버퍼들은 다양한 크기에 요구사항에 사용되므로 버퍼들은 보통 필요한 최소 크기보다 약간 크다. 불일치(역, 버퍼 크기의 불일치) 발생은 제한적이며 실제로 임펙트를 주지는 않는다.

11.2 드라이버는 항상 11.1 이나 10.2.0.4.0 드라이버보다 메모리를 같게 혹은 적게 사용한다. 어찌보면 11.2 드라이버가 충분한 성능을 내는데 비해 너무 적은 힙 크기로 실행되는 것이 말이 안되기도 한다. 더 적은 메모리로 실행된다는 이유만으로 그것이 효율적이라는 뜻은 아니다. 11.2 드라이버에서 대규모의 성능향상을 위해 -Xms 값을 크게 설정하는 것은 드문일이 아니다. 아래에 자바 힙 제어하기 섹션을 보라.

11.2 드라이버는 maxCachedBufferSize 를 지원하지만 별로 중요하지 않다. 11.1 에서 정확한 maxCachedBufferSize 설정은 탁월한 성능과 OutOfMemoryException 의 차이를 만들어 낸다. 11.2 드라이버에 maxCachedBufferSize 설정은 가끔 대규모 명령문 캐시와 광범위하게 나뉘는 버퍼 크기의 요구사항을 갖는 대규모 시스템에서 성능을 개선 시킬 수 있다. 11.2 에서 maxCachedBufferSize 는 최대 버퍼 크기의 log2 로 해석된다. 예를들어, maxCahcedBufferSize 가 20 으로 설정했다면 캐시 된 최대 크기 버퍼는 2^20 = 1048576 이 된다. 이전 버전과 호환성을 위해, 30 보다 큰 크기의 값은 log2 크기가 아닌 실제 크기처럼 해석되지만, 2의 거듭 제곱을 사용하는 것이 좋다.

maxCachedBufferSize 값의 합리적인 설정은 시스템에 임팩트를 주지 않는다. 만약 여러분이 maxCachedBufferSize 를 설정해야 한다면, 18부터 시작해라. 만약 16보다 적은 값을 설정 해야한다면 아마도 더 많은 메모리가 필요하다.

11.2 드라이버는 파라메터 데이터 버퍼를 위해서 같은 크기의 버퍼 캐시와 캐싱 스키마를 사용 한다. PreparedStatement 가 묵시적 명령문 캐시에 있으면 파라메터 데이터 버퍼들은 버퍼 캐시에 캐시 된다. PreparedStatement 가 맨 처음 실행되거나 묵시적 명령문 캐시에서 검색된 후 처음으로 실행될 때 버퍼 캐시로부터 파라메터 데이터 버퍼를 얻는다. 전형적으로 파라메터 데이터 버퍼들은 로우(row) 데이터 버퍼들보다 훨씬 작지만, 이런 버퍼들은 아주 큰 배치 크기를 가질 수 있을만큼 클 수 있다. 또, 11.2 드라이버는 Bfile, Blob, Clob 연산을 위한 char[] 버퍼와 대규모 byte[] 에 대해 다른 버퍼 캐시를 사용한다. 

11.2 드라이버도 useThreadLocalBufferCache 를 매우 잘 지원한다. 이것에 대한 기능과 이것을 언제/어떻게 사용할지 대한 권장사항은 11.1.0.7.0 과 동일하다.

또, 11.2 드라이버는 묵시적 명령문 캐시를 활성화 하기 위해서 새로운 속성을 추가한다.

oracle.jdbc.implicitStatementCacheSize

이 속성의 값은 “100” 과 같은 정수형 문자열이다. 이것은 명령문 캐시의 초기(initial) 크기다. 속성을 양수로 지정하면 묵시적 명령문 캐시가 활성화 된다. 기본값은 0 이다. 이 속성은 “-D” 를 통해서 시스템 속성이나 getConnection 을 통한 커넥션 속성으로 지정할 수 있다. OracleConnection.setStatementCacheSize 나 OracleConnection.setImplicitCachingEnabled 를 호출하면 implicitiStateCacheSize 값이 무시된다. 이 속성은 코드로 활성화하는 것이 비실용적인 경우 묵시적 명령문 캐시 활성화를 더 쉽게해 준다.

Oracle Database Release 12.1.0.1.0 Oracle JDBC Drivers

앞에서 설명했듯이, 12.1.0.1.0 드라이버는 다른 메모리 관리 체계를 사용한다. 12c 드라이버는 정의된 컬럼 크기에 민감하지 않다. VARCHAR2(32000) 컬럼은 같은 데이터에 대해 VARCHAR2(1) 보다 메모리를 더 사용하지 않는다. 10i 나 11g 드라이버 처럼, 12c 드라이버는 (쿼리 후 받은 데이터) 결과에서 fetchSize 와 실제 데이터 크기를 더한 컬럼의 수에 민감하다. Oracle 은 스키마 디자인이 다른 시스템 부분에 영향을 줄 수 있기 때문에 신중한 설계를 지속적으로 권장한다. 신중한 쿼리 디자인, 최소한의 컬럼과 로우(row) 세트 검색과 신중한 fetchSize 선택은 그 어떤것보다 중요하다.

12c 드라이버는 버퍼나 버퍼 캐시 크기를 제어하기 위한 옵션을 가지지 않는다. 

어떤 애플리케이션은 쓰레드 수에 비해 아주 많은 idle 커넥션을 가진다. 애플리케이션은 아주 많은 데이터베이스 중에 하나와 연결해야 하지만 오직 한번에 하나만 연결해야 한다. 만약 대략 각 데이터베이스를 위해 쓰레드당 하나의 연결이 있다고 한다면, 쓰레드보다 더 많은 idle 커넥션이 있게 된다. 기본적으로 버퍼 캐시는 커넥션당 이기 때문에 idle 커넥션은 버퍼 캐시에서 비사용 버퍼가 되는 결과가 된다. 이것은 필요 이상으로 많은 메모리가 필요함을 뜻한다. 이것은 비교적 드문 상황이지만, 알려지지 않은 것은 아니다. 

이 경우 커넥션 속성 설정을 통해 해결할 수 있다.

oracle.jdbc.useThreadLocalBufferCache

이 속성의 값은 Boolean 스트링, “true” 이거나 “false” 이다. 기본값은 false 다. 이 속성이 true 일때, 버퍼 캐시는 직접 커넥션을 저장하지 않고 ThreadLocal 에 저장 된다. 만약 커녁션보다 적은 쓰레드만 있다면, 이것은 많은 양의 사용할 메모리를 줄여준다. 이 속성은 -D 를 통한 시스템 속성이나 getConnection 을 통한 연결 속성으로 설정할 수 있다.

useThreadLocalBufferCache = “true” 를 사용하는 모든 커넥션은 같은 정적 ThreadLocal 필드를 공유하며 따라서 같은 버퍼 캐시 세트를 공유한다. 만얄 useThreadLocalBufferCache = “true” 설정되어 있다면, 사용하는 모든 커넥션은 같은 maxCacheBufferSize 를 가진다. 스레드가 처음 하나의 커넥션을 사용한 다음 다른 커넥션을 사용하는 경우 두 커넥션는 사용 된 버퍼의 크기와 수에 따라 서로의 성능에 간접적인 영향을 미친다. 보통 ThreadLocal 버퍼 캐시를 사용하는 모든 커넥션은 같은 애플리케이션의 일부일 것임으로 다른 커넥션을 사용하는 경우의 서로의 성능에 간접적인 영향을 미치는 일은 없다. 만약 한 쓰레드가 명령문을 생성하고 다른 쓰레드가 명령문을 닫는다면, 버퍼는 한개의 ThreadLocal 버퍼 캐시에서 다른 ThreadLocal 버퍼 캐시로 마이그레이션할 것이고 재사용되지 않을 것이다. 이것은 사례로 권장하지 않는다. 만약 모든 명령문이 하나의 ThreadLocal 에서 생성되었고 다른 쓰레드가 닫는다면 전혀 버퍼를 재사용하지 않게 된다. 다시말해 이것은 권장하지 않는다, 만일 여러분의 애플리케이션이 이러한 경우에 속한다면 절대로 useThreadLocalBufferCache 를 true 로 세팅하지 말라. 이것은 어떤 커넥션은 ThreadLocal 버퍼 캐시를 사용하고 어떤 것은 커넥션 버퍼 캐시당 기본값을 사용할 가능성이 있다. 

12c 드라이버는 좀 더 동적인 메모리 관리를 하기 때문에 LONG 및 LONG RAW 컬럼을 메모리로 직접 가져오는 것이 합리적일때가 있다. 만약 여러분의 애플리케이션이 LONG 이나 LONG RAW 에 대한 전체 값을 저장할 충분한 메모리를 가지고 있다면 oracle.jdbc.useFetchSizeWithLongColumn 속성을 설정할 수 있다. 기본적으로, 쿼리가 LONG이나 LONG RAW 컬럼으로 반환하는 경우 드라이버는 fetchSize 를 1로 설정하고 요청시에만 네트워크를 통해서 LONG 나 LONG RAW 값을 읽는다. 만약 이 설정을 설정하면 드라이버는 정의된 fetchSize 를 사용할 것이며 VARCHAR 나 RAW 처럼 전체 LONG 이나 LONG RAW 를 메모리에서 읽는다. LONG 과 LONG RAW 값이 아주 클 수 있기 때문에, 그 값의 크기를 정확하게 메모리에 맞는지를 알지 못한다면 이 설정을 지정해서는 안된다. 만약 이값이 적절하지 않다면 드라이버는 OutOfMemoryException 을 얻을 수 있다. 만일 그것을 저장할 힙(heap) 을 가지고 있다면 메모리에서 읽을 수 있는 가장 큰 단일 값은 2GB 이다.

연결 속성은 

oracle.jdbc.useFetchSizeWithLongColumns

이 값은 Boolean 스트링으로 “true”나 “false” 다. 만약 “true” 이면 LONG 과 LONG RAW 값들은 VARCHAR 과 RAW 값들처럼 메모리에서 읽는다. 기본값 “false” 면 쿼리에서 LONG 이나 LONG RAW 컬럼을 포함해 fetchSize 1 로 강제되고 요청시에만 네트워크를 통해서 LONG 과 LONG RAW 값들을 읽게 된다. 이 속성 역시 “-D” 를 통해서 시스템 속성으로 지정하거나 getConnection 메소드를 통한 연결 속성으로 지정이 가능하다. 주의할 것은 이 속성은 10i, 11g 드라이버에도 존재하지만 잘 사용되지 않는다.

Controlling the Java Heap

자바 런타임 메모리 튜닝은 블랙 아트 영역이다. 가장 중요한 두 가지 옵션은 -Xmx 와 -Xms 다. 자바 런타임 버전 및 OS 에 따라 다른 매개 변수가 있다. -Xmx 는 자바 런타임에서 사용할 최대 힙 크기를 설정한다. -Xms 는 초기 힙 크기를 설정한다. 디폴트 값은 OS 나 자바 런타임에 의존적이지만 대충 기본적으로 -Xmx 는 64MB, -Xms 는 1MB 다. 32 bit JVM 은 2GB 이상의 힙을 지원한다. 64bit JVM 은 아주 큰 힙을 지원할 것이다. 이 옵션들은 “k”, “m”, “g” 의 접미사를 가진 값을 받을 수 있는데, 이것은 kilo-, mega-, gigabytes 다. e.g -Xmx1G

Oracle JDBC 드라이버는 적절한 힙 크기로 실행될때 최고의 성능을 낼 수 있다. 대부분의 애플리케이션의 경우 애플리케이션 힙 크기를 일부 제한으로 늘리면 성능이 향상된다. 그 이후에 제한된 값 이상으로 애플리케이션 힙 크기를 늘려도 성능 차이는 없다. 만약 힙 크기가 머신이 가지고 있는 물리 메모리나 힙 메모리보다 훨씬 크기되면 세컨드 스토리지로 페이지되고 성능은 급격히 나빠질 것이다. 힙 크기를 설정할때, 최대 값 -Xmx 만으로는 출분하지 않다. 특히 11.2 드라이버는 메모리 할당에 그렇게 적극적이지 않다. 빈번하게 실행 가능한 최소값을 초과하여 힙 메모리를 늘리지 않는다. 만약 -Xmx 를 사용해 최대 힙 크기만 지정한다면, 11.2 드라이버는 설사 -Xmx 를 허용했다고 하더라도 최대 성능을 내기위한 충분한 메모리를 결코 사용하지 않는다. 만약 -Xms 를 사용해 최소 힙 크기를 함께 늘리면, 11.2 드라이버는 추가적인 메모리를 사용하게 만들 것이고 자주 최고의 성능을 제공할 것이다. 

애플리케이션 성능 튜닝시에 -Xmx 와 -Xms 모두 설정되고 모두 같은 값으로 설정된 고정된 힙 크기를 가지고 테스트하는 것은 매우 중요하다. JVM 이 힙 크기를 선택하도록 하면 고장된 힙 크기로 명확해진 성능이 모호하게 변화할 수 있다. 일반적으로 프로덕트 애플리케이션은 고정된 힙 크기에서도 잘 동작한다. -server 옵션 설정은 JVM 이 최소화 힙 크기에 대해서 좀 덜 적극적이게 될 것이고 이것은 약간의 성능 향상을 제공한다. -Xms 를 적절하게 설정하면 -server 설정만으로 제공되는 성능 이상으로 추가적인 성능을 제공하는 경우가 종종 있다. Oracle 은 서버 애플리케이션에 대해 -server, -Xms, -Xmx 설정을 권장한다. 보통 -Xms 와 -Xmx 는 같은 값으로 설정한다. 이것은 10i, 11g 그리고 12c 를 포함한 모든 Oracle JDBC 드라이버에 적용된다.

Conclusion

Oracle JDBC 드라이버는 최대 성능을 이룩하기 위해서 많은 양의 메모리를 사용할 수 있다. 만약 메모리가 여러분의 애플리케이션 성능을 제한한다면 최고 성능을 위해서 튜닝할 수 있다. 이상적으로 실제 애플리케이션 워크로드를 대표하는 재현 가능한 테스트 사례가 있어야 한다. 다양한 노브를 체계적으로 변경하고 테스트를 실행하면 최적의 설정을 식별할 수 있다. 모든 버전의 Oracle JDBC 드라이버에 대해, 묵시적 명령문 캐시 활성화, 적절한 데이터베이스 스키마 디자인, 신중한 SQL 코딩 그리고 적절한 fetch 크기 설정이 첫 단계다. 그 다음 Java 힙 크기를 대규모 실제 크기로 설정하기 위해 -Xmx 와 -Xms를 사용해 늘려라. 어떠한 값으로도 수용가능한 성능이 주어지면 -Xms 와 -Xmx 를 줄일 수 있다. 최대의 실제 힙 메모리를 가지고 성능이 원하는 것보다 낮고 10i 나 11g 드라이버를 사용 중이라면 필요에 따라 freeMemoryOnEnterImplictCache 이나 maxCachedBufferSize 를 사용해 실험해야 한다. 이것은 useThreadLocalBufferCache 를 설정해야할지 말지를 여러분의 애플리케이션 설계에서 명확하게 해준다. 만약 여러분이 모든 나머지 성능 영역을 찾고 있다면 자바 가비지 컬렉터 튜닝을 해봐야 겠지만 대부분의 경우 -Xmx 및 -Xms 설정이 필요로 하는 전부다.

Oracle 12c 데이터베이스 삭제하기

Oracle 12c 데이터베이스 삭제하기 위해서는 다음과 같이 하면 된다.

Oracle 12c 데이터베이스 삭제하기
Oracle PL/SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SQL> startup force mount exclusive restrict
ORACLE instance started.
 
Total System Global Area 6610223104 bytes
Fixed Size     8635576 bytes
Variable Size 3506441032 bytes
Database Buffers 3087007744 bytes
Redo Buffers     8138752 bytes
Database mounted.
SQL> drop database;
 
Database dropped.
 
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL>

/dev/shm 공유메모리 설정.

Oracle 12c 에서 메모리의 사용을 어떻게 할 것인가에 따라서 다음 두가지로 나뉜다.

  • AMM
  • ASMM

AMM을 사용할 경우에 init.ora 시작 파일에 MEMORY_TARGET, MEMORY_MAX_TARGET 의 값을 사용하며 ASMM 의 경우에는 SGA_TARGET, SGA_MAX_TARGET 값을 사용한다.

AMM은 SGA, PGA 내의 각종 메모리 구역을 사용하는 목적에 따라서 자동으로 조절해 항상 최대의 성능을 내도록 해준다. 이는 Oracle 11g 에서 소개된 것으로 Oracle 은 이를 적극 사용할 것을 권하고 있다. 하지만 한가지 문제가 있는데, Linux 시스템의 경우에 HugePageSize 를 사용할 수 없다.

ASMM은 SGA 만 자동으로 메모리를 관리해준다. PGA는 자동으로 해주지 않으며 이는 상황에 맞게 normal한 값을 주어야 하며 잘못 설정할 경우에 성능에 심각한 영향을 줄 수 있다. 하지만 Linux 시스템의 경우에 HugePageSize 를 사용할 수 있다.

AMM을 사용할 경우에 초점을 맞춰, 메모리 용량을 설정할 때에 Linux 시스템에 tmpfs 으로 마운트된 /dev/shm 에 Oracle 에서 사용할 메모리양 만큼 마운트를 해줘야 한다.

/dev/shm 는 다음과 같은 모습을 보인다.

/dev/shm 모습
ZSH
1
2
3
4
[oraserv1:oracle:~:O11R2]$ df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/vda1        42G   13G   27G  33% /
tmpfs           4.0G     0  4.0G   0% /dev/shm

/dev/shm 는 기본적으로 마운팅이 되는데, 이는 전체 물리메모리의 절반이 기본으로 된다.

왜 /dev/shm 의 크기가 중요한가?

이유는 간단하다. 이 크기만큼만 Oracle 이 사용할 수 있다. 정확하게는 AMM 기능을 사용할때에만 해당된다. 예를들어, init.ora 에 다음과 같이 /dev/shm 의 값보다 크게 설정했다고 치자.

init.ora 의 MEMORY_TARGET 갑 조정.
INI
1
2
memory_target=6G
memory_max_target=6G

/dev/shm 크기는 현재 4G 이다. 이 상태에서 Oracle 를 시작하면 다음과 같은 오류가 난다.

Oracle 시작 오류
Oracle PL/SQL
1
2
3
SQL> startup
ORA-00845: MEMORY_TARGET not supported on this system
SQL>

그렇다면 /dev/shm 값을 변경해줘야 한다. 어떻게 할까?

먼저, /etc/fstab 을 다음과 같이 변경한다.

/etc/fstab 변경.
diff
1
2
- tmpfs                   /dev/shm                tmpfs   defaults        0 0
+ tmpfs                   /dev/shm                tmpfs   size=4g        0 0

그리고 /etc/fstab 를 기반으로 마운트를 전부 재마운트 하도록 한다.

remount
ZSH
1
]# mount -a

위와같이 조정을 한 후에 init.ora 의 MEMORY_TARGET 를 4G 로 지정해도 정상적으로 동작하게된다.

ps, /etc/fstab 를 조작하지 않고 다음과 같이 수동으로 할 수 있다. 먼저 umount 를 다음과 같이 해준다.

/dev/shm 언마운트
ZSH
1
umount tmpfs

그리고 다음과 같이 크기를 지정해 마운트 해주면 된다.

/dev/shm 마운트.
ZSH
1
mount -t tmpfs shmfs -o size=4g /dev/shm

 

ps, 좀 더 정확하게 말하자면 /dev/shm 은 Virtual Shared Memory Filesystem 이다. Linux 는 공유 메모리를 위한 인프라가 다양한데, 그중에 하나가 파일시스템(Filesystem) 을 이용하는 것이다.

파일시스템에 데이터를 기록하면 여러 프로세스가 액세스를 할 수 있어 손쉽게 공유메모리 기능을 구현할 수 있다. 이것은 POSIX 에서 마련한 공유메모리 기법으로 보통 POSIX 공유메모리 인프라로 불리운다. 그것을 위해서 물리적 메모리의 일부를 Virtual Shared Memory Filesystem 으로 사용하는데, Linux 는 물리적 메모리의 50%를 /dev/shm 에 할당 한다.

Linux 에서 기본은 System V IPC 로 이는 커널파라메터 vm.shmmax, vm.shmall 의 값을 기준으로 공유메모리를 마련한다.

Oracle 11g 의 경우에는 POSIX 규격의 공유 메모리를 사용하기도 하고 System V IPC 규격의 공유 메모리를 설정에 따라서 사용하게 된다. AMM 기능은 POSIX 규격의 공유 메모리를, 그래서 /dev/shm 을 사용하며 이 크기에 따라서 Oracle 의 SGA+PGA 가 결정된다. 만일 AMM 기능을 사용하지 않을 경우에는 System V IPC 를 이용해야하기 때문에 커널 파라메터인 vm.shmmax, vm.shmall 값에 따라서 SGA 크기가 결정된다.

AMM 기능을 사용하면 HugePage 를 사용할 수 없다라고 하는데, 이는 당연한 것으로 HugePage 는 System V IPC 공유 메모리 인프라로 AMM 은 POSIX 규격의 공유 메모리 인프라를 사용하기 때문에 서로 호환이 될 수가 없는 것이다.

HugePage 는 System V IPC 의 공유 메모리를 구성하는 방법중에 하나로 기존의 4k 크기의 페이지를 2MB 크기의 페이지로 나뉘어진 공유 메모리를 사용하게 된다.

 

Oracle 11gR2 설치

Oracle 11gR2 설치

Silent Installation 방식이며 OFA(Optimal Flexible Architecture) 를 따른다.

환경

  • CentOS 6.8 64bit
  • Memory 4GB
  • Swap 8GB (Swap must be enabled double the size of RAM)
  • Storage Size 50GB

호스트네임 변경

호스트 네임 변경
1
2
3
4
5
]# vi /etc/sysconfig/network
HOSTNAME=oraserv1.systemv.pe.kr
]# vi /etc/hosts
192.168.96.42 oraserv1.systemv.pe.kr oraserv1
]# /etc/init.d/network restart

Selinux 설정 변경

1
2
]# vi /etc/selinux/config
SELINUX=disable

Kernel 3.10 설치

1
2
3
4
5
]# rpm --import https://www.elrepo.org/RPM-GPG-KEY-elrepo.org
]# rpm -Uvh http://www.elrepo.org/elrepo-release-6-6.el6.elrepo.noarch.rpm
]# sed -i "s/enabled=1/enabled=0/g" /etc/yum.repos.d/elrepo.repo
]# yum --enablerepo=elrepo-kernel install kernel-lt
]# sed -i "s/default=1/default=0/g" /boot/grub/grub.conf

설치 완료하고 나서 reboot.

계정생성

계정, 그룹 생성
ZSH
1
2
3
4
5
groupadd -g 500 oinstall
groupadd -g 501 dba
groupadd -g 502 oper
 
useradd -u 500 -g oinstall -G dba,oper oracle

 

의존성 패키지 설치

install packages of dependency
ZSH
1
2
3
4
]# yum install compat-libstdc++-33.x86_64 elfutils-libelf-devel.x86_64 elfutils-libelf-devel-static.x86_64 gcc.x86_64 gcc-c++.x86_64 glibc-devel.x86_64 ksh.x86_64 libaio-devel.i686 libaio-devel.x86_64 libgomp.x86_64 libstdc++-devel.x86_64 sysstat.x86_64 unzip unixODBC.i686 unixODBC-devel.i686 unixODBC.x86_64 unixODBC-devel.x86_64
]# yum remove ksh
]# wget ftp://ftp.pbone.net/mirror/www.whiteboxlinux.org/whitebox/4/en/os/x86_64/WhiteBox/RPMS/pdksh-5.2.14-30.x86_64.rpm
]# yum localinstall pdksh-5.2.14-30.x86_64.rpm

 

OFA 디렉토리 생성

OFA 디렉토리 생성
ZSH
1
2
3
mkdir -p /u01/app/oracle/product/11.2.0/db_1
mkdir -p /u01/app/oraInventory
chown oracle:oinstall -R /u01

 

sysctl.conf

Vim
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
fs.aio-max-nr = 1048576
# It is recommended to have a minimum of 256 as value for every 4MB of RAM you have.
# So for 8GB RAM = 2048 * 4MB = 2048 * 256 = 524288
fs.file-max = 6815744
# It sets the maximum number of shared memory segments that server can handle.
# As Oracle recommends the value should be at least 4096,
# it says that we cannot find more than 4096 number of shared memory segments at any instance on the server.
# Note that SHMMNI value is in numbers.
#
# No change it should be 4096. It must be increased if you have more than at least one fourth (1024) Oracle Databases running on the server. Which we never recommend.
kernel.shmmni = 4096
# It defines the total amount of shared memory PAGES that can be used system-wide.
# It means that to the use all the physical memory this value should be less than or equal to total physical memory size.
# For DBA's, it means that sum of all SGA sizes on the server should be less than or equal to SHMALL value.
# Note that SHMALL value is a number of pages.
#
# If you want the maximum size of SGA on this server to be 5GB, then this parameter value should be 5*1024*1024*1024 = 5368709120 bytes. This, in turn, says that you should not have any database with more than 5GB of SGA. But you can have multiple databases with each 5GB of SGA or even less. This is the fact why Oracle recommends to have this value more than half of the memory to utilize it for SGA(s).
#
# By chance, if your SGA size is more than 5GB say it is 7GB then 2 shared memory areas will be allocated to SGA with one of 5GB and two of 2GB sizes, which doesn's perform well.
kernel.shmmax = 2147483648
 
#  It defines the total amount of shared memory PAGES that can be used system-wide. It means that to the use all the physical memory this value should be less than or equal to total physical memory size. For DBA's, it means that sum of all SGA sizes on the server should be less than or equal to SHMALL value. Note that SHMALL value is a number of pages.
# By default the page size on Linux is 4KB. The total size of RAM is 8GB. Let us leave at least 1GB of RAM for Linux kernel to run, with which consider 7GB can be used for Oracle Databases. Now value of SHMALL can be:
#
# (7*1024*1024)KB/4KB = 1835008
kernel.shmall = 95325
 
kernel.sem = 250 32000 100 128
 
# This parameter defines the range of port numbers that system can use for programs which want to connect to the server without a specific port number.
#
# Now, it makes sense if you have come across somebody advising you not to use port numbers for listener beyond 9000. Also, just look back to documents on OEM installation, Oracle uses and advises all the default port numbers less than 9000.
net.ipv4.ip_local_port_range = 9000 65500
 
# This parameter defines the default  and maximum RECEIVE socket memory through TCP.
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
 
# This parameter defines the default  and maximum SEND socket memory through TCP.
net.core.wmem_default = 262144
net.core.wmem_max = 1048586

 

/etc/security/limits.conf

1
2
3
4
5
6
7
# http://www.tecmint.com/setting-up-prerequisites-for-oracle-12c-installation/
oracle          soft    nofile  1024
oracle          hard    nofile  65536
oracle          soft    nproc   2047
oracle          hard    nproc   16384
oracle          soft    stack   10240
oracle          hard    stack   32768

/etc/security/limits.d/90-nproc.conf

1
2
*          -    nproc     16384
root       soft    nproc     unlimited

 

/etc/pam.d/login

1
session required pam_limits.so

 

/etc/oraInst.loc

1
2
3
4
5
6
]# vim /etc/oraInst.loc
inventory_loc=/u01/app/oraInventory
inst_group=oinstall
 
]# chown oracle:oinstall /etc/oraInst.loc
]# chmod 664 /etc/oraInst.loc

oracle 계정으로 로그인 한후에 Oracle 11gR2 바이너리 설치 파일을 다음과 같이 압축 해제.

ZSH
1
2
3
4
5
]$ mkdir -p orainst/11.2.0
]$ cp *.zip orainst/11.2.0/
]$ cd orainst/11.2.0
]$ unzip linux.x64_11gR2_database_1of2.zip
]$ unzip linux.x64_11gR2_database_2of2.zip

 

Silent 설치를 위한 파일 작성

ZSH
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
$ cd orainst/11.2.0/database
$ cp response/db_install.rsp inst.rsp
$ vim inst.rsp
oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v11.2.0
oracle.install.option=INSTALL_DB_SWONLY
ORACLE_HOSTNAME=oraserv1
UNIX_GROUP_NAME=oinstall
INVENTORY_LOCATION=/home/oracle/orainst/11.2.0/database/stage/products.xml
SELECTED_LANGUAGES=en
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
ORACLE_BASE=/u01/app/oracle
oracle.install.db.InstallEdition=EE
oracle.install.db.DBA_GROUP=dba
oracle.install.db.OPER_GROUP=oper
DECLINE_SECURITY_UPDATES=true

 

Silent 설치 파일 설행.

1
2
3
4
5
6
7
8
9
10
11
12
[oracle@oraserv1 database]$ ./runInstaller -ignoreSysPrereqs -force -silent -responseFile /home/oracle/orainst/11.2.0/database/inst.rsp
[oracle@oraserv1 database]$ Oracle Database 11g의 설치을(를) 성공했습니다.
자세한 내용은 '/u01/app/oraInventory/logs/silentInstall2017-02-14_11-04-17PM.log'을(를) 확인하십시오.
 
루트 사용자로 다음 스크립트를 실행합니다.
1. /u01/app/oracle/product/11.2.0/db_1/root.sh
 
 
 
Successfully Setup Software.
 
[oracle@oraserv1 database]$

설치 시간은 시스템에 따라 다르지만 설치하는 동안에 java 프로세스가 동작한다. 그리고 설치가 성공하면 위와같이 메시지가 나온다.

oracle bashrc 설정.

.bashrc of Oracle
ZSH
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export ORACLE_BASE=/u01/app/oracle
export TNS_ADMIN=$ORACLE_HOME/network/admin
export ADR_BASE=$ORACLE_BASE/diag
export ORACLE_SID=O11R2
 
export LD_LIBRARY_PATH=/usr/lib64:$ORACLE_HOME/lib
export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib;
export PATH=$ORACLE_HOME/bin:$PATH
 
export PS1='[\h:\u:\W:${ORACLE_SID}]$ '
 
alias cdob='cd $ORACLE_BASE'
alias cdoh='cd $ORACLE_HOME'
alias tns='cd $ORACLE_HOME/network/admin'
alias envo='env | grep ORACLE'
 
umask 022
 
envo

 

Create Database

ZSH
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
#]$ cd $ORACLE_HOME/dbs
#]$ cp init.org inito11c.ora
db_name='O11R2'
memory_target=1G
memory_max_target=1G
processes = 200
audit_file_dest='/u01/app/oracle/admin/orcl/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest='/u01/app/oracle'
dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
open_cursors=499
remote_login_passwordfile='EXCLUSIVE'
undo_management='AUTO'
undo_tablespace='UNDOTBS1'
# You may want to ensure that control files are created on separate physical
# devices
control_files = (/u01/dbfile/O11R2/control01.ctl, /u02/dbfile/O11R2/control02.ctl)
job_queue_processes=10
fast_start_mttr_target=500
 
compatible ='11.2.0'

inito11c.ora 를 위와 같이 작성하고 다음과 같이 디렉토리를 작성한다.

create directory for Creating database
ZSH
1
2
3
4
5
6
7
8
$ mkdir -p /u01/app/oracle/admin/orcl/adump
$ mkdir -p /u01/app/oracle/flash_recovery_area
$ mkdir -p /u01/dbfile/O11R2
# mkdir -p /u02/dbfile/O11R2
$ mkdir -p /u01/oraredo/O11R2
# mkdir -p /u02/oraredo/O11R2
# chown -R oracle:dba /u01
# chown -R oracle:dba /u02

oracle 사용자의 기본 그룹을 dba 로 변경합니다.

1
]# usermode -g dba oracle

다음과 같은 내용으로 credb.sql 파일로 작성한다.

Create database SQL
Oracle PL/SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
CREATE DATABASE O11R2
  MAXLOGFILES 16
  MAXLOGMEMBERS 4
  MAXDATAFILES 1024
  MAXINSTANCES 1
  MAXLOGHISTORY 680
  character set "UTF8"
DATAFILE
'/u01/dbfile/O11R2/system01.dbf'
   SIZE 500M REUSE
EXTENT MANAGEMENT LOCAL
UNDO TABLESPACE undotbs1 DATAFILE
'/u01/dbfile/O11R2/undotbs01.dbf'
  SIZE 800M
SYSAUX DATAFILE
'/u01/dbfile/O11R2/sysaux01.dbf'
  SIZE 500M
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE
'/u01/dbfile/O11R2/temp01.dbf'
  SIZE 500M
DEFAULT TABLESPACE USERS DATAFILE
'/u01/dbfile/O11R2/users01.dbf'
  SIZE 20M
LOGFILE GROUP 1
       ('/u01/oraredo/O11R2/redo01a.rdo',
       '/u02/oraredo/O11R2/redo01b.rdo') SIZE 50M,
       GROUP 2
       ('/u01/oraredo/O11R2/redo02a.rdo',
       '/u02/oraredo/O11R2/redo02b.rdo') SIZE 50M,
       GROUP 3
       ('/u01/oraredo/O11R2/redo03a.rdo',
       '/u02/oraredo/O11R2/redo03b.rdo') SIZE 50M
USER sys    IDENTIFIED BY foo
USER system IDENTIFIED BY foo;

oracle 을 nomount 로 시작시킨다. 그리고 위 credb.sql 를 실행한다.

Oracle PL/SQL
1
2
3
4
$ sqlplus / as sysdba
SQL> startup nomount;
SQL> @credb.sql
Database created.

Create a Data Dictionary

Oracle PL/SQL
1
2
3
4
5
6
7
8
SQL> show user
USER is "SYS"
 
SQL> @?/rdbms/admin/catalog.sql
SQL> @?/rdbms/admin/catproc.sql
 
SQL> connect system/foo
SQL> @?/sqlplus/admin/pupbld

Configure Listener

listener.ora
ZSH
1
2
3
4
5
6
7
8
9
10
11
12
13
14
]$ vim $ORACLE_HOME/network/admin/listener.ora
LISTENER =
  (ADDRESS_LIST=
        (ADDRESS=(PROTOCOL=tcp)(HOST=oraserv1)(PORT=1521)))
 
SID_LIST_LISTENER=
   (SID_LIST=
        (SID_DESC=
          (GLOBAL_DBNAME=O11R2)
          (SID_NAME=O11R2)
          (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
 
        )
       )

그리고 listener 를 시작.

lsnrctl start
ZSH
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
[oracle@oraserv1 admin]$ lsnrctl start
 
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 24-FEB-2017 01:22:08
 
Copyright (c) 1991, 2009, Oracle.  All rights reserved.
 
Starting /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...
 
TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/oraserv1/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraserv1.systemv.pe.kr)(PORT=1521)))
 
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                24-FEB-2017 01:22:11
Uptime                    0 days 0 hr. 0 min. 7 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/oraserv1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraserv1.systemv.pe.kr)(PORT=1521)))
The listener supports no services
The command completed successfully

 

SYS 패스워드 파일 생성.

SYS 패스워드 생성
ZSH
1
2
]$ cd $ORACLE_HOME/dbs
]$ orapwd file=orapwO11R2 password=foo

 

EM 수동 구성

ZSH
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
]$ emca -config dbcontrol db -repos create
 
STARTED EMCA at Feb 26, 2017 2:21:17 AM
EM Configuration Assistant, Version 11.2.0.0.2 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
 
Enter the following information:
Database SID: O11R2
Listener port number: 1521
Listener ORACLE_HOME [ /u01/app/oracle/product/11.2.0/db_1 ]:
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
-----------------------------------------------------------------
 
You have specified the following settings
 
Database ORACLE_HOME ................ /u01/app/oracle/product/11.2.0/db_1
 
Local hostname ................ oraserv1.systemv.pe.kr
Listener ORACLE_HOME ................ /u01/app/oracle/product/11.2.0/db_1
Listener port number ................ 1521
Database SID ................ O11R2
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............
 
-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: Y

EM 시작/중지

시작과 중지는 다음과 같이 합니다.

EM 시작/중지
ZSH
1
2
3
emctl start dbconsole
emctl stop dbconsole
emctl status dbconsole

 

Oracle 12c 설치

Oracle 12c 설치

Silent Installation 방식이며 OFA(Optimal Flexible Architecture) 를 따른다.

환경

  • CentOS 7 64bit
  • Memory 8GB
  • Swap 16GB (Swap must be enabled double the size of RAM)
  • Storage Size 40GB

호스트네임 변경

호스트 네임 변경
ZSH
1
2
3
4
hostnamectl set-hostname oraserv1
hostnamectl set-hostname oraserv1 --static
hostnamectl set-hostname "Server1 of Oracle Database" --pretty
hostnamectl set-hostname oraserv1 --transient

 

계정생성

계정, 그룹 생성
ZSH
1
2
3
4
5
6
7
8
9
10
11
groupadd -g 500 oinstall
groupadd -g 501 dba
groupadd -g 502 oper
groupadd -g 503 backupdba
groupadd -g 504 dgdba
groupadd -g 505 kmdba
groupadd -g 506 asmdba
groupadd -g 507 asmoper
groupadd -g 508 asmadmin
 
useradd -u 500 -g oinstall -G dba,oper,backupdba,dgdba,kmdba oracle

 

의존성 패키지 설치

의존성 패키지 설치
ZSH
1
yum install compat-libcap1-1.10 compat-libstdc++-* libstdc++-devel-* sysstat-10.1.5* gcc-4.* gcc-c++-4.* ksh* glibc-devel-* libaio-* libaio-devel-*

 

OFA 디렉토리 생성

OFA 디렉토리 생성
ZSH
1
2
3
mkdir -p /u01/app/oracle/product/12.1.0.2/db_1
mkdir -p /u01/app/oraInventory
chown oracle:oinstall -R /u01

 

sysctl.conf

Vim
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
fs.aio-max-nr = 1048576
# It is recommended to have a minimum of 256 as value for every 4MB of RAM you have.
# So for 8GB RAM = 2048 * 4MB = 2048 * 256 = 524288
fs.file-max = 6815744
# It sets the maximum number of shared memory segments that server can handle.
# As Oracle recommends the value should be at least 4096,
# it says that we cannot find more than 4096 number of shared memory segments at any instance on the server.
# Note that SHMMNI value is in numbers.
#
# No change it should be 4096. It must be increased if you have more than at least one fourth (1024) Oracle Databases running on the server. Which we never recommend.
kernel.shmmni = 4096
# It defines the total amount of shared memory PAGES that can be used system-wide.
# It means that to the use all the physical memory this value should be less than or equal to total physical memory size.
# For DBA's, it means that sum of all SGA sizes on the server should be less than or equal to SHMALL value.
# Note that SHMALL value is a number of pages.
#
# If you want the maximum size of SGA on this server to be 5GB, then this parameter value should be 5*1024*1024*1024 = 5368709120 bytes. This, in turn, says that you should not have any database with more than 5GB of SGA. But you can have multiple databases with each 5GB of SGA or even less. This is the fact why Oracle recommends to have this value more than half of the memory to utilize it for SGA(s).
#
# By chance, if your SGA size is more than 5GB say it is 7GB then 2 shared memory areas will be allocated to SGA with one of 5GB and two of 2GB sizes, which doesn's perform well.
kernel.shmmax = 4294967296
 
#  It defines the total amount of shared memory PAGES that can be used system-wide. It means that to the use all the physical memory this value should be less than or equal to total physical memory size. For DBA's, it means that sum of all SGA sizes on the server should be less than or equal to SHMALL value. Note that SHMALL value is a number of pages.
# By default the page size on Linux is 4KB. The total size of RAM is 8GB. Let us leave at least 1GB of RAM for Linux kernel to run, with which consider 7GB can be used for Oracle Databases. Now value of SHMALL can be:
#
# (7*1024*1024)KB/4KB = 1835008
kernel.shmall = 1835008
 
kernel.sem = 250 32000 100 128
 
# This parameter defines the range of port numbers that system can use for programs which want to connect to the server without a specific port number.
#
# Now, it makes sense if you have come across somebody advising you not to use port numbers for listener beyond 9000. Also, just look back to documents on OEM installation, Oracle uses and advises all the default port numbers less than 9000.
net.ipv4.ip_local_port_range = 9000 65500
 
# This parameter defines the default  and maximum RECEIVE socket memory through TCP.
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
 
# This parameter defines the default  and maximum SEND socket memory through TCP.
net.core.wmem_default = 262144
net.core.wmem_max = 1048586

 

/etc/security/limits.conf

1
2
3
4
5
6
7
# http://www.tecmint.com/setting-up-prerequisites-for-oracle-12c-installation/
oracle          soft    nofile  1024
oracle          hard    nofile  65536
oracle          soft    nproc   2047
oracle          hard    nproc   16384
oracle          soft    stack   10240
oracle          hard    stack   32768

 

/etc/security/limits.d/20-nproc.conf

1
2
*          -    nproc     16384
root       soft    nproc     unlimited

 

/etc/oraInst.loc

1
2
3
4
5
6
]# vim /etc/oraInst.loc
inventory_loc=/u01/app/oraInventory
inst_group=oinstall
 
]# chown oracle:oinstall /etc/oraInst.loc
]# chmod 664 /etc/oraInst.loc

 

oracle 계정으로 로그인 한후에 Oracle 12c 바이너리 설치 파일을 다음과 같이 압축 해제.

ZSH
1
2
3
4
5
]$ mkdir -p orainst/12.1.0.2
]$ cp *.zip orainst/12.1.0.2/
]$ cd orainst/12.1.0.2
]$ unzip linuxamd64_12102_database_1of2.zip
]$ unzip linuxamd64_12102_database_2of2.zip

 

Silent 설치를 위한 파일 작성

ZSH
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
$ cd orainst/12.1.0.2/database
$ cp response/db_install.rsp inst.rsp
$ vim inst.rsp
oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v12.1.0
oracle.install.option=INSTALL_DB_SWONLY
ORACLE_HOSTNAME=oraserv1
UNIX_GROUP_NAME=oinstall
INVENTORY_LOCATION=/u01/app/oraInventory
SELECTED_LANGUAGES=en
ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/db_1
ORACLE_BASE=/u01/app/oracle
oracle.install.db.InstallEdition=EE
oracle.install.db.DBA_GROUP=dba
oracle.install.db.OPER_GROUP=oper
oracle.install.db.BACKUPDBA_GROUP=backupdba
oracle.install.db.DGDBA_GROUP=dgdba
oracle.install.db.KMDBA_GROUP=kmdba
oracle.install.db.OSRACDBA_GROUP=dba
DECLINE_SECURITY_UPDATES=true

 

Silent 설치 파일 설행.

1
2
3
4
5
6
7
8
9
10
11
12
[oracle@oraserv1 database]$ ./runInstaller -ignoreSysPrereqs -force -silent -responseFile /home/oracle/orainst/12.1.0.2/database/inst.rsp
[oracle@oraserv1 database]$ Oracle Database 12c의 설치을(를) 성공했습니다.
자세한 내용은 '/u01/app/oraInventory/logs/silentInstall2017-02-14_11-04-17PM.log'을(를) 확인하십시오.
 
루트 사용자로 다음 스크립트를 실행합니다.
1. /u01/app/oracle/product/12.1.0.2/db_1/root.sh
 
 
 
Successfully Setup Software.
 
[oracle@oraserv1 database]$

설치 시간은 시스템에 따라 다르지만 설치하는 동안에 java 프로세스가 동작한다. 그리고 설치가 성공하면 위와같이 메시지가 나온다.

oracle bashrc 설정.

.bashrc of Oracle
ZSH
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
export ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/db_1
export ORACLE_BASE=/u01/app/oracle
export TNS_ADMIN=$ORACLE_HOME/network/admin
export ADR_BASE=$ORACLE_BASE/diag
export ORACLE_SID=o12c
 
export LD_LIBRARY_PATH=/usr/lib64:$ORACLE_HOME/lib
export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib;
export PATH=$ORACLE_HOME/bin:$PATH
 
export PS1='[\h:\u:\W:${ORACLE_SID}]$ '
 
alias cdob='cd $ORACLE_BASE'
alias cdoh='cd $ORACLE_HOME'
alias tns='cd $ORACLE_HOME/network/admin'
alias envo='env | grep ORACLE'
 
umask 022
 
envo

 

Create Database

ZSH
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
#]$ cd $ORACLE_HOME/dbs
#]$ cp init.org inito12c.ora
db_name='o12c'
memory_target=1G
memory_max_target=1G
processes = 200
audit_file_dest='/u01/app/oracle/admin/orcl/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest='/u01/app/oracle'
dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
open_cursors=499
remote_login_passwordfile='EXCLUSIVE'
undo_management='AUTO'
undo_tablespace='UNDOTBS1'
# You may want to ensure that control files are created on separate physical
# devices
control_files = (/u01/dbfile/o12c/control01.ctl, /u02/dbfile/o12c/control02.ctl)
job_queue_processes=10
fast_start_mttr_target=500
 
compatible ='11.2.0'

inito12c.ora 를 위와 같이 작성하고 다음과 같이 디렉토리를 작성한다.

create directory for Creating database
ZSH
1
2
3
4
5
6
7
8
$ mkdir -p /u01/app/oracle/admin/orcl/adump
$ mkdir -p /u01/app/oracle/fast_recovery_area
$ mkdir -p /u01/dbfile/o12c
$ mkdir -p /u02/dbfile/o12c
$ mkdir -p /u01/oraredo/o12c
$ mkdir -p /u02/oraredo/o12c
# chown -R oracle:dba /u01
# chown -R oracle:dba /u02

다음과 같은 내용으로 credb.sql 파일로 작성한다.

Create database SQL
Oracle PL/SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
CREATE DATABASE o12c
  MAXLOGFILES 16
  MAXLOGMEMBERS 4
  MAXDATAFILES 1024
  MAXINSTANCES 1
  MAXLOGHISTORY 680
  CHARACTER SET AL32UTF8
DATAFILE
'/u01/dbfile/o12c/system01.dbf'
   SIZE 500M REUSE
EXTENT MANAGEMENT LOCAL
UNDO TABLESPACE undotbs1 DATAFILE
'/u01/dbfile/o12c/undotbs01.dbf'
  SIZE 800M
SYSAUX DATAFILE
'/u01/dbfile/o12c/sysaux01.dbf'
  SIZE 500M
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE
'/u01/dbfile/o12c/temp01.dbf'
  SIZE 500M
DEFAULT TABLESPACE USERS DATAFILE
'/u01/dbfile/o12c/users01.dbf'
  SIZE 20M
LOGFILE GROUP 1
       ('/u01/oraredo/o12c/redo01a.rdo',
       '/u02/oraredo/o12c/redo01b.rdo') SIZE 50M,
       GROUP 2
       ('/u01/oraredo/o12c/redo02a.rdo',
       '/u02/oraredo/o12c/redo02b.rdo') SIZE 50M,
       GROUP 3
       ('/u01/oraredo/o12c/redo03a.rdo',
       '/u02/oraredo/o12c/redo03b.rdo') SIZE 50M
USER sys    IDENTIFIED BY foo
USER system IDENTIFIED BY foo;

oracle 을 nomount 로 시작시킨다. 그리고 위 credb.sql 를 실행한다.

Oracle PL/SQL
1
2
3
4
$ sqlplus / as sysdba
SQL> startup nomount;
SQL> @credb.sql
Database created.

Create a Data Dictionary

Oracle PL/SQL
1
2
3
4
5
6
7
8
SQL> show user
USER is "SYS"
 
SQL> @?/rdbms/admin/catalog.sql
SQL> @?/rdbms/admin/catproc.sql
 
SQL> connect system/foo
SQL> @?/sqlplus/admin/pupbld

Configure Listener

listener.ora
ZSH
1
2
3
4
5
6
7
8
9
10
11
12
13
14
]$ vim $ORACLE_HOME/network/admin/listener.ora
LISTENER =
  (ADDRESS_LIST=
        (ADDRESS=(PROTOCOL=tcp)(HOST=oraserv1)(PORT=1521)))
 
SID_LIST_LISTENER=
   (SID_LIST=
        (SID_DESC=
          (GLOBAL_DBNAME=o12c)
          (SID_NAME=o12c)
          (ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/db_1)
 
        )
       )

외부인증을 위한 패스워드 파일을 생성한다. 이것을 생성하지 않으면 외부로부터 접속을 할때에 인증정보가 아무리 맞아도 ora-01017 메시지를 내보낸다. 다음과 같이 패스워드 파일을 생성해 준다.

패스워드 파일 생성
ZSH
1
orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=패스워드 entries=20

listener 를 시작.

lsnrctl start
ZSH
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
[oracle@oraserv1 admin]$ lsnrctl start
 
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 17-FEB-2017 21:14:07
 
Copyright (c) 1991, 2014, Oracle.  All rights reserved.
 
Starting /u01/app/oracle/product/12.1.0.2/db_1/bin/tnslsnr: please wait...
 
TNSLSNR for Linux: Version 12.1.0.2.0 - Production
System parameter file is /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/oraserv1/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraserv1)(PORT=1521)))
 
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                17-FEB-2017 21:14:07
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/oraserv1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraserv1)(PORT=1521)))
The listener supports no services
The command completed successfully

EM Database Express 12c 활성화.

Check DB Express 12c port
Oracle PL/SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SQL> select dbms_xdb_config.gethttpport() from dual;
 
DBMS_XDB_CONFIG.GETHTTPPORT()
----------------------
     0
 
SQL> exec dbms_xdb_config.sethttpport(8080);
 
PL/SQL procedure successfully completed.
 
SQL> select dbms_xdb.gethttpport() from dual;
 
DBMS_XDB.GETHTTPPORT()
----------------------
  8080

lsnrctl status 에서 8080 포트가 보이면 정상. http://ip:8080/em 으로 접속해서 로그인 화면 나오면 정상입니다.