[오라클]Undo Segment 삭제 방법

0001. Undo Segment 삭제 방법

ORA-01552 ORA-00376 ORA-01110 ORA-01548 - UNDO tablespace

 

Recently our Helpdesk people configured backup on Payroll server. while running the backup, backup applications locking the writing file.for that issue alert log showing errors like below:

Mon Jul 06 05:07:36 2009
KCF: write/open error block=0x6f6 online=1
file=2 D:\ORACLE9\PRODUCT\PAYROLL\ORADATA\PAYROLL9\UNDOTBS01.DBF
error=27072 txt: 'OSD-04008: WriteFile() failure, unable to write to file
O/S-Error: (OS 33) The process cannot access the file because another process has locked a portion of the file.'
Automatic datafile offline due to write error on
file 2: D:\ORACLE9\PRODUCT\PAYROLL\ORADATA\PAYROLL9\UNDOTBS01.DBF
Mon Jul 06 05:07:36 2009
Errors in file d:\oracle9\product\payroll\admin\payroll9\bdump\payroll9_smon_2700.trc:
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: 'D:\ORACLE9\PRODUCT\PAYROLL\ORADATA\PAYROLL9\UNDOTBS01.DBF'

Immediately we stopped the backup & restarted the database .
Still the Application is not working. Application show error message

 

"ORA-01552: cannot use system rollback segment for non-system tablespace"

then I checked for the corrupted rollback segments

SQL>select segment_name,status,tablespace_name from dba_rollback_segs where status='NEEDS RECOVERY';
SEGMENT_NAME STATUS TABLESPACE_NAME
------------------------------ ---------------- -----------------
_SYSSMU1$ NEEDS RECOVERY UNDOTBS1
_SYSSMU2$ NEEDS RECOVERY UNDOTBS1
_SYSSMU3$ NEEDS RECOVERY UNDOTBS1
_SYSSMU4$ NEEDS RECOVERY UNDOTBS1
_SYSSMU5$ NEEDS RECOVERY UNDOTBS1
_SYSSMU6$ NEEDS RECOVERY UNDOTBS1
_SYSSMU7$ NEEDS RECOVERY UNDOTBS1
_SYSSMU8$ NEEDS RECOVERY UNDOTBS1
_SYSSMU9$ NEEDS RECOVERY UNDOTBS1
_SYSSMU10$ NEEDS RECOVERY UNDOTBS1

then I recreated another tablespace as UNDOTBS2

CREATE UNDO TABLESPACE "UNDOTBS2"
DATAFILE 'D:\ORACLE9\PRODUCT\PAYROLL\ORADATA\PAYROLL9\UNDOTBS02.DBF' SIZE 200M REUSE
AUTOEXTEND ON NEXT 51200K MAXSIZE 800M;

ALTER SYSTEM SET undo_tablespace=UNDOTBS2 SCOPE=BOTH;

Now I try to drop the old UNDO tablespace but it's giving error:

ora-01548 active rollback segment found

Now the issue is resolved but in Alert log we are getting errors like:

Wed Jul 15 11:50:52 2009
SMON: about to recover undo segment 1
SMON: mark undo segment 1 as needs recovery
……..
SMON: about to recover undo segment 10
SMON: mark undo segment 10 as needs recovery

& for some transactions from applications we are getting some more errors:

ORA-00376: file 2 cannot be read at this tme ORA-01110: data file 2: 'D:\ORACLE9\PRODUCT\PAYROLL\ORADATA\PAYROLL9\UNDOTBS01.DBF'

Solution:
1. create pfile from spfile;

2. Add the following line to pfile:

_corrupted_rollback_segments =('_SYSSMU1$','_SYSSMU2$','_SYSSMU3$','_SYSSMU4$',
'_SYSSMU5$','_SYSSMU6$','_SYSSMU7$','_SYSSMU8$','_SYSSMU9$','_SYSSMU10$')

