Why we need standby redo log file?

If you configure your standby for Maximum Protection, then Standby Redo Logs are required. Most implementations are configured for Maximum Performance because they do not want the performance hit Max Protect may impart on their application. Even if you are using Max Performance, you still want to implement SRLs. To understand why, we first need to start by examining how redo transport works when SRLs do not exist.  We’ll start by exploring the diagram below. 

In the system above, SRLs are not configured on the standby database. The arrows show how redo transport flows through the system. Redo travels along this path:

1. A transaction writes redo records into the Log Buffer in the System Global Area (SGA).
2. The Log Writer process (LGWR) writes redo records from the Log Buffer to the Online Redo Logs (ORLs).
3. When the ORL switches to the next log sequence (normally when the ORL fills up), the Archiver process (ARC0) will copy the ORL to the Archived Redo Log.
4. Because a standby database exists, a second Archiver process (ARC1) will read from a completed Archived Redo Log and transmit the redo over the network to the Remote File Server (RFS) process running for the standby instance.
5. RFS sends the redo stream to the local Archiver process (ARCn).
6. ARCn then writes the redo to the archived redo log location on the standby server.
7. Once the archived redo log is completed, the Managed Recovery Process (MRP0) sends the redo to the standby instance for applying the transaction.

The picture is more complex when we have Standby Redo Logs in place, as can be seen below. 

With SRLs, not only do we have more items in the picture, we also have different choices, i.e. different paths to get from the primary to the standby. The first choice is to decide if we are configured for Max Protect or Max Performance as I will discuss its impact below.

1. Just like without SRLs, a transaction generates redo in the Log Buffer in the SGA.
2. The LGWR process writes the redo to the ORL.
3. Are we in Max Protect/Max availability or Max Performance mode?
   1. If Max Protect/availability, then we are performing SYNC redo transport. The Network Server SYNC process (NSSn) is a slave process to LGWR. It ships redo to the RFS process on the standby server.
   2. If Max Performance mode, then we are performing ASYNC redo transport. The Network Server ASYNC process (NSAn) reads from the ORL and transports the redo to the RFS process on the standby server.
4. RFS on the standby server simply writes the redo stream directly to the SRLs.
5. How the redo gets applied depends if we are using Real Time Apply or not.
   1. If we are using Real Time Apply, MRP0 will read directly from the SRLs and apply the redo to the standby database.
   2. If we are not using Real Time Apply, MRP0 will wait for the SRL’s contents to be archived and then once archived and once the defined delay has elapsed, MRP0 will apply the redo to the standby database.

Step 3 above is the entire reason we want to use Standby Redo Logs. If we are in Max Protect (SYNC) mode, then SRLs are required otherwise this process will not work. If we are in Max Performance mode, will still want SRLs. Why? We want SRLs to be configured, even in Max Performance mode because they reduce data loss to seconds, rather than minutes or hours. Max Performance mode with SRLs often achieves a near-zero data loss solution. Take a minute to go back and re-read the underlined portions again. The underlined passage above is why you want to configure SRLs if you are in Max Performance mode.  The other big benefit to SRLs is when Real Time Apply is being performed. As soon as the redo is in the SRL, it can be replayed on the standby database. We do not have to wait for a log switch to occur. Real Time Apply, only possible with SRLs, means the recovery time to open the stnadby database in a failover operation is as low as it can be.

It is often find that people are operating under the misconception that if you configure for ASYNC, configure for Max Performance, then only ARCn can transport redo from the primary to the standby. This used to be true in much older versions, but in 10g (maybe 9i), ARCn is only used to transport redo only if SRLs are not configured.Also sometimes if the communication between primary and standby are interrupted(standby down for a while)ARCH process will send the archived redo logs to RFS process directly to resolve the archive log gap. If SRLs are configured, then for ASYNC, NSAn is used to transport redo. Furthermore, NSAn does this in near real time. 

Note: The NSSn and NSAn processes are new to Oracle 12c. In prior versions, a singular process, LNS performed this job.

Without SRLs, we must wait for a log switch to occur on the primary before the redo can be transported. If it takes one hour for the log switch to occur, then we can have one hour’s worth of data loss. If it takes six hours for that log switch to occur, then I can have six hour’s worth of data loss. This behavior was improved by implementing the ARCHIVE_LAG_TARGET initialization parameter in their primary configuration. If the DBA set this parameter to 3600 seconds, then a log switch would occur at most once per hour. Even with this parameter, one hour of data loss may seem like a lot to most companies, especially when you do better. 

Soource:- https://community.oracle.com/docs/DOC-1007036

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.