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
