MIITDocID-993: Redo log is accidentally dropped. Now what? --Moid

Scenario is:

Farhan had lost one of the redolog when he accidentally use "rm -rf" command. Since there was no sufficient backups and archivelogs to restore, I used the following steps to open the db.

All the steps are pretty self explanatory,  so I wont be putting too much time describing what I am doing here.

DR-server-01:/u02/oradata/farhan01(farhan01)$ export ORACLE_SID=farhan01
DR-server-01:/u02/oradata/farhan01(farhan01)$ sq
SQL> startup
ORACLE instance started.
Total System Global Area  159383552 bytes
Fixed Size                  1266344 bytes
Variable Size             104861016 bytes
Database Buffers           50331648 bytes
Redo Buffers                2924544 bytes
Database mounted.
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u14/oradata/farhan01/system01.dbf';

when I try to recover database from the old backup, it was clear to me that redo log was missing. Alert log reported the following:

Errors in file /u01/app/oracle/admin/farhan01/udump/farhan01_ora_11555.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u15/oradata/farhan01/redo01.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory

.
.
Media Recovery Start
Thu Nov 26 00:11:24 2009
Errors in file /u01/app/oracle/admin/farhan01/udump/farhan01_ora_2683.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u15/oradata/farhan01/redo01.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Thu Nov 26 00:11:27 2009
Media Recovery failed with error 313
ORA-283 signalled during: alter database recover if needed
 start
...

AT this point, since the controlfiles were still good and the database was in mount stage, I took a backup of controlfile to trace in /tmp location as shown below.

SQL> alter database backup controlfile to trace as '/tmp/farhan01.controlfile.trace.trc' reuse;

SQL!
:/u02/oradata/farhan01(arhan01)$ vi /tmp/farhan01.controlfile.trace.trc


CREATE CONTROLFILE REUSE DATABASE "FARHAN01" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u15/oradata/farhan01/redo01.log'  SIZE 50M,
  GROUP 2 '/u16/oradata/farhan01/redo02.log'  SIZE 50M,
  GROUP 3 '/u17/oradata/farhan01/redo03.log'  SIZE 50M,
  GROUP 4 '/u15/oradata/farhan01/redo04.log'  SIZE 100M
-- STANDBY LOGFILE

DATAFILE
  '/u14/oradata/farhan01/system01.dbf',
  '/u14/oradata/farhan01/undotbs01.dbf',
  '/u14/oradata/farhan01/sysaux01.dbf',
  '/u14/oradata/farhan01/users01.dbf',
  '/u14/oradata/farhan01/dev01',
  '/u14/oradata/farhan01/system02.dbf'
CHARACTER SET WE8ISO8859P1
;
.
.
.

:/u02/oradata/farhan01farhan01)$ exit
SQL> shutdown immediate;

The file which was accidentally dropped earlier was marked in yellow ( GROUP 1 '/u15/oradata/farhan01/redo01.log'  SIZE 50M,) . I removed the file from the "create controlfile" statement and created a new controlfile as shown below:

DR-server-01:/u02/oradata/farhan01(farhan01)$ sq
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Nov 26 00:18:24 2009
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  159383552 bytes
Fixed Size                  1266344 bytes
Variable Size             104861016 bytes
Database Buffers           50331648 bytes
Redo Buffers                2924544 bytes

SQL> CREATE CONTROLFILE REUSE DATABASE "FARHAN01" RESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 2 '/u16/oradata/farhan01/redo02.log'  SIZE 50M,
  9    GROUP 3 '/u17/oradata/farhan01/redo03.log'  SIZE 50M,
 10    GROUP 4 '/u15/oradata/farhan01/redo04.log'  SIZE 100M
 11  DATAFILE
 12    '/u14/oradata/farhan01/system01.dbf',
 13    '/u14/oradata/farhan01/undotbs01.dbf',
 14    '/u14/oradata/farhan01/sysaux01.dbf',
 15    '/u14/oradata/farhan01/users01.dbf',
 16    '/u14/oradata/farhan01/dev01',
 17    '/u14/oradata/farhan01/system02.dbf'
 18  CHARACTER SET WE8ISO8859P1
 19  ;

