0

need immediate assistance on an issue... Oracle Support is taking way too long to respond back on.

We have a corrupt datafile in one of our tablespaces, I attempted to shut the datafile offline, and then perform a restore and recover from the standby. However, the recover returned an error saying that a sequence was missing or not applied. Now I can't turn the datafile back online and all operations on the schema are halted.

RMAN> recover datafile 88;
...
...
...
archived log thread=1 sequence=94742
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 09/08/2021 22:05:54
RMAN-06055: could not find archived log with sequence 94742 for thread 1

RMAN> alter database datafile 88 online;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 09/08/2021 22:07:49
ORA-01113: file 88 needs media recovery
ORA-01110: data file 88: '/ora03/oracle/oradata/REPPRD01/AFM_DATA05.DBF'

Any tips? I am desperately trying anything.

EDIT: Yes, I tried recover datafile 88 until sequence 94742

EDIT2: The data guard configuration:

DGMGRL> show configuration

Configuration - REPPRD01_DR

  Protection Mode: MaxPerformance
  Members:
  REPPRD01 - Primary database
    Error: ORA-16724: cannot resolve gap for one or more standby databases

    rep01    - Physical standby database
      Error: ORA-16766: Redo Apply is stopped

Fast-Start Failover: DISABLED

Configuration Status:
ERROR   (status updated 21 seconds ago)

The standby configuration:

RMAN> show all;

RMAN configuration parameters for database with db_unique_name REP01 are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/oracle/product/12.1.0.2/dbs/snapcf_REP01.f'; # default
8
  • 1
    Is the standby database fully up to date? Do you have a dba on site? If your standby is up to date, why not just do a failover(where it is intended for)?
    – user953
    Commented Sep 9, 2021 at 13:30
  • @ik_zelf - Our senior DBA had passed away recently, and I'm the only DBA on-site with minimal experience, (have mentioned this in a lot of my previous posts. It's been a rough time). I'm not too sure how to do a failover, and I'm not sure if the standby is fully up-to-date. How can I do this?
    – Phillip
    Commented Sep 9, 2021 at 13:42
  • Is it dataguard managed? If so, show the configuration. You could also check current_scn on both primary and standby v$database. On the other hand…. It looks like your company does not have any love for their data….
    – user953
    Commented Sep 9, 2021 at 13:58
  • @ik_zelf - See Edit 2, I submitted the configuration.
    – Phillip
    Commented Sep 9, 2021 at 14:44
  • Ah and also the reason for getting in this trouble: rman archive deletion policy set to none instead of applied to all standbys. Version 12.2.0.1
    – user953
    Commented Sep 9, 2021 at 14:49

2 Answers 2

0

The fix was to run the following command in standby before running the BMR recovery in the primary database:

RMAN> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
0

Change the archive deletion policy to applied on all standbys. This will prevent the situation that an archive can’t be transported because it is deleted after a backup.

And now hope your backup is ok and restore all archivelogs:

restore archivelog from sequence 94742;

After this restart the recovery process.

Monitor your archive space after this setting.

11
  • Do I need to perform this configuration on standby or primary?
    – Phillip
    Commented Sep 9, 2021 at 15:27
  • Both sides start with primary.
    – user953
    Commented Sep 9, 2021 at 15:29
  • With a little luck this (restored archives) also fixes the dataguard transport lag.
    – user953
    Commented Sep 9, 2021 at 15:31
  • I've just configured this now. Is this the appropriate command to restore the archivelogs? restore archivelog all? If so, do I restore in primary?
    – Phillip
    Commented Sep 9, 2021 at 15:40
  • FYI, in all this time, with a severity 1 SR, I still haven't gotten a response from Oracle.
    – Phillip
    Commented Sep 9, 2021 at 15:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.