꿈꾸는 개발자, DBA 커뮤니티 구루비

※ 제약 사항 : 데이터파일 생성 이후 모든 Archive 로그가 존재해야 함

example02.dbf 파일에 대해서 백업본이 없는 경우 복구하는 예제입니다.

데이터파일 추가

example테이블 스페이스에 example02.dbf 데이터파일을 추가 합니다.

 
SQL> ALTER TABLESPACE example
     ADD DATAFILE 'd:\oracle\oradata\ghlee\example02.dbf' SIZE 50m;
테이블 영역이 변경되었습니다.

로그파일 생성

 
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
System altered.

SQL> ARCHIVE LOG LIST;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination D:\archive
Oldest online log sequence 28
Next log sequence to archive 30
Current log sequence 30

기존의 백업 본으로 RESTORE

데이터베이스 종료 후 기존의 백업 본으로 Restore

기존백업에는 위에서 추가한 example02.dbf 파일이 존재하지 않음

 
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> host copy d:\backup\ghlee\*.ctl d:\oracle\oradata\ghlee\
d:\backup\ghlee\CONTROL01.CTL
d:\backup\ghlee\CONTROL02.CTL
d:\backup\ghlee\CONTROL03.CTL
3개 파일이 복사되었습니다.

SQL> host copy d:\backup\ghlee\*.dbf d:\oracle\oradata\ghlee\
d:\backup\ghlee\CWMLITE01.DBF
d:\backup\ghlee\DRSYS01.DBF
d:\backup\ghlee\EXAMPLE01.DBF
d:\backup\ghlee\INDX01.DBF
d:\backup\ghlee\ODM01.DBF
d:\backup\ghlee\SYSTEM01.DBF
d:\backup\ghlee\TEMP01.DBF
d:\backup\ghlee\TOOLS01.DBF
d:\backup\ghlee\UNDOTBS01.DBF
d:\backup\ghlee\USERS01.DBF
d:\backup\ghlee\XDB01.DBF
11개 파일이 복사되었습니다.

Database Starup

현재 로그와 CONTROL FILE, DATA FILE의 일관성이 맞지 않아, Database가 Open되지 않음

 
SQL> startup
ORACLE instance started.

Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
ORA-00314: log 1 of thread 1, expected sequence# doesn't match
ORA-00312: online log 1 thread 1:
'D:\ORACLE\ORADATA\GHLEE\REDO01.LOG'

Recovery 수행

 
-- Recovery시도 했으나 실패
SQL> RECOVER DATABASE;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required

-- 기존의 Controlfile을 가지고 복구 수행
SQL> RECOVER DATABASE USING BACKUP CONTROLFILE;
ORA-00279: change 922690 generated at 04/29/2008 15:25:51 needed for thread 1
ORA-00289: suggestion : D:\ARCHIVE\ARC00027.001
ORA-00280: change 922690 for thread 1 is in sequence #27


Specify log: {=suggested | filename | AUTO | CANCEL}
auto

ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to controlfile by media recovery
ORA-01110: data file 11:
'D:\ORACLE\ORADATA\GHLEE\EXAMPLE02.DBF'

* 위에서 보이는것 처럼 현재 Controlfile에 Archive 파일 27번에 의해서 Unnamed Datafile이 추가 되었음
(실제로 파일이 존재 하는 것이 아니라 Controlfile에만 추가 되었음)
ORA-01112: media recovery not started

SQL> SELECT file#, name, status FROM V$DATAFILE;

FILE# NAME                                      STATUS
----- ----------------------------------------  -------
1     D:\ORACLE\ORADATA\GHLEE\SYSTEM01.DBF      SYSTEM
2     D:\ORACLE\ORADATA\GHLEE\UNDOTBS01.DBF     ONLINE
3     D:\ORACLE\ORADATA\GHLEE\CWMLITE01.DBF     ONLINE
4     D:\ORACLE\ORADATA\GHLEE\DRSYS01.DBF       ONLINE
5     D:\ORACLE\ORADATA\GHLEE\EXAMPLE01.DBF     ONLINE
6     D:\ORACLE\ORADATA\GHLEE\INDX01.DBF        ONLINE
7     D:\ORACLE\ORADATA\GHLEE\ODM01.DBF         ONLINE
8     D:\ORACLE\ORADATA\GHLEE\TOOLS01.DBF       ONLINE
9     D:\ORACLE\ORADATA\GHLEE\USERS01.DBF       ONLINE
10    D:\ORACLE\ORADATA\GHLEE\XDB01.DBF         ONLINE
11    D:\ORACLE\ORA92\DATABASE\UNNAMED00011     RECOVER

