예상보다 많은 스토리지를 사용하는 Amazon RDS Oracle DB 인스턴스 문제를 해결하려면 어떻게 해야 합니까?

최종 업데이트 날짜: 2020년 2월 6일

Amazon Relational Database Service(Amazon RDS) Oracle DB 인스턴스를 설정했지만 예상보다 커집니다. 이런 일이 발생하는 이유는 무엇이며 어떻게 해야 합니까?

간략한 설명

데이터베이스 기능에 필요한 작업을 수용하기 위해 임시 테이블 공간이 크게 증가할 수 있습니다. Amazon RDS Oracle DB 인스턴스는 모든 테이블스페이스에 대해 자동 확장이 활성화된 상태로 생성됩니다. 즉, 각 테이블스페이스는 더 이상 필요하지 않거나 할당된 스토리지를 사용할 때까지 더 많은 데이터를 수용하기 위해 계속 증가합니다. 이 문제를 해결하려면 다음을 수행하십시오.

  1. 테이블스페이스의 데이터에 할당된 공간의 양을 찾습니다.
  2. 아카이브 로그 또는 추적 파일에 대한 공간 할당을 확인합니다.
  3. 데이터 펌프 디렉터리에 대한 공간 할당을 확인합니다.

​해결 방법

테이블스페이스의 데이터에 할당된 공간 크기 찾기

1.    Oracle 데이터베이스의 여러 구성 요소에 할당된 공간의 확산을 확인합니다.

set pages 200
select
'===========================================================' || chr(10) ||
'Total Database Physical Size = ' || round(redolog_size_gb+dbfiles_size_gb+tempfiles_size_gb+ctlfiles_size_gb,2) || ' GB' || chr(10) ||
'===========================================================' || chr(10) ||
' Redo Logs Size : ' || round(redolog_size_gb,3) || ' GB' || chr(10) ||
' Data Files Size : ' || round(dbfiles_size_gb,3) || ' GB' || chr(10) ||
' Temp Files Size : ' || round(tempfiles_size_gb,3) || ' GB' || chr(10) ||
' Archive Log Size - Approx only : ' || round(archlog_size_gb,3) || ' GB' || chr(10) ||
' Control Files Size : ' || round(ctlfiles_size_gb,3) || ' GB' || chr(10) ||
'===========================================================' || chr(10) ||
' Used Database Size : ' || used_db_size_gb || ' GB' || chr(10) ||
' Free Database Size : ' || free_db_size_gb || ' GB' ||chr(10) ||
' Data Pump Directory Size : ' || dpump_db_size_gb || ' GB' || chr(10) ||
' BDUMP Directory Size : ' || bdump_db_size_gb || ' GB' || chr(10) ||
' ADUMP Directory Size : ' || adump_db_size_gb || ' GB' || chr(10) ||
'===========================================================' || chr(10) ||
'Total Size (including Dump and Log Files) = ' || round(round(redolog_size_gb,2) +round(dbfiles_size_gb,2)+round(tempfiles_size_gb,2)+round(ctlfiles_size_gb,2) +round(adump_db_size_gb,2) +round(dpump_db_size_gb,2)+round(bdump_db_size_gb,2),2) || ' GB' || chr(10) ||
'===========================================================' as summary
FROM (SELECT sys_context('USERENV', 'DB_NAME')
db_name,
(SELECT SUM(bytes) / 1024 / 1024 / 1024 redo_size
FROM v$log)
redolog_size_gb,
(SELECT SUM(bytes) / 1024 / 1024 / 1024 data_size
FROM dba_data_files)
dbfiles_size_gb,
(SELECT nvl(SUM(bytes), 0) / 1024 / 1024 / 1024 temp_size
FROM dba_temp_files)
tempfiles_size_gb,
(SELECT SUM(blocks * block_size / 1024 / 1024 / 1024) size_gb
FROM v$archived_log
WHERE first_time >= SYSDATE - (
(SELECT value
FROM rdsadmin.rds_configuration
WHERE name =
'archivelog retention hours') /
24 ))
archlog_size_gb,
(SELECT SUM(block_size * file_size_blks) / 1024 / 1024 / 1024
controlfile_size
FROM v$controlfile)
ctlfiles_size_gb,
round(SUM(used.bytes) / 1024 / 1024 / 1024, 3)
db_size_gb,
round(SUM(used.bytes) / 1024 / 1024 / 1024, 3) - round(
free.f / 1024 / 1024 / 1024)
used_db_size_gb,
round(free.f / 1024 / 1024 / 1024, 3)
free_db_size_gb,
(SELECT round(SUM(filesize) / 1024 / 1024 / 1024, 3)
FROM TABLE(rdsadmin.rds_file_util.listdir('BDUMP')))
bdump_db_size_gb,
(SELECT round(SUM(filesize) / 1024 / 1024 / 1024, 3)
FROM TABLE(rdsadmin.rds_file_util.listdir('ADUMP')))
adump_db_size_gb,
(SELECT round(SUM(filesize) / 1024 / 1024 / 1024, 3)
FROM TABLE(rdsadmin.rds_file_util.listdir('DATA_PUMP_DIR')))
dpump_db_size_gb
FROM (SELECT bytes
FROM v$datafile
UNION ALL
SELECT bytes
FROM v$tempfile) used,
(SELECT SUM(bytes) AS f
FROM dba_free_space) free
GROUP BY free.f);

