ORA-25153: Temporary Tablespace is Empty

Cause :- You have no temp file in your temporary tablespace.

Solution:- Check your temporary tablespace and add temp file.

SQL> FLASHBACK TABLE A1 TO TIMESTAMP (TO_DATE('20-JUL-14 11:54:36','DD-MON-YY HH:MI:SS'));
FLASHBACK TABLE A1 TO TIMESTAMP (TO_DATE('20-JUL-14 11:54:36','DD-MON-YY HH:MI:SS'))
                *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-25153: Temporary Tablespace is Empty

SQL> SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE '%DEFAULT_TEMP_TABLESPACE%';

PROPERTY_VALUE
--------------------------------------------------------------------------------
TEMP

SQL> SELECT NAME FROM V$TEMPFILE;

no rows selected

SQL> ALTER TABLESPACE TEMP
  2  ADD TEMPFILE 'D:\APP\ORADATA\ORCL\TEMP02.DBF' SIZE 10M AUTOEXTEND ON;

Tablespace altered.

SQL> SELECT NAME FROM V$TEMPFILE;

NAME
--------------------------------------------------------------------------------
D:\APP\ORADATA\ORCL\TEMP02.DBF

SQL> FLASHBACK TABLE A1 TO TIMESTAMP (TO_DATE('20-JUL-14 11:54:36','DD-MON-YY HH:MI:SS'));

Flashback complete.

SQL>

Take RMAN backup using allocation chennel.

RMAN Scripts ....................................

RUN
 {
  ALLOCATE CHANNEL CH1 TYPE DISK FORMAT 'K:\ORCL_RMAN_BACKUP\RMAN_BKP_DF1_%d_%t_%s_%p_%U';
  ALLOCATE CHANNEL CH2 TYPE DISK FORMAT 'K:\ORCL_RMAN_BACKUP\RMAN_BKP_DF2_%d_%t_%s_%p_%U';
  ALLOCATE CHANNEL CH3 TYPE DISK FORMAT 'K:\ORCL_RMAN_BACKUP\RMAN_BKP_DF3_%d_%t_%s_%p_%U';
  ALLOCATE CHANNEL CH4 TYPE DISK FORMAT 'K:\ORCL_RMAN_BACKUP\RMAN_BKP_DF4_%d_%t_%s_%p_%U';
  ALLOCATE CHANNEL CH5 TYPE DISK FORMAT 'K:\ORCL_RMAN_BACKUP\RMAN_BKP_DF5_%d_%t_%s_%p_%U';
  ALLOCATE CHANNEL CH6 TYPE DISK FORMAT 'K:\ORCL_RMAN_BACKUP\RMAN_BKP_DF6_%d_%t_%s_%p_%U';
  ALLOCATE CHANNEL CH7 TYPE DISK FORMAT 'K:\ORCL_RMAN_BACKUP\RMAN_BKP_DF7_%d_%t_%s_%p_%U';
  ALLOCATE CHANNEL CH8 TYPE DISK FORMAT 'K:\ORCL_RMAN_BACKUP\RMAN_BKP_DF8_%d_%t_%s_%p_%U';
  ALLOCATE CHANNEL CH9 TYPE DISK FORMAT 'K:\ORCL_RMAN_BACKUP\RMAN_BKP_DF9_%d_%t_%s_%p_%U';
  ALLOCATE CHANNEL CH10 TYPE DISK FORMAT 'K:\ORCL_RMAN_BACKUP\RMAN_BKP_DF10_%d_%t_%s_%p_%U';
  SET LIMIT CHANNEL CH1 KBYTES 2097152;
  SET LIMIT CHANNEL CH2 KBYTES 2097152;
  SET LIMIT CHANNEL CH3 KBYTES 2097152;
  SET LIMIT CHANNEL CH4 KBYTES 2097152;
  SET LIMIT CHANNEL CH5 KBYTES 2097152;
  SET LIMIT CHANNEL CH6 KBYTES 2097152;
  SET LIMIT CHANNEL CH7 KBYTES 2097152;
  SET LIMIT CHANNEL CH8 KBYTES 2097152;
  SET LIMIT CHANNEL CH9 KBYTES 2097152;
  SET LIMIT CHANNEL CH10 KBYTES 2097152;
  BACKUP DATABASE;
  SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT';
  BACKUP ARCHIVELOG ALL FORMAT 'K:\ORCL_RMAN_BACKUP\RMAN_ARCHIVE_%d_%t_%s_%p_%U' DELETE INPUT;
  BACKUP CURRENT CONTROLFILE
  TAG = CF1
  FORMAT 'K:\ORCL_RMAN_BACKUP\RMAN_CONTRL_%d_%t_%s_%p_%U';
 }




Output :-
=====================================================================

RMAN> RUN
2>  {
3>   ALLOCATE CHANNEL CH1 TYPE DISK FORMAT 'K:\ORCL_RMAN_BACKUP\RMAN_BKP_DF1_%d_%t_%s_%p_%U';
4>   ALLOCATE CHANNEL CH2 TYPE DISK FORMAT 'K:\ORCL_RMAN_BACKUP\RMAN_BKP_DF2_%d_%t_%s_%p_%U';
5>   ALLOCATE CHANNEL CH3 TYPE DISK FORMAT 'K:\ORCL_RMAN_BACKUP\RMAN_BKP_DF3_%d_%t_%s_%p_%U';
6>   ALLOCATE CHANNEL CH4 TYPE DISK FORMAT 'K:\ORCL_RMAN_BACKUP\RMAN_BKP_DF4_%d_%t_%s_%p_%U';
7>   ALLOCATE CHANNEL CH5 TYPE DISK FORMAT 'K:\ORCL_RMAN_BACKUP\RMAN_BKP_DF5_%d_%t_%s_%p_%U';
8>   ALLOCATE CHANNEL CH6 TYPE DISK FORMAT 'K:\ORCL_RMAN_BACKUP\RMAN_BKP_DF6_%d_%t_%s_%p_%U';
9>   ALLOCATE CHANNEL CH7 TYPE DISK FORMAT 'K:\ORCL_RMAN_BACKUP\RMAN_BKP_DF7_%d_%t_%s_%p_%U';
10>   ALLOCATE CHANNEL CH8 TYPE DISK FORMAT 'K:\ORCL_RMAN_BACKUP\RMAN_BKP_DF8_%d_%t_%s_%p_%U';
11>   ALLOCATE CHANNEL CH9 TYPE DISK FORMAT 'K:\ORCL_RMAN_BACKUP\RMAN_BKP_DF9_%d_%t_%s_%p_%U';
12>   ALLOCATE CHANNEL CH10 TYPE DISK FORMAT 'K:\ORCL_RMAN_BACKUP\RMAN_BKP_DF10_%d_%t_%s_%p_%U';
13>   SET LIMIT CHANNEL CH1 KBYTES 2097152;
14>   SET LIMIT CHANNEL CH2 KBYTES 2097152;
15>   SET LIMIT CHANNEL CH3 KBYTES 2097152;
16>   SET LIMIT CHANNEL CH4 KBYTES 2097152;
17>   SET LIMIT CHANNEL CH5 KBYTES 2097152;
18>   SET LIMIT CHANNEL CH6 KBYTES 2097152;
19>   SET LIMIT CHANNEL CH7 KBYTES 2097152;
20>   SET LIMIT CHANNEL CH8 KBYTES 2097152;
21>   SET LIMIT CHANNEL CH9 KBYTES 2097152;
22>   SET LIMIT CHANNEL CH10 KBYTES 2097152;
23>   BACKUP DATABASE;
24>   SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT';
25>   BACKUP ARCHIVELOG ALL FORMAT 'K:\ORCL_RMAN_BACKUP\RMAN_ARCHIVE_%d_%t_%s_%p_%U' DELETE INPUT;
26>   BACKUP CURRENT CONTROLFILE
27>   TAG = CF1
28>   FORMAT 'K:\ORCL_RMAN_BACKUP\RMAN_CONTRL_%d_%t_%s_%p_%U';
29>  }

