Problem : We received an ORA-00392 error while trying to open a database after restoring and recovering a database,

Steps to reproduce the error

SQL> ALTER DATABASE OPEN resetlogs
ORA-00392: log 2 OF thread 1 IS being cleared, operation NOT allowed

Solution :
Clear the log group with the below command

SQL>ALTER DATABASE clear logfile GROUP 2;
SQL>ALTER DATABASE OPEN resetlogs;

Logfile got cleared however we were getting a different error this time

SQL>ALTER DATABASE clear logfile GROUP 2;
ALTER DATABASE clear logfile GROUP 2
*
ERROR at line 1:
ORA-00344: unable TO re-CREATE online log 'P:\ORACLE\ORADATA\LOG02B.DBF'
ORA-27040: file CREATE error, unable TO CREATE file
OSD-04002: unable TO OPEN file
O/S-Error: (OS 3) The system cannot find the path specified.

Since the path on source and destination is different, it is trying to create a file under old destination which is not present on the target server.
Solution to this is rename the files with your current location

SQL>ALTER DATABASE RENAME file 'P:\ORACLE\ORADATA\LOG02A.DBF' TO 'D:\app\o
racle\fast_recovery_area\ONLINELOG\LOG02A.DBF'
;
DATABASE altered.
SQL>ALTER DATABASE RENAME file 'P:\ORACLE\ORADATA\LOG01A.DBF' TO 'D:\app\o
acle\fast_recovery_area\ONLINELOG\LOG01A.DBF'
;
DATABASE altered.

Now try opening the database with resetlogs, it will succeed

SQL>ALTER DATABASE OPEN resetlogs;
DATABASE altered.
Tagged with →  
Share →
0 comments
Skip to toolbar