[ORACLE] Alter database open resetlogs failed after restore. And the problem was my Redo logs.

Hello all

 

How are you doing?

 

I was creating a new test environment today and I tried to open a database that I had restored. 

 

Simple. Of course not.

 

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00349: failure obtaining block size for '+REDO/FOLHAHOM/ONLINELOG/group_4a.rdo'
ORA-15001: diskgroup "REDO" does not exist or is not mounted
ORA-15001: diskgroup "REDO" does not exist or is not mounted
ORA-15001: diskgroup "REDO" does not exist or is not mounted

 

I checked the redo log files. They were wrong and I have no idea why.

set pages 120 lines 10000;
SELECT 

    l.INST_ID, l.THREAD#, l.GROUP#, 

    substr(lf.MEMBER,1,75) as MEMBER, 

    l.SEQUENCE#, l.first_change#, 

    l.BYTES/(1024 * 1024) AS "Size MB", 

    l.MEMBERS, l.ARCHIVED, l.STATUS, 

    lf.IS_RECOVERY_DEST_FILE, l.CON_ID
FROM gv$log l
JOIN gv$logfile lf ON
    l.INST_ID = lf.INST_ID AND
    l.GROUP# = lf.GROUP#
ORDER BY 1,2,3;

Maybe I forgot to set log_file_name_convert. 

 

I don't know and It doesn't matter. I didn't want to do the whole process again.

I can't drop the log file group because the status “CLEARING_CURRENT”, of course.

Kids, don't try it at home? 😜

I'm just kidding, but this solution is a workaround and should only be used in an emergency.

And remember: This is a test environment. Production can and probably will be different, so use at your own risk.

Let's go to the party:

 

-- Thread 1

SQL> ALTER DATABASE RENAME FILE '+REDO/FOLHAHOM/ONLINELOG/group_6a.rdo' TO '+REDO1/FOLHAHOM/ONLINELOG/group_6a.rdo';
Database altered.

-- Thread 2
SQL> ALTER DATABASE RENAME FILE '+REDO/FOLHAHOM/ONLINELOG/group_11a.rdo' TO '+REDO1/FOLHAHOM/ONLINELOG/group_11a.rdo';
Database altered.

set pages 120 lines 10000;
SELECT 

    l.INST_ID, l.THREAD#, l.GROUP#, 

    substr(lf.MEMBER,1,75) as MEMBER, 

    l.SEQUENCE#, l.first_change#, 

    l.BYTES/(1024 * 1024) AS "Size MB", 

    l.MEMBERS, l.ARCHIVED, l.STATUS, 

    lf.IS_RECOVERY_DEST_FILE, l.CON_ID
FROM gv$log l
JOIN gv$logfile lf ON
    l.INST_ID = lf.INST_ID AND
    l.GROUP# = lf.GROUP#
ORDER BY 1,2,3;

 

Can I rename all the members of the log file?

Yes, but I preferred to recreate all the log file groups.

 

 

 

SQL>  alter database add logfile thread 1 ('+REDO1','+REDO2') size 512M;
Database altered.

SQL> alter database add logfile thread 2 ('+REDO1','+REDO2') size 512M;
Database altered.
 

SQL>  alter database add logfile thread 1 ('+REDO1','+REDO2') size 512M;
Database altered.

SQL>  alter database add logfile thread 1 ('+REDO1','+REDO2') size 512M;
Database altered.

SQL>  alter database add logfile thread 1 ('+REDO1','+REDO2') size 512M;
Database altered.

SQL>  alter database add logfile thread 1 ('+REDO1','+REDO2') size 512M;
Database altered.

SQL> alter database add logfile thread 2 ('+REDO1','+REDO2') size 512M;
Database altered.

SQL> alter database add logfile thread 2 ('+REDO1','+REDO2') size 512M;
Database altered.

SQL> alter database add logfile thread 2 ('+REDO1','+REDO2') size 512M;
Database altered.

SQL> alter database add logfile thread 2 ('+REDO1','+REDO2') size 512M;
Database altered.

 

SQL> ALTER DATABASE DROP LOGFILE GROUP 4;
Database altered.

SQL> ALTER DATABASE DROP LOGFILE GROUP 5;
Database altered.

SQL> ALTER DATABASE DROP LOGFILE GROUP 7;
Database altered.

SQL> ALTER DATABASE DROP LOGFILE GROUP 8;
Database altered.

SQL> ALTER DATABASE DROP LOGFILE GROUP 9;
Database altered.

SQL> ALTER DATABASE DROP LOGFILE GROUP 10;
Database altered.

SQL> ALTER DATABASE DROP LOGFILE GROUP 12;
Database altered.

SQL> ALTER DATABASE DROP LOGFILE GROUP 13;
Database altered.

SQL> alter system checkpoint;
System altered.

I checked that the old groups were inactive and dropped the last two groups.

set pages 120 lines 10000;
SELECT 

    l.INST_ID, l.THREAD#, l.GROUP#, 

    substr(lf.MEMBER,1,75) as MEMBER, 

    l.SEQUENCE#, l.first_change#, 

    l.BYTES/(1024 * 1024) AS "Size MB", 

    l.MEMBERS, l.ARCHIVED, l.STATUS, 

    lf.IS_RECOVERY_DEST_FILE, l.CON_ID
FROM gv$log l
JOIN gv$logfile lf ON
    l.INST_ID = lf.INST_ID AND
    l.GROUP# = lf.GROUP#
ORDER BY 1,2,3;

SQL> ALTER DATABASE DROP LOGFILE GROUP 6;
Database altered.

SQL> ALTER DATABASE DROP LOGFILE GROUP 11;
Database altered.

 

SQL> ALTER DATABASE ENABLE thread 2;

 

 

And that's it. I hope I've helped you.

Regards

Mario