0 Replies Latest reply: Aug 14, 2013 10:33 PM by rontrinidadcabral RSS

    resize ORL in 2 node standby database

    rontrinidadcabral

      primary: 3 node RAC

      standby: 2 node RAC

       

      Tried to resize ORL in standby (thread 4 and thread 5) but cannot drop the old ORL. Error is ORA-01624 needed for crash recovery for UNNAMED INSTANCE5 (thread5)  (these are unused)

       

      Any idea on how to drop these ORLs?

       

      I am thinking to disable the thread 4 and thread 5. but do I need to recreate the standby controlfile after this? how about in primary what is the needed step?

      Thanks.

       

      SQL>select GROUP#,THREAD#,MEMBERS,BYTES,STATUS from v$log;

          GROUP#    THREAD#    MEMBERS      BYTES STATUS
      ---------- ---------- ---------- ---------- ----------------
               1          1          2 1073741824 CLEARING
               2          1          2 1073741824 CURRENT
               3          2          2 1073741824 CLEARING
               4          2          2 1073741824 CLEARING
               5          3          2 1073741824 CLEARING
               6          3          2 1073741824 CURRENT
               7          2          2 1073741824 CURRENT
               8          1          2 1073741824 CLEARING
               9          3          2 1073741824 CLEARING
              22          4          2  104857600 UNUSED
              23          4          2  104857600 UNUSED

          GROUP#    THREAD#    MEMBERS      BYTES STATUS
      ---------- ---------- ---------- ---------- ----------------
              24          5          2  104857600 UNUSED
              25          5          2  104857600 UNUSED

       
       
       

       

      1. Stop Redo apply:


      SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

      2. Set STANDBY_FILE_MANAGEMENT to MANUAL.

      check first:

      show parameter standby_file_management

      ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='MANUAL' SID='*';

      check again:

      show parameter standby_file_management

       

      3. Add Redolog File Group:

      ALTER DATABASE ADD LOGFILE THREAD 4 GROUP 26 ('+DATA','+FLASH') SIZE 1073741824;
      ALTER DATABASE ADD LOGFILE THREAD 4 GROUP 27 ('+DATA','+FLASH') SIZE 1073741824;
      ALTER DATABASE ADD LOGFILE THREAD 5 GROUP 28 ('+DATA','+FLASH') SIZE 1073741824;
      ALTER DATABASE ADD LOGFILE THREAD 5 GROUP 29 ('+DATA','+FLASH') SIZE 1073741824;

      4. query newly added redo log;

      set linesize 300
      col member for a50
      select GROUP#,STATUS,MEMBER,TYPE from v$logfile;


      5. check status before drop;

      SELECT GROUP#, STATUS FROM V$LOG;

      If Status is CLEARING_CURRENT or CURRENT then you cannot drop Online Redolog Group.
      You will get ORA-01623 if you try to drop a Redolog Group with Status CLEARING_CURRENT or CURRENT.

      For Status CLEARING, UNUSED, INACTIVE please follow below steps.

      6. Clear the Online Redo Logfile Group:

      ALTER DATABASE CLEAR LOGFILE THREAD 4 GROUP 22;
      ALTER DATABASE CLEAR LOGFILE THREAD 4 GROUP 23;
      ALTER DATABASE CLEAR LOGFILE THREAD 5 GROUP 24;
      ALTER DATABASE CLEAR LOGFILE THREAD 5 GROUP 25;

      7. Drop the Online Redo Logfile Group:

      ALTER DATABASE DROP LOGFILE THREAD 4 GROUP 22;
      ALTER DATABASE DROP LOGFILE THREAD 4 GROUP 23;
      ALTER DATABASE DROP LOGFILE THREAD 5 GROUP 24;
      ALTER DATABASE DROP LOGFILE THREAD 5 GROUP 25;


      8. query to check if redo log is dropped.

      set linesize 300
      col member for a50
      select GROUP#,STATUS,MEMBER,TYPE from v$logfile;

      9. set standby file management = AUTO

      ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO' SID='*';

      check:

      show parameter standby_file_management

       

      10. Start Redo Apply:

      alter database recover managed standby database using current logfile disconnect;