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>
LVL 1
joe_echavarriaDatabase AdministratorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Geert GOracle dbaCommented:
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

0
joe_echavarriaDatabase AdministratorAuthor 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
0
slightwv (䄆 Netminder) 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.
0
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

slightwv (䄆 Netminder) 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.
0
Geert GOracle dbaCommented:
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
0
joe_echavarriaDatabase AdministratorAuthor 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>
0
slightwv (䄆 Netminder) Commented:
How important is the data in this database?
0
Geert GOracle dbaCommented:
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 !
0
joe_echavarriaDatabase AdministratorAuthor 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 ?   -
0
Geert GOracle dbaCommented:
ah ... redo the copy ?
0
Geert GOracle dbaCommented:
i hope your production db is setup with multiplexed redo logs and controlfiles
0
Geert GOracle dbaCommented:
another option ... run, run away as far as possible
0
slightwv (䄆 Netminder) Commented:
0
schwertnerCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
slightwv (䄆 Netminder) 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.
0
joe_echavarriaDatabase AdministratorAuthor Commented:
0
joe_echavarriaDatabase AdministratorAuthor Commented:
Using this article really worked.
0
slightwv (䄆 Netminder) Commented:
Remember:  You are now in an unsupported configuration.
0
joe_echavarriaDatabase AdministratorAuthor Commented:
Yeah, it is a Dev database.

I'll configure better the red and controlfile multiplexing.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.