During a usual import process, I noticed that it was taking more time than usual. In fact it was stuck in the INDEX part of the import.
Both the import logfile and the Oracle alert log were not showing any kind of errors.
I tend to read them both dynamically during any import:
tail -f nohup.out (for the import logfile)
tail -f alert_SID.log (for the alert log)
Since the logfiles did not give me any signs, I started checking on my own what was going on.
First I checked and indeed the datapump process was waiting.
Then, I checked the tablespaces and everything seemed to be in order and they had available space left:
select df.tablespace_name «Tablespace»,
totalusedspace «Used MB»,
(df.totalspace – tu.totalusedspace) «Free MB»,
df.totalspace «Total MB»,
round(100 * ( (df.totalspace – tu.totalusedspace)/ df.totalspace))
«Pct. Free»
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name ;
Then, I had to investigate the Fast Recovery Area (FRA) and its space usage.
select name
, round(space_limit / 1024 / 1024) size_mb
, round(space_used / 1024 / 1024) used_mb
, decode(nvl(space_used,0),0,0,round((space_used/space_limit) * 100)) pct_used
from v$recovery_file_dest
order by name;
It seemed that the FRA was becoming full and it was stalling the import process.
I could give if I wanted more space to the FRA e.g. :
ALTER system SET db_recovery_file_dest_size=3500G scope=BOTH ;
But it was best to find the root cause.
Then, I checked and it seemed that there was a Restore Point created and left unchecked from previous DBA work and needs, which was making the FRA to become full.
select * from v$restore_point;
Since this restore point was not serving any needs anymore, I had the luxury to remove it:
Drop restore_point AFTER_REFRESH_RP;
After dropping the restore point, the allocated space was freed from the FRA, and it enabled the import process to stop hanging and continue normally.
Written by Nikos Minaidis
15/12/2021
Nikos Minaidis 