Sometimes, varity business data need create restore point for differ performance test. So how to manange restore point and how to flashback, see the below ->

Restore Point

1
2
#Login sqlplus use sysdba
Sqlplus / as sysdba
1
2
3
4
5
6
7
8
9
10
11
12
--list all restore points:
SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE 
	FROM V$RESTORE_POINT;

-- also simple syntax
select scn, GUARANTEE_FLASHBACK_DATABASE,TIME,name from v$restore_point;

--drop old restore point:
DROP RESTORE POINT ICC_20160824;

--create new restore point:
CREATE RESTORE POINT ICC_20160830 GUARANTEE FLASHBACK DATABASE;

Flash Back

1
2
3
4
5
6
7
8
-- flashback.sql

spool flashback.log
startup mount;
flashback database to restore point "ICC_20161014"
alter database open resetlogs;
spool off
exit
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
#!/bin/bash
#
# file name: flashback.sh
# author: itabas
#

ORACLE_UNQNAME=perfcli
ORACLE_SID=perfcli2
echo stopping RAC database
srvctl stop database -d perfcli
srvctl status database -d perfcli
rm -f flashback.log
echo starting sqlplus
sqlplus "/ as sysdba" @flashback.sql
cat flashback.log
srvctl start instance -d perfcli -i "perfcli1"
srvctl status database -d perfcli
echo stopping  oracle services
srvctl stop service -d perfcli
echo starting oracle services
srvctl start service -d perfcli
srvctl status service -d perfcli

Of course, if your oracle is ASM cluster, maybe have many instances, just modify related instance script, will be all right.

other node example

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
#!/bin/bash
#
# file name: flashback.sh
# author: itabas
#

#Node1:
ORACLE_UNQNAME=perfcdb
ORACLE_SID=perfcdb1
echo `date`
echo stopping RAC database
srvctl stop database -d perfcdb
srvctl status database -d perfcdb
rm -f flashback.log
echo starting sqlplus
sqlplus "/ as sysdba" @flashback.sql
cat flashback.log
echo starting instance 2
srvctl start instance -d perfcdb -i "perfcdb2"
srvctl status database -d perfcdb
echo stopping  oracle services
srvctl stop service -d perfcdb
echo starting oracle services
srvctl start service -d perfcdb
srvctl status service -d perfcdb


#Node2:
ORACLE_UNQNAME=perfcdb
ORACLE_SID=perfcdb2
echo stopping RAC database
srvctl stop database -d perfcdb
srvctl status database -d perfcdb
rm -f flashback.log
echo starting sqlplus
sqlplus "/ as sysdba" @flashback.sql
cat flashback.log
srvctl start instance -d perfcdb -i "perfcdb1"
srvctl status database -d perfcdb
echo stopping  oracle services
srvctl stop service -d perfcdb
echo starting oracle services
srvctl start service -d perfcdb
srvctl status service -d perfcdb