Oracle 19.3 on Rocky linux

티스토리 뷰

기록남기기

Oracle 19.3 on Rocky linux

양들의침묵1 2021. 9. 4. 14:21

[oracle@localhost ~]$ alter user pdbadmin identified by xxxxxx;

Linux 서버에 Oralce 을 설치해본게 너무 오랜만이어서 약간의 시간이 걸렸고,
이번에 설치하면서 기록을 해본다

우선 ubuntu 20.04 에 해당 버전을 설치하려고 했더니, 안된다는 내용이 있었고
CentOS는 더이상 사용하기에는 이슈가 있어서, rocky linux 를 선택했고, 여기에 설치해봤다

구성환경

OS : Rocky Linux release 8.4 (Green Obsidian)
Hostname : oracle-db-19c.linux.com

사전에 필요한 패키지 설정

[root@oracle-db-19c ~]# yum -y groupinstall 'Development Tools'
[root@oracle-db-19c ~]# yum install -y libnsl
[root@oracle-db-19c ~]# yum -y install bc \
binutils \
elfutils-libelf \
elfutils-libelf-devel \
fontconfig-devel \
glibc \
glibc-devel \
ksh \
libaio \
libaio-devel \
libXrender \
libXrender-devel \
libX11 \
libXau \
libXi \
libXtst \
libgcc \
librdmacm-devel \
libstdc++ \
libstdc++-devel \
libxcb \
make \
net-tools \
smartmontools \
sysstat \
unzip \
libnsl \
libnsl2

DNS 설정

[root@oracle-db-19c ~]# echo "192.168.5.183 oracle-db-19c.linux.com oracle-db-19c" >> /etc/hosts

Disable Transparent HugePages in Rocky Linux 8:

[root@oracle-db-19c ~]# cat /sys/kernel/mm/transparent_hugepage/enabled
[always] madvise never

[root@oracle-db-19c ~]# vim /etc/default/grub

##transparent_hugepage=never 추가

GRUB_TIMEOUT=5
GRUB_DISTRIBUTOR="$(sed 's, release .*$,,g' /etc/system-release)"
GRUB_DEFAULT=saved
GRUB_DISABLE_SUBMENU=true
GRUB_TERMINAL_OUTPUT="console"
GRUB_CMDLINE_LINUX="crashkernel=auto resume=/dev/mapper/rl-swap rd.lvm.lv=rl/root rd.lvm.lv=rl/swap transparent_hugepage=never"
GRUB_DISABLE_RECOVERY="true"
GRUB_ENABLE_BLSCFG=true

[root@oracle-db-19c ~]# grub2-mkconfig -o /boot/grub2/grub.cfg
Generating grub configuration file ...
done

[root@oracle-db-19c ~]# systemctl reboot

[root@oracle-db-19c ~]# cat /sys/kernel/mm/transparent_hugepage/enabled
always madvise [never]

Create Linux users and groups as required by Oracle Database 19c.

[root@oracle-db-19c ~]# groupadd -g 1501 oinstall
[root@oracle-db-19c ~]# groupadd -g 1502 dba
[root@oracle-db-19c ~]# groupadd -g 1503 oper
[root@oracle-db-19c ~]# groupadd -g 1504 backupdba
[root@oracle-db-19c ~]# groupadd -g 1505 dgdba
[root@oracle-db-19c ~]# groupadd -g 1506 kmdba
[root@oracle-db-19c ~]# groupadd -g 1507 racdba
[root@oracle-db-19c ~]# useradd -u 1501 -g oinstall -G dba,oper,backupdba,dgdba,kmdba,racdba oracle
[root@oracle-db-19c ~]# echo "oracle" | passwd oracle --stdin
Changing password for user oracle.
passwd: all authentication tokens updated successfully.

Set Security limits for Oracle user:

[root@oracle-db-19c ~]# vi /etc/security/limits.d/30-oracle.conf

oracle   soft   nofile    1024
oracle   hard   nofile    65536
oracle   soft   nproc    16384
oracle   hard   nproc    16384
oracle   soft   stack    10240
oracle   hard   stack    32768
oracle   hard   memlock    134217728
oracle   soft   memlock    134217728

Adjust Kernel Parameters in Rocky Linux 8:

[root@oracle-db-19c ~]# vi /etc/sysctl.d/98-oracle.conf

