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 ?

Date de la dernière mise à jour : 06/02/2020

J'ai configuré une instance de base de données Oracle Amazon Relational Database Service (Amazon RDS), mais elle ne cesse de devenir plus volumineuse 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 continue de croître pour accueillir plus de données, jusqu'à ce qu'il n'y en ait plus besoin 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.

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);

2.    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.

3.    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;

4.    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;

5.    Cette commande peut échouer si l'espace de table a des étendues allouées au-delà du seuil de 10 Go. 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;

6.    Pour vérifier les sessions 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;

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

8.    Si l'arrêt des sessions n'est pas une option, vous pouvez créer un nouvel espace de table temporaire, définir le nouvel espace de table par défaut et supprimer 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érifier l'allocation d'espace pour les journaux d'archive ou les fichiers de trace

1.    Calculez l'espace utilisé par les journaux redo archivés :

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

2.    Si l'espace alloué aux journaux d'archive ou aux fichiers de trace est plus que prévu, utilisez les requêtes suivantes pour analyser plus en profondeur l'espace par les journaux d'archive et les fichiers de trace.

3.    Vérifiez les paramètres actuels de conservation des journaux d'archive et des fichiers de trace :

SET SERVEROUTPUT ON;
EXEC rdsadmin.rdsadmin_util.show_configuration;

4.    Mettez à jour la valeur de la stratégie de conservation et autorisez l'automatisation Amazon RDS à effacer les fichiers journaux d'archive ou les fichiers de trace plus anciens. L'exemple suivant conserve 24 heures de journaux redo :

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;

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, vous pouvez les supprimer en exécutant la requête suivante et en remplaçant le nom de fichier :

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