Can we flashback a table dropped by sys user ?

It depends on the ownership of the table that being dropped by sys.

If SYS user dropped a table that has been owned by some other user, then we can flashback that table.

But what if SYS dropped its on table? Can we flashback ? As recyclebin does not work for objects owned by SYS user, we can't flashback a table that has been owned by SYS.

Here is an example for this.

C:\Users\Mahi>sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Fri Oct 6 22:36:35 2017
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> sho user;
USER is "SYS"
SQL>
SQL> drop table hr.emp;

Table dropped.

SQL>  select OBJECT_NAME,ORIGINAL_NAME from dba_recyclebin;

OBJECT_NAME
--------------------------------------------------------------------------------
ORIGINAL_NAME
--------------------------------------------------------------------------------
BIN$cTyO8WzRQFGqDa97TfzN8A==$0
EMP

SQL>  flashback table hr.emp to before drop;

Flashback complete.

SQL> select count(1) from hr.emp;

  COUNT(1)
----------
       107

SQL> create table ta as select * from dba_users;

Table created.

SQL> select table_name,owner from dba_tables where table_name='TA';

TABLE_NAME
--------------------------------------------------------------------------------
OWNER
--------------------------------------------------------------------------------
TA
SYS

SQL> drop table ta;

Table dropped.

SQL> select OBJECT_NAME,ORIGINAL_NAME from dba_recyclebin;

no rows selected

SQL> flashback table ta to before drop;
flashback table ta to before drop
*
ERROR at line 1:
ORA-38305: object not in RECYCLE BIN

SQL>

Col backup without redo log files and tempfiles - Is it really required to include redo and temp files in cold backup ?

Since temporary tablespaces don't hold any objects and are created in seconds, there's no reason to back them up. What about redo log files ?

A straight forward answer for the question will be, If you do not wish to do a RESETLOGS then, include redo logs in your cold backup.

In this example, I will take cold backup without my redo log files and tempfiles. Later I will restore my database using my cold backup.

Let's do some transaction to verify the data consistency after the restore operation.

SQL> select name from v$database ;

NAME
---------
ORCL

SQL> update emp set salary=10000;

107 rows updated.

SQL> commit;

Commit complete.

SQL> select sum(salary) from emp;

SUM(SALARY)
-----------
    1070000

SQL>

SQL> select name from v$datafile ;

NAME
--------------------------------------------------------------------------------
E:\ORADATA\ORCL\SYSTEM01.DBF
E:\ORADATA\ORCL\EXAMPLE01.DBF
E:\ORADATA\ORCL\SYSAUX01.DBF
E:\ORADATA\ORCL\UNDOTBS01.DBF
E:\ORADATA\ORCL\USERS01.DBF

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
E:\ORADATA\ORCL\REDO03.LOG
E:\ORADATA\ORCL\REDO02.LOG
E:\ORADATA\ORCL\REDO01.LOG

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
E:\ORADATA\ORCL\CONTROL01.CTL
E:\FAST_RECOVERY_AREA\ORCL\CONTROL02.CTL

SQL> shut immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

E:\oradata>cd E:\ORADATA\

E:\oradata>mkdir ORADATA_BKP

In my cold backup,I am gonna to consider only my datafiles (but no temp files) and one of my controlfile from it's multiplexed location.

E:\oradata>copy E:\ORADATA\ORCL\SYSTEM01.DBF ORADATA_BKP\
        1 file(s) copied.

E:\oradata>copy E:\ORADATA\ORCL\EXAMPLE01.DBF ORADATA_BKP\
        1 file(s) copied.

E:\oradata>copy E:\ORADATA\ORCL\SYSAUX01.DBF ORADATA_BKP\
        1 file(s) copied.

E:\oradata>copy E:\ORADATA\ORCL\UNDOTBS01.DBF ORADATA_BKP\
        1 file(s) copied.

E:\oradata>copy E:\ORADATA\ORCL\USERS01.DBF ORADATA_BKP\
        1 file(s) copied.

E:\oradata>copy E:\ORADATA\ORCL\CONTROL01.CT ORADATA_BKP\
The system cannot find the file specified.

E:\oradata>copy E:\ORADATA\ORCL\CONTROL01.CTL ORADATA_BKP\
        1 file(s) copied.

E:\oradata>cd  ORADATA_BKP\

E:\oradata\ORADATA_BKP>dir *
 Volume in drive E is New Volume
 Volume Serial Number is 0AF9-618D

 Directory of E:\oradata\ORADATA_BKP

09/30/2017  05:34 PM    <DIR>          .
09/30/2017  05:34 PM    <DIR>          ..
09/30/2017  05:21 PM        10,141,696 CONTROL01.CTL
09/30/2017  05:21 PM       375,529,472 EXAMPLE01.DBF
09/30/2017  05:21 PM       786,440,192 SYSAUX01.DBF
09/30/2017  05:21 PM       859,840,512 SYSTEM01.DBF
09/30/2017  05:21 PM       760,225,792 UNDOTBS01.DBF
09/30/2017  05:21 PM         5,251,072 USERS01.DBF
               6 File(s)  2,797,428,736 bytes
               2 Dir(s)  42,555,953,152 bytes free

I have a cold backup of my database now ,So I will delete all my datafiles, redolog files and controlfiles.

E:\oradata\ORADATA_BKP>cd ../ORCL

E:\oradata\ORCL>dir
 Volume in drive E is New Volume
 Volume Serial Number is 0AF9-618D

 Directory of E:\oradata\ORCL

09/30/2017  05:23 PM    <DIR>          .
09/30/2017  05:23 PM    <DIR>          ..
09/30/2017  05:21 PM        10,141,696 CONTROL01.CTL
09/30/2017  05:21 PM       375,529,472 EXAMPLE01.DBF
09/30/2017  05:21 PM        52,429,312 REDO01.LOG
09/30/2017  05:21 PM        52,429,312 REDO02.LOG
09/30/2017  05:21 PM        52,429,312 REDO03.LOG
09/30/2017  05:21 PM       786,440,192 SYSAUX01.DBF
09/30/2017  05:21 PM       859,840,512 SYSTEM01.DBF
09/30/2017  05:21 PM        67,117,056 TEMP01.DBF
09/30/2017  05:21 PM       760,225,792 UNDOTBS01.DBF
09/30/2017  05:21 PM         5,251,072 USERS01.DBF
              10 File(s)  3,021,833,728 bytes
               2 Dir(s)  42,555,953,152 bytes free

E:\oradata\ORCL>del *
E:\oradata\ORCL\*, Are you sure (Y/N)? Y

E:\oradata\ORCL>dir
 Volume in drive E is New Volume
 Volume Serial Number is 0AF9-618D

 Directory of E:\oradata\ORCL

09/30/2017  05:37 PM    <DIR>          .
09/30/2017  05:37 PM    <DIR>          ..
               0 File(s)              0 bytes
               2 Dir(s)  45,577,797,632 bytes free

Deleted second copy of my control file. 

E:\oradata\ORCL>del E:\FAST_RECOVERY_AREA\ORCL\CONTROL02.CTL

E:\oradata\ORCL>set ORACLE_SID=ORCL

E:\oradata\ORCL>sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Sat Sep 30 17:37:55 2017
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup;
ORACLE instance started.
Total System Global Area  626327552 bytes
Fixed Size                  2405760 bytes
Variable Size             507513472 bytes
Database Buffers          109051904 bytes
Redo Buffers                7356416 bytes
ORA-00205: error in identifying control file, check alert log for more info
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Let's move my coldbackup to it's original location.

E:\oradata\ORCL>cd ../ORADATA_BKP

E:\oradata\ORADATA_BKP>dir
 Volume in drive E is New Volume
 Volume Serial Number is 0AF9-618D

 Directory of E:\oradata\ORADATA_BKP

09/30/2017  05:34 PM    <DIR>          .
09/30/2017  05:34 PM    <DIR>          ..
09/30/2017  05:21 PM        10,141,696 CONTROL01.CTL
09/30/2017  05:21 PM       375,529,472 EXAMPLE01.DBF
09/30/2017  05:21 PM       786,440,192 SYSAUX01.DBF
09/30/2017  05:21 PM       859,840,512 SYSTEM01.DBF
09/30/2017  05:21 PM       760,225,792 UNDOTBS01.DBF
09/30/2017  05:21 PM         5,251,072 USERS01.DBF
               6 File(s)  2,797,428,736 bytes
               2 Dir(s)  45,587,349,504 bytes free

E:\oradata\ORADATA_BKP>move * ../ORCL
E:\oradata\ORADATA_BKP\CONTROL01.CTL
E:\oradata\ORADATA_BKP\EXAMPLE01.DBF
E:\oradata\ORADATA_BKP\SYSAUX01.DBF
E:\oradata\ORADATA_BKP\SYSTEM01.DBF
E:\oradata\ORADATA_BKP\UNDOTBS01.DBF
E:\oradata\ORADATA_BKP\USERS01.DBF
        6 file(s) moved.

