3 Replies Latest reply: Sep 19, 2012 3:01 AM by Shivananda Rao RSS

    how to resize standby logfiles? or it will be synced from primary?

    877440
      I changed primary db logfile size from 50m to 200m.

      I wonder if I have to do that on standby site?

      I tried, and it seems not working on standby site:

      SQL> select group#, thread#, status, bytes/1024/1024 from v$log;

      GROUP# THREAD# STATUS BYTES/1024/1024
      ---------- ---------- ---------------- ---------------
      1 1 CLEARING 50
      2 1 CLEARING 50
      3 2 CLEARING 50
      4 2 CURRENT 50
      5 1 CLEARING 50
      6 2 CLEARING 50
      15 1 UNUSED 200
      16 1 UNUSED 200
      17 2 UNUSED 200
      18 2 UNUSED 200

      10 rows selected.

      SQL> alter database drop logfile group 1;
      alter database drop logfile group 1
      *
      ERROR at line 1:
      ORA-01624: log 1 needed for crash recovery of instance REMEPRDS1 (thread 1)
      ORA-00312: online log 1 thread 1:
      '+DAT/prds/onlinelog/group_1.961.793523169'
      ORA-00312: online log 1 thread 1:
      '+FRA/prds/onlinelog/group_1.5605.793523169'


      SQL> alter system switch logfile;
      alter system switch logfile
      *
      ERROR at line 1:
      ORA-16000: database open for read-only access


      SQL> alter system switch logfile;
      alter system switch logfile
      *
      ERROR at line 1:
      ORA-16000: database open for read-only access


      SQL> /
      alter system switch logfile
      *
      ERROR at line 1:
      ORA-16000: database open for read-only access


      SQL> alter database drop logfile group 1;
      alter database drop logfile group 1
      *
      ERROR at line 1:
      ORA-01624: log 1 needed for crash recovery of instance PRDS1 (thread 1)
      ORA-00312: online log 1 thread 1:
      '+DAT/prds/onlinelog/group_1.961.793523169'
      ORA-00312: online log 1 thread 1:
      '+FRA/prds/onlinelog/group_1.5605.793523169'


      SQL>

      Please help me here.

      Thanks,
        • 1. Re: how to resize standby logfiles? or it will be synced from primary?
          CKPT
          Hello,

          Even if you add standby or online redo log files or even if you resize by drop and recreate redo log files, they won't be created on standby database. They are instance specific.

          Let perform all the recovery, cancel MRP.
          And then drop, add standby redo log files, I think you must put standby_file_management to MANUAL before adding new standby redo log files. And don't forget to set standby file management to AUTO again after performing changes.

          And you trying to perform log switch on standby, it's applicable only on primary because log switch can be performed only on database which uses online redo log files. Even if you open standby databases with recovery no redo will be generated hence no log switches are allowed too.
          • 2. Re: how to resize standby logfiles? or it will be synced from primary?
            mseberg
            Hello;

            I don't have an exact document. But you stop apply on the standby and then :

            Example
            alter database drop standby logfile group 5;
            Then you can use this document to recreate :

            http://www.visi.com/~mseberg/create_missing_standby_redo_logs.html

            Remember if the old files on the OS remain you could have a conflict.

            Another example

            http://jhdba.wordpress.com/2011/02/28/scripts-to-resize-standby-redolog-files/


            Best Regards

            mseberg
            • 3. Re: how to resize standby logfiles? or it will be synced from primary?
              Shivananda Rao
              Hi,

              Please take a look at this http://emrebaransel.blogspot.in/2008/08/changing-log-file-size-on-dataguard.html