6장 Datafile 복구하기 (물리적 복구)

[방금 사용한 쿼리를 저장하기!!] 열라신기 

 save dd.sql

[Recover 종류]

Recover database 

Recover tablespace

Recover datafile 

◎ 원리 

 전에 있던 back up data file을 가고 와서(Restore 복원) Redo log & Archive log File을 통해서 현재의 시점(SCN)

 까지 복구 하는 기술

 

※만일 bacup 없으면 Oracle 명령어로 backup DataFile을 만들 수 있음 

※ 장애가 발생하면 해당 Data File I/O를 없애고(OFFLINE, shutdown) 작업을 해야함 →(복사,복구,이동등)

◎ 완전 복구

□  no Archive 일때 (추가적인 log 파일이 없을때 )

 ① 데이터 파일 삭제 장애 

  [상황]

① alter database mount 

② alter database noarchivelog;

③ alter database open;

④ 현재 talblespace 확인후 TEST 로 만들어줌 

 create tablespace test01

  datafile '/app/oracle/oradata/testdb/test01.dbf' size 30M

  autoextend on ;

⑤ 혹시 몰라서 Data back up 해줌 

⑥ startup을 해준 후에 만든 Tablespace에 데이터를 입력 (redo log에 기록됨) 

  create table scott.tt700(no number)

  tablespace test01;

  insert into scott.tt700 values(1);

  insert into scott.tt700 values(2);

⑦  데이터 파일 삭제

⑧ alter system logfile;

[에러 화면]

ERROR at line 1:

ORA-03113: end-of-file on communication channel

Process ID: 31911

Session ID: 17 Serial number: 3

[alter에 기록]

ORA-01242: data file suffered media failure: database in NOARCHIVELOG mode

ORA-01116: error in opening database file 6

ORA-01110: data file 6: '/app/oracle/oradata/testdb/test01.dbf'

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

CKPT (ospid: 31838): terminating the instance due to error 1242

Instance terminated by CKPT, pid = 31838

⑨ 다시 startup 

[화면]

SQL> startup

ORACLE instance started.

Total System Global Area  422670336 bytes

Fixed Size                  1336960 bytes

Variable Size             260049280 bytes

Database Buffers          155189248 bytes

Redo Buffers                6094848 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 6 - see DBWR trace file

ORA-01110: data file 6: '/app/oracle/oradata/testdb/test01.dbf'

[alter ]

ALTER DATABASE OPEN

Errors in file /app/oracle/diag/rdbms/testdb/testdb/trace/testdb_dbw0_32048.trc:

ORA-01157: cannot identify/lock data file 6 - see DBWR trace file

ORA-01110: data file 6: '/app/oracle/oradata/testdb/test01.dbf'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

Errors in file /app/oracle/diag/rdbms/testdb/testdb/trace/testdb_ora_32130.trc:

ORA-01157: cannot identify/lock data file 6 - see DBWR trace file

ORA-01110: data file 6: '/app/oracle/oradata/testdb/test01.dbf'

ORA-1157 signalled during: ALTER DATABASE OPEN...

Mon Mar 30 05:53:37 2015

Checker run found 1 new persistent data failures

전체 파일 복구 (나중에)

 삭제가 되었지만 백업되어 있는 파일을 가져오지만 System Change Number 가달라서 켜지지 못함 

  그것을 변환하는 작업 

장애 난파일 빼고 복구 하는경우 

⑩ alter database datafile '/app/oracle/oradata/testdb/test01.dbf' offline drop;

[alter file]

alter database datafile '/app/oracle/oradata/testdb/test01.dbf' offline drop

Completed: alter database datafile '/app/oracle/oradata/testdb/test01.dbf' offline drop

      ※ no Archive 모드라도 Redo log 에 정보가 있으면 복구 가능

□  Archive 일때 (추가적인 log 파일이 있어서 복구 가능할때)

  ① 무정지 상태에서의 복구 (Offline 되는 tablespace)

 [상황]

① archive log 모드로 변경 후에 open 

② tablespace 1개 생성

create tablespace test02

  datafile '/app/oracle/oradata/testdb/test01.dbf' size 30M

  autoextend on ;

③ 정상 종료후 혹시모르니 backup 

④ 데이터 입력 

   create table scott.tt700(no number)

tablespace test02;