E:\oradata\ORADATA_BKP>cd ../ORCL

E:\oradata\ORCL>dir
 Volume in drive E is New Volume
 Volume Serial Number is 0AF9-618D

 Directory of E:\oradata\ORCL

09/30/2017  05:41 PM    <DIR>          .
09/30/2017  05:41 PM    <DIR>          ..
09/30/2017  05:21 PM        10,141,696 CONTROL01.CTL
09/30/2017  05:21 PM       375,529,472 EXAMPLE01.DBF
09/30/2017  05:21 PM       786,440,192 SYSAUX01.DBF
09/30/2017  05:21 PM       859,840,512 SYSTEM01.DBF
09/30/2017  05:21 PM       760,225,792 UNDOTBS01.DBF
09/30/2017  05:21 PM         5,251,072 USERS01.DBF
               6 File(s)  2,797,428,736 bytes
               2 Dir(s)  45,587,349,504 bytes free

E:\oradata\ORCL>copy CONTROL01.CTL E:\FAST_RECOVERY_AREA\ORCL\CONTROL02.CTL
        1 file(s) copied.

E:\oradata\ORCL>sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Sat Sep 30 17:42:52 2017
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> alter database mount ;

Database altered.

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
E:\ORADATA\ORCL\CONTROL01.CTL
E:\FAST_RECOVERY_AREA\ORCL\CONTROL02.CTL

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 3788
Session ID: 242 Serial number: 3

Alertlog clearly shows it can't identify the redo log member.

O/S-Error: (OS 2) The system cannot find the file specified.
Starting background process TMON
TMON started with pid=24, OS id=4300
LGWR: STARTING ARCH PROCESSES
Starting background process ARC0
ARC0 started with pid=25, OS id=4696
Errors in file E:\APP\MAHI\diag\rdbms\orcl\orcl\trace\orcl_m000_2880.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: 'E:\ORADATA\ORCL\REDO02.LOG'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
2017-09-30 17:43:42.981000 +05:30
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
Errors in file E:\APP\MAHI\diag\rdbms\orcl\orcl\trace\orcl_lgwr_2464.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: 'E:\ORADATA\ORCL\REDO01.LOG'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
Errors in file E:\APP\MAHI\diag\rdbms\orcl\orcl\trace\orcl_lgwr_2464.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: 'E:\ORADATA\ORCL\REDO01.LOG'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
Errors in file E:\APP\MAHI\diag\rdbms\orcl\orcl\trace\orcl_ora_3788.trc:
ORA-00313: open failed for members of log group 1 of thread
ORA-00312: online log 1 thread 1: 'E:\ORADATA\ORCL\REDO01.LOG'
System state dump requested by (instance=1, osid=3788), summary=[abnormal instance termination].
System State dumped to trace file E:\APP\MAHI\diag\rdbms\orcl\orcl\trace\orcl_diag_4988.trc
ARC0: STARTING ARCH PROCESSES
2017-09-30 17:43:44.624000 +05:30
Dumping diagnostic data in directory=[cdmp_20170930174343], requested by (instance=1, osid=3788), summary=[abnormal instance termination].
2017-09-30 17:43:46.297000 +05:30
USER (ospid: 3788): terminating the instance due to error 313
2017-09-30 17:43:51.373000 +05:30
Instance terminated by USER, pid = 3788

So let's try to open our database in resetlogs mode.

E:\oradata\ORCL>sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Sat Sep 30 17:48:38 2017
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2405760 bytes
Variable Size             507513472 bytes
Database Buffers          109051904 bytes
Redo Buffers                7356416 bytes
Database mounted.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery

As we all know, it's not possible to open a database in resetlogs without doing a incomplete recovery. So try to mimic it.

SQL> recover database until cancel;
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

SQL>

Yes, we could able to start our database in resetlogs mode. The resetlogs command recreate any redologs file that's physically not exist.

E:\oradata\ORCL>dir
 Volume in drive E is New Volume
 Volume Serial Number is 0AF9-618D

 Directory of E:\oradata\ORCL

