Flashback Database - Revision history

Ittichai at 12:23, 7 March 2011

2011-03-07T12:23:35Z

Ittichai at 10:00, 7 March 2011

2011-03-07T10:00:53Z

Ittichai at 10:00, 7 March 2011

2011-03-07T10:00:38Z

Ittichai at 09:57, 7 March 2011

2011-03-07T09:57:09Z

Ittichai at 01:40, 7 March 2011

2011-03-07T01:40:57Z

Ittichai at 01:32, 7 March 2011

2011-03-07T01:32:05Z

Ittichai: Created page with "== What is Flashback Database? == Coming soon == Configuration == * Verify that the '''db_recovery_file_dest''' is set. This is the location of the flashback recovery area where..."

2011-03-07T01:30:10Z

Created page with "== What is Flashback Database? == Coming soon == Configuration == * Verify that the '''db_recovery_file_dest''' is set. This is the location of the flashback recovery area where..."

New page

== What is Flashback Database? ==
Coming soon

== Configuration ==
* Verify that the '''db_recovery_file_dest''' is set. This is the location of the flashback recovery area where the flash recovery files will be stored. Verify that there is sufficient space in the '''db_recovery_file_dest_size''' as well. Lastly, set an appropriate retention time of '''db_flashback_retention_target''' in minutes for flashback files.
<pre>
SQL> show parameter db_recovery_file

NAME TYPE VALUE
------------------------------------ ----------- -------------
db_recovery_file_dest string +DG2
db_recovery_file_dest_size big integer 100G

SQL> show parameter db_flashback

NAME TYPE VALUE
------------------------------------ ----------- -------------
db_flashback_retention_target integer 2880
</pre>

* Turn the flashback ON.
[Sample here is for RAC database.]
<pre>
-- Execute this on one of the nodes
SQL> alter system set cluster_database=false scope=spfile;

System altered.

-- Stop database.
$ srvctl stop database -d DB11G

-- Startup MOUNT
SQL> startup mount
ORACLE instance started.

Total System Global Area 1.3700E+10 bytes
Fixed Size 2113448 bytes
Variable Size 7385194584 bytes
Database Buffers 6308233216 bytes
Redo Buffers 4440064 bytes
Database mounted.

-- Turn flashback ON.
SQL> alter database flashback on;

Database altered.

-- Put cluster_database back to TRUE.
SQL> alter system set cluster_database=true scope=spfile;

System altered.

SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

-- Restart database.
$ srvctl start database -d DB11G
</pre>

* Verify that flashback is ON.
<pre>
SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES
</pre>

* Test flashback database.
<pre>
SQL> create restore point before_flashback;

Restore point created.

SQL> select * from v$restore_point;

SCN DATABASE_INCARNATION# GUA STORAGE_SIZE
---------- --------------------- --- ------------
TIME
---------------------------------------------------------------------------
RESTORE_POINT_TIME PRE
--------------------------------------------------------------------------- ---
NAME
--------------------------------------------------------------------------------
1.0376E+13 1 NO 0
10-APR-10 05.10.40.000000000 PM
NO
BEFORE_FLASHBACK

-- Create a test table.
SQL> create table a (a number);

Table created.

-- Verify that table is created.
SQL> select * from tab where tname='A';

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
A TABLE

-- To flashback database, it needs to startup to MOUNT
$ srvctl stop database -d DB11G

SYS@PMDW1 AS SYSDBA> startup mount
ORACLE instance started.

Total System Global Area 1.3700E+10 bytes
Fixed Size 2113448 bytes
Variable Size 7385194584 bytes
Database Buffers 6308233216 bytes
Redo Buffers 4440064 bytes
Database mounted.

-- Easiest way is to flashback to a RESTORE POINT.
SQL> flashback database to restore point BEFORE_FLASHBACK;

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

-- Verify that table created after the restore point is no longer there.
SQL> select * from tab where tname='A';

no rows selected
</pre>

