Oracle:How to enable and use the Flashback Technology?
Enable the Flashback Recovery
Step 1: Add the initialization parameters
Open the init.ora file and add following parameters:
*.db_recovery_file_dest='/app/oracle/product/flash_recovery_area'
*.db_recovery_file_dest_size=2G
*.db_flashback_retention_target=10080 # Represents 7 days (60 minutes*24*7)
Note: DB_FLASHBACK_RETENTION_TARGET specifies the upper limit (in minutes) on how far back in time the database may be flashed back. How far back one can flashback a database depends on how much flashback data Oracle has kept in the flash recovery area.
Step 2: Enable flashback recovery in the database
$ sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jun 2 23:26:03 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218292 bytes
Variable Size 67111180 bytes
Database Buffers 92274688 bytes
Redo Buffers 7168000 bytes
Database mounted.
SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38707: Media recovery is not enabled.
(Make sure the "Archive log mode" is turned ON)
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 12
Current log sequence 14
SQL>
SQL> alter database archivelog;
Database altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 12
Next log sequence to archive 14
Current log sequence 14
SQL> alter database open;
Database altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
$ sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jun 2 23:40:00 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218292 bytes
Variable Size 67111180 bytes
Database Buffers 92274688 bytes
Redo Buffers 7168000 bytes
Database mounted.
SQL> alter database flashback on;
Database altered.
You can now notice that the flashback recovery logs are now being generated :
$ pwd
/app/oracle/product/flash_recovery_area/UNIDB/flashback
$ ls -l
total 8008
-rw-r----- 1 oracle oinstall 8200192 2010-06-02 23:40 o1_mf_60g90zgg_.flb
$
How to do the flashback recovery?
Step 1: Lets create a new table and note the timestamp when the table is created.
$ sqlplus scott/tiger
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jun 2 23:44:27 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> create table flashback_testing (column1 number(20));
Table created.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
FLASHBACK_TESTING TABLE
SQL>
SQL>
SQL> select to_char(sysdate,'dd-mm-yyyy hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'DD
-------------------
02-06-2010 23:45:51
SQL>
Step 2: Drop the table and note the time when it was dropped.
$ sqlplus scott/tiger
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jun 2 23:49:57 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select to_char(sysdate,'dd-mm-yyyy hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'DD
-------------------
02-06-2010 23:50:12
SQL> drop table flashback_testing;
Table dropped.
SQL> select to_char(sysdate,'dd-mm-yyyy hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'DD
-------------------
02-06-2010 23:50:26
SQL>
Step 3: Flashback the database
How far can we flashback the database?
$ sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jun 2 23:52:02 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';
Session altered.
SQL> select sysdate, min(first_time), (sysdate-min(first_time)) from v$flashback_database_logfile;
SYSDATE MIN(FIRST_TIME) (SYSDATE-MIN(FIRST_TIME))
-------------------- -------------------- -------------------------
02-jun-2010 23:52:13 02-jun-2010 23:40:15 .008310185
Note: we can flashback the database to 02-jun-2010 23:40:15 or later.
Perform the Flashback Recovery
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218292 bytes
Variable Size 67111180 bytes
Database Buffers 92274688 bytes
Redo Buffers 7168000 bytes
Database mounted.
SQL> flashback database to timestamp to_date('02-jun-2010 23:50:12', 'dd-mon-yyyy hh24:mi:ss');
Flashback complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
Note: You must open the database with resetlogs option.
SQL> alter database open resetlogs;
Database altered.
SQL> conn scott/tiger
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
FLASHBACK_TESTING TABLE < -- The table is back.
- inforadar's blog
- Login or register to post comments
