Oracle RAC - Flashback Database

It was a simple enough request, flashback this particular database to a guaranteed restore point. What could possibly go wrong?

Database names etc have been changed to protect the innocent, and me!

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
$ sqlplus / as sysdba

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

SQL> startup mount
ORACLE instance started.
...
Database mounted.

SQL> flashback database
  2  to restore point GRP_2014_05_17_13_10;

However, this raised the following error:

1
2
ORA-38748: cannot flashback data file 1 - file is in use or recovery
ORA-01110: data file 1: '+DATA/XXXXXX/datafile/system.269.759338709'

A minor panic then ensued! It’s a production database, and I have limited downtime allocated!

In the back of my brain, I thought “maybe it’s a RAC database”?

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
SQL> show parameter cluster

NAME                       TYPE        VALUE
-------------------------------------- -----
cluster_database           boolean     TRUE
cluster_database_instances integer     2

SQL> show parameter instance_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_name                        string      XXXXXX_1

Ok, It is RAC, there are two instances, and I’m on instance XXXXXX_1. It is obviously still in use by the other instance which will be XXXXXX_2 using the naming conventions for this system. I need to bring it all down. The easiest way is to use srvctl:

1
SQL> exit

1
2
$ srvctl stop database -d XXXXXX -o immediate
$sqlplus / as sysdba

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
SQL> startup mount
ORACLE instance started.
...
Database mounted.

SQL> flashback database to restore point GRP_2014_05_17_13_10;
Flashback complete.

SQL> alter database open resetlogs;
Database altered.

All I need to do now is start the other instance:

1
$ srvctl start instance -d XXXXXX -i XXXXXX_2

Job done, and still within the downtime!

And here’s a quick tip. Because the database is effectively right back at 17th May 2014, the guaranteed restore point and its numerous archived and flashback logs, which are taking up space in the FRA, can be dropped and recreated to save FRA space:

1
2
3
4
5
SQL> drop restore point GRP_2014_05_17_13_10;
Restore point dropped.

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