All posts, Backup, Oracle by Team Ajara

Flashback RAC database to restore point

Flashback RAC database to restore point.

While doing flashback RAC DB we will start one instance in mount and another instance will be in shutdown state.

High Level Steps

1)select  the incarnation
2)verify Instances
3)select restore point details
4)Verify DB status and stop the DB
5)Start one instance in mount mode
6)flashback database to restore point
7)Open DB with resetlogs
8)Stop the database
9)Start the DB on all Instances
10)check DB incarnation from SQL and RMAN

Steps in detail

Step 1 : check the database incarnation.

set pages 1000 lines 120
select INCARNATION# INC#, RESETLOGS_CHANGE # RS_CHANGE#, RESETLOGS_TIME, PRIOR_RESETLOGS_CHANGE# PRIOR_RS_CHANGE#, STATUS, FLASHBACK_DATABASE_ALLOWED FB_OK from v$database_incarnation;
INC# RS_CHANGE# RESETLOGS PRIOR_RS_CHANGE# STATUS FB_OK
---- ---------- --------- ---------------- ------- -----
1            1  07-JUL-16                0 PARENT  NO
2      1694147  01-JUL-20                1 CURRENT NO

Step 2: Verify the instances 
$ps -ef |grep pmon

Step 3: Restorepoint Details

SQL> set linesize 300
col time for a35
set numwidth 30
col name for a30
col RESTORE POINT TIME for a25
select SCN, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE, TIME, PRESERVED, NAME from v$restore_point; 

SCN              GUA STORAGE_SIZE  TIME                            PRE NAME
----            ---- ------------- ----                           --------
675780849        YES 3774873600    23-AUG-21 06.47.48.000000000 AM YES BEFORE_UPGRADE

Step 4:check the DB status

$srvctl status database -d ORDEV1 -v -f 
Instance ORDEV11 is running on node oratst01. Instance status: Open.
Instance ORDEV12 is running on node oratst02. Instance status: Open. 
$

Step 5: stop the DB 

$srvctl stop database -d ORDEV1 -o immediate 
$srvctl status database -d ORDEV1 -v -f 
Instance ORDEV11 is not running on node oratst01 
Instance ORDEV12 is not running on node oratst02 
$

Step 6: Start once instance in mount state using srvctl

$srvctl start instance -d ORDEV1 -i ORDEV11 -o mount 

$srvctl status database -d ORDEV1 -v -f 
Instance ORDEV11 is running on node oratst01. Instance status: Mounted (Closed). 
Instance ORDEV12 is not running on node oratst02
$

Step 7: Flashback to restore point using sqlplus

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

Step 8: Select resetlogs and open Database with resetlogs
SQL> select open Resetlogs from gv$database;
OPEN RESETL
-----------
ALLOWED
SQL>
SQL> alter database open resetlogs;
Database altered.

Step 9: Stop the database using srvctl.

$srvctl stop database -d ORDEV1 -o immediate 
$srvctl status database -d ORDEV1 -v -f 
Instance ORDEV11 is not running on node oratst01 
Instance ORDEV12 is not running on node oratst02 
$

Step 10: start the database and make sure all instances are up, using srvctl.

$srvctl start database -d ORDEV1


$srvctl status database -d ORDEV1 -v -f 
Instance ORDEV11 is running on node oratst01. Instance status: Open. 
Instance ORDEV12 is running on node oratst02. Instance status: Open.
$ 

Step 11: check the database incarnation from SQL.

SQL> set pages 1000 lines 120
select INCARNATION# INC#, RESETLOGS_CHANGE# RS_CHANGE#, RESETLOGS_TIME, PRIOR_RESETLOGS_CHANGE# PRIOR_RS_CHANGE#, STATUS,
FLASHBACK DATABASE ALLOWED FB OK from v$database incarnation; SQL> 2 3
INC# RS_CHANGE# RESETLOGS PRIOR_RS_CHANGE# STATUS FB_OK
1            1  07-JUL-16                0 PARENT  NO
2      1694147  01-JUL-20                1 CURRENT NO
3    675780843  23-AUG-21          1694147 CURRENT YES 

Step 12: Verify Incarnation from RMAN

RMAN> list incarnation;
using target database control file instead of recovery catalog
List of Database Incarnations
DB Key Inc Key DB Name DB ID     STATUS  Reset SCN  Reset Time
1            1 ORDEV1 8167706219 PARENT  1          07-JUL-16
2            2 ORDEV1 8167706219 PARENT  1694147    01-JUL-20
3            3 ORDEV1 8167706219 CURRENT 675780843  23-AUG-21

See also