 |
|
Disabling Flashback Database
Oracle Tips by Burleson |
Flashback Standby Database
If you have multiple standby sites, you may
utilize the DELAY option in Data Guard to prevent physical/logical
corruption or user errors in your primary database.
For example, the first scenario in the diagram
below has only one standby database. Here, a logical or physical
corruption in the primary database will cause an immediate
corruption in the standby database.
To avoid such a pitfall, you can implement a
second standby database with the ‘Delay’ option (introducing a delay
of minutes or hours on the second standby database for applying
archive log changes). This will prevent the corruptions on the
second standby database and allow recovery from a possible
physical/logical corruption or user errors in the primary database.
You can issue the following command to
accomplish this:
SQL> alter
database recover managed standby database delay 60 disconnect;
Figure 12.3 Using Flashback Database in a Standby Database
Configuration
However, in Oracle 10g, you can configure the
standby database with Flashback Database to achieve the same benefit
as the DELAY option. Therefore, there is no need to implement a
second standby database with the DELAY option.
Oracle 10g Disabling Flashback Database
Issue the following command to disable Flashback
Database:
SQL> ALTER
DATABASE FLASHBACK OFF;
You can also perform the same task in
Enterprise Manager:
Figure 12.2 Disabling Flashback Database in Enterprise Manager
Monitoring Flashback Database
The following displays a selection of the
Flashback Database logs:
SQL> select
begin_time, flashback_data,
2 db_data, redo_data, ESTIMATED_FLASHBACK_SIZE
3 from v$flashback_database_stat;
BEGIN_TIME
FLASHBACK_DATA DB_DATA REDO_DATA ESTIMATED_FLASHBACK_SIZE
-------------------- -------------- ---------- ----------
------------------------
Oct 08 2003
14:17:34 753664 5324800
970752
0
Oct 08 2003
13:17:32 1720320 4751360 3124224
21749760
Oct 08 2003
12:17:31 1802240 4833280 3168256
21774336
Oct 08 2003
11
Oct 07 2003 16:46:24 1851392 4603904
3120128 21577728
Oct 07 2003 15:46:21 1720320 4816896
3154944 21577728
Oct 07 2003 14:46:18 1736704 4587520
3196928 21577728
Oct 07 2003 13:46:16 1736704 4685824 3194880
21602304
25 rows
selected.
You can use the v$flashback_database_log to
monitor the Flashback Database retention target.
SQL> select
*
2 from v$flashback_database_log;
OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TIME RETENTION_TARGET
FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- --------------------- ----------------
-------------- ------------------------
2.2029E+12 Oct 06 2003 09:44:42 1440
48316416 21774336
Note: The default flashback retention time is
1400 minutes. See Chapter 8 for more details on how to set up this
value.
You can also use the v$flashback_database_log
view to assist in adjusting the recovery area disk quota:
SQL> select
estimated_flashback_size
2 from v$flashback_database_log;
ESTIMATED_FLASHBACK_SIZE
------------------------
21823488
Example 1: Flashback a Database using RMAN
RMAN>
FLASHBACK DATABASE
2> TO TIME = TO_DATE
3> (‘06/25/03 12:00:00’,’MM/DD/YY HH:MI:SS’);
Example 2: Flashback a database using SQL command
The database must be in mount state to issue
these commands:
SQL>
FLASHBACK DATABASE TO TIMESTAMP (SYSDATE – 5/24);
SQL> FLASHBACK DATABASE TO SCN 76239;
You must issue the following command
afterwards:
SQL> ALTER
DATABASE RESETLOGS;
|
