Resetlogs Fails with ORA-00349

During a database restore on a separate server from the source DB following error occurred during open resetlogs.

RMAN> alter database open resetlogs;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 08/10/2016 18:48:18
ORA-00349: failure obtaining block size for '/data/oradata/apcdb/redo01.log'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 9

The source database was on a non-OMF setup and the DB was restored on OMF paths. As such the original redo directory paths does not exists on the restored sever. Typical solution is to clear the unarchived log file groups but option failed in this case.

SQL> alter database clear unarchived logfile group 1 ;
alter database clear unarchived logfile group 1
*
ERROR at line 1:
ORA-00349: failure obtaining block size for '/data/oradata/apcdb/redo01.log'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 9

However it is possible to drop and add log file groups

SQL> alter database drop logfile group 1;
Database altered.

SQL> alter database add logfile group 1;
Database altered.

SQL>  alter database drop logfile group 3;
Database altered.

SQL> alter database add logfile group 3;
Database altered.

Yet was unable to drop and add one log group as it was considered current

SQL> alter database drop logfile group 2;
alter database drop logfile group 2
*
ERROR at line 1:
ORA-01623: log 2 is current log for instance apcdb (thread 1) - cannot drop
ORA-00312: online log 2 thread 1: '/data/oradata/apcdb/redo02.log'

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

    GROUP# STATUS
---------- ----------------
         1 UNUSED
         3 UNUSED
         2 CLEARING_CURRENT

Solution in this case is to backup the control file to trace and edit the log file locations.

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/tmp/control.sql' resetlogs ;

On the trace file the control file create was as follows

CREATE CONTROLFILE REUSE DATABASE "APCDB" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 1024
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    '/opt/app/oracle/oradata/APCDB/onlinelog/o1_mf_1_ctptbqxp_.log',
    '/opt/app/oracle/flash_recovery_area/APCDB/onlinelog/o1_mf_1_ctptbr4t_.log'
  ) SIZE 100M BLOCKSIZE 512,
  GROUP 2 '/data/oradata/apcdb/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 (
    '/opt/app/oracle/oradata/APCDB/onlinelog/o1_mf_3_ctptc5mn_.log',
    '/opt/app/oracle/flash_recovery_area/APCDB/onlinelog/o1_mf_3_ctptc5tb_.log'
  ) SIZE 100M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/opt/app/oracle/oradata/APCDB/datafile/o1_mf_system_ctpskmcr_.dbf',
  '/opt/app/oracle/oradata/APCDB/datafile/o1_mf_sysaux_ctpskmcd_.dbf',
  '/opt/app/oracle/oradata/APCDB/datafile/o1_mf_undotbs1_ctpskmgg_.dbf',
  '/opt/app/oracle/oradata/APCDB/datafile/o1_mf_system_ctpsnlq7_.dbf',
  '/opt/app/oracle/oradata/APCDB/datafile/o1_mf_users_ctpskmhd_.dbf',
  '/opt/app/oracle/oradata/APCDB/datafile/o1_mf_sysaux_ctpsnlp4_.dbf',
  '/opt/app/oracle/oradata/APCDB/datafile/o1_mf_system_ctpsm0jd_.dbf',
  '/opt/app/oracle/oradata/APCDB/datafile/o1_mf_sysaux_ctpsm0hp_.dbf',
  '/opt/app/oracle/oradata/APCDB/datafile/o1_mf_system_ctpsmsmb_.dbf',
  '/opt/app/oracle/oradata/APCDB/datafile/o1_mf_sysaux_ctpsmsm4_.dbf',
  '/opt/app/oracle/oradata/APCDB/datafile/o1_mf_test_ctpsm0l1_.dbf',
  '/opt/app/oracle/oradata/APCDB/datafile/o1_mf_test_ctpsmsnt_.dbf',
  '/opt/app/oracle/oradata/APCDB/datafile/o1_mf_newtest_ctpsm0m8_.dbf'
CHARACTER SET AL32UTF8
;

As seen on the trace the log file groups 1 and 3 are using the new OMF paths but group 2 still refer to source DB path where the backups were taken from. Two options are available to deal with the group 2, one is to replace the group 2 with an OMF path, in this case

GROUP 2 '/data/oradata/apcdb/redo02.log'  SIZE 50M BLOCKSIZE 512,

with

GROUP 2 (
    '/opt/app/oracle/oradata/APCDB/onlinelog/o1_mf_2_captbqxp_.log',
    '/opt/app/oracle/flash_recovery_area/APCDB/onlinelog/o1_mf_2_captbr4t_.log'
  ),

Or the second option is to completely omit the group 2 and then add group 2 once the control file is created and before resetlogs is run again. Once the control file create section is edited start the database in nomount and run the script

SQL> startup nomount;
SQL> @/tmp/control.sql

This will leave the DB on mount mode. If group 2 was omitted from the control file create then add the group 2 now.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME     CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------
         3          1          0  104857600        512          2 YES CURRENT                      0                      0                    0
         1          1          0  104857600        512          2 YES UNUSED                       0                      0                    0

SQL> alter database add logfile group 2;
Database altered.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME     CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------
         1          1          0  104857600        512          2 YES UNUSED                       0                      0                    0
         3          1          0  104857600        512          2 YES CURRENT                      0                      0                    0
         2          1          0  104857600        512          2 YES UNUSED                       0                      0                    0

Run a recovery using backup control file until cancel.

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL
ORA-00279: change 4285165 generated at 08/10/2016 15:52:55 needed for thread 1
ORA-00289: suggestion :
/opt/app/oracle/flash_recovery_area/APCDB/archivelog/2016_08_10/o1_mf_1_206_%u_.
arc
ORA-00280: change 4285165 for thread 1 is in sequence #206

Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.

Finally run the open resetlogs

SQL> alter database open resetlogs;
Database altered.

Useful metalink note
ALTER DATABASE OPEN RESETLOGS fails with ORA-00392 [ID 1352133.1]

Enable Flashback On Standby

It is possible to enable flashback on a standby independent of the primary. (It's good to have flashback enabled both on primary and standby, if flashback IO is no concern).
Data guard environment created earlier is used here. Steps are similar to How to Enable Flashback for a RAC Database on ASM ( 819905.1)

To enable flashback on standby bring the stop log apply on standby (transport could also be stopped for the duration).

DGMGRL>  edit database rac11g2 set state='TRANSPORT-OFF';
Succeeded.
DGMGRL> edit database rac11g2s set state='APPLY-OFF';
Succeeded.

Trying to enable flashback while log apply is on will result in

SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active

If the standby is open in read only mode then shutdown and bring it up only one instance in mount mode as this is required to enable flashback.

srvctl stop database -d rac11g2s
srvctl start instance -d rac11g2s -i rac11g2s1 -o mount

Once in mount mode enable flashback on the standby

SQL> alter database flashback on;

Database altered.

Enable log apply and transport on

DGMGRL> edit database rac11g2 set state='TRANSPORT-ON';
Succeeded.
DGMGRL> edit database rac11g2s set state='APPLY-ON';
Succeeded.

Open the standby mode in read only if required. Check the flashback on status of the database

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------
YES

Having flashback on standby is important especially to recover from open resetlogs situations in primary. If the standby has applied changes past the new resetlog scn and there's no flashback enabled on standby, only way to recover is to recreate the standby. If flashback was enabled on standby then it could be used to flashback to a scn prior to resetlogs and continue to use the standby from then onwards.