using target database control file instead of recovery catalog
allocated channel: CH1
channel CH1: SID=10 device type=DISK

allocated channel: CH2
channel CH2: SID=9 device type=DISK

allocated channel: CH3
channel CH3: SID=133 device type=DISK

allocated channel: CH4
channel CH4: SID=196 device type=DISK

allocated channel: CH5
channel CH5: SID=12 device type=DISK

allocated channel: CH6
channel CH6: SID=74 device type=DISK

allocated channel: CH7
channel CH7: SID=135 device type=DISK

allocated channel: CH8
channel CH8: SID=198 device type=DISK

allocated channel: CH9
channel CH9: SID=5 device type=DISK

allocated channel: CH10
channel CH10: SID=73 device type=DISK











Starting backup at 18-JUL-14
channel CH1: starting full datafile backup set
channel CH1: specifying datafile(s) in backup set
input datafile file number=00008 name=D:\APP\ORADATA\SPFTL\TBFES.DBF
input datafile file number=00012 name=D:\APP\ORADATA\SPFTL\MIG_BCBL01.DBF
channel CH1: starting piece 1 at 18-JUL-14
channel CH2: starting full datafile backup set
channel CH2: specifying datafile(s) in backup set
input datafile file number=00023 name=D:\APP\ORADATA\STLBAS\ORBIMG01.DBF
input datafile file number=00029 name=D:\APP\ORADATA\ORCL\RND_TBS.DBF
input datafile file number=00016 name=D:\APP\ORADATA\STLBAS\ORBSYS01.DBF
channel CH2: starting piece 1 at 18-JUL-14
channel CH3: starting full datafile backup set
channel CH3: specifying datafile(s) in backup set
input datafile file number=00022 name=D:\APP\ORADATA\STLBAS\ORBIND01.DBF
input datafile file number=00027 name=D:\APP\ORADATA\SPFTL\SHRINK_TEST.DBF
input datafile file number=00021 name=D:\APP\ORADATA\STLBAS\ORBMAV01.DBF
channel CH3: starting piece 1 at 18-JUL-14
channel CH4: starting full datafile backup set
channel CH4: specifying datafile(s) in backup set
input datafile file number=00003 name=D:\APP\ORADATA\ORCL\UNDOTBS01.DBF
input datafile file number=00013 name=D:\APP\ORADATA\SPFTL\STLBAS01.DBF
input datafile file number=00024 name=D:\APP\ORADATA\STLBAS\ORBBBR01.DBF
channel CH4: starting piece 1 at 18-JUL-14
channel CH5: starting full datafile backup set
channel CH5: specifying datafile(s) in backup set
input datafile file number=00026 name=D:\APP\ORADATA\STLBAS\MYBANK01.DBF
input datafile file number=00002 name=D:\APP\ORADATA\ORCL\SYSAUX01.DBF
input datafile file number=00020 name=D:\APP\ORADATA\STLBAS\ORBLOG01.DBF
channel CH5: starting piece 1 at 18-JUL-14
channel CH6: starting full datafile backup set
channel CH6: specifying datafile(s) in backup set
input datafile file number=00010 name=D:\APP\ORADATA\SPFTL\DATA.DBF
input datafile file number=00007 name=D:\APP\ORADATA\SPFTL\TRBFES.DBF
input datafile file number=00028 name=D:\APP\ORADATA\ORCL\RECLAIM01.DBF
channel CH6: starting piece 1 at 18-JUL-14
channel CH7: starting full datafile backup set
channel CH7: specifying datafile(s) in backup set
input datafile file number=00001 name=D:\APP\ORADATA\ORCL\SYSTEM01.DBF
input datafile file number=00015 name=D:\APP\ORADATA\STLBAS\ORBMAS01.DBF
input datafile file number=00006 name=D:\APP\ORADATA\SPFTL\TBACNTS.DBF
channel CH7: starting piece 1 at 18-JUL-14
channel CH8: starting full datafile backup set
channel CH8: specifying datafile(s) in backup set
input datafile file number=00014 name=D:\APP\ORADATA\STLBAS\ORBITS01.DBF
input datafile file number=00025 name=D:\APP\ORADATA\ORCL\APEX_DATA01.DBF
input datafile file number=00011 name=D:\APP\ORADATA\SPFTL\TBSTRAN .DBF
channel CH8: starting piece 1 at 18-JUL-14
channel CH9: starting full datafile backup set
channel CH9: specifying datafile(s) in backup set
input datafile file number=00004 name=D:\APP\ORADATA\ORCL\USERS01.DBF
input datafile file number=00005 name=D:\APP\ORADATA\ORCL\EXAMPLE01.DBF
input datafile file number=00009 name=D:\APP\ORADATA\SPFTL\TBAML.DBF
channel CH9: starting piece 1 at 18-JUL-14
channel CH10: starting full datafile backup set
channel CH10: specifying datafile(s) in backup set
input datafile file number=00019 name=D:\APP\ORADATA\STLBAS\ORBDT301.DBF
input datafile file number=00018 name=D:\APP\ORADATA\STLBAS\ORBDT201.DBF
input datafile file number=00017 name=D:\APP\ORADATA\STLBAS\ORBDT101.DBF
channel CH10: starting piece 1 at 18-JUL-14
channel CH4: finished piece 1 at 18-JUL-14
piece handle=K:\ORCL_RMAN_BACKUP\RMAN_BKP_DF4_ORCL_853204137_24_1_0OPDLN59_1_1 tag=TAG20140718T010852 comment=NONE
channel CH4: backup set complete, elapsed time: 00:01:30
channel CH8: finished piece 1 at 18-JUL-14
piece handle=K:\ORCL_RMAN_BACKUP\RMAN_BKP_DF8_ORCL_853204150_28_1_0SPDLN5M_1_1 tag=TAG20140718T010852 comment=NONE
channel CH8: backup set complete, elapsed time: 00:05:28
channel CH3: finished piece 1 at 18-JUL-14
piece handle=K:\ORCL_RMAN_BACKUP\RMAN_BKP_DF3_ORCL_853204136_23_1_0NPDLN58_1_1 tag=TAG20140718T010852 comment=NONE
channel CH3: backup set complete, elapsed time: 00:08:22
channel CH6: finished piece 1 at 18-JUL-14
piece handle=K:\ORCL_RMAN_BACKUP\RMAN_BKP_DF6_ORCL_853204144_26_1_0QPDLN5G_1_1 tag=TAG20140718T010852 comment=NONE
channel CH6: backup set complete, elapsed time: 00:08:21
channel CH10: finished piece 1 at 18-JUL-14
piece handle=K:\ORCL_RMAN_BACKUP\RMAN_BKP_DF10_ORCL_853204153_30_1_0UPDLN5P_1_1 tag=TAG20140718T010852 comment=NONE
channel CH10: backup set complete, elapsed time: 00:09:05
channel CH9: finished piece 1 at 18-JUL-14
piece handle=K:\ORCL_RMAN_BACKUP\RMAN_BKP_DF9_ORCL_853204151_29_1_0TPDLN5N_1_1 tag=TAG20140718T010852 comment=NONE
channel CH9: backup set complete, elapsed time: 00:09:17
channel CH5: finished piece 1 at 18-JUL-14
piece handle=K:\ORCL_RMAN_BACKUP\RMAN_BKP_DF5_ORCL_853204139_25_1_0PPDLN5B_1_1 tag=TAG20140718T010852 comment=NONE
channel CH5: backup set complete, elapsed time: 00:11:16
channel CH7: finished piece 1 at 18-JUL-14
piece handle=K:\ORCL_RMAN_BACKUP\RMAN_BKP_DF7_ORCL_853204148_27_1_0RPDLN5K_1_1 tag=TAG20140718T010852 comment=NONE
channel CH7: backup set complete, elapsed time: 00:11:29
channel CH1: finished piece 1 at 18-JUL-14
piece handle=K:\ORCL_RMAN_BACKUP\RMAN_BKP_DF1_ORCL_853204134_21_1_0LPDLN56_1_1 tag=TAG20140718T010852 comment=NONE
channel CH1: starting piece 2 at 18-JUL-14
channel CH2: finished piece 1 at 18-JUL-14
piece handle=K:\ORCL_RMAN_BACKUP\RMAN_BKP_DF2_ORCL_853204135_22_1_0MPDLN57_1_1 tag=TAG20140718T010852 comment=NONE
channel CH2: starting piece 2 at 18-JUL-14
channel CH2: finished piece 2 at 18-JUL-14
piece handle=K:\ORCL_RMAN_BACKUP\RMAN_BKP_DF2_ORCL_853204135_22_2_0MPDLN57_2_1 tag=TAG20140718T010852 comment=NONE
channel CH2: backup set complete, elapsed time: 00:14:00
channel CH1: finished piece 2 at 18-JUL-14
piece handle=K:\ORCL_RMAN_BACKUP\RMAN_BKP_DF1_ORCL_853204134_21_2_0LPDLN56_2_1 tag=TAG20140718T010852 comment=NONE
channel CH1: starting piece 3 at 18-JUL-14
channel CH1: finished piece 3 at 18-JUL-14
piece handle=K:\ORCL_RMAN_BACKUP\RMAN_BKP_DF1_ORCL_853204134_21_3_0LPDLN56_3_1 tag=TAG20140718T010852 comment=NONE
channel CH1: backup set complete, elapsed time: 00:16:16
Finished backup at 18-JUL-14

