RMAN Performing Flashback Recovery

Checking the Flashback Status of a Database
Problem You want to check whether your database is flashback enabled.

Solution

The data dictionary view V$DATABASE contains information about the flashback status of the
database. Check the column FLASHBACK_ON on that view to ascertain the flashback

status:

SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES

SQL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG

If the result of the query is different, as in the example shown here, then the database is
not running in archivelog mode:
SQL> select log_mode from v$database;
LOG_MODE
------------
NOARCHIVELOG

4. To enable archivelog mode, follow these steps:
a. Shut down the database by issuing the following SQL statement:

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
b. Start the database in mount mode by issuing the following SQL statement:

SQL> startup mount
ORACLE instance started.
Total System Global Area 289406976 bytes
Fixed Size 1248576 bytes
Variable Size 83886784 bytes
Database Buffers 197132288 bytes
Redo Buffers 7139328 bytes
Database mounted.

c. Enable archivelog mode by issuing the following command:

SQL> alter database archivelog;
Database altered.

d. At this point, you can open the database for business, but since your objective is to
enable flashback, go to the next step.

5. Make sure the database is in mounted state by issuing the following SQL statement:

SQL> select OPEN_MODE from v$database;
OPEN_MODE
----------
MOUNTED

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

SQL> startup mount
ORACLE instance started.
Total System Global Area 289406976 bytes
Fixed Size 1248576 bytes
Variable Size 83886784 bytes
Database Buffers 197132288 bytes
Redo Buffers 7139328 bytes
Database mounted.
The final line confirms that the database is now mounted.

7. Enable flashback for the database by issuing the following SQL statement:

SQL> alter database flashback on;

Database altered.

8. Open the database:

SQL> alter database open;
Database altered.

Disabling Flashback on a Database

SQL> alter database flashback off;
Database altered

Solution 1: Flashing Back to a Specific SCN

In this example, you will see how to flash back a database to a specific SCN, which is the most precise flashback procedure possible. Here are the steps to follow:
1. First, check the SCN of the database now. Connecting as sys or any other DBA account,issue the following SQL statement:


SQL> select current_scn
2 from v$database;
CURRENT_SCN
-----------
1137633

The output shows the current SCN is 1,137,633. You can flash back to an SCN prior to
this number only.

2. Execute the “common presteps” 1 through 4.

3. Flash the database back to your desired SCN. For instance, to flash back to SCN
1,050,951, issue the following RMAN command:

RMAN> flashback database to scn 1050951;

Solution 2: Flashing Back to a Specific Time

You want to flash the database to a specific time, not an SCN. Here are the steps to follow:

1. Follow common steps 1 through 4.
2. Use the following command to flash back to a time just two minutes ago:

RMAN> flashback database to time 'sysdate-2/60/24';

RMAN> flashback database to time "to_date('01/23/07 13:00:00','mm/dd/yyhh24:mi:ss')";

Solution 3: Flashing Back to a Restore Point

In this solution, you will learn how to flash back the database to a restore point. You can learn about creating restore points in recipe 13-9 and recipe 13-10. Here are the steps to follow to flash back to a restore point:

1. Follow “common presteps” 1 through 4.
2. To flash back to a restore point named rp6, issue the following SQL:

RMAN> flashback database to restore point rp6;

Flashing Back to Before the Last resetlogs Operation

RMAN> flashback database to before resetlogs;

Solution 1: Flashing Back to a Time

You have a specific time—such as January 21, 2007, at 10 p.m.—that you want to flash back to. This time must be in the past. Here are the steps to follow:

1. Perform the “common presteps” 1 through 3.
2. Flash the database to your desired time stamp by issuing the following SQL statement:

SQL> flashback database to timestamp
2> to_date('1/22/2007 00:00:00','mm/dd/yyyy hh24:mi:ss');

Finding Out How Far Back into the Past You Can Flash Back

SQL> select * from v$flashback_database_log;

