Avatar of joe_echavarria
joe_echavarria
Flag for Dominican Republic asked on

Problem Opening the Database.

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>
DatabasesOracle Database

Avatar of undefined
Last Comment
joe_echavarria

8/22/2022 - Mon
Geert G

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_echavarria

ASKER
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
slightwv (䄆 Netminder)

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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
slightwv (䄆 Netminder)

>>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 G

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_echavarria

ASKER
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>
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
slightwv (䄆 Netminder)

How important is the data in this database?
Geert G

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_echavarria

ASKER
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 ?   -
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Geert G

ah ... redo the copy ?
Geert G

i hope your production db is setup with multiplexed redo logs and controlfiles
Geert G

another option ... run, run away as far as possible
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
slightwv (䄆 Netminder)

ASKER CERTIFIED SOLUTION
schwertner

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
slightwv (䄆 Netminder)

>>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_echavarria

ASKER
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
joe_echavarria

ASKER
Using this article really worked.
slightwv (䄆 Netminder)

Remember:  You are now in an unsupported configuration.
joe_echavarria

ASKER
Yeah, it is a Dev database.

I'll configure better the red and controlfile multiplexing.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.