fs.file-max = 6815744
kernel.sem = 250 32000 100 128
kernel.shmmni = 4096
kernel.shmall = 1073741824
kernel.shmmax = 4398046511104
kernel.panic_on_oops = 1
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
net.ipv4.conf.all.rp_filter = 2
net.ipv4.conf.default.rp_filter = 2
fs.aio-max-nr = 1048576
net.ipv4.ip_local_port_range = 9000 65500

[root@oracle-db-19c ~]# sysctl -p

Create Directories for Oracle Database 19c:

[root@oracle-db-19c ~]# mkdir -p /u01/app/oracle/product/19.3.0/dbhome_1
[root@oracle-db-19c ~]# mkdir -p /u02/oradata
[root@oracle-db-19c ~]# chown -R oracle:oinstall /u01 /u02
[root@oracle-db-19c ~]# chmod -R 775 /u01 /u02

/u01 은 Oracle DBMS 디렉토리 , /u02 는 Racle Databases 디렉토리이다

Configure Linux Environment for Oracle User:

[root@oracle-db-19c ~]# su - oracle
[oracle@oracle-db-19c ~]$
[oracle@oracle-db-19c ~]$ vi ~/.bash_profile

# Oracle Settings
export TMP=/tmp
export TMPDIR=$TMP

export ORACLE_HOSTNAME=oracle-db-19c.linux.com
export ORACLE_UNQNAME=cdb1
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=cdb1
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

[oracle@oracle-db-19c ~]$ source ~/.bash_profile

Oracle 파일 다운로드 는 아래에서 할 수 있다

https://www.oracle.com/database/technologies/oracle-database-software-downloads.html#19c

[oracle@oracle-db-19c ~]$ unzip LINUX.X64_193000_db_home.zip -d $ORACLE_HOME

[oracle@oracle-db-19c ~]$ export CV_ASSUME_DISTID=RHEL8.0

[oracle@oracle-db-19c ~]$ cd $ORACLE_HOME
[oracle@oracle-db-19c dbhome_1]$ ./runInstaller -ignorePrereq -waitforcompletion -silent \
oracle.install.option=INSTALL_DB_SWONLY \
ORACLE_HOSTNAME=${ORACLE_HOSTNAME} \
UNIX_GROUP_NAME=oinstall \
INVENTORY_LOCATION=${ORA_INVENTORY} \
ORACLE_HOME=${ORACLE_HOME} \
ORACLE_BASE=${ORACLE_BASE} \
oracle.install.db.InstallEdition=EE \
oracle.install.db.OSDBA_GROUP=dba \
oracle.install.db.OSBACKUPDBA_GROUP=backupdba \
oracle.install.db.OSDGDBA_GROUP=dgdba \
oracle.install.db.OSKMDBA_GROUP=kmdba \
oracle.install.db.OSRACDBA_GROUP=racdba \
SECURITY_UPDATES_VIA_MYORACLESUPPORT=false \
DECLINE_SECURITY_UPDATES=true
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_2020-04-28_02-44-19PM.rsp

You can find the log of this install session at:
 /tmp/InstallActions2020-04-28_02-44-19PM/installActions2020-04-28_02-44-19PM.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:
[oracle-db-19c]
Execute /u01/app/oracle/product/19.3.0/dbhome_1/root.sh on the following nodes:
[oracle-db-19c]

[root@oracle-db-19c ~]# /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.
[root@oracle-db-19c ~]# /u01/app/oracle/product/19.3.0/dbhome_1/root.sh
Check /u01/app/oracle/product/19.3.0/dbhome_1/install/root_oracle-db-19c.linux.com_2020-04-28_15-01-30-090367646.log for the output of root script

[oracle@oracle-db-19c ~]$ lsnrctl start

[oracle@oracle-db-19c ~]$ dbca -silent -createDatabase \
> -templateName General_Purpose.dbc \
> -gdbname ${ORACLE_SID} -sid  ${ORACLE_SID} \
> -responseFile NO_VALUE \
> -characterSet AL32UTF8 \
> -sysPassword V3ryStr@ng \
> -systemPassword V3ryStr@ng \
> -createAsContainerDatabase true \
> -numberOfPDBs 1 \
> -pdbName ${PDB_NAME} \
> -pdbAdminPassword V3ryStr@ng \
> -databaseType MULTIPURPOSE \
> -automaticMemoryManagement false \
> -totalMemory 800 \
> -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/cdb1.
Database Information:
Global Database Name:cdb1
System Identifier(SID):cdb1
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/cdb1/cdb1.log" for further details.

[oracle@oracle-db-19c ~]$ su -
Password:
[root@oracle-db-19c ~]# sed -i 's/:N$/:Y/g' /etc/oratab

