버전 비교

이전 버전 14

changes.mady.by.user 윤병훈

에 저장

비교대상

새 버전 현재

changes.mady.by.user 윤병훈

에 저장

키

  • 이 줄이 추가되었습니다.
  • 이 줄이 삭제되었습니다.
  • 서식이 변경되었습니다.


준비 작업

리스너 생성

리스너 갯수와 이름 그리고 사용할 포트에 맞게 response 파일 내의 변수를 설정하기 바랍니다.

코드 블럭
languagebash
titleListener response 파일 생성
linenumberstrue
cat > ~/netca.rsp
[GENERAL]
RESPONSEFILE_VERSION="19.0"
CREATE_TYPE="CUSTOM"
SHOW_GUI=false

[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"}
코드 블럭
languagebash
title리스너 생성
linenumberstrue
netca -silent -responsefile ~/netca.rsp
펼치기
서식 미적용
[ora19c:/home/oracle]> $ORACLE_HOME/bin/netca -silent -responsefile ~/netca.rsp

명령행 인수 분석:
    매개변수 "silent" = true
    매개변수 "responsefile" = /home/oracle/netca.rsp
명령행 인수 분석을 완료했습니다.
Oracle Net Service 구성:
프로파일 구성이 완료되었습니다.
Oracle Net 리스너 시작:
    실행 중인 리스너 제어:
      /u01/app/oracle/product/19c/db_1/bin/lsnrctl start LISTENER
    리스너 제어가 완료되었습니다.
    리스너가 성공적으로 시작되었습니다.
리스너 구성이 완료되었습니다.
Oracle Net Service 구성을 성공했습니다. 종료 코드는 다음과 같습니다. 0


코드 블럭
languagebash
title리스너 생성 결과 확인
linenumberstrue
lsnrctl status <리스너명>
펼치기
서식 미적용
[ora19c:/home/oracle]> lsnrctl status LISTENER

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 08-JAN-2024 14:22:21

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora19c)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                08-JAN-2024 14:20:24
Uptime                    0 days 0 hr. 1 min. 57 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/19c/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/ora19c/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora19c)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "orcl19c" has 1 instance(s).
  Instance "orcl19c", status READY, has 1 handler(s) for this service...
Service "orcl19cXDB" has 1 instance(s).
  Instance "orcl19c", status READY, has 1 handler(s) for this service...
The command completed successfully


DB

파일 저장 경로 생성

스크립트 생성

코드 블럭
languagebash
titleDB 생성 스크립트 추출데이터 파일, FRA 경로 생성 및 권한 변경
linenumberstrue
dbcamkdir -silent -generateScripts -gdbName <글로벌DB명> -templateName New_Database.dbt -scriptDest <스크립트 저장 경로> -createAsContainerDatabase <CDB 여부> -databaseConfigType <DB 타입> -memoryMgmtType <메모리 관리 타입> -totalMemory <DB에 할당할 메모리 크기(MB)> -characterSet <문자셋> -nationalCharacterSet <National 문자셋> -storageType <스토리지 타입> -redoLogFileSize <Redo Log 크기(MB)> -databaseType <DB 용도> -datafileDestination <db_create_file_dest> -recoveryAreaDestination <db_recovery_file_dest> -recoveryAreaSize <FRA 영역 크기(MB)> -emConfiguration <EM 설정> -listeners <리스너명>p /oradata/ORCL19C/online  # 데이터 파일을 저장할 경로
mkdir -p /FRA/ORCL19C/online      # FRA 설정 경로
chown -R oracle:oinstall /oradata
chown -R oracle:oinstall /FRA
펼치기
서식 미적용
[root@ora19c ~]# mkdir -p /oradata/ORCL19C/online
[root@ora19c ~]# mkdir -p /FRA/ORCL19C/online
[root@ora19c ~]# chown -R oracle:oinstall /oradata
[root@ora19c ~]# chown -R oracle:oinstall /FRA


DB 생성 스크립트 생성

[ora19c:/home/oracle]> 
dbca -silent -generateScripts -gdbName
orcl19c
<글로벌DB명> -templateName New_Database.dbt -scriptDest
~/dbca_scripts
<스크립트 저장 경로> -createAsContainerDatabase
false
<CDB 여부> -databaseConfigType <DB
SINGLE
타입> -memoryMgmtType
AUTO_SGA -totalMemory 2000
<메모리 관리 타입> -totalMemory <DB에 할당할 메모리 크기(MB)> -characterSet
AL32UTF8 
<문자셋> -nationalCharacterSet
AL16UTF16
<National 문자셋> -storageType
FS
<스토리지 타입> -redoLogFileSize
200
 <Redo Log 크기(MB)> -databaseType
