Pavan DBA's Blog

The DBA Knowledge Store

Posts Tagged ‘redolog file’

clearing redolog files

Posted by Pavan DBA on March 18, 2010


sometimes we may get below error while working on the database or when starting database

ORA-16014: log 1 sequence# 53 not archived, no available destinations
ORA-00312: online log 1 thread 1: ‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\REDO01.LOG’

The reason for this could be that your redolog file is about to archive and at same time database was shutdown which will lead to some corruptions in redolog file

When i tried to take backup using RMAN, i got this error

C:\>rman target /

Recovery Manager: Release 10.2.0.1.0 – Production on Thu Mar 18 12:40:04 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: PROD (DBID=120728071, not open)

RMAN> sql ‘alter database open’;

using target database control file instead of recovery catalog
sql statement: alter database open
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of sql command on default channel at 03/18/2010 12:41:42
RMAN-11003: failure during parse/execution of SQL statement: alter database open
ORA-16014: log 1 sequence# 53 not archived, no available destinations
ORA-00312: online log 1 thread 1: ‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\REDO01.LOG’

Then i checked if archivelog mode is enabled

C:\>sqlplus “/ as sysdba”

SQL*Plus: Release 10.2.0.1.0 – Production on Thu Mar 18 12:42:13 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     53
Next log sequence to archive   53
Current log sequence           55

so, archivelog mode is enabled. I checked status of redolog groups to check if there are any INVALID groups

SQL> select group#,status from v$log;

    GROUP# STATUS
———- —————-
         1 INACTIVE
         3 CURRENT
         2 INACTIVE

so, even we don’t have any INVALID groups. I tried to start the database, but landed in above mentioned error.

Then i tried to clear logfile using below command

SQL> alter database clear logfile ‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\REDO01.LOG’;
alter database clear logfile ‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\REDO01.LOG’
*
ERROR at line 1:
ORA-00350: log 1 of instance prod (thread 1) needs to be archived
ORA-00312: online log 1 thread 1:
‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\REDO01.LOG’

it didn’t allowed because it was not yet archived. so i tried below command and it worked

SQL> alter database clear unarchived logfile ‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\REDO01.LOG’;

Database altered.

this command tells to not archive the file and clear the contents

Now my database is opened fine

SQL> alter database open;

Database altered.

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
——— ———-
PROD      READ WRITE

If you also got similar or same error, then try to clear redolog files first

Posted in Admin | Tagged: , | Leave a Comment »

 
%d bloggers like this: