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/

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply

Your email address will not be published. Required fields are marked *