Flashback database can be used to reverse most unwanted changes to a database if the database files are intact. You are able to rewind a database to a previous time. But the rewind of a database can be guaranteed or not. If not, which is the default, the possible rewind ‘window’ is based on the value of the parameter db_flashback_retention_target. The parameter is in minutes and defaults to 1 day.
We focus on the guarantee part of flashback database in an Cluster environment.
To enable flashback database execute the following steps:
SQL> select flashback_on from v$database;
FLASHBACK_ON
——————
NO
SQL> alter database flashback on;
Database altered.
SQL> select flashback_on from v$database;
FLASHBACK_ON
——————
YES
In the alert log you can see when flashback database is enabled.
2011-06-03 10:14:17.770000 +02:00
Flashback Database Enabled at SCN 1468785
Flashback logs are now created by the background process RVWR as a result of the enabling and are stored in the fast recovery area. The flashback logs will be used if a rewind is required.
Now everything is ready to be used during change processes. Let’s assume our change will implement the user scott with the objects, but at the end something goes wrong and we need to rewind the change.
In the change process the first action we do is to create a ‘guarantee restore point’. This will make sure, as the name indicates, we can always return to this point in time. If disk space becomes a problem, the database instance will stop processing.
SQL> create restore point start_change_3june guarantee flashback database;
SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE,SCN from v$restore_point;
NAME GUA SCN
——————– — ———-
START_CHANGE_3JUNE YES 1476588
SQL> select count(*) from dummy.xyz;
no rows selected
create the table insert the rows;
select count(*) from dummy.xyz;
count(*) - 1
Now let’s assume the change is unsuccessful, this would mean without flashback database we need to have a script to undo all the modifications or restore and recover the database just before the time of the particular database change. But Instead of these options we perform a flashback database to the restore point.
SQL> flashback database to restore point START_CHANGE_3JUNE;
flashback database to restore point START_CHANGE_3JUNE
*
ERROR at line 1:
ORA-38757: Database must be mounted and not open to FLASHBACK.
First lets mount the database as recovery is required, in RAC to recover an instance only 1 instance can be in mount state, the other instances needs to be down.
SQL> !srvctl stop database -d rac
SQL> !srvctl start instance -d rac -i RAC1 -o mount
SQL> connect / as sysdba
Connected.
SQL> flashback database to restore point START_CHANGE_3JUNE;
Flashback complete.
SQL> alter database open resetlogs;
SQL> !srvctl start instance -d RAC -i RAC2
SQL> !srvctl status database -d rac
Instance RAC1 is running on node server1
Instance RAC2 is running on node server2
SQL> select count(*) from dummy.xyz;
no rows selected
Now the change is rolled back we still need to drop the ‘guarantee restore point’. This step is also required when the change is successful, do not forget this step as all the flashback logs will be kept until the restore point is dropped!
SQL> drop restore point START_CHANGE_3JUNE;
Restore point dropped.
Above steps where executed in sqlplus, combined with srvctl, but RMAN can also be used to perform these steps. In thas case it looks like this:
RMAN> create restore point change_two_3june guarantee flashback database;
RMAN> list restore point all;
SCN RSP Time Type Time Name
—————- ——— ———- ——— —-
1483389 GUARANTEED 03-JUN-11 CHANGE_TWO_3JUNE
Now we perform a change, which is not successful, so we need to restore again to change_two_3june point in time.
RMAN> flashback database to restore point CHANGE_TWO_3JUNE;
Starting flashback at 03-JUN-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=62 instance=RAC1 device type=DISK
starting media recovery
media recovery failed
RMAN-00571:=================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ====
RMAN-00571:==================================================
RMAN-03002: failure of flashback command at 06/03/2011 11:44:04
ORA-38757: Database must be mounted and not open to FLASHBACK.
As you can see RMAN will try to perform the flashback, but also here we must have the database mounted on one instance.
[oracle@server1 ~]$ srvctl stop database -d rac
[oracle@server1 ~]$ srvctl start instance -d rac -i RAC1 -o mount
[oracle@server1 ~]$ srvctl status database -d rac
Instance RAC1 is running on node server1
Instance RAC2 is not running on node server2
[oracle@server1 ~]$ rman target /
Recovery Manager: Release 11.2.0.2.0 – Production on Fri Jun 3 11:47:24 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: RAC (DBID=2331183497, not open)
RMAN> flashback database to restore point CHANGE_TWO_3JUNE;
Starting flashback at 03-JUN-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 instance=RAC1 device type=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:03
Finished flashback at 03-JUN-11
RMAN> alter database open resetlogs;
database opened
RMAN> list restore point all;
SCN RSP Time Type Time Name
—————- ——— ———- ——— —-
1483389 GUARANTEED 03-JUN-11 CHANGE_TWO_3JUNE
RMAN> drop restore point CHANGE_TWO_3JUNE;
RMAN> exit
Recovery Manager complete.
[oracle@server1 ~]$ srvctl start instance -d rac -i RAC2
[oracle@server1 ~]$ srvctl status database -d rac
Instance RAC1 is running on node server1
Instance RAC2 is running on node server2
As you can see using RMAN the same steps can be executed. Now the last step is to start the other instance again.
No comments