-- 11번 파일이 unnamed라고 추가 되었음. 실제 데이터파일이 추가 된 건 아님


SQL> ALTER DATABASE RENAME FILE
     'D:\ORACLE\ORA92\DATABASE\UNNAMED00011' to
     'D:\oracle\oradata\ghlee\example02.dbf';
     
ALTER DATABASE RENAME FILE
'D:\ORACLE\ORA92\DATABASE\UNNAMED00011' to
'd:\oracle\oradata\ghlee\example02.dbf'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01516: nonexistent log file, datafile, or tempfile "
D:\ORACLE\ORA92\DATABASE\UNNAMED00011"

-- 실제 데이터파일이 존재 하지 않으므로 rename으로는 불가


-- 위에서 추가된 UNNAMED00011이름을 example02.dbf로 datafile을 생성
SQL> ALTER DATABASE CREATE DATAFILE
         'D:\ORACLE\ORA92\DATABASE\UNNAMED00011' AS
         'D:\ORACLE\ORADATA\GHLEE\EXAMPLE02.DBF';

Database altered.

-- 다시 Recover 명령을 이용 recovery수행
SQL> RECOVER DATABASE USING BACKUP CONTROLFILE;
ORA-00279: change 922826 generated at 05/07/2008 11:35:52 needed for thread 1
ORA-00289: suggestion : D:\ARCHIVE\ARC00027.001
ORA-00280: change 922826 for thread 1 is in sequence #27

Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 922921 generated at 05/07/2008 11:36:29 needed for thread 1
ORA-00289: suggestion : D:\ARCHIVE\ARC00028.001
ORA-00280: change 922921 for thread 1 is in sequence #28
ORA-00278: log file 'D:\ARCHIVE\ARC00027.001' no longer needed for this
recovery

ORA-00279: change 925497 generated at 05/07/2008 11:49:14 needed for thread 1
ORA-00289: suggestion : D:\ARCHIVE\ARC00029.001
ORA-00280: change 925497 for thread 1 is in sequence #29
ORA-00278: log file 'D:\ARCHIVE\ARC00028.001' no longer needed for this
recovery

ORA-00279: change 930459 generated at 05/07/2008 12:15:26 needed for thread 1
ORA-00289: suggestion : D:\ARCHIVE\ARC00030.001
ORA-00280: change 930459 for thread 1 is in sequence #30
ORA-00278: log file 'D:\ARCHIVE\ARC00029.001' no longer needed for this
recovery

ORA-00308: cannot open archived log 'D:\ARCHIVE\ARC00030.001'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) 지정된 파일을 찾을 수 없습니다.

SQL> RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE;
ORA-00279: change 930459 generated at 05/07/2008 12:15:26 needed for thread 1
ORA-00289: suggestion : D:\ARCHIVE\ARC00030.001
ORA-00280: change 930459 for thread 1 is in sequence #30
Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.

SQL> ARCHIVE LOG LIST;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination D:\archive
Oldest online log sequence 0
Next log sequence to archive 1
Current log sequence 1

SQL> SELECT file#, name, status FROM V$DATAFILE;

FILE# NAME STATUS
---------- ---------------------------------------- -------
1 D:\ORACLE\ORADATA\GHLEE\SYSTEM01.DBF SYSTEM
2 D:\ORACLE\ORADATA\GHLEE\UNDOTBS01.DBF ONLINE
3 D:\ORACLE\ORADATA\GHLEE\CWMLITE01.DBF ONLINE
4 D:\ORACLE\ORADATA\GHLEE\DRSYS01.DBF ONLINE
5 D:\ORACLE\ORADATA\GHLEE\EXAMPLE01.DBF ONLINE
6 D:\ORACLE\ORADATA\GHLEE\INDX01.DBF ONLINE
7 D:\ORACLE\ORADATA\GHLEE\ODM01.DBF ONLINE
8 D:\ORACLE\ORADATA\GHLEE\TOOLS01.DBF ONLINE
9 D:\ORACLE\ORADATA\GHLEE\USERS01.DBF ONLINE
10 D:\ORACLE\ORADATA\GHLEE\XDB01.DBF ONLINE
11 D:\ORACLE\ORADATA\GHLEE\EXAMPLE02.DBF ONLINE

11 rows selected.