[oracle@oracle-db-19c ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Apr 28 15:57:06 2020
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>

SQL> ALTER SYSTEM SET DB_CREATE_FILE_DEST='/u02/oradata' SCOPE=BOTH;

System altered.

SQL> ALTER PLUGGABLE DATABASE PDB1 SAVE STATE;

Pluggable database altered.

Create SystemD Service for Oracle Database 19c on Rocky Linux 8:

[root@oracle-db-19c ~]#vim /usr/lib/systemd/system/dbora.service


[Unit]
Description=Oracle Database Service
After=network.target
 
[Service]
Type=forking
ExecStart=/u01/app/oracle/product/19.3.0/dbhome_1/bin/dbstart /u01/app/oracle/product/19.3.0/dbhome_1
ExecStop=/u01/app/oracle/product/19.3.0/dbhome_1/bin/dbshut /u01/app/oracle/product/19.3.0/dbhome_1
User=oracle
TimeoutSec=300s
 
[Install]
WantedBy=multi-user.target

[root@oracle-db-19c ~]# systemctl daemon-reload

[root@oracle-db-19c ~]# systemctl enable --now dbora.service
Created symlink /etc/systemd/system/multi-user.target.wants/dbora.service â /usr/lib/systemd/system/dbora.service

[root@oracle-db-19c ~]# systemctl status dbora.service
● dbora.service - Oracle Database Service
   Loaded: loaded (/usr/lib/systemd/system/dbora.service; enabled; vendor preset: disabled)
   Active: active (running) since Sat 2021-09-04 13:56:01 KST; 3min 3s ago
  Process: 2734 ExecStop=/u01/app/oracle/product/19.3.0/dbhome_1/bin/dbshut /u01/app/oracle/product/19.3.0/dbhome_1 (code=exited, status=0/SUCCESS)
  Process: 14294 ExecStart=/u01/app/oracle/product/19.3.0/dbhome_1/bin/dbstart /u01/app/oracle/product/19.3.0/dbhome_1 (code=exited, status=0/SUCCESS)
    Tasks: 70 (limit: 100771)
   Memory: 975.7M
   CGroup: /system.slice/dbora.service
           ├─14404 ora_pmon_neo
           ├─14406 ora_clmn_neo
           ├─14408 ora_psp0_neo
           ├─14411 ora_vktm_neo
           ├─14415 ora_gen0_neo
           ├─14417 ora_mman_neo
           ├─14421 ora_gen1_neo
           ├─14424 ora_diag_neo
           ├─14426 ora_ofsd_neo
           ├─14429 ora_dbrm_neo
           ├─14431 ora_vkrm_neo
           ├─14433 ora_svcb_neo
           ├─14435 ora_pman_neo
           ├─14437 ora_dia0_neo
           ├─14439 ora_dbw0_neo
           ├─14441 ora_lgwr_neo
           ├─14443 ora_ckpt_neo
           ├─14445 ora_lg00_neo
           ├─14447 ora_smon_neo
           ├─14449 ora_lg01_neo
           ├─14451 ora_smco_neo
           ├─14453 ora_reco_neo
           ├─14455 ora_w000_neo

사용자 추가하기

sqldeveloper 툴을 통하여 system 계정으로 로그인한다

로그인 후 사용자 추가

alter session set "_ORACLE_SCRIPT"=true;
create user aaa IDENTIFIED BY "aaa!!";
GRANT CONNECT, RESOURCE, DBA TO aaa;

PDB 접속

[oracle@localhost ~]$ tnsping pdb1

vim /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/tnsnames.ora

CDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.33)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = CDB1)
    )
  )

PDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.33)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PDB1)
    )
  )
~

vim /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/listener.ora

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.33)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER=
 (SID_LIST =
  (SID_DESC =
    (SID_NAME = pdb1)
  )
 )

[root@localhost admin]# su - oracle
마지막 로그인: 금 10월 18 22:11:46 KST 2024 일시 pts/1
[oracle@localhost ~]$ lsnrctl stop

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 18-10월-2024 22:54:47

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle-db-19c.linux.com)(PORT=1521)))에 연결되었습니다
명령이 성공적으로 수행되었습니다
[oracle@localhost ~]$ lsnrctl start

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 18-10월-2024 22:54:51

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

시작 /u01/app/oracle/product/19.3.0/dbhome_1/bin/tnslsnr: 잠시만 기다리세요...