MULTIPURPOSE
<DB 용도> -datafileDestination
/oradata
<db_create_file_dest> -recoveryAreaDestination
/FRA
<db_recovery_file_dest> -recoveryAreaSize
20000
<FRA 영역 크기(MB)> -emConfiguration
none
<EM 설정> -listeners
LISTENER DB 작업 준비 5% 완료 Oracle 인스턴스 생성 및 시작 중 6% 완료 9% 완료 데이터베이스 파일 생성 중 10% 완료 14% 완료 데이터 딕셔너리 뷰 생성 중 15% 완료 18% 완료 20% 완료 22% 완료 23% 완료 27% 완료 Oracle JVM 34% 완료 41% 완료 48% 완료 50% 완료 Oracle Text 53% 완료 55% 완료 Oracle Multimedia 68% 완료 Oracle OLAP 73% 완료 Oracle Spatial 74% 완료 82% 완료 데이터베이스 생성 완료 중 84% 완료 85% 완료 86% 완료 사후 구성 작업 실행 중 100% 완료 스크립트 "/home/oracle/dbca_scripts" 생성을 성공했습니다. 자세한 내용은 로그 파일 "/u01/app/oracle/cfgtoollogs/dbca/orcl19c/orcl19c0.log"을(를) 참조하십시오.
<리스너명>
코드 블럭
languagebash
titleDB 생성 스크립트 추출
linenumberstrue
펼치기
서식 미적용


펼치기


서식 미적용
[ora19c:/home/oracle]> dbca -silent -generateScripts -gdbName orcl19c -templateName New_Database.dbt -scriptDest ~/dbca_scripts -createAsContainerDatabase false -databaseConfigType SINGLE -memoryMgmtType AUTO_SGA -totalMemory 2000 -characterSet AL32UTF8  -nationalCharacterSet AL16UTF16 -storageType FS -redoLogFileSize 200 -databaseType MULTIPURPOSE -datafileDestination /oradata -recoveryAreaDestination /FRA -recoveryAreaSize 20000 -emConfiguration none -listeners LISTENER
DB 작업 준비
5% 완료
Oracle 인스턴스 생성 및 시작 중
6% 완료
9% 완료
데이터베이스 파일 생성 중
10% 완료
14% 완료
데이터 딕셔너리 뷰 생성 중
15% 완료
18% 완료
20% 완료
22% 완료
23% 완료
27% 완료
Oracle JVM
34% 완료
41% 완료
48% 완료
50% 완료
Oracle Text
53% 완료
55% 완료
Oracle Multimedia
68% 완료
Oracle OLAP
73% 완료
Oracle Spatial
74% 완료
82% 완료
데이터베이스 생성 완료 중
84% 완료
85% 완료
86% 완료
사후 구성 작업 실행 중
100% 완료
스크립트 "/home/oracle/dbca_scripts" 생성을 성공했습니다.
자세한 내용은 로그 파일 "/u01/app/oracle/cfgtoollogs/dbca/orcl19c/orcl19c0.log"을(를) 참조하십시오.



스크립트 수정

코드 블럭
languagebash
title파라미터(init.ora) 수정
nls_language="AMERICAN" # 변경
audit_trail=none # 변경
# db_create_online_log_dest_1="/oradata" # 추가
# db_create_online_log_dest_2="/FRA" #추가


펼치기


서식 미적용
##############################################################################
# Copyright (c) 1991, 2013 by Oracle Corporation
##############################################################################

###########################################
# NLS
###########################################
nls_language="AMERICAN"
nls_territory="KOREA"

###########################################
# SGA Memory
###########################################
sga_target=1500m

###########################################
# Shared Server
###########################################
dispatchers="(PROTOCOL=TCP) (SERVICE=orcl19cXDB)"

###########################################
# Miscellaneous
###########################################
compatible=19.0.0
diagnostic_dest=/u01/app/oracle

###########################################
# Network Registration
###########################################
#local_listener=LISTENER_ORCL19C

###########################################
# Database Identification
###########################################
db_name="orcl19c"

###########################################
# Security and Auditing
###########################################
audit_file_dest="/u01/app/oracle/admin/orcl19c/adump"
audit_trail=none
remote_login_passwordfile=EXCLUSIVE

###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_tablespace=UNDOTBS1

###########################################
# Sort, Hash Joins, Bitmap Indexes
###########################################
pga_aggregate_target=500m

###########################################
# Cache and I/O
###########################################
db_block_size=8192

###########################################
# Cursors and Library Cache
###########################################
open_cursors=300

###########################################
# File Configuration
###########################################
control_files=("/oradata/ORCL19C/control01.ctl", "/FRA/ORCL19C/control02.ctl")
db_recovery_file_dest="/FRA"
db_recovery_file_dest_size=20000m

###########################################
# Processes and Sessions
###########################################
processes=300



코드 블럭
languagebash
title<인스턴스 명>.sql 에서 미설치 컴포넌트 주석 처리
#@

스크립트 수정

코드 블럭
languagebash
title파라미터(init.ora) 수정
nls_language="AMERICAN" # 변경
audit_trail=none # 변경
db_create_online_log_dest_1="/oradata" # 추가
db_create_online_log_dest_2="/FRA" #추가
펼치기
서식 미적용
##############################################################################
# Copyright (c) 1991, 2013 by Oracle Corporation
##############################################################################

###########################################
# NLS
###########################################
nls_language="AMERICAN"
nls_territory="KOREA"

###########################################
# SGA Memory
###########################################
sga_target=1500m

###########################################
# Shared Server
###########################################
dispatchers="(PROTOCOL=TCP) (SERVICE=orcl19cXDB)"

###########################################
# Miscellaneous
###########################################
compatible=19.0.0
diagnostic_dest=/u01/app/oracle

###########################################
# Network Registration
###########################################
#local_listener=LISTENER_ORCL19C

###########################################
# Database Identification
###########################################
db_name="orcl19c"

###########################################
# Security and Auditing
###########################################
audit_file_dest="/u01/app/oracle/admin/orcl19c/adump"
audit_trail=none
remote_login_passwordfile=EXCLUSIVE

###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_tablespace=UNDOTBS1

###########################################
# Sort, Hash Joins, Bitmap Indexes
###########################################
pga_aggregate_target=500m

###########################################
# Cache and I/O
###########################################
db_block_size=8192

###########################################
# Cursors and Library Cache
###########################################
open_cursors=300

###########################################
# File Configuration
###########################################
control_files=("/oradata/ORCL19C/control01.ctl", "/FRA/ORCL19C/control02.ctl")
db_recovery_file_dest="/FRA"
db_recovery_file_dest_size=20000m
db_create_online_log_dest_1="/oradata"
db_create_online_log_dest_2="/FRA"

###########################################
# Processes and Sessions
###########################################
processes=300
코드 블럭
languagebash
title<인스턴스 명>.sql 에서 미설치 컴포넌트 주석 처리
#@/home/oracle/dbca_scripts/context.sql
#@/home/oracle/dbca_scripts/ordinst.sql
#@/home/oracle/dbca_scripts/interMedia.sql
#@/home/oracle/dbca_scripts/cwmlite.sql
#@/home/oracle/dbca_scripts/spatial.sql
set verify off ACCEPT sysPassword CHAR PROMPT 'Enter new password for SYS: ' HIDE ACCEPT systemPassword CHAR PROMPT 'Enter new password for SYSTEM: ' HIDE host /u01/app/oracle/product/19c/db_1/bin/orapwd file=/u01/app/oracle/product/19c/db_1/dbs/orapworcl19c force=y format=12 @/home/oracle/dbca_scripts/CreateDB.sql @
/home/oracle/dbca_scripts/
CreateDBFiles
context.sql
@
#@/home/oracle/dbca_scripts/
CreateDBCatalog
ordinst.sql
@
#@/home/oracle/dbca_scripts/
JServer
interMedia.sql
#@/home/oracle/dbca_scripts/
context
cwmlite.sql
#@/home/oracle/dbca_scripts/spatial.sql
펼치기
서식 미적용


펼치기


서식 미적용
set verify off
ACCEPT sysPassword CHAR PROMPT 'Enter new password for SYS: ' HIDE
ACCEPT systemPassword CHAR PROMPT 'Enter new password for SYSTEM: ' HIDE
host /u01/app/oracle/product/19c/db_1/bin/orapwd file=/u01/app/oracle/product/19c/db_1/dbs/orapworcl19c force=y format=12
@/home/oracle/dbca_scripts/CreateDB.sql
@/home/oracle/dbca_scripts/CreateDBFiles.sql
@ordinst.sql
#@/home/oracle/dbca_scripts/interMedia.sql
#@/home/oracle/dbca_scripts/cwmlite.sql
#@/home/oracle/dbca_scripts/spatial.sql
@/home/oracle/dbca_scripts/lockAccount.sql
@/home/oracle/dbca_scripts/postDBCreation.sqlCreateDBCatalog.sql
@/home/oracle/dbca_scripts/JServer.sql
#@/home/oracle/dbca_scripts/context.sql
#@/home/oracle/dbca_scripts/ordinst.sql
#@/home/oracle/dbca_scripts/interMedia.sql
#@/home/oracle/dbca_scripts/cwmlite.sql
#@/home/oracle/dbca_scripts/spatial.sql
@/home/oracle/dbca_scripts/lockAccount.sql
@/home/oracle/dbca_scripts/postDBCreation.sql


코드 블럭
titleDB 생성(CreateDB.sql) 스크립트 수정
linenumberstrue
DATAFILE '/oradata/ORCL19C/system01.dbf' SIZE 1000M REUSE AUTOEXTEND ON NEXT  10240K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL                  -- 파일 크기 수정
SYSAUX DATAFILE '/oradata/ORCL19C/sysaux01.dbf' SIZE 1000M REUSE AUTOEXTEND ON NEXT  10240K MAXSIZE UNLIMITED                                   -- 파일 크기 수정
SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/oradata/ORCL19C/temp01.dbf' SIZE 500M REUSE AUTOEXTEND ON NEXT  640K MAXSIZE UNLIMITED   -- 파일 크기 수정
SMALLFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE  '/oradata/ORCL19C/undotbs01.dbf' SIZE 1000M REUSE AUTOEXTEND ON NEXT  5120K MAXSIZE UNLIMITED    -- 파일 크기 수정
LOGFILE GROUP 1 ('/oradata/ORCL19C/online/redo01.log','/FRA/ORCL19C/online/redo01.log') SIZE 200M,                                              -- Redo 로그 이중화 추가
GROUP 2 ('/oradata/ORCL19C/online/redo02.log','/FRA/ORCL19C/online/redo02.log') SIZE 200M,                                                      -- Redo 로그 이중화 추가
GROUP 3 ('/oradata/ORCL19C/online/redo03.log','/FRA/ORCL19C/online/redo03.log') SIZE 200M                                                       -- Redo 로그 이중화 추가
펼치기
코드 블럭
titleDB 생성(CreateDB.sql) 스크립트 수정
linenumberstrue
서식 미적용
SET VERIFY OFF
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool /home/oracle/dbca_scripts/CreateDB.log append
startup nomount pfile="/home/oracle/dbca_scripts/init.ora";
CREATE DATABASE "orcl19c"
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
DATAFILE '/oradata/ORCL19C/system01.dbf' SIZE 1000M REUSE AUTOEXTEND ON NEXT  10240K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/oradata/ORCL19C/sysaux01.dbf' SIZE 1000M REUSE AUTOEXTEND ON NEXT  10240K MAXSIZE UNLIMITED
SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/oradata/ORCL19C/temp01.dbf' SIZE 500M REUSE AUTOEXTEND ON NEXT  640K MAXSIZE UNLIMITED
SMALLFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE  '/oradata/ORCL19C/undotbs01.dbf' SIZE
500M
1000M REUSE AUTOEXTEND ON NEXT  5120K MAXSIZE UNLIMITED
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
LOGFILE GROUP 1 ('/oradata/ORCL19C/online/redo01.log','/FRA/ORCL19C/online/redo01.log') SIZE 200M,
GROUP 2 ('/oradata/ORCL19C/online/redo02.log','/FRA/ORCL19C/online/redo02.log') SIZE 200M,
GROUP 3 ('/oradata/ORCL19C/online/redo03.log','/FRA/ORCL19C/online/redo03.log') SIZE 200M
USER SYS IDENTIFIED BY "&&sysPassword" USER SYSTEM IDENTIFIED BY "&&systemPassword";
spool off


DB 생성

스크립트 실행

코드 블럭
languagebash
titleDB 생성 스크립트 수행 : 설치 노드 최종 수행
~/dbca_scripts/<인스턴스명>.sh


펼치기

dump 파일 경로와 파라미터 파일 생성 후 database 생성

서식 미적용
[ora19c:/home/oracle]> ~/dbca_scripts/orcl19c.sh
/etc/oratab에 추가해야 할 항목: orcl19c:/u01/app/oracle/product/19c/db_1:Y

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jan 8 14:26:03 2024
Version 19.21.0.0.0

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

Enter new password for SYS:
Enter new password for SYSTEM:

Enter password for SYS:

Connected to an idle instance.
SQL> spool /home/oracle/dbca_scripts/CreateDB.log append
SQL> startup nomount pfile="/home/oracle/dbca_scripts/init.ora";
ORACLE instance started.

Total System Global Area 1577054648 bytes
Fixed Size                  8925624 bytes
Variable Size             385875968 bytes
Database Buffers         1174405120 bytes
Redo Buffers                7847936 bytes
SQL> CREATE DATABASE "orcl19c"
  2  MAXINSTANCES 8
  3  MAXLOGHISTORY 1
  4  MAXLOGFILES 16
  5  MAXLOGMEMBERS 3
  6  MAXDATAFILES 100
  7  DATAFILE '/oradata/ORCL19C/system01.dbf' SIZE 1000M REUSE AUTOEXTEND ON NEXT  10240K MAXSIZE UNLIMITED
  8  EXTENT MANAGEMENT LOCAL
  9  SYSAUX DATAFILE '/oradata/ORCL19C/sysaux01.dbf' SIZE 1000M REUSE AUTOEXTEND ON NEXT  10240K MAXSIZE UNLIMITED
 10  SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/oradata/ORCL19C/temp01.dbf' SIZE 500M REUSE AUTOEXTEND ON NEXT  640K MAXSIZE UNLIMITED
 11  SMALLFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE  '/oradata/ORCL19C/undotbs01.dbf' SIZE 500M1000M REUSE AUTOEXTEND ON NEXT  5120K MAXSIZE UNLIMITED
 12  CHARACTER SET AL32UTF8
 13  NATIONAL CHARACTER SET AL16UTF16
 14  LOGFILE LOGFILE GROUP 1 ('/oradata/ORCL19C/online/redo01.log','/FRA/ORCL19C/online/redo01.log') SIZE 200M,
 15 15 GROUP  GROUP 2 ('/oradata/ORCL19C/online/redo02.log','/FRA/ORCL19C/online/redo02.log') SIZE 200M,
 16 16 GROUP  GROUP 3 ('/oradata/ORCL19C/online/redo03.log','/FRA/ORCL19C/online/redo03.log') SIZE 200M
 17 17  USER SYS IDENTIFIED BY "&&sysPassword" USER SYSTEM IDENTIFIED BY "&&systemPassword";

...... << 생략 >> ......

SQL> SET serveroutput off
SQL>
SQL>
SQL> Rem =====================================================================
SQL> Rem Run component validation procedure
SQL> Rem =====================================================================
SQL>
SQL> SET serveroutput on
SQL> EXECUTE dbms_registry_sys.validate_components;

PL/SQL procedure successfully completed.

SQL> SET serveroutput off
SQL>
SQL>
SQL> Rem ===========================================================================
SQL> Rem END utlrp.sql
SQL> Rem ===========================================================================
SQL> select 'utlrp_end: ' || to_char(sysdate, 'HH:MI:SS') from dual;

'UTLRP_END:'||TO_CH
-------------------
utlrp_end: 02:41:46

1 row selected.

SQL> select comp_id, status from dba_registry;

COMP_ID                        STATUS
------------------------------ --------------------------------------------
CATALOG                        VALID
CATPROC                        VALID
RAC                            OPTION OFF
JAVAVM                         VALID
XML                            VALID
CATJAVA                        VALID
XDB                            VALID
OWM                            VALID

8 rows selected.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> connect "SYS"/"&&sysPassword" as SYSDBA
Connected to an idle instance.
SQL> startup ;
ORACLE instance started.

Total System Global Area 1577054648 bytes
Fixed Size                  8925624 bytes
Variable Size             385875968 bytes
Database Buffers         1174405120 bytes
Redo Buffers                7847936 bytes
Database mounted.
Database opened.
SQL> spool off
SQL> exit;
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.21.0.0.0