2.    임시 테이블스페이스에 할당된 공간이 예상보다 크면 다음 쿼리를 사용하여 데이터베이스에 사용된 공간을 추가로 분석합니다.

3.    임시 테이블스페이스 사용에 대한 정보를 보려면 DBA_TEMP_FREE_SPACE 보기에서 다음 쿼리를 실행합니다.

SQL> SELECT * FROM dba_temp_free_space;

4.    임시 테이블스페이스의 크기를 조정하려면(예: 10 GB) 테이블스페이스 사용량 쿼리의 출력에 따라 다음 쿼리를 실행합니다.

SQL> ALTER TABLESPACE temp RESIZE 10g;

5.    테이블스페이스에 10GB 임계값을 초과하는 영역이 할당되면 이 명령이 실패할 수 있습니다. 명령이 실패하면 다음 명령을 실행하여 임시 테이블스페이스의 공간을 축소합니다.

SQL> ALTER TABLESPACE temp SHRINK SPACE KEEP 10g;

6.    디스크에 대한 활성 정렬을 수행하고 임시 세그먼트가 할당되어 있는 장기 실행 세션을 확인하려면 다음 쿼리를 실행합니다.

SQL> SELECT * FROM v$sort_usage;

7.    출력에 따라 세션을 종료하고(애플리케이션 로직과 비즈니스에서 허용하는 경우) 4단계에 따라 임시 테이블스페이스 크기 조정을 다시 시도할 수 있습니다.

8.    세션 종료가 옵션이 아닌 경우 새 임시 테이블스페이스를 생성하고 새 테이블스페이스를 기본값으로 설정한 다음, 이전 임시 테이블스페이스를 삭제할 수 있습니다.

SQL> SELECT property_name,property_value FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';
SQL> create temporary tablespace temp2;
SQL> exec rdsadmin.rdsadmin_util.alter_default_temp_tablespace(tablespace_name => 'temp2');
<wait for a few minutes and verify if the default temporary tablespace for all users have been updated>
SQL> set pages 2000
SQL> column username for a30
SQL> select username, TEMPORARY_TABLESPACE from dba_users;
SQL> drop tablespace temp including contents and datafiles;

아카이브 로그 또는 추적 파일에 대한 공간 할당 확인

1.    아카이브된 다시 실행 로그에 사용되는 공간을 계산합니다.

SELECT sum(blocks*block_size)/1024/1024/1024 FROM v$archived_log WHERE next_time >=sysdate-X/24 AND dest_id=1;

2.    아카이브 로그 또는 추적 파일에 할당된 공간이 예상보다 크면 다음 쿼리를 사용하여 아카이브 로그 및 추적 파일별로 공간을 추가로 분석합니다.

3.    현재 아카이브 로그 및 추적 파일 보존 설정 확인:

SET SERVEROUTPUT ON;
EXEC rdsadmin.rdsadmin_util.show_configuration;

4.    보존 정책 값을 업데이트하고 Amazon RDS 자동화를 통해 오래된 아카이브 로그 파일 또는 추적 파일을 지울 수 있습니다. 다음은 24시간 동안 다시 실행 로그를 보관하는 예제입니다.

SQL> begin
SQL> rdsadmin.rdsadmin_util.set_configuration(
SQL> name => 'archivelog retention hours',
SQL> value => '24');
SQL> end;
SQL> /
SQL> commit;
# Set the tracefile retention to 24 hours:
SQL> exec rdsadmin.rdsadmin_util.set_configuration('tracefile retention',1440);
SQL> commit;

데이터 펌프 디렉터리에 대한 공간 할당 확인

1.    데이터 펌프 디렉터리에 할당된 공간이 예상보다 크면 공간을 확보하기 위해 제거할 수 있는 사용 가능한.dmp 파일을 확인합니다.

SQL> SELECT * FROM TABLE(RDSADMIN.RDS_FILE_UTIL.LISTDIR('DATA_PUMP_DIR')) ORDER by mtime;

2.    이 쿼리가 .dmp 파일을 찾으면 다음 쿼리를 실행하고 파일 이름을 교체하여 삭제할 수 있습니다.

SQL> EXEC utl_file.fremove('DATA_PUMP_DIR','[file name]');