1.서비스위 해 방화벽 오픈
firewall-cmd --permanent --add-port=1521/tcp
2추가 패키지 설치
yum install epel-release
yum groupinstall "Development Tools"
yum install java-11-openjdk
yum install java-11-openjdk-devel
yum install libnsl #오라클 rpm 설치에 필요
yum update
=== 오라클이 사용하는 포더 생성및 권한
[root@localhost ~]# mkdir -p /app/oracle
[root@localhost ~]# chown -R oracle:dba /app
[root@localhost ~]# chmod -R 775 /app
3.설치 준비
Install an RPM package for Pre-Installation first.
a.curl public-yum.oracle.com/public-yum-ol7.repo -o /etc/yum.repos.d/public-yum-ol7.repo
b. sed -i -e "s/enabled=1/enabled=0/g" /etc/yum.repos.d/public-yum-ol7.repo
c. rpm --import yum.oracle.com/RPM-GPG-KEY-oracle-ol7
d. yum --enablerepo=ol7_latest -y install oracle-database-preinstall-18c
#oracle 18c rpm버전 다운로드 설치
rpm -Uvh oracle-database-ee-18c-1.0-1.x86_64.rpm
#오라클 rpm 설치 후 확인 변수
vi /etc/sysconfig/oracledb_ORCLCDB-18c.conf
#오라클 초기 DB 설정 (oracledb_ORCLCDB-18c 편집가능)
/etc/init.d/oracledb_ORCLCDB-18c configure
#오라클 사용자 환경 설정
su - oracle
4. 프로 파잉 편집
vi .bash_profile
umask 022
export ORACLE_SID=orcldb <== 소문자로
export ORACLE_BASE=/opt/oracle/oradata
export ORACLE_HOME=/opt/oracle/product/18c/dbhome_1
export PATH=$PATH:$ORACLE_HOME/bin
export NLS_LANG=KOREAN_KOREA.AL32UTF8
source .bash_profile
=======예외 사항
ORA-00205
===============패키지 설치후 sqlplus 가 안될때..
yum install -y libnsl
==================================
os 공유 메모리
echo "2547483648" > /proc/sys/kernel/shmmax
echo "kernel.shmmax=2547483648" >> /etc/sysctl.conf
vi /etc/sysctl.conf
# Controls the maximum shared segment size, in bytes
kernel.shmmax = 68719476736
# Controls the maximum number of shared memory segments, in pages
kernel.shmall = 10523004
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.aio-max-nr = 1048576
fs.file-max = 6815744
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048586
================================================
/sbin/sysctl -p
=======샘플 오라클 설정 파일 hared_pool_size = 123232153
memory_target=1G
processes = 150
audit_file_dest='<ORACLE_BASE>/admin/orcl/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest='<ORACLE_BASE>/fast_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest='<ORACLE_BASE>'
dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
# You may want to ensure that control files are created on separate physical
# devices
control_files = (ora_control1, ora_control2)
compatible ='11.2.0'
==== 만들 디비로 편집 initorcldb.ora 파일명
# Change '<ORACLE_BASE>' to point to the oracle base (the one you specify at
# install time)
db_name='orcldb'
shared_pool_size = 123232153
memory_target=1G
processes = 150
audit_file_dest='$ORACLE_HOME/admin/orcl/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
#diagnostic_dest='$ORACLE_HOME'
#dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
# You may want to ensure that control files are created on separate physical
# devices
control_files = ("/opt/oracle/product/18c/dbhome_1/dbs/ORACTL1.CTL","/opt/oracle/product/18c/dbhome_1/dbs/ORACTL2.CTL")
select value from v$spparameter where name = 'control_files';
alter system set control_files="/opt/oracle/orcldbuct/18c/dbhome_1/dbs/ORACTL1.CTL","/opt/oracle/orcldbuct/18c/dbhome_1/dbs/ORACTL2.CTL", scope=spfile ;
show parameter control_files
select value from v$spparameter where name='control_files';
ALTER SYSTEM SET control_files = '/opt/oracle/orcldbuct/18c/dbhome_1/dbs/ORACTL1.CTL','/opt/oracle/orcldbuct/18c/dbhome_1/dbs/ORACTL2.CTL' scope=spfile;
SQL> create spfile from pfile;
spfile 에서 pfile 만들기
SQL> create pfile from spfile;
현재 pfile인지 spfile인가 확인하기
SQL> show parameter pfile; or SQL> show parameter spfile;
VALUE값에 아무것도 없으면 pfile
VALUE값에 경로파일 있으면 spfile
27
DB 끄기
SQL> shutdown immediate;
nomount로 DB 켜기
SQL> startup nomount
현재 DB 상태 보기
SQL> select status from v$instance;
※ 참고
STATUS
-----------
STARTED → nomount 상태
MOUNTED → mount 상태
OPEN → open 상태
mount로 바꾸기
SQL> alter database mount;
open로 바꾸기
SQL> alter database open;
select value from v$spparameter where name = 'control_files';
====================================================
db 생성
sqlplus / as sysdba
create spfile from pfile;
startup nomount
mkdir //data
sqlplus / as sysdba
=============================db 생성=====================
CREATE DATABASE orcldb
USER SYS IDENTIFIED BY oracle
USER SYSTEM IDENTIFIED BY oracle
CONTROLFILE REUSE
LOGFILE GROUP 1 ('//data/redo01a.log'
,'//data/redo01b.log') SIZE 100M REUSE,
GROUP 2 ('//data/redo02a.log'
,'//data/redo02b.log') SIZE 100M REUSE
EXTENT MANAGEMENT LOCAL
DATAFILE '//data/system01.dbf' SIZE 400M REUSE AUTOEXTEND ON
SYSAUX
DATAFILE '//data/sysaux01.dbf' SIZE 200M REUSE AUTOEXTEND ON
DEFAULT TEMPORARY TABLESPACE TEMP
TEMPFILE '//data/temp01.dbf' SIZE 100M REUSE AUTOEXTEND ON
UNDO TABLESPACE UNDOTBS1
DATAFILE '//data/undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16;
=====================================================
select instance_name, status from v$instance;
db 새성후 카탈로그 생성
@?/rdbms/admin/catalog.sql
===========================리스너 파일 생성
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = orcldb)
(ORACLE_HOME = /opt/oracle/product/18c/dbhome_1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.20)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /app/oracle
===============================================
sqlnet.ora 생성
# SQLNET.ORA Network Configuration File: C:\\oracle\\ora92\\network\\admin\\sqlnet.ora
# Generated by Oracle configuration tools.
#SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
====================================================
========================= db 구동후 아카이브 설정
archive log list
select * from v$logfile;
<== 파라메터 파일 에 설정 적용
select group#, sequence#, bytes, members, status from v$log;
SQL> alter system set log_archive_dest='/opt/oracle/app/oradata/arch' scope=spfile;
SQL> alter system set log_archive_format='arch_%t_%s_%r.arc' scope=spfile
1.shutdown
2.startup mount
3 alter database archivelog <==아카이브 모드로
alter database noarchivelog; <==non 아카이브 모드로
=================================샘플 스키마 설치
alter session set "_ORACLE_SCRIPT"=true
@?/demo/schema/human_resources/hr_main.sql
//data
#백업한 Data 불러오기(imp)
SQL> create tablespace pgspace datafile '//data/pg.dbf' size 1G autoextend on next 100M;
SQL> create user pg identified by "metal1603/" default tablespace pgspace temporary tablespace temp;
SQL> grant connect, resource, dba to pg;
SQL> exit
create user pg identified by "metal1603/" default tablespace pgspace temporary tablespace temp;
#샘플(HR) 스키마 설치
SQL> alter session set "_ORACLE_SCRIPT"=true
SQL> @?/demo/schema/human_resources/hr_main.sql
1:패스워드
2:users #기본테이블스페이스
3:temp #temp테이블스페이스
4:$ORACLE_HOME/demo/schema/log
#백업한 Data 불러오기(imp)
SQL> create tablespace pgspace datafile '/opt/oracle/oradata/ORCLCDB/pg.dbf' size 1G autoextend on next 100M;
SQL> create user pg identified by "*****" default tablespace pgspace temporary tablespace temp;
SQL> grant connect, resource, dba to pg;
SQL> exit
imp userid=pg/***** file=./backup.dmp ignore=y fromuser=xxx touser=pg
@?/sqlplus/admin/pupbld.sql
@?rdbms/admin/dbmsapin.sql, rdbms/admin/prvtapin.plb
@?/rdbms/admin/dbmsapin.sql
sys유저로
@catalog,@catproc을 한번 더 돌려주면 어떨런지
@?/rdbms/admin/catproc.sql
@?/rdbms/admin/catalog.sql
==============================================인스턴스추가
1. 데이터 베이스 생성문 만들고
CREATE DATABASE myorcldb
USER SYS IDENTIFIED BY oracle
USER SYSTEM IDENTIFIED BY oracle
CONTROLFILE REUSE
LOGFILE GROUP 1 ('/data/redo01a.log'
,'/data/redo01b.log') SIZE 100M REUSE,
GROUP 2 ('/data/redo02a.log'
,'/data/redo02b.log') SIZE 100M REUSE
EXTENT MANAGEMENT LOCAL
DATAFILE '/data/system01.dbf' SIZE 400M REUSE AUTOEXTEND ON
SYSAUX
DATAFILE '/data/sysaux01.dbf' SIZE 200M REUSE AUTOEXTEND ON
DEFAULT TEMPORARY TABLESPACE TEMP
TEMPFILE '/data/temp01.dbf' SIZE 100M REUSE AUTOEXTEND ON
UNDO TABLESPACE UNDOTBS1
DATAFILE '/data/undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16;
2.초기설정 파일 생성 initmyorcldb.ora
db_name='myorcldb'
shared_pool_size = 123232153
memory_target=1G
processes = 150
audit_file_dest='$ORACLE_HOME/admin/orcl/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
#diagnostic_dest='$ORACLE_HOME'
#dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
# You may want to ensure that control files are created on separate physical
# devices
control_files = ("/data/ORACTL1.CTL","/data/ORACTL2.CTL")
compatible ='11.2.0'
==============================
setenv ORACLE_SID myorcldb
export ORACLE_SID=myorcldb
export ORACLE_SID=orcldb
확인 은 env
createmyorcldb.sql
# sqlplus '/as sysdba'
SQL> startup nomount pfile="/opt/oracle/product/18c/dbhome_1/dbs/initmyorcldb.ora";
ORACLE instance started.
이다음 디비생성
/oracle/app/product/10.2/rdbms/admin 밑에서
utlrp.sql catproc.sql catalog.sql
[출처] 오라클 인스턴스 2개 띄우기 |작성자 어퓨굿맨
--db 생서후 추가한다
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
@?/rdbms/admin/utlrp.sql
-- p 파일 생성
show parameter spfile;
create pfile from spfile;
아카이브 설정
archive log list;
select * from v$logfile;
<== 파라메터 파일 에 설정 적용
select group#, sequence#, bytes, members, status from v$log;
SQL> alter system set log_archive_dest='/data/arch' scope=spfile;
SQL> alter system set log_archive_format='arch_%t_%s_%r.arc' scope=spfile
1.shutdown
2.startup mount
3 alter database archivelog <==아카이브 모드로
alter database noarchivelog; <==non 아카이브 모드로
=================================샘플 스키마 설치
1073740616 bytes
export ORACLE_SID=myorcldb
export ORACLE_SID=orcldb
env
sqlplus /nolog
conn /as sysdba
shutdown
sqlplus "/as sysdba" << EOF
startup
EOF
sqlplus "/as sysdba" << EOF
shutdown
EOF
======================== 마지막으로 리스너 파일 수정
firewall-cmd --permanent --add-port=1522/tcp
# firewall-cmd --reload
===================================================
orcldb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.20)(PORT = 1521))
)
sid_list_orcldb =
(sid_list=
(sid_desc=
(sid_name=orcldb)
(oracle_home=/opt/oracle/product/18c/dbhome_1)
)
)
myorcldb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.20)(PORT = 1522))
)
sid_list_myorcldb =
(sid_list=
(sid_desc=
(sid_name=myorcldb)
(oracle_home=/opt/oracle/product/18c/dbhome_1)
)
)
==========================================listener.fh cnrk
lsnrctl start orcldb
lsnrctl stop orcldb
lsnrctl start myorcldb
lsnrctl stop myorcldb
lsnrctl service orcldb
lsnrctl stop orcldb