Re: archivelog error

Phil wrote:

> Hi,
>
> I am not a DBA and know a little about Oracle. We have a DB with archive
> log enabled, for some testing purpose, we messed up archivelog file. I
> truned off the archivelog and cannot trun it on, and cannot open the
> database. This is a test database and has no data on it.
> Anyone can help me? Thank you.
>
> Regards,
> Phillip
>
> Here are some info.
> ------------------------------------------------------------------------------------------
>
> SVRMGR> archive log list;
> Database log mode No Archive Mode
> Automatic archival Enabled
> Archive destination /u01/oracle/admin/sunrcat/arch
> Oldest online log sequence 234
> Current log sequence 236
>
> SVRMGR> alter database archivelog;
> alter database archivelog
> *
> ORA-00265: instance recovery required, cannot set ARCHIVELOG mode
>
> SVRMGR> recover database;
> ORA-00283: recovery session canceled due to errors
> ORA-00355: change numbers out of order
> ORA-00353: log corruption near block 2 change 58791 time 04/19/2001
> 07:49:23
> ORA-00312: online log 2 thread 1:
> '/u01/oracle/oradata/sunrcat/redo02.log'
>
> SVRMGR> alter database open;
> alter database open
> *
> ORA-00355: change numbers out of order
> ORA-00353: log corruption near block 2 change 58800 time 04/19/2001
> 07:49:23
> ORA-00312: online log 2 thread 1:
> '/u01/oracle/oradata/sunrcat/redo02.log'
> SVRMGR>
>
> SVRMGR> select * from v$log;
> GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC
> STATUS FIRST_CHAN FIR
> ST_TIM
> ---------- ---------- ---------- ---------- ---------- ---
> ---------------- ---------- ---
> ------
> 2 1 236 1048576 1 NO
> CURRENT 58363 19-
> APR-01
> 3 1 234 1048576 1 YES
> INACTIVE 57660 18-
> APR-01
> 2 rows selected.
>
> SVRMGR> select * from v$logfile;
> GROUP# STATUS MEMBER
>
> ---------- -------
> -----------------------------------------------------------------------
> ---------
> 2 /u01/oracle/oradata/sunrcat/redo02.log
>
> 3 /u01/oracle/oradata/sunrcat/redo03.log
>
> 2 rows selected.

Phil,

My sympathies. If you have support with Oracle, open a TAR. If you do not have a cold backup of the database as it stands, please produce one before attempting anything further.
If you had anything else to restore from (export, older cold backup) I'm sure that you already have done so by this point.

You may still have a couple of options:

Since its likely that the datafiles themselves are okay, clearing the log files may take care of the corrupt log.
(just a hunch). This will mean that recovery will be incomplete, and an alter database open resetlogs may be needed.
If so, shut down the database normal after that and take a full cold backup before doing anything else.
If you have no backups with which to restore from, you might want to open the db read only for the purpose of performing an export, with which you could rebuild the database and import.

The Oracle Data Unloader tool is an option - a $10,000 option.

There's docs on Metalink for how to open a db with underscore parameters set so as to be able to export the db with corruption.

hth,

Paul Received on Tue Apr 24 2001 - 18:37:12 CDT