ORA-00266: name of archived log file needed

OS환경 : Oracle Linux 6.8 (64bit)

DB 환경 : Oracle Database 11.2.0.4

에러 : ORA-00266: name of archived log file needed

rac db에서 single db로 clone 후 recover 명령으로 복구 시 발생하는 에러

1 2 3 4 5 6 7 8 9 10 11 12

SQL> recover database until time '2020-02-22:02:10:00' using backup controlfile; ORA-00279: change 241661 generated at 02/22/2020 01:39:05 needed for thread 1 ORA-00289: suggestion : /home/oracle/arch/rac11g_115970626162.arc ORA-00280: change 241661 for thread 1 is in sequence #15     Specify log: {<RET>=suggested | filename | AUTO | CANCEL} auto <-- auto 입력 시도 ORA-00279: change 241661 generated at  needed for thread 2     ORA-00266: name of archived log file needed

해결 방법 : 원본db(source rac db)에서 v$archived_log 를 조회해서 해당 파일을 확인

원본db에서 위에 나온 change 번호로 조회

1 2 3 4 5 6 7 8 9 10 11 12 13

SQL>  col name for a50 select thread#, FIRST_CHANGE#, NEXT_CHANGE#, name from v$archived_log where 241661 between FIRST_CHANGE# and NEXT_CHANGE# /      THREAD# FIRST_CHANGE# NEXT_CHANGE# NAME ---------- ------------- ------------ --------------------------------------------------      1      231611       244906 /home/oracle/arch/rac11g_115970626162.arc      2      194681       244918 /home/oracle/arch/rac11g_21970626162.arc   2 rows selected.

THREAD 1번 2번이 나오는데 

나의 경우는 ORA-00279: change 241661 generated at  needed for thread 2 로 표시되기 때문에 

2번 아카이브파일 경로(/home/oracle/arch/rac11g_21970626162.arc)를 입력해주면됨

THREAD 1번 파일을 입력해보면 똑같이 에러 발생함

SQL> recover database until time '2020-02-22:02:10:00' using backup controlfile;
ORA-00279: change 241661 generated at 02/22/2020 01:39:05 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch/rac11g_115970626162.arc
ORA-00280: change 241661 for thread 1 is in sequence #15
 
 
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/home/oracle/arch/rac11g_115970626162.arc
ORA-00279: change 241661 generated at  needed for thread 2

THREAD 2번 파일을 입력하면 no longer needed for this recovery(이 복구에 더 이상 필요하지 않음)이라고 표시됨

이후 auto 를 입력하면 자동으로 아카이브를 적용시킴

SQL> recover database until time '2020-02-22:02:10:00' using backup controlfile;
ORA-00279: change 241661 generated at 02/22/2020 01:39:05 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch/rac11g_115970626162.arc
ORA-00280: change 241661 for thread 1 is in sequence #15
 
 
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/home/oracle/arch/rac11g_21970626162.arc
ORA-00279: change 244906 generated at 02/22/2020 02:09:10 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch/rac11g_116970626162.arc
ORA-00280: change 244906 for thread 1 is in sequence #16
ORA-00278: log file '/home/oracle/arch/rac11g_115970626162.arc' no longer
needed for this recovery
 
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto  <- auto 입력
ORA-00279: change 244911 generated at 02/22/2020 02:09:14 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch/rac11g_117970626162.arc
ORA-00280: change 244911 for thread 1 is in sequence #17
ORA-00278: log file '/home/oracle/arch/rac11g_116970626162.arc' no longer
needed for this recovery
 
 
ORA-00279: change 244914 generated at 02/22/2020 02:09:14 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch/rac11g_118970626162.arc
ORA-00280: change 244914 for thread 1 is in sequence #18
ORA-00278: log file '/home/oracle/arch/rac11g_117970626162.arc' no longer
needed for this recovery
 
 
ORA-00279: change 244918 generated at 02/22/2020 02:09:15 needed for thread 2
ORA-00289: suggestion : /home/oracle/arch/rac11g_22970626162.arc
ORA-00280: change 244918 for thread 2 is in sequence #2
ORA-00278: log file '/home/oracle/arch/rac11g_21970626162.arc' no longer needed
for this recovery
 
 
ORA-00279: change 244921 generated at 02/22/2020 02:09:15 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch/rac11g_119970626162.arc
ORA-00280: change 244921 for thread 1 is in sequence #19
ORA-00278: log file '/home/oracle/arch/rac11g_118970626162.arc' no longer
needed for this recovery
 
 
ORA-00279: change 244932 generated at 02/22/2020 02:09:40 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch/rac11g_120970626162.arc
ORA-00280: change 244932 for thread 1 is in sequence #20
ORA-00278: log file '/home/oracle/arch/rac11g_119970626162.arc' no longer
needed for this recovery
 
 
ORA-00308: cannot open archived log '/home/oracle/arch/rac11g_120970626162.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

마지막 파일은 내가 원하지 않는 시간대여서 제외함

원인 : controfile에 해당 scn 를 가지고 있는 2번 노드의 archive file 이름이 등록되어 있지 않아서 발생

무정지복구 시 clone쪽에서 controfile이 재생성되어

controfile에 해당 scn 를 가지고 있는 2번 노드의 archive file 이름이 등록되어 있지 않아서 발생한 에러임

RAC에서 흔하게 발생하는 문제로, 해당 SCN 을 가지고 있는 아카이브 파일명만 

원본db(source rac db)서버의 v$archived_log에서 확인 후에 full path에 넣어주면 그 다음 파일부턴 AUTO로 복구가 가능함

참조 : 

https://m.blog.naver.com/PostView.nhn?blogId=hanccii&logNo=220985607102&proxyReferer=https%3A%2F%2Fwww.google.com%2F

https://gyh214.tistory.com/162