_OFFLINE_ROLLBACK_SEGMENTS=('_SYSSMU1$','_SYSSMU2$','_SYSSMU3$','_SYSSMU4$','_SYSSMU5$','_SYSSMU6$','_SYSSMU7$','_SYSSMU8$','_SYSSMU9$','_SYSSMU10$')

3. comment undo management-Auto

#undo_management=AUTO

4. create spfile from pfile

5. Start the database again:

SQL> STARTUP RESTRICT

6. Drop bad rollback segments

SQL> drop rollback segment "_SYSSMU11$";
Rollback segment dropped.
......
SQL> drop rollback segment "_SYSSMU10$";
Rollback segment dropped.

7. Check again

SQL> select segment_name,status,tablespace_name from dba_rollback_segs where status='NEEDS RECOVERY';

No rows returned

8. DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES ;

9. shutdown immedaite

10. remove added lines & comments from pfile and recreate the spfile

11.startup

Now the issue is resolved.

 

 

SELECT tablespace_name,status FROM dba_rollback_segs;

select segment_name, tablespace_name, status from dba_rollback_segs order by 2;

 

Response 폴더의 clientadmin.rsp 파일을 연다.


#------------------------------------------------------------------------------
#Name : ORACLE_HOME
#Datatype : String
#Description: Complete path of the Oracle Home.
#Example : ORACLE_HOME = "/product/10.2.0/client"
#------------------------------------------------------------------------------
ORACLE_HOME="c:\oracle\product\10.2.0\client"

#------------------------------------------------------------------------------
#Name : ORACLE_HOME_NAME
#Datatype : String
#Description: Oracle Home Name. Used in creating folders and services.
#Example : ORACLE_HOME_NAME = "OraClient10ghome1"
#------------------------------------------------------------------------------
ORACLE_HOME_NAME="OraClient10ghome1"

위와 같이 수정한다

그런다음

실행창에서 cmd.exe 를 입력하여 커멘드 창을 연 후 실행 폴더로 이동한다음

setup.exe -silent -responsefile D:\Download\Oracle\Response\clientadmin.rsp

아래와 같이 입력하고 실행 하면 콘솔창에서 oracle이 설치된다.

PATCH
/data/oracle/Disk1/stage/products.xml

 

커널 파라미터 설정

# cd /proc/sys/kernel
# echo 2147483648 > shmmax

# vi /etc/sysctl.conf

fs.file-max = 65536
kernel.shmmax = 2147483648
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range= 1024 65000
net.core.rmem_default = 262144
net.core.rmem_max = 262144
net.core.wmem_default = 262144
net.core.wmem_max = 262144

# sysctl -p

참고 : kernel_parameter.rar

-----------------

CentOS 5.2를 지원하지 않으므로 레드햇 4로 수정.

# vi /etc/redhat-release

Red Hat Enterprise Linux AS release 4 (Taroon)

-----------------

매뉴얼이 제공하는 값과는 다르다.
다른 머신에 설치된 값을 참고했다.

# vi /etc/security/limits.conf

oracle soft nofile 65536
oracle hard nofile 65536
oracle soft nproc 16384
oracle hard nproc 16384

참고 : Shell_Limits.rar

-----------------

# SELINUX=disabled

-----------------

적당히 최신 버전을 설치한다.

# rpm -Uvh setarch-2*
# rpm -Uvh make-3*
# rpm -Uvh glibc-2*
# rpm -Uvh libaio-0*

# rpm -Uvh compat-libstdc++-33-3*
# rpm -Uvh compat-gcc-34-3*
# rpm -Uvh compat-gcc-34-c++-3*
# rpm -Uvh gcc-4*
# rpm -Uvh libXp-1.0.0-8.1.el5.x86_64.rpm
# rpm -Uvh libXp-1.0.0-8.1.el5.i386.rpm

# rpm -Uvh openmotif-2*
# rpm -Uvh compat-db-4*

-------------------

그룹과 유저 생성

# groupadd oinstall
# groupadd dba
# groupadd oper

# useradd -g oinstall -G dba oracle
# passwd oracle