09/30/2017  05:51 PM    <DIR>          .
09/30/2017  05:51 PM    <DIR>          ..
09/30/2017  06:30 PM        10,141,696 CONTROL01.CTL
09/30/2017  05:51 PM       375,529,472 EXAMPLE01.DBF
09/30/2017  05:51 PM        52,429,312 REDO01.LOG
09/30/2017  05:51 PM        52,429,312 REDO02.LOG
09/30/2017  05:51 PM        52,429,312 REDO03.LOG
09/30/2017  05:51 PM       786,440,192 SYSAUX01.DBF
09/30/2017  05:51 PM       859,840,512 SYSTEM01.DBF
09/30/2017  05:51 PM        67,117,056 TEMP01.DBF
09/30/2017  05:51 PM       760,225,792 UNDOTBS01.DBF
09/30/2017  05:51 PM         5,251,072 USERS01.DBF
              10 File(s)  3,021,833,728 bytes
               2 Dir(s)  45,341,138,944 bytes free.

I learned something new today- by analyzing the alertlog file,I could see oracle recreated all missing TEMP files as part of resetlogs operation - Great.

ORA-01186: file 201 failed verification tests
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-01110: data file 201: 'E:\ORADATA\ORCL\TEMP01.DBF'
File 201 not verified due to error ORA-01157
Re-creating tempfile E:\ORADATA\ORCL\TEMP01.DBF
Starting background process SMCO

SQL> select sum(salary) from emp;

SUM(SALARY)
-----------
    1070000
SQL>

Database recovery from the loss of all database file - datafiles, controlfiles , redologfiles ,archivelog files and spfile

Before deleting the entire database files for simulating the scenario I ensured I have a full database backup which I took some time back.

C:\Users\Mahi>set ORACLE_SID=ORCL
C:\Users\Mahi>rman target /
Recovery Manager: Release 12.1.0.1.0 - Production on Sun Sep 24 19:19:13 2017
Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.
connected to target database: ORCL (DBID=1482239337)

RMAN> list backup summary ;

using target database control file instead of recovery catalog
List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
1       B  A  X DISK        12-SEP-17       1       1       NO         TAG20170912T225210
2       B  A  A DISK        12-SEP-17       1       1       NO         TAG20170912T225432
3       B  F  A DISK        12-SEP-17       1       1       NO         TAG20170912T225434
4       B  A  A DISK        13-SEP-17       1       1       NO         TAG20170913T221354
5       B  A  A DISK        13-SEP-17       1       1       NO         TAG20170913T221354
6       B  A  A DISK        13-SEP-17       1       1       NO         TAG20170913T222738
7       B  A  A DISK        13-SEP-17       1       1       NO         TAG20170913T222738
8       B  A  A DISK        13-SEP-17       1       1       NO         TAG20170913T223828
9       B  A  A DISK        13-SEP-17       1       1       NO         TAG20170913T223828
10      B  A  A DISK        13-SEP-17       1       1       NO         TAG20170913T223828
11      B  A  A DISK        13-SEP-17       1       1       NO         TAG20170913T223828
12      B  F  A DISK        18-SEP-17       1       1       NO         TAG20170918T215104
13      B  F  A DISK        18-SEP-17       1       1       NO         TAG20170918T215210
14      B  F  A DISK        19-SEP-17       1       1       NO         TAG20170919T223124
15      B  F  A DISK        19-SEP-17       1       1       NO         TAG20170919T223221
16      B  F  A DISK        20-SEP-17       1       1       NO         TAG20170920T093037
17      B  F  A DISK        20-SEP-17       1       1       NO         TAG20170920T093037
18      B  F  A DISK        20-SEP-17       1       1       NO         TAG20170920T093037
19      B  F  A DISK        20-SEP-17       1       1       NO         TAG20170920T093234

RMAN> show all;

RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 3 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   'E:\Rman\Backup_%d_set%s_piece%p_%T_%U';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'E:\APP\MAHI\PRODUCT\12.1.0\DBHOME_3\DATABASE\SNCFORCL.ORA'; # default

RMAN> exit
Recovery Manager complete.

I haven't enabled FRA for my database also I enabled CONTROLFILE AUTOBACKUP ON. So default location of controlfile autobackup will be $ORACLE_HOME/database(for windows).

C:\Users\Mahi>sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Sun Sep 24 19:20:17 2017
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show parameter recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string
db_recovery_file_dest_size           big integer 6930M
recovery_parallelism                 integer     0
SQL>
SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
E:\ORADATA\ORCL\CONTROL01.CTL
E:\FAST_RECOVERY_AREA\ORCL\CONTROL02.CTL

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
E:\ORADATA\ORCL\REDO03.LOG
E:\ORADATA\ORCL\REDO02.LOG
E:\ORADATA\ORCL\REDO01.LOG

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
E:\ORADATA\ORCL\SYSTEM01.DBF
E:\ORADATA\ORCL\EXAMPLE01.DBF
E:\ORADATA\ORCL\SYSAUX01.DBF
E:\ORADATA\ORCL\UNDOTBS01.DBF
E:\ORADATA\ORCL\USERS01.DBF

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
E:\ORADATA\ORCL\TEMP01.DBF

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            E:\archive\
Oldest online log sequence     17
Next log sequence to archive   19
Current log sequence           19

