resetlogs 无法创建redo ORA-00344: unable to re-create online log

数据库rman恢复recover

SQL> recover database using backup controlfile until time '2018-11-27 02:00:00';

ORA-00279: change 10495820489810 generated at 11/27/2018 00:00:15 needed for

thread 1

ORA-00289: suggestion : /archlog/1_24363_764960754.dbf

ORA-00280: change 10495820489810 for thread 1 is in sequence #24363

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

auto

ORA-00279: change 10495820511332 generated at 11/27/2018 01:00:14 needed for

thread 1

ORA-00289: suggestion : /archlog/1_24364_764960754.dbf

ORA-00280: change 10495820511332 for thread 1 is in sequence #24364

ORA-00278: log file '/archlog/1_24363_764960754.dbf' no longer needed for this

recovery

ORA-00279: change 10495820512543 generated at 11/27/2018 01:01:23 needed for

thread 1

ORA-00289: suggestion : /archlog/1_24365_764960754.dbf

ORA-00280: change 10495820512543 for thread 1 is in sequence #24365

ORA-00278: log file '/archlog/1_24364_764960754.dbf' no longer needed for this

recovery

ORA-00279: change 10495820523738 generated at 11/27/2018 01:53:07 needed for

thread 1

ORA-00289: suggestion : /archlog/1_24366_764960754.dbf

ORA-00280: change 10495820523738 for thread 1 is in sequence #24366

ORA-00278: log file '/archlog/1_24365_764960754.dbf' no longer needed for this

recovery

ORA-00279: change 10495820523777 generated at 11/27/2018 01:53:09 needed for

thread 1

ORA-00289: suggestion : /archlog/1_24367_764960754.dbf

ORA-00280: change 10495820523777 for thread 1 is in sequence #24367

ORA-00278: log file '/archlog/1_24366_764960754.dbf' no longer needed for this

recovery

Log applied.

Media recovery complete.

recover完成后open read only是没问题的

SQL> alter database open read only;

Database altered.

open resetlogs 报错

SQL> alter database open resetlogs;

alter database open resetlogs

ERROR at line 1:

ORA-00344: unable to re-create online log '/dev/rredo01'

ORA-27042: not enough space on raw partition to fullfill request

Additional information: 3

查看redo在控制文件中记录情况

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                   IS_

---------- ------- ------- ---------------------------------------- ---

         4         ONLINE  /dev/rredo01                             NO

         5         ONLINE  /dev/rredo02                             NO

         6         ONLINE  /dev/rredo03                             NO

         7         ONLINE  /dev/rredo04                             NO

         8         ONLINE  /dev/rredo05                             NO

SQL>

rman 恢复并未恢复redo，这里resetlogs的时候会自动创建，但因为原库使用的是裸设备，所以未创建成功，下面手工添加

 

SQL> alter database add logfile group 1  '/orawxdata01/ccicwx/redo01.log' size 100m;

Database altered.

SQL>

SQL> alter database add logfile group 2  '/orawxdata01/ccicwx/redo02.log' size 100m;

Database altered.

SQL> alter database add logfile group 3  '/orawxdata01/ccicwx/redo03.log' size 100m;

Dtabase altered.

再次resetlog打开又报错如下：

SQL> alter database open resetlogs;

alter database open resetlogs

ERROR at line 1:

ORA-00392: log 5 of thread 1 is being cleared, operation not allowed

ORA-00312: online log 5 thread 1: '/dev/rredo02'

这里我们创建的是group 1 2 4 但是group 4我们没操作，再次resetlog时居然报错log 5了，难道是因为有了group 1 2 3

 

SQL> alter database add logfile group 4  '/orawxdata01/ccicwx/redo04.log' size 100m;

ERROR at line 1:

ORA-01184: logfile group 4 already exists

SQL>

SQL> alter system switch logfile;

alter system switch logfile

*

ERROR at line 1:

ORA-01109: database not open

SQL> alter database drop logfile group 5;

alter database drop logfile group 5

*

ERROR at line 1:

ORA-01623: log 5 is current log for instance ccicwx (thread 1) - cannot drop

ORA-00312: online log 5 thread 1: '/dev/rredo02'

这里要rename一下

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-00392: log 5 of thread 1 is being cleared, operation not allowed

ORA-00312: online log 5 thread 1: '/dev/rredo02'

SQL> alter database clear logfile group 5;

alter database clear logfile group 5

*

ERROR at line 1:

ORA-00344: unable to re-create online log '/dev/rredo02'

ORA-27042: not enough space on raw partition to fullfill request

Additional information: 3

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

 

    GROUP# STATUS

---------- ----------------

         1 UNUSED

         2 UNUSED

         3 UNUSED

         8 CLEARING

         5 CLEARING_CURRENT

         6 CLEARING

         7 CLEARING

         4 CLEARING

8 rows selected.

SQL> alter database drop logfile group 4;

Database altered.

 

SQL> alter database drop logfile group 7;

Database altered.

SQL> alter database drop logfile group 6;

Database altered.

SQL> alter database drop logfile group 8;

Database altered.

SQL>

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

    GROUP# STATUS

---------- ----------------

         1 UNUSED

         5 CLEARING_CURRENT

         3 UNUSED

         2 UNUSED

SQL> alter database

SQL>

SQL> select member from v$logfile;

MEMBER

--------------------------------------------------------------------------------

/dev/rredo02

/orawxdata01/ccicwx/redo01.log

/orawxdata01/ccicwx/redo02.log

/orawxdata01/ccicwx/redo03.log

SQL> alter database rename file '/dev/rredo02' to '/orawxdata01/ccicwx/redo04.log';

alter database rename file '/dev/rredo02' to '/orawxdata01/ccicwx/redo04.log'

*

ERROR at line 1:

ORA-01511: error in renaming log/data files

ORA-01516: nonexistent log file, data file, or temporary file "/dev/rredo02"

SQL> alter database open resetlogs;

alter database open resetlogs

ERROR at line 1:

ORA-00392: log 5 of thread 1 is being cleared, operation not allowed

ORA-00312: online log 5 thread 1: '/dev/rredo02'

SQL> alter database rename file '/dev/rredo02' to '/orawxdata01/ccicwx/redo04.log';

Database altered.

SQL>

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-00392: log 5 of thread 1 is being cleared, operation not allowed

ORA-00312: online log 5 thread 1: '/orawxdata01/ccicwx/redo04.log'

SQL> alter database clearing logfile group 5;

alter database clearing logfile group 5

ERROR at line 1:

ORA-02231: missing or invalid option to ALTER DATABASE

SQL> alter database clear logfile group 5;

Database altered.

SQL> alter database open resetlogs;

Database altered.

SQL>