Table Recovery in Standard Edition

Table Recovery in Standard Edition:

Tablespace Point in Time Recovery/Table Recovery  is not Possible in Standard Edition .

I am not convinced with this statement and trying  to perform Table Recovery in Standard edition using RMAN.

Created Tablespace Called "test1"  and Table called "test1".

Taken Backup of Database and Archivelogs:

RUN
{
  ALLOCATE CHANNEL ch11 TYPE DISK MAXPIECESIZE 10G;
  BACKUP
  FORMAT '/u06/odb/backup/%d_D_%T_%u_s%s_p%p'
  DATABASE
  PLUS ARCHIVELOG
  FORMAT '/u06/odb/backup/%d_A_%T_%u_s%s_p%p';
  RELEASE CHANNEL ch11;
}

Taken Current_scn from v$database:

SQL>  select current_scn from v$database;

CURRENT_SCN
-----------
    1655232

Dropped table test1.test1;

Now Started working Restore/Recover Dropped Table test1.test1:


Created Pfile for auxiliary Instance with following parameters:

***************************************************
initialization parameters used for automatic instance:
db_name=RECSETST
db_unique_name=flAE_pitr_RECSETST
compatible=11.2.0.4.0
db_block_size=8192
db_files=200
diagnostic_dest=/u01/app/oracle
_system_trig_enabled=FALSE
sga_target=2560M
processes=200
db_create_file_dest=/u06/aux
log_archive_dest_1='location=/u06/aux'
**************************************************

Started Auxiliary Instance in NOMOUNT State:


[oracle@sal-oratipstest2 dbs]$ mkdir -p /u06/aux
[oracle@sal-oratipstest2 dbs]$ vi initF1AE.ora
[oracle@sal-oratipstest2 dbs]$ export ORACLE_SID=F1AE
[oracle@sal-oratipstest2 dbs]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 3 00:34:10 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SYS@F1AE > startup nomount
ORACLE instance started.

Total System Global Area 2672361472 bytes
Fixed Size                  2256232 bytes
Variable Size             587203224 bytes
Database Buffers         2063597568 bytes
Redo Buffers               19304448 bytes
SYS@F1AE >


Connected to RMAN to Perform Table Recovery:

The Oracle base remains unchanged with value /u01/app/oracle/base
[oracle@sal-oratipstest2 dbs]$ rman

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Jan 3 00:39:00 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

RMAN> connect target sys/manager@RECSETST

connected to target database: RECSETST (DBID=796995508)

RMAN>  connect auxiliary /

connected to auxiliary database: RECSETST (not mounted)

RMAN> run
{
set until  scn 1655232;
restore clone controlfile;
sql clone 'alter database mount clone database';
sql 'alter system archive log current';
}2> 3> 4> 5> 6> 7>

executing command: SET until clause

Starting restore at 03-JAN-17
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=249 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u06/odb/ORAFRA_1/RECSETST/autobackup/2017_01_03/o1_mf_s_932257625_d6o8n1hq_.bkp
channel ORA_AUX_DISK_1: piece handle=/u06/odb/ORAFRA_1/RECSETST/autobackup/2017_01_03/o1_mf_s_932257625_d6o8n1hq_.bkp tag=TAG20170103T002705
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u06/aux/FLAE_PITR_RECSETST/controlfile/o1_mf_d6o9h78k_.ctl
Finished restore at 03-JAN-17

sql statement: alter database mount clone database
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of sql command on clone_default channel at 01/03/2017 00:41:40
RMAN-11003: failure during parse/execution of SQL statement: alter database mount clone database
ORA-00439: feature not enabled: Point-in-time tablespace recovery

SYS@RECSETST > select * from v$option where parameter like '%Point-in-time%' ;

PARAMETER                                                        VALUE
---------------------------------------------------------------- --------------------
Point-in-time tablespace recovery                                FALSE


alter database mount clone database is internally trying to use Point-in-time tablespace recovery Feature and it is failing in First step itslef.

I am trying to understand how to enable this feature in Standard edition.

We already know there are other options like logical Backups (expdp/exp) and use to restore tables, restore database until scn in different machine and take export of required tables and import .