ORA-38884: Cannot shrink data file on standby database due to guaranteed restore points

When you startup Oracle Database, you can get ” ORA-38884: Cannot shrink data file on standby database due to guaranteed restore points ” error.

 

ORA-38884: Cannot shrink data file

When I check Oracle dataguard, I saw following error in the alertlog.

Errors with log +RECO/MSDBDG/ARCHIVELOG/2021_04_08/thread_7_seq_19528.94318.1069369141
PR00 (PID:75748): MRP0: Background Media Recovery terminated with error 38884
2021-04-09T23:59:56.090998+03:00
Errors in file /u01/app/oracle/diag/rdbms/MSDBdg/MSDBDG1/trace/MSDBDG1_pr00_75748.trc:
ORA-38884: Cannot shrink data file +DATA/MSDBDG/DATAFILE/default_tbs2.5141.1065371263 on standby database due to guaranteed restore points.
2021-04-09T23:59:56.092067+03:00
.... (PID:1401): Managed Standby Recovery not using Real Time Apply
2021-04-10T00:00:00.274775+03:00
Recovery interrupted!
2021-04-10T00:00:05.680373+03:00
ARC8 (PID:15615): Archived Log entry 74705 added for T-1.S-21594 ID 0x7f61218a LAD:1
-2021-04-10T00:02:15.836215+03:00
Recovered data files to a consistent state at change 17054718453705
2021-04-10T00:02:20.303634+03:00
db_recovery_file_dest_size of 153600000 MB is 3.37% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
2021-04-10T00:02:20.339088+03:00
stopping change tracking
2021-04-10T00:02:20.340050+03:00
Errors in file /u01/app/oracle/diag/rdbms/MSDBdg/MSDBDG1/trace/MSDBDG1_pr00_75748.trc:
ORA-38884: Cannot shrink data file +DATA/MSDBDG/DATAFILE/default_tbs2.5141.1065371263 on standby database due to guaranteed restore points.
2021-04-10T00:02:20.363673+03:00
Background Media Recovery process shutdown (MSDBDG1)

 

 

 

Background Media Recovery process shutdown

Standby is not running, so we need to drop restore point that is created in the past. Find it and drop this restore point like following.

 

SQL> select NAME,SCN,GUARANTEE_FLASHBACK_DATABASE,DATABASE_INCARNATION# from v$restore_point;

NAME
--------------------------------------------------------------------------------
SCN GUA DATABASE_INCARNATION#
---------- --- ---------------------
RESTORE_POINT_17032021
5.9932E+12 YES 1


SQL> 
SQL> 

SQL> drop restore point RESTORE_POINT_17032021;

Restore point dropped.

 

After drop restore point, you can start background Managed Standby Recovery process as follows.

 

SQL> alter database recover managed standby database disconnect from session;

 

Now check alert log, you won’t see the same errors.

 

Do you want to learn Oracle Database for Beginners, then read the following articles.

https://ittutorial.org/oracle-database-19c-tutorials-for-beginners/