Database : Oracle Database 12.1.0.2 EE
Operating System : Linux 64 Bit
Database Type : Standalone non-RAC DB
Dataguard : No
Incident Summary: There was a power failure at the data center, due to which the database instance was abruptly terminated and failed to open beyond the mount stage during startup. The DBA team has performed multiple troubleshooting steps to resolve the issue; however, the problem still persists. It is also observed that the last valid backup for this database was taken in 2023, which is approximately two years old. Sequence of event: 1)Few data files were moved to a different location (External Media). 2)The problematic data files were made offline to allow the database to be opened. 3)An attempt was made to recover the problematic data files. 4)Despite multiple recovery attempts, the issue remains unresolved. Error Message: SQL*Plus: Retease 12.1.0.2.0 Production on Fri Oct 31 16:25:32 2025 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 4932581504 bytes Fixed Size 5293752 bytes Variable Size 1241516360 bytes Database Buffers 3674210384 bytes Redo Buffers 11481088 bytes Database mounted. ORA-01157: cannot identify/lock data file 13 see DBWR trace file ORA-01110: data file 13: '/run/media/root/Backup-Drive/db/oradata/users10.dbf Steps performed to troubleshoot the issue :Step1 : Lets check whether the database is in archivelog mode or not. SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination USE DE RECOVERY FILE DEST Oldest online log sequence 129251 Next log sequence to archive 129251 Current log sequence 129253 An attempt was made to recover datafile 13, which was identified as problematic. However, the datafile recovery failed. Ideally, the recovery process should prompt for the required archivelogs to complete the operation. In this case, the recovery terminated immediately without requesting any archivelog, indicating a potential corruption or inconsistency in the datafile or controlfile. SQL> recover datafile 13: ORA-00283: recovery session canceled due to errors ORA-81110: data file 13: /run/media/root/Backup-Drive/db/oradata/users10.dbf ORA-01157: cannot identify/lock data file 13 see DBWR trace file ORA-01110: data file 13: '/run/media/root/Backup-Drive/db/oradata/users10.dbf Step 2: The problematic datafile 13 was brought offline, and an attempt was made to open the database. SQL> alter database datafile 13 offline; Database altered. SQL> alter database open; ERROR at line 1: ORA-01157: cannot identify/lock data file 14 see DBWR trace file ORA-01110: data file 14: '/run/media/root/Backup-Drive/db/oradata/users11.dbf At this stage, the database prompted for datafile 14 recovery. An attempt was made to recover datafile 14 to verify whether the same error persists. SQL> recover datafile 14; ORA-00283: recovery session canceled due to errors ORA-01110: data file 14: '/run/media/root/Backup-Drive/db/oradata/users11.dbf ORA-01157: cannot identify/lock data file 14 see DBWR trace file ORA-01110: data file 14: /run/media/root/Backup-Drive/db/oradata/users11.dbf The same error was encountered during the recovery of datafile 14. Consequently, datafile 14 was brought offline, and an attempt was made to open the database again. SQL> alter database datafile 14 offline; Database altered. SQL> alter database open; alter database open ERROR at line 1: ORA-01157: cannot identify/lock data file 15 see DBWR trace file ORA-01110: data file 15: '/run/media/root/Backup-Drive/db/oradata/users12.dbf The same error persisted even after bringing datafile 14 offline and attempting to open the database. SQL> alter database datafile 15 offline; Database altered. SQL> alter database open; Database altered. The database was successfully opened by keeping the problematic datafiles offline. However, these datafiles may contain critical data, and the application will not be able to access this portion of the data.
This situation needs to be addressed appropriately to ensure data consistency and application functionality. Step 3: Now, verify the number of datafiles that are behind in recovery and not consistent with the rest of the datafiles in the database. This will help identify which files require additional recovery or synchronization to bring the database to a consistent state. SQL> set lines 300 pages 3000 SQL> col name for a70 SQL> select file#,name,status from v$datafile; FILE# NAME STATUS ----- --------------------------------------------------- ------- 1 /u02/db/oracle/oradata/TESTDB/system01.dbf SYSTEM 2 /u02/db/oracle/oradata/TESTDB/users03.dbf ONLINE 3 /u02/db/oracle/oradata/TESTDB/sysaux01.dbf ONLINE 4 /u02/db/oracle/oradata/TESTDB/undotbs01.dbf ONLINE 5 /u02/db/oracle/oradata/TESTDB/users02.dbf ONLINE 6 /u02/db/oracle/oradata/TESTDB/users01.dbf ONLINE 7 /u02/db/oracle/oradata/TESTDB/users04.dbf ONLINE 8 /u02/db/oracle/oradata/TESTDB/users05.dbf ONLINE 9 /u02/db/oracle/oradata/TESTDB/users06.dbf ONLINE 10 /u02/db/oracle/oradata/TESTDB/users07.dbf ONLINE 11 /u02/db/oracle/oradata/TESTDB/users08.dbf ONLINE 12 /u02/db/oracle/oradata/TESTDB/users09.dbf ONLINE 13 /run/media/root/Backup-Drive/db/oradata/users10.dbf RECOVER 14 /run/media/root/Backup-Drive/db/oradata/users11.dbf RECOVER 15 /run/media/root/Backup-Drive/db/oradata/users12.dbf RECOVER 15 rows selected. It was observed that the last three datafiles are located in a different location from the rest of the database files. The issue appears to be isolated to these specific files, which seem to reside on an external media. Upon verification with the client, it was confirmed that these datafiles were manually moved to the external location in the past due to space constraints in the original datafile location. Upon verification of the datafile locations, it was identified that there was a permission issue on one of the recursive directories, due to which the affected datafiles were not accessible to the Oracle database. The directory ownership was found to be set as root:oinstall, which prevented Oracle from accessing the files. It was found that the last directory in the datafile path was correctly owned by oracle:oinstall. However, one of the intermediate (middle) directories in the path was owned by root:oinstall, which caused a permission restriction and made the datafiles inaccessible to the Oracle database process. [oracle@TEST-0204 ~]$ ls -l /run/media/root/Backup-Drive/db/oradata ls: cannot access /run/media/root/Backup-Drive/db/oradata: Permission denied [oracle@TEST-0204 ~]$ ls -ltr /run/media/root/Backup-Drive/db/oradata/users10.dbf ls: cannot access /run/media/root/Backup-Drive/db/oradata/users10.dbf: Permission denied The directory ownership was corrected to oracle:oinstall, ensuring appropriate permissions throughout the entire directory path. After applying the correct permissions, the datafiles became accessible, and the database prompted for the required archived logs to proceed with the datafile recovery. By root: #cd /run/media #chmod -R 777 root [oracle@TEST-0204 ~]$ ls -ltr /run/media/root/Backup-Drive/db/oradata/users10.dbf -rwxrwxrwx. 1 oracle oinstall 33554440192 Oct 31 15:48 /run/media/root/Backup-Drive/db/oradata/users10.dbf [oracle@TEST-0204 ~]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Fri Oct 31 19:05:21 2025 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> select name,open_mode from v$database; NAME OPEN_MODE --------- ------------- TESTDB READ WRITE SQL> recover datafile 13 ; ORA-00279: change 2400452354 generated at 10/31/2025 15:43:15 needed for thread 1 ORA-00289: suggestion : /u02/db/oracle/fast_recovery_area/TESTDB/archivelog/2025_10_31/o1_mf_1_129253_%u_.arc ORA-00280: change 2400452354 for thread 1 is in sequence #129253 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} /u02/db/oracle/fast_recovery_area/TESTDB/archivelog/2025_10_31/o1_mf_1_129253_%u_.arc ORA-00308: cannot open archived log '/u02/db/oracle/fast_recovery_area/TESTDB/archivelog/2025_10_31/o1_mf_1_129253_%u_.arc' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} AUTO ORA-00308: cannot open archived log '/u02/db/oracle/fast_recovery_area/TESTDB/archivelog/2025_10_31/o1_mf_1_129253_%u_.arc' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 ORA-00308: cannot open archived log '/u02/db/oracle/fast_recovery_area/TESTDB/archivelog/2025_10_31/o1_mf_1_129253_%u_.arc' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 During the recovery process, a new error was encountered: “No such file or directory.” An attempt was made to bring the affected datafile online to verify its accessibility and consistency within the database. SQL> alter database datafile 13 online; alter database datafile 13 online * ERROR at line 1: ORA-01113: file 13 needs media recovery ORA-01110: data file 13: '/run/media/root/Backup-Drive/db/oradata/users10.dbf' Step 4: Proceed to check the checkpoint_change# and checkpoint_time for all datafiles to identify any inconsistencies or out-of-sync datafiles compared to the controlfile. SQL> alter session set nls_date_format='dd-mm-yyyy hh24:mi:ss'; Session altered. SQL> select file#,name,checkpoint_change#,checkpoint_time from v$datafile_header; FILE# NAME CHECKPOINT_CHANGE# CHECKPOINT_TIME ----- --------------------------------------------------- ------------------ ------------------- 1 /u02/db/oracle/oradata/TESTDB/system01.dbf 2400560219 31-10-2025 17:29:17 2 /u02/db/oracle/oradata/TESTDB/users03.dbf 2400560219 31-10-2025 17:29:17 3 /u02/db/oracle/oradata/TESTDB/sysaux01.dbf 2400560219 31-10-2025 17:29:17 4 /u02/db/oracle/oradata/TESTDB/undotbs01.dbf 2400560219 31-10-2025 17:29:17 5 /u02/db/oracle/oradata/TESTDB/users02.dbf 2400560219 31-10-2025 17:29:17 6 /u02/db/oracle/oradata/TESTDB/users01.dbf 2400560219 31-10-2025 17:29:17 7 /u02/db/oracle/oradata/TESTDB/users04.dbf 2400560219 31-10-2025 17:29:17 8 /u02/db/oracle/oradata/TESTDB/users05.dbf 2400560219 31-10-2025 17:29:17 9 /u02/db/oracle/oradata/TESTDB/users06.dbf 2400560219 31-10-2025 17:29:17 10 /u02/db/oracle/oradata/TESTDB/users07.dbf 2400560219 31-10-2025 17:29:17 11 /u02/db/oracle/oradata/TESTDB/users08.dbf 2400560219 31-10-2025 17:29:17 12 /u02/db/oracle/oradata/TESTDB/users09.dbf 2400560219 31-10-2025 17:29:17 13 /run/media/root/Backup-Drive/db/oradata/users10.dbf 2400452354 31-10-2025 15:43:15 14 /run/media/root/Backup-Drive/db/oradata/users11.dbf 2400452354 31-10-2025 15:43:15 15 /run/media/root/Backup-Drive/db/oradata/users12.dbf 2400452354 31-10-2025 15:43:15 15 rows selected. It was observed that the checkpoint_change# and checkpoint_time values are consistent across all datafiles, except for the three problematic datafiles. These specific datafiles are lagging behind the others, indicating they were not synchronized during the last checkpoint, which caused them to become inconsistent with the rest of the database. Step 5: Proceed to check the availability of the required archived logs needed to recover the out-of-sync datafiles and bring them to a consistent state with the rest of the database. RMAN> list backup of archivelog sequence 129253; specification does not match any backup in the repository RMAN> list archivelog sequence 129253; specification does not match any archived log in the repository RMAN> exit Upon verification, it was found that the required archived log files needed for datafile recovery are physically missing from the server. Step 6: Let's try to recover the datafile until cancel. Is it feasible ? SQL> recover datafile 13 until cancel; ORA-00274: illegal recovery option UNTIL You can see this option is not available. You can't. Proceed to recover the entire database using the UNTIL CANCEL option.SQL> recover database until cancel; Media recovery complete. SQL> The media recovery for the entire database was successfully completed without any errors using the UNTIL CANCEL option. Proceed to bring the previously problematic three datafiles online and verify the checkpoint_change# and checkpoint_time for all datafiles to confirm that they are now synchronized and consistent across the database. SQL> alter database datafile 13 online; Database altered. SQL> alter database datafile 14 online; Database altered. SQL> alter database datafile 15 online; Database altered. All the previously problematic datafiles are now online without any issues. Proceed to check the checkpoint_change# and checkpoint_time for all datafiles to confirm that they are fully consistent and aligned with the controlfile after the successful recovery. SQL> alter session set nls_date_format='dd-mm-yyyy hh24:mi:ss'; Session altered. SQL> select file#,name,checkpoint_change#,checkpoint_time from v$datafile_header; FILE# NAME CHECKPOINT_CHANGE# CHECKPOINT_TIME ----- --------------------------------------------------- ------------------ ------------------- 1 /u02/db/oracle/oradata/TESTDB/system01.dbf 2400571468 31-10-2025 19:27:44 2 /u02/db/oracle/oradata/TESTDB/users03.dbf 2400571468 31-10-2025 19:27:44 3 /u02/db/oracle/oradata/TESTDB/sysaux01.dbf 2400571468 31-10-2025 19:27:44 4 /u02/db/oracle/oradata/TESTDB/undotbs01.dbf 2400571468 31-10-2025 19:27:44 5 /u02/db/oracle/oradata/TESTDB/users02.dbf 2400571468 31-10-2025 19:27:44 6 /u02/db/oracle/oradata/TESTDB/users01.dbf 2400571468 31-10-2025 19:27:44 7 /u02/db/oracle/oradata/TESTDB/users04.dbf 2400571468 31-10-2025 19:27:44 8 /u02/db/oracle/oradata/TESTDB/users05.dbf 2400571468 31-10-2025 19:27:44 9 /u02/db/oracle/oradata/TESTDB/users06.dbf 2400571468 31-10-2025 19:27:44 10 /u02/db/oracle/oradata/TESTDB/users07.dbf 2400571468 31-10-2025 19:27:44 11 /u02/db/oracle/oradata/TESTDB/users08.dbf 2400571468 31-10-2025 19:27:44 12 /u02/db/oracle/oradata/TESTDB/users09.dbf 2400571468 31-10-2025 19:27:44 13 /run/media/root/Backup-Drive/db/oradata/users10.dbf 2400571468 31-10-2025 19:27:44 14 /run/media/root/Backup-Drive/db/oradata/users11.dbf 2400571468 31-10-2025 19:27:44 15 /run/media/root/Backup-Drive/db/oradata/users12.dbf 2400571468 31-10-2025 19:27:44 15 rows selected. Step 7: Since an incomplete recovery of the database was performed, the database must now be opened with the RESETLOGS option. This action will reset the online redo log sequence, start a new incarnation of the database, and allow normal operations to resume. If an attempt is made to open the database using the NORESETLOGS option after performing an incomplete recovery, Oracle will display the following error message: SQL> alter database open; alter database open * ERROR at line 1: ORA-01589: must use RESETLOGS or NORESETLOGS option for database open Let's open the database with RESETLOGS option. SQL> alter database open RESETLOGS; Database altered. SQL> select name,open_mode from v$database; NAME OPEN_MODE ---------- ------------- TESTDB READ WRITE The database was opened successfully using the RESETLOGS option. This initiated a new incarnation of the database, resetting the redo log sequence and allowing the database to resume normal operations. The issue has been successfully resolved. The database is open and fully operational, and the application is able to connect without any issues. |
Thanks for reading this post ! Please comment if you like this post ! Click on FOLLOW to get next blog updates !
Thank you for visiting my blog ! Thanks for your comment !