Courses/아이티윌 오라클 DBA 과정
251222 TIL
DevJoy
2025. 12. 22. 21:33
시나리오 21 : 데이터베이스 정상적인 종료 후 inactive log 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> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.장애 유발
SYS@ora19c> SYS@ora19c> ! rm /u01/app/oracle/oradata/ORA19C/redo02.log
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-03113: end-of-file on communication channel
Process ID: 26950
Session ID: 125 Serial number: 44224startup mount
SYS@ora19c> conn / as sysdba
Connected to an idle instance.
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> ! ls /u01/app/oracle/oradata/ORA19C/redo02.log
ls: cannot access /u01/app/oracle/oradata/ORA19C/redo02.log: No such file or directory
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문제가 발생한 inactive 상태 redo log group을 삭제
SYS@ora19c> alter database drop logfile group 2;
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 28 /u01/app/oracle/oradata/ORA19C/redo03.log 50 CURRENT 2822872 1.8447E+19
1 26 /u01/app/oracle/oradata/ORA19C/redo01.log 50 INACTIVE 2767903 2797846
SYS@ora19c> alter database open;
Database altered.
리두 로그 그룹 추가
SYS@ora19c> alter database add logfile group 2 '/u01/app/oracle/oradata/ORA19C/redo02.log' size 50m;
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 28 /u01/app/oracle/oradata/ORA19C/redo03.log 50 CURRENT 2822872 1.8447E+19
2 0 /u01/app/oracle/oradata/ORA19C/redo02.log 50 UNUSED 0 0
1 26 /u01/app/oracle/oradata/ORA19C/redo01.log 50 INACTIVE 2767903 2797846시나리오 22 : inactive log 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> ! ls /u01/app/oracle/oradata/ORA19C/redo02.log
/u01/app/oracle/oradata/ORA19C/redo02.log
SYS@ora19c> ! rm /u01/app/oracle/oradata/ORA19C/redo02.log
SYS@ora19c> ! ls /u01/app/oracle/oradata/ORA19C/redo02.log
ls: cannot access /u01/app/oracle/oradata/ORA19C/redo02.log: No such file or directory
# 로그 스위치 발생
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 2846815
2 27 /u01/app/oracle/oradata/ORA19C/redo02.log 50 INACTIVE 2797846 2822872
1 29 /u01/app/oracle/oradata/ORA19C/redo01.log 50 CURRENT 2846815 1.8447E+19
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
select a.group#, a.sequence#, b.member, a.bytes/1024/1024 mb, a.status, a.first_change#, a.next_change#
*
ERROR at line 1:
ORA-03135: connection lost contact
Process ID: 28138
Session ID: 125 Serial number: 35988alert log
2025-12-22T10:21:50.981179+09:00
Errors in file /u01/app/oracle/diag/rdbms/ora19c/ora19c/trace/ora19c_mz00_28851.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/ORA19C/redo02.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
Checker run found 2 new persistent data failures손상된 로그 그룹 삭제 시도(실패)
SYS@ora19c> conn / as sysdba
Connected to an idle instance.
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 29 /u01/app/oracle/oradata/ORA19C/redo01.log 50 ACTIVE 2846815 2846864
3 28 /u01/app/oracle/oradata/ORA19C/redo03.log 50 INACTIVE 2822872 2846815
2 30 /u01/app/oracle/oradata/ORA19C/redo02.log 50 CURRENT 2846864 1.8447E+19
# current 로그 그룹은 삭제 불가
SYS@ora19c> alter database drop logfile group 2;
alter database drop logfile group 2
*
ERROR at line 1:
ORA-01623: log 2 is current log for instance ora19c (thread 1) - cannot drop
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/ORA19C/redo02.log'
# current redo log group 삭제 방법으로 수행했지만 오류 발생
SYS@ora19c> alter database clear logfile group 2;
alter database clear logfile group 2
*
ERROR at line 1:
ORA-01624: log 2 needed for crash recovery of instance ora19c (thread 1)
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/ORA19C/redo02.log'모든 백업 데이터 파일 restore
SYS@ora19c> !
[oracle@ora19c ~]$ cd backup/noarch
[oracle@ora19c noarch]$ cp -v *.dbf /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’
[oracle@ora19c noarch]$ exit
exitcancel base recover
SYS@ora19c> recover database 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_22/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-00279: change 2846815 generated at 12/22/2025 10:19:47 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/fast_recovery_area/ORA19C/archivelog/2025_12_22/o1_mf_1_29_%u_.arc
ORA-00280: change 2846815 for thread 1 is in sequence #29
ORA-00278: log file '/u01/app/oracle/oradata/ORA19C/redo03.log' no longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/ORA19C/redo01.log
ORA-00279: change 2846864 generated at 12/22/2025 10:21:41 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/fast_recovery_area/ORA19C/archivelog/2025_12_22/o1_mf_1_30_%u_.arc
ORA-00280: change 2846864 for thread 1 is in sequence #30
ORA-00278: log file '/u01/app/oracle/oradata/ORA19C/redo01.log' no longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.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 2846865 1.8447E+19시나리오 23 : current redo log 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
장애 유발(current redo log file 삭제)
SYS@ora19c> ! ls /u01/app/oracle/oradata/ORA19C/redo03.log
/u01/app/oracle/oradata/ORA19C/redo03.log
SYS@ora19c> ! rm /u01/app/oracle/oradata/ORA19C/redo03.log
SYS@ora19c> ! ls /u01/app/oracle/oradata/ORA19C/redo03.log
ls: cannot access /u01/app/oracle/oradata/ORA19C/redo03.log: No such file or directory
SYS@ora19c> alter database clear logfile group 3;
alter database clear logfile group 3
*
ERROR at line 1:
ORA-01624: log 3 needed for crash recovery of instance ora19c (thread 1)
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/ORA19C/redo03.log'데이터베이스 정상 종료 후 mount로 startup
SYS@ora19c> shutdown immediate
Database closed.
Database dismounted.
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.
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문제가 발생한 current redo log file 삭제 후 재생성
SYS@ora19c> alter database clear logfile group 3;
Database altered.데이터베이스 open
SYS@ora19c> alter database open;
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 2822872 2847903
2 27 /u01/app/oracle/oradata/ORA19C/redo02.log 50 INACTIVE 2797846 2822872
1 29 /u01/app/oracle/oradata/ORA19C/redo01.log 50 CURRENT 2847903 1.8447E+19- 로그 파일 시퀀스에 갭이 생겼으므로 close whole backup을 받아놔야 함
시나리오 24 : current redo log 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> ! ls /u01/app/oracle/oradata/ORA19C/redo03.log
/u01/app/oracle/oradata/ORA19C/redo03.log
SYS@ora19c> ! rm /u01/app/oracle/oradata/ORA19C/redo03.log
SYS@ora19c> ! ls /u01/app/oracle/oradata/ORA19C/redo03.log
ls: cannot access /u01/app/oracle/oradata/ORA19C/redo03.log: No such file or directory데이터베이스 비정상 종료
SYS@ora19c> shutdown abort
ORACLE instance shut down.
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-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/ORA19C/redo03.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
SYS@ora19c> select status from v$instance;
STATUS
------------
MOUNTED손상된 current redo log file 삭제 및 재생성 시도(실패)
SYS@ora19c> alter database clear logfile group 3;
alter database clear logfile group 3
*
ERROR at line 1:
ORA-01624: log 3 needed for crash recovery of instance ora19c (thread 1)
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/ORA19C/redo03.log'백업 받은 모든 데이터파일 restore
SYS@ora19c> !
[oracle@ora19c ~]$ cd backup/noarch
[oracle@ora19c noarch]$ cp -v *.dbf /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’
[oracle@ora19c noarch]$ exit
exitcancel base recover
SYS@ora19c> recover database 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_22/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.resetlogs로 데이터베이스 open
SYS@ora19c> alter database open resetlogs;
Database altered.시나리오 25 : 디스크 장애가 발생해서 데이터파일, 컨트롤파일, 리두로그파일 손상되었을 경우 복구(백업은 데이터파일, 컨트롤파일, 리두로그파일)
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 2797846SYS@ora19c> select name from v$controlfile;
NAME
--------------------------------------------------
/u01/app/oracle/oradata/ORA19C/control01.ctl
SYS@ora19c> select name from v$datafile;
NAME
--------------------------------------------------
/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
SYS@ora19c> select name from v$tempfile;
NAME
--------------------------------------------------
/u01/app/oracle/oradata/ORA19C/temp01.dbf
SYS@ora19c> select member from v$logfile;
MEMBER
--------------------------------------------------
/u01/app/oracle/oradata/ORA19C/redo03.log
/u01/app/oracle/oradata/ORA19C/redo02.log
/u01/app/oracle/oradata/ORA19C/redo01.log장애 유발
SYS@ora19c> ! ls /u01/app/oracle/oradata/ORA19C/
control01.ctl redo01.log redo03.log system01.dbf undotbs01.dbf
recover redo02.log sysaux01.dbf temp01.dbf users01.dbf
SYS@ora19c> ! rm /u01/app/oracle/oradata/ORA19C/*
SYS@ora19c> ! ls /u01/app/oracle/oradata/ORA19C/
SYS@ora19c> alter system checkpoint;
alter system checkpoint
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 11998
Session ID: 125 Serial number: 53143
SYS@ora19c> conn / as sysdba
Connected to an idle instance.새로운 디렉터리 생성
[oracle@ora19c ~]$ mkdir oradata
[oracle@ora19c ~]$ cd oradata/
[oracle@ora19c oradata]$ pwd
/home/oracle/oradata모든 백업 컨트롤파일, 데이터파일, 리두로그파일을 새로운 디렉터리로 복사
[oracle@ora19c oradata]$ cd ~/backup/noarch
[oracle@ora19c noarch]$ cp -v *.{dbf,ctl,log} /home/oracle/oradata
‘sysaux01.dbf’ -> ‘/home/oracle/oradata/sysaux01.dbf’
‘system01.dbf’ -> ‘/home/oracle/oradata/system01.dbf’
‘temp01.dbf’ -> ‘/home/oracle/oradata/temp01.dbf’
‘undotbs01.dbf’ -> ‘/home/oracle/oradata/undotbs01.dbf’
‘users01.dbf’ -> ‘/home/oracle/oradata/users01.dbf’
‘control01.ctl’ -> ‘/home/oracle/oradata/control01.ctl’
‘redo01.log’ -> ‘/home/oracle/oradata/redo01.log’
‘redo02.log’ -> ‘/home/oracle/oradata/redo02.log’
‘redo03.log’ -> ‘/home/oracle/oradata/redo03.log’초기 파라미터 파일에서 control_file 파라미터 수정
[oracle@ora19c noarch]$ cd $ORACLE_HOME/dbs
[oracle@ora19c dbs]$ ls
hc_ora19c.dat init.ora initora19c.ora lkORA19C orapwora19c spfileora19c.bak spfileora19c.ora
[oracle@ora19c dbs]$ vi initora19c.ora
[oracle@ora19c dbs]$ cat initora19c.ora
...
*.control_files='/u01/app/oracle/oradata/ORA19C/control01.ctl'
...
[oracle@ora19c dbs]$ vi initora19c.ora
[oracle@ora19c dbs]$ cat initora19c.ora
...
*.control_files='/home/oracle/oradata/control01.ctl'
...pfile로 startup mount
SYS@ora19c> startup pfile=$ORACLE_HOME/dbs/initora19c.ora 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 name from v$controlfile;
NAME
--------------------------------------------------
/home/oracle/oradata/control01.ctl
SYS@ora19c> select name from v$datafile;
NAME
--------------------------------------------------
/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
SYS@ora19c> select name from v$tempfile;
NAME
--------------------------------------------------
/u01/app/oracle/oradata/ORA19C/temp01.dbf
SYS@ora19c> select member from v$logfile;
MEMBER
--------------------------------------------------
/u01/app/oracle/oradata/ORA19C/redo03.log
/u01/app/oracle/oradata/ORA19C/redo02.log
/u01/app/oracle/oradata/ORA19C/redo01.logrename
alter database rename file '/u01/app/oracle/oradata/ORA19C/system01.dbf' to '/home/oracle/oradata/system01.dbf';
alter database rename file '/u01/app/oracle/oradata/ORA19C/sysaux01.dbf' to '/home/oracle/oradata/sysaux01.dbf';
alter database rename file '/u01/app/oracle/oradata/ORA19C/undotbs01.dbf' to '/home/oracle/oradata/undotbs01.dbf';
alter database rename file '/u01/app/oracle/oradata/ORA19C/users01.dbf' to '/home/oracle/oradata/users01.dbf';
alter database rename file '/u01/app/oracle/oradata/ORA19C/temp01.dbf' to '/home/oracle/oradata/temp01.dbf';
alter database rename file '/u01/app/oracle/oradata/ORA19C/redo01.log' to '/home/oracle/oradata/redo01.log';
alter database rename file '/u01/app/oracle/oradata/ORA19C/redo02.log' to '/home/oracle/oradata/redo02.log';
alter database rename file '/u01/app/oracle/oradata/ORA19C/redo03.log' to '/home/oracle/oradata/redo03.log';SYS@ora19c> select name from v$datafile;
NAME
--------------------------------------------------
/home/oracle/oradata/system01.dbf
/home/oracle/oradata/sysaux01.dbf
/home/oracle/oradata/undotbs01.dbf
/home/oracle/oradata/users01.dbf
SYS@ora19c> select name from v$tempfile;
NAME
--------------------------------------------------
/home/oracle/oradata/temp01.dbf
SYS@ora19c> select member from v$logfile;
MEMBER
--------------------------------------------------
/home/oracle/oradata/redo03.log
/home/oracle/oradata/redo02.log
/home/oracle/oradata/redo01.log데이터베이스 open
SYS@ora19c> alter database open;
Database altered.시나리오 26 : 디스크 장애가 발생해서 데이터파일, 컨트롤파일, 리두로그파일 손상되었을 경우 복구(백업은 데이터파일, 컨트롤파일)
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> select name from v$controlfile;
NAME
--------------------------------------------------
/u01/app/oracle/oradata/ORA19C/control01.ctl
SYS@ora19c> select name from v$datafile;
NAME
--------------------------------------------------
/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
SYS@ora19c> select name from v$tempfile;
NAME
--------------------------------------------------
/u01/app/oracle/oradata/ORA19C/temp01.dbf
SYS@ora19c> select member from v$logfile;
MEMBER
--------------------------------------------------
/u01/app/oracle/oradata/ORA19C/redo03.log
/u01/app/oracle/oradata/ORA19C/redo02.log
/u01/app/oracle/oradata/ORA19C/redo01.log장애 유발
SYS@ora19c> ! ls /u01/app/oracle/oradata/ORA19C/
control01.ctl redo01.log redo03.log system01.dbf undotbs01.dbf
recover redo02.log sysaux01.dbf temp01.dbf users01.dbf
SYS@ora19c> ! rm /u01/app/oracle/oradata/ORA19C/*
SYS@ora19c> ! ls /u01/app/oracle/oradata/ORA19C/
SYS@ora19c> alter system checkpoint;
alter system checkpoint
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 14429
Session ID: 125 Serial number: 450
SYS@ora19c> conn / as sysdba
Connected to an idle instance.새로운 디렉터리 생성
[oracle@ora19c ~]$ ls
20251219.txt create_control.sql new_control2.txt spfileora19c.ora
backup LINUX.X64_193000_db_home.zip new_control.txt userdata
[oracle@ora19c ~]$ mkdir oradata
[oracle@ora19c ~]$ ls
20251219.txt create_control.sql new_control2.txt oradata userdata
backup LINUX.X64_193000_db_home.zip new_control.txt spfileora19c.orarestore
[oracle@ora19c ~]$ cd backup/noarch
[oracle@ora19c noarch]$ cp -v *.{dbf,ctl} /home/oracle/oradata
‘sysaux01.dbf’ -> ‘/home/oracle/oradata/sysaux01.dbf’
‘system01.dbf’ -> ‘/home/oracle/oradata/system01.dbf’
‘temp01.dbf’ -> ‘/home/oracle/oradata/temp01.dbf’
‘undotbs01.dbf’ -> ‘/home/oracle/oradata/undotbs01.dbf’
‘users01.dbf’ -> ‘/home/oracle/oradata/users01.dbf’
‘control01.ctl’ -> ‘/home/oracle/oradata/control01.ctl’초기 파라미터 파일에서 control_file 파라미터 수정
[oracle@ora19c noarch]$ cd $ORACLE_HOME/dbs
[oracle@ora19c dbs]$ ls
hc_ora19c.dat init.ora initora19c.ora lkORA19C orapwora19c spfileora19c.bak spfileora19c.ora
[oracle@ora19c dbs]$ vi initora19c.ora
[oracle@ora19c dbs]$ cat initora19c.ora
...
*.control_files='/u01/app/oracle/oradata/ORA19C/control01.ctl'
...
[oracle@ora19c dbs]$ vi initora19c.ora
[oracle@ora19c dbs]$ cat initora19c.ora
...
*.control_files='/home/oracle/oradata/control01.ctl'
...pfile로 startup mount
SYS@ora19c> startup pfile=$ORACLE_HOME/dbs/initora19c.ora 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 name from v$controlfile;
NAME
--------------------------------------------------
/home/oracle/oradata/control01.ctl
SYS@ora19c> select name from v$datafile;
NAME
--------------------------------------------------
/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
SYS@ora19c> select name from v$tempfile;
NAME
--------------------------------------------------
/u01/app/oracle/oradata/ORA19C/temp01.dbf
SYS@ora19c> select member from v$logfile;
MEMBER
--------------------------------------------------
/u01/app/oracle/oradata/ORA19C/redo03.log
/u01/app/oracle/oradata/ORA19C/redo02.log
/u01/app/oracle/oradata/ORA19C/redo01.logrename
alter database rename file '/u01/app/oracle/oradata/ORA19C/system01.dbf' to '/home/oracle/oradata/system01.dbf';
alter database rename file '/u01/app/oracle/oradata/ORA19C/sysaux01.dbf' to '/home/oracle/oradata/sysaux01.dbf';
alter database rename file '/u01/app/oracle/oradata/ORA19C/undotbs01.dbf' to '/home/oracle/oradata/undotbs01.dbf';
alter database rename file '/u01/app/oracle/oradata/ORA19C/users01.dbf' to '/home/oracle/oradata/users01.dbf';
alter database rename file '/u01/app/oracle/oradata/ORA19C/temp01.dbf' to '/home/oracle/oradata/temp01.dbf';redo는 물리적인 파일이 없으므로 rename 불가
SYS@ora19c> alter database rename file '/u01/app/oracle/oradata/ORA19C/redo03.log' to '/home/oracle/oradata/redo03.log'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01512: error renaming log file /u01/app/oracle/oradata/ORA19C/redo03.log - new file /home/oracle/oradata/redo03.log not found
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
SYS@ora19c> alter database rename file '/u01/app/oracle/oradata/ORA19C/redo02.log' to '/home/oracle/oradata/redo02.log'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01512: error renaming log file /u01/app/oracle/oradata/ORA19C/redo02.log - new file /home/oracle/oradata/redo02.log not found
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
SYS@ora19c>
alter database rename file '/u01/app/oracle/oradata/ORA19C/redo01.log' to '/home/oracle/oradata/redo01.log'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01512: error renaming log file /u01/app/oracle/oradata/ORA19C/redo01.log - new file /home/oracle/oradata/redo01.log not found
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
SYS@ora19c> select name from v$datafile;
NAME
--------------------------------------------------
/home/oracle/oradata/system01.dbf
/home/oracle/oradata/sysaux01.dbf
/home/oracle/oradata/undotbs01.dbf
/home/oracle/oradata/users01.dbf
SYS@ora19c> select name from v$tempfile;
NAME
--------------------------------------------------
/home/oracle/oradata/temp01.dbf
SYS@ora19c> select member from v$logfile;
MEMBER
--------------------------------------------------
/home/oracle/oradata/redo03.log
/home/oracle/oradata/redo02.log
/home/oracle/oradata/redo01.log백업 컨트롤파일 트레이스 파일 생성
SYS@ora19c> alter database backup controlfile to trace as '/home/oracle/create_controlfile.txt';
Database altered.
SYS@ora19c> shutdown abort
ORACLE instance shut down.
SYS@ora19c> !
[oracle@ora19c ~]$ vi create_controlfile.txt컨트롤 파일 재생성
- 로그 파일 위치 수정
STARTUP pfile=$ORACLE_HOME/dbs/initora19c.ora NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORA19C" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/home/oracle/oradata/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/home/oracle/oradata/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/home/oracle/oradata/redo03.log' SIZE 50M BLOCKSIZE 512
DATAFILE
'/home/oracle/oradata/system01.dbf',
'/home/oracle/oradata/sysaux01.dbf',
'/home/oracle/oradata/undotbs01.dbf',
'/home/oracle/oradata/users01.dbf'
CHARACTER SET AL32UTF8
;
ALTER DATABASE OPEN RESETLOGS;
ALTER TABLESPACE TEMP ADD TEMPFILE '/home/oracle/oradata/temp01.dbf' REUSE; SYS@ora19c> select count(*) from hr.employees;
COUNT(*)
----------
107