* The '''V$FLASH_RECOVERY_AREA_USAGE''' view shows what kind of files are available and its percent of the space usage in the Flash Recovery Area.
<pre>
SQL> select * from V$FLASH_RECOVERY_AREA_USAGE;

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE 0 0 0
ONLINELOG 0 0 0
ARCHIVELOG .02 .02 1
BACKUPPIECE 68.98 1.02 10
IMAGECOPY 0 0 0
FLASHBACKLOG .95 0 3
</pre>
To determine the actual space usage, simply query the view '''V$RECOVERY_FILE_DEST'''.
<pre>
SQL> select * from V$RECOVERY_FILE_DEST;

NAME
----------------------------------------------------------
SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
----------- ---------- ----------------- ---------------
+DG2
2147483648 1502122496 22201856 14
</pre>

@@ Line 158: / Line 158: @@
 ** ''v$flashback_database_stat'' - Monitor logging
 ** ''v$flashback_database_log'' - Monitor flashback retention target
 == See also ==

@@ Line 1: / Line 1: @@
 == What is Flashback Database? ==
-return a database to its state at a time in the recent past. Flashback Database allows to return the database to its state in the past. It is much faster than the RMAN's point-in-time recovery because it does not require restoring datafiles from backup and it requires applying fewer changes from the archived redo logs.
+The Flashback Database allows to return the database to its state in the past. It is much faster than the RMAN's point-in-time recovery because it does not require restoring datafiles from backup and it requires applying fewer changes from the archived redo logs.
 == Configuration ==

← Older revision		Revision as of 09:57, 7 March 2011
Line 154:		Line 154:
	2147483648 1502122496 22201856 14		2147483648 1502122496 22201856 14
	</pre>		</pre>
		+
		+	* Other views
		+	** ''v$flashback_database_stat'' - Monitor logging
		+	** ''v$flashback_database_log'' - Monitor flashback retention target
		+	** ''v$flashback_database_log'' - Estimate flashback size
		+
		+	== See also ==
		+	* http://www.oracle-base.com/articles/10g/Flashback10g.php#flashback_database
		+
		+	[[Category:Oracle_10g]]
		+	[[Category:Oracle_11g]]

@@ Line 3: / Line 3: @@
 == Configuration ==
-* Verify that the '''db_recovery_file_dest''' is set. This is the location of the flashback recovery area where the flash recovery files will be stored. Verify that there is sufficient space in the '''db_recovery_file_dest_size''' as well. Lastly, set an appropriate retention time of '''db_flashback_retention_target''' in minutes for flashback files.
+* Verify that the '''db_recovery_file_dest''' is set. This is the location of the flashback recovery area where the flash recovery files will be stored. Verify that there is sufficient space in the '''db_recovery_file_dest_size''' as well. Lastly, set an appropriate retention time (in minutes) in the '''db_flashback_retention_target'''.
 <pre>
 SQL> show parameter db_recovery_file
@@ Line 22: / Line 22: @@
 [Sample here is for RAC database.]
 <pre>
--- Execute this on one of the nodes
+-- Execute this on one of the nodes.
 SQL> alter system set cluster_database=false scope=spfile;
@@ Line 30: / Line 30: @@
 $ srvctl stop database -d DB11G
--- Startup MOUNT
+-- Startup in MOUNT mode.
 SQL> startup mount
 ORACLE instance started.
@@ Line 105: / Line 105: @@
 $ srvctl stop database -d DB11G
-SYS@PMDW1 AS SYSDBA> startup mount
+SQL> startup mount
 ORACLE instance started.
@@ Line 115: / Line 115: @@
 Database mounted.
--- Easiest way is to flashback to a RESTORE POINT.
+-- Let's flashback to a RESTORE POINT created earlier.
 SQL> flashback database to restore point BEFORE_FLASHBACK;
@@ Line 124: / Line 124: @@
 Database altered.
--- Verify that table created after the restore point is no longer there.
+-- Verify that the table created after the restore point is no longer there.
 SQL> select * from tab where tname='A';

@@ Line 130: / Line 130: @@
 </pre>
-* The '''V$FLASH_RECOVERY_AREA_USAGE''' view shows what kind of files are available and its percent of the space usage in the Flash Recovery Area.
+* The '''V$FLASH_RECOVERY_AREA_USAGE''' view shows what types of files are and their percentages of the space usage in the Flash Recovery Area.
 <pre>
 SQL> select * from V$FLASH_RECOVERY_AREA_USAGE;