Backup-Recovery 모든 시나리오 User Managed

YoonSeo Park ·2024년 1월 16일

Oracle Architecture

목록 보기

9/11

2023/01/15

● 백업 대상 확인할것

테이블스페이스의 마지막 체크포인트 정보

select a.file#, b.name tbs_name, a.name file_name,
        a.status, a.checkpoint_change#
from v$datafile a, v$tablespace b
where a.ts# = b.ts#;

백업된 datafile 확인

select a.file#, a.name, a.checkpoint_change#,
        b.status, b.change#, to_char(b.time,'yyyy/mm/dd hh24:mi:ss')
from v$datafile a, v$backup b
where a.file#=b.file#;

tempfile 확인

select * from database_properties;
select * from v$tempfile;

redolog file 확인

select * from v$log;

아카이브 로그 확인

archive log list
select destination, binding, status from v$archive_dest;

select sequence#, name, first_change#, next_change#, next_time
from v$archived_log;

아카이브 파일 확인

SQL> ! ls /home/oracle/arch1 /home/oracle/arch2
/home/oracle/arch1:
arch_1_3_1157971653.arc  arch_1_5_1157971653.arc  arch_1_7_1157971653.arc
arch_1_4_1157971653.arc  arch_1_6_1157971653.arc

/home/oracle/arch2:
arch_1_3_1157971653.arc  arch_1_5_1157971653.arc  arch_1_7_1157971653.arc
arch_1_4_1157971653.arc  arch_1_6_1157971653.arc

▶ 4. 백업 받지 않은 테이블스페이스에 데이터 파일 손상 시나리오

● 1. 작업

테이블스페이스 생성

create tablespace data01
datafile '/u01/app/oracle/oradata/ora11g/data01.dbf' size 5m;

테이블 생성

create table hr.dept_temp tablespace data01
as 
select * from hr.departments;

로그스위치 발생

alter system switch logfile;

아카이브 로그 확인

select sequence#, name, first_change#, next_change#, next_time
from v$archived_log;

테이블 만들어진 데이터파일 확인

select f.file_name
from dba_extents e, dba_data_files f
where e.file_id = f.file_id
and e.segment_name = 'DEPT_TEMP'
and e.owner = 'HR';

● 2. 장애발생 : datafile 삭제

SQL> ! rm /u01/app/oracle/oradata/ora11g/data01.dbf

SQL> shutdown immediate
ORA-01116: error in opening database file 3
ORA-01110: data file 3: '/u01/app/oracle/oradata/ora11g/data01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3


SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  531476480 bytes
Fixed Size                  1365796 bytes
Variable Size             201328860 bytes
Database Buffers          322961408 bytes
Redo Buffers                5820416 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/u01/app/oracle/oradata/ora11g/data01.dbf'

● 3. recover file 확인후, offline, db open

recover file 확인

select * from v$recover_file;

문제되는 datafile offilne 으로 변경

alter database datafile 3 offline;

alter database datafile '/u01/app/oracle/oradata/ora11g/data01.dbf' offline;

datafile 상태확인

select file#, name, status from v$datafile;
---
1	/u01/app/oracle/oradata/ora11g/system01.dbf	SYSTEM
2	/u01/app/oracle/oradata/ora11g/sysaux01.dbf	ONLINE
3	/u01/app/oracle/oradata/ora11g/data01.dbf	RECOVER
4	/u01/app/oracle/oradata/ora11g/users01.dbf	ONLINE
5	/u01/app/oracle/oradata/ora11g/example01.dbf	ONLINE
6	/u01/app/oracle/oradata/ora11g/undotbs01.dbf	ONLINE

db open

SQL> alter database open;

Database altered.

● 3. 복구작업 수행해야할 기존 디렉토리에 데이터파일 재생성

alter database create datafile '/u01/app/oracle/oradata/ora11g/data01.dbf';

재생성 했는데 그대로 5m 인 이유
: 딕셔너리와 컨트롤파일이 테이블스페이스 생성시 정보를 다 가지고있으므로

확인

select file#, name, status from v$datafile;

만약 기존위치 디스크가 문제가 있다면 새로운위치에 만든다.

기존위치가 아닌 새로운 디렉토리에 데이터파일 재생성
alter database create datafile '/u01/app/oracle/oradata/ora11g/data01.dbf' as '/home/oracle/data01.dbf';

● 4. recover 테이블스페이스

SQL> recover tablespace data01;
ORA-00279: change 1605680 generated at 01/12/2024 03:28:57 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch2/arch_1_8_1157971653.arc
ORA-00280: change 1605680 for thread 1 is in sequence #8


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto  -- auto 입력
ORA-00279: change 1606178 generated at 01/12/2024 03:34:05 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch2/arch_1_9_1157971653.arc
ORA-00280: change 1606178 for thread 1 is in sequence #9


Log applied.
Media recovery complete.

● 5. 테이블스페이스 online으로 변경

SQL> alter tablespace data01 online;

Tablespace altered.

datafile 확인

select file#, name, status from v$datafile;

생성했던 테이블 조회

SQL> select count(*) from hr.dept_temp;

  COUNT(*)
----------
        27

▶ 5. 테이블스페이스에 데이터 파일 손상시 기존 위치가 아닌 새로운 위치로 복원 작업 시나리오

● 1. 작업 : hot backup, table 생성

table 만들기

create table hr.emp_temp tablespace data01
as 
select * from hr.employees;

select count(*) from hr.emp_temp;

