Offline 되는 일반 Tablespace 장애 복구하기

★ 장애상황 운영중인 DB중 teest01.dbf가 삭제된것을 발견 다행이 백업파일은 존재하며, 현재 DB는 OPEN 상태 DB종료없이 빨리 복구를 해야함. 

 

SQL> startup mount
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1272600 bytes
Variable Size              62915816 bytes
Database Buffers          100663296 bytes
Redo Buffers                2920448 bytes
Database mounted.

SQL> alter database archivelog;

SQL> archive log list;

 

 

장애를 재연하기위해 hot backup 실시

 

SQL> alter tablespace test begin backup;

Tablespace altered.

 

SQL> !cp /home/oracle/oradata/testdb/test01.dbf /data/backup/open/

 

SQL> alter tablespace test end backup;

Tablespace altered.

 

SQL> !rm -rf /home/oracle/oradata/testdb/test01.dbf <-장애 시작

 

SQL> create table abc (no number) tablespace test;

Table created.

 

SQL> insert into abc values(1);

1 row created.

 

SQL> ed
Wrote file afiedt.buf

  1* insert into abc values(2)
SQL> /

1 row created.

 

SQL> commit;

Commit complete.

 

SQL> select * from abc;

 

-------------------------------------------------------------------------------------------

★ 장애 발생 확인하기

 

SQL> alter tablespace test offline; <- test offline

Tablespace altered.

 

SQL> alter tablespace test online; <- test online
alter tablespace test online
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/home/oracle/oradata/testdb/test01.dbf' 

-------------------------------------------------------------------------------------------

★ 백업으로부터 복원후 복구하기!!

SQL> !cp /data/backup/open/test01.dbf /home/oracle/oradata/testdb/

 Restore 복원

 

SQL> recover tablespace test; <- 해당 tablespace test 복구중
Media recovery complete.

SQL> alter tablespace test online;

Tablespace altered.