'alter'에 해당하는 글 1건

2011.02.18 [오라클] 오픈 데이터베이스 복구 2

[오라클] 오픈 데이터베이스 복구 2

서버관리 2011.02.18 15:01

share this post

목적:

손상된 데이터 파일 완전 복구

장애:

데이터 파일을 지워버렸다.

장애 재현:

SQL> connect /as sysdba

Connected.

SQL> connect /as sysdba

Connected.

SQL> connect test/1111

Connected.

SQL> select * from test;

----------

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;

----------

복구완료.

'서버관리' 카테고리의 다른 글

[오라클] 백업되지 않은 데이터파일 완전 복구 (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