251218 TIL
2025. 12. 18. 21:49ㆍCourses/아이티윌 오라클 DBA 과정
Deferred Segment Creation(11g)
- partition 되지 않은 일반 테이블(힙 테이블)을 생성하는 경우 첫 번째 행을 입력할 때까지는 세그먼트 생성이 지연됨
- 이 기능은 기본적으로 deferred_segment_creation 파라미터 값이 true로 설정되어 있음
세그먼트 생성 단계
테이블 생성 : 데이터 딕셔너리 작업
create table hr.seg_new(id number) tablespace users;
-- 세그먼트 생성 안됨
select * from dba_segments
where owner = 'HR'
and segment_name = 'SEG_NEW';
select * from dba_extents
where owner = 'HR'
and segment_name = 'SEG_NEW';-- 테이블에 대한 딕셔너리 정보는 조회 가능
select * from dba_tables
where owner = 'HR'
and table_name = 'SEG_NEW';
select * from dba_tab_columns
where owner = 'HR'
and table_name = 'SEG_NEW';insert : 세그먼트 생성
insert into hr.seg_new(id) values(1);
-- insert하면 세그먼트 생성됨
select * from dba_segments
where owner = 'HR'
and segment_name = 'SEG_NEW';
select * from dba_extents
where owner = 'HR'
and segment_name = 'SEG_NEW';이점
- 디스크 공간 절약
- 응용 프로그램 설치 시간 향상
deferred_segment_creation
SYS@ora19c> show parameter deferred_segment_creation
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation boolean TRUEselect isses_modifiable, issys_modifiable from v$parameter where name = 'deferred_segment_creation';
- alter system set
- alter session set
-- deferred_segment_creation = false
alter session set deferred_segment_creation = false;
create table hr.seg_new(id number)tablespace users;
-- 세그먼트 생성됨
select * from dba_segments
where owner = 'HR'
and segment_name = 'SEG_NEW';
drop table hr.seg_new purge;
-- deferred_segment_creation = true
alter session set deferred_segment_creation = true;
-- 세그먼트 생성 안됨(deferred)
create table hr.seg_new(id number)tablespace users;
select * from dba_segments
where owner = 'HR'
and segment_name = 'SEG_NEW';옵션
segment creation immediate
create table hr.seg_new(id number) segment creation immediate tablespace users;
-- 세그먼트 생성됨
select * from dba_segments
where owner = 'HR'
and segment_name = 'SEG_NEW';segment create deferred(기본값)
create table hr.seg_new(id number) segment creation deferred tablespace users;
-- 세그먼트 생성 안됨(deferred)
select * from dba_segments
where owner = 'HR'
and segment_name = 'SEG_NEW';Segment Creation 옵션에 따른 장애 테스트
deferred 방식으로 테이블 생성
-- deferred_segment_creation이 true이므로 기본적으로 deferred 방식으로 테이블 생성됨
SYS@ora19c> show parameter deferred_segment_creation
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation boolean TRUE
SYS@ora19c> create table hr.test1(id number) tablespace users;
Table created.
SYS@ora19c> insert into hr.test1(id) values(1);
1 row created.
SYS@ora19c> commit;
Commit complete.
SYS@ora19c> select * from hr.test1;
ID
----------
1장애 유발
SYS@ora19c> select f.file_name
from dba_extents e, dba_data_files f
where e.file_id = f.file_id
and e.segment_name = 'TEST1'
and owner = 'HR'; 2 3 4 5
FILE_NAME
--------------------------------------------------
/u01/app/oracle/oradata/ORA19C/users01.dbf
SYS@ora19c> ! ls /u01/app/oracle/oradata/ORA19C/users01.dbf
/u01/app/oracle/oradata/ORA19C/users01.dbf
SYS@ora19c> ! rm /u01/app/oracle/oradata/ORA19C/users01.dbf
SYS@ora19c> ! ls /u01/app/oracle/oradata/ORA19C/users01.dbf
ls: cannot access /u01/app/oracle/oradata/ORA19C/users01.dbf: No such file or directory
-- 메모리에서 작업을 수행하기 때문에 문제 발생 X
SYS@ora19c> select * from hr.test1;
ID
----------
1
SYS@ora19c> insert into hr.test1(id) values(2);
1 row created.
SYS@ora19c> commit;
Commit complete.
SYS@ora19c> select * from hr.test1;
ID
----------
1
2
-- deferred 방식으로 테이블을 생성하기 때문에 문제 발생 X
SYS@ora19c> create table hr.test2(id number) tablespace users;
Table created.
SYS@ora19c> create table hr.test2(id number) segment creation immediate tablespace users;
create table hr.test2(id number) segment creation immediate tablespace users
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
-- immediate 방식으로 생성한 테이블도 문제 발생 X
SYS@ora19c> create table hr.test3(id number) segment creation immediate tablespace users;
Table created.
SYS@ora19c> create table hr.test4(id number) tablespace users;
Table created.HR 세션에서 작업 수행
HR@ora19c> select * from hr.test1;
ID
----------
1
2
HR@ora19c> select * from hr.test2;
no rows selected
HR@ora19c> select * from hr.test3;
no rows selected
HR@ora19c> select * from hr.test4;
ID
----------
1 NUMBER
-- insert시 오류 발생(오류 발생 안하는 경우도 있음)
HR@ora19c> insert into hr.test2(id) values(1);
insert into hr.test2(id) values(1)
*
ERROR at line 1:
ORA-01116: error in opening database file 7
ORA-01110: data file 7: '/u01/app/oracle/oradata/ORA19C/users01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
-- but 오류로 데이터베이스가 내려가지는 않음
HR@ora19c> select * from hr.test1;
ID
----------
1
2
- 세그먼트 생성 시 immediate 옵션을 사용하든 deferred 옵션을 사용하든 우선은 딕셔너리 테이블에 정보를 저장하고 메모리 상에서 작업하는 것 같음
- insert 시 발생했던 오류
ERROR at line 1:
ORA-01116: error in opening database file 7
ORA-01110: data file 7: '/u01/app/oracle/oradata/ORA19C/users01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
-- 또는
ERROR at line 1:
ORA-01565: error in identifying file '/u01/app/oracle/oradata/ORA19C/users01.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7복구 작업
1. 데이터파일 offline drop
SYS@ora19c> select a.file#, b.name tbs_name, a.name file_name, a.checkpoint_change#, a.status
from v$datafile a, v$tablespace b
where a.ts# = b.ts#; 2 3
FILE# TBS_NAME FILE_NAME CHECKPOINT_CHANGE# STATUS
---------- ------------------------------ -------------------------------------------------- ------------------ -------
3 SYSAUX /u01/app/oracle/oradata/ORA19C/sysaux01.dbf 2845590 ONLINE
1 SYSTEM /u01/app/oracle/oradata/ORA19C/system01.dbf 2845590 SYSTEM
4 UNDOTBS1 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf 2845590 ONLINE
7 USERS /u01/app/oracle/oradata/ORA19C/users01.dbf 2845590 ONLINE
SYS@ora19c> alter database datafile 7 offline drop;
Database altered.2. 백업 restore
SYS@ora19c> !
[oracle@ora19c ~]$ cd backup/noarch
[oracle@ora19c noarch]$ ls users01.dbf
users01.dbf
[oracle@ora19c noarch]$ cp -v users01.dbf /u01/app/oracle/oradata/ORA19C/users01.dbf
‘users01.dbf’ -> ‘/u01/app/oracle/oradata/ORA19C/users01.dbf’
[oracle@ora19c noarch]$ exit
exit3. 백업 이후 redo 적용
SYS@ora19c> recover datafile 7
Media recovery complete.4. 데이터파일 online으로 변경
SYS@ora19c> alter database datafile 7 online;
Database altered.
SYS@ora19c> select a.file#, b.name tbs_name, a.name file_name, a.checkpoint_change#, a.status
from v$datafile a, v$tablespace b
where a.ts# = b.ts#; 2 3
FILE# TBS_NAME FILE_NAME CHECKPOINT_CHANGE# STATUS
---------- ------------------------------ -------------------------------------------------- ------------------ -------
3 SYSAUX /u01/app/oracle/oradata/ORA19C/sysaux01.dbf 2845590 ONLINE
1 SYSTEM /u01/app/oracle/oradata/ORA19C/system01.dbf 2845590 SYSTEM
4 UNDOTBS1 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf 2845590 ONLINE
7 USERS /u01/app/oracle/oradata/ORA19C/users01.dbf 2849195 ONLINECTAS 복제 테이블 복구
CTAS로 테이블 복제한 경우
SYS@ora19c> create table hr.emp tablespace users as select * from hr.employees;
Table created.
SYS@ora19c> select count(*) from hr.emp;
COUNT(*)
----------
107장애 유발
SYS@ora19c> select f.file_name
from dba_extents e, dba_data_files f
where e.file_id = f.file_id
and e.segment_name = 'EMP'
and owner = 'HR'; 2 3 4 5
FILE_NAME
--------------------------------------------------
/u01/app/oracle/oradata/ORA19C/users01.dbf
SYS@ora19c> ! ls /u01/app/oracle/oradata/ORA19C/users01.dbf
/u01/app/oracle/oradata/ORA19C/users01.dbf
SYS@ora19c> ! rm /u01/app/oracle/oradata/ORA19C/users01.dbf
SYS@ora19c> ! ls /u01/app/oracle/oradata/ORA19C/users01.dbf
ls: cannot access /u01/app/oracle/oradata/ORA19C/users01.dbf: No such file or directory
SYS@ora19c> alter system checkpoint;
alter system checkpoint
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 11401
Session ID: 125 Serial number: 22975
데이터베이스 재시작
SYS@ora19c> conn / as sysdba
Connected to an idle instance.
SYS@ora19c> startup
ORACLE instance started.
Total System Global Area 830469472 bytes
Fixed Size 8901984 bytes
Variable Size 595591168 bytes
Database Buffers 218103808 bytes
Redo Buffers 7872512 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '/u01/app/oracle/oradata/ORA19C/users01.dbf'
SYS@ora19c> select * from v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME CON_ID
---------- ------- ------- ------------------------------ ---------- --------- ----------
7 ONLINE ONLINE FILE NOT FOUND 0 0복구
SYS@ora19c> alter database datafile 7 offline drop;
Database altered.
SYS@ora19c> select a.file#, b.name tbs_name, a.name file_name, a.checkpoint_change#, a.status
from v$datafile a, v$tablespace b
where a.ts# = b.ts#; 2 3
FILE# TBS_NAME FILE_NAME CHECKPOINT_CHANGE# STATUS
---------- ------------------------------ -------------------------------------------------- ------------------ -------
1 SYSTEM /u01/app/oracle/oradata/ORA19C/system01.dbf 2845590 SYSTEM
3 SYSAUX /u01/app/oracle/oradata/ORA19C/sysaux01.dbf 2845590 ONLINE
4 UNDOTBS1 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf 2845590 ONLINE
7 USERS /u01/app/oracle/oradata/ORA19C/users01.dbf 2845590 RECOVER
SYS@ora19c> alter database open;
Database altered.
SYS@ora19c> !
[oracle@ora19c ~]$ cd backup/noarch
[oracle@ora19c noarch]$ ls users01.dbf
users01.dbf
[oracle@ora19c noarch]$ cp -v users01.dbf /u01/app/oracle/oradata/ORA19C/users01.dbf
‘users01.dbf’ -> ‘/u01/app/oracle/oradata/ORA19C/users01.dbf’
[oracle@ora19c noarch]$ exit
exit
SYS@ora19c> recover datafile 7
Media recovery complete.
SYS@ora19c> select a.file#, b.name tbs_name, a.name file_name, a.checkpoint_change#, a.status
from v$datafile a, v$tablespace b
where a.ts# = b.ts#; 2 3
FILE# TBS_NAME FILE_NAME CHECKPOINT_CHANGE# STATUS
---------- ------------------------------ -------------------------------------------------- ------------------ -------
3 SYSAUX /u01/app/oracle/oradata/ORA19C/sysaux01.dbf 2947687 ONLINE
1 SYSTEM /u01/app/oracle/oradata/ORA19C/system01.dbf 2947687 SYSTEM
4 UNDOTBS1 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf 2947687 ONLINE
7 USERS /u01/app/oracle/oradata/ORA19C/users01.dbf 2947687 OFFLINE
SYS@ora19c> alter database datafile 7 online;
Database altered.완전 복구 실패
- CTAS로 테이블 생성 시 redo가 발생하지 않음
- But 복제 테이블이기 때문에 다시 복제하면 됨
SYS@ora19c> select count(*) from hr.emp;
select count(*) from hr.emp
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 7, block # 347)
ORA-01110: data file 7: '/u01/app/oracle/oradata/ORA19C/users01.dbf'
ORA-26040: Data block was loaded using the NOLOGGING optionCTAS로 스키마만 복제 후 insert 서브쿼리로 데이터 복제한 경우
SYS@ora19c> create table hr.emp tablespace users as select * from hr.employees where 1=2;
Table created.
SYS@ora19c> select count(*) from hr.emp;
COUNT(*)
----------
0
SYS@ora19c> insert into hr.emp select * from hr.employees;
107 rows created.
SYS@ora19c> select count(*) from hr.emp;
COUNT(*)
----------
107
SYS@ora19c> commit;
Commit complete.장애 유발
SYS@ora19c> select f.file_name
from dba_extents e, dba_data_files f
where e.file_id = f.file_id
and e.segment_name = 'EMP'
and owner = 'HR'; 2 3 4 5
FILE_NAME
--------------------------------------------------
/u01/app/oracle/oradata/ORA19C/users01.dbf
SYS@ora19c> ! ls /u01/app/oracle/oradata/ORA19C/users01.dbf
/u01/app/oracle/oradata/ORA19C/users01.dbf
SYS@ora19c> ! rm /u01/app/oracle/oradata/ORA19C/users01.dbf
SYS@ora19c> ! ls /u01/app/oracle/oradata/ORA19C/users01.dbf
ls: cannot access /u01/app/oracle/oradata/ORA19C/users01.dbf: No such file or directory
SYS@ora19c> alter system checkpoint;
alter system checkpoint
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 13129
Session ID: 125 Serial number: 2838
데이터베이스 재시작
SYS@ora19c> conn / as sysdba
Connected to an idle instance.
SYS@ora19c> startup
ORACLE instance started.
Total System Global Area 830469472 bytes
Fixed Size 8901984 bytes
Variable Size 595591168 bytes
Database Buffers 218103808 bytes
Redo Buffers 7872512 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '/u01/app/oracle/oradata/ORA19C/users01.dbf'
SYS@ora19c> select * from v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME CON_ID
---------- ------- ------- ------------------------------ ---------- --------- ----------
7 ONLINE ONLINE FILE NOT FOUND 0 0복구
SYS@ora19c> alter database datafile 7 offline drop;
Database altered.
SYS@ora19c> select a.file#, b.name tbs_name, a.name file_name, a.checkpoint_change#, a.status
from v$datafile a, v$tablespace b
where a.ts# = b.ts#; 2 3
FILE# TBS_NAME FILE_NAME CHECKPOINT_CHANGE# STATUS
---------- ------------------------------ -------------------------------------------------- ------------------ -------
1 SYSTEM /u01/app/oracle/oradata/ORA19C/system01.dbf 2845590 SYSTEM
3 SYSAUX /u01/app/oracle/oradata/ORA19C/sysaux01.dbf 2845590 ONLINE
4 UNDOTBS1 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf 2845590 ONLINE
7 USERS /u01/app/oracle/oradata/ORA19C/users01.dbf 2845590 RECOVER
SYS@ora19c> alter database open;
Database altered.
SYS@ora19c> !
[oracle@ora19c ~]$ cd backup/noarch
[oracle@ora19c noarch]$ ls users01.dbf
users01.dbf
[oracle@ora19c noarch]$ cp -v users01.dbf /u01/app/oracle/oradata/ORA19C/users01.dbf
‘users01.dbf’ -> ‘/u01/app/oracle/oradata/ORA19C/users01.dbf’
[oracle@ora19c noarch]$ exit
exit
SYS@ora19c> recover datafile 7
Media recovery complete.
SYS@ora19c> select a.file#, b.name tbs_name, a.name file_name, a.checkpoint_change#, a.status
from v$datafile a, v$tablespace b
where a.ts# = b.ts#; 2 3
FILE# TBS_NAME FILE_NAME CHECKPOINT_CHANGE# STATUS
---------- ------------------------------ -------------------------------------------------- ------------------ -------
3 SYSAUX /u01/app/oracle/oradata/ORA19C/sysaux01.dbf 2947743 ONLINE
1 SYSTEM /u01/app/oracle/oradata/ORA19C/system01.dbf 2947743 SYSTEM
4 UNDOTBS1 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf 2947743 ONLINE
7 USERS /u01/app/oracle/oradata/ORA19C/users01.dbf 2947743 OFFLINE
SYS@ora19c> alter database datafile 7 online;
Database altered.
SYS@ora19c> select a.file#, b.name tbs_name, a.name file_name, a.checkpoint_change#, a.status
from v$datafile a, v$tablespace b
where a.ts# = b.ts#; 2 3
FILE# TBS_NAME FILE_NAME CHECKPOINT_CHANGE# STATUS
---------- ------------------------------ -------------------------------------------------- ------------------ -------
3 SYSAUX /u01/app/oracle/oradata/ORA19C/sysaux01.dbf 2947743 ONLINE
1 SYSTEM /u01/app/oracle/oradata/ORA19C/system01.dbf 2947743 SYSTEM
4 UNDOTBS1 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf 2947743 ONLINE
7 USERS /u01/app/oracle/oradata/ORA19C/users01.dbf 2948566 ONLINE완전 복구 성공
SYS@ora19c> select count(*) from hr.emp;
COUNT(*)
----------
107시나리오 14 - 1 : 특정한 데이터 파일이 손상(백업 이후 리두 없음, 불완전 복구, 백업은 데이터 파일, 컨트롤 파일만)
- 실제 현장에서는 redo log를 백업하지 않는 경우가 많음
현재 상태 확인
SYS@ora19c> select checkpoint_change#, scn_to_timestamp(checkpoint_change#) from v$database;
CHECKPOINT_CHANGE# SCN_TO_TIMESTAMP(CHECKPOINT_CHANGE#)
------------------ ---------------------------------------------------------------------------
2845590 15-DEC-25 04.00.14.000000000 PM
SYS@ora19c> select a.file#, b.name tbs_name, a.name file_name, a.checkpoint_change#, a.status
from v$datafile a, v$tablespace b
where a.ts# = b.ts#;
2 3
FILE# TBS_NAME FILE_NAME CHECKPOINT_CHANGE# STATUS
---------- ------------------------------ -------------------------------------------------- ------------------ -------
3 SYSAUX /u01/app/oracle/oradata/ORA19C/sysaux01.dbf 2845590 ONLINE
1 SYSTEM /u01/app/oracle/oradata/ORA19C/system01.dbf 2845590 SYSTEM
4 UNDOTBS1 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf 2845590 ONLINE
7 USERS /u01/app/oracle/oradata/ORA19C/users01.dbf 2845590 ONLINE
SYS@ora19c> select a.group#, a.sequence#, b.member, a.bytes/1024/1024 mb, a.status, a.first_change#, a.next_change#
from v$log a, v$logfile b
where a.group# = b.group#; 2 3
GROUP# SEQUENCE# MEMBER MB STATUS FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- -------------------------------------------------- ---------- ---------------- ------------- ------------
3 28 /u01/app/oracle/oradata/ORA19C/redo03.log 50 CURRENT 2822872 1.8447E+19
2 27 /u01/app/oracle/oradata/ORA19C/redo02.log 50 INACTIVE 2797846 2822872
1 26 /u01/app/oracle/oradata/ORA19C/redo01.log 50 INACTIVE 2767903 2797846백업 이후 작업 수행
SYS@ora19c> create table hr.emp tablespace users as select * from hr.employees where 1=2;
Table created.
SYS@ora19c> insert into hr.emp select * from hr.employees;
107 rows created.
SYS@ora19c> commit;
Commit complete.로그 스위치 발생
SYS@ora19c> alter system switch logfile;
System altered.
SYS@ora19c> /
System altered.
SYS@ora19c> /
System altered.
SYS@ora19c> select a.group#, a.sequence#, b.member, a.bytes/1024/1024 mb, a.status, a.first_change#, a.next_change#
from v$log a, v$logfile b
where a.group# = b.group#; 2 3
GROUP# SEQUENCE# MEMBER MB STATUS FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- -------------------------------------------------- ---------- ---------------- ------------- ------------
3 31 /u01/app/oracle/oradata/ORA19C/redo03.log 50 CURRENT 2847817 1.8447E+19
2 30 /u01/app/oracle/oradata/ORA19C/redo02.log 50 INACTIVE 2847814 2847817
1 29 /u01/app/oracle/oradata/ORA19C/redo01.log 50 INACTIVE 2847811 2847814장애 유발
SYS@ora19c> select f.file_name
from dba_extents e, dba_data_files f
where e.file_id = f.file_id
and e.segment_name = 'EMP'
and owner = 'HR'; 2 3 4 5
FILE_NAME
--------------------------------------------------
/u01/app/oracle/oradata/ORA19C/users01.dbf
SYS@ora19c> ! ls /u01/app/oracle/oradata/ORA19C/users01.dbf
/u01/app/oracle/oradata/ORA19C/users01.dbf
SYS@ora19c> ! rm /u01/app/oracle/oradata/ORA19C/users01.dbf
SYS@ora19c> ! ls /u01/app/oracle/oradata/ORA19C/users01.dbf
ls: cannot access /u01/app/oracle/oradata/ORA19C/users01.dbf: No such file or directory체크 포인트 발생
SYS@ora19c> alter system checkpoint;
System altered.
-- alert log
2025-12-18T12:03:05.081289+09:00
Errors in file /u01/app/oracle/diag/rdbms/ora19c/ora19c/trace/ora19c_mz00_16612.trc:
ORA-01110: data file 7: '/u01/app/oracle/oradata/ORA19C/users01.dbf'
ORA-01565: error in identifying file '/u01/app/oracle/oradata/ORA19C/users01.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7완전 복구 시도 실패(리두가 없기 때문)
SYS@ora19c> select a.file#, b.name tbs_name, a.name file_name, a.checkpoint_change#, a.status
from v$datafile a, v$tablespace b
where a.ts# = b.ts#; 2 3
FILE# TBS_NAME FILE_NAME CHECKPOINT_CHANGE# STATUS
---------- ------------------------------ -------------------------------------------------- ------------------ -------
3 SYSAUX /u01/app/oracle/oradata/ORA19C/sysaux01.dbf 2848057 ONLINE
1 SYSTEM /u01/app/oracle/oradata/ORA19C/system01.dbf 2848057 SYSTEM
4 UNDOTBS1 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf 2848057 ONLINE
7 USERS /u01/app/oracle/oradata/ORA19C/users01.dbf 2848057 ONLINE
SYS@ora19c> alter database datafile 7 offline drop;
Database altered.
SYS@ora19c> !
[oracle@ora19c ~]$ cd backup/noarch
[oracle@ora19c noarch]$ ls users01.dbf
users01.dbf
[oracle@ora19c noarch]$ cp -v users01.dbf /u01/app/oracle/oradata/ORA19C/users01.dbf
‘users01.dbf’ -> ‘/u01/app/oracle/oradata/ORA19C/users01.dbf’
[oracle@ora19c noarch]$ exit
exit
SYS@ora19c> recover datafile 7
ORA-00279: change 2845587 generated at 12/15/2025 16:00:17 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/fast_recovery_area/ORA19C/archivelog/2025_12_18/o1_mf_1_28_%u_.arc
ORA-00280: change 2845587 for thread 1 is in sequence #28
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SYS@ora19c> alter database datafile 7 online;
alter database datafile 7 online
*
ERROR at line 1:
ORA-01113: file 7 needs media recovery
ORA-01110: data file 7: '/u01/app/oracle/oradata/ORA19C/users01.dbf'불완전 복구 방식 진행(cancel base recovery)
- 백업은 컨트롤 파일, 데이터 파일만 있기 때문
1. 컨트롤 파일, 데이터 파일 restore
SYS@ora19c> shutdown abort
ORACLE instance shut down.
SYS@ora19c> !
[oracle@ora19c ~]$ cd backup/noarch
[oracle@ora19c noarch]$ ls *.{dbf,ctl}
control01.ctl sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf
[oracle@ora19c noarch]$ cp -v *.{dbf,ctl} /u01/app/oracle/oradata/ORA19C/
‘sysaux01.dbf’ -> ‘/u01/app/oracle/oradata/ORA19C/sysaux01.dbf’
‘system01.dbf’ -> ‘/u01/app/oracle/oradata/ORA19C/system01.dbf’
‘temp01.dbf’ -> ‘/u01/app/oracle/oradata/ORA19C/temp01.dbf’
‘undotbs01.dbf’ -> ‘/u01/app/oracle/oradata/ORA19C/undotbs01.dbf’
‘users01.dbf’ -> ‘/u01/app/oracle/oradata/ORA19C/users01.dbf’
‘control01.ctl’ -> ‘/u01/app/oracle/oradata/ORA19C/control01.ctl’
[oracle@ora19c noarch]$ exit
exit2. 백업 컨트롤 파일 이용해서 startup mount 시작
SYS@ora19c> startup mount
ORACLE instance started.
Total System Global Area 830469472 bytes
Fixed Size 8901984 bytes
Variable Size 595591168 bytes
Database Buffers 218103808 bytes
Redo Buffers 7872512 bytes
Database mounted.
SYS@ora19c> select status from v$instance;
STATUS
------------
MOUNTED
SYS@ora19c> select a.group#, a.sequence#, b.member, a.bytes/1024/1024 mb, a.status, a.first_change#, a.next_change#
from v$log a, v$logfile b
where a.group# = b.group#; 2 3
GROUP# SEQUENCE# MEMBER MB STATUS FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- -------------------------------------------------- ---------- ---------------- ------------- ------------
1 26 /u01/app/oracle/oradata/ORA19C/redo01.log 50 INACTIVE 2767903 2797846
3 28 /u01/app/oracle/oradata/ORA19C/redo03.log 50 CURRENT 2822872 1.8447E+19
2 27 /u01/app/oracle/oradata/ORA19C/redo02.log 50 INACTIVE 2797846 28228723. 백업 컨트롤 파일 이용해서 cancel base recovery 수행
SYS@ora19c> recover database until cancel using backup controlfile
ORA-00279: change 2845587 generated at 12/15/2025 16:00:17 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/fast_recovery_area/ORA19C/archivelog/2025_12_18/o1_mf_1_28_%u_.arc
ORA-00280: change 2845587 for thread 1 is in sequence #28
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.4. 데이터베이스를 resetlogs 옵션으로 open
SYS@ora19c> alter database open resetlogs;
Database altered.
SYS@ora19c> select a.group#, a.sequence#, b.member, a.bytes/1024/1024 mb, a.status, a.first_change#, a.next_change#
from v$log a, v$logfile b
where a.group# = b.group#; 2 3
GROUP# SEQUENCE# MEMBER MB STATUS FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- -------------------------------------------------- ---------- ---------------- ------------- ------------
3 0 /u01/app/oracle/oradata/ORA19C/redo03.log 50 UNUSED 0 0
2 0 /u01/app/oracle/oradata/ORA19C/redo02.log 50 UNUSED 0 0
1 1 /u01/app/oracle/oradata/ORA19C/redo01.log 50 CURRENT 2845588 1.8447E+19시나리오 14 - 2 : 전체 데이터파일 손상(백업 이후 리두 없음, 불완전 복구, 백업은 데이터 파일, 컨트롤 파일만)
백업
SYS@ora19c> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@ora19c> !
[oracle@ora19c ~]$ cd backup
[oracle@ora19c backup]$ ls
control01.ctl recover redo02.log sysaux01.dbf temp01.dbf users01.dbf
noarch redo01.log redo03.log system01.dbf undotbs01.dbf
# 새로운 백업 폴더 생성
[oracle@ora19c backup]$ mkdir 20251218
[oracle@ora19c backup]$ cd 20251218
[oracle@ora19c 20251218]$ pwd
/home/oracle/backup/20251218
[oracle@ora19c 20251218]$ cp -v /u01/app/oracle/oradata/ORA19C/* .
‘/u01/app/oracle/oradata/ORA19C/control01.ctl’ -> ‘./control01.ctl’
‘/u01/app/oracle/oradata/ORA19C/recover’ -> ‘./recover’
‘/u01/app/oracle/oradata/ORA19C/redo01.log’ -> ‘./redo01.log’
‘/u01/app/oracle/oradata/ORA19C/redo02.log’ -> ‘./redo02.log’
‘/u01/app/oracle/oradata/ORA19C/redo03.log’ -> ‘./redo03.log’
‘/u01/app/oracle/oradata/ORA19C/sysaux01.dbf’ -> ‘./sysaux01.dbf’
‘/u01/app/oracle/oradata/ORA19C/system01.dbf’ -> ‘./system01.dbf’
‘/u01/app/oracle/oradata/ORA19C/temp01.dbf’ -> ‘./temp01.dbf’
‘/u01/app/oracle/oradata/ORA19C/undotbs01.dbf’ -> ‘./undotbs01.dbf’
‘/u01/app/oracle/oradata/ORA19C/users01.dbf’ -> ‘./users01.dbf’
[oracle@ora19c 20251218]$ ls
control01.ctl redo01.log redo03.log system01.dbf undotbs01.dbf
recover redo02.log sysaux01.dbf temp01.dbf users01.dbf
# 리두 로그 파일 삭제
[oracle@ora19c 20251218]$ rm redo0*
[oracle@ora19c 20251218]$ ls
control01.ctl recover sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf장애 유발
# 전체 데이터파일 삭제
[oracle@ora19c ~]$ cd $ORACLE_BASE/oradata/ORA19C
[oracle@ora19c ORA19C]$ ls
control01.ctl redo01.log redo03.log system01.dbf undotbs01.dbf
recover redo02.log sysaux01.dbf temp01.dbf users01.dbf
[oracle@ora19c ORA19C]$ rm -f *
[oracle@ora19c ORA19C]$ ls
[oracle@ora19c ORA19C]$ exit
exit
SYS@ora19c> startup
ORACLE instance started.
Total System Global Area 830469472 bytes
Fixed Size 8901984 bytes
Variable Size 595591168 bytes
Database Buffers 218103808 bytes
Redo Buffers 7872512 bytes
ORA-00205: error in identifying control file, check alert log for more info복구
1. 백업본 restore
SYS@ora19c> shutdown abort
ORACLE instance shut down.
SYS@ora19c> !
[oracle@ora19c ~]$ cd backup/20251218/
[oracle@ora19c 20251218]$ pwd
/home/oracle/backup/20251218
[oracle@ora19c 20251218]$ ls
control01.ctl recover sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf
[oracle@ora19c 20251218]$ cp -v * /u01/app/oracle/oradata/ORA19C/
‘control01.ctl’ -> ‘/u01/app/oracle/oradata/ORA19C/control01.ctl’
‘recover’ -> ‘/u01/app/oracle/oradata/ORA19C/recover’
‘sysaux01.dbf’ -> ‘/u01/app/oracle/oradata/ORA19C/sysaux01.dbf’
‘system01.dbf’ -> ‘/u01/app/oracle/oradata/ORA19C/system01.dbf’
‘temp01.dbf’ -> ‘/u01/app/oracle/oradata/ORA19C/temp01.dbf’
‘undotbs01.dbf’ -> ‘/u01/app/oracle/oradata/ORA19C/undotbs01.dbf’
‘users01.dbf’ -> ‘/u01/app/oracle/oradata/ORA19C/users01.dbf’
[oracle@ora19c 20251218]$ ls /u01/app/oracle/oradata/ORA19C/
control01.ctl recover sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf
[oracle@ora19c 20251218]$ exit
exit2. 백업 컨트롤 파일 이용해 startup mount
SYS@ora19c> startup mount
ORACLE instance started.
Total System Global Area 830469472 bytes
Fixed Size 8901984 bytes
Variable Size 595591168 bytes
Database Buffers 218103808 bytes
Redo Buffers 7872512 bytes
Database mounted.3. 백업 컨트롤 파일 이용해 cancel base recovery
SYS@ora19c> recover database until cancel using backup controlfile
ORA-00279: change 2847570 generated at 12/18/2025 14:28:36 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/fast_recovery_area/ORA19C/archivelog/2025_12_18/o1_mf_1_28_%u_.arc
ORA-00280: change 2847570 for thread 1 is in sequence #28
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SYS@ora19c> select a.group#, a.sequence#, b.member, a.bytes/1024/1024 mb, a.status, a.first_change#, a.next_change#
from v$log a, v$logfile b
where a.group# = b.group#; 2 3
GROUP# SEQUENCE# MEMBER MB STATUS FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- -------------------------------------------------- ---------- ---------------- ------------- ------------
1 26 /u01/app/oracle/oradata/ORA19C/redo01.log 50 INACTIVE 2767903 2797846
3 28 /u01/app/oracle/oradata/ORA19C/redo03.log 50 CURRENT 2822872 1.8447E+19
2 27 /u01/app/oracle/oradata/ORA19C/redo02.log 50 INACTIVE 2797846 28228724. 데이터베이스를 resetlogs 옵션으로 open
SYS@ora19c> alter database open resetlogs;
Database altered.
SYS@ora19c> select a.group#, a.sequence#, b.member, a.bytes/1024/1024 mb, a.status, a.first_change#, a.next_change#
from v$log a, v$logfile b
where a.group# = b.group#; 2 3
GROUP# SEQUENCE# MEMBER MB STATUS FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- -------------------------------------------------- ---------- ---------------- ------------- ------------
3 0 /u01/app/oracle/oradata/ORA19C/redo03.log 50 UNUSED 0 0
2 0 /u01/app/oracle/oradata/ORA19C/redo02.log 50 UNUSED 0 0
1 1 /u01/app/oracle/oradata/ORA19C/redo01.log 50 CURRENT 2847571 1.8447E+19
SYS@ora19c> select count(*) from hr.employees;
COUNT(*)
----------
107시나리오 15 - 1 : 데이터 파일, 리두로그 파일은 손상되지 않고 컨트롤 파일만 손상되었을 경우 복구
현재 상태 확인
SYS@ora19c> select checkpoint_change#, scn_to_timestamp(checkpoint_change#) from v$database;
CHECKPOINT_CHANGE# SCN_TO_TIMESTAMP(CHECKPOINT_CHANGE#)
------------------ ---------------------------------------------------------------------------
2845590 15-DEC-25 04.00.14.000000000 PM
SYS@ora19c> select a.file#, b.name tbs_name, a.name file_name, a.checkpoint_change#, a.status
from v$datafile a, v$tablespace b
where a.ts# = b.ts#; 2 3
FILE# TBS_NAME FILE_NAME CHECKPOINT_CHANGE# STATUS
---------- ------------------------------ -------------------------------------------------- ------------------ -------
3 SYSAUX /u01/app/oracle/oradata/ORA19C/sysaux01.dbf 2845590 ONLINE
1 SYSTEM /u01/app/oracle/oradata/ORA19C/system01.dbf 2845590 SYSTEM
4 UNDOTBS1 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf 2845590 ONLINE
7 USERS /u01/app/oracle/oradata/ORA19C/users01.dbf 2845590 ONLINE
SYS@ora19c> select a.group#, a.sequence#, b.member, a.bytes/1024/1024 mb, a.status, a.first_change#, a.next_change#
from v$log a, v$logfile b
where a.group# = b.group#; 2 3
GROUP# SEQUENCE# MEMBER MB STATUS FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- -------------------------------------------------- ---------- ---------------- ------------- ------------
3 28 /u01/app/oracle/oradata/ORA19C/redo03.log 50 CURRENT 2822872 1.8447E+19
2 27 /u01/app/oracle/oradata/ORA19C/redo02.log 50 INACTIVE 2797846 2822872
1 26 /u01/app/oracle/oradata/ORA19C/redo01.log 50 INACTIVE 2767903 2797846백업 이후 작업 수행
SYS@ora19c> create table hr.emp tablespace users as select * from hr.employees where 1 = 2;
Table created.
SYS@ora19c> insert into hr.emp select * from hr.employees;
107 rows created.
SYS@ora19c> commit;
Commit complete.
SYS@ora19c> select count(*) from hr.emp;
COUNT(*)
----------
107장애 유발
SYS@ora19c> select name from v$controlfile;
NAME
--------------------------------------------------
/u01/app/oracle/oradata/ORA19C/control01.ctl
SYS@ora19c> ! ls /u01/app/oracle/oradata/ORA19C/control01.ctl
/u01/app/oracle/oradata/ORA19C/control01.ctl
SYS@ora19c> ! rm /u01/app/oracle/oradata/ORA19C/control01.ctl
SYS@ora19c> ! ls /u01/app/oracle/oradata/ORA19C/control01.ctl
ls: cannot access /u01/app/oracle/oradata/ORA19C/control01.ctl: No such file or directory
SYS@ora19c> alter system checkpoint;
System altered.
SYS@ora19c> shutdown immediate
Database closed.
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/ORA19C/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3복구
1. 백업 컨트롤 파일 restore
SYS@ora19c> conn / as sysdba
Connected to an idle instance.
SYS@ora19c> !
[oracle@ora19c ~]$ cd backup/noarch
[oracle@ora19c noarch]$ ls control01.ctl
control01.ctl
[oracle@ora19c noarch]$ cp -v control01.ctl /u01/app/oracle/oradata/ORA19C/control01.ctl
‘control01.ctl’ -> ‘/u01/app/oracle/oradata/ORA19C/control01.ctl’
[oracle@ora19c noarch]$ exit
exit2. 백업 컨트롤 파일 이용해서 mount 시작
SYS@ora19c> startup mount
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number: 0
SYS@ora19c> exit
Disconnected
[oracle@ora19c ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Dec 18 15:23:29 2025
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected.
SYS@ora19c> shutdown abort
ORACLE instance shut down.
SYS@ora19c> startup mount
ORACLE instance started.
Total System Global Area 830469472 bytes
Fixed Size 8901984 bytes
Variable Size 595591168 bytes
Database Buffers 218103808 bytes
Redo Buffers 7872512 bytes
Database mounted.3. 백업 컨트롤 파일 이용해 cancel base recovery
SYS@ora19c> recover database using backup controlfile
ORA-00279: change 2845587 generated at 12/12/2025 18:00:43 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/fast_recovery_area/ORA19C/archivelog/2025_12_18/o1_mf_1_28_%u_.arc
ORA-00280: change 2845587 for thread 1 is in sequence #28
-- 자동으로 필요한 리두 로그 파일 찾지 못함
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log '/u01/app/oracle/fast_recovery_area/ORA19C/archivelog/2025_12_18/o1_mf_1_28_%u_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
ORA-00308: cannot open archived log '/u01/app/oracle/fast_recovery_area/ORA19C/archivelog/2025_12_18/o1_mf_1_28_%u_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
SYS@ora19c> recover database using backup controlfile
ORA-00279: change 2845587 generated at 12/12/2025 18:00:43 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/fast_recovery_area/ORA19C/archivelog/2025_12_18/o1_mf_1_28_%u_.arc
ORA-00280: change 2845587 for thread 1 is in sequence #28 <<- redo log file을 찾아야함(alert_ora19c.log)
-- alert log에서 찾은 리두 로그 파일 적용
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/ORA19C/redo03.log
Log applied.
Media recovery complete.4. 데이터베이스를 resetlogs 옵션으로 open
SYS@ora19c> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SYS@ora19c> alter database open resetlogs;
Database altered.
SYS@ora19c> select a.group#, a.sequence#, b.member, a.bytes/1024/1024 mb, a.status, a.first_change#, a.next_change#
from v$log a, v$logfile b
where a.group# = b.group#; 2 3
GROUP# SEQUENCE# MEMBER MB STATUS FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- -------------------------------------------------- ---------- ---------------- ------------- ------------
3 0 /u01/app/oracle/oradata/ORA19C/redo03.log 50 UNUSED 0 0
2 0 /u01/app/oracle/oradata/ORA19C/redo02.log 50 UNUSED 0 0
1 1 /u01/app/oracle/oradata/ORA19C/redo01.log 50 CURRENT 2848980 1.8447E+19시나리오 15 - 2 : Log Switch 발생
SYS@ora19c> select a.group#, a.sequence#, b.member, a.bytes/1024/1024 mb, a.status, a.first_change#, a.next_change#
from v$log a, v$logfile b
where a.group# = b.group#;
2 3
GROUP# SEQUENCE# MEMBER MB STATUS FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- -------------------------------------------------- ---------- ---------------- ------------- ------------
3 28 /u01/app/oracle/oradata/ORA19C/redo03.log 50 CURRENT 2822872 1.8447E+19
2 27 /u01/app/oracle/oradata/ORA19C/redo02.log 50 INACTIVE 2797846 2822872
1 26 /u01/app/oracle/oradata/ORA19C/redo01.log 50 INACTIVE 2767903 2797846
SYS@ora19c> create table hr.emp as select * from hr.employees where 1 = 2;
Table created.
SYS@ora19c> insert into hr.emp select * from hr.employees;
107 rows created.
SYS@ora19c> commit;
Commit complete.
SYS@ora19c> select count(*) from hr.emp;
COUNT(*)
----------
107
# 로그 스위치 발생(1번만)
SYS@ora19c> alter system switch logfile;
System altered.
SYS@ora19c> select a.group#, a.sequence#, b.member, a.bytes/1024/1024 mb, a.status, a.first_change#, a.next_change#
from v$log a, v$logfile b
where a.group# = b.group#;
2 3
GROUP# SEQUENCE# MEMBER MB STATUS FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- -------------------------------------------------- ---------- ---------------- ------------- ------------
3 28 /u01/app/oracle/oradata/ORA19C/redo03.log 50 ACTIVE 2822872 2847622
2 27 /u01/app/oracle/oradata/ORA19C/redo02.log 50 INACTIVE 2797846 2822872
1 29 /u01/app/oracle/oradata/ORA19C/redo01.log 50 CURRENT 2847622 1.8447E+19장애 유발
SYS@ora19c> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@ora19c> ! rm /u01/app/oracle/oradata/ORA19C/control01.ctl
SYS@ora19c> startup
ORACLE instance started.
Total System Global Area 830469472 bytes
Fixed Size 8901984 bytes
Variable Size 595591168 bytes
Database Buffers 218103808 bytes
Redo Buffers 7872512 bytes
ORA-00205: error in identifying control file, check alert log for more info복구
1. 백업본으로 restore
SYS@ora19c> shutdown abort
ORACLE instance shut down.
SYS@ora19c> !
[oracle@ora19c ~]$ cd backup/noarch
[oracle@ora19c noarch]$ ls control01.ctl
control01.ctl
[oracle@ora19c noarch]$ cp -v control01.ctl /u01/app/oracle/oradata/ORA19C/control01.ctl
‘control01.ctl’ -> ‘/u01/app/oracle/oradata/ORA19C/control01.ctl’
[oracle@ora19c noarch]$ exit
exit2. 백업 컨트롤 파일 이용해 startup mount
SYS@ora19c> startup mount
ORACLE instance started.
Total System Global Area 830469472 bytes
Fixed Size 8901984 bytes
Variable Size 595591168 bytes
Database Buffers 218103808 bytes
Redo Buffers 7872512 bytes
Database mounted.
SYS@ora19c> select a.group#, a.sequence#, b.member, a.bytes/1024/1024 mb, a.status, a.first_change#, a.next_change#
from v$log a, v$logfile b
where a.group# = b.group#; 2 3
GROUP# SEQUENCE# MEMBER MB STATUS FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- -------------------------------------------------- ---------- ---------------- ------------- ------------
1 26 /u01/app/oracle/oradata/ORA19C/redo01.log 50 INACTIVE 2767903 2797846
3 28 /u01/app/oracle/oradata/ORA19C/redo03.log 50 CURRENT 2822872 1.8447E+19
2 27 /u01/app/oracle/oradata/ORA19C/redo02.log 50 INACTIVE 2797846 28228723. 백업 컨트롤 파일 이용해 cancel base recovery
SYS@ora19c> recover database using backup controlfile;
ORA-00279: change 2845587 generated at 12/12/2025 18:00:43 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/fast_recovery_area/ORA19C/archivelog/2025_12_18/o1_mf_1_28_%u_.arc
ORA-00280: change 2845587 for thread 1 is in sequence #28
# 시퀀스 28번 redo log file 적용
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/ORA19C/redo03.log
ORA-00279: change 2847622 generated at 12/18/2025 15:46:05 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/fast_recovery_area/ORA19C/archivelog/2025_12_18/o1_mf_1_29_%u_.arc
ORA-00280: change 2847622 for thread 1 is in sequence #29
ORA-00278: log file '/u01/app/oracle/oradata/ORA19C/redo03.log' no longer needed for this recovery
# 시퀀스 29번 redo log file 적용
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/ORA19C/redo01.log
Log applied.
Media recovery complete.4. 데이터베이스를 resetlogs 옵션으로 open
SYS@ora19c> alter database open resetlogs;
Database altered.시나리오 16 : 데이터 파일, 리두로그 파일은 손상되지 않고 정상 종료 후 컨트롤 파일만 손상되었을 경우(백업 이후 리두 존재, 컨트롤 파일 재생성 복구)
현재 상태 확인
SYS@ora19c> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
2845590
SYS@ora19c> select a.file#, b.name tbs_name, a.name file_name, a.checkpoint_change#, a.status
from v$datafile a, v$tablespace b
where a.ts# = b.ts#; 2 3
FILE# TBS_NAME FILE_NAME CHECKPOINT_CHANGE# STATUS
---------- ------------------------------ -------------------------------------------------- ------------------ -------
3 SYSAUX /u01/app/oracle/oradata/ORA19C/sysaux01.dbf 2845590 ONLINE
1 SYSTEM /u01/app/oracle/oradata/ORA19C/system01.dbf 2845590 SYSTEM
4 UNDOTBS1 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf 2845590 ONLINE
7 USERS /u01/app/oracle/oradata/ORA19C/users01.dbf 2845590 ONLINE
SYS@ora19c> select a.group#, a.sequence#, b.member, a.bytes/1024/1024 mb, a.status, a.first_change#, a.next_change#
from v$log a, v$logfile b
where a.group# = b.group#; 2 3
GROUP# SEQUENCE# MEMBER MB STATUS FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- -------------------------------------------------- ---------- ---------------- ------------- ------------
3 28 /u01/app/oracle/oradata/ORA19C/redo03.log 50 CURRENT 2822872 1.8447E+19
2 27 /u01/app/oracle/oradata/ORA19C/redo02.log 50 INACTIVE 2797846 2822872
1 26 /u01/app/oracle/oradata/ORA19C/redo01.log 50 INACTIVE 2767903 2797846
SYS@ora19c> select name from v$controlfile;
NAME
--------------------------------------------------
/u01/app/oracle/oradata/ORA19C/control01.ctl
SYS@ora19c> create table hr.emp as select * from hr.employees where 1 = 2;
Table created.
SYS@ora19c> insert into hr.emp select * from hr.employees;
107 rows created.
SYS@ora19c> commit;
Commit complete.
SYS@ora19c> select count(*) from hr.emp;
COUNT(*)
----------
107
SYS@ora19c> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.장애유발
SYS@ora19c> ! ls /u01/app/oracle/oradata/ORA19C/control01.ctl
/u01/app/oracle/oradata/ORA19C/control01.ctl
SYS@ora19c> ! rm /u01/app/oracle/oradata/ORA19C/control01.ctl
SYS@ora19c> ! ls /u01/app/oracle/oradata/ORA19C/control01.ctl
ls: cannot access /u01/app/oracle/oradata/ORA19C/control01.ctl: No such file or directory컨트롤 파일 재생성 복구 진행, 백업 컨트롤 파일을 이용해서 trace file 생성한 후 재생성 진행
1. 백업 컨트롤 파일 restore
SYS@ora19c> !
[oracle@ora19c ~]$ cd backup/noarch
[oracle@ora19c noarch]$ ls control01.ctl
control01.ctl
[oracle@ora19c noarch]$ cp -v control01.ctl /u01/app/oracle/oradata/ORA19C/control01.ctl
‘control01.ctl’ -> ‘/u01/app/oracle/oradata/ORA19C/control01.ctl’
[oracle@ora19c noarch]$ exit
exit2. 백업 컨트롤 파일 이용해서 mount 시작
SYS@ora19c> startup mount
ORACLE instance started.
Total System Global Area 830469472 bytes
Fixed Size 8901984 bytes
Variable Size 595591168 bytes
Database Buffers 218103808 bytes
Redo Buffers 7872512 bytes
Database mounted.3. 컨트롤 파일 trace file 생성
SYS@ora19c> alter database backup controlfile to trace as '/home/oracle/create_control.sql';
Database altered.
SYS@ora19c> ! ls
backup create_control.sql LINUX.X64_193000_db_home.zip spfileora19c.ora userdata4. 데이터베이스 종료
SYS@ora19c> shutdown abort
ORACLE instance shut down.5. 컨트롤 파일 재생성
SYS@ora19c> ! ls
backup create_control.sql LINUX.X64_193000_db_home.zip spfileora19c.ora userdata
SYS@ora19c> ! cat create_control.sql
-- The following are current System-scope REDO Log Archival related
-- parameters and can be included in the database initialization file.
--
-- LOG_ARCHIVE_DEST=''
-- LOG_ARCHIVE_DUPLEX_DEST=''
--
-- LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf
--
-- DB_UNIQUE_NAME="ora19c"
--
-- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG'
-- LOG_ARCHIVE_MAX_PROCESSES=4
-- STANDBY_FILE_MANAGEMENT=MANUAL
-- FAL_CLIENT=''
-- FAL_SERVER=''
--
-- LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST'
-- LOG_ARCHIVE_DEST_1='MANDATORY REOPEN=300 NODELAY'
-- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM NOVERIFY SYNC'
-- LOG_ARCHIVE_DEST_1='NOREGISTER'
-- LOG_ARCHIVE_DEST_1='NOALTERNATE'
-- LOG_ARCHIVE_DEST_1='NODEPENDENCY'
-- LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'
-- LOG_ARCHIVE_DEST_1='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'
-- LOG_ARCHIVE_DEST_STATE_1=ENABLE
--
-- Below are two sets of SQL statements, each of which creates a new
-- control file and uses it to open the database. The first set opens
-- the database with the NORESETLOGS option and should be used only if
-- the current versions of all online logs are available. The second
-- set opens the database with the RESETLOGS option and should be used
-- if online logs are unavailable.
-- The appropriate set of statements can be copied from the trace into
-- a script file, edited as necessary, and executed when there is a
-- need to re-create the control file.
--
-- Set #1. NORESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- Additional logs may be required for media recovery of offline
-- Use this only if the current versions of all online logs are
-- available.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORA19C" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/ORA19C/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/oradata/ORA19C/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/oradata/ORA19C/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/ORA19C/system01.dbf',
'/u01/app/oracle/oradata/ORA19C/sysaux01.dbf',
'/u01/app/oracle/oradata/ORA19C/undotbs01.dbf',
'/u01/app/oracle/oradata/ORA19C/users01.dbf'
CHARACTER SET AL32UTF8
;
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/fast_recovery_area/ORA19C/archivelog/2025_12_18/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/fast_recovery_area/ORA19C/archivelog/2025_12_18/o1_mf_1_1_%u_.arc';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE
-- Database can now be opened normally.
ALTER DATABASE OPEN;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/ORA19C/temp01.dbf' REUSE;
-- End of tempfile additions.
--
-- Set #2. RESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- The contents of online logs will be lost and all backups will
-- be invalidated. Use this only if online logs are damaged.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORA19C" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/ORA19C/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/oradata/ORA19C/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/oradata/ORA19C/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/ORA19C/system01.dbf',
'/u01/app/oracle/oradata/ORA19C/sysaux01.dbf',
'/u01/app/oracle/oradata/ORA19C/undotbs01.dbf',
'/u01/app/oracle/oradata/ORA19C/users01.dbf'
CHARACTER SET AL32UTF8
;
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/fast_recovery_area/ORA19C/archivelog/2025_12_18/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/fast_recovery_area/ORA19C/archivelog/2025_12_18/o1_mf_1_1_%u_.arc';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE
-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/ORA19C/temp01.dbf' REUSE;
-- End of tempfile additions.
--# 첫번째 방식(NORESETLOGS)으로 복구 진행
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORA19C" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/ORA19C/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/oradata/ORA19C/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/oradata/ORA19C/redo03.log' SIZE 50M BLOCKSIZE 512
DATAFILE
'/u01/app/oracle/oradata/ORA19C/system01.dbf',
'/u01/app/oracle/oradata/ORA19C/sysaux01.dbf',
'/u01/app/oracle/oradata/ORA19C/undotbs01.dbf',
'/u01/app/oracle/oradata/ORA19C/users01.dbf'
CHARACTER SET AL32UTF8
;
ALTER DATABASE OPEN;
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/ORA19C/temp01.dbf' REUSE;MAXLOGFILES 16: 최대로 생성할 수 있는 리두 로그 그룹 수MAXLOGMEMBERS 3: 하나의 로그 그룹 당 가질 수 있는 최대 멤버 수MAXDATAFILES 100: 최대로 생성할 수 있는 데이터 파일 수MAXINSTANCES 8: RAC에서 최대 노드 수MAXLOGHISTORY 292: archivelolg mode에서 보유할 최대 로그 히스토리 수
'Courses > 아이티윌 오라클 DBA 과정' 카테고리의 다른 글
| 251217 TIL (0) | 2025.12.18 |
|---|---|
| 251216 TIL (0) | 2025.12.17 |
| 251215 TIL (0) | 2025.12.15 |
| 251212 TIL (0) | 2025.12.12 |
| 251211 TIL (0) | 2025.12.11 |