Recovering a Oracle Corrupted datafile without backup
If a datafile is damaged and no backup of that file is available, then you can still recover the datafile given the following condition:
1. All archived log files written after the creation of the original datafile are available
2.The control file contains the name of the damaged file (that is, the control file is current, or is a backup taken after the damaged datafile was added to the database).
Steps to recover a datafile without backup
1. Very first step we have to do is to re-create a datafile for recovery:
Create a new, empty datafile to replace a damaged datafile that has no corresponding backup. For example, assume that the datafile /u01/oradata/users01.dbf has been damaged, and no backup is available. The following statement re-creates the original datafile (same size) on u02(another location):
ALTER DATABASE CREATE DATAFILE '/u01/oradata/users01.dbf' AS '/u02/users01.dbf';
This statement creates an empty file that is the same size as the lost file. The database looks at information in the control file and the data dictionary to obtain size information. The old datafile is renamed as the new datafile.
2. Perform media recovery on the empty datafile.
For example, enter:
RECOVER DATAFILE '/u02/users01.dbf'
--Or use datafile number
RECOVER DATAFILE 8;
All archived logs written after the original datafile was created must be applied to the new, empty version of the lost datafile during recovery.
Here is a overall summary:
1.shutdown your database
2.take the database in mount state
3.alter database create datafile 'old_datafile_path' as 'new_datafile_path';
4.once new file created do the incomplete recovery.
5. open the database with resetlogs.
Note:
If after creating new datafile got error:
ERROR at line 1:
ORA-01182: cannot create database file 8 - file is in use or recovery
ORA-01110: data file 8: '/u01/oradata/users01.dbf'
To avoid error - before creating new datafile, you must take the existing datafile (or the tablespace) offline.
SQL> alter database datafile 8 offline drop;
After offline datafile then do recover and finally online it.
********************
Restore/Recover datafile from Standby without database catalog (19c)
SQL> startup
ORACLE instance started.
Total System Global Area #### bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/u01/oradata/sysaux01.dbf'
RMAN> list backup of datafile 2;
specification does not match any backup in the repository
As you can see on primary database we can not start db and have problem on sysaux.
And supposed that we have no backup.
Solution:
Connect to the Standby as primary connection and to the primary as auxiliary connection, and make a backup of the datafile 2.
$ rman target sys/pass@pop_stby auxiliary sys/pass@orcl_prim
RMAN>
RMAN> backup as copy datafile 2 auxiliary format '/backup/sysaux01.dbf';
Connect to the primary database, catalog the backup of the datafile as copy, restore and recover the datafile.
$ rman target sys/pass@pop_prim
connected to target database: pop_prim (DBID=1014132133, not open)
RMAN> catalog datafilecopy '/backup/sysaux01.dbf';
RMAN> restore datafile 3;
RMAN> recover datafile 3;
RMAN> alter database open;
Also Recovery From Service is available.
****************************************
♨️Some recommendations about backup strategy:
Based on my experiences, many disasters may be occurred on production database, such as human intervention (error) , malware attacks, viruses, disk and media failure,...
Therefore, having a backup on a safe place is always recommended.
Because all the standbys are also affected when an failure and data corruption occurs.
On the other hand, preparing and recovering backups from the era of tape tools is also a time-consuming task.
Therefore, one of the best recommendations is to set up a delayed standby that can save you.
Regards,
Alireza Kamrani.