Let's do some transaction and generate archive logs for that.

SQL> create table emp as select * from hr.employees ;

Table created.

SQL>
SQL> select count(*) from emp;

  COUNT(*)
----------
       107

SQL> alter system switch logfile;

System altered.

SQL> update emp set SALARY=10000;

107 rows updated.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> update emp set SALARY=555555;

107 rows updated.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> select sum(salary) from emp;

SUM(SALARY)
-----------
   59444385

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Take the entire archivelog backup and delete it - as we have enabled controlfile autobackup the controlfile will automatically backed up at the end of the archivelog bakcup . 

C:\Users\Mahi>rman target /
Recovery Manager: Release 12.1.0.1.0 - Production on Sun Sep 24 19:29:27 2017
Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.
connected to target database: ORCL (DBID=1482239337)

RMAN> backup archivelog all delete input;

Starting backup at 24-SEP-17
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=373 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=15 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=134 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=12 RECID=9 STAMP=954542282
input archived log thread=1 sequence=13 RECID=10 STAMP=954542321
input archived log thread=1 sequence=14 RECID=11 STAMP=954542322
input archived log thread=1 sequence=15 RECID=22 STAMP=954628034
input archived log thread=1 sequence=16 RECID=23 STAMP=954628034
input archived log thread=1 sequence=17 RECID=24 STAMP=954628034
input archived log thread=1 sequence=18 RECID=25 STAMP=954628034
input archived log thread=1 sequence=19 RECID=26 STAMP=954628034
input archived log thread=1 sequence=20 RECID=27 STAMP=954628034
channel ORA_DISK_1: starting piece 1 at 24-SEP-17
channel ORA_DISK_2: starting archived log backup set
channel ORA_DISK_2: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=18 STAMP=954626630
input archived log thread=1 sequence=2 RECID=19 STAMP=954627226
input archived log thread=1 sequence=3 RECID=20 STAMP=954627378
input archived log thread=1 sequence=4 RECID=21 STAMP=954627507
input archived log thread=1 sequence=5 RECID=28 STAMP=954628058
input archived log thread=1 sequence=6 RECID=29 STAMP=954628707
input archived log thread=1 sequence=7 RECID=30 STAMP=954628880
input archived log thread=1 sequence=8 RECID=31 STAMP=955057332
input archived log thread=1 sequence=9 RECID=32 STAMP=955144317
input archived log thread=1 sequence=10 RECID=33 STAMP=955145066
channel ORA_DISK_2: starting piece 1 at 24-SEP-17
channel ORA_DISK_3: starting archived log backup set
channel ORA_DISK_3: specifying archived log(s) in backup set
input archived log thread=1 sequence=11 RECID=34 STAMP=955146500
input archived log thread=1 sequence=12 RECID=35 STAMP=955146628
input archived log thread=1 sequence=13 RECID=36 STAMP=955147900
input archived log thread=1 sequence=14 RECID=37 STAMP=955184978
input archived log thread=1 sequence=15 RECID=38 STAMP=955185627
input archived log thread=1 sequence=16 RECID=39 STAMP=955231812
input archived log thread=1 sequence=17 RECID=40 STAMP=955490250
input archived log thread=1 sequence=18 RECID=41 STAMP=955566481
input archived log thread=1 sequence=19 RECID=42 STAMP=955567686
channel ORA_DISK_3: starting piece 1 at 24-SEP-17
channel ORA_DISK_1: finished piece 1 at 24-SEP-17
piece handle=E:\RMAN\BACKUP_ORCL_SET23_PIECE1_20170924_0NSF9JO9_1_1 tag=TAG20170924T193118 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:12
channel ORA_DISK_1: deleting archived log(s)
archived log file name=E:\ARCHIVE\ORCL_1_12_954458606.ARC RECID=9 STAMP=954542282
archived log file name=E:\ARCHIVE\ORCL_1_13_954458606.ARC RECID=10 STAMP=954542321
archived log file name=E:\ARCHIVE\ORCL_1_14_954458606.ARC RECID=11 STAMP=954542322
archived log file name=E:\ARCHIVE\OLD\ORCL_1_15_954458606.ARC RECID=22 STAMP=954628034
archived log file name=E:\ARCHIVE\OLD\ORCL_1_16_954458606.ARC RECID=23 STAMP=954628034
archived log file name=E:\ARCHIVE\OLD\ORCL_1_17_954458606.ARC RECID=24 STAMP=954628034
archived log file name=E:\ARCHIVE\OLD\ORCL_1_18_954458606.ARC RECID=25 STAMP=954628034
archived log file name=E:\ARCHIVE\OLD\ORCL_1_19_954458606.ARC RECID=26 STAMP=954628034
archived log file name=E:\ARCHIVE\OLD\ORCL_1_20_954458606.ARC RECID=27 STAMP=954628034
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=20 RECID=43 STAMP=955567722
input archived log thread=1 sequence=21 RECID=44 STAMP=955567745
input archived log thread=1 sequence=22 RECID=45 STAMP=955567877
channel ORA_DISK_1: starting piece 1 at 24-SEP-17
channel ORA_DISK_2: finished piece 1 at 24-SEP-17
piece handle=E:\RMAN\BACKUP_ORCL_SET24_PIECE1_20170924_0OSF9JO9_1_1 tag=TAG20170924T193118 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:21
channel ORA_DISK_2: deleting archived log(s)
archived log file name=E:\ARCHIVE\ORCL_1_1_954582352.ARC RECID=18 STAMP=954626630
archived log file name=E:\ARCHIVE\ORCL_1_2_954582352.ARC RECID=19 STAMP=954627226
archived log file name=E:\ARCHIVE\ORCL_1_3_954582352.ARC RECID=20 STAMP=954627378
archived log file name=E:\ARCHIVE\ORCL_1_4_954582352.ARC RECID=21 STAMP=954627507
archived log file name=E:\ARCHIVE\ORCL_1_5_954582352.ARC RECID=28 STAMP=954628058
archived log file name=E:\ARCHIVE\ORCL_1_6_954582352.ARC RECID=29 STAMP=954628707
archived log file name=E:\ARCHIVE\ORCL_1_7_954582352.ARC RECID=30 STAMP=954628880
archived log file name=E:\ARCHIVE\ORCL_1_8_954582352.ARC RECID=31 STAMP=955057332
archived log file name=E:\ARCHIVE\ORCL_1_9_954582352.ARC RECID=32 STAMP=955144317
archived log file name=E:\ARCHIVE\ORCL_1_10_954582352.ARC RECID=33 STAMP=955145066
channel ORA_DISK_3: finished piece 1 at 24-SEP-17
piece handle=E:\RMAN\BACKUP_ORCL_SET25_PIECE1_20170924_0PSF9JOB_1_1 tag=TAG20170924T193118 comment=NONE
channel ORA_DISK_3: backup set complete, elapsed time: 00:00:12
channel ORA_DISK_3: deleting archived log(s)
archived log file name=E:\ARCHIVE\ORCL_1_11_954582352.ARC RECID=34 STAMP=955146500
archived log file name=E:\ARCHIVE\ORCL_1_12_954582352.ARC RECID=35 STAMP=955146628
archived log file name=E:\ARCHIVE\ORCL_1_13_954582352.ARC RECID=36 STAMP=955147900
archived log file name=E:\ARCHIVE\ORCL_1_14_954582352.ARC RECID=37 STAMP=955184978
archived log file name=E:\ARCHIVE\ORCL_1_15_954582352.ARC RECID=38 STAMP=955185627
archived log file name=E:\ARCHIVE\ORCL_1_16_954582352.ARC RECID=39 STAMP=955231812
archived log file name=E:\ARCHIVE\ORCL_1_17_954582352.ARC RECID=40 STAMP=955490250
archived log file name=E:\ARCHIVE\ORCL_1_18_954582352.ARC RECID=41 STAMP=955566481
archived log file name=E:\ARCHIVE\ORCL_1_19_954582352.ARC RECID=42 STAMP=955567686
channel ORA_DISK_1: finished piece 1 at 24-SEP-17
piece handle=E:\RMAN\BACKUP_ORCL_SET26_PIECE1_20170924_0QSF9JOV_1_1 tag=TAG20170924T193118 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: deleting archived log(s)
archived log file name=E:\ARCHIVE\ORCL_1_20_954582352.ARC RECID=43 STAMP=955567722
archived log file name=E:\ARCHIVE\ORCL_1_21_954582352.ARC RECID=44 STAMP=955567745
archived log file name=E:\ARCHIVE\ORCL_1_22_954582352.ARC RECID=45 STAMP=955567877
Finished backup at 24-SEP-17

