| # 요약 | |
| ■ NoArchive Log Mode ■ Archive Log Mode ■ 일관성 있는 Backup = Cold Backup = Offline Backup = Closed Backup ■ 일관성 없는 Backup = Hot Backup = Online Backup = Open Backup << 시나리오 1 >> 운영 중 offline되는 데이터 파일 손상되었을 때 복구 방식 << 시나리오 2 >> DB가 종료된 후 데이터파일 삭제 후 DB 올릴때 문제 복구 << 시나리오 3 >> DB가 종료 후 데이터파이을 삭제 후 DB 올릴 때 문제 |
|
■ NoArchive Log Mode
- 기본적으로 데이터베이스는 NoArchive Log Mode로 생성된다.
- Redo Log File이 순환 방식으로 사용된다.
- Log Switch가 발생하면 Checkpoint가 발생한 즉시 Redo Log File은 재사용할 수 있다.
- Redo Log가 겹쳐 쓰여지면 마지막 전체 Backup에 대해서만 Media Recovery를 할 수 있다.
안전 복구보다는 불안전한 복구 방식을 수행 할 경우가 많다.
- 데이터베이스 정상적인 종료한 후 백업을 수행해야한다.
SHUTDOWN [NORMAL | TRANSACTIONAL | IMMEDIATE]
- Backup할 때마다 전체 필수 : Datafile, Temp File, Control File
옵션 : Redo Log File
* CREATE PFILE - SHUTDOWN - Backup(cp)
* Instance Recovery -> SMON : ORACLE 업무
■ Archive Log Mode
- Log Switch가 발생하면 Checkpoint가 발생하고 ARC Background Process가 현재 Redo Log File을 물리적 다른 위치에 복사 한 후 Redo Log File은 재사용할 수 있다.
- Control File에 Archive된 Redo Log File의 정보를 기록하고있다.
- 데이터베이스 정상적인 종료 후 백업을 수행할 수 있고 운영중에 백업을 수행할 수 있다.
일관성 없는 백업, Online Backup, Hot Backup, Open Backup을 수행할 수 있다.
- 문제되는 파일을 현재 시점까지 복구할 수 있다. Redo Log가 있기 때문에 가능하다.
- Mode를 변경하면 기존 백업파일은 사용 불가. 다시 백업하자.
[oracle@oracle ~]$ pwd
/home/oracle
# Archive Log File이 생성되는 디렉터리 생성
[oracle@oracle ~]$ mkdir arch1 arch2
[oracle@oracle ~]$ ls /home/oracle/arch*
/home/oracle/arch1:
/home/oracle/arch2:
SELECT NAME, VALUE, ISSES_MODIFIABLE, ISSYS_MODIFIABLE
FROM v$parameter
WHERE name IN ('log_archive_dest_1','log_archive_dest_2','log_archive_format');
NAME VALUE ISSES_MODIFIABLE ISSYS_MODIFIABLE
------------------------ ---------------------------------- ----------------- -----------------
log_archive_dest_1 TRUE IMMEDIATE
log_archive_dest_2 TRUE IMMEDIATE
log_archive_format %t_%s_%r.dbf FALSE FALSE
- log_archive_format
VALUE : %t_%s_%r.dbf 아카이브 파일 포멧
ISSES_MODIFIABLE : FALSE alter system set
ISSYS_MODIFIABLE : FALSE scope=spfile
# Archive Log File이 생성되는 정보 설정
SQL> ALTER SYSTEM SET log_archive_dest_1 = "location=/home/oracle/arch1 mandatory" SCOPE = SPFILE;
SQL> ALTER SYSTEM SET log_archive_dest_2 = "location=/home/oracle/arch2 optional" SCOPE = SPFILE;
- Mandatory : Archive 작업이 성공적으로 완료되어야 Online Redo Log File을 겹처 사용할 수 있다.
공간이 부족할 경우 기다린다.
- Optional : Archive 작업이 성공적으로 완료되지 않은 경우에도 Online Redo Log File을 겹처 사용할 수 있다.
(기본값)
공간이 부족하면 Archive Log File이 생성되지 않는다. -> 여유공간 데일리 체크!
# Archive Log File이 생성될 때 이름 포맷을 설정
%t : thread number(instance number) THREAD#
%s : log sequence number SEQUENCE#
%r : resetlogs id
여러 데이터베이스에 걸쳐 아카이브된 로그파일의 유일한 이름을 나타낸다.
-- 생성 시점 : 처음 DB오픈 시, 불안전한 복구 resetlog 적용 시(ALTER DATABASE OPEN RESETLOGS)
ALTER SYSTEM SET log_archive_format = "arch_%t_%s_%r.arc" SCOPE = SPFILE;
# 디스크 사용량 확인
[oracle@oracle ~]$ df -h
Filesystem Size Used Avail Use% Mounted on
devtmpfs 831M 0 831M 0% /dev
tmpfs 850M 234M 616M 28% /dev/shm
tmpfs 850M 42M 808M 5% /run
tmpfs 850M 0 850M 0% /sys/fs/cgroup
/dev/mapper/ol-root 47G 19G 29G 40% /
/dev/sda1 1014M 265M 750M 27% /boot
tmpfs 170M 4.0K 170M 1% /run/user/1001
tmpfs 170M 12K 170M 1% /run/user/42
tmpfs 170M 0 170M 0% /run/user/0
/dev/mapper/ol-home /home
왜 없지? -> archieve log file이 생성되는 공간의 용량 체크 필수
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP MOUNT
# NoArchive Log Mode 에서 Archive Log Mode 전환
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;
SQL> ARCHIVE LOG LIST
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/arch2
Oldest online log sequence 1
Next log sequence to archive 2
Current log sequence 2
SQL> SELECT log_mode FROM v$database;
LOG_MODE
------------
ARCHIVELOG
SELECT THREAD#,SEQUENCE#,ARCHIVED,STATUS,FIRST_CHANGE#,FIRST_TIME,NEXT_CHANGE#,NEXT_TIME
FROM v$log;
THREAD# SEQUENCE# ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- --------- ---------------- ------------- ----------- ------------ ---------
1 1 YES INACTIVE 2037544 11-JAN-24 2047260 11-JAN-24
1 2 NO CURRENT 2047260 11-JAN-24 2.8147E+14
1 0 YES UNUSED 0 0
SELECT NAME,VALUE,ISSES_MODIFIABLE,ISSYS_MODIFIABLE
FROM v$parameter WHERE name IN ('log_archive_dest_1','log_archive_dest_2','log_archive_format');
NAME VALUE ISSES_MODIFIABLE ISSYS_MODIFIABLE
------------------- -------------------------------------------------- ----------------- -----------------
log_archive_dest_1 location=/home/oracle/arch1 mandatory TRUE IMMEDIATE
log_archive_dest_2 location=/home/oracle/arch2 optional TRUE IMMEDIATE
log_archive_format arch_%t_%s_%r.arc FALSE FALSE
SQL> select DEST_NAME,STATUS,BINDING,NAME_SPACE,DESTINATION from v$archive_dest;
DEST_NAME STATUS BINDING NAME_SP DESTINATION
-------------------- --------- --------- ------- --------------------------------------------------
LOG_ARCHIVE_DEST_1 VALID MANDATORY SYSTEM /home/oracle/arch1
LOG_ARCHIVE_DEST_2 VALID OPTIONAL SYSTEM /home/oracle/arch2
LOG_ARCHIVE_DEST_3 INACTIVE OPTIONAL SYSTEM
...31
- STATUS : VALID 설정 됨
- LOG_ARCHIVE_DEST_1 + LOG_ARCHIVE_DEST_2 = MANDATORY 설정하면 이중화로 운영된다.
SQL> ! ls /home/oracle/arch1 /home/oracle/arch2
/home/oracle/arch1:
/home/oracle/arch2:
SQL> select * from v$archived_log;
no rows selected
SQL> select * from v$log;
THREAD# SEQUENCE# ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- --------- ---------------- ------------- ----------- ------------ ---------
1 1 YES INACTIVE 2037544 11-JAN-24 2047260 11-JAN-24
1 2 NO CURRENT 2047260 11-JAN-24 2.8147E+14
1 0 YES UNUSED 0 0
# 백업 직후 archive log file 생성
ALTER SYSTEM ARCHIVE LOG CURRENT;
--Thread 1 advanced to log sequence 3 (LGWR switch)
-- Current log# 3 seq# 3 mem# 0: /u01/app/oracle/oradata/ora11g/redo03.log
--Archived Log entry 1 added for thread 1 sequence 2 ID 0xf72f47c dest 1:
--Archived Log entry 2 added for thread 1 sequence 2 ID 0xf72f47c dest 2:
SQL> select * from v$log;
THREAD# SEQUENCE# ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- --------- ---------------- ------------- ----------- ------------ ---------
1 1 YES INACTIVE 2037544 11-JAN-24 2047260 11-JAN-24
1 2 YES ACTIVE 2047260 11-JAN-24 2075466 12-JAN-24
1 3 NO CURRENT 2075466 12-JAN-24 2.8147E+14
SQL> select sequence#, name from v$archived_log;
SEQUENCE# NAME
---------- --------------------------------------------------
2 /home/oracle/arch1/arch_1_2_1157971650.arc
2 /home/oracle/arch2/arch_1_2_1157971650.arc
# 물리적 파일도 순서대로 있는지 확인하자
SQL> ! ls /home/oracle/arch1 /home/oracle/arch2
/home/oracle/arch1:
arch_1_2_1157971650.arc
/home/oracle/arch2:
arch_1_2_1157971650.arc
예시) 백업시점 scn#1000 , log seq#100
log switch log switch log switch log switch ...
#100 current #100 active #100 inactive #103 current #103 active ...
#98 inactive #101 current #101 active #101 inactive #104 current ...
#99 inactive #99 inactive #102 current #102 active #103 inactive ...
archive log file 생성
#100 백업시점
->> #101 archive log file 유실?! -> cancel base recovery seq#100까지만 복구 가능하다.
#102 복구불가
#103 복구불가
#104 -> 즉시 최근 시점으로 당장 백업하자. 기존 archive log file과 backup file은 필요없다.
#105
#106 -> archive log file이 sequence가 연속 생성되어있다면 #104시점의 백업파일로 #106시점으로 복구가 가능하다
...
예시) backup 시점
1/1 backup : datafile, temp file, control file
log#100, scn#1000
.
.
1/15 log #150 scn#1000
.
.
2/1 backup : datafile, temp file, control file
log #200 scn#2000
.
.
2/15 장애발생 log#210 -> 2/1 backup파일로 복구
- 업무요청 1/15시점의 데이터를 복구해달라
> 1/1 backup 파일을 사용, archive log #100~150 정보가 모두 있어야 한다.
> #140 파일이 없다?! -> #139까지만 복구 가능하다.
>>> 백업본 파일시점부터 복구하려는 시점까지의 archive log(redo정보)가 누락없이 있어야 한다.
SQL> select * from v$archive_processes;
PROCESS STATUS LOG_SEQUENCE STATE ROLES
---------- ---------- ------------ ------ ------------------------------------
0 ACTIVE 0 IDLE
1 ACTIVE 0 IDLE NO_FAL NO_SRL
2 ACTIVE 0 IDLE HEART_BEAT
3 ACTIVE 0 IDLE
4 STOPPED 0 IDLE
...
29 STOPPED 0 IDLE
ACTIVE : 자동관리
NO_FAL NO_SRL : 오라클에서 사용하는 롤
HEART_BEAT : 임시 대기 롤?
SQL> ! ps -ef | grep arc
oracle 8839 1 0 10:39 ? 00:00:00 ora_arc0_ora11g
oracle 8841 1 0 10:39 ? 00:00:00 ora_arc1_ora11g
oracle 8843 1 0 10:39 ? 00:00:00 ora_arc2_ora11g
oracle 8845 1 0 10:39 ? 00:00:00 ora_arc3_ora11g -- 임시 대기
oracle 10720 7240 0 12:04 pts/0 00:00:00 /bin/bash -c ps -ef | grep arc
oracle 10722 10720 0 12:04 pts/0 00:00:00 grep arc
■ 일관성 있는 Backup = Cold Backup = Offline Backup = Closed Backup
# 백업 전 로그 및 파일 상태 체크
SELECT * FROM v$log;
THREAD# SEQUENCE# ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- --------- ---------------- ------------- ----------- ------------ ---------
1 4 NO CURRENT 2082030 12-JAN-24 2.8147E+14
1 2 YES INACTIVE 2047260 11-JAN-24 2075466 12-JAN-24
1 3 YES ACTIVE 2075466 12-JAN-24 2082030 12-JAN-24
SQL> SELECT name,checkpoint_change#,status FROM v$datafile;
NAME CHECKPOINT_CHANGE# STATUS
-------------------------------------------------- ------------------ -------
/u01/app/oracle/oradata/ora11g/system01.dbf 2082030 SYSTEM
/u01/app/oracle/oradata/ora11g/sysaux01.dbf 2082030 ONLINE
/u01/app/oracle/oradata/ora11g/users01.dbf 2082030 ONLINE
/u01/app/oracle/oradata/ora11g/example01.dbf 2082030 ONLINE
/u01/app/oracle/oradata/ora11g/undotbs01.dbf 2082030 ONLINE
SQL> SELECT tablespace_name,logging FROM dba_tablespaces;
TABLESPACE_NAME LOGGING
------------------------------ ---------
SYSTEM LOGGING
SYSAUX LOGGING
USERS LOGGING
EXAMPLE LOGGING
UNDOTBS LOGGING
TEMP_NEW NOLOGGING
SQL> SELECT * FROM v$tempfile;
FILE# CREATION_CHANGE# CREATION_ TS# RFILE# STATUS ENABLED BYTES BLOCKS CREATE_BYTES BLOCK_SIZE NAME
---------- ---------------- --------- ---------- ---------- ------- ----------- ---------- ---------- ------------ ---------- --------------------------------------------------
4 2056893 11-JAN-24 24 1 ONLINE READ WRITE 2170552320 264960 20971520 8192 /u01/app/oracle/oradata/ora11g/temp_new01.dbf
SQL> SELECT * FROM v$controlfile;
STATUS NAME IS_RECOVERY_DEST_FILE BLOCK_SIZE FILE_SIZE_BLKS
------- ----------------------------------------------- ---------------------- ---------- --------------
/u01/app/oracle/oradata/ora11g/control01.ctl NO 16384 594
# Cold Backup : Data File, Temp File, Control File, Redo Log File
SELECT 'cp -av '||name||' /home/oracle/backup/arch/cold_20240112' FROM v$datafile
UNION ALL
SELECT 'cp -av '||name||' /home/oracle/backup/arch/cold_20240112' FROM v$tempfile
UNION ALL
SELECT 'cp -av '||name||' /home/oracle/backup/arch/cold_20240112' FROM v$controlfile
UNION ALL
SELECT 'cp -av '||member||' /home/oracle/backup/arch/cold_20240112' FROM v$logfile;
# 디렉터리 생성
SQL> ! mkdir -p /home/oracle/backup/arch/cold_20240112
# 초기 파라미터 파일 백업
SQL> CREATE PFILE ='/home/oracle/backup/arch/cold_20240112/initora11g_20240112.ora' FROM SPFILE;
SQL> ! ls /home/oracle/backup/arch/cold_20240112
initora11g_20240112.ora
# DB 정상 종료
SQL> SHUTDOWN IMMEDIATE
[oracle@oracle ~]$ cd /home/oracle/backup/arch/cold_20240112
# cp 일괄작업.sh
[oracle@oracle cold_20240112]$ vi backup.sh
cp -av /u01/app/oracle/oradata/ora11g/system01.dbf /home/oracle/backup/arch/cold_20240112
cp -av /u01/app/oracle/oradata/ora11g/sysaux01.dbf /home/oracle/backup/arch/cold_20240112
cp -av /u01/app/oracle/oradata/ora11g/users01.dbf /home/oracle/backup/arch/cold_20240112
cp -av /u01/app/oracle/oradata/ora11g/example01.dbf /home/oracle/backup/arch/cold_20240112
cp -av /u01/app/oracle/oradata/ora11g/undotbs01.dbf /home/oracle/backup/arch/cold_20240112
cp -av /u01/app/oracle/oradata/ora11g/temp_new01.dbf /home/oracle/backup/arch/cold_20240112
cp -av /u01/app/oracle/oradata/ora11g/control01.ctl /home/oracle/backup/arch/cold_20240112
cp -av /u01/app/oracle/oradata/ora11g/redo01.log /home/oracle/backup/arch/cold_20240112
cp -av /u01/app/oracle/oradata/ora11g/redo02.log /home/oracle/backup/arch/cold_20240112
cp -av /u01/app/oracle/oradata/ora11g/redo03.log /home/oracle/backup/arch/cold_20240112
:wq
[oracle@oracle cold_20240112]$ sh backup.sh > backup.log
[oracle@oracle cold_20240112]$ cat backup.log
[oracle@oracle cold_20240112]$ ls /home/oracle/backup/arch/cold_20240112
backup.log example01.dbf redo02.log system01.dbf users01.dbf
backup.sh initora11g_20240112.ora redo03.log temp_new01.dbf
control01.ctl redo01.log sysaux01.dbf undotbs01.dbf
SQL> STARTUP
■ 일관성 없는 Backup = Hot Backup = Online Backup = Open Backup
- Tablespace Level Backup
# Online Backup 시 확인 필수 정보
SELECT a.file#,a.name, a.checkpoint_change#,b.status,b.change#,b.time
FROM v$datafile a, v$backup b
WHERE a.file# = b.file#;
FILE# NAME CHECKPOINT_CHANGE# STATUS CHANGE# TIME
---------- -------------------------------------------------- ------------------ ------------------ ---------- ---------
1 /u01/app/oracle/oradata/ora11g/system01.dbf 2201592 NOT ACTIVE 0
2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf 2201592 NOT ACTIVE 0
4 /u01/app/oracle/oradata/ora11g/users01.dbf 2201592 NOT ACTIVE 0
5 /u01/app/oracle/oradata/ora11g/example01.dbf 2201592 NOT ACTIVE 0
6 /u01/app/oracle/oradata/ora11g/undotbs01.dbf 2201592 NOT ACTIVE 0
NOT ACTIVE : 백업진행 중이 아니아
# 백업대상 CP 일괄명령어 추출
SELECT 'cp -av '||name||' /home/oracle/backup/arch/hot_20240112' FROM v$datafile
UNION ALL
SELECT 'cp -av '||name||' /home/oracle/backup/arch/hot_20240112' FROM v$tempfile;
[oracle@oracle ~]$ mkdir -p /home/oracle/backup/arch/hot_20240112
[oracle@oracle ~]$ cd /home/oracle/backup/arch/hot_20240112
# shell 파일 생성
[oracle@oracle hot_20240112]$ vi backup.sh
cp -av /u01/app/oracle/oradata/ora11g/system01.dbf /home/oracle/backup/arch/hot_20240112
cp -av /u01/app/oracle/oradata/ora11g/sysaux01.dbf /home/oracle/backup/arch/hot_20240112
cp -av /u01/app/oracle/oradata/ora11g/users01.dbf /home/oracle/backup/arch/hot_20240112
cp -av /u01/app/oracle/oradata/ora11g/example01.dbf /home/oracle/backup/arch/hot_20240112
cp -av /u01/app/oracle/oradata/ora11g/undotbs01.dbf /home/oracle/backup/arch/hot_20240112
cp -av /u01/app/oracle/oradata/ora11g/temp_new01.dbf /home/oracle/backup/arch/hot_20240112
:wq
SELECT a.file#,a.name, a.checkpoint_change#,b.status,b.change#,b.time
FROM v$datafile a, v$backup b
WHERE a.file# = b.file#;
FILE# NAME CHECKPOINT_CHANGE# STATUS CHANGE# TIME
---------- -------------------------------------------------- ------------------ ------------------ ---------- ---------
1 /u01/app/oracle/oradata/ora11g/system01.dbf 2201592 NOT ACTIVE 0
2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf 2201592 NOT ACTIVE 0
4 /u01/app/oracle/oradata/ora11g/users01.dbf 2201592 NOT ACTIVE 0
5 /u01/app/oracle/oradata/ora11g/example01.dbf 2201592 NOT ACTIVE 0
6 /u01/app/oracle/oradata/ora11g/undotbs01.dbf 2201592 NOT ACTIVE 0
SQL> ALTER SYSTEM CHECKPOINT;
SELECT a.file#,a.name, a.checkpoint_change#,b.status,b.change#,b.time
FROM v$datafile a, v$backup b
WHERE a.file# = b.file#;
FILE# NAME CHECKPOINT_CHANGE# STATUS CHANGE# TIME
---------- -------------------------------------------------- ------------------ ------------------ ---------- ---------
1 /u01/app/oracle/oradata/ora11g/system01.dbf 2203248 NOT ACTIVE 0
2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf 2203248 NOT ACTIVE 0
4 /u01/app/oracle/oradata/ora11g/users01.dbf 2203248 NOT ACTIVE 0
5 /u01/app/oracle/oradata/ora11g/example01.dbf 2203248 NOT ACTIVE 0
6 /u01/app/oracle/oradata/ora11g/undotbs01.dbf 2203248 NOT ACTIVE 0
# Online Backup 설정
SQL> ALTER DATABASE BEGIN BACKUP;
- 모든 테이블스페이스를 백업모드로 설정
- Full Checkpoint 발생
- Data Buffer Cache의 내용이 Data File에 기록, 이후 변경 작업은 모두 Redo Log에 기록
SELECT a.file#,a.name, a.checkpoint_change#,b.status,b.change#,b.time
FROM v$datafile a, v$backup b
WHERE a.file# = b.file#;
FILE# NAME CHECKPOINT_CHANGE# STATUS CHANGE# TIME
---------- -------------------------------------------------- ------------------ ------------------ ---------- ---------
1 /u01/app/oracle/oradata/ora11g/system01.dbf 2203301 ACTIVE 2203301 12-JAN-24
2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf 2203301 ACTIVE 2203301 12-JAN-24
4 /u01/app/oracle/oradata/ora11g/users01.dbf 2203301 ACTIVE 2203301 12-JAN-24
5 /u01/app/oracle/oradata/ora11g/example01.dbf 2203301 ACTIVE 2203301 12-JAN-24
6 /u01/app/oracle/oradata/ora11g/undotbs01.dbf 2203301 ACTIVE 2203301 12-JAN-24
CHECKPOINT_CHANGE# : 현재 시점의 SCN
STATUS : ACTIVE : 백업대기
CHANGE# : 백업 시점의 SCN
TIME : 백업작업일
[oracle@oracle ~]$ cd /home/oracle/backup/arch/hot_20240112
# shell 실행 : cp
[oracle@oracle hot_20240112]$ sh backup.sh > backup.log
[oracle@oracle hot_20240112]$ ls
backup.log backup.sh example01.dbf sysaux01.dbf system01.dbf
temp_new01.dbf undotbs01.dbf users01.dbf
# Online Backup 종료
SQL> ALTER DATABASE END BACKUP;
SELECT a.file#,a.name, a.checkpoint_change#,b.status,b.change#,b.time
FROM v$datafile a, v$backup b
WHERE a.file# = b.file#;
FILE# NAME CHECKPOINT_CHANGE# STATUS CHANGE# TIME
---------- -------------------------------------------------- ------------------ ------------------ ---------- ---------
1 /u01/app/oracle/oradata/ora11g/system01.dbf 2203301 NOT ACTIVE 2203301 12-JAN-24
2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf 2203301 NOT ACTIVE 2203301 12-JAN-24
4 /u01/app/oracle/oradata/ora11g/users01.dbf 2203301 NOT ACTIVE 2203301 12-JAN-24
5 /u01/app/oracle/oradata/ora11g/example01.dbf 2203301 NOT ACTIVE 2203301 12-JAN-24
6 /u01/app/oracle/oradata/ora11g/undotbs01.dbf 2203301 NOT ACTIVE 2203301 12-JAN-24
# Online 중에 Control File Backup
SQL> ALTER DATABASE BACKUP CONTROLFILE TO '/home/oracle/backup/arch/hot_20240112/control01.ctl';
SQL> ! ls /home/oracle/backup/arch/hot_20240112
backup.log backup.sh control01.ctl example01.dbf sysaux01.dbf
system01.dbf temp_new01.dbf undotbs01.dbf users01.dbf
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --------- ---------------- ------------- ----------- ------------ ---------
1 1 4 52428800 512 1 NO CURRENT 2082030 12-JAN-24 2.8147E+14
2 1 2 52428800 512 1 YES INACTIVE 2047260 11-JAN-24 2075466 12-JAN-24
3 1 3 52428800 512 1 YES INACTIVE 2075466 12-JAN-24 2082030 12-JAN-24
SQL> select sequence#, name from v$archived_log;
SEQUENCE# NAME
---------- --------------------------------------------------
2 /home/oracle/arch1/arch_1_2_1157971650.arc
2 /home/oracle/arch2/arch_1_2_1157971650.arc
3 /home/oracle/arch1/arch_1_3_1157971650.arc
3 /home/oracle/arch2/arch_1_3_1157971650.arc
# 현재 current 상태를 archive log
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
SQL> SELECT * FROM v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --------- ---------------- ------------- ----------- ------------ ---------
1 1 4 52428800 512 1 YES ACTIVE 2082030 12-JAN-24 2205455 12-JAN-24
2 1 5 52428800 512 1 NO CURRENT 2205455 12-JAN-24 2.8147E+14
3 1 3 52428800 512 1 YES INACTIVE 2075466 12-JAN-24 2082030 12-JAN-24
SQL> SELECT sequence#, name FROM v$archived_log;
SEQUENCE# NAME
---------- --------------------------------------------------
2 /home/oracle/arch1/arch_1_2_1157971650.arc
2 /home/oracle/arch2/arch_1_2_1157971650.arc
3 /home/oracle/arch1/arch_1_3_1157971650.arc
3 /home/oracle/arch2/arch_1_3_1157971650.arc
4 /home/oracle/arch1/arch_1_4_1157971650.arc
4 /home/oracle/arch2/arch_1_4_1157971650.arc
<< 시나리오 1 >> 운영 중 offline되는 데이터 파일 손상되었을 때 복구 방식
- 운영 중 offline되는 : system, undo 제외
# 백업받는 시점의 SCN 정보, 현재 데이터 파일의 scn 정보
SELECT a.file#,a.name, a.checkpoint_change#,b.status,b.change#,b.time
FROM v$datafile a, v$backup b
WHERE a.file# = b.file#;
FILE# NAME CHECKPOINT_CHANGE# STATUS CHANGE# TIME
---------- -------------------------------------------------- ------------------ ------------------ ---------- ---------
1 /u01/app/oracle/oradata/ora11g/system01.dbf 2205455 NOT ACTIVE 2203301 12-JAN-24
2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf 2205455 NOT ACTIVE 2203301 12-JAN-24
4 /u01/app/oracle/oradata/ora11g/users01.dbf 2205455 NOT ACTIVE 2203301 12-JAN-24
5 /u01/app/oracle/oradata/ora11g/example01.dbf 2205455 NOT ACTIVE 2203301 12-JAN-24
6 /u01/app/oracle/oradata/ora11g/undotbs01.dbf 2205455 NOT ACTIVE 2203301 12-JAN-24
CHECKPOINT_CHANGE# : 현재 데이터파일의 SCN 정보 2205455
CHANGE# : 백업 시점 데이터 파일 SCN 정보 2203301 -> 갭은 redo 정보가 복구가 가능한다.
TIME : 백업 작업일
# 마지막 백업받은 시점의 scn번호를 기준으로 현재 scn번호까지 redo log 확인
SQL> SELECT * FROM v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --------- ---------------- ------------- ----------- ------------ ---------
1 1 4 52428800 512 1 YES INACTIVE 2082030 12-JAN-24 2205455 12-JAN-24
2 1 5 52428800 512 1 NO CURRENT 2205455 12-JAN-24 2.8147E+14
3 1 3 52428800 512 1 YES INACTIVE 2075466 12-JAN-24 2082030 12-JAN-24
FIRST_CHANGE# >=
NEXT_CHANGE# <
>> 백업 시점 CHANGE# : 2203301 = SEQUENCE# 4
# 아카이브 확인
select * from v$archived_log;
SQL> select SEQUENCE#,NAME,FIRST_CHANGE#,NEXT_CHANGE# from v$archived_log;
SEQUENCE# NAME FIRST_CHANGE# NEXT_CHANGE#
---------- -------------------------------------------------- ------------- ------------
2 /home/oracle/arch1/arch_1_2_1157971650.arc 2047260 2075466
2 /home/oracle/arch2/arch_1_2_1157971650.arc 2047260 2075466
3 /home/oracle/arch1/arch_1_3_1157971650.arc 2075466 2082030
3 /home/oracle/arch2/arch_1_3_1157971650.arc 2075466 2082030
4 /home/oracle/arch1/arch_1_4_1157971650.arc 2082030 2205455
4 /home/oracle/arch2/arch_1_4_1157971650.arc 2082030 2205455
# 물리적 파일 체크
SQL> ! ls /home/oracle/arch1 /home/oracle/arch2
/home/oracle/arch1:
arch_1_2_1157971650.arc arch_1_3_1157971650.arc arch_1_4_1157971650.arc
/home/oracle/arch2:
arch_1_2_1157971650.arc arch_1_3_1157971650.arc arch_1_4_1157971650.arc
SQL> create table hr.test1(id number) tablespace users;
SQL> insert into hr.test1(id) values(1);
SQL> commit;
select f.file_name
from dba_extents e, dba_data_files f
where f.file_id = e.file_id
and e.segment_name = 'TEST1'
and e.owner = 'HR';
FILE_NAME
--------------------------------------------------
/u01/app/oracle/oradata/ora11g/users01.dbf
# 장애 발생
! rm /u01/app/oracle/oradata/ora11g/users01.dbf
SQL> select * from hr.test1;
ID
----------
1
>> 메모리에 데이터가 있어서 조회 가능
SQL> CREATE TABLE hr.test2 TABLESPACE users AS SELECT * FROM hr.employees;
>> 오류발생 : users에 속한 데이터 파일이 없다.
>> DB는 운영 중
--ORA-01116: error in opening database file 4
--ORA-01110: data file 4: '/u01/app/oracle/oradata/ora11g/users01.dbf'
--ORA-27041: unable to open file
# 복구 방법
1. 대상 테이블 스페이스를 offline immediate 수행
alter tablespace users offline immediate
>> offline immediate : checkpoint 발생하지않는다.
SELECT name,checkpoint_change#,status FROM v$datafile;
status : recover
2. 가장 최근의 hot backup file을 문제되는 파일위치에 복사
! cp -av /home/oracle/backup/arch/hot_20240112/users01.dbf /u01/app/oracle/oradata/ora11g/
3. 백업 이후에 변경된 redo 정보를 적용
recover tablespace users;
4. 대상 테이블 스페이스를 online으로 변경
alter tablespace users online;
SELECT name,checkpoint_change#,status FROM v$datafile;
status : online
5. 확인
select * from hr.test1;
create table hr.test2 tablespace users as select * from hr.employees;
<< 시나리오 2 >> DB가 종료된 후 데이터파일 삭제 후 DB 올릴때 문제 복구
- 현실 : 시나리오 1 진행 중 데이터파일 손상 후 작업 중에 DB가 내려가버렸다.
# 장애 발생
! rm /u01/app/oracle/oradata/ora11g/users01.dbf
SQL> create table hr.test2 tablespace users as select * from hr.employees;
ORA-03135: connection lost contact
Process ID: 12612
Session ID: 9 Serial number: 3
--Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_dbw1_12586.trc:
--ORA-01116: error in opening database file 4
--ORA-01110: data file 4: '/u01/app/oracle/oradata/ora11g/users01.dbf'
--ORA-27041: unable to open file
-- ...
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/u01/app/oracle/oradata/ora11g/users01.dbf'
>> 여러개의 파일이 손상되어도 오류메세지는 1개만 나온다
>> DB MOUNT 단계
SQL> select * from v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- -------------------------------------------------- ---------- ---------
4 ONLINE ONLINE FILE NOT FOUND 0
1. 오류대상만 offline 상태로 변경
alter database datafile '/u01/app/oracle/oradata/ora11g/users01.dbf' offline;
SQL> select * from v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- -------------------------------------------------- ---------- ---------
4 OFFLINE OFFLINE FILE NOT FOUND 0
2. DB OPEN
SQL> alter database open;
SQL> SELECT name,checkpoint_change#,status FROM v$datafile;
NAME CHECKPOINT_CHANGE# STATUS
-------------------------------------------------- ------------------ -------
/u01/app/oracle/oradata/ora11g/system01.dbf 2227528 SYSTEM
/u01/app/oracle/oradata/ora11g/sysaux01.dbf 2227528 ONLINE
/u01/app/oracle/oradata/ora11g/users01.dbf 2205455 RECOVER
/u01/app/oracle/oradata/ora11g/example01.dbf 2227528 ONLINE
/u01/app/oracle/oradata/ora11g/undotbs01.dbf 2227528 ONLINE
3. 가장 최근의 백업파일을 문제되는 파일위치에 복사
SQL> ! cp -av /home/oracle/backup/arch/hot_20240112/users01.dbf /u01/app/oracle/oradata/ora11g/
4. 백업 이후에 변경된 redo 정보를 적용
SQL> recover tablespace users;
SQL> select * from v$recover_file;
no rows selected
5. 대상 테이블 스페이스를 online으로 변경
SQL> alter tablespace users online;
SQL> SELECT name,checkpoint_change#,status FROM v$datafile;
NAME CHECKPOINT_CHANGE# STATUS
-------------------------------------------------- ------------------ -------
/u01/app/oracle/oradata/ora11g/system01.dbf 2227528 SYSTEM
/u01/app/oracle/oradata/ora11g/sysaux01.dbf 2227528 ONLINE
/u01/app/oracle/oradata/ora11g/users01.dbf 2228087 ONLINE
/u01/app/oracle/oradata/ora11g/example01.dbf 2227528 ONLINE
/u01/app/oracle/oradata/ora11g/undotbs01.dbf 2227528 ONLINE
6. 확인
SQL> select * from hr.test1;
ID
----------
1
SQL> create table hr.test2 tablespace users as select * from hr.employees;
Table created.
SELECT a.file#,a.name, a.checkpoint_change#,b.status,b.change#,b.time
FROM v$datafile a, v$backup b
WHERE a.file# = b.file#;
FILE# NAME CHECKPOINT_CHANGE# STATUS CHANGE# TIME
---------- -------------------------------------------------- ------------------ ------------------ ---------- ---------
1 /u01/app/oracle/oradata/ora11g/system01.dbf 2227528 NOT ACTIVE 2203301 12-JAN-24
2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf 2227528 NOT ACTIVE 2203301 12-JAN-24
4 /u01/app/oracle/oradata/ora11g/users01.dbf 2228087 NOT ACTIVE 2203301 12-JAN-24
5 /u01/app/oracle/oradata/ora11g/example01.dbf 2227528 NOT ACTIVE 2203301 12-JAN-24
6 /u01/app/oracle/oradata/ora11g/undotbs01.dbf 2227528 NOT ACTIVE 2203301 12-JAN-24
>> users01.dbf의 깨진 시점까지의 복구는 되었지만 SCN 번호가 다르다
SQL> shutdown abort
SQL> startup
>> open 시점에 동기화 체크 = CHECKPOINT_CHANGE# > 싱크 맞추기위해 리두 적용
-> instance recovery가 하는 일
SELECT a.file#,a.name, a.checkpoint_change#,b.status,b.change#,b.time
FROM v$datafile a, v$backup b
WHERE a.file# = b.file#;
FILE# NAME CHECKPOINT_CHANGE# STATUS CHANGE# TIME
---------- -------------------------------------------------- ------------------ ------------------ ---------- ---------
1 /u01/app/oracle/oradata/ora11g/system01.dbf 2248299 NOT ACTIVE 2203301 12-JAN-24
2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf 2248299 NOT ACTIVE 2203301 12-JAN-24
4 /u01/app/oracle/oradata/ora11g/users01.dbf 2248299 NOT ACTIVE 2203301 12-JAN-24
5 /u01/app/oracle/oradata/ora11g/example01.dbf 2248299 NOT ACTIVE 2203301 12-JAN-24
6 /u01/app/oracle/oradata/ora11g/undotbs01.dbf 2248299 NOT ACTIVE 2203301 12-JAN-24
파샬은 ......
백업시점 scn#1000
...
현재 scn#1100 <- 장애 발생
users01.dbf #1050
백업파일#1000로 현재시점#1100까지 복구
users01.dbf #1100 로
<< 시나리오 3 >> DB가 종료 후 데이터파이을 삭제 후 DB 올릴 때 문제
select * from v$datafile;
SQL> SELECT name,checkpoint_change#,status FROM v$datafile;
NAME CHECKPOINT_CHANGE# STATUS
-------------------------------------------------- ------------------ -------
/u01/app/oracle/oradata/ora11g/system01.dbf 2248299 SYSTEM
/u01/app/oracle/oradata/ora11g/sysaux01.dbf 2248299 ONLINE
/u01/app/oracle/oradata/ora11g/users01.dbf 2248299 ONLINE
/u01/app/oracle/oradata/ora11g/example01.dbf 2248299 ONLINE
/u01/app/oracle/oradata/ora11g/undotbs01.dbf 2248299 ONLINE
SQL> shutdown immediate
# 장애 유발
! rm /u01/app/oracle/oradata/ora11g/users01.dbf
! rm /u01/app/oracle/oradata/ora11g/example01.dbf
SQL> startup
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/u01/app/oracle/oradata/ora11g/users01.dbf'
>> 오류 파일 정보가 1개밖에 안나온다!! >> 항상 v$recover_file 여기에서 확인하자.
SQL> select * from v$recover_file;
FILE# ONLINE ONLINE_STATUS ERROR CHANGE# TIME
---------- ------- -------------- -------------------------------------------------- ---------- ---------
4 ONLINE ONLINE FILE NOT FOUND 0
5 ONLINE ONLINE FILE NOT FOUND 0
SQL> alter database datafile 5 offline;
SQL> alter database datafile 5 offline;
SQL> select * from v$recover_file;
FILE# ONLINE ONLINE_STATUS ERROR CHANGE# TIME
---------- ------- -------------- -------------------------------------------------- ---------- ---------
4 OFFLINE OFFLINE FILE NOT FOUND 0
5 OFFLINE OFFLINE FILE NOT FOUND 0
SQL> SELECT name,checkpoint_change#,status FROM v$datafile;
NAME CHECKPOINT_CHANGE# STATUS
-------------------------------------------------- ------------------ -------
/u01/app/oracle/oradata/ora11g/system01.dbf 2249244 SYSTEM
/u01/app/oracle/oradata/ora11g/sysaux01.dbf 2249244 ONLINE
/u01/app/oracle/oradata/ora11g/users01.dbf 2249244 OFFLINE
/u01/app/oracle/oradata/ora11g/example01.dbf 2249244 OFFLINE
/u01/app/oracle/oradata/ora11g/undotbs01.dbf 2249244 ONLINE
# DB OPEN
SQL> alter database open;
1. 가장 최근의 hot backup file을 문제되는 파일위치에 복사
! cp -av /home/oracle/backup/arch/hot_20240112/users01.dbf /u01/app/oracle/oradata/ora11g/
! cp -av /home/oracle/backup/arch/hot_20240112/example01.dbf /u01/app/oracle/oradata/ora11g/
SQL> ! ls /home/oracle/backup/arch/hot_20240112
backup.log backup.sh control01.ctl example01.dbf sysaux01.dbf system01.dbf temp_new01.dbf undotbs01.dbf users01.dbf
2. 백업 이후에 변경된 redo 정보를 적용 (Tablespace Level)
SQL> recover tablespace users;
SQL> recover tablespace example;
ORA-00279: change 2203301 generated at 01/12/2024 14:07:23 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch2/arch_1_4_1157971650.arc
ORA-00280: change 2203301 for thread 1 is in sequence #4
>> 백업받은 시점의 리두가 바껴서 #4부터는 아카이드 적용해야한다.
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO -- 아카이브에서 찾아서 적용해줘
Log applied.
Media recovery complete.
3. 대상 테이블 스페이스를 online으로 변경
SQL> alter tablespace users online;
SQL> alter tablespace example online;
SQL> select * from v$recover_file;
no rows selected
SQL> SELECT name,checkpoint_change#,status FROM v$datafile;
NAME CHECKPOINT_CHANGE# STATUS
-------------------------------------------------- ------------------ -------
/u01/app/oracle/oradata/ora11g/system01.dbf 2249247 SYSTEM
/u01/app/oracle/oradata/ora11g/sysaux01.dbf 2249247 ONLINE
/u01/app/oracle/oradata/ora11g/users01.dbf 2249750 ONLINE
/u01/app/oracle/oradata/ora11g/example01.dbf 2249837 ONLINE
/u01/app/oracle/oradata/ora11g/undotbs01.dbf 2249247 ONLINE
4. 확인
SQL> select count(*) from hr.employees;
COUNT(*)
----------
106
SQL> create table hr.test5 tablespace users as select * from hr.employees;
Table created.
select * from dba_tables where owner = 'HR';
Cold Backup은 DB 종료 후 전체 DB를 백업
Hot Backup은 Tablespace 단위로 백업
노아카이브, 아카이브 둘 다 system, undo 제외하고 운영 중(온라인) 백업&리커버리 가능
노아카이브 최신 리두가 없다
아카이브는 최신 리두가 있다
<< Archive Log Mode >>
# 환경설정
ALTER SYSTEM SET log_archive_dest_#
ALTER SYSTEM SET log_archive_format
SHUTDOWN IMMEDIATE
STARTUP MOUNT
ALTER DATABASE ARCHIVELOG
ALTER DATABASE OPEN
# Cold Backup
CREATE PFILE
SHUTDOWN
mkdir
cp
STARTUP
ALTER SYSTEM ARCHIVE LOG CURRENT
# Hot Backup
ALTER DATABASE
BEGIN BACKUP
FROM v$datafile a, v$backup b
mkdir
cp
ALTER DATABASE END BACKUP
FROM v$datafile a, v$backup b
ALTER DATABASE BACKUP CONTROLFILE
ALTER SYSTEM ARCHIVE LOG CURRENT
<< 시나리오 1 >> 운영 중 DataFile 손상
tablespace offline immediate
cp
recover tablespace
alter tablespace ONLINE
<< 시나리오 2 >> 운영 중 DataFile 손상, DB 끊어짐
conn
mount
database datafile offline
database open
cp
recover tablespace
alter tablespace online
shutdown abort
startup
<< 시나리오3 > db 종료 - DataFile 손상 - open
shutdown immediate
mount
database datafile offline
database open
cp
recover tablespace - AUTO
tablespace online
'Oracle 11g > Backup & Recovery' 카테고리의 다른 글
| 66 Backup&Recovery, Archive Log Mode, Backup (0) | 2024.01.16 |
|---|---|
| 65 Backup&Recovery, Archive Log Mode (0) | 2024.01.15 |
| 63 Backup&Recovery, Noarchive Log, Redo Log File, Temp File (0) | 2024.01.11 |
| 62 Backup&Recovery, Noarchive Log, Data File, Undo Data File (1) | 2024.01.10 |
| 61 Backup&Recovery, Noarchive Log, Data File (0) | 2024.01.09 |