Problem Opening the Database.

joe_echavarria
joe_echavarria used Ask the Experts™
on
Hi,

  I am getting the below error when trying to open the database.   Please help me how might the right way to solve it.

  Please find the problem below.

SQL> select status from v$instance;

STATUS
------------
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 4343 change 10073170157523 time 05/03/2018
06:00:01
ORA-00312: online log 2 thread 1: 'E:\ORADATA\QMS01QA\REDO02.LOG'


SQL>
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Geert GOracle dba
Top Expert 2009

Commented:
the corruption might be only in 1 of the online redo logs.

first attempt would be to copy them from one other multiplexed locations
i hope they are multiplexed ...

to find all:
select member from v$logfile

Open in new window

joe_echavarriaDatabase Administrator

Author

Commented:
Hi,

  They are all in the same location.  But i have three of them.  Is there something i can do ?

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------

E:\ORADATA\QMS01QA\REDO03.LOG
E:\ORADATA\QMS01QA\REDO02.LOG
E:\ORADATA\QMS01QA\REDO01.LOG
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
If they aren't multiplexed or if all copies of the log are corrupted, you need to perform instance recovery.

Simple first step:
rman target /

recover database;

If this is an important/production database, I would contact Oracle Support and open an SR for down system.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>But i have three of them

You have three groups with one member each.  You don't have multiple copies of each one.

You need to perform a recover/restore.
Geert GOracle dba
Top Expert 2009

Commented:
you didn't multiplex them ?
ooo ... bad habit

and if you start correcting that, at least make sure they are on different drives
so when you lose a drive, you at least have a copy

i encounter this problem about once year
joe_echavarriaDatabase Administrator

Author

Commented:
I am getting the below error when trying to recover it.


RMAN> recover database;

Starting recover at 08-MAY-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=148 devtype=DISK

starting media recovery
media recovery failed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/08/2018 09:48:30
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database reco
ver if needed
 start
ORA-00283: recovery session canceled due to errors
ORA-00399: corrupt change description in redo log
ORA-00353: log corruption near block 4343 change 10073170157523 time 05/03/2018
06:00:01
ORA-00312: online log 2 thread 1: 'E:\ORADATA\QMS01QA\REDO02.LOG'

RMAN>
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
How important is the data in this database?
Geert GOracle dba
Top Expert 2009

Commented:
step 1:
stop the database and make a file based copy of all it's files:
initfile or spfile, control files, those 3 online redo logs, data files and archived logs

then try :
recover database until time "to_date('05/03/2018 05:59:59', 'mm/dd/yyyy hh24:mi:ss')";

Open in new window


go further back until it no longer fails (it that's even possible)

as you have corruption, you'll have to open it with resetlogs

alter database open resetlogs;

Open in new window


and then mutliplex the controlfile and online redo logs !
joe_echavarriaDatabase Administrator

Author

Commented:
It is a QA database.   It is a recently copy of production.   It  is a 1.5 TB Database

Unfortunately it is not running in Archive Mode.

What can i do ?   -
Geert GOracle dba
Top Expert 2009

Commented:
ah ... redo the copy ?
Geert GOracle dba
Top Expert 2009

Commented:
i hope your production db is setup with multiplexed redo logs and controlfiles
Geert GOracle dba
Top Expert 2009

Commented:
another option ... run, run away as far as possible
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Top Expert 2008
Commented:
There is an undocumented parameter that allows opening the DB with some lose of data

http://osamamustafa.blogspot.de/2012/07/opening-database-with-corrupted-redo.html

In the past there was another parameter and it works, but now my notes in the office.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>There is an undocumented parameter that allows opening the DB with some lose of data

CAUTION: This will leave the database in an unsupported configuration. I would not suggest using it.
joe_echavarriaDatabase Administrator

Author

Commented:
joe_echavarriaDatabase Administrator

Author

Commented:
Using this article really worked.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Remember:  You are now in an unsupported configuration.
joe_echavarriaDatabase Administrator

Author

Commented:
Yeah, it is a Dev database.

I'll configure better the red and controlfile multiplexing.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial