복습
💡 oracle dba의 기술
1. Oracle SQL
2. Oracle 관리 : 오라클 아키텍쳐
3. Oracle 백업
4. Oracle 튜닝(SQL튜닝+서버튜닝)
5. RAC와 DATA GUARD
6. 엑사 데이터
오라클 데이터베이스를 구성하는 파일들
인스턴스 데이터베이스 1. data file 2. redo log file 3. archive log file 5. parameter file 6. password file 7. alert log file, trace file 8. 백업파일
parameter file 복습
💡 오라클 인스턴스의 구조 정보와 오라클 환경 셋팅 정보를 담고있는 파일
1. pfile : text file <- vi 편집기로 수정
2. spfile : binary file <- alter system 명령어
💡 오라클 설치 이후에 개발 dba가 반드시 수정해줘야 하는 파라미터들
1. db_files : database 에서 생성할 수 있는 파일들의 최대갯수(200개 -> 3000개)
2. process : database에서 생성할 수 있는 프로세서의 최대갯수 (150개 -> 500개)
3. memory_target : 오라클 인스턴스 메모리 사이즈
4. memory_max_target : 오라클 인스턴스 메모리 최대 사이즈
➡️ memory_target, memory_max_target는 갯수가 아니라 사이즈이다. 그래서 500M가 아닌 500이라고 쓰면 500 바이트라고 된다.
1024 byte -> 1kb
1024 kb -> 1mb
1024 mb -> 1gb
1024 gb -> 1tera byte
1024 tera byte -> 1 peta byte
1024 peta byte -> 1 exa byte
오라클에서 ASM을 엄청 밀다보니 스토리지를 ASM으로 관리하는 서버들이 많다.
파라미터 파일을 ASM스토리지에 두게 되면 관리가 불편하다 그래서 파일 시스템쪽으로 가져와야 한다.
SQL> create pfile from spfile;이렇게 하니 pfile(initorcl.ora)가 $ORACLE_HOME/dbs 밑에 생성되었습니다. pfile로 오라클을 관리하는 것 보다는 spfile로 관리하는게 훨씬 쉽다. 그래서 spfile을 생성했다 !
SQL> create spfile from pfile;
$ORACLE_HOME/dbs 밑에 initorcl.ora 파일로 spfileorcl.ora을 같은 위치에 생성한다. pfile(백업용)
spfile(운영용)
문제 파라미터 파일중 pfile인 initorcl.ora파일을 메모장으로 열어서 그 내용을 메일로 보내기
[orcl:~]$ cd $ORACLE_HOME/dbs
[orcl:dbs]$ ls -rlt
합계 36
.
.
.
-rw-r----- 1 oracle dba 2560 9월 11 17:21 spfileorcl.ora
-rw-r----- 1 oracle oinstall 952 9월 11 17:33 initorcl.ora
초기화 파라미터 : 예제
실습1. 컨트롤 파일의 위치와 이름을 확인하시오
SQL> select name, value, issys_modifiable
from v$parameter
where name ='control_files';
-- value(두개가 실시간으로 동시에 쓰여지고있다.)
+DATA/orcl/controlfile/current.260.796857737, # 원본
+FRA/orcl/controlfile/current.256.796857739 # 백업본 ✅ issys_modifiable가 false 이므로 controlfile의 위치를 변경하고 싶어서 이 파라미터를 수정해야 한다면, 파라미터값을 수정하고 db를 내렸다 올려야 합니다.
실습2. 현재 database의 데이터의 최소 저장단위인 block의 크기를 확인하기
SQL> select name, value, issys_modifiable
from v$parameter
where name ='db_block_size';
✅ issys_modifiable가 false 이므로 값을 변경하려면 db를 내렸다 올려야 합니다.
회사에는 두가지 서버가 있다.
OLTP서버 Data Warehouse서버
db block size 8kb db block size 16kb, 64kb
↓ ↓
실시간 주문 데이터 저번달에 가장 많이 팔린 상품이 무엇?
SQL SQLOLTP서버 는 오늘, 한시간된 애들이라 가볍고 Data Warehouse서버는 10년치 데이터가 담겨있어서 무겁다.
실습3. db블럭 사이즈를 기존 사이즈의 2배로 늘리기
SQL> select 8192*2 from dual; #수정할 사이즈확인
9182*2
----------
18364
SQL> show parameter spfile #pfile, spfile 어떤건지 확인
SQL> alter system set db_block_size=16384 scope=spfile;
-- ERROR at line 1:
-- ORA-32017:
-- ORA-32016:위 해결 x
🤔 db_block_size를 변경하려면 2군데를 변경해주어야 한다.
1. 파라미터 파일
2. 컨트롤 파일
- 이 실습은 컨트롤 파일을 다시 생성하는 뒷단원에서 실습!!!
DB_CHACHE_SIZE
💡 db buffer cache 의 사이즈를 조정하는 것.
실습4. db_cache_size는 자동 조절이 되는 사이즈이다. 즉 memory_target 사이즈 내에서 자동으로 조절되는 사이즈이다. 현재 db_cache_size가 어떻게 셋팅되어 있는지 확인해보기!
SQL> @/home/oracle/sga.sql
*** Current parameter settings ***
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 500M
sga_target big integer 0
*** SGA Dynamic Component Size Information***
COMPONENT CURRENT_SIZE MIN_SIZE
---------------------- --------------- ---------------
shared pool 344M 344M
large pool 4M 4M
java pool 4M 4M
DEFAULT buffer cache 20M 20M
*** Current parameter settings in V$PARAMETER ***
NAME VALUE ISDEFAULT
-------------------- -------------------- ---------
shared_pool_size 0 TRUE
large_pool_size 0 TRUE
java_pool_size 0 TRUE
db_cache_size 0 TRUE
실습5. spfile에서 db_cache_size의 값이 무엇인지 확인
select value, issys_modifiable
from v$parameter
where name='db_cache_size';
✅IMMEDIATE라서 db를 내렸다 올리지 않아도 바로 수정이 가능하다!
실습6. db_cache_size를 80M로 늘리기(spfile말고 인스턴스에만 적용하기)
alter system set db_cache_size=80m scope=memory;
#System altered.
select value, issys_modifiable
from v$parameter
where name='db_cache_size';SQL> @sga.sql
*** Current parameter settings ***
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 500M
sga_target big integer 0
*** SGA Dynamic Component Size Information***
COMPONENT CURRENT_SIZE MIN_SIZE
---------------------- --------------- ---------------
shared pool 284M 284M
large pool 4M 4M
java pool 4M 4M
DEFAULT buffer cache 80M ★ 20M
# 80M로 늘어나면서 다른 메모리의 사이즈를 줄였다.
*** Current parameter settings in V$PARAMETER ***
NAME VALUE ISDEFAULT
-------------------- -------------------- ---------
shared_pool_size 0 TRUE
large_pool_size 0 TRUE
java_pool_size 0 TRUE
db_cache_size 83886080 TRUE실습7. 그럼 이상태에서 DB를 내렸다 올리고 sga.sql을 수행해서 어떻게 달라졌는지 확인
SQL> shutdown immediate
SQL> startup
SQL> @sga.sqlNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 500M
sga_target big integer 0
*** SGA Dynamic Component Size Information***
COMPONENT CURRENT_SIZE MIN_SIZE
---------------------- --------------- ---------------
shared pool 284M 284M
large pool 4M 4M
java pool 4M 4M
DEFAULT buffer cache 80M 80M
*** Current parameter settings in V$PARAMETER ***
NAME VALUE ISDEFAULT
-------------------- -------------------- ---------
shared_pool_size 0 TRUE
large_pool_size 0 TRUE
java_pool_size 0 TRUE
db_cache_size 0 TRUE문제 db cache size를 다시 72m로 조정하기
alter system set db_cache_size=72m scope=memory;
select value, issys_modifiable
from v$parameter
where name='db_cache_size';
SQL> select value, issys_modifiable
from v$parameter
where name='db_cache_size';
VALUE ISSYS_MOD
-------------------- ---------
0 IMMEDIATE
SQL> @sga.sql
*** Current parameter settings ***
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 500M
sga_target big integer 0
*** SGA Dynamic Component Size Information***
COMPONENT CURRENT_SIZE MIN_SIZE
---------------------- --------------- ---------------
shared pool 276M 276M
large pool 4M 4M
java pool 4M 4M
DEFAULT buffer cache 72M 72M
*** Current parameter settings in V$PARAMETER ***
NAME VALUE ISDEFAULT
-------------------- -------------------- ---------
shared_pool_size 0 TRUE
large_pool_size 0 TRUE
java_pool_size 0 TRUE
db_cache_size 0 TRUE✅ 답: 메모리 사이즈가 자동조절 되고 있으므로 줄이는것은 어렵다.
초기화 파라미터 예제
💡 SGA + PGA를 합쳐놓은게 메모리 타겟. memory_target만 설정하면 오라클이 알아서 sga영역과 pga영역의 사이즈를 자동 조절합니다. memory_target의 사이즈는 os 전체 메모리 사이즈의 40% ~ 50% 사이로 설정하는것을 오라클에서 권장하고 있다.
실습1. 리눅스 가상 머신의 메모리 사이즈를 4gb로 늘린다.
1. 시스템 전원 끄기 한다.
2. oracle vm 에서 설정 -> 시스템 -> 메모리 사이즈를 4gb로 늘린다.
3. 11g vm을 다시 시작
4. oracle유저에서 top을 실행합니다. Mem: 3631912k total
실습2. os메모리 사이즈의 40% 정도로 memory_target_size를 늘리시오! (1420m로)
# KB -> MB로 바꾸기
SQL> select 3631912/1024 from dual;
3631912/1024
------------
3546.78906
# MB에서 0.4를 곱했다.
SQL> select 3546.78906 * 0.4 from dual;
3546.78906*0.4
--------------
1418.71562 #1420M 정도로 잡으면 40%이다.SQL> alter system set memory_max_target=1420m scope=spfile; SQL> shutdown immediate SQL> startupSQL> alter system set memory_target=1420m scope=both; SQL> shutdown immediate SQL> startupSQL> show parameter memory_target NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ memory_target big integer 1424M
💡 dba를 위한 tip
위 작업을 하다보니, 오라클 접속하는것과 os에서 이동하는것이 명령어가 너무 길어서 작업속도가 느리다. alias 다음 3가지를 만들어놓고 .bash_profile에 추가하기 !
alias sys="sqlplus / as sysdba"
alias scott="sqlplus scott/tiger"
alias dbs="cd /u01/app/oracle/product/11.2.0/dbhome_1/dbs"
$ cd
$ vi .bash_profile #열고 맨 아래에 위 내용 추가!
$ source .bash_profileASSM(Automatic Shared Memory Management)에 영향을 받는 메모리 영역
💡 SGA_TARGET은 모든 SGA 구성 요소의 전체 크기를 지정합니다. SGA_TARGET을 지정하는
경우 다음 메모리 풀의 크기가 자동으로 지정됩니다.
자동으로 사이즈가 알아서 조정되는 메모리 영역
• db byffer cache(DB_CACHE_SIZE)
• Shared pool(SHARED_POOL_SIZE)
• Large pool(LARGE_POOL_SIZE)
• Java pool(JAVA_POOL_SIZE)
• Streams pool(STREAMS_POOL_SIZE)
ASSM(Automatic Shared Memory Management)에 영향을 받지 않는 메모리 영역
• 리두 로그 버퍼(redo log buffer) <-ocp시험문제
• keep buffer pool
• 기타 고정된 SGA영역들
다른 중요 파라미터
1. pga_aggregate_target
SQL> show parameter pga
✅ memory_target = sga_target + pga_aggregate_target
memory_target만 dba가 설정해놓으면 sga_target, pga_aggregate_target size를 오라클이 알아서 사이즈 관리를 하겠다는 것 !
os의 메모리의 40 ~ 50%사이로 메모리타겟 사이즈만 셋팅해주면 된다.
2. shared pool 사이즈 결정하는 shared_pool_size 파라미터
💡 shared pool의 역할? SQL의 파싱으로 인한 resoure(자원) 사용을 최소화 하기 위한 메모리 영역. 한번 Parsing한 데이터를 shared pool에 올려둡니다.
실습1. shared pool의 사이즈를 확인하기
SQL> show parameter shared_pool
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_reserved_size big integer 8M
shared_pool_size big integer 0
# shared_pool_size가 0이면, 오라클에 의해서 이 영역이 자동 관리되고 있다 라는 뜻이다. 만약 현재 shared pool 사이즈를 확인하고싶다면
@sga.sql
3. undo_management
undo data관리를 오라클이 자동으로 하게 할지 수동으로 하게 할지를 결정하는 파라미터
값 : auto (자동으로 관리)
값 : manual (수동으로 관리)
✔️ redo vs undo의 차이?
- redo -> (re+do) 다시 작업하다.
- undo -> 취소하다.
redo data : 복구를 하기 위해 필요한 data
undo data : rollback을 하기 위해 필요한 data
문제 오라클이 undo관리를 자동으로 하는지 수동으로 하는지 확인하기
SQL> show parameter undo;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1✅ 이렇게 자동으로 undo data를 관리하게 해놔야 ORA-01555 snap shot too old 에러를 피할 수 있다. 그런데 일부러 undo_managment를 manual로 해주어야 할 때가 있다. 언제냐면 undo data가 굉장히 많이 발생하는 SQL을 수행하기 전에 해줘야 한다.
대량의 데이터를 한번에 UPDATE하는 경우에 UPDATE가 안되고 자꾸 에러가 나면 수동으로 설정해주고 작업을 해주어야 한다.
초기화 파라미터 값 변경
1. 정적 파라미터 : pfile, spfile에서만 변경이 가능하고 db를 다시 재시작해야 적용이 되는 파라미터이다.
예) alter system set db_files=2000 scope=spfile;2. 동적 파라미터 : db를 재시작하지 않아도 바로 적용 가능한 파라미터
예) alter system set db_file_multiblock_read_count=128 scope=memory;💡 파라미터 값 적용 범위 2가지 (system, session 구분!)
1. db전체에 영향을 줌예) alter system set db_file_multiblock_read_count=128 scope=memory;2. 접속한 세션에만 영향을 주는 것
예) alter session set db_file_multiblock_read_count=128 scope=memory;✅ 개인적인건 세션, 모든 디비에 해야겠다 하는건 system
실습3. 날짜 형식을 변경하는 실험을 다음과 같이 수행하기
1. scott으로 접속해서 날짜 형식을 mon-dd-yyyy로 세션 레벨에서 변경하기
alter session set nls_date_format='mon-dd-yyyy';
select hiredate from emp;
- 다른 창에서 scott으로 접속해서 아래의 SQL수행
문제 날짜 형식을 mon-dd-yyyy로 설정하는 것을 db 전체에 접속하는 세션에게 영향을 미치도록 설정하기!
alter system set nls_date_format='mon-dd-yyyy' scope=spfile;- 아래의 파라미터 값을 시스템레벨로 설정하면 어떻게 되는지 확인하기
SQL> alter system set sec_max_failed_login_attempts=2
comment='Reduce from 10 for tighter security.' scope=spfile;
System altered.디비 내렸다 올리고 scott으로 두번 로그인 실패해보기
➡️ 영향을 안받은 것 보니 사용되지 않는 파라미터같다.
pfile, spfile의 차이점 테스트 (19c ocp시험문제)
-
pfile은 text형태의 파라미터 파일이고, ⭐ alter system 명령어로 안의 내용을 수정할 수는 없다. 반드시 vi편집기로 열어서 수정해야한다. 그리고 변경된 pfile의 내용을 인스턴스에 반영하려면 반드시 db를 내렸다가 올려야한다. pfile로 인스턴스 구성하고 다음과 같이 alter system 명령어로 파라미터를 수정하면 인스턴스에만 적용되고, pfile에는 적용 안된다.
-
spfile은 binary형태의 파라미터 파일로 alter system 명령어로 spfile안의 내용을 수정할 수 있다. 그리고 alter system 명령어의 scope 옵션으로 다음과 같이 다 변경이 가능하다.
scope=spfile : spfile에만 적용
scope=memory : 인스턴스에 바로 적용
scope=both : spfile, 인스턴스 둘다 적용그런데 만약 다음과 같이 scope옵션을 생략한다면 default가 scope=both이다.
alter system set memory_target=1024m;pfile로 인스턴스를 올리고 파라미터값 수정해보기
1. pfile로 인스턴스를 올리기 위해 db를 shutdown immediate 하기.
2. $ORACLE_HOME/dbs 밑에 가서 backup 이라는 디렉토리 만들고 spfileorcl.ora파일을 백업 디렉토리 밑으로 이동시키기
[orcl:~]$ dbs
[orcl:dbs]$ mkdir backup
[orcl:dbs]$ mv spfileorcl.ora ./backup/spfileorcl.ora
[orcl:dbs]$ cd backup
[orcl:backup]$ ls
spfileorcl.ora3. pfile을 열고 맨 아래의 ASM쪽 spfile의 위치를 지정한 파라미터를 주석으로 막기
[orcl:~]$ dbs
[orcl:dbs]$ vi initorcl.ora
4. db 올립니다.
5. spfile로 인스턴스가 구성되었는지 pfile로 인스턴스가 구성되었는지 확인하기
show parameter spfile
- pfile로 인스턴스를 구성하고 아래와 같이 파라미터를 수정하면 pfile과 인스턴스 중에 어디에 내용이 변경되는가?
alter system set db_cache_size=20m;
✅ pfile로 인스턴스가 구성되었기 때문에 위와같이 명령어를 수행하면, pfile은 text 파일이라서 반영이 안되고 그냥 인스턴스 메모리에만 반영이 된다.
$vi initoracl.ora를 열어보면 db_cache_size가 20m로 반영이 안되었다.
밑에 두개 2g가 셋팅되어있다는 것은 자동 공유 메모리 관리를 안하겠다 라는 것 !
A. 오답.
B. 오답. 피파일 안은 변경 안된다.
C. 오답. 실패 안된다.
D. 정답. 인스턴스를 다시 재시작했을 때 지속되지 않을 것이다.
➡️ 단, 인스턴스는 여러개 관리가 가능하다.
데이터베이스 시작 및 종료를 EM을 이용해서 버튼클릭으로 하기
오라클 db startup의 4가지 단계
1.
shutdown: db와 instance 둘다 close된 상태
2.nomount: instance가 열리는 단계
3.mount: control file이 열리면서 db의 구조정보를 읽을 수 있는 단계
4.open: datafile, redo log file등 모든 db의 파일들이 다 열리는 단계
✅ nomount단계로 가려면parameter file이 필요, mount로 올라가려면control file이 필요, OPEN으로 올라가려면data file, redo log file두개가 필요하다. emp테이블을 select 할 수 있는 단계는 open이다. 4개가 모두 있어야 select를 할 수 있다.
실습1. db를 shutdown 시키기
실습2. db를 startup단계 nomount로 올리기 (백업 db를 하나 더 만들고 싶을 때)
SQL> startup nomount
select status from v$instance;
✅ nomount라는 뜻 ! 인스턴스가 시작 되었다 라는 것이다.
실습3. mount 단계로 올라가보자
SQL> alter database mount;
실습4. mount에서 open으로 올리기
SQL> alter database open;
SQL> select status from v$instance;
STATUS
------------
OPEN
✅ 위의 startup단계를 왜 dba가 알고있어야 하냐면
1. db생성, controlfile생성과 같은 작업을 하기 위해
2. startup하고 db 안올라오면 어느 단계에서 안올라오는건지 알아야 무슨 파일이 손상되어 안올라오는건지 알아낼 수 있기 때문에
문제 db를 내리고, mount 상태로 db 올리기
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 523108352 bytes
Fixed Size 1337632 bytes
Variable Size 494929632 bytes
Database Buffers 20971520 bytes
Redo Buffers 5869568 bytes
Database mounted.
SQL> select status from v$instance;
STATUS
------------
MOUNTED➡️ i.sql안에 select status from v$instance; 저장
SQL> ed i.sql SQL> @i.sql STATUS ------------ MOUNTED
startup 각 단계에서 할 수 있는 일 !
nomount-> DB생성, control file 생성mount-> database위 구조를 변경하는 작업
(예: data file의 이름이나 위치 변경하는 작업, database 모드를 아카이브 모드로 변경하는 작업, database를 read only로 여는 작업)
C,F이다.
지금은 노마운트 상태 . 마운트로 올라가려고 할 때 어떤 액션이 일어나는가?
a,b는 open으로 갈 때 열린다. c 얼럿로그파일에 기록된다.
디는 셧다운에서 노마운트갈때 이 이니셜파라미터는 셧다운에서 노마운트
(마지막문제)
오라클 데이터베이스에서 생성할 수 있는 데이터 파일의 갯수 바꿔줄것임 200 -> 2000으로 해줄건데 두 파일해서 해줄거임!
일단 아까 spfile 없앴으니까 다시 만들고
create spfile from pfile수정해주고
SQL> alter system set db_files=2000 scope=spfile;
SQL> shutdown immediate
SQL> startup orcl에서 sh dba.sh 해서 8 실행하고 컨트롤파일 백업하기
PL/SQL procedure successfully completed. # 8번 실행 되었고
SQL> exit;
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[orcl:~]$ cd backup_controlfile/ # 여기로 이동
[orcl:backup_controlfile]$ ls
20230907_controlfile.txt 20230912_controlfile.txt # 예전에만든거, 오늘 만든거 있음
[orcl:backup_controlfile]$ vi 20230912_controlfile.txt
# 오늘 만든거 열어서 :set nu 해서 :37,117 w create_control.sql 실행하면 스크립트 생성됨
[orcl:backup_controlfile]$ ls # 확인하면
20230907_controlfile.txt 20230912_controlfile.txt create_control.sql # 여기 생성
[orcl:backup_controlfile]$ vi create_control.sql # 열고 안에 내용 2000으로 수정해주기
