Amazon RDS for Oracle DB 인스턴스가 예상보다 많은 스토리지를 사용하는 이유는 무엇입니까?

최종 업데이트 날짜: 2022년 9월 20일

Amazon Relational Database Service(RDS) for Oracle DB 인스턴스를 가지고 있지만 예상보다 많은 공간을 사용합니다. 이 문제를 해결하려면 어떻게 해야 하나요?

간략한 설명

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

테이블스페이스의 예기치 않은 사용 문제를 해결하려면 다음 단계를 수행하십시오.

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

해결 방법

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

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. 임시 테이블스페이스 사용에 대한 정보를 보려면 DBA_TEMP_FREE_SPACE 보기에서 이 쿼리를 실행합니다.

SQL> SELECT * FROM dba_temp_free_space;

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

SQL> ALTER TABLESPACE temp RESIZE 10g;

테이블스페이스가 10GB 임계값을 초과하여 확장 할당한 경우 이 명령이 실패할 수 있습니다.

4. 명령이 실패하면 이 명령을 실행하여 임시 테이블스페이스의 공간을 축소합니다.

SQL> ALTER TABLESPACE temp SHRINK SPACE KEEP 10g;

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

SQL> SELECT * FROM v$sort_usage;

6. 애플리케이션 로직 및 비즈니스에서 허용하는 경우 출력을 기반으로 세션을 종료할 수 있습니다. 그런 다음 3단계에 따라 임시 테이블스페이스의 크기를 다시 조정합니다.

7. 종료 세션이 옵션이 아닌 경우 새 임시 테이블스페이스를 생성합니다. 그런 다음 새 테이블스페이스를 기본값으로 설정하고, 이전 임시 테이블스페이스를 삭제합니다.

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 value FROM rdsadmin.rds_configuration WHERE name ='archivelog retention hours';

2. 이 쿼리를 실행하여 아카이브 로그에 사용되는 공간을 계산합니다.

참고: X는 RDS for Oracle 인스턴스에 설정된 아카이브 로그 보존입니다. X를 이전 쿼리에서 보고된 값으로 바꿉니다.

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

3. 아카이브 로그에 할당된 공간이 예상보다 많으면 보존 정책 값을 업데이트합니다. 그런 다음 Amazon RDS 자동화를 통해 이전 아카이브 로그 파일을 지우도록 허용할 수 있습니다. 이 예제에서는 24시간 동안 아카이브 로그를 보존하도록 RDS for Oracle 인스턴스를 구성합니다.

begin
 rdsadmin.rdsadmin_util.set_configuration(name => 'archivelog retention hours', value => '24');
end;
 /
commit;

추적 파일 나열 및 제거에 대한 자세한 내용은 추적 파일 제거를 참조하십시오.

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

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

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

2. 이 쿼리에서 .dmp 파일을 찾으면 이 쿼리를 실행하여 삭제하십시오. 파일 이름을 .dmp 파일의 이름으로 변경합니다.

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