|
No. 20680
(9i)UNDO_MANAGEMENT을 AUTO에서 MANUAL로 바꾸는 방법
===================================================
Purpose
-------
undo_management mode을 AUTO에서 MANUAL로 바꾸고자 하는 유저들을 위해 만들어진 자료이다.
또한 MANUAL에서 AUTO로 바꾸는 내용도 포함한다.
Explanation & Example
----------------------
< UNDO_MANAGEMENT를 AUTO 에서 MANUAL 로 바꾸기 >
1. undo management mode가 AUTO인지 확인한다.
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- --------------
undo_management string AUTO
undo_retention integer 10800
undo_suppress_errors boolean FALSE
undo_tablespace string UNDOTBS1
SQL> select SEGMENT_NAME,TABLESPACE_NAME,STATUS from dba_rollback_segs;
SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ --------------
SYSTEM SYSTEM ONLINE
_SYSSMU1$ UNDOTBS1 ONLINE
_SYSSMU2$ UNDOTBS1 ONLINE
_SYSSMU3$ UNDOTBS1 ONLINE
_SYSSMU4$ UNDOTBS1 ONLINE
_SYSSMU5$ UNDOTBS1 ONLINE
_SYSSMU6$ UNDOTBS1 ONLINE
_SYSSMU7$ UNDOTBS1 ONLINE
_SYSSMU8$ UNDOTBS1 ONLINE
_SYSSMU9$ UNDOTBS1 ONLINE
_SYSSMU10$ UNDOTBS1 ONLINE
11 rows selected.
위의 내용을 보면 undo management 이 AUTO 라는 것을 알수 있다.
2. 아래와 같이 undo_management을 MANUAL로 바꾼다.
SQL> alter system set undo_management=manual scope=spfile;
System altered.
3. 정상적으로 database을 shutdown 한다.
SQL> shutdown normal;
Database closed.
Database dismounted.
ORACLE instance shut down.
4. restrict mode로 database을 start한후 undo management mode을 확인한다..
SQL> startup restrict
ORACLE instance started.
Total System Global Area 320303136 bytes
Fixed Size 737312 bytes
Variable Size 285212672 bytes
Database Buffers 33554432 bytes
Redo Buffers 798720 bytes
Database mounted.
Database opened.
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- -------------
undo_management string MANUAL
undo_retention integer 10800
undo_suppress_errors boolean FALSE
undo_tablespace string UNDOTBS1
SQL> select SEGMENT_NAME,TABLESPACE_NAME,STATUS from dba_rollback_segs;
SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ------------
SYSTEM SYSTEM ONLINE
_SYSSMU1$ UNDOTBS1 OFFLINE
_SYSSMU2$ UNDOTBS1 OFFLINE
_SYSSMU3$ UNDOTBS1 OFFLINE
_SYSSMU4$ UNDOTBS1 OFFLINE
_SYSSMU5$ UNDOTBS1 OFFLINE
_SYSSMU6$ UNDOTBS1 OFFLINE
_SYSSMU7$ UNDOTBS1 OFFLINE
_SYSSMU8$ UNDOTBS1 OFFLINE
_SYSSMU9$ UNDOTBS1 OFFLINE
_SYSSMU10$ UNDOTBS1 OFFLINE
11 rows selected.
5. non-system rollback segment을 위한 non-system(RBS) tablespace을 생성한다.
SQL> create tablespace rbs datafile
'/apac/rdbms/64bit/app/oracle/oradata/APR920K6/rbs01.dbf' size 50m;
6. non-system rollback segment을 만들기에 앞서, 먼저 system tablespace에
private rollback segment을 만들어야 한다. 그렇지 않으면 ORA-01552 error
를 만나게 된다.
SQL> create rollback segment r01 tablespace rbs
storage (initial 1m next 1m minextents 2);
create rollback segment r01 tablespace rbs
*
ERROR at line 1:
ORA-01552: cannot use system rollback segment for non-system tablespace 'RBS'
다음과 같이 private rollback segment 을 만든후에 작업한다.
SQL> create rollback segment x;
Rollback segment created.
SQL> alter rollback segment x online;
Rollback segment altered.
7. non-system tablespace(rbs) 에 non-system rollback segment을 생성한다.
SQL> create rollback segment r01 tablespace rbs
storage (initial 1m next 1m minextents 2);
Rollback segment created.
SQL> create rollback segment r02 tablespace rbs
storage (initial 1m next 1m minextents 2);
Rollback segment created.
SQL> create rollback segment r03 tablespace rbs
storage (initial 1m next 1m minextents 2);
Rollback segment created.
SQL> create rollback segment r04 tablespace rbs
storage (initial 1m next 1m minextents 2);
Rollback segment created.
SQL> alter rollback segment r01 online;
Rollback segment altered.
SQL> alter rollback segment r02 online;
Rollback segment altered.
SQL> alter rollback segment r03 online;
Rollback segment altered.
SQL> alter rollback segment r04 online;
Rollback segment altered.
8. system tablespace 에 만들었던 rollback segment X 를 drop한다.
SQL> alter rollback segment X offline;
Rollback segment altered.
SQL> drop rollback segment X;
Rollback segment dropped.
9. non-restricted mode로 바꾼다.
SQL> alter system disable restricted session;
System altered.
10. 제대로 되었는지 확인한다.
SQL>select SEGMENT_NAME,TABLESPACE_NAME,STATUS from dba_rollback_segs;
SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ -----------
SYSTEM SYSTEM ONLINE
_SYSSMU1$ UNDOTBS1 OFFLINE
_SYSSMU2$ UNDOTBS1 OFFLINE
_SYSSMU3$ UNDOTBS1 OFFLINE
_SYSSMU4$ UNDOTBS1 OFFLINE
_SYSSMU5$ UNDOTBS1 OFFLINE
_SYSSMU6$ UNDOTBS1 OFFLINE
_SYSSMU7$ UNDOTBS1 OFFLINE
_SYSSMU8$ UNDOTBS1 OFFLINE
_SYSSMU9$ UNDOTBS1 OFFLINE
_SYSSMU10$ UNDOTBS1 OFFLINE
SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ -----------
R02 RBS ONLINE
R01 RBS ONLINE
R03 RBS ONLINE
R04 RBS ONLINE
15 rows selected.
주의 : non-system tablespace(rbs) 에 만들어진 non-system rollback segments(eg R01) 은
database가 open되어 있는 동안에만 우효하다. 만약 database을 restart하게 되면
위의 rollback segment들이 offline이 되어 작업시에 ORA-01555 를 만나게 될 것이다.
이를 피하기 위하여 rollback_segments 절을 설정한다.
R01,R02,R03,R04 는 위에서 생성한 non-system rollback segment 를 나열한다.
SQL> alter system set rollback_segments=r01,r02,r03,r04 scope=spfile;
System altered.
11. undo_tablespace 에 지정하셨던 UNDOTBS1 을 drop한다.
SQL> Drop tablespace UNDOTBS1 including contents;
Tablespace dropped.
< UNDO_MANAGEMENT를 MANUAL 에서 AUTO 로 바꾸기 >
1. 아래와 같이 undo_management을 AUTO 로 바꾼다.
SQL> alter system set undo_management=auto scope=spfile;
System altered.
2. undo tablespace 를 만든다.
SQL> create UNDO tablespace undotbs1 datafile
'/apac/rdbms/64bit/app/oracle/oradata/APR920K6/undotbs01.dbf' size 100m;
주의 :
UNDO tablespace를 만들때는 extent size등을 제어할수 없다.
그러므로 UNIFORM 옵션이나, DICTIONARY 방식으로 만들지는 못한다.
UNDO tablespace는 반드시 LOCALLY MANAGED 이여야 한다.
3. undo_tablespace 을 설정한다.
SQL> alter system set undo_tablespace=undotbs1 scope=spfile;
4. database을 restart한다.
5. rollback segment 의 상태를 확인한다.
SQL>select SEGMENT_NAME,TABLESPACE_NAME,STATUS from dba_rollback_segs;
SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ------------
SYSTEM SYSTEM ONLINE
_SYSSMU1$ UNDOTBS1 ONLINE
_SYSSMU2$ UNDOTBS1 ONLINE
_SYSSMU3$ UNDOTBS1 ONLINE
_SYSSMU4$ UNDOTBS1 ONLINE
_SYSSMU5$ UNDOTBS1 ONLINE
_SYSSMU6$ UNDOTBS1 ONLINE
_SYSSMU7$ UNDOTBS1 ONLINE
_SYSSMU8$ UNDOTBS1 ONLINE
_SYSSMU9$ UNDOTBS1 ONLINE
_SYSSMU10$ UNDOTBS1 ONLINE
SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ------------
R02 RBS OFFLINE
R01 RBS OFFLINE
R03 RBS OFFLINE
R04 RBS OFFLINE
15 rows selected.
6. 만약 manual하게 사용하던 rollback segment가 필요하지 않다면 drop해도 무방하다.
SQL> drop tablespace rbs including contents;
Tablespace dropped.
SQL> select SEGMENT_NAME,TABLESPACE_NAME,STATUS from dba_rollback_segs;
SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------
SYSTEM SYSTEM ONLINE
_SYSSMU1$ UNDOTBS1 ONLINE
_SYSSMU2$ UNDOTBS1 ONLINE
_SYSSMU3$ UNDOTBS1 ONLINE
_SYSSMU4$ UNDOTBS1 ONLINE
_SYSSMU5$ UNDOTBS1 ONLINE
_SYSSMU6$ UNDOTBS1 ONLINE
_SYSSMU7$ UNDOTBS1 ONLINE
_SYSSMU8$ UNDOTBS1 ONLINE
_SYSSMU9$ UNDOTBS1 ONLINE
_SYSSMU10$ UNDOTBS1 ONLINE
11 rows selected.
RELATED DOCUMENTS
-----------------
<Note:231464.1>
출처:http://211.106.111.2:8880/bulletin/list.jsp?seq=20680&pg=1&sort_by=last_updated&keyfield=subject&keyword= |
