RE: Question about resizing datafiles

Hello,

We are running in MaxAvailability mode

We are also in standby_file_management=auto

I think the issue we saw indicated that the users on the primary system were impacted by the file getting resized. My assumption was that the users would just be waiting for their commits to write to both the local redologs and the standby logs on the standby server, and would not be impacted by the file growing. Since that wasn't the case, I am still failing to grasp how the file resizing would have caused the users to pile up waiting on the logfile sync

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Nuno Souto Sent: Tuesday, January 08, 2013 4:19 AM
To: oracle-l_at_freelists.org
Subject: Re: Question about resizing datafiles

Michael Schmitt wrote,on my timestamp of 8/01/2013 8:56 AM:

> Does resizing a datafile put any sort of lock on the underlying
> objects or logfiles that would prevent updates from committing? We
> had an occurrence today during a resize of a datafile where the number
> of sessions doubled until it hit the max limit for the database. Grid
> control is indicating that the sessions were waiting on 'log file
> sync'. This is an 11.1.0.7 active dataguard instance (does not use
> ASM for datafiles), so I was thinking perhaps the active dataguard
> played a role. Otherwise perhaps the I/O throughput just went south
> on us during the resize. The resize was adding 5Gig to a 10Gig file.
> Once the file resize completed, everything kind of fell back to normal
>

My DG is a bit rusty but if I recall correctly, depending on how it is setup the database may be waiting for the standby instance(s) to also expand the file. Once at least one of the standby dbs has finished applying the relevant portion of the relevant redo log, then all proceeds normally. Again: this is only in maximum protection mode. But like I said: I'm a bit rusty on this and there may be other reasons.

--
Cheers
Nuno Souto
in sunny Sydney, Australia
dbvision_at_iinet.net.au
--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l