26 Kasım 2010 Cuma

Flashback Technologies


Flashback Query.


Query values in a table at a point in time.

select * from rays.VIMPORTANTDATA
as of timestamp to_timestamp('25-FEB-2010 08:10:00',
'DD-MON-YYYY HH24:MI:SS');

or

select * from rays.VIMPORTANTDATA
as of timestamp to_timestamp('25-FEB-2010 08:10:00',
  'DD-MON-YYYY HH24:MI:SS');
where employee_name = 'Smith';

It can also be used with an insert data to restore data

Insert into rays.VIMPORTANTDATA (
select * from rays.VIMPORTANTDATA
as of timestamp to_timestamp('25-FEB-2010 08:10:00',
  'DD-MON-YYYY HH24:MI:SS');
where employee_name = 'Smith');

WARNINGS
1) Does not work for truncated data (not in undo),
2) Subject to UNDO retention rules.
3) LONG columns cannot be selected in this way



Flashback table       


Restore an entire table to a point in time.

flashback table rays.VIMPORTANTDATA to timestamp to_timestamp('25-FEB-2010 08:10:00','DD-MON-YYYY HH24:MI:SS');


Flashback drop

No explanation necessary. Requires RECYCLEBIN

drop table rays.VIMPORTANTDATA;
flashback table rays.VIMPORTANTDATA to before drop;
                          

Flashback Versions


Allows the user to view changes in data over time

select Currency_code, Currency, VERSIONS_STARTTIME,
VERSIONS_ENDTIME, VERSIONS_OPERATION
from rays.fv1
  VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE
Where Currency_code = 'USD'
ORDER  BY Currency_code, VERSIONS_ENDTIME;

Example of output:

CUR   CURRENCY
--- ----------
VERSIONS_STARTTIME
---------------------------------------------------------------------------
VERSIONS_ENDTIME                                                            V
--------------------------------------------------------------------------- -
USD          5

25-FEB-10 11.09.19 AM

USD        .91
25-FEB-10 11.09.19 AM
25-FEB-10 11.12.13 AM                                                       U

USD        .95
25-FEB-10 11.12.13 AM
                                                                            U



Flashback Transaction


Allows the DBA to see who changed what, when

Firstly query the VERSIONS_XID (transaction ID)

select versions_xid, Currency_code, Currency, VERSIONS_STARTTIME, VERSIONS_ENDTIME, VERSIONS_OPERATION
    from rays.fv1
    VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE
    where Currency_code = 'USD'
 ORDER  BY Currency_code, VERSIONS_ENDTIME;

VERSIONS_XID     CUR   CURRENCY
---------------- --- ----------
VERSIONS_STARTTIME
---------------------------------------------------------------------------
VERSIONS_ENDTIME                                                            V
--------------------------------------------------------------------------- -
                 USD        .95

25-FEB-10 11.36.52 AM

0005000B00000152 USD        .96
25-FEB-10 11.36.52 AM
25-FEB-10 11.37.10 AM                                                       U

000900210000013B USD          5
25-FEB-10 11.37.10 AM
                                                                            U

Then query the transaction in the FLASHBACK_TRANSACTION_QUERY view

select XID, LOGON_USER, UNDO_SQL
from FLASHBACK_TRANSACTION_QUERY
where xid= HEXTORAW('&XID_VALUE');

When prompted, enter the Transaction ID

XID                LOGON_USER   UNDO_CHANGE# OPERATION
------------------ ------------ --------------------------------
000900210000013B          SCOTT update "RAYS"."FV1" set
  "CURRENCY" = '.96' where ROWID =
  'AAADY6AAEAAAACOAAC';


Flashback Database


Requires setup (See slides)

How far back can I recover?

SELECT oldest_flashback_time FROM gv$flashback_database_log;

In order to flashback database the database must be in mount mode:

flashback database to timestamp
to_timestamp('25-FEB-2010 08:10:00','DD-MON-YYYY HH24:MI:SS');

if the time is not quite right you can go back and forth until you get the right time

flashback database to timestamp
to_timestamp('25-FEB-2010 08:10:00','DD-MON-YYYY HH24:MI:SS');

      alter database open resetlogs

Example showing flashback through resetlogs:       

flash2> sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.1.0 Production on Thu Feb 25 11:48:57 2010

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

FLASH2> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
FLASH2> startup mount
ORACLE instance started.

Total System Global Area  522092544 bytes
Fixed Size                  2149672 bytes
Variable Size             218108632 bytes
Database Buffers          293601280 bytes
Redo Buffers                8232960 bytes
Database mounted.
FLASH2> flashback database to timestamp
  2  to_timestamp('25-FEB-2010 08:10:00','DD-MON-YYYY HH24:MI:SS');

Flashback complete.

FLASH2> alter database open resetlogs;

Database altered.

FLASH2> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

FLASH2> startup mount;
ORACLE instance started.

Total System Global Area  522092544 bytes
Fixed Size                  2149672 bytes
Variable Size             218108632 bytes
Database Buffers          293601280 bytes
Redo Buffers                8232960 bytes
Database mounted.
FLASH2> flashback database to timestamp
  2  to_timestamp('25-FEB-2010 07:10:00','DD-MON-YYYY HH24:MI:SS');