Starting Control File and SPFILE Autobackup at 18-JUL-14
piece handle=K:\ORCL_RMAN_BACKUP\BKP_CF_C-1350456456-20140718-01 comment=NONE
Finished Control File and SPFILE Autobackup at 18-JUL-14

sql statement: ALTER SYSTEM ARCHIVE LOG CURRENT

Starting backup at 18-JUL-14
current log archived
channel CH1: starting archived log backup set
channel CH1: specifying archived log(s) in backup set
input archived log thread=1 sequence=9 RECID=12 STAMP=853205123
channel CH1: starting piece 1 at 18-JUL-14
channel CH2: starting archived log backup set
channel CH2: specifying archived log(s) in backup set
input archived log thread=1 sequence=10 RECID=13 STAMP=853205124
channel CH2: starting piece 1 at 18-JUL-14
channel CH1: finished piece 1 at 18-JUL-14
piece handle=K:\ORCL_RMAN_BACKUP\RMAN_ARCHIVE_ORCL_853205125_32_1_10PDLO45_1_1 tag=TAG20140718T012524 comment=NONE
channel CH1: backup set complete, elapsed time: 00:00:02
channel CH1: deleting archived log(s)
archived log file name=D:\APP\ORADATA\ARCHIVE\ARC0000000009_0853199878.0001 RECID=12 STAMP=853205123
channel CH2: finished piece 1 at 18-JUL-14
piece handle=K:\ORCL_RMAN_BACKUP\RMAN_ARCHIVE_ORCL_853205125_33_1_11PDLO45_1_1 tag=TAG20140718T012524 comment=NONE
channel CH2: backup set complete, elapsed time: 00:00:02
channel CH2: deleting archived log(s)
archived log file name=D:\APP\ORADATA\ARCHIVE\ARC0000000010_0853199878.0001 RECID=13 STAMP=853205124
Finished backup at 18-JUL-14

Starting backup at 18-JUL-14
channel CH1: starting full datafile backup set
channel CH1: specifying datafile(s) in backup set
including current control file in backup set
channel CH1: starting piece 1 at 18-JUL-14
channel CH1: finished piece 1 at 18-JUL-14
piece handle=K:\ORCL_RMAN_BACKUP\RMAN_CONTRL_ORCL_853205133_34_1_12PDLO4D_1_1 tag=CF1 comment=NONE
channel CH1: backup set complete, elapsed time: 00:00:01
Finished backup at 18-JUL-14

Starting Control File and SPFILE Autobackup at 18-JUL-14
piece handle=K:\ORCL_RMAN_BACKUP\BKP_CF_C-1350456456-20140718-02 comment=NONE
Finished Control File and SPFILE Autobackup at 18-JUL-14
released channel: CH1
released channel: CH2
released channel: CH3
released channel: CH4
released channel: CH5
released channel: CH6
released channel: CH7
released channel: CH8
released channel: CH9
released channel: CH10

RMAN>

ORA-01196: file 1 is inconsistent due to a failed media recovery session

Cause :-
1. Unfortunately I have delete all data from a table. Table row movement was not enable. But I need to retrieve this data.
2. For this reason I want to flashback database but I have also delete some archive log files from my system. This the reason flashback are not completed.
3. When I want to open database from mounted mode i got this error. 

SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: 'D:\APP\ORADATA\ORCL\SYSTEM01.DBF'


Solution :- 
1. First take full database backup using RMAN from mount mode.
2. RESTORE DATABASE From RMAN.
3. Now RECOVER DATABASE From RMAN. It can get error. If you get an error open database using resetlogs from isqlplus.


If you can not take backup and restore you can try after create control file.


RMAN> SHOW ALL;

RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'D:\app\oradata\ORCL_RMAN_BACKUP\bkp_cf_%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
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   'D:\app\oradata\ORCL_RMAN_BACKUP\bkp.%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 ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\APP\PRODUCT\11.2.0\DBHOME_1\DATABASE\SNCFORCL.ORA'; # default

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'K:\ORCL_RMAN_BACKUP\bkp_cf_%F';

old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'D:\app\oradata\ORCL_RMAN_BACKUP\bkp_cf_%F';
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'K:\ORCL_RMAN_BACKUP\bkp_cf_%F';
new RMAN configuration parameters are successfully stored

RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'K:\ORCL_RMAN_BACKUP\bkp.%U';

old RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   'D:\app\oradata\ORCL_RMAN_BACKUP\bkp.%U';
new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   'K:\ORCL_RMAN_BACKUP\bkp.%U';
new RMAN configuration parameters are successfully stored
released channel: ORA_DISK_1

RMAN>  backup incremental level 0 database;

Starting backup at 17-JUL-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=129 device type=DISK
channel ORA_DISK_1: starting incremental level 0 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00008 name=D:\APP\ORADATA\SPFTL\TBFES.DBF
input datafile file number=00023 name=D:\APP\ORADATA\STLBAS\ORBIMG01.DBF
input datafile file number=00022 name=D:\APP\ORADATA\STLBAS\ORBIND01.DBF
input datafile file number=00003 name=D:\APP\ORADATA\ORCL\UNDOTBS01.DBF
input datafile file number=00010 name=D:\APP\ORADATA\SPFTL\DATA.DBF
input datafile file number=00001 name=D:\APP\ORADATA\ORCL\SYSTEM01.DBF
input datafile file number=00004 name=D:\APP\ORADATA\ORCL\USERS01.DBF
input datafile file number=00019 name=D:\APP\ORADATA\STLBAS\ORBDT301.DBF
input datafile file number=00026 name=D:\APP\ORADATA\STLBAS\MYBANK01.DBF
input datafile file number=00014 name=D:\APP\ORADATA\STLBAS\ORBITS01.DBF
input datafile file number=00025 name=D:\APP\ORADATA\ORCL\APEX_DATA01.DBF
input datafile file number=00002 name=D:\APP\ORADATA\ORCL\SYSAUX01.DBF
input datafile file number=00018 name=D:\APP\ORADATA\STLBAS\ORBDT201.DBF
input datafile file number=00017 name=D:\APP\ORADATA\STLBAS\ORBDT101.DBF
input datafile file number=00005 name=D:\APP\ORADATA\ORCL\EXAMPLE01.DBF
input datafile file number=00015 name=D:\APP\ORADATA\STLBAS\ORBMAS01.DBF
input datafile file number=00020 name=D:\APP\ORADATA\STLBAS\ORBLOG01.DBF
input datafile file number=00007 name=D:\APP\ORADATA\SPFTL\TRBFES.DBF
input datafile file number=00009 name=D:\APP\ORADATA\SPFTL\TBAML.DBF
input datafile file number=00011 name=D:\APP\ORADATA\SPFTL\TBSTRAN .DBF
input datafile file number=00028 name=D:\APP\ORADATA\ORCL\RECLAIM01.DBF
input datafile file number=00006 name=D:\APP\ORADATA\SPFTL\TBACNTS.DBF
input datafile file number=00013 name=D:\APP\ORADATA\SPFTL\STLBAS01.DBF
input datafile file number=00024 name=D:\APP\ORADATA\STLBAS\ORBBBR01.DBF
input datafile file number=00027 name=D:\APP\ORADATA\SPFTL\SHRINK_TEST.DBF
input datafile file number=00029 name=D:\APP\ORADATA\ORCL\RND_TBS.DBF
input datafile file number=00021 name=D:\APP\ORADATA\STLBAS\ORBMAV01.DBF
input datafile file number=00016 name=D:\APP\ORADATA\STLBAS\ORBSYS01.DBF
input datafile file number=00012 name=D:\APP\ORADATA\SPFTL\MIG_BCBL01.DBF
channel ORA_DISK_1: starting piece 1 at 17-JUL-14
channel ORA_DISK_1: finished piece 1 at 17-JUL-14
piece handle=K:\ORCL_RMAN_BACKUP\BKP.04PDLFP7_1_1 tag=TAG20140717T230301 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:25:55
Finished backup at 17-JUL-14

RMAN-06497: WARNING: control file is not current, control file AUTOBACKUP skipped

RMAN> SHUTDOWN ABORT;

Oracle instance shut down

RMAN> STARTUP FORCE MOUNT;

Oracle instance started
database mounted

Total System Global Area     753278976 bytes

Fixed Size                     1374724 bytes
Variable Size                520095228 bytes
Database Buffers             226492416 bytes
Redo Buffers                   5316608 bytes

RMAN> RESTORE DATABASE;

Starting restore at 17-JUL-14
using channel ORA_DISK_1

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 00001 to D:\APP\ORADATA\ORCL\SYSTEM01.DBF
channel ORA_DISK_1: restoring datafile 00002 to D:\APP\ORADATA\ORCL\SYSAUX01.DBF
channel ORA_DISK_1: restoring datafile 00003 to D:\APP\ORADATA\ORCL\UNDOTBS01.DBF
channel ORA_DISK_1: restoring datafile 00004 to D:\APP\ORADATA\ORCL\USERS01.DBF
channel ORA_DISK_1: restoring datafile 00005 to D:\APP\ORADATA\ORCL\EXAMPLE01.DBF
channel ORA_DISK_1: restoring datafile 00006 to D:\APP\ORADATA\SPFTL\TBACNTS.DBF
channel ORA_DISK_1: restoring datafile 00007 to D:\APP\ORADATA\SPFTL\TRBFES.DBF
channel ORA_DISK_1: restoring datafile 00008 to D:\APP\ORADATA\SPFTL\TBFES.DBF
channel ORA_DISK_1: restoring datafile 00009 to D:\APP\ORADATA\SPFTL\TBAML.DBF
channel ORA_DISK_1: restoring datafile 00010 to D:\APP\ORADATA\SPFTL\DATA.DBF
channel ORA_DISK_1: restoring datafile 00011 to D:\APP\ORADATA\SPFTL\TBSTRAN .DBF
channel ORA_DISK_1: restoring datafile 00012 to D:\APP\ORADATA\SPFTL\MIG_BCBL01.DBF
channel ORA_DISK_1: restoring datafile 00013 to D:\APP\ORADATA\SPFTL\STLBAS01.DBF
channel ORA_DISK_1: restoring datafile 00014 to D:\APP\ORADATA\STLBAS\ORBITS01.DBF
channel ORA_DISK_1: restoring datafile 00015 to D:\APP\ORADATA\STLBAS\ORBMAS01.DBF
channel ORA_DISK_1: restoring datafile 00016 to D:\APP\ORADATA\STLBAS\ORBSYS01.DBF
channel ORA_DISK_1: restoring datafile 00017 to D:\APP\ORADATA\STLBAS\ORBDT101.DBF
channel ORA_DISK_1: restoring datafile 00018 to D:\APP\ORADATA\STLBAS\ORBDT201.DBF
channel ORA_DISK_1: restoring datafile 00019 to D:\APP\ORADATA\STLBAS\ORBDT301.DBF
channel ORA_DISK_1: restoring datafile 00020 to D:\APP\ORADATA\STLBAS\ORBLOG01.DBF
channel ORA_DISK_1: restoring datafile 00021 to D:\APP\ORADATA\STLBAS\ORBMAV01.DBF
channel ORA_DISK_1: restoring datafile 00022 to D:\APP\ORADATA\STLBAS\ORBIND01.DBF
channel ORA_DISK_1: restoring datafile 00023 to D:\APP\ORADATA\STLBAS\ORBIMG01.DBF
channel ORA_DISK_1: restoring datafile 00024 to D:\APP\ORADATA\STLBAS\ORBBBR01.DBF
channel ORA_DISK_1: restoring datafile 00025 to D:\APP\ORADATA\ORCL\APEX_DATA01.DBF
channel ORA_DISK_1: restoring datafile 00026 to D:\APP\ORADATA\STLBAS\MYBANK01.DBF
channel ORA_DISK_1: restoring datafile 00027 to D:\APP\ORADATA\SPFTL\SHRINK_TEST.DBF
channel ORA_DISK_1: restoring datafile 00028 to D:\APP\ORADATA\ORCL\RECLAIM01.DBF
channel ORA_DISK_1: restoring datafile 00029 to D:\APP\ORADATA\ORCL\RND_TBS.DBF
channel ORA_DISK_1: reading from backup piece K:\ORCL_RMAN_BACKUP\BKP.04PDLFP7_1_1
channel ORA_DISK_1: piece handle=K:\ORCL_RMAN_BACKUP\BKP.04PDLFP7_1_1 tag=TAG20140717T230301
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:16:05
Finished restore at 17-JUL-14

