Re: Which tablespace offline mode will cause a checkpoint to occur?

Hi Peter,

Comments in there somewhere.

"Peter" <peter_at_nomorenewsspammin.ca> wrote in message news:qh13fv0vuumep25bio0i3sn57iagrvilon_at_4ax.com...
> On Thu, 19 Jun 2003 07:55:15 GMT, Kenneth Koenraadt wrote:
>
> >On Thu, 19 Jun 2003 06:37:28 GMT, Peter <peter_at_nomorenewsspammin.ca>
> >wrote:
> >
> >>If you perform
> >>
> >>alter tablespace offline
> >>
> >>which mode will cause a checkpoint to occur?
> >>
> >>Normal, immediate or transactional?
> >>
> >
> >Hi Peter,
> >
> >"Transactional" does not exist (in 9i). "Temporary" does.
> >
>
> I checked the manual again , "temporary" does instigate a check point
> for this tablespace.

Yes, but the key point here is that Oracle will only checkpoint those datafiles that it can. You could have some datafiles that are stuffed or offline, Oracle will not checkpoint those. That being the case, those files will not be marked as safe in the controlfile and you will need to perform some sort of recovery of them before they can be brought online.

With immediate, Oracle doesn't bother with checkpointing any datafile.

>
>
> What I am not sure about is that if a checkpoint occurs for this
> tablespace right before it is taken offline, then when it is taken
> online again, its SCN will be lagging behind SCN of other tablespaces.
> How is this reconciled?
>

The point to note here is that Oracle documents the successful checkpointing of the datafile(s) in it's control file. So providing the data file has been successfully checkpointed (meaning that all of it's changes must have been written to the OS) and as the data file has subsequently been offline (meaning that no further changes could have occurred) then Oracle implicitly knows that no recovery is required.

That being the case, when an attempt is made to bring the data file back online again, Oracle goes, "yeah no worries" (you have to have your ear near the server to hear it), it simply resyncs the header of the data files with the last confirmed SCN of the database, which again it nabs from the control file.

Hope this makes some kinda sense :)

Good Luck

Richard Received on Thu Jun 19 2003 - 09:05:15 CDT