Starting Control File and SPFILE Autobackup at 24-SEP-17
piece handle=E:\APP\MAHI\PRODUCT\12.1.0\DBHOME_3\DATABASE\C-1482239337-20170924-00 comment=NONE
Finished Control File and SPFILE Autobackup at 24-SEP-17
RMAN> exit
Recovery Manager complete.

C:\Users\Mahi>sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Sun Sep 24 19:36:38 2017
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> shut abort;
ORACLE instance shut down.
SQL>

Now I will delete all my database files

E:\oradata\ORCL>del *
E:\oradata\ORCL\*, Are you sure (Y/N)? Y

E:\fast_recovery_area\ORCL>del CONTROL02.CTL

E:\app\Mahi\product\12.1.0\dbhome_3\database>del SPFILEORCL.ORA

We lost everything. Now we will recover SPFILE/controlfile from autobackup , as I haven't enabled FRA my autobackup goes to $ORACLE_HOME/database directory. 

E:\app\Mahi\product\12.1.0\dbhome_3\database>dir
 Volume in drive E is New Volume
 Volume Serial Number is 0AF9-618D

 Directory of E:\app\Mahi\product\12.1.0\dbhome_3\database

09/24/2017  07:47 PM    <DIR>          .
09/24/2017  07:47 PM    <DIR>          ..
09/11/2017  10:38 PM    <DIR>          archive
09/19/2017  10:32 PM        10,223,616 C-1482239337-20170919-00
09/24/2017  07:31 PM        10,223,616 C-1482239337-20170924-00
09/11/2017  10:52 PM             2,048 hc_orcl.dat
12/22/2005  04:07 AM            31,744 oradba.exe
09/24/2017  07:09 PM             1,763 oradim.log
09/11/2017  11:37 PM             7,680 PWDorcl.ora
09/24/2017  07:31 PM        10,141,696 SNCFORCL.ORA
               8 File(s)     30,635,747 bytes
               3 Dir(s)  51,603,574,784 bytes free

