프린트 하기

내맘대로긍정이 알려주는

Oracle 23ai 신기능
무료 세미나 발표자료

다운로드 trending_flat

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

DB 문제로 인해 alter database backup controlfile to trace as '/home/oracle/trace.sql' 으로 
컨트롤파일 재생성 후 기동 시 실패

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
실패


6. using backup controlfile 넣고 시도(최신 아카이브 입력)
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


저작자표시

'ORACLE > Trouble Shooting' 카테고리의 다른 글

잘못 추가한 Datafile 삭제(alter tablespace drop datafile)  (8) 2018.03.27
오라클DB 시간 변경 Time drifts can result in an unexpected behavior such as time-outs. Please check trace file for more details.  (0) 2018.03.23
11gR2 Grid install – clscfg.bin: error while loading shared libraries: libcap.so.1: cannot open shared object file  (0) 2018.03.20
11gR2 Grid install – Error: ohasd failed to start the Clusterware.  (2) 2018.03.13
ORA-00600[1:dbktDefFileInit] 해결  (0) 2018.03.09

티스토리툴바