251215 TIL

DBA(Database Administration)

https://www.oracle.com/kr/database/what-is-a-dba/

• 주요 역할 중 하나는 항상 데이터베이스를 사용할 수 있도록 유지하는 것
• DBA는 시스템 장애를 최소화하기 위해 예방 조치를 취할 수 있어야 함
• 예방 조치를 했다고 하여 모든 장애를 방지할 수 있다고 생각해서는 안됨
• DBA는 장애 발생 시 가능한 빨리 데이터베이스가 작동되도록 만들어 데이터 손실을 최소화해야 함

백업의 중요성

• 발생할 수 있는 모든 유형의 장애로부터 데이터를 보호하기 위해 DBA는 정기적으로 데이터베이스를 백업해야 함
• 현재 백업이 없으면 파일 손실이 발생한 경우 데이터 손실 없이 DBA가 데이터베이스를 시작하여 실행하는 것이 불가능
• 백업은 여러 유형의 장애로부터 데이터를 복구하는데 반드시 필요하며 백업을 검증하는 작업은 아무리 강조해도 지나치지 않음

Failure 범주

statement failure

• 응용 프로그램의 논리 오류
• 부적합한 데이터를 테이블에 입력하려고 시도하는 경우
• 충분하지 않은 권한으로 작업을 수행하려고 시도하는 경우
• 테이블을 생성하려고 시도했지만 할당량 한계를 초과한 경우
• extent를 할당하게 만드는 insert, update 작업을 테이블에 시도했지만 테이블스페이스에 사용 가능한 공간이 부족한 경우

statement failure 해결

• 프로그램의 논리적 흐름 수정(업무 파악이 중요)
• SQL문 수정 및 재실행
• 적절한 권한 부여
• 유저에게 할당한 테이블스페이스 quota 값을 수정
• 테이블스페이스 데이터 파일을 추가, 데이터 파일 resize, autoextend 옵션을 사용

user process failure

• user session에서 비정상적인 연결 해제를 수행하는 경우
• 유저 프로그램에서 exception이 발생한 경우

user process failure 해결

• PMON 백그라운드 프로세스는 비정상적으로 종료된 user process를 감지하고 유저가 작업하고 있떤 자원들을 전부 다 해지하는 작업을 수행
• PMON은 transaction을 rollback하고 lock을 해지

user failure

• 유저가 실수로 테이블을 drop, truncate한 경우
• 유저가 DML작업을 잘못 수행한 경우

user failure 해결

• 백업본을 이용해서 복구해야 함(point-in-time recovery)
• export 파일에서 테이블을 import
• logminer(redo 분석기)를 이용해서 DML 작업 내용을 확인
• flashback 기능 사용

instance failure

• 정전으로 인해 서버를 사용할 수 없게 되는 경우
• CPU 고장, 메모리 훼손, 운영체제에 문제가 생긴 경우
• 오라클 필수 백그라운드 프로세스가 문제가 생긴 경우(dbwn, lgwr, pmon, smon, ckpt)
• shutdown abort, startup force

instance failure 해결

• 오라클 startup을 수행하게 되면 smon 백그라운드 프로세스가 자동으로 복구 작업을 진행

media failure

• 디스크 드라이브 헤더가 고장난 경우
• 데이터 파일을 실수로 삭제한 경우

media failure 해결

• 백업본을 가지고 복구 수행해야 함

Instance Recovery

1. Data File이 동기화 되지 않은 상태
   • 마지막 체크포인트 이후에 리두 정보가 발생되어 있음
   • commit된 변경된 블록이 데이터 파일로 저장되지 않은 상태
   • commit, rollback 하지 않은 블록이 있는 상태
2. smon은 마지막 체크포인트 이후에 변경된 리두 정보를 이용해서 rollforward 작업 수행
3. 데이터베이스 오픈 수행
4. commit, rollback 하지 않은 블록 rollback

백업

백업 용어

Whole Database Backup

