Tuesday, July 28, 2009

Test flashback

# stop applications (forms,concurrent managers etc)

# check parameters
sqlplus / as sysdba
show parameter recovery
show parameter flash

--ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=20G;
--ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='+ASM_ORADATA226';
--alter system set db_flashback_retention_target = 1440;

# Check archivelog, flashback

select log_mode, flashback_on from v$database;

# Enable flashback

sqlplus / as sysdba
shutdown immediate;
startup mount exclusive;
alter database flashback on;
select flashback_on from v$database;
shutdown immediate;
startup

# Test flashback point
select TO_CHAR(CURRENT_SCN) FROM V$DATABASE; - record this
create table flashback_test (id integer primary key);
create restore point before_load guarantee flashback database;
select TO_CHAR(CURRENT_SCN) FROM V$DATABASE;
insert into flashback_test values (1);
commit;
select * from flashback_test;
select TO_CHAR(CURRENT_SCN) FROM V$DATABASE;

# Test flashback to restore point
shutdown immediate;
startup mount exclusive;
flashback database to restore point before_load;
alter database open resetlogs;
select * from flashback_test;
drop restore point before_load;
shutdown immediate;

# Test flashback to scn
startup mount exclusive
flashback database to scn ;
alter database open resetlogs;
select * from flashback_test;