Flashback Database – Enable/Disable

Hi, this is the quick one, sometimes we forget the basic stuffs, so …
https://docs.oracle.com/cd/E11882_01/backup.112/e10642/flashdb.htm#BRADV582

--- Enable Flashback Database:

select FLASHBACK_ON from v$database;

1.) Choose the DIRECTORY:
/u01/oradata/KDBPP/FRA

2.) SHUT Immediate;

3.) Startup mount;

4.) Enable Archivelog Mode, if needed

alter database archivelog;

5.) Define the FRA - Size:
alter system set db_recovery_file_dest_size=50g scope=both;

6.) Define the FRA - Directory:
alter system set db_recovery_file_dest='/u01/oradata/KDBPP/FRA' scope=both;

7.) Enable FLASHBACK on DATABASE:
alter database flashback on;

8.) Open database:
alter database open;

-- Disable:
1.) Shutdown Database;
shut immediate;

2.) Startup mount;

3.) alter database flashback off;

4.) If needed, disable the Archivelog
alter database noarchivelog;

5.) alter database open;

select FLASHBACK_ON from v$database;

--------------------------------------------------------------------
Note:

If you face this issue (ORA-38781: cannot disable media recovery - 
have guaranteed restore points) trying put back in NOARCHIVELOG mode.

You need drop the active restore point before, so :

a.) SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,
        GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE
        FROM V$RESTORE_POINT; 

b.) drop restore point <RESTORE POINT NAME>;

c.) if you are in MOUNT state you can execute:
alter database noarchivelog;


Flashback Data Archive

https://docs.oracle.com/cd/B28359_01/appdev.111/b28424/adfns_flashback.htm#BJFFDCEH
1.) Grant
SQL> conn / as sysdba
 Connected.
 SQL>
 SQL>
 SQL> grant flashback archive administer to cmagno;
Grant succeeded.
SQL>
2.) tablespaces:
SQL> select tablespace_name, contents, status from dba_tablespaces;
TABLESPACE_NAME                CONTENTS  STATUS
 ------------------------------ --------- ---------
 SYSTEM                         PERMANENT ONLINE
 SYSAUX                         PERMANENT ONLINE
 UNDOTBS1                       UNDO      ONLINE
 TEMP                           TEMPORARY ONLINE
 USERS                          PERMANENT ONLINE
 EXAMPLE                        PERMANENT ONLINE
create tablespace ts_fda datafile 'D:\APP\CANDRADE\ORADATA\DB01\TS_FDA_01.DBF' size 10m autoextend on next 10m maxsize 8g;
3.) Flashback Data Archive
create flashback archive FDA_01 tablespace ts_fda retention 1 month;
1* SELECT * FROM DBA_FLASHBACK_ARCHIVE
 SQL> /
OWNER_NAME      FLASHBACK_ARCHIVE_NAME         FLASHBACK_ARCHIVE# RETENTION_IN_DAYS CREATE_TIME                                                         LAST_PURGE_TIME                                                     STATUS
 --------------- ------------------------------ ------------------ ----------------- --------------------------------------------------------------------------- --------------------------------------------------------------------------- -------
 SYS             FDA_01                                          1                30 25-FEB-16 02.44.41.000000000 PM                                             25-FEB-16 02.44.41.000000000 PM
SQL> SELECT * FROM DBA_FLASHBACK_ARCHIVE_TS;
FLASHBACK_ARCHIVE_NAME         FLASHBACK_ARCHIVE# TABLESPACE_NAME                QUOTA_IN_MB
 ------------------------------ ------------------ ------------------------------ ------------------------
 FDA_01                                          1 TS_FDA
4.) Table
create table cmagno.FDA_TESTE
 (num number,
 text varchar2(1000))
 tablespace users;
insert into cmagno.FDA_TESTE values (1,'TEST FDA 1');
 insert into cmagno.FDA_TESTE values (2,'TEST FDA 2');
 insert into cmagno.FDA_TESTE values (3,'TEST FDA 3');
 COMMIT;
5.) FDA to the table:
alter table cmagno.FDA_TESTE flashback archive FDA_01;
SQL> select * from dba_FLASHBACK_ARCHIVE_TABLES;
TABLE_NAME                                                                                                               OWNER_NAME      FLASHBACK_ARCHIVE_NAME         ARCHIVE_TABLE_NAME                            STATUS
 -------------------------------------------------------------------------------------------------------------------------------- --------------- ------------------------------ ----------------------------------------------
 FDA_TESTE                                                                                                                CMAGNO          FDA_01                         SYS_FBA_HIST_93691                            ENABLED
1* select * from dba_FLASHBACK_ARCHIVE_TABLES
 SQL> /
TABLE_NAME           OWNER_NAME      FLASHBACK_ARCHIVE_NAME         ARCHIVE_TABLE_NAME                            STATUS
 -------------------- --------------- ------------------------------ -----------------------------------------------------
 FDA_TESTE            CMAGNO          FDA_01                         SYS_FBA_HIST_93691                            ENABLED
