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 :-)

About these ads

  1. #1 by sunilbhola on July 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 by sunilbhola on July 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 by Uwe Hesse on July 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 by sunilbhola on February 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 by Michael Seberg on March 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 11.2.0.1. Thanks for keeping me thinking Uwe!

  6. #6 by Uwe Hesse on April 1, 2011 - 08:56

    Michael,
    you’re welcome :-)
    Thank you for your positive feedback!

  7. #7 by mundaoloco on December 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 by Uwe Hesse on December 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;

    NAME
    ——————————————————————————–
    /home/oracle/prima/system01.dbf
    /home/oracle/prima/sysaux01.dbf
    /home/oracle/prima/undotbs01.dbf
    /home/oracle/prima/users01.dbf

    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
    Databases:
    prima – Primary database
    physt – Physical standby database

    Fast-Start Failover: DISABLED

    Configuration Status:
    SUCCESS

    DGMGRL> show database physt

    Database – physt

    Role: PHYSICAL STANDBY
    Intended State: APPLY-ON
    Transport Lag: 0 seconds
    Apply Lag: 0 seconds
    Real Time Query: ON
    Instance(s):
    physt

    Database Status:
    SUCCESS

  9. #9 by mundaoloco on December 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!!! :)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 2,975 other followers

%d bloggers like this: