SOLUTION
Please perform the following steps on the physical standby database to resolve the issue.
– Please cancel the managed recovery by
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
– Set standby_file_management=manual
SQL>ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;
If the physical standby database is RAC, then please make the change to all standby instances.
SQL>ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL SID='*';
– Rename the unknown datafile 97.
SQL> ALTER DATABASE RENAME FILE '/<oracle_home path>/dbs/UNNAMED00097' to '</absolute path/real datafile name>';
Or
Create a empty datafile which same structure as the datafile 97 and it would need all archivelogs from time of creation for recovery
For filesystem
—————-
Alter database create datafile '/<oracle_home path>/dbs/UNNAMED00097' as '</absolute path/real datafile name>' ;
For ASM
———-
Alter database create datafile '/<oracle_home path>/dbs/UNNAMED00097' as '+DATA_ACS4/' size <Actual size of datafile on Primary>
Please note while giving the above command on ASM you also need to specify the action size of the datafile from the primary (Query v$datafiles for Bytes column)
Else you would give error ORA-01136
Creating Missing Datafile from the Backup when Using ASM Failing with: ORA-01136 (Doc ID 1444075.1)
– Set standby_file_management=auto
SQL>ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
If the physical standby database is RAC, then please make the change to all standby instances.
SQL>ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO SID='*';
– Start managed recovery by
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
If you configured standby redo log groups on the physical standby database, then you could start the real time apply.
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
If you use data guard broker, then you could cancel the managed recovery by changing the database status as below.
– Please connect to DGMGRL from the standby server.
% dgmgrl
DGMGRL> connect sys/<sys password>
DGMGRL>EDIT DATABASE '<standby db_unique_name>' SET STATE='APPLY-OFF';
To change the standby_file_management value, please use the command below from DGMGRL.
DGMGRL> EDIT DATABASE '<standby db_unique_name>' SET PROPERTY 'StandbyFileManagement' = 'MANUAL';
You could use the same sqlplus command to rename the datafile name.
SQL> ALTER DATABASE RENAME FILE '/<oracle_home path>/dbs/UNNAMED00097' to '</absolute path/real datafile name>';
Or
SQL> Alter database create datafile '/<oracle_home path>/db_1/dbs/UNNAMED00097' as '</absolute path/real datafile name>' ;
Then set standby_file_management to AUTO from DGMGRL,
DGMGRL> EDIT DATABASE '<standby db_unique_name>' SET PROPERTY 'StandbyFileManagement' = 'AUTO';
and start the managed recovery from DGMGRL.
DGMGRL>EDIT DATABASE '<standby db_unique_name>' SET STATE='APPLY-ON';