insert into scott.tt700 values(1);

commit ;

⑤ data File 삭제하기 

⑥ alter tablespace test02 offline;

[OFF → ON 하면 에러]

SQL> alter tablespace test02 online;

alter tablespace test02 online

*

ERROR at line 1:

ORA-01157: cannot identify/lock data file 6 - see DBWR trace file

ORA-01110: data file 6: '/app/oracle/oradata/testdb/test02.dbf'

[alter log]

alter tablespace test02 online

Mon Mar 30 06:28:45 2015

Errors in file /app/oracle/diag/rdbms/testdb/testdb/trace/testdb_dbw0_32621.trc:

ORA-01157: cannot identify/lock data file 6 - see DBWR trace file

ORA-01110: data file 6: '/app/oracle/oradata/testdb/test02.dbf'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

⑦ 백업에서 test02 복사하기 

⑧ 다시 online

[화면]

SQL> alter tablespace test02 online;

alter tablespace test02 online

*

ERROR at line 1:

ORA-01113: file 6 needs media recovery

ORA-01110: data file 6: '/app/oracle/oradata/testdb/test02.dbf'

[alter log]

Mon Mar 30 06:32:29 2015

alter tablespace test02 online

ORA-1113 signalled during: alter tablespace test02 online...

Mon Mar 30 06:32:30 2015

Checker run found 1 new persistent data failures

⑨  recover tablespace test02;

⑩ alter tablespace test02 online;

② DB 정지 후에 복구 (OFFLIne 안되는 tablespace)

①  system 에 table입력 

  create table scott.tt800(no number) tablespace system;

insert into scott.tt800 values(1);

insert into scott.tt800 values(2);

commit ;

select * from scott.tt800;

② rm system01.dbf

③ 정상 종료 시키기  

④ startup

SQL> startup

ORACLE instance started.

Total System Global Area  422670336 bytes

Fixed Size                  1336960 bytes

Variable Size             260049280 bytes

Database Buffers          155189248 bytes

Redo Buffers                6094848 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 1 - see DBWR trace file

ORA-01110: data file 1: '/app/oracle/oradata/testdb/system01.dbf'

[alter]

ALTER DATABASE OPEN

Errors in file /app/oracle/diag/rdbms/testdb/testdb/trace/testdb_dbw0_472.trc:

ORA-01157: cannot identify/lock data file 1 - see DBWR trace file

ORA-01110: data file 1: '/app/oracle/oradata/testdb/system01.dbf'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

Errors in file /app/oracle/diag/rdbms/testdb/testdb/trace/testdb_ora_559.trc:

ORA-01157: cannot identify/lock data file 1 - see DBWR trace file

ORA-01110: data file 1: '/app/oracle/oradata/testdb/system01.dbf'

ORA-1157 signalled during: ALTER DATABASE OPEN...

Mon Mar 30 06:44:56 2015

Checker run found 1 new persistent data failures

⑤ 백업 본으로 부터 복사하기 

⑥ recover table space system 수행

[alter ]

ALTER DATABASE RECOVER  tablespace system

Media Recovery Start

Serial Media Recovery started

Recovery of Online Redo Log: Thread 1 Group 2 Seq 14 Reading mem 0

  Mem# 0: /app/oracle/oradata/testdb/redo02.log

  Mem# 1: /app/oracle/oradata/testdb/redo02_b.log

Media Recovery Complete (testdb)

Completed: ALTER DATABASE RECOVER  tablespace system

⑦  alter database open ; 

    select * from scott.tt800;

③ backup 이 없는 상태에서 복구 

① 특정 tablespace 삭제 

② 삭제된 tablespace에 데이터 입력 하기 

③ SQL> select * from scott.tt900;

select * from scott.tt900

                    

ERROR at line 1:

ORA-00376: file 6 cannot be read at this time

ORA-01110: data file 6: '/app/oracle/oradata/testdb/test02.dbf'

④ 백업 생성 하기 

SQL> alter database create datafile

  2  '/app/oracle/oradata/testdb/test02.dbf'

  3  as '/app/oracle/oradata/testdb/test02.dbf';

⑤ recover 명령 수행 

redo log 에 있으면 그냥 수 행 

archive에 있으면 

Specify log: {<RET>=suggested | filename | AUTO | CANCEL} 엔터

⑥ tablespace online