How to rename a redo logfile.


While Opening the database with resetlogs option post incomplete media recovery (Migrated database to another server), receiving the following error:


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

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [ksvworkmsgalloc: bad reaper],
[0x080010003], [], [], [], [], [], [], [], [], [], []


SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2;
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2
*
ERROR at line 1:
ORA-00349: failure obtaining block size for '/oracle/app/oracle/oradata/ORCL/redo02.log'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 9

Reason:

Since this database was migrated to another server, the redo log file which it is trying to read or flush is not physically available there. Also controlfile is having the information of redo log file as per your previous server structure. Hence you are receiving this error.

Solution:


SQL> select * from v$log;
SQL> set pages 333 line 222
    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME     CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------
         1          1          0  209715200        512          1 NO  CLEARING_CURRENT    1.0844E+13 16-APR-20   1.8447E+19                    0
         3          1          0  209715200        512          1 NO  CLEARING            1.0844E+13 16-APR-20   1.0844E+13 16-APR-20          0
         2          1          0  209715200        512          1 NO  CLEARING            1.0844E+13 16-APR-20   1.0844E+13 16-APR-20          0



SQL> col member for a70
SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                                                 IS_     CON_ID
---------- ------- ------- ---------------------------------------------------------------------- --- ----------
         3         ONLINE  /oracle/app/oracle/oradata/ORCL/redo03.log                           NO           0
         2         ONLINE  /oracle/app/oracle/oradata/ORCL/redo02.log                           NO           0
         1         ONLINE  /oracle/app/oracle/oradata/ORCL/redo01.log                           NO           0



SQL> alter database rename file '/oracle/app/oracle/oradata/ORCL/redo03.log' to '/orcl_data/oradata/ORCL/redo/redo03.log';

Database altered.

SQL> alter database rename file '/oracle/app/oracle/oradata/ORCL/redo02.log' to '/orcl_data/oradata/ORCL/redo/redo02.log';

SQL>
Database altered.

SQL>
alter database rename file '/oracle/app/oracle/oradata/ORCL/redo01.log' to '/orcl_data/oradata/ORCL/redo/redo01.log';


SQL>
Database altered.

SQL>ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2;

Database altered.

SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 1;

Database altered.

SQL>  ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;

Database altered.

SQL> alter database open resetlogs;

Database altered.

SQL>


Congrats, It's all done.

Thanks & Regards
Nikhil Kumar.

Comments

Popular posts from this blog

How to set the root password after creating instance on oracle cloud

TNS-12541 TNS-12560 TNS-00511 TNS:protocol adapter error No listener

Queryable inventory could not determine the current opatch status.