• 모든 데이터파일, 컨트롤파일, 리두로그파일
• 데이터베이스 OPEN 상태이거나 SHUTDOWN 되어 있는 상태에서 백업

Partial Database Backup

• 특정한 테이블스페이스에 속한 데이터파일 백업

일관성 있는 백업(Consistent Backup)

• close backup, cold backup, offline backup
• 데이터베이스를 정상적으로 종료 후 백업을 수행
• 모든 데이터파일, 컨트롤파일, 리두로그파일의 checkpoint 정보가 일치됨
• noarchivelog mode, archivelog mode 모두 가능

일관성 없는 백업(Inconsistent Backup)

• open backup, hot backup, online backup
• 데이터베이스 운영 중에 백업 수행
• archivelog mode에서만 가능
• 데이터베이스 레벨, 테이블스페이스 레벨

Full Backup

• 백업 대상 파일에 속한 모든 데이터 블록을 백업 수행

Incremental Backup

• 이전 Full Backup 이후에 변경된 블록에 대해서만 백업 수행
• RMAN 백업을 수행해야 함

백업 대상 파일

데이터 파일

SYS@ora19c> select name from v$datafile;

NAME
--------------------------------------------------
/u01/app/oracle/oradata/ORA19C/system01.dbf
/u01/app/oracle/oradata/ORA19C/sysaux01.dbf
/u01/app/oracle/oradata/ORA19C/undotbs01.dbf
/u01/app/oracle/oradata/ORA19C/users01.dbf

SYS@ora19c> select name from v$tempfile;

NAME
--------------------------------------------------
/u01/app/oracle/oradata/ORA19C/temp01.dbf

컨트롤 파일

SYS@ora19c> select name from v$controlfile;

NAME
--------------------------------------------------
/u01/app/oracle/oradata/ORA19C/control01.ctl

리두 로그 파일

SYS@ora19c> select member from v$logfile;

MEMBER
--------------------------------------------------
/u01/app/oracle/oradata/ORA19C/redo03.log
/u01/app/oracle/oradata/ORA19C/redo02.log
/u01/app/oracle/oradata/ORA19C/redo01.log

로그 모드 조회

SYS@ora19c> select name, log_mode from v$database;

NAME                           LOG_MODE
------------------------------ ------------
ORA19C                         NOARCHIVELOG

SYS@ora19c> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     26
Current log sequence           28

User Managed Backup & Recovery

• 운영체제 명령으로 파일 backup (cp)
• 운영체제 명령으로 파일 restore(cp)
• SQL 및 SQL*Plus 명령으로 recovery 수행

RMAN(Recovery Manager)

• 오라클에서 제공하는 backup, restore, recovery 작업을 관리하는 오라클 유틸리티

No Archivelog Mode 백업

• 일관성 있는 백업(close, cold, offline backup)
• 데이터베이스를 정상적인 종료한 후 백업 수행
  • shutdown [ normal | transactional | immediate ]
• Whole Database Backup : 모든 데이터 파일, 컨트롤 파일, 리두 로그 파일

현재 데이터베이스 checkpoint 발생 시점 scn 정보

select checkpoint_change#, current_scn from v$database; -- 2822872    2843479
select checkpoint_change#, current_scn from v$database; -- 2822872    2843485

SYS@ora19c> select checkpoint_change#, scn_to_timestamp(checkpoint_change#) from v$database;

CHECKPOINT_CHANGE# SCN_TO_TIMESTAMP(CHECKPOINT_CHANGE#)
------------------ ----------------------------------------------------------------------
           2822872 12-DEC-25 06.00.42.000000000 PM

SYS@ora19c> select name, checkpoint_change# from v$datafile;

NAME                                               CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
/u01/app/oracle/oradata/ORA19C/system01.dbf                   2822872
/u01/app/oracle/oradata/ORA19C/sysaux01.dbf                   2822872
/u01/app/oracle/oradata/ORA19C/undotbs01.dbf                  2822872
/u01/app/oracle/oradata/ORA19C/users01.dbf                    2822872        

