Re: Question on dropping tablespace and transportable tablespaces
- From: Kevin Jernigan <kevin.jernigan@xxxxxxxxxx>
- To: mschmitt@xxxxxxxxxxxx
- Date: Thu, 05 Sep 2013 14:43:31 -0700
We have customers that use Flashback Data Archive together with login
triggers to limit users' sessions to a specific point-in-time view of
the database. When they are ready for the users to see newer data, they
update the login trigger, and then tell the users to log out and log in
again.
This approach would give you maximum flexibility in terms of what
timestamps / snapshots are available for users - essentially all points
in time back to the beginning of your Flashback Data Archive history for
the tables in question. It would also require you to configure enough
storage in your production environment to hold the historical data - and
that is very much dependent on the change rates on the tables you're
tracking, as well as the compression ratios for that data (if you have
licensed the Advanced Compression option and thus are able to apply
compression to the history tables).
Note that there's a licensing quirk wrt Flashback Data Archive: prior to
11.2.0.4, you are REQUIRED to license Advanced Compression to use it.
From 11.2.0.4 forward, you can use Flashback Data Archive in both EE
and SE without licensing anything other than the database, EXCEPT that
you cannot use "optimization" on the history tables unless you license
Advanced Compression. As you might guess, history table optimization in
11.2.0.4 consists of applying OLTP Table Compression, SecureFiles
Compression, and SecureFiles Deduplication to the Flashback Data Archive
history tables, and there is new DDL syntax to enable / disable history
table optimization.
Let me know if you have questions etc.
-KJ
--
Kevin Jernigan
Senior Director Product Management
Advanced Compression, Hybrid Columnar
Compression (HCC), Database File System
(DBFS), SecureFiles, Database Smart Flash
Cache, Total Recall, Database Resource
Manager (DBRM), Direct NFS Client (dNFS),
Continuous Query Notification (CQN),
Index Organized Tables (IOT), Information
Lifecycle Management (ILM)
(650) 607-0392 (o)
(415) 710-8828 (m)
On 9/5/13 12:43 PM, Michael Schmitt wrote:
> 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:
