■ User Managed ( archive log mode ) Recovery 23개 시나리오

YoonSeo Park ·2024년 2월 3일

ORACLE 백업/복구 시나리오

목록 보기

2/6

장애, 백업,복구 시나리오 - archive log mode

● 복구전에 백업본,scn번호 전부 확인

scn 번호 확인

백업 받는 시점의 scn번호, 현재 데이터 파일의 scn번호
checkpoint_change# : 현재 데이터파일의 scn번호
change# : 백업 시점 데이터 파일 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
------------------------------------------------------------------------------------
/u01/app/oracle/oradata/ora11g/system01.dbf		1550864	NOT ACTIVE	1549712	24/01/11
/u01/app/oracle/oradata/ora11g/sysaux01.dbf		1550864	NOT ACTIVE	1549712	24/01/11
/u01/app/oracle/oradata/ora11g/users01.dbf		1550864	NOT ACTIVE	1549712	24/01/11
/u01/app/oracle/oradata/ora11g/example01.dbf	1550864	NOT ACTIVE	1549712	24/01/11
/u01/app/oracle/oradata/ora11g/undotbs01.dbf	1550864	NOT ACTIVE	1549712	24/01/11

마지막 백업받은 시점의 scn번호를 기준으로 현재 scn 번호까지 리두 정보 확인

select * from v$log

시스템상에 archive log 있는지 확인

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

물리적인 위치에 있는지도 확인

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

/home/oracle/arch2:
arch_1_3_1157971653.arc  arch_1_4_1157971653.arc

▶ 1. 운영중 장애때문에 offline 되는 데이터파일이 손상되었을때 복구 시나리오

● 1. 장애발생 -> user 데이터파일 삭제

users테이블스페이스에 테스트 테이블 생성

create table hr.test1(id number)
tablespace users;

insert into hr.test1(id) values(1);

commit;

select * from v$log;

hr.test1 테이블 위치확인

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

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

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

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

삭제하였지만, 아직 메모리에 남아있어서 조회는된다

SQL> select * from hr.test1;

        ID
----------
         1

users테이블스페이스에 새로운 테이블 생성 -> 에러

SQL> create table hr.test2
tablespace users
as select * from hr.employees;  2    3
as select * from hr.employees
                    *
ERROR at line 3:
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
Linux Error: 2: No such file or directory
Additional information: 3

● 2. 대상 테이블 스페이스 offline immediate 변경

SQL> alter tablespace users offline immediate;

Tablespace altered.
SQL> select name, status from v$datafile;

/u01/app/oracle/oradata/ora11g/system01.dbf		SYSTEM
/u01/app/oracle/oradata/ora11g/sysaux01.dbf		ONLINE
/u01/app/oracle/oradata/ora11g/users01.dbf		RECOVER -- recover로 변경되었다.
/u01/app/oracle/oradata/ora11g/example01.dbf	ONLINE
/u01/app/oracle/oradata/ora11g/undotbs01.dbf	ONLINE

● 3. 가장 최근에 hot 백업 파일을 문제되는 파일 위치에 restore

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

● 4. 백업이후에 변경된 redo 정보를 적용

SQL> recover tablespace users;
Media recovery complete.

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

SQL> alter tablespace users online;

Tablespace altered.

확인

select name, status from v$datafile;

● 6. 확인

SQL> select * from hr.test1;

        ID
----------
         1

SQL> create table hr.test2
tablespace users
as select * from hr.employees;

Table created.

▶ 2. DB가 종료후 데이터파일이 손상되어 DB를 올릴때 시나리오, 데이터파일이 손상되어 DB가 종료되었을때

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

db 종료

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

user 데이터파일 삭제

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

db startup : 장애발생

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 4 - see DBWR trace file
ORA-01110: data file 4: '/u01/app/oracle/oradata/ora11g/users01.dbf'
SQL> select status from v$instance;

STATUS
------------
MOUNTED

● 2. recover 대상 file 조회

select * from v$recover_file;
FILE#	ONLINE  ONLINE_ERROR  CHANGE# 		TIME
------------------------------------------------
4		ONLINE	ONLINE		 FILE NOT FOUND	0

● 3. 대상 테이블 스페이스 offline 변경

SQL> alter database datafile '/u01/app/oracle/oradata/ora11g/users01.dbf' offline;

Database altered.

recover 대상 file 조회

select * from v$recover_file;
FILE#	ONLINE  ONLINE_ERROR  CHANGE# 		TIME
------------------------------------------------
4		OFFLINE	ONLINE		 FILE NOT FOUND	0

● 4. db open

SQL> alter database open;

Database altered.

● 5. 가장 최근에 hot 백업 파일을 문제되는 파일 위치에 restore

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

● 6. 백업이후에 변경된 redo 정보를 적용

SQL> recover tablespace users;
Media recovery complete.

● 7. 대상 테이블 스페이스 online 으로 변경

SQL> alter tablespace users online;

Tablespace altered.

recover file 확인

SQL> select * from v$recover_file;

no rows selected

● 8. scn 맞추기

checkpoint 확인

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

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

db shutdown abort

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.
Database opened.

checkpoint 확인

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

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

▶ 3. DB가 종료후 데이터파일 여러개가 손상되어 DB를 올릴때 시나리오, 데이터파일이 손상되어 DB가 종료되었을때

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

db 종료

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

삭제

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

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

db startup : 장애

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 4 - see DBWR trace file
ORA-01110: data file 4: '/u01/app/oracle/oradata/ora11g/users01.dbf'

● 2. recover file 확인

SQL> select * from v$recover_file;

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

● 3. 대상 테이블 스페이스 offline 변경

SQL> alter database datafile 4 offline;

Database altered.

SQL> alter database datafile 5 offline;

Database altered.

SQL> select * from v$recover_file;

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

● 4. db open

SQL> alter database open;

Database altered.

● 5. 가장 최근에 hot 백업 파일을 문제되는 파일 위치에 restore

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

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

● 6. 백업이후에 변경된 redo 정보를 적용 user테이블스페이스

db level로
recover tablespace 테이블스페이스명;
이렇게 해야한다.
recover database; -> 하면 안된다.

1번경우

SQL> recover tablespace users;
Media recovery complete.

