Restore points

Below You may find a 'session' from enabling restore point technology.

-- first try old a hacker method - just start do it ;-)
SQL> create restore point rp1 guarantee flashback database;
create restore point rp1 guarantee flashback database
*
ERROR at line 1:
ORA-38784: Cannot create restore point 'RP1'.
ORA-38785: Media recovery must be enabled for guaranteed restore point.
-------------------------
-- thus I see one needs to set db into ARCHIVELOG mode
-- despite that we need also settings for db_recovery_file_dest
-- though we do not need to enable flashback mode

-- creating pfile in order to edit settings
SQL> create pfile from spfile;

File created.

-- create directory for fast recovery area (FRA)
SQL> !mkdir [path]

-- add settings for FRA
SQL> !vim $ORACLE_HOME/dbs/initcdw.ora
--*.db_recovery_file_dest_size=10G
--*.db_recovery_file_dest=[path]'
SQL> create spfile from pfile;

File created.

-- now enabling ARCHIVELOG mode
SQL> startup nomount

ORACLE instance started.

Total System Global Area 2137886720 bytes
Fixed Size      2228200 bytes
Variable Size    503316504 bytes
Database Buffers  1627389952 bytes
Redo Buffers      4952064 bytes

-- create directory for archive logs
SQL> !mkdir /ora_csdb/oradata/CDW/arch

-- set local destination for archive logs to newly created directory
-- of course it was possible to add it to pfile when editing
-- in case of not setting a custom location the archive logs are stored
-- in $ORACLE_HOME/dbs
SQL> alter system set 
SQL> log_archive_dest_1= 'LOCATION=/ora_csdb/oradata/CDW/arch' 
SQL> ;

System altered.

-- switching to ARCHIVELOG mode
SQL> alter database mount;

Database altered.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

-- there is no switch of flashback on - so as You see this is quite minimal scenario
-- enabling flashback is not required but at a price - one cannot restore to
-- point in time between snapshots
-- as You may see there is special state for such situation provided 
-- in the FLASHBACK_ON column of V$DATABASE
SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
RESTORE POINT ONLY


-- recovery writer RVWR is started
-- as You may see, the technology makes use of flashback fascilites

-- creating a restore point
-- as You see now all works properly 
SQL> create  restore point rp1 guarantee flashback database;
[..alert.log..]
Wed Jun 10 12:44:47 2015
Starting background process RVWR
Wed Jun 10 12:44:47 2015
RVWR started with pid=28, OS id=17240 
Allocated 8388608 bytes in shared pool for flashback generation buffer
Wed Jun 10 12:45:27 2015
Created guaranteed restore point RP1
[..alert.log..]
Restore point created.

-- now preparations for a short test
SQL> 
SQL> conn / as sysdba
Connected.
SQL> alter user rems identified by simple1;

User altered.

SQL> conn rems/simple1
Connected.
SQL> create table t1 (a number)
  2  ;

Table created.

SQL> insert into t1 select object_id from dba_objects;

473175 rows created.

SQL> commit;

Commit complete.

-- now we return to the state marked by RP1
SQL> conn / as sysdba
Connected.

-- we need to stop the database and mount it
SQL> flashback database to restore point rp1;
flashback database to restore point rp1
*
ERROR at line 1:
ORA-38757: Database must be mounted and not open to FLASHBACK.

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

Total System Global Area 2137886720 bytes
Fixed Size      2228200 bytes
Variable Size    503316504 bytes
Database Buffers  1627389952 bytes
Redo Buffers      4952064 bytes
Database mounted.
SQL> flashback database to restore point rp1;
[..alert.log..]
Wed Jun 10 13:12:29 2015
Flashback Restore Start
Wed Jun 10 13:13:05 2015
Flashback Restore Complete
Flashback Media Recovery Start
 started logmerger process
Wed Jun 10 13:15:29 2015
db_recovery_file_dest_size of 10240 MB is 0.30% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Wed Jun 10 13:17:42 2015
Parallel Media Recovery started with 2 slaves
Wed Jun 10 13:17:43 2015
Recovery of Online Redo Log: Thread 1 Group 7 Seq 20393 Reading mem 0
  Mem# 0: /ora_csdb/oradata/CDW/onlinelog/o1_mf_7_97grvlnx_.log
  Mem# 1: /ora_csdb/oradata/CDW/onlinelog/o1_mf_7_97grvp66_.log
Incomplete Recovery applied until change 117062103544 time 06/10/2015 12:45:28
Flashback Media Recovery Complete
Wed Jun 10 13:20:53 2015
Completed: flashback database to restore point rp1
[..]
Flashback complete.

-- after flashback we have to open with RESETLOGS
SQL> alter database open
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;    


SQL> alter system set log_archive_format='cdw_%d_%r_%T_%S.arc';

-- %r, %T/%t, %S/%s have to be contained within the archive log format