오라클 DB 디스크 용량 비우기 1

오라클 DB에 사용량관련 alert이 떠서 확인해보니 디스크 파티션이 90%이상 찼다. 
확인해서 Online 으로 지울 수 있는 것을 지워보자.

 

1. 로그파일 2개 삭제 (리스너 로그, 트레이스 로그)

 

1) 리스너로 로그파일 위치확인

[oracle@admin-test ~]$ lsnrctl status LISTENER

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 21-JUL-2021 16:49:25

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                27-NOV-2018 06:30:07
Uptime                    470 days 7 hr. 51 min. 25 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/app/oracle/product/11.2.0/network/admin/listener.ora
Listener Log File         /oracle/app/oracle/diag/tnslsnr/admin-test/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
Services Summary...
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully

2) 로그파일 위치
Listener Log File         /oracle/app/oracle/diag/tnslsnr/admin-test/listener/alert/log.xml

 

3) 로그비우기

혹시 모를 상황에 대비해서 파일을 바로 삭제하지 않고 별도 파티션으로 파일 이동후 삭제한다.

 

3.1) 백업폴더 생성

[root@admin-test ] cd /backup
[root@admin-test ] mkdir alert
[root@admin-test backup]# chown -Rf oracle:oinstall alert

 

3.2) 리스너 로그삭제

[root@admin-test alert]# su - oracle
[oracle@admin-test ~]$ cd /oracle/app/oracle/diag/tnslsnr/admin-test/listener/alert
[oracle@admin-test ~]$ find . -name "*.xml" -mtime +30 -exec mv {} /backup/alert/ \;

// 30일이 지난 파일을 /backup/alert 폴더로 이동시킨다.
// 이동하지 않고 그냥 삭제하고 싶다면 --> find . -name "*.xml" -mtime +30 -type f -exec rm -vf {} \

 

3.3) 트레이스 로그삭제

[oracle@admin-test ~]$ cd /oracle/app/oracle/diag/tnslsnr/admin-test/listener/trace
[oracle@admin-test ~]$ cp -rp listener.log listener.log.20210722
//백업하고 삭제한다

[oracle@admin-test ~]$ cp /dev/null listener.log
//trace 로그를 비운다

su
tar -cvzf listener.log_20210722.tar.gz listener.log.20210722
chown -Rf oracle:oinstall listener.log_20210722.tar.gz
rm listener.log.20210722
//백업한 파일은 압축후 삭제한다.

 

4) 테이블스페이스 데이터 정상여부 확인

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME 	       STATUS
------------------------------ ---------
SYSTEM			       ONLINE
SYSAUX			       ONLINE
UNDOTBS1               ONLINE
TEMP			       ONLINE
USERS			       ONLINE
EXAMPLE 		       ONLINE
TS_xxx		       	   ONLINE
TS_xxxx		       	   ONLINE
TS_xxxxx		       ONLINE
TS_xxxxxx		       ONLINE
TS_xxxxxxx		       ONLINE

TABLESPACE_NAME 	       STATUS
------------------------------ ---------
TS_xxxxxxx		       ONLINE

12 rows selected.

 

5) 디스크 용량 확인

df -Th
Filesystem     Type   Size  Used Avail Use% Mounted on
...
/dev/sdb1      ext4   275G  233G   29G  80% /oracle
/dev/sdc1      ext4   550G  173G  350G  34% /backup

끝~

 

# trace 파일 삭제 후 장애발생시 대처

DB 접속은 되는데 테이블 조회가 안된다.

data tablespace 용량도 0, 을 넘어서 아에 조회가 되지 않는다;;;

#[예제]
SQL> select tablespace_name,status from dba_tablespaces;
 
TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
UNDOTBS1                       ONLINE
SYSAUX                         ONLINE
TEMP                           ONLINE
USERS                          ONLINE
TEMP3                          ONLINE
 
6 rows selected.

SQL> alter tablespace users offline;
 
Tablespace altered.
 
SQL> select tablespace_name,status from dba_tablespaces;
 
TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
UNDOTBS1                       ONLINE
SYSAUX                         ONLINE
TEMP                           ONLINE
USERS                          OFFLINE
TEMP3                          ONLINE
 
6 rows selected.
 
SQL> alter tablespace users online;
 
Tablespace altered.
 
SQL> select tablespace_name,status from dba_tablespaces;
 
TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
UNDOTBS1                       ONLINE
SYSAUX                         ONLINE
TEMP                           ONLINE
USERS                          ONLINE
TEMP3                          ONLINE
 
6 rows selected.
 
SQL>

중요한 것은 select tablespace_name,status from dba_tablespaces;
조회를 했을때 빨간 글씨처럼 OFFLINE이라고 써진 테이블 스페이스가 비활성화 되어있는 것이 있을 것이다.
이 tablespace가 data를 담당하는 tablespace라면 당연히 기존 데이터나 테이블을 참조하지 못하게 되는것이다.
따라서 이걸 ONLINE상태로 변경해주면 된다.
그럼 정상적으로 데이터나 테이블을 확인할 수 있다.

 

참조 : https://m.blog.naver.com/PostView.naver?isHttpsRedirect=true&blogId=cestlavie_01&logNo=40210123206