RMAN> RECOVER DATABASE;

Starting recover at 17-JUL-14
using channel ORA_DISK_1

starting media recovery

unable to find archived log
archived log thread=1 sequence=1556
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/17/2014 23:56:09
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 1556 and starting SCN of 390117984

RMAN>

SQL> alter database open resetlogs;

Database altered.

SQL> select status from v$instance;

STATUS
------------
OPEN

Creating Control File in oracle database.

We can create control file using backup scrips of current control file.

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS 'D:\APP\CONTROL_FILE.SQL';

Now open your CONTROL_FILE.SQL and copy the following line and run from isqlplus.

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 'D:\APP\ORADATA\ORCL\REDO01.LOG'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 'D:\APP\ORADATA\ORCL\REDO02.LOG'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 'D:\APP\ORADATA\ORCL\REDO03.LOG'  SIZE 50M BLOCKSIZE 512
DATAFILE
  'D:\APP\ORADATA\ORCL\SYSTEM01.DBF',
  'D:\APP\ORADATA\ORCL\SYSAUX01.DBF',
  'D:\APP\ORADATA\ORCL\UNDOTBS01.DBF',
  'D:\APP\ORADATA\ORCL\USERS01.DBF',
  'D:\APP\ORADATA\ORCL\EXAMPLE01.DBF',
  'D:\APP\ORADATA\SPFTL\TBACNTS.DBF',
  'D:\APP\ORADATA\SPFTL\TRBFES.DBF',
  'D:\APP\ORADATA\SPFTL\TBFES.DBF',
  'D:\APP\ORADATA\SPFTL\TBAML.DBF',
  'D:\APP\ORADATA\SPFTL\DATA.DBF',
  'D:\APP\ORADATA\SPFTL\TBSTRAN .DBF',
  'D:\APP\ORADATA\SPFTL\MIG_BCBL01.DBF',
  'D:\APP\ORADATA\SPFTL\STLBAS01.DBF',
  'D:\APP\ORADATA\STLBAS\ORBITS01.DBF',
  'D:\APP\ORADATA\STLBAS\ORBMAS01.DBF',
  'D:\APP\ORADATA\STLBAS\ORBSYS01.DBF',
  'D:\APP\ORADATA\STLBAS\ORBDT101.DBF',
  'D:\APP\ORADATA\STLBAS\ORBDT201.DBF',
  'D:\APP\ORADATA\STLBAS\ORBDT301.DBF',
  'D:\APP\ORADATA\STLBAS\ORBLOG01.DBF',
  'D:\APP\ORADATA\STLBAS\ORBMAV01.DBF',
  'D:\APP\ORADATA\STLBAS\ORBIND01.DBF',
  'D:\APP\ORADATA\STLBAS\ORBIMG01.DBF',
  'D:\APP\ORADATA\STLBAS\ORBBBR01.DBF',
  'D:\APP\ORADATA\ORCL\APEX_DATA01.DBF',
  'D:\APP\ORADATA\STLBAS\MYBANK01.DBF',
  'D:\APP\ORADATA\SPFTL\SHRINK_TEST.DBF',
  'D:\APP\ORADATA\ORCL\RECLAIM01.DBF',
  'D:\APP\ORADATA\ORCL\RND_TBS.DBF'
CHARACTER SET WE8MSWIN1252
;

Another way to create control file you need to collect control file information form data dictionary view.

SELECT MEMBER FROM V$LOGFILE;
SELECT NAME FROM V$DATAFILE; 
SELECT VALUE FROM V$PARAMETER WHERE NAME = 'CONTROL_FILES';

After collect information create control file from nomount mode.

CREATE CONTROLFILE
   SET DATABASE prod
   LOGFILE GROUP 1 ('/u01/oracle/prod/redo01_01.log', 
                    '/u01/oracle/prod/redo01_02.log'),
           GROUP 2 ('/u01/oracle/prod/redo02_01.log', 
                    '/u01/oracle/prod/redo02_02.log'),
           GROUP 3 ('/u01/oracle/prod/redo03_01.log', 
                    '/u01/oracle/prod/redo03_02.log') 
   NORESETLOGS
   DATAFILE '/u01/oracle/prod/system01.dbf' SIZE 3M,
            '/u01/oracle/prod/rbs01.dbs' SIZE 5M,
            '/u01/oracle/prod/users01.dbs' SIZE 5M,
            '/u01/oracle/prod/temp01.dbs' SIZE 5M
   MAXLOGFILES 50
   MAXLOGMEMBERS 3
   MAXLOGHISTORY 400
   MAXDATAFILES 200
   MAXINSTANCES 6
   ARCHIVELOG; 

Restore and Recover Database From RMAN Backup.

C:\Users\rajib.pradhan>SET ORACLE_SID=DBRMAN

C:\Users\rajib.pradhan>RMAN

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Jul 16 16:21:34 2014

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

RMAN> CONNECT TARGET /

connected to target database (not started)

RMAN> STARTUP FORCE MOUNT;

Oracle instance started
database mounted

Total System Global Area     535662592 bytes

Fixed Size                     1375792 bytes
Variable Size                478151120 bytes
Database Buffers              50331648 bytes
Redo Buffers                   5804032 bytes