E:\app\Mahi\product\12.1.0\dbhome_3\database>

C:\Users\Mahi>set ORACLE_SID=ORCL
C:\Users\Mahi>rman target /
Recovery Manager: Release 12.1.0.1.0 - Production on Sun Sep 24 19:50:39 2017
Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.
connected to target database (not started)

RMAN> startup nomount;
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file 'E:\APP\MAHI\PRODUCT\12.1.0\DBHOME_3\DATABASE\INITORCL.ORA'
starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started
Total System Global Area    1068937216 bytes
Fixed Size                     2410864 bytes
Variable Size                293602960 bytes
Database Buffers             767557632 bytes
Redo Buffers                   5365760 bytes

RMAN> restore spfile from autobackup;

Starting restore at 24-SEP-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=169 device type=DISK

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 09/24/2017 19:51:33
RMAN-06495: must explicitly specify DBID with SET DBID command

So for restoring SPFILE from autobacup(and autobackup is in default location) you need to specify SET DBID command. By specifying the DBID you instruct oracle to search for matching autobackup from it's default location . Keep in mind By default, the format of the autobackup file for all configured devices is the substitution variable %F. This variable format translates into c-IIIIIIIIII-YYYYMMDD-QQ, where:

IIIIIIIIII stands for the DBID.

RMAN> set dbid 1482239337;

executing command: SET DBID

RMAN>  restore spfile from autobackup;

Starting restore at 24-SEP-17
using channel ORA_DISK_1

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20170924
channel ORA_DISK_1: AUTOBACKUP found: c-1482239337-20170924-00
channel ORA_DISK_1: restoring spfile from AUTOBACKUP c-1482239337-20170924-00
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 24-SEP-17

RMAN>  shutdown;

Oracle instance shut down

RMAN>  startup nomount;

connected to target database (not started)
Oracle instance started

Total System Global Area     626327552 bytes

Fixed Size                     2405760 bytes
Variable Size                507513472 bytes
Database Buffers             109051904 bytes
Redo Buffers                   7356416 bytes


RMAN> restore controlfile from autobackup;

Starting restore at 24-SEP-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=5 device type=DISK

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20170924
channel ORA_DISK_1: AUTOBACKUP found: c-1482239337-20170924-00
channel ORA_DISK_1: restoring control file from AUTOBACKUP c-1482239337-20170924-00
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=E:\ORADATA\ORCL\CONTROL01.CTL
output file name=E:\FAST_RECOVERY_AREA\ORCL\CONTROL02.CTL
Finished restore at 24-SEP-17

