[oracle1@oracle ~]$ cd arch1
[oracle1@oracle arch1]$ ls
arch_1_1_1158542235.arc arch_1_3_1158542235.arc arch_1_5_1158542235.arc
arch_1_2_1158542235.arc arch_1_4_1158542235.arc arch_1_6_1158542235.arc
[oracle1@oracle arch1]$ rm *.*
[oracle1@oracle arch1]$ ls
SQL> select * from v$log;
SQL>
select a.file#, a.name file_name, b.name file_name, b.name tbs_name, a.status, a.checkpoint_change#
from v$datafile a, v$tablespace b
where a.ts# = b.ts#;
SQL> drop table hr.emp_50 purge;
SQL> create table hr.emp_50 as select * from hr.employees where department_id = 50;
SQL> select count(*) from hr.emp_50;
SQL> select * from v$log;
SQL> alter system switch logfile;
SQL> select * from v$log;
SQL>
SELECT a.group#, b.sequence#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
FROM v$logfile a, v$log b
WHERE a.group#=b.group#
ORDER BY 1;
=> trace 에서 확인
Thu Jan 18 02:13:53 2024
Archived Log entry 15 added for thread 1 sequence 3 ID 0xf7aeb36 dest 1:
Beginning log switch checkpoint up to RBA [0x5.2.10], SCN: 850630
Thread 1 advanced to log sequence 5 (LGWR switch)
Current log# 8 seq# 5 mem# 0: /u01/app/oracle/oradata/ora11g/redo08.log
Archived Log entry 16 added for thread 1 sequence 4 ID 0xf7aeb36 dest 1:
SQL> ! ls /home/oracle1/arch1
#) redo lof file, control file 위치 확인
SQL> ! ls /u01/app/oracle/oradata/ora11g/
SQL> ! ls /u01/app/oracle/oradata/ora11g/*.log
SQL> ! ls /u01/app/oracle/oradata/ora11g/control01.ctl
# 장애 유발 #
SQL> ! rm /u01/app/oracle/oradata/ora11g/*.log
SQL> ! rm /u01/app/oracle/oradata/ora11g/*.ctl
SQL> ! ls /u01/app/oracle/oradata/ora11g/*.log
ls: cannot access /u01/app/oracle/oradata/ora11g/*.log: No such file or directory
SQL> ! ls /u01/app/oracle/oradata/ora11g/*.ctl
ls: cannot access /u01/app/oracle/oradata/ora11g/*.ctl: No such file or directory
SQL> alter system switch logfile;
=> trace 확인
Thu Jan 18 02:17:12 2024
Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_m000_15171.trc:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/ora11g/control01.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
SQL> select status from v$instance;
<HR SESSION>
[oracle1@oracle ~]$ sqlplus hr/hr
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 18 01:05:37 2024
Copyright (c) 1982, 2013, Oracle. All rights reserved.
ERROR:
ORA-00604: error occurred at recursive SQL level 2
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/app/oracle/oradata/ora11g/system01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
Warning: You are no longer connected to ORACLE.
=> '장애 공지'
=> trace 에서 확인
Thu Jan 18 01:35:41 2024
Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_m000_14201.trc:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/ora11g/control01.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
SQL> shutdown abort
SQL> !
[oracle1@oracle cold_20240117]$ cp -av *.dbf /u01/app/oracle/oradata/ora11g/
‘example01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/example01.dbf’
‘sysaux01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/sysaux01.dbf’
‘system01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/system01.dbf’
‘temp01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/temp01.dbf’
‘undo01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/undo01.dbf’
‘users01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/users01.dbf’
[oracle1@oracle cold_20240117]$ cp -av *.ctl /u01/app/oracle/oradata/ora11g/
‘control01.ctl’ -> ‘/u01/app/oracle/oradata/ora11g/control01.ctl’
[oracle1@oracle cold_20240117]$ sqlplus / as sysdba
SQL> startup mount
ORACLE instance started.
Total System Global Area 711430144 bytes
Fixed Size 1367004 bytes
Variable Size 440402980 bytes
Database Buffers 264241152 bytes
Redo Buffers 5419008 bytes
Database mounted.
# recover 시도
SQL> recover database until cancel using backup controlfile
ORA-00279: change 849668 generated at 01/18/2024 01:57:11 needed for thread 1
ORA-00289: suggestion : /home/oracle1/arch1/arch_1_1_1158543774.arc
ORA-00280: change 849668 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO (입력)
ORA-00279: change 850615 generated at 01/18/2024 02:13:47 needed for thread 1
ORA-00289: suggestion : /home/oracle1/arch1/arch_1_2_1158543774.arc
ORA-00280: change 850615 for thread 1 is in sequence #2
ORA-00278: log file '/home/oracle1/arch1/arch_1_1_1158543774.arc' no longer
needed for this recovery
ORA-00279: change 850618 generated at 01/18/2024 02:13:49 needed for thread 1
ORA-00289: suggestion : /home/oracle1/arch1/arch_1_3_1158543774.arc
ORA-00280: change 850618 for thread 1 is in sequence #3
ORA-00278: log file '/home/oracle1/arch1/arch_1_2_1158543774.arc' no longer
needed for this recovery
ORA-00279: change 850624 generated at 01/18/2024 02:13:53 needed for thread 1
ORA-00289: suggestion : /home/oracle1/arch1/arch_1_4_1158543774.arc
ORA-00280: change 850624 for thread 1 is in sequence #4
ORA-00278: log file '/home/oracle1/arch1/arch_1_3_1158543774.arc' no longer
needed for this recovery
ORA-00279: change 850630 generated at 01/18/2024 02:13:57 needed for thread 1
ORA-00289: suggestion : /home/oracle1/arch1/arch_1_5_1158543774.arc
ORA-00280: change 850630 for thread 1 is in sequence #5
ORA-00278: log file '/home/oracle1/arch1/arch_1_4_1158543774.arc' no longer
needed for this recovery
ORA-00308: cannot open archived log
'/home/oracle1/arch1/arch_1_5_1158543774.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
# recover 다시 시도
SQL> recover database until cancel using backup controlfile
ORA-00279: change 850630 generated at 01/18/2024 02:13:57 needed for thread 1
ORA-00289: suggestion : /home/oracle1/arch1/arch_1_5_1158543774.arc
ORA-00280: change 850630 for thread 1 is in sequence #5
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel (입력)
Media recovery cancelled.
SQL> alter database open resetlogs;
SQL> select count(*) from hr.emp_50;
# redo 정보 확인
SQL> select * from v$log;
'Backup > Archive Log Mode' 카테고리의 다른 글
모든 data file, redo log file, control file 손상되었을 경우 (0) | 2024.01.19 |
---|---|
undo Data file 장애 발생했을 경우 (0) | 2024.01.18 |
system data file, control file 손상되었을 경우 (0) | 2024.01.18 |
data file, control file 손상되었을 경우 (0) | 2024.01.18 |
data file, redo log file 손상되지 않고 control file 손상되었을 경우 (0) | 2024.01.18 |