2023/01/10
■ 장애, 백업,복구 시나리오 - noarchive log mode
▶ 1. 백업 이후에 redo정보가 있을 경우 시나리오 (특정한 데이터 파일이 손상)
● 1. backup할것 위치 확인
SELECT tablespace_name, file_name, bytes, status
FROM dba_data_files;
SELECT tablespace_name, file_name, bytes, status
FROM dba_temp_files;● 2. db shutdown
- 이유
: 데이터베이스를 백업할 때 데이터베이스를 내리는 이유는 백업 중에 데이터의 일관성과 정합성을 유지하기 위함입니다. 데이터베이스를 온라인 상태에서 백업하는 경우, 데이터베이스는 계속해서 변경되고 새로운 트랜잭션이 발생할 수 있습니다. 이러한 상태에서 백업을 수행하면 백업 파일에 포함된 데이터가 중간에 변경되거나 불일치할 수 있습니다
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.● 3. 원하는 위치에 현재 datafile, tempfile, controlfile, redologfile 복사
[oracle@oracle ~]$ mkdir -p backup/noarch/
[oracle@oracle ~]$ cd backup/noarch/
[oracle@oracle noarch]$ cd /u01/app/oracle/oradata/ora11g/
[oracle@oracle ora11g]$ ls
control01.ctl redo01.log redo03.log system01.dbf undotbs01.dbf
example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf
[oracle@oracle ora11g]$ date
Tue Jan 9 10:31:14 KST 2024
[oracle@oracle ora11g]$ cp -av *.* /home/oracle/backup/noarch
‘control01.ctl’ -> ‘/home/oracle/backup/noarch/control01.ctl’
‘example01.dbf’ -> ‘/home/oracle/backup/noarch/example01.dbf’
‘redo01.log’ -> ‘/home/oracle/backup/noarch/redo01.log’
‘redo02.log’ -> ‘/home/oracle/backup/noarch/redo02.log’
‘redo03.log’ -> ‘/home/oracle/backup/noarch/redo03.log’
‘sysaux01.dbf’ -> ‘/home/oracle/backup/noarch/sysaux01.dbf’
‘system01.dbf’ -> ‘/home/oracle/backup/noarch/system01.dbf’
‘temp01.dbf’ -> ‘/home/oracle/backup/noarch/temp01.dbf’
‘undotbs01.dbf’ -> ‘/home/oracle/backup/noarch/undotbs01.dbf’
‘users01.dbf’ -> ‘/home/oracle/backup/noarch/users01.dbf’
[oracle@oracle noarch]$ pwd
/home/oracle/backup/noarch
[oracle@oracle noarch]$ ls
control01.ctl redo01.log redo03.log system01.dbf undotbs01.dbf
example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf
select * from v$log;[hr session]
create table new(id number) tablespace example;
insert into new(id) values(1);
commit;
select * from new;[sys session]
select f.file_name
from dba_extents e, dba_data_files f
where e.file_id = f.file_id
and e.segment_name = 'NEW'
and e.owner = 'HR';
FILE_NAME
----------
/u01/app/oracle/oradata/ora11g/example01.dbf● 4. 장애 유발
- db shutdown
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !- 물리적인 example 테이블스페이스 rm
[oracle@oracle ~]$ rm /u01/app/oracle/oradata/ora11g/example01.dbf
[oracle@oracle ~]$ ls /u01/app/oracle/oradata/ora11g/example01.dbf
ls: cannot access /u01/app/oracle/oradata/ora11g/example01.dbf: No such file or directory
[oracle@oracle ~]$ exit
exit- db startup , example테이블스페이스가 없어서 mount까지만 된다. (open에서 장애)
SQL> startup
ORACLE instance started.
Total System Global Area 531476480 bytes
Fixed Size 1365796 bytes
Variable Size 201328860 bytes
Database Buffers 322961408 bytes
Redo Buffers 5820416 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/u01/app/oracle/oradata/ora11g/example01.dbf'● 5. backup 파일 restore
[oracle@oracle noarch]$ pwd
/home/oracle/backup/noarch
[oracle@oracle noarch]$ ls example01.dbf
example01.dbf
[oracle@oracle noarch]$ cp -av example01.dbf /u01/app/oracle/oradata/ora11g/example01.dbf
‘example01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/example01.dbf’● 6. recover
- recover
: 마지막 백업시점까지
리두 정보를 가지고 recover
- tablespace 이름으로 recover
SQL> recover tablespace example;
Media recovery complete.- 그냥 db recover
recover database;● 7. db open
SQL> alter database open;
Database altered.
▶ 2. 백업 이후에 redo정보가 없을 경우 시나리오 (특정한 데이터 파일이 손상)
★ noarchive 모드에서 redo 가 살아있지 않을때
: 로그스위치로 인하여 redo 정보가 살아있지않고 gap이 있을경우 불완전한 복구가 된다.
따라서 datafile,tempfile,controlfile,redologfile 전체를 백업하여 백업한시점으로 돌아가야한다 -> 불완전복구
● 1. db shutdown
- SCN 번호 확인
select current_scn from v$database;
select name, checkpoint_change# from v$datafile;
select * from v$log;
---
/u01/app/oracle/oradata/ora11g/system01.dbf 1448669
/u01/app/oracle/oradata/ora11g/sysaux01.dbf 1448669
/u01/app/oracle/oradata/ora11g/users01.dbf 1448669
/u01/app/oracle/oradata/ora11g/example01.dbf 1448669
/u01/app/oracle/oradata/ora11g/undotbs01.dbf 1448669
---- shutdown
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !● 2. backup 파일 restore
[oracle@oracle ~]$ cd /home/oracle/backup/noarch/
[oracle@oracle noarch]$ ls
control01.ctl redo01.log redo03.log system01.dbf undotbs01.dbf
example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf
[oracle@oracle noarch]$ cp -av *.* /u01/app/oracle/oradata/ora11g/
‘control01.ctl’ -> ‘/u01/app/oracle/oradata/ora11g/control01.ctl’
‘example01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/example01.dbf’
‘redo01.log’ -> ‘/u01/app/oracle/oradata/ora11g/redo01.log’
‘redo02.log’ -> ‘/u01/app/oracle/oradata/ora11g/redo02.log’
‘redo03.log’ -> ‘/u01/app/oracle/oradata/ora11g/redo03.log’
‘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’
‘undotbs01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/undotbs01.dbf’
‘users01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/users01.dbf’
[oracle@oracle noarch]$ exit
exit● 3. db start
SQL> startup
ORACLE instance started.
Total System Global Area 531476480 bytes
Fixed Size 1365796 bytes
Variable Size 201328860 bytes
Database Buffers 322961408 bytes
Redo Buffers 5820416 bytes
Database mounted.
Database opened.- SCN 번호 확인
select current_scn from v$database;
select name, checkpoint_change# from v$datafile;
select * from v$log;
---
/u01/app/oracle/oradata/ora11g/system01.dbf 1446401
/u01/app/oracle/oradata/ora11g/sysaux01.dbf 1446401
/u01/app/oracle/oradata/ora11g/users01.dbf 1446401
/u01/app/oracle/oradata/ora11g/example01.dbf 1446401
/u01/app/oracle/oradata/ora11g/undotbs01.dbf 1446401
---● 4 . hr에서 작업
[hr session]
- new 테이블 만들기 전으로 돌아갔으니 테이블이 없다
select * from new;
ORA-00942: 테이블 또는 뷰가 존재하지 않습니다
00942. 00000 - "table or view does not exist"- 작업
create table new(id number) tablespace example;
insert into new(id) values(1);
commit;
select * from new;● 5 . sys session에서 로그스위치 발생
- 강제로 로그스위치해서 over write 되게 만들기
[sys session]
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;- scn 확인
select current_scn from v$database;
CURRENT_SCN
-----------
1452489
select name, checkpoint_change# from v$datafile;
NAME CHECKPOINT_CHANGE#
------------------------------------------------
/u01/app/oracle/oradata/ora11g/system01.dbf 1452371
/u01/app/oracle/oradata/ora11g/sysaux01.dbf 1452371
/u01/app/oracle/oradata/ora11g/users01.dbf 1452371
/u01/app/oracle/oradata/ora11g/example01.dbf 1452371
/u01/app/oracle/oradata/ora11g/undotbs01.dbf 1452371
select * from v$log;select f.file_name
from dba_extents e, dba_data_files f
where e.file_id = f.file_id
and e.segment_name = 'NEW'
and e.owner = 'HR';
FILE_NAME
------------------------------------------------
/u01/app/oracle/oradata/ora11g/example01.dbf
● 6 . emample01.dbf 삭제 -> 장애발생
- db shutdown
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.- example01.dbf 파일 삭제 -> 장애유발
SQL> !
[oracle@oracle ~]$ rm /u01/app/oracle/oradata/ora11g/example01.dbf
[oracle@oracle ~]$ ls /u01/app/oracle/oradata/ora11g/example01.dbf
ls: cannot access /u01/app/oracle/oradata/ora11g/example01.dbf: No such file or directory
[oracle@oracle ~]$ exit
exit- db startup
SQL> startup
ORACLE instance started.
Total System Global Area 531476480 bytes
Fixed Size 1365796 bytes
Variable Size 201328860 bytes
Database Buffers 322961408 bytes
Redo Buffers 5820416 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/u01/app/oracle/oradata/ora11g/example01.dbf'
SQL> select status from v$instance;
STATUS
------------
MOUNTED● 7. backup 파일 restore
SQL> !
[oracle@oracle ~]$ cd /home/oracle/backup/noarch
[oracle@oracle noarch]$ cp -av example01.dbf /u01/app/oracle/oradata/ora11g/example01.dbf
‘example01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/example01.dbf’● 8. recover 실패
: redo 정보가 없기때문에
SQL> recover database;
ORA-00279: change 1446398 generated at 01/09/2024 10:38:43 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2024_01_09/o1_mf_1_43_%u_.a
rc
ORA-00280: change 1446398 for thread 1 is in sequence #43
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00308: cannot open archived log
'/u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2024_01_09/o1_mf_1_43_%u_.
arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00308: cannot open archived log
'/u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2024_01_09/o1_mf_1_43_%u_.
arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3● 9. backup 파일 restore , 불완전 복구를 해야한다.
- db shutdown
SQL> shutdown abort
ORACLE instance shut down.- backup 파일 restore
[oracle@oracle ~]$ cd /home/oracle/backup/noarch/
[oracle@oracle noarch]$ ls
control01.ctl redo01.log redo03.log system01.dbf undotbs01.dbf
example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf
[oracle@oracle noarch]$ cp -av *.* /u01/app/oracle/oradata/ora11g/
‘control01.ctl’ -> ‘/u01/app/oracle/oradata/ora11g/control01.ctl’
‘example01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/example01.dbf’
‘redo01.log’ -> ‘/u01/app/oracle/oradata/ora11g/redo01.log’
‘redo02.log’ -> ‘/u01/app/oracle/oradata/ora11g/redo02.log’
‘redo03.log’ -> ‘/u01/app/oracle/oradata/ora11g/redo03.log’
‘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’
‘undotbs01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/undotbs01.dbf’
‘users01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/users01.dbf’● 10. db start
SQL> startup
ORACLE instance started.
Total System Global Area 531476480 bytes
Fixed Size 1365796 bytes
Variable Size 201328860 bytes
Database Buffers 322961408 bytes
Redo Buffers 5820416 bytes
Database mounted.
Database opened.▶ 3. 백업 받지 않은 테이블 스페이스 1시나리오
[he session]
- 테이블스페이스 생성
create tablespace insa_tbs datafile
'/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf' size 10m;- 확인
select tablespace_name, file_name from dba_data_files;- 체크포인트 확인
select name, checkpoint_change# from v$datafile;
---
/u01/app/oracle/oradata/ora11g/system01.dbf 1450997
/u01/app/oracle/oradata/ora11g/sysaux01.dbf 1450997
/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf 1452359
/u01/app/oracle/oradata/ora11g/users01.dbf 1450997
/u01/app/oracle/oradata/ora11g/example01.dbf 1450997
/u01/app/oracle/oradata/ora11g/undotbs01.dbf 1450997● 1. 장애발생 : db 올린상태에서 테이블스페이스 삭제
[oracle@oracle ~]$ rm /u01/app/oracle/oradata/ora11g/insa_tbs01.dbf
[oracle@oracle ~]$ exit
exit● 2. db shutdown, startup
SQL> shutdown immediate
ORA-01116: error in opening database file 3
ORA-01110: data file 3: '/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 531476480 bytes
Fixed Size 1365796 bytes
Variable Size 201328860 bytes
Database Buffers 322961408 bytes
Redo Buffers 5820416 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf'● 3. 문제가 되는 테이블스페이스 offline으로 떨어 뜨리기
- noarchive log mode 에서는 데이터파일을 offline drop 해야한다. (archive mode에서는 offline 까지만 써도 된다.)
SQL> alter database datafile '/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf' offline drop; Database altered.
- 확인
select name,status from v$datafile;● 4. db open
SQL> alter database open;
Database altered.- 테이블 스페이스 삭제 (backup 해놓은게 없으니깐)
DROP TABLESPACE insa_tbs INCLUDING CONTENTS
AND DATAFILES; ▶ 4. 백업 받지 않은 테이블 스페이스 2시나리오 (redo정보가 있을 경우)
- 테이블스페이스 생성
create tablespace insa_tbs datafile
'/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf' size 10m;- 테이블스페이스 확인
select name,status from v$datafile;- 테이블 create,insert
create table hr.new(id number) tablespace insa_tbs;
insert into hr.new(id) values(1);
commit;● 1. 장애유발 : 운영중에 테이블스페이스 삭제
SQL> ! rm /u01/app/oracle/oradata/ora11g/insa_tbs01.dbf
SQL> ! ls /u01/app/oracle/oradata/ora11g/insa_tbs01.dbf
ls: cannot access /u01/app/oracle/oradata/ora11g/insa_tbs01.dbf: No such file or directory
● 2. 문제가 되는 테이블스페이스 offline으로 떨어 뜨리기
select name,status from v$datafile;
alter database datafile '/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf' offline drop;● 3. 비어있는 테이블스페이스(datafile) 생성
alter database create datafile '/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf';- 확인
SQL> ! ls /u01/app/oracle/oradata/ora11g/insa_tbs01.dbf
/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf● 4. datafile recover
SQL> alter database recover datafile '/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf';
Database altered.● 5. datafile online 으로 변경
select name,status from v$datafile;
alter database datafile '/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf' online;
select name,status from v$datafile;- 조회
select * from hr.new;- 테이블스페이스 삭제
DROP TABLESPACE insa_tbs INCLUDING CONTENTS
AND DATAFILES;▶ 5. 백업 받지 않은 테이블 스페이스 3시나리오 (redo정보가 없을 경우)
- 테이블스페이스 생성
create tablespace insa_tbs datafile
'/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf' size 10m;- 테이블스페이스 확인
select name,status from v$datafile;- 테이블 create,insert
create table hr.new(id number) tablespace insa_tbs;
insert into hr.new(id) values(1);
commit;● 1. 장애유발 : 운영중에 테이블스페이스 삭제
SQL> ! rm /u01/app/oracle/oradata/ora11g/insa_tbs01.dbf
SQL> ! ls /u01/app/oracle/oradata/ora11g/insa_tbs01.dbf
ls: cannot access /u01/app/oracle/oradata/ora11g/insa_tbs01.dbf: No such file or directory
● 2. 문제가 되는 테이블스페이스 offline으로 떨어 뜨리기
select name,status from v$datafile;
alter database datafile '/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf' offline drop;● 3. 비어있는 테이블스페이스(datafile) 생성
alter database create datafile '/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf';- 확인
SQL> ! ls /u01/app/oracle/oradata/ora11g/insa_tbs01.dbf
/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf● 4. sys session에서 로그스위치 발생
- alert log file 항상보기
[oracle@oracle trace]$ pwd
/u01/app/oracle/diag/rdbms/ora11g/ora11g/trace
[oracle@oracle trace]$ tail -f alert_ora11g.log- 강제로 로그스위치해서 over write 되게 만들기
[sys session]
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;● 5. datafile recover -> 에러
SQL> alter database recover datafile '/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf';
alter database datafile '/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf' offline drop
*
ERROR at line 1:
ORA-01156: recovery or flashback in progress may need access to files
● 5. db shutdown, startup
SQL> shutdown immediate
ORA-01116: error in opening database file 3
ORA-01110: data file 3: '/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 531476480 bytes
Fixed Size 1365796 bytes
Variable Size 201328860 bytes
Database Buffers 322961408 bytes
Redo Buffers 5820416 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf'● 6. 테이블 스페이스 삭제 (backup 해놓은게 없으니깐)
DROP TABLESPACE insa_tbs INCLUDING CONTENTS
AND DATAFILES; - 테이블 조회 안되는 것 확인
SQL> select * from hr.new;
select * from hr.new
*
ERROR at line 1:
ORA-00942: table or view does not exist2023/01/10
■ 어제꺼 정리하면서 백업 실습
● 1. archive log mode 인지 noarchive log mode 인지 확인하세요.
- db 상태 되어있는지 확인
SQL> select status from v$instance;
STATUS
------------
OPEN- archive mode 확인
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 46
Current log sequence 48● 2. data file, temp file, control file, redo log file 정보를 확인하세요.
- datafile 확인
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/ora11g/system01.dbf
/u01/app/oracle/oradata/ora11g/sysaux01.dbf
/u01/app/oracle/oradata/ora11g/users01.dbf
/u01/app/oracle/oradata/ora11g/example01.dbf
/u01/app/oracle/oradata/ora11g/undotbs01.dbf- tempfile 확인
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/ora11g/temp01.dbf- controlfile 확인
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/ora11g/control01.ctl- redologfile 확인
select a.group#,b.thread#,b.sequence#, member, b.bytes/1024/1024 mb, b.archived, b.status,
b.first_change#, b.next_change#, b.next_time
from v$logfile a, v$log b
where a.group# = b.group#
order by 1,2;
---
1 1 46 /u01/app/oracle/oradata/ora11g/redo01.log 50 NO INACTIVE 1478364 1478367 24/01/09
2 1 47 /u01/app/oracle/oradata/ora11g/redo02.log 50 NO INACTIVE 1478367 1478444 24/01/09
3 1 48 /u01/app/oracle/oradata/ora11g/redo03.log 50 NO CURRENT 1478444 281474976710655 ● 3. noarchive log mode 입니다. Whole database backup(일관성있는 백업, cold backup)을
/home/oracle/backup/noarch/20240110 디렉토리에 백업해주세요. 초기파라미터 파일도 백업해주세요.
- db shutdown
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.- datafile,tempfile,controlfile,redologfile Backup
[oracle@oracle ~]$ mkdir -p /home/oracle/backup/noarch/20240110
[oracle@oracle ~]$ cd /u01/app/oracle/oradata/ora11g/
[oracle@oracle ora11g]$ ls
control01.ctl redo01.log redo03.log system01.dbf undotbs01.dbf
example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf
[oracle@oracle ora11g]$ date
Tue Jan 9 18:48:03 KST 2024
[oracle@oracle ora11g]$ cp -av *.* /home/oracle/backup/noarch/20240110
‘control01.ctl’ -> ‘/home/oracle/backup/noarch/20240110/control01.ctl’
‘example01.dbf’ -> ‘/home/oracle/backup/noarch/20240110/example01.dbf’
‘redo01.log’ -> ‘/home/oracle/backup/noarch/20240110/redo01.log’
‘redo02.log’ -> ‘/home/oracle/backup/noarch/20240110/redo02.log’
‘redo03.log’ -> ‘/home/oracle/backup/noarch/20240110/redo03.log’
‘sysaux01.dbf’ -> ‘/home/oracle/backup/noarch/20240110/sysaux01.dbf’
‘system01.dbf’ -> ‘/home/oracle/backup/noarch/20240110/system01.dbf’
‘temp01.dbf’ -> ‘/home/oracle/backup/noarch/20240110/temp01.dbf’
‘undotbs01.dbf’ -> ‘/home/oracle/backup/noarch/20240110/undotbs01.dbf’
‘users01.dbf’ -> ‘/home/oracle/backup/noarch/20240110/users01.dbf’- 확인
[oracle@oracle ~]$ cd /home/oracle/backup/noarch/20240110
[oracle@oracle ora11g]$ cd /home/oracle/backup/noarch/20240110
[oracle@oracle 20240110]$ ls
control01.ctl redo01.log redo03.log system01.dbf undotbs01.dbf
example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf▶ 초기파라미터 파일 백업
- spfile위치에 pfile 생성
create pfile from spfile;
- 절대경로에 pfile 생성
create pfile='/home/oracle/backup/noarch/20240110/initora11g_20240110.ora' from spfile;
- 초기파라미터 파일 backup (위치:$ORACLE_HOME/dbs)
[oracle@oracle dbs]$ cd $ORACLE_HOME/dbs
[oracle@oracle dbs]$ pwd
/u01/app/oracle/product/11.2.0.4/db_1/dbs
[oracle@oracle dbs]$ ls
hc_ora11g.dat initora11g.ora orapwora11g spfileora11g.ora
init.ora lkORA11G spfileora11g_20231208.bak spfileora11g.ora.bak
[oracle@oracle dbs]$ cp -av initora11g.ora /home/oracle/backup/noarch/20240110
‘initora11g.ora’ -> ‘/home/oracle/backup/noarch/20240110/initora11g.ora’
[oracle@oracle dbs]$ cp -av spfileora11g.ora /home/oracle/backup/noarch/20240110
‘spfileora11g.ora’ -> ‘/home/oracle/backup/noarch/20240110/spfileora11g.ora’- 확인
[oracle@oracle dbs]$ cd /home/oracle/backup/noarch/20240110
[oracle@oracle 20240110]$ ls
control01.ctl redo01.log spfileora11g.ora temp01.dbf
example01.dbf redo02.log sysaux01.dbf undotbs01.dbf
initora11g.ora redo03.log system01.dbf users01.dbf- db open
SQL> startup
ORACLE instance started.
Total System Global Area 531476480 bytes
Fixed Size 1365796 bytes
Variable Size 201328860 bytes
Database Buffers 322961408 bytes
Redo Buffers 5820416 bytes
Database mounted.
Database opened.■ 스크립트를 이용한 backup을 이용한 복구
● 1. db shutdown
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.● 2. 쿼리문 만들기
select 'cp -av '||name||' /home/oracle/backup/noarch/20240110/' from v$datafile
union all
select 'cp -av '||name||' /home/oracle/backup/noarch/20240110/' from v$tempfile
union all
select 'cp -av '||name||' /home/oracle/backup/noarch/20240110/' from v$controlfile
union all
select 'cp -av '||member||' /home/oracle/backup/noarch/20240110/' from v$logfile;● 3. 쉘 스크립트 만들기
[oracle@oracle ~]$ cd /home/oracle/backup/noarch/20240110/
[oracle@oracle 20240110]$ vi backup.sh
cp -av /u01/app/oracle/oradata/ora11g/system01.dbf /home/oracle/backup/noarch/20240110/
cp -av /u01/app/oracle/oradata/ora11g/sysaux01.dbf /home/oracle/backup/noarch/20240110/
cp -av /u01/app/oracle/oradata/ora11g/users01.dbf /home/oracle/backup/noarch/20240110/
cp -av /u01/app/oracle/oradata/ora11g/example01.dbf /home/oracle/backup/noarch/20240110/
cp -av /u01/app/oracle/oradata/ora11g/undotbs01.dbf /home/oracle/backup/noarch/20240110/
cp -av /u01/app/oracle/oradata/ora11g/temp01.dbf /home/oracle/backup/noarch/20240110/
cp -av /u01/app/oracle/oradata/ora11g/control01.ctl /home/oracle/backup/noarch/20240110/
cp -av /u01/app/oracle/oradata/ora11g/redo03.log /home/oracle/backup/noarch/20240110/
cp -av /u01/app/oracle/oradata/ora11g/redo02.log /home/oracle/backup/noarch/20240110/
cp -av /u01/app/oracle/oradata/ora11g/redo01.log /home/oracle/backup/noarch/20240110/● 4. 쉘 스크립트 실행
[oracle@oracle 20240110]$ sh backup.sh > backup.log
[oracle@oracle 20240110]$ ls
backup.log example01.dbf redo03.log temp01.dbf
backup.sh redo01.log sysaux01.dbf undotbs01.dbf
control01.ctl redo02.log system01.dbf users01.dbf● 5. 복구
[oracle@oracle 20240110]$ rm backup.*
[oracle@oracle 20240110]$ ls
control01.ctl redo01.log redo03.log system01.dbf undotbs01.dbf
example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf
[oracle@oracle 20240110]$ cp -av *.* /u01/app/oracle/oradata/ora11g/
‘control01.ctl’ -> ‘/u01/app/oracle/oradata/ora11g/control01.ctl’
‘example01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/example01.dbf’
‘redo01.log’ -> ‘/u01/app/oracle/oradata/ora11g/redo01.log’
‘redo02.log’ -> ‘/u01/app/oracle/oradata/ora11g/redo02.log’
‘redo03.log’ -> ‘/u01/app/oracle/oradata/ora11g/redo03.log’
‘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’
‘undotbs01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/undotbs01.dbf’
‘users01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/users01.dbf’● 6. db startup
SQL> startup
ORACLE instance started.
Total System Global Area 531476480 bytes
Fixed Size 1365796 bytes
Variable Size 201328860 bytes
Database Buffers 322961408 bytes
Redo Buffers 5820416 bytes
Database mounted.
Database opened.▶ 6. SYSTEM TABLESAPCE 속한 데이터 파일 삭제 시나리오 (redo정보가 있을 경우)
● 1. scn 확인
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1490227
SQL> select name, checkpoint_change# from v$datafile;
NAME CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
/u01/app/oracle/oradata/ora11g/system01.dbf 1489665
/u01/app/oracle/oradata/ora11g/sysaux01.dbf 1489665
/u01/app/oracle/oradata/ora11g/users01.dbf 1489665
/u01/app/oracle/oradata/ora11g/example01.dbf 1489665
/u01/app/oracle/oradata/ora11g/undotbs01.dbf 1489665● 2. 장애유발 : system01.dbf 파일 삭제
SQL> ! rm /u01/app/oracle/oradata/ora11g/system01.dbf
SQL> alter system checkpoint;
alter system checkpoint
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 8610
Session ID: 9 Serial number: 3
SQL> select status from v$instance;
ERROR:
ORA-03114: not connected to ORACLE
SQL> startup
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist- 접속이 끊어져서 재접속후 startup
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 531476480 bytes
Fixed Size 1365796 bytes
Variable Size 201328860 bytes
Database Buffers 322961408 bytes
Redo Buffers 5820416 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/u01/app/oracle/oradata/ora11g/system01.dbf'
SQL> select status from v$instance;
STATUS
------------
MOUNTED- 시스템테이블스페이스의 데이터파일 system01.dbf 는 dictionary 정보가 가지고있는 파일이기때문에 offline으로 db를 start 할수는 없다.
SQL> select name, status from v$datafile;
NAME STATUS
-------------------------------------------------- -------
/u01/app/oracle/oradata/ora11g/system01.dbf SYSTEM
/u01/app/oracle/oradata/ora11g/sysaux01.dbf ONLINE
/u01/app/oracle/oradata/ora11g/users01.dbf ONLINE
/u01/app/oracle/oradata/ora11g/example01.dbf ONLINE
/u01/app/oracle/oradata/ora11g/undotbs01.dbf ONLINE● 3. 백업 restore
[oracle@oracle ~]$ cd backup/noarch/20240110/
[oracle@oracle 20240110]$ ls
control01.ctl redo01.log redo03.log system01.dbf undotbs01.dbf
example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf
[oracle@oracle 20240110]$ cp -av system01.dbf /u01/app/oracle/oradata/ora11g/
‘system01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/system01.dbf’
[oracle@oracle 20240110]$ ls /u01/app/oracle/oradata/ora11g/
control01.ctl redo01.log redo03.log system01.dbf undotbs01.dbf
example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf● 4. system tablesapce 복구
- 백업 restore의 마지막 SCN번호와 redo log file에 있는 SCN번호를 비교하여 복구한다.
- 마지막 백업시점(마지막 체크포인트(SCN)) 이후 변경 이력정보를 redo log file 에서 찾아서 복구 작업 진행한다.
SQL> recover tablespace system;
Media recovery complete.
SQL> alter database open;
Database altered.
● 5. 마지막 scn 까지 맞춰졌는지 확인
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1510972
SQL> select name, checkpoint_change# from v$datafile;
NAME CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
/u01/app/oracle/oradata/ora11g/system01.dbf 1510435
/u01/app/oracle/oradata/ora11g/sysaux01.dbf 1510435
/u01/app/oracle/oradata/ora11g/users01.dbf 1510435
/u01/app/oracle/oradata/ora11g/example01.dbf 1510435
/u01/app/oracle/oradata/ora11g/undotbs01.dbf 1510435▶ 7. SYSTEM TABLESAPCE 속한 데이터 파일 삭제 시나리오 (redo정보가 없을 경우)
● 1. SCN, logfile 확인
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1510972
SQL> select name, checkpoint_change# from v$datafile;
NAME CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
/u01/app/oracle/oradata/ora11g/system01.dbf 1510435
/u01/app/oracle/oradata/ora11g/sysaux01.dbf 1510435
/u01/app/oracle/oradata/ora11g/users01.dbf 1510435
/u01/app/oracle/oradata/ora11g/example01.dbf 1510435
/u01/app/oracle/oradata/ora11g/undotbs01.dbf 1510435
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 49 52428800 512 1 NO CURRENT 1510434 09-JAN-24 2.8147E+14
2 1 47 52428800 512 1 NO INACTIVE 1478367 09-JAN-24 1478444 09-JAN-24
3 1 48 52428800 512 1 NO INACTIVE 1478444 09-JAN-24 1510434 09-JAN-24● 2. redo log file 에 overwrite 되게 로그 스위치 발생
- 로그스위치 발생
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.- backup파일과 redo정보 gap이 발생한다.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 52 52428800 512 1 NO CURRENT 1512550 09-JAN-24 2.8147E+14
2 1 50 52428800 512 1 NO ACTIVE 1512538 09-JAN-24 1512547 09-JAN-24
3 1 51 52428800 512 1 NO ACTIVE 1512547 09-JAN-24 1512550 09-JAN-24
● 3. 장애발생 : 시스템테이블스페이스 데이터파일 삭제
SQL> ! rm /u01/app/oracle/oradata/ora11g/system01.dbf
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 531476480 bytes
Fixed Size 1365796 bytes
Variable Size 201328860 bytes
Database Buffers 322961408 bytes
Redo Buffers 5820416 bytes
Database mounted.● 4. 시스템 데이터파일 restore
[oracle@oracle ~]$ cp -av backup/noarch/20240110/system01.dbf /u01/app/oracle/oradata/ora11g/
‘backup/noarch/20240110/system01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/system01.dbf’● 5. 완전복구 실패
: 마지막 백업 이후에 변경 이력정보가 없어서 완전 복구는 할 수 없다.
SQL> recover database;
ORA-00279: change 1489662 generated at 01/09/2024 19:36:55 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2024_01_09/o1_mf_1_48_%u_.arc
ORA-00280: change 1489662 for thread 1 is in sequence #48● 6. datafile,tempfile,controlfile,redologfile 전부다 restore 해야한다. (불완전복구)
- db shutdown
SQL> shutdown abort
ORACLE instance shut down.- 전부 restore
[oracle@oracle ~]$ cp -av backup/noarch/20240110/*.* /u01/app/oracle/oradata/ora11g/
‘backup/noarch/20240110/control01.ctl’ -> ‘/u01/app/oracle/oradata/ora11g/control01.ctl’
‘backup/noarch/20240110/example01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/example01.dbf’
‘backup/noarch/20240110/redo01.log’ -> ‘/u01/app/oracle/oradata/ora11g/redo01.log’
‘backup/noarch/20240110/redo02.log’ -> ‘/u01/app/oracle/oradata/ora11g/redo02.log’
‘backup/noarch/20240110/redo03.log’ -> ‘/u01/app/oracle/oradata/ora11g/redo03.log’
‘backup/noarch/20240110/sysaux01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/sysaux01.dbf’
‘backup/noarch/20240110/system01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/system01.dbf’
‘backup/noarch/20240110/temp01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/temp01.dbf’
‘backup/noarch/20240110/undotbs01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/undotbs01.dbf’
‘backup/noarch/20240110/users01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/users01.dbf’- db open
SQL> startup
ORACLE instance started.
Total System Global Area 531476480 bytes
Fixed Size 1365796 bytes
Variable Size 201328860 bytes
Database Buffers 322961408 bytes
Redo Buffers 5820416 bytes
Database mounted.
Database opened.▶ 8. UNDO datafile 손상 시나리오 (redo정보가 없을 경우)
- datafile, tablesapce 조회
select a.file#, b.name tbs_name, a.name file_name,
a.status, a.checkpoint_change#
from v$datafile a, v$tablespace b
where a.ts# = b.ts#;- undo segment 조회
SQL> select segment_id, segment_name, owner, tablespace_name, status
from dba_rollback_segs;
SEGMENT_ID SEGMENT_NAME OWNER TABLESPACE_NAME STATUS
---------- ------------------------------ ------ ------------------------------ ----------------
0 SYSTEM SYS SYSTEM ONLINE
1 _SYSSMU1_498470694$ PUBLIC UNDOTBS ONLINE
2 _SYSSMU2_1139286244$ PUBLIC UNDOTBS ONLINE
3 _SYSSMU3_2494311762$ PUBLIC UNDOTBS ONLINE
4 _SYSSMU4_535427529$ PUBLIC UNDOTBS ONLINE
5 _SYSSMU5_390294765$ PUBLIC UNDOTBS ONLINE
6 _SYSSMU6_972788150$ PUBLIC UNDOTBS ONLINE
7 _SYSSMU7_4232614060$ PUBLIC UNDOTBS ONLINE
8 _SYSSMU8_1195849182$ PUBLIC UNDOTBS ONLINE
9 _SYSSMU9_3360189142$ PUBLIC UNDOTBS ONLINE
10 _SYSSMU10_1141378060$ PUBLIC UNDOTBS ONLINE- transaction 발생
[hr session]
update hr.employees
set salary = 2000
where employee_id = 100;- 트랜잭션이있는것 조회
[sys session]
select s.username, s.sid, s.serial#, r.name,
t.xidusn, t.ubafil, t.ubablk, t.used_ublk
from v$session s, v$transaction t, v$rollname r
where s.taddr = t.addr
and t.xidusn = r.usn;
- 트랜잭션 kill session 'SID,SERIAL#'
alter system kill session '17,65' immediate;- 접속이 끊어진다
[hr session]
SQL> select * from hr.employees;
select * from hr.employees
*
ERROR at line 1:
ORA-03135: connection lost contact
Process ID: 21443
Session ID: 17 Serial number: 65- redo log 조회
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 46 52428800 512 1 NO INACTIVE 1478364 09-JAN-24 1478367 09-JAN-24
2 1 47 52428800 512 1 NO INACTIVE 1478367 09-JAN-24 1478444 09-JAN-24
3 1 48 52428800 512 1 NO CURRENT 1478444 09-JAN-24 2.8147E+14● 1. 장애유발 : undo테이블스페이스 데이터파일 삭제
SQL> ! rm /u01/app/oracle/oradata/ora11g/undotbs01.dbf
SQL> alter sysetem checkpoint;
alter sysetem checkpoint
*
ERROR at line 1:
ORA-00940: invalid ALTER command- db shutdown, startup
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 531476480 bytes
Fixed Size 1365796 bytes
Variable Size 201328860 bytes
Database Buffers 322961408 bytes
Redo Buffers 5820416 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/u01/app/oracle/oradata/ora11g/undotbs01.dbf'- datafile 상태 확인
SQL> select name, status from v$datafile;
NAME STATUS
-------------------------------------------------- -------
/u01/app/oracle/oradata/ora11g/system01.dbf SYSTEM
/u01/app/oracle/oradata/ora11g/sysaux01.dbf ONLINE
/u01/app/oracle/oradata/ora11g/users01.dbf ONLINE
/u01/app/oracle/oradata/ora11g/example01.dbf ONLINE
/u01/app/oracle/oradata/ora11g/undotbs01.dbf ONLINE● 2. 장애난 datafile 6 를 offline 상태로 변경
SQL> alter database datafile 6 offline for drop;
Database altered.
SQL> select name, status from v$datafile;
NAME STATUS
-------------------------------------------------- -------
/u01/app/oracle/oradata/ora11g/system01.dbf SYSTEM
/u01/app/oracle/oradata/ora11g/sysaux01.dbf ONLINE
/u01/app/oracle/oradata/ora11g/users01.dbf ONLINE
/u01/app/oracle/oradata/ora11g/example01.dbf ONLINE
/u01/app/oracle/oradata/ora11g/undotbs01.dbf OFFLINE● 3. db open
SQL> alter database open;
Database altered.● 4. undo 테이블스페이스 datafile 생성
- DML (insert, update, delete) 작업이 안된다.
undo테이블스페이스가 없으므로
SQL> update hr.employees
set salary = 2000
where employee_id = 100; 2 3
update hr.employees
*
ERROR at line 1:
ORA-01552: cannot use system rollback segment for non-system tablespace 'EXAMPLE'- undo 테이블스페이스 datafile 생성
SQL> create undo tablespace undo1 datafile '/u01/app/oracle/oradata/ora11g/undo01.dbf' size 10m autoextend on;
Tablespace created.● 5. undo tablespace 변경
- 현재 undo_tablespace 조회
SQL> show parameter undo;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 1800
undo_tablespace string UNDOTBS <- 새로운 언두 수정- undo_tablespace 변경
SQL> alter system set undo_tablespace = undo1;
System altered.- undo_tablespace 변경된것 확인
SQL> show parameter undo;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 1800
undo_tablespace string UNDO1● 6. undo segments 확인
6-1. 만약 UNDOTBS가 OFFLINE 상태이면
7번으로 간다.
6-2. 만약 UNDOTBS가 NEED RECOVERY 상태이면
8번으로 간다.
SQL> select segment_id, segment_name, owner, tablespace_name, status
from dba_rollback_segs; 2
SEGMENT_ID SEGMENT_NAME OWNER TABLESPACE_NAME STATUS
---------- ------------------------------ ------ ------------------------------ ----------------
0 SYSTEM SYS SYSTEM ONLINE
1 _SYSSMU1_498470694$ PUBLIC UNDOTBS OFFLINE
2 _SYSSMU2_1139286244$ PUBLIC UNDOTBS OFFLINE
3 _SYSSMU3_2494311762$ PUBLIC UNDOTBS OFFLINE
4 _SYSSMU4_535427529$ PUBLIC UNDOTBS OFFLINE
5 _SYSSMU5_390294765$ PUBLIC UNDOTBS OFFLINE
6 _SYSSMU6_972788150$ PUBLIC UNDOTBS OFFLINE
7 _SYSSMU7_4232614060$ PUBLIC UNDOTBS OFFLINE
8 _SYSSMU8_1195849182$ PUBLIC UNDOTBS OFFLINE
9 _SYSSMU9_3360189142$ PUBLIC UNDOTBS OFFLINE
10 _SYSSMU10_1141378060$ PUBLIC UNDOTBS OFFLINE
SEGMENT_ID SEGMENT_NAME OWNER TABLESPACE_NAME STATUS
---------- ------------------------------ ------ ------------------------------ ----------------
11 _SYSSMU11_723168542$ PUBLIC UNDO1 ONLINE
12 _SYSSMU12_2689673708$ PUBLIC UNDO1 ONLINE
13 _SYSSMU13_3980157822$ PUBLIC UNDO1 ONLINE
14 _SYSSMU14_2819430755$ PUBLIC UNDO1 ONLINE
15 _SYSSMU15_42532201$ PUBLIC UNDO1 ONLINE
16 _SYSSMU16_422771187$ PUBLIC UNDO1 ONLINE
17 _SYSSMU17_3987586638$ PUBLIC UNDO1 ONLINE
18 _SYSSMU18_2242969461$ PUBLIC UNDO1 ONLINE
19 _SYSSMU19_3530733757$ PUBLIC UNDO1 ONLINE
20 _SYSSMU20_3172209127$ PUBLIC UNDO1 ONLINE
21 rows selected.● 7. 기존 UNDO 테이블스페이스 삭제 (OFFLINE 상태)
SQL> drop tablespace undotbs including contents and datafiles;
Tablespace dropped.끝
● 8. 초기파라미터 파일로 확인 (NEED RECOVERY 상태)
- 초기파라미터 파일 생성
SQL> create pfile form spfile;- db shutdown
SQL> shutdown immediate● 9. NEED RECOVERY 상태 인것만 OFFLINE 상태로 변경 ( 수동 변경 )
[oracle@oracle ~]$ cd $ORACLE_HOME/dbs
[oracle@oracle dbs]$ ls
hc_ora11g.dat initora11g.ora orapwora11g spfileora11g.ora
init.ora lkORA11G spfileora11g_20231208.bak spfileora11g.ora.bak
[oracle@oracle dbs]$ vi initora11g.ora
--밑에추가--
_offline_rollback_segments=(
_SYSSMU1_498470694$,
_SYSSMU2_1139286244$,
_SYSSMU3_2494311762$,
_SYSSMU4_535427529$,
_SYSSMU5_390294765$,
_SYSSMU6_972788150$,
_SYSSMU7_4232614060$,
_SYSSMU8_1195849182$,
_SYSSMU9_3360189142$,
_SYSSMU10_1141378060$)● 10. 변경된 pfile로 db start
SQL> startup pfile='$ORACLE_HOME/dbs/initora11g.ora'
ORACLE instance started- 전의 undo tablespace 삭제
SQL> drop tablespace undotbs including contents and datafiles;
Tablespace dropped.▶ 9.현재undo tbs와 백업 undo tbs가 다를때, 모든 datafile,tempfile,controlfile,redologfile 삭제 되었을때 시나리오
● 1. 장애발생 : 모두 삭제
[oracle@oracle ~]$ rm $ORACLE_HOME/dbs/*.*● 2. backup본으로 restore
[oracle@oracle ~]$ cp -av /home/oracle/backup/noarch/20240110 $ORACLE_HOME/dbs/
[oracle@oracle ~]$ cd $ORACLE_HOME/dbs/
[oracle@oracle dbs]$ ls
hc_ora11g.dat initora11g.ora orapwora11g spfileora11g.ora
init.ora lkORA11G spfileora11g_20231208.bak spfileora11g.ora.bak● 3. init 파일의 undotbs 명 변경
[oracle@oracle dbs]$ vi initora11g.ora
--전에 undotbs 로 변경--
*.undo_tablespace='UNDOTBS'● 4. pfile로 db start
[oracle@oracle dbs]$ exit
exit
SQL> startup pfile='$ORACLE_HOME/dbs/initora11g.ora'
ORACLE instance started.
Total System Global Area 531476480 bytes
Fixed Size 1365796 bytes
Variable Size 201328860 bytes
Database Buffers 322961408 bytes
Redo Buffers 5820416 bytes
Database mounted.
Database opened.● 5. spfile 생성 pfile을 이용한
SQL> create spfile from pfile;
File created.● 6. db 재시작 (spfile로 열리게)
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 531476480 bytes
Fixed Size 1365796 bytes
Variable Size 201328860 bytes
Database Buffers 322961408 bytes
Redo Buffers 5820416 bytes
Database mounted.
Database opened.▶ 10. 모든 데이터파일, 리두로그파일, 컨트롤 파일이 있는 디스크 손상 시나리오
● 1. 쿼리문작성
■ control file 위치 변경 작업, 초기파라미터 파일중 pfile을 수정해서 수행
▶ 이전컨트롤파일 위치
*.control_files='/u01/app/oracle/oradata/ora11g/control01.ctl'▶ 새로운 컨트롤 파일 위치
*.control_files='/home/oracle/ora_data/control01.ctl'■ 데이터 파일, 리두로그파일 위치변경, 데이터 이관작업
alter database rename file '이전경로의 파일' to '새로운경로의 파일';================
▶ 데이터 파일
- 쿼리문
select 'alter database rename file '''||name||''' to ''/home/oracle/ora_data/''' from v$datafile;alter database rename file '/u01/app/oracle/oradata/ora11g/system01.dbf' to '/home/oracle/ora_data/system01.dbf';
alter database rename file '/u01/app/oracle/oradata/ora11g/sysaux01.dbf' to '/home/oracle/ora_data/sysaux01.dbf';
alter database rename file '/u01/app/oracle/oradata/ora11g/users01.dbf' to '/home/oracle/ora_data/users01.dbf';
alter database rename file '/u01/app/oracle/oradata/ora11g/example01.dbf' to '/home/oracle/ora_data/example01.dbf';
alter database rename file '/u01/app/oracle/oradata/ora11g/undotbs01.dbf' to '/home/oracle/ora_data/undotbs01.dbf';▶ temp 파일
- 쿼리문
select 'alter database rename file '''||name||''' to ''/home/oracle/ora_data/'';' from v$tempfile;alter database rename file '/u01/app/oracle/oradata/ora11g/temp01.dbf' to '/home/oracle/ora_data/temp01.dbf';▶ redo 파일
- 쿼리문
select 'alter database rename file '''||member||''' to ''/home/oracle/ora_data/'';' from v$logfile;alter database rename file '/u01/app/oracle/oradata/ora11g/redo01.log' to '/home/oracle/ora_data/redo01.log';
alter database rename file '/u01/app/oracle/oradata/ora11g/redo02.log' to '/home/oracle/ora_data/redo02.log';
alter database rename file '/u01/app/oracle/oradata/ora11g/redo03.log' to '/home/oracle/ora_data/redo03.log';● 2. 백업파일 체크하기
- 초기파라미터 파일 생성
SQL> create pfile from spfile;
File created.
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
.4/db_1/dbs/spfileora11g.ora- 대상파일 확인
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ora11g/system01.dbf
/u01/app/oracle/oradata/ora11g/sysaux01.dbf
/u01/app/oracle/oradata/ora11g/users01.dbf
/u01/app/oracle/oradata/ora11g/example01.dbf
/u01/app/oracle/oradata/ora11g/undotbs01.dbf
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ora11g/temp01.dbf
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ora11g/control01.ctl
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ora11g/redo03.log
/u01/app/oracle/oradata/ora11g/redo02.log
/u01/app/oracle/oradata/ora11g/redo01.log
- 백업본 체크
[oracle@oracle ~]$ cd backup/noarch/20240110/
[oracle@oracle 20240110]$ ls
control01.ctl redo01.log redo03.log system01.dbf undotbs01.dbf
example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf
● 3. 장애유발 : 모든파일삭제
[oracle@oracle ~]$ cd /u01/app/oracle/oradata/ora11g/
[oracle@oracle ora11g]$ ls
control01.ctl redo01.log redo03.log system01.dbf undotbs01.dbf
example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf
[oracle@oracle ora11g]$ rm *.*
[oracle@oracle ora11g]$ ls
[oracle@oracle ora11g]$ exit
exit
SQL> alter system checkpoint;
alter system checkpoint
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 31931
Session ID: 162 Serial number: 5
SQL> conn / as sysdba
Connected to an idle instance.
SQL> exit
Disconnected● 4. 새로운 디스크에 백업본을 이관작업
[oracle@oracle ~]$ ls backup/noarch/20240110
control01.ctl redo01.log redo03.log system01.dbf undotbs01.dbf
example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf
[oracle@oracle ~]$ pwd
/home/oracle
[oracle@oracle ~]$ mkdir ora_data -- 다른디스크라고 생각
[oracle@oracle ~]$ cd ora_data
[oracle@oracle ora_data]$ pwd
/home/oracle/ora_data
[oracle@oracle ora_data]$ cp -av /home/oracle/backup/noarch/20240110/*.* /home/oracle/ora_data/
‘/home/oracle/backup/noarch/20240110/control01.ctl’ -> ‘/home/oracle/ora_data/control01.ctl’
‘/home/oracle/backup/noarch/20240110/example01.dbf’ -> ‘/home/oracle/ora_data/example01.dbf’
‘/home/oracle/backup/noarch/20240110/redo01.log’ -> ‘/home/oracle/ora_data/redo01.log’
‘/home/oracle/backup/noarch/20240110/redo02.log’ -> ‘/home/oracle/ora_data/redo02.log’
‘/home/oracle/backup/noarch/20240110/redo03.log’ -> ‘/home/oracle/ora_data/redo03.log’
‘/home/oracle/backup/noarch/20240110/sysaux01.dbf’ -> ‘/home/oracle/ora_data/sysaux01.dbf’
‘/home/oracle/backup/noarch/20240110/system01.dbf’ -> ‘/home/oracle/ora_data/system01.dbf’
‘/home/oracle/backup/noarch/20240110/temp01.dbf’ -> ‘/home/oracle/ora_data/temp01.dbf’
‘/home/oracle/backup/noarch/20240110/undotbs01.dbf’ -> ‘/home/oracle/ora_data/undotbs01.dbf’
‘/home/oracle/backup/noarch/20240110/users01.dbf’ -> ‘/home/oracle/ora_data/users01.dbf’● 5. 초기파라미터 파일에서 컨트롤 파일 위치정보 수정
[oracle@oracle ora_data]$ cd $ORACLE_HOME/dbs
[oracle@oracle dbs]$ ls
hc_ora11g.dat initora11g.ora orapwora11g spfileora11g.ora
init.ora lkORA11G spfileora11g_20231208.bak spfileora11g.ora.bak- controlfile 위치 새로운디스크로 변경
[oracle@oracle dbs]$ vi initora11g.ora
-- 변경 --
*.control_files='/home/oracle/ora_data/control01.ctl'● 6. pfile 을 이용하여 mount단계까지 db start
system, undo, redologfile 파일은 open 단계(운영중)에서 변경 불가 이므로 mount단계 까지 db start
[oracle@oracle dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Jan 10 01:54:03 2024
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup pfile='$ORACLE_HOME/dbs/initora11g.ora' mount
ORACLE instance started.
Total System Global Area 531476480 bytes
Fixed Size 1365796 bytes
Variable Size 201328860 bytes
Database Buffers 322961408 bytes
Redo Buffers 5820416 bytes
Database mounted.
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /home/oracle/ora_data/control0
1.ctl
● 7. datafile,tempfile,redo logfile 위치변경
- datafile 위치변경
alter database rename file '/u01/app/oracle/oradata/ora11g/system01.dbf' to '/home/oracle/ora_data/system01.dbf';
alter database rename file '/u01/app/oracle/oradata/ora11g/sysaux01.dbf' to '/home/oracle/ora_data/sysaux01.dbf';
alter database rename file '/u01/app/oracle/oradata/ora11g/users01.dbf' to '/home/oracle/ora_data/users01.dbf';
alter database rename file '/u01/app/oracle/oradata/ora11g/example01.dbf' to '/home/oracle/ora_data/example01.dbf';
alter database rename file '/u01/app/oracle/oradata/ora11g/undotbs01.dbf' to '/home/oracle/ora_data/undotbs01.dbf';- tempfile 위치변경
alter database rename file '/u01/app/oracle/oradata/ora11g/temp01.dbf' to '/home/oracle/ora_data/temp01.dbf';- redologfile 위치변경
alter database rename file '/u01/app/oracle/oradata/ora11g/redo01.log' to '/home/oracle/ora_data/redo01.log';
alter database rename file '/u01/app/oracle/oradata/ora11g/redo02.log' to '/home/oracle/ora_data/redo02.log';
alter database rename file '/u01/app/oracle/oradata/ora11g/redo03.log' to '/home/oracle/ora_data/redo03.log';
● 8. db open
SQL> alter database open;
se
Database altered.- 확인
select tablespace_name, file_name from dba_data_files;
select name from v$tempfile;
select name from v$controlfile;
select member from v$logfile;● 9. spfile로 db 재 open
SQL> create spfile from pfile;
File created.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 531476480 bytes
Fixed Size 1365796 bytes
Variable Size 201328860 bytes
Database Buffers 322961408 bytes
Redo Buffers 5820416 bytes
Database mounted.
Database opened.▶ 11. 10번시나리오 되돌리기 시나리오
● 1. db 정상종료
SQL> shutdown immediatew
SP2-0717: illegal SHUTDOWN option
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !● 2. 장애유발 : 모든데이터파일 삭제
[oracle@oracle dbs]$ ls
hc_ora11g.dat initora11g.ora orapwora11g spfileora11g.ora
init.ora lkORA11G spfileora11g_20231208.bak spfileora11g.ora.bak
[oracle@oracle dbs]$ cd /home/oracle/ora_data
[oracle@oracle ora_data]$ ls
control01.ctl redo01.log redo03.log system01.dbf undotbs01.dbf
example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf
[oracle@oracle ora_data]$ rm *.*
[oracle@oracle ora_data]$ exit
exit- db open안되는것 확인
SQL> startup
ORACLE instance started.
Total System Global Area 531476480 bytes
Fixed Size 1365796 bytes
Variable Size 201328860 bytes
Database Buffers 322961408 bytes
Redo Buffers 5820416 bytes
ORA-00205: error in identifying control file, check alert log for more info
SQL> shutdown abort
ORACLE instance shut down.
SQL> !● 3. 백업파일로 restore
[oracle@oracle 20240110]$ cd /home/oracle/backup/noarch/20240110
[oracle@oracle 20240110]$ cp -av *.* /u01/app/oracle/oradata/ora11g/
‘control01.ctl’ -> ‘/u01/app/oracle/oradata/ora11g/control01.ctl’
‘example01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/example01.dbf’
‘redo01.log’ -> ‘/u01/app/oracle/oradata/ora11g/redo01.log’
‘redo02.log’ -> ‘/u01/app/oracle/oradata/ora11g/redo02.log’
‘redo03.log’ -> ‘/u01/app/oracle/oradata/ora11g/redo03.log’
‘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’
‘undotbs01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/undotbs01.dbf’
‘users01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/users01.dbf’
[oracle@oracle 20240110]$ cd /u01/app/oracle/oradata/ora11g/
[oracle@oracle ora11g]$ ls
control01.ctl redo01.log redo03.log system01.dbf undotbs01.dbf
example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf● 4. 초기파라미터 파일에서 control file 위치정보수정
[oracle@oracle ora11g]$ cd $ORACLE_HOME/dbs
[oracle@oracle dbs]$ ls
hc_ora11g.dat initora11g.ora orapwora11g spfileora11g.ora
init.ora lkORA11G spfileora11g_20231208.bak spfileora11g.ora.bak
[oracle@oracle dbs]$ vi initora11g.ora
--변경--
*.control_files='/u01/app/oracle/oradata/ora11g/control01.ctl'● 5. pfile이용하여 open
backup본이 전에꺼 그대로 니깐
alter database rename file안해도 된다
SQL> startup pfile='$ORACLE_HOME/dbs/initora11g.ora'
ORACLE instance started.
Total System Global Area 531476480 bytes
Fixed Size 1365796 bytes
Variable Size 201328860 bytes
Database Buffers 322961408 bytes
Redo Buffers 5820416 bytes
Database mounted.
Database opened.● 6. pfile을 이용하여 spfile 만들기
SQL> create spfile from pfile;
File created.● 7. spfile로 db 재시작
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 531476480 bytes
Fixed Size 1365796 bytes
Variable Size 201328860 bytes
Database Buffers 322961408 bytes
Redo Buffers 5820416 bytes
Database mounted.
Database opened.2023/01/11
▶ 12. datafile, controlfile만 백업 이후에 현재 redo에 정보가 없을 경우 시나리오 (특정한 데이터 파일이 손상)
noarchive log mode 입니다. Whole database backup (일관성있는 백업, cold backup)을
/home/oracle/backup/noarch/20240111 디렉토리에 백업해주세여. 단, datafile,control file 만 백업
- 백업할곳 만들기
[oracle@oracle ~]$ mkdir -p /home/oracle/backup/noarch/20240111
[oracle@oracle 20240111]$ pwd
/home/oracle/backup/noarch/20240111- 대상, scn 확인
select name,checkpoint_change# from v$datafile;
select name from v$tempfile;
select name from v$controlfile;
select member from v$logfile;- db 정상 종료
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.- 초기파라미터 파일 backup
create pfile='/home/oracle/backup/noarch/20240111/initora11g_20240110.ora' from spfile;- 디렉토리에 backup
[oracle@oracle ora11g]$ cp -av *.dbf /home/oracle/backup/noarch/20240111/
‘example01.dbf’ -> ‘/home/oracle/backup/noarch/20240111/example01.dbf’
‘sysaux01.dbf’ -> ‘/home/oracle/backup/noarch/20240111/sysaux01.dbf’
‘system01.dbf’ -> ‘/home/oracle/backup/noarch/20240111/system01.dbf’
‘temp01.dbf’ -> ‘/home/oracle/backup/noarch/20240111/temp01.dbf’
‘undotbs01.dbf’ -> ‘/home/oracle/backup/noarch/20240111/undotbs01.dbf’
‘users01.dbf’ -> ‘/home/oracle/backup/noarch/20240111/users01.dbf’
[oracle@oracle ora11g]$ cp -av control01.ctl /home/oracle/backup/noarch/20240111/
‘control01.ctl’ -> ‘/home/oracle/backup/noarch/20240111/control01.ctl’
[oracle@oracle ora11g]$ cd /home/oracle/backup/noarch/20240111/
[oracle@oracle 20240111]$ ls
control01.ctl sysaux01.dbf temp01.dbf users01.dbf
example01.dbf system01.dbf undotbs01.dbf
[oracle@oracle 20240111]$ rm temp01.dbf
[oracle@oracle 20240111]$ ls
control01.ctl sysaux01.dbf undotbs01.dbf
example01.dbf system01.dbf users01.dbf- db open
SQL> startup
ORACLE instance started.
Total System Global Area 531476480 bytes
Fixed Size 1365796 bytes
Variable Size 201328860 bytes
Database Buffers 322961408 bytes
Redo Buffers 5820416 bytes
Database mounted.
Database opened.● 1. 작업
create table hr.emp_new
as
select * from hr.employees;
select f.file_name
from dba_extents e, dba_data_files f
where e.file_id = f.file_id
and e.segment_name = 'EMP_NEW'
and e.owner = 'HR';
FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ora11g/users01.dbf
select * from v$log;- 로그스위치 발생
SQL> ALTER SYSTEM SWITCH logfile;
System altered.
SQL> ALTER SYSTEM SWITCH logfile;
System altered.
SQL> ALTER SYSTEM SWITCH logfile;
System altered.● 2. 장애발생 : user테이블스페이스 데이터파일삭제
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !
[oracle@oracle 20240111]$ rm /u01/app/oracle/oradata/ora11g/users01.dbf
SQL> startup
ORACLE instance started.
Total System Global Area 531476480 bytes
Fixed Size 1365796 bytes
Variable Size 201328860 bytes
Database Buffers 322961408 bytes
Redo Buffers 5820416 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/u01/app/oracle/oradata/ora11g/users01.dbf'
● 3. 문제가 되는 datafile offline 상태로 변경
SQL> alter database datafile 4 offline drop;
Database altered.
SQL> select name, status from v$datafile;
NAME
--------------------------------------------------------------------------------
STATUS
-------
/u01/app/oracle/oradata/ora11g/system01.dbf
SYSTEM
/u01/app/oracle/oradata/ora11g/sysaux01.dbf
ONLINE
/u01/app/oracle/oradata/ora11g/users01.dbf
OFFLINE
NAME
--------------------------------------------------------------------------------
STATUS
-------
/u01/app/oracle/oradata/ora11g/example01.dbf
ONLINE
/u01/app/oracle/oradata/ora11g/undotbs01.dbf
ONLINE● 4. backup파일 restore
SQL> !
[oracle@oracle 20240111]$ cp -av /home/oracle/backup/noarch/20240111/users01.dbf /u01/app/oracle/oradata/ora11g/
‘/home/oracle/backup/noarch/20240111/users01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/users01.dbf’
[oracle@oracle 20240111]$ cd /u01/app/oracle/oradata/ora11g/
[oracle@oracle ora11g]$ ls
control01.ctl redo01.log redo03.log system01.dbf undotbs01.dbf
example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf● 5. redologfile에 정보 있는줄 알고 recover
근데 redo에 정보가 없다.
SQL> recover tablespace users;
ORA-00279: change 1513640 generated at 01/11/2024 10:06:03 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2024_01_11/o1_mf_1_49_%u_.a
rc
ORA-00280: change 1513640 for thread 1 is in sequence #49
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log
'/u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2024_01_11/o1_mf_1_49_%u_.
arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00308: cannot open archived log
'/u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2024_01_11/o1_mf_1_49_%u_.
arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
SQL> select * from hr.emp_new;
select * from hr.emp_new
*
ERROR at line 1:
ORA-00376: file 4 cannot be read at this time
ORA-01110: data file 4: '/u01/app/oracle/oradata/ora11g/users01.dbf'- db 강제종료
SQL> shutdown abort
ORACLE instance shut down.● 6. datafile,controlfile backup본 restore
[oracle@oracle 20240111]$ cp -av /home/oracle/backup/noarch/20240111/*.dbf /u01/app/oracle/oradata/ora11g/
‘/home/oracle/backup/noarch/20240111/example01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/example01.dbf’
‘/home/oracle/backup/noarch/20240111/sysaux01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/sysaux01.dbf’
‘/home/oracle/backup/noarch/20240111/system01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/system01.dbf’
‘/home/oracle/backup/noarch/20240111/temp01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/temp01.dbf’
‘/home/oracle/backup/noarch/20240111/undotbs01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/undotbs01.dbf’
‘/home/oracle/backup/noarch/20240111/users01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/users01.dbf’
[oracle@oracle 20240111]$ cp -av /home/oracle/backup/noarch/20240111/*.ctl /u01/app/oracle/oradata/ora11g/
‘/home/oracle/backup/noarch/20240111/control01.ctl’ -> ‘/u01/app/oracle/oradata/ora11g/control01.ctl’● 7. mount 단계까지 db start
datafile, controlfile 까지만 backup을 했으므로 mount단계 까지만 start
SQL> startup mount
ORACLE instance started.
Total System Global Area 531476480 bytes
Fixed Size 1365796 bytes
Variable Size 201328860 bytes
Database Buffers 322961408 bytes
Redo Buffers 5820416 bytes
Database mounted.● 8. cancel base recover
: controlfile의 scn번호와 redologfile의 scn번호가 다르므로
SQL> recover database until cancel using backup controlfile;
ORA-00279: change 1513640 generated at 01/11/2024 10:06:03 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2024_01_11/o1_mf_1_49_%u_.a
rc
ORA-00280: change 1513640 for thread 1 is in sequence #49
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel -- cancel 입력!
Media recovery cancelled.● 9. redologfile 의 scn을 controlfile scn과 맞춰서 싱크를 맞게해서 open 가능하게 하기
- redologfile db scn번호 reset
resetlog를 쓴순가 시퀀스 번호가 다르므로 현재시점까지 backup으로 돌아갈수 없으므로
db 정상종료후 모든데이터파일 백업해야한다.
SQL> alter database open resetlogs;
Database altered.- redologfile scn 확인
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
1 1 1 52428800 512 1 NO
CURRENT 1513641 11-JAN-24 2.8147E+14
2 1 0 52428800 512 1 YES
UNUSED 0 0
3 1 0 52428800 512 1 YES
UNUSED 0 0리두로그그룹 사이즈가 너무작아서
로그스위치가 빈번히 일어나면서
dbwr도 빈번히 일어나는데 이때 로그스위치 wait event 발생한다.
▶ 13. temp file 손상 되었을 경우 복구 방식 시나리오(특정한 데이터 파일이 손상)
● 1. 장애발생 : tempfile 삭제
- tempfile 확인
select * from v$tempfile;
select * from dba_temp_files;- tempfile 삭제
SQL> ! rm /u01/app/oracle/oradata/ora11g/temp01.dbf
SQL> ! ls /u01/app/oracle/oradata/ora11g/temp01.dbf
ls: cannot access /u01/app/oracle/oradata/ora11g/temp01.dbf: No such file or directory● 2. 정렬작업 수행
- sort_area_size 변경
: sort_area_size는 정렬 작업을 수행할 때 사용하는 메모리 영역의 크기를 나타냅니다
SQL> alter session set sort_area_size = 0;
Session altered.:이렇게 설정하면 오라클은 정렬 작업을 위한 메모리를 할당하지 않고, 대신 디스크 기반 정렬을 수행하게 됩니다.
- sort 작업 실행 -> 오류
: tempfile이 손상되었을 경우 문제되는 SQL문에 대해서만 오류가 발생한다.
SQL> select s.*, b.*
from dba_objects s, dba_objects b
order by 1,2,3,4; 2 3
from dba_objects s, dba_objects b
*
ERROR at line 2:
ORA-01565: error in identifying file
'/u01/app/oracle/oradata/ora11g/temp01.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3- alert log file이 있는 위치 정보
SQL> show parameter background_dump_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest string /u01/app/oracle/diag/rdbms/ora
11g/ora11g/trace- default temp tablespace 확인
select * from database_properties;해결방법 1
● 3. 새로운 tempfile 생성후, 기존 tempfile 삭제
- tempfile 생성
alter tablespace temp add tempfile '/u01/app/oracle/oradata/ora11g/temp02.dbf' size 20m;- 확인
select name from v$tempfile;- 기존 tempfile 삭제
alter tablespace temp drop tempfile '/u01/app/oracle/oradata/ora11g/temp01.dbf';- 확인
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ora11g/temp02.dbf● 4. 다시 sort 작업
SQL> alter session set sort_area_size = 0;
Session altered.
SQL> select s.*, b.*
from dba_objects s, dba_objects b
order by 1,2,3,4; 2 3
from dba_objects s, dba_objects b
*
ERROR at line 2:
ORA-01652: unable to extend temp segment by 128 in tablespace TEMPORA-01652 오류는 임시 세그먼트 확장할 수 없을때 발생
해결방법 3가지
- tempfile 사이즈 조정
alter database tempfile '/u01/app/oracle/oradata/ora11g/temp02.dbf' resize 100m;
- 자동 확장 기능 활성화
alter database tempfile '/u01/app/oracle/oradata/ora11g/temp02.dbf' autoextend on;
- tempfile 추가
alter tablespace temp add tempfile '/u01/app/oracle/oradata/ora11g/temp03.dbf' size 10m autoextend on;
- tempfile 확인
select * from dba_temp_files;해결방법 2.
● 3. 새로운 temp tablespace 생성하고 default temp tablespace 지정
- 새로운 temp tablespace 생성
create temporary tablespace temp_new tempfile
'/u01/app/oracle/oradata/ora11g/temp_new01.dbf' size 20m autoextend on;- default temp tablespace 지정
alter database default temporary tablespace temp_new;- 확인
select * from database_properties;
select * from dba_temp_files;
select * from dba_users;● 4. 기존 temp 테이블스페이스 삭제
- 기존 temp tablespace 삭제
drop tablespace temp including contents and datafiles;- 확인
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ora11g/temp_new01.dbf해결방법 3.
temp file 손상된걸 모르는 상황에서 DB 종료했다가 다시시작하면 오라클이 알아서 Re-creating 해준다.
SQL> ! rm /u01/app/oracle/oradata/ora11g/temp_new01.dbf
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 531476480 bytes
Fixed Size 1365796 bytes
Variable Size 201328860 bytes
Database Buffers 322961408 bytes
Redo Buffers 5820416 bytes
Database mounted.
Database opened.- alertlog 창에 re-creating tempfile 했다고 뜬다.
SMON: enabling tx recovery
Re-creating tempfile /u01/app/oracle/oradata/ora11g/temp_new01.dbf2023/01/12
■ noarchive log mode
- 기본적으로 데이터베이스는 noarchive log mode 로 생성됩니다.
- redo log file이 순환 방식으로 사용된다.
- log switch 발생하면 checkpoint 발생한 즉시 redo log file은 재사용할 수 있다.
- redo log 가 겹쳐쓰여지면 마지막 전체 backup 에 대해서만 media recovery 를 할 수 있다.
안전 복구 보다는 불안전한 복구 방식을 수행 할 경우가 많다. - 데이터베이스 정상적인 종료한 후 백업을 수행 해야 한다.
shutdown (normal|transactional|immdiate) - backup 할 때마다 datafile,tmepfile,controlfile,redo log file(옵션)
■ archive log mode
- log switch 발생하면 checkpoint가 발생하고 ARC background process 가 현재 redo log file 을
물리적 다른 위치에 복사 한 후 redo log file은 재사용할 수 있다. - control file에 archive 된 redo log file의 정보를 기록한다.
- 데이터베이스 정상적인 종료 한 후 백업을 수행할 수 있고 운영중에 백업을 수행 할 수 있다.
- 일관성 없는 백업, online backup, hot backup, open backup을 수행할 수 있다.
- 문제되는 파일을 현재 시점까지 복구 할 수 있다. redo log 정보가 있기 때문에 가능하다.
■ archive log mode
- archive log file이 생성되는 디렉토리 생성
[oracle@oracle ~]$ pwd
/home/oracle
[oracle@oracle ~]$ mkdir arch1
[oracle@oracle ~]$ mkdir arch2▶ 아카이브 조회
- 아카이브 로그의 저장 위치1 조회
select * from v$parameter where name = 'log_archive_dest_1';- 아카이브 로그의 저장 위치1 조회
select * from v$parameter where name = 'log_archive_dest_2';- 아카이브 로그 파일의 형식 조회
select * from v$parameter where name = 'log_archive_format';▶ archive log file이 생성 되는 위치 설정
- archive disk를 이중화 해놓았다.
- mandatory
: archive 작업이 성공적으로 완료되어야 online redo log file을 겹쳐 사용할 수 있다.
공간이 부족할 경우 wait 한다.alter system set log_archive_dest_1='location=/home/oracle/arch1 mandatory' scope=spfile;
- optional
: archive 작업이 성공적으로 완료 되지 않은 경우에도 online redolog file을 겹쳐사용할 수 있다. (기본값)
공간이 부족하면 archivelog file이 생성 되지 않습니다.alter system set log_archive_dest_2='location=/home/oracle/arch2 optional' scope=spfile;
- archivelog file의 여유 공간을 항상 확인해줘야한다.
[oracle@oracle ~]$ df -h /dev/mapper/ol-root 45G 24G 21G 54% /
- archive log file이 생성 될때 이름 포맷을 설정
%s : log sequence number
%t : thread number (instance number)
%r : resetlogs id 여러 데이터베이스에 걸쳐 아카이브 된 로그파일의 유일한 이름을 나타낸다.alter system set log_archive_format='arch_%t_%s_%r.arc' scope=spfile;
- 만약 archive log mode 상태에서 archive log file이 생성 되는 위치 설정을 같은곳에하거나 잘못설정하거나 archive log file이 생성 될때 이름 포맷을 설정을 잘못하면 db mount 단계까지도 start 안된다.
- 해결방법
- db 정상종료
- pfile을 이용하여 archive log file 위치정보, 포맷설정 변경
- db open
- create spfile from pfile;
- db 재시작
▶ archive log mode 로 변경
● 1. db 정상 종료
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.● 2. mount 단계까지만 db start (control file까지만 열기)
SQL> startup mount
ORACLE instance started.
Total System Global Area 531476480 bytes
Fixed Size 1365796 bytes
Variable Size 201328860 bytes
Database Buffers 322961408 bytes
Redo Buffers 5820416 bytes
Database mounted.● 3. archive log mode로 변경
SQL> alter database archivelog;
Database altered.- noarchive log mode로 변경하고 싶으면
SQL> alter database noarchivelog;● 4. db open
SQL> alter database open;
Database altered.● 5. archive log mode 확인
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/arch2
Oldest online log sequence 1
Next log sequence to archive 3
Current log sequence 3
SQL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG- redo log file 확인
select * from v$log;- archive log dest 정보 확인
select * from v$archive_dest;
-- 보통 3개 정도 본다
select destination, binding, status from v$archive_dest;- archive log 조회
select * from v$archived_log;현재의 current한 redo 로그 파일을 아카이브(log_archive_dest에 지정된 위치로) 변경
alter system archive log current;
- log_archived 확인
select sequence#, name from v$archived_log;
[oracle@oracle ~]$ ls /home/oracle/arch1 /home/oracle/arch2
/home/oracle/arch1:
arch_1_3_1157971653.arc
/home/oracle/arch2:
arch_1_3_1157971653.arc
- ※ 주의사항
만약에 db 운영중에 arc file이 유실 되었다는것을 알았다면 그 즉시 backup본을 만들어야한다. 만약 유실되었다는것을 모르고 만들지않고 db 종료하면, 완전복구를 못하므로 과거시점으로 restore해서 돌아가는 방법밖에 없다.
- 아카이브 프로세스에 관한 세부 정보를
select * from v$archive_processes;SQL> ! ps -ef | grep arc
oracle 7843 1 0 19:26 ? 00:00:00 ora_arc0_ora11g
oracle 7846 1 0 19:26 ? 00:00:00 ora_arc1_ora11g
oracle 7848 1 0 19:26 ? 00:00:00 ora_arc2_ora11g
oracle 7850 1 0 19:26 ? 00:00:00 ora_arc3_ora11g
oracle 13351 23064 0 20:51 pts/0 00:00:00 /bin/bash -c ps -ef | grep arc
oracle 13353 13351 0 20:51 pts/0 00:00:00 grep arcarchive log mode backup
select * from vlog; select name, checkpoint_change#, status from vdatafile;
select tablespace_name, logging from dba_tablespaces;
■ 백업 시나리오 - archive log mode
▶ 일관성 있는 백업 (cold backup) 시나리오
● 1. 백업 대상 확인
- cold backup 대상 data file,temp file,control file, redolog file
select * from v$log;
select name, checkpoint_change#, status from v$datafile;
select tablespace_name, logging from dba_tablespaces;
select name from v$tempfile;
select name from v$controlfile;
select member from v$logfile;● 2. 백업 쉘 쿼리문 작성
- 쿼리문 작성
select 'cp -av '||name||' /home/oracle/backup/arch/cold_20240112/' from v$datafile union all select 'cp -av '||name||' /home/oracle/backup/arch/cold_20240112/' from v$tempfile union all select 'cp -av '||name||' /home/oracle/backup/arch/cold_20240112/' from v$controlfile union all select 'cp -av '||member||' /home/oracle/backup/arch/cold_20240112/' from v$logfile;cp -av /u01/app/oracle/oradata/ora11g/system01.dbf /home/oracle/backup/arch/cold_20240112/ cp -av /u01/app/oracle/oradata/ora11g/sysaux01.dbf /home/oracle/backup/arch/cold_20240112/ cp -av /u01/app/oracle/oradata/ora11g/users01.dbf /home/oracle/backup/arch/cold_20240112/ cp -av /u01/app/oracle/oradata/ora11g/example01.dbf /home/oracle/backup/arch/cold_20240112/ cp -av /u01/app/oracle/oradata/ora11g/undotbs01.dbf /home/oracle/backup/arch/cold_20240112/ cp -av /u01/app/oracle/oradata/ora11g/temp_new01.dbf /home/oracle/backup/arch/cold_20240112/ cp -av /u01/app/oracle/oradata/ora11g/control01.ctl /home/oracle/backup/arch/cold_20240112/ cp -av /u01/app/oracle/oradata/ora11g/redo03.log /home/oracle/backup/arch/cold_20240112/ cp -av /u01/app/oracle/oradata/ora11g/redo02.log /home/oracle/backup/arch/cold_20240112/ cp -av /u01/app/oracle/oradata/ora11g/redo01.log /home/oracle/backup/arch/cold_20240112/
● 3. 초기 파라미터 파일 백업
create pfile ='/home/oracle/backup/arch/cold_20240112/initora11g_20240112.ora' from spfile;● 4. 백업 디렉토리 생성
SQL> ! mkdir -p /home/oracle/backup/arch/cold_20240112/[oracle@oracle ~]$ ls /home/oracle/backup/arch/cold_20240112/
initora11g_20240112.ora● 5. db 정상종료
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !● 6. 새로운 디렉토리에 백업쉘 실행
[oracle@oracle ~]$ cd /home/oracle/backup/arch/cold_20240112/
[oracle@oracle cold_20240112]$ ls
initora11g_20240112.ora
[oracle@oracle cold_20240112]$ vi backup.sh
-- 입력
cp -av /u01/app/oracle/oradata/ora11g/system01.dbf /home/oracle/backup/arch/cold_20240112/
cp -av /u01/app/oracle/oradata/ora11g/sysaux01.dbf /home/oracle/backup/arch/cold_20240112/
cp -av /u01/app/oracle/oradata/ora11g/users01.dbf /home/oracle/backup/arch/cold_20240112/
cp -av /u01/app/oracle/oradata/ora11g/example01.dbf /home/oracle/backup/arch/cold_20240112/
cp -av /u01/app/oracle/oradata/ora11g/undotbs01.dbf /home/oracle/backup/arch/cold_20240112/
cp -av /u01/app/oracle/oradata/ora11g/temp_new01.dbf /home/oracle/backup/arch/cold_20240112/
cp -av /u01/app/oracle/oradata/ora11g/control01.ctl /home/oracle/backup/arch/cold_20240112/
cp -av /u01/app/oracle/oradata/ora11g/redo03.log /home/oracle/backup/arch/cold_20240112/
cp -av /u01/app/oracle/oradata/ora11g/redo02.log /home/oracle/backup/arch/cold_20240112/
cp -av /u01/app/oracle/oradata/ora11g/redo01.log /home/oracle/backup/arch/cold_20240112/
[oracle@oracle cold_20240112]$ sh backup.sh > backup.log
[oracle@oracle cold_20240112]$ ls
backup.log initora11g_20240112.ora sysaux01.dbf users01.dbf
backup.sh redo01.log system01.dbf
control01.ctl redo02.log temp_new01.dbf
example01.dbf redo03.log undotbs01.dbf▶일관성 없는 백업 (online backup, hotbackup, open backup)
● 1. 백업 대상 확인
● 2. 백업 쉘 쿼리문 작성
- datafile의 백업 상태
select a.file#, a.name, a.checkpoint_change#,
b.status, b.change#, b.time
from v$datafile a, v$backup b
where a.file#=b.file#;
file# name checkpoint_change# status change#
-------------------------------------------------------------------------------
1 /u01/app/oracle/oradata/ora11g/system01.dbf 1547960 NOT ACTIVE 0
2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf 1547960 NOT ACTIVE 0
4 /u01/app/oracle/oradata/ora11g/users01.dbf 1547960 NOT ACTIVE 0
5 /u01/app/oracle/oradata/ora11g/example01.dbf 1547960 NOT ACTIVE 0
6 /u01/app/oracle/oradata/ora11g/undotbs01.dbf 1547960 NOT ACTIVE 0 - 백업 쉘 쿼리문 작성
select 'cp -av '||name||' /home/oracle/backup/arch/hot_20240112/' from v$datafile
union all
select 'cp -av '||name||' /home/oracle/backup/arch/hot_20240112/' from v$tempfile;
----------------------------------
cp -av /u01/app/oracle/oradata/ora11g/system01.dbf /home/oracle/backup/arch/hot_20240112/
cp -av /u01/app/oracle/oradata/ora11g/sysaux01.dbf /home/oracle/backup/arch/hot_20240112/
cp -av /u01/app/oracle/oradata/ora11g/users01.dbf /home/oracle/backup/arch/hot_20240112/
cp -av /u01/app/oracle/oradata/ora11g/example01.dbf /home/oracle/backup/arch/hot_20240112/
cp -av /u01/app/oracle/oradata/ora11g/undotbs01.dbf /home/oracle/backup/arch/hot_20240112/
cp -av /u01/app/oracle/oradata/ora11g/temp_new01.dbf /home/oracle/backup/arch/hot_20240112/● 3. 백업 디렉토리 생성후, 백업 쉘파일 만들기
[oracle@oracle ~]$ cd backup/arch
[oracle@oracle arch]$ mkdir hot_20240112
[oracle@oracle arch]$ cd hot_20240112/
[oracle@oracle hot_20240112]$ vi backup.sh
---
cp -av /u01/app/oracle/oradata/ora11g/system01.dbf /home/oracle/backup/arch/hot_20240112/
cp -av /u01/app/oracle/oradata/ora11g/sysaux01.dbf /home/oracle/backup/arch/hot_20240112/
cp -av /u01/app/oracle/oradata/ora11g/users01.dbf /home/oracle/backup/arch/hot_20240112/
cp -av /u01/app/oracle/oradata/ora11g/example01.dbf /home/oracle/backup/arch/hot_20240112/
cp -av /u01/app/oracle/oradata/ora11g/undotbs01.dbf /home/oracle/backup/arch/hot_20240112/
cp -av /u01/app/oracle/oradata/ora11g/temp_new01.dbf /home/oracle/backup/arch/hot_20240112/● 4. checkpoint의 수위를 맞쳐주고 시작하는것이 좋다. (5번을 실행해서 굳이 할필요는 없다.)
- checkpoint 변경
alter system checkpoint;- 확인
select a.file#, a.name, a.checkpoint_change#,
b.status, b.change#, b.time
from v$datafile a, v$backup b
where a.file#=b.file#;● 5. online backup 수행한다고 begin
- full checkpoint 발생
alter database begin backup;- 확인
file# name checkpoint_change# status change# time
------------------------------------------------------------------------------------
1 /u01/app/oracle/oradata/ora11g/system01.dbf 1549712 ACTIVE 1549712 24/01/11
2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf 1549712 ACTIVE 1549712 24/01/11
4 /u01/app/oracle/oradata/ora11g/users01.dbf 1549712 ACTIVE 1549712 24/01/11
5 /u01/app/oracle/oradata/ora11g/example01.dbf 1549712 ACTIVE 1549712 24/01/11
6 /u01/app/oracle/oradata/ora11g/undotbs01.dbf 1549712 ACTIVE 1549712 24/01/11● 6. 백업쉘파일 실행 (datafile 백업)
[oracle@oracle hot_20240112]$ sh backup.sh > backup.log
[oracle@oracle hot_20240112]$ ls
backup.log example01.dbf system01.dbf undotbs01.dbf
backup.sh sysaux01.dbf temp_new01.dbf users01.dbf● 7. online backup 끝낸다고 end
alter database end backup;- 확인
select a.file#, a.name, a.checkpoint_change#,
b.status, b.change#, b.time
from v$datafile a, v$backup b
where a.file#=b.file#;
file# name checkpoint_change# status change# time
------------------------------------------------------------------------------------
/u01/app/oracle/oradata/ora11g/system01.dbf 1549712 NOT ACTIVE 1549712 24/01/11
/u01/app/oracle/oradata/ora11g/sysaux01.dbf 1549712 NOT ACTIVE 1549712 24/01/11
/u01/app/oracle/oradata/ora11g/users01.dbf 1549712 NOT ACTIVE 1549712 24/01/11
/u01/app/oracle/oradata/ora11g/example01.dbf 1549712 NOT ACTIVE 1549712 24/01/11
/u01/app/oracle/oradata/ora11g/undotbs01.dbf 1549712 NOT ACTIVE 1549712 24/01/11● 8. online 중에 controlfile backup
alter database backup controlfile to '/home/oracle/backup/arch/hot_20240112/control01.ctl';- 확인
[oracle@oracle hot_20240112]$ ls
backup.log control01.ctl sysaux01.dbf temp_new01.dbf users01.dbf
backup.sh example01.dbf system01.dbf undotbs01.dbf
[oracle@oracle hot_20240112]$ pwd
/home/oracle/backup/arch/hot_20240112● 9. archive log current로 변경
: online backup 동안 기록 못한 아카이브 파일 기록
alter system archive log current;■ 장애, 백업,복구 시나리오 - archive log mode
● 복구전에 백업본,scn번호 전부 확인
- scn 번호 확인
- 백업 받는 시점의 scn번호, 현재 데이터 파일의 scn번호
checkpoint_change# : 현재 데이터파일의 scn번호
change# : 백업 시점 데이터 파일 scn 정보select a.file#, a.name, a.checkpoint_change#, b.status, b.change#, b.time from v$datafile a, v$backup b where a.file#=b.file#; ---- file# name checkpoint_change# status change# time ------------------------------------------------------------------------------------ /u01/app/oracle/oradata/ora11g/system01.dbf 1550864 NOT ACTIVE 1549712 24/01/11 /u01/app/oracle/oradata/ora11g/sysaux01.dbf 1550864 NOT ACTIVE 1549712 24/01/11 /u01/app/oracle/oradata/ora11g/users01.dbf 1550864 NOT ACTIVE 1549712 24/01/11 /u01/app/oracle/oradata/ora11g/example01.dbf 1550864 NOT ACTIVE 1549712 24/01/11 /u01/app/oracle/oradata/ora11g/undotbs01.dbf 1550864 NOT ACTIVE 1549712 24/01/11
- 마지막 백업받은 시점의 scn번호를 기준으로 현재 scn 번호까지 리두 정보 확인
select * from v$log- 시스템상에 archive log 있는지 확인
select sequence#, name, first_change#, next_change#
from v$archived_log;- 물리적인 위치에 있는지도 확인
SQL> ! ls /home/oracle/arch1 /home/oracle/arch2
/home/oracle/arch1:
arch_1_3_1157971653.arc arch_1_4_1157971653.arc
/home/oracle/arch2:
arch_1_3_1157971653.arc arch_1_4_1157971653.arc▶ 1. 운영중 장애때문에 offline 되는 데이터파일이 손상되었을때 복구 시나리오
● 1. 장애발생 -> user 데이터파일 삭제
- users테이블스페이스에 테스트 테이블 생성
create table hr.test1(id number)
tablespace users;
insert into hr.test1(id) values(1);
commit;select * from v$log;- hr.test1 테이블 위치확인
select f.file_name
from dba_extents e, dba_data_files f
where e.file_id = f.file_id
and e.segment_name = 'TEST1'
and e.owner = 'HR';
FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ora11g/users01.dbf- 장애발생 : user데이터파일 삭제
SQL> ! rm /u01/app/oracle/oradata/ora11g/users01.dbf- 삭제하였지만, 아직 메모리에 남아있어서 조회는된다
SQL> select * from hr.test1;
ID
----------
1- users테이블스페이스에 새로운 테이블 생성 -> 에러
SQL> create table hr.test2
tablespace users
as select * from hr.employees; 2 3
as select * from hr.employees
*
ERROR at line 3:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/u01/app/oracle/oradata/ora11g/users01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3● 2. 대상 테이블 스페이스 offline immediate 변경
SQL> alter tablespace users offline immediate;
Tablespace altered.
SQL> select name, status from v$datafile;
/u01/app/oracle/oradata/ora11g/system01.dbf SYSTEM
/u01/app/oracle/oradata/ora11g/sysaux01.dbf ONLINE
/u01/app/oracle/oradata/ora11g/users01.dbf RECOVER -- recover로 변경되었다.
/u01/app/oracle/oradata/ora11g/example01.dbf ONLINE
/u01/app/oracle/oradata/ora11g/undotbs01.dbf ONLINE● 3. 가장 최근에 hot 백업 파일을 문제되는 파일 위치에 restore
SQL> ! cp -av /home/oracle/backup/arch/cold_20240112/users01.dbf /u01/app/oracle/oradata/ora11g/
‘/home/oracle/backup/arch/cold_20240112/users01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/users01.dbf’● 4. 백업이후에 변경된 redo 정보를 적용
SQL> recover tablespace users;
Media recovery complete.● 5. 대상 테이블 스페이스 online 으로 변경
SQL> alter tablespace users online;
Tablespace altered.- 확인
select name, status from v$datafile;● 6. 확인
SQL> select * from hr.test1;
ID
----------
1
SQL> create table hr.test2
tablespace users
as select * from hr.employees;
Table created.▶ 2. DB가 종료후 데이터파일이 손상되어 DB를 올릴때 시나리오, 데이터파일이 손상되어 DB가 종료되었을때
● 1. 장애발생 : user데이터파일 삭제
- db 종료
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.- user 데이터파일 삭제
SQL> ! rm /u01/app/oracle/oradata/ora11g/users01.dbf- db startup : 장애발생
SQL> startup
ORACLE instance started.
Total System Global Area 531476480 bytes
Fixed Size 1365796 bytes
Variable Size 201328860 bytes
Database Buffers 322961408 bytes
Redo Buffers 5820416 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/u01/app/oracle/oradata/ora11g/users01.dbf'
SQL> select status from v$instance;
STATUS
------------
MOUNTED
● 2. recover 대상 file 조회
select * from v$recover_file;
FILE# ONLINE ONLINE_ERROR CHANGE# TIME
------------------------------------------------
4 ONLINE ONLINE FILE NOT FOUND 0 ● 3. 대상 테이블 스페이스 offline 변경
SQL> alter database datafile '/u01/app/oracle/oradata/ora11g/users01.dbf' offline;
Database altered.- recover 대상 file 조회
select * from v$recover_file;
FILE# ONLINE ONLINE_ERROR CHANGE# TIME
------------------------------------------------
4 OFFLINE ONLINE FILE NOT FOUND 0 ● 4. db open
SQL> alter database open;
Database altered.● 5. 가장 최근에 hot 백업 파일을 문제되는 파일 위치에 restore
SQL> ! cp -av /home/oracle/backup/arch/cold_20240112/users01.dbf /u01/app/oracle/oradata/ora11g/
‘/home/oracle/backup/arch/cold_20240112/users01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/users01.dbf’● 6. 백업이후에 변경된 redo 정보를 적용
SQL> recover tablespace users;
Media recovery complete.● 7. 대상 테이블 스페이스 online 으로 변경
SQL> alter tablespace users online;
Tablespace altered.- recover file 확인
SQL> select * from v$recover_file;
no rows selected● 8. scn 맞추기
- checkpoint 확인
select file#, name, checkpoint_change#
from v$datafile;
select a.file#, a.name, a.checkpoint_change#,
b.status, b.change#, b.time
from v$datafile a, v$backup b
where a.file#=b.file#;- db shutdown abort
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 531476480 bytes
Fixed Size 1365796 bytes
Variable Size 201328860 bytes
Database Buffers 322961408 bytes
Redo Buffers 5820416 bytes
Database mounted.
Database opened.- checkpoint 확인
select file#, name, checkpoint_change#
from v$datafile;
select a.file#, a.name, a.checkpoint_change#,
b.status, b.change#, b.time
from v$datafile a, v$backup b
where a.file#=b.file#;▶ 3. DB가 종료후 데이터파일 여러개가 손상되어 DB를 올릴때 시나리오, 데이터파일이 손상되어 DB가 종료되었을때
● 1. 장애발생 : user데이터파일, example데이터 파일 삭제
- db 종료
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.- 삭제
SQL> ! rm /u01/app/oracle/oradata/ora11g/users01.dbf
SQL> ! rm /u01/app/oracle/oradata/ora11g/example01.dbf- db startup : 장애
SQL> startup
ORACLE instance started.
Total System Global Area 531476480 bytes
Fixed Size 1365796 bytes
Variable Size 201328860 bytes
Database Buffers 322961408 bytes
Redo Buffers 5820416 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/u01/app/oracle/oradata/ora11g/users01.dbf'● 2. recover file 확인
SQL> select * from v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- ---------
4 ONLINE ONLINE FILE NOT FOUND 0
5 ONLINE ONLINE FILE NOT FOUND 0● 3. 대상 테이블 스페이스 offline 변경
SQL> alter database datafile 4 offline;
Database altered.
SQL> alter database datafile 5 offline;
Database altered.
SQL> select * from v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- ---------
4 OFFLINE OFFLINE FILE NOT FOUND 0
5 OFFLINE OFFLINE FILE NOT FOUND 0● 4. db open
SQL> alter database open;
Database altered.● 5. 가장 최근에 hot 백업 파일을 문제되는 파일 위치에 restore
SQL> ! cp -av /home/oracle/backup/arch/cold_20240112/users01.dbf /u01/app/oracle/oradata/ora11g/
‘/home/oracle/backup/arch/cold_20240112/users01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/users01.dbf’
SQL> ! cp -av /home/oracle/backup/arch/cold_20240112/example01.dbf /u01/app/oracle/oradata/ora11g/
‘/home/oracle/backup/arch/cold_20240112/example01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/example01.dbf’
● 6. 백업이후에 변경된 redo 정보를 적용 user테이블스페이스
db level로
recover tablespace테이블스페이스명;
이렇게 해야한다.
recover database;-> 하면 안된다.
1번경우
SQL> recover tablespace users; Media recovery complete.2번경우 : logswitch가 발생한것이다.
SQL> recover tablespace users; Specify log: {<RET>=suggested | filename | AUTO | CANCEL} AUTO -- 입력
● 7. 대상 user테이블 스페이스 online 으로 변경
SQL> alter tablespace users online;
Tablespace altered.SQL> select * from v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- ---------
5 OFFLINE OFFLINE 1547957 11-JAN-24● 8. 백업이후에 변경된 redo 정보를 적용 user테이블스페이스, 대상 user테이블 스페이스 online 으로 변경
- redo 정보를 적용
SQL> recover tablespace example;
Media recovery complete.- 대상 user테이블 스페이스 online 으로 변경
SQL> alter tablespace example online;
Tablespace altered.- 확인
SQL> select * from v$recover_file;
no rows selected2023/01/15
● 백업 대상 확인할것
- 테이블스페이스의 마지막 체크포인트 정보
select a.file#, b.name tbs_name, a.name file_name,
a.status, a.checkpoint_change#
from v$datafile a, v$tablespace b
where a.ts# = b.ts#;- 백업된 datafile 확인
select a.file#, a.name, a.checkpoint_change#,
b.status, b.change#, to_char(b.time,'yyyy/mm/dd hh24:mi:ss')
from v$datafile a, v$backup b
where a.file#=b.file#;- tempfile 확인
select * from database_properties;
select * from v$tempfile;- redolog file 확인
select * from v$log;- 아카이브 로그 확인
archive log list
select destination, binding, status from v$archive_dest;
select sequence#, name, first_change#, next_change#, next_time
from v$archived_log;- 아카이브 파일 확인
SQL> ! ls /home/oracle/arch1 /home/oracle/arch2
/home/oracle/arch1:
arch_1_3_1157971653.arc arch_1_5_1157971653.arc arch_1_7_1157971653.arc
arch_1_4_1157971653.arc arch_1_6_1157971653.arc
/home/oracle/arch2:
arch_1_3_1157971653.arc arch_1_5_1157971653.arc arch_1_7_1157971653.arc
arch_1_4_1157971653.arc arch_1_6_1157971653.arc▶ 4. 백업 받지 않은 테이블스페이스에 데이터 파일 손상 시나리오
● 1. 작업
- 테이블스페이스 생성
create tablespace data01
datafile '/u01/app/oracle/oradata/ora11g/data01.dbf' size 5m;- 테이블 생성
create table hr.dept_temp tablespace data01
as
select * from hr.departments;- 로그스위치 발생
alter system switch logfile;- 아카이브 로그 확인
select sequence#, name, first_change#, next_change#, next_time
from v$archived_log;- 테이블 만들어진 데이터파일 확인
select f.file_name
from dba_extents e, dba_data_files f
where e.file_id = f.file_id
and e.segment_name = 'DEPT_TEMP'
and e.owner = 'HR';● 2. 장애발생 : datafile 삭제
SQL> ! rm /u01/app/oracle/oradata/ora11g/data01.dbf
SQL> shutdown immediate
ORA-01116: error in opening database file 3
ORA-01110: data file 3: '/u01/app/oracle/oradata/ora11g/data01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 531476480 bytes
Fixed Size 1365796 bytes
Variable Size 201328860 bytes
Database Buffers 322961408 bytes
Redo Buffers 5820416 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/u01/app/oracle/oradata/ora11g/data01.dbf'● 3. recover file 확인후, offline, db open
- recover file 확인
select * from v$recover_file;- 문제되는 datafile offilne 으로 변경
alter database datafile 3 offline;
alter database datafile '/u01/app/oracle/oradata/ora11g/data01.dbf' offline;- datafile 상태확인
select file#, name, status from v$datafile;
---
1 /u01/app/oracle/oradata/ora11g/system01.dbf SYSTEM
2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf ONLINE
3 /u01/app/oracle/oradata/ora11g/data01.dbf RECOVER
4 /u01/app/oracle/oradata/ora11g/users01.dbf ONLINE
5 /u01/app/oracle/oradata/ora11g/example01.dbf ONLINE
6 /u01/app/oracle/oradata/ora11g/undotbs01.dbf ONLINE- db open
SQL> alter database open;
Database altered.● 3. 복구작업 수행해야할 기존 디렉토리에 데이터파일 재생성
alter database create datafile '/u01/app/oracle/oradata/ora11g/data01.dbf';
- 재생성 했는데 그대로 5m 인 이유
: 딕셔너리와 컨트롤파일이 테이블스페이스 생성시 정보를 다 가지고있으므로
- 확인
select file#, name, status from v$datafile;만약 기존위치 디스크가 문제가 있다면 새로운위치에 만든다.
- 기존위치가 아닌 새로운 디렉토리에 데이터파일 재생성
alter database create datafile '/u01/app/oracle/oradata/ora11g/data01.dbf' as '/home/oracle/data01.dbf';
● 4. recover 테이블스페이스
SQL> recover tablespace data01;
ORA-00279: change 1605680 generated at 01/12/2024 03:28:57 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch2/arch_1_8_1157971653.arc
ORA-00280: change 1605680 for thread 1 is in sequence #8
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto -- auto 입력
ORA-00279: change 1606178 generated at 01/12/2024 03:34:05 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch2/arch_1_9_1157971653.arc
ORA-00280: change 1606178 for thread 1 is in sequence #9
Log applied.
Media recovery complete.● 5. 테이블스페이스 online으로 변경
SQL> alter tablespace data01 online;
Tablespace altered.- datafile 확인
select file#, name, status from v$datafile;- 생성했던 테이블 조회
SQL> select count(*) from hr.dept_temp;
COUNT(*)
----------
27▶ 5. 테이블스페이스에 데이터 파일 손상시 기존 위치가 아닌 새로운 위치로 복원 작업 시나리오
● 1. 작업 : hot backup, table 생성
- table 만들기
create table hr.emp_temp tablespace data01
as
select * from hr.employees;
select count(*) from hr.emp_temp;- hot backup
SQL> alter tablespace data01 begin backup;
Tablespace altered.
SQL> ! cp -av /u01/app/oracle/oradata/ora11g/data01.dbf /home/oracle/backup/arch/hot_20240112/
‘/u01/app/oracle/oradata/ora11g/data01.dbf’ -> ‘/home/oracle/backup/arch/hot_20240112/data01.dbf’
SQL> alter tablespace data01 end backup;
Tablespace altered.- 백업 확인
select a.file#, a.name, a.checkpoint_change#,
b.status, b.change#, to_char(b.time,'yyyy/mm/dd hh24:mi:ss')
from v$datafile a, v$backup b
where a.file#=b.file#;
select * from v$log;- table 만들기
create table hr.loc_temp tablespace data01
as
select * from hr.locations;
select f.file_name
from dba_extents e, dba_data_files f
where e.file_id = f.file_id
and e.segment_name = 'LOC_TEMP'
and e.owner = 'HR';- 로그 스위치 발생
alter system switch logfile;● 2. 장애발생 : datafile 삭제
SQL> ! rm /u01/app/oracle/oradata/ora11g/data01.dbf
● 3. 문제되는것 offline immediate 변경
SQL> alter tablespace data01 offline immediate;
Tablespace altered.- datafile 확인
select name,status from v$datafile;● 4. 기존 위치가 아닌 새로운 위치로 restore
SQL> ! cp -av /home/oracle/backup/arch/hot_20240112/data01.dbf /home/oracle
‘/home/oracle/backup/arch/hot_20240112/data01.dbf’ -> ‘/home/oracle/data01.dbf’● 5. control file 에게 위치변경 작업
alter database rename file '이전경로의 파일' to '새로운경로의 파일';
alter database rename file
'/u01/app/oracle/oradata/ora11g/data01.dbf' to
'/home/oracle/data01.dbf';● 6. 아카이브 적용
SQL> recover tablespace data01;
ORA-00279: change 1629177 generated at 01/12/2024 04:31:27 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch2/arch_1_12_1157971653.arc
ORA-00280: change 1629177 for thread 1 is in sequence #12
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
Log applied.
Media recovery complete.● 7. tablespace online으로 변경
SQL> alter tablespace data01 online;
Tablespace altered.- 테이블 확인
SQL> select count(*) from hr.loc_temp;
COUNT(*)
----------
23▶ 6. 테이블스페이스에 속한 여러 데이터파일들 중에 특정한이 손상시나리오 (offline temporary)
● 1. 작업
- 테이블스페이스에 datafile 추가
alter tablespace data01 add datafile '/u01/app/oracle/oradata/ora11g/data02.dbf' size 10m;- 확인
select tablespace_name, file_name
from dba_data_files;- 테이블생성
create table hr.emp_2024 tablespace data01
as
select * from hr.employees;- 어느 datafile인지 확인
select f.file_name
from dba_extents e, dba_data_files f
where e.file_id = f.file_id
and e.segment_name = 'EMP_2024'
and e.owner = 'HR';- 대용량 데이터를 위해서 자기자신 insert 여러번
insert into hr.emp_2024
select * from hr.emp_2024;- segment 사이즈 확인
select bytes
from dba_segments
where segment_name = 'EMP_2024'
and owner = 'HR';- datafile에 extent가 몇개 만들어졌는지
select f.tablespace_name, f.file_name, count(*)
from dba_extents e, dba_data_files f
where e.file_id = f.file_id
and e.segment_name = 'EMP_2024'
and e.owner = 'HR'
group by f.tablespace_name, f.file_name;- hot backup 받기
SQL> alter tablespace data01 begin backup;
Tablespace altered.
SQL> ! cp -av /u01/app/oracle/oradata/ora11g/data02.dbf /home/oracle/backup/arch/hot_20240112/
‘/u01/app/oracle/oradata/ora11g/data02.dbf’ -> ‘/home/oracle/backup/arch/hot_20240112/data02.dbf’
SQL> alter tablespace data01 end backup;
Tablespace altered.- 로그스위치 발생
alter system switch logfile;- 테이블생성
create table hr.dept_2024 tablespace data01
as
select * from hr.departments;- 생성된 테이블이 어디 datafile인지 확인
select f.tablespace_name, f.file_name, count(*)
from dba_extents e, dba_data_files f
where e.file_id = f.file_id
and e.segment_name = 'DEPT_2024'
and e.owner = 'HR'
group by f.tablespace_name, f.file_name;
------
DATA01 /u01/app/oracle/oradata/ora11g/data02.dbf 1● 2. 장애발생 : datafile 삭제
SQL> ! rm /u01/app/oracle/oradata/ora11g/data02.dbf● 3. 테이블스페이스에 속한 데이터파일을 offline temporary
select a.file#, b.name tbs_name, a.name file_name,
a.status, a.checkpoint_change#
from v$datafile a, v$tablespace b
where a.ts# = b.ts#;
---------
1 SYSTEM /u01/app/oracle/oradata/ora11g/system01.dbf SYSTEM 1633230
2 SYSAUX /u01/app/oracle/oradata/ora11g/sysaux01.dbf ONLINE 1633230
3 DATA01 /home/oracle/data01.dbf ONLINE 1633230
4 USERS /u01/app/oracle/oradata/ora11g/users01.dbf ONLINE 1633230
5 EXAMPLE /u01/app/oracle/oradata/ora11g/example01.dbf ONLINE 1633230
6 UNDOTBS /u01/app/oracle/oradata/ora11g/undotbs01.dbf ONLINE 1633230
7 DATA01 /u01/app/oracle/oradata/ora11g/data02.dbf ONLINE 1633230
- offline temporary
: 테이블스페이스에 속한 데이터파일을 offline 으로 수행하되 가능한 데이터파일은 체크포인트 발생하고 가능하지 않은 데이터파일은 그냥 offline 으로 수행하라는 의도SQL> alter tablespace data01 offline temporary; Tablespace altered.
select a.file#, b.name tbs_name, a.name file_name,
a.status, a.checkpoint_change#
from v$datafile a, v$tablespace b
where a.ts# = b.ts#;
---------
1 SYSTEM /u01/app/oracle/oradata/ora11g/system01.dbf SYSTEM 1633230
2 SYSAUX /u01/app/oracle/oradata/ora11g/sysaux01.dbf ONLINE 1633230
3 DATA01 /home/oracle/data01.dbf OFFLINE 1637372
4 USERS /u01/app/oracle/oradata/ora11g/users01.dbf ONLINE 1633230
5 EXAMPLE /u01/app/oracle/oradata/ora11g/example01.dbf ONLINE 1633230
6 UNDOTBS /u01/app/oracle/oradata/ora11g/undotbs01.dbf ONLINE 1633230
7 DATA01 /u01/app/oracle/oradata/ora11g/data02.dbf OFFLINE 1637372● 4. 백업본을 이용하여 restore
SQL> ! cp -av /home/oracle/backup/arch/hot_20240112/data02.dbf /u01/app/oracle/oradata/ora11g/data02.dbf
‘/home/oracle/backup/arch/hot_20240112/data02.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/data02.dbf’● 5. 아카이브 적용
SQL> recover tablespace data01;
ORA-00279: change 1629177 generated at 01/12/2024 04:31:27 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch2/arch_1_12_1157971653.arc
ORA-00280: change 1629177 for thread 1 is in sequence #12
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
Log applied.
Media recovery complete.● 6. tablespace online으로 변경
SQL> alter tablespace data01 online;
Tablespace altered.- 테이블확인
select count(*) from hr.dept_2024;▶ 7. 시스템데이터파일 손상 시나리오
● 1. 장애발생 : system 데이터파일 삭제
- 백업 데이터파일 확인
select a.file#, a.name, a.checkpoint_change#,
b.status, b.change#, to_char(b.time,'yyyy/mm/dd hh24:mi:ss')
from v$datafile a, v$backup b
where a.file#=b.file#;- 장애발생 : system 데이터파일 삭제
SQL> ! rm /u01/app/oracle/oradata/ora11g/system01.dbf
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 531476480 bytes
Fixed Size 1365796 bytes
Variable Size 201328860 bytes
Database Buffers 322961408 bytes
Redo Buffers 5820416 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/u01/app/oracle/oradata/ora11g/system01.dbf'
SQL> select * from v$recover_file;
---
1 ONLINE ONLINE FILE NOT FOUND 0 ● 2. 백업본을 이용하여 restore
SQL> ! cp -av /home/oracle/backup/arch/hot_20240112/system01.dbf /u01/app/oracle/oradata/ora11g/system01.dbf
‘/home/oracle/backup/arch/hot_20240112/system01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/system01.dbf’● 3. recover 테이블스페이스 (redo log 적용)
SQL> recover tablespace system;
ORA-00279: change 1581340 generated at 01/12/2024 02:36:49 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch2/arch_1_6_1157971653.arc
ORA-00280: change 1581340 for thread 1 is in sequence #6
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 1581433 generated at 01/12/2024 02:38:15 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch2/arch_1_7_1157971653.arc
ORA-00280: change 1581433 for thread 1 is in sequence #7
ORA-00279: change 1601618 generated at 01/12/2024 02:42:43 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch2/arch_1_8_1157971653.arc
ORA-00280: change 1601618 for thread 1 is in sequence #8
ORA-00279: change 1606178 generated at 01/12/2024 03:34:05 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch2/arch_1_9_1157971653.arc
ORA-00280: change 1606178 for thread 1 is in sequence #9
ORA-00279: change 1606181 generated at 01/12/2024 03:34:06 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch2/arch_1_10_1157971653.arc
ORA-00280: change 1606181 for thread 1 is in sequence #10
ORA-00279: change 1606185 generated at 01/12/2024 03:34:09 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch2/arch_1_11_1157971653.arc
ORA-00280: change 1606185 for thread 1 is in sequence #11
ORA-00279: change 1626796 generated at 01/12/2024 03:49:31 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch2/arch_1_12_1157971653.arc
ORA-00280: change 1626796 for thread 1 is in sequence #12
ORA-00279: change 1629556 generated at 01/12/2024 04:39:15 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch2/arch_1_13_1157971653.arc
ORA-00280: change 1629556 for thread 1 is in sequence #13
ORA-00279: change 1629559 generated at 01/12/2024 04:39:18 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch2/arch_1_14_1157971653.arc
ORA-00280: change 1629559 for thread 1 is in sequence #14
ORA-00279: change 1629562 generated at 01/12/2024 04:39:19 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch2/arch_1_15_1157971653.arc
ORA-00280: change 1629562 for thread 1 is in sequence #15
Log applied.
Media recovery complete.● 4. db open
SQL> alter database open;
Database altered.- recover file 확인
SQL> select * from v$recover_file;
no rows selected- 확인
select a.file#, a.name, a.checkpoint_change#,
b.status, b.change#, to_char(b.time,'yyyy/mm/dd hh24:mi:ss')
from v$datafile a, v$backup b
where a.file#=b.file#;▶ 8. 모든 데이터파일 손상 시나리오
● 1. 장애유발 : 모든 데이터 파일 삭제
SQL> ! rm /u01/app/oracle/oradata/ora11g/*.dbf
SQL> ! ls /u01/app/oracle/oradata/ora11g/*.dbf
ls: cannot access /u01/app/oracle/oradata/ora11g/*.dbf: No such file or directory- DB 내려감
SQL> alter system checkpoint;
alter system checkpoint
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 7893
Session ID: 9 Serial number: 3- 세션이 종료되었기 때문에 바로 startup 불가
SQL> startup
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist- 새로운 세션에 접속
SQL> conn / as sysdba
Connected to an idle instance.- 오류 확인
SQL> startup
ORACLE instance started.
Total System Global Area 828608512 bytes
Fixed Size 1367792 bytes
Variable Size 545259792 bytes
Database Buffers 276824064 bytes
Redo Buffers 5156864 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/u01/app/oracle/oradata/ora11g/system01.dbf'- recover file 확인
★ 어떤 데이터 파일에 문제가 발생한 것인지 v$recover_file 로 꼭 확인해봐야 한다!
SQL> SELECT * FROM v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- --------------- ---------- ---------
1 ONLINE ONLINE FILE NOT FOUND 0
2 ONLINE ONLINE FILE NOT FOUND 0
4 ONLINE ONLINE FILE NOT FOUND 0
5 ONLINE ONLINE FILE NOT FOUND 0
6 ONLINE ONLINE FILE NOT FOUND 0
7 ONLINE ONLINE FILE NOT FOUND 0● 2. 백업 파일 restore 작업
SQL> ! cp -av /home/oracle/backup/arch/hot_20240112/*.dbf /u01/app/oracle/oradata/ora11g
‘/home/oracle/backup/arch/hot_20240112/data01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/data01.dbf’
‘/home/oracle/backup/arch/hot_20240112/data02.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/data02.dbf’
‘/home/oracle/backup/arch/hot_20240112/example01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/example01.dbf’
‘/home/oracle/backup/arch/hot_20240112/sysaux01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/sysaux01.dbf’
‘/home/oracle/backup/arch/hot_20240112/system01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/system01.dbf’
‘/home/oracle/backup/arch/hot_20240112/temp_new01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/temp_new01.dbf’
‘/home/oracle/backup/arch/hot_20240112/undotbs01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/undotbs01.dbf’
‘/home/oracle/backup/arch/hot_20240112/users01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/users01.dbf’
SQL> ! ls /u01/app/oracle/oradata/ora11g
control01.ctl data02.dbf redo01.log redo03.log system01.dbf undotbs01.dbf
data01.dbf example01.dbf redo02.log sysaux01.dbf temp_new01.dbf users01.dbf● 3. 복구 하기 -> 모든 파일을 복구 하는 것이기 때문에 DATABASE 레벨로
SQL> RECOVER DATABASE;
ORA-00279: change 1835109 generated at 01/12/2024 16:02:01 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch2/arch_1_7_1157971653.arc
ORA-00280: change 1835109 for thread 1 is in sequence #7
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
...
Log applied.
Media recovery complete.● 4. DB OPEN 하기
SQL> ALTER DATABASE OPEN;
Database altered.- 복구해야할 데이터 파일 있는지
SQL> SELECT * FROM v$recover_file;
no rows selected- data01 테이블 스페이스 삭제
SQL> DROP TABLESPACE data01 INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped◈ cold backup, hot backup 다시 복습 (오늘 날짜로)
- [ cold backup, close backup, offline backup, 일관성 백업]
cold backup후
지난 coldbackup 삭제
아카이브로그 삭제
- [ hotbackup, openbackup, online backup, 일관성없는 백업]
▶ 9. 아카이브 파일이 다른 위치에 있을 경우 복구
● 1. 작업 : archive log 기록 남게한후, 아카이브 로그파일 삭제
- 테이블생성, 로그스위치발생
: archive log 기록 남게한후
alter system switch logfile;
create table hr.new_2024
as
select * from hr.employees;
alter system switch logfile;
create table hr.old_2024
as
select * from hr.employees;
alter system switch logfile;- 아카이브 로그파일 옮기고, 지정된 아카이브로그 파일 삭제
[oracle@oracle archive_file]$ mv /home/oracle/arch2/*.* .
[oracle@oracle archive_file]$ ls
arch_1_2_1158049110.arc arch_1_4_1158049110.arc arch_1_6_1158049110.arc
arch_1_3_1158049110.arc arch_1_5_1158049110.arc
[oracle@oracle archive_file]$ ls /home/oracle/arch2
[oracle@oracle archive_file]$ ls /home/oracle/arch1
arch_1_3_1158049110.arc arch_1_5_1158049110.arc
arch_1_4_1158049110.arc arch_1_6_1158049110.arc
[oracle@oracle archive_file]$ rm /home/orace/arch1/*.*
rm: cannot remove ‘/home/orace/arch1/*.*’: No such file or directory
[oracle@oracle archive_file]$ rm /home/oracle/arch1/*.*
[oracle@oracle archive_file]$ ls
arch_1_2_1158049110.arc arch_1_4_1158049110.arc arch_1_6_1158049110.arc
arch_1_3_1158049110.arc arch_1_5_1158049110.arc
[oracle@oracle archive_file]$ ls /home/oracle/arch1 /home/oracle/arch2
/home/oracle/arch1:
/home/oracle/arch2:● 2. 장애 발생
[oracle@oracle archive_file]$ rm /u01/app/oracle/oradata/ora11g/example01.dbf
[oracle@oracle archive_file]$ exit
exit
SQL> startup
ORACLE instance started.
Total System Global Area 531476480 bytes
Fixed Size 1365796 bytes
Variable Size 201328860 bytes
Database Buffers 322961408 bytes
Redo Buffers 5820416 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/u01/app/oracle/oradata/ora11g/example01.dbf'- offiline
SQL> select * from v$recover_file;
SQL> alter database datafile 5 offline;
Database altered.
SQL> alter database open;
Database altered.● 3. 백업본을 이용하여 restore
SQL> ! cp -av /home/oracle/backup/arch/hot_20240115/example01.dbf /u01/app/oracle/oradata/ora11g/
‘/home/oracle/backup/arch/hot_20240115/example01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/example01.dbf’● 4. recover
SQL> recover tablespace example;
ORA-00279: change 1584587 generated at 01/12/2024 08:58:20 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch2/arch_1_1_1158049110.arc
ORA-00280: change 1584587 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/home/oracle/archive_file/arch_1_2_1158049110.arc
/home/oracle/archive_file/arch_1_4_1158049110.arc
/home/oracle/archive_file/arch_1_6_1158049110.arc
/home/oracle/archive_file/arch_1_3_1158049110.arc
/home/oracle/archive_file/arch_1_5_1158049110.arc
하나씩 넣어준다.▶ 10. redolog file백업 안햇을때, 모든 데이터파일, 컨트롤파일 손상시 시나리오 (cold backup 이용)
- db 정상종료
shutdown immediate- backup본으로 restore
! cp -av /home/oracle/backup/arch/cold_20240115/*.dbf /u01/app/oracle/oradata/ora11g/
! cp -av /home/oracle/backup/arch/cold_20240115/*.ctl /u01/app/oracle/oradata/ora11g/
! cp -av /home/oracle/backup/arch/cold_20240115/initora11g_0115.ora /u01/app/oracle/product/11.2.0.4/db_1/dbs/initora11g.ora- 아카이브 로그 다 지우기
cd ar
- pfile로 열고, cancelbase recover, resetlogs
SQL> startup pfile='$ORACLE_HOME/dbs/initora11g.ora' mount
ORACLE instance started.
Total System Global Area 531476480 bytes
Fixed Size 1365796 bytes
Variable Size 201328860 bytes
Database Buffers 322961408 bytes
Redo Buffers 5820416 bytes
Database mounted.
SQL> recover database until cancel using backup controlfile;
ORA-00279: change 1584058 generated at 01/12/2024 08:50:16 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch2/arch_1_1_1158049110.arc
ORA-00280: change 1584058 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;
Database altered.
- spfile 생성
create spfile from pfile;2023/01/16
▶ 11. db down시에 undo 데이터파일 손상시 시나리오 (hot backup 이용)
- backup본 확인
select a.file#, a.name, a.checkpoint_change#,
b.status, b.change#, b.time
from v$datafile a, v$backup b
where a.file#=b.file#;- undo segments 확인
select segment_id, segment_name, owner, tablespace_name, status
from dba_rollback_segs;[hr session]
update hr.employees
set salary = 2000
where employee_id = 100;
[sys session]
select s.username, s.sid, s.serial#, r.name,
t.xidusn, t.ubafil, t.ubablk, t.used_ublk
from v$session s, v$transaction t, v$rollname r
where s.taddr = t.addr
and t.xidusn = r.usn;● 1. 장애발생 : db down후 undo 데이터파일 삭제
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> ! rm /u01/app/oracle/oradata/ora11g/undotbs01.dbf
SQL> startup
ORACLE instance started.
Total System Global Area 531476480 bytes
Fixed Size 1365796 bytes
Variable Size 201328860 bytes
Database Buffers 322961408 bytes
Redo Buffers 5820416 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/u01/app/oracle/oradata/ora11g/undotbs01.dbf'- recover file 확인
SQL> select * from v$recover_file;
FILE# ONLINE ONLINE_
---------- ------- -------
ERROR CHANGE#
----------------------------------------------------------------- ----------
TIME
---------
6 ONLINE ONLINE
FILE NOT FOUND 0
● 2. 백업본으로 restore
undo, system 테이블스페이스는 offline으로 변경 불가하므로
mount단계에서 복구해야한다.
SQL> ! cp -av /home/oracle/backup/arch/hot_20240115/undotbs01.dbf /u01/app/oracle/oradata/ora11g/undotbs01.dbf
‘/home/oracle/backup/arch/hot_20240115/undotbs01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/undotbs01.dbf’● 3. archive file, redologfile 로 recove하기
SQL> recover tablespace undotbs;
ORA-00279: change 1588474 generated at 01/12/2024 11:57:07 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch2/arch_1_1_1158057974.arc
ORA-00280: change 1588474 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
Log applied.
Media recovery complete.● 4. db open
SQL> alter database open;
Database altered.
SQL> select salary from hr.employees where employee_id =100;
SALARY
----------
24000▶ 12. 운영중에 undo 데이터파일 손상시 시나리오 (새로운 undo 만들기)
● 1. 작업 : 트랜잭션작업
- undo segment 확인
select segment_id, segment_name, owner, tablespace_name, status
from dba_rollback_segs;- 트랜잭션 작업
[hr session]
update hr.employees
set salary = 2000
where employee_id = 100;- 트랜잭션확인
[sys session]
select s.username, s.sid, s.serial#, r.name,
t.xidusn, t.ubafil, t.ubablk, t.used_ublk
from v$session s, v$transaction t, v$rollname r
where s.taddr = t.addr
and t.xidusn = r.usn;● 2. 장애발생 : 운영중 undo 데이터파일 삭제
SQL> ! rm /u01/app/oracle/oradata/ora11g/undotbs01.dbf- alert log 창에 error 뜬게 보인다
[oracle@oracle trace]$ tail -F alert_ora11g.log
---
Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_j000_16331.trc:
ORA-12012: error on auto execute of job 3
ORA-01116: error in opening database file 6
ORA-01110: data file 6: '/u01/app/oracle/oradata/ora11g/undotbs01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3● 3. default undo 테이블스페이스 변경
- 새로운 undo tablespace 생성
create undo tablespace undo_new datafile
'/u01/app/oracle/oradata/ora11g/undo_new01.dbf' size 10m autoextend on;- undo segments 확인
select segment_id, segment_name, owner, tablespace_name, status
from dba_rollback_segs;- 새로운 undo 테이블스페이스 변경
alter system set undo_tablespace = UNDO_NEW;- 변경된것 확인
show parameter undo;- 기존 undo는 offline으로, 변경된 undo는 online 확인
트랜잭션이 남아있어서 기존 undo segment 하나가 online 이다.
select segment_id, segment_name, owner, tablespace_name, status
from dba_rollback_segs;- 트랜잭션 확이
select s.username, s.sid, s.serial#, r.name,
t.xidusn, t.ubafil, t.ubablk, t.used_ublk
from v$session s, v$transaction t, v$rollname r
where s.taddr = t.addr
and t.xidusn = r.usn;● 4. 작업
- 다른 세션에서 트랜잭션 작업
[hr session]
update hr.employees
set salary = 2000
where employee_id = 200;- 트랜잭션확인
: 서로 다른 undo segment 에서 트랜잭션이 걸려있다.
[sys session]
select s.username, s.sid, s.serial#, r.name,
t.xidusn, t.ubafil, t.ubablk, t.used_ublk
from v$session s, v$transaction t, v$rollname r
where s.taddr = t.addr
and t.xidusn = r.usn;
● 5. 기존 undo segments 상태보고 해당 session kill
- segments 상태 확인
SQL> select a.name, b.status
from v$rollname a, v$rollstat b
where a.usn = b.usn; 2 3
NAME STATUS
------------------------------ ---------------
SYSTEM ONLINE
_SYSSMU7_4232614060$ PENDING OFFLINE
_SYSSMU11_750594733$ ONLINE
_SYSSMU12_2450822632$ ONLINE
_SYSSMU13_3547298789$ ONLINE
_SYSSMU14_216201895$ ONLINE
_SYSSMU15_4153513860$ ONLINE
_SYSSMU16_2784954839$ ONLINE
_SYSSMU17_2317241280$ ONLINE
_SYSSMU18_689459114$ ONLINE
_SYSSMU19_4003129141$ ONLINE
NAME STATUS
------------------------------ ---------------
_SYSSMU20_1610075169$ ONLINE- 트랜젝션 걸려있는 SID,SERIAL# 확인
select s.username, s.sid, s.serial#, r.name,
t.xidusn, t.ubafil, t.ubablk, t.used_ublk
from v$session s, v$transaction t, v$rollname r
where s.taddr = t.addr
and t.xidusn = r.usn;- 기존 hr session 킬
alter system kill session '21,61' immediate● 5. 기존 undo 테이블스페이스 삭제
SQL> drop tablespace undotbs including contents and datafiles;
Tablespace dropped.
- 만약에 기존 undo 테이블스페이스를 삭제 할 수 없을 경우
: 그 테이블 스페이스 어떤 undo segment가 PENDING OFFLINE 이면 삭제 할 수 없다. -> 7번으로 간다
● 6. spfile을 이용하여 pfile 생성
create pfile from spfile;번외
● 7. 기존 undo 테이블스페이스 삭제에러
drop tablespace undotbs including contents and datafiles
오류 보고 -
ORA-30013: 실행 취소 테이블스페이스 'UNDOTBS'은(는) 현재 사용 중임
30013. 00000 - "undo tablespace '%s' is currently in use"- pfile 생성
create pfile='$ORACLE_HOME/dbs/initora11g_20240116.ora' from spfile;- shut down
shutdown immediate- pfile 수정 (_SYSSMU7_4232614060$ PENDING OFFLINE 으로 되어있는거 offline으로 수정)
vi initora11g_20240116.ora
---
_offline_rollback_segments=(_SYSSMU7_4232614060$)- pfile로 db 열기
startup pfile=$ORACLE_HOME/dbs/initora11g_20240116.ora번외
- pfile로 db 열때 open할때 에러
alter database datafile '/u01/app/oracle/oradata/ora11g/undotbs01.dbf' offline;
alter database open;
drop tablespace undotbs including contents and datafiles;▶ 13. 특정 데이터 파일과 아카이브 파일이 손상되었을 경우 불완전 복구를 수행
● 백업 확인
- 일관성 있는 백업정보
- 일관성 없는 백업정보
- 리두정보
- 아카이브정보
select a.file#, a.name, a.checkpoint_change#,
b.status, b.change#, to_char(b.time,'yyyy/mm/dd hh24:mi:ss')
from v$datafile a, v$backup b
where a.file#=b.file#;- 작업
drop table hr.new_2024 purge;
create table hr_new_2024
as select * from hr.employees;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;- 아카이브파일, 리두로그파일 확인
select sequence#, name, first_change#, next_change#
from v$archived_log;
select * from v$log;- OS 아카이브 파일 확인
SQL> ! ls /home/oracle/arch1 /home/oracle/arch2/
/home/oracle/arch1:
arch_1_1_1158057974.arc arch_1_3_1158057974.arc arch_1_6_1158057974.arc
arch_1_2_1158057974.arc arch_1_4_1158057974.arc
/home/oracle/arch2/:
arch_1_1_1158057974.arc arch_1_3_1158057974.arc arch_1_6_1158057974.arc
arch_1_2_1158057974.arc arch_1_4_1158057974.arc● 1. 장애발생 : 아카이브파일 삭제, example 데이터파일 삭제
SQL> ! rm /home/oracle/arch1/*.*
SQL> ! rm /home/oracle/arch2/*.*
SQL> ! rm /u01/app/oracle/oradata/ora11g/example01.dbf
SQL> alter system checkpoint;
alter system checkpoint
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 27362
Session ID: 162 Serial number: 5
SQL> conn / as sysdba
Connected to an idle instance.
SQL> shutdown immediate
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory
SQL> startup
ORACLE instance started.
Total System Global Area 531476480 bytes
Fixed Size 1365796 bytes
Variable Size 201328860 bytes
Database Buffers 322961408 bytes
Redo Buffers 5820416 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/u01/app/oracle/oradata/ora11g/example01.dbf'● 2. 문제되는 데이터파일 offline 상태로 만들고, db open
- recover file 조회
SQL> select * from v$recover_file;
FILE# ONLINE ONLINE_
---------- ------- -------
ERROR CHANGE#
----------------------------------------------------------------- ----------
TIME
---------
5 ONLINE ONLINE
FILE NOT FOUND 0
- datafile offline으로 변경 db open
SQL> alter database datafile 5 offline;
Database altered.- db open
SQL> alter database open;● 3. hot backup본을 이용하여 restore 후 recover
- backup 본으로 datafile restore
SQL> ! cp -av /home/oracle/backup/arch/hot_20240116/example01.dbf /u01/app/oracle/oradata/ora11g/example01.dbf
‘/home/oracle/backup/arch/hot_20240116/example01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/example01.dbf’● 4. recover -> 장애발생 : 아카이브파일, 리두로그파일에 없으므로
- archive 파일, redo logfile 없으므로 recover 이 안된다.
SQL> recover tablespace example
ORA-00279: change 1588474 generated at 01/12/2024 11:57:07 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch2/arch_1_1_1158057974.arc
ORA-00280: change 1588474 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log
'/home/oracle/arch2/arch_1_1_1158057974.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00308: cannot open archived log
'/home/oracle/arch2/arch_1_1_1158057974.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3- cancel base recover 해도 안된다.
SQL> recover tablespace example
ORA-00279: change 1588474 generated at 01/12/2024 11:57:07 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch2/arch_1_1_1158057974.arc
ORA-00280: change 1588474 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter tablespace example online;
alter tablespace example online
*
ERROR at line 1:
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: '/u01/app/oracle/oradata/ora11g/example01.dbf'불완전한 복구는 테이블스페이스 레벨이 아닌 데이터베이스 레벨에서 수행 해야한다. 아카이브 파일이 손상되었을 경우 전체 데이터베이스를 과거 시간으로 되돌아 가야하는 복구 방식을 수행해야 한다.
● 5. db 내려주고 , restore
SQL> shutdown abort
ORACLE instance shut down.- 백업 데이터파일로 restore
SQL> ! cp -av /home/oracle/backup/arch/cold_20240115/*.dbf /u01/app/oracle/oradata/ora11g/
‘/home/oracle/backup/arch/cold_20240115/example01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/example01.dbf’
‘/home/oracle/backup/arch/cold_20240115/sysaux01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/sysaux01.dbf’
‘/home/oracle/backup/arch/cold_20240115/system01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/system01.dbf’
‘/home/oracle/backup/arch/cold_20240115/temp01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/temp01.dbf’
‘/home/oracle/backup/arch/cold_20240115/undotbs01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/undotbs01.dbf’
‘/home/oracle/backup/arch/cold_20240115/users01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/users01.dbf’● 6. mount 단계 까지 db start
SQL> startup mount
ORACLE instance started.
Total System Global Area 531476480 bytes
Fixed Size 1365796 bytes
Variable Size 201328860 bytes
Database Buffers 322961408 bytes
Redo Buffers 5820416 bytes
Database mounted.● 7. 문제되는 데이터파일 ONLINE 시키기
SQL> select file#, name, status from v$datafile;
SQL> alter database datafile 4 online;
Database altered.● 8. cancel base recover
SQL> recover database until cancel
ORA-00279: change 1586690 generated at 01/12/2024 11:26:17 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch2/arch_1_1_1158057974.arc
ORA-00280: change 1586690 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.● 9. 수위를 맞추기위해서 resetlogs 로 db open해야한다
SQL> alter database open resetlogs;
Database altered.- redo log file 확인
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
1 1 1 52428800 512 1 NO
CURRENT 1586691 12-JAN-24 2.8147E+14
2 1 0 52428800 512 1 YES
UNUSED 0 0
3 1 0 52428800 512 1 YES
UNUSED 0 0- resetlogs 로그 기록
select * from v$database_incarnation;
---
1 1 13/08/25 0 PARENT 824361512 0 NO
2 635002 23/12/07 1 13/08/25 PARENT 1154915172 1 NO
3 1513641 24/01/11 635002 23/12/07 PARENT 1157971653 2 NO
4 1580233 24/01/12 1513641 24/01/11 PARENT 1158049053 3 NO
5 1580748 24/01/12 1580233 24/01/12 PARENT 1158049110 4 NO
6 1584059 24/01/12 1580748 24/01/12 PARENT 1158057974 5 NO
7 1586691 24/01/12 1584059 24/01/12 CURRENT 1158085227 6 NO- 로그스위치 발생
alter system switch logfile;● 10. cold, hot backup 하기
- resetlogs
: 데이터베이스를 resetlogs 을 이용해서 open 을 한 후에 과거 백업본은 사용할 수 없다. 현재 상태에서 일관성 있는 백업과 일관성 없는 백업을 수행해야한다.
과거의 아카이브 파일은 필요가없다.
usermanaged 는 hotbackup 으로는 불완전 복구 불가능하지만
rman 의 hot backup 가지고도 불완전 복구가 가능하다.
2023/01/17
- archive dest 보기
select destination, binding, status from v$archive_dest;- archive dest1, 2 경로 보기
SQL> show parameter log_archive_dest_1
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1 string location=/home/oracle/arch1 ma
ndatory
log_archive_dest_10 string
log_archive_dest_11 string
log_archive_dest_12 string
log_archive_dest_13 string
log_archive_dest_14 string
log_archive_dest_15 string
log_archive_dest_16 string
log_archive_dest_17 string
log_archive_dest_18 string
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_19 string
SQL> show parameter log_archive_dest_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string location=/home/oracle/arch2 op
tional
log_archive_dest_20 string
log_archive_dest_21 string
log_archive_dest_22 string
log_archive_dest_23 string
log_archive_dest_24 string
log_archive_dest_25 string
log_archive_dest_26 string
log_archive_dest_27 string
log_archive_dest_28 string
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_29 string- archive dest 2 없는 상태로 변경
SQL> alter system reset log_archive_dest_2 scope=spfile;
또는
SQL> alter system set log_archive_dest_2 = '' scope=spfile;
System altered.- db 껏다 켜주면 바껴있다.
shutdown immdeiate
startup- 필요 없는 os 상의 archive 파일 지우기
rm /home/oracle/arch2/*.*▶ 14. archive 생성된후 db close 상태에서 inactive log file이 손상된 시나리오
● 1. 확인
select a.group#,b.thread#,b.sequence#, member, b.bytes/1024/1024 mb, b.archived, b.status,
b.first_change#, b.next_change#, b.next_time
from v$logfile a, v$log b
where a.group# = b.group#
order by 1,2;
---
1 1 1 /u01/app/oracle/oradata/ora11g/redo01.log 50 YES INACTIVE 1584059 1590360 24/01/13
2 1 2 /u01/app/oracle/oradata/ora11g/redo02.log 50 YES INACTIVE 1590360 1590514 24/01/13
3 1 3 /u01/app/oracle/oradata/ora11g/redo03.log 50 NO CURRENT 1590514 281474976710655 ● 2. 장애발생 : redolog file 삭제, alert log에서 확인
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> ! rm /u01/app/oracle/oradata/ora11g/redo01.log
SQL> startup
ORACLE instance started.
Total System Global Area 531476480 bytes
Fixed Size 1365796 bytes
Variable Size 201328860 bytes
Database Buffers 322961408 bytes
Redo Buffers 5820416 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 645
Session ID: 162 Serial number: 5- alert log 창에 1번 그룹 에러가 뜬다
Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_645.trc:
ORA-00313: open failed for members of log group 1 of thread
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/ora11g/redo01.log'● 3. 문제가 되었던 redo log 그룹삭제후 open
- mount 단계까지 start
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 531476480 bytes
Fixed Size 1365796 bytes
Variable Size 201328860 bytes
Database Buffers 322961408 bytes
Redo Buffers 5820416 bytes
Database mounted.- 문제가 되었던 1번 그룹 삭제
SQL> alter database drop logfile group 1;
Database altered.- 그룹 확인
select a.group#,b.thread#,b.sequence#, member, b.bytes/1024/1024 mb, b.archived, b.status,
b.first_change#, b.next_change#, b.next_time
from v$logfile a, v$log b
where a.group# = b.group#
order by 1,2;SQL> alter database open;
Database altered.● 4. redo log 그룹 추가
SQL> alter database add logfile group 1 '/u01/app/oracle/oradata/ora11g/redo01.log' size 50m;
Database altered.- 확인
select a.group#,b.thread#,b.sequence#, member, b.bytes/1024/1024 mb, b.archived, b.status,
b.first_change#, b.next_change#, b.next_time
from v$logfile a, v$log b
where a.group# = b.group#
order by 1,2;▶ 15. DB open 상태에서 current 그룹이 아닌 redo log 그룹이 삭제
● 1. 확인
- 로그 스위치 발생
alter system switch logfile;- 아카이브 파일에 current log 기록
alter system archive log current;- logfile 그룹 보기
select a.group#,b.thread#,b.sequence#, member, b.bytes/1024/1024 mb, b.archived, b.status,
b.first_change#, b.next_change#, b.next_time
from v$logfile a, v$log b
where a.group# = b.group#
order by 1,2;● 2. 장애 발생 : redologfile 삭제
SQL> ! rm /u01/app/oracle/oradata/ora11g/redo02.log- 로그 스위치 발생 -> 행걸림
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
- alter log file 보기
Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_m000_rc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/ora11g/redo02.l
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
- 행(중단)이 발생하면서 archive 파일에 간격이 생겼다.
[oracle@oracle arch1]$ ls arch_1_1_1158057974.arc arch_1_4_1158057974.arc arch_1_7_1158057974.arc arch_1_2_1158057974.arc arch_1_5_1158057974.arc arch_1_3_1158057974.arc arch_1_6_1158057974.arcselect a.group#,b.thread#,b.sequence#, member, b.bytes/1024/1024 mb, b.archived, b.status, b.first_change#, b.next_change#, b.next_time from v$logfile a, v$log b where a.group# = b.group# order by 1,2; --- 1 1 10 /u01/app/oracle/oradata/ora11g/redo01.log 50 NO CURRENT 1592257 281474976710655 2 1 8 /u01/app/oracle/oradata/ora11g/redo02.log 50 NO INACTIVE 1592246 1592254 2024-01-13 01:23:59 3 1 9 /u01/app/oracle/oradata/ora11g/redo03.log 50 NO INACTIVE 1592254 1592257 2024-01-13 01:24:02
● 3. redo log file을 삭제후 재생성 해준다.
SQL> ! ls /u01/app/oracle/oradata/ora11g/redo02.log
ls: cannot access /u01/app/oracle/oradata/ora11g/redo02.log: No such file or directory- redo log file 그룹 재생성
SQL> alter database clear unarchived logfile group 2;
Database altered.SQL> ! ls /u01/app/oracle/oradata/ora11g/redo02.log
/u01/app/oracle/oradata/ora11g/redo02.log▶ 16. current redo log 그룹이 삭제된후 DB 정상적인 종료 되었을때 시나리오
● 1. 작업
create table hr.new_loc
as select * from hr.locations;
select count(*) from hr.new_loc;● 2. 장애 발생 : current 한 redo log 그룹 삭제
- redolog group 확인
select a.group#,b.thread#,b.sequence#, member, b.bytes/1024/1024 mb, b.archived, b.status,
b.first_change#, b.next_change#, b.next_time
from v$logfile a, v$log b
where a.group# = b.group#
order by 1,2;
---
1 1 1 /u01/app/oracle/oradata/ora11g/redo01.log 50 YES INACTIVE 1584059 1590335 24/01/13
2 1 2 /u01/app/oracle/oradata/ora11g/redo02.log 50 YES INACTIVE 1590335 1590594 24/01/13
3 1 3 /u01/app/oracle/oradata/ora11g/redo03.log 50 NO CURRENT 1590594 281474976710655 - current 한 redo log 그룹 삭제
SQL> ! rm /u01/app/oracle/oradata/ora11g/redo03.log● 3. db 정상적인 종료후 db startup
- db 정상적인 종료
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.- db startup 하면 에러가 나면서 db 내려감
SQL> startup
ORACLE instance started.
Total System Global Area 531476480 bytes
Fixed Size 1365796 bytes
Variable Size 201328860 bytes
Database Buffers 322961408 bytes
Redo Buffers 5820416 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 5892
Session ID: 162 Serial number: 5cacel base recover 을 하면 control 파일하고 수위가 안맞기때문에 무조건 resetlogs로 db open 해야한다.
● 4. cancel base recover
- mount 단계 까지 start
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 531476480 bytes
Fixed Size 1365796 bytes
Variable Size 201328860 bytes
Database Buffers 322961408 bytes
Redo Buffers 5820416 bytes
Database mounted.- cancel base recover
current 한 redolog 전 까지 recover
SQL> recover database until cancel
Media recovery complete.- open resetlogs
cacel base recover 을 하면 control 파일하고 수위가 안맞기때문에 무조건 resetlogs로 db open 해야한다.
SQL> alter database open resetlogs;
Database altered.- 정상적인 종료를 하였으므로 깨진 시점 전까지 redo가 생성된다.
SQL> ! ls /u01/app/oracle/oradata/ora11g/redo*.*
/u01/app/oracle/oradata/ora11g/redo01.log
/u01/app/oracle/oradata/ora11g/redo02.log
/u01/app/oracle/oradata/ora11g/redo03.logcurrent 한 redolog 그룹이 손상되었어도 DB가 정상적으로 종료되면 체크포인트가 발생하면서 dirty buffer들을 모두 디스크로 내리기 때문에 복구할 것이 없게되는 것이다.
=> 삭제된 리두만 새로 만들어준다고 보면 된다.
▶ 17. current redo log 그룹이 삭제된후 DB 비정상적인 종료 되었을때 시나리오
● 1. 작업
- 테이블 생성
create table hr.copy_emp
as select * from hr.employees;- 리두로그 그룹 확인
select a.group#,b.thread#,b.sequence#, member, b.bytes/1024/1024 mb, b.archived, b.status,
b.first_change#, b.next_change#, b.next_time
from v$logfile a, v$log b
where a.group# = b.group#
order by 1,2;
---
1 1 4 /u01/app/oracle/oradata/ora11g/redo01.log 50 YES INACTIVE
2 1 5 /u01/app/oracle/oradata/ora11g/redo02.log 50 NO CURRENT
3 1 3 /u01/app/oracle/oradata/ora11g/redo03.log 50 YES INACTIVE- 아카이브 로그파일에 current redolog 기록
alter system archive log current;- current 한 redolog그룹 메모리에서 테이블생성
create table hr.copy_emp
as select * from hr.employees;- os 상에서 확인
[oracle@oracle arch1]$ ls
arch_1_1_1158057974.arc arch_1_3_1158057974.arc
arch_1_2_1158057974.arc arch_1_4_1158057974.arc● 2. 장애발생 : current 한 redolog file 삭제
SQL> ! rm /u01/app/oracle/oradata/ora11g/redo02.log● 3. db 비정상적인 종료
SQL> shutdown abort
ORACLE instance shut down.● 4. startup : 에러발생
SQL> startup
ORACLE instance started.
Total System Global Area 531476480 bytes
Fixed Size 1365796 bytes
Variable Size 201328860 bytes
Database Buffers 322961408 bytes
Redo Buffers 5820416 bytes
Database mounted.
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/ora11g/redo02.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3- mount 단계까지 start 되어있다
SQL> select status from v$instance;
STATUS
------------
MOUNTED● 5. 데이터파일만 백업본을 이용해 restore
SQL> ! cp -av /home/oracle/backup/arch/cold_20240116/*.dbf /u01/app/oracle/oradata/ora11g/
‘/home/oracle/backup/arch/cold_20240116/example01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/example01.dbf’
‘/home/oracle/backup/arch/cold_20240116/sysaux01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/sysaux01.dbf’
‘/home/oracle/backup/arch/cold_20240116/system01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/system01.dbf’
‘/home/oracle/backup/arch/cold_20240116/temp01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/temp01.dbf’
‘/home/oracle/backup/arch/cold_20240116/undotbs01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/undotbs01.dbf’
‘/home/oracle/backup/arch/cold_20240116/users01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/users01.dbf’● 5. recover 적용까지
- #1,#2,#3,#4 번까지 적용인된다 #5번은 안된다.
SQL> recover database until cancel
ORA-00279: change 1588754 generated at 01/12/2024 17:26:30 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_1_1158057974.arc
ORA-00280: change 1588754 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
-- 엔터
ORA-00279: change 1590996 generated at 01/13/2024 02:49:19 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_2_1158057974.arc
ORA-00280: change 1590996 for thread 1 is in sequence #2
ORA-00278: log file '/home/oracle/arch1/arch_1_1_1158057974.arc' no longer
needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
-- 엔터
ORA-00279: change 1591000 generated at 01/13/2024 02:49:24 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_3_1158057974.arc
ORA-00280: change 1591000 for thread 1 is in sequence #3
ORA-00278: log file '/home/oracle/arch1/arch_1_2_1158057974.arc' no longer
needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
-- 엔터
ORA-00279: change 1591004 generated at 01/13/2024 02:49:31 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_4_1158057974.arc
ORA-00280: change 1591004 for thread 1 is in sequence #4
ORA-00278: log file '/home/oracle/arch1/arch_1_3_1158057974.arc' no longer
needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
-- 엔터
ORA-00279: change 1591019 generated at 01/13/2024 02:49:43 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_5_1158057974.arc
ORA-00280: change 1591019 for thread 1 is in sequence #5
ORA-00278: log file '/home/oracle/arch1/arch_1_4_1158057974.arc' no longer
needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
-- 엔터
ORA-00308: cannot open archived log
'/home/oracle/arch1/arch_1_5_1158057974.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3● 6. cancel base recover
SQL> recover database until cancel
ORA-00279: change 1591019 generated at 01/13/2024 02:49:43 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_5_1158057974.arc
ORA-00280: change 1591019 for thread 1 is in sequence #5
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.● 7. resetlogs 로 db open
SQL> alter database open resetlogs;
Database altered.● 8. 만들었던 테이블 확인
- 아카이브파일에 있던것은 돌아왔지만
SQL> select count(*) from hr.copy_emp;
COUNT(*)
----------
107
- current redolog 그룹에 있었던 테이블은 조회가안된다.
SQL> select count(*) from hr.copy_dept;
select count(*) from hr.copy_dept
*
ERROR at line 1:
ORA-00942: table or view does not exist
2023/01/18
▶ 18. control file의 백업본이 있을때 datafile, redolog file 손상되지 않고 control file 만 손상되었을때 시나리오.
● 1. 작업 및 확인
- 확인
select * from v$database;
select * from v$log;
---
1 1 4 52428800 512 1 NO CURRENT 1589427
2 1 2 52428800 512 1 YES INACTIVE 1589415
3 1 3 52428800 512 1 YES INACTIVE 1589423
---
select * from v$logfile;
select a.file#, b.name tbs_name, a.name file_name,
a.status, a.checkpoint_change#
from v$datafile a, v$tablespace b
where a.ts# = b.ts#;
select name from v$controlfile;아카이브 dest 파일과, control file의 정보는 초기파라미터 파일이 가지고 있다.
- 테이블생성
create table hr.emp_20
as
select * from hr.employees where department_id=20;- 확인
SQL> ! ls /u01/app/oracle/oradata/ora11g/
control01.ctl redo02.log system01.dbf undotbs01.dbf
example01.dbf redo03.log temp01.dbf users01.dbf
redo01.log sysaux01.dbf undo_new01.dbf● 2. 장애발생 : controlfile 손상
- shutdown immediate
SQL> ! rm /u01/app/oracle/oradata/ora11g/control01.ctl- db startup -> 에러
SQL> startup
ORACLE instance started.
Total System Global Area 531476480 bytes
Fixed Size 1365796 bytes
Variable Size 201328860 bytes
Database Buffers 322961408 bytes
Redo Buffers 5820416 bytes
ORA-00205: error in identifying control file, check alert log for more info
SQL> select status from v$instance;
STATUS
------------
STARTED- alert log 창에 에러 나온다
Sat Jan 13 05:40:00 2024
ALTER DATABASE MOUNT
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/ora11g/control01.ctl'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3- db 강제종료
SQL> shutdown abort
ORACLE instance shut down.● 3. cold 백업본을 이용하여 restore -> 에러
SQL> ! cp -av /home/oracle/backup/arch/cold_20240116/control01.ctl /u01/app/oracle/oradata/ora11g/
‘/home/oracle/backup/arch/cold_20240116/control01.ctl’ -> ‘/u01/app/oracle/oradata/ora11g/control01.ctl’- db startup
SQL> startup
ORACLE instance started.
Total System Global Area 531476480 bytes
Fixed Size 1365796 bytes
Variable Size 201328860 bytes
Database Buffers 322961408 bytes
Redo Buffers 5820416 bytes
Database mounted.
ORA-01190: control file or data file 1 is from before the last RESETLOGS
ORA-01110: data file 1: '/u01/app/oracle/oradata/ora11g/system01.dbf'● 4. 아카이브 파일로 recover
: 리커버할때 아카이브 파일만 보기때문에 아카이브 파일로만 적용된다.
SQL> recover database using backup controlfile
ORA-00279: change 1588754 generated at 01/12/2024 10:46:14 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_1_1158057974.arc
ORA-00280: change 1588754 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 1589415 generated at 01/13/2024 06:00:02 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_2_1158057974.arc
ORA-00280: change 1589415 for thread 1 is in sequence #2
ORA-00278: log file '/home/oracle/arch1/arch_1_1_1158057974.arc' no longer
needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 1589423 generated at 01/13/2024 06:00:14 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_3_1158057974.arc
ORA-00280: change 1589423 for thread 1 is in sequence #3
ORA-00278: log file '/home/oracle/arch1/arch_1_2_1158057974.arc' no longer
needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 1589427 generated at 01/13/2024 06:00:18 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_4_1158057974.arc
ORA-00280: change 1589427 for thread 1 is in sequence #4
ORA-00278: log file '/home/oracle/arch1/arch_1_3_1158057974.arc' no longer
needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00308: cannot open archived log
'/home/oracle/arch1/arch_1_4_1158057974.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3● 5. 리두로그 그룹으로 recover
SQL> recover database using backup controlfile
ORA-00279: change 1589427 generated at 01/13/2024 06:00:18 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_4_1158057974.arc
ORA-00280: change 1589427 for thread 1 is in sequence #4
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/ora11g/redo01.log
-- 현재 current한 리두로그 그룹 넣기
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.- 아까 생성한 테이블확인
SQL> SQL>
SQL> select count(*) from hr.emp_20;'
COUNT(*)
----------
2▶ 19. datafile, redolog file 손상되지 않고 control file 만 손상되었을때 control file의 백업본을 이용해서 재생성하여 복구 시나리오
● 1. 작업
- 테이블 생성
create table hr.emp_30
as
select * from hr.employees where department_id =30;● 2. 장애발생 : control file 삭제
- 삭제
SQL> ! rm /u01/app/oracle/oradata/ora11g/control01.ctl● 3. control파일이 가지고있는것을 작업하면 에러가나온다.
- 로그 스위치발생
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
1 1 4 52428800 512 1 YES
INACTIVE 1591878 13-JAN-24 1591881 13-JAN-24
2 1 5 52428800 512 1 NO
CURRENT 1591881 13-JAN-24 2.8147E+14
3 1 3 52428800 512 1 YES
INACTIVE 1591874 13-JAN-24 1591878 13-JAN-24- os에 archive 파일 확인
SQL> ! ls /home/oracle/arch1
arch_1_1_1158057974.arc arch_1_3_1158057974.arc
arch_1_2_1158057974.arc arch_1_4_1158057974.arc- 오류
SQL> select * from v$datafile;
select * from v$datafile
*
ERROR at line 1:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/ora11g/control01.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3- alert log 창에 오류가 뜬다
Sat Jan 13 07:09:50 2024
Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_m000_25776.trc:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/ora11g/control01.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3- db 정상종료 -> 에러가뜬다.
SQL> shutdown immediate
Database closed.
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/ora11g/control01.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3- db 강제종료
SQL> shutdown abort
ORACLE instance shut down.● 4. 백업본을 이용하여 restore -> 에러
SQL> ! cp -av /home/oracle/backup/arch/cold_20240116/control01.ctl /u01/app/oracle/oradata/ora11g/
‘/home/oracle/backup/arch/cold_20240116/control01.ctl’ -> ‘/u01/app/oracle/oradata/ora11g/control01.ctl’- mount 단계까지 start
SQL> startup mount
ORACLE instance started.
Total System Global Area 531476480 bytes
Fixed Size 1365796 bytes
Variable Size 201328860 bytes
Database Buffers 322961408 bytes
Redo Buffers 5820416 bytes
Database mounted.● 5. 복구 작업 실패
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/u01/app/oracle/oradata/ora11g/system01.dbf'
ORA-01207: file is more recent than control file - old control file● 6. 컨트롤 파일을 재생성한 후 db 시작
- 컨트롤 파일의 백업 받아놓기 trace '경로'
SQL> alter database backup controlfile to trace as '/home/oracle/new_control.sql';
Database altered.- db 강제종료
SQL> shutdown abort
ORACLE instance shut down.- 생성되었는지 확인
[oracle@oracle ~]$ ls /home/oracle/new_control.sql- 생성된 controlfile 확인
[oracle@oracle ~]$ vi new_control.sql
---
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORA11G" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16 # 리두로그파일 max 갯수
MAXLOGMEMBERS 3 # logmember max 갯수
MAXDATAFILES 100 # datafile의 max 갯수
MAXINSTANCES 8 # instance max 갯수
MAXLOGHISTORY 292 # log history max 갯수
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/ora11g/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/oradata/ora11g/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/oradata/ora11g/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/ora11g/system01.dbf',
'/u01/app/oracle/oradata/ora11g/sysaux01.dbf',
'/u01/app/oracle/oradata/ora11g/users01.dbf',
'/u01/app/oracle/oradata/ora11g/example01.dbf',
'/u01/app/oracle/oradata/ora11g/undotbs01.dbf'
CHARACTER SET AL32UTF8
;
- control file 재생성 할때
- MAX 값을 변경할때
- 컨트롤파일이 깨져서 재생성할때
- DB 이름을 바꿀때(거의없다.)
● 7. 생성된것을 보고 새로 control 파일 vi로 생성
[oracle@oracle ~]$ vi create_control.sql
---
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORA11G" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/ora11g/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/oradata/ora11g/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/oradata/ora11g/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/ora11g/system01.dbf',
'/u01/app/oracle/oradata/ora11g/sysaux01.dbf',
'/u01/app/oracle/oradata/ora11g/users01.dbf',
'/u01/app/oracle/oradata/ora11g/example01.dbf',
'/u01/app/oracle/oradata/ora11g/undotbs01.dbf'
CHARACTER SET AL32UTF8
;● 8. control 파일 재생성한것을 실행
- 위치확인
SQL> ! ls
afiedt.buf data01.dbf Music Public
arch1 database new_control.sql spool
arch2 Desktop ora_data Templates
archive_file Documents p13390677_112040_LINUX_1of7.zip userdata
backup Downloads p13390677_112040_LINUX_2of7.zip Videos
create_control.sql emp_sal.csv Pictures- control 파일 만드는 파일 실행
SQL> @create_control.sql
ORACLE instance started.
Total System Global Area 531476480 bytes
Fixed Size 1365796 bytes
Variable Size 201328860 bytes
Database Buffers 322961408 bytes
Redo Buffers 5820416 bytes
Control file created.● 9. db open (필요시 recover)
- recover database : 할 필요가 없다.
: recover 할 필요가 없다.
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required- db open;
SQL> recover database open;- 만약 db open이 안되고 recover 필요하다 하면 recover 해주면된다
SQL> recover database;● 10. tempfile 생성
- tempfile 생성
: 아까 재생성한 controlfile 안에는
tempfile이 없다 따라서 tempfile 생성해준다
(os 상에는 있으므로 재사용)
ALTER TABLESPACE TEMP add tempfile '/u01/app/oracle/oradata/ora11g/temp01.dbf' REUSE;▶ 20. datafile, controlfile 손상
- 요약
- datafile 손상된것만, controlfile을 restore
- archive file 로 recover database using backup controlfile
- current redolog 파일 직접 넣어줘서 recover
- resetlogs open
● 1. 확인
alter system switch logfile;
alter system checkpoint;
select * from v$log;
1 1 1 52428800 512 1 YES INACTIVE 1584059 24/01/12 1589759 24/01/13
2 1 2 52428800 512 1 YES INACTIVE 1589759 24/01/13 1589764 24/01/13
3 1 3 52428800 512 1 NO CURRENT 1589764 24/01/13 281474976710655 - alert log 창에 current 그룹도 보인다.
Beginning log switch checkpoint up to RBA [0x3.2.10], SCN: 1589764
Thread 1 advanced to log sequence 3 (LGWR switch)
Current log# 3 seq# 3 mem# 0: /u01/app/oracle/oradata/ora11g/redo03.lo
Sat Jan 13 10:38:09 2024
Archived Log entry 10 added for thread 1 sequence 2 ID 0xf745693 dest 1:
Sat Jan 13 10:38:25 2024- current한 그룹에 테이블 생성
create table hr.emp_30
as
select * from hr.employees where department_id=20;● 2. 장애 발생 : datafile, controlfile 손상
SQL> ! rm /u01/app/oracle/oradata/ora11g/*.dbf
SQL> ! rm /u01/app/oracle/oradata/ora11g/*.ctl- alert log 창에 에러가 뜬다
Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_j001_8171.trc (incident=301622):
ORA-00603: ORACLE server session terminated by fatal error
ORA-01116: error in opening database file 2
ORA-01110: data file 2: '/u01/app/oracle/oradata/ora11g/sysaux01.dbf'
ORA-27041: unable to open file- datagfile, controlfile 없는것 확인
SQL> ! ls /u01/app/oracle/oradata/ora11g/*.dbf
ls: cannot access /u01/app/oracle/oradata/ora11g/*.dbf: No such file or directory
SQL> ! ls /u01/app/oracle/oradata/ora11g/*.ctl
ls: cannot access /u01/app/oracle/oradata/ora11g/*.ctl: No such file or directory● 3. 백업본으로 datafile, controlfile을 restore
만약 datafile 전체가 아니라 그중에 몇개만 깨졋으면 그것만 restore 해주면 된다.
- db 강제 종료
: control 파일이 깨졋으므로 무조건 db 내리고 해야한다.
SQL> shutdown abort
ORACLE instance shut down.- datafile, controlfile 만 백업본으로 restore
SQL> !
[oracle@oracle ~]$ cp -av /home/oracle/backup/arch/cold_20240116/*.dbf /u01/app/oracle/oradata/ora11g/
‘/home/oracle/backup/arch/cold_20240116/example01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/example01.dbf’
‘/home/oracle/backup/arch/cold_20240116/sysaux01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/sysaux01.dbf’
‘/home/oracle/backup/arch/cold_20240116/system01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/system01.dbf’
‘/home/oracle/backup/arch/cold_20240116/temp01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/temp01.dbf’
‘/home/oracle/backup/arch/cold_20240116/undotbs01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/undotbs01.dbf’
‘/home/oracle/backup/arch/cold_20240116/users01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/users01.dbf’
[oracle@oracle ~]$ cp -av /home/oracle/backup/arch/cold_20240116/*.ctl /u01/app/oracle/oradata/ora11g/
‘/home/oracle/backup/arch/cold_20240116/control01.ctl’ -> ‘/u01/app/oracle/oradata/ora11g/control01.ctl’● 4. 아카이브 파일에있는것으로 recover
- mount 단계 까지 start
SQL> startup mount
ORACLE instance started.
Total System Global Area 531476480 bytes
Fixed Size 1365796 bytes
Variable Size 201328860 bytes
Database Buffers 322961408 bytes
Redo Buffers 5820416 bytes
Database mounted.- 아카이브 파일로 recover
SQL> recover database using backup controlfile
ORA-00279: change 1588754 generated at 01/12/2024 17:26:30 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_1_1158057974.arc
ORA-00280: change 1588754 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
-- 엔터
ORA-00279: change 1589759 generated at 01/13/2024 10:38:02 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_2_1158057974.arc
ORA-00280: change 1589759 for thread 1 is in sequence #2
ORA-00278: log file '/home/oracle/arch1/arch_1_1_1158057974.arc' no longer
needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
-- 엔터
ORA-00279: change 1589764 generated at 01/13/2024 10:38:09 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_3_1158057974.arc
ORA-00280: change 1589764 for thread 1 is in sequence #3
ORA-00278: log file '/home/oracle/arch1/arch_1_2_1158057974.arc' no longer
needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
-- 엔터
ORA-00279: change 1590432 generated at 01/13/2024 10:44:35 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_4_1158057974.arc
ORA-00280: change 1590432 for thread 1 is in sequence #4
ORA-00278: log file '/home/oracle/arch1/arch_1_3_1158057974.arc' no longer
needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
-- 엔터
ORA-00279: change 1590468 generated at 01/13/2024 10:45:50 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_5_1158057974.arc
ORA-00280: change 1590468 for thread 1 is in sequence #5
ORA-00278: log file '/home/oracle/arch1/arch_1_4_1158057974.arc' no longer
needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
-- 엔터
ORA-00279: change 1590474 generated at 01/13/2024 10:46:01 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_6_1158057974.arc
ORA-00280: change 1590474 for thread 1 is in sequence #6
ORA-00278: log file '/home/oracle/arch1/arch_1_5_1158057974.arc' no longer
needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
-- 엔터
ORA-00279: change 1590482 generated at 01/13/2024 10:46:13 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_7_1158057974.arc
ORA-00280: change 1590482 for thread 1 is in sequence #7
ORA-00278: log file '/home/oracle/arch1/arch_1_6_1158057974.arc' no longer
needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
-- 엔터
ORA-00308: cannot open archived log
'/home/oracle/arch1/arch_1_7_1158057974.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3● 5. current 한 redolog 파일로 recover
: recover 할때는 아카이브파일만 보기떄문에 직접 적어줘야함
SQL> recover database using backup controlfile
ORA-00279: change 1590482 generated at 01/13/2024 10:46:13 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_7_1158057974.arc
ORA-00280: change 1590482 for thread 1 is in sequence #7
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/ora11g/redo01.log -- current redolog file 찾아서 적용
Log applied.
Media recovery complete.● 6. resetlogs 로 db open
SQL> alter database open resetlogs;
Database altered.▶ 21. redolog file, controlfile 손상
- 요약
- cold backup 본을 controlfile, datafile 전부 restore
- archive file 로 recover database using backup controlfile
- cancel base recover 실행
- resetlogs open
● 1. 장애 발생 : redologfile, controlfile 손상
SQL> ! rm /u01/app/oracle/oradata/ora11g/*.log
SQL> ! rm /u01/app/oracle/oradata/ora11g/*.ctl- alert log 창에 에러가 뜬다
Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_j001_8171.trc (incident=301622):
ORA-00603: ORACLE server session terminated by fatal error
ORA-01116: error in opening database file 2
ORA-01110: data file 2: '/u01/app/oracle/oradata/ora11g/redolog01.dbf'
ORA-27041: unable to open file- redo log file, controlfile 없는것 확인
SQL> ! ls /u01/app/oracle/oradata/ora11g/*.log
ls: cannot access /u01/app/oracle/oradata/ora11g/*.dbf: No such file or directory
SQL> ! ls /u01/app/oracle/oradata/ora11g/*.ctl
ls: cannot access /u01/app/oracle/oradata/ora11g/*.ctl: No such file or directory● 3. 백업본으로 datafile전부, controlfile restore
redo log file이 깨졋으므로 datafile도 restore 해줘야한다.
- db 강제 종료
: control 파일이 깨졋으므로 무조건 db 내리고 해야한다.
SQL> shutdown abort
ORACLE instance shut down.- datafile, controlfile 만 백업본으로 restore
SQL> !
[oracle@oracle ~]$ cp -av /home/oracle/backup/arch/cold_20240116/*.dbf /u01/app/oracle/oradata/ora11g/
‘/home/oracle/backup/arch/cold_20240116/example01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/example01.dbf’
‘/home/oracle/backup/arch/cold_20240116/sysaux01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/sysaux01.dbf’
‘/home/oracle/backup/arch/cold_20240116/system01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/system01.dbf’
‘/home/oracle/backup/arch/cold_20240116/temp01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/temp01.dbf’
‘/home/oracle/backup/arch/cold_20240116/undotbs01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/undotbs01.dbf’
‘/home/oracle/backup/arch/cold_20240116/users01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/users01.dbf’
[oracle@oracle ~]$ cp -av /home/oracle/backup/arch/cold_20240116/*.ctl /u01/app/oracle/oradata/ora11g/
‘/home/oracle/backup/arch/cold_20240116/control01.ctl’ -> ‘/u01/app/oracle/oradata/ora11g/control01.ctl’● 4. 아카이브 파일에있는것으로 cancel base recover
: redologfile 이 깨졋으므로 cancel base recover 해야한다.
- mount 단계 까지 start
SQL> startup mount
ORACLE instance started.
Total System Global Area 531476480 bytes
Fixed Size 1365796 bytes
Variable Size 201328860 bytes
Database Buffers 322961408 bytes
Redo Buffers 5820416 bytes
Database mounted.- 아카이브 파일로 recover
SQL> recover database until cancel using backup controlfile
ORA-00279: change 1588754 generated at 01/12/2024 17:26:30 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_1_1158057974.arc
ORA-00280: change 1588754 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto -- auto 입력
ORA-00279: change 1589151 generated at 01/13/2024 11:59:42 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_2_1158057974.arc
ORA-00280: change 1589151 for thread 1 is in sequence #2
ORA-00278: log file '/home/oracle/arch1/arch_1_1_1158057974.arc' no longer
needed for this recovery
ORA-00279: change 1589154 generated at 01/13/2024 11:59:45 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_3_1158057974.arc
ORA-00280: change 1589154 for thread 1 is in sequence #3
ORA-00278: log file '/home/oracle/arch1/arch_1_2_1158057974.arc' no longer
needed for this recovery
ORA-00279: change 1589157 generated at 01/13/2024 11:59:48 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_4_1158057974.arc
ORA-00280: change 1589157 for thread 1 is in sequence #4
ORA-00278: log file '/home/oracle/arch1/arch_1_3_1158057974.arc' no longer
needed for this recovery
ORA-00308: cannot open archived log
'/home/oracle/arch1/arch_1_4_1158057974.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
● 5. current 한 redolog 파일이 없으므로 cancel base recover
SQL> recover database until cancel using backup controlfile
ORA-00279: change 1589157 generated at 01/13/2024 11:59:48 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_4_1158057974.arc
ORA-00280: change 1589157 for thread 1 is in sequence #4
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
● 6. resetlogs 로 db open
SQL> alter database open resetlogs;
Database altered.
- cancel base recver
: 과거시점으로 돌아가야해서 datafile을 전부 backup본으로 restore 해야한다.
- current한 리두로그가 깨지면 완전복구 작업은 못한다.
- 아카이브 파일에 gap 이 생길때
2023/01/19
▶ 문제
▶ [문제1] users01.dbf, control01.ctl 파일이 삭제된 경우 복구 수행해주세요
- 요약
- datafile 손상된것만, controlfile을 restore
- archive file 로 recover database using backup controlfile
- current redolog 파일 직접 넣어줘서 recover
- resetlogs open
● 1. 장애 발생 : users01.dbf, control01.ctl 파일이 삭제
SQL> ! rm /u01/app/oracle/oradata/ora11g/users01.dbf
SQL> ! rm /u01/app/oracle/oradata/ora11g/control01.ctl- 장애발생
SQL> shutdown immediate
Database closed.
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/ora11g/control01.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3- controlfile 이 없으므로 db shutdown
SQL> shutdown abort
ORACLE instance shut down.● 2. cold 백업본으로 손상된 데이터파일, 컨트롤파일 restore
SQL> ! cp -av /home/oracle/backup/arch/cold_20240116/users01.dbf /u01/app/oracle/oradata/ora11g/
‘/home/oracle/backup/arch/cold_20240116/users01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/users01.dbf’
SQL> ! cp -av /home/oracle/backup/arch/cold_20240116/control01.ctl /u01/app/oracle/oradata/ora11g/
‘/home/oracle/backup/arch/cold_20240116/control01.ctl’ -> ‘/u01/app/oracle/oradata/ora11g/control01.ctl’● 3. recover database using controlfile
: 아카이브 파일이용
SQL> startup mount
ORACLE instance started.
Total System Global Area 531476480 bytes
Fixed Size 1365796 bytes
Variable Size 201328860 bytes
Database Buffers 322961408 bytes
Redo Buffers 5820416 bytes
Database mounted.
SQL> recover tablespace users
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required
SQL> recover database using controlfile
ORA-01906: BACKUP keyword expected
SQL> recover database using backup controlfile
ORA-00279: change 1588754 generated at 01/12/2024 17:26:30 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_1_1158057974.arc
ORA-00280: change 1588754 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 1593691 generated at 01/13/2024 14:15:32 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_2_1158057974.arc
ORA-00280: change 1593691 for thread 1 is in sequence #2
ORA-00278: log file '/home/oracle/arch1/arch_1_1_1158057974.arc' no longer
needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 1593694 generated at 01/13/2024 14:15:35 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_3_1158057974.arc
ORA-00280: change 1593694 for thread 1 is in sequence #3
ORA-00278: log file '/home/oracle/arch1/arch_1_2_1158057974.arc' no longer
needed for this recovery
ORA-00279: change 1593764 generated at 01/13/2024 14:17:39 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_4_1158057974.arc
ORA-00280: change 1593764 for thread 1 is in sequence #4
ORA-00278: log file '/home/oracle/arch1/arch_1_3_1158057974.arc' no longer
needed for this recovery
ORA-00308: cannot open archived log
'/home/oracle/arch1/arch_1_4_1158057974.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3● 4. recover database using backup controlfile
: current한 redologfile 이용
SQL> recover database using backup controlfile
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/ora11g/redo01.log
Log applied.
Media recovery complete.● 5. resetlogs db open
SQL> alter database open resetlogs;
Database altered.● 6. 백업 다 수행
▶ [문제2] user01.dbf, control01.ctl, inactive reodo log 파일이 삭제된 경우 복구 수행해주세요.
- 요약
- cold backup 본을 controlfile, datafile 전부 restore
- archive file 로 recover database using backup controlfile
- cancel base recover 실행
- resetlogs open
SQL> ! rm /u01/app/oracle/oradata/ora11g/redo01.log
SQL> ! rm /u01/app/oracle/oradata/ora11g/users01.dbf
SQL> ! rm /u01/app/oracle/oradata/ora11g/control01.ctl
SQL> alter system checkpoint;
System altered.
SQL> shutdown immediate
Database closed.
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/ora11g/control01.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
SQL> shutdown abort
ORACLE instance shut down.
SQL> ! cp -av /home/oracle/backup/arch/cold_20240116/users01.dbf /u01/app/oracle/oradata/ora11g/
‘/home/oracle/backup/arch/cold_20240116/users01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/users01.dbf’
SQL> ! cp -av /home/oracle/backup/arch/cold_20240116/control01.ctl /u01/app/oracle/oradata/ora11g/
‘/home/oracle/backup/arch/cold_20240116/control01.ctl’ -> ‘/u01/app/oracle/oradata/ora11g/control01.ctl’
SQL> startup mount
ORACLE instance started.
Total System Global Area 531476480 bytes
Fixed Size 1365796 bytes
Variable Size 201328860 bytes
Database Buffers 322961408 bytes
Redo Buffers 5820416 bytes
Database mounted.- redo logfile이 깨지면 datafile 전부를 restore해야한다.
SQL> recover database using backup controlfile
ORA-00283: recovery session canceled due to errors
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: '/u01/app/oracle/oradata/ora11g/system01.dbf'SQL> shutdown abort
ORACLE instance shut down.
SQL> ! cp -av /home/oracle/backup/arch/cold_20240116/*.dbf /u01/app/oracle/oradata/ora11g/
‘/home/oracle/backup/arch/cold_20240116/example01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/example01.dbf’
‘/home/oracle/backup/arch/cold_20240116/sysaux01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/sysaux01.dbf’
‘/home/oracle/backup/arch/cold_20240116/system01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/system01.dbf’
‘/home/oracle/backup/arch/cold_20240116/temp01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/temp01.dbf’
‘/home/oracle/backup/arch/cold_20240116/undotbs01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/undotbs01.dbf’
‘/home/oracle/backup/arch/cold_20240116/users01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/users01.dbf’
SQL> ! cp -av /home/oracle/backup/arch/cold_20240116/control01.ctl /u01/app/oracle/oradata/ora11g/
‘/home/oracle/backup/arch/cold_20240116/control01.ctl’ -> ‘/u01/app/oracle/oradata/ora11g/control01.ctl’
SQL> startup mount
ORACLE instance started.
Total System Global Area 531476480 bytes
Fixed Size 1365796 bytes
Variable Size 201328860 bytes
Database Buffers 322961408 bytes
Redo Buffers 5820416 bytes
Database mounted.
SQL> recover database using backup controlfile
ORA-00279: change 1588754 generated at 01/12/2024 17:26:30 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_1_1158057974.arc
ORA-00280: change 1588754 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 1593691 generated at 01/13/2024 14:15:32 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_2_1158057974.arc
ORA-00280: change 1593691 for thread 1 is in sequence #2
ORA-00278: log file '/home/oracle/arch1/arch_1_1_1158057974.arc' no longer
needed for this recovery
ORA-00279: change 1593694 generated at 01/13/2024 14:15:35 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_3_1158057974.arc
ORA-00280: change 1593694 for thread 1 is in sequence #3
ORA-00278: log file '/home/oracle/arch1/arch_1_2_1158057974.arc' no longer
needed for this recovery
ORA-00279: change 1593764 generated at 01/13/2024 14:17:39 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_4_1158057974.arc
ORA-00280: change 1593764 for thread 1 is in sequence #4
ORA-00278: log file '/home/oracle/arch1/arch_1_3_1158057974.arc' no longer
needed for this recovery
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.
▶ 21. 모든 datafile, redolog file, controlfile 손상
● 1. 장애발생 : 모든 datafile, redolog file, controlfile 손상
SQL> ! rm /u01/app/oracle/oradata/ora11g/*.dbf
SQL> ! rm /u01/app/oracle/oradata/ora11g/*.ctl
SQL> ! rm /u01/app/oracle/oradata/ora11g/*.log- alert log 창에 error가 뜬다 -> 오류
Sat Jan 13 16:24:30 2024
Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_m000_31011.trc:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/ora11g/control01.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3- 아카이브 파일 있는지 확인
SQL> ! ls /home/oracle/arch1
arch_1_1_1158057974.arc arch_1_2_1158057974.arc● 2. restore
- db 강제종료
SQL> shutdown abort
ORACLE instance shut down.- 백업본 datafile, controlfile 로 restores
SQL> ! cp -av /home/oracle/backup/arch/cold_20240116/*.ctl /u01/app/oracle/oradata/ora11g/
‘/home/oracle/backup/arch/cold_20240116/control01.ctl’ -> ‘/u01/app/oracle/oradata/ora11g/control01.ctl’
SQL> ! cp -av /home/oracle/backup/arch/cold_20240116/*.dbf /u01/app/oracle/oradata/ora11g/
‘/home/oracle/backup/arch/cold_20240116/example01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/example01.dbf’
‘/home/oracle/backup/arch/cold_20240116/sysaux01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/sysaux01.dbf’
‘/home/oracle/backup/arch/cold_20240116/system01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/system01.dbf’
‘/home/oracle/backup/arch/cold_20240116/temp01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/temp01.dbf’
‘/home/oracle/backup/arch/cold_20240116/undotbs01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/undotbs01.dbf’
‘/home/oracle/backup/arch/cold_20240116/users01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/users01.dbf’● 3. cancel base recover
- mount 단계까지 startup
SQL> startup mount
ORACLE instance started.
Total System Global Area 531476480 bytes
Fixed Size 1365796 bytes
Variable Size 201328860 bytes
Database Buffers 322961408 bytes
Redo Buffers 5820416 bytes
Database mounted.- 아카이브파일로 recover
SQL> recover database until cancel using backup controlfile
ORA-00279: change 1588754 generated at 01/12/2024 17:26:30 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_1_1158057974.arc
ORA-00280: change 1588754 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 1597447 generated at 01/13/2024 16:22:55 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_2_1158057974.arc
ORA-00280: change 1597447 for thread 1 is in sequence #2
ORA-00278: log file '/home/oracle/arch1/arch_1_1_1158057974.arc' no longer
needed for this recovery
ORA-00279: change 1597450 generated at 01/13/2024 16:22:55 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_3_1158057974.arc
ORA-00280: change 1597450 for thread 1 is in sequence #3
ORA-00278: log file '/home/oracle/arch1/arch_1_2_1158057974.arc' no longer
needed for this recovery
ORA-00308: cannot open archived log
'/home/oracle/arch1/arch_1_3_1158057974.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3- cancel base recover
SQL> recover database until cancel using backup controlfile
ORA-00279: change 1597450 generated at 01/13/2024 16:22:55 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_3_1158057974.arc
ORA-00280: change 1597450 for thread 1 is in sequence #3
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.- open resetlogs
SQL> alter database open resetlogs;
Database altered.▶ 22. 백업한 control file 내용과 현재 datafile 정보가 틀린경우 복구
● 1. 작업
- users 테이블스페이스에 테이블생성
create table hr.emp_20
as select * from hr.employees where department_id = 20;- 테이블스페이스 생성후, 테이블생성
CREATE TABLESPACE data_tbs
DATAFILE '/u01/app/oracle/oradata/ora11g/data_tbs01.dbf' SIZE 5M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT AUTO;
create table hr.emp_50
tablespace data_tbs
as select * from hr.employees where department_id=50;- 백업된 datafile 확인
select a.file#, a.name, a.checkpoint_change#,
b.status, b.change#, to_char(b.time,'yyyy/mm/dd hh24:mi:ss')
from v$datafile a, v$backup b
where a.file#=b.file#;- 테이블이 어느 tablespace, datafile에 있는지확인
select f.file_name
from dba_extents e, dba_data_files f
where e.file_id = f.file_id
and e.segment_name = 'EMP_50'
and e.owner = 'HR';● 2. 장애발생 : controlfile 삭제
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> ! rm /u01/app/oracle/oradata/ora11g/control01.ctl- 장애발생
SQL> startup
ORACLE instance started.
Total System Global Area 531476480 bytes
Fixed Size 1365796 bytes
Variable Size 201328860 bytes
Database Buffers 322961408 bytes
Redo Buffers 5820416 bytes
ORA-00205: error in identifying control file, check alert log for more info● 3. restore
- 백업 controlfile로 restore
SQL> shutdown abort
ORACLE instance shut down.
SQL> ! cp -av /home/oracle/backup/arch/cold_20240116/*.ctl /u01/app/oracle/oradata/ora11g/
‘/home/oracle/backup/arch/cold_20240116/control01.ctl’ -> ‘/u01/app/oracle/oradata/ora11g/control01.ctl’● 4. recover (아카이브 파일로)
SQL> recover database using backup controlfile
ORA-00279: change 1588754 generated at 01/12/2024 10:46:14 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_1_1158057974.arc
ORA-00280: change 1588754 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 1589329 generated at 01/13/2024 18:14:09 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_2_1158057974.arc
ORA-00280: change 1589329 for thread 1 is in sequence #2
ORA-00278: log file '/home/oracle/arch1/arch_1_1_1158057974.arc' no longer
needed for this recovery
ORA-00279: change 1589332 generated at 01/13/2024 18:14:10 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_3_1158057974.arc
ORA-00280: change 1589332 for thread 1 is in sequence #3
ORA-00278: log file '/home/oracle/arch1/arch_1_2_1158057974.arc' no longer
needed for this recovery
ORA-00308: cannot open archived log
'/home/oracle/arch1/arch_1_3_1158057974.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3● 5. recover 하고 current redolog 그룹 넣기
-> 에러
: 컨트롤파일이 갇고 있는 datafile이 다르므로 에러가 뜬다.
SQL> recover database using backup controlfile
ORA-00279: change 1589332 generated at 01/13/2024 18:14:10 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_3_1158057974.arc
ORA-00280: change 1589332 for thread 1 is in sequence #3
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/ora11g/redo03.log
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 3: '/u01/app/oracle/oradata/ora11g/data_tbs01.dbf'
ORA-01112: media recovery not started● 6. 백업컨트롤 파일 trace 남기기
SQL> alter database backup controlfile to trace as '/home/oracle/20240119.sql';
Database altered- trace를 보고 새롭게 추가된것 보기
[oracle@oracle ~]$ vi 20240119.sql
---
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORA11G" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/ora11g/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/oradata/ora11g/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/oradata/ora11g/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/ora11g/system01.dbf',
'/u01/app/oracle/oradata/ora11g/sysaux01.dbf',
'/u01/app/oracle/product/11.2.0.4/db_1/dbs/UNNAMED00003',
'/u01/app/oracle/oradata/ora11g/users01.dbf',
'/u01/app/oracle/oradata/ora11g/example01.dbf',
'/u01/app/oracle/oradata/ora11g/undotbs01.dbf'
CHARACTER SET AL32UTF8
;- 이쿼리문으로도 확인 가능
select a.file#, b.name tbs_name, a.name file_name,
a.status, a.checkpoint_change#
from v$datafile a, v$tablespace b
where a.ts# = b.ts#;
---
1 SYSTEM /u01/app/oracle/oradata/ora11g/system01.dbf SYSTEM 1588754
2 SYSAUX /u01/app/oracle/oradata/ora11g/sysaux01.dbf ONLINE 1588754
4 USERS /u01/app/oracle/oradata/ora11g/users01.dbf ONLINE 1588754
5 EXAMPLE /u01/app/oracle/oradata/ora11g/example01.dbf ONLINE 1588754
6 UNDOTBS /u01/app/oracle/oradata/ora11g/undotbs01.dbf ONLINE 1588754
3 DATA_TBS /u01/app/oracle/product/11.2.0.4/db_1/dbs/UNNAMED00003 RECOVER 1589561
---● 7. controlfile 백업본 이후 rename 작업
- controlfile에 없는 datafile 을 임시적으로 만든 이름을 -> 백업후에 추가된 datafile, tablespae 로 이름으로 변경해준다.
SQL> alter database rename file '/u01/app/oracle/product/11.2.0.4/db_1/dbs/UNNAMED00003'
to '/u01/app/oracle/oradata/ora11g/data_tbs01.dbf'; 2
Database altered.● 8. recover 하고 db open
: 다시 현재 current한 redolog 그룹을 넣어준다.
SQL> recover database using backup controlfile
ORA-00279: change 1589563 generated at 01/13/2024 18:20:38 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_3_1158057974.arc
ORA-00280: change 1589563 for thread 1 is in sequence #3
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/ora11g/redo03.log
Log applied.
Media recovery complete.- resetlogs db open
SQL> alter database open resetlogs;
Database altered.- 확인
select f.file_naㄱme
from dba_extents e, dba_data_files f
where e.file_id = f.file_id
and e.segment_name in ('EMP_50','EMP_20')
and e.owner = 'HR';
----
FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ora11g/users01.dbf
/u01/app/oracle/oradata/ora11g/data_tbs01.dbf▶ 23. 시간을 기준으로 데이터베이스를 복구 시나리오
- timebase recovery 할 경우
- drop tablespace|table 잘못했을때
- truncate 잘못했을때
- update, delete를 잘못하고 commit했을때
● 1. 작업
- 테이블스페이스 생성
CREATE TABLESPACE insa_tbs
DATAFILE '/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf' SIZE 10M
AUTOEXTEND ON NEXT 1M MAXSIZE 20M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT AUTO;- datafile, tablesapce 조회
select a.file#, b.name tbs_name, a.name file_name,
a.status, a.checkpoint_change#
from v$datafile a, v$tablespace b
where a.ts# = b.ts#;- control file trace 남기기
SQL> alter database backup controlfile to trace as '/home/oracle/control_20240119.sql';
Database altered.- 로그스위치 발생(운영중이라서 로그스위치 발생했다고 생각)
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.● 2. 장애발생 : 테이블스페이스삭제 (데이터파일에 테이블 있다고 가정)
- 테이블스페이스 삭제
SQL> drop tablespace insa_tbs including contents and datafiles;
Tablespace dropped.- alert log 창에 테이블스페이스 drop 한 시간이 나온다.
Sat Jan 13 19:39:21 2024
drop tablespace insa_tbs including contents and datafiles
Deleted file /u01/app/oracle/oradata/ora11g/insa_tbs01.dbf
Completed: drop tablespace insa_tbs including contents and datafiles● 3. 백업본 control file을 resotre
- db 내리기
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.- 백업본 datafile로 restore
SQL> ! cp -av /home/oracle/backup/arch/cold_20240119/*.dbf /u01/app/oracle/oradata/ora11g/
‘/home/oracle/backup/arch/cold_20240119/example01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/example01.dbf’
‘/home/oracle/backup/arch/cold_20240119/insa_tbs01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf’
‘/home/oracle/backup/arch/cold_20240119/sysaux01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/sysaux01.dbf’
‘/home/oracle/backup/arch/cold_20240119/system01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/system01.dbf’
‘/home/oracle/backup/arch/cold_20240119/temp01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/temp01.dbf’
‘/home/oracle/backup/arch/cold_20240119/undotbs01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/undotbs01.dbf’
‘/home/oracle/backup/arch/cold_20240119/users01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/users01.dbf’● 4. time base recovery
- 날짜 포맷변경 (보기편하게)
SQL> alter session set nls_date_format ='yyyy/mm/dd hh24:mi:ss';
Session altered.- drop 테이블스페이스 이전으로 time base recovery
SQL> recover database until time '2024/01/13 19:38:00'
ORA-00279: change 1590203 generated at 01/13/2024 19:33:07 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/arch_1_1_1158057974.arc
ORA-00280: change 1590203 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
Log applied.
Media recovery complete.
SQL> recover database open resetlogs;
ORA-00905: missing keyword
- alertlog 창에 보이는 경우는
테이블스페이스 레벨은 보이지만
세그먼트레벨은 안보이므로 테이블 작업을할경우는
redologfile을 봐야한다.- 또한, 현장에서는 이렇게하면 data의 갭이 발생하므로 clone db를 만들고 실행한다.
- open resetlogs
SQL> alter database open resetlogs;
Database altered.
- 주의
: resetlogs 를 실행한 이후는 당연히 전의 백업본을 사용못한다.
다시 백업 해놔야한다.
DB 공부를 하고 있는 사람입니다. 글을 읽어주셔서 감사하고 더 좋은 글을 쓰기 위해 노력하겠습니다 :)