Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ Schema snapshot rollback     - OpenDJ installed on RHEL6 (Linux)

- Transfer files and permissions from Windows to Linux system by Samba (Linux)

- Xtrabackup creates a slave node without downtime (Database)

- Use Mop monitor stock prices at the Linux command line (Linux)

- Zabbix monitoring platform to extract a single server graphical Python implementation of concurrent (Server)

- Spring JDBC Comments (Programming)

- Oracle user lock how to know what causes (Database)

- Use Visual Studio to compile and use WinGDB remote debugging embedded Linux programs (Programming)

- Java object serialization and deserialization (Programming)

- Virtual Judge structures under Ubuntu 14.04 (Server)

- Android custom controls create the simplest skid menu in the history (Programming)

- Nagios (centreon) monitoring LVS (Server)

- How to upgrade to Oracle 11g Oracle 12c (Database)

- Generated characters using Java Videos (Programming)

- Protection Docker container Notice (Server)

- Systemd on RHEL7 (Linux)

- HTML5 Application Cache (Programming)

- Linux systems dmesg command processing failures and system information collected seven usage (Linux)

- How to run in support UDp Docker container JBoss data virtualization GA (Server)

- How to write a new Git protocol (Linux)

 
         
  Schema snapshot rollback
     
  Add Date : 2017-04-13      
         
         
         
  Days ago, one of our customers do not care on site to do a "delete" operation. But he found he could not confirm whether the object is deleted he really should be deleted. So we turned to, hoping to see the "Delete" before the operation interface.

Our first thought is by means of Flashback Query (Flashback Query). But his simple deletion is actually among the back-end database to delete the data of more than a dozen tables. And logic is used to display the code of these objects on the page is also quite complex. Therefore, Flashback Query does not contribute to his request. We finally use flashback_time parameters expdp will export the schema of the data, and then import a test environment to complete his request.

I was thinking, if Oracle provides a parameter flashback_scn at the session level / flashback_time control all queries in this session are flashed back to a certain point in time, it is very easy to solve this problem: just create a new connection when connected to the database to modify the parameters, you can query to the point in time of the snapshot.

So I thought, if we can find a way to achieve a simulation of a schema flashback snapshot yet. Eventually, I found a perfect way: Create a new schema, the schema in which to create a view for each source schema table, trying to introduce the flashback queries. And the introduction of a "global variables" to control the view of the flashback time / scn.

Here is the code

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

- #

- # $ 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 should 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 will generate the code used to create the flashback snapshot schema objects. Clients connect to the schema will read data to a point in time snapshot of the source schema. Of course, if there are other source schema plsql code words stored procedures, views, etc., but also to re-create them in the schema.
     
         
         
         
  More:      
 
- Java String type time compare the size (Programming)
- C ++ you can become a new scripting language (Programming)
- CentOS 6 Install Xen4.2 Virtualization Practice (Linux)
- PXE + Kickstart automatically install CentOS 6.5 (Linux)
- Java implementation heap sort (large root heap) (Programming)
- Btrfs file system repair techniques (Linux)
- Use C program in JavaScript (Programming)
- A new method for Linux hidden files (Linux)
- Different between Linux file path and the windows (Linux)
- Efficient Linux Shell - Shell special characters Summary (Linux)
- CentOS and RHEL installation under GAMIT10.6 (Linux)
- Source code to compile and install MySQL 5.7.9 (Database)
- CentOS 6.4 install and configure Squid Proxy Server (Linux)
- Install Oracle JDK 8 and JVM class loading mechanism in Linux (Linux)
- Justniffer installed on Ubuntu 15.04 (Linux)
- HashMap in Android and Java different implementations (Programming)
- Oracle 11.2.0.3 and MySQL5.6 DDL comparison (Database)
- Mutt - an email client that sends messages through the terminal (Linux)
- Use cmake to compile and install MySQL 5.5 (Database)
- Linux common network tools: hping Advanced Host Scan (Linux)
     
           
     
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.