Recover Tablespace

Oracle Recover Tablespace . 

ERROR at line 1:

ORA-00376: file 5  cannot be read at this time

ORA-01110: data file 5: '/u01/app/oracle/oradata/oraeed/anuj01.dbf'

for search key !!

ORA-00376: file   cannot be read at this time

ORA-01110: data file : 

Taking Backup .. 

RUN {

configure controlfile autobackup on;

set command id to 'ORCLEEDBackupFull';

ALLOCATE CHANNEL c1 DEVICE TYPE disk;

backup AS COMPRESSED BACKUPSET full database tag ORCLEED_FULL format '/u01/app/oracle/RmanBackup/%d_%T_%s_%p_FULL' ;

sql 'alter system archive log current';

backup tag ORCLEED_ARCHIVE format '/u01/app/oracle/RmanBackup/%d_%T_%s_%p_ARCHIVE' archivelog all delete all input ;

backup tag ORCLEED_CONTROL current controlfile format '/u01/app/oracle/RmanBackup/%d_%T_%s_%p_CONTROL';

release channel c1;

}

RMAN>

RUN {

configure controlfile autobackup on;

RMAN> 2> set command id to 'ORCLEEDBackupFull';

ALLOCATE CHANNEL c1 DEVICE TYPE disk;

backup AS COMPRESSED BACKUPSET full database tag ORCLEED_FULL format '/u01/app/oracle/RmanBackup/%d_%T_%s_%p_FULL' ;

sql 'alter system archive log current';

backup tag ORCLEED_ARCHIVE format '/u01/app/oracle/RmanBackup/%d_%T_%s_%p_ARCHIVE' archivelog all delete all input ;

backup tag ORCLEED_CONTROL current controlfile format '/u01/app/oracle/RmanBackup/%d_%T_%s_%p_CONTROL';

release channel c1;

}

3> 4> 5> 6> 7> 8> 9> 10>

using target database control file instead of recovery catalog

old RMAN configuration parameters:

CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters:

CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters are successfully stored

executing command: SET COMMAND ID

allocated channel: c1

channel c1: SID=42 device type=DISK

Starting backup at 15-JAN-17

channel c1: starting compressed full datafile backup set

channel c1: specifying datafile(s) in backup set

input datafile file number=00001 name=/u01/app/oracle/oradata/oraeed/system01.dbf

input datafile file number=00002 name=/u01/app/oracle/oradata/oraeed/sysaux01.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/oraeed/undotbs01.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/oraeed/users01.dbf

channel c1: starting piece 1 at 15-JAN-17

channel c1: finished piece 1 at 15-JAN-17

piece handle=/u01/app/oracle/RmanBackup/ORAEED_20170115_18_1_FULL tag=ORCLEED_FULL comment=NONE

channel c1: backup set complete, elapsed time: 00:05:16

Finished backup at 15-JAN-17

Starting Control File and SPFILE Autobackup at 15-JAN-17

piece handle=/u01/app/oracle/RmanBackup/c-2939668799-20170115-08.bck comment=NONE

Finished Control File and SPFILE Autobackup at 15-JAN-17

sql statement: alter system archive log current

Starting backup at 15-JAN-17

current log archived

channel c1: starting archived log backup set

channel c1: specifying archived log(s) in backup set

input archived log thread=1 sequence=9 RECID=9 STAMP=933362415

input archived log thread=1 sequence=10 RECID=10 STAMP=933367281

input archived log thread=1 sequence=11 RECID=11 STAMP=933367281

channel c1: starting piece 1 at 15-JAN-17

channel c1: finished piece 1 at 15-JAN-17

piece handle=/u01/app/oracle/RmanBackup/ORAEED_20170115_20_1_ARCHIVE tag=ORCLEED_ARCHIVE comment=NONE

channel c1: backup set complete, elapsed time: 00:00:01

channel c1: deleting archived log(s)

archived log file name=/u01/app/oracle/ArchiveEED/1_9_933325760.dbf RECID=9 STAMP=933362415

archived log file name=/u01/app/oracle/ArchiveEED/1_10_933325760.dbf RECID=10 STAMP=933367281

archived log file name=/u01/app/oracle/ArchiveEED/1_11_933325760.dbf RECID=11 STAMP=933367281

Finished backup at 15-JAN-17

Starting backup at 15-JAN-17

channel c1: starting full datafile backup set

channel c1: specifying datafile(s) in backup set

including current control file in backup set

channel c1: starting piece 1 at 15-JAN-17

channel c1: finished piece 1 at 15-JAN-17

piece handle=/u01/app/oracle/RmanBackup/ORAEED_20170115_21_1_CONTROL tag=ORCLEED_CONTROL comment=NONE

channel c1: backup set complete, elapsed time: 00:00:01

Finished backup at 15-JAN-17

