Flashback-Database

How to Configure and use Flashback Database
Flashback database is useful feature introduced with 10g that allows the database to be effictively rewound to a previous point in time.
This feature is particularly useful for test, V&P, and development environments where you may wish to try out a new piece of code or fuctionality over and over, refining as you go. Rather than having to restore the database after each test, flashback database allows the database to be rewound very quickly to a previous point in time.
Flashback database can be effectively run in two modes. One mode where you log every change to every block, which allows you to flahsback the database to any previous point in time. Or another mode, where Oracle only tracks the "before" images of changed blocks, so that you can flashback to the start of the restore point.

How to Set-up Flashback Database

1. Ensure db_recovery_file_dest is set.
  • sqlplus '/ as sysdba'
  • SQL> alter system set db_recovery_file_dest='+' SCOPE=spfile;
2. Ensure db_recovery_file_dest_size is set
  • SQL> alter system set db_recovery_file_dest_size=100G SCOPE=spfile;
3. Stop and start the database
  • sqlplus '/ as sysdba'
  • SQL> shutdown immediate;
  • SQL> startup mount;
  • If flashback to any previous point in time is required, then turn flashback on using the following command
    • SQL> alter database flashback on;
  • SQL> alter database open;
  • SQL> alter system set db_flashback_retention_target=2880;
NOTES
  • Set the db_recovery_file_dest to an appropriate location for the flashback recovery files.
  • Set the db_recovery_file_dest_size to an appropriate size for the amount and size of the testing required.
  • Set the db_flashback_retention_target to an appropriate time, in mins, to retain flashbackability.
  • Only run alter database flashback on; if there is a requirement to flashback to ANY previous point in time.

Determine if Flashback Database is Already Enabled

1. Run the following commands to determing Flashback is turned on.
  • sqlplus '/ as sysdba'
  • SQL> select flashback_on from v$database;

Creating and Using Flashback Restore points.

This worked example assumes the database is using ASM to manage its storage.

Createing a Restore point

Create a restore point whenever the database is at a state that it may needed to be flashed back to. Use the optional GUARANTEE FLASHBACK DATABASE clause to ensure that the restore point is not aged out of the flashback recovery area (FRA) as dictated by the db_flashback_retention_target parameter.
1. You may want to create the restore point in mount mode. If so, put the database into mount mode now.
2. Create a restore point
  • sqlplus '/ as sysdba'
  • SQL> create restore point [GUARANTEE FLASHBACK DATABASE];

Rolling Back to a Restore Point

1. Identify the Restore point
  • sqlplus '/ as sysdba'
  • SQL> select name, time,guarantee_flashback_databse from v$restore_point;
  • SQL> quit
2. For a non RAC environment use the following commands to flashback to a restore point.
  • sqlplus '/ as sysdba'
  • SQL> shutdown immediate;
  • SQL> startup mount;
  • SQL> flashback database to restore point ;
  • SQL> alter database open resetlogs;
3. For RAC instances use the following commands.
  • One one of the nodes run, srvctl stop database -d -o immediate
  • sqlplus '/ as sysdba'
  • SQL> startup mount;
  • SQL> flashback database to restore point ;
  • SQL> alter database open resetlogs;
  • SQL> shutdown immediate;
  • SQL> quit
  • srvctl start database -d
  • Run crs_stat -t to confirm that the database is backup okay.
NOTES
  • Any tables created and updated without the LOGGING option will be suseptable to block curruption errors when the database is flashed back. These can be remedied by issuing the TRUNCATE TABLE command against the affected object(s).

Dropping a Restore Point

1. Restore points can be dropped with the database open using the following commands
  • sqlplus '/ as sysdba'
  • SQL> drop restore poijnt ;
  • SQL> quit

Monitoring Flashback Logging

After enabling flashback logging, Oracle keeps track of the amount of logging generated. This can be queried from v$flashback_database_log, the estimate gets better with age. Note that this is the size of the flashback logs only and does not include space used by archive logs and RMAN backups.
1. Monitor flashback logs
  • sqlplus '/ as sysdba'
  • SQL> select estimated_flashback_size/1024/1024/1024 "EST_FLASHBACK_SIZE(GB)" from v$flashback_database_log;
  • SQL> quit

Finding the Earliest Flashback Point

Querying V$flashback_database_log will show you the earliest point you can flashback your database to based on the size of the FRA and the currently available flashback logs.
1. Find the earliest flashback point
  • sqlplus '/ as sysdba'
  • SQL> alter session set nls_date_format='dd/mm/yy hh24:mi:ss';
  • SQL> select oldest_flashback_scn,oldest_flashback_time from v$flashback_database_log;
  • SQL> quit

Disabling Flashback Database

Full any previous point in time flashback can be disabled with the database open. Any unused Flashback logs will be automatically removed at this point and a message detailing the file deletion written to the alert log.
1. Disabling flashback
  • sqlplus '/ as sysdba'
  • SQL> ALTER DATABASE FLASHBACK OFF;
  • SQL> quit

Troubleshooting

The following common errors can occur.

Message

ORA-38754: FLASHBACK DATABASE not started; required redo log is not available

Cause

Oracle needs to have the required archive logs in the archive destination at the time of flashback.

Solution

Use rman to restore the missing archive logs. The sequence and thread numbers are in the error message.
      rman target /
      run{
         allocate channel t1 device type 'sbt_tape';=
         FLASHBACK DATABASE TO RESTORE POINT ;=
         }
Now resume the flashback process from the RESETLOGS step.

Useful Scripts for Flashback

The following scripts can be used for flashing back a database automatically for a group of users. For example, a training environment.

No comments:

Post a Comment

Subscribe to: Posts (Atom)