RMAN> RESTORE DATABASE;

Starting restore at 16-JUL-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=129 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 00001 to D:\APP\ORADATA\DBRMAN\SYSTEM01.DBF
channel ORA_DISK_1: restoring datafile 00002 to D:\APP\ORADATA\DBRMAN\SYSAUX01.DBF
channel ORA_DISK_1: restoring datafile 00003 to D:\APP\ORADATA\DBRMAN\UNDOTBS01.DBF
channel ORA_DISK_1: restoring datafile 00004 to D:\APP\ORADATA\DBRMAN\USERS01.DBF
channel ORA_DISK_1: restoring datafile 00005 to D:\APP\ORADATA\DBRMAN\CATALOG_SPC01.DBF
channel ORA_DISK_1: restoring datafile 00006 to D:\APP\ORADATA\DBRMAN\TBS_RAMN_CAT_01.DBF
channel ORA_DISK_1: restoring datafile 00008 to D:\APP\ORADATA\DBRMAN\BEFTN01.DBF
channel ORA_DISK_1: restoring datafile 00009 to D:\APP\ORADATA\DBRMAN\MICR01.DBF
channel ORA_DISK_1: restoring datafile 00010 to D:\APP\ORADATA\DBRMAN\EXAMPLE01.DBF
channel ORA_DISK_1: restoring datafile 00011 to D:\APP\ORADATA\DBRMAN\MICRACK01.DBF
channel ORA_DISK_1: restoring datafile 00012 to D:\APP\ORADATA\DBRMAN\MICRDFT01.DBF
channel ORA_DISK_1: restoring datafile 00013 to D:\APP\ORADATA\DBRMAN\MICR_IMG01.DBF
channel ORA_DISK_1: restoring datafile 00014 to D:\APP\ORADATA\DBRMAN\MICRIND01.DBF
channel ORA_DISK_1: restoring datafile 00015 to D:\APP\ORADATA\DBRMAN\MICRINW01.DBF
channel ORA_DISK_1: restoring datafile 00016 to D:\APP\ORADATA\DBRMAN\MICRLOG01.DBF
channel ORA_DISK_1: restoring datafile 00017 to D:\APP\ORADATA\DBRMAN\MICROUT01.DBF
channel ORA_DISK_1: restoring datafile 00018 to D:\APP\ORADATA\DBRMAN\MICRSTP01.DBF
channel ORA_DISK_1: reading from backup piece D:\APP\ORADATA\DBRMAN_BACKUP\BKP_DBRMAN_853084086_53_1_1LPDI1TM
channel ORA_DISK_1: piece handle=D:\APP\ORADATA\DBRMAN_BACKUP\BKP_DBRMAN_853084086_53_1_1LPDI1TM tag=TAG20140716T154806
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:06:16
Finished restore at 16-JUL-14

RMAN> recover database;

Starting recover at 16-JUL-14
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:19

Finished recover at 16-JUL-14

RMAN> ALTER DATABASE OPEN;

database opened

RMAN>

RMAN-06056: could not access datafile 7

Cause :- In my case i can see one of my data file (7) are in offline for this reason I can see this error after taken this file in online I can take complete backup using RMAN. You can also use "SKIP OFFLINE" in your RMAN BACKUP DATABASE command. This error can be for :

1 File does not exist.
2 File is offline.
3 File is not verified.
4 DBWR could not find the file.
5 unable to open file.
6 I/O error during read.
7 File header is corrupt. 
8 File is not a datafile.
9 File does not belong to this database.
10 File number is incorrect.
11 Wrong file version.
12 Control file is not current.

Starting backup at 16-JUL-14
using channel ORA_DISK_1
RMAN-06169: could not read file header for datafile 7 error reason 4
RMAN-06169: could not read file header for datafile 7 error reason 4
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup plus archivelog command at 07/16/2014 14:27:30
RMAN-06056: could not access datafile 7

RMAN>

SQL> select file#, status, enabled from v$datafile;

     FILE# STATUS  ENABLED
---------- ------- ----------
         1 SYSTEM  READ WRITE
         2 ONLINE  READ WRITE
         3 ONLINE  READ WRITE
         4 ONLINE  READ WRITE
         5 ONLINE  READ WRITE
         6 ONLINE  READ WRITE
         7 OFFLINE DISABLED
         8 ONLINE  READ WRITE
         9 ONLINE  READ WRITE
        10 ONLINE  READ WRITE
        11 ONLINE  READ WRITE

     FILE# STATUS  ENABLED
---------- ------- ----------
        12 ONLINE  READ WRITE
        13 ONLINE  READ WRITE
        14 ONLINE  READ WRITE
        15 ONLINE  READ WRITE
        16 ONLINE  READ WRITE
        17 ONLINE  READ WRITE
        18 ONLINE  READ WRITE

18 rows selected.
SQL> ALTER TABLESPACE TEST ONLINE;

Tablespace altered.

 RMAN> BACKUP AS BACKUPSET DATABASE PLUS ARCHIVELOG;

Starting backup at 16-JUL-14
current log archived
using channel ORA_DISK_1
Starting backup at 16-JUL-14
current log archived
using channel ORA_DISK_1
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=114 RECID=116 STAMP=853080236
channel ORA_DISK_1: starting piece 1 at 16-JUL-14
channel ORA_DISK_1: finished piece 1 at 16-JUL-14
piece handle=D:\APP\ORADATA\DBRMAN_BACKUP\BKP.15PDHU5D_1_1 tag=TAG20140716T144356 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 16-JUL-14

Starting Control File and SPFILE Autobackup at 16-JUL-14
piece handle=D:\APP\ORADATA\DBRMAN_BACKUP\BKP_CF_C-3796675194-20140716-02 comment=NONE
Finished Control File and SPFILE Autobackup at 16-JUL-14

RMAN>

Moving or Renaming Data Files While the Database is Online.

C:\Users\rajib.pradhan>SET ORACLE_SID=DBRMAN

C:\Users\rajib.pradhan>SQLPLUS /NOLOG

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jul 14 14:46:17 2014

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

SQL> CONN SYS /AS SYSDBA
Enter password:
Connected.
SQL>
SQL> SELECT FILE_NAME FROM DBA_DATA_FILES;

FILE_NAME
--------------------------------------------------------------------------------
D:\APP\ORADATA\DBRMAN\USERS01.DBF
D:\APP\ORADATA\DBRMAN\UNDOTBS01.DBF
D:\APP\ORADATA\DBRMAN\SYSAUX01.DBF
D:\APP\ORADATA\DBRMAN\SYSTEM01.DBF
D:\APP\ORADATA\DBRMAN\CATALOG_SPC01.DBF
D:\APP\ORADATA\DBRMAN\TBS_RAMN_CAT_01.DBF
D:\APP\ORADATA\DBRMAN\TEST01.DBF

7 rows selected.

SQL> ALTER TABLESPACE TEST OFFLINE;

Tablespace altered.

