251219 TIL

2025. 12. 22. 21:28ㆍCourses/아이티윌 오라클 DBA 과정

컨트롤 파일 재생성이 필요할 때

Redo가 변경되었을 때
데이터 파일이 변경되었을 때
일자 별로 trace 뜨는 것이 좋음

불완전 복구가 필요할 때

redo 가 손상됐을 때
DROP TABLE 했을 때
TRUNCATE TABLE 했을 때

시나리오 17 : 데이터 파일, 리두로그 파일은 손상되지 않고 컨트롤 파일만 손상된 후 비정상 종료 발생 복구(백업 이후 리두 존재, 컨트롤 파일 재생성 복구)

현재 상태 확인

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> 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> 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 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                2846665   1.8447E+19
         2         30 /u01/app/oracle/oradata/ORA19C/redo02.log                  50 INACTIVE               2846661  2846665
         1         29 /u01/app/oracle/oradata/ORA19C/redo01.log                  50 INACTIVE               2846658  2846661

새로운 SYS 세션에서 오류 발생

SYS@ora19c> select * from v$database;
select * from v$database
              *
ERROR at line 1:
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

데이터베이스 비정상 종료

SYS@ora19c> shutdown abort
ORACLE instance shut down.

복구

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
exit

2. 백업 컨트롤 파일 이용해서 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. 컨트롤 파일 트레이스 파일 생성

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  userdata

4. 데이터베이스 종료

SYS@ora19c> shutdown abort
ORACLE instance shut down.

5. 컨트롤 파일 재생성

SYS@ora19c> STARTUP NOMOUNT
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
SYS@ora19c> 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
;  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17

Control file created.

SYS@ora19c> ALTER DATABASE OPEN;
ALTER DATABASE OPEN
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u01/app/oracle/oradata/ORA19C/system01.dbf'

# control file 손상 후에 데이터베이스가 비정상적인 종료가 발생했기 때문에 꼭 recover database 수행해야 함
SYS@ora19c> recover database
Media recovery complete.
SYS@ora19c> alter database open;

Database altered.

SYS@ora19c> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/ORA19C/temp01.dbf' REUSE;

Tablespace altered.

완전 복구

SYS@ora19c> select count(*) from hr.emp;

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

문제 : 데이터파일, 컨트롤 파일 손상되었을 경우 복구(데이터베이스 정상적인 종료, 백업 이후 리두 정보가 있음)

