HelloDBA [中文]
Search Internet Search HelloDBA
  Oracle Technology Site. email: fuyuncat@gmail.com  MSN: fuyuncat@hotmail.com Add to circle  acoug  acoug 

Flashback Snapshot of Schema

[中文]

Author:  fuyuncat

Source:  www.HelloDBA.com

Date:  2015-11-03 21:41:06

Share to  Twitter Facebook GMail Blogger Orkut Google Bookmarks Sina Weibo QQ Renren Tieba Kaixin Douban Taobao

Few days ago, one of my clients performed a "drop" action occidentally on the web site. But he could not confirm what he dropped is correct or not. He want to see the screen before he dropped the items.

 The first thought we got is to utilize flashback query. However, this simple action actually deleted important data from many tables (over 10), and the code of displaying the i is fairly complex. Flashback query doesn't help in this case. We finally exported entire schema using expdp with flashback_time option, and imported it to a test environment.

I ever thought if oracle provided a session parameter flashback_scn/flashback_time to allow the user flashback query all of data to a specified scn/timestamp in the session, it will make things simple. What we need to do is to create a new connection, and change this parameter after connected to database.

Then I was thinking can I find a workaround way? I finally got one. I can build a new schema, then create a set of views referring to the existing schema, involving the flashback query feature. And I can use a "global parameter" to control the flashback scn/timestamp.

Here is the code.

SQL代码
  1. -- ################################################################################  
  2. -- #  
  3. -- #         $Id: schema_snapshot.sql  
  4. -- #  
  5. -- #        File: $RCSfile: schema_snapshot.sql,v $  
  6. -- # Description: create a snapshot for a schema  
  7. -- #       Usage: sqlplus -s /nolog @schema_snapshot <existing_schema_name> <snapshot_schema_name>  
  8. -- #     Created: 07/02/2014  
  9. -- #      Author: Wei Huang  
  10. -- # User run as: / as sysdba (OS user should be oracle owner)  
  11. -- #  Parameters: 1: existing schema name  
  12. -- #  Parameters: 2: new schema name  
  13. -- #  Parameters: 3: snapshot timestamp  
  14. -- #  
  15. -- #  Copyright (c) 2014 Wei Huang  
  16. -- #  
  17. -- # History  
  18. -- # Modified by   When      Why  
  19. -- # -----------   -------   ----------------------------------------------------  
  20. -- ################################################################################  
  21.   
  22. prompt Usage: @schema_snapshot <existing_schema_name> <snapshot_schema_name>  
  23. prompt Description: create a snapshot for a schema  
  24. prompt   
  25.   
  26. declare  
  27.   sql_str varchar2(4000);  
  28.   c number;  
  29. begin  
  30.   select count(1) into c from dba_users where username = upper('&2');  
  31.   if c = 0 then  
  32.     execute immediate 'create user &2 identified by &2';  
  33.     execute immediate 'grant connect,resource to &2';  
  34.     sql_str := q'[ 
  35. CREATE OR REPLACE PACKAGE &2.var_pkg IS 
  36.   var varchar2(255); 
  37.   PROCEDURE set_var(val varchar2); 
  38.   function get_var return varchar2; 
  39. END var_pkg ; 
  40. / 
  41.  
  42. CREATE OR REPLACE PACKAGE BODY &2.var_pkg IS 
  43.   PROCEDURE set_var(val varchar2) IS 
  44.   BEGIN 
  45.     var := val; 
  46.   end set_var; 
  47.  
  48.   function get_var return varchar2  
  49.   IS 
  50.   BEGIN 
  51.     return var; 
  52.   END get_var; 
  53. END var_pkg; 
  54. / 
  55. ]';  
  56.   
  57.     execute immediate sql_str;  
  58.     for q in (select 'grant select,flashback on '||owner||'.'||table_name||' to '||upper('&2')||';' str from dba_tables where owner=upper('&1')) loop  
  59.       execute immediate q.str;  
  60.     end loop;  
  61.     for q in (select 'create or replace view '||upper('&2')||'.V_'||table_name||' as select * from '||owner||'.'||table_name||'  as of timestamp to_timestamp(var_pkg.get_var,''yyyymmddhh24miss'');' from dba_tables where owner=upper('&1') loop  
  62.       execute immediate q.str;  
  63.     end loop;  
  64.     for q in (select 'create or replace synonym '||upper('&2')||'.'||table_name||' for '||upper('&2')||'.V_'||table_name||';' from dba_tables where owner=upper('&1') loop  
  65.       execute immediate q.str;  
  66.     end loop;  
  67.   
  68.     &2.var_pkg.set_var('&3');  
  69.   end if;  
  70. end;  
  71. /  

This will generate the code to create a "snapshot schema". Clients connecting this schema will query all the data before the specified time. Of course, if there are procedures/views in the existing schema, they should be created in the new schema referring to those synonyms.

Top

Copyright ©2005, HelloDBA.Com All reseverd.

Declaration
by fuyuncat