TNSLSNR for Linux: Version 19.0.0.0.0 - Production
시스템 매개변수 파일은 /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/listener.ora 입니다
/u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml (으)로 로그 메시지를 기록했습니다
리스닝이: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
리스닝이: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle-db-19c.linux.com)(PORT=1521)))에 연결되었습니다
리스너의 상태
------------------------
별칭                     LISTENER
버전                     TNSLSNR for Linux: Version 19.0.0.0.0 - Production
시작 날짜                 18-10월-2024 22:54:51
업타임                   0 일 0 시간. 0 분. 0 초
트레이스 수준            off
보안                     ON: Local OS Authentication
SNMP                     OFF리스너 매개변수 파일   /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/listener.ora
리스너 로그 파일         /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
끝점 요약 청취 중...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
서비스 요약...
"pdb1" 서비스는 1개의 인스턴스를 가집니다.
  "pdb1" 인스턴스(UNKNOWN 상태)는 이 서비스에 대해 1 처리기를 가집니다.
명령이 성공적으로 수행되었습니다

tns 정상으로 인식하는지 확인

[oracle@localhost ~]$ tnsping pdb1

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 18-10월-2024 22:11:59

Copyright (c) 1997, 2019, Oracle.  All rights reserved.

사용된 매개변수 파일:


별칭 분석을 위해 TNSNAMES 어댑터 사용
(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.33)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = PDB1)))에 접속하려고 시도하는 중
확인(0밀리초)

PDB 활성화

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on 금 10월 18 22:56:06 2024
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


다음에 접속됨:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO

pdbadmin 비밀번호 변경

[oracle@localhost ~]$ alter user pdbadmin identified by xxxxxxx;

pdb연결

[oracle@localhost ~]$ sqlplus pdbadmin@PDB1

SQL*Plus: Release 19.0.0.0.0 - Production on 금 10월 18 23:01:13 2024
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

비밀번호 입력:
마지막 성공한 로그인 시간: 금 10월 18 2024 22:16:15 +09:00

다음에 접속됨:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL>

추가)

컬럼사이즈 변경

Increase VARCHAR2, NVARCHAR2, And RAW Data Types Size | Ivica Arsov’s blog (iarsov.com)

Increase VARCHAR2, NVARCHAR2, And RAW Data Types Size

Starting with 12c we can extend the size for VARCHAR2, NVARCHAR2 and RAW data types. The modification is done by setting MAX_STRING_SIZE init parameter. Possible values are { STANDARD | EXTENDED } where:

blog.iarsov.com

MAX_STRING_SIZE (oracle.com)

Database Reference

MAX_STRING_SIZE controls the maximum size of VARCHAR2, NVARCHAR2, and RAW data types in SQL.

docs.oracle.com

참고)

https://www.centlinux.com/2020/04/install-oracle-database-19c-on-centos-8.html

Install Oracle Database 19c on CentOS 8

In this article, you will learn how to install Oracle Database 19c on CentOS 8 on-premises server in silent mode and create a multitenant database on this server. This Article Provides: What is Oracle Database 19c? : Oracle Database 19c is a multi-model da

www.centlinux.com

https://blogger.pe.kr/840/

Oracle 12c의 PDB에 dba 계정 만들기 - taeho's life blog

Oracle 12c는 Cloud Computing을 지향하는 Oracle의 첫 버전이다. 지금은 19c 버전까지 나와 있지만 (12c -> 18c -> 19c) 사실… 아직도 11g 버전, 심지어 9i 버전을 사용하는 기업이나 기관도 있다. Oracle 12c가 갖

blogger.pe.kr

https://saidulhaque.com/knowledgebase/article-52#/

How to Create a User, Grant Permissions, use user as database in Oracle

In this tutorial, we will learn how to create oracle user under PDB database and how to use the...

saidulhaque.com

'기록남기기' 카테고리의 다른 글

redmine4 설치 (0)	2022.05.18
YAML 문법 검사 사이트 (0)	2021.11.09
kafka on kubernetes (with Strimzi) (0)	2021.08.19
mariadb Galera Cluster on kubernetes (0)	2021.08.18
k8s log monitoring Loki (0)	2021.08.10

공지사항

최근에 올라온 글

최근에 달린 댓글

Total

Today

Yesterday

링크

TAG more

« 2025/05 »
일	월	화	수	목	금	토
				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

글 보관함

꿈꾸는 키드의 세계

티스토리 뷰

Oracle 19.3 on Rocky linux

'기록남기기' 카테고리의 다른 글

티스토리툴바