current한 redo group이 삭제된 후 복구 작업

목표: 현재 current한 리두그룹이 삭제된 후 !!DB가 정상적으로 종료!!
POINT: cancel base recovery, resetlogs

1. 사전작업
1) 조회

SELECT a.group#, b.sequence#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
FROM v$logfile a, v$log b
WHERE a.group#=b.group#
ORDER BY 1;

 

--> Current한 그룹 : 2

2) 테이블 생성

CREATE TABLE hr.new_loc as select * from hr.locations;
select count(*) from hr.new_loc;

2. 장애유발
1) current한 그룹 삭제
! ls /u01/app/oracle/oradata/ora11g/redo02.log
! rm /u01/app/oracle/oradata/ora11g/redo02.log
! ls /u01/app/oracle/oradata/ora11g/redo02.log

2) 삭제된지 모르고 DB정상적으로 내리기
SQL> shutdown immediate

3) startup
SQL> startup

ORA-03113: end-of-file on communication channel
Process ID: 4850
Session ID: 125 Serial number: 5
--> 있어야 할 위치에 redo log file이 없음

4) 재접속
SQL> conn / as sysdba

5) startup mount
--> 복구하기위해서 마운트까지 올림

6) 복구작업
-- cancel base recovery 해야함.
SQL> recover database until cancel

--> ★CURRENT한 리두 그룹은 깨졌으니★ 복구안해도되고 전까지 복구해야하니 cancel base recovery함
--> why? DB를 정상적으로 종료했으니, commit된 더티버퍼들이 디스크에 다 내려갔고, 트랜잭션이 진행 중이던 데이터는 전부 롤백됐으니 복구해야할 대상이 없기때문이다.
SQL> alter database open resetlogs;

SQL> ! ls /u01/app/oracle/oradata/ora11g/redo02.log

--> resetlogs하니까 삭제되었던 redo log file이 재생성됨

 

select count(*) from hr.new_loc;

--> 조회됨 why? DB를 정상적으로 종료했으니, commit된 더티버퍼들이 디스크에 다 내려갔고, 트랜잭션이 진행 중이던 데이터는 전부 롤백됐으니 복구해야할 대상이 없기때문이다. 즉, 정상적인 종료했을때 CKPT가 발생하고 COMMIT된 더티버퍼가 디스크에 내려갔으니 회복된다.

7) 조회

SELECT a.group#, b.sequence#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
FROM v$logfile a, v$log b
WHERE a.group#=b.group#
ORDER BY 1;

8) cold, hot 백업 받아야함

SQL> shutdown immediate

다음 시나리오를 위해 복구하자(최근에 받았던걸로 cold backup recovery하자)
1)
[oracle@oracle cold_20240117]$ cp -av *.dbf /u01/app/oracle/oradata/ora11g/
[oracle@oracle cold_20240117]$ cp -av *.ctl /u01/app/oracle/oradata/ora11g/
[oracle@oracle cold_20240117]$ cp -av *.log /u01/app/oracle/oradata/ora11g/

2) 아카이브 파일 지우기
[oracle@oracle ~]$ cd arch1
[oracle@oracle arch1]$ ls
arch_1_1_1158491749.arc  arch_1_2_1158491749.arc
[oracle@oracle arch1]$ rm *.*

3)startup

4) alter system switch logfile;

alter system switch logfile;

5)

SELECT a.group#, b.sequence#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
FROM v$logfile a, v$log b
WHERE a.group#=b.group#
ORDER BY 1;