Flashback old primary database after dataguard failover.

# On new primary (old standby)
select to_char(standby_became_primary_scn) from v$database;

TO_CHAR(STANDBY_BECAME_PRIMARY_SCN)
----------------------------------------
32940869569

select scn_to_timestamp(32940869569) from dual;

SCN_TO_TIMESTAMP(32940869569)
----------------------------------------
28-APR-09 23.50.25.000000000

# On the old primary, flashback and start managed recovery:

select * from v$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TIM RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- -------------------- ---------------- -------------- ------------------------
32934668982 28-apr-2009 17:39:08 1440 8305770496 19973136384

shutdown immediate;
startup mount;
flashback database to scn 32940869569;
alter database convert to physical standby;
shutdown immediate;
startup mount;
alter database recover managed standby database using current logfile disconnect;

Flashback query

Flashback query relies on data in the UNDO tablespace. If the data has aged out of the undo tablespace, then you will get ORA-01555 snapshot too old.

ORADBA @pd01dwh1> DECLARE
CURSOR c IS select DEFAULTEMAILADDRESS from BKO_SERVER.EMAILDETAILS where action_type_id = 164;
v_rec c%ROWTYPE;
BEGIN
DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER (32774517554);
OPEN c;
DBMS_FLASHBACK.DISABLE;
LOOP
FETCH c INTO v_rec;
EXIT WHEN c%NOTFOUND;
dbms_output.put_line(v_rec.defaultemailaddress);
END LOOP;
CLOSE c;
END;
/
DECLARE
*
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 24 with name "_SYSSMU24$"
too small
ORA-06512: at line 9

Beware flashback bug in 10.2.0.3

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

# But the v$flashback_database_log table is confused

SQL> select * from v$flashback_database_log
2 ;

OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE
-------------------- --------- ---------------- --------------
ESTIMATED_FLASHBACK_SIZE
------------------------
0 60 4.1465E+10
1813890048

# Fix is to disable and re-enable

SQL> alter database flashback off;

Database altered.

SQL> alter database flashback on;

Database altered.

SQL> select * from v$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE
-------------------- --------- ---------------- --------------
ESTIMATED_FLASHBACK_SIZE
------------------------
3.2802E+10 23-APR-09 60 15941632
0

Add a semi colon for dbms_metadata.get_ddl

execute dbms_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);

select 'SELECT dbms_metadata.get_ddl(''USER'','''||USERNAME||''') stmt FROM dual;'
from dba_users
where profile <> 'DEFAULT'
and lock_date < sysdate
and not exists (select object_name from dba_objects where owner = username)
order by username;