FLASHBACK DATABASE

I've used Oracle's flashback technology to restore data, but I never realized that it was possible to flashback the objects as well.

Typically, I've taken an export of the database objects at the point where I want to restore them, do my testing, drop all the users, recreate them and import the dump file back in to "restore."

A colleague of mine sent me a link about GUARANTEE FLASHBACK DATABASE. Pretty cool.

Fired up the local instance and began.

SELECT log_mode, flashback_on 
FROM v$database;

LOG_MODE     FLASHBACK_ON
------------ ------------------
ARCHIVELOG   YES

OK, I cheated and set this all up. My sandbox is typically set in NOARCHIVELOG mode and I had to figure out how to turn flashback on.

Now to create a restore point.

CREATE RESTORE POINT my_restore_point;

There are 2 types of restore points, Normal (the default) and Guarantee. Normal will age out of the control file after a set amount of time. Guarantee must be explicitly dropped. Oracle has the capability to store thousands of restore points.

So I mocked up a small example:

SYS@ELEVEN>SYS@ELEVEN>SYS@ELEVEN>DESC CJUSTICE.DEPT
 Name                       Null?    Type
 -------------------------- -------- --------------------- 
 DEPTNO                     NOT NULL NUMBER(2)
 DNAME                               VARCHAR2(14)
 LOC                                 VARCHAR2(13)

SYS@ELEVEN>ALTER TABLE CJUSTICE.DEPT ADD ( SOME_COLUMN NUMBER );

Table altered.

Elapsed: 00:00:00.87

SYS@ELEVEN>DESC CJUSTICE.DEPT

 Name                       Null?    Type
 -------------------------- -------- ---------------
 DEPTNO                     NOT NULL NUMBER(2)
 DNAME                               VARCHAR2(14)
 LOC                                 VARCHAR2(13)
 SOME_COLUMN                         NUMBER

SYS@ELEVEN>ALTER DATABASE CLOSE;

Database altered.

Elapsed: 00:00:05.57

SYS@ELEVEN>FLASHBACK DATABASE TO RESTORE POINT my_restore_point;

Flashback complete.

Elapsed: 00:00:05.84

SYS@ELEVEN>SHUTDOWN;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SYS@ELEVEN>STARTUP;
ORACLE instance started.
Total System Global Area  535662592 bytes
Fixed Size                  1334380 bytes
Variable Size             327156628 bytes
Database Buffers          201326592 bytes
Redo Buffers                5844992 bytes
Database mounted.

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SYS@ELEVEN>ALTER DATABASE OPEN RESETLOGS;

Database altered.

Elapsed: 00:00:25.18
SYS@ELEVEN>DESC CJUSTICE.DEPT

 Name                     Null?    Type
 ------------------------ -------- -------------------------------
 DEPTNO                   NOT NULL NUMBER(2)
 DNAME                             VARCHAR2(14)
 LOC                               VARCHAR2(13)

Voila!

Pretty amazing me thinks.

I still don't know if you can do this without shutting down the database. But it suits my purposes for the time being.

I'm constantly amazed at how much I don't know about Oracle.