hot backup

SQL> alter tablespace data01 begin backup;

Tablespace altered.

SQL> ! cp -av /u01/app/oracle/oradata/ora11g/data01.dbf /home/oracle/backup/arch/hot_20240112/
‘/u01/app/oracle/oradata/ora11g/data01.dbf’ -> ‘/home/oracle/backup/arch/hot_20240112/data01.dbf’

SQL> alter tablespace data01 end backup;

Tablespace altered.

백업 확인

select a.file#, a.name, a.checkpoint_change#,
        b.status, b.change#, to_char(b.time,'yyyy/mm/dd hh24:mi:ss')
from v$datafile a, v$backup b
where a.file#=b.file#;

select * from v$log;

table 만들기

create table hr.loc_temp tablespace data01
as
select * from hr.locations;

select f.file_name
from dba_extents e, dba_data_files f
where e.file_id = f.file_id
and e.segment_name = 'LOC_TEMP'
and e.owner = 'HR';

로그 스위치 발생

alter system switch logfile;

● 2. 장애발생 : datafile 삭제

SQL> ! rm /u01/app/oracle/oradata/ora11g/data01.dbf

● 3. 문제되는것 offline immediate 변경

SQL> alter tablespace data01 offline immediate;

Tablespace altered.

datafile 확인

select name,status from v$datafile;

● 4. 기존 위치가 아닌 새로운 위치로 restore

SQL> ! cp -av /home/oracle/backup/arch/hot_20240112/data01.dbf /home/oracle
‘/home/oracle/backup/arch/hot_20240112/data01.dbf’ -> ‘/home/oracle/data01.dbf’

● 5. control file 에게 위치변경 작업

alter database rename file '이전경로의 파일' to '새로운경로의 파일';

alter database rename file 
'/u01/app/oracle/oradata/ora11g/data01.dbf' to 
'/home/oracle/data01.dbf';

● 6. 아카이브 적용

SQL> recover tablespace data01;
ORA-00279: change 1629177 generated at 01/12/2024 04:31:27 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch2/arch_1_12_1157971653.arc
ORA-00280: change 1629177 for thread 1 is in sequence #12


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
Log applied.
Media recovery complete.

● 7. tablespace online으로 변경

SQL> alter tablespace data01 online;

Tablespace altered.

테이블 확인

SQL> select count(*) from hr.loc_temp;

  COUNT(*)
----------
        23

▶ 6. 테이블스페이스에 속한 여러 데이터파일들 중에 특정한이 손상시나리오 (offline temporary)

● 1. 작업

테이블스페이스에 datafile 추가

alter tablespace data01 add datafile '/u01/app/oracle/oradata/ora11g/data02.dbf' size 10m;

확인

select tablespace_name, file_name
from dba_data_files;

테이블생성

create table hr.emp_2024 tablespace data01
as
select * from hr.employees;

어느 datafile인지 확인

select f.file_name
from dba_extents e, dba_data_files f
where e.file_id = f.file_id
and e.segment_name = 'EMP_2024'
and e.owner = 'HR';

대용량 데이터를 위해서 자기자신 insert 여러번

insert into hr.emp_2024
select * from hr.emp_2024;

segment 사이즈 확인

select bytes
from dba_segments
where segment_name = 'EMP_2024'
and owner = 'HR';

datafile에 extent가 몇개 만들어졌는지

select f.tablespace_name, f.file_name, count(*)
from dba_extents e, dba_data_files f
where e.file_id = f.file_id
and e.segment_name = 'EMP_2024'
and e.owner = 'HR'
group by f.tablespace_name, f.file_name;

hot backup 받기

SQL> alter tablespace data01 begin backup;

Tablespace altered.

SQL> ! cp -av /u01/app/oracle/oradata/ora11g/data02.dbf /home/oracle/backup/arch/hot_20240112/
‘/u01/app/oracle/oradata/ora11g/data02.dbf’ -> ‘/home/oracle/backup/arch/hot_20240112/data02.dbf’

SQL> alter tablespace data01 end backup;

Tablespace altered.

로그스위치 발생

alter system switch logfile;

테이블생성

create table hr.dept_2024 tablespace data01
as 
select * from hr.departments;

생성된 테이블이 어디 datafile인지 확인

select f.tablespace_name, f.file_name, count(*)
from dba_extents e, dba_data_files f
where e.file_id = f.file_id
and e.segment_name = 'DEPT_2024'
and e.owner = 'HR'
group by f.tablespace_name, f.file_name;
------
DATA01	/u01/app/oracle/oradata/ora11g/data02.dbf	1

● 2. 장애발생 : datafile 삭제

SQL> ! rm /u01/app/oracle/oradata/ora11g/data02.dbf

● 3. 테이블스페이스에 속한 데이터파일을 offline temporary

select a.file#, b.name tbs_name, a.name file_name,
        a.status, a.checkpoint_change#
from v$datafile a, v$tablespace b
where a.ts# = b.ts#;
---------
1	SYSTEM	/u01/app/oracle/oradata/ora11g/system01.dbf		SYSTEM	1633230
2	SYSAUX	/u01/app/oracle/oradata/ora11g/sysaux01.dbf		ONLINE	1633230
3	DATA01	/home/oracle/data01.dbf							ONLINE	1633230
4	USERS	/u01/app/oracle/oradata/ora11g/users01.dbf		ONLINE	1633230
5	EXAMPLE	/u01/app/oracle/oradata/ora11g/example01.dbf	ONLINE	1633230
6	UNDOTBS	/u01/app/oracle/oradata/ora11g/undotbs01.dbf	ONLINE	1633230
7	DATA01	/u01/app/oracle/oradata/ora11g/data02.dbf		ONLINE	1633230

