Oracle Flashback database and restore points

itoporacledbaadmin | 22nd November 2016

FLASHBACK DATABASE WITH GUARANTEED RESTORE POINT

ENVIRONMENT

Platform : Solaris 5.11
Server Name : Scorpio
DB Version : Oracle 11.2.0.4
DB Name : prim1124
DB_UNIQUE_NAME : prim1124
Flashback : enabled

PREPARE AND CONFIGURATION

Ensure db_recovery_file_dest is set
Ensure db_recovery_file_dest_size is set
Ensure db_flashback_retention_target in second

***Redo logfiles must be available for the entire time period spanned by the flashback logs. depend on db_flashback_retention_target ***

(prim1124) /home/oracle $mkdir -p /oradbs/archive/FRA_prim1124
(prim1124) /home/oracle $sqlplus “/as sysdba”SQL*Plus: Release 11.2.0.4.0 Production on Wed Sep 8 08:52:25 2012Copyright (c) 1982, 2011, Oracle. All rights reserved.Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL>
SQL> alter system set db_recovery_file_dest=’/oradbs/archive/FRA_prim1124′ scope=spfile;System altered.SQL> alter system set db_recovery_file_dest_size=300G scope=spfile;System altered.
SQL> show parameter recovery
NAME TYPE VALUE
———————————— ———– ——————————
db_recovery_file_dest string /oradbs/archive/FRA_prim1124
db_recovery_file_dest_size big integer 300G
recovery_parallelism integer 0
SQL>
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database open;
SQL> alter system set db_flashback_retention_target=2880;

DETERMINE WHETHER FLASHBACK IS ALREADY ENABLED

Check Flashback, It’s available or not if It isn’t, You have to set it.

SQL> select flashback_on from v$database;
FLASHBACK_ON

——————

YES

*****if Flashback isn’t enabling *****
SQL> alter database flashback on;

CREATING AND USING FLASHBACK RESTORE POINT

SQL> CREATE RESTORE POINT grp_prim1124 GUARANTEE FLASHBACK DATABASE;

Restore point created.

SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE=’YES’;

NAME
————————————————
SCN TIME DATABASE_INCARNATION# GUARANTEE STORAGE_SIZE
———- ———————————————— ——————— ——— ————
grp_prim1124
8919818891 08-SEP-12 04.23.41.000000000 PM 2 YES 104765658

Flashback database to guarantee restore point

Shut down the database and start the database in mount mode.

(prim1124) /home/oracle $sqlplus “/as sysdba”

SQL*Plus: Release 11.2.0.4.0 Production on Wed Sep 8 08:52:25 2012

Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>
SQL>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup mount;

ORACLE instance started.

Total System Global Area 938451800 bytes
Fixed Size 761024 bytes
Variable Size 29874796 bytes
Database Buffers 9660700 bytes
Redo Buffers 7542480 bytes
Database mounted.
SQL>
SQL> flashback database to RESTORE POINT grp_prim1124;

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

DROP GUARANTEED RESTORE POINT

(prim1124) /home/oracle $sqlplus “/as sysdba”

SQL*Plus: Release 11.2.0.4.0 Production on Wed Sep 8 08:52:25 2012

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE=’YES’;

NAME
——————————————-
SCN
———-
TIME
———————————————-
DATABASE_INCARNATION# GUA STORAGE_SIZE
——————— — ————
grp_prim1124
8919819034
08-SEP-12 04.23.53.000000000 PM
2 YES 104765658

SQL> drop RESTORE POINT grp_prim1124;

Restore point dropped.

MONITORING FLASHBACK

SQL> select estimated_flashback_size/1024/1024/1024 “EST_FLASHBACK_SIZE(GB)” from v$flashback_database_log;

1,419

I'm Top Oracle DBA