목적
임의의 디렉토리에 복구
장애
shutdown 상태에서 users01.dbf 삭제

아카이브 로그 파일의 수 늘리기 위해 로그 스위치 강제 실행
SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.


SQL> col datafile format a45
SQL> select v$tablespace.name TS, v$datafile.name datafile from v$tablespace,v$datafile where v$datafile.ts#=v$tablespace.ts#;

TS                             DATAFILE
------------------------------ ---------------------------------------------
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> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.


파일삭제 장애 재현
SQL> !rm /oracle/oradata/orcl/users01.dbf



복구

데이터 베이스 마운트 단계까지 시작

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
--------------- ---------------------------------------- ----------
SYSTEM          /oracle/oradata/orcl/system01.dbf
SYSAUX          /oracle/oradata/orcl/sysaux01.dbf
UNDOTBS1        /oracle/oradata/orcl/undotbs01.dbf
                /oracle/oradata/orcl/users01.dbf         FILE NOT                          ---> 이파일이 장애
                                                         FOUND

EXAMPLE         /oracle/oradata/orcl/example01.dbf
GUK             /oracle/oradata/orcl/guk.dbf
TEST            /oracle/oradata/orcl/test.dbf

7 rows selected.


문제 파일 offline 변경

SQL> alter database datafile '/oracle/oradata/orcl/users01.dbf' offline;

Database altered.


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

     FILE# NAME                                     STATUS  STATUS
---------- ---------------------------------------- ------- -------
         1 /oracle/oradata/orcl/system01.dbf        SYSTEM  ONLINE
         2 /oracle/oradata/orcl/sysaux01.dbf        ONLINE  ONLINE
         3 /oracle/oradata/orcl/undotbs01.dbf       ONLINE  ONLINE
         4 /oracle/oradata/orcl/users01.dbf         OFFLINE OFFLINE                 ---> 변경된것 확인됨.
         5 /oracle/oradata/orcl/example01.dbf       ONLINE  ONLINE
         6 /oracle/oradata/orcl/guk.dbf             ONLINE  ONLINE
         7 /oracle/oradata/orcl/test.dbf            ONLINE  ONLINE

7 rows selected.


SQL> alter database open;

Database altered.


장애 파일 백업본을 임의의 디렉토리에 복사

SQL> !mkdir /oracle/temp

SQL> !cp /backup/close/20110217/oradata/users01.dbf /oracle/temp/

SQL> !ls /oracle/temp
users01.dbf


SQL> alter database rename file '/oracle/oradata/orcl/users01.dbf' to '/oracle/temp/users01.dbf';

Database altered.


SQL> select file_name , tablespace_name from dba_data_files;

FILE_NAME                                TABLESPACE_NAME
---------------------------------------- ---------------
/oracle/temp/users01.dbf                 USERS                             ---> 위치가 변경된것 확인됨.
/oracle/oradata/orcl/undotbs01.dbf       UNDOTBS1
/oracle/oradata/orcl/sysaux01.dbf        SYSAUX
/oracle/oradata/orcl/system01.dbf        SYSTEM
/oracle/oradata/orcl/example01.dbf       EXAMPLE
/oracle/oradata/orcl/guk.dbf             GUK
/oracle/oradata/orcl/test.dbf            TEST

7 rows selected.


recover 명령으로 파일 복구

SQL> recover datafile '/oracle/temp/users01.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


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
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


ORA-00279: change 973479 generated at 02/17/2011 22:01:56 needed for thread 1
ORA-00289: suggestion :
/oracle/flash_recovery_area/ORCL/archivelog/2011_02_17/o1_mf_1_15_6ot94gdz_.arc
ORA-00280: change 973479 for thread 1 is in sequence #15


ORA-00279: change 978379 generated at 02/17/2011 22:37:18 needed for thread 1
ORA-00289: suggestion :
/oracle/flash_recovery_area/ORCL/archivelog/2011_02_18/o1_mf_1_16_6ov0wgtn_.arc
ORA-00280: change 978379 for thread 1 is in sequence #16


ORA-00279: change 1009050 generated at 02/18/2011 05:22:37 needed for thread 1
ORA-00289: suggestion :
/oracle/flash_recovery_area/ORCL/archivelog/2011_02_18/o1_mf_1_17_6ovng7j2_.arc
ORA-00280: change 1009050 for thread 1 is in sequence #17


ORA-00279: change 1052352 generated at 02/18/2011 10:56:22 needed for thread 1
ORA-00289: suggestion :
/oracle/flash_recovery_area/ORCL/archivelog/2011_02_18/o1_mf_1_18_6ow143ph_.arc
ORA-00280: change 1052352 for thread 1 is in sequence #18

ORA-00279: change 1070284 generated at 02/18/2011 14:32:51 needed for thread 1
ORA-00289: suggestion :
/oracle/flash_recovery_area/ORCL/archivelog/2011_02_18/o1_mf_1_19_6ow4nh9r_.arc
ORA-00280: change 1070284 for thread 1 is in sequence #19


ORA-00279: change 1074904 generated at 02/18/2011 15:32:47 needed for thread 1
ORA-00289: suggestion :
/oracle/flash_recovery_area/ORCL/archivelog/2011_02_18/o1_mf_1_20_6ow4np4n_.arc
ORA-00280: change 1074904 for thread 1 is in sequence #20


Log applied.
Media recovery complete.


