This discussion is archived
13 Replies Latest reply: Apr 13, 2013 10:19 AM by garywicke RSS

Standby datafile different size than Production ... how to fix?

garywicke Newbie
Currently Being Moderated
Environment:

Oracle 11.2.0.3 EE on Solaris

I have a Production database with a Standby in a remote location.

I had an issue this morning with a datafile running out of space. In the process of fixing that issue I changed the size of the datafile a couple of times due to the size of the mount point it was on. I had increased it too much initially and had to reduce the size so as not to fill the mount point.

It apparently confused the Standby somewhere in that process because now my Production datafile is 10GB and my Standby datafile is 5GB and I can't get it to update the size of the Standby datafile.

This is now causing an error on the Standby because the Production database has written data to the datafile above the 5GB mark and is causing an error on the Standby with the 'unable to extend ....' message. I have a stack of archive logs waiting to be applied on the Standby.

So, the question is: how do I synch up the size of the Standby datafile to match Production?

I have tried to resize the Production datafile again but using the same parameters hoping to force the changes to the Standby but so far that hasn't worked.

I can't manually change the size on the Standby because it's READ-ONLY.

Any help is GREATLY appreciated!!

-gary
  • 1. Re: Standby datafile different size than Production ... how to fix?
    teits Pro
    Currently Being Moderated
    hello,

    what is the value of STANDBY_FILE_MANAGEMENT parameter in your standby DB?

    to be able to help you specifically, we need error message in alertlog file.

    check:
    http://gavinsoorma.com/2009/07/data-guard-adding-and-resizing-a-datafile-on-the-primary-database/

    http://docs.oracle.com/cd/E11882_01/server.112/e25608/manage_ps.htm#i1022518


    Tobi
    HTH
  • 2. Re: Standby datafile different size than Production ... how to fix?
    garywicke Newbie
    Currently Being Moderated
    Hi Tobi

    Thanks for the quick response.

    The STANDBY_FILE_MANAGEMENT parameter is set to AUTO.

    Here is the error message from the alert log:
    <msg time='2013-04-12T21:50:49.891-04:00' org_id='oracle' comp_id='rdbms'
     client_id='' type='UNKNOWN' level='16'
     host_id='apsfdbb' host_addr='10.180.51.166' module=''
     pid='19779'>
     <txt>Errors in file /u01/app/oracle/diag/rdbms/apsmdmp1stby/APSMDMP1/trace/APSMDMP1_pr00_19779.trc:
    ORA-01237: cannot extend datafile 9
    ORA-01110: data file 9: &apos;/u02/oradata/APSMDMP1/mdm_data_dt01_01.dbf&apos;
    ORA-19502: write error on file &quot;/u02/oradata/APSMDMP1/mdm_data_dt01_01.dbf&quot;, block number 1303168 (block size=8192)
    ORA-27063: number of bytes read/written is incorrect
    SVR4 Error: 28: No space left on device
    Additional information: -1
    Additional information: 1048576
    ORA-19502: write error on file &quot;/u02/oradata/APSMDMP1/mdm_data_dt01_01.dbf&quot;, block number 1303040 (block size=8192)
    ORA-27063: number of bytes read/written is incorrect
    SVR4 Error: 28: No space left on device
    Additional information: -1
    Additional information: 1048576
     </txt>
    </msg>
    As I mentioned the datafile on the Production side is currently defined to be 10GB but the one on the Standby side is only 5GB.

    I will check out the other links you posted as well.

    Thanks again for your help!!

    -gary
  • 3. Re: Standby datafile different size than Production ... how to fix?
    garywicke Newbie
    Currently Being Moderated
    Question: Could I set the STANDBY_FILE_MANAGEMENT to MANUAL and then alter the size of the datafile and set the parameter back to AUTO?

    Will that cause the missing data to be applied to the now larger datafile?

    Thanks very much!!

    -gary
  • 4. Re: Standby datafile different size than Production ... how to fix?
    mseberg Guru
    Currently Being Moderated
    Gary;

    You have an issue I have not faced before so I want to answer very carefully.

    Question have you addressed "No space left on device" ?

    You also need to confirm if you have a gap. I'm assumming yes.

    If you run this query on the Primary it should give an idea :

    http://www.visi.com/~mseberg/data_guard/monitor_data_guard_transport.html

    I believe I have notes for fixing the Data File issue but will need a moment to find them.

    Refresh my memory, Are you doing an RMAN backup on the Primary

    Can you confirm the question while I look for my notes?

    h2. Update

    My notes are weak on this and several years old. What I have is

    1. Set to STANDBY_FILE_MANAGEMENT to MANUAL ( on the Standby ) ( ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL SCOPE=BOTH; )
    2. Alter the data file to increase it's size.
    3. Set STANDBY_FILE_MANAGEMENT to AUTO ( ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO SCOPE=BOTH; )
    4. Start apply again ( ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION; )

    Best Regards

    mseberg

    Edited by: mseberg on Apr 13, 2013 8:17 AM
  • 5. Re: Standby datafile different size than Production ... how to fix?
    garywicke Newbie
    Currently Being Moderated
    Hi Michael

    Thanks very much for jumping in!!

    Let's see if I can answer your questions:

    Yes, I have addresses the 'no space on device' issue. The mount point is pretty full (98%) but there is still over 2GB of space left. The datafile was set to NOT extend and was fixed at 5GB so I believe the issue was there was no space left in the datafile and unable to extend it. I had it set to not extend because of the tight space on the mount point and also the user told me the amount of data being added would fit in the allocated space (5GB). It turned out his estimate was way off!! Surprise!!

    I have added a 2nd datafile to the tablespace on another mount point and the tablespace has plenty of room now.

    I have about 100 archived log files that need to be applied to the Standby at the moment.

    I am indeed doing an RMAN backup on the Primary, FULL on Sunday, INCR daily and ARCH backups every 4 hours (actually every hour lately as the large data load is going on).

    It looks to me like the datafile size changes I did on the Primary didn't all get transferred to the Standby. I was using TOAD and didn't force a log switch between changes as suggested in the above response.

    I am thinking there might be two (2) options:

    1- Change the STANDBY_FILE_MANAGEMENT to MANUAL and resize the datafile to the desired 10GB
    2- Delete the existing datafile (rename would be better) and then copy the datafile from the Primary and hope the system would re-synch everything.

    I am obviously open to any and all suggestions.

    Thanks very much!!!

    -gary
  • 6. Re: Standby datafile different size than Production ... how to fix?
    mseberg Guru
    Currently Being Moderated
    Gary;

    Based on my old ( probably Oracle 10 ) notes I would do number 1. I posted what I have in in the prior post as an edit.

    If this does not work I would not do number 2. I would consider RMAN inc roll forward.

    So try the resize of the datafile and then monitor after apply starts and lets see what we have.

    Best Regards

    mseberg
  • 7. Re: Standby datafile different size than Production ... how to fix?
    garywicke Newbie
    Currently Being Moderated
    Michael

    It looks like we're thinking along the same lines!! See what you've taught me already!

    When I turn off log apply, change the standby file management and attempt to resize the datafile, I get the message 'ORA-16000: database open for read-only access'.

    I'm doing all this on the Standby side, right?

    -gary
  • 8. Re: Standby datafile different size than Production ... how to fix?
    mseberg Guru
    Currently Being Moderated
    OK

    Stop apply on the standby if running and shutdown the database.

    Then startup mount

    Make sure you are set to MANUAL at this point

    Then issue ALTER DATABASE DATAFILE .. commands to resize the datafiles

    Best Regards

    mseberg

    Edited by: mseberg on Apr 13, 2013 8:38 AM
  • 9. Re: Standby datafile different size than Production ... how to fix?
    garywicke Newbie
    Currently Being Moderated
    Well cwap! :-)

    I get the 'database not open' message when trying to resize the datafile when the database is in mounted state.

    I opened the database and tried again and got the 'ORA-16000: database open for read-only access' message again.

    Seems like a Catch-22! :-(

    I did verify the standby file management was set to MANUAL before trying the resizing.

    -gary
  • 10. Re: Standby datafile different size than Production ... how to fix?
    mseberg Guru
    Currently Being Moderated
    Right. You cannot resize a datafile in mount and you know about the read-only issue.

    Moving the copy of database from the Primary will not work since its SCN number are well ahead of the standby database.

    Thinking...

    Found you exact issue here :

    http://gavinsoorma.com/2009/06/resize-standby-datafile-if-disk-runs-out-of-space/

    This guy always gives great information. The problem I see with his solution is the "db_file_name_convert" My concern is this might conflict with something else.

    Checking for MOS note...

    Edited by: mseberg on Apr 13, 2013 8:55 AM

    Edited by: mseberg on Apr 13, 2013 8:58 AM
  • 11. Re: Standby datafile different size than Production ... how to fix?
    garywicke Newbie
    Currently Being Moderated
    Michael

    Do you think changing the standby file management back to AUTO and re-doing the datafile resize on the Primary, even though it would be changing it to what it already is, and forcing a log switch would propagate the changes to the Standby??

    Seems a bit kludgy but I'm getting desperate!

    Thanks!!

    -gary
  • 12. Re: Standby datafile different size than Production ... how to fix?
    mseberg Guru
    Currently Being Moderated
    Gary;

    I'm thinking no because that change would be in the current log file and you are 100 behind.

    I'm leaning towards RMAN roll forward at this point.

    Insight - but no solution

    ORA-600 [3020] During Standby Recovery after Datafile Resize to Smaller Size On Primary [ID 1446482.1]

    h2. Update

    What I would probably try ( my concern would be that it somehow fails to increase the datafile )

    RMAN Incremental Backups to Roll Forward a Physical Standby Database

    http://www.oracle-ckpt.com/articles/


    So CKPT has this note. If it fails you might have to recreate the standby DB, but it should work and take much less time. If you go this route I would probably reset the parameters on the standby first ( back to AUTO )


    Mseberg

    Edited by: mseberg on Apr 13, 2013 9:07 AM

    Edited by: mseberg on Apr 13, 2013 9:08 AM

    Edited by: mseberg on Apr 13, 2013 9:21 AM
  • 13. Re: Standby datafile different size than Production ... how to fix?
    garywicke Newbie
    Currently Being Moderated
    Michael

    Well I think I've made progress. I was looking at the error message again in the alert log and was questioning the 'no space left on device' message. I was originally thinking I had just run out of room in the datafile.

    Upon investigating there were a couple of leftover redo log files and a control file from the Test database still on that mount point that I hadn't moved to their new directories yet so there was truly no space on the device to expand the datafile to 10GB.

    I moved the out-of-place files and set the standby file management back to AUTO and restarted the log apply and the datafile was successfully expanded to 10GB!!!!!

    Now I'm holding my breath to see if the logs start being applied correctly.

    UPDATE!!!!!

    Logs being applied so I'm in catch-up mode but it looks like things are OK.

    As usual there's a long story as to why I had the Test files in the directories that should belong to the Standby but I won't bore you with that tome!!

    Michael, as usual I can't thank you enough for your help and guidance. If nothing else I have learned from you to step back and take a deep breath and look at exactly what has happened and investigate the alert logs, etc. before jumping in and trying things.

    Enjoy the rest of your weekend. Now I can go watch my grandkids play soccer!!

    -gary


    Michael, even though your method in the last post wasn't used I marked it as 'correct' because of all your help before that and I wanted to give you credit for that.

    Edited by: garywicke on Apr 13, 2013 1:17 PM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points