Comment résoudre les problèmes liés à l'utilisation par mon instance de base de données Oracle Amazon RDS d'un espace de stockage plus grand que prévu ?

Dernière mise à jour : 28/10/2020

J'ai configuré une instance de base de données Oracle Amazon Relational Database Service (Amazon RDS), mais elle occupe plus d'espace que prévu. Pourquoi cela se produit-il et que puis-je faire?

Brève description

L'espace de table temporaire peut croître de manière significative pour s'adapter aux opérations nécessaires pour les fonctions de bases de données. Les instances de bases de données Oracle Amazon RDS sont créées avec l'extension automatique activée pour tous les espaces de table. Cela signifie que chaque espace de table croît pour accueillir plus de données, jusqu'à ce que le stockage disponible soit suffisant ou que le stockage alloué soit utilisé. Pour résoudre ce problème :

  1. Recherchez la quantité d'espace allouée aux données dans les espaces de table.
  2. Vérifiez l'allocation d'espace pour les journaux d'archive ou les fichiers de trace.
  3. Vérifiez l'allocation d'espace pour le répertoire de la pompe de données.

Résolution

Rechercher la quantité d'espace allouée aux données dans les espaces de table

1.    Déterminez la répartition de l'espace alloué aux différents composants de la base de données 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);

Si l'espace alloué à l'espace de table temporaire est plus grand que prévu, utilisez les requêtes suivantes pour analyser en profondeur l'espace utilisé sur votre base de données.

2.    Pour afficher les informations sur l'utilisation d'un espace de table temporaire, exécutez la requête suivante dans la vue DBA_TEMP_FREE_SPACE :

SQL> SELECT * FROM dba_temp_free_space;

3.    Pour redimensionner l'espace de table temporaire (par exemple, à 10 Go), exécutez la requête suivante en fonction de la sortie de la requête d'utilisation de l'espace de table :

SQL> ALTER TABLESPACE temp RESIZE 10g;

Cette commande peut échouer si l'espace de table a des étendues allouées au-delà du seuil de 10 Go.

4.    Si la commande échoue, réduisez l'espace sur l'espace de table temporaire en exécutant la commande suivante :

SQL> ALTER TABLESPACE temp SHRINK SPACE KEEP 10g;

5.    Pour vérifier les séances de longue durée qui effectuent un tri actif sur le disque et ont des segments temporaires alloués, exécutez la requête suivante :

SQL> SELECT * FROM v$sort_usage;

6.    En fonction de la sortie, vous pouvez arrêter la séance (si la logique d'application et l'activité le permettent) et réessayer de redimensionner l'espace de table temporaire en suivant l'étape 3.

7.    Si l'arrêt des séances n'est pas une option, créez un nouvel espace de table temporaire. Ensuite, définissez le nouvel espace de table comme paramètre par défaut et supprimez l'ancien espace de table temporaire :

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;

Vérifiez l'allocation d'espace pour les journaux d'archive ou les fichiers de trace

1.    Vérifiez la conservation actuelle du journal d'archivage :

SELECT value FROM rdsadmin.rds_configuration WHERE name ='archivelog retention hours';

2.    Calculez l'espace utilisé par les journaux d'archivage en exécutant la requête suivante.

Remarque : X est la conservation du journal d'archivage définie dans l'instance RDS. Remplacez X par la valeur indiquée dans la requête précédente.

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

3.    Si l'espace alloué aux journaux d'archivage est plus important que prévu, il faut alors mettre à jour la valeur de la politique de conservation. Vous pouvez ensuite autoriser l'automatisation Amazon RDS à effacer les anciens fichiers de journaux d'archivage. L'exemple suivant configure l'instance RDS pour retenir 24 heures de journaux d'archivage :

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

Pour obtenir plus d'informations sur la liste et la purge des fichiers de trace, consultez Purger les fichiers de trace.

Vérifier l'allocation d'espace pour le répertoire de la pompe de données

1.    Si l'espace alloué sur le répertoire de la pompe de données est plus grand que prévu, vérifiez les fichiers .dmp disponibles qui peuvent être supprimés pour libérer de l'espace :

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

2.    Si cette requête trouve des fichiers .dmp, supprimez-les en exécutant la requête suivante, en remplaçant le nom de fichier par le nom des fichiers .dmp :

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