SYS@ora19c> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------
         1          1         26   52428800        512          1 NO  INACTIVE               2767903 11-DEC-25      2797846 12-DEC-25          0
         2          1         27   52428800        512          1 NO  INACTIVE               2797846 12-DEC-25      2822872 12-DEC-25          0
         3          1         28   52428800        512          1 NO  CURRENT                2822872 12-DEC-25   1.8447E+19                    0  <<- 2822872     

Full Checkpoint 발생 후 SCN 조회

SYS@ora19c> alter system checkpoint;

System altered.

SYS@ora19c> select checkpoint_change#, scn_to_timestamp(checkpoint_change#) from v$database;

CHECKPOINT_CHANGE# SCN_TO_TIMESTAMP(CHECKPOINT_CHANGE#)
------------------ ---------------------------------------------------------------------------
           2844561 15-DEC-25 03.55.54.000000000 PM

SYS@ora19c> select name, checkpoint_change# from v$datafile;

NAME                                               CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
/u01/app/oracle/oradata/ORA19C/system01.dbf                   2844561
/u01/app/oracle/oradata/ORA19C/sysaux01.dbf                   2844561
/u01/app/oracle/oradata/ORA19C/undotbs01.dbf                  2844561
/u01/app/oracle/oradata/ORA19C/users01.dbf                    2844561

SYS@ora19c> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------
         1          1         26   52428800        512          1 NO  INACTIVE               2767903 11-DEC-25      2797846 12-DEC-25          0
         2          1         27   52428800        512          1 NO  INACTIVE               2797846 12-DEC-25      2822872 12-DEC-25          0
         3          1         28   52428800        512          1 NO  CURRENT                2822872 12-DEC-25   1.8447E+19                    0

SYS@ora19c> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@ora19c> startup
ORACLE instance started.

Total System Global Area  830469472 bytes
Fixed Size                  8901984 bytes
Variable Size             553648128 bytes
Database Buffers          260046848 bytes
Redo Buffers                7872512 bytes
Database mounted.
Database opened.
SYS@ora19c> select checkpoint_change#, scn_to_timestamp(checkpoint_change#) from v$database;

CHECKPOINT_CHANGE# SCN_TO_TIMESTAMP(CHECKPOINT_CHANGE#)
------------------ ---------------------------------------------------------------------------
           2844635 15-DEC-25 03.57.18.000000000 PM

SYS@ora19c> select name, checkpoint_change# from v$datafile;

NAME                                               CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
/u01/app/oracle/oradata/ORA19C/system01.dbf                   2844635
/u01/app/oracle/oradata/ORA19C/sysaux01.dbf                   2844635
/u01/app/oracle/oradata/ORA19C/undotbs01.dbf                  2844635
/u01/app/oracle/oradata/ORA19C/users01.dbf                    2844635

SYS@ora19c> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------
         1          1         26   52428800        512          1 NO  INACTIVE               2767903 11-DEC-25      2797846 12-DEC-25          0
         2          1         27   52428800        512          1 NO  INACTIVE               2797846 12-DEC-25      2822872 12-DEC-25          0
         3          1         28   52428800        512          1 NO  CURRENT                2822872 12-DEC-25   1.8447E+19                    0

백업

# 데이터베이스 종료
SYS@ora19c> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@ora19c> !

# 백업 폴더 생성
[oracle@ora19c ~]$ mkdir -p backup/noarch
[oracle@ora19c ~]$ cd backup/noarch
[oracle@ora19c noarch]$ pwd
/home/oracle/backup/noarch