Control file created.

Elapsed: 00:00:00.80

SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-01100: database already mounted


Elapsed: 00:00:00.00
SQL> alter database open resetlogs;
Database altered.
Elapsed: 00:00:16.67

Great. DB is open for I/O. However, lets create/change backup settings and take a immediate level 0 backup as shown below:

DR-server-01:/home/oracle(farhan01)$ mkdir -p /u99/farhan01/backup
DR-server-01:/home/oracle(farhan01)$ mkdir -p /u99/farhan01/archive
DR-server-01:/home/oracle(farhan01)$ mkdir -p /u99/farhan01/archive2

DR-server-01:/u99(farhan01)$ sq
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Nov 26 00:39:33 2009
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show parameter archive


SQL> alter system set log_archive_dest_1='LOCATION=/u99/farhan01/archive' scope=both;
System altered.
Elapsed: 00:00:00.00

SQL> alter system set log_archive_dest_2='LOCATION=/u99/farhan01/archive2' scope=both;
System altered.
Elapsed: 00:00:00.00


DR-server-01:/u01/app/oracle/admin/farhan01/bdump(farhan01)$ rman target /
Recovery Manager: Release 10.2.0.4.0 - Production on Thu Nov 26 00:34:03 2009
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
connected to target database: FARHAN01 (DBID=1364576715)

RMAN> CONFIGURE DEFAULT DEVICE TYPE TO DISK;

using target database control file instead of recovery catalog
new RMAN configuration parameters:
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
new RMAN configuration parameters are successfully stored

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u99/farhan01/backup/control_n_spfile_%F';
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u99/farhan01/backup/control_n_spfile_%F';
new RMAN configuration parameters are successfully stored

RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO COMPRESSED BACKUPSET;
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO COMPRESSED BACKUPSET;
new RMAN configuration parameters are successfully stored


RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/u99/farhan01/backup/full_%d_%T_%t_%U.bak' MAXPIECESIZE 1 G;
new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/u99/farhan01/backup/full_%d_%T_%t_%U.bak' MAXPIECESIZE 1 G;
new RMAN configuration parameters are successfully stored

RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO COMPRESSED BACKUPSET;
old RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO COMPRESSED BACKUPSET;
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO COMPRESSED BACKUPSET;
new RMAN configuration parameters are successfully stored


DR-server-01:/u99(farhan01)$ rman target /
Recovery Manager: Release 10.2.0.4.0 - Production on Thu Nov 26 00:43:47 2009
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
connected to target database: FARHAN01 (DBID=1364576715)

At this point, I realize that rman needs more DBA's attention to clear the old archivelogs from its repository. Using the "force" rman command helped in cleaning up missing archivelogs.

RMAN> backup database plus archivelog;
Starting backup at 26-NOV-09
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=256 devtype=DISK
archived log /u99/archive/farhan01/1_78_699291916.dbf not found or out of sync with catalog
trying alternate file for archivelog thread 1, sequence 78
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup plus archivelog command at 11/26/2009 00:43:53
RMAN-06059: expected archived log not found, lost of archived log compromises recoverability
ORA-19625: error identifying file /u99/arch/farhan011_78_699291916.dbf
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3



RMAN> crosscheck backup;
using channel ORA_DISK_1

RMAN> delete force archivelog all;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=256 devtype=DISK

