OS환경 : Oracle Linux 6.8(64bit)
DB 환경 : Oracle Database 10.2.0.5
에러 : ORA-01113: file 1 needs media recovery if it was restored from backup, or END BACKUP if it was not
SYSTEM dbf 파일의 손상으로 인해 DB가 정상 기동되지 않음 여러번의 복구 시도 끝에 히든파라미터로 해결
1. Startup 시도
1 2 3 4 5 6 7 8 9 10 | SYS@orcl> startup ORACLE instance started. Total System Global Area 285212672 bytes Fixed Size 2095704 bytes Variable Size 184550824 bytes Database Buffers 92274688 bytes Redo Buffers 6291456 bytes Database mounted. ORA-01589: must use RESETLOGS or NORESETLOGS option for database open |
실패
2. noresetlogs 모드로 open 시도
1 2 3 4 5 | SYS@orcl> alter database open noresetlogs; alter database open noresetlogs * ERROR at line 1: ORA-01588: must use RESETLOGS option for database open |
실패
3. resetlogs 모드로 open 시도
1 2 3 4 5 6 | SYS@orcl> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-01113: file 1 needs media recovery if it was restored from backup, or END BACKUP if it was not ORA-01110: data file 1: '/oracle/app/oracle/oradata/orcl/system01.dbf' |
실패
4. datafile1(system01.dbf)파일 recover 시도
1 2 3 | SYS@orcl> recover datafile 1; ORA-00283: recovery session canceled due to errors ORA-01610: recovery using the BACKUP CONTROLFILE option must be done |
실패
5. using backup controlfile 넣고 시도(엔터)
1 2 3 4 5 6 7 8 9 10 11 | SYS@orcl> recover database using backup controlfile; ORA-00279: change 3380181 generated at 03/22/2018 13:38:53 needed for thread 1 ORA-00289: suggestion : /oracle/app/oracle/product/10.2.0/db_1/dbs/arch1_3_971442649.dbf ORA-00280: change 3380181 for thread 1 is in sequence #3 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} 엔터 ORA-00308: cannot open archived log '/oracle/app/oracle/product/10.2.0/db_1/dbs/arch1_3_971442649.dbf' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 |
1 2 3 4 5 6 7 8 9 | SYS@orcl> recover database using backup controlfile; ORA-00279: change 3380181 generated at 03/22/2018 13:38:53 needed for thread 1 ORA-00289: suggestion : /oracle/app/oracle/product/10.2.0/db_1/dbs/arch/1_3_971442649.dbf ORA-00280: change 3380181 for thread 1 is in sequence #3 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} /oracle/app/oracle/product/10.2.0/db_1/dbs/arch1_111_968680619.dbf(다른최신 아카이브입력) ORA-00342: archived log does not have expected resetlogs SCN 3337895 ORA-00334: archived log: '/oracle/app/oracle/product/10.2.0/db_1/dbs/arch1_111_968680619.dbf' |
7. until cancel 넣고 다시 시도(auto)
1 2 3 4 5 6 7 8 9 10 11 | SYS@orcl> recover database using backup controlfile until cancel; ORA-00279: change 3380181 generated at 03/22/2018 13:38:53 needed for thread 1 ORA-00289: suggestion : /oracle/app/oracle/product/10.2.0/db_1/dbs/arch1_3_971442649.dbf ORA-00280: change 3380181 for thread 1 is in sequence #3 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} auto ORA-00308: cannot open archived log '/oracle/app/oracle/product/10.2.0/db_1/dbs/arch1_3_971442649.dbf' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 |
실패
8. until cancel 넣고 다시 시도(cancel)
1 2 3 4 5 6 7 8 9 10 | SYS@orcl> recover database using backup controlfile until cancel; ORA-00279: change 3380181 generated at 03/22/2018 13:38:53 needed for thread 1 ORA-00289: suggestion : /oracle/app/oracle/product/10.2.0/db_1/dbs/arch1_3_971442649.dbf ORA-00280: change 3380181 for thread 1 is in sequence #3 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} cancel ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below ORA-01194: file 1 needs more recovery to be consistent ORA-01110: data file 1: '/oracle/app/oracle/oradata/orcl/system01.dbf' |
실패
해결 방법 : _allow_resetlogs_corruption=true 사용
1. shutdown immediate 로 종료
1 | SYS@orcl> shutdown immediate |
2. pfile에 히든파라미터 추가
1 2 3 4 | $ cd $ORACLE_HOME/dbs/ $ vi initorcl.ora 제일 밑에 아래 히든 파라미터 추가 _allow_resetlogs_corruption=true |
3. spfile있다면 mv명령어로 rename
1 | $ mv spfileorcl.ora spfileorcl.orabak |
4. sqlplus / as sysdba 접속 후 startup
1 | SYS@orcl> startup |
5. 정상기동 됨.
6. alter system switch logfile;로 로그스위치 해서 아카이브 로그파일 생성
1 2 3 4 | SYS@orcl> alter system switch logfile / / / |
7. shutdown immediate 후 pfile에서 히든파라미터 제거
1 2 | $ vi initorcl.ora 히든파라미터 제거 |
8. startup
1 | SYS@orcl> startup |
9. 정상으로 돌아옴
원인 :
ORA-01113: file 1 needs media recovery if it was restored from backup, or END BACKUP if it was not
=> resetlogs로 open 하기 전에는 recover 또는 restore를 먼저 한 뒤에 명령을 입력해야함
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
=> recover은 성공했지만 resetlogs로 open 해야함
참조 : https://positivemh.tistory.com/267
https://positivemh.tistory.com/268
https://startingpitcher.tistory.com/274
https://itsiti.com/ora-00342-archived-log-does-not-have-expected-resetlogs-scn-string