목적:
손상된 데이터 파일 완전 복구
장애:
데이터 파일을 지워버렸다.
장애 재현:
SQL> connect /as sysdba
Connected.
SQL> connect /as sysdba
Connected.
SQL> connect test/1111
Connected.
SQL> select * from test;
ID
----------
1
2
3
SQL> commit;
Commit complete.
SQL> col table_name format a30
SQL> col owner format a20
SQL> select table_name,tablespace_name,owner from dba_tables where owner='TEST';
TABLE_NAME TABLESPACE_NAME OWNER
------------------------------ --------------- --------------------
TEST TEST TEST
SQL> alter system checkpoint;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> !rm /oracle/oradata/orcl/users01.dbf
SQL> !ls /oracle/oradata/orcl/user01.dbf
ls: /oracle/oradata/orcl/user01.dbf: No such file or directory
SQL> !ls /oracle/oradata/orcl/test.dbf
ls: /oracle/oradata/orcl/test.dbf: No such file or directory
SQL> !rm /oracle/oradata/orcl/test.dbf
복구
장애 파일 확인하고 오프라인으로 변경
SQL> col tablespace_name format a15
SQL> col error format a20
SQL> col name format a40
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 CANNOT
OPEN FILE
EXAMPLE /oracle/oradata/orcl/example01.dbf
GUK /oracle/oradata/orcl/guk.dbf
/oracle/oradata/orcl/test.dbf CANNOT
OPEN FILE
7 rows selected.
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> alter database datafile '/oracle/oradata/orcl/test.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 RECOVER OFFLINE
7 rows selected.
장애 파일 백업에서 복사
SQL> !cp /backup/close/20110217/oradata/users01.dbf /oracle/oradata/orcl/
SQL> !cp /backup/close/20110217/oradata/test.dbf /oracle/oradata/orcl/
recover 명령으로 파일 복구
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
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
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
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
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
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto ---> 이부분은 직접 입력(아카이브로그 파일이 자동 적용)
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
Log applied.
Media recovery complete.
SQL> recover datafile '/oracle/oradata/orcl/test.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
Log applied.
Media recovery complete.
SQL> alter database datafile '/oracle/oradata/orcl/users01.dbf' online;
Database altered.
SQL> alter database datafile '/oracle/oradata/orcl/test.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.
SQL> connect test/1111
Connected.
SQL> select * from test;
ID
----------
1
2
3
복구완료.
'서버관리' 카테고리의 다른 글
[오라클] 백업되지 않은 데이터파일 완전 복구 (0) | 2011.02.18 |
---|---|
[오라클] 임시 디렉토리를 이용한 데이터 베이스 복구 (0) | 2011.02.18 |
[오라클] 오픈 데이터베이스 복구 2 (0) | 2011.02.18 |
[오라클] sqlplus 사용시 이전 명령어 방향키로 나오게 하기 (0) | 2011.02.18 |
powershell 설치 (0) | 2011.02.18 |
[오라클] 오픈 상태의 데이터 베이스 복구 (0) | 2011.02.18 |
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
받은 트랙백이 없고
,
댓글이 없습니다.