Schema rollback Snapshot

Schema rollback Snapshot

Many days ago, one of our customers accidentally performed a "delete" Operation on the website. however, he found that he could not confirm whether the objects he deleted should be deleted. so we turned to us to see the interface before the "delete" operation.

The first thing we think of is Flashback Query ). However, this simple delete operation actually deletes data from more than 10 tables in the background database. The logic for displaying code for these objects on the page is also quite complex. Therefore, flash query does not help his requirements. We finally use the flashback_time parameter of expdp to export the schema data and then import the data to a test environment to complete the request.

I thought at the time that if Oracle provided a parameter flashback_scn/flashback_time at the session level to control all queries of this session to a certain time point, the problem would be easily solved: you only need to create a new connection. After you connect to the database, you can modify this parameter to query the snapshots at this time point.

So I thought again, could I find a way to simulate and implement a schema flash snapshot. Finally, I found an imperfect method: to create a new schema, create a view for each table in the source schema, and introduce a flashback query in the attempt. A "global variable" is introduced to control the view's flash time/scn.

The following code is used:

--####################################### ######################################## #

--#

-- # $ Id: schema_snapshot. SQL

--#

-- # File: $ RCSfile: schema_snapshot. SQL, v $

-- # Description: create a snapshot for a schema

-- # Usage: sqlplus-s/nolog @ schema_snapshot <existing_schema_name> <snapshot_schema_name>

-- # Created: 07/02/2014

-- # Author: Wei Huang

-- # User run as:/as sysdba (OS user shocould be oracle owner)

-- # Parameters: 1: existing schema name

-- # Parameters: 2: new schema name

-- # Parameters: 3: snapshot timestamp

--#

-- # Copyright (c) 2014 Wei Huang

--#

-- # History

-- # Modified by When Why

--#----------------------------------------------------------------------

--####################################### ######################################## #

Prompt Usage: @ schema_snapshot <existing_schema_name> <snapshot_schema_name>

Prompt Description: create a snapshot for a schema

Prompt

Declare

SQL _str varchar2 (4000 );

C number;

Begin

Select count (1) into c from dba_users where username = upper ('& 2 ');

If c = 0 then

Execute immediate 'create user & 2 identified by & 2 ';

Execute immediate 'Grant connect, resource to & 2 ';

SQL _str: = Q '[

Create or replace package & 2. var_pkg IS

Var varchar2 (255 );

PROCEDURE set_var (val varchar2 );

Function get_var return varchar2;

END var_pkg;

/

 

Create or replace package body & 2. var_pkg IS

PROCEDURE set_var (val varchar2) IS

BEGIN

Var: = val;

End set_var;

 

Function get_var return varchar2

IS

BEGIN

Return var;

END get_var;

END var_pkg;

/

] ';

Execute immediate SQL _str;

For q in (select 'Grant select, flashback on '| owner | '. '| table_name | 'to' | upper (' & 2') | '; 'str from dba_tables where owner = upper (' & 1') loop

Execute immediate q. str;

End loop;

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

Execute immediate q. str;

End loop;

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

Execute immediate q. str;

End loop;

& 2. var_pkg.set_var ('& 3 ');

End if;

End;

/

This code generates code for creating objects in the Flash snapshot schema. The Client Connected to the schema reads the snapshot data at a certain time point of the source schema. Of course, if there are other plsql Code such as stored procedures and views in the source schema, re-create the schema.