Flashback complete.

FLASH2> alter database open resetlogs;

Database altered.

FLASH2> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

FLASH2> startup mount;
ORACLE instance started.

Total System Global Area  522092544 bytes
Fixed Size                  2149672 bytes
Variable Size             218108632 bytes
Database Buffers          293601280 bytes
Redo Buffers                8232960 bytes
Database mounted.

FLASH2>   flashback database to timestamp
2  to_timestamp('25-FEB-2010 09:10:00','DD-MON-YYYY HH24:MI:SS');

Flashback complete.

FLASH2> alter database open resetlogs;

Database altered.
                   

 



Flasback Database – Restore points

FLASH2> create restore point BASE_SOFTWARE guarantee flashback database;

Restore point created.

FLASH2> create restore point SOFTWARE_REL_1 guarantee flashback database;         

Restore point created.

FLASH2> create restore point SOFTWARE_REL_2 guarantee flashback database;         

Restore point created.

FLASH2> create restore point SOFTWARE_REL_3 guarantee flashback database;

Restore point created.

FLASH2> select NAME, TIME from v$restore_point order by scn;

NAME
----------------------------------------------------------------------------------------------------
TIME
---------------------------------------------------------------------------
BASE_SOFTWARE
23-FEB-10 11.00.17.000000000 AM

SOFTWARE_REL_1
24-FEB-10 09.00.38.000000000 AM

SOFTWARE_REL_2
24-FEB-10 11.00.43.000000000 AM

SOFTWARE_REL_3
25-FEB-10 08.00.48.000000000 AM

Restores:

FLASH2> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
FLASH2> startup mount;
ORACLE instance started.

Total System Global Area  522092544 bytes
Fixed Size                  2149672 bytes
Variable Size             184554200 bytes
Database Buffers          327155712 bytes
Redo Buffers                8232960 bytes
Database mounted.
FLASH2> flashback database to restore point SOFTWARE_REL_1;           

Flashback complete.

FLASH2> alter database open resetlogs;   

Database altered.

FLASH2> shutdown immediate;    
Database closed.
Database dismounted.
ORACLE instance shut down.
FLASH2> startup mount;   
ORACLE instance started.

Total System Global Area  522092544 bytes
Fixed Size                  2149672 bytes
Variable Size             184554200 bytes
Database Buffers          327155712 bytes
Redo Buffers                8232960 bytes
Database mounted.
FLASH2> flashback database to restore point SOFTWARE_REL_2;

Flashback complete.

FLASH2> alter database open resetlogs;

Database altered.

FLASH2> shutdown immediate;    
Database closed.
Database dismounted.
ORACLE instance shut down.

FLASH2> startup mount
FLASH2> ORACLE instance started.

Total System Global Area  522092544 bytes
Fixed Size                  2149672 bytes
Variable Size             184554200 bytes
Database Buffers          327155712 bytes
Redo Buffers                8232960 bytes
Database mounted.
FLASH2> flashback database to restore point BASE_SOFTWARE;

Flashback complete.

FLASH2> alter database open resetlogs;

Database altered.

 

Flashback Archives


Gives the ability to use flash query without relying no UNDO. We use a tablespace and FLASH ARCHIVE to determine how long to record data.

--
-- Firstly Create the Flashback tablespace
--
CREATE TABLESPACE flasharea1 datafile '/v05/ORACLE/flash1/flasharea1_01.dbf' size 200M;
--
-- Then assign a flashback archive to the tablespaces
--
CREATE FLASHBACK ARCHIVE flasharchive_st TABLESPACE flasharea1 QUOTA 5M RETENTION 5 DAY;
CREATE FLASHBACK ARCHIVE flasharchive_lt TABLESPACE flasharea1 QUOTA 100M RETENTION 1 MONTH;
--
--
CREATE TABLE rays.fla_data1 (Currency VARCHAR2(3), exch_rate NUMBER);
CREATE TABLE rays.fla_data2 (Currency VARCHAR2(3), exch_rate NUMBER);
--
--
-- Assign the table to the flash area
--
--
ALTER TABLE rays.fla_data1 FLASHBACK ARCHIVE flasharchive_st;
ALTER TABLE rays.fla_data2 FLASHBACK ARCHIVE flasharchive_lt;
--
-- View flashback setup:

SELECT * FROM dba_flashback_archive_ts;

col create_time format a31
col last_purge_time format a31

SELECT flashback_archive_name, retention_in_days,
create_time, last_purge_time
FROM dba_flashback_archive;

SELECT * FROM dba_flashback_archive_tables;


--
-- Cleanup
ALTER TABLE rays.fla_data1 NO FLASHBACK ARCHIVE;
ALTER TABLE rays.fla_data2 NO FLASHBACK ARCHIVE;
--
DROP TABLE rays.fla_data1 CASCADE CONSTRAINTS;
DROP TABLE rays.fla_data2 CASCADE CONSTRAINTS;
--
DROP FLASHBACK ARCHIVE FLASHARCHIVE_ST;
DROP FLASHBACK ARCHIVE FLASHARCHIVE_LT;
DROP TABLESPACE FLASHAREA1 INCLUDING CONTENTS AND DATAFILES;

Hiç yorum yok:

Yorum Gönder