database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
PostgreSQL
Firebird
ㆍOracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
Oracle Tutorials 17311 게시물 읽기
 News | Q&A | Columns | Tutorials | Devel | Files | Links
No. 17311
(9i)UNDO_MANAGEMENT을 AUTO에서 MANUAL로 바꾸는 방법
작성자
정재익(xlib)
작성일
2004-02-11 19:51
조회수
9,806

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=

[Top]
No.
제목
작성자
작성일
조회
17315알디에이:RDA, REMOTE DIAGNOSTIC AGENT
정재익
2004-02-11
7060
17313SQL*Net을 통해 listener에 접속할 수 있는 Client Node를 제한하는 방법 [1]
정재익
2004-02-11
8782
17312IFS: JAVA.LANG.NULLPOINTEREXCEPTION DURING IFSCA COLLECTING FILES DB INFO
정재익
2004-02-11
6123
17311(9i)UNDO_MANAGEMENT을 AUTO에서 MANUAL로 바꾸는 방법
정재익
2004-02-11
9806
17310IFS: USING FILES AND WEBFOLDER AFTER CREATING SSO USER
정재익
2004-02-11
5989
17309Direct mode에서의 parallel Data Loading
정재익
2004-02-11
6665
17308(OFS)ORACLE FAILSAFE FOR NT 와 ORACLE PARALLEL FAILSAFE와의 차이점
정재익
2004-02-11
6519
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2023 DSN, All rights reserved.
작업시간: 0.051초, 이곳 서비스는
	PostgreSQL v16.1로 자료를 관리합니다