Restore Point Prerequisites change in 11.2

For 10.2 and 11.1 to enable guarantee restore points following were prerequisites, from Oracle Documentation (Database Backup and Recovery User Guide)

To support the use of guaranteed restore points, the database must satisfy the
following prerequisites:
The COMPATIBLE initialization parameter must be set to 10.2 or greater.

The database must be running in ARCHIVELOG mode. To rewind your database to a guaranteed restore point, the FLASHBACK DATABASE command requires the use of archived redo logs starting from around the time of the restore point.

A flash recovery area must be configured, Guaranteed restore points use a mechanism similar to flashback logging. As with flashback logging, Oracle Database must store the required logs in the flash recovery area.

If Flashback Database is not enabled, then the database must be mounted, not open, when creating the first guaranteed restore point (or if all previously created guaranteed restore points have been dropped).

If a restore point command is issued on 11.1 in the open mode or without first creating a restore point in the mount mode following error would be thrown

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE='YES';

no rows selected

SQL> select open_mode from v$database;

OPEN_MODE
----------
READ WRITE

SQL> create restore point first_restore guarantee flashback database;
create restore point first_restore guarantee flashback database
*
ERROR at line 1:
ORA-38784: Cannot create restore point 'FIRST_RESTORE'.
ORA-38787: Creating the first guaranteed restore point requires mount mode when
flashback database is off.

But the prerequisites has changed in 11.2 now the only pre-req is (as per Database Backup and Recovery User Guide) To use guaranteed restore points, the database must satisfy the following additional prerequisite: the COMPATIBLE initialization parameter must be set to 10.2.0 or greater and that's all.

Same steps above done on a 11.2 db

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE='YES';

no rows selected

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

SQL> create restore point first_restore guarantee flashback database;

Restore point created.

SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE='YES';

NAME                        SCN TIME                           DATABASE_INCARNATION# GUA STORAGE_SIZE
-------------------- ---------- ------------------------------ --------------------- --- ------------
FIRST_RESTORE          17213755 09-JUN-11 12.05.26.000000000                       2 YES     15941632

On 11.2 it is possible to create restore points without first having the database in a mount mode (when flashback is off)

Update 17 September 2012
When a restore point is created with flashback is not enabled the recovery writer processes (RVWR) get started. Following could be seen on the alert log

Mon Sep 17 11:19:52 2012
Starting background process RVWR
Mon Sep 17 11:19:52 2012
RVWR started with pid=41, OS id=32173
Allocated 15937344 bytes in shared pool for flashback generation buffer
Created guaranteed restore point FIRST_RESTORE

and monitoring the oracle processes the newly started RVWR could be seen.

ps ax | grep ora_
...
32162 ?        Ss     0:00 ora_q000_ent11g2
32164 ?        Ss     0:00 ora_q001_ent11g2
32173 ?        Ss     0:00 ora_rvwr_ent11g2

Also the flashback_on will be set to "restore point only". Before restore point creation on 11gR2

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

After restore point creation on 11gR2

SQL> select flashback_on from v$database;

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

ORA 03137 TTC protocol internal error

Some useful metalink notes for ora-03137.

Intermittant Ora-03137, Ora-03106, Ora-03124 When Forking Across Connections (Doc ID 859472.1)

ora-600 [12333] / ora-3137 [12333] Troubleshooting (Doc ID 828123.1)

ORA-03137: TTC Protocol Internal Error : [12333] Using JDBC Driver (Doc ID 752297.1)

Bug 8625762 - ORA-3137 [12333] due to bind data not read from wire (Doc ID 8625762.8)

Effects Of Sequence Aging Out Of Shared Pool

Like any other database object in shared pool sequence could also be aged out or flushed out of the shared pool to make room for other objects. When this happens values return by the sequence may not be in the expected order.

SQL> create sequence mseq ;

SQL> select * from user_sequences;
SEQUENCE_NAME    MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
--------------- ---------- ---------- ------------ - - ---------- -----------
MSEQ                     1 1.0000E+28            1 N N         20           1

Take the first value from the sequence and examine the last number

SQL> select mseq.nextval from dual;

   NEXTVAL
----------
         1

SQL> select * from user_sequences;

SEQUENCE_NAME    MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
--------------- ---------- ---------- ------------ - - ---------- -----------
MSEQ                     1 1.0000E+28            1 N N         20          21

At this stage if the sequence is aged out of the shared pool (in this test case by flushing the shared pool) the next value returned would be 21 instead of 2. As sysdba run

SQL> alter system flush shared_pool;

and then as owner of the sequence

SQL> select mseq.nextval from dual;

   NEXTVAL
----------
        21

SQL> select * from user_sequences;

SEQUENCE_NAME    MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
--------------- ---------- ---------- ------------ - - ---------- -----------
MSEQ                     1 1.0000E+28            1 N N         20          41

This creates gaps in the values returned by the sequences and it only affects the cached sequences and nocache, order sequences are not affected

SQL> create sequence mseq nocache order;

SQL> select mseq.nextval from dual;

   NEXTVAL
----------
         1

SQL> select * from user_sequences;

SEQUENCE_NAME    MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
--------------- ---------- ---------- ------------ - - ---------- -----------
MSEQ                     1 1.0000E+28            1 N Y          0           2

Flush the shared pool

SQL> alter system flush shared_pool;

Sequence returns the next value 2 and flushing shared pool has no effect

SQL> select * from user_sequences;

SEQUENCE_NAME    MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
--------------- ---------- ---------- ------------ - - ---------- -----------
MSEQ                     1 1.0000E+28            1 N Y          0           2

SQL> select mseq.nextval from dual;

   NEXTVAL
----------
         2

SQL>  select * from user_sequences;

SEQUENCE_NAME    MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
--------------- ---------- ---------- ------------ - - ---------- -----------
MSEQ                     1 1.0000E+28            1 N Y          0           3

One way to prevent is to pin the sequence in the shared pool

SQL> create sequence mseq ;

SQL> select mseq.nextval from dual;

   NEXTVAL
----------
         1

SQL> select * from user_sequences;

SEQUENCE_NAME    MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
--------------- ---------- ---------- ------------ - - ---------- -----------
MSEQ                     1 1.0000E+28            1 N N         20          21

Pin the sequence with

SQL> exec dbms_shared_pool.keep('ASANGA.MSEQ','Q');

and flush the shared pool

SQL> alter system flush shared_pool;

Ordered values are returned from the sequence without a gap

SQL> select mseq.nextval from dual;

   NEXTVAL
----------
         2

SQL> /

   NEXTVAL
----------
         3

SQL> select * from user_sequences;

SEQUENCE_NAME    MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
--------------- ---------- ---------- ------------ - - ---------- -----------
MSEQ                     1 1.0000E+28            1 N N         20          21