Starting Control File and SPFILE Autobackup at 15-JAN-17

piece handle=/u01/app/oracle/RmanBackup/c-2939668799-20170115-09.bck comment=NONE

Finished Control File and SPFILE Autobackup at 15-JAN-17

released channel: c1

=============

set linesize 200

col FILE_NAME for a70

SELECT TABLESPACE_NAME, FILE_NAME FROM DBA_DATA_FILES ORDER BY TABLESPACE_NAME;

TABLESPACE_NAME                FILE_NAME

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

SYSAUX                         /u01/app/oracle/oradata/oraeed/sysaux01.dbf

SYSTEM                         /u01/app/oracle/oradata/oraeed/system01.dbf

UNDOTBS1                       /u01/app/oracle/oradata/oraeed/undotbs01.dbf

USERS                          /u01/app/oracle/oradata/oraeed/users01.dbf

Created tablespace ....

SQL> CREATE TABLESPACE anuj DATAFILE '/u01/app/oracle/oradata/oraeed/anuj01.dbf' SIZE 10M  AUTOEXTEND ON;

Tablespace created.

set linesize 200

col FILE_NAME for a70

SELECT TABLESPACE_NAME, FILE_NAME FROM DBA_DATA_FILES ORDER BY TABLESPACE_NAME;

SQL> SQL>

TABLESPACE_NAME                FILE_NAME

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

ANUJ                           /u01/app/oracle/oradata/oraeed/anuj01.dbf  ----<<<<<<

SYSAUX                         /u01/app/oracle/oradata/oraeed/sysaux01.dbf

SYSTEM                         /u01/app/oracle/oradata/oraeed/system01.dbf

UNDOTBS1                       /u01/app/oracle/oradata/oraeed/undotbs01.dbf

USERS                          /u01/app/oracle/oradata/oraeed/users01.dbf

SQL> create table anuj_table tablespace anuj as select * from dual;

Table created.

SQL> select * from anuj_table;

D

-

X

SQL> !rm /u01/app/oracle/oradata/oraeed/anuj01.dbf

SQL> !ls -ltr /u01/app/oracle/oradata/oraeed/

total 1367728

drwxr-x--- 3 oracle oinstall      4096 Jan 15 09:09 ORAEED

-rw-r----- 1 oracle oinstall  20979712 Jan 15 10:09 temp01.dbf

-rw-r----- 1 oracle oinstall  31465472 Jan 15 20:36 undotbs01.dbf

-rw-r----- 1 oracle oinstall 492838912 Jan 15 20:36 sysaux01.dbf

-rw-r----- 1 oracle oinstall   5251072 Jan 15 20:36 users01.dbf

-rw-r----- 1 oracle oinstall  52429312 Jan 15 20:41 redo01.log

-rw-r----- 1 oracle oinstall  52429312 Jan 15 20:41 redo02.log

-rw-r----- 1 oracle oinstall 702554112 Jan 15 20:53 system01.dbf

-rw-r----- 1 oracle oinstall  52429312 Jan 15 20:54 redo03.log

-rw-r----- 1 oracle oinstall  10076160 Jan 15 20:55 control01.ctl

SQL> insert into anuj_table values('t');

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> insert into anuj_table values('t');

insert into anuj_table values('t')

            *

ERROR at line 1:

ORA-00376: file 5 cannot be read at this time

ORA-01110: data file 5: '/u01/app/oracle/oradata/oraeed/anuj01.dbf'

Starting recovery .... 

SQL> alter tablespace anuj offline immediate;

Tablespace altered.

SQL> exit

Disconnected from Oracle Database 11g Release 11.2.0.1.0 - 64bit Production

[oracle@ora RmanBackup]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Sun Jan 15 21:05:44 2017

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

connected to target database: ORAEED (DBID=2939668799)

RMAN> restore datafile 5;

Starting restore at 15-JAN-17

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=35 device type=DISK

creating datafile file number=5 name=/u01/app/oracle/oradata/oraeed/anuj01.dbf

restore not done; all files read only, offline, or already restored

Finished restore at 15-JAN-17

RMAN> recover datafile 5;

Starting recover at 15-JAN-17

using channel ORA_DISK_1

starting media recovery

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

Finished recover at 15-JAN-17

[oracle@ora RmanBackup]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Jan 15 21:08:45 2017

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

Connected to:

Oracle Database 11g Release 11.2.0.1.0 - 64bit Production

SQL> select * from anuj_table ;

select * from anuj_table

              *

ERROR at line 1:

ORA-00376: file 5 cannot be read at this time

ORA-01110: data file 5: '/u01/app/oracle/oradata/oraeed/anuj01.dbf'

SQL> alter tablespace anuj online;

Tablespace altered.

SQL> select * from anuj_table ;

D

-

X

t