Oracle 12c provides the ability to flashback the database to a previous state. This procedures demonstrates the use of this process to flashback the database 15 minutes. NOTE: Four states must exist for flashback to work:
- Must be in archivelog mode.
- Database must have flashback on.
- Control files must contain flashback logs, thus not recreated.
- No tablespaces exist with flashback off set.
- Logon to your Oracle database server as the Oracle software owner and logon to SQLPLUS.
[root@linux2 etc]# su – oracle
Last login: Thu Jul 1 09:25:23 EST 2015 on pts/1
[oracle@linux2 ~]$
[oracle@linux2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Thu Jul 1 13:47:01 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL>
- Determine SCN of database 15 min ago by using the timestamp_to_scn().
SQL> select timestamp_to_scn(sysdate-1/96) from dual;
TIMESTAMP_TO_SCN(SYSDATE-1/96)
——————————
5067892
SQL>
- Now using SCN extracted by the previous step, shutdown the database, startup in mount mode and execute the flashback command.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 734003200 bytes
Fixed Size 2928728 bytes
Variable Size 524292008 bytes
Database Buffers 201326592 bytes
Redo Buffers 5455872 bytes
Database mounted.
SQL> FLASHBACK DATABASE TO SCN 5067892;
Flashback complete.
SQL>
- Startup the database for normal use with resetlogs option and see that all transactions of the last 15 minutes are gone.
SQL> alter database open resetlogs;
Database altered.
SQL>
- NOTE: You should take a full backup of the database as soon after flashback as possible to ensure future recovery.
- This completes the use of Flashback in Oracle 12c.
Larry Catt
OCP