'닫힌백업'에 해당하는 글 1건



완전복구 : 닫힌 데이터 베이스 복구


장애:
데이터 파일중 system 테이블 스페이스나 활성화된 undo(rollback) 세그먼트를 포함한
테이블스페이스에 포함된 데이터 파일이 손상
장애재현:
[root@oracle orcl]# cd /oracle/oradata/orcl/
[root@oracle orcl]# ls
control01.ctl  example01.dbf  guk.dbf  redo01.log  redo02.log  redo03.log  sysaux01.dbf  system01.dbf  temp01.dbf  test.dbf  undotbs01.dbf  users01.dbf


여기서 system01.dbf 파일을 실수로 지워 버렸다.

[root@oracle orcl]# rm system01.dbf 
rm: remove regular file `system01.dbf'? y



복구:


오라클을 종료하면서 파일이 삭제된것을 확인하였다.
SQL> shutdown immediate
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/oracle/oradata/orcl/system01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3


SQL> shutdown abort
ORACLE instance shut down.


SQL> startup mount
ORACLE instance started.

Total System Global Area  238530560 bytes
Fixed Size                  1335724 bytes
Variable Size             150998612 bytes
Database Buffers           83886080 bytes
Redo Buffers                2310144 bytes
Database mounted.


확인 

SQL> col tablespace_name format a15
SQL> col name format a40
SQL> col error format a10
SQL> select h.tablespace_name,d.name,h.error from v$datafile d,v$datafile_header h where d.file# = h.file#;

TABLESPACE_NAME NAME                                     ERROR
--------------- ---------------------------------------- ----------
                /oracle/oradata/orcl/system01.dbf        FILE NOT
                                                         FOUND

SYSAUX          /oracle/oradata/orcl/sysaux01.dbf
UNDOTBS1        /oracle/oradata/orcl/undotbs01.dbf
USERS           /oracle/oradata/orcl/users01.dbf
EXAMPLE         /oracle/oradata/orcl/example01.dbf
GUK             /oracle/oradata/orcl/guk.dbf
TEST            /oracle/oradata/orcl/test.dbf

7 rows selected.


백업자료로 부터 복사

SQL> !cp /backup/close/20110217/oradata/system01.dbf /oracle/oradata/orcl

recover 명령으로 파일 복구

SQL> set autorecovery on

SQL> recover datafile '/oracle/oradata/orcl/system01.dbf'
ORA-00279: change 945400 generated at 02/17/2011 16:39:57 needed for thread 1
ORA-00289: suggestion :
/oracle/flash_recovery_area/ORCL/archivelog/2011_02_17/o1_mf_1_10_6osq55bn_.arc
ORA-00280: change 945400 for thread 1 is in sequence #10


ORA-00279: change 951877 generated at 02/17/2011 17:30:28 needed for thread 1
ORA-00289: suggestion :
/oracle/flash_recovery_area/ORCL/archivelog/2011_02_17/o1_mf_1_11_6osq99oz_.arc
ORA-00280: change 951877 for thread 1 is in sequence #11


ORA-00279: change 952160 generated at 02/17/2011 17:32:41 needed for thread 1
ORA-00289: suggestion :
/oracle/flash_recovery_area/ORCL/archivelog/2011_02_17/o1_mf_1_12_6osqz7vm_.arc
ORA-00280: change 952160 for thread 1 is in sequence #12


ORA-00279: change 953139 generated at 02/17/2011 17:44:23 needed for thread 1
ORA-00289: suggestion :
/oracle/flash_recovery_area/ORCL/archivelog/2011_02_17/o1_mf_1_13_6osr2031_.arc
ORA-00280: change 953139 for thread 1 is in sequence #13


ORA-00279: change 953301 generated at 02/17/2011 17:45:51 needed for thread 1
ORA-00289: suggestion :
/oracle/flash_recovery_area/ORCL/archivelog/2011_02_17/o1_mf_1_14_6ot725sz_.arc
ORA-00280: change 953301 for thread 1 is in sequence #14


Log applied.
Media recovery complete.


DB 오픈

SQL> alter database open;

Database altered.



확인 해 보면 정상적으로 복구된것을 확인할 수 있다.

SQL> select h.tablespace_name,d.name,h.error from v$datafile d,v$datafile_header h where d.file# = h.file#;

TABLESPACE_NAME NAME                                     ERROR
--------------- ---------------------------------------- ----------
SYSTEM          /oracle/oradata/orcl/system01.dbf
SYSAUX          /oracle/oradata/orcl/sysaux01.dbf
UNDOTBS1        /oracle/oradata/orcl/undotbs01.dbf
USERS           /oracle/oradata/orcl/users01.dbf
EXAMPLE         /oracle/oradata/orcl/example01.dbf
GUK             /oracle/oradata/orcl/guk.dbf
TEST            /oracle/oradata/orcl/test.dbf

7 rows selected.

유저 접속 잘된다.

SQL> connect test/1111
Connected.
SQL> select * from test;

        ID
----------
         1
         2

저작자 표시 비영리 변경 금지
신고

WRITTEN BY
김병국
유용했던 자료를 기록해 두었습니다. 도움이 되시길~~~ Welcome! I started this blog as a way to give back to all of the other system administrators who have taught me something in the past. Writing these posts brings me a lot of enjoyment and I hope you fun

받은 트랙백이 없고 , 댓글이 없습니다.
secret