CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle Flashback Database
Version 11.1
 
Flashback Database Demo
An alternative strategy to the demo presented here is to use Recovery Manager

RMAN> FLASHBACK DATABASE TO SCN = <system_change_number>;
Dependent Objects
GV_$FLASHBACK_DATABASE_LOG V_$FLASHBACK_DATABASE_LOG
GV_$FLASHBACK_DATABASE_LOGFILE V_$FLASHBACK_DATABASE_LOGFILE
GV_$FLASHBACK_DATABASE_STAT V_$FLASHBACK_DATABASE_STAT
Syntax 1: SCN FLASHBACK [STANDBY] DATABASE [<database_name>]
TO [BEFORE] SCN <system_change_number>
Syntax 2: TIMESTAMP FLASHBACK [STANDBY] DATABASE [<database_name>]
TO [BEFORE] TIMESTMP <system_timestamp_value>
Syntax 3: RESTORE POINT FLASHBACK [STANDBY] DATABASE [<database_name>]
TO [BEFORE] RESTORE POINT <restore_point_name>
 
Flashback Syntax Elements
OFF ALTER DATABASE FLASHBACK OFF
alter database flashback off;
ON ALTER DATABASE FLASHBACK ON
alter database flashback on;
Set Retention Target ALTER SYSTEM SET db_flashback_retention_target = <number_of_minutes>;
alter system set DB_FLASHBACK_RETENTION_TARGET = 2880;
Start flashback on a tablespace ALTER TABLESPACE <tablespace_name> FLASHBACK ON;
alter tablespace example flashback on;
Stop flashback on a tablespace ALTER TABLESPACE <tablespace_name> FLASHBACK OFF;
alter tablespace example flashback off;
 
Initialization Parameters
Setting the location of the flashback 
recovery area
db_recovery_file_dest=/oracle/flash_recovery_area
Setting the size of the flashback 
recovery area
db_recovery_file_dest_size=2147483648
Setting the retention time for flashback files (in minutes) -- 2 days
db_flashback_retention_target=2880
 
Demo
conn / as sysdba

SELECT flashback_on, log_mode
FROM v$database;

set linesize 121
col name format a30
col value format a30

SELECT name, value
FROM gv$parameter
WHERE name LIKE '%flashback%';

shutdown immediate;

startup mount exclusive;

alter database archivelog;

alter database flashback on;

alter database open;

SELECT flashback_on, log_mode
FROM v$database;

SELECT name, value
FROM gv$parameter
WHERE name LIKE '%flashback%';

-- 2 days
alter system set DB_FLASHBACK_RETENTION_TARGET=2880;

SELECT name, value
FROM gv$parameter
WHERE name LIKE '%flashback%';

SELECT estimated_flashback_size
FROM gv$flashback_database_log;
 

As SYS

As UWCLASS

SELECT current_scn
FROM v$database;

SELECT oldest_flashback_scn,
oldest_flashback_time
FROM gv$flashback_database_log;

GRANT flashback any table TO uwclass;
 
  create table t (
mycol VARCHAR2(20))
ROWDEPENDENCIES;

INSERT INTO t VALUES ('ABC');

INSERT INTO t VALUES ('DEF');

COMMIT;

CREATE RESTORE POINT bef_damage;

INSERT INTO t VALUES ('GHI');

COMMIT;

SELECT ora_rowscn, mycol FROM t;
SHUTDOWN immediate;

startup mount exclusive;

-- be sure to substitute your SCN
FLASHBACK DATABASE TO SCN 19513917;
or
FLASHBACK DATABASE TO RESTORE POINT bef_damage;

/*
FLASHBACK DATABASE TO TIMESTAMP (SYSDATE-1/24);

FLASHBACK DATABASE TO TIMESTAMP timestamp'2002-11-05 14:00:00';

FLASHBACK DATABASE 
TO TIMESTAMP to_timestamp('2002-11-11 16:00:00', 'YYYY-MM-DD HH24:MI:SS');
*/

-- this will fail
alter database open;

-- this will succeed
alter database open resetlogs;
 
  conn uwclass/uwclass

SELECT ora_rowscn, mycol FROM t;
SELECT *
FROM gv$flashback_database_stat;

alter system switch logfile;

shutdown immediate;

startup mount exclusive;

alter database flashback off;

alter database noarchivelog;

alter database open;

SELECT flashback_on, log_mode
FROM v$database;
 
host

rman target sys/pwd@orabase

RMAN> crosscheck archivelog all;

RMAN> delete archivelog all;

RMAN> list archivelog all;
 
 
-- if out of disk space
ORA-16014: log 2 sequence# 4163 not archived, no available destinations
ORA-00312: online log 2 thread 1: 'c:\oracle\oradata\orabase edo02.log'

-- what happens
The error ora-16014 is the real clue for this problem. Once the archive destination becomes full the location also becomes invalid. Normally Oracle does not do a recheck to see if space has been made available.

-- then
shutdown abort;

-- clean up disk space: then

startup

alter system archive log all to '/oracle/flash_recovery_area/ORABASE/ARCHIVELOG';
 
Related Topics
Flashback Archive
Flashback Drop
Flashback Query
Flashback Table
Flashback Transaction Backout
Flashback Transaction Query
Flashback Version Query
 Recycle Bin
Restore Points
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [275 users online]    © 2010 psoug.org