-------------------

설치 디렉토리 생성.
# mkdir -p /u01/app/oracle/product/10.2.0/db_1
# chown -R oracle.oinstall /u01

-------------------

베이스 디렉토리, 홈 디렉토리, 라이브러리 경로 설정

# su - oracle
$ vi .bash_profile

export ORACLE_BASE=/u01/app/oracle
export ORACLE_SID=orcl
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
export PATH=$PATH:$ORACLE_HOME/bin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib

-------------------

오라클 설치 파일의 압축을 푼다.

$ gzip -d 10201_database_linux_x86_64.cpio.gz
$ cpio -idmvc < 10201_database_linux_x86_64.cpio

-------------------

다음은 오라클 인스톨러를 사일런트 모드로 실행하기 위한 작업이다.

oraInst.loc 파일을 수통 생성한다.

# su -
# cd /etc
# vi oraInst.loc

inventory_loc=/u01/app/oracle/oraInventory
inst_group=

# chown oracle:oinstall oraInst.loc
# chmod 664 oraInst.loc

---------------------

리스폰스 파일 수정, 엔터프라이즈의 다음 항목만 수정했다.

# vi /home/oracle/database/response/enterprise.rsp

ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
ORACLE_HOME_NAME=orcl

---------------------

# su - oracle

./runInstaller -silent -responseFile /home/oracle/database/response/enterprise.rsp

글자가 마구 깨져나왔다.

설치 디렉토리를 삭제하고 재성성, 재권한설정을 한다.

$ echo $LANG
$ ko_KR.UTF-8

$ LANG=en_US.UTF-8

재실행, 설치가 완료되었다면 root.sh를 실행한다.

$ su -
# /$ORACLE_HOME/root.sh

---------------------

사일런트 모드로 인스톨을 했다면 NetCA 와 DBCA를 따로 셋팅해 주어야 한다.
$ vi /home/oracle/database/response/netca.rsp

수정 한 후 다음과 같이 실행한다.

$ $ORACLE_HOME/bin/netca /silent /responsefile /local_dir/netca.rsp

DBCA도 비슷하다.

$ vi /local_dir/dbca.rsp

$ $ORACLE_HOME/bin/dbca {-progressOnly | -silent} [-cloneTemplate] [-datafileDestination /datafilepath] -responseFile /local_dir/dbca.rsp

---------------------

마지막으로 listener.ora, tnsname.ora 를 셋팅하고 리스너를 켜봅시다.
$ cd $ORACLE_HOME/network/admin/

예제: listener.ora tnsnames.ora

$ lsnrctl start

리스너 상태 확인
$lsnrctl status

문자셋을 확인하자.->따로 포스팅 되어있다.

### 신규 롤백 테이블 생성 ###
CREATE UNDO TABLESPACE "UNDOTBS2" DATAFILE '/data1/oradata/COSS/undotbs02.dbf' SIZE 100M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 200M;

### 신규 테이블로 변경 ###
ALTER SYSTEM SET undo_tablespace=UNDOTBS2 SCOPE=BOTH;

### UNDO 테이블 확인 ###
SELECT tablespace_name,status FROM dba_rollback_segs;

### 온/오프라인 확인 ###
select segment_name, tablespace_name, status from dba_rollback_segs order by 2;

### 테이블 스페이스 드랍 ###
DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;

### 이전 테이블로 변경하기 위한 테이블스페이스 생성 ###
CREATE UNDO TABLESPACE "UNDOTBS1" DATAFILE '/data1/oradata/COSS/undotbs01.dbf' SIZE 100M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 200M;

### 이전 테이블로 변경 ###
ALTER SYSTEM SET undo_tablespace=UNDOTBS1 SCOPE=BOTH;

### 온/오프라인 확인 ###
select segment_name, tablespace_name, status from dba_rollback_segs order by 2;

### 신규 테이블 스페이스 드랍 ###
DROP TABLESPACE undotbs2 INCLUDING CONTENTS AND DATAFILES;