# 컨트롤 파일 백업
[oracle@ora19c noarch]$ cp -v /u01/app/oracle/oradata/ORA19C/*.ctl .
‘/u01/app/oracle/oradata/ORA19C/control01.ctl’ -> ‘./control01.ctl’

# 데이터 파일 백업
[oracle@ora19c noarch]$ cp -v /u01/app/oracle/oradata/ORA19C/*.dbf .
‘/u01/app/oracle/oradata/ORA19C/sysaux01.dbf’ -> ‘./sysaux01.dbf’
‘/u01/app/oracle/oradata/ORA19C/system01.dbf’ -> ‘./system01.dbf’
‘/u01/app/oracle/oradata/ORA19C/temp01.dbf’ -> ‘./temp01.dbf’
‘/u01/app/oracle/oradata/ORA19C/undotbs01.dbf’ -> ‘./undotbs01.dbf’
‘/u01/app/oracle/oradata/ORA19C/users01.dbf’ -> ‘./users01.dbf’

# 리두 로그 파일 백업
[oracle@ora19c noarch]$ cp -v /u01/app/oracle/oradata/ORA19C/*.log .
‘/u01/app/oracle/oradata/ORA19C/redo01.log’ -> ‘./redo01.log’
‘/u01/app/oracle/oradata/ORA19C/redo02.log’ -> ‘./redo02.log’
‘/u01/app/oracle/oradata/ORA19C/redo03.log’ -> ‘./redo03.log’

[oracle@ora19c noarch]$ ls
control01.ctl  redo02.log  sysaux01.dbf  temp01.dbf     users01.dbf
redo01.log     redo03.log  system01.dbf  undotbs01.dbf

[oracle@ora19c noarch]$ exit
exit

# 데이터베이스 시작
SYS@ora19c> startup
ORACLE instance started.

Total System Global Area  830469472 bytes
Fixed Size                  8901984 bytes
Variable Size             553648128 bytes
Database Buffers          260046848 bytes
Redo Buffers                7872512 bytes
Database mounted.
Database opened.

SYS@ora19c> select checkpoint_change#, scn_to_timestamp(checkpoint_change#) from v$database;

CHECKPOINT_CHANGE# SCN_TO_TIMESTAMP(CHECKPOINT_CHANGE#)
------------------ ---------------------------------------------------------------------------
           2845590 15-DEC-25 04.00.14.000000000 PM

SYS@ora19c> select name, checkpoint_change# from v$datafile;

NAME                                               CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
/u01/app/oracle/oradata/ORA19C/system01.dbf                   2845590
/u01/app/oracle/oradata/ORA19C/sysaux01.dbf                   2845590
/u01/app/oracle/oradata/ORA19C/undotbs01.dbf                  2845590
/u01/app/oracle/oradata/ORA19C/users01.dbf                    2845590

SYS@ora19c> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------
         1          1         26   52428800        512          1 NO  INACTIVE               2767903 11-DEC-25      2797846 12-DEC-25          0
         2          1         27   52428800        512          1 NO  INACTIVE               2797846 12-DEC-25      2822872 12-DEC-25          0
         3          1         28   52428800        512          1 NO  CURRENT                2822872 12-DEC-25   1.8447E+19                    0

HR

# 백업 이후 작업 수행
HR@ora19c> create table hr.emp(id number, name varchar2(30)) tablespace users;

Table created.

HR@ora19c> insert into hr.emp(id, name) values(1, 'oracle');

1 row created.

HR@ora19c> commit;

Commit complete.

HR@ora19c> select * from hr.emp;

        ID NAME
---------- ------------------------------
         1 oracle

SYS@ora19c> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------
         1          1         26   52428800        512          1 NO  INACTIVE               2767903 11-DEC-25      2797846 12-DEC-25          0
         2          1         27   52428800        512          1 NO  INACTIVE               2797846 12-DEC-25      2822872 12-DEC-25          0
         3          1         28   52428800        512          1 NO  CURRENT                2822872 12-DEC-25   1.8447E+19                    0

SYS@ora19c> select f.file_name
from dba_extents e, dba_data_files f
where e.file_id = f.file_id
and e.segment_name = 'EMP'
and e.owner = 'HR';

FILE_NAME
--------------------------------------------------
/u01/app/oracle/oradata/ORA19C/users01.dbf

시나리오 1 : 특정 데이터 파일 손상(단, 백업 이후 리두 정보 존재)

⇒ 완전 복구 가능

장애 유발

SYS@ora19c> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SYS@ora19c> !
[oracle@ora19c ~]$ ls /u01/app/oracle/oradata/ORA19C/users01.dbf
/u01/app/oracle/oradata/ORA19C/users01.dbf
[oracle@ora19c ~]$ rm /u01/app/oracle/oradata/ORA19C/users01.dbf
[oracle@ora19c ~]$ ls /u01/app/oracle/oradata/ORA19C/users01.dbf
ls: cannot access /u01/app/oracle/oradata/ORA19C/users01.dbf: No such file or directory
[oracle@ora19c ~]$ exit
exit

SYS@ora19c> startup
ORACLE instance started.

Total System Global Area  830469472 bytes
Fixed Size                  8901984 bytes
Variable Size             553648128 bytes
Database Buffers          260046848 bytes
Redo Buffers                7872512 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '/u01/app/oracle/oradata/ORA19C/users01.dbf'

SYS@ora19c> select * from v$recover_file;

     FILE# ONLINE  ONLINE_ ERROR                             CHANGE# TIME          CON_ID
---------- ------- ------- ------------------------------ ---------- --------- ----------
         7 ONLINE  ONLINE  FILE NOT FOUND                          0                    0

백업 파일을 찾아서 restore

[oracle@ora19c ~]$ cd backup/noarch

[oracle@ora19c noarch]$ pwd
/home/oracle/backup/noarch

[oracle@ora19c noarch]$ ls
control01.ctl  redo02.log  sysaux01.dbf  temp01.dbf     users01.dbf
redo01.log     redo03.log  system01.dbf  undotbs01.dbf

[oracle@ora19c noarch]$ cp -v users01.dbf /u01/app/oracle/oradata/ORA19C/users01.dbf
‘users01.dbf’ -> ‘/u01/app/oracle/oradata/ORA19C/users01.dbf’

[oracle@ora19c noarch]$ ls /u01/app/oracle/oradata/ORA19C/users01.dbf
/u01/app/oracle/oradata/ORA19C/users01.dbf

[oracle@ora19c noarch]$ exit
exit

# 백업 이후 redo 적용
SYS@ora19c> recover tablespace users
Media recovery complete.

SYS@ora19c> alter database open;

Database altered.

백업본으로 원상복구

SYS@ora19c> shutdown abort
ORACLE instance shut down.

SYS@ora19c> !
[oracle@ora19c ~]$ cd backup/noarch

[oracle@ora19c noarch]$ ls
control01.ctl  redo02.log  sysaux01.dbf  temp01.dbf     users01.dbf
redo01.log     redo03.log  system01.dbf  undotbs01.dbf
[oracle@ora19c noarch]$ cp -v * /u01/app/oracle/oradata/ORA19C/
‘control01.ctl’ -> ‘/u01/app/oracle/oradata/ORA19C/control01.ctl’
‘redo01.log’ -> ‘/u01/app/oracle/oradata/ORA19C/redo01.log’
‘redo02.log’ -> ‘/u01/app/oracle/oradata/ORA19C/redo02.log’
‘redo03.log’ -> ‘/u01/app/oracle/oradata/ORA19C/redo03.log’
‘sysaux01.dbf’ -> ‘/u01/app/oracle/oradata/ORA19C/sysaux01.dbf’
‘system01.dbf’ -> ‘/u01/app/oracle/oradata/ORA19C/system01.dbf’
‘temp01.dbf’ -> ‘/u01/app/oracle/oradata/ORA19C/temp01.dbf’
‘undotbs01.dbf’ -> ‘/u01/app/oracle/oradata/ORA19C/undotbs01.dbf’
‘users01.dbf’ -> ‘/u01/app/oracle/oradata/ORA19C/users01.dbf’
[oracle@ora19c noarch]$ exit
exit

SYS@ora19c> startup
ORACLE instance started.

Total System Global Area  830469472 bytes
Fixed Size                  8901984 bytes
Variable Size             553648128 bytes
Database Buffers          260046848 bytes
Redo Buffers                7872512 bytes
Database mounted.
Database opened.