List of Archived Log Copies
Key     Thrd Seq     S Low Time  Name
------- ---- ------- - --------- ----
3       1    78      A 21-NOV-09 /u99/archive/farhan01/1_78_699291916.dbf
4       1    78      A 21-NOV-09 /u99/arch/farhan011_78_699291916.dbf
2       1    80      A 22-NOV-09 /u99/arch/farhan011_80_699291916.dbf
1       1    80      A 22-NOV-09 /u99/archive/farhan01/1_80_699291916.dbf
6       1    1       A 26-NOV-09 /u99/farhan01/archive2/1_1_703901992.dbf
5       1    1       A 26-NOV-09 /u99/farhan01/archive/1_1_703901992.dbf

Do you really want to delete the above objects (enter YES or NO)? yes
deleted archive log
archive log filename=/u99/archive/farhan01/1_78_699291916.dbf recid=3 stamp=703901993
deleted archive log
archive log filename=/u99/arch/farhan011_78_699291916.dbf recid=4 stamp=703901993
deleted archive log
archive log filename=/u99/arch/farhan011_80_699291916.dbf recid=2 stamp=703901992
deleted archive log
archive log filename=/u99/archive/farhan01/1_80_699291916.dbf recid=1 stamp=703901992
deleted archive log
archive log filename=/u99/farhan01/archive2/1_1_703901992.dbf recid=6 stamp=703903431
deleted archive log
archive log filename=/u99/farhan01/archive/1_1_703901992.dbf recid=5 stamp=703903431
Deleted 6 objects




RMAN>  backup database plus archivelog;
Starting backup at 26-NOV-09
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=2 recid=8 stamp=703903601
channel ORA_DISK_1: starting piece 1 at 26-NOV-09
channel ORA_DISK_1: finished piece 1 at 26-NOV-09
piece handle=/u99/farhan01/backup/full_FARHAN01_20091126_703903601_01kv9drh_1_1.bak tag=TAG20091126T004641 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 26-NOV-09

Starting backup at 26-NOV-09
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u14/oradata/farhan01/system01.dbf
input datafile fno=00006 name=/u14/oradata/farhan01/system02.dbf
input datafile fno=00003 name=/u14/oradata/farhan01/sysaux01.dbf
input datafile fno=00005 name=/u14/oradata/farhan01/dev01
input datafile fno=00002 name=/u14/oradata/farhan01/undotbs01.dbf
input datafile fno=00004 name=/u14/oradata/farhan01/users01.dbf
channel ORA_DISK_1: starting piece 1 at 26-NOV-09
channel ORA_DISK_1: finished piece 1 at 26-NOV-09
piece handle=/u99/farhan01/backup/full_FARHAN01_20091126_703903603_02kv9drj_1_1.bak tag=TAG20091126T004643 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05
Finished backup at 26-NOV-09

Starting backup at 26-NOV-09
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=3 recid=10 stamp=703903669
channel ORA_DISK_1: starting piece 1 at 26-NOV-09
channel ORA_DISK_1: finished piece 1 at 26-NOV-09
piece handle=/u99/farhan01/backup/full_FARHAN01_20091126_703903669_03kv9dtl_1_1.bak tag=TAG20091126T004749 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 26-NOV-09

Starting Control File and SPFILE Autobackup at 26-NOV-09
piece handle=/u99/farhan01/backup/control_n_spfile_c-1364576715-20091126-00 comment=NONE
Finished Control File and SPFILE Autobackup at 26-NOV-09

RMAN> exit

DR-server-01:/u99(farhan01)$ sq
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Nov 26 00:51:53 2009
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> col member format a70
SQL> set linesize 200
SQL> select group#, member from v$logfile;
    GROUP# MEMBER
---------- ----------------------------------------------------------------------
         4 /u15/oradata/farhan01/redo04.log
         3 /u17/oradata/farhan01/redo03.log
         2 /u16/oradata/farhan01/redo02.log

See how group#1, is missing. This is because we removed it from the database during our rebuilding of controlfile. I will leave this to Farhanbhia to recreate group#1 and also recommend HIM AND EVERYONE ELSE to have multiplex redolog members in each group to avoid this kind of recovery in the future. Hope this helps.


--Moid