RE: Question on dropping tablespace and transportable tablespaces

I am not that familiar with the details of Flashback Data Archive and will look into it for more information

Someone mentioned partitioning. We do have a partitioning license and my initial thought was that using partition exchange would be a great way to handle what the users want. Unfortunately my users are dismissing that since they feel it would be too much work on their end to re-design their current code

Mike   

-----Original Message-----
From: Kevin Jernigan [mailto:kevin.jernigan_at_oracle.com] Sent: Thursday, September 05, 2013 4:44 PM To: Michael Schmitt
Cc: Grabowy, Chris; oracle-l_at_freelists.org Subject: Re: Question on dropping tablespace and transportable tablespaces

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_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