Recovery Scenario: Deleted online redo logs and controlfile

oracle@nodename:/u03/oradata[SID18]
> ls
SID3 SID2 SID28 SID1
oracle@nodename:/u03/oradata[SID18]
> rm -rf SID2*
I realize that typing a * instead of an 8 too late, hit Enter key already.
Try to figure out what was damaged.

This is a non-production database so I don’t take a backup at this time, I check to see if last night’s backup is good before continuting.

oracle@nodename:/u03/oradata[SID18]
> . oraenv
ORACLE_SID = [SID18] ? SID2
oracle@nodename:/u03/oradata[SID2]
> sqlplus /nolog

SQL*Plus: Release 10.2.0.4.0 – Production on Thu May 1 10:30:53 2008

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

SQL> connect / as sysdba
Connected.
SQL> select * from dba_data_files;
select * from dba_data_files
*
ERROR at line 1:
ORA-00210: cannot open the specified control file
ORA-00202: control file: ‘/u03/oradata/SID2/control02_SID2.ctl’
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

Missing control file….tried this but I kinda already knew it wasn’t going to work.

SQL> alter system set control_files=’/u02/ordata/SID2/control01_SID2.ctl’;
alter system set control_files=’/u02/ordata/SID2/control01_SID2.ctl’
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

SQL> shutdown immediate
ORA-00210: cannot open the specified control file
ORA-00202: control file: ‘/u03/oradata/SID2/control02_SID2.ctl’
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> shutdown abort;
ORACLE instance shut down.
SQL> exit

Need to modify spfileSID2.ora or initSID2.ora to exclude missing control file, easiest fix for now since I multiplex there are a couple more copies out there.
oracle@nodename:/u01/app/oracle/product/10gR2/dbs[SID2]
> ls sp*
spfileSID3.ora spfileSID3.txt

No spfile so I modify the initSID2.ora to exclude missing /u03/oradata/SID2 controlfile.
*.control_files=’/u02/oradata/SID2/control01_SID2.ctl’,’/u04/oradata/SID2/contro
l03_SID2.ctl’

SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area 2885681152 bytes
Fixed Size 2086736 bytes
Variable Size 1610614960 bytes
Database Buffers 1258291200 bytes
Redo Buffers 14688256 bytes
Database mounted.
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: ‘/u03/oradata/SID2/redo02b_SID2.rdo’

Missing redo log, it was the online one so I can’t switch logfiles.
Time for recovery, this will only get your SCN matched up on all the datafiles, no real recovery takes place.

SQL> recover database until cancel;
Media recovery complete.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00344: unable to re-create online log ‘/u03/oradata/SID2/redo02b_SID2.rdo’
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory

I forgot to create the /u03/oradata/SID2/ directory…once that was done I can try the command again. I check to see if all datafiles still there.

SQL> l
1* alter database open resetlogs
SQL> /

Database altered.

This recreates any missing online redo logs.

SQL> select * from dba_data_files;

SQL> set head off
SQL> /

/u02/oradata/SID2/system01_SID2.dbf
1 SYSTEM 1551892480 189440 AVAILABLE
1 YES 3.4360E+10 4194302 1280 1551826944 189432
SYSTEM

/u02/oradata/SID2/undotbs01_SID2.dbf
2 UNDOTBS1 7119831040 869120 AVAILABLE
2 YES 3.4360E+10 4194302 640 7119765504 869112
ONLINE

/u02/oradata/SID2/sysaux01_SID2.dbf
3 SYSAUX 650117120 79360 AVAILABLE
3 YES 3.4360E+10 4194302 1280 650051584 79352

ONLINE

/u02/oradata/SID2/users01_SID2.dbf
4 USERS 1584660480 193440 AVAILABLE
4 YES 3.4360E+10 4194302 160 1584594944 193432
ONLINE

/u02/oradata/SID2/tools01_SID2.dbf
5 TOOLS 3101163520 378560 AVAILABLE
5 YES 3.4360E+10 4194302 160 3101097984 378552
ONLINE

5 rows selected.

SQL> select * from v$log;

1 1 1 104857600 1 YES INACTIVE
668724596 01-MAY-08

2 1 2 104857600 1 NO CURRENT
668724597 01-MAY-08

3 1 0 104857600 1 YES UNUSED
0

4 1 0 104857600 1 YES UNUSED
0

5 1 0 104857600 1 YES UNUSED

0

SQL> alter system switch logfile;

System altered.

SQL>

Whew!

Advertisements

About April C Sims

Oracle DBA for over a decade...enough said.
This entry was posted in Recovery and tagged . Bookmark the permalink.

5 Responses to Recovery Scenario: Deleted online redo logs and controlfile

  1. Kakoli says:

    I too did a recovery of a 10gR2 database, but
    I get the following error on issuing
    ‘Alter database open resetlogs’ as follows:
    SQL> ALTER DATABASE OPEN RESETLOGS;
    ALTER DATABASE OPEN RESETLOGS
    *
    ERROR at line 1:
    ORA-00344: unable to re-create online log
    ‘/U01/APP/ORACLE/ORADATA/ORCLAG/REDO03.LOG’
    ORA-27040: file create error, unable to create file
    Linux Error: 2: No such file or directory

    I have checked the directory / file permissions; it exists.
    How do I get over this error?
    -kakoli

    • aprilcsims says:

      I noticed it shows the /u01/app/oracle/oradata as uppercase….check that if you are on a Unix platform that the directories are in the case it is expecting.
      Also check the permissions all the way back up to /u01/….if there isn’t at least read for all of the directories above, it won’t allow you to write lower down in the directory.

  2. Vikas Bagga says:

    I used rman to restore my database to another server that is using asm (we want to test asm performance).
    After completing my media recovery I am getting the
    SQL> alter database open resetlogs;
    alter database open resetlogs
    *
    ERROR at line 1:
    ORA-00344: unable to re-create online log ‘/mv1/oradata/remedy/redo02a_t1.dbf’
    ORA-27040: file create error, unable to create file

    I have dropped all the inactive groups and added them into ASM disk group. This is an ACTIVE logfile and I cannot drop or alter it.
    Any suggestions will be apprecaited.

    • aprilcsims says:

      Workaround :
      This looks to be an existing bug… see the MOS document: Alter Database Open Resetlogs Failed With Ora-344, Ora-17502, Ora-15046 on ASM [ID 298567.1]
      Workardound is to recreate the controlfile with the following changes.

      Using only the diskgroup-name in the CREATE CONTROLFILE command :

      LOGFILE
      GROUP 1 (‘+REDO1_RAID1’ ) SIZE 10M,
      GROUP 2 (‘+REDO2_RAID1’ ) SIZE 10M

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s