undo tablespace 장애 복구

- 운영중인 undo tablespace 장애 복구 

 

undo_management=auto <- manual 로 변경

_offline_rollback_segments=(_SYSSMU6$)

 

해당 undo 파일 offline drop 시킨후 오픈시킨다.

 

undo tablespace 생성한다음 undo01

 

alter system set undo_tablespace=undo01 변경

 

고장난 undo는 삭제를한다.

 

- No archive log mode 에서 undo tablespace 의 Data file이 삭제 된 장애 복구하기

 

SQL> create table scott.tt01(no number) tablespace example;

Table created.

 

SQL> insert into scott.tt01 values(1);

1 row created.

 

SQL> ed
Wrote file afiedt.buf

  1* insert into scott.tt01 values(2)
SQL> /

1 row created.

 

SQL> alter system switch logfile;

System altered.

 

SQL> /

System altered.

 

SQL> /
alter system switch logfile
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel <- DB 비정상 종료가 됨

 

 

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-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/home/oracle/oradata/testdb/undo01.dbf'

 

SQL> startup abort

 

 

*.undo_management='manual'
#*.undo_tablespace='UNDO'
_offline_rollback_segments=(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,_SYSSMU4$,_SYSSMU5$,_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$)

 

SQL> startup mount

 

SQL> alter database datafile '/home/oracle/oradata/testdb/undo01.dbf' offline drop;

 

SQL> alter database open;

 

SQL> create undo tablespace undotbs1
         datafile '/home/oracle/oradata/testdb/undotbs01.dbf' size 10m

 

SQL> SHUTDOWN immediate

 

SQL> !vi $ORACLE_HOME/dbs/inittestdb.ora

 

*.undo_management='auto'
*.undo_tablespace='undotbs1'