Oracle Database

listener.ora, tnsnames.ora 생성하기

오라클 데이터베이스 19c 를 Silent 설치를 하고 나면 listener.ora, tnsnames.ora 가 생성되지 않는다. 어떻게 수동으로 이것을 생성하는지에 대해서 알아보고 차이에 대해서 간단히 설명한다. 신기하게도 오라클 데이터베이스 19c를 Silent 설치하고 난 후에 이것을 생성하지 않는다고 하더라도 원격 클라이언트 접속에는 아무런 문제가 되지 않는다. 하지만 접속 서비스를 할당하고 접속을 쪼개고 싶다면 tnsnames.ora 파일이 반드시 있어야 한다. Oracle Net Listener Listener 는 정확하게는 ‘Oracle Net Listener’ 라고 한다. 리스너는 하나 혹은 그 이상의 지원하는 서비스에 대한 정보, 프로토콜 주소들을 리스닝하도록 설정된다. 리스너의 설정 […]

more
Oracle Database

오라클 기본 정보 확인

오라클을 설치하고 나면 정보를 확인해야 한다. 오라클은 많은 테이블, 뷰, 동적쿼리등을 지원하는데 워낙 많다보니까 다 알수는 없다. 필요한 정보를 위한 간단한 쿼리들을 소개 한다. 데이터베이스 컴포넌트들의 상태 확인 오라클 데이터베이스는 다양한 컴포넌트들로 구성되는데, 이에 대한 상태를 확인할 수 있다.

데이터베이스 컴포넌트들의 상태 확인
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 은 쉘 환경변수에도 사용되고 있어 확인해 둘 […]

more
Oracle Database

오라클 데이터베이스 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 개념이 존재한다. 실제 사용자 데이터베이스는 […]

more
Oracle Database

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 바이너리 설치 […]

more
Database, Oracle Database

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 설치를 위한 파일 작성 […]

more