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