Home » RDBMS Server » Backup & Recovery » Not able to open database in Oracle (Oracle 11g )
Not able to open database in Oracle [message #662176] Thu, 20 April 2017 01:22 Go to next message
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
Re: Not able to open database in Oracle [message #662178 is a reply to message #662176] Thu, 20 April 2017 01:29 Go to previous messageGo to next message
John Watson
Messages: 7150
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.
Re: Not able to open database in Oracle [message #662181 is a reply to message #662178] Thu, 20 April 2017 01:48 Go to previous messageGo to next message
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 Go to previous messageGo to next message
John Watson
Messages: 7150
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).
Re: Not able to open database in Oracle [message #662183 is a reply to message #662182] Thu, 20 April 2017 02:09 Go to previous messageGo to next message
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>
Re: Not able to open database in Oracle [message #662185 is a reply to message #662183] Thu, 20 April 2017 02:36 Go to previous messageGo to next message
John Watson
Messages: 7150
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.
Re: Not able to open database in Oracle [message #662187 is a reply to message #662185] Thu, 20 April 2017 05:55 Go to previous messageGo to next message
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.
Re: Not able to open database in Oracle [message #662188 is a reply to message #662187] Thu, 20 April 2017 05:58 Go to previous messageGo to next message
John Watson
Messages: 7150
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.
Re: Not able to open database in Oracle [message #662189 is a reply to message #662188] Thu, 20 April 2017 06:45 Go to previous messageGo to next message
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


Re: Not able to open database in Oracle [message #662191 is a reply to message #662189] Thu, 20 April 2017 06:59 Go to previous messageGo to next message
BlackSwan
Messages: 25720
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?
Re: Not able to open database in Oracle [message #662193 is a reply to message #662189] Thu, 20 April 2017 07:36 Go to previous messageGo to next message
John Watson
Messages: 7150
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.
Re: Not able to open database in Oracle [message #662205 is a reply to message #662176] Thu, 20 April 2017 11:48 Go to previous messageGo to next message
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;
Re: Not able to open database in Oracle [message #662223 is a reply to message #662193] Fri, 21 April 2017 00:57 Go to previous messageGo to next message
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>

Re: Not able to open database in Oracle [message #662224 is a reply to message #662191] Fri, 21 April 2017 00:59 Go to previous messageGo to next message
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.
Re: Not able to open database in Oracle [message #662227 is a reply to message #662223] Fri, 21 April 2017 01:15 Go to previous messageGo to next message
John Watson
Messages: 7150
Registered: January 2010
Location: Global Village
Senior Member
Did you restore all the datafiles?
Re: Not able to open database in Oracle [message #662228 is a reply to message #662227] Fri, 21 April 2017 01:16 Go to previous messageGo to next message
Michel Cadot
Messages: 65208
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

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

Re: Not able to open database in Oracle [message #662243 is a reply to message #662205] Fri, 21 April 2017 04:42 Go to previous message
gazzag
Messages: 906
Registered: November 2010
Location: Bristol, UK
Senior Member
The issue is a corrupt log; nothing to do with the DB_RECOVERY_FILE_DEST_SIZE parameter.
Previous Topic: v$recovery_file_dest don't show actual space usage
Next Topic: Manual Hot Backup in Oracle 11gR2
Goto Forum:
  


Current Time: Fri Nov 24 23:25:01 CST 2017

Total time taken to generate the page: 0.02109 seconds