offline temporary
: 테이블스페이스에 속한 데이터파일을 offline 으로 수행하되 가능한 데이터파일은 체크포인트 발생하고 가능하지 않은 데이터파일은 그냥 offline 으로 수행하라는 의도
SQL> alter tablespace data01 offline temporary;
Tablespace altered.

select a.file#, b.name tbs_name, a.name file_name,
        a.status, a.checkpoint_change#
from v$datafile a, v$tablespace b
where a.ts# = b.ts#;
---------
1	SYSTEM	/u01/app/oracle/oradata/ora11g/system01.dbf		SYSTEM	1633230
2	SYSAUX	/u01/app/oracle/oradata/ora11g/sysaux01.dbf		ONLINE	1633230
3	DATA01	/home/oracle/data01.dbf					OFFLINE	1637372
4	USERS	/u01/app/oracle/oradata/ora11g/users01.dbf		ONLINE	1633230
5	EXAMPLE	/u01/app/oracle/oradata/ora11g/example01.dbf	ONLINE	1633230
6	UNDOTBS	/u01/app/oracle/oradata/ora11g/undotbs01.dbf	ONLINE	1633230
7	DATA01	/u01/app/oracle/oradata/ora11g/data02.dbf		OFFLINE	1637372

● 4. 백업본을 이용하여 restore

SQL> ! cp -av /home/oracle/backup/arch/hot_20240112/data02.dbf /u01/app/oracle/oradata/ora11g/data02.dbf
‘/home/oracle/backup/arch/hot_20240112/data02.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/data02.dbf’

● 5. 아카이브 적용

SQL> recover tablespace data01;
ORA-00279: change 1629177 generated at 01/12/2024 04:31:27 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch2/arch_1_12_1157971653.arc
ORA-00280: change 1629177 for thread 1 is in sequence #12


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
Log applied.
Media recovery complete.

● 6. tablespace online으로 변경

SQL> alter tablespace data01 online;

Tablespace altered.

테이블확인

select count(*) from hr.dept_2024;

▶ 7. 시스템데이터파일 손상 시나리오

● 1. 장애발생 : system 데이터파일 삭제

백업 데이터파일 확인

select a.file#, a.name, a.checkpoint_change#,
        b.status, b.change#, to_char(b.time,'yyyy/mm/dd hh24:mi:ss')
from v$datafile a, v$backup b
where a.file#=b.file#;

장애발생 : system 데이터파일 삭제

SQL> ! rm /u01/app/oracle/oradata/ora11g/system01.dbf

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  531476480 bytes
Fixed Size                  1365796 bytes
Variable Size             201328860 bytes
Database Buffers          322961408 bytes
Redo Buffers                5820416 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/u01/app/oracle/oradata/ora11g/system01.dbf'


SQL> select * from v$recover_file;
---
1	ONLINE	ONLINE	FILE NOT FOUND	0

● 2. 백업본을 이용하여 restore

SQL> ! cp -av /home/oracle/backup/arch/hot_20240112/system01.dbf /u01/app/oracle/oradata/ora11g/system01.dbf
‘/home/oracle/backup/arch/hot_20240112/system01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/system01.dbf’

● 3. recover 테이블스페이스 (redo log 적용)

SQL> recover tablespace system;
ORA-00279: change 1581340 generated at 01/12/2024 02:36:49 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch2/arch_1_6_1157971653.arc
ORA-00280: change 1581340 for thread 1 is in sequence #6


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 1581433 generated at 01/12/2024 02:38:15 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch2/arch_1_7_1157971653.arc
ORA-00280: change 1581433 for thread 1 is in sequence #7


ORA-00279: change 1601618 generated at 01/12/2024 02:42:43 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch2/arch_1_8_1157971653.arc
ORA-00280: change 1601618 for thread 1 is in sequence #8


ORA-00279: change 1606178 generated at 01/12/2024 03:34:05 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch2/arch_1_9_1157971653.arc
ORA-00280: change 1606178 for thread 1 is in sequence #9


ORA-00279: change 1606181 generated at 01/12/2024 03:34:06 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch2/arch_1_10_1157971653.arc
ORA-00280: change 1606181 for thread 1 is in sequence #10


ORA-00279: change 1606185 generated at 01/12/2024 03:34:09 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch2/arch_1_11_1157971653.arc
ORA-00280: change 1606185 for thread 1 is in sequence #11


ORA-00279: change 1626796 generated at 01/12/2024 03:49:31 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch2/arch_1_12_1157971653.arc
ORA-00280: change 1626796 for thread 1 is in sequence #12


ORA-00279: change 1629556 generated at 01/12/2024 04:39:15 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch2/arch_1_13_1157971653.arc
ORA-00280: change 1629556 for thread 1 is in sequence #13


ORA-00279: change 1629559 generated at 01/12/2024 04:39:18 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch2/arch_1_14_1157971653.arc
ORA-00280: change 1629559 for thread 1 is in sequence #14


ORA-00279: change 1629562 generated at 01/12/2024 04:39:19 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch2/arch_1_15_1157971653.arc
ORA-00280: change 1629562 for thread 1 is in sequence #15


Log applied.
Media recovery complete.

● 4. db open

