Ghost's Oracle Database !

새로운 파라미터설정
UNDO_MANAGEMENT = AUTO [MANUAL]
UNDO_TABLESPACE = UNDOTBS1
UNDO_SUPPRESS_ERRORS = TRUE <--- 설정되어 있지 않음 (~9i)
UNDO_RETENTION = integer (시간:초)


실습 #1 현재 Undo 관련 설정 확인
#현재 Oracle Database 10g R2 버전이기 때문에 에러가 발생한다. (~9i)
SQL> show parameter undo;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1
SQL> create rollback segment rbs01 tablespace users;

Rollback segment created.

SQL> alter system set undo+suppress_errors = true;
alter system set undo+suppress_errors = true
                 *
ERROR at line 1:
ORA-02065: illegal option for ALTER SYSTEM


SQL> alter system set undo_suppress_errors = true;
alter system set undo_suppress_errors = true
*
ERROR at line 1:
ORA-25138: UNDO_SUPPRESS_ERRORS initialization parameter has been made obsolete


SQL> show parameter undo;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1
SQL> select segment_name from dba_segments
  2  where lower(segment_name) like 'rbs%';

no rows selected

SQL>



실습 #2 UNDO Tablespace 생성과 Default Undo Tablespace 지정
SQL> create undo tablespace undo
  2  datafile '/home/oracle/oradata/testdb/undo01.dbf' size 10m;

Tablespace created.

SQL> select tablespace_name, bytes, file_name from dba_data_files;

TABLESPACE_NAME                     BYTES FILE_NAME
------------------------------ ---------- --------------------------------------------------
USERS                             5242880 /disk3/users01.dbf
SYSAUX                          251658240 /disk1/sysaux01.dbf
UNDOTBS1                         36700160 /disk2/undotbs01.dbf
SYSTEM                          503316480 /disk1/system01.dbf
UNDO                             10485760 /home/oracle/oradata/testdb/undo01.dbf
EXAMPLE                         104857600 /disk3/example01.dbf

6 rows selected.

SQL> alter system set undo_tablespace = undo;

System altered.

SQL> show parameter undo;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDO
SQL> select segment_name, owner, tablespace_name, status from dba_rollback_segs;

SEGMENT_NAME                   OWNER  TABLESPACE_NAME                STATUS
------------------------------ ------ ------------------------------ ----------------
SYSTEM                         SYS    SYSTEM                         ONLINE
_SYSSMU10$                     PUBLIC UNDOTBS1                       OFFLINE
_SYSSMU9$                      PUBLIC UNDOTBS1                       OFFLINE
_SYSSMU8$                      PUBLIC UNDOTBS1                       OFFLINE
_SYSSMU7$                      PUBLIC UNDOTBS1                       OFFLINE
_SYSSMU6$                      PUBLIC UNDOTBS1                       OFFLINE
_SYSSMU5$                      PUBLIC UNDOTBS1                       OFFLINE
_SYSSMU4$                      PUBLIC UNDOTBS1                       OFFLINE
_SYSSMU3$                      PUBLIC UNDOTBS1                       OFFLINE
_SYSSMU2$                      PUBLIC UNDOTBS1                       OFFLINE
_SYSSMU1$                      PUBLIC UNDOTBS1                       OFFLINE
_SYSSMU20$                     PUBLIC UNDO                           ONLINE
_SYSSMU19$                     PUBLIC UNDO                           ONLINE
_SYSSMU18$                     PUBLIC UNDO                           ONLINE
_SYSSMU17$                     PUBLIC UNDO                           ONLINE
_SYSSMU16$                     PUBLIC UNDO                           ONLINE
_SYSSMU15$                     PUBLIC UNDO                           ONLINE
_SYSSMU14$                     PUBLIC UNDO                           ONLINE
_SYSSMU13$                     PUBLIC UNDO                           ONLINE
_SYSSMU12$                     PUBLIC UNDO                           ONLINE
_SYSSMU11$                     PUBLIC UNDO                           ONLINE

21 rows selected.

SQL> show parameter undo;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDO
SQL> drop tablespace undotbs1;

Tablespace dropped.

SQL> select tablespace_name, bytes, file_name from dba_data_files;

TABLESPACE_NAME                     BYTES FILE_NAME
------------------------------ ---------- --------------------------------------------------
USERS                             5242880 /disk3/users01.dbf
SYSAUX                          251658240 /disk1/sysaux01.dbf
SYSTEM                          503316480 /disk1/system01.dbf
UNDO                             10485760 /home/oracle/oradata/testdb/undo01.dbf
EXAMPLE                         104857600 /disk3/example01.dbf

SQL> select segment_name, owner, tablespace_name, status from dba_rollback_segs;

SEGMENT_NAME                   OWNER  TABLESPACE_NAME                STATUS
------------------------------ ------ ------------------------------ ----------------
SYSTEM                         SYS    SYSTEM                         ONLINE
_SYSSMU20$                     PUBLIC UNDO                           ONLINE
_SYSSMU19$                     PUBLIC UNDO                           ONLINE
_SYSSMU18$                     PUBLIC UNDO                           ONLINE
_SYSSMU17$                     PUBLIC UNDO                           ONLINE
_SYSSMU16$                     PUBLIC UNDO                           ONLINE
_SYSSMU15$                     PUBLIC UNDO                           ONLINE
_SYSSMU14$                     PUBLIC UNDO                           ONLINE
_SYSSMU13$                     PUBLIC UNDO                           ONLINE
_SYSSMU12$                     PUBLIC UNDO                           ONLINE
_SYSSMU11$                     PUBLIC UNDO                           ONLINE

11 rows selected.

SQL> 

#현재 상태로 shutdown 했다가 startup 을 할 경우 에러가 발생한다.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              92276304 bytes
Database Buffers          188743680 bytes
Redo Buffers                2973696 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced


#Parameter file의 내용을 아래와 같이 편집한다.
SQL> !vi /home/oracle/product/10g/dbs/inittestdb.ora
*.undo_tablespace='UNDO' <--- 기존 설정 내용은 *.undo_tablespace='UNDOTBS1' 이다.

SQL> startup
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              92276304 bytes
Database Buffers          188743680 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
SQL>