Resizing datafiles with Data Guard in place

This week, I am in Kista (Sweden, near Stockholm) teaching an 11g Data Guard course. Boy, I love my job! Not only does it give me the opportunity to talk to intelligent, open minded people, I can also travel through the world and visit interesting countries  -not to mention, that I can make a living from it 🙂

One of the students asked, whether a resize of a datafile on the primary database would also be reproduced at the standby database – and then simply tested it live to see that it is actually done. That is the good thing about having courses with practices: You can simply try out things and don’t have to speculate about it.

Thanks to Niklas Isaksson for pointing out, that a resize of datafiles on the primary is reproduced on the standby! To be honest, I wasn’t sure about that, but now I am 🙂

  1. #1 von sunilbhola am Juli 7, 2010 - 07:09

    Operation that propagate from production to standby:-
    1) resize datafile
    2) dropping tablespace with include datafile

    Operation that NOT propagate from production to standby:-
    1) Rename tablespace
    2) Dropping datafile from tablespace on production

  2. #2 von sunilbhola am Juli 8, 2010 - 14:41

    Below error comes up when you are modifying the file size or other parameters in READ ONLY MODE.

    ORA-00600: internal error code, arguments: [kcvrsz_01], [19], [100], [], [], [], [], []

  3. #3 von Uwe Hesse am Juli 8, 2010 - 16:06

    Thank you for sharing that! Presently, I just don’t have the time to reproduce that and will just believe you 🙂

  4. #4 von sunilbhola am Februar 10, 2011 - 06:45

    – Errors in file /opt/oracle/admin/WWKA_RPT/udump/wwka_rpt_ora_25934.trc:
    – ORA-00600: internal error code, arguments: [kcvrsz_01], [1
    – 9], [100], [], [], [], [], []
    – ORA-600 signalled during: alter database datafile ‚/oradata/WWKA_RPT/data0
    – 8/…
    – Errors in file /opt/oracle/admin/WWKA_RPT/udump/wwka_rpt_ora_25934.trc:
    – ORA-00600: internal error code, arguments: [kcvrsz_0
    – 1], [18], [100], [], [], [], [], []
    – ORA-600 signalled during: alter database datafile ‚/oradata/WWKA_RPT
    – /data08/…

    This error comes up when you are modifying the file size in READ ONLY MODE.

  5. #5 von Michael Seberg am März 31, 2011 - 23:00

    Never thought to test this before. Decided to set standby_file_management to manual and test. Still works at least on Thanks for keeping me thinking Uwe!

  6. #6 von Uwe Hesse am April 1, 2011 - 08:56

    you’re welcome 🙂
    Thank you for your positive feedback!

  7. #7 von mundaoloco am Dezember 4, 2014 - 16:24

    Friends, I have a question about it.

    Imagina I have to change the disk driver for some datafiles on PRIMARY only. at this point the structures are the same on both servers, but we added another disk to move some datafiles to, just on Primary.

    How do I do this without impacting the sincronyzation? How to tell DG that just some datafiles from Primary changed place?

    tks a lot I’m loving this site

  8. #8 von Uwe Hesse am Dezember 4, 2014 - 18:18

    mundaoloco, I have just tested it: You just move the datafile on the primary. The redo is still applied on the standby, no problem. See:
    SQL> select name from v$datafile;


    SQL> alter database datafile 4 offline;

    Database altered.

    SQL> host cp /home/oracle/prima/users01.dbf /home/oracle/

    SQL> alter database rename file ‚/home/oracle/prima/users01.dbf‘ to
    2 ‚/home/oracle/users01.dbf‘;

    Database altered.

    SQL> alter database datafile 4 online;
    alter database datafile 4 online
    ERROR at line 1:
    ORA-01113: file 4 needs media recovery
    ORA-01110: data file 4: ‚/home/oracle/users01.dbf‘

    SQL> recover datafile 4;
    Media recovery complete.
    SQL> alter database datafile 4 online;

    Database altered.

    The standby is still fine after that without any action there:

    DGMGRL> show configuration

    Configuration – myconf

    Protection Mode: MaxPerformance
    prima – Primary database
    physt – Physical standby database

    Fast-Start Failover: DISABLED

    Configuration Status:

    DGMGRL> show database physt

    Database – physt

    Intended State: APPLY-ON
    Transport Lag: 0 seconds
    Apply Lag: 0 seconds
    Real Time Query: ON

    Database Status:

  9. #9 von mundaoloco am Dezember 5, 2014 - 17:15

    Great Uwe,

    No further worries or set up after that. just like you said, as simple as possible.

    I really appreciate your help. valuable information, great blog!!! 🙂

  10. #10 von Mustapha am Mai 4, 2017 - 15:57

    Actually i have just resized my table spaces on the primary and on checking my logical standby database, it didnt resize automatically. do i have to go ahead and do it again on the logical side?

  11. #11 von Johannes am Juni 22, 2017 - 17:39

    Great answers so far. Thx for that.

    Just 3 Questions:
    1. What about resize (shrink to lower size)? Is that also replicated on standby?
    2. Special Question in this topic: Has the standby database similar HWMs?
    3. Does this work also with tempfiles?

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

Du kommentierst mit Deinem Abmelden /  Ändern )


Du kommentierst mit Deinem Twitter-Konto. Abmelden /  Ändern )


Du kommentierst mit Deinem Facebook-Konto. Abmelden /  Ändern )

Verbinde mit %s

Diese Seite verwendet Akismet, um Spam zu reduzieren. Erfahre, wie deine Kommentardaten verarbeitet werden..