RE: Question on dropping tablespace and transportable tablespaces
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <mschmitt@xxxxxxxxxxxx>, "'Grabowy, Chris'" <chris.grabowy@xxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 5 Sep 2013 17:38:09 -0400
Well, yes you can use flash back. You can clone with a tool such as Delphix.
BUT ALSO, since at least 6.0.37 circa 1989 the following has worked:
1) Set up a physical backup. Whether you use RMAN, or just physical files is
up to you.
2) Start recovery (whether this is in Dataguard or simply recovery is up to
you. If you're rolling you own, you'll have to ship the archived redo logs)
until cancel
3) Just keep rolling forward until you get near the exact vintage you want
(prior to recover until time and recover until scn type stuff, you had to
throw in an alter system switch logfile on the "primary" at the desired time
or event completion if you wanted or needed a fine granularity).
4) Cancel recovery
5) If you're looking for fine granularity, now you probably know the time or
scn you need, so recovery until <the syntax specifying time or scn desired>
6) Cancel recovery
[Steps 5 and 6 not needed if end of some particular log applied is an okay
time to cancel recovery]
7) Shut the recovery (physical backup down).
8) Make a copy of all the pieces of the physical backup.
9) Start up noopen with a pfile that points to your copy location for the
controlfile. Probably from last month or last week or last whatever. If your
file structure changed, make a new one, out of band with this process. You
probably renamed all the datafile locations in the controlfile last time. If
you didn't save that so you can use it, rename them now.
10) startup rename using backupcontrol file
11) open resetlogs -- make sure this is the COPY of the physical backup that
is currently shut down
12) The database (with a new name) that you opened in 11 is now a new,
frozen in time database that is open read/write. You can create extra
aggregations, add indexes, do whatever, and the underlying data for the
queries that you want to be frozen for a period of time are just that.
13) Shut down the new database and restart it. (This may not be needed any
more. At some vintages some shared memory at a hash that interfered with
restarting the physical recovery database didn't get relocated until the
restart with the new name, and/or some lockfiles were hanging around, etc.
Unless you're in a hurry it can't hurt. You'd probably like to know you can
shut down and restart your renamed frozen copy anyway.
14) start recovery back up on the physical backup
You can make as many vintages as you want and have space and names for. You
can also make clones of this frozen guy (probably very cheaply since you've
stopped updating it), after any special aggregations and indexes might be
optimal with something like Delphix.
IF it is just the one tablespace, then you can set up a slender physical
recovery database. You'll still need all the redo, but perhaps a lot of it
will not need to be applied. Just remember that this is no longer a
physical recovery source for anything else.
IF you plan to support multiple vintages, then research queries can continue
on prior vintages until you garbage can them (just remember the names cannot
overlap). Two vintages is useful because you can just tell them when the new
one is available and they are never without at least one authorized vintage.
Then you blow the older one away (ready to be copied over) later.
This all runs a bit contrary to the more recent idea that everyone wants the
latest greatest update and active dataguard.
But it fits quite nicely with the use case you have claimed. If you know
from history a bunch of the queries and analytics they are going to run,
then the aforementioned special aggregations and extra indexes might be like
a parlor trick. Once you've decided on a firmly frozen point in time for the
user data, all the ideas from Codds rules through the extra performance
overhead for maintaining indexes simply does not matter.
Have fun,
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Michael Schmitt
Sent: Thursday, September 05, 2013 3:44 PM
To: Grabowy, Chris; oracle-l@xxxxxxxxxxxxx
Subject: RE: Question on dropping tablespace and transportable tablespaces
I can't really see standby as an option with the requirement that they do
not want the data changing in the underlying tables until they give the
green light. They want to present the users with snapshots of the data that
they control. I was thinking it might be possible to do somehow using
flashback, but have not looked into it in detail yet
Thanks
-----Original Message-----
From: Grabowy, Chris [mailto:chris.grabowy@xxxxxxxx]
Sent: Thursday, September 05, 2013 2:19 PM
To: Michael Schmitt; oracle-l@xxxxxxxxxxxxx
Subject: RE: Question on dropping tablespace and transportable tablespaces
I guess a read only standby database is not an option?
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Michael Schmitt
Sent: Thursday, September 05, 2013 11:50 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: EXTERNAL: Question on dropping tablespace and transportable
tablespaces
Hello,
We have a user that wants to use transportable tablespaces to consistently
refresh a query database for a set of users. (Oracle 11.2.0.1, will be
11.2.0.4 soon)
For example, they have 3 tablespaces in instance A that they do different
transformation work on. At different points in the week they will have each
of the tablespaces moved over to instance B that the users are running
reports against. I would suspect that something like Active Data Guard
might be the best solution for this, but they only want the users to see the
data when they have completed their work on instance A and are good with it
(they do not want the data changing, but just want to allow the users
snapshots of data)
Transportable tablespaces seem pretty straight forward, but I am having some
questions about what the challenges might be when running dropping and then
re-adding the tablespace into a live system. With some quick testing, it
seems like I am able to take the tablespace offline (any users running
queries would receive an error). The drop of the tablespace proceeds as
well with the users receiving an object does not exist. Once the tablespace
has been added, then users are able to run selects again.
Is the only impact of this operation that user queries will fail? Is there
anything else that I will need to consider? Is there any operation that
might cause the 'offline' statement to fail?
Thanks in advance for your help
Mike
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