2번경우 : logswitch가 발생한것이다.

SQL> recover tablespace users;
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO -- 입력

● 7. 대상 user테이블 스페이스 online 으로 변경

SQL> alter tablespace users online;
Tablespace altered.

SQL> select * from v$recover_file;

     FILE# ONLINE  ONLINE_ ERROR                                                                CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- ---------
         5 OFFLINE OFFLINE                                                                      1547957 11-JAN-24

● 8. 백업이후에 변경된 redo 정보를 적용 user테이블스페이스, 대상 user테이블 스페이스 online 으로 변경

redo 정보를 적용

SQL> recover tablespace example;
Media recovery complete.

대상 user테이블 스페이스 online 으로 변경

SQL> alter tablespace example online;

Tablespace altered.

확인

SQL> select * from v$recover_file;

no rows selected

▶ 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.

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

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

● 5. 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.

● 6. 테이블스페이스 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

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

▶ 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 데이터파일 손상시 시나리오 (새로운 undo 만들기)

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

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

● 백업 확인

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

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'

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

● 5. db 내려주고 , restore

SQL> shutdown abort
ORACLE instance shut down.

백업 데이터파일로 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 start

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. 문제되는 데이터파일 ONLINE 시키기

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

SQL> alter database datafile 4 online;

Database altered.

● 8. 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.

● 9. 수위를 맞추기위해서 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;

● 10. cold, hot backup 하기

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

usermanaged 는 hotbackup 으로는 불완전 복구 불가능하지만
rman 의 hot backup 가지고도 불완전 복구가 가능하다.

archive dest 보기

select destination, binding, status from v$archive_dest;

archive dest1, 2 경로 보기

SQL> show parameter log_archive_dest_1

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1                   string      location=/home/oracle/arch1 ma
                                                 ndatory
log_archive_dest_10                  string
log_archive_dest_11                  string
log_archive_dest_12                  string
log_archive_dest_13                  string
log_archive_dest_14                  string
log_archive_dest_15                  string
log_archive_dest_16                  string
log_archive_dest_17                  string
log_archive_dest_18                  string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_19                  string
SQL> show parameter log_archive_dest_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string      location=/home/oracle/arch2 op
                                                 tional
log_archive_dest_20                  string
log_archive_dest_21                  string
log_archive_dest_22                  string
log_archive_dest_23                  string
log_archive_dest_24                  string
log_archive_dest_25                  string
log_archive_dest_26                  string
log_archive_dest_27                  string
log_archive_dest_28                  string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_29                  string

archive dest 2 없는 상태로 변경

SQL> alter system reset log_archive_dest_2 scope=spfile;
또는
SQL> alter system set log_archive_dest_2 = '' scope=spfile;

System altered.

db 껏다 켜주면 바껴있다.

shutdown immdeiate
startup

필요 없는 os 상의 archive 파일 지우기

rm /home/oracle/arch2/*.*

▶ 14. archive 생성된후 db close 상태에서 inactive log file이 손상된 시나리오

● 1. 확인

select a.group#,b.thread#,b.sequence#, member, b.bytes/1024/1024 mb, b.archived, b.status,
        b.first_change#, b.next_change#, b.next_time
from v$logfile a, v$log b
where a.group# = b.group#
order by 1,2;
---
1	1	1	/u01/app/oracle/oradata/ora11g/redo01.log	50	YES	INACTIVE	1584059	1590360	24/01/13
2	1	2	/u01/app/oracle/oradata/ora11g/redo02.log	50	YES	INACTIVE	1590360	1590514	24/01/13
3	1	3	/u01/app/oracle/oradata/ora11g/redo03.log	50	NO	CURRENT	1590514	281474976710655

● 2. 장애발생 : redolog file 삭제, alert log에서 확인

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

SQL> ! rm /u01/app/oracle/oradata/ora11g/redo01.log

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-03113: end-of-file on communication channel
Process ID: 645
Session ID: 162 Serial number: 5

alert log 창에 1번 그룹 에러가 뜬다

Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_645.trc:
ORA-00313: open failed for members of log group 1 of thread
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/ora11g/redo01.log'

● 3. 문제가 되었던 redo log 그룹삭제후 open

mount 단계까지 start

SQL> conn / as sysdba
Connected to an idle instance.
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.

문제가 되었던 1번 그룹 삭제

SQL> alter database drop logfile group 1;

Database altered.

그룹 확인

select a.group#,b.thread#,b.sequence#, member, b.bytes/1024/1024 mb, b.archived, b.status,
        b.first_change#, b.next_change#, b.next_time
from v$logfile a, v$log b
where a.group# = b.group#
order by 1,2;

SQL> alter database open;

Database altered.

● 4. redo log 그룹 추가

SQL> alter database add logfile group 1 '/u01/app/oracle/oradata/ora11g/redo01.log' size 50m;

Database altered.

확인

select a.group#,b.thread#,b.sequence#, member, b.bytes/1024/1024 mb, b.archived, b.status,
        b.first_change#, b.next_change#, b.next_time
from v$logfile a, v$log b
where a.group# = b.group#
order by 1,2;

▶ 15. DB open 상태에서 current 그룹이 아닌 redo log 그룹이 삭제

● 1. 확인

로그 스위치 발생

alter system switch logfile;

아카이브 파일에 current log 기록

alter system archive log current;

logfile 그룹 보기

select a.group#,b.thread#,b.sequence#, member, b.bytes/1024/1024 mb, b.archived, b.status,
        b.first_change#, b.next_change#, b.next_time
from v$logfile a, v$log b
where a.group# = b.group#
order by 1,2;

● 2. 장애 발생 : redologfile 삭제

SQL> ! rm /u01/app/oracle/oradata/ora11g/redo02.log

로그 스위치 발생 -> 행걸림

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

alter log file 보기

Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_m000_rc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/ora11g/redo02.l
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

행(중단)이 발생하면서 archive 파일에 간격이 생겼다.

[oracle@oracle arch1]$ ls
arch_1_1_1158057974.arc  arch_1_4_1158057974.arc  arch_1_7_1158057974.arc
arch_1_2_1158057974.arc  arch_1_5_1158057974.arc
arch_1_3_1158057974.arc  arch_1_6_1158057974.arc

select a.group#,b.thread#,b.sequence#, member, b.bytes/1024/1024 mb, b.archived, b.status,
        b.first_change#, b.next_change#, b.next_time
from v$logfile a, v$log b
where a.group# = b.group#
order by 1,2;
---
1	1	10	/u01/app/oracle/oradata/ora11g/redo01.log	50	NO	CURRENT	1592257	281474976710655	
2	1	8	/u01/app/oracle/oradata/ora11g/redo02.log	50	NO	INACTIVE	1592246	1592254	2024-01-13 01:23:59
3	1	9	/u01/app/oracle/oradata/ora11g/redo03.log	50	NO	INACTIVE	1592254	1592257	2024-01-13 01:24:02

● 3. redo log file을 삭제후 재생성 해준다.

SQL> ! ls /u01/app/oracle/oradata/ora11g/redo02.log
ls: cannot access /u01/app/oracle/oradata/ora11g/redo02.log: No such file or directory

redo log file 그룹 재생성

SQL> alter database clear unarchived logfile group 2;

Database altered.

SQL> ! ls /u01/app/oracle/oradata/ora11g/redo02.log
/u01/app/oracle/oradata/ora11g/redo02.log

▶ 16. current redo log 그룹이 삭제된후 DB 정상적인 종료 되었을때 시나리오

● 1. 작업

create table hr.new_loc
as select * from hr.locations;

select count(*) from hr.new_loc;

● 2. 장애 발생 : current 한 redo log 그룹 삭제

redolog group 확인

select a.group#,b.thread#,b.sequence#, member, b.bytes/1024/1024 mb, b.archived, b.status,
        b.first_change#, b.next_change#, b.next_time
from v$logfile a, v$log b
where a.group# = b.group#
order by 1,2;
---
1	1	1	/u01/app/oracle/oradata/ora11g/redo01.log	50	YES	INACTIVE	1584059	1590335	24/01/13
2	1	2	/u01/app/oracle/oradata/ora11g/redo02.log	50	YES	INACTIVE	1590335	1590594	24/01/13
3	1	3	/u01/app/oracle/oradata/ora11g/redo03.log	50	NO	CURRENT	1590594	281474976710655

current 한 redo log 그룹 삭제

SQL> ! rm /u01/app/oracle/oradata/ora11g/redo03.log

● 3. db 정상적인 종료후 db startup

db 정상적인 종료

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

db startup 하면 에러가 나면서 db 내려감

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-03113: end-of-file on communication channel
Process ID: 5892
Session ID: 162 Serial number: 5

cacel base recover 을 하면 control 파일하고 수위가 안맞기때문에 무조건 resetlogs로 db open 해야한다.

● 4. cancel base recover

mount 단계 까지 start

SQL> conn / as sysdba
Connected to an idle instance.
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.

cancel base recover

current 한 redolog 전 까지 recover

SQL> recover database until cancel
Media recovery complete.

open resetlogs

cacel base recover 을 하면 control 파일하고 수위가 안맞기때문에 무조건 resetlogs로 db open 해야한다.

SQL> alter database open resetlogs;

Database altered.

정상적인 종료를 하였으므로 깨진 시점 전까지 redo가 생성된다.

SQL> ! ls /u01/app/oracle/oradata/ora11g/redo*.*
/u01/app/oracle/oradata/ora11g/redo01.log
/u01/app/oracle/oradata/ora11g/redo02.log
/u01/app/oracle/oradata/ora11g/redo03.log

current 한 redolog 그룹이 손상되었어도 DB가 정상적으로 종료되면 체크포인트가 발생하면서 dirty buffer들을 모두 디스크로 내리기 때문에 복구할 것이 없게되는 것이다.
=> 삭제된 리두만 새로 만들어준다고 보면 된다.

▶ 17. current redo log 그룹이 삭제된후 DB 비정상적인 종료 되었을때 시나리오

● 1. 작업

테이블 생성

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

리두로그 그룹 확인

select a.group#,b.thread#,b.sequence#, member, b.bytes/1024/1024 mb, b.archived, b.status,
        b.first_change#, b.next_change#, b.next_time
from v$logfile a, v$log b
where a.group# = b.group#
order by 1,2;
---
1	1	4	/u01/app/oracle/oradata/ora11g/redo01.log	50	YES	INACTIVE
2	1	5	/u01/app/oracle/oradata/ora11g/redo02.log	50	NO	CURRENT
3	1	3	/u01/app/oracle/oradata/ora11g/redo03.log	50	YES	INACTIVE

아카이브 로그파일에 current redolog 기록

alter system archive log current;

current 한 redolog그룹 메모리에서 테이블생성

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

os 상에서 확인

[oracle@oracle arch1]$ ls
arch_1_1_1158057974.arc  arch_1_3_1158057974.arc
arch_1_2_1158057974.arc  arch_1_4_1158057974.arc

● 2. 장애발생 : current 한 redolog file 삭제

SQL> ! rm /u01/app/oracle/oradata/ora11g/redo02.log

● 3. db 비정상적인 종료

SQL> shutdown abort
ORACLE instance shut down.

● 4. startup : 에러발생

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-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/ora11g/redo02.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

mount 단계까지 start 되어있다

SQL> select status from v$instance;

STATUS
------------
MOUNTED

● 5. 데이터파일만 백업본을 이용해 restore

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

● 5. recover 적용까지

#1,#2,#3,#4 번까지 적용인된다 #5번은 안된다.

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


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
-- 엔터
ORA-00279: change 1590996 generated at 01/13/2024 02:49:19 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_2_1158057974.arc
ORA-00280: change 1590996 for thread 1 is in sequence #2
ORA-00278: log file '/home/oracle/arch1/arch_1_1_1158057974.arc' no longer
needed for this recovery


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
-- 엔터
ORA-00279: change 1591000 generated at 01/13/2024 02:49:24 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_3_1158057974.arc
ORA-00280: change 1591000 for thread 1 is in sequence #3
ORA-00278: log file '/home/oracle/arch1/arch_1_2_1158057974.arc' no longer
needed for this recovery


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
-- 엔터
ORA-00279: change 1591004 generated at 01/13/2024 02:49:31 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_4_1158057974.arc
ORA-00280: change 1591004 for thread 1 is in sequence #4
ORA-00278: log file '/home/oracle/arch1/arch_1_3_1158057974.arc' no longer
needed for this recovery


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
-- 엔터
ORA-00279: change 1591019 generated at 01/13/2024 02:49:43 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_5_1158057974.arc
ORA-00280: change 1591019 for thread 1 is in sequence #5
ORA-00278: log file '/home/oracle/arch1/arch_1_4_1158057974.arc' no longer
needed for this recovery

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

● 6. cancel base recover

SQL> recover database until cancel
ORA-00279: change 1591019 generated at 01/13/2024 02:49:43 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_5_1158057974.arc
ORA-00280: change 1591019 for thread 1 is in sequence #5


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

● 7. resetlogs 로 db open

SQL> alter database open resetlogs;

Database altered.

● 8. 만들었던 테이블 확인

아카이브파일에 있던것은 돌아왔지만

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

  COUNT(*)
----------
       107

current redolog 그룹에 있었던 테이블은 조회가안된다.

SQL> select count(*) from hr.copy_dept;
select count(*) from hr.copy_dept
                        *
ERROR at line 1:
ORA-00942: table or view does not exist

▶ 18. control file의 백업본이 있을때 datafile, redolog file 손상되지 않고 control file 만 손상되었을때 시나리오.

● 1. 작업 및 확인

확인

select * from v$database;

select * from v$log;
---
1	1	4	52428800	512	1	NO	CURRENT	1589427
2	1	2	52428800	512	1	YES	INACTIVE	1589415
3	1	3	52428800	512	1	YES	INACTIVE	1589423
---


select * from v$logfile;

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


select name from v$controlfile;

아카이브 dest 파일과, control file의 정보는 초기파라미터 파일이 가지고 있다.

테이블생성

create table hr.emp_20
as 
select * from hr.employees where department_id=20;

확인

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

● 2. 장애발생 : controlfile 손상

shutdown immediate

SQL> ! rm /u01/app/oracle/oradata/ora11g/control01.ctl

db startup -> 에러

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
ORA-00205: error in identifying control file, check alert log for more info


SQL> select status from v$instance;

STATUS
------------
STARTED

alert log 창에 에러 나온다

Sat Jan 13 05:40:00 2024
ALTER DATABASE   MOUNT
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/ora11g/control01.ctl'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

db 강제종료

SQL> shutdown abort
ORACLE instance shut down.

● 3. cold 백업본을 이용하여 restore -> 에러

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

db startup

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-01190: control file or data file 1 is from before the last RESETLOGS
ORA-01110: data file 1: '/u01/app/oracle/oradata/ora11g/system01.dbf'

● 4. 아카이브 파일로 recover

: 리커버할때 아카이브 파일만 보기때문에 아카이브 파일로만 적용된다.

SQL>  recover database using backup controlfile
ORA-00279: change 1588754 generated at 01/12/2024 10:46:14 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_1_1158057974.arc
ORA-00280: change 1588754 for thread 1 is in sequence #1


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00279: change 1589415 generated at 01/13/2024 06:00:02 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_2_1158057974.arc
ORA-00280: change 1589415 for thread 1 is in sequence #2
ORA-00278: log file '/home/oracle/arch1/arch_1_1_1158057974.arc' no longer
needed for this recovery


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00279: change 1589423 generated at 01/13/2024 06:00:14 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_3_1158057974.arc
ORA-00280: change 1589423 for thread 1 is in sequence #3
ORA-00278: log file '/home/oracle/arch1/arch_1_2_1158057974.arc' no longer
needed for this recovery


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00279: change 1589427 generated at 01/13/2024 06:00:18 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_4_1158057974.arc
ORA-00280: change 1589427 for thread 1 is in sequence #4
ORA-00278: log file '/home/oracle/arch1/arch_1_3_1158057974.arc' no longer
needed for this recovery


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

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

● 5. 리두로그 그룹으로 recover

SQL>  recover database using backup controlfile
ORA-00279: change 1589427 generated at 01/13/2024 06:00:18 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_4_1158057974.arc
ORA-00280: change 1589427 for thread 1 is in sequence #4


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/ora11g/redo01.log
-- 현재 current한 리두로그 그룹 넣기
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;


Database altered.

아까 생성한 테이블확인

SQL> SQL>
SQL> select count(*) from hr.emp_20;'

  COUNT(*)
----------
         2

▶ 19. datafile, redolog file 손상되지 않고 control file 만 손상되었을때 control file의 백업본을 이용해서 재생성하여 복구 시나리오

● 1. 작업

테이블 생성

create table hr.emp_30
as
select * from hr.employees where department_id =30;

● 2. 장애발생 : control file 삭제

삭제

SQL>  ! rm /u01/app/oracle/oradata/ora11g/control01.ctl

● 3. control파일이 가지고있는것을 작업하면 에러가나온다.

로그 스위치발생

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
         1          1          4   52428800        512          1 YES
INACTIVE               1591878 13-JAN-24      1591881 13-JAN-24

         2          1          5   52428800        512          1 NO
CURRENT                1591881 13-JAN-24   2.8147E+14

         3          1          3   52428800        512          1 YES
INACTIVE               1591874 13-JAN-24      1591878 13-JAN-24

os에 archive 파일 확인

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

오류

SQL> select * from v$datafile;
select * from v$datafile
              *
ERROR at line 1:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/ora11g/control01.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

alert log 창에 오류가 뜬다

Sat Jan 13 07:09:50 2024
Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_m000_25776.trc:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/ora11g/control01.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

db 정상종료 -> 에러가뜬다.

SQL> shutdown immediate
Database closed.
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/ora11g/control01.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

db 강제종료

SQL> shutdown abort
ORACLE instance shut down.

● 4. 백업본을 이용하여 restore -> 에러

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

mount 단계까지 start

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.

● 5. 복구 작업 실패

SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/u01/app/oracle/oradata/ora11g/system01.dbf'
ORA-01207: file is more recent than control file - old control file

● 6. 컨트롤 파일을 재생성한 후 db 시작

컨트롤 파일의 백업 받아놓기 trace '경로'

SQL> alter database backup controlfile to trace as '/home/oracle/new_control.sql';

Database altered.

db 강제종료

SQL> shutdown abort
ORACLE instance shut down.

생성되었는지 확인

[oracle@oracle ~]$ ls /home/oracle/new_control.sql

생성된 controlfile 확인

[oracle@oracle ~]$ vi new_control.sql
---
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORA11G" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16   	# 리두로그파일 max 갯수
    MAXLOGMEMBERS 3		# logmember max 갯수
    MAXDATAFILES 100 	# datafile의 max 갯수
    MAXINSTANCES 8		# instance max 갯수
    MAXLOGHISTORY 292	# log history max 갯수
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/ora11g/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u01/app/oracle/oradata/ora11g/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oracle/oradata/ora11g/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/ora11g/system01.dbf',
  '/u01/app/oracle/oradata/ora11g/sysaux01.dbf',
  '/u01/app/oracle/oradata/ora11g/users01.dbf',
  '/u01/app/oracle/oradata/ora11g/example01.dbf',
  '/u01/app/oracle/oradata/ora11g/undotbs01.dbf'
CHARACTER SET AL32UTF8
;

control file 재생성 할때

MAX 값을 변경할때

컨트롤파일이 깨져서 재생성할때

DB 이름을 바꿀때(거의없다.)

● 7. 생성된것을 보고 새로 control 파일 vi로 생성

[oracle@oracle ~]$ vi create_control.sql
---
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORA11G" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3	
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/ora11g/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u01/app/oracle/oradata/ora11g/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oracle/oradata/ora11g/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/ora11g/system01.dbf',
  '/u01/app/oracle/oradata/ora11g/sysaux01.dbf',
  '/u01/app/oracle/oradata/ora11g/users01.dbf',
  '/u01/app/oracle/oradata/ora11g/example01.dbf',
  '/u01/app/oracle/oradata/ora11g/undotbs01.dbf'
CHARACTER SET AL32UTF8
;

● 8. control 파일 재생성한것을 실행

위치확인

SQL> ! ls
afiedt.buf          data01.dbf   Music                            Public
arch1               database     new_control.sql                  spool
arch2               Desktop      ora_data                         Templates
archive_file        Documents    p13390677_112040_LINUX_1of7.zip  userdata
backup              Downloads    p13390677_112040_LINUX_2of7.zip  Videos
create_control.sql  emp_sal.csv  Pictures

control 파일 만드는 파일 실행

SQL> @create_control.sql
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

Control file created.

● 9. db open (필요시 recover)

recover database : 할 필요가 없다.
: recover 할 필요가 없다.

SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required

db open;

SQL> recover database open;

만약 db open이 안되고 recover 필요하다 하면 recover 해주면된다

SQL> recover database;

● 10. tempfile 생성

tempfile 생성
: 아까 재생성한 controlfile 안에는
tempfile이 없다 따라서 tempfile 생성해준다
(os 상에는 있으므로 재사용)

ALTER TABLESPACE TEMP add tempfile '/u01/app/oracle/oradata/ora11g/temp01.dbf' REUSE;

▶ 20. datafile, controlfile 손상

요약

datafile 손상된것만, controlfile을 restore

archive file 로 recover database using backup controlfile

current redolog 파일 직접 넣어줘서 recover

resetlogs open

● 1. 확인

alter system switch logfile;
alter system checkpoint;
select * from v$log;
1	1	1	52428800	512	1	YES	INACTIVE	1584059	24/01/12	1589759	24/01/13
2	1	2	52428800	512	1	YES	INACTIVE	1589759	24/01/13	1589764	24/01/13
3	1	3	52428800	512	1	NO	CURRENT	1589764	24/01/13	281474976710655

alert log 창에 current 그룹도 보인다.

Beginning log switch checkpoint up to RBA [0x3.2.10], SCN: 1589764
Thread 1 advanced to log sequence 3 (LGWR switch)
  Current log# 3 seq# 3 mem# 0: /u01/app/oracle/oradata/ora11g/redo03.lo
Sat Jan 13 10:38:09 2024
Archived Log entry 10 added for thread 1 sequence 2 ID 0xf745693 dest 1:
Sat Jan 13 10:38:25 2024

current한 그룹에 테이블 생성

create table hr.emp_30
as 
select * from hr.employees where department_id=20;

● 2. 장애 발생 : datafile, controlfile 손상

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

SQL> ! rm /u01/app/oracle/oradata/ora11g/*.ctl

alert log 창에 에러가 뜬다

Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_j001_8171.trc  (incident=301622):
ORA-00603: ORACLE server session terminated by fatal error
ORA-01116: error in opening database file 2
ORA-01110: data file 2: '/u01/app/oracle/oradata/ora11g/sysaux01.dbf'
ORA-27041: unable to open file

datagfile, controlfile 없는것 확인

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

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

● 3. 백업본으로 datafile, controlfile을 restore

만약 datafile 전체가 아니라 그중에 몇개만 깨졋으면 그것만 restore 해주면 된다.

db 강제 종료
: control 파일이 깨졋으므로 무조건 db 내리고 해야한다.

SQL> shutdown abort
ORACLE instance shut down.

datafile, controlfile 만 백업본으로 restore

SQL> !    
[oracle@oracle ~]$ cp -av /home/oracle/backup/arch/cold_20240116/*.dbf /u01/app/oracle/oradata/ora11g/
‘/home/oracle/backup/arch/cold_20240116/example01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/example01.dbf’
‘/home/oracle/backup/arch/cold_20240116/sysaux01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/sysaux01.dbf’
‘/home/oracle/backup/arch/cold_20240116/system01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/system01.dbf’
‘/home/oracle/backup/arch/cold_20240116/temp01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/temp01.dbf’
‘/home/oracle/backup/arch/cold_20240116/undotbs01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/undotbs01.dbf’
‘/home/oracle/backup/arch/cold_20240116/users01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/users01.dbf’
[oracle@oracle ~]$ cp -av /home/oracle/backup/arch/cold_20240116/*.ctl /u01/app/oracle/oradata/ora11g/
‘/home/oracle/backup/arch/cold_20240116/control01.ctl’ -> ‘/u01/app/oracle/oradata/ora11g/control01.ctl’

● 4. 아카이브 파일에있는것으로 recover

mount 단계 까지 start

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.

아카이브 파일로 recover

SQL> recover database using backup controlfile
ORA-00279: change 1588754 generated at 01/12/2024 17:26:30 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_1_1158057974.arc
ORA-00280: change 1588754 for thread 1 is in sequence #1


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
-- 엔터
ORA-00279: change 1589759 generated at 01/13/2024 10:38:02 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_2_1158057974.arc
ORA-00280: change 1589759 for thread 1 is in sequence #2
ORA-00278: log file '/home/oracle/arch1/arch_1_1_1158057974.arc' no longer
needed for this recovery


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
-- 엔터
ORA-00279: change 1589764 generated at 01/13/2024 10:38:09 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_3_1158057974.arc
ORA-00280: change 1589764 for thread 1 is in sequence #3
ORA-00278: log file '/home/oracle/arch1/arch_1_2_1158057974.arc' no longer
needed for this recovery


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
-- 엔터
ORA-00279: change 1590432 generated at 01/13/2024 10:44:35 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_4_1158057974.arc
ORA-00280: change 1590432 for thread 1 is in sequence #4
ORA-00278: log file '/home/oracle/arch1/arch_1_3_1158057974.arc' no longer
needed for this recovery


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
-- 엔터
ORA-00279: change 1590468 generated at 01/13/2024 10:45:50 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_5_1158057974.arc
ORA-00280: change 1590468 for thread 1 is in sequence #5
ORA-00278: log file '/home/oracle/arch1/arch_1_4_1158057974.arc' no longer
needed for this recovery


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
-- 엔터
ORA-00279: change 1590474 generated at 01/13/2024 10:46:01 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_6_1158057974.arc
ORA-00280: change 1590474 for thread 1 is in sequence #6
ORA-00278: log file '/home/oracle/arch1/arch_1_5_1158057974.arc' no longer
needed for this recovery


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
-- 엔터
ORA-00279: change 1590482 generated at 01/13/2024 10:46:13 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_7_1158057974.arc
ORA-00280: change 1590482 for thread 1 is in sequence #7
ORA-00278: log file '/home/oracle/arch1/arch_1_6_1158057974.arc' no longer
needed for this recovery


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

● 5. current 한 redolog 파일로 recover
: recover 할때는 아카이브파일만 보기떄문에 직접 적어줘야함

SQL>  recover database using backup controlfile
ORA-00279: change 1590482 generated at 01/13/2024 10:46:13 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_7_1158057974.arc
ORA-00280: change 1590482 for thread 1 is in sequence #7


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/ora11g/redo01.log -- current redolog file 찾아서 적용
Log applied.
Media recovery complete.

● 6. resetlogs 로 db open

SQL> alter database open resetlogs;

Database altered.

▶ 21. redolog file, controlfile 손상

요약

cold backup 본을 controlfile, datafile 전부 restore

archive file 로 recover database using backup controlfile

cancel base recover 실행

resetlogs open

● 1. 장애 발생 : redologfile, controlfile 손상

SQL> ! rm /u01/app/oracle/oradata/ora11g/*.log

SQL> ! rm /u01/app/oracle/oradata/ora11g/*.ctl

alert log 창에 에러가 뜬다

Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_j001_8171.trc  (incident=301622):
ORA-00603: ORACLE server session terminated by fatal error
ORA-01116: error in opening database file 2
ORA-01110: data file 2: '/u01/app/oracle/oradata/ora11g/redolog01.dbf'
ORA-27041: unable to open file

redo log file, controlfile 없는것 확인

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

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

● 3. 백업본으로 datafile전부, controlfile restore

redo log file이 깨졋으므로 datafile도 restore 해줘야한다.

db 강제 종료
: control 파일이 깨졋으므로 무조건 db 내리고 해야한다.

SQL> shutdown abort
ORACLE instance shut down.

datafile, controlfile 만 백업본으로 restore

SQL> !    
[oracle@oracle ~]$ cp -av /home/oracle/backup/arch/cold_20240116/*.dbf /u01/app/oracle/oradata/ora11g/
‘/home/oracle/backup/arch/cold_20240116/example01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/example01.dbf’
‘/home/oracle/backup/arch/cold_20240116/sysaux01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/sysaux01.dbf’
‘/home/oracle/backup/arch/cold_20240116/system01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/system01.dbf’
‘/home/oracle/backup/arch/cold_20240116/temp01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/temp01.dbf’
‘/home/oracle/backup/arch/cold_20240116/undotbs01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/undotbs01.dbf’
‘/home/oracle/backup/arch/cold_20240116/users01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/users01.dbf’
[oracle@oracle ~]$ cp -av /home/oracle/backup/arch/cold_20240116/*.ctl /u01/app/oracle/oradata/ora11g/
‘/home/oracle/backup/arch/cold_20240116/control01.ctl’ -> ‘/u01/app/oracle/oradata/ora11g/control01.ctl’

● 4. 아카이브 파일에있는것으로 cancel base recover
: redologfile 이 깨졋으므로 cancel base recover 해야한다.

mount 단계 까지 start

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.

아카이브 파일로 recover

SQL> recover database until cancel using backup controlfile
ORA-00279: change 1588754 generated at 01/12/2024 17:26:30 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_1_1158057974.arc
ORA-00280: change 1588754 for thread 1 is in sequence #1


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto -- auto 입력
ORA-00279: change 1589151 generated at 01/13/2024 11:59:42 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_2_1158057974.arc
ORA-00280: change 1589151 for thread 1 is in sequence #2
ORA-00278: log file '/home/oracle/arch1/arch_1_1_1158057974.arc' no longer
needed for this recovery


ORA-00279: change 1589154 generated at 01/13/2024 11:59:45 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_3_1158057974.arc
ORA-00280: change 1589154 for thread 1 is in sequence #3
ORA-00278: log file '/home/oracle/arch1/arch_1_2_1158057974.arc' no longer
needed for this recovery


ORA-00279: change 1589157 generated at 01/13/2024 11:59:48 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_4_1158057974.arc
ORA-00280: change 1589157 for thread 1 is in sequence #4
ORA-00278: log file '/home/oracle/arch1/arch_1_3_1158057974.arc' no longer
needed for this recovery


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

● 5. current 한 redolog 파일이 없으므로 cancel base recover

SQL> recover database until cancel using backup controlfile
ORA-00279: change 1589157 generated at 01/13/2024 11:59:48 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_4_1158057974.arc
ORA-00280: change 1589157 for thread 1 is in sequence #4


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

● 6. resetlogs 로 db open

SQL> alter database open resetlogs;

Database altered.

cancel base recver
: 과거시점으로 돌아가야해서 datafile을 전부 backup본으로 restore 해야한다.

current한 리두로그가 깨지면 완전복구 작업은 못한다.

아카이브 파일에 gap 이 생길때

▶ 21. 모든 datafile, redolog file, controlfile 손상

● 1. 장애발생 : 모든 datafile, redolog file, controlfile 손상

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

SQL> ! rm /u01/app/oracle/oradata/ora11g/*.ctl

SQL> ! rm /u01/app/oracle/oradata/ora11g/*.log

alert log 창에 error가 뜬다 -> 오류

Sat Jan 13 16:24:30 2024
Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_m000_31011.trc:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/ora11g/control01.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

아카이브 파일 있는지 확인

SQL> ! ls /home/oracle/arch1
arch_1_1_1158057974.arc  arch_1_2_1158057974.arc

● 2. restore

db 강제종료

SQL> shutdown abort
ORACLE instance shut down.

백업본 datafile, controlfile 로 restores

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

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

● 3. cancel base recover

mount 단계까지 startup

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.

아카이브파일로 recover


SQL> recover database until cancel using backup controlfile
ORA-00279: change 1588754 generated at 01/12/2024 17:26:30 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_1_1158057974.arc
ORA-00280: change 1588754 for thread 1 is in sequence #1


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 1597447 generated at 01/13/2024 16:22:55 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_2_1158057974.arc
ORA-00280: change 1597447 for thread 1 is in sequence #2
ORA-00278: log file '/home/oracle/arch1/arch_1_1_1158057974.arc' no longer
needed for this recovery


ORA-00279: change 1597450 generated at 01/13/2024 16:22:55 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_3_1158057974.arc
ORA-00280: change 1597450 for thread 1 is in sequence #3
ORA-00278: log file '/home/oracle/arch1/arch_1_2_1158057974.arc' no longer
needed for this recovery


ORA-00308: cannot open archived log
'/home/oracle/arch1/arch_1_3_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 database until cancel using backup controlfile
ORA-00279: change 1597450 generated at 01/13/2024 16:22:55 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_3_1158057974.arc
ORA-00280: change 1597450 for thread 1 is in sequence #3


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

open resetlogs

SQL> alter database open resetlogs;

Database altered.

▶ 22. 백업한 control file 내용과 현재 datafile 정보가 틀린경우 복구

● 1. 작업

users 테이블스페이스에 테이블생성

create table hr.emp_20
as select * from hr.employees where department_id = 20;

테이블스페이스 생성후, 테이블생성

CREATE TABLESPACE data_tbs 
DATAFILE '/u01/app/oracle/oradata/ora11g/data_tbs01.dbf' SIZE 5M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT AUTO;

create table hr.emp_50
tablespace data_tbs
as select * from hr.employees where department_id=50;

백업된 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#;

테이블이 어느 tablespace, 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_50'
and e.owner = 'HR';

● 2. 장애발생 : controlfile 삭제

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

장애발생

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
ORA-00205: error in identifying control file, check alert log for more info

● 3. restore

백업 controlfile로 restore

SQL> shutdown abort
ORACLE instance shut down.
SQL>  ! cp -av /home/oracle/backup/arch/cold_20240116/*.ctl /u01/app/oracle/oradata/ora11g/
‘/home/oracle/backup/arch/cold_20240116/control01.ctl’ -> ‘/u01/app/oracle/oradata/ora11g/control01.ctl’

● 4. recover (아카이브 파일로)

SQL> recover database using backup controlfile
ORA-00279: change 1588754 generated at 01/12/2024 10:46:14 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_1_1158057974.arc
ORA-00280: change 1588754 for thread 1 is in sequence #1


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 1589329 generated at 01/13/2024 18:14:09 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_2_1158057974.arc
ORA-00280: change 1589329 for thread 1 is in sequence #2
ORA-00278: log file '/home/oracle/arch1/arch_1_1_1158057974.arc' no longer
needed for this recovery


ORA-00279: change 1589332 generated at 01/13/2024 18:14:10 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_3_1158057974.arc
ORA-00280: change 1589332 for thread 1 is in sequence #3
ORA-00278: log file '/home/oracle/arch1/arch_1_2_1158057974.arc' no longer
needed for this recovery


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

● 5. recover 하고 current redolog 그룹 넣기
-> 에러
: 컨트롤파일이 갇고 있는 datafile이 다르므로 에러가 뜬다.

SQL>  recover database using backup controlfile
ORA-00279: change 1589332 generated at 01/13/2024 18:14:10 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_3_1158057974.arc
ORA-00280: change 1589332 for thread 1 is in sequence #3


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/ora11g/redo03.log
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 3: '/u01/app/oracle/oradata/ora11g/data_tbs01.dbf'


ORA-01112: media recovery not started

● 6. 백업컨트롤 파일 trace 남기기

SQL> alter database backup controlfile to trace as '/home/oracle/20240119.sql';

Database altered

trace를 보고 새롭게 추가된것 보기

[oracle@oracle ~]$ vi 20240119.sql
---
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORA11G" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/ora11g/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u01/app/oracle/oradata/ora11g/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oracle/oradata/ora11g/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/ora11g/system01.dbf',
  '/u01/app/oracle/oradata/ora11g/sysaux01.dbf',
  '/u01/app/oracle/product/11.2.0.4/db_1/dbs/UNNAMED00003',
  '/u01/app/oracle/oradata/ora11g/users01.dbf',
  '/u01/app/oracle/oradata/ora11g/example01.dbf',
  '/u01/app/oracle/oradata/ora11g/undotbs01.dbf'
CHARACTER SET AL32UTF8
;

이쿼리문으로도 확인 가능

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	1588754
2	SYSAUX	/u01/app/oracle/oradata/ora11g/sysaux01.dbf	ONLINE	1588754
4	USERS	/u01/app/oracle/oradata/ora11g/users01.dbf	ONLINE	1588754
5	EXAMPLE	/u01/app/oracle/oradata/ora11g/example01.dbf	ONLINE	1588754
6	UNDOTBS	/u01/app/oracle/oradata/ora11g/undotbs01.dbf	ONLINE	1588754
3	DATA_TBS	/u01/app/oracle/product/11.2.0.4/db_1/dbs/UNNAMED00003	RECOVER	1589561
---

● 7. controlfile 백업본 이후 rename 작업

controlfile에 없는 datafile 을 임시적으로 만든 이름을 -> 백업후에 추가된 datafile, tablespae 로 이름으로 변경해준다.

SQL> alter database rename file '/u01/app/oracle/product/11.2.0.4/db_1/dbs/UNNAMED00003'
to '/u01/app/oracle/oradata/ora11g/data_tbs01.dbf';  2

Database altered.

● 8. recover 하고 db open
: 다시 현재 current한 redolog 그룹을 넣어준다.

SQL> recover database using backup controlfile
ORA-00279: change 1589563 generated at 01/13/2024 18:20:38 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_3_1158057974.arc
ORA-00280: change 1589563 for thread 1 is in sequence #3


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/ora11g/redo03.log
Log applied.
Media recovery complete.

resetlogs db open

SQL> alter database open resetlogs;

Database altered.

확인

select f.file_naㄱme
from dba_extents e, dba_data_files f
where e.file_id = f.file_id
and e.segment_name in ('EMP_50','EMP_20')
and e.owner = 'HR';
----
FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ora11g/users01.dbf
/u01/app/oracle/oradata/ora11g/data_tbs01.dbf

▶ 23. 시간을 기준으로 데이터베이스를 복구 시나리오

timebase recovery 할 경우

drop tablespace|table 잘못했을때

truncate 잘못했을때

update, delete를 잘못하고 commit했을때

● 1. 작업

테이블스페이스 생성

CREATE TABLESPACE insa_tbs 
DATAFILE '/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf' SIZE 10M
AUTOEXTEND ON NEXT 1M MAXSIZE 20M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT AUTO;

datafile, tablesapce 조회

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

control file trace 남기기

SQL> alter database backup controlfile to trace as '/home/oracle/control_20240119.sql';

Database altered.

로그스위치 발생(운영중이라서 로그스위치 발생했다고 생각)

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

● 2. 장애발생 : 테이블스페이스삭제 (데이터파일에 테이블 있다고 가정)

테이블스페이스 삭제

SQL> drop tablespace insa_tbs including contents and datafiles;

Tablespace dropped.

alert log 창에 테이블스페이스 drop 한 시간이 나온다.

Sat Jan 13 19:39:21 2024
drop tablespace insa_tbs including contents and datafiles
Deleted file /u01/app/oracle/oradata/ora11g/insa_tbs01.dbf
Completed: drop tablespace insa_tbs including contents and datafiles

● 3. 백업본 datafile로 restore

db 내리기

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

백업본 datafile로 restore

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

● 4. time base recovery

날짜 포맷변경 (보기편하게)

SQL> alter session set nls_date_format ='yyyy/mm/dd hh24:mi:ss';

Session altered.

drop 테이블스페이스 이전으로 time base recovery

SQL> recover database until time '2024/01/13 19:38:00'
ORA-00279: change 1590203 generated at 01/13/2024 19:33:07 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_1_1158057974.arc
ORA-00280: change 1590203 for thread 1 is in sequence #1


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
Log applied.
Media recovery complete.
SQL> recover database open resetlogs;
ORA-00905: missing keyword

alertlog 창에 보이는 경우는
테이블스페이스 레벨은 보이지만
세그먼트레벨은 안보이므로 테이블 작업을할경우는
redologfile을 봐야한다.

또한, 현장에서는 이렇게하면 data의 갭이 발생하므로 clone db를 만들고 실행한다.

open resetlogs

SQL> alter database open resetlogs;

Database altered.

주의
: resetlogs 를 실행한 이후는 당연히 전의 백업본을 사용못한다.
다시 백업 해놔야한다.

YoonSeo Park

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

■ User Managed ( archive log mode ) Recovery 23개 시나리오

ORACLE 백업/복구 시나리오

장애, 백업,복구 시나리오 - archive log mode

● 복구전에 백업본,scn번호 전부 확인

▶ 1. 운영중 장애때문에 offline 되는 데이터파일이 손상되었을때 복구 시나리오

▶ 2. DB가 종료후 데이터파일이 손상되어 DB를 올릴때 시나리오, 데이터파일이 손상되어 DB가 종료되었을때

▶ 3. DB가 종료후 데이터파일 여러개가 손상되어 DB를 올릴때 시나리오, 데이터파일이 손상되어 DB가 종료되었을때

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

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

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

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

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

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

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

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

▶ 12. 운영중에 undo 데이터파일 손상시 시나리오 (새로운 undo 만들기)

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

▶ 14. archive 생성된후 db close 상태에서 inactive log file이 손상된 시나리오

▶ 15. DB open 상태에서 current 그룹이 아닌 redo log 그룹이 삭제

▶ 16. current redo log 그룹이 삭제된후 DB 정상적인 종료 되었을때 시나리오

▶ 17. current redo log 그룹이 삭제된후 DB 비정상적인 종료 되었을때 시나리오

▶ 18. control file의 백업본이 있을때 datafile, redolog file 손상되지 않고 control file 만 손상되었을때 시나리오.

▶ 19. datafile, redolog file 손상되지 않고 control file 만 손상되었을때 control file의 백업본을 이용해서 재생성하여 복구 시나리오

▶ 20. datafile, controlfile 손상

▶ 21. redolog file, controlfile 손상

▶ 21. 모든 datafile, redolog file, controlfile 손상

▶ 22. 백업한 control file 내용과 현재 datafile 정보가 틀린경우 복구

▶ 23. 시간을 기준으로 데이터베이스를 복구 시나리오

■ User Managed ( noarchive log mode ) Recovery 13개 시나리오

■ RMAN - Recovery 20개 시나리오

0개의 댓글