SQL> CONN TEST/TEST;
Connected.
SQL> SELECT * FROM TAB;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
READ_ONLY                      TABLE
TEST_DATA                      TABLE

SQL> DESC TEST_DATA;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 NAME                                               VARCHAR2(300)

SQL> SELECT TABLE_NAME, TABLESPACE_NAME FROM USER_TABLES
  2  WHERE TABLE_NAME='TEST_DATA';

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
TEST_DATA                      TEST

SQL> INSERT INTO TEST_DATA VALUES(10,'RAJIB');
INSERT INTO TEST_DATA VALUES(10,'RAJIB')
            *
ERROR at line 1:
ORA-00376: file 7 cannot be read at this time
ORA-01110: data file 7: 'D:\APP\ORADATA\TEST01.DBF'


SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\Users\rajib.pradhan>move D:\app\oradata\DBRMAN\TEST01.DBF D:\app\oradata\TEST01.DBF
        1 file(s) moved.

C:\Users\rajib.pradhan>SET ORACLE_SID=DBRMAN

C:\Users\rajib.pradhan>SQLPLUS /NOLOG

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jul 14 14:52:10 2014

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

SQL> CONN SYS /AS SYSDBA
Enter password:
Connected.
SQL>
SQL> ALTER DATABASE RENAME FILE 'D:\APP\ORADATA\DBRMAN\TEST01.DBF' TO 'D:\app\oradata\TEST01.DBF';

Database altered.

SQL> ALTER TABLESPACE TEST ONLINE;

Tablespace altered.

SQL> SELECT FILE_NAME FROM DBA_DATA_FILES;

FILE_NAME
--------------------------------------------------------------------------------
D:\APP\ORADATA\DBRMAN\USERS01.DBF
D:\APP\ORADATA\DBRMAN\UNDOTBS01.DBF
D:\APP\ORADATA\DBRMAN\SYSAUX01.DBF
D:\APP\ORADATA\DBRMAN\SYSTEM01.DBF
D:\APP\ORADATA\DBRMAN\CATALOG_SPC01.DBF
D:\APP\ORADATA\DBRMAN\TBS_RAMN_CAT_01.DBF
D:\APP\ORADATA\TEST01.DBF

7 rows selected.

SQL>

Read Only Table In Oracle 11g Database

In oracle 11g database allowed read only table where INSERT/UPDATE are not allowed, Only select statement allowed in this types of table.


SQL> CONN TEST/TEST;
Connected.
SQL> CREATE TABLE READ_ONLY(ID NUMBER, NAME VARCHAR2(300));

Table created.

SQL> INSERT INTO READ_ONLY VALUES(10, 'RAJIB');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SET LINE 2000;
SQL> /

        ID NAME
---------- -----------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------
        10 RAJIB

SQL> ALTER TABLE READ_ONLY READ ONLY;

Table altered.

SQL> INSERT INTO READ_ONLY VALUES(12, 'TEST USER');
INSERT INTO READ_ONLY VALUES(12, 'TEST USER')
            *
ERROR at line 1:
ORA-12081: update operation not allowed on table "TEST"."READ_ONLY"


SQL> SELECT * FROM READ_ONLY;

        ID NAME
---------- -----------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------
        10 RAJIB

SQL> UPDATE READ_ONLY
  2  SET ID=100;
UPDATE READ_ONLY
       *
ERROR at line 1:
ORA-12081: update operation not allowed on table "TEST"."READ_ONLY"

SQL>

Finding spfile Location from your database.

Using this query you can find your physical spfile location.

SQL> CONN /AS SYSDBA
Connected.
SQL> select value from v$parameter where name = 'spfile';

VALUE
--------------------------------------------------------------------------------
D:\APP\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILEDBRMAN.ORA

SQL>

Configure RMAN in oracle database

C:\Users\rajib.pradhan>SET ORACLE_SID=ORCL

C:\Users\rajib.pradhan>SQLPLUS /NOLOG

SQL*Plus: Release 11.2.0.1.0 Production on Sun Feb 1 01:56:39 2015

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

SQL> CONN /AS SYSDBA
Connected.

1. Checking Archive Log Mode Enable/Disable.
=============================================
SQL> SELECT LOG_MODE FROM V$DATABASE;

LOG_MODE
------------
NOARCHIVELOG

2. Enable Archive Log Mode.
=============================================
SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP MOUNT;
ORACLE instance started.

Total System Global Area 2137886720 bytes
Fixed Size                  2177456 bytes
Variable Size            1291847248 bytes
Database Buffers          838860800 bytes
Redo Buffers                5001216 bytes
Database mounted.
SQL> ALTER DATABASE ARCHIVELOG;

Database altered.

SQL> ALTER DATABASE OPEN;

Database altered.

3. Checking Parameter recovery file
=============================================
SQL> SHOW PARAMETER db_recovery_file_dest;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      D:\app\flash_recovery_area
db_recovery_file_dest_size           big integer 3912M

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Now Configure In RMAN
=============================================
C:\Users\rajib.pradhan>SET ORACLE_SID=ORCL

C:\Users\rajib.pradhan>RMAN TARGET /

Recovery Manager: Release 11.2.0.1.0 - Production on Sun Feb 1 02:03:58 2015

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

connected to target database: ORCL (DBID=1397951475)

RMAN> SHOW ALL;

using target database control file instead of recovery catalog
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 OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
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 ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\APP\PRODUCT\11.2.0\DBHOME_1\DATABASE\SNCFORCL.ORA'; # default

Configure Control File Auto backup Mode.
=============================================
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

Configure Control File Backup Destination
=============================================
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'D:\app\RMAN_BACKUP\cf_%F';

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'D:\app\RMAN_BACKUP\cf_%F';
new RMAN configuration parameters are successfully stored

Configure Datafile Backup Destination
=============================================
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'K:\ORCL_RMAN_BACKUP\df_%d_%t_%s_%p';

new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   'K:\ORCL_RMAN_BACKUP\df_%d_%t_%s_%p';
new RMAN configuration parameters are successfully stored

Backup Database.
=============================================
RMAN> BACKUP DATABASE;

Starting backup at 01-FEB-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=D:\APP\ORADATA\ORCL\SYSTEM01.DBF
input datafile file number=00002 name=D:\APP\ORADATA\ORCL\SYSAUX01.DBF
input datafile file number=00005 name=D:\APP\ORADATA\ORCL\EXAMPLE01.DBF
input datafile file number=00003 name=D:\APP\ORADATA\ORCL\UNDOTBS01.DBF
input datafile file number=00004 name=D:\APP\ORADATA\ORCL\USERS01.DBF
channel ORA_DISK_1: starting piece 1 at 01-FEB-15
channel ORA_DISK_1: finished piece 1 at 01-FEB-15
piece handle=D:\APP\RMAN_BACKUP\DF_ORCL_870488830_4_1 tag=TAG20150201T022710 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55
Finished backup at 01-FEB-15

Starting Control File and SPFILE Autobackup at 01-FEB-15
piece handle=D:\APP\RMAN_BACKUP\CF_C-1397951475-20150201-01 comment=NONE
Finished Control File and SPFILE Autobackup at 01-FEB-15

Checking Backup List.
=============================================
RMAN> LIST BACKUP;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2       Full    1.04G      DISK        00:00:49     01-FEB-15
        BP Key: 2   Status: AVAILABLE  Compressed: NO  Tag: TAG20150201T022710
        Piece Name: D:\APP\RMAN_BACKUP\DF_ORCL_870488830_4_1
  List of Datafiles in backup set 2
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 1170457    01-FEB-15 D:\APP\ORADATA\ORCL\SYSTEM01.DBF
  2       Full 1170457    01-FEB-15 D:\APP\ORADATA\ORCL\SYSAUX01.DBF
  3       Full 1170457    01-FEB-15 D:\APP\ORADATA\ORCL\UNDOTBS01.DBF
  4       Full 1170457    01-FEB-15 D:\APP\ORADATA\ORCL\USERS01.DBF
  5       Full 1170457    01-FEB-15 D:\APP\ORADATA\ORCL\EXAMPLE01.DBF

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3       Full    9.36M      DISK        00:00:01     01-FEB-15
        BP Key: 3   Status: AVAILABLE  Compressed: NO  Tag: TAG20150201T022806
        Piece Name: D:\APP\RMAN_BACKUP\CF_C-1397951475-20150201-01
  SPFILE Included: Modification time: 01-FEB-15
  SPFILE db_unique_name: ORCL
  Control File Included: Ckp SCN: 1170900      Ckp time: 01-FEB-15

RMAN> EXIT

Recovery Manager complete.

C:\Users\rajib.pradhan>

ORA-00205: error in identifying control file, check alert log for more info

Cause :- This error can occur for lost/corrupted any control file.
In my situation I have unfortunately lost one of control file from two files.

Solution :- Create pfile from spfile > Edit pfile > Change "*.control_files=" parameter. Open database using pfile.

SQL> select status from v$instance;

STATUS
------------
STARTED

SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-00205: error in identifying control file, check alert log for more info


SQL> show parameter contr

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      D:\APP\ORADATA\DBRMAN\CONTROL0
                                                 1.CTL, D:\APP\FLASH_RECOVERY_A
                                                 REA\DBRMAN\CONTROL02.CTL
control_management_pack_access       string      DIAGNOSTIC+TUNING
SQL> create pfile='D:\pfile.ora' from spfile;

File created.

Now edit pfile

=================================== Old Pfile ===================================================
dbrman.__db_cache_size=218103808
dbrman.__java_pool_size=4194304
dbrman.__large_pool_size=4194304
dbrman.__oracle_base='D:\app'#ORACLE_BASE set from environment
dbrman.__pga_aggregate_target=255852544
dbrman.__sga_target=381681664
dbrman.__shared_io_pool_size=0
dbrman.__shared_pool_size=146800640
dbrman.__streams_pool_size=0
*.audit_file_dest='D:\app\admin\DBRMAN\adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='D:\app\oradata\DBRMAN\control01.ctl','D:\app\oradata\DBRMAN\control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='DBRMAN'
*.db_recovery_file_dest='D:\app\flash_recovery_area'
*.db_recovery_file_dest_size=4039114752
*.diagnostic_dest='D:\app'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=DBRMANXDB)'
*.memory_target=634388480
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
==================================== After Editing New Pfile ==========================================
dbrman.__db_cache_size=218103808
dbrman.__java_pool_size=4194304
dbrman.__large_pool_size=4194304
dbrman.__oracle_base='D:\app'#ORACLE_BASE set from environment
dbrman.__pga_aggregate_target=255852544
dbrman.__sga_target=381681664
dbrman.__shared_io_pool_size=0
dbrman.__shared_pool_size=146800640
dbrman.__streams_pool_size=0
*.audit_file_dest='D:\app\admin\DBRMAN\adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='D:\app\oradata\DBRMAN\control01.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='DBRMAN'
*.db_recovery_file_dest='D:\app\flash_recovery_area'
*.db_recovery_file_dest_size=4039114752
*.diagnostic_dest='D:\app'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=DBRMANXDB)'
*.memory_target=634388480
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
============================================================================================================

SQL> ALTER DATABASE MOUNT;
ALTER DATABASE MOUNT
*
ERROR at line 1:
ORA-00205: error in identifying control file, check alert log for more info


SQL> SHUTDOWN IMMEDIATE;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> CONN /AS SYSDBA
Connected to an idle instance.
SQL> STARTUP PFILE=D:\pfile.ora;
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1375792 bytes
Variable Size             310378960 bytes
Database Buffers          218103808 bytes
Redo Buffers                5804032 bytes
Database mounted.
Database opened.
SQL>

SQL> create spfile from pfile='D:\pfile.ora';

File created.

Enable Telnet command line utilities in Windows 7

To enable Telnet command line utilities:
Start
> Control Panel
>Programs and Features
>Turn Windows features on or off.
>check the Telnet Client check box.
>Click OK.
The system installs the appropriate files. This will take a few seconds to a minute.

ORA-12560: TNS:protocol adapter error

Cause :- In my case I have two database in my PC. One is ORCL and another is DBRMAN my default ORACLE_SID is ORCL when I am trying to connect DBRMAN database I get this error.

Solution :- For raising this error you can check after set your SID in which database you want to connect.

SQL> conn /as sysdba
ERROR:
ORA-12560: TNS:protocol adapter error


SQL> exit

C:\Users\rajib.pradhan>set ORACLE_SID=DBRMAN

C:\Users\rajib.pradhan>sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Tue Jul 8 11:47:44 2014

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

SQL> conn /as sysdba
Connected.
SQL>

How to switch/show current log list

C:\Users\rajib.pradhan>SQLPLUS /NOLOG

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jul 2 12:02:23 2014

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

SQL> CONN /AS SYSDBA
Connected.
SQL>
SQL> ARCHIVE LOG LIST
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            D:\app\oradata\ARCHIVE\
Oldest online log sequence     1547
Next log sequence to archive   1549
Current log sequence           1549
SQL>

SQL> ALTER SYSTEM SWITCH LOGFILE;

System altered.

SQL> ARCHIVE LOG LIST
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            D:\app\oradata\ARCHIVE\
Oldest online log sequence     1548
Next log sequence to archive   1550
Current log sequence           1550
SQL> ALTER SYSTEM SWITCH LOGFILE;

System altered.

SQL> ARCHIVE LOG LIST
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            D:\app\oradata\ARCHIVE\
Oldest online log sequence     1549
Next log sequence to archive   1551
Current log sequence           1551
SQL>

How to null log_archive_dest parameter

==================================================================
In this case if database are archive log mode database will send archive log file in DB_RECOVERY_FILE_DEST (Value of this Parameter).
==================================================================

SQL> ALTER SYSTEM SET log_archive_dest_2='' SCOPE=BOTH;

System altered.

SQL> ALTER SYSTEM SET log_archive_dest_3='' SCOPE=BOTH;

System altered.

SQL> ALTER SYSTEM SET log_archive_dest_4='' SCOPE=BOTH;

System altered.