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: 9The 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: 9However 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_CURRENTSolution 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 scriptSQL> startup nomount; SQL> @/tmp/control.sqlThis 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 0Run 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 resetlogsSQL> alter database open resetlogs; Database altered.Useful metalink note
ALTER DATABASE OPEN RESETLOGS fails with ORA-00392 [ID 1352133.1]
