'alter'에 해당하는 글 1건

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

서버관리 2011.02.18 15:01

목적:

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

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


장애 재현:

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
, , , , ,
받은 트랙백이 없고 , 댓글이 없습니다.
secret