RMAN> alter database mount;

Statement processed
released channel: ORA_DISK_1

RMAN> restore database ;

Starting restore at 24-SEP-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=5 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=123 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=244 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00002 to E:\ORADATA\ORCL\EXAMPLE01.DBF
channel ORA_DISK_1: restoring datafile 00005 to E:\ORADATA\ORCL\UNDOTBS01.DBF
channel ORA_DISK_1: reading from backup piece E:\RMAN\BACKUP_ORCL_SET19_PIECE1_20170920_0JSETV1U_1_1
channel ORA_DISK_2: starting datafile backup set restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_DISK_2: restoring datafile 00001 to E:\ORADATA\ORCL\SYSTEM01.DBF
channel ORA_DISK_2: reading from backup piece E:\RMAN\BACKUP_ORCL_SET20_PIECE1_20170920_0KSETV1U_1_1
channel ORA_DISK_3: starting datafile backup set restore
channel ORA_DISK_3: specifying datafile(s) to restore from backup set
channel ORA_DISK_3: restoring datafile 00003 to E:\ORADATA\ORCL\SYSAUX01.DBF
channel ORA_DISK_3: restoring datafile 00006 to E:\ORADATA\ORCL\USERS01.DBF
channel ORA_DISK_3: reading from backup piece E:\RMAN\BACKUP_ORCL_SET21_PIECE1_20170920_0LSETV1V_1_1
channel ORA_DISK_1: piece handle=E:\RMAN\BACKUP_ORCL_SET19_PIECE1_20170920_0JSETV1U_1_1 tag=TAG20170920T093037
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:57
channel ORA_DISK_2: piece handle=E:\RMAN\BACKUP_ORCL_SET20_PIECE1_20170920_0KSETV1U_1_1 tag=TAG20170920T093037
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:01:57
channel ORA_DISK_3: piece handle=E:\RMAN\BACKUP_ORCL_SET21_PIECE1_20170920_0LSETV1V_1_1 tag=TAG20170920T093037
channel ORA_DISK_3: restored backup piece 1
channel ORA_DISK_3: restore complete, elapsed time: 00:01:57
Finished restore at 24-SEP-17

RMAN> recover database ;

Starting recover at 24-SEP-17
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3

starting media recovery

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=16
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=17
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=18
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=19
channel ORA_DISK_1: reading from backup piece E:\RMAN\BACKUP_ORCL_SET25_PIECE1_20170924_0PSF9JOB_1_1
channel ORA_DISK_2: starting archived log restore to default destination
channel ORA_DISK_2: restoring archived log
archived log thread=1 sequence=20
channel ORA_DISK_2: restoring archived log
archived log thread=1 sequence=21
channel ORA_DISK_2: restoring archived log
archived log thread=1 sequence=22
channel ORA_DISK_2: reading from backup piece E:\RMAN\BACKUP_ORCL_SET26_PIECE1_20170924_0QSF9JOV_1_1
channel ORA_DISK_2: piece handle=E:\RMAN\BACKUP_ORCL_SET26_PIECE1_20170924_0QSF9JOV_1_1 tag=TAG20170924T193118
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:00:07
channel ORA_DISK_1: piece handle=E:\RMAN\BACKUP_ORCL_SET25_PIECE1_20170924_0PSF9JOB_1_1 tag=TAG20170924T193118
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
archived log file name=E:\ARCHIVE\ORCL_1_16_954582352.ARC thread=1 sequence=16
archived log file name=E:\ARCHIVE\ORCL_1_17_954582352.ARC thread=1 sequence=17
archived log file name=E:\ARCHIVE\ORCL_1_18_954582352.ARC thread=1 sequence=18
archived log file name=E:\ARCHIVE\ORCL_1_19_954582352.ARC thread=1 sequence=19
archived log file name=E:\ARCHIVE\ORCL_1_20_954582352.ARC thread=1 sequence=20
archived log file name=E:\ARCHIVE\ORCL_1_21_954582352.ARC thread=1 sequence=21
archived log file name=E:\ARCHIVE\ORCL_1_22_954582352.ARC thread=1 sequence=22
archived log file name=E:\ARCHIVE\ORCL_1_23_954582352.ARC thread=1 sequence=23
unable to find archived log
archived log thread=1 sequence=24
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 09/24/2017 20:02:14
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 24 and starting SCN of 2697270

RMAN> alter database open resetlogs;

Statement processed

RMAN> select sum(salary) from emp;

SUM(SALARY)
-----------
   59444385

RMAN>

So my data is intact after recovery.