Estimating the Amount of Flashback Logs Generated at Various Times
Problem You want to find out how much space the flashback logs are expected to consume in the database at various points of time.

Solution
The solution is rather simple. The Oracle database already has a view that shows the estimated database changes and flashback changes in a one-hour period. This view is
V$FLASHBACK_DATABASE_STAT. Here is a sample of how to use the view to identify how
much flashback and database changes are generated in hour-long intervals:

SQL> alter session set nls_date_format = 'mm/dd/yy hh24:mi:ss';
Session altered.

SQL> select * from v$flashback_database_stat
2 order by begin_time
3 /
BEGIN_TIME END_TIME FLASHBACK_DATA DB_DATA REDO_DATA➥
ESTIMATED_FLASHBACK_SIZE
----------------- ----------------- -------------- ---------- ----------➥
------------------------
01/25/07 21:53:08 01/25/07 22:59:40 27860992 33284096 21613056➥
194224128
01/25/07 22:59:40 01/25/07 23:16:56 2138112 2285568 749056➥
0
... and so on ...
The data of interest is the column ESTIMATED_FLASHBACK_SIZE, which shows the expected
flashback log generated in the time period shown by the columns BEGIN_TIME and END_TIME.

Using this view, you can see an hour-by-hour progress of the flashback data generation. Issue

the following query to find out the estimated total size of the flashback logs at the end of each
period:

SQL> select end_time, estimated_flashback_size
2 from v$flashback_database_stat
3 order by 1
4 /

Here is the output:
END_TIME ESTIMATED_FLASHBACK_SIZE
----------------- ------------------------
01/25/07 19:58:00 73786720
01/25/07 20:53:10 164890123
01/25/07 21:57:37 287563456
01/25/07 22:59:40 194224128

Creating Guaranteed Restore Points

Problem

You want to create guaranteed restore points to ensure that you can flash back to them as needed. You want to require the database to retain any needed logs to support those points.

Solution
Add the guarantee keyword to your create restore point command. For example:

SQL> create restore point rp2 guarantee flashback database;
Restore point created.

Listing Restore Points
Problem

You want to list the various restore points in the database and the information about them. Solution Query the view V$RESTORE_POINT. For example:

SQL> col time format a32
SQL> col name format a10
SQL> select * from v$restore_point
2 order by 2,1;

SCN DATABASE_INCARNATION# GUA STORAGE_SIZE TIME NAME
---------- --------------------- --- ------------ ------------------------- ----
1047095 2 NO 0 22-JAN-07 01.57.14.00 PM RP1
1049764 2 YES 4096000 22-JAN-07 03.40.55.00 PM RP2
1051267 2 YES 0 22-JAN-07 04.32.55.00 PM RP3
1051276 2 NO 0 22-JAN-07 04.33.05.00 PM RP4
1047289 3 NO 0 22-JAN-07 05.13.55.00 PM RP5
1047301 3 YES 3981312 22-JAN-07 05.14.17.00 PM RP6

Dropping Restore Points

Problem

You want to drop a specific restore point.

Solution

To drop a restore point named rp2, whether normal or guaranteed, simply execute the following
SQL statement:

SQL> drop restore point rp2;

Recovering a Dropped Table

SQL> show recyclebin

SQL> flashback table accounts to before drop;

Tip If you want to delete a table permanently, without sending it to the recycle bin, then use the purge clause in the drop statement. For example:

SQL> drop table accounts purge;
Table dropped.

The table is now completely dropped; similar to the pre-10g behavior, it does not go to the recycle bin.

Renaming the Reinstated Table

The alternative approach is safer because you do not need to drop anything. When you flash back a table to undrop it, you can optionally rename it. In this case, when you flash back the table ACCOUNTS, you want to reinstate it as NEW_ACCOUNTS.

SQL> flashback table accounts to before drop rename to new_accounts;

Solution