6.) Test
SQL> select to_char(sysdate,'dd-mon-rr hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'D
 ------------------
 25-feb-16 15:09:26
delete cmagno.FDA_TESTE;
 commit;
SQL> select count(*) from cmagno.FDA_TESTE;
COUNT(*)
 ----------
 0
SQL> select * from cmagno.FDA_TESTE as of timestamp to_timestamp('25-feb-16 15:09:26','dd-mon-rr hh24:mi:ss');
NUM TEXT
 ---------- --------------------------------------------------
 1 TEST FDA 1
 2 TEST FDA 2
 3 TEST FDA 3
SQL> show parameter undo
NAME                                 TYPE        VALUE
 ------------------------------------ ----------- -------------
 temp_undo_enabled                    boolean     FALSE
 undo_management                      string      AUTO
 undo_retention                       integer     900
 undo_tablespace                      string      UNDOTBS1
create undo tablespace undotbs2 datafile 'D:\APP\CANDRADE\ORADATA\DB01\UNDOTBS2_01.DBF' size 10m autoextend on next 10m maxsize 8g;
alter system set undo_tablespace=undotbs2 scope=both;
-- Just to show
SQL> shut immediate
 Database closed.
 Database dismounted.
 ORACLE instance shut down.
 SQL> startup
 ORACLE instance started.
Total System Global Area 5133828096 bytes
 Fixed Size                  3842712 bytes
 Variable Size            1107299688 bytes
 Database Buffers         4009754624 bytes
 Redo Buffers               12931072 bytes
 Database mounted.
 Database opened.
 SQL> show parameter undo
NAME                                 TYPE        VALUE
 ------------------------------------ ----------- --------------------
 temp_undo_enabled                    boolean     FALSE
 undo_management                      string      AUTO
 undo_retention                       integer     900
 undo_tablespace                      string      UNDOTBS2
 SQL>
SQL> select * from cmagno.FDA_TESTE as of timestamp to_timestamp('25-feb-16 15:09:26','dd-mon-rr hh24:mi:ss');
NUM TEXT
 ---------- --------------------------------------------------
 1 TEST FDA 1
 2 TEST FDA 2
 3 TEST FDA 3
SQL> truncate table cmagno.FDA_TESTE;
Table truncated.
SQL> select * from cmagno.FDA_TESTE as of timestamp to_timestamp('25-feb-16 15:09:26','dd-mon-rr hh24:mi:ss');
NUM TEXT
 ---------- --------------------------------------------------
 1 TEST FDA 1
 2 TEST FDA 2
 3 TEST FDA 3
SQL> create view cmagno.vw_fda as select * from cmagno.FDA_TESTE as of timestamp to_timestamp('25-feb-16 15:09:26','dd-mon-rr hh24:mi:ss');
View created.
SQL> select * from cmagno.vw_fda;
NUM TEXT
 ---------- --------------------------------------------------
 1 TEST FDA 1
 2 TEST FDA 2
 3 TEST FDA 3
SQL> select * from cmagno.FDA_TESTE;
no rows selected

FLASHBACK DATABASE – RESTORE POINT

FRA=> /u01/disk/clonetst/FRA
shut immediate;
 startup mount;
alter system set db_recovery_file_dest='/u01/disk/clonetst/FRA' SCOPE=both;
 alter system set db_recovery_file_dest_size=10G SCOPE=both;
 alter database archivelog; --- IF NOT IN ARCHIVE MODE
 alter database flashback on;
alter database open;
alter system set db_flashback_retention_target=2880; --- NEED CHOOSE THE BEST OPTION FOR YOUR ENVIRONMENT
clonetst > select flashback_on from v$database;
FLASHBACK_ON
 ------------------
 YES
clonetst > select * from v$restore_point;
no rows selected
-- Test Data
create table cmagno.teste_flashback
 (num number,
 sys_date date,
 info varchar2(1000));
clonetst > create restore point BEFORE_INSERT_DATA guarantee flashback database;
Restore point created.
1* select * from v$restore_point
 SYS @ clonetst > /
SCN DATABASE_INCARNATION# GUA         STORAGE_SIZE TIME                           RESTORE_POINT_TIME   PRE NAME
 -------------------- --------------------- --- -------------------- ------------------------------ -------------------- --- ------------------------------
 47103817886                     2 YES             52428800 15/02/16 16:57:01,000000000                         YES BEFORE_INSERT_DATA
insert into cmagno.teste_flashback values (1,sysdate,'LINE 1 AFTER RESTORE POINT');
 insert into cmagno.teste_flashback values (2,sysdate,'LINE 2 AFTER RESTORE POINT');
 insert into cmagno.teste_flashback values (3,sysdate,'LINE 3 AFTER RESTORE POINT');
 COMMIT;
clonetst > SELECT * FROM cmagno.teste_flashback;
NUM SYS_DATE INFO
 ---------- -------- --------------------------------------------------
 1 15/02/16 LINE 1 AFTER RESTORE POINT
 2 15/02/16 LINE 2 AFTER RESTORE POINT
 3 15/02/16 LINE 3 AFTER RESTORE POINT
SYS @ clonetst > SHUT IMMEDIATE
 Database closed.
 Database dismounted.
 ORACLE instance shut down.
 SYS @ clonetst > startup mount;
 ORACLE instance started.
Total System Global Area  588746752 bytes
 Fixed Size                  2255472 bytes
 Variable Size             369100176 bytes
 Database Buffers          209715200 bytes
 Redo Buffers                7675904 bytes
 Database mounted.
SYS @ clonetst > flashback database to restore point BEFORE_INSERT_DATA;
Flashback complete.
SYS @ clonetst > alter database open resetlogs;
Database altered.
SYS @ clonetst > SELECT * FROM cmagno.teste_flashback;
no rows selected