Offline 안되는 Tablespace 장애 복구하기

★ 장애상황 만들기~

 

SQL> select status from v$instance;

STATUS
------------------------
OPEN

 

SQL> desc test1
 Name                          Null?    Type
 NO                                                                                                                         NUMBER

 

SQL> drop table test1 purge; <- test1 바로 삭제

Table dropped.

 

SQL> create table test1 (no number) tablespace system;

Table created.

 

SQL> insert into test1 values(1);

1 row created.

 

SQL> commit;

Commit complete.

 

SQL> @dd

TABLESPACE         MB FILE_NAME
---------- ---------- --------------------------------------------------
UNDO               10 /home/oracle/oradata/testdb/undo01.dbf
TEST               10 /home/oracle/oradata/testdb/test01.dbf
EXAMPLE           100 /home/oracle/oradata/testdb/example01.dbf
USERS               5 /home/oracle/oradata/testdb/users01.dbf
SYSAUX            250 /home/oracle/oradata/testdb/sysaux01.dbf
SYSTEM            450 /home/oracle/oradata/testdb/system01.dbf

6 rows selected.

 

 

SQL> !rm /home/oracle/oradata/testdb/system01.dbf

 

SQL> alter tablespace system offline;
alter tablespace system offline
*
ERROR at line 1:
ORA-01541: system tablespace cannot be brought offline; shut down if necessary

SQL> shutdown immediate <- 저장 후 종료

 

★ 백업파일 복원

 

SQL> !cp /data/backup/close/system01.dbf /home/oracle/oradata/testdb/

 

SQL> startup
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.
ORA-01113: file 1 needs media recovery if it was restored from backup, or END BACKUP if it was not
ORA-01110: data file 1: '/home/oracle/oradata/testdb/system01.dbf'

SQL> recover database; <- 복구
Media recovery complete.

SQL> alter database open;

Database altered.

 

SQL> select * from test1;

        NO
----------

1