To reinstate a specific dropped table, follow the steps:
1. First find out the presence of these objects in the recycle bin:

SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJEC DROP TIME
------------- ------------------------------ ------ -------------------
ACCOUNTS BIN$VKGbC+r/Qjqg3avhlpBpqw==$0 TABLE 2007-01-23:00:46:50
ACCOUNTS BIN$bQ8QU1bWSD2Rc9uHevUkTw==$0 TABLE 2007-01-23:00:23:39


SQL> flashback table "BIN$bQ8QU1bWSD2Rc9uHevUkTw==$0" to before drop;

SQL> select * from user_recyclebin;

SQL> select * from dba_recyclebin;


Problem

You want to recover all the subordinate objects such as the indexes, constraints, and so on, of a table that has been undropped


SQL> select original_name, object_name, type, can_undrop
2 from user_recyclebin;

SQL> select index_name
2 from user_indexes
3 where table_name = 'ACCOUNTS';
INDEX_NAME
------------------------------
BIN$9POlL6gfQK6RBoOK4klc3Q==$0
BIN$PookVi5nRpmhmPaVOThGQQ==$0
BIN$fzY77+GmTzqz/3u4dqac9g==$0
6. Note the names, and compare them to the names you got in step 1. It’s not easy, but
you can make a clear connection. Using the output from step 1, rename the indexes:

SQL> alter index "BIN$9POlL6gfQK6RBoOK4klc3Q==$0" rename to IN_ACC_01;
Index altered.

SQL> alter index "BIN$PookVi5nRpmhmPaVOThGQQ==$0" rename to SYS_C005457;
Index altered.

SQL> alter index "BIN$fzY77+GmTzqz/3u4dqac9g==$0" rename to in_acc_02;

ORIGINAL_NAME OBJECT_NAME TYPE CAN
--------------- ------------------------------ ------- ---
IN_ACC_03 BIN$mc3jkDZRR42mswWBR0XPyA==$0 INDEX NO
IN_ACC_02 BIN$B8BgSIvWTweFyZv/y57GHg==$0 INDEX NO
IN_ACC_01 BIN$t8poR1f4SIKTOTpH2vYKSQ==$0 INDEX NO
TR_ACC_01 BIN$dt6tBSIWSn+F5epvjybKmw==$0 TRIGGER NO
ACCOUNTS BIN$laonDjIDS6macycHgAjP1Q==$0 TABLE YES

SQL> select trigger_name
2 from user_triggers;
TRIGGER_NAME
------------------------------
BIN$dt6tBSIWSn+F5epvjybKmw==$0

9. Rename triggers to their original names:

SQL> alter trigger "BIN$dt6tBSIWSn+F5epvjybKmw==$0" rename to tr_acc_01;
Trigger altered.


SQL> alter session set recyclebin = off;
Session altered.

After setting recyclebin to off, if you drop a table, the table is completely dropped:

SQL> drop table accounts;
Table dropped.

Now, if you check the recycle bin:

SQL> show recyclebin

the command returns no output, indicating that the recycle bin is empty.
You can turn off the recycle bin for the entire database by executing this:

SQL> alter system set recyclebin = off;
System altered.

Clearing the Recycle Bin

Problem
You want to remove all dropped objects from the recycle bin.

Solution
You can clean up the recycle bin using the purge statement, which clears the recycle bin of the
currently logged on user. For example:
SQL> purge recyclebin;
Recyclebin purged.

SQL> begin
2 purge recyclebin;
3 end;
4 /
purge recyclebin;

Flashing Back a Specific Table

SQL> select row_movement
2 from user_tables
3 where table_name = 'ACCOUNTS';
ROW_MOVE
--------
ENABLED

SQL> alter table accounts enable row movement;
Table altered.

This prepares the table for flashback.

SQL> flashback table accounts to timestamp to_date ('23-JAN-07 18.23.00','dd-MON-YY hh24.mi.ss');