OraFAQ Forum: Backup & Recovery » Not able to open database in Oracle

Home » RDBMS Server » Backup & Recovery » Not able to open database in Oracle (Oracle 11g )

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Not able to open database in Oracle [message #662176]

Thu, 20 April 2017 01:22

Patel86
Messages: 7
Registered: April 2017

Junior Member

I am facing an issue where i am not able to open the database. Gettting the error "ORA-00312: online log 2 thread 1: '/u01/app/oracle/fast_recovery_area/XE/onlinelog/o1_mf_2_dgy5sxv5_.log'"

Since i am unable to open the database, i cant do log switching. If anyone has faced this issue, please share the inputs to overcome above error and open the database. Tried all the different ways mentioned in different forums but still stuck.

SQL> ALTER SYSTEM SWITCH LOGFILE
2 ;
ALTER SYSTEM SWITCH LOGFILE
*
ERROR at line 1:
ORA-01109: database not open

SQL> ALTER SYSTEM ARCHIVE LOG GROUP 2;
ALTER SYSTEM ARCHIVE LOG GROUP 2
*
ERROR at line 1:
ORA-00259: log 2 of open instance XE (thread 1) is the current log, cannot archive

Report message to a moderator

Re: Not able to open database in Oracle [message #662178 is a reply to message #662176]

Thu, 20 April 2017 01:29

John Watson
Messages: 8964
Registered: January 2010
Location: Global Village

Senior Member

Welcome to the forum. Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read

This is expected behaviour: if the database is not open, you can't switch and archive online logs. Why would you want to? A closed database is not generating redo.

Report message to a moderator

Re: Not able to open database in Oracle [message #662181 is a reply to message #662178]

Thu, 20 April 2017 01:48

Patel86
Messages: 7
Registered: April 2017

Junior Member

I want to Open the database, but because of that error i am unable to do that.
Since this log is the CURRENT log, i am unable to DROP it.

SQL> ALTER DATABASE DROP LOGFILE GROUP 2;
ALTER DATABASE DROP LOGFILE GROUP 2
*
ERROR at line 1:
ORA-01623: log 2 is current log for instance XE (thread 1) - cannot drop
ORA-00312: online log 2 thread 1: '/u01/app/oracle/fast_recovery_area/XE/onlinelog/o1_mf_2_dgy5sxv5_.log'

Is there any way where we can perform the log switch offline ?

[Updated on: Thu, 20 April 2017 01:50]

Report message to a moderator

Re: Not able to open database in Oracle [message #662182 is a reply to message #662181]

Thu, 20 April 2017 01:52

John Watson
Messages: 8964
Registered: January 2010
Location: Global Village

Senior Member

Please use [code] tags when you post. I have already directed you towards a doc describing them, if you are not familiar with the standard [bb] tags.

If your problem is that you cannot open the database, you should show the error (and stop going on about the redo log).

Report message to a moderator

Re: Not able to open database in Oracle [message #662183 is a reply to message #662182]

Thu, 20 April 2017 02:09

Patel86
Messages: 7
Registered: April 2017

Junior Member

When i perform startup, i get the below error.

SQL> startup
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2233344 bytes
Variable Size             817892352 bytes
Database Buffers          243269632 bytes
Redo Buffers                5541888 bytes
Database mounted.
ORA-00341: log 2 of thread 1, wrong log # 1 in header
ORA-00312: online log 2 thread 1: '/u01/app/oracle/fast_recovery_area/XE/onlinelog/o1_mf_2_dgy5sxv5_.log'


SQL>
SQL> select GROUP#,member from  v$logfile;

    GROUP# MEMBER
---------- -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
         2 /u01/app/oracle/fast_recovery_area/XE/onlinelog/o1_mf_2_dgy5sxv5_.log
         1 /u01/app/oracle/fast_recovery_area/XE/onlinelog/o1_mf_1_dhch18s7_.log
         3 /u01/app/oracle/fast_recovery_area/XE/onlinelog/redo3.log

SQL> select group#, sequence#, bytes/1024/1024 as "Size (MB)", members, status from v$log;

    GROUP#  SEQUENCE#  Size (MB)    MEMBERS STATUS
---------- ---------- ---------- ---------- ----------------
         1          0         50          1 UNUSED
         3          0         10          1 UNUSED
         2          2         50          1 CURRENT

SQL>

Report message to a moderator

Re: Not able to open database in Oracle [message #662185 is a reply to message #662183]

Thu, 20 April 2017 02:36

John Watson
Messages: 8964
Registered: January 2010
Location: Global Village

Senior Member

You need to give a lot more detail on how you got into this situation. Did you, for example, copy online logfile members by hand?
In principal, if your current group is damaged then your only option is incomplete recovery: restore all the datafiles from backup and recover up to the damaged sequence number.

Report message to a moderator

Re: Not able to open database in Oracle [message #662187 is a reply to message #662185]

Thu, 20 April 2017 05:55

Patel86
Messages: 7
Registered: April 2017

Junior Member

Last week we faced an abrupt shutdown of the server. After that, the redo log seems to be corrupted as we faced the below error during startup.

SQL> startup
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2233344 bytes
Variable Size             817892352 bytes
Database Buffers          243269632 bytes
Redo Buffers                5541888 bytes
Database mounted.
ORA-00399: corrupt change description in redo log
ORA-00353: log corruption near block 4 change 21317325 time 04/18/2017 12:43:44
ORA-00312: online log 2 thread 1:
'/u01/app/oracle/fast_recovery_area/XE/onlinelog/o1_mf_2_dgy5sxv5_.log'

SQL>

We had 2 redo log files : o1_mf_1_dhch18s7_.log & o1_mf_2_dgy5sxv5_.log. Since we were not really worried about data loss, we have tried copy o1_mf_1_dhch18s7_.log to o1_mf_2_dgy5sxv5_.log (this suggestion we read in few forums, not sure if this is a right approach).

We were able to succesfully clear contents of o1_mf_1_dhch18s7_.log => this log was not corrupted. Hence we copied.

Report message to a moderator

Re: Not able to open database in Oracle [message #662188 is a reply to message #662187]

Thu, 20 April 2017 05:58

John Watson
Messages: 8964
Registered: January 2010
Location: Global Village

Senior Member

Copying the log file like that has destroyed your database. You will need to restore it from backup.

Report message to a moderator

Re: Not able to open database in Oracle [message #662189 is a reply to message #662188]

Thu, 20 April 2017 06:45

Patel86
Messages: 7
Registered: April 2017

Junior Member

We had taken the backup of this log file(before copying) and we have restored the same. Unfortunately, we do not have any backup of these files before the abrupt server shutdown. When we tried to start the DB once server was up, we were encountered with the below error.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2233344 bytes
Variable Size             817892352 bytes
Database Buffers          243269632 bytes
Redo Buffers                5541888 bytes
Database mounted.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00399: corrupt change description in redo log
ORA-00353: log corruption near block 4 change 21317325 time 04/18/2017 12:43:44
ORA-00312: online log 2 thread 1:
'/u01/app/oracle/fast_recovery_area/XE/onlinelog/o1_mf_2_dgy5sxv5_.log'

SQL> recover database until cancel;
ORA-00283: recovery session canceled due to errors
ORA-16433: The database must be opened in read/write mode.


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery

Report message to a moderator

Re: Not able to open database in Oracle [message #662191 is a reply to message #662189]

Thu, 20 April 2017 06:59

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>We had taken the backup of this log file
Exactly how & when was the backup of the log file taken?
Was Oracle actively writing to this REDO file while the backup was being done?

Report message to a moderator

Re: Not able to open database in Oracle [message #662193 is a reply to message #662189]

Thu, 20 April 2017 07:36

John Watson
Messages: 8964
Registered: January 2010
Location: Global Village

Senior Member

Restore all the datafiles and then

SHU ABORT;
STARTUP MOUNT;
RECOVER DATABASE UNTIL CHANGE 21317324;
ALTER DATABASE OPEN RESETLOGS;

you may have to take the change number a bit further back, but try the above first. You will of course have lost all work done later on.

Report message to a moderator

Re: Not able to open database in Oracle [message #662205 is a reply to message #662176]

Thu, 20 April 2017 11:48

mikek
Messages: 29
Registered: January 2017

Junior Member

Based on your post it appears that the Database maybe using Flash Recovery. Not sure if this applies, but I had a
similar issue with Personnel Database at Home where the Database would not startup and would error out.

You may need to find out if the Database is using a PFILE or SPFILE for startup which is important to know if you must modify parameters. PFILE can be modified by an Editor and the SPFILE can be modified using the ALTER Command. That is my understanding.

I found that the Flash recovery "db_recovery_file_dest_size" has been reached which caused
the archive process to stop and error out.

Look in the generated alert_{sid}.log possible location in the {Oracle Home}\{sid}\{sid}\trace Directory
or in the Directory where the "BACKGROUND_DUMP_DEST" is setup. ook at the most recent Log Information looking
for ORA- Errors to help in the research process. The Alert Log helped me determine the errors and
which I used to search the Web for possible solutions.

Start the database manually to a Mount Phase (Not Opened) You can issue some commands as well as alter parameters at this point.

SQL> STARTUP MOUNT {Optional Parameter File}

To show the settings of parameters:

SQL> SHOW PARAMETER BACKGROUND_DUMP_DEST;

SQL> SHOW PARAMETER DB_RECOVERY_FILE_DEST_SIZE;

The size can be modified for "DB_RECOVERY_FILE_DEST_SIZE" using the Alter Command. You may be able to ALter the database
to a Open State a this point.

SQL> ALTER DATABASE OPEN;

Report message to a moderator

Re: Not able to open database in Oracle [message #662223 is a reply to message #662193]

Fri, 21 April 2017 00:57

Patel86
Messages: 7
Registered: April 2017

Junior Member

Hi John,

I tried with the steps you provided, but still facing the same error.

SQL>
SQL> shu abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2233344 bytes
Variable Size             817892352 bytes
Database Buffers          243269632 bytes
Redo Buffers                5541888 bytes
Database mounted.
SQL> RECOVER DATABASE UNTIL CHANGE 21317324;
ORA-00283: recovery session canceled due to errors
ORA-16433: The database must be opened in read/write mode.


SQL> RECOVER DATABASE UNTIL CHANGE 21317100;
ORA-00283: recovery session canceled due to errors
ORA-16433: The database must be opened in read/write mode.


SQL> RECOVER DATABASE UNTIL CHANGE 21307100;
ORA-00283: recovery session canceled due to errors
ORA-16433: The database must be opened in read/write mode.


SQL> RECOVER DATABASE UNTIL CHANGE 21100000;
ORA-00283: recovery session canceled due to errors
ORA-16433: The database must be opened in read/write mode.


SQL>

Report message to a moderator

Re: Not able to open database in Oracle [message #662224 is a reply to message #662191]

Fri, 21 April 2017 00:59

Patel86
Messages: 7
Registered: April 2017

Junior Member

This backup of this file was taken when the database was down and nothing was getting written on this REDO file.

Report message to a moderator

Re: Not able to open database in Oracle [message #662227 is a reply to message #662223]

Fri, 21 April 2017 01:15

John Watson
Messages: 8964
Registered: January 2010
Location: Global Village

Senior Member

Did you restore all the datafiles?

Report message to a moderator

Re: Not able to open database in Oracle [message #662228 is a reply to message #662227]

Fri, 21 April 2017 01:16

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

And control files? And redo logs? Are you in ARCHIVELOG or NOARCHIVELOG mode?

Report message to a moderator

Re: Not able to open database in Oracle [message #662243 is a reply to message #662205]

Fri, 21 April 2017 04:42

gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK

Senior Member

The issue is a corrupt log; nothing to do with the DB_RECOVERY_FILE_DEST_SIZE parameter.

Report message to a moderator

Previous Topic:	Manual Hot Backup in Oracle 11gR2
Next Topic:	v$recovery_file_dest don't show actual space usage

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Feb 01 20:31:56 CST 2025