This content has been marked as final. Show 13 replies
hello,1 person found this helpful
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.
Thanks for the quick response.
The STANDBY_FILE_MANAGEMENT parameter is set to AUTO.
Here is the error message from the alert log:
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.
<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: '/u02/oradata/APSMDMP1/mdm_data_dt01_01.dbf' ORA-19502: write error on file "/u02/oradata/APSMDMP1/mdm_data_dt01_01.dbf", 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 "/u02/oradata/APSMDMP1/mdm_data_dt01_01.dbf", 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>
I will check out the other links you posted as well.
Thanks again for your help!!
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;1 person found this helpful
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 :
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?
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; )
Edited by: mseberg on Apr 13, 2013 8:17 AM
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;1 person found this helpful
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.
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?
OK1 person found this helpful
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
Edited by: mseberg on Apr 13, 2013 8:38 AM
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.
Right. You cannot resize a datafile in mount and you know about the read-only issue.1 person found this helpful
Moving the copy of database from the Primary will not work since its SCN number are well ahead of the standby database.
Found you exact issue here :
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
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!
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  During Standby Recovery after Datafile Resize to Smaller Size On Primary [ID 1446482.1]
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
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 )
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
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.
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!!
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