복습
오라클 아키텍쳐 구조
DBWR, LGWR, CKPT, PMON, SMON, RECO, ARCn
↑
instance : SGA + Background process
↓
shared pool, db buffer cache, redo log buffer,
large pool, java pool, stream pool
database :
1. data file : data 가 들어있는 파일 , 바이너리(binary) 파일
2. control file : 데이터베이스의 구조 정보를 담고 있는 파일
3. redo log file 실습2. controlfile의 내용 (database의 구조정보)를 확인하세요
binary file (컴퓨터 만이 이해할 수 있는 파일. 사람의 언어로 되어있지 않다.)
control file을 텍스트로 백업하는 명령어
: db를 구성하는 각각의 파일들이 어디에 있고 이름은 뭔지 그 정보가 들어있다.alter database backup controlfile to trace as '/home/oracle/controlfile_text.txt';
문제 19c에서 control file을 텍스트로 생성
SQL> alter database backup controlfile to trace 2 as 'c:\\data\\controlfile_text.txt';
💡 controlfile에 대한 dba를 위한 tip
컴트롤 파일의 내용을 백업하는 명령어는 db구조가 변경될 때 마다 반드시 수행해야한다. 그냥 평상시에도 퇴근전에 한번 생성하기를 권장한다. datafile, redo logfile, controlfile이 깨지는 장애가 발생했을 때 이 텍스트 파일이 있으면 복구가 빨라진다.
dba.sh 쉘 스크립트 8번에 위 명령어 실행 추가하기
✅ 오늘 날짜만 추출하는 쉘스크립트 작성
$ vi a.sh
#현재 날짜와 시간을 변수에 저장
current_data=$(date)
#변수에서 일부분만 추출
substring=${current_date:2:10}
#변수의 내용 출력
echo $substring
$ sh a.sh아래 PL/SQL로 진행했음
SQL> ed c.sql
declare
v_date varchar2(20);
v_sttm varchar2(200);
begin
select to_char(sysdate,'YYYYMMDD') into v_date
from dual;
v_sttm :='alter database backup controlfile to trace as
''/home/oracle/backup_controlfile/' || v_date || '_controlfile.txt''' ;
dbms_output.put_line( v_sttm );
execute immediate v_sttm;
end;
/✅ ls -lrt 쓰면 시간 순서대로 최근생성된 파일이 끝에 뜬다.
SQL> ed create_controlfile_text.txt.sql
alter database backup controlfile to trace
as '/home/oracle/controlfile_text.txt';$ vi dba.sh
# /bin/bash
echo -e "
aaa a aa aaa aaa a aaaa
a a a aa a a a a a a
a a aa a a a aaaaaa
a a a aaaaa a a a
a a a a a a a a
aaa a aaa aaa a aaaa
"
echo -e "======================="
echo " "
echo " [1] 테이블 스페이스 공간 확인 "
echo " [2] 디스크 i/o 확인하는 sar 명령어 수행 "
echo " [3] 현재 데이터베이스 이슈 확인 "
echo " [4] 테이블 스페이스 공간 확보(80% 이상) "
echo " [5] 현재 오라클 메모리 상태 확인 "
echo " [6] 체크포인트를 수동으로 일으키기 "
echo " [7] 아카이브 로그 파일의 디스크 용량을 확인하기"
echo " [8] 컨트롤 파일 백업 텍스트 생성하기"
echo -n "원하는 작업 선택"
read aa
echo " "
case $aa in
1) sh /home/oracle/t.sh;;
2) sh /home/oracle/sar.sh;;
3) sh /home/oracle/o.sh;;
4) sh /home/oracle/add_t.sh;;
5) sqlplus scott/tiger @/home/oracle/sga.sql ;;
6) sqlplus scott/tiger @/home/oracle/ckpt.sql ;;
7) sqlplus "/as sysdba" @/home/oracle/fra_space.sql ;;
8) sqlplus "/as sysdba" @/home/oracle/c.sql ;;
esac
echo " "redo log file
💡복구를 위해서 db의 변경사항의 히스토리를 기록해놓은 파일
실습1.(리눅스) 리두 로그 그룹과 상태를 확인하기
select group#, status, members
from v$log;
✅ member가 두개이다. 두개이상으로 사용하길 권장. LGMR가 멤버 2개에 같은 내용을 동시에 적는다. 그래서 하나가 손상되면 다른 하나로 db를 운영하기 위햐서 이비다.
✔️ 리두 로그 그룹 상태 4가지
1. currnet : 현재 쓰고 있는 상태
2. active : 다 쓰긴 했지만 아관련된 변경사항이 메모리에서 디비로 반영이 안된상태
3. nactive : 다 썼고 관련된 변경사항이 메모리에서 디비로 방=ㄴ영돤 ㅏ= 상태
4. unused : 한번도 사용한적 없는 상태
실습2. 로그 스위치를 수동으로 일으키고 리두 로그 그룹의 상태를 확인하기
SQL> alter system swith logfile;
select group#, status, members
from v$log;
문제 19c에서 리두 로그 그룹의 상태 확인하기
하나인뎅오
실습3. (리눅스) 리두 로그 그룹의 멤버들 확인
SLQ> col member for a50
select group#, member
from v$logfile;
실습4. 19c에서 리두 로그 그룹의 멤버들 확인
✅오라클을 처음 설치하면 기본값이 그룹당 멤버 1개입니다. 이렇게 운영하면, 멤버가 손상되었을 때 db가 shutdown 됩니다.
실습5. 1번 리두로그 그룹에 멤버를 하나 더 추가합니다.
alter database add logfile member
'C:\USERS\ITWILL\DESKTOP\ORADATA\ORCL\REDO01b.LOG' to group 1;
문제 리두 로그 그룹 2번에 멤버를 하나 더 추가하기
alter database add logfile member
'C:\USERS\ITWILL\DESKTOP\ORADATA\ORCL\REDO02b.LOG' to group 2;💡 리두로그 그룹에 멤버 추가에 대한 dba를 위한 팁
위 작업을 할 때 다음과 같이 스크립트를 모아서 한번에 막 돌리지 말기
한번에 많은 로그 멤버를 추가하게 되면 컨트롤 파일이 깨질 확률이 높다. 한번에 하나씩 추가하고 확인, 추가하고 확인 천천히 하기
SQL> ed add_log.sql
alter database add logfile member
'C:\USERS\ITWILL\DESKTOP\ORADATA\ORCL\REDO02b.LOG' to group 1;
alter database add logfile member
'C:\USERS\ITWILL\DESKTOP\ORADATA\ORCL\REDO02b.LOG' to group 2;
alter database add logfile member
'C:\USERS\ITWILL\DESKTOP\ORADATA\ORCL\REDO02b.LOG' to group 3;
SQL> @add_log.sql 점심시간문제 v$log, v$logfile을 조인해서 group#, status, members, member을 조회하기
select a.group#, a.status, a.members, b.member from v$log a , v$logfile b;
✍🏻 DML작업이 활발하게 일어나는 database는 redo log group을 5-10개로 group의 갯수를 3개가 아니라 더 늘려줘야 합니다. 그룹의 갯수가 3개라면 DML작업이 느려집니다.
✔️ group의 갯수가 많을때의 장점과 단점
- 장점 : DML작업이 활발하게 일어날 때 성능이 느려지는 현상이 적게 발생한다.
- 단점 : 백업할 때 시간이 좀 더 걸린다.
실습6. 리두 로그 그룹을 늘리기 (19c window)
SQL> select group#, status, sequence# from v$log;
✅ 강사님은 INACTIVE가 두개, 커런트가 3번인데 나는 하나밖에없고 커런트가 2번이다.
수업은, 4번을 추가할건데 바로 직전 3번이 CURRENT면 조금 부담스러워서 로그 스위치를 일으켜 다른 번호가 CURRENT가 되게 한다.alter system switch logfile;
✅ 로그 수위치를 일으키니, 다른 컴퓨터와 같이 잘 되었다. 그렇지만 가급적 ACTIVE가 없이 INACTIVE인 상태로 두고 작업한다. (기존것을 덮어쓰거나, 파일이 깨질까봐)
: 수동으로 체크포인트를 일으키면 된다.SQL> alter system checkpoint;
⭐ 이렇게 하면 나중에 redo log 파일이 깨졌을때 복구하기가 수월하다.
SQL> alter database add logfile group 4 'C:\USERS\ITWILL\DESKTOP\ORADATA\ORCL\REDO04.LOG' size 200m; SQL> alter database add logfile member 'C:\USERS\ITWILL\DESKTOP\ORADATA\ORCL\REDO04b.LOG' to group 4; select group#, status, members, sequence# from v$log;
문제 이번에는 redo log file group5번을 추가하기
SQL> alter database add logfile group 5 'C:\USERS\ITWILL\DESKTOP\ORADATA\ORCL\REDO05.LOG' size 200m; SQL> alter database add logfile member 'C:\USERS\ITWILL\DESKTOP\ORADATA\ORCL\REDO05b.LOG' to group 5; select group#, status, members, sequence# from v$log;
문제 unused 파일에 로그가 잘 쓰여지는지 확인하기 위해 로그스위치를 수동으로 일으키고, unused가 사라지게 해보기
alter system switch logfile;
- 체크포인트 까지 일으켜서 ACTIVE를 INACTIVE로 변경
💡 스토리지 종류 3가지
1. file system
2. raw device
3. ASM
실습7. 리눅스의 ASM에서 리두 로그 그룹을 추가
$ select group#, status, members, sequence#
from v$log;오라클 데이터베이스 스토리지를 SML으로관리한다면 다음과 가이 group에 추가가 가능합니다.
alter database add logfile group 4;
-- 라클이 알ㅇ서 위치아 이름 정저딘다.
-- 오라클이 알아서 위치와 이름을 지정하겠다. dba사용할 가능성이 줄어든다.
select group#, member
from v$logfile; alter database add logfile group 5;파라미터 파일
💡 오라클 인스턴스의 구조 정보와 database의 설정 정보를 담고있는 파일
만약 오라클 db에 어떤 설정값을 변경하고 싶다면? 이 파라미터 파일을 수정해야 합니다.
실습1. (19c) 파라미터 파일의 위치 확인
SQL> show parameter spfile;C:\USERS\ITWILL\DESKTOP\WINDOWS.X64_193000_DB_HOME\DATABASE\SPFILEORCL.ORA
select name, value
from v$spparameter;실습2. plsql 시간에 배운 cursor 에서 커서를 코드에서 닫지 않게 되면 발생하는 문제가 더이상 커서를 열 수 없다고 하면서 에러가 난다고 했습니다. 관련된 파라미터를 조회하기
ORA-01000: 최대 열기 커서를 초과 했습니다. <- 이 에러가 나면서 운영이 안되면
SQL> select name, value
from v$spparameter
where name like '%open_cursor%';
✅ 300 개는 최대로 동시에 열 수 있는 커서갯수
실습3. 위와 같은 상황이 벌어졌을 때 해결방법?
1. 근본적인 원인은 PL/SQL 코드에서 close 커서를 모두 넣어주도록 한다.
2. open_cursors의 파라미터 갯수를 400개로 늘린다.
오라클의 처음 startup 하게 되면 오라클의 spfile.ora의 내용을 보고 오라클 인스턴스를 구성합니다. 인스턴스 -> 영향을 주고있는 설정값
⭐ 중요 !
인스턴스는 v$parameter
spfile.ora는 v$spparameter 로 조회한다.
현재 설정되어서 db에 영향을 주고있는 파라미터 값을 보고 싶을때는
v$parameter
그냥 spfile.ora의 내용만 확인하고 싶을때는v$spparameterselect name, value from v$parameter where name like '%open_cursor%';select name, value from v$spparameter where name like '%open_cursor%';✅ 400개로 늘리기
alter system set open_cursors=400;
문제 다시 open_cursors를 300으로 줄이기
alter system set open_cursors=300;
문제 리눅스에서 open_cursors를 400으로 늘리기
alter system set open_cursors=400;패스워드 파일(password file)
💡 특별한 권한을 가진 유저를 인증을 해주기 위한 파일 -> 디비를 올렸다 내렸다 할 수 있는 sysdba권한
다음과 같이 sys ㅇ유저로 접속하는데 패스워드도 물어보지 않고 접속하는 환경에서 패스워드를 물어보게 하려면 패스워드 파일에 대해서 이해하고 있어야 합니다.
SQL> sqlplus "/as sysdba"
실습 패스워드 파일에 등록된 유저를 확인핸보기
select * from v$pwfile_users;
실습2. SCOTT유저를 패스워드 파일에 등록시키시오
grant sysdba to scott;
select username, sysdba from v$pwfile_users; 
실습3. scott유저로 접속해서 db 내려보기
ㅅ실습4. 다시 sys유저로 와서 scott에게 부여한 sys한을무력하시오
revoke sysdba from scott; -- 권한회수실습5. 패스워드의 위치 확인
C:\Users\ITWILL\Desktop\WINDOWS.X64_193000_db_home\database
오라클 홈 디렉토리 밑에 dbs 밑에 있음
PWDorcl.ora <- 이 파일이 패스워드 파일임 !
이 파일을 지웠다가 다시 생성해야 할 일이 있는데 언제냐면 sys패스워드를 오라클 설치할 때 지정하는데 패스워드를 잊어버렸을 때 이 파일을 다시 생성하면 됩니다.
명령어(OS)/도스창 열고
C:\Users\ITWILL>cd C:\Users\ITWILL\Desktop\WINDOWS.X64_193000_db_home\database
C:\Users\ITWILL\Desktop\WINDOWS.X64_193000_db_home\database>orapwd file=PWDorcl.ora password=oracle_4U실습6. sqlplus "/as sysdba" 이렇게 접속하지 못하게 막으시오! 정확하게 패스워드를 넣고 다음과 같이 접속되게 하세요
C:\Users\ITWILL>sqlplus sys/oracle_4U as sysdbasqlnet.ora 파일에
C:>sqlplus / as sysdba ====> 오라클 계정 sys 사용자로 접속하는 것입니다.
접속을 막기 위해서는 아래의 sqlnet.ora 파일에
SQLNET.AUTHENTICATION_SERVICES= (none) 라인을 추가하시면 됩니다.
SQLNET.AUTHENTICATION_SERVICES= (NTS) 라인은 / as sysdba 접속을 허용하는 파라미터입니다.
💡 sqlnet.ora 파일은
C:\Users\ITWILL\Desktop\WINDOWS.X64_193000_db_home\network\admin에 있다.
❓ sys 패스워드 변경
alter user sys
identified by oracle_4U💡 dba를 위한 팁
sqlplus "/as sysdba"로 접속되게 db를 운영하게 되면 공공기관의 경우 db 감사때 지적을 당하게 되고 일반 사기업은 감사는 없지만 고객데이터를 잘못되어서 유출될 수도 있다. 반드시 sys/패스워드 as sysdba로 접속되게 만들어놓기
alert log file, trace file
➡️ 오라클을 진단하기위해 확인해야하는 파일과 사전
1. alert log file : 주요 장애 이슈 에러 번호
2. trace file : 상세한 에러 메세지와 이유
3. dba_outstanding_alerts 사전 : 현재 db에 주목할만한 큰 이슈
실습1. (리눅스) alert log file 위치 확인
SQL> show parameter background_dump_dest #/u01/app/oracle/diag/rdbms/orcl/orcl/trace
[orcl:~]$ cd /u01/app/oracle/diag/rdbms/orcl/orcl/trace [orcl:trace]$ ls -l alert* -rw-r----- 1 oracle dba 158929 9월 7 15:13 alert_orcl.log
❓ alert log file 의 내용
1. 오라클에서 발생한 에러 (ORA-600 internal 에러)
2. db를 startup, shutdown한 정보들
3. db의 구조를 변경한 명령어들
(예: create tavlespace 명령어, alter database 명령어등)
4. 기본값이 아닌 파라미터틀(값을 변경한 파라미터)
5. block이 손상도었을 때 그 정보
6. dead lock 정보
b,c,d
문제 오늘 control file의 백업본을 생성하는 다음의 명령어를 수행했는데
오늘 몇시에 수행했는지 alert log file 을 보고 알아내시오!
alter database backup controlfile to trace as '/home/oracle/..';💡힌트: 리눅스 때 배웠던 grep 명령어 이용하기
[orcl:trace]$ grep -B 1 'alter database*' alert*
-B옵션이 찾으려는 명령어가 있는 행의 이전라인을 보는데, 이전 1 라인만 보여달라는 것이다.
문제 출력되는 내용을 별도의 파일로 생성하시오 (alter.txt로 )
-B 1 'shutdown*' alert*
문제 오늘 shutdown을 언제 했는지 알아내기
$ grep -B 5 -Ei 'shut.*abort' alert_orcl.log✅ shut, abort 를 포함하는 행의 5번째 이전행까지 출력
문제 푸티창을 하나 더 열고 oracle유저로 접속해서 alert log file이 있는곳으로 이동해서 다음과 같이 alertlog file 뒷부분을 실시간 모니터링 되게 하시오!
tail -f alert*
마지막 문제 dba.sh 스크립트의 9번에 alert log file을 실시간 모니터링 하는 tail -f alert*가 자동으로 수행되게 하시오
$ vi dba.sh
# /bin/bash
echo -e "
aaa a aa aaa aaa a aaaa
a a a aa a a a a a a
a a aa a a a aaaaaa
a a a aaaaa a a a
a a a a a a a a
aaa a aaa aaa a aaaa
"
echo -e "======================="
echo " "
echo " [1] 테이블 스페이스 공간 확인 "
echo " [2] 디스크 i/o 확인하는 sar 명령어 수행 "
echo " [3] 현재 데이터베이스 이슈 확인 "
echo " [4] 테이블 스페이스 공간 확보(80% 이상) "
echo " [5] 현재 오라클 메모리 상태 확인 "
echo " [6] 체크포인트를 수동으로 일으키기 "
echo " [7] 아카이브 로그 파일의 디스크 용량을 확인하기"
echo " [8] 컨트롤 파일 백업 텍스트 생성하기"
echo " [9] alert log file을 실시간 모니터링 하기"
echo -n "원하는 작업 선택"
read aa
echo " "
case $aa in
1) sh /home/oracle/t.sh;;
2) sh /home/oracle/sar.sh;;
3) sh /home/oracle/o.sh;;
4) sh /home/oracle/add_t.sh;;
5) sqlplus scott/tiger @/home/oracle/sga.sql ;;
6) sqlplus scott/tiger @/home/oracle/ckpt.sql ;;
7) sqlplus "/as sysdba" @/home/oracle/fra_space.sql ;;
8) sqlplus "/as sysdba" @/home/oracle/c.sql ;;
9) sh /home/oracle/testlog.sh ;;
esac
echo " "
- cd /u01/app/oracle/diag/rdbms/orcl/orcl/trace 에서 vi testlog.sh 만들고 그 안에
cd /u01/app/oracle/diag/rdbms/orcl/orcl/trace tail -f alert* 쓰고 저장함
- dba.sh에 cd명령어 안되나? 위처럼 작성 -> 안된다 얘는 쉘스크립트를 실행하도록 하는것만
cd /u01/app/oracle/diag/rdbms/orcl/orcl/trace tail -f /u01/app/oracle/diag/rdbms/orcl/orcl/trace/testlog.sh ;;- 안댐
💡 수정
1. testlog.sh를 /home/oracle 밑으로 옮겼다. 안에 내용은 동일.mv testlog.sh /home/oracle/testlog.sh
- dba.sh에
9) sh /home/oracle/testlog.sh ;;