복구파일 online 으로 변경

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

     FILE# NAME                                     STATUS  STATUS
---------- ---------------------------------------- ------- -------
         1 /oracle/oradata/orcl/system01.dbf        SYSTEM  ONLINE
         2 /oracle/oradata/orcl/sysaux01.dbf        ONLINE  ONLINE
         3 /oracle/oradata/orcl/undotbs01.dbf       ONLINE  ONLINE
         4 /oracle/temp/users01.dbf                 OFFLINE OFFLINE
         5 /oracle/oradata/orcl/example01.dbf       ONLINE  ONLINE
         6 /oracle/oradata/orcl/guk.dbf             ONLINE  ONLINE
         7 /oracle/oradata/orcl/test.dbf            ONLINE  ONLINE

7 rows selected.


SQL> alter database datafile '/oracle/temp/users01.dbf' online;

Database altered.


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

     FILE# NAME                                     STATUS  STATUS
---------- ---------------------------------------- ------- -------
         1 /oracle/oradata/orcl/system01.dbf        SYSTEM  ONLINE
         2 /oracle/oradata/orcl/sysaux01.dbf        ONLINE  ONLINE
         3 /oracle/oradata/orcl/undotbs01.dbf       ONLINE  ONLINE
         4 /oracle/temp/users01.dbf                 ONLINE  ONLINE
         5 /oracle/oradata/orcl/example01.dbf       ONLINE  ONLINE
         6 /oracle/oradata/orcl/guk.dbf             ONLINE  ONLINE
         7 /oracle/oradata/orcl/test.dbf            ONLINE  ONLINE

7 rows selected.


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/temp/users01.dbf
EXAMPLE         /oracle/oradata/orcl/example01.dbf
GUK             /oracle/oradata/orcl/guk.dbf
TEST            /oracle/oradata/orcl/test.dbf

7 rows selected.

저작자 표시 비영리 변경 금지
신고
크리에이티브 커먼즈 라이선스
Creative Commons License

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

장애:
실수로 데이터 파일을 지워버렸다.
[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
[root@oracle orcl]# rm -fv users01.dbf
removed `users01.dbf'


복구:

SQL> connect /as sysdba
Connected.

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

     FILE# NAME                                     STATUS  STATUS
---------- ---------------------------------------- ------- -------
         1 /oracle/oradata/orcl/system01.dbf        SYSTEM  ONLINE
         2 /oracle/oradata/orcl/sysaux01.dbf        ONLINE  ONLINE
         3 /oracle/oradata/orcl/undotbs01.dbf       ONLINE  ONLINE
         4 /oracle/oradata/orcl/users01.dbf         ONLINE  ONLINE
         5 /oracle/oradata/orcl/example01.dbf       ONLINE  ONLINE
         6 /oracle/oradata/orcl/guk.dbf             ONLINE  ONLINE
         7 /oracle/oradata/orcl/test.dbf            ONLINE  ONLINE

7 rows selected.


SQL> alter database datafile '/oracle/oradata/orcl/users01.dbf' offline;

Database altered.


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

     FILE# NAME                                     STATUS  STATUS
---------- ---------------------------------------- ------- -------
         1 /oracle/oradata/orcl/system01.dbf        SYSTEM  ONLINE
         2 /oracle/oradata/orcl/sysaux01.dbf        ONLINE  ONLINE
         3 /oracle/oradata/orcl/undotbs01.dbf       ONLINE  ONLINE
         4 /oracle/oradata/orcl/users01.dbf         RECOVER OFFLINE
         5 /oracle/oradata/orcl/example01.dbf       ONLINE  ONLINE
         6 /oracle/oradata/orcl/guk.dbf             ONLINE  ONLINE
         7 /oracle/oradata/orcl/test.dbf            ONLINE  ONLINE

7 rows selected.

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


SQL> recover datafile '/oracle/oradata/orcl/users01.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


ORA-00279: change 973479 generated at 02/17/2011 22:01:56 needed for thread 1
ORA-00289: suggestion :
/oracle/flash_recovery_area/ORCL/archivelog/2011_02_17/o1_mf_1_15_6ot94gdz_.arc
ORA-00280: change 973479 for thread 1 is in sequence #15


Log applied.
Media recovery complete.


SQL> alter database datafile '/oracle/oradata/orcl/users01.dbf' online;

Database altered.

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

     FILE# NAME                                     STATUS  STATUS
---------- ---------------------------------------- ------- -------
         1 /oracle/oradata/orcl/system01.dbf        SYSTEM  ONLINE
         2 /oracle/oradata/orcl/sysaux01.dbf        ONLINE  ONLINE
         3 /oracle/oradata/orcl/undotbs01.dbf       ONLINE  ONLINE
         4 /oracle/oradata/orcl/users01.dbf         ONLINE  ONLINE
         5 /oracle/oradata/orcl/example01.dbf       ONLINE  ONLINE
         6 /oracle/oradata/orcl/guk.dbf             ONLINE  ONLINE
         7 /oracle/oradata/orcl/test.dbf            ONLINE  ONLINE

7 rows selected.



저작자 표시 비영리 변경 금지
신고
크리에이티브 커먼즈 라이선스
Creative Commons License

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


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


장애:
데이터 파일중 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

저작자 표시 비영리 변경 금지
신고
크리에이티브 커먼즈 라이선스
Creative Commons License

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