SQL> alter database open;

Database altered.

recover file 확인

SQL> select * from v$recover_file;

no rows selected

확인

select a.file#, a.name, a.checkpoint_change#,
        b.status, b.change#, to_char(b.time,'yyyy/mm/dd hh24:mi:ss')
from v$datafile a, v$backup b
where a.file#=b.file#;

▶ 8. 모든 데이터파일 손상 시나리오

● 1. 장애유발 : 모든 데이터 파일 삭제

SQL> ! rm /u01/app/oracle/oradata/ora11g/*.dbf
SQL> ! ls /u01/app/oracle/oradata/ora11g/*.dbf
ls: cannot access /u01/app/oracle/oradata/ora11g/*.dbf: No such file or directory

DB 내려감

SQL> alter system checkpoint;
alter system checkpoint
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 7893
Session ID: 9 Serial number: 3

세션이 종료되었기 때문에 바로 startup 불가

SQL> startup
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist

새로운 세션에 접속

SQL> conn / as sysdba
Connected to an idle instance.

오류 확인

SQL> startup
ORACLE instance started.

Total System Global Area  828608512 bytes
Fixed Size                  1367792 bytes
Variable Size             545259792 bytes
Database Buffers          276824064 bytes
Redo Buffers                5156864 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/u01/app/oracle/oradata/ora11g/system01.dbf'

recover file 확인
★ 어떤 데이터 파일에 문제가 발생한 것인지 v$recover_file 로 꼭 확인해봐야 한다!

SQL> SELECT * FROM v$recover_file;

     FILE# ONLINE  ONLINE_ ERROR              CHANGE# TIME
---------- ------- ------- --------------- ---------- ---------
         1 ONLINE  ONLINE  FILE NOT FOUND           0
         2 ONLINE  ONLINE  FILE NOT FOUND           0
         4 ONLINE  ONLINE  FILE NOT FOUND           0
         5 ONLINE  ONLINE  FILE NOT FOUND           0
         6 ONLINE  ONLINE  FILE NOT FOUND           0
         7 ONLINE  ONLINE  FILE NOT FOUND           0

● 2. 백업 파일 restore 작업

SQL> ! cp -av /home/oracle/backup/arch/hot_20240112/*.dbf /u01/app/oracle/oradata/ora11g
‘/home/oracle/backup/arch/hot_20240112/data01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/data01.dbf’
‘/home/oracle/backup/arch/hot_20240112/data02.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/data02.dbf’
‘/home/oracle/backup/arch/hot_20240112/example01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/example01.dbf’
‘/home/oracle/backup/arch/hot_20240112/sysaux01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/sysaux01.dbf’
‘/home/oracle/backup/arch/hot_20240112/system01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/system01.dbf’
‘/home/oracle/backup/arch/hot_20240112/temp_new01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/temp_new01.dbf’
‘/home/oracle/backup/arch/hot_20240112/undotbs01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/undotbs01.dbf’
‘/home/oracle/backup/arch/hot_20240112/users01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/users01.dbf’

SQL> ! ls /u01/app/oracle/oradata/ora11g
control01.ctl  data02.dbf     redo01.log  redo03.log    system01.dbf    undotbs01.dbf
data01.dbf     example01.dbf  redo02.log  sysaux01.dbf  temp_new01.dbf  users01.dbf

● 3. 복구 하기 -> 모든 파일을 복구 하는 것이기 때문에 DATABASE 레벨로

SQL> RECOVER DATABASE;
ORA-00279: change 1835109 generated at 01/12/2024 16:02:01 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch2/arch_1_7_1157971653.arc
ORA-00280: change 1835109 for thread 1 is in sequence #7

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
...
Log applied.
Media recovery complete.

● 4. DB OPEN 하기

SQL> ALTER DATABASE OPEN;

Database altered.

복구해야할 데이터 파일 있는지

SQL> SELECT * FROM v$recover_file;

no rows selected

data01 테이블 스페이스 삭제

SQL> DROP TABLESPACE data01 INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped

◈ cold backup, hot backup 다시 복습 (오늘 날짜로)

[ cold backup, close backup, offline backup, 일관성 백업]

cold backup후

지난 coldbackup 삭제

아카이브로그 삭제

[ hotbackup, openbackup, online backup, 일관성없는 백업]

▶ 9. 아카이브 파일이 다른 위치에 있을 경우 복구

● 1. 작업 : archive log 기록 남게한후, 아카이브 로그파일 삭제

테이블생성, 로그스위치발생
: archive log 기록 남게한후

alter system switch logfile;

create table hr.new_2024
as
select * from hr.employees;

alter system switch logfile;

create table hr.old_2024
as
select * from hr.employees;

alter system switch logfile;

아카이브 로그파일 옮기고, 지정된 아카이브로그 파일 삭제

[oracle@oracle archive_file]$ mv /home/oracle/arch2/*.* .
[oracle@oracle archive_file]$ ls
arch_1_2_1158049110.arc  arch_1_4_1158049110.arc  arch_1_6_1158049110.arc
arch_1_3_1158049110.arc  arch_1_5_1158049110.arc
[oracle@oracle archive_file]$ ls /home/oracle/arch2
[oracle@oracle archive_file]$ ls /home/oracle/arch1
arch_1_3_1158049110.arc  arch_1_5_1158049110.arc
arch_1_4_1158049110.arc  arch_1_6_1158049110.arc
[oracle@oracle archive_file]$ rm /home/orace/arch1/*.*
rm: cannot remove ‘/home/orace/arch1/*.*’: No such file or directory
[oracle@oracle archive_file]$ rm /home/oracle/arch1/*.*
[oracle@oracle archive_file]$ ls
arch_1_2_1158049110.arc  arch_1_4_1158049110.arc  arch_1_6_1158049110.arc
arch_1_3_1158049110.arc  arch_1_5_1158049110.arc
[oracle@oracle archive_file]$ ls /home/oracle/arch1 /home/oracle/arch2
/home/oracle/arch1:

/home/oracle/arch2:

● 2. 장애 발생

[oracle@oracle archive_file]$ rm /u01/app/oracle/oradata/ora11g/example01.dbf
[oracle@oracle archive_file]$ exit
exit

SQL> startup
ORACLE instance started.

Total System Global Area  531476480 bytes
Fixed Size                  1365796 bytes
Variable Size             201328860 bytes
Database Buffers          322961408 bytes
Redo Buffers                5820416 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/u01/app/oracle/oradata/ora11g/example01.dbf'

offiline

SQL> select * from v$recover_file;

SQL> alter database datafile 5 offline;

Database altered.

SQL> alter database open;

Database altered.

● 3. 백업본을 이용하여 restore

SQL> ! cp -av /home/oracle/backup/arch/hot_20240115/example01.dbf /u01/app/oracle/oradata/ora11g/
‘/home/oracle/backup/arch/hot_20240115/example01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/example01.dbf’

● 4. recover

SQL> recover tablespace example;
ORA-00279: change 1584587 generated at 01/12/2024 08:58:20 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch2/arch_1_1_1158049110.arc
ORA-00280: change 1584587 for thread 1 is in sequence #1


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
  
/home/oracle/archive_file/arch_1_2_1158049110.arc
/home/oracle/archive_file/arch_1_4_1158049110.arc
/home/oracle/archive_file/arch_1_6_1158049110.arc
/home/oracle/archive_file/arch_1_3_1158049110.arc
/home/oracle/archive_file/arch_1_5_1158049110.arc

하나씩 넣어준다.

▶ 10. redolog file백업 안햇을때, 모든 데이터파일, 컨트롤파일 손상시 시나리오 (cold backup 이용)

db 정상종료

shutdown immediate

backup본으로 restore

! cp -av /home/oracle/backup/arch/cold_20240115/*.dbf /u01/app/oracle/oradata/ora11g/
! cp -av /home/oracle/backup/arch/cold_20240115/*.ctl /u01/app/oracle/oradata/ora11g/
! cp -av /home/oracle/backup/arch/cold_20240115/initora11g_0115.ora /u01/app/oracle/product/11.2.0.4/db_1/dbs/initora11g.ora

아카이브 로그 다 지우기

cd ar

pfile로 열고, cancelbase recover, resetlogs

SQL> startup pfile='$ORACLE_HOME/dbs/initora11g.ora' mount
ORACLE instance started.

Total System Global Area  531476480 bytes
Fixed Size                  1365796 bytes
Variable Size             201328860 bytes
Database Buffers          322961408 bytes
Redo Buffers                5820416 bytes
Database mounted.
SQL> recover database until cancel using backup controlfile;
ORA-00279: change 1584058 generated at 01/12/2024 08:50:16 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch2/arch_1_1_1158049110.arc
ORA-00280: change 1584058 for thread 1 is in sequence #1


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;

Database altered.

spfile 생성

create spfile from pfile;

2023/01/16

▶ 11. db down시에 undo 데이터파일 손상시 시나리오 (hot backup 이용)

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#;

undo segments 확인

select segment_id, segment_name, owner, tablespace_name, status
from dba_rollback_segs;

[hr session]

update hr.employees
set salary = 2000
where employee_id = 100;

[sys session]

select s.username, s.sid, s.serial#, r.name,
        t.xidusn, t.ubafil, t.ubablk, t.used_ublk
from v$session s, v$transaction t, v$rollname r
where s.taddr = t.addr
and t.xidusn = r.usn;

● 1. 장애발생 : db down후 undo 데이터파일 삭제

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> ! rm /u01/app/oracle/oradata/ora11g/undotbs01.dbf

SQL> startup
ORACLE instance started.

Total System Global Area  531476480 bytes
Fixed Size                  1365796 bytes
Variable Size             201328860 bytes
Database Buffers          322961408 bytes
Redo Buffers                5820416 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/u01/app/oracle/oradata/ora11g/undotbs01.dbf'

recover file 확인

SQL> select * from v$recover_file;

     FILE# ONLINE  ONLINE_
---------- ------- -------
ERROR                                                                CHANGE#
----------------------------------------------------------------- ----------
TIME
---------
         6 ONLINE  ONLINE
FILE NOT FOUND                                                             0

● 2. 백업본으로 restore

undo, system 테이블스페이스는 offline으로 변경 불가하므로
mount단계에서 복구해야한다.

SQL> ! cp -av /home/oracle/backup/arch/hot_20240115/undotbs01.dbf /u01/app/oracle/oradata/ora11g/undotbs01.dbf
‘/home/oracle/backup/arch/hot_20240115/undotbs01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/undotbs01.dbf’

● 3. archive file, redologfile 로 recove하기

SQL> recover tablespace undotbs;
ORA-00279: change 1588474 generated at 01/12/2024 11:57:07 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch2/arch_1_1_1158057974.arc
ORA-00280: change 1588474 for thread 1 is in sequence #1


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
Log applied.
Media recovery complete.

● 4. db open

SQL> alter database open;

Database altered.

SQL> select salary from hr.employees where employee_id =100;

    SALARY
----------
     24000

▶ 12. 운영중에 undo 데이터파일 손상시 시나리오 (hot backup 이용)

● 1. 작업 : 트랜잭션작업

undo segment 확인

select segment_id, segment_name, owner, tablespace_name, status
from dba_rollback_segs;

트랜잭션 작업

[hr session]

update hr.employees
set salary = 2000
where employee_id = 100;

트랜잭션확인

[sys session]
select s.username, s.sid, s.serial#, r.name,
        t.xidusn, t.ubafil, t.ubablk, t.used_ublk
from v$session s, v$transaction t, v$rollname r
where s.taddr = t.addr
and t.xidusn = r.usn;

● 2. 장애발생 : 운영중 undo 데이터파일 삭제

SQL> ! rm /u01/app/oracle/oradata/ora11g/undotbs01.dbf

alert log 창에 error 뜬게 보인다

[oracle@oracle trace]$ tail -F alert_ora11g.log
---
Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_j000_16331.trc:
ORA-12012: error on auto execute of job 3
ORA-01116: error in opening database file 6
ORA-01110: data file 6: '/u01/app/oracle/oradata/ora11g/undotbs01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

● 3. default undo 테이블스페이스 변경

새로운 undo tablespace 생성

create undo tablespace undo_new datafile
'/u01/app/oracle/oradata/ora11g/undo_new01.dbf' size 10m autoextend on;

undo segments 확인

select segment_id, segment_name, owner, tablespace_name, status
from dba_rollback_segs;

새로운 undo 테이블스페이스 변경

alter system set undo_tablespace = UNDO_NEW;

변경된것 확인

show parameter undo;

기존 undo는 offline으로, 변경된 undo는 online 확인

트랜잭션이 남아있어서 기존 undo segment 하나가 online 이다.

select segment_id, segment_name, owner, tablespace_name, status
from dba_rollback_segs;

트랜잭션 확이

select s.username, s.sid, s.serial#, r.name,
        t.xidusn, t.ubafil, t.ubablk, t.used_ublk
from v$session s, v$transaction t, v$rollname r
where s.taddr = t.addr
and t.xidusn = r.usn;

● 4. 작업

다른 세션에서 트랜잭션 작업

[hr session]

update hr.employees
set salary = 2000
where employee_id = 200;

트랜잭션확인
: 서로 다른 undo segment 에서 트랜잭션이 걸려있다.

[sys session]
select s.username, s.sid, s.serial#, r.name,
        t.xidusn, t.ubafil, t.ubablk, t.used_ublk
from v$session s, v$transaction t, v$rollname r
where s.taddr = t.addr
and t.xidusn = r.usn;

● 5. 기존 undo segments 상태보고 해당 session kill

segments 상태 확인

SQL> select a.name, b.status
from v$rollname a, v$rollstat b
where a.usn = b.usn;  2    3

NAME                           STATUS
------------------------------ ---------------
SYSTEM                         ONLINE
_SYSSMU7_4232614060$           PENDING OFFLINE
_SYSSMU11_750594733$           ONLINE
_SYSSMU12_2450822632$          ONLINE
_SYSSMU13_3547298789$          ONLINE
_SYSSMU14_216201895$           ONLINE
_SYSSMU15_4153513860$          ONLINE
_SYSSMU16_2784954839$          ONLINE
_SYSSMU17_2317241280$          ONLINE
_SYSSMU18_689459114$           ONLINE
_SYSSMU19_4003129141$          ONLINE

NAME                           STATUS
------------------------------ ---------------
_SYSSMU20_1610075169$          ONLINE

트랜젝션 걸려있는 SID,SERIAL# 확인

select s.username, s.sid, s.serial#, r.name,
        t.xidusn, t.ubafil, t.ubablk, t.used_ublk
from v$session s, v$transaction t, v$rollname r
where s.taddr = t.addr
and t.xidusn = r.usn;

기존 hr session 킬

alter system kill session '21,61' immediate

● 5. 기존 undo 테이블스페이스 삭제

SQL> drop tablespace undotbs including contents and datafiles;

Tablespace dropped.

만약에 기존 undo 테이블스페이스를 삭제 할 수 없을 경우
: 그 테이블 스페이스 어떤 undo segment가 PENDING OFFLINE 이면 삭제 할 수 없다. -> 7번으로 간다

● 6. spfile을 이용하여 pfile 생성

create pfile from spfile;

번외

● 7. 기존 undo 테이블스페이스 삭제에러

drop tablespace undotbs including contents and datafiles
오류 보고 -
ORA-30013: 실행 취소 테이블스페이스 'UNDOTBS'은(는) 현재 사용 중임
30013. 00000 -  "undo tablespace '%s' is currently in use"

pfile 생성

create pfile='$ORACLE_HOME/dbs/initora11g_20240116.ora' from spfile;

shut down

shutdown immediate

pfile 수정 (_SYSSMU7_4232614060$ PENDING OFFLINE 으로 되어있는거 offline으로 수정)

vi initora11g_20240116.ora
---
_offline_rollback_segments=(_SYSSMU7_4232614060$)

pfile로 db 열기

startup pfile=$ORACLE_HOME/dbs/initora11g_20240116.ora

번외

pfile로 db 열때 open할때 에러

alter database datafile '/u01/app/oracle/oradata/ora11g/undotbs01.dbf' offline;

alter database open;

drop tablespace undotbs including contents and datafiles;

▶ 12. 특정 데이터 파일과 아카이브 파일이 손상되었을 경우 불완전 복구를 수행

● 백업 확인

일관성 있는 백업정보
일관성 없는 백업정보
리두정보
아카이브정보

select a.file#, a.name, a.checkpoint_change#,
        b.status, b.change#, to_char(b.time,'yyyy/mm/dd hh24:mi:ss')
from v$datafile a, v$backup b
where a.file#=b.file#;

작업

drop table hr.new_2024 purge;
create table hr_new_2024
as select * from hr.employees;

alter system switch logfile;

alter system switch logfile;

alter system switch logfile;

아카이브파일, 리두로그파일 확인

select sequence#, name, first_change#, next_change#
from v$archived_log;

select * from v$log;

OS 아카이브 파일 확인

SQL> ! ls /home/oracle/arch1 /home/oracle/arch2/
/home/oracle/arch1:
arch_1_1_1158057974.arc  arch_1_3_1158057974.arc  arch_1_6_1158057974.arc
arch_1_2_1158057974.arc  arch_1_4_1158057974.arc

/home/oracle/arch2/:
arch_1_1_1158057974.arc  arch_1_3_1158057974.arc  arch_1_6_1158057974.arc
arch_1_2_1158057974.arc  arch_1_4_1158057974.arc

● 1. 장애발생 : 아카이브파일 삭제, example 데이터파일 삭제

SQL> ! rm /home/oracle/arch1/*.*

SQL> ! rm /home/oracle/arch2/*.*

SQL> ! rm /u01/app/oracle/oradata/ora11g/example01.dbf

SQL> alter system checkpoint;
alter system checkpoint
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 27362
Session ID: 162 Serial number: 5


SQL> conn / as sysdba
Connected to an idle instance.
SQL> shutdown immediate
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory
SQL> startup
ORACLE instance started.

Total System Global Area  531476480 bytes
Fixed Size                  1365796 bytes
Variable Size             201328860 bytes
Database Buffers          322961408 bytes
Redo Buffers                5820416 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/u01/app/oracle/oradata/ora11g/example01.dbf'

● 2. 문제되는 데이터파일 offline 상태로 만들고, db open

recover file 조회

SQL> select * from v$recover_file;

     FILE# ONLINE  ONLINE_
---------- ------- -------
ERROR                                                                CHANGE#
----------------------------------------------------------------- ----------
TIME
---------
         5 ONLINE  ONLINE
FILE NOT FOUND                                                             0

datafile offline으로 변경 db open

SQL> alter database datafile 5 offline;

Database altered.

db open

SQL> alter database open;

● 3. hot backup본을 이용하여 restore 후 recover

backup 본으로 datafile restore

SQL> ! cp -av /home/oracle/backup/arch/hot_20240116/example01.dbf /u01/app/oracle/oradata/ora11g/example01.dbf
‘/home/oracle/backup/arch/hot_20240116/example01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/example01.dbf’

● 4. recover -> 장애발생 : 아카이브파일, 리두로그파일에 없으므로

archive 파일, redo logfile 없으므로 recover 이 안된다.

SQL> recover tablespace example
ORA-00279: change 1588474 generated at 01/12/2024 11:57:07 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch2/arch_1_1_1158057974.arc
ORA-00280: change 1588474 for thread 1 is in sequence #1


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log
'/home/oracle/arch2/arch_1_1_1158057974.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3


ORA-00308: cannot open archived log
'/home/oracle/arch2/arch_1_1_1158057974.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

cancel base recover 해도 안된다.

SQL> recover tablespace example
ORA-00279: change 1588474 generated at 01/12/2024 11:57:07 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch2/arch_1_1_1158057974.arc
ORA-00280: change 1588474 for thread 1 is in sequence #1


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter tablespace example online;
alter tablespace example online
*
ERROR at line 1:
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: '/u01/app/oracle/oradata/ora11g/example01.dbf'

SQL> shutdown abort
ORACLE instance shut down.

불완전한 복구는 테이블스페이스 레벨이 아닌 데이터베이스 레벨에서 수행 해야한다. 아카이브 파일이 손상되었을 경우 전체 데이터베이스를 과거 시간으로 되돌아 가야하는 복구 방식을 수행해야 한다.

● 5. 백업 데이터파일만 restore

SQL> ! cp -av /home/oracle/backup/arch/cold_20240115/*.dbf /u01/app/oracle/oradata/ora11g/
‘/home/oracle/backup/arch/cold_20240115/example01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/example01.dbf’
‘/home/oracle/backup/arch/cold_20240115/sysaux01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/sysaux01.dbf’
‘/home/oracle/backup/arch/cold_20240115/system01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/system01.dbf’
‘/home/oracle/backup/arch/cold_20240115/temp01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/temp01.dbf’
‘/home/oracle/backup/arch/cold_20240115/undotbs01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/undotbs01.dbf’
‘/home/oracle/backup/arch/cold_20240115/users01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/users01.dbf’

● 6. mount 단계 까지 db open

SQL> startup mount
ORACLE instance started.

Total System Global Area  531476480 bytes
Fixed Size                  1365796 bytes
Variable Size             201328860 bytes
Database Buffers          322961408 bytes
Redo Buffers                5820416 bytes
Database mounted.

● 7. cancel base recover

SQL> recover database until cancel
ORA-00279: change 1586690 generated at 01/12/2024 11:26:17 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch2/arch_1_1_1158057974.arc
ORA-00280: change 1586690 for thread 1 is in sequence #1


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.

● 8. 수위를 맞추기위해서 resetlogs 로 db open해야한다

SQL> alter database open resetlogs;

Database altered.

redo log file 확인

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
         1          1          1   52428800        512          1 NO
CURRENT                1586691 12-JAN-24   2.8147E+14

         2          1          0   52428800        512          1 YES
UNUSED                       0                      0

         3          1          0   52428800        512          1 YES
UNUSED                       0                      0

resetlogs 로그 기록

select * from v$database_incarnation;
---
1	1	13/08/25	0		PARENT	824361512	0	NO
2	635002	23/12/07	1	13/08/25	PARENT	1154915172	1	NO
3	1513641	24/01/11	635002	23/12/07	PARENT	1157971653	2	NO
4	1580233	24/01/12	1513641	24/01/11	PARENT	1158049053	3	NO
5	1580748	24/01/12	1580233	24/01/12	PARENT	1158049110	4	NO
6	1584059	24/01/12	1580748	24/01/12	PARENT	1158057974	5	NO
7	1586691	24/01/12	1584059	24/01/12	CURRENT	1158085227	6	NO

로그스위치 발생

alter system switch logfile;

resetlogs
: 데이터베이스를 resetlogs 을 이용해서 open 을 한 후에 과거 백업본은 사용할 수 없다. 현재 상태에서 일관성 있는 백업과 일관성 없는 백업을 수행해야한다.
과거의 아카이브 파일은 필요가없다.

??????????????
alter database 백업

SQL> ! cp -av /home/oracle/backup/arch/cold_20240115/*.dbf /u01/app/oracle/oradata/ora11g/
‘/home/oracle/backup/arch/cold_20240115/example01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/example01.dbf’
‘/home/oracle/backup/arch/cold_20240115/sysaux01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/sysaux01.dbf’
‘/home/oracle/backup/arch/cold_20240115/system01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/system01.dbf’
‘/home/oracle/backup/arch/cold_20240115/temp01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/temp01.dbf’
‘/home/oracle/backup/arch/cold_20240115/undotbs01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/undotbs01.dbf’
‘/home/oracle/backup/arch/cold_20240115/users01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/users01.dbf’

SQL> ! cp -av /home/oracle/backup/arch/cold_20240115/*.ctl /u01/app/oracle/oradata/ora11g/
‘/home/oracle/backup/arch/cold_20240115/control01.ctl’ -> ‘/u01/app/oracle/oradata/ora11g/control01.ctl’

SQL> ! cp -av /home/oracle/backup/arch/cold_20240115/*.log /u01/app/oracle/oradata/ora11g/
‘/home/oracle/backup/arch/cold_20240115/redo01.log’ -> ‘/u01/app/oracle/oradata/ora11g/redo01.log’
‘/home/oracle/backup/arch/cold_20240115/redo02.log’ -> ‘/u01/app/oracle/oradata/ora11g/redo02.log’
‘/home/oracle/backup/arch/cold_20240115/redo03.log’ -> ‘/u01/app/oracle/oradata/ora11g/redo03.log’

-0----

SQL> startup mount
ORACLE instance started.

Total System Global Area 531476480 bytes
Fixed Size 1365796 bytes
Variable Size 201328860 bytes
Database Buffers 322961408 bytes
Redo Buffers 5820416 bytes
Database mounted.
SQL> recover database until cancel using backup controlfile;
ORA-00283: recovery session canceled due to errors
ORA-01110: data file 6: '/u01/app/oracle/oradata/ora11g/undotbs01.dbf'
ORA-01122: database file 6 failed verification check
ORA-01110: data file 6: '/u01/app/oracle/oradata/ora11g/undotbs01.dbf'
ORA-01203: wrong incarnation of this file - wrong creation SCN

-00---

YoonSeo Park

DB 공부를 하고 있는 사람입니다. 글을 읽어주셔서 감사하고 더 좋은 글을 쓰기 위해 노력하겠습니다 :)

Backup-Recovery 모든 시나리오 User Managed

Oracle Architecture

● 백업 대상 확인할것

▶ 4. 백업 받지 않은 테이블스페이스에 데이터 파일 손상 시나리오

▶ 5. 테이블스페이스에 데이터 파일 손상시 기존 위치가 아닌 새로운 위치로 복원 작업 시나리오

▶ 6. 테이블스페이스에 속한 여러 데이터파일들 중에 특정한이 손상시나리오 (offline temporary)

▶ 7. 시스템데이터파일 손상 시나리오

▶ 8. 모든 데이터파일 손상 시나리오

◈ cold backup, hot backup 다시 복습 (오늘 날짜로)

▶ 9. 아카이브 파일이 다른 위치에 있을 경우 복구

▶ 10. redolog file백업 안햇을때, 모든 데이터파일, 컨트롤파일 손상시 시나리오 (cold backup 이용)

▶ 11. db down시에 undo 데이터파일 손상시 시나리오 (hot backup 이용)

▶ 12. 운영중에 undo 데이터파일 손상시 시나리오 (hot backup 이용)

▶ 12. 특정 데이터 파일과 아카이브 파일이 손상되었을 경우 불완전 복구를 수행

Backup-Recovery 모든 시나리오 - User Managed

log miner, 클론 db, export & import, data pump

0개의 댓글