[RESOLVED] Oracle Standard Edition 11.2.0.4.0 - ORA-00439: feature not enabled: Flashback Database - [Workaround] - no license violation

posted on 01 Mar 2017 by in Oracle, Oracle 11g

Before a few days, one of my colleagues performs the creation of a “restore point guarantee flashback database” without considering that flashback database feature is available only in Enterprise edition.

What is really annoying me is that the database accepts to create a “restore point guarantee flashback database” but you are unable to restore/flashback database. :(

Maybe there is a special reason for this or somewhere in the documentation exist cases where GRP (guarantee restore point) is necessary for Standard Edition.

( Please if you find anything related to that case – share with me )

Oracle information in regards to that flashback database feature

https://docs.oracle.com/cd/E11882_01/backup.112/e10642/flashdb.htm#BRADV601

In the above documentation, you cannot find any information for flashback database limitation or reference to the feature matrix.

Oracle Database Feature matrix:

As per the below, information flashback database is not supported In Standard Edition and Standard One Edition only is available in Enterprise Edition

But you are able to create restore point guarantee flashback database J

Feature/Option	SE1	SE	EE	Notes
Flashback Table	N	N	Y
Flashback Database	N	N	Y
Flashback Transaction	N	N	Y
Flashback Transaction Query	N	N	Y
Flashback Data Archive (Total Recall)	N	N	Y	Requires Oracle Advanced Compression option

Let’s Play:

Create one table and insert a row in order to validate the flashback database to restore point

SQL> create table DBABG(i int);

Table created.

SQL> insert into DBABG values(1);

1 row created.

SQL> commit;

Commit complete.

SQL>

Checking database version and products feature_usage

This is important in order to avoid license violation and additional issues.

PRODUCT VERSION STATUS

------------------------------ -------------------- --------------------

NLSRTL 11.2.0.4.0 Production

Oracle Database 11g 11.2.0.4.0 64bit Production

PL/SQL 11.2.0.4.0 Production

TNS for Linux: 11.2.0.4.0 Production

SQL> COLUMN name FORMAT A60

SQL> COLUMN detected_usages FORMAT 999999999999

SELECT u1.name,

u1.detected_usages,

u1.currently_used,

u1.version

FROM dba_feature_usage_statistics u1

WHERE u1.version = (SELECT MAX(u2.version)

FROM dba_feature_usage_statistics u2

WHERE u2.name = u1.name)

AND u1.detected_usages > 0

AND u1.dbid = (SELECT dbid FROM v$database)

ORDER BY name;

NAME DETECTED_USAGES CURRE VERSION

------------------------------------------------------------ --------------- ----- --------------------

Audit Options 1 TRUE 11.2.0.4.0

Automatic Maintenance - Optimizer Statistics Gathering 1 TRUE 11.2.0.4.0

Automatic Maintenance - Space Advisor 1 TRUE 11.2.0.4.0

Automatic SGA Tuning 1 TRUE 11.2.0.4.0

Automatic SQL Execution Memory 1 TRUE 11.2.0.4.0

Automatic Segment Space Management (system) 1 TRUE 11.2.0.4.0

Automatic Undo Management 1 TRUE 11.2.0.4.0

Character Set 1 TRUE 11.2.0.4.0

Deferred Segment Creation 1 TRUE 11.2.0.4.0

HeapCompression 1 TRUE 11.2.0.4.0

Locally Managed Tablespaces (system) 1 TRUE 11.2.0.4.0

Locally Managed Tablespaces (user) 1 TRUE 11.2.0.4.0

Oracle Java Virtual Machine (system) 1 TRUE 11.2.0.4.0

Partitioning (system) 1 TRUE 11.2.0.4.0

Recovery Area 1 TRUE 11.2.0.4.0

SecureFiles (system) 1 TRUE 11.2.0.4.0

SecureFiles (user) 1 TRUE 11.2.0.4.0

Server Parameter File 1 TRUE 11.2.0.4.0

Services 1 TRUE 11.2.0.4.0

Virtual Private Database (VPD) 1 TRUE 11.2.0.4.0

20 rows selected.

Create restore point ( keep in mind that the database should be in archive log mode )

SQL> create restore point DBABG_LAB_TEST GUARANTEE FLASHBACK DATABASE;

Restore point created.

Alert log

Wed Mar 01 06:33:19 2017

Allocated 3981120 bytes in shared pool for flashback generation buffer

Created guaranteed restore point DBABG_LAB_TEST

Wed Mar 01 06:33:53 2017

Checking if there are any statistics information in regards to flashback database

--- no J

NAME DETECTED_USAGES CURRE VERSION

------------------------------------------------------------ --------------- ----- --------------------

Audit Options 1 TRUE 11.2.0.4.0

Automatic Maintenance - Optimizer Statistics Gathering 1 TRUE 11.2.0.4.0

Automatic Maintenance - Space Advisor 1 TRUE 11.2.0.4.0

Automatic SGA Tuning 1 TRUE 11.2.0.4.0

Automatic SQL Execution Memory 1 TRUE 11.2.0.4.0

Automatic Segment Space Management (system) 1 TRUE 11.2.0.4.0

Automatic Undo Management 1 TRUE 11.2.0.4.0

Character Set 1 TRUE 11.2.0.4.0

Deferred Segment Creation 1 TRUE 11.2.0.4.0

HeapCompression 1 TRUE 11.2.0.4.0

Locally Managed Tablespaces (system) 1 TRUE 11.2.0.4.0

Locally Managed Tablespaces (user) 1 TRUE 11.2.0.4.0

Oracle Java Virtual Machine (system) 1 TRUE 11.2.0.4.0

Partitioning (system) 1 TRUE 11.2.0.4.0

Recovery Area 1 TRUE 11.2.0.4.0

SecureFiles (system) 1 TRUE 11.2.0.4.0

SecureFiles (user) 1 TRUE 11.2.0.4.0

Server Parameter File 1 TRUE 11.2.0.4.0

Services 1 TRUE 11.2.0.4.0

Virtual Private Database (VPD) 1 TRUE 11.2.0.4.0

let’s play with table drop

SQL> drop table DBABG;

Table dropped.

SQL> select flashback_on from v$database;

FLASHBACK_ON

------------------

RESTORE POINT ONLY

SQL>

Let’s test the flashback database

SQL> shu immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 668082176 bytes

Fixed Size 2256232 bytes

Variable Size 247464600 bytes

Database Buffers 411041792 bytes

Redo Buffers 7319552 bytes

Database mounted.

SQL>

SQL> FLASHBACK DATABASE TO RESTORE POINT DBABG_LAB_TEST;

FLASHBACK DATABASE TO RESTORE POINT DBABG_LAB_TEST

ERROR at line 1:

ORA-00439: feature not enabled: Flashback Database

SQL>

Unfortunately, in Standard Edition this is not possible as option J

The question here is how to use that restore point?

1. Restore database with RMAN, but you need to have a full backup prior that on disk or tape and RMAN will perform point in time recovery by using a restore point as timestamp …. Toooo bad!!!!

2. Restore database/flashback database with the normal command “FLASHBACK DATABASE” but with additional tips and tricks

Are there any flashback logs created for the restore point?

YesJ

Let`s play:

Testing option 1 - .Restore/Recover database with RMAN

RMAN> shutdown immediate RMAN> startup mount

run { set until restore point DBABG_LAB_TEST; restore database; - This will trigger restore from disk or tape recover database; - This will trigger recovery from disk or tape }

RMAN> shutdown immediate RMAN> startup mount

RMAN> restore database until restore point DBABG_LAB_TEST;

This will trigger restore from disk or tape

Starting restore at Mar 1 2017 10:36:26

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=247 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

------ cut ----------

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:01:35

Finished restore at Mar 1 2017 10:38:02

RMAN> recover database until restore point DBABG_LAB_TEST;

This will trigger recovery from disk or tape

Starting recover at Mar 1 2017 10:38:45

using channel ORA_DISK_1

starting media recovery

media recovery complete, elapsed time: 00:00:01

Finished recover at Mar 1 2017 10:38:49

Testing option 2 - .Restore database/flashback database with the normal command but with additional tips and tricks
Prerequisites

Install New Oracle Home with Enterprise Edition for temporary pursue

Let’s start :)

Oracle Home with Standard Edition -> /u01/app/db/11.2.0/db_2

Oracle Home with Enterprise Edition -> /u01/app/db/11.2.0/db_1

From the above information is visible that restore point has been created with Standard Edition, now we are going to stop the database from Standard Edition home, copy the configuration from SE oracle_home/dbs location to EE oracle_home/dbs location in order to be able to start the database.

Checking environment - Standard Edition

[oracle@lab13 trace]$ env | grep ORA

ORACLE_SID=orcl11gsd

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=/u01/app/db/11.2.0/db_2

[oracle@lab13 trace]$

Export Enterprise Edition Home

[oracle@lab13 trace]$ export ORACLE_HOME=/var/www/html/INSTALL/product/11.2.0/db_1

Checking enironment - Enterprise Edition

[oracle@lab13 trace]$ env | grep ORA

ORACLE_SID=orcl11gsd

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=/u01/app/db/11.2.0/db_1

[oracle@lab13 trace]$

Startup database in mount face with Oracle Enterprise Edition Home

SQL> startup mount;

ORACLE instance started.

Total System Global Area 668082176 bytes

Fixed Size 2256232 bytes

Variable Size 247464600 bytes

Database Buffers 411041792 bytes

Redo Buffers 7319552 bytes

Database mounted.

SQL>

Checking restore point information

set lines 555

col INSTANCE_NAME for a13

col GRP_NAME for a20

col HOST for a15

col TIME for a22

select d.instance_name as INSTANCE_NAME,d.host_name as HOST,r.name as GRP_NAME,to_char(r.time,'DD-MON-YY HH24:MI:SS') as TIME

from gv$instance d,v$restore_point r ;

INSTANCE_NAME HOST GRP_NAME TIME

------------- --------------- -------------------- ----------------------

orcl11gsd lab13 DBABG_LAB_TEST 01-MAR-17 06:29:09

SQL>

Flashback database to restore point

SQL> FLASHBACK DATABASE TO RESTORE POINT DBABG_LAB_TEST;

Flashback complete.

SQL>

Checking alert log

Wed Mar 01 07:17:14 2017

RVWR started with pid=20, OS id=24745

Database mounted in Exclusive Mode

Lost write protection disabled

Completed: ALTER DATABASE MOUNT

Wed Mar 01 07:17:36 2017

FLASHBACK DATABASE TO RESTORE POINT DBABG_LAB_TEST

Flashback Restore Start

Flashback Restore Complete

Flashback Media Recovery Start

started logmerger process

Parallel Media Recovery started with 8 slaves

Wed Mar 01 07:17:37 2017

Recovery of Online Redo Log: Thread 1 Group 2 Seq 8 Reading mem 0

Mem# 0: /var/www/html/INSTALL/product/oradata/orcl11g/redo02.log

Incomplete Recovery applied until change 1029543 time 03/01/2017 06:33:19

Flashback Media Recovery Complete

Completed: FLASHBACK DATABASE TO RESTORE POINT DBABG_LAB_TEST

Wed Mar 01 07:18:07 2017

Checking environment - Enterprise Edition

[oracle@lab13 trace]$ env | grep ORA

ORACLE_SID=orcl11gsd

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=/u01/app/db/11.2.0/db_1

[oracle@lab13 trace]$

Switch to Standard Edition Home

[oracle@lab13 trace]$ export ORACLE_HOME=/u01/app/db/11.2.0/db_2

Checking environment - Standard Edition

[oracle@lab13 trace]$ env | grep ORA

ORACLE_SID=orcl11gsd

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=/u01/app/db/11.2.0/db_2

[oracle@lab13 trace]$

Or you can perform this by using oraenv

[oracle@lab13 trace]$ . oraenv

ORACLE_SID = [orcl11gsd] ? orcl11gsd

The Oracle base remains unchanged

[oracle@lab13 trace]$

[oracle@lab13 trace]$ env | grep ORA

ORACLE_SID=orcl11gsd

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=/u01/app/db/11.2.0/db_2

[oracle@lab13 trace]$

Startup database in mount face with original Oracle Standard Edition Home and execute open resetlogs

[oracle@lab13 trace]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.4.0 Production on Wed Mar 1 07:18:32 2017

SQL> conn / as sysdba

Connected to an idle instance.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 668082176 bytes

Fixed Size 2256232 bytes

Variable Size 247464600 bytes

Database Buffers 411041792 bytes

Redo Buffers 7319552 bytes

Database mounted.

SQL> alter database open resetlogs;

Database altered.

SQL>

Alert log

Wed Mar 01 07:18:51 2017

RVWR started with pid=20, OS id=26485

Database mounted in Exclusive Mode

Lost write protection disabled

Completed: ALTER DATABASE MOUNT

Wed Mar 01 07:19:00 2017

alter database open resetlogs

RESETLOGS after incomplete recovery UNTIL CHANGE 1029543

Archived Log entry 2 added for thread 1 sequence 7 ID 0x3e3a4ef4 dest 1:

Archived Log entry 3 added for thread 1 sequence 8 ID 0x3e3a4ef4 dest 1:

Archived Log entry 4 added for thread 1 sequence 9 ID 0x3e3a4ef4 dest 1:

Clearing online redo logfile 1 /oradata/orcl11g/redo01.log

Clearing online log 1 of thread 1 sequence number 7

Clearing online redo logfile 1 complete

Clearing online redo logfile 2 /oradata/orcl11g/redo02.log

Clearing online log 2 of thread 1 sequence number 8

Clearing online redo logfile 2 complete

Clearing online redo logfile 3 /oradata/orcl11g/redo03.log

Clearing online log 3 of thread 1 sequence number 9

Clearing online redo logfile 3 complete

Resetting resetlogs activation ID 1044008692 (0x3e3a4ef4)

Online log /oradata/orcl11g/redo01.log: Thread 1 Group 1 was previously cleared

Online log /oradata/orcl11g/redo02.log: Thread 1 Group 2 was previously cleared

Online log /oradata/orcl11g/redo03.log: Thread 1 Group 3 was previously cleared

Wed Mar 01 07:19:07 2017

Setting recovery target incarnation to 3

Wed Mar 01 07:19:07 2017

Assigning activation ID 1044342423 (0x3e3f6697)

LGWR: STARTING ARCH PROCESSES

Wed Mar 01 07:19:07 2017

ARC0 started with pid=21, OS id=26752

ARC0: Archival started

LGWR: STARTING ARCH PROCESSES COMPLETE

ARC0: STARTING ARCH PROCESSES

Wed Mar 01 07:19:08 2017

ARC1 started with pid=22, OS id=26782

Thread 1 opened at log sequence 1

Current log# 1 seq# 1 mem# 0: /oradata/orcl11g/redo01.log

Successful open of redo thread 1

Wed Mar 01 07:19:08 2017

SMON: enabling cache recovery

Wed Mar 01 07:19:08 2017

ARC2 started with pid=23, OS id=26786

Wed Mar 01 07:19:08 2017

ARC3 started with pid=24, OS id=26790

ARC1: Archival started

ARC2: Archival started

ARC1: Becoming the 'no FAL' ARCH

ARC1: Becoming the 'no SRL' ARCH

ARC2: Becoming the heartbeat ARCH

ARC3: Archival started

ARC0: STARTING ARCH PROCESSES COMPLETE

Checking database version and products feature_usage

NAME DETECTED_USAGES CURRE VERSION

Audit Options 1 TRUE 11.2.0.4.0

Automatic Maintenance - Optimizer Statistics Gathering 1 TRUE 11.2.0.4.0

Automatic Maintenance - Space Advisor 1 TRUE 11.2.0.4.0

Automatic SGA Tuning 1 TRUE 11.2.0.4.0

Automatic SQL Execution Memory 1 TRUE 11.2.0.4.0

Automatic Segment Space Management (system) 1 TRUE 11.2.0.4.0

Automatic Undo Management 1 TRUE 11.2.0.4.0

Character Set 1 TRUE 11.2.0.4.0

Deferred Segment Creation 1 TRUE 11.2.0.4.0

HeapCompression 1 TRUE 11.2.0.4.0

Locally Managed Tablespaces (system) 1 TRUE 11.2.0.4.0

Locally Managed Tablespaces (user) 1 TRUE 11.2.0.4.0

Oracle Java Virtual Machine (system) 1 TRUE 11.2.0.4.0

Partitioning (system) 1 TRUE 11.2.0.4.0

Recovery Area 1 TRUE 11.2.0.4.0

SecureFiles (system) 1 TRUE 11.2.0.4.0

SecureFiles (user) 1 TRUE 11.2.0.4.0

Server Parameter File 1 TRUE 11.2.0.4.0

Services 1 TRUE 11.2.0.4.0

Virtual Private Database (VPD) 1 TRUE 11.2.0.4.0

As you can see, no information for flashback usage!

Checking table information after flashback database

[oracle@lab13 trace]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.4.0 Production on Wed Mar 1 08:45:08 2017

SQL> conn / as sysdba

Connected.

SQL> select count(1) from DBABG;

COUNT(1)

----------

SQL>

Please be aware that this has been tested only for training purposes and implementation on the production system is up-to your own risk.

3 comments

Comment from: Petar S Visitor

Very useful trick Krasi!

Comment from: adm1n1strat0r Visitor

Hi Krasi,

Did you force the view to be updated by using the DBMS_FEATURE_USAGE_INTERNAL package? By default the feature usage view is updated about once per week.

EXEC DBMS_FEATURE_USAGE_INTERNAL.exec_db_usage_sampling(SYSDATE);

Comment from: kkovachki Member

Hello

Thanks for your feedback, point on the feature usage is absolutely correct..
my test is following

SQL> EXEC DBMS_FEATURE_USAGE_INTERNAL.exec_db_usage_sampling(SYSDATE);
PL/SQL procedure successfully completed.
SQL>
NAME DETECTED_USAGES CURRE VERSION
—————————————————————- ————— —– —————–
Audit Options 2 FALSE 11.2.0.4.0
Automatic Maintenance - Optimizer Statistics Gathering 2 FALSE 11.2.0.4.0
Automatic Maintenance - Space Advisor 2 FALSE 11.2.0.4.0
Automatic SGA Tuning 2 FALSE 11.2.0.4.0
Automatic SQL Execution Memory 2 FALSE 11.2.0.4.0
Automatic Segment Space Management (system) 2 FALSE 11.2.0.4.0
Automatic Undo Management 2 FALSE 11.2.0.4.0
Character Set 2 FALSE 11.2.0.4.0
Deferred Segment Creation 2 FALSE 11.2.0.4.0
HeapCompression 1 TRUE 11.2.0.4.0
Locally Managed Tablespaces (system) 2 FALSE 11.2.0.4.0

NAME DETECTED_USAGES CURRE VERSION
—————————————————————- ————— —– —————–
Locally Managed Tablespaces (user) 2 FALSE 11.2.0.4.0
Oracle Java Virtual Machine (system) 2 FALSE 11.2.0.4.0
Partitioning (system) 2 FALSE 11.2.0.4.0
Recovery Area 2 FALSE 11.2.0.4.0
Restore Point 1 FALSE 11.2.0.4.0
SecureFiles (system) 2 FALSE 11.2.0.4.0
SecureFiles (user) 2 FALSE 11.2.0.4.0
Server Parameter File 2 FALSE 11.2.0.4.0
Services 2 FALSE 11.2.0.4.0
Virtual Private Database (VPD) 2 FALSE 11.2.0.4.0
21 rows selected.

As per the above is visible update on Restore Point 1 FALSE 11.2.0.4.0 but we perform flashback database which is other story, as per the documentation flashback database should be visible if you trigger such action.

Also keep in mind that we did flashback database from enterprise edition and then open resetlogs database from standard edition .

Thanks ones again.
Krasi

Form is loading...