How to resolve ORA-01111 ORA-01110 ORA-01157 in a physical standby database 

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';