Sunday, August 14, 2016

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]