현재 checkpoint 조회

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 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 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> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@ora19c> ! ls /u01/app/oracle/oradata/ORA19C/*.{dbf,ctl}
/u01/app/oracle/oradata/ORA19C/control01.ctl  /u01/app/oracle/oradata/ORA19C/temp01.dbf
/u01/app/oracle/oradata/ORA19C/sysaux01.dbf   /u01/app/oracle/oradata/ORA19C/undotbs01.dbf
/u01/app/oracle/oradata/ORA19C/system01.dbf   /u01/app/oracle/oradata/ORA19C/users01.dbf

SYS@ora19c> ! rm /u01/app/oracle/oradata/ORA19C/*.{dbf,ctl}

SYS@ora19c> ! ls /u01/app/oracle/oradata/ORA19C/*.{dbf,ctl}
ls: cannot access /u01/app/oracle/oradata/ORA19C/*.dbf: No such file or directory
ls: cannot access /u01/app/oracle/oradata/ORA19C/*.ctl: No such file or directory

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

1. restore

SYS@ora19c> shutdown abort
ORACLE instance shut down.
SYS@ora19c> !
[oracle@ora19c ~]$ cd backup/noarch
[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
exit

2. 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. recover

SYS@ora19c> recover database 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_19/o1_mf_1_28_%u_.arc
ORA-00280: change 2845587 for thread 1 is in sequence #28

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

Database altered.

완전 복구(redo log는 reset)

SYS@ora19c> select count(*) from hr.emp;

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

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                2846912   1.8447E+19

복구 2

1. restore

SYS@ora19c> shutdown abort
ORACLE instance shut down.
SYS@ora19c> !
[oracle@ora19c ~]$ cd backup/noarch
[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
exit

2. 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. 컨트롤 파일 트레이스 파일 생성

SYS@ora19c> ! rm /home/oracle/create_control.sql

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  userdata

데이터베이스 종료

SYS@ora19c> shutdown abort
ORACLE instance shut down.

컨트롤 파일 재생성

SYS@ora19c> STARTUP NOMOUNT
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
SYS@ora19c> 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
;  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17

Control file created.

SYS@ora19c> recover database
Media recovery complete.

SYS@ora19c> ALTER DATABASE OPEN;

Database altered.

SYS@ora19c> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/ORA19C/temp01.dbf' REUSE;

Tablespace altered.

완전 복구(redo log 보존)

SYS@ora19c> select count(*) from hr.emp;

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

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 INACTIVE               2822872  2846894
         2         27 /u01/app/oracle/oradata/ORA19C/redo02.log                  50 INACTIVE               2797846  2822872
         1         29 /u01/app/oracle/oradata/ORA19C/redo01.log                  50 CURRENT                2846894   1.8447E+19

문제 : 데이터파일, 컨트롤 파일 손상되었을 경우 복구(데이터베이스 비정상적인 종료, 백업 이후 리두 정보가 없음)

현재 상태 조회

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 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 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> 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                2846678   1.8447E+19
         2         30 /u01/app/oracle/oradata/ORA19C/redo02.log                  50 INACTIVE               2846674  2846678
         1         29 /u01/app/oracle/oradata/ORA19C/redo01.log                  50 INACTIVE               2846671  2846674

장애 유발

SYS@ora19c> ! ls /u01/app/oracle/oradata/ORA19C/*.{dbf,ctl}
/u01/app/oracle/oradata/ORA19C/control01.ctl  /u01/app/oracle/oradata/ORA19C/temp01.dbf
/u01/app/oracle/oradata/ORA19C/sysaux01.dbf   /u01/app/oracle/oradata/ORA19C/undotbs01.dbf
/u01/app/oracle/oradata/ORA19C/system01.dbf   /u01/app/oracle/oradata/ORA19C/users01.dbf

SYS@ora19c> ! rm /u01/app/oracle/oradata/ORA19C/*.{dbf,ctl}

SYS@ora19c> ! ls /u01/app/oracle/oradata/ORA19C/*.{dbf,ctl}
ls: cannot access /u01/app/oracle/oradata/ORA19C/*.dbf: No such file or directory
ls: cannot access /u01/app/oracle/oradata/ORA19C/*.ctl: No such file or directory

새로운 SYS 세션에서 오류 발생

SYS@ora19c> select * from v$database;
select * from v$database
              *
ERROR at line 1:
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

-- alert log
2025-12-19T13:58:12.598754+09:00
Errors in file /u01/app/oracle/diag/rdbms/ora19c/ora19c/trace/ora19c_mz00_2102.trc:
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
2025-12-19T13:58:12.766869+09:00
Errors in file /u01/app/oracle/diag/rdbms/ora19c/ora19c/trace/ora19c_mz00_2102.trc:
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

데이터베이스 종료

SYS@ora19c> ! ls /u01/app/oracle/oradata/ORA19C/
recover  redo01.log  redo02.log  redo03.log

SYS@ora19c> shutdown abort
ORACLE instance shut down.

복구

1. restore

SYS@ora19c> !
[oracle@ora19c ~]$ cd backup/noarch
[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
exit

2. 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 recover

SYS@ora19c> recover database using backup controlfile until cancel
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_19/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 count(*) from hr.emp;
select count(*) from hr.emp
                        *
ERROR at line 1:
ORA-00942: table or view does not exist

시나리오 18 : 데이터베이스 정상적인 종료 후 redo log file, control file 손상되었을 경우 복구

현재 체크포인트 확인

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 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/*.{ctl,log}
/u01/app/oracle/oradata/ORA19C/control01.ctl  /u01/app/oracle/oradata/ORA19C/redo02.log
/u01/app/oracle/oradata/ORA19C/redo01.log     /u01/app/oracle/oradata/ORA19C/redo03.log

SYS@ora19c> ! rm /u01/app/oracle/oradata/ORA19C/*.{ctl,log}

SYS@ora19c> ! ls /u01/app/oracle/oradata/ORA19C/*.{ctl,log}
ls: cannot access /u01/app/oracle/oradata/ORA19C/*.ctl: No such file or directory
ls: cannot access /u01/app/oracle/oradata/ORA19C/*.log: No such file or directory

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

SYS@ora19c> select status from v$instance;

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

SYS@ora19c> ! ls /u01/app/oracle/oradata/ORA19C/
recover  sysaux01.dbf  system01.dbf  temp01.dbf  undotbs01.dbf  users01.dbf

복구

1. 백업 컨트롤 파일 restore

SYS@ora19c> shutdown abort
ORACLE instance shut down.
SYS@ora19c> !
[oracle@ora19c ~]$ cd backup/noarch
[oracle@ora19c noarch]$ cp -v control01.ctl /u01/app/oracle/oradata/ORA19C/
‘control01.ctl’ -> ‘/u01/app/oracle/oradata/ORA19C/control01.ctl’
[oracle@ora19c noarch]$ exit
exit

2. 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. 컨트롤 파일 트레이스 파일 생성

SYS@ora19c> alter database backup controlfile to trace as '/home/oracle/20251219.txt';

Database altered.

4. 데이터베이스 종료

SYS@ora19c> shutdown abort
ORACLE instance shut down.

5. 컨트롤 파일 재생성

SYS@ora19c> !
[oracle@ora19c ~]$ ls
20251219.txt  backup  create_control.sql  LINUX.X64_193000_db_home.zip  spfileora19c.ora  userdata
[oracle@ora19c ~]$ vi 20251219.txt

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

ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/ORA19C/temp01.dbf' REUSE;                         

[oracle@ora19c ~]$ exit
exit

SYS@ora19c> STARTUP NOMOUNT
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
SYS@ora19c> 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
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
;  7    8    9   10   11   12   13   14   15   16   17

Control file created.

SYS@ora19c> ALTER DATABASE OPEN RESETLOGS;

Database altered.

SYS@ora19c> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/ORA19C/temp01.dbf' REUSE;

Tablespace altered.

완전 복구(redo log는 reset)

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                2846983   1.8447E+19

SYS@ora19c> select count(*) from hr.emp;

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

시나리오 19 : redo log file, control file 손상, 데이터베이스는 비정상 종료

현재 체크포인트 확인

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 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 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> ! ls /u01/app/oracle/oradata/ORA19C/*.{ctl,log}
/u01/app/oracle/oradata/ORA19C/control01.ctl  /u01/app/oracle/oradata/ORA19C/redo02.log
/u01/app/oracle/oradata/ORA19C/redo01.log     /u01/app/oracle/oradata/ORA19C/redo03.log

SYS@ora19c> ! rm /u01/app/oracle/oradata/ORA19C/*.{ctl,log}

SYS@ora19c> ! ls /u01/app/oracle/oradata/ORA19C/*.{ctl,log}
ls: cannot access /u01/app/oracle/oradata/ORA19C/*.ctl: No such file or directory
ls: cannot access /u01/app/oracle/oradata/ORA19C/*.log: No such file or directory

# 리두 로그 파일 손상됐지만 로그 스위치가 발생됨
SYS@ora19c> alter system switch logfile;

System altered.

SYS@ora19c> /

System altered.

SYS@ora19c> /

System altered.

alert log

2025-12-19T15:12:01.289589+09:00
Errors in file /u01/app/oracle/diag/rdbms/ora19c/ora19c/trace/ora19c_lg00_8532.trc:
ORA-00313: open failed for members of log group 3 of thread 1
2025-12-19T15:12:01.347974+09:00
Errors in file /u01/app/oracle/diag/rdbms/ora19c/ora19c/trace/ora19c_mz00_9076.trc:
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
2025-12-19T15:12:01.535155+09:00
Errors in file /u01/app/oracle/diag/rdbms/ora19c/ora19c/trace/ora19c_mz00_9076.trc:
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
2025-12-19T15:12:04.416525+09:00
PMON (ospid: ): terminating the instance due to ORA error
2025-12-19T15:12:04.417277+09:00
Cause - 'Instance is being terminated due to fatal process death (pid: 20, ospid: 8532, LG00)'
2025-12-19T15:12:04.418185+09:00
System state dump requested by (instance=1, osid=8494 (PMON)), summary=[abnormal instance termination].
System State dumped to trace file /u01/app/oracle/diag/rdbms/ora19c/ora19c/trace/ora19c_diag_8513.trc
2025-12-19T15:12:04.980326+09:00
Dumping diagnostic data in directory=[cdmp_20251219151204], requested by (instance=1, osid=8494 (PMON)), summary=[abnormal instance termination].
2025-12-19T15:12:06.113839+09:00
Instance terminated by PMON, pid = 8494

데이터베이스 종료

SYS@ora19c> select status from v$instance;
select status from v$instance
*
ERROR at line 1:
ORA-03135: connection lost contact
Process ID: 8574
Session ID: 125 Serial number: 7801

SYS@ora19c> conn / as sysdba
Connected to an idle instance.
SYS@ora19c> shutdown abort
ORACLE instance shut down.

복구

1. 백업본 restore

SYS@ora19c> !
[oracle@ora19c ~]$ cd backup/noarch
[oracle@ora19c noarch]$ cp -v *.{dbf,ctl} /u01/app/oracle/oradata/ORA19C/
‘control01.ctl’ -> ‘/u01/app/oracle/oradata/ORA19C/control01.ctl’
‘sysaux01.dbf’ -> ‘/u01/app/oracle/oradata/ORA19C/sysaux01.dbf’
‘system01.dbf’ -> ‘/u01/app/oracle/oradata/ORA19C/system01.dbf’
‘temp01.dbf’ -> ‘/u01/app/oracle/oradata/ORA19C/temp01.dbf’
‘undotbs01.dbf’ -> ‘/u01/app/oracle/oradata/ORA19C/undotbs01.dbf’
‘users01.dbf’ -> ‘/u01/app/oracle/oradata/ORA19C/users01.dbf’
[oracle@ora19c noarch]$ exit
exit

2. 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 using backup controlfile until cancel
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_19/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#;

    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

불완전 복구

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

시나리오 20 : 백업 컨트롤 파일 내용과 현재 데이터 파일 정보가 틀린 경우 복구 방식

현재 체크포인트 확인

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 tablespace data_tbs datafile '/u01/app/oracle/oradata/ORA19C/data_tbs01.dbf' size 5m;

Tablespace created.

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
         5 DATA_TBS                       /u01/app/oracle/oradata/ORA19C/data_tbs01.dbf                 2846668 ONLINE

테이블 생성 및 데이터 입력

SYS@ora19c> create table hr.new(id number) tablespace data_tbs;

Table created.

SYS@ora19c> insert into hr.new(id) values(1);

1 row created.

SYS@ora19c> commit;

Commit complete.

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

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

SYS@ora19c> select status from v$instance;

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

SYS@ora19c> ! ls /u01/app/oracle/oradata/ORA19C/
data_tbs01.dbf  redo01.log  redo03.log    system01.dbf  undotbs01.dbf
recover         redo02.log  sysaux01.dbf  temp01.dbf    users01.dbf

복구

1. 컨트롤 파일 restore

SYS@ora19c> shutdown abort
ORACLE instance shut down.
SYS@ora19c> !
[oracle@ora19c ~]$ cd backup/noarch
[oracle@ora19c noarch]$ cp -v control01.ctl /u01/app/oracle/oradata/ORA19C/
‘control01.ctl’ -> ‘/u01/app/oracle/oradata/ORA19C/control01.ctl’
[oracle@ora19c noarch]$ exit
exit

2. 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  2822872

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                   2845587 SYSTEM
         3 SYSAUX                         /u01/app/oracle/oradata/ORA19C/sysaux01.dbf                   2845587 ONLINE
         4 UNDOTBS1                       /u01/app/oracle/oradata/ORA19C/undotbs01.dbf                  2845587 ONLINE
         7 USERS                          /u01/app/oracle/oradata/ORA19C/users01.dbf                    2845587 ONLINE

3. 백업 컨트롤 파일과 현재 데이터 파일 정보가 틀리기 때문에 복구 실패

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_19/o1_mf_1_28_%u_.arc
ORA-00280: change 2845587 for thread 1 is in sequence #28

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/ORA19C/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 5: '/u01/app/oracle/oradata/ORA19C/data_tbs01.dbf'

ORA-01112: media recovery not started

해결방법 : 컨트롤 파일 재생성

4. 컨트롤 파일 트레이스 파일 생성

SYS@ora19c> alter database backup controlfile to trace as '/home/oracle/new_control.txt';

Database altered.

SYS@ora19c> ! ls /home/oracle/new_control.txt
/home/oracle/new_control.txt

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/data_tbs01.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;

SYS@ora19c> STARTUP NOMOUNT
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
SYS@ora19c> 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/data_tbs01.dbf',
  '/u01/app/oracle/oradata/ORA19C/users01.dbf'
CHARACTER SET AL32UTF8
;  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18

Control file created.

SYS@ora19c> ALTER DATABASE OPEN;

Database altered.

SYS@ora19c> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/ORA19C/temp01.dbf' REUSE;

Tablespace 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                   2848055 SYSTEM
         3 SYSAUX                         /u01/app/oracle/oradata/ORA19C/sysaux01.dbf                   2848055 ONLINE
         4 UNDOTBS1                       /u01/app/oracle/oradata/ORA19C/undotbs01.dbf                  2848055 ONLINE
         5 DATA_TBS                       /u01/app/oracle/oradata/ORA19C/data_tbs01.dbf                 2848055 ONLINE
         7 USERS                          /u01/app/oracle/oradata/ORA19C/users01.dbf                    2848055 ONLINE

SYS@ora19c> select * from hr.new;

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

저작자표시 비영리 변경금지 (새창열림)

'Courses > 아이티윌 오라클 DBA 과정' 카테고리의 다른 글

251223 TIL (0)	2025.12.24
251222 TIL (0)	2025.12.22
251218 TIL (1)	2025.12.18
251217 TIL (0)	2025.12.18
251216 TIL (0)	2025.12.17

Joy's Devlog

태그

최근글

댓글

공지사항

아카이브

컨트롤 파일 재생성이 필요할 때

불완전 복구가 필요할 때

시나리오 17 : 데이터 파일, 리두로그 파일은 손상되지 않고 컨트롤 파일만 손상된 후 비정상 종료 발생 복구(백업 이후 리두 존재, 컨트롤 파일 재생성 복구)

현재 상태 확인

백업 이후 작업 수행

장애 유발

로그 스위치 발생

새로운 SYS 세션에서 오류 발생

데이터베이스 비정상 종료

복구

1. 백업 컨트롤 파일 restore

2. 백업 컨트롤 파일 이용해서 mount 시작

3. 컨트롤 파일 트레이스 파일 생성

4. 데이터베이스 종료

5. 컨트롤 파일 재생성

완전 복구

문제 : 데이터파일, 컨트롤 파일 손상되었을 경우 복구(데이터베이스 정상적인 종료, 백업 이후 리두 정보가 있음)

현재 checkpoint 조회

백업 이후 작업 수행

장애 유발

복구 1

1. restore

2. startup mount

3. recover

4. resetlogs 옵션으로 데이터베이스 open

완전 복구(redo log는 reset)

복구 2

1. restore

2. startup mount

3. 컨트롤 파일 트레이스 파일 생성

데이터베이스 종료

컨트롤 파일 재생성

완전 복구(redo log 보존)

문제 : 데이터파일, 컨트롤 파일 손상되었을 경우 복구(데이터베이스 비정상적인 종료, 백업 이후 리두 정보가 없음)

현재 상태 조회

백업 이후 작업 수행

로그 스위치 발생

장애 유발

새로운 SYS 세션에서 오류 발생

데이터베이스 종료

복구

1. restore

2. startup mount

3. cancel base recover

4. resetlogs 옵션으로 데이터베이스 open

불완전 복구

시나리오 18 : 데이터베이스 정상적인 종료 후 redo log file, control file 손상되었을 경우 복구

현재 체크포인트 확인

백업 이후 작업 수행

데이터베이스 정상 종료

장애 유발

복구

1. 백업 컨트롤 파일 restore

2. startup mount

3. 컨트롤 파일 트레이스 파일 생성

4. 데이터베이스 종료

5. 컨트롤 파일 재생성

완전 복구(redo log는 reset)

시나리오 19 : redo log file, control file 손상, 데이터베이스는 비정상 종료

현재 체크포인트 확인

백업 이후 작업 수행

장애 유발

alert log

데이터베이스 종료

복구

1. 백업본 restore

2. startup mount

3. cancel base recovery

4. resetlogs 옵션으로 데이터베이스 open

불완전 복구

시나리오 20 : 백업 컨트롤 파일 내용과 현재 데이터 파일 정보가 틀린 경우 복구 방식

현재 체크포인트 확인

백업 이후 작업 수행

새로운 테이블스페이스 생성