Re: Updating SYS.TS$ to help with transportable tablespace

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:MeVua.31757$1s1.465064_at_newsfeeds.bigpond.com...
> You already clearly know the perils of updating the data dictionary
> directly, so I won't repeat all that.
>
> All I would say is that you'd need to be a better person than me to state
> with any confidence that you have tracked down all the very many
> dependencies that exist between the highly normalized tables that comprise
> the data dictionary. And if one hasn't done that, then one can't be
certain
> that what appears to work as a quick update today won't cause catastrophic
> failure next week, next month or whatever.
>
> As an example of the sort of stuff that goes on, though I realise it's not
> exactly relevant to what you are doing: did you know that in 9i, you can
> switch from one Undo tablespace to a new one. But that you should never
> switch back to the earlier undo tablespace, because data corruption can
> occur? And, what's more, if you instead drop the original undo tablespace
> and create a brand new one with merely the same name as the original one,
> and then switch back to it, the same thing can occur: re-using the same
undo
> tablespace name alone can cause problems (and apparently this is a
> deliberate design feature, not a bug).
>
> Now if an undo tablespace name stored somewhere in the depths of the data
> dictionary can wreak such havoc, I wouldn't be too keen on dabbling in ts$
> myself.
>
> I think the real issue, though, is the lack of support you'd get from
> Oracle, on absolutely any topic under the sun, if they ever get wind of
> what's been done. Even if you ask about an optimizer bug, they'd drop you
> saying merely that yours in an unsupported installation (cynics might say
> they look for any opportunity to avoid giving support anyway, so one more
> excuse won't make any difference... but is it worth it?)
>
> Regards
> HJR
>
>
> "Ryan Gaffuri" <rgaffuri_at_cox.net> wrote in message
> news:1efdad5b.0305090635.43501128_at_posting.google.com...
> > Asked this on ORACLE-L also, but its not a 100% mix between people who
> > frequent each and this is an question for me.
> >
> > BTW, someone on ORACLE-L got rather upset and told me to get my resume
> > ready. Dont need that. Not my idea. Not my implementation. Just asking
> > about it.
> >
> > ----------------
> >
> > My Question:
> >
> > We are doing this to make our transportable tablespace process
> > simpler.
> > We import the transported tablespace. Drop the old one. Rename the new
> > one.
> >
> > Anyone else do this? Any pitfuls other than the shock and awe of us
> > touching TS$? I know its not supported.
> >
> > For those of you who hate this idea, they've been doing it since
> > before
> > I got here. Just looking for outside opinions on this.
>
>

Id prefer not to do it, but I cant find a viable alternative. We are pushing up to 50 gigabytes a day to our production servers and if you include total I/Os and bandwidth to send it we are looking at 400-500 Gigabytes or more just on the ingestion. We are going to have to push about 1.5 TBs to a data mining application soon....

Its not that bad if the staging database goes down. Simple wipe. Reinstall. Import transported tablespaces. We have copies. May take 1-2 hours at most(possibly less than an hour). Users never see this. Though it does concern me.

Here is what we do, please let me know if you or anyone else has an alternative. We SQLLOAD data to our staging database. We ingest it, ie, scrub it and such. Time is an issue here first off. Happens all day 5 days a week.

To transport it we first update our staging database and change the tablespace name to something of the form: <tablespace_name>_date. We then transport it to a remote site. This remote site can not have any interruption in service. We import the tablespace. The name has to be changed since you cannot have two tablespaces with the same name.

We have two schemas for each tablespace in our production server. One active, one inactive. We take the inactive one and set its default tablespace to our new tablespace. We then point synonyms to the new schema. We have tested this against in doubt queries and Oracle is smart enough to handle the switch in tablespaces with no issues. Seems to pick up on the new data. Not sure how it does that... since it may have already read the changed blocks...

User experiences no downtime. Anyone have a better solution? I hope Oracles adds a 'RENAME TABLESPACE' to the next version... would make things easier. Received on Fri May 09 2003 - 17:35:31 CDT