RE: Question on dropping tablespace and transportable tablespaces

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Michael Schmitt
Sent: Thursday, September 05, 2013 3:44 PM To: Grabowy, Chris; oracle-l_at_freelists.org 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_at_lmco.com] Sent: Thursday, September 05, 2013 2:19 PM To: Michael Schmitt; oracle-l_at_freelists.org 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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Michael Schmitt
Sent: Thursday, September 05, 2013 11:50 AM To: oracle-l_at_freelists.org
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

--

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

--

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

--

http://www.freelists.org/webpage/oracle-